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.
tömbképlet, egy értékhez legközelebb álló elem kiválasztása
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.
tömbképlet, listában keresés összefűzött tulajdonságok alapján
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?
tömbképlet, HA függvény, feltételek láncolása
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!”
tömbképlet, HOL.VAN függvény, több feltételnek megfelelő lista-elem kiválasztása
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.
tömbképlet, egyedi elemek listájának képzése
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…
tömbképlet, egyedi elemek listájának képzése
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.
tömbképlet, egyedi elemek listájának képzése
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!
hibaérték megjelenítésének megakadályozása tömbképlet másolásakor
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.
tömbösen kiértékelt függvények
Ö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@gmail.com