Diagramm valitud lahtri järgi

Oletame, et teie ja mina peame visualiseerima järgmise tabelis olevad andmed autode müügiväärtustega erinevates riikides 2021. aastal (muide, tegelikud andmed on võetud siit):

Kuna andmeseeriate (riikide) arv on suur, siis nende kõigi korraga ühte graafikusse toppimine toob kaasa kas kohutava “spagetidiagrammi” või iga seeria jaoks eraldi diagrammide koostamise, mis on väga tülikas.

Selle probleemi elegantne lahendus võib olla diagrammi joonistamine ainult praeguse rea andmetele, st reale, kus asub aktiivne lahter:

Selle rakendamine on väga lihtne – vajate ainult kahte valemit ja ühte pisikest makrot kolmel real.

Samm 1. Praeguse rea number

Esimene asi, mida vajame, on nimega vahemik, mis arvutab rea numbri lehel, kus meie aktiivne lahter praegu asub. Avamine vahekaardil Valemid – nimehaldur (Valemid – nimehaldur), klõpsake nuppu Looma (Loo) ja sisestage sinna järgmine struktuur:

2. samm. Link pealkirjale

Valitud riigi kuvamiseks diagrammi pealkirjas ja legendis peame esimesest veerust hankima viite selle (riigi)nimega lahtrile. Selleks loome teise kohaliku (st Piirkond = praegune leht, mitte raamat!) nimega vahemik järgmise valemiga:

Siin valib funktsioon INDEX etteantud vahemikust (veerg A, kus asuvad meie allkirjastavad riigid) lahtri, mille reanumber on varem määratud.

3. samm. Andmete linkimine

Nüüd saame sarnaselt lingi vahemikule kõigi müügiandmetega praegusest reast, kus nüüd asub aktiivne lahter. Looge teise nimega vahemik järgmise valemiga:

Siin põhjustab kolmas argument, mis on null, tagastama INDEX mitte ühe väärtuse, vaid selle tulemusel kogu rea.

4. samm. Linkide asendamine diagrammil

Nüüd vali tabeli päis ja esimene rida andmetega (vahemik) ning koosta nende põhjal diagramm Sisesta – diagrammid (Sisesta – diagrammid). Kui valite diagrammil andmetega rea, kuvatakse funktsioon valemiribal ROW (SERIES) on erifunktsioon, mida Excel kasutab automaatselt mis tahes diagrammi loomisel, et viidata algandmetele ja siltidele:

Asendame hoolikalt selle funktsiooni esimese (signatuur) ja kolmanda (andmed) argumendi meie vahemike nimedega alates sammudest 2 ja 3:

Diagramm hakkab kuvama müügiandmeid praegusest reast.

Samm 5. Ümberarvutamise makro

Viimane lihv jääb. Microsoft Excel arvutab valemid ümber ainult siis, kui lehel olevad andmed muutuvad või kui vajutatakse klahvi F9, ja me tahame, et ümberarvutamine toimuks valiku muutumisel, st aktiivse lahtri teisaldamisel üle lehe. Selleks peame oma töövihikusse lisama lihtsa makro.

Paremklõpsake andmelehe vahekaarti ja valige käsk allikas (Lähtekood). Sisestage avanevas aknas valiku muutmise sündmuse makrokäsitleja kood:

Nagu võite kergesti ette kujutada, käivitab see ainult lehe ümberarvutamise, kui aktiivse lahtri asukoht muutub.

6. samm. Praeguse rea esiletõstmine

Selguse huvides saate lisada ka tingimusliku vormindamise reegli, et tõsta esile praegu diagrammil kuvatav riik. Selleks valige tabel ja valige Avaleht — Tingimuslik vormindamine — Loo reegel — Kasutage vormindatavate lahtrite määramiseks valemit (Avaleht – Tingimuslik vormindamine – Uus reegel – Kasutage vormindatavate lahtrite määramiseks valemit):

Siin kontrollib valem iga tabeli lahtri puhul, et selle rea number ühtiks TekRow muutujas salvestatud numbriga ja kui see on sobiv, siis käivitatakse valitud värviga täitmine.

See on kõik – lihtne ja ilus, eks?

märkused

• Suurtel tabelitel võib kogu see ilu aeglustada – tingimuslik vormindamine on ressursimahukas asi ja ka iga valiku ümberarvutamine võib olla raske.
• Andmete kadumise vältimiseks diagrammil, kui lahter valitakse kogemata tabeli kohal või all, saate lisada TekRow nimele täiendava kontrolli, kasutades vormi pesastatud IF-funktsioone:

  =IF(CELL(“rida”)<4,IF(CELL("rida")>4,CELL("rida")))
  

• Diagrammi määratud veergude esiletõstmine
• Kuidas luua Excelis interaktiivset diagrammi
• Koordinaatide valik