a formázó képlet
2021-12-08 Excel 2019
fogalmak
A formázó képlet a feltételes formázás alapja. Eredménye logikai érték. Egy excel-képlet csak akkor adhat logikai értéket eredményül, ha egy logikai értéket eredményező függvény, vagy ha egy összehasonlítás. Mármint a képlet. Az összehasonlítás általános alakja: =<kifejezés><reláció><viszonyítási alap>. Ne felejtsük el, hogy maga a kifejezés is tartalmazhat összehasonlításokat!
Vegyünk egy egyszerű példát! Egy tanár a következő feltételekhez kötötte a félév elfogadását: (1) a hallgató vagy tart egy kiselőadást vagy ír egy esszét és (2) a beszámolója legalább ötven pontos.
Ha az IGAZ értéke egy és a HAMIS-é nulla, akkor a B2 és C2 összegének legalább egynek kell lennie, mert vagy a kiselőadásnak vagy az esszének meg kell lennie. Ha ez teljesül, akkor az első összehasonlítás IGAZ, tehát egy…
Ismerkedjünk meg néhány gyakori feltételes formázással és főleg a képletükkel.
dátumok feltételes formázása
…ami lehet (1) meghatározott nap, illetve időszak vagy (2) az aktuális dátumhoz viszonyított dátumegység. Például, formázd a 2021.11.27-es dátumokat, vagy formázd a 2021.03.07. és a 2021.07.03. közé eső dátumokat, a határnapokat is beleértve. Ezek az első csoportba tartozó műveletek. Formázd az aktuális dátumokat, vagy formázd a következő hét napjainak dátumait. Ezek meg a másodikba. Az első csoport formázó képletei nem okozhatnak gondot. Viszont a második csoport már érdekesebb.
A legáltalánosabb, az aktuális dátumhoz köthető „időszakok”, a múlt és a jövő. Gondoljunk csak a lejárt követelések illetve a várható bevételek dátumaira.
Ha akarjuk, akkor a múltba és a jövőbe is belevehetjük az aktuális dátumot, csak a relációt kell kiegészíteni az egyenlőségjellel. A bemutatott formázó képletekben álló „A1” a vizsgált halmaz első elemét tartalmazó cella hivatkozását helyettesíti.
Egyszerűen meg tudjuk határozni az aktuális dátumot megelőző, illetve azt követő „n” napos időszak dátumait is. Mondjuk „n” legyen tíz.
…ha az aktuális dátum 2021.11.28. Ha úgy akarjuk, akkor az aktuális dátumot is beleérthetjük a tíz napba. Most pedig vegyük sorra az aktuális dátumot és az őt közvetlenül megelőző illetve követő dátumegységek formázó képleteit. Dátumegységek a nap, a hét, hónap, negyedév, félév és az év. Haladjunk ebben a sorrendben!
Az aktuális dátumot tartalmazó, valamint az azt közvetlenül megelőző illetve követő hetek feltételes formázására a program beépített szolgáltatását is igénybe vehetjük. De van vele egy kis bibi: a hét náluk vasárnappal kezdődik és szombattal végződik. Nálunk viszont hétfővel kezdődik és vasárnappal záródik!
Mindhárom esetben, a vizsgált dátumnak egyeznie kell a határnapokkal magadott időszak valamelyik dátumával, a határnapokat is beleértve. A két összehasonlítást az ÉS függvénnyel értékeltem ki. A függvény csak akkor ad IGAZ eredményt, ha minden argumentuma IGAZ.
A HÉT.NAPJA függvény az első argumentumával deklarált dátum napjának heti sorszámát adja eredményül. A második argumentumában álló kettes szám a számozás módját írja elő: a hétfő legyen az egyes, a vasárnap pedig a hetes.
[jótanács] A képlet szerkesztésére szolgáló vezérlő a képlet szerkesztésére alkalmatlan. Ezért a munkalap egyik cellájában állítsuk össze a formázó képletet! Ha a kiértékelés logikai értéket eredményez, akkor oké: a képletet másoljuk vágólapra és illesszük be a béna vezérlőbe!
A DÁTUM függvény három argumentumából (évek száma, hónapok száma és napok száma) dátumot képez. Az ÉV és a HÓNAP pedig egy dátum éveinek, illetve hónapjainak számát adja eredményül.
Ha valaki azt mondja, hogy az aktuális hónap formázó képlete túl van bonyolítva, igaza van! Mert így is lehet: =ÉS( ÉV( A1 ) = ÉV( MA()) ; HÓNAP( A1 ) = HÓNAP( MA())). És ha így is lehet, akkor módosítanunk kell az összehasonlításos formázó képlet sémáját: =<kifejezés a halmaz első elemével><reláció><viszonyítási alapot adó kifejezés>.
Ezek után az évek formázó képleteit összeállítani már gyerekjáték.
középértékes vizuális elemzés
Vagy úgy is mondhatnánk: középértékes feltételes formázás. Olyan numerikus halmazok vizsgálhatók ezzel a módszerrel, amelynek elemeitől azt várjuk el, hogy a számegyenesen ábrázolva az ideális értékhez, azaz a középértékhez, a lehető legközelebb álljanak. Másként fogalmazva a vizsgált érték és a középérték abszolút különbsége a lehető legkisebb legyen. Példaként gondoljunk autóbusz járatok követési idejére vagy egy valutára, amelyet az ország nemzeti bankja próbál egy árfolyam-sávban tartani.
A középértékes feltételes formázás dátum-halamazok esetén is szóba jöhet. Például, egy tizenegynapos időszak dátumait akarom formázással kiemelni. A középérték az aktuális dátum, a határnapok pedig az aktuális dátumot megelőző, illetve az azt követő ötödik nap dátumai.
[jótanács] A dátumos formázó képletet a vizsgált időszak minden dátumát tartalmazó vektorban ki kell próbálni! Még a legegyszerűbb képletet se véglegesítsük tesztelés nélkül! A képen látható kis táblázatban én is ezt tettem. Aznap, amikor a képet készítettem, november harmadika volt.
göngyölített összeg képzése feltételes formázással
Miről van szó? Van egy folyamatosan bővülő szám-halmazunk. A számok összegének egy adott határérték alatt kell maradnia, ezért feltételes formázást deklarálunk. Ha az új elem hozzáadásával a halamaz-összeg átlépi a határértéket, akkor a „túlcsordulást” okozó minden elem a többiekétől eltérő formázással lesz megjelenítve.
Nézzünk egy egyszerű példát. Kereskedők vagyunk. Adunk hitelre is, de a hitel összege nem lépheti túl a száz-ezer forintot. Feltételes formázást határoztunk meg a határérték feletti hitelek kiemelésére.
Először elkészítettem az adatbázis-táblázatot, majd kijelöltem a táblázat adatterületét (képen fent). Ezután deklaráltam a feltételes formázást (képen középen). A SZUMHATÖBB függvénnyel összegeztem „név” és „típus” szerint a „vásárlás”-okat. Az összeadandó számokat a függvény első argumentumával kell meghatározni. A további argumentumok párban állnak: tartomány-feltétel. Az első határátlépőig töltöttem fel a táblázatot (képen lent). Pintér Olivér: 60 000 hitel+75 000 hitel.
ismétlődések feltételes formázása
Miért kell ezzel külön foglalkoznunk? Erre van beépített eszköze a programnak! Azért, mert a program szóhasználata eltér a számítástechnikában szokásostól és ezért a végrehajtott művelet sem feltétlenül az lesz, mint amit elképzeltünk.
Tehát, először tisztáznunk kell a fogalmakat! Egy halmaz EGYEDI elemei azok az elemek, amelyek legalább egyszer előfordulnak a halmazban. Például, van-e zöld színű golyó a színes golyók között? Tehát nem azt kérdezzük, hogy hány zöld színű golyó van, hanem azt, hogy van-e egyáltalán.
A halmaz ISMÉTLŐDŐ elemei, azok az elemek, amelyek legalább kétszer előfordulnak a halmazban. Például, van tíz golyónk: egy piros, három zöld, kettő fehér, egy fekete és három kék. Az ismétlődő színek tehát: zöld, fehér és a kék.
És mit nevezünk ISMÉTLŐDÉS-nek? Az ismétlődő elem az elsőt követő előfordulásait. Például, a sorba állított golyók közül a második és a harmadik zöld golyó.
Az alábbi példában a program „ismétlődő” feltételes formázását használtam szürke betűszínnel.
Ahogy látjuk a program az ismétlődő sorok minden előfordulását szürke karakterekkel jeleníti meg. Ha nem az „ismétlődő”, hanem az „egyedi” lehetőséget választjuk, akkor pont fordítva: csak az egyszer előforduló sorokat emeli ki. Erre a mindennapi életben a legritkább esetben van szükségünk!
Általában vagy az egyedi sorokra vagyunk kíváncsiak vagy az ismétlődéseket akarjuk vizsgálni. Az előbbit nevezhetjük akár „mindegyikből az elsőt”, az utóbbit „csak a másolatokat” feltételes formázásnak.
A bal oldali táblázatban az egyedi sorokat a jobb oldaliban az ismétlődő sorok ismétlődéseit emeltem ki kék betűszínes feltételes formázással. A DARABHATÖBB függvény a feltételes megszámlálás eszköze. Argumentumai párban állnak: tartomány-feltétel. A feltételek ÉS logikai viszonyban állnak egymással! A függvény az összes feltételt teljesítő sorok darabszámát adja eredményül.
margitfalvi.arpad@gmail.com
