Con mucha frecuencia me encuentro con gente que usa Excel como una calculadora. Siempre pienso… “ay si supieran usar Excel para análisis de datos”. Pero ¿qué es el análisis de datos? Pues muy sencillo. Según Wikipedia es: “inspeccionar, limpiar y transformar datos con el objetivo de resaltar información útil, lo que sugiere conclusiones, y apoyo en la toma de decisiones.”
¿Por qué hacer análisis de datos con Excel?
Excel fue creado básicamente para poder analizar datos. Si te fijas, su configuración no es la de una calculadora, sino la de una tabla. Es decir, lo que ves en Excel es una hoja (o varias hojas) con filas y columnas… lo que divide la hoja en celdas. Esta es una visión normal de Excel según abrimos el programa.
La idea de Excel es poder usar grandes cantidades de datos. Pero para ello habrá que procesarlos, limpiarlos y analizarlos. A continuación vamos a ir viendo los principales pasos que hay que dar para hacer un análisis de datos correcto.
Procesar datos (obtención y volcado de datos en Excel)
El procesamiento de datos es algo que va desde muy sencillo hasta muy complicado. Podemos procesar datos de las siguientes maneras:
- Crear los datos nosotros mismos: para ello iremos alimentando filas y columnas de Excel insertando los datos manualmente. Esto, en muchas ocasiones es lo único que podremos hacer. Si, es un rollo pero es lo que hay.
- Copiar y pegar datos: en ocasiones podremos copiar los datos de alguna fuente de datos. Una tabla de Excel de otro sitio, datos de una página web, etc.
- Obtener datos de una BBDD en Excel: por ejemplo, podremos obtener datos de una tabla de Access o de una query de Access (importar datos de Access). Con estos sencillos pasos aprenderás a traer datos de una tabla de Access en un abrir y cerrar de ojos.. También podremos obtener datos de otros libros de Excel o de otras fuentes de datos accesibles online. Para llevar a cabo este proceso tenemos multitud de opciones. En mi caso, yo suelo descargarme directamente datos de mi BBDD (según la empresa en la que trabaje) para poder crear los cuadros de mando de Excel. Las opciones disponibles son:
Trabajar con datos en Excel: las tablas
Si trabajamos con grandes cantidades de datos, o si trabajas con pequeñas cantidades siempre será bueno que crees tablas de datos. ¿Por qué crear tablas en Excel? Muy sencillo, hay poderosas razones que te ayudarán a decantarte por ello.
Algunas de estas razones son:
- Crear fórmulas es mucho más intuitivo
- Puedes personalizar la manera en la que se visualizan los datos.
- Es un objeto en Excel por lo que tiene entidad propia (nombre, rango definido…) y esto es ideal para trabajar con tablas dinámicas.
- Al moverse de arriba a abajo con la hoja, si tienes muchos datos, la cabecera siempre estará visible.
Como ves, son razones muy importantes. Quizás todavía no lo aprecies pero no te preocupes que pronto empezarás a hacerlo.
Limpiar datos en Excel
Quizás sea una de las peores tareas a las que deba enfrentarse un analista de datos. La limpieza de los datos en Excel es clave para poder realizar cualquier análisis decente. ¿Por qué? Porque si tus datos no están claros o están desestructurados será muy difícil que saques conclusiones interesantes.
El típico caso es aquel en que un mismo dato puede estar escrito de manera muy similar pero no es exactamente igual. Por ejemplo: Terminado y Terminada. La diferencia está en la última letra pero para Excel esto no es la misma palabra y por lo tanto, si queremos analizar información deberemos unificar todos estos datos en Excel.
Hay das maneras de hacerlo como veremos.
Buscar y reemplazar
Mediante la funcionalidad de buscar y reemplazar. En la siguiente imagen puedes encontrar las opciones disponibles… y no son pocas. Ser
Filtrar datos
Esta opción es muy buena y permite ir haciendo un pequeño análisis de nuestra tabla de datos para ir modificando los valores que necesitemos poco a poco.
En este tutorial puedes ver todo lo que necesitas saber sobre el filtrado de datos. Ordenar y filtrar: pequeños trucos que harán tú vida, en cuanto al uso de tablas se refiere, mucho más sencilla. Todo lo que necesitas para ordenar y filtrar.
Plus: creación limpia de datos en Excel
Otras herramientas de Excel que pueden resultarte útiles en la limpieza de datos son;
1. Eliminar duplicados: cuando descubres esta pequeña funcionalidad, trabajar con listas de Excel es mucho más sencillo.
3. Validación de datos: ¿sabías que… puedes utilizar listas predefinidas en desplegables en Excel?
4. Tipos de validación de datos: todos los tipos de validación de datos que existen y cómo usarlos.
Análisis de datos
Una vez que todos nuestros datos están limpios y ordenados es cuando deberemos empezar a llevar a cabo nuestros análisis. Pero ojo, no te lances a lo loco a hacer análisis sin saber hacia donde quieres llegar. Para ello te proponemos algunos tipos de análisis de datos que pueden resultar útiles.
Análisis de datos para el departamento de ventas
- Ingresos diarios, ingresos mensuales, ingresos semanales
- Ventas diarias, ventas mensuales, ventas semanales
- Ingresos por producto, ingresos por categoría, ingresos por región
- Devoluciones
- Importe de devoluciones
- Categoría de devoluciones
Análisis de datos para el departamento de Recursos Humanos
- Personal activo, altas y bajas
- Personal por departamentos
- Suma de salarios mensuales
- Suma de salarios por departamento
- Costes de impuestos
- Días de vacaciones
- Absentismo
- Cotizaciones
Análisis de datos para el departamento de compras y logística
- Stock actual
- Volumen de compras en el último periodo
- Volumen de compras esperado
- Facturas pendientes de pago
- Productos con mayor rotación
- Productos con menor rotación
Análisis de datos para el departamento de marketing
- Coste de campañas
- Impactos de campaña
- Tasa de retención de clientes
- Tasa de satisfacción de clientes
- Coste de adquisición de usuarios
Conclusión
Una vez que sepas qué tipo de datos quieres obtener deberás empezar a sacarlos de la tabla de datos que tengas en Excel. Para ello puedes hacerlo de muchas maneras pero el mejor método inicialmente será mediante tablas dinámicas y por eso dedicamos un apartado entero a ello.
Tablas dinámicas
Las tablas dinámicas son para el analista de datos la principal herramienta en Excel. En muy pocos clicks puedes conseguir segmentar y agrupar tus datos de manera muy sencilla. Un buen ejemplo de tabla dinámica sería el siguiente:
Como puedes ver en la imagen anterior tenemos:
- Tabla dinámica: a la izquierda de la imagen
- Campos de tabla dinámica a la derecha de la imagen
- Segmentación de datos: recuadro en azul en el centro de la imagen
Si quieres aprender un poco más sobre el tema de tablas dinámicas te recomendamos que te pases por los siguientes tutoriales y ejercicios. Merecen totalmente la pena para perderle el miedo a esta gran herramienta de análisis de datos.
1. Tutorial de tablas dinámicas: para aprender a utilizar esta potente herramienta explicado paso a paso.
2. Segmentación de datos: esto va a hacer de tú trabajo con Excel un antes y un después. Sin duda, una de las herramientas más potentes para análisis de datos con Excel.
7. Ejercicio resuelto. Porque no hay nada mejor que la práctica para aprender a trabajar con esta funcionalidad de Excel.
Otras herramientas de análisis de datos
Pero Excel tiene herramientas mucho más complejas. El objetivo no es llegar a conocer todas pero si tu curiosidad no tiene límites puedes echarle un vistazo a estos otros artículos.
1. Introducción a Solver: este potente add-in de Excel nos permite plantear problemas complejos con varias restricciones y hallar una de sus soluciones.
2. Buscar objetivo: dada una solución a un problema, esta funcionalidad nos permite encontrar una variable que nos de como resultado dicha solución.
3. Escenarios: Escenarios de Excel 2013 es una funcionalidad avanzada del análisis “y si”. A menudo, previamente se habrá realizado un estudio de sensibilidad de cada una de las variables mediante la técnica Tabla de datos. Ahora, se trata de calcular el resultado de cambios en las variables de nuestra elección y mostrar en un informe tanto cada cambio como su resultado.
4. Tablas de datos: las tablas de datos son un tipo de análisis “y si” que, siendo un aspecto muy potente de Excel es, quizá de los menos utilizados de manera sistemática