Diagramă după celula selectată

Să presupunem că tu și cu mine trebuie să vizualizăm datele din următorul tabel cu valorile vânzărilor de mașini pe diferite țări în 2021 (date reale luate de aici, de altfel):

Deoarece numărul de serii de date (țări) este mare, încercarea de a le înghesui pe toate într-un singur grafic deodată va duce fie la o „diagramă spaghetti” groaznică, fie la construirea de diagrame separate pentru fiecare serie, ceea ce este foarte greoi.

O soluție elegantă la această problemă poate fi reprezentarea unui grafic numai pe datele din rândul curent, adică rândul în care se află celula activă:

Implementarea acestui lucru este foarte ușoară – aveți nevoie doar de două formule și o macrocomandă mică în 3 rânduri.

Pasul 1. Numărul liniei curente

Primul lucru de care avem nevoie este un interval numit care calculează numărul rândului de pe foaia în care se află acum celula noastră activă. Deschiderea pe o filă Formule – Manager de nume (Formule — Manager de nume), faceți clic pe butonul Crea (Crea) și introduceți următoarea structură acolo:

Pasul 2. Link către titlu

Pentru a afișa țara selectată în titlul și legenda diagramei, trebuie să obținem o referință la celula cu numele acesteia (țara) din prima coloană. Pentru a face acest lucru, creăm un alt local (de ex Zonă = foaia curentă, nu carte!) un interval numit cu următoarea formulă:

Aici, funcția INDEX selectează dintr-un interval dat (coloana A, unde se află țările noastre semnatare) o celulă cu numărul rândului pe care l-am determinat anterior.

Pasul 3. Link către date

Acum, într-un mod similar, să obținem un link către un interval cu toate datele de vânzări de pe rândul curent, unde se află acum celula activă. Creați un alt interval denumit cu următoarea formulă:

Aici, al treilea argument, care este zero, face ca INDEX să returneze nu o singură valoare, ci întregul rând ca rezultat.

Pasul 4. Înlocuirea legăturilor în diagramă

Acum selectați antetul tabelului și primul rând cu date (interval) și construiți o diagramă bazată pe ele folosind Inserare – Diagrame (Inserați — Diagrame). Dacă selectați un rând cu date în diagramă, atunci funcția va fi afișată în bara de formule RÂND (SERIE) este o funcție specială pe care Excel o folosește automat când creează orice diagramă pentru a se referi la datele și etichetele originale:

Să înlocuim cu atenție primul (semnătură) și al treilea (date) argument din această funcție cu numele intervalelor noastre de la pașii 2 și 3:

Graficul va începe să afișeze datele vânzărilor de pe rândul curent.

Pasul 5. Macro de recalculare

Atingerea finală rămâne. Microsoft Excel recalculează formulele numai atunci când datele de pe foaie se modifică sau când este apăsată o tastă F9, și dorim ca recalcularea să aibă loc atunci când selecția se schimbă, adică atunci când celula activă este mutată pe foaie. Pentru a face acest lucru, trebuie să adăugăm o macrocomandă simplă la registrul nostru de lucru.

Faceți clic dreapta pe fila fișei de date și selectați comanda Sursă (Cod sursa). În fereastra care se deschide, introduceți codul macro-handler-ului pentru evenimentul de modificare a selecției:

După cum vă puteți imagina cu ușurință, tot ceea ce face este să declanșeze o recalculare a foii ori de câte ori poziția celulei active se schimbă.

Pasul 6. Evidențierea liniei curente

Pentru claritate, puteți adăuga și o regulă de formatare condiționată pentru a evidenția țara care este afișată în prezent pe diagramă. Pentru a face acest lucru, selectați tabelul și selectați Acasă — Formatare condiționată — Creare regulă — Utilizați formula pentru a determina celulele de format (Acasă — Formatare condiționată — Regulă nouă — Folosiți o formulă pentru a determina ce celule să formatați):

Aici formula verifică pentru fiecare celulă din tabel dacă numărul rândului său se potrivește cu numărul stocat în variabila TekRow și, dacă există o potrivire, se declanșează umplerea cu culoarea selectată.

Asta e - simplu și frumos, nu?

notițe

• Pe mesele mari, toată această frumusețe poate încetini – formatarea condiționată este un lucru care necesită mult resurse, iar recalcularea pentru fiecare selecție poate fi, de asemenea, grea.
• Pentru a preveni dispariția datelor din diagramă atunci când o celulă este selectată accidental deasupra sau sub tabel, puteți adăuga o verificare suplimentară la numele TekRow folosind funcțiile IF imbricate ale formularului:

  =IF(CELL(„rând”)<4,IF(CELL(„rând”)>4,CELL(„rând”)))
  

• Evidențierea coloanelor specificate într-o diagramă
• Cum se creează o diagramă interactivă în Excel
• Selectarea coordonatelor