vsebina
Predpostavimo, da si moramo vi in jaz vizualizirati podatke iz naslednje tabele z vrednostmi prodaje avtomobilov po različnih državah v letu 2021 (mimogrede, resnični podatki vzeti od tukaj):
Ker je število podatkovnih nizov (držav) veliko, bo poskus strpanja vseh v en graf naenkrat pripeljal do groznega "špageti grafikona" ali do gradnje ločenih grafikonov za vsako serijo, kar je zelo okorno.
Elegantna rešitev tega problema je lahko izris grafikona samo na podatkih iz trenutne vrstice, torej vrstice, kjer se nahaja aktivna celica:
Implementacija tega je zelo enostavna – potrebujete samo dve formuli in en majhen makro v 3 vrsticah.
Korak 1. Trenutna številka vrstice
Prva stvar, ki jo potrebujemo, je imenovani obseg, ki izračuna številko vrstice na listu, kjer se zdaj nahaja naša aktivna celica. Odpiranje na zavihku Formule – Upravitelj imen (Formule — Upravitelj imen), kliknite na gumb ustvarjanje (Ustvari) in tam vnesite naslednjo strukturo:
- Ime – poljubno primerno ime za našo spremenljivko (v našem primeru je to TekString)
- Območje – v nadaljevanju morate izbrati trenutni list, tako da bodo ustvarjena imena lokalna
- Območje – tukaj uporabljamo funkcijo CELICA (CELICA), ki lahko izda kup različnih parametrov za določeno celico, vključno s številko vrstice, ki jo potrebujemo – za to je odgovoren argument »line«.
Korak 2. Povezava do naslova
Za prikaz izbrane države v naslovu in legendi grafikona moramo pridobiti sklic na celico z imenom njene (države) iz prvega stolpca. Da bi to naredili, ustvarimo drugo lokalno (tj Območje = trenutni list, ne knjiga!) imenovan obseg z naslednjo formulo:
Tukaj funkcija INDEX izbere iz danega obsega (stolpec A, kjer so naše države podpisnice) celico s številko vrstice, ki smo jo predhodno določili.
Korak 3. Povezava do podatkov
Zdaj pa na podoben način dobimo povezavo do obsega z vsemi podatki o prodaji iz trenutne vrstice, kjer se zdaj nahaja aktivna celica. Ustvarite drug imenovan obseg z naslednjo formulo:
Tukaj tretji argument, ki je nič, povzroči, da INDEX kot rezultat ne vrne ene vrednosti, ampak celotno vrstico.
Korak 4. Zamenjava povezav v grafikonu
Sedaj izberite glavo tabele in prvo vrstico s podatki (obseg) ter na podlagi njih sestavite grafikon z uporabo Vstavi – grafikoni (Vstavi — grafikoni). Če v grafikonu izberete vrstico s podatki, bo funkcija prikazana v vrstici s formulami ROW (SERIJA) je posebna funkcija, ki jo Excel samodejno uporablja pri ustvarjanju katerega koli grafikona za sklicevanje na izvirne podatke in oznake:
Previdno zamenjajmo prvi (podpis) in tretji (podatki) argument v tej funkciji z imeni naših obsegov iz 2. in 3. koraka:
Grafikon bo začel prikazovati podatke o prodaji iz trenutne vrstice.
Korak 5. Makro za ponovni izračun
Ostane še zadnji dotik. Microsoft Excel ponovno izračuna formule samo, ko se podatki na listu spremenijo ali ko pritisnete tipko F9, in želimo, da se preračun izvede, ko se spremeni izbor, torej ko se aktivna celica premakne po listu. Da bi to naredili, moramo v svoj delovni zvezek dodati preprost makro.
Z desno miškino tipko kliknite zavihek podatkovnega lista in izberite ukaz vir (izvorna koda). V okno, ki se odpre, vnesite kodo upravljavca makra za dogodek spremembe izbire:
Kot si zlahka predstavljate, vse, kar naredi, je, da sproži ponovni izračun lista, kadar koli se spremeni položaj aktivne celice.
Korak 6. Označevanje trenutne vrstice
Zaradi jasnosti lahko dodate tudi pravilo pogojnega oblikovanja, da označite državo, ki je trenutno prikazana na grafikonu. Če želite to narediti, izberite tabelo in izberite Domov — Pogojno oblikovanje — Ustvari pravilo — Uporabi formulo za določitev celic za oblikovanje (Domov — Pogojno oblikovanje — Novo pravilo — Uporabite formulo, da določite, katere celice oblikovati):
Tukaj formula za vsako celico v tabeli preveri, ali se njena številka vrstice ujema s številko, shranjeno v spremenljivki TekRow, in če obstaja ujemanje, se sproži polnjenje z izbrano barvo.
To je to – preprosto in lepo, kajne?
Opombe
- Na velikih tabelah se lahko vsa ta lepota upočasni – pogojno oblikovanje je stvar, ki zahteva veliko virov, ponovni izračun za vsako izbiro pa je lahko tudi težak.
- Če želite preprečiti, da bi podatki izginili na grafikonu, ko je celica pomotoma izbrana nad ali pod tabelo, lahko imenu TekRow dodate dodatno preverjanje z uporabo ugnezdenih funkcij IF obrazca:
=ČE(CELICA(“vrstica”)<4,ČE(CELICA("vrstica")>4,CELICA(“vrstica”)))
- Označevanje določenih stolpcev v grafikonu
- Kako ustvariti interaktivni grafikon v Excelu
- Izbira koordinat