contingut
Suposem que tu i jo necessitem visualitzar dades de la taula següent amb els valors de vendes de cotxes per diferents països el 2021 (per cert, dades reals extretes d'aquí):
Atès que el nombre de sèries de dades (països) és gran, intentar agrupar-les totes en un gràfic alhora conduirà a un "gràfic d'espaguetis" terrible o a crear gràfics separats per a cada sèrie, cosa que és molt feixuc.
Una solució elegant a aquest problema pot ser traçar un gràfic només a les dades de la fila actual, és a dir, la fila on es troba la cel·la activa:
Implementar això és molt fàcil: només necessiteu dues fórmules i una petita macro en 3 línies.
Pas 1. Número de línia actual
El primer que necessitem és un rang amb nom que calculi el número de fila del full on ara es troba la nostra cel·la activa. Obrint en una pestanya Fórmules – Gestor de noms (Fórmules — Gestor de noms), feu clic al botó Create (Crear) i introduïu-hi l'estructura següent:
- Nom – qualsevol nom adequat per a la nostra variable (en el nostre cas, això és TekString)
- Àrea – d'ara endavant, cal seleccionar el full actual perquè els noms creats siguin locals
- Abast – aquí fem servir la funció CEL·LULAR (CEL·LULA), que pot emetre un munt de paràmetres diferents per a una cel·la determinada, inclòs el número de línia que necessitem; l'argument "línia" és responsable d'això.
Pas 2. Enllaç al títol
Per mostrar el país seleccionat al títol i la llegenda del gràfic, hem d'obtenir una referència a la cel·la amb el seu nom (país) de la primera columna. Per fer-ho, creem un altre local (és a dir Àrea = full actual, no llibre!) un rang anomenat amb la fórmula següent:
Aquí, la funció INDEX selecciona d'un rang determinat (columna A, on es troben els nostres països signants) una cel·la amb el número de fila que hem determinat anteriorment.
Pas 3. Enllaç a dades
Ara, de manera similar, obtenim un enllaç a un interval amb totes les dades de vendes de la fila actual, on ara es troba la cel·la activa. Creeu un altre rang amb nom amb la fórmula següent:
Aquí, el tercer argument, que és zero, fa que INDEX no torni un sol valor, sinó tota la fila com a resultat.
Pas 4. Substitució d'enllaços al gràfic
Ara seleccioneu la capçalera de la taula i la primera fila amb dades (interval) i creeu un gràfic basat en elles Inserir - Gràfiques (Insereix — Gràfiques). Si seleccioneu una fila amb dades al gràfic, la funció es mostrarà a la barra de fórmules FILA (SÈRIE) és una funció especial que Excel utilitza automàticament quan es crea qualsevol gràfic per fer referència a les dades i etiquetes originals:
Substituïm amb cura el primer (signatura) i el tercer (dades) arguments d'aquesta funció amb els noms dels nostres intervals dels passos 2 i 3:
El gràfic començarà a mostrar les dades de vendes de la fila actual.
Pas 5. Macro de recàlcul
El toc final queda. Microsoft Excel torna a calcular les fórmules només quan canvien les dades del full o quan es prem una tecla F9, i volem que el recàlcul es produeixi quan canviï la selecció, és a dir, quan la cel·la activa es mou pel full. Per fer-ho, hem d'afegir una macro senzilla al nostre llibre de treball.
Feu clic amb el botó dret a la pestanya del full de dades i seleccioneu l'ordre font (Codi font). A la finestra que s'obre, introduïu el codi del gestor de macros per a l'esdeveniment de canvi de selecció:
Com us podeu imaginar fàcilment, tot el que fa és activar un recàlcul del full sempre que canviï la posició de la cel·la activa.
Pas 6. Ressaltant la línia actual
Per a més claredat, també podeu afegir una regla de format condicional per ressaltar el país que es mostra actualment al gràfic. Per fer-ho, seleccioneu la taula i seleccioneu Inici — Format condicional — Crea una regla — Utilitza la fórmula per determinar les cel·les a formatar (Inici — Format condicional — Regla nova — Utilitzeu una fórmula per determinar quines cel·les voleu formatar):
Aquí, la fórmula comprova per a cada cel·la de la taula que el seu número de fila coincideixi amb el nombre emmagatzemat a la variable TekRow i, si hi ha una coincidència, s'activa l'ompliment amb el color seleccionat.
Això és tot, senzill i bonic, oi?
notes
- A les taules grans, tota aquesta bellesa pot alentir-se: el format condicional és una cosa que requereix molts recursos i el recàlcul de cada selecció també pot ser pesat.
- Per evitar que les dades desapareguin del gràfic quan es selecciona accidentalment una cel·la a sobre o a sota de la taula, podeu afegir una comprovació addicional al nom de TekRow mitjançant les funcions IF imbricades de la forma:
=IF(CELL(“fila”)<4,IF(CELL(“fila”)>4,CEL·LULA(“fila”)))
- Ressaltar columnes especificades en un gràfic
- Com crear un gràfic interactiu a Excel
- Selecció de coordenades