Az adatok gyors áttekinthetőségét azzal is elősegítjük, hogy formázással kiemelünk bizonyos tulajdonságú adatokat, elősegítve ezzel azok könnyű összehasonlítását, és a kirívó esetek gyors észrevételét. Ezeket a formázásokat beállíthatnánk éppen manuálisan is, de az egyrészről sokkal több időt igényelne, másrészről az adatok megváltozása esetén kezdhetnénk az egészet elölről.
Az ilyen esetekre a feltételes formázás való, ami nem csak a gyakorlati életben hasznos, de kedvelt témája a versenypéldáknak is: szinte nincs is olyan forduló, amikor nincs ehhez kapcsolódó feladat. Ritka azonban, hogy a példa szövege konkrétan a funkció használatát kéri, elvárás ugyanis a versenyzőktől, hogy maguktól is felismerjék, hogy erre van szükség. Aki ennek nem tesz eleget, és kézzel végzi el a formázást, biztosan nem kap egyetlen pontot sem a feladatra.
Az újabb Excel rengeteg beépített lehetőséget tartalmaz, úgymint színskálák, ikonok használata, értéktől függő színátmenetes kitöltés stb. Ezekről olvashatunk a súgóban, vagy felfedezhetjük a lehetőségeket magunk is. A versenyeken inkább arra szokott szükség lenni, hogy egy új szabályt hozzunk létre, ahol a formázandó cellákat képlettel jelöljük ki.
28_full.jpg28. ábra: Speciális (nem beépített) szabály megadásakor az utolsó szabálytípust kell választani.Tehát egy új szabály létrehozásakor a következőket kell megadni:
Néhányan jobban szeretik a formázást csak egy cellára/sorra létrehozni, majd a formátummásoló ecsettel átvinni a többi cellára is. Ezt a módszert azonban veszélyesen találom, mert megnehezíti a megfelelő hivatkozások megadását.
A szabályok kezelése ablakban lehetőség van megnézni, hogy az éppen aktuális kijelölésben vagy az egész munkalapon milyen szabályok vannak érvényben. Ezek közül egyet kijelölve módosíthatjuk a paramétereit (Szabály szerkesztése), vagy akár törölhetjük is (Szabály törlése).
29_full.jpg29. ábra: Kezdőlap/ Stílusok/ Feltételes formázás/ Szabályok kezelése. Az éppen aktuális kijelölésre három szabály vonatkozik, ezek mind ugyanarra a tartományra érvényesek. (Az érvényességnél a dollárjelek automatikusan kerülnek oda, de egyébként semmi jelentőségük sincs.)A feltételekhez tartozik egy leállítás, ha igaz jelölőnégyzet is. Ez egyrészt a korábbi verziókkal való kompatibilitás végett került ide, másrészt sok esetben megkönnyítheti a szabályok feltételeinek megfogalmazását is. Erre mutatok majd egy példát, a Négyzetszámok című feladatban.
Az ablakban ez a számomra megtévesztő tájékoztatás olvasható: "szabály (ebben a sorrendben alkalmazva)". Talán jobb lett volna a "fontossági sorrend" kifejezést használni, a félreértések elkerülése végett. Ennek akkor van jelentősége, ha egy cellára több, egymásnak ellentmondó formázást tartalmazó szabály feltétele is teljesül. Például, ha pirosra akarjuk színezni az 5-nél nagyobb értékeket, zöldre pedig a párosakat, akkor mi legyen az 5-nél nagyobb páros számokkal? A kérdés ebben a formában nem megválaszolható, szükség van annak pontosítására. Az ablakban a feljebb lévő feltétel számít fontosabbnak, így elsősorban az érvényesül. Ha például az előbb említett piros színezést tartjuk fontosabbnak, annak kell az elsőnek lennie. Ekkor 6 piros lesz, és nem zöld. A nyilak segítségével lehetőség van a szabályok felcserélésre, ezzel változtatva prioritásukat. Például a zöld színezést az első helyre áthelyezve 6 már nem piros, hanem zöld színű lesz. Ha pedig a zöld betűszín helyett zöld háttérszínt állítanánk be, akkor a mindkét feltételnek eleget tevő cellák mindegyike zöld háttérszínnel és piros betűszínnel rendelkezne, függetlenül a feltételek sorrendjétől.
Számomra sokkal testhezállóbb a sorrend értelmezésének algoritmikus megközelítése. Eszerint a szabályok kiértékelése éppen ellenkező sorrendben történik, mint a felsorolás: alulról felfelé haladva teljesíti az Excel a szabályokban megadottakat, elrontva esetleg ezzel egy korábbi (lentebbi) szabály formázását. Természetesen ez a fajta megközelítés is ugyanazt az eredményt szolgáltatja, mint az Excel súgójában szereplő fontossági sorrend, amennyiben nem használjuk a leállítás, ha igaz jelölőnégyzeteket.
Nekem egyébként az a módszerem, hogy összetettebb képletek esetén, vagy ha nem tudom pontosan a használt függvény argumentumlistáját, a feltételes formázás feltételének képletét előbb a munkalap egy nem használt cellájában készítem el. Ha elkészültem, és úgy tűnik, hogy jól működik, kijelölöm a képletet a szerkesztőlécen, és a vágólap segítségével beillesztem a feltételes formázás ablakába.
Zavaró ugyanis számomra, hogy a feltételes formázás ablakában, a feltétel megadásakor, nem működik a súgó, ráadásul a billentyűzet nyilait sem tudom a megszokott módon használni. Azt is furcsának találom, hogy képletszerkesztéskor, amikor a használni kívánt cellákra kattintok, a feltételes formázás ablakában alapértelmezetten abszolút hivatkozások keletkeznek, míg a munkalap egy cellájában állva relatívak.
Ez a módszer akkor a leghasznosabb, ha a képletet az érintett tartománytól jobbra, annak első sorában készítjük el. Amennyiben a képlet helytelenül működik, másoljuk azt lefele (és jobbra)! Így ugyanis keletkezik egy IGAZ/HAMIS értékeket tartalmazó tartomány, melynek értékei könnyen összevethetőek az eredeti táblázat megfelelő celláival. Így könnyebben rá lehet jönni, mit rontottunk el a képletben (gyakori hiba például a nem megfelelő hivatkozás). A feladatok megoldásánál is ezt a módszert alkalmazom majd.
30_full.jpg30. ábra: A módszerrel ellenőrizhető, hogy mely cellára milyen értéket ad az a képletünk, amit a feltételes formázásnál szeretnénk majd használni. Így könnyebb észrevenni a hibás működést, és a megoldásra is hamarabb rájöhetünk.11. példa: Egy tetszőleges táblázat minden második sorának árnyékolása
Versenyen sem ritka (például OKTV 2005-2006 3. forduló, 5/B), hogy egy összetettebb feltételes formázási feladatnak egy része az, hogy a táblázat minden második sora legyen a többitől eltérő színű, de egyébként is gyakran alkalmazzák ezt a módszert. Az ilyen formázással megakadályozható a sorok közti "elcsúszás" olvasáskor.
Természetesen az nem opció, hogy minden második sort kijelöljük, majd beállítjuk rá a háttérszínt, hiszen egy nagyobb táblázat esetén ez lehetetlen, és egy sor kitörlésénél vagy beszúrásánál kezdhetnénk elölről a formázást. A megoldás a feltételes formázás.
Az utasítások könnyebb követhetőségéért tételezzük fel, hogy egy fejléccel ellátott, A oszloptól az E-ig terjedő táblázatról van szó. Így a feltétel képletét az előbb ismertetett módszer szerint F2-ben készítjük el.
Mivel a SOR() függvény az adott sor minden oszlopában ugyanazt az eredményt adja, így kivételesen nem számít, teszünk-e $-jelet az A elé. A sorokra viszont muszáj relatívan hivatkozni, mert különben a lejjebb lévő sorokat is a szerint színezné, hogy a tartomány első sora páros sorszámú-e. Érdemes a képletet lefele végigmásolni, hogy lássuk a hivatkozások alakulását és a feltétel teljesülését minden sorra.
A szabályt alkalmazva az eredmény látható: a felváltott színezés akkor sem romlik el, ha beszúrunk vagy kitörlünk egy sort.
A megadott feltétel minden egyes cellára IGAZ vagy HAMIS értéket ad. Mivel relatív hivatkozást használtunk, B2-re a feltétel valójában =MARADÉK(SOR(B2);2)=0 lesz, A3-ra =MARADÉK(SOR(A3);2)=0 stb. Ez ellenőrizhető is az F oszlopban, amennyiben végrehajtottuk a függvénymásolást.
A példa megértését követően érdemesnek tartom a feltételes formázás leírásának újbóli elolvasását, a leírtak elmélyítése érdekében.
12. példa: Nemes 2010-2011 1. forduló, 5/C (A szabadságharc kronológiája)
Az 1848-1849-es szabadságharc eseményeit tartalmazza a táblázat, melynek sorait a következőképpen kell színezni:
A harmadik pont igazából nem tartogat számunkra feladatot, hiszen a betűszín és a háttérszín nem kizáró formázások: ha megadjuk az első két pontban leírtaknak megfelelő szabályokat, akkor azok a sorok, amelyekre mindkét feltétel teljesül, mindkét formázással rendelkezni fognak. Megjegyezném, hogy bizonyos korábbi Excel-verzióknál ez még nem volt igaz, ezért is szerepel sok helyütt a pontozókban külön az ÉS kapcsolatnak megfelelő feltétel megadása.
Feltételes formázásnál sajnos nem lehet helyettesítő karaktereket használni, így szövegkezelő függvényekre van szükség a feladat megoldásához.
Mindkét szabályról elmondható, hogy a feltételek csak bizonyos oszlopokkal dolgoznak, a formázás viszont a teljes sorokra teljesül. Ezért használtunk a soroknál relatív, az oszlopoknál pedig abszolút hivatkozást.
13. példa: Négyzetszámok
Ezzel a példával először azt fogom bemutatni, hogy hogyan alakulnak a hivatkozások, amennyiben nem soronként, hanem cellánként kell formáznunk, majd ismertetem a leállítás, ha igaz jelölőnégyzet működését.
"Emeljük ki a négyzetszámokat narancssárga háttérszínnel egy 10×10-es, számokat tartalmazó táblázatban! A nem négyzetszámok közül a párosak legyenek kék, a páratlanok zöld betűszínnel formázva!"
31_full.jpg31. ábra: Négyzetszámok kiemelése.Foglalkozzunk egyelőre csak a négyzetszámok kiemelésével!
Ellentétben mind az előbbi, mind az ezt követő példával, most nem sorokat, hanem egyesével a cellákat kell színezni, ezért másképp alakulnak a hivatkozások.
Mind az oszlopra, mind a sorszámra relatívan kell hivatkoznunk, hogy a cellák formátuma a saját tartalmuktól függjön. Érdemes a képletet végigmásolni lefele, illetve jobbra kilenc soron és oszlopon át, hogy lássuk, az egyes cellákra milyen képlet adódik majd, és azok milyen értéket vesznek fel.
Fontos, hogy a kitöltésen kívül más formázást ne adjunk meg, így a betűszínt is maradjon automatikus. Ekkor a formázás egész egyszerűen "békén hagyja" a cella eredeti betűszínét, legyen az bármilyen.
A négyzetszámok kiemelésével végeztünk, a páros és páratlan számok formázása következik. A szabály létrehozása az előbb leírt lépésekben történik, így részletezni nem fogom.
Az Excel 2013-as verziójától MARADÉK() függvény helyett PÁROSE() vagy PÁRATLANE() függvényeket is használhatunk.
Látható, hogy ekkor nem az történik, mint amit a feladat kért: a négyzetszámokat is kiszíneztük kékre, illetve zöldre, holott azokat csak a narancssárga háttérrel kellett volna ellátni. Az ok az, hogy az egyik feltétel lefutását követően lefut egy másik is. Sokan ilyenkor beállítják a négyzetszámokat kiemelő szabály formátumában a betűszínt feketére, helytelenül. Látszatra ugyan megoldja a problémát, amennyiben az adataink eredetileg feketék voltak, de akkor is hibás a működése: az egész táblázat kijelölését követő betűszín-módosítás nem lesz hatással egyetlen cellára sem, holott a feladat szövege alapján a négyzetszámoknak a kézzel beállított színnel kellene rendelkezniük.
Szoftverhiba okozhatja azt a jelenséget, hogy ha egyszer megadjuk az automatikus helyett pl. a fekete betűszínt, hiába is állítjuk később azt vissza automatikusra, a fekete betűszín "bennragad". Ilyenkor ki kell törölni a szabályt, és újra megadni.
Megoldja az előbbi problémát, ha a feltételeket kiegészítjük a következőképp:
=ÉS(NEM(INT(GYÖK(A1))=GYÖK(A1));MARADÉK(A1;2)=0) és
=ÉS(NEM(INT(GYÖK(A1))=GYÖK(A1));MARADÉK(A1;2)=1)
Ebben a konkrét példában ez még nem nagy munka, összetettebb szabályok esetén viszont könnyen azzá válhat. E helyett a módszer helyett bölcsebb inkább az ezt a célt szolgáló leállítás, ha igaz funkciót használni: ha már egyszer formáztuk az egyik feltétel szerint, a többi szabályt ne vegye figyelembe. Ekkor a feltételek a következő egyszerű formában maradnak:
32_full.jpg32. ábra: A "leállítás, ha igaz" funkció használata esetén egyszerűsödnek a feltételek.14. példa: OKTV 2009-2010 2. forduló, 3/O (Afrimpex)
Adott egy cég telefonhívásainak adatait tartalmazó táblázat, ahol a hívások időtartama soha nem éri el az egy órát. A feladat a sorok formázása, a következőképp:
Az utolsó ponttal most sincs dolgunk, tehát csak három szabályt kell elkészítenünk. Elsőként értelmezzük a feladatot, hogy könnyebben megfogalmazzuk majd a feltételeket! A következő jelöléseket fogom használni: K - a hívás kezdete, V - a hívás vége.
# | Feltétel | Formátum |
1. | K<09:00:00 és V≥09:00:00 | dőlt, zöld |
2. | K≤17:00:00 és V>17:00:00 | dőlt, kék |
3. | K>17:00:00 vagy V<09:00:00 | félkövér, piros |
Ha valaki nincs tisztában azzal, miként kezeli az Excel az időt, szükség van az időpontok függvényekkel történő részekre bontására, és így a következőhöz hasonló bonyolult feltételek születnének:
=ÉS(
VAGY(
ÓRA(K)<17;
ÉS(
ÓRA(K)=17;
PERCEK(K)=0;
MPERC(K)=0
)
);
ÉS(
ÓRA(V)=17;
VAGY(
PERCEK(V)<>0;
MPERC(V)<>0
)
)
)
Ha már nem emlékeznél, hogyan is tárolja az Excel a dátum+időket, olvasd el a Dátum- és időkezelés című leckét, mielőtt továbbhaladnál!
Ha valaki nem olvassa el elég figyelmesen a feladatszöveget, és átsiklik afelett, hogy a hívások mindenképpen egy óra alattiak, akkor a képlet még tovább bonyolódna. Persze a feladat így is megoldható, de mivel összetettebbek a képletek, könnyebben hibázhatunk, a rengeteg pluszmunkáról nem is beszélve. A mellékelt megoldásban ez a módszer is megtalálható, a Hívások (3) munkalapon.
A másik módszer kitalálásához érdemes előbb átgondolni, miként is tárolódnak az Excelben az időpontok. Valójában minden időpont egyben dátum is, és fordítva. A két fogalom különválasztását csupán az adja, hogy a tizedesvesszőtől jobbra vagy balra lévő számjegyekkel foglalkozunk. Tehát ha csak az adott dátum+idő idő részével szeretnénk foglalkozni (mivel most csak azok lényegesek), egész egyszerűen "le kell vágni" a tizedesvesszőtől jobbra eső részt, majd csak ezzel a "levágott" résszel végezni az összehasonlításokat. Erre kiváló a MARADÉK() függvény, amennyiben osztónak egyet adunk meg.
A MARADÉK() függvény helyett használható a K-INT(K) módszer is.
A kívánt feltételeket tehát elég így megadni:
1. feltétel:
=ÉS(
MARADÉK(K;1)<IDŐ(9;0;0);
MARADÉK(V;1)>=IDŐ(9;0;0)
)
2. feltétel:
=ÉS(
MARADÉK(K;1)<=IDŐ(17;0;0);
MARADÉK(V;1)>IDŐ(17;0;0)
)
3. feltétel:
=VAGY(
MARADÉK(K;1)>IDŐ(17;0;0);
MARADÉK(V;1)<IDŐ(9;0;0)
)
Érdemes ezeket - a korábban ismertetett módszer alapján - egy cellában ténylegesen elkészíteni, hogy lássuk, helyes-e a meggondolásunk. Mellesleg így sokkal átláthatóbb a képlet, könnyebb szerkeszteni, és működik a függvények súgója is, ha megfeledkeznénk az argumentumlistáról.
Adjuk meg a helyes hivatkozásokat! Ne feledjük, az elkészített képlet jelen példánál A2-re vonatkozik. Mivel azt szeretnénk, hogy az A oszloptól jobbra is ugyanez legyen a feltétel - vagyis pontosan ezeket a cellákat vizsgálja - "le kell rögzíteni az oszlopokat egy dollárjellel" (abszolút hivatkozás). A sorokban lefele haladva már az aktuális sor értékeitől szeretnénk, hogy a formázás függjön, tehát a sorszámok elé ne tegyünk dollárjelet (relatív hivatkozás)!
Szükség van még K és V meghatározására, hiszen a C és D oszlopban a magyar időszámítás szerinti időpontok vannak, nem pedig a tárcsázott országoké. A dolog első nekifutásra egyszerűnek tűnik: kikeressük például FKERES() függvénnyel az időeltolódás mértékét, és hozzáadjuk a vizsgált időponthoz. Ez azonban csak akkor lenne jó, ha az adott időpont óra részéhez adnánk, ami ismét az elején megadott bonyolult feltételekhez vezetne. Ezért ilyen fontos, hogy időpontok helyett tizedes törteket képzeljünk magunk elé! Például Egyiptom esetén 1 hozzáadása nem változtatna az időértéken semmit (valójában 1 nap eltolást jelentene). A helyes megoldás az, ha egy olyan szám többszörösét adjuk hozzá a magyar időpontokhoz, ami az 1 óra értékével egyezik meg. Például Egyiptom esetén az 1 óra számértékének 1-szeresét, Algéria esetén 0-szorosát stb. kell hozzáadni a magyar időponthoz. Ezt a számot megkaphatjuk az =IDŐ(1;0;0) képlettel is, de ha végiggondoljuk, hogy 1 nap "1"-nek felel meg, könnyen megkapjuk, hogy 1 óra ennek huszonnegyed része, vagyis 1/24. Ezt a számot kell tehát beszoroznunk az időeltolódással, és hozzáadnunk a magyar időhöz.
Így már meg tudjuk fogalmazni a feltételeket, és nekiláthatunk a feltételes formázás beállításainak.
Érdemes megnézni a megoldásfájl Hívások (2) munkalapját is, ahol szerepelnek az említett segédcellák, bennük a képlettel.
 |
 |
 |
| Készült az "Országos koordinációval a pedagógusképzés megújításáért” című TÁMOP-4.1.2.B.2-13/1-2013-0007 pályázat keretében. | ||
A tananyag az ELTESCORM keretrendszerrel készült