15 maneras en Excel para el análisis de datos rápidas y potentes

Para empezar has de saber que en este post vas a conocer varias acciones en concreto 15 maneras en Excel para el análisis de datos con las hojas de cálculo Excel rápidas y potentes, en concreto 15 maneras en Excel para el análisis de datos, que te van a ser de gran ayuda a la hora preparar y realizar análisis de los datos de la empresa.

Si te ha interesado el título de este post también te será útil: 101 ideas Excel para mejorar tus destrezas.

En primer lugar, aprovecho al contarte las 15 maneras en Excel para el análisis de datos, para darte un pequeño consejo:

Confía en tus datos, un buen análisis de los datos es similar a tener una conversación privada con la empresa o el negocio del que tienes los datos.

En dicha conversación el negocio te está diciendo exactamente lo que puede hacer para ayudar a que mejore y entender las causas de los diferentes comportamientos.

Las nuevas herramientas de Excel usan los datos y los transforman en información, en conocimiento imprescindible, que te ayudan a tomar decisiones estratégicas de manera más rápida y reduciendo el margen de error.

Además cómo te imaginas me estoy refiriendo tanto a datos de la facturación, contabilidad, de recursos humanos, de los impuestos, del mercado, de la competencia,…

Así que estas son las 15 maneras en Excel rápidas para el análisis de los datos:

(La primera mitad de las mismas se encuadran en un nivel básico a medio de Excel, y el resto en nivel intermedio a avanzado).

1. Filtros texto.
2. Filtros por color.
3. Ordenar.
4. Subtotales.
5. Formato Condicional Reglas de celdas.
6. Formato Condicional Color e iconos.
7. Formato Condicional Crear Reglas nuevas.
8. Buscar objetivo.
9. Escenarios.
10. Texto en columnas.
11. Tablas dinámicas.
12. Solver.
13. Validación de datos.
14. Gráficos.
15. Minigráficos.
16. 15+1 Segmentadores.

Vas a ver una a una a ver las diferentes formas de análisis de datos con Excel:                            

1. Filtros texto.

A la hora de realizar informes y controlar la evolución de la actividad de la empresa o negocio necesitarás llevar a cabo filtrado de datos para obtener la información que estás buscando.

Con el uso de los filtros en Excel lo que haces es trabajar con un subconjunto de datos, de hecho temporalmente desaparecerán de la pantalla el resto de datos.

Por lo tanto te puedes centrar en los que te interesan en ese momento.

Resumiendo, estableciendo un filtro en la hoja de cálculo Excel (u otras como por ejemplo Google hojas de cálculo en Drive):

Lo que haces es seleccionar los registros dentro de la base de datos que dispones para que se muestren si cumplen un o unos criterios especificados por ti.

Para acceder a la herramienta Filtros en Excel 2007 a 2016 estos son los pasos:

• Haz clic en la pestaña Datos.
• Luego en el grupo Ordenar y Filtrar.
• Pulsa en el icono Filtro.

En la fila donde tienes los datos de cada columna, también llamados campos, aparece un triángulo negro que pulsando sobre el despliega la lista de condiciones de cada campo.

Finalmente, selecciona los que necesites en ese momento el resto desaparecen momentáneamente de la pantalla si bien cuando quitas el filtro vuelven a aparecer.

Además si quieres hacer un ejercicio sobre este recurso Excel puedes visitar este post: Filtro en Excel de una base de datos.

2. Filtros por color.

Análogamente a lo explicado en el apartado anterior, en este caso te comento que en Excel es posible filtrar por el color de las celdas.

Pero OJO: Previamente han de tener algún color las mismas.

Por ejemplo suponte que tienes una tabla con las cifras de ventas de la empresa y has establecido el relleno de las celdas de color verde para las cifras  que superan los 1000 euros

Por lo tanto a la hora de establecer el filtro en esa tabla se aparece una opción nueva que si las celdas no tenían color no aparece.

Y además si la eliges te muestra el subconjunto de datos con las celdas cuyo color es el verde

Ejercicio fácil 15 maneras en Excel para el análisis de datos.

En primer lugar copia estos datos en Excel y da formato de relleno de celda en verde en las celdas que contienen el dato 1000.

Ventas 

500

200

1000

800

1000

Cuando apliques el filtro por color solo se muestran las dos que reflejan la cifra de 1000 euros.

Esta tabla de datos es muy sencilla pero imagina el potencial para listas largas.

Además también se puede dar no solo uno sino varios y también dar color a la fuente.

3. Ordenar.

El programa de hojas de cálculo Excel te permite trabajar con listados o bases de datos y entre otras funciones puedes ordenar los datos de tus listas.

Puedes empezar a Ordenar datos en Excel a través de los iconos del orden ascendente AZ o descendente ZA.

Así que lo que haces es realizar con los datos de la columna seleccionada un orden de mayor a menor o viceversa, lo que te permitirá una búsqueda de información rápida.

4. Subtotales.

Con la herramienta llamada Subtotales de Excel lo que puedes hacer es de forma automática obtener de forma resumida una serie de datos de tu hoja de cálculo.

Por lo tanto esta acción Excel te permite realizar un análisis de los datos de forma rápida realizando agrupaciones.

Y a la vez (y a diferencia de los filtros) realiza cálculo sobre la selección, lo más habitual es hacer sumas de subconjuntos de datos.

Si bien los subtotales calculados además de ser sumas, también pueden calcular promedios, productos, máximos, mínimos,…

Así no tienes que realizar por separado las acciones de filtrar y las de cálculo, sino que en una sola acción lo tienes todo.

Igualmente para practicar y hacer ejercicios de Subtotales visita: Crea subtotales y analiza los datos eficazmente.

5. Formato Condicional Reglas de celdas.

Establece que formato han de tener las celdas en función de unos valores que cumplan o no.

Resaltar reglas de celdas:

• Es mayor que…
• Es menor que…
• Entre…
• Es igual a…
• Texto contiene…
• Una fecha…
• Duplicar valores…
• Más reglas…

Con las que tras seleccionar un conjunto de celdas a las que quieras aplicar formato.

Puedes realizar las acciones siguientes:

Es mayor que…:

Haz clic sobre esta  opción y se abre una nueva ventana.

Has de indicar en el rectángulo blanco el valor de referencia para dar formato a las celdas mayores a dicho valor,

A continuación en el siguiente rectángulo se especifica el formato que viene dado por defecto en Excel, pero puedes desplegar en el triángulo negro y optar por otras posibilidades.

Ejemplo:

Dar formato condicional a las celdas que superen el valor 100, te colorea todas las celdas seleccionadas con importe mayor a 100.

Es menor que…:

Análogamente a la opción anterior si bien para resaltar las celdas cuyos valores sean menores al especificado.

Ejemplo: dar formato condicional a las celdas que sean inferiores al valor 100, te coloreara todas las celdas seleccionadas con importe menor a 100.

Entre…:

En este caso al elegir esta opción la ventana que se abre nos muestra dos rectángulos blancos en los que escribir los dos valores entre los que quieres que se encuentren los valores a dar formato.

Ejemplo: Dar formato a las celdas que se encuentren entre los valores 100 y 200, te da formato a todas las casillas que tengan valores comprendidos entre ambos valores.

Es igual a…:

En este caso el formato se aplica a las celdas que coinciden exactamente con el valor indicado.

Texto contiene…: Análogamente en relación a datos de texto.

Una fecha…: No se trata de una fecha concreta sino de una serie de valores predefinidos, como Ayer, Hoy, Mañana,…Despliega en el triángulo negro para verlas todas.

Duplicar valores…: Encuentra valores duplicados en las celdas seleccionadas y les aplica el formato que se establezca.

Más reglas…: Permite introducir nuevas reglas de formato de forma específica.

Reglas superiores e inferiores: 

• 10 superiores
• 10% de valores superiores
• 10 inferiores
• 10% de valores inferiores
• Por encima del promedio
• Por debajo del promedio

• Más reglas…

 Por su propia descripción ves lo que aplica para generar el formato.

Más reglas…: Permite introducir nuevas reglas de formato de forma específica.

Reglas superiores e inferiores:

Siguiendo con los datos del ejercicio anterior, yo he realizado la condición de que me muestre y el resultado es la celda con el dato 200 aparece en color de fuente y fondo diferente.

6. Formato Condicional Color e iconos.

Esta herramienta Excel es de las más rápidas y visuales para trabajar con datos.

Simplemente selecciona un rango de datos y pulsa en la pestaña de Inicio en el triángulo negro al lado de donde pone Formato condicional y luego en una de las siguientes opciones:

• Barra de datos: se te mostrará con un mayor tono o intensidad de color aquellos valores cuyo importe sea superior.
• Escalas de color: similar con diferentes colores y reglas según valores cuyo importe sea superior.
• Conjunto de iconos: Semáforo, tics de verificación, flechas, banderas…

Practica con los mismos datos de antes:

Ventas 

500

200

1000

800

1000

Para empezar selecciona los números y seguidamente haz clic siguiendo la ruta que te he dicho arriba.

Con la previsualización si te detienes en cada una de las opciones puedes ver cómo va quedando.

Por ejemplo yo he elegido las flechas de Conjunto de iconos, como puedes ver en la imagen:

A partir de aquí de las 15 maneras en Excel para el análisis de datos, están son de nivel algo más avanzado.

7. Conoce Formato Condicional Crear Reglas nuevas.

Además te todo lo visto en los puntos anteriores, aquí vas a poder crear tus propias reglas de formatos.

Por lo tanto los pasos para aplicar formatos condicionales personalizados:

1.- Seleccionar las celdas a las que se quiere dar formato.

2.- Pinchar en el menú Inicio, en el grupo de trabajo Estilos, en el icono donde pone Formato condicional.

Administrar reglas – Nueva regla – Aplicar formato únicamente a las celdas que  contengan – Editar una descripción de regla.

Así que aparecerá una ventana donde se establece la condición y aplicar el formato deseado:

• En el primer apartado, elije Valor de la celda.
• En el segundo, marca la opción que desees realizar respecto a la condición (mayor que, menor que, igual a, etc. Fíjate que cambia la pantalla en función de la condición que escojas).
• En el tercer apartado, escribe la palabra para cumplir la condición.
• Así ya tienes especificada la condición.
• Ahora debes indicar qué formato tendrán las celdas que cumplan con dicha condición.
• Haz clic en el botón Formato…, en el cuadro de diálogo que se abre, clic en las opciones que se necesitan.
• Luego pulsa en Aceptar para volver al cuadro de Formato condicional.
• Para especificar dos condiciones más, oprima Agregar; repita los pasos.
• Con un clic en Aceptar, terminará la especificación del formato.

8. Buscar objetivo.

Buscar objetivo en Excel consiste en encontrar un resultado deseado mediante la modificación del valor de una determinada celda de la que va a depender el valor deseado.

Empezaré con un ejemplo sencillo:

Quieres comprar un nuevo mobiliario para tu oficina que vale 1.200  euros y lo vas a pagar a plazos.

El número de plazo de pago que estableces con el vendedor es de 24 meses, a un tipo de interés del 10% te sale una cuota a pagar de 55 euros.

Pero resulta que no puedes pagar esa cantidad mensual, solo puedes hacer frente al pago de 30 euros al mes, esto alarga los pagos a realizar.

Al usar la herramienta Excel Buscar objetivo encontrarás rápidamente la respuesta acerca de en cuantos plazos se ha de pagar la compra.

Por lo tanto los pasos básicos en el programa Excel son:

• Desde la pestaña Datos.
• Herramientas de análisis, haz clic en el triángulo negro al lado de Análisis Y sí.
• Pulsa en Buscar objetivo.
• Se abre una ventana en la que vas a colocar los datos.

Descubre ejercicios sobre Buscar objetivo en: Buscar objetivo en Excel.

9.Escenarios.

Con la herramienta Escenarios de Excel se calcula en una misma celda diferentes valores en función de los posibles escenarios o situaciones hipotéticas.

Un escenario es un conjunto de valores que se utilizan para prever los resultados de un modelo.

Así que con ellos se puede responder a preguntas del tipo:

• Sí cambia el tipo de interés de mi hipoteca cuál será el nuevo capital a devolver o en el mismo ejemplo cuál será la nueva cuota mensual a pagar.
• Cuál es la previsión de ventas dadas unas condiciones del mercado: situación pesimista, normal, pesimista.

Accede a Escenarios en Excel: analiza distintas alternativas y compáralas para practicarlos.

10. Texto en columnas.

Así que por lo general este recurso suele ser muy útil, ya que Convertir Texto en columnas te ayuda a la hora de separar datos que están en la misma celda y  te interesa separar unos de otros y se sitúen cada uno en columnas distintas.

El ejemplo más usado es que en la misma columna tienes nombre y apellidos de una persona y quieres que en una columna aparezcan los nombre y en la otra los apellidos.

Por lo general puedes hacerlo  desde la ficha de Datos encuentras el icono Texto en columnas y basta con que pinches sobre él, tras indicar la columna en la que tiene los datos y seguir los pasos que te indica el asistente sin apenas variar nada para los casos más sencillos.

Como ejemplo escribe en la celda A1 tu nombre y apellidos, selecciona la columna A, haz clic en Datos y luego en Texto en columnas  y sin cambiar nada más avanza en las diferentes ventanas pulsando el botón Siguiente y por último el botón Finalizar.

11. Tablas dinámicas.

Una tabla dinámica sirve para obtener informes resumidos a partir de los datos que tienes en una hoja de cálculo.

Aunque la herramienta Subtotales te resulte bastante práctica,preferirán sin duda trabajar con las tablas dinámicas

Esta aparecerá en una hoja de cálculo distinta a la que tiene los datos, eso sí en el mismo libro.

Además de otras importantes utilidades como cálculos, ordenaciones, formatos,…

También es posible en Excel hacer gráficos dinámicos, sigue el enlace para saber cómo.

Accede a este post del blog sobre tablas dinámicas para ampliar conocimientos y hacer un ejercicio.

Una vez aprendas a crear tablas dinámicas podrás también modificarlas. Consulta mi ebook  tablas dinámicas (incluidas consultas por email) para aprender aún más sobre el tema.

Ebook sobre Tablas dinámicas: Ya a la venta. 

12. Solver.

Otro recurso interesante es Solver es una herramienta de análisis que tienes en el programa Excel, aplicado sobre todo en el mundo empresarial.

Sirve para realizar problemas de programación lineal en donde se ha de calcular el valor de una celda.

Esta depende de diversos factores o variables cuando a la vez existen una serie de restricciones que han de cumplirse.

Finalmente totalmente recomendable leer y practicar desde este post de mi otro blog: Solver en Excel.

13. Validación de datos.

Esta herramienta te ayuda a la hora de introducir datos en la hoja de Excel.

Consiste en fijar unos límites a los datos de una hoja electrónica.

Estos límites bien referidos a una celda, a una fila o columna, bien en relación a datos de tipo número, texto o fórmula.

Así que te servirá para evitar introducción de datos erróneos.

Al usar una hoja con Validación de datos mostrará un mensaje de advertencia o incluso no dejara introducir ciertos valores según como la crees.

14.Gráficos.

Continuando con estas 15 maneras en Excel para el análisis de datos hablamos de gráficos:

Los tipos de gráficos que puedes realizar en el programa hojas de cálculo Excel y en otras similares son variados.

Por eso puedes usar  diferentes opciones de gráficos para reflejar la información de tus datos y que al realizar un gráfico elijas el que mejor representa los datos que tienes.

• Gráficos de columnas.
• Gráficos de barras.
• Gráficos de líneas.
• Gráficos circulares.
• Gráficos XY (Dispersión).
• Gráficos de áreas.
• Gráficos de anillos.
• Gráficos radiales.
• Gráficos de superficie.
• Gráficos de burbujas.
• Gráficos de cotizaciones.
• Gráficos con marcadores en forma de cilindro, cono o pirámide.

Así que recuerda que una vez creado también puedes editar un gráfico en Excel para personalizarlo.

Además es posible crear una plantilla Excel del gráfico para guardarlo y usarlo en posteriores informes.

Puedes leer en mi otro blog sobre: Paso a paso hacer un gráfico circular.

15. Minigráficos.

Esta opción es muy rápida y visual, consiste en que aparezca un gráfico pequeño en una celda que recoja una serie de datos.

En nuestro ejemplo de antes, si seleccionas los datos y después pulsas en la pestaña Insertar y luego en Minigráficos.

Eliges uno de ellos por ejemplo Columna, a continuación indica dónde, en qué celda quieres dicho Minigráfico, pulsar aceptar.

Por lo tanto obtendrás una representación gráfica que acompaña a los números y hace más intuitiva la interpretación de la serie.

15+1 Segmentadores.

Excel filtrar datos en las tablas dinámicas usando los segmentadores a partir de los encabezados de las tablas, tanto en formatos tabla como en tablas dinamicas, puedes saber mas en este post de mi blog: Excel filtrar datos en las tablas dinámicas.

Concluyendo 15 maneras en Excel para el análisis de datos:

En resumen hasta aquí, 15 maneras en Excel para el análisis de datos y sus aplicaciones.

Ahora si tienes un momento me gustaría saber cuál o cuáles de los consejos que te he dado han sido de mayor interés para ti.

Y también si has encontrado alguna que te  haya sorprendido o agradado especialmente.

Finalmente si hay alguna herramienta que crees que se me ha olvidado y debería estar en estas 15 maneras en Excel para el análisis de datos, comenta al pie del post.

También puedes escribirme al email de este site: hola@excelcontabilidadytic.com.