Contenido
Supongamos que tú y yo necesitamos visualizar datos de la siguiente tabla con valores de ventas de autos por diferentes países en 2021 (datos reales tomados de aquí, por cierto):
Dado que la cantidad de series de datos (países) es grande, tratar de incluirlos todos en un gráfico a la vez conducirá a un terrible "gráfico de espagueti" o a la creación de gráficos separados para cada serie, lo cual es muy engorroso.
Una solución elegante a este problema puede ser trazar un gráfico solo en los datos de la fila actual, es decir, la fila donde se encuentra la celda activa:
Implementar esto es muy fácil: solo necesita dos fórmulas y una pequeña macro en 3 líneas.
Paso 1. Número de línea actual
Lo primero que necesitamos es un rango con nombre que calcule el número de fila en la hoja donde ahora se encuentra nuestra celda activa. Abrir en una pestaña Fórmulas – Administrador de nombres (Fórmulas — Administrador de nombres), haga clic en el botón Crear (Crear) e ingrese la siguiente estructura allí:
- Nombre – cualquier nombre adecuado para nuestra variable (en nuestro caso, este es TekString)
- Área – en lo sucesivo, debe seleccionar la hoja actual para que los nombres creados sean locales
- Gama de Colores – aquí usamos la función CÉLULA (CÉLULA), que puede emitir un montón de parámetros diferentes para una celda determinada, incluido el número de línea que necesitamos; el argumento "línea" es responsable de esto.
Paso 2. Enlace al título
Para mostrar el país seleccionado en el título y la leyenda del gráfico, necesitamos obtener una referencia a la celda con su nombre (país) de la primera columna. Para ello, creamos otro local (es decir, Área = hoja actual, ¡no Libro!) un rango con nombre con la siguiente fórmula:
Aquí, la función ÍNDICE selecciona de un rango dado (columna A, donde se encuentran nuestros países firmantes) una celda con el número de fila que determinamos previamente.
Paso 3. Enlace a datos
Ahora, de manera similar, obtengamos un enlace a un rango con todos los datos de ventas de la fila actual, donde ahora se encuentra la celda activa. Cree otro rango con nombre con la siguiente fórmula:
Aquí, el tercer argumento, que es cero, hace que ÍNDICE no devuelva un solo valor, sino la fila completa como resultado.
Paso 4. Sustitución de enlaces en el gráfico
Ahora seleccione el encabezado de la tabla y la primera fila con datos (rango) y cree un gráfico basado en ellos usando Insertar – Gráficos (Insertar — Gráficos). Si selecciona una fila con datos en el gráfico, la función se mostrará en la barra de fórmulas FILA (SERIE) es una función especial que Excel usa automáticamente al crear cualquier gráfico para referirse a los datos y etiquetas originales:
Reemplacemos cuidadosamente el primer argumento (firma) y el tercero (datos) en esta función con los nombres de nuestros rangos de los pasos 2 y 3:
El gráfico comenzará a mostrar los datos de ventas de la fila actual.
Paso 5. Macro de Recálculo
Queda el toque final. Microsoft Excel vuelve a calcular las fórmulas solo cuando cambian los datos de la hoja o cuando se presiona una tecla F9, y queremos que el recálculo ocurra cuando cambie la selección, es decir, cuando la celda activa se mueva por la hoja. Para hacer esto, necesitamos agregar una macro simple a nuestro libro de trabajo.
Haga clic derecho en la pestaña de la hoja de datos y seleccione el comando Fuente (Código fuente). En la ventana que se abre, ingrese el código del controlador de macros para el evento de cambio de selección:
Como puede imaginar fácilmente, todo lo que hace es activar un recálculo de la hoja cada vez que cambia la posición de la celda activa.
Paso 6. Resaltar la línea actual
Para mayor claridad, también puede agregar una regla de formato condicional para resaltar el país que se muestra actualmente en el gráfico. Para hacer esto, seleccione la tabla y seleccione Inicio — Formato condicional — Crear regla — Usar fórmula para determinar las celdas a formatear (Inicio — Formato condicional — Nueva regla — Use una fórmula para determinar qué celdas formatear):
Aquí, la fórmula comprueba para cada celda de la tabla que su número de fila coincida con el número almacenado en la variable TekRow y, si hay una coincidencia, se activa el relleno con el color seleccionado.
Eso es todo, simple y hermoso, ¿verdad?
Notas
- En tablas grandes, toda esta belleza puede ralentizarse: el formato condicional requiere muchos recursos, y el recálculo para cada selección también puede ser pesado.
- Para evitar que los datos desaparezcan en el gráfico cuando se selecciona accidentalmente una celda encima o debajo de la tabla, puede agregar una verificación adicional al nombre de TekRow usando las funciones IF anidadas del formulario:
=SI(CELDA(“fila”)<4,SI(CELDA(“fila”)>4,CELDA(“fila”)))
- Resaltar columnas específicas en un gráfico
- Cómo crear un gráfico interactivo en Excel
- Selección de coordenadas