Debemos saber cómo trabajar Excel para sacarle el máximo rendimiento a la hora de manipular gran cantidad de datos.
En el artículo «Enganchados al Excel: cómo su (mal) uso está alimentando el caos en los datos del covid» publicado el 28/10/2020 en elconfidencial.com, se desaconsejaba el uso de Excel para gestionar los datos del COVID-19.
Ponían como razón que se habían producido algunos fallos debido a: «…una falta de conocimiento del ‘software’ adecuado para este cometido dentro de las administraciones públicas y una dependencia exagerada de lo público por unos pocos programas estándar.»
Excel es uno de esos “programas estándar” muy útil que ha evolucionado con los años y puede utilizarse en el análisis de datos para obtener informes que ayuden en la toma de decisiones, lo que se conoce como Inteligencia de Negocio (BI) o Business Intelligence.
Es cierto que en la actualidad existe “software adecuado”, como el Power BI de Microsoft, pero el problema está en el conocimiento que se tenga de la herramienta, sea cuál sea. Es decir, si se utiliza Excel de manera correcta podremos analizar los datos del COVID-19 sin fallos.
Contenido del artículo:
Complementos de Excel para la Inteligencia de Negocio (BI)
Hay cuatro complementos Power BI perfectamente integrados, aunque pueden utilizarse de forma individual, para realizar un mejor análisis de datos con Excel:
- Tratamiento de datos con Power Query: facilita el tratamiento y manipulación de grandes cantidades de datos de diferentes fuentes, tanto del propio Excel como de otras fuentes externas.
- Modelos de datos con Power Pivot: permite procesar gran cantidad de información compleja en poco tiempo y de manera sencilla generando un modelo de datos (conjunto de tablas relacionadas).
- Presentaciones de Power View: herramienta que sirve para confeccionar presentaciones interactivas y visualmente intuitivas, a modo de dashboard o cuadro de mando.
- Power Map: permite representar datos de forma geográfica en mapas.
¿Qué versión de Excel necesitamos?
En la siguiente tabla se muestra en que versiones de Excel están disponibles los complementos. Es mejor utilizar una versión reciente de Excel porque Microsoft ya no ofrece actualizaciones de estos complementos para las versiones Excel 2010 y Excel 2013.
Siempre es mejor tener una versión de Office actualizada para poder disfrutar de todas sus características. Por lo tanto, como mínimo, deberíamos utilizar el Excel 2016 actualizado.
¿Qué se puede estar haciendo mal en Excel?
En Excel 2016 podemos analizar datos externos de dos formas distintas, utilizando:
- Obtener datos externos: En la pestaña Datos, dentro del grupo obtener datos externos, tenemos la posibilidad de importar a Excel datos desde distintos archivos de origen.
- Obtener y transformar: En la pestaña Datos, desde el grupo obtener y transformar, utilizaremos Power Query para consultar el origen de datos sin necesidad de importarlos a Excel.
Si utilizamos la opción 1, obtener datos externos, lo estaremos haciendo mal por dos razones fundamentales:
- La actualización del informe obtenido (cálculos o modificaciones, tablas dinámicas, gráficos, …) es más laboriosa porque al actualizar el origen de datos tenemos que repetir ciertos procesos de forma manual, es un trabajo tedioso que consume tiempo y en el que podemos cometer errores cada vez que actualicemos.
- La versión de Excel utilizada admite un máximo de 1.048.576 filas o registros por hoja. Si nuestro origen de datos tiene más registros de los admitidos en una hoja tendríamos que importar los datos por partes, en hojas distintas, lo que dificultaría enormemente las actualizaciones futuras e incrementaría la posibilidad de cometer errores.
¿Cómo analizar datos de manera correcta con Excel?
Utilizando el complemento Power Query, que en Excel 2016 aparece dentro del grupo Obtener y transformar de la pestaña Datos (opción 2 de la figura anterior), no necesitamos importar los datos del archivo original, tan solo crear la consulta o consultas que necesitemos para obtener nuestro informe.
Es decir, se obtiene el resultado conectando con el origen, sin importar los datos originales a Excel. Con lo que la actualización del informe es mecánica, cada vez que se actualice el origen de datos es suficiente actualizar las consultas creadas.
A continuación, describo en 3 pasos la forma de utilizar los complementos Power BI de Excel. No pretendo profundizar o entrar en el uso detallado de estos complementos, tan solo quiero recalcar que utilizándolos se puede analizar gran cantidad de datos en Excel de forma óptima, reduciendo la posibilidad de cometer errores.
3 pasos para utilizar los complementos Power BI de Excel.
Paso 1: Consultar el origen de datos agregando la consulta, o consultas, al modelo de datos sin importar datos.
En la elaboración del ejemplo que voy a mostrar se ha utilizado Excel 2016 y se han analizado los datos facilitados por el Centro europeo para la prevención y el control de enfermedades (ECDC) en la dirección: https://www.ecdc.europa.eu/en/publications-data/weekly-subnational-14-day-notification-rate-covid-19
Desde esa dirección se descargan los datos en un archivo. Por ejemplo, descarga.xlsx.
Abrimos Excel y creamos un nuevo libro en blanco. Desde Datos – Nueva consulta creamos tantas consultas como necesitemos, transformando el origen de datos (descarga.xlsx) para obtener los datos deseados. La siguiente figura muestra la edición de una de las consultas creadas en la ventana de Power Query, en la que se están obteniendo los casos de COVID-19 por cada 100.000 habitantes de los últimos 14 días agrupados por país y semana:
Todas las consultas creadas se han agregado al modelo de datos, desde Inicio – Cerrar y cargar – Cerrar y cargar en… creando solo la conexión y agregando la consulta al modelo de datos, sin importar los datos a una tabla, como se indica en la siguiente figura:
Paso 2: Obtener las tablas dinámicas y gráficos deseados desde el modelo de datos.
En Excel podemos acceder a Power Pivot para administrar el modelo de datos mediante la opción Datos – Administrar modelo de datos o desde Power Pivot – Administrar. Los datos de la consulta seleccionada se cargan en la memoria del ordenador, no en Excel, y obtenemos la tabla o gráfico dinámico desde la opción Inicio – Tabla dinámica.
Para obtener determinada información puede que necesitemos relacionar consultas. Para crear una relación se requiere un campo común en ambas consultas (o tablas) que pueda tener los mismos valores. Las relaciones se pueden crear y editar desde Excel, seleccionando Datos – relaciones y también desde la Vista de diagrama de Power Pivot.
En la siguiente figura vemos un extracto del informe creado, en el que se tienen varios gráficos y dos tablas dinámicas. Se han creado las dos relaciones anteriores para vincular las tablas dinámicas y los gráficos de la evolución del coronavirus en Europa y en España a la misma segmentación de datos (slicer) con lo que ambos gráficos y tablas dinámicas nos mostrarán la evolución en la semana o semanas filtradas, o seleccionadas, en la segmentación de datos.
Paso 3: Actualizar el informe
Si queremos agregar nuevos datos a nuestro informe hay que hacer 2 sencillos pasos:
- Ir a la URI de descarga y reemplazar nuestro archivo origen de datos por el nuevo (descarga.xlsx)
- Actualizar nuestro informe desde Datos – Actualizar todo.
Los gráficos que no estén vinculados a la segmentación de datos se actualizarán automáticamente, y si se había aplicado algún filtro en la segmentación de datos habrá que quitarlo para ver la evolución en los gráficos y tablas dinámicas vinculados.
Conclusiones
Excel es una gran herramienta que puede ayudarnos en la toma de decisiones, pero una herramienta hay que tenerla correctamente “engrasada”, en este caso actualizada, y saber cómo utilizarla para sacarle el rendimiento correcto. Si tenemos una versión de Excel actualizada dispondremos de los complementos Power BI de Excel: Power Query, Power Pivot, Power View y Power Map.
Estos complementos se pueden utilizar de forma conjunta o independiente (en este ejemplo no se han utilizado Power View ni Power Map) para obtener informes en Excel desde distintos orígenes de datos sin necesidad de “duplicarlos” con lo que se reduce mucho la posibilidad de cometer errores en la gestión de grandes cantidades de datos.
Como consejo si necesitas formación específica para manipular y tratar datos puedes informarte de nuestro Curso de Excel Avanzado donde obtendrás los conocimientos que necesitas.
