Crea gráficas interactivas en Excel: Crossfiltering

Crea gráficas interactivas en Excel: Crossfiltering

Crea gráficos interactivos en Excel utilizando el poder de las funciones dinámicas para modificar los datos de origen y generar efectos avanzados que antes solo eran posibles con un conocimiento profundo del modelo de datos, suando cómo usar funciones como AGRUPARPOR y FILTRAR para preparar arrays dinámicos que ofrecen una flexibilidad increíble para construir gráficos adaptables y personalizados

El mejor cumplido que puedes recibir al presentar una visualización es la pregunta "¿Pero esto es Excel?". Con las matrices dinámicas podemos conseguir efectos visuales y un nivel de interactividad que hasta hace poco solo eran posibles mediante macros o desarrollos muy complejos. En este tutorial construimos un cuadro de mando interactivo con la técnica del crossfiltering: un ranking de clientes ajustable, una barra resaltada según la selección y un gráfico de detalle que cambia con cada cliente elegido.

Crear un ranking dinámico con AGRUPARPOR

Partimos de una tabla con tres columnas: cliente, fecha e importe. Para saber qué clientes han comprado más usamos la función AGRUPARPOR, que agrega y ordena los datos en un solo paso. Sus argumentos clave son:

  • Filas: la columna por la que agrupar (cliente).
  • Valores: la columna que se agrega (importe).
  • Función: cómo se agrega, en este caso SUMA.
  • Cabeceras: como trabajamos con una tabla, los datos llegan sin encabezados, así que indicamos que no.
  • Totales: ponemos 0 para que no añada fila de totales.
  • Orden: para un ranking de mayor a menor usamos -2, donde el 2 apunta a la segunda columna (la agregación) y el signo negativo invierte el orden a descendente.

El resultado es una matriz ya ordenada con el cliente que más ha comprado en primer lugar.

Limitar resultados con TOMAR y un control de formulario

Para añadir interactividad, envolvemos el ranking con la función TOMAR, que devuelve el número de filas que indiquemos de una matriz. Si conectamos ese número a una celda, cambiar el valor (5, 7, 3...) ajusta al instante cuántos clientes se muestran.

Un truco muy útil que emplea Carlos Barbosa es insertar un control de formulario con flechas arriba/abajo. En el formato del control definimos valor mínimo 1, valor máximo 10, un incremento y, lo más importante, lo vinculamos con la celda que contiene el valor. Así el usuario sube o baja el ranking con un clic.

Series con nombres definidos

Para conectar los datos con el gráfico de forma robusta, separamos el resultado en dos columnas con ELEGIRCOLS (clientes en la primera, ventas en la segunda), referenciando la matriz con el # (operador de rango desbordado).

Después creamos nombres definidos desde el administrador de nombres (pestaña Fórmulas). Usamos un guion bajo inicial como convención (_CLIENTES, _VENTAS), los definimos a nivel de hoja y añadimos # a la celda de referencia para que el nombre tome todo el rango desbordado. Al editar la serie del gráfico, pulsamos F3 para insertar esos nombres en lugar de rangos de celdas, lo que mejora la trazabilidad y evita problemas al recalcular.

Resaltar el cliente seleccionado

Para destacar una barra concreta generamos primero una SECUENCIA con las FILAS de la matriz, que numera cada posición del ranking. Con un control de formulario elegimos qué fila resaltar.

La clave es dividir las ventas en dos series complementarias mediante un condicional SI:

  • En una columna: si la secuencia coincide con el valor seleccionado, devuelve la venta; si no, 0.
  • En la otra columna: lo contrario, 0 en el seleccionado y la venta en el resto.

Convertimos cada columna en un nombre definido (importe_seleccion, importes_no_seleccionados) y las cargamos como dos series del gráfico. Al darles colores distintos (por ejemplo naranja para la selección), la barra elegida se distingue de forma inmediata. Cambiamos el tipo de gráfico a barras apiladas para que ambas series queden centradas, ya que una siempre vale cero.

Para alinear el orden visual con el ranking, en el formato del eje activamos categorías en orden inverso. Un detalle práctico: como el control mueve el valor al revés de lo intuitivo, calculamos el inverso con =11 - valor para que al subir se seleccione la barra superior.

Gráfico de detalle por cliente

El último paso muestra la evolución mensual del cliente resaltado. Volvemos a usar AGRUPARPOR, esta vez agrupando por fecha y sumando importes, pero aprovechando dos opciones extra:

  • El argumento de filtro: filtramos por cliente = el cliente seleccionado, que recuperamos con INDICE sobre la matriz de etiquetas usando el número del control.
  • Aplicar una función a la columna de agrupación: si pasamos MES(fecha), Excel agrupa directamente por número de mes sin necesidad de crear una columna auxiliar.

Separamos el resultado con ELEGIRCOLS, creamos los nombres detalle_fechas y detalle_importe (a nivel de hoja, con #) e insertamos un gráfico de líneas que toma esas series con F3. Por último, enlazamos el título del gráfico a la celda con el nombre del cliente, de modo que cambia automáticamente al movernos por el ranking.

Conclusión

Con AGRUPARPOR, TOMAR, ELEGIRCOLS, SECUENCIA, INDICE y nombres definidos basados en rangos desbordados, construimos un panel donde todo se recalcula al instante: el usuario decide cuántos clientes ve, resalta uno concreto y obtiene su evolución mensual. Las matrices dinámicas abren puertas que antes estaban cerradas en Excel y demuestran que sí es una herramienta más que capaz para crear visualizaciones interactivas.

Más casos con estas funciones

Más contenido de Excel en InflueXcel