tartalom
Tegyük fel, hogy Önnek és nekem meg kell jelenítenünk az alábbi táblázat adatait a különböző országok autóeladási értékeivel 2021-ben (a valós adatok egyébként innen származnak):
Mivel az adatsorok (országok) száma nagy, az összeset egyszerre egy grafikonba zsúfolva vagy szörnyű „spagettidiagramhoz” vezet, vagy külön diagramok készítéséhez minden sorozathoz, ami nagyon körülményes.
Elegáns megoldás erre a problémára, ha egy diagramot csak az aktuális sor adataira rajzolunk, vagyis arra a sorra, ahol az aktív cella található:
Ennek megvalósítása nagyon egyszerű – mindössze két képletre és egy apró makróra van szüksége 3 sorban.
1. lépés: Aktuális sorszám
Az első dolog, amire szükségünk van, egy elnevezett tartomány, amely kiszámítja a sor számát azon a lapon, ahol az aktív cellánk most található. Megnyitás egy lapon Képletek – Névkezelő (Képletek — Névkezelő), kattintson a gombra Teremt (Teremt) és írja be oda a következő struktúrát:
- Keresztnév – bármilyen megfelelő név a változónknak (esetünkben ez a TekString)
- Terület – a továbbiakban az aktuális lapot kell kiválasztani, hogy a létrehozott nevek helyiek legyenek
- Választék – itt a függvényt használjuk SEJT (SEJT), amely egy csomó különböző paramétert tud kiadni egy adott cellához, beleértve a szükséges sorszámot is – ezért a „sor” argumentum a felelős.
2. lépés: Link a címhez
Ahhoz, hogy a kiválasztott országot a diagram címében és jelmagyarázatában megjelenítsük, az első oszlopból hivatkozást kell kapnunk a cellára a (ország)nevével. Ehhez létrehozunk egy másik helyi (pl Terület = aktuális munkalap, nem könyv!) egy elnevezett tartomány a következő képlettel:
Itt az INDEX függvény egy adott tartományból (A oszlop, ahol aláíró országaink találhatók) kiválaszt egy cellát az általunk korábban meghatározott sorszámmal.
3. lépés Hivatkozás az adatokhoz
Most hasonló módon kapjunk egy hivatkozást egy olyan tartományra, amely az aktuális sor összes értékesítési adatát tartalmazza, ahol most az aktív cella található. Hozzon létre egy másik elnevezett tartományt a következő képlettel:
Itt a harmadik argumentum, amely nulla, azt eredményezi, hogy az INDEX nem egyetlen értéket, hanem az egész sort ad vissza.
4. lépés: Hivatkozások helyettesítése a diagramban
Most válassza ki a táblázat fejlécét és az első sort az adatokkal (tartomány), és készítsen ezek alapján diagramot Beszúrás – Diagramok (Beszúrás – diagramok). Ha a diagramban adatokat tartalmazó sort választ ki, akkor a függvény megjelenik a képletsorban SOR (SOROZAT) egy speciális funkció, amelyet az Excel automatikusan használ bármilyen diagram létrehozásakor, hogy hivatkozzon az eredeti adatokra és címkékre:
Óvatosan cseréljük ki ebben a függvényben az első (aláírás) és harmadik (adat) argumentumot a 2. és 3. lépéstől származó tartományaink nevére:
A diagram az aktuális sor értékesítési adatait kezdi el megjeleníteni.
5. lépés: Újraszámítási makró
Az utolsó simítás marad. A Microsoft Excel csak akkor számítja újra a képleteket, ha a lapon szereplő adatok megváltoznak, vagy ha lenyomnak egy billentyűt F9, és azt akarjuk, hogy az újraszámítás akkor történjen meg, amikor a kijelölés megváltozik, azaz amikor az aktív cellát áthelyezik a lapon. Ehhez egy egyszerű makrót kell hozzáadnunk a munkafüzetünkhöz.
Kattintson a jobb gombbal az adatlap fülre, és válassza ki a parancsot forrás (Forráskód). A megnyíló ablakban írja be a makrókezelő kódját a kiválasztás módosítási eseményéhez:
Könnyen elképzelhető, hogy ez csak egy lap újraszámítását indítja el, amikor az aktív cella helyzete megváltozik.
6. lépés: Az aktuális vonal kiemelése
Az egyértelműség kedvéért feltételes formázási szabályt is hozzáadhat a diagramon jelenleg látható ország kiemeléséhez. Ehhez válassza ki a táblázatot, és válassza ki Kezdőlap — Feltételes formázás — Szabály létrehozása — Használja a képletet a formázandó cellák meghatározásához (Főoldal — Feltételes formázás — Új szabály — Képlet segítségével határozza meg, mely cellákat kell formázni):
Itt a képlet a táblázat minden cellájánál ellenőrzi, hogy a sorszáma megegyezik-e a TekRow változóban tárolt számmal, és ha van egyezés, akkor a kiválasztott színnel való kitöltést indítja el.
Ennyi – egyszerű és szép, igaz?
Megjegyzések
- A nagy asztalokon mindez a szépség lelassulhat – a feltételes formázás erőforrás-igényes dolog, és az egyes kijelölések újraszámítása is nehézkes lehet.
- Annak elkerülése érdekében, hogy az adatok eltűnjenek a diagramról, amikor véletlenül egy cellát választanak ki a táblázat felett vagy alatt, további ellenőrzést adhat a TekRow névhez az űrlap beágyazott IF-függvényeivel:
=IF(CELL("sor")<4,IF(CELL("sor")>4,CELL("sor")))
- Meghatározott oszlopok kiemelése egy diagramban
- Hogyan készítsünk interaktív diagramot Excelben
- Koordináta kiválasztása