tömbképlet, létrehozás
2020-05-31 Excel 2019
Az alapfogalmak tisztázását követően, ismerkedjünk meg a képletek tömbös kiértékelése szolgáltatás részleteivel. Először két keresés algoritmusát fogjuk tanulmányozni. [első] Egy kutató-csoport tagjai megtippelték egy kísérletük eredményét. A próba elvégzése után a „jegyzőkönyv” alapján kiíratták a legjobb becslést adó nevét.
Az első két lépés tömböt eredményezett és a tömbök első elelemei, a -1,8 és az 1,8 kerültek a képlet-cellába. A tömb elemeit a képletrész kiértékelése szolgáltatással jelenítettem meg.
[második] Dolgozók órabérét kell megállapítanunk két tulajdonságuk alapján. A tulajdonságok összes variációját és a hozzájuk tartozó órabéreket egy segédtáblázat tartalmazza.
A becslések és az órabérek (segédtábla) egy-egy halmaz elemei. A becslés halmaz elemeit a nevük, az órabér halmaz elemeit a státuszuk és a kategóriájuk azonosítja. Ezek a karakteres azonosítók a halmaz-elemek címkéi.
Mindkét algoritmusban tömböt használunk az adat-feldolgozás rész-eredményeinek tárolására. Az első esetben a halmaz elemeivel, a másodikban a címkéikkel végzett műveletek eredményei kerülnek a tömbbe. Így jön létre az abszolút különbségek és az összefűzött tulajdonságok tömbje.
A következő lépés egy tömb-elem, az első feladatban a legkisebb abszolút különbség, a másodikban egy tulajdonság-párossal egyező összefűzött tulajdonság meghatározása.
A kiválasztott elem tömbben elfoglalt pozíciójával azonos pozícióban álló halmaz-elem (órabér), illetve a halmaz-elem címkéje (név) adja a végeredményt.
Ha a halmaz minden elemén elvégzett műveletet halmaz-műveletnek nevezzük, egyszerűen összefoglalhatjuk a tapasztalatainkat: a tömbös adatfeldolgozással a halmaz-műveleteket egyszerűsíthetjük.
Halmazok tömbös feldolgozásához a HA függvényt is igénybe vehetjük, de csak egy speciális megkötéssel: az első argumentumban az ÉS, valamint a VAGY függvényt nem használhatjuk. Akkor, hogyan deklarálhatunk több feltételt? A feltételek zárójelezésével és a csillag (*) valamint a plusz (+) karakterekkel. Előbbivel a logikai ÉS, utóbbival a logikai VAGY viszonyt jelöljük. Kettőnél több feltétel esetén figyelembe kell venni a logikai operátok rangsorát is: először az ÉS, azután a VAGY logikai viszonyban álló relációk értékelődnek ki. Nézzünk egy példát!
Egy vállalat üzletkötőinek bevételeit látjuk. A munkatársak jutalékát bevételük három millió feletti részének pontozásával állapítják meg. Minden fél millió forint egy pontot ér. Hány pontot gyűjtöttek összesen az A státuszú érdi és váci üzletkötők?
Az első lépésben létrehozott tömb HAMIS elemeit a HA függvény harmadik argumentumának hiánya generálja. Bár a példa nem tökéletes, de a lényeget szemlélteti: a HA függvény tömbös kiértékelésű változatával a halmazok feldolgozását is vezérelhetjük feltételekkel.
A három algoritmus mutatja, hogy a „kezeld tömbként” utasítás (Shift+Ctrl+Enter) végrehajtása mindig a rész-eredmények tömbös tárolásával történik.
A fent bemutatott szintaktikával a HOL.VAN függvény tömbös változatában is találkozhatunk. Ezt látjuk a következő feladat megoldásában. Van egy boltunk, amelyben fűrészelt fát árulunk. Raktár-készletünket a képen látható táblázat tartalmazza. „Tessék mondani, van másfél vagy két centiméter vastag, legalább három méter hosszú, tíz centiméter széles deszkájuk? Várjon, azonnal megnézem!”
Talán furcsának tűnhet, hogy az első lépés reláció-láncát a program „simán” kiértékeli. De nincs ebben semmi rendkívüli. Gondoljunk csak az =A2 + 300 és az =A2 > 300 képletekre. Kiértékelés szempontjából, nincs köztük semmi különbség. Csak az egyik aritmetikai, a másik meg logikai művelet. Az más kérdés, hogy a relációt a többség, gondolkodás nélkül beágyazza egy HA függvénybe.
A képletben megadott oszlop-tartományokat a program tömbként kezeli és az azonos sorszámú elemeiket, tehát az egyes sorokat, egy munkamenetben vizsgálja. Ha a relációk kiértékelése IGAZ logikai értéket eredményez, akkor egyes, különben nulla kerül a tömbbe. A második lépésben ezért áll egyes a HOL.VAN függvény első argumentumában. Vigyázat! Az „1”-es helyett nem írhatunk IGAZ-t, mert akkor #HIÁNYZIK-ot kapunk.
Az utolsó algoritmusban a DARABTELI függvénnyel fogunk tömböt képezni. Egy tartomány és egy feltétel a függvény két argumentuma. Hány olyan adat szerepel a tartományban, amellyel a feltételt kiértékelve IGAZ eredményt kapunk. Tömbös formájában a függvény második argumentuma is tartomány, és ennek a tartománynak a celláit fogjuk tömbösen feldolgozni.
A képen látható táblázat alapján össze kell állítani az igénylő települések listáját.
Az igénylők tartománya kilenc települést tartalmaz. Az egyik település sem szerepel a megadott cellában, ezért a képzett tömb kilenc nullát tartalmaz. A DARABTELI függvény első argumentumában álló, piros betűszínnel és félkövéren formázott hivatkozás egy másolással növekvő tartományt határoz meg. Persze itt, az E4-ben még csak egy darab cella, de amikor a képletet az alsó, szomszédos cellába másoljuk…
Most már kiderült, miért hivatkoztunk az E4-es cella képletében, a képletet tartalmazó cella felett álló cellára… Ez egy mesterfogás. Így válik másolhatóvá a képlet. A másolással bővülő tartomány pedig az aktuálisan már kiírt eredeti listaelemeket tartalmazza. Ebben az esetben Dömsödöt. Tehát az első és a nyolcadik igénylő, mert az is Dömsöd, szerepel a megadott tartományban, ezért a tömb első és nyolcadik eleme egyes. Mivel az első nulla a tömbben a második pozícióban áll, ezért a második igénylőt, azaz Vecsést kell a programnak kiírnia az E5-ös cellába. A tanulság kedvéért, még nézzük meg az E6-os cella képletét is.
A táblázat alatt, bal oldalon a DARABTELI függvény első argumentumában álló tartományt, középen a kilenc igénylőt, jobb oldalon a DARABTELI által képzett tömböt látjuk.
Az egyedi elemek számát a képlet másolásakor nem ismerjük, ezért általában a szükségesnél több másolatot hozunk létre. Ha nem akarjuk a sok #HIÁNYZIK feliratot, akkor egészítsük ki a képletet a HAHIBÁS függvénnyel!
Ez tehát, egy lista egyedi elemeinek kiíratására szolgáló, másolandó képlet. A képlet feletti cellát „másolással növekvő tartományként” kell megadni! Például $G$5:G5. Érdekességként elmesélem, hogy erre a módszerre a program fel van készítve. Ha képlet-szerkesztés közben rákattintunk egy cellára, majd begépeljük a kettőspontot az Excel automatikusan megismétli a kettőspont előtt álló cellahivatkozást.
Számos függvényt a program tömbösen értékel ki, de erről csak akkor szerzünk tudomást, ha a tömbös feldolgozás több adatot eredményez. Ebben az esetben a függvény leírása figyelmeztet, a képlet-szerkesztés előtt kijelölendő tartományra. Ilyen például a GYAKORISÁG függvény, amellyel egy számhalmaz összetételét vizsgálhatjuk. Ennél a függvénynél a kategóriák számával megegyező számú cellát kell kijelölni és természetesen a Shift+Ctrl+Enter billentyűparanccsal kell a szerkesztést befejezni.
Összefoglalva, a tömbképlet egy vagy több halmaz tömbös feldolgozására összeállított képlet. A program szóhasználata félrevezető, amikor a tömb szót tartomány megnevezésére használja. A tömb indexelt elemek halmaza a számítógép operatív tárjában, amely az eredmény kiírását követően törlődik.
margitfalvi.arpad@proton.me
