Képletek használata, feltételes formázás

A képletek (kiszámolandó kifejezések) elemei az operátorok (vagy más szóval műveleti jelek), cellahivatkozások, értékek (állandók, amelyet közvetlenül gépelünk be), munkalapfüggvények, nevek. A képleteket ezekből állítjuk össze a Szerkesztőlécen, miközben egyes elemek beviteléhez segítséget is kapunk. Például a neveket a cím listájáról választhatjuk ki, a beépített függvények használatához a Beszúrás szalag Függvény parancsát alkalmazzuk, vagy a képletsegítő felajánlott lehetőségeiből választunk. Ha kijelölünk a Szerkesztőléc képletében egy hivatkozást, akkor az a táblázatba kattintva, illetve az egér húzásával módosítható.

A képletek szintaxisa

A számítás folyamatát a képlet szintaxisa szabja meg, amely egy (program-) nyelv elemeinek felhasználási sorrendjének, az elemek típusainak képzésére vonatkozó szabályok összessége. A képletek értékét a szabály szerinti sorrendben számolja ki a program. 

A képlet egyenlőségjellel (=) kezdődik, utána szerepel a képlet által kiszámolandó kifejezés, amelyeket balról jobbra értékel ki a program, ugyanakkor a sorrendet befolyásolják a képletekben szereplő műveleti jelek. Így először a hivatkozási operátorokat, legutoljára az összehasonlító operátorokat értékeli ki a program, a köztük lévő sorrend a következő listában látható:

  1. : (kettőspont) , (vessző)  (szóköz) hivatkozási operátorok,
  2. beépített függvény, AND, OR, NOT,
  3. %  százalék,
  4. ^ (hatványozás operátor például a =2^3 képlet eredménye 8),
  5. vagy / (szorzás vagy osztás),
  6. + vagy – (előjel),
  7. + vagy – (összeadás vagy kivonás),
  8. &, (összefűző operátor, több szövegdarabot egyetlen szöveggé fűz össze.)
  9. = < > <= >= <> (összehasonlító operátorok: egyenlő, nem egyenlő, kisebb vagy egyenlő, nagyobb vagy egyenlő, kisebb, nagyobb)

Ennek megfelelően az =6-2*3 képlet értéke 0, mert a szorzás ugyan jobbra áll a kivonástól, de mivel magasabb rendű, mint a kivonás, a program előbb a szorzást hajtja végre. Ez az alapértelmezett kiértékelési sorrend a zárójelekkel módosítható. Például, az =(6-2)*3 képlet értéke 12. 

Az Excel az operátorainak megfelelő típusú értéket vár. Ha ettől eltérő típusút kap, akkor azt megkísérli átalakítani (például a “6” szöveget 6 számmá).

1. Feladat

Készítsünk szorzótáblát az 1 és 10 közötti számok szorzási eredményének bemutatására, majd eredményeit változatlan formában, de a képletek nélkül rögzítsük!

Az alapadatokat az A1:A10, illetve az A1:J1 tartományokban adjuk meg. 

Megoldás

  1. Írjuk az A1 cellába az 1 értéket!
  2. Az A1 cella kitöltőjelét a Ctrl billentyű nyomva tartása közben húzzuk a J1 celláig!
  3. Az A1 cella kitöltőjelét a Ctrl billentyű nyomva tartása közben húzzuk az A10 celláig!
  4. Az Excel beállításai párbeszédpanel Speciális lapján, a Beállítások megjelenítése ehhez a munkalaphoz csoportban jelöljük be a Számított eredmények helyett képletek megjelenítése a cellákban jelölőnégyzetet! Ezt az alapértelmezés szerinti beállítással szemben csak akkor használjuk, ha kíváncsiak vagyunk, hogy milyen képletek vannak a cellákban. Esetünkben pont erről van szó.
  5. Írjuk ugyanis a B2 cellába az =B$1*$A2 képletet! Ez a képlet vegyes hivatkozást tartalmaz a cella egyszerű másolásához.
  6. A B2 cella kitöltőjelét húzzuk a J2 celláig!
  7. A kijelölt B2:J2 tartomány kitöltőjelét húzzuk a J10 celláig! Ezzel a szorzótábla készen is van, az eredményeket megláthatnánk, ha törölnénk a Számított eredmények helyett képletek megjelenítése a cellákban jelölőnégyzetet, de várjunk ezzel…

A rögzítendő képletek

  1. Jelöljük ki az A1:J10 tartományt és másoljuk a vágólapra a Ctrl+C billentyűkombinációval!
  2. Kattintsunk az A12 cellába!
  3. Adjuk ki a Kezdőlap szalag BeillesztésIrányított beillesztés parancsát!
  4. A párbeszédpanelen válasszuk az Értéket választókapcsolót, majd kattintsunk az OK gombra! Azonnal láthatóvá válnak a képletek nélküli eredmények. 

Az eredmények képletek nélkül

  1. A helyi menüből vagy a Kezdőlap szalag Beillesztés gombjának menüjéből is választhatunk a különféle irányított beillesztési módok közül (ahogyan az az ábrán is látható).

Tartománynevek 

A képletekben nagyobb területeket, több cellát lefedő tartományokra a sarokcellákkal hivatkozunk. Az ilyen, bonyolultabb hivatkozások egyszerűsíthetők a tartományok elnevezésével. Az alábbi ábra példáján: ha az A4:B6 tartományt tagok néven nevezzük el, akkor az =SZUM(tagok) függvény a tartomány összegét adja (ami esetünkben 45).

A képletekben egyszerűen hivatkozhatunk névvel a tartományra

Az elnevezett tartományt sor, oszlop beszúrásával megnövelhetjük. Ilyenkor a tartomány mérete megnő, de továbbra is a korábbi névvel hivatkozhatunk rá (így, ha az 5. sor elé szúrunk be sorokat és a keletkező celláknak értéket adunk, akkor az eredetileg C6 cellában lévő eredmény a módosításnak megfelelően változik). 

A neveket a tartomány kijelölése után, a szerkesztőléc elején látható név mezőbe írva hozzuk létre, vagy a Névkezelő Új parancsát adjuk ki. A felesleges neveket a Képletek szalag Névkezelő parancsával megjelenített panelen törölhetjük, kijelölésük után, a Törlés gombbal.

Hivatkozás munkafüzetre és munkalapra

Gyakran előfordul, hogy a számításokban másik munkalap vagy munkafüzet tartományaira kell hivatkoznunk. A képletekben másik munkalapra a következő formán hivatkozunk: 

Munkalapnév!tartomány. ahol a tartomány egyetlen cella vagy tartománynév is lehet.

Másik munkafüzetre utaló külső hivatkozás általános formája: 

[Munkafüzet]Munkalapnév!tartomány.

Az Excel a képletek szerkesztésekor automatikusan bővíti a Szerkesztőlécen kidolgozott képletet, ha húzással vagy kattintással hivatkozást adunk meg. A képletek szerkesztésekor a Szerkesztőlécre automatikusan bekerül, ha egy másik munkalap fülére vagy munkafüzetbe kattintunk.

A leggyakrabban használt függvények

A leggyakrabban használt beépített függvényekkel a cellatartalmakat összegezzük, átlagoljuk, illetve megszámoljuk az értéket tartalmazó cellákat. Az ilyen függvények külön csoportban találhatók az AutoSzum gomb menüjében is.

Függvényt tartalmazó képlet készítésénél vagy utólagos módosításánál, szerkesztésénél a Függvénytár segítségét vehetjük igénybe a munkalapfüggvények beillesztésére. A Függvénytár csoportot a Képletek szalagon találjuk. Itt az AutoSzum gomb menüjében a leggyakoribb függvényeket, a többi gomb menüjében a gombnévhez csoportosított függvényeket választhatjuk ki. Mindegyik gomb menüjében megjelenik a További függvények parancs, amellyel, illetve a Függvénytár vagy a szerkesztőléc Függvény beszúrása ikonjával az összes beépített függvény elérhető.

A művelet végrehajtása előtt kijelöljük az argumentumnak szánt cellatartományt, majd a Képletek vagy Kezdőlap szalag AutoSzum gombjára kattintva legördített menüből kiválasztjuk az alkalmazandó függvényt. Ahogy említettük, így egyetlen kijelölt sort összegezhetünk, melynek eredménye a kijelölést követő első cellába kerül. Ha több, egymás feletti cellával végzünk műveletet, akkor az eredmény a cellák alatti cellába kerül akkor is, ha eredeti szándékunk az egymás feletti sorok, vagy a teljes kijelölt tartomány összegzése lett volna. A függvénytári gombból menü gördíthető le

Ha nem az oszlop aljáig terjedő tartományt jelöltük ki, akkor az eredmények egy új, beszúrt sorban jelennek meg (pontosabban a számoszlop alján is így van ez, csak az üres sorban ezt már nem vesszük észre). Ilyen esetekben a szerkesztőlécen kell módosítanunk a függvény paraméterezésén. Az utoljára használt függvények csoportja is a gyorsabb függvénybeillesztést szolgálja a Függvény beszúrása párbeszédpanelen vagy a Legutóbbiak gomb menüjében.

Néhány gyakori függvény ellenőrzésére, illetve gyors számolásra használhatjuk az AutoSzámolás mezőt. Itt automatikusan hajthatjuk végre hatféle függvény kiértékelését. A függvényeket a számolási tartomány előzetes kijelölését követően, az AutoSzámolás mező jobb egérgombbal kattintva megjeleníthető listájából választjuk ki. E függvények argumentuma mindig az aktuálisan kijelölt munkalaptartomány. E kijelölt tartomány több részletből is állhat, azaz nem szükséges összefüggőnek lennie. A számolás eredménye csak az állapotsorban jelenik meg, cellába nem kerül. A menüben kattintással, kapcsolókkal adjuk meg, mely eredményeket szeretnénk az állapotsorban megjeleníteni. Az eredmények ugyanakkor mind megjelennek a menüben is, a megfelelő kapcsoló mellett, jobb oldalon (még akkor is, ha nem kértük megjelenítésüket a kapcsoló beállításával az állapotsorban).

Az állapotsor AutoSzámolás mezője gyors ellenőrzésre használható

Egyes ritkábban használt – általában matematikai, pénzügyi – függvények csak akkor érhetők el, ha telepítjük és betöltjük (az Office gomb menüjéből legördíthető menü Az Excel beállításai parancsával megjelenített párbeszédpanel Bővítmények kategóriájában) az Analysis ToolPak bővítményt. Erre figyelmeztet, ha a jól megadott függvénynév ellenére a képlet kiértékelése után a cellában a #NÉV? hibaértéket látjuk.

2. Feladat

Gyakorlásképpen számoljuk ki a következő ábrán látható B7 és D7 cella tartalmát az AutoSzum ikon alkalmazásával. Most egyszerre, a nem összefüggő kijelölést és az automatikus tartománykijelölést alkalmazva számoljuk ki e két cella tartalmát! 

Megoldás

1. Rögzítsük az alapadatokat az ábra szerint!2. Kattintsunk a B7 cellába, majd a Ctrl billentyű nyomva tartása mellett a D7 cellába! Ezzel kijelöltük az eredmények megjelenésének helyét.3. Kattintsunk a Képletek szalagra!4. Kattintsunk az Autoszum ikonra. Ekkor megjelenik a szerkesztőlécen a függvény, amely az utoljára kijelölt eredménycellához tartozó összegzendő tartományt mutatja: „=SZUM(D2:D6)”. 5. Az eredmény megjelenik a B7 és D7 cellákban.Alapadatok és eredmények

Függvények használata

A függvények olyan, az Excel fejlesztői által matematikai szabályokat alkalmazva előre meghatározott képletek, amelyek számításokat hajtanak végre argumentumoknak nevezett, általunk adott értékek szintaxisnak nevezett meghatározott sorrendje szerint. Mint láttuk a SZUM függvény értékeket vagy cellatartományokat ad össze, a RÉSZLET függvény a törlesztési időszakra vonatkozó törlesztési összeget számítja ki állandó nagyságú törlesztőrészletek és kamatláb esetén stb. Az Excel mintegy 400 beépített munkalapfüggvényt biztosít a számítások végrehajtására.

A függvény beírása

A függvény szintaxisa a függvény nevével kezdődik, amelyet egy nyitó zárójel, a függvény vesszőkkel elválasztott argumentumai és egy jobb oldali zárójel követ. A képletekben szereplő függvényeket a képletet kezdő egyenlőségjel (=) és az operátorok mögé írhatjuk. A függvényt tartalmazó képletek létrehozásában segítséget nyújt a képletpaletta. 

A függvények által várt argumentumok lehetnek számok, szöveg, logikai érték (IGAZTRUE vagy HAMISFALSE), tömbök, hibaértékek (például: #HIÁNYZIK!) vagy cellahivatkozások. A könyv további részeiben az összes függvény szerepét, alkalmazási körét, kötelezően megadandó és választható argumentumait is ismertetjük. Az argumentumok lehetnek állandók, képletek vagy más függvények is, de ezeknek az adott argumentum számára érvényes értéket kell előállítania. A függvények más függvény számára argumentumként való használata a függvények egymásba ágyazása. 

Az Excel az #ÉRTÉK! hibaértéket jeleníti meg, ha egy beágyazott függvény nem a megfelelő típusú értéket adja. A következő HA függvény például egy beágyazott ÁTLAG függvényt használ, és azt összehasonlítja a 0 értékkel:

=HA(ÁTLAG(F1:F6)>0;”pozitív”;”negatív”)

Egy HA függvényben az első argumentum értékének kötelező típusa logikai érték, így az összehasonlításnak az IGAZ vagy HAMIS értéket kell adnia. Ez a mintafüggvény az őt tartalmazó cellában a “pozitív” értéket jeleníti meg, ha az F1:F6 cellatartomány átlaga nagyobb, mint 0, és a “negatív” értéket jeleníti meg, ha az átlag kisebb vagy egyenlő, mint 0.

A függvények többsége argumentumot vár, kivétel a PI() függvény, amely a Ludolf-féle számot 9 jegy pontossággal adja vissza, illetve a MA() függvény, amely az aktuális dátumot, a MOST(), amely az aktuális időt adja vissza. Ha az = jelet követően csak ezeket írjuk a cellába, akkor a megfelelő cellaértéket kapjuk. Néhány függvényhez tetszőlegesen megválasztható argumentumot is megadhatunk, melyek a számításokhoz nem feltétlenül szükségesek.

Függvény beillesztése gombmenüből

Függvényt tartalmazó képlet készítésénél vagy utólagos módosításánál, szerkesztésénél a Függvénytár segítségét vehetjük igénybe a beillesztéshez. A Függvénytár csoportot a Képletek szalagon találjuk. Itt az AutoSzum gomb menüjében a leggyakoribb függvényeket, a többi gomb menüjében a gombnévhez csoportosított függvényeket választhatjuk ki. Mindegyik gomb menüjében megjelenik a További függvények parancs, amellyel, illetve a Függvénytár vagy a szerkesztőléc Függvény beszúrása ikonjával az összes beépített függvény elérhető.

A függvénytár gombjaiból menük gördíthetők le

Függvényt azonban a képlet belsejében is elhelyezhetünk, mint láttuk, akár egymásba is ágyazhatunk. A függvénybeillesztést segítő párbeszédpanel megjelenítéséhez az = jel begépelése után kattintsunk (vagy akár a = jel begépelése nélkül) a szerkesztőléc Függvény beszúrása ikonjára, vagy valamelyik gombmenü parancsra. Ekkor, ha még nem lett volna a szerkesztőlécen, akkor bekerül az = jel, egyébként a program a kurzor pozíciójára szúrja be a függvényt. 

A cellában hivatkozott cellák és tartományok más és más színnel kijelölve jelennek meg a szerkesztőlécen. Az éppen szerkesztett függvény argumentumainak helyes megadását a szerkesztőléc alatt megjelenő súgó segíti (ha a képletet közvetlenül a cellában szerkesztjük, akkor a segítség a cella alatt jelenik meg). A hivatkozott cellákat egérrel is áthelyezhetjük, illetve megváltoztathatjuk a képletben hivatkozott tartomány méretét. Ügyeljünk arra, hogy bármely cellába kattintás beviszi az adott cella hivatkozását a képletbe.

A szokásos segítség képletszerkesztés közben

A fentebb bemutatott segítséget már korábbról ismertük. Most azonban mindjárt négy újabb segítséget kaptunk. Először: a képlet beírásakor a szerkesztőléc alatt megjelenik a képlet egymondatos leírása (lásd a következő ábrát).

A függvény leírása

Másodszor: a képletben leírt első betű máris egy listát jelenít meg az adott betűvel kezdődő függvényekről, ahonnan gyorsan kiválaszthatjuk a megfelelő függvényt, amelyet így nem kell begépelnünk.

Harmadszor: a legördített gombmenü felett mozgatott kurzor alatt megjelenik a kiválasztott függvény paraméterezése is.

A bevitelt gyorsítja a függvénylista

Negyedszer: a cella vagy a szerkesztőléc alatt megjelenő rövid emlékeztető függvényneve egyben hiperhivatkozás is, amelyre kattintva az adott függvényhez tartozó súgólapot jeleníthetjük meg. A többi „helyzetérzékeny” súgóval ellentétben, amely már egyre kevésbé helyzetérzékeny, vagyis nem a súgókérés helyére vonatkozó segítséget adja, most tényleg a függvény leírását kapjuk.

A függvény paraméterezése is megjelenik

A súgólapon a pontos leírást kipróbálható példa egészíti ki

Függvény beillesztése párbeszédpanelről

A szerkesztőléc Függvény beszúrása ikonjára kattintás után, vagy a valamelyik függvénytári gomb menüjének További függvények parancsa kiadását követően párbeszédpanelen választhatjuk ki az alkalmazandó függvényt. A panel felső részén a Függvény keresése mezőbe beírhatjuk a keresett függvény funkcióját, majd a Keresés gombbal megkerestethetjük az optimális megoldást. 

Ha a panelt választjuk a függvény bevitelére, vagy akár ha a szerkesztőlécen kézzel szerkesztett képletben a kérdéses függvény nevére állunk és a Függvény beszúrása ikonjára kattintunk, akkor egy újabb panel jelenik meg, amely mutatja a függvény megadandó argumentumait, valamint az eddig megadott paraméterek kiértékelésének eredményét (lásd a következő ábrát). 

A függvény beszúrását és kiválasztását segítő párbeszédpanel 

Az argumentumok megadását végezhetjük e panel – függvénytől függő számú – változó mezőibe írva, illetve a bevinni szánt mezőbe kattintás után a munkalapon kattintva, cellatartomány argumentum esetén a munkalapon egeret húzva. 

Ez a párbeszédpanel felhasználható a hibakeresés során is

Ha az argumentum ilyen, kattintással vagy húzással végzett megadásánál zavar (takar) a párbeszédpanel, akkor kattintsunk a párbeszédpanel adott mezőjét követő ikonra. 

Ekkor a párbeszédpanel összezsugorodik, majd az argumentum megadása után szintén a mezőt követő ikonra kattintva felveszi eredeti méretét. Az összezsugorítás mellett a párbeszédpanel természetesen címsoránál fogva új helyre húzható, hogy ne zavarjon a munkalapon végzett függvényparaméterezés közben.

A hivatkozott (a munkalapon színes kerettel jelölt) cellák vagy tartományok jobb alsó sarkában megtalálható kitöltőjelet az egér bal gombjának folyamatos nyomva tartása mellett elhúzva adhatjuk meg a tartomány új méretét. A hivatkozás áthelyezéséhez a hivatkozott cella vagy tartomány határán látható színes szegélyt kell hasonló módon egérrel megfognunk, és az új területre mozgatnunk.

A párbeszédpanel átmenetileg összezsugorítható

A Függvényargumentumok párbeszédpanel segítségével a függvényeket argumentumokként ágyazhatjuk egymásba. Például a B függvényt beszúrhatjuk argumentumként az A függvénybe, ha a szerkesztőlécen a nyílra kattintunk. Ha az A függvénybe további argumentumokat szeretnénk bevinni, a szerkesztőlécen kattintsunk az A függvény mögé, majd a szerkesztőléc elejéről válasszunk újabb függvényt vagy a munkalapról hivatkozást. Ilyen egymásba ágyazott függvényeket láthattunk korábban a GYÖK függvény paramétereiként megadott HATVÁNY függvények esetében is (lásd a fenti ábrát).

A Függvényargumentumok panel a képletekben előforduló függvények szerkesztésére is alkalmas. Jelöljünk ki egy képletet tartalmazó cellát, majd kattintsunk a szerkesztőléc függvény beszúrása ikonjára, hogy megjelenjék a panel. A panel tartalmazza a képlet első függvényét és annak minden argumentumát. Ekkor szerkeszthetjük az első függvényt, a képlet bármely más függvényének szerkesztéséhez kattintsunk a szerkesztőlécen a képletben a módosítandó helyre, majd a szerkesztőléc függvény beszúrása ikonjára.

A leggyakoribb függvények

A munkalapokon leggyakrabban használt függvények a cellatartalmak összegzésére, átlagolására, az értéket tartalmazó cellák megszámolására szolgálnak. Az ilyen függvények külön csoportban találhatók az AutoSzum gomb menüjében is. Ezeket a függvényeket a fentiektől eltérően, egy még egyszerűbb módszerrel is beilleszthetjük, de csak akkor, ha oszloponként, vagy egyetlen soronként szerepelnek a paraméterek.

Néhány a leggyakrabban használt műveletek közül

Tartománynevek 

A képletekben nagyobb területeket, több cellát lefedő tartományokra a sarokcellákkal hivatkozunk. Az ilyen, bonyolultabb hivatkozások egyszerűsíthetők a tartományok elnevezésével. Az alábbi ábra példáján: ha az A4:B6 tartományt tagok néven nevezzük el, akkor az =SZUM(tagok) függvény a tartomány összegét adja (ami esetünkben 45).

A képletekben egyszerűen hivatkozhatunk névvel a tartományra

Nevek létrehozása és törlése a Névkezelőben

Az elnevezett tartományt sor, oszlop beszúrásával megnövelhetjük. Ilyenkor a tartomány mérete megnő, de továbbra is a korábbi névvel hivatkozhatunk rá (így, ha az 5. sor elé szúrunk be sorokat és a keletkező celláknak értéket adunk, akkor az eredetileg C6 cellában lévő eredmény a módosításnak megfelelően változik). 

A neveket a tartomány kijelölése után, a szerkesztőléc elején látható név mezőbe írva hozzuk létre, vagy a Névkezelő Új parancsát adjuk ki. A felesleges neveket a Képletek szalag Névkezelő parancsával megjelenített párbeszédpanelen törölhetjük, kijelölésük után, a Törlés gombbal.

Hivatkozás munkafüzetre és munkalapra

Gyakran előfordul, hogy a számításokban másik munkalap vagy munkafüzet tartományaira kell hivatkoznunk. A képletekben másik munkalapra a következő formán hivatkozunk: 

Munkalapnév!tartomány. ahol a tartomány egyetlen cella vagy tartománynév is lehet.

Másik munkafüzetre utaló külső hivatkozás általános formája: 

[Munkafüzet]Munkalapnév!tartomány.

Az Excel a képletek szerkesztésekor automatikusan bővíti a Szerkesztőlécen kidolgozott képletet, ha húzással vagy kattintással hivatkozást adunk meg. A képletek szerkesztésekor a Szerkesztőlécre automatikusan bekerül, ha egy másik munkalap fülére vagy munkafüzetbe kattintunk.

Adatbázis-kezelés 

A lista a valamilyen logikai csoportosításba vonható – hasonló adatokat tartalmazó – és címkével rendelkező munkalaptartomány, illetve kapcsolt adatokat tartalmazó munkalapsorok sorozata, például számla adatbázis, vagy az ügyfelek nevének és telefonszámainak halmaza. A lista adatbázisként is használható, amelyben a sorok a rekordok és az oszlopok a mezők. A lista első sora tartalmazza az oszlopok feliratait, azaz azonosításukra szolgáló címkéiket.

Adatbázis-kezelő függvények

Az adatbázis-kezelő (D – magyarul AB) munkalapfüggvények három – a munkalaptartományokra vonatkozó – argumentumot tartalmaznak: adatbázist, mezőt és kritériumot. Az AB függvények általános szintaxisa, vagyis az alkalmazandó nyelvi – parancsmegadási szabályok formája: Dfüggvény(adatbázis; mező; kritérium), ahol:

Adatbázis:    Az adatbázist alkotó cellatartomány, amelyet kijelöléssel illetve beírással adjuk meg. Megadhatjuk a folyamatos – üres sorok nélküli – tartományhoz rendelt névvel is. A tartomány sorai az adatbázis rekordjai. Az első rekord fölé írjuk be az oszlopok hivatkozási nevét jelentő mezőfejlécet.

Mező:    A számolásba vont adatoszlop azonosító neve. Az argumentumban szövegként megadva idézőjelek között áll, számként megadva az oszlop (a kijelölt tartományon belüli) sorszámát írjuk be.

Kritérium:    Az adatbázishoz tartozó szűrőfeltételeket megadó cellatartomány. Megadható cellahivatkozásként (például B9:D11), vagy a tartományhoz rendelt névvel, de legalább egy cellahivatkozást kell tartalmaznia. Teljes oszloppal végzett művelethez a kritériumtartományban egy üres sort szúrunk be a mezőnevek alá. Az egyes sorokban szereplő feltételek egymással logikai ÉS, az egymás melletti oszlopokban szereplő feltételek egymással VAGY logikai kapcsolatban vannak, így összetett szűrőfeltételeket is megadhatunk. Az ÉS kapcsolatban minden feltételnek egyszerre kell teljesülnie, míg a VAGY logikai kapcsolatban elegendő az egyik feltételnek.

Az adatbázis feladatok alapadatai

A függvények és számítási módszerek ismertetésénél egy vállalkozásnak az ábrán látható táblázatba bevitt adatbázisára fogunk hivatkozni. Minden rekord egy dolgozó adatait tartalmazza. Az adatbázist példánkban az A1:E8, a kritériumot például a B9:D11 tartomány jelenti.

3. Feladat

Határozzuk meg a programozók átlagjövedelmét!

Megoldás

A mintatáblázat B8:B9 tartományát használjuk kritériumnak. Kattintsunk a C12 cellába, ide kerül az eredmény.Adjuk ki a Képletek szalag Függvény beszúrása parancsát, illetve nyomjuk meg a Shift+F3 billentyűkombinációt vagy kattintsunk a szerkesztőléc Függvény beszúrása ikonjára.Válasszuk ki a Függvény beszúrása párbeszédpanel Adatbázis kategóriáját, majd az AB.ÁTLAG függvényt!Az átlag függvény beszúrása panelről
  1. A Függvényargumentumok párbeszédpanelen vagy a szerkesztőlécen adjuk meg az AB.ÁTLAG függvény paramétereit! Ha az adatokat a párbeszédpanelen adjuk meg, akkor az eredmény már a párbeszédpanelen is megjelenik (lásd az ábrát). 

Az adatbázis-mező átlagát számító függvény argumentumai

4. Feladat

Határozzuk meg a programozók és adminisztrátorok átlagjövedelmét!

Megoldás

Az előző feladat annyiban módosul, hogy a kritériumtartományba egy összetett szűrőfeltételt foglalunk. Most a mintatáblázat B9:B11 tartományát használjuk kritériumnak, ahol a B9 és a B11 cellák logikai ÉS kapcsolatban vannak. Így az eredménycella (C13) képlete: =AB.ÁTLAG(A13:E20;C13;B9:B11), értéke 2738987 Ft.

5. Feladat

Határozzuk meg, hány adminisztrátor és programozó dolgozik a cégnél!

Megoldás

  1. A mintatáblázat B9:B11 tartományát használjuk kritériumnak. Kattintsunk a C14 cellába, ide kerül az eredmény.
  2. Adjuk ki a Képletek szalag Függvény beszúrása parancsát, illetve nyomjuk meg a Shift+F3 billentyűkombinációt vagy kattintsunk a Függvény beillesztése ikonra.
  3. A párbeszédpanelen vagy a szerkesztőlécen adjuk meg az AB.DARAB függvény paramétereit: =AB.DARAB(A1:E8;C1;B9:B11), eredménye 4.

6. Feladat

Határozzuk meg, hány programozó jövedelme nagyobb, mint 2000000 Ft!

Megoldás

  1. A mintatáblázat B9:C10 tartományát használjuk kritériumnak. Kattintsunk a C15 cellába, ide kerül az eredmény.
  2. Írjuk a cellába a következő képletet: =AB.DARAB2(A1:E8;C1;B9:C10) majd nyomjuk meg az Enter billentyűt. A kiértékelés után a cella értéke 2 lesz.

Az AB.DARAB2 függvény megszámolja az adatbázisban a mező argumentummal megadott oszlop azon nem üres celláinak számát, amelyek teljesítik a kritériumot.

Feltételes formázás

A feltételes formázással tartalomtól függően formázhatunk, hogy kiemeljük a veszteséges, nyereséges adatokat tartalmazó, vagy más adattartalmú cellákat. 

A régebbi változatoknál a legfeljebb három feltétel vizsgálatából adódó három különböző forma meglehetősen szerény lehetőség volt. Most azonban jóval több, eddig teljesen ismeretlen lehetőség is megjelent, de valamennyi azt szolgálja, hogy a megadott értékeken vagy képleteken alapulva speciális feltételeknek megfelelő kijelölt cellákat formázzon. Egyébként a feltételes formázás is másolható cellatulajdonság.

Mindez azért fontos, mert a feltételes formázás célja, hogy kiemelje a táblázatokban szereplő adatokban rejlő mondanivalót, lényeget. Ehhez korábban diagramokat kellett készítenünk. A diagramokat természetesen most is használhatjuk, de számos olyan lehetőséghez jutottunk, amelyek akár már a cella értékének bevitelekor figyelmeztetnek a besorolásra, az adott érték elhelyezkedésére saját kategóriáján belül.

Adatsávok beállítása

A kijelölt cellákban ez esetben a cella értékével arányos méretű, beállítható színű sávot jelenít meg a program. A sáv méretét a legkisebb és a legnagyobb értékű cella között egyenesen arányosítva jeleníti meg úgy, hogy a legkisebb értékhez a cellaszélesség körülbelül 10 százalékát, a legnagyobbhoz a cellaszélesség 90 %-át rendeli.

Az adatsávok megjelenhetnek értékkel vagy anélkül

Fontos, hogy ha egy táblázat több oszlopára, tartományára is kiterjesztenénk a formázást, akkor ezeket oszloponként, illetve nagyságrendenként végezzük el, különben a legnagyobb értékek elnyomják a kisebbeket (lásd az ábrán). Tehát a formázás előtt jelöljük ki a formázandó tartományt, utána kattintsunk a Kezdőlap/Stílusok/ Feltételes formázás gombra, majd adjuk ki az Adatsávok parancsot és válasszunk a megjelenő menü hat színéből. A színek kiválasztását és a beállítást itt is segíti, hogy a kijelölés felett átmenetileg megjelenik az éppen az egérmutató alatti minta.

Ha ez a hat szín nincs ínyünkre, vagy éppen el akarjuk rejteni az értéket, illetve más formázási szabályt szeretnénk beállítani, akkor adjuk ki a színminták alatti További szabályok parancsot! Az Új formázási szabály panelen módosíthatunk a megjelenést befolyásoló beállításokon. A jelölőnégyzettel például elrejthetjük a cella értékét. A következő ábrán a kijelölt tartomány első három értékét vörös kitöltéssel is kiemeltük.

A szabályok hatása összeadódik

Színskálák alkalmazása

A színskálákkal az adattartományt egy megadott kezdő és zárószín közötti szín háttérkitöltésként alkalmazásával jelöljük, például az átlag alatti értékeket megnyugtató zöld színnel, az átlag felettieket vörössel, a köztes értékeket semleges színnel emeljük ki. Itt is fontos azonban, hogy csak azonos „mintavételből származó”, vagyis egy táblázat általában egy oszlopában megjelenő értékeket formázzunk egyszerre. 

Színskálák alkalmazása adatsávokkal és adatsávok nélkül

Ugyancsak nem szerencsés, ha az egymás melletti oszlopokban megváltoztatjuk a színskálát, például ha az első oszlopban a minimális értékeket jelöljük zöld színnel, a másodikban a maximálisakat. A színskálák alkalmazásához a Kezdőlap/Stílusok/Feltételes formázás gomb Színskálák almenüjéből választunk mintát. Ha a beépített nyolc színskála nem tetszik, akkor saját mintát alakíthatunk ki az almenü További szabályok parancsával. 

Ikonkészletek használata

A cellában a jobbra igazított érték előtt balra igazítva olyan szimbólumokat jeleníthetünk meg, amelyek jelzik, hogy az adott érték az értéktartomány melyik harmadába, ötödébe esik. 

Az ikonkészletek is megférnek más formázással

Az ikonkészlet formázáshoz a Feltételes formázás gomb Ikonkészletek almenüjéből választjuk ki a megfelelő formát. Választásunkat most is segíti a kijelölt tartományon megjelenő ideiglenes előkép. Itt sem feledkezhetünk meg azonban arról, hogy ne alkalmazzuk mechanikusan, egy lista teljes tartományára, mert nem biztos, hogy a különböző oszlopok azonos minimum és maximum értékekkel rendelkeznek. 

Formázás speciális cellatartalom alapján

A furcsa fejezetcím jelzi, hogy itt olyan lehetőségről lesz szó, amellyel a cella szövege, szám dátum vagy időpont értéke alapján formázunk. Ezzel a módszerrel kiemelhetjük az “egyöntetű szürkeségből” az egyazon időből, (szövegesen jelölt) helyről származó, illetve egy adott határnál kisebb, illetve nagyobb adatokat.

Egyszerűen beállíthatjuk az értékhez illeszkedő formázást is

Ehhez a formázáshoz a Kezdőlap/Stílusok/Feltételes formázás gomb menüjének Cellakijelölési szabályok almenüjét alkalmazzuk. Miután a menüből kiválasztottuk a megfelelő parancsot, választásunktól függő párbeszédpanel jelenik meg, amelyen a formázási feltételt, és magát a formát is megadjuk. 

Formázás rangsor alapján

Már az előző programváltozatban is volt lehetőség az értéktartomány szűrésére egy rangsorban elfoglalt helyezés alapján. Most azonban a Kezdőlap/Stílusok/Feltételes formázás gomb menüjének Legelső/legalsó értékek szabályai almenüjének parancsaival ilyen szűrőfeltételhez is egyedi formázást rendelhetünk. 

Az átlag feletti elemek formázása

Új formázási szabály készítése

Miután láttuk, milyen feltételes formázási lehetőségeink vannak, és azokat hogyan állíthatjuk be, megismertük azt is, hogy minden, feltételes formázási almenü utolsó parancsa a További szabályok parancs, amelynek segítségével az összes, menüből kiválasztható formátum egyetlen párbeszédpanelen állítható be. Ezt a párbeszédpanelt jeleníti meg a Feltételes formázás gomb Új szabály parancsa is.

A képlettel kiértékelhetünk nem munkalapadatokon alapuló feltételeket is. Például a =HÉT.NAPJA(1995-10-12;1)=1 képlet IGAZ értéket ad eredményül, mert a dátum vasárnapra esik. Ilyenkor tehát vizsgálható a cellatartalom IGAZ értéke is (természetesen ennek csak akkor van értelme, ha a képletben változó tartalmú cellahivatkozás van).

A formázási szabályok kezelése

Végül nézzük azt a lehetőséget, amely a legjobban emlékeztet a korábbi verziók formázási feltételeinek meghatározására, bár természetesen most sokkal többet tud. A feltételes formázási szabályok egyetlen párbeszédpanelen is megadhatók, kombinálhatók egymással. A Feltételes formázás gomb Szabályok kezelése parancsával jelenítjük meg a szabálykezelőt, amelyen a Formázási szabályok a következőhöz lista lehetőségei szerint beállíthatjuk a formátumot az aktuális kijelölésre, az aktuális munkalapra, egy tetszőleges másik munkalapra, valamint egy névvel hivatkozott táblázatra.

A párbeszédpanel Új szabály gombjával a korábbiak szerint hozhatunk létre új feltételes formázási szabályt a panel Érvényesség oszlopában megadott tartományhoz. Új érvényességi tartományt csak akkor hozhatunk létre, ha a Szabályok kezelése parancs kiadása előtt kiválasztottuk a formázandó cellákat és a panelen az Aktuális kijelölés elemet választjuk.

Bizonyos szabályok esetén a feltétel teljesülésekor leállíthatjuk a feltételes formázást. Ehhez a szabálykezelő Leállítás, ha igaz oszlopában látható jelölőnégyzetet kell bekapcsolnunk (viszont nem mindig működik – ilyenkor töröljük inkább a szabályt). Mivel a feltételes formázási szabályok is másolható cellatulajdonságok, így ezeket a Kezdőlap/Vágólap Formátummásoló ikonjával tetszőleges másik cellára másolhatjuk.

A szabálykezelő igazi mindenes

7. Feladat

Formázzuk az A1:F10 tartományban a páros számokat félkövér, piros színű karakterrel. 

Az alapadatok és a megoldás

Megoldás

Feltételként A képlet értéke listaelemet választjuk. A MARADÉK függvény segítségével határozzuk meg a páros számokat. Ez minden egyes számot eloszt 2-vel, és IGAZ eredményt ad vissza, ha nincs maradék. Használhatnánk a PÁROS vagy a PÁRATLAN függvényt is. A páratlan számok jelölését nem kell beállítanunk, ha a teljes tartományra vonatkozó, két, egymást kölcsönösen kizáró feltétel szerint akarunk formázni, akkor az egyik feltétel szerint már előzetesen formázhatjuk a teljes tartományt, annak megfelelő részét a feltételes formázás úgy is átalakítja (a másik feltétel szerint).

A képlet értékeFormátum
feltétel:=MARADÉK(A2;2)=0Félkövér, piros betű

Ahhoz, hogy a képlet a tartomány minden egyes celláját kiértékelje elegendő csak a kijelölt tartomány aktív cellájának a cellahivatkozását beírni. Az Excel a többi cella hivatkozását az aktív cellához viszonyítja.

A megoldás lépései tehát:Vigyük be az alapadatokat az A1:F10 tartományba!Kattintsunk az A2 cellába!Adjuk ki a Kezdőlap/Feltételes formázás/Szabályok kezelése parancsot!Kattintsunk az Új szabály gombra!Válasszuk az Új formázási szabály párbeszédpanel A formázandó cellák kijelölése képlettel elemét!A párbeszédpanel alsó részén adjuk meg a képletet (lásd az ábrát)!Kattintsunk a Formátum gombra és állítsuk be a félkövér, vörös betűtípust!A feltétel beállítása
  1. Kattintsunk a Kezdőlap szalag Formátummásoló ikonjára és az A2 cellára beállított tulajdonságot festéssel másoljuk át a teljes A1:F10 tartományra!

Ellenőrző feladat

Válaszoljunk a mellékelt táblázat adatainak felhasználásával, függvények alkalmazásával a következő kérdésekre:

Mennyi az olimpiákon résztvevő sportolók legnagyobb és legkisebb száma, a résztvevők átlaga?

Hány olyan olimpia volt, ahol 3000-nél több sportoló vett részt?

Melyik olimpián, mikor vett rész a legtöbb női sportoló, és mekkora volt ekkor a számuk?

Listázzuk azoknak az olimpiáknak adatait, ahol a női résztvevők száma nagyobb volt, mint a férfi résztvevők számának negyede!

Megoldás

A strukturált hivatkozású képletekhez a táblázatot olimpia néven neveztük el.

  1. A sportolók legnagyobb számát adja vissza az =MAX(C2:C24), illetve strukturált hivatkozással az =MAX(olimpia[Résztvevők]) függvény. Kattintsunk a cellába ahová az eredményt várjuk, írjuk be a képletet a szerkesztőlécbe, majd nyomjuk meg az Enter billentyűt!

A feladat alapadatai

  1. A sportolók legkisebb számát adja vissza az =MIN(C2:C24), illetve strukturált hivatkozással az =MIN(olimpia[Résztvevők]) függvény. 
  2. A résztvevők átlagát az =SZUM(C2:C24)/DARAB(C2:C24) képlettel számoljuk ki. A megfelelő strukturált hivatkozásokat tartalmazó képlet: =SZUM(olimpia[Résztvevők])/DARAB(olimpia[Résztvevők]).
  3. Bár a feladatot többféle módon megoldhatjuk, az egyszerűség kedvéért a 3000-nél több résztvevőjű olimpiák meghatározásához létrehozzuk a H segédoszlopot. Írjuk a H2 cellába a =HA(C2>3000;1;0) képletet! Ez a cella értékét 1-re állítja, ha a résztvevők száma nagyobb volt, mint 3000, és 0-ra, ha kisebb volt 3001-nél. 
  4. Fogjuk meg a cella jobb alsó sarkában látható kitöltőjelet, és húzzuk a H24 celláig!
  5. Számoljuk meg a H oszlop 1-et tartalmazó celláinak számát! Írjuk az eredménycellába a =SZUM(H2:H24) képletet! Ugyanezt az eredményt adja és három lépést helyettesít a 2010-es megoldás: =DARABHATÖBB(olimpia[Résztvevők];”>3000″).
  6. Most előbb a legtöbb női résztvevő számát határozzuk meg a következő képlettel: =MAX(E2:E24), vagy a következő strukturált hivatkozású képlettel: =MAX(olimpia[Nők]). Az eredmény a D33 cellába kerül. Ezt az értéket felhasználjuk a legtöbb női résztvevővel rendelkező év és olimpia kikereséséhez is.

A képletek eredményei

  1. A legtöbb női résztvevővel rendelkező évet a következő képlettel számoljuk: =INDEX(A2:A24;HOL.VAN(MAX(E2:E24);E2:E24);1), pontosabban keressük ki az A2:A24 tartományból. Láthatjuk, hogy az egymásba ágyazott képlet egyszerűsíthető, ha a D33 hivatkozást írjuk az =MAX(E2:E24) paraméter helyére. Ez a (2.) paraméter írja le, hogy melyik sor, a 3. paraméter (E2:E24), hogy melyik oszlop értékét keressük, végül az utolsó paraméter azt adja meg, hogy a sor hányadik cállájának értékét írja a képlet az eredménycellába. Itt az 1, jelenti az első oszlopot, azaz az évet.
  2. A legtöbb női résztvevővel rendelkező olimpiai helyszínt a következő képlettel határozzuk meg:
    =INDEX(B2:B24;HOL.VAN(D33;E2:E24);1). Láthatjuk, hogy az egymásba ágyazott képletet itt egyszerűsítettük a D33 hivatkozással. Itt a 2, jelenti a második oszlopot, azaz a helyszínt. Ugyanezt az eredményt adja a következő strukturált hivatkozású képlet: =INDEX(olimpia[Helyszín];HOL.VAN(D33;olimpia[Nők]);1).
  3. Azon olimpiák kikereséséhez, amelyekben a női résztvevők száma meghaladta a férfiak számának negyedét ismét egy segédoszlopot veszünk fel. Az I2 cellába írjuk a következő képletet: =HA(E2>D2/4;1;0), vagyis ha az adott sorban a női résztvevők száma (E2) nagyobb, mint a férfi résztvevők negyede (D2/4), akkor 1-et, egyébként 0 értéket írjon a cellába. 
  4. Az I2 cella kitöltőjelét húzzuk az I24 celláig. Most már látjuk, hogy az 1976-tól kezdődő olimpiákon haladta meg a hölgyek száma a férfi résztvevők számának negyedét.
  5. Most ezen olimpiák adatainak kiírásához kattintsunk az A36 cellába, majd írjuk be az =HA(E20>D20/4;A20;””) képletet, vagyis, ha a sorban tárolt női résztvevők száma nagyobb a férfi résztvevőkénél, a cella értéke legyen az olimpia éve, egyébként üres marad. 
  6. A fenti képlet kis módosításával írassuk ki a sorban az olimpia többi adatát is, például a helyszínt az =HA(E20>D20/4;B20;””) képlettel stb. Végül jelöljük ki az A37:E37 tartományt, majd a kitöltőjelet húzzuk az E41 celláig!