Contidos
Supoña que vostede e eu necesitamos visualizar os datos da seguinte táboa cos valores de vendas de coches por diferentes países en 2021 (datos reais tomados de aquí, por certo):
Dado que o número de series de datos (países) é grande, tentar agrupalos todos nun gráfico á vez levará a un terrible "gráfico de espagueti" ou a construír gráficos separados para cada serie, o que é moi engorroso.
Unha solución elegante a este problema pode ser trazar un gráfico só nos datos da fila actual, é dicir, a fila onde se atopa a cela activa:
Implementar isto é moi sinxelo: só precisas dúas fórmulas e unha pequena macro en 3 liñas.
Paso 1. Número de liña actual
O primeiro que necesitamos é un intervalo con nome que calcule o número de fila da folla onde se atopa agora a nosa cela activa. Abrindo nunha pestana Fórmulas – Xestor de nomes (Fórmulas — Xestor de nomes), fai clic no botón crear (Crear) e introduza alí a seguinte estrutura:
- Nome – calquera nome axeitado para a nosa variable (no noso caso, este é TekString)
- Área – en diante, cómpre seleccionar a folla actual para que os nomes creados sexan locais
- Alcance – aquí usamos a función CÉLULA (CELULA), que pode emitir unha morea de parámetros diferentes para unha determinada cela, incluído o número de liña que necesitamos; o argumento "liña" é o responsable diso.
Paso 2. Ligazón ao título
Para mostrar o país seleccionado no título e a lenda do gráfico, necesitamos obter unha referencia á cela co seu nome (país) na primeira columna. Para iso, creamos outro local (ie Área = folla actual, non Libro!) un intervalo nomeado coa seguinte fórmula:
Aquí, a función INDEX selecciona dun intervalo determinado (columna A, onde se atopan os nosos países asinantes) unha cela co número de fila que determinamos previamente.
Paso 3. Ligazón aos datos
Agora, dun xeito similar, imos obter unha ligazón a un intervalo con todos os datos de vendas da fila actual, onde agora se atopa a cela activa. Cree outro intervalo con nome coa seguinte fórmula:
Aquí, o terceiro argumento, que é cero, fai que INDEX non devolva un só valor, senón toda a fila como resultado.
Paso 4. Substituíndo ligazóns no gráfico
Agora selecciona a cabeceira da táboa e a primeira fila con datos (rango) e crea un gráfico baseado neles Inserir - Gráficos (Inserir — Gráficos). Se selecciona unha fila con datos no gráfico, a función mostrarase na barra de fórmulas Row (SERIE) é unha función especial que Excel utiliza automaticamente ao crear calquera gráfico para referirse aos datos e etiquetas orixinais:
Substitúamos coidadosamente o primeiro (sinatura) e o terceiro (datos) argumentos desta función cos nomes dos nosos intervalos dos pasos 2 e 3:
O gráfico comezará a mostrar os datos de vendas da fila actual.
Paso 5. Macro de recálculo
Queda o toque final. Microsoft Excel recalcula fórmulas só cando os datos da folla cambian ou cando se preme unha tecla F9, e queremos que o recálculo se produza cando a selección cambie, é dicir, cando a cela activa se move pola folla. Para iso, necesitamos engadir unha macro sinxela ao noso libro de traballo.
Fai clic co botón dereito na pestana da folla de datos e selecciona o comando fonte (Código fonte). Na xanela que se abre, introduza o código do controlador de macros para o evento de cambio de selección:
Como podes imaxinar facilmente, o único que fai é activar un recálculo da folla sempre que cambie a posición da cela activa.
Paso 6. Destacando a liña actual
Para máis claridade, tamén pode engadir unha regra de formato condicional para resaltar o país que se amosa actualmente no gráfico. Para iso, seleccione a táboa e seleccione Inicio — Formato condicional — Crear regra — Use a fórmula para determinar as celas a formatar (Inicio — Formato condicional — Nova regra — Use unha fórmula para determinar que celas formatar):
Aquí a fórmula verifica para cada cela da táboa que o seu número de fila coincide co número almacenado na variable TekRow e, se hai unha coincidencia, activarase o recheo coa cor seleccionada.
Iso é todo: sinxelo e fermoso, non?
Notas
- En mesas grandes, toda esta beleza pode ralentizarse: o formato condicional é unha cousa que require moitos recursos e o recálculo para cada selección tamén pode ser pesado.
- Para evitar que os datos desaparezan no gráfico cando se selecciona accidentalmente unha cela encima ou debaixo da táboa, pode engadir unha comprobación adicional ao nome de TekRow usando funcións IF aniñadas do formulario:
=IF(CELULA(“fila”)<4;SE(CELA(“fila”)>4,CELA(“fila”)))
- Resaltar columnas especificadas nun gráfico
- Como crear un gráfico interactivo en Excel
- Selección de coordenadas