sadržaj
Pretpostavimo da vi i ja trebamo vizualizirati podatke iz sljedeće tabele sa vrijednostima prodaje automobila (usput rečeno stvarni podaci preuzeti odavde):
Budući da je broj serija podataka (zemalja) velik, pokušaj da ih se sve strpa u jedan grafikon odjednom će dovesti do užasnog "špageti grafikona" ili do pravljenja zasebnih grafikona za svaku seriju, što je vrlo glomazno.
Elegantno rješenje ovog problema može biti iscrtavanje grafikona samo na podacima iz trenutnog reda, odnosno reda u kojem se nalazi aktivna ćelija:
Implementacija ovoga je vrlo jednostavna – potrebne su vam samo dvije formule i jedan mali makro u 3 reda.
Korak 1. Broj trenutne linije
Prva stvar koju trebamo je imenovani raspon koji izračunava broj reda na listu gdje se sada nalazi naša aktivna ćelija. Otvaranje na kartici Formule – Menadžer imena (Formule — Menadžer imena), kliknite na dugme stvoriti (Stvoriti) i tamo unesite sljedeću strukturu:
- Ime – bilo koje prikladno ime za našu varijablu (u našem slučaju, ovo je TekString)
- oblast – u daljem tekstu potrebno je odabrati trenutni list tako da kreirani nazivi budu lokalni
- domet – ovdje koristimo funkciju CELL (ĆELIJA), koji može izdati gomilu različitih parametara za datu ćeliju, uključujući i broj reda koji nam je potreban – za to je odgovoran argument “line”.
Korak 2. Link do naslova
Za prikaz odabrane zemlje u naslovu i legendi grafikona, potrebno je da iz prve kolone dobijemo referencu na ćeliju sa njenim nazivom (države). Da bismo to učinili, kreiramo još jedan lokalni (tj oblast = trenutni list, a ne knjiga!) imenovani raspon sa sljedećom formulom:
Ovdje funkcija INDEX bira iz datog raspona (kolona A, gdje se nalaze naše zemlje potpisnice) ćeliju s brojem reda koji smo prethodno odredili.
Korak 3. Link na podatke
Sada, na sličan način, dobijemo vezu do raspona sa svim podacima o prodaji iz trenutnog reda, gdje se sada nalazi aktivna ćelija. Kreirajte drugi imenovani raspon sa sljedećom formulom:
Ovdje treći argument, koji je nula, uzrokuje da INDEX vrati ne jednu vrijednost, već cijeli red kao rezultat.
Korak 4. Zamjena veza u grafikonu
Sada odaberite zaglavlje tabele i prvi red sa podacima (opseg) i na osnovu njih napravite grafikon Umetnuti – grafikoni (Umetnuti — grafikoni). Ako odaberete red s podacima u grafikonu, funkcija će biti prikazana u traci formule RED (SERIJA) je posebna funkcija koju Excel automatski koristi kada kreira bilo koji grafikon za upućivanje na originalne podatke i oznake:
Pažljivo zamijenimo prvi (potpis) i treći (podaci) argument u ovoj funkciji s imenima naših raspona iz koraka 2 i 3:
Grafikon će početi prikazivati podatke o prodaji iz trenutnog reda.
Korak 5. Makro za ponovno izračunavanje
Ostaje završni dodir. Microsoft Excel ponovo izračunava formule samo kada se podaci na listu promijene ili kada se pritisne tipka F9, i želimo da se ponovno izračunavanje dogodi kada se izbor promijeni, tj. kada se aktivna ćelija pomjeri preko lista. Da bismo to uradili, moramo dodati jednostavan makro u našu radnu svesku.
Kliknite desnim tasterom miša na karticu sa podacima i izaberite komandu izvor (Izvorni kod). U prozoru koji se otvori unesite kod makro rukovatelja za događaj promjene odabira:
Kao što možete lako zamisliti, sve što radi je da pokrene ponovno izračunavanje lista kad god se promijeni pozicija aktivne ćelije.
Korak 6. Isticanje trenutne linije
Radi jasnoće, možete dodati i pravilo uslovnog oblikovanja da biste istakli zemlju koja je trenutno prikazana na grafikonu. Da biste to učinili, odaberite tabelu i odaberite Početna — Uvjetno oblikovanje — Kreirajte pravilo — Koristite formulu za određivanje ćelija za formatiranje (Početna — Uvjetno oblikovanje — Novo pravilo — Koristite formulu da odredite koje ćelije treba formatirati):
Ovdje formula provjerava za svaku ćeliju u tabeli da li se njen broj reda poklapa s brojem pohranjenim u varijabli TekRow, a ako postoji podudaranje, tada se pokreće popunjavanje odabranom bojom.
To je to – jednostavno i lijepo, zar ne?
bilješke
- Na velikim tablicama, sva ova ljepota može usporiti – uslovno formatiranje je stvar koja zahtijeva resurse, a ponovno izračunavanje za svaki odabir također može biti teško.
- Da biste spriječili da podaci nestanu na grafikonu kada je ćelija slučajno odabrana iznad ili ispod tabele, možete dodati dodatnu provjeru imenu TekRow koristeći ugniježđene IF funkcije obrasca:
=IF(CELL("red")<4,IF(CELL("red")>4,CELL("red")))
- Isticanje određenih kolona u grafikonu
- Kako napraviti interaktivni grafikon u Excelu
- Odabir koordinata