Sadržaj
Pretpostavimo da ti i ja trebamo vizualizirati podatke iz sljedeće tablice s vrijednostima prodaje automobila po različitim zemljama u 2021. godini (usput, stvarni podaci preuzeti odavde):
Budući da je broj serija podataka (zemalja) velik, pokušaj strpanja svih njih u jedan grafikon odjednom dovest će ili do užasnog "špageti grafikona" ili do izgradnje zasebnih grafikona za svaku seriju, što je vrlo glomazno.
Elegantno rješenje ovog problema može biti iscrtavanje grafikona samo na podacima iz tekućeg retka, odnosno retka u kojem se nalazi aktivna ćelija:
Implementacija ovoga je vrlo jednostavna – potrebne su vam samo dvije formule i jedna mala makronaredba u 3 retka.
Korak 1. Broj trenutne linije
Prvo što nam treba je imenovani raspon koji izračunava broj retka na listu gdje se sada nalazi naša aktivna ćelija. Otvaranje na kartici Formule – Upravitelj imena (Formule — Upravitelj imena), kliknite na gumb stvoriti (Stvoriti) i tamo unesite sljedeću strukturu:
- Ime – bilo koje prikladno ime za našu varijablu (u našem slučaju to je TekString)
- Površina – u nastavku trebate odabrati trenutni list kako bi stvorena imena bila lokalna
- Raspon – ovdje koristimo funkciju CELL (ĆELIJA), koji može izdati hrpu različitih parametara za određenu ćeliju, uključujući broj retka koji nam je potreban – za to je odgovoran argument "linija".
Korak 2. Link na naslov
Za prikaz odabrane zemlje u naslovu i legendi grafikona, moramo dobiti referencu na ćeliju s nazivom (države) iz prvog stupca. Da bismo to učinili, stvaramo još jedno lokalno (tj Površina = trenutni list, ne knjiga!) imenovani raspon sa sljedećom formulom:
Ovdje funkcija INDEX odabire iz zadanog raspona (stupac A, gdje se nalaze naše zemlje potpisnice) ćeliju s brojem retka koji smo prethodno odredili.
Korak 3. Veza s podacima
Sada, na sličan način, dobijmo poveznicu na raspon sa svim podacima o prodaji iz trenutnog retka, gdje se sada nalazi aktivna ćelija. Stvorite još jedan imenovani raspon pomoću sljedeće formule:
Ovdje treći argument, koji je nula, uzrokuje da INDEX ne vrati jednu vrijednost, već cijeli red kao rezultat.
Korak 4. Zamjena veza u grafikonu
Sada odaberite zaglavlje tablice i prvi red s podacima (raspon) i pomoću njih izgradite grafikon na temelju njih Umetni – grafikoni (Umetni — grafikoni). Ako odaberete redak s podacima u grafikonu, funkcija će se prikazati u traci formule RED (NIZ) je posebna funkcija koju Excel automatski koristi pri stvaranju bilo kojeg grafikona za pozivanje na izvorne podatke i oznake:
Pažljivo zamijenimo prvi (potpis) i treći (podaci) argument u ovoj funkciji nazivima naših raspona iz koraka 2 i 3:
Grafikon će početi prikazivati podatke o prodaji iz trenutnog retka.
Korak 5. Makro ponovnog izračuna
Ostaje završni dodir. Microsoft Excel ponovno izračunava formule samo kada se podaci na listu promijene ili kada se pritisne tipka F9, a želimo da se ponovno izračunavanje dogodi kada se promijeni odabir, odnosno kada se aktivna ćelija pomakne po listu. Da bismo to učinili, moramo dodati jednostavnu makronaredbu u našu radnu knjigu.
Desnom tipkom miša kliknite karticu podatkovne tablice i odaberite naredbu izvor (Izvorni kod). U prozoru koji se otvori unesite kod rukovatelja makronaredbom za događaj promjene odabira:
Kao što lako možete zamisliti, sve što radi je pokretanje ponovnog izračuna lista kad god se promijeni položaj aktivne ćelije.
Korak 6. Označavanje trenutne linije
Radi jasnoće, također možete dodati pravilo uvjetnog oblikovanja da biste istaknuli zemlju koja je trenutno prikazana na grafikonu. Da biste to učinili, odaberite tablicu i odaberite Početna — Uvjetno oblikovanje — Stvori pravilo — Koristite formulu za određivanje ćelija za oblikovanje (Početna — Uvjetno oblikovanje — Novo pravilo — Koristite formulu za određivanje ćelija za oblikovanje):
Ovdje formula provjerava za svaku ćeliju u tablici odgovara li njen broj retka broju pohranjenom u varijabli TekRow, a ako postoji podudaranje, tada se pokreće ispuna odabranom bojom.
To je to – jednostavno i lijepo, zar ne?
Bilješke
- Na velikim tablicama sva ta ljepota može usporiti - uvjetno oblikovanje je stvar koja zahtijeva mnogo resursa, a ponovno izračunavanje za svaki odabir također može biti teško.
- Kako biste spriječili nestanak podataka na grafikonu kada je ćelija slučajno odabrana iznad ili ispod tablice, možete dodati dodatnu provjeru nazivu TekRow pomoću ugniježđenih IF funkcija obrasca:
=IF(CELL(“red”)<4,IF(CELL("red")>4,CELL(“red”)))
- Označavanje određenih stupaca u grafikonu
- Kako napraviti interaktivni grafikon u Excelu
- Odabir koordinata