Elágazás, feltételes összegzés

Tartalomjegyzék:

Másodfokú egyenlet
Tervezés alatt álló épület költségterve
Adatok szűrése, rendezése

Bevezető

Elágazás

Képlet:

Eredmény:

Bizonyos esetekben valamilyen feltételhez kötjük az elvégzendő műveletet. Az IF() (HA()) függvény segítségével tudunk vizsgálni egy feltételt és meg tudjuk határozni, hogy mi történjen, ha igaz és mi történjen, ha hamis a feltétel. A függvény használata:

IF(feltétel; érték_ha_igaz; érték_ha_hamis)
feltétel:: ide olyan képletet kell írni, aminek az értéke igaz vagy hamis lehet. Lehet egy cellára hivatkozni (pl. B3), vagy valamilyen összehasonlítást használni (pl. B3>2 vagy B3=2).
érték_ha_igaz:: itt kell megadnunk, hogy ha a fenti feltétel eredménye "igaz", akkor milyen műveletet végezzen el a program. Kerülhet ide egy függvény (pl. sum(B2:B5)) vagy szöveg, szám.
érték_ha_hamis:: itt kell megadnunk, hogy ha a fenti feltétel eredménye "hamis", akkor milyen műveletet végezzen el a program.

TIPP: Az Excel a számokat is tudja értelmezni igaz/hamis értékként. Ha egy szám nullától különböző, akkor "igaz", ha nulla, akkor "hamis".

Feltételes aggregáció

Az IF() függvény szabadon kombinálható a többi függvénnyel ezáltal a legtöbb elágazást igénylő feladat megoldható vele. Vannak viszont olyan speciális esetek, amelyekre olyan gyakran szükségünk lehet, hogy külön Excel függvény is létezik hozzájuk. Ezek a feltételes aggregációs függvények, például a COUNTIF() (DARABTELI()) függvény. A függvény megszámolja, hogy hány olyan cella van a kiválasztott tartományban, amelyre igaz a feltétel. Használata:

COUNTIF(tartomány; feltétel)
tartomány:: azon cellák, amelyekben ellenőrizzük, hogy teljesül-e a feltétel
feltétel:: ha egyenlőséget vizsgálunk, akkor csak az adatot írjuk be (pl. A2), összetett vizsgálatnál idézőjelek közé kell tennünk a feltételt (pl. ">0" vagy ">"&A2)

További feltételes aggregációs függvények:

SUMIF() (SZUMHA()): összegzi a tartomány azon celláinak tartalmát, amelyeknél teljesül a feltétel
AVERAGEIF() (ÁTLAGHA()): átlagolja a tartomány azon celláinak tartalmát, amelyeknél teljesül a feltétel

Ezek a függvények kicsit bonyolultabbak, mert lehetőséget adnak arra, hogy a vizsgált és az összegzett/átlagolt tartomány különböző legyen. A SUMIF() függvény használata:

SUMIF(vizsgált_tartomány; feltétel; [összegzési_tartomány])
tartomány:: azon cellák, amelyekben ellenőrizzük, hogy teljesül-e a feltétel
feltétel:: ha egyenlőséget vizsgálunk, akkor csak az adatot írjuk be (pl. A2), összetett vizsgálatnál idézőjelek közé kell tennünk a feltételt (pl. ">0" vagy ">"&A2)
összegzési tartomány:: azon cellák, amelyek tartalmát össze szeretnénk adni, ha a vizsgált_tartomány adott sorában a feltétel igaz

TIPP: Ha a függvény egyik bemeneti paramétere szögletes zárójelben van, akkor azt nem kötelező megadni.

Tartomány elnevezése

Bonyolult képletek esetén átláthatóbb a megoldás, ha nem az alapértelmezett cellaneveket használjuk, hanem egyedi neveket adunk a tartományoknak. Elnevezhetünk egy darab cellát (pl. "A1" helyett "hossz"-ra), vagy egy tartományt is (pl. "B2:B10" helyett "darab"). Az elnevevezéshez a név mezőben át kell írnunk a cella nevét a választott névre. A nevek törlése/módosítása a Formulas / Name manager menüben lehetséges.

TIPP: Ha egyedi névvel hivatkozunk egy tartományra/cellára, akkor az Excel azt automatikusan abszolút hivatkozásként kezeli.

Teljes oszlopot is elnevezhetünk. Ekkor az oszlopfejlécre kattintva ki kell jelölni a teljes oszlopot és a név mezőben átírni a nevét (pl. "A:A" helyett "ertek"-re). Amennyiben ezután olyan képletet szeretnénk használni, amely az A oszlop adott sorában lévő elemre hivatkozik, akkor az oszlop neve elé egy @ jelet kell tenni. Így az @ertek a B2 cellában az A2 cellát jelenti, a B3 cellában az A3-at és így tovább.

Másodfokú egyenlet

Feladat: az ax² + bx + c = 0 másodfokú egyenlet megoldásait írjuk ki az a, b, c paraméterek függvényében. Ha nincs megoldás a valós számok halmazán, akkor írja ki a program, hogy "a diszkrimináns negatív"!

Az ax² + bx + c = 0 másodfokú egyenlet megoldóképlete:
, a diszkrimináns pedig a gyök alatti rész. Ha a diszkrimináns negatív, akkor nincs megoldása az egyenletnek a valós számok halmazán.

Először hozzuk létre a paramétereket és nevezzük is el a cellákat, hogy a képletekben egyszerűen hivatkozhassunk rájuk.

Az A1, A2, A3 cellákba írjuk be rendre, hogy "a", "b", "c".
Ezután a B1, B2, B3 cellákba írjunk be tetszőleges számokat.
Nevezzük el a B1, B2, B3 cellákat rendre a, b, cc néven. (A c név már sajnos alapértelmezetten foglalt.)

Számoljuk ki a diszkriminánst és vizsgáljuk, hogy negatív-e.

Írjuk be az A5 cellába, hogy "D".
Számítsuk ki a B5 cellában a diszkrimináns értékét, használjuk az egyedi neveket:
=b^2-4*a*cc
Nevezzük el a B5 cellát D-nek.
Vizsgáljuk a C6 cellában, hogy a diszkrimináns negatív-e:
=IF(D>=0; "nemnegatív"; "negatív")

A megoldóképletet csak akkor használjuk, ha a diszkrimináns nemnegatív, egyébként pedig írjuk ki, hogy "negatív diszkrimináns". Az IF() függvény használatával elkerülhetjük, hogy az Excel megpróbáljon negatív számból gyököt vonni. Először megvizsgáljuk, hogy a diszkrimináns nemnegatív-e és csak akkor helyettesítünk be a megoldóképletbe, ha a vizsgálat eredménye "igaz".

Írjuk be az A6, A7 cellákba, hogy "x1", "x2".
A B6 cellába írjuk be a megoldóképletet:
=IF(D>=0;(-b+sqrt(D))/(2*a);"negatív diszkrimináns")
A B7 cellába írjuk be a megoldóképletet:
=IF(D>=0;(-b-sqrt(D))/(2*a);"negatív diszkrimináns")

TIPP: Ha azt szeretnénk, hogy egyáltalán ne kerüljön semmi a cellába, akkor a "negatív diszkrimináns" helyett írjuk az alábbit "".

Tervezés alatt álló épület költségterve

Feladat: egy nagy épület éppen tervezés alatt áll. Sajnos a tervről még nem sokat lehet tudni, még a belmagasság sincs megadva, azt viszont tudjuk, hogy téglalap alakúak a helyiségek. Ha véglegesednek a tervek, akkor Ön egy .csv fájlban fogja megkapni a helyiségek adatait (helyiségnév, hossz, szélesség, padlóburkolat). Ekkor nagyon rövid idő alatt válaszolnia kell majd az alábbi kérdésekre:
- hány m² kell az egyes burkolattípusokból?
- összesen mennyibe kerülnek az egyes burkolatok fajtánként?
- az egyes burkolatok hány helyiségben szerepelnek?
- hány m²-t kell lefesteni?
Készítsen el úgy egy táblázatot, hogy amikor megkapja az adatokat és kiderül, hogy pontosan mely burkolatok és festék lesz betervezve (és ez esetleg később még megváltozik), akkor ezeket gyorsan be tudja írni és a kért adatokat kiszámolni.

A .csv fájlt könnyen be tudjuk olvasni az Excellel. Ha az ábra szerinti formátumban érkeznek az adatok, akkor minden sorhoz egy helyiség tartozik és négy oszlopunk lesz: helyiségnév, hossz, szélesség, padlóburkolat. Először készítsük el ezeknek az adatoknak a helyét és írjunk be tetszőleges adatokat, hogy legyen mivel dolgozni.

Az A1:D1 cellákba írja be a fejléceket és írjon be 2-3 sornyi adatot.
Nevezze el a B oszlopot "hossz" néven, a C oszlopot "szelesseg", a D oszlopot "padlo" néven.

A padlóburkolathoz az alapterületet kell kiszámolnunk, a festékhez pedig a falfelületet + a mennyezet felületét. Ezeknek hozzunk létre külön oszlopokat az adatok mellett.

Az E1, F1 cellákba írja be az "alapterület", "festendő felület" kifejezéseket.
Nevezzük el az E oszlopot "alapterulet" néven.

TIPP: Ha nem sikerült az elnevezés, mert véletlenül rossz tartományt neveztünk el, akkor a Formulas / Name manager menüpontban tudjuk kitörölni az elrontott nevet.

A festendő felület kiszámításához szükségünk lesz majd a belmagasságra is. Ezt még nem tudjuk, hozzunk létre egy cellát, amibe majd beírhatjuk.

A H1 cellába írjuk, hogy "belmagasság".
Az I1 cellába írjunk be egy tetszőleges számot (pl. 2,65) és nevezzük el a cellát "bm" néven.

Számítsuk ki az alapterületet és a festendő felületet. Figyeljünk az elnevezett tartományokra való hivatkozásnál, hogy az oszlophivatkozásoknál mindig az adott sorban lévő elemre lesz szükségünk, ezért "@"" jelet kell írni a név elé. A belmagasságra való hivatkozásnál viszont nincs szükség "@" jelre.

Az E2 cellában számítsuk ki a hossz és a szélesség segítségével az alapterületet:
=@hossz*@szelesseg
A festendő felület alapterület + kerület*belmagasság. Az F2 cellában számítsuk ki ezt:
=(@hossz+@szelesseg)*2*bm + @alapterulet

Ezekből az adatokból már könnyen ki tudjuk számolni a kért értékeket.

A H2 cellába írjuk, hogy "festendő felület", az I2 cellában pedig számítsa ki az értékét:
=sum(F:F)
Alatta készítsünk kis táblázatot az ábra szerint.
Az I6, I7 cellákba írjunk be tetszőleges egységárakat (még nem tudjuk a pontos értékeket).
Számítsuk ki az első burkolatnál a költséget (egységár * összterület)! Az összterülethez feltételes összegzést használunk, mivel csak azoknak a helyiségeknek a területét szeretnénk összegezni, ahol az adott padlóburkolat szerepel:
=I6*SUMIF(padlo;H6;alapterulet)
Számítsuk ki az első burkolatnál, hogy hány helyiségben szerepel!
=COUNTIF(padlo;H6)
Lefelé húzással másoljuk a képleteket.

Adatok szűrése, rendezése

Feladat: az előző feladatban tárgyalt épület terve elkészült, az adatokat a helyiseglista.csv fájlban kaptuk meg. A belmagasság 3m, az árakat nekünk kell megbecsülni. A táblázatot egyeztetésre kell vinnünk, ahol gyorsan kell tudnunk válaszolni olyan kérdésekre, hogy:
- Melyik helyiség a legnagyobb?
- Mutasson egy listát a parkettával burkolt helyiségekről!
- Mutasson egy listát a 30m²-nél nagyobb helyiségekről!
- Nézzük végig a helyiségeket padlóburkolatonként, alapterület szerint növekvő sorrendben! Készüljön fel az egyeztetésre. Olvassa be az előző feladatban elkészített táblázatba az adatokat, majd használjon szűrőt és végezze el a fenti lekérdezéseket.

Először töröljük ki a random beírt adatainkat és töltsük fel a helyes értékekkel.

A File/Import menüpontban válasszuk a CSV lehetőséget és importáljuk új fülre a csv filet. A beállításoknál jelöljük, hogy pontosvesszővel tagoltak az adatok.
Másoljuk át az adatokat a már előkészített táblázat megfelelő celláiba.
Javítsuk a belmagasság értékét 3m-re.

A burkolatokról szóló, jobboldali kis táblázatokban a burkolatok neveit javítani kell. Kapcsoljuk be a szűrőt, hogy meg tudjuk nézni hány különféle burkolat van.

Kattintsunk bele az adatokat tartalmazó táblázatba (pl. B3 cella).
A Home / Sort&Filter menüben kapcsoljuk be a szűrőt. Ekkor az adatokat tartalmazó táblázat fejlécében a nevek mellett megjelennek kis nyilak.
Kattintsunk a padlóburkolatokat tartalmazó oszlop kis nyilára és nézzük meg a beállítási lehetőségeket. Így látjuk az összes különböző padlóburkolatot.
Javítsuk a lista alapján a padlóburkolatok kimutatást: töröljük ki a régi burkolatokat és írjuk be a helyes értékeket.
Keressünk rá az interneten az egyes burkolatok árára, válasszunk egyet és írjuk be a táblázatba az árat

Megoldását összevetheti a megoldókulccsal.

A szűrő használatával válaszoljunk a fenti kérdésekre.

A legnagyobb helyiség megtalálásához rendezzük a helyiségeket alapterület szerint csökkenő sorrendbe az alapterület oszlopfejléc szűrőjével.
Ha csak azokat a helyiségeket szeretnénk látni, amelyekben parketta van, akkor a padlóburkolatok szűrőjénél ki kell venni a pipát a többi burkolat mellől.
A 30m²-nél nagyobb alapterületű helyiségekhez az alapterület szűrőjénél a legördülő menüből a "Greater than"-t kell választani és a mellette lévő mezőbe a 30-at írni.
Vegyünk ki minden szűrőt, majd rendezzük először alapterület szerint, majd pedig padlóburkolat szerint a táblázatot a szűrő segítségével.

A szűrő helyett feltételes formázást is használhatunk a második és a harmadik kérdéshez:

Jelöljük ki a padlóburkolatok listáját, majd pedig adjuk meg, hogy csak a "parketta" szót tartalmazó cellák legyenek pirosak.
Jelöljük ki az alapterületek listáját, majd pedig adjuk meg, hogy csak a 30m²-nél nagyobb alapterületű helyiségek legyenek zöldek.

Megjegyzés: Bonyolultabb táblázatok, többszáz helyiséges épületek esetén a fenti megoldás lassú, sok hibázási lehetőségeket tartalmaz. A dinamikus képletek segítségével lehetőség van még hatékonyabban megoldani hasonló feladatokat. A dinamikus képletek a Digitális ábrázolás c. tárgy anyagában szerepelnek.

^{Bevezetés az alkalmazott informatikába} / _BMEEPAG0203

Elágazás, feltételes összegzés, cellák elnevezése, rendezés, szűrő