Charts con ZOOM en Excel

Charts con ZOOM en Excel

Transforma tus datos en insights visuales efectivos

Quien afirma que con Excel no se pueden conseguir gráficas profesionales suele estar pensando en un Excel de hace años, con visualizaciones estáticas. Hoy, gracias a las matrices dinámicas, se pueden montar cuadros interactivos realmente potentes. En este tutorial construimos desde cero una gráfica de serie temporal sobre la que podemos hacer una selección y desplazarnos, viendo el detalle de ese periodo en un segundo gráfico de forma totalmente dinámica.

Las funciones que vamos a combinar

Antes de montar la gráfica conviene repasar las cuatro funciones principales que sostienen los cálculos:

  • AGRUPARPOR: agrupa los valores de una columna y aplica una operación (suma, promedio, etc.) sobre otra columna. Se le indica la columna de agrupación, la columna de valores y la operación. Tiene parámetros adicionales para indicar si hay cabeceras (0 si no las pasamos), si queremos totales, por qué columna ordenar y un filtro opcional por condición de fila.
  • ELEGIRCOLS: sobre una matriz o rango, selecciona qué columna o columnas queremos trabajar. Le pasamos el rango y el número de columna (la 1, la 2, la 3...). También admite varias columnas a la vez y permite decidir el orden en que aparecen.
  • TEXTO: muy útil para convertir una fecha y extraer información en el formato que queramos, usando aaaa para el año, mm para meses y dd para días. Por ejemplo, mmmm muestra el mes escrito; combinando códigos obtenemos distintas versiones legibles de la misma fecha.
  • BYROW: recorre una matriz fila a fila y aplica una LAMBDA a cada valor de forma independiente. Esto evita conflictos cuando una función no se propaga bien por todo el rango.

Paso 1: importe por día

Partimos de unos datos con fecha e importe. Con AGRUPARPOR agrupamos por la columna de fechas, sumamos los importes y pedimos que ordene por el primer campo (las fechas), sin cabeceras ni totales, para obtener una serie temporal limpia. El resultado son las fechas en formato Excel junto al acumulado de ventas de cada día. Le damos formato de fecha para compararlo con la tabla original.

Paso 2: separar las series

A partir de ese resultado generamos las series que necesitaremos:

  • Las etiquetas: con ELEGIRCOLS aislamos la primera columna (las fechas) y le aplicamos TEXTO con formato dd mm para que sean legibles dentro de la gráfica.
  • Los importes: con ELEGIRCOLS tomamos la segunda columna de la agrupación.

Separar cada serie de forma independiente es fundamental: al asignarles nombres definidos, las actualizaciones y los cambios de selección se transmiten a la gráfica sin generar errores ni desajustes de rangos.

Paso 3: la columna de selección

Queremos marcar qué días caen dentro de un rango "desde / hasta". La doble condición no funciona si se aplica directamente con un SI y un Y, porque por la forma en que se expanden las funciones internamente no devuelve el resultado esperado. La solución es BYROW: le pasamos el rango de fechas y dentro una LAMBDA que, para cada fecha, comprueba con SI y Y que sea mayor o igual que el desde y menor o igual que el hasta, devolviendo 1 o 0. Así obtenemos el rango completo de unos y ceros.

Paso 4: el detalle filtrado

Para resumir solo los días seleccionados usamos FILTRAR: le pasamos la matriz de días y le indicamos como criterio la columna de selección (los unos y ceros). Repetimos lo mismo con los importes. Con esto tenemos preparadas las series del segundo gráfico de detalle. Recuerda que FILTRAR incluye una fila cuando el rango de criterio marca verdadero.

Paso 5: nombrar los rangos

Desde el administrador de nombres creamos un nombre para cada columna (etiquetas, importes, selección, detalle de etiquetas y detalle de importes), añadiendo el # al final de la referencia para que abarque todo el rango dinámico (por ejemplo Chart!$F$3#). En total, cinco nombres que alimentarán las dos gráficas.

Paso 6: el gráfico principal

Insertamos un gráfico de líneas en blanco y, en Seleccionar datos, agregamos la serie de importes, la serie de selección y las etiquetas. Después, en Cambiar tipo de gráfico, elegimos un gráfico combinado: las ventas como líneas y la columna de unos y ceros como columnas al 100% en el eje secundario (de 0 a 100). En el formato de la serie ponemos el ancho de rango a 0 para que las barras se junten y creen un efecto cortina, y le damos transparencia para resaltar la zona seleccionada.

Paso 7: el gráfico de detalle y los controles

Creamos un segundo gráfico, de columnas, con las series de detalle (importes y etiquetas). Para navegar cómodamente insertamos dos controles de formulario desde la pestaña Programador:

  • Una barra de desplazamiento vinculada a una celda, para movernos por la serie principal.
  • Un control de número para ampliar o reducir cuántos días queremos observar.

La fecha "hasta" se calcula como la de inicio más el número de días elegido, de modo que la selección mantiene su ancho al desplazarnos. Por último, podemos construir un título con TEXTO (concatenando fecha inicial y final con &) y vincularlo al título del gráfico para que se actualice solo.

Conclusión

El resultado es un cuadro de mando interactivo donde nos desplazamos por la serie temporal, ampliamos o reducimos el tramo y vemos su detalle en vivo. La clave no está en las gráficas en sí, sino en las matrices dinámicas que las soportan: vinculando ambas tablas a través de los datos conseguimos un dinamismo tan espectacular como útil para potenciar cualquier análisis.

Más contenido de Excel en InflueXcel