Gráficos personalizados en Excel
Herramientas: Excel
La semana pasada impartí un curso de formación interna en la Universidad de Deusto en el que vimos cómo crear gráficos personalizados con Excel.
En la introducción del curso, que titulé “Visualización de datos intermedia con Excel”, expliqué los tres niveles de complejidad a la hora de crear gráficos con Excel:
- Gráficos básicos: gráficos que podemos crear directamente usando la galería de gráficos. Podemos usar tablas dinámicas (o mejor, matrices dinámicas) para controlar las series que nos interesa destacar de alguna manera.
- Gráficos intermedios: gráficos personalizados que no están disponibles en la galería. Suelen requerir cálculos específicos para trasladar los datos de la forma que nos interese al gráfico. Por ejemplo, si queremos crear un gráfico de puntos y ordenar uno de los ejes por una variable cualitativa, primero tendremos que transformarla en una variable cuantitativa.
- Gráficos avanzados: gráficos en los que usamos scripts VBA para controlar el gráfico y, sobre todo, para automatizar determinadas tareas tediosas.
El proceso para crear un gráfico personalizado se puede resumir en estos pasos:
- Preparar los datos en formato ordenado (tidy data).
- Preparar los datos para el análisis: agregar, calcular estadísticos, etc.
- Preparar los datos del análisis para dibujarlos. Jon Peltier tiene un post muy interesante sobre cómo le gustan los datos a Excel a la hora de crear un gráfico: “Good Chart Data”.
- Crear el gráfico a partir de una de las opciones de la galería
- Configurar el gráfico: series, tipos de marcas (en gráficos combinados), ejes, etiquetas, barras de error…
Es decir, gran parte del trabajo consiste en preparar los datos para poder visualizarlos. Aunque no se trata de un problema exclusivo de Excel, herramientas como ggplot2 o Tableau Desktop nos ahorran bastantes cálculos que con Excel nos vemos obligados a realizar manualmente. El caso más evidente puede ser colorear las marcas a partir de una variable; con las herramientas indicadas simplemente tenemos que mapear esa variable al color, mientras que en Excel tenemos que crear primero una tabla dinámica para poder controlar la series.
A la hora de preparar los datos usamos tablas para almacenar los datos ordenados y matrices dinámicas para el análisis y la preparación. Las matrices dinámicas son una funcionalidad relativamente reciente (de hecho, nos hemos quedado sin poder probar las funciones PIVOTARPOR() o AGRUPARPOR() porque en el momento en el que se ha impartido el curso solo estaban disponibles en las versiones beta de Excel). Probablemente por ser una de las últimas adiciones a Excel no están del todo integradas con otras funcionalidades más tradicionales. Por ejemplo, cuando configuramos las fuentes de datos de un gráfico no podemos usar referencias directas a matrices dinámicas. Solventamos este problema guardando las matrices dinámicas que hayamos creado como nombres para poder usarlas en los gráficos.
Una de las grandes ventajas de usar estas dos funcionalidades consiste en que cuando realizamos modificaciones en los datos el gráfico se actualiza automáticamente, sin necesidad de realizar ningún retoque.
Funciones para crear matrices dinámicas
Nota: en la referencia de Excel algunas de las funciones aparecen sin traducir a español.
ENFILA(): para devolver un rango en N columnas y una única fila en lugar de por filas.INDICE(): permite crear referencias a un subrango dentro de un rango (por ejemplo, para recuperar una única columna de una matriz dinámica).INDIRECTO(): permite interpretar una cadena de texto como una referencia. Lo usamos para recoger entradas del usuario e incluirlas en fórmulas como referencias (por ejemplo, para indicar la columna por la que ordenar una matriz).ORDENARPOR()SECUENCIA(): permite crear secuencias de N filas por M columnas; también podemos indicar el número de inicio de la secuencia y la cantidad de paso de un número al siguiente.UNICOS(): devuelve valores únicos dentro de un rango, ordenados por orden de aparición.