laikus függvények rendszerezése - kettő
2024-02-13 Excel 2021
átalakító függvények
Nagyon egyszerűen fogalmazva: ezek a függvények számból szöveget, vagy szövegből számot csinálnak. De ne egyszerű adattípus-cserére gondoljunk: eddig balra volt igazítva, most meg majd jobbra lesz, mert akkor „adattípus-cserélők” lenne a nevük!
A „szöveggé alakítók” eredménye egy karakterlánc, amely a feldolgozott numerikus adatot tartalmazza. De, miben is áll ez a feldolgozás? ÉRTÉK.SZÖVEGGÉ: az input számformátumának eltávolítása. SZÖVEG: a „natúr” input számformázása. FIX: az input csonkolása a megadott nagyságrendben és az ezres elválasztó elhagyása, ha kérjük. FORINT: a „natúr” input pénznemmé alakítása. RÓMAI: az input számkaraktereinek római számokra cserélése. T: minden nem szöveg adattípusú cellatartalmat, kivéve a hibaértékeket, nulla hosszúságú szöveggé (””) alakít. Az input szöveget változatlan formában adja vissza.
Az ÉRTÉK és a SZÁMÉRTÉK függvények a szövegként tárolt numerikus inputot natúr, tehát „Általános” formátumú számmá alakítja át. A különbség a két függvény között az, hogy a SZÁMÉRTÉK-ket használva egyéni ezres- és tizedeselválasztó karaktereket deklarálhatunk.
A DÁTUMÉRTÉK és az IDŐÉRTÉK függvények a szövegént (!) tárolt dátum- és időinputok számmá alakítását végzik. Figyelem, figyelem! Tehát a dátumokat, az időpontokat és az időtartamokat az ÉRTÉK és a SZÁMÉRTÉK függvényekkel kell számmá alakítani. De erre nagyon ritkán van szükség.
Az S függvény is számot csinál, ha működik. Eredménye az input típusától függ. Ha az input numerikus adat, akkor az eredmény az input általános számformátumban. Ha IGAZ logikai érték, akkor egy egyes szám. Minden további input esetében, beleértve az üres cella hivatkozását is, de nem beleértve a hibaértékeket: egy nulla. Hibaérték-input esetén a függvény eredménye az input hibaérték. (A függvény nem működik, de az angol megfelelője az N, igen. Természetesen az angol nyelvű Excel-ben.)
Az ARAB függvény funkciója egy római számokat tartalmazó karakterlánc számmá alakítása a római számok arab számokra cserélésével.
hivatkozás-kezelő függvények
A cella azonosítója oszlopának betűjele és sorának indexe. Az „A1”-es rendszerben! De, az „S1O1” rendszerben már sorának indexe és oszlopának indexe. Az index szót itt „sorszám” értelmében használom. A képletben szereplő cella-azonosító a hivatkozás. A hivatkozást lehet deklarálni, de lehet képezni is. A hivatkozás-kezelő függvényekkel.
Az első három információt szolgáltató függvény egy sorszámot ad eredményül: az input cella a munkalap hányadik sorában- (SOR), hányadik oszlopában- (OSZLOP) vagy a munkafüzet hányadik munkalapján (LAP) áll? A sorszámozás felülről lefelé, illetve balról jobbra halad. Az első sorszám az egyes (1). Ha az input cella hiányzik, tehát nem deklaráltunk cella-azonosítót, akkor a függvények a tartalmazó celláról adnak információt.
A SOROK, az OSZLOPOK, a LAPOK és a TERÜLET függvények inputja egy tartomány vagy egy tömb hivatkozása. Ne feledkezzünk meg az ebbe a körbe tartozó háromdimenziós tartományról és a névvel egyesített tartományokról sem! Azt kérdezzük a függvényekkel, hogy hány sor magas- (SOROK), vagy hány oszlop széles (OSZLOPOK) az input tartomány. Valamint azt, hogy hány munkalapot foglal magába a 3D tartomány (LAPOK), illetve hány területet tartalmaz a megadott név, illetve kifejezés (TERÜLET).
A CÍM függvény kakukktojás a „hivatkozást készítők” csoportjában, mert nem hivatkozást ad eredményül, hanem egy szöveg adattípusú cella-azonosítót. Ebből a karakterláncból kell az INDIREKT függvénnyel hivatkozást csinálni. De miért kell például a balra igazított N1-t egy jobbra igazított N1-re cserélni? Mindkettő N1. Az embernek, de nem a programnak! A szöveges N1 a program számára egy adat, a hivatkozás N1 azonban egy utasítás: olvasd ki az N1 cellában álló adatot és azzal végezd el a megadott műveletet!
Az ELTOLÁS függvénnyel nemcsak egyetlen cella, hanem egy egész tartomány hivatkozását is képezhetjük. A függvény eredményeként kapott cella-hivatkozás a bázis cellához képest megadott pozícióban álló cella azonosítója lesz. Amennyiben tartomány-hivatkozást képzünk, akkor ez a cella lesz a függvény eredményében hivatkozott tartomány első cellája. Azt is tudnunk kell az ELTOLÁS függvényről, hogy az eredményét a képlet-környezet határozza meg: vagy (1) a cella, illetve a tartomány azonosítója vagy (2) a cellában álló adat, illetve a tartományban álló adatok tömbje.
A HIPERHIVATKOZÁS függvény kattintható linket hoz létre egy hivatkozásból.
adatbázis-kezelő függvények
De hol van az Excelben adatbázis? Sehol. Csak adatbázis-táblázat van. És a hozzá kapcsolódó szolgáltatások. Amelynek része az adatbázis-kezelő függvény-család.
A szorosan vett családot az AB függvények alkotják, az ÖSSZESÍT és a RÉSZÖSSZEG csak rokonok.
Az AB (AdatBázis) függvények inputja egy adatbázis-táblázat (első argumentum) egyik mezőjének (második argumentum), feltételekkel szelektált (harmadik argumentum) rekordjaiban álló bejegyzései.
Az AB függvényekkel lekérdezhetjük az input szám-bejegyzéseinek darabszámát (AB.DARAB), bejegyzéseinek darabszámát (AB.DARAB2), a bejegyzéseinek szélső értékeit (AB.MIN, AB.MAX) illetve a bejegyzéseinek átlagát (AB.ÁTLAG).
A függvény inputjának összegét az AB.SZUM, szorzatát az AB.SZORZAT függvénnyel képezhetjük. Az AB.MEZŐ a feltételeknek megfelelő egyetlen bejegyzést adja eredményül. Amennyiben nincs ilyen bejegyzés vagy éppenséggel több is van belőle, akkor a függvény hibaüzenetet ad eredményül.
Az AB függvények inputja nem befolyásolható az adatbázis-táblázat szűrésével illetve rekordjainak elrejtésével. Magyarul, a feltételeknek megfelelő minden rekord bejegyzése bekerül a függvény inputjába, akár látszik aktuálisan a munkalapon, akár nem. Van még egy, gyakran előforduló probléma: a feldolgozandó mezőben álló hibaérték blokkolja az AB függvények működését, és ebben az esetben csak a feldolgozást megakasztó hibaértéket kapjuk eredményül. Ezeket a problémákat csak a multifunkciós függvényekkel tudjuk orvosolni.
De mik is ezek a funkciók?
Ahogy látjuk az ÖSSZESÍT függvény multibb :), mint a RÉSZÖSSZEG. De hasonlítsuk össze a két függvény egyéb tulajdonságait is.
Ahogy látjuk az ÖSSZESÍT nem tesz különbséget a szűréssel és a felhasználói művelettel elrejtett sorok között. A függvény csak „rejtett sorok”-at ismer. Sajnos a hibaértékek a RÉSZÖSSZEG függvényt is blokkolják. A részeredményes sorok kihagyásakor a két függvény a másik függvény részeredményes sorait is kihagyja. Az összehasonlításban azért írtam mindenhol „sorok”-at a „rekordok” helyet mert a két függvényt nem csak adatbázis-táblázatok elemzésére használhatjuk!
logikai függvények
A logikai függvények eredménye egy logikai érték. Vagy IGAZ vagy HAMIS.
A „kiértékelő” logikai függvények argumentumai logikai eredményt adó kifejezések. Egyszerűen: logikai kifejezések vagy mondhatjuk azt is, hogy összehasonlítások, idegen szóval relációk. A függvények ezeket a logikai kifejezéseket értékelik ki. A NEM függvény csak egyet, a többiek akár kettőszázötvenötöt.
Az XVAGY-nak ez a meghatározása egzakt, de nem sokat mond a függvény gyakorlati alkalmazhatóságáról! Megpróbálom megmagyarázni. Képzeljük el azt a helyzetet, amelyben két „valami” közt választanunk kell. A választott valami legyen IGAZ! A nem-választott meg legyen HAMIS.
Nem, nem viheti el! És választania is muszáj! Tehát, az XVAGY függvényt akkor használjuk, amikor két feltétel közül az egyiknek, de csak az egyiknek kell teljesülnie. Ha kettőnél több logikai kifejezést adunk meg, akkor az argumentumok egymás után, göngyölítve lesznek kiértékelve…
…és a függvény, akkor ad IGAZ eredményt, ha a göngyölített kiértékelés IGAZ eredménnyel fejeződik be. És ez csak akkor fordulhat elő, ha az argumentumok önálló kiértékelése páratlan számú IGAZ eredményt ad.
A konstansként bevitt, illetve a függvénnyel generált (IGAZ, HAMIS) logikai érték kezelése között nincs különbség. A két függvényre más táblázatkezelő szoftverekkel való kompatibilitás fenntartása miatt van szükség.
tömb-függvények
A tömb összetartozó adatok logikai struktúrája a számítógép memóriájában. A tömbben az adatok képzelt sorokba és képzelt oszlopokba rendezetten állnak. A tömbben tárolt adatok a tömb elemei. A tömb elemeit a sorszámuk (indexük) vagy a tömbben elfoglalt pozíciójuk (hányadik sor, hányadik oszlop metszésében állnak) azonosítjuk. A tömb lehet egydimenziós (vektor), amely egyetlen sorból illetve egyetlen oszlopból áll vagy lehet kétdimenziós (mátrix), amely több sorból és több oszlopból áll.
A tömböt a program generálja az adatfeldolgozás közbenső lépéseként, illetve az output adatok tárolására a folyamat végén. Előbbi a munka-, utóbbi az eredménytömb. Munkatömböt sok függvény használ, de eredménytömböt csak azok, amelyek több adatot adnak eredményül. Ilyenek a tömb-függvények is.
A TRANSZPONÁLÁS függvény eredménye inputjának ellenkező tájolású tömbje. Az input lehet tartomány is, nemcsak tömb. Tájolás alatt a vízszintes-függőleges fogalompárt értem. Másként fogalmazva, az input sorai lesznek az eredménytömb oszlopai, és oszlopai pedig az eredménytömb sorai. Az átalakítás balról jobbra és felülről lefelé halad.
A két rendező függvény eredménye inputjuk rendezett tömbje. A SORBA.RENDEZ egyszerű, a RENDEZÉS.ALAP.SZERINT függvény összetett virtuális rendezésre képes. A SZŰRŐ is inputját tömbösíti, de csak annak szelektált részét. A SORSZÁMLISTA pedig a felhasználó által megadott szerkezetű (sor*oszlop) tömböt tölt fel egy, az argumentumaival meghatározott számtani sorozattal. A feltöltés, a tömb szerkezetétől függetlenül, az első sor feltöltésével kezdődik, balról jobbra haladva, majd a második sor következik balról jobbra…
Ez az öt függvény tehát egy másik műveletnek szolgáltat kiindulási alapot. Az EGYEDI és TÖMB.SZÖVEGGÉ outputja már akár egy megjeleníthető végeredmény is lehet. Az előbbi inputjának ismétlődések nélküli másolatát tömbösíti, utóbbi inputjának elemeit jeleníti meg egyetlen cellában, egymástól vesszővel és szóközzel elválasztva.
A program újabb verziói (2021, 365) már meg is jelenítik az eredménytömb elemeit. (Nem a tömböt, Pistike!) A visszaadott adatok tartományának felépítése (sor*oszlop) azonos az eredménytömb felépítésével, első cellája (a tartomány bal felső sarkában álló cella) a képletet tartalmazó cella.
A képen az EGYEDI függvény output-ját vizsgálhatjuk (C2:C4). Az E4:E6 tartomány képletei megszámlálják, hányszor fordul elő az adott város a listában. A C6-os cellában álló képlet pedig az eredménytömb elemeinek (C2#) darabszámát adja eredményül.
Az eredménytömb elemeinek megjelenítését a program „kibontásnak”, az eredménytömb megjelenített elemeit pedig „kibontott tömb”-nek nevezi. A több eredményt adó képletek, a többi képlettel együtt újraszámolódnak. Ha például, a fenti ábrán látható város-listába beszúrjuk Kisbért, Putnok és Cegléd közé, akkor az az eredménytömb megjelenített elemei között is meg fog jelenni (C5). Ezért a Microsoft a több eredményt adó képletet „dinamikus tömbképlet”-nek nevezi. Na, de ilyen alapon az összes excel-képlet „dinamikus képlet”.
A program csak akkor jeleníti meg az eredménytömb elemeit, ha az eredménytömb szerkezetével megegyező tartomány, a képletet tartalmazó cellától kezdődően, üres.
Ahogy megfigyelhetjük, a C2-es cella hivatkozását tartalmazó képletek tönkrementek. És a C3-asra, valamint a C4-esre hivatkozók is. A C6-ban álló esetében még reménykedhetünk az ÖSSZESÍT vagy a RÉSZÖSSZEG függvényben, de sajnos ez a remény: hiú!
Folytatása következik.
margitfalvi.arpad@gmail.com
