Az Excel függvényeinek magas szintű ismerete és biztos használata alapvető a függvények másik dimenziójának, a tömbfüggvényeknek a használatához - tudjuk, ezek a CTRL+SHIFT+ENTER segítségével előhívható és tömbök kezelésére szolgáló függvények. Ennek tükrében és főleg használatuk nehézkessége miatt nem igazán voltak elterjedtek, de ahogy az elmúlt hetek híreiből kiderült, nem is fognak nagyobb szintű ismertségre szert tenni a jövőben sem, ugyanis már itt vannak az előszobában az Excel beépített, dinamikus tömbfüggvényei. 2018. szeptemberében ugyanis a Microsoft bemutatott hét új függvényt, amelyek dinamikus tömbök kezelésére fognak szolgálni és ezzel feleslegessé teszik a régi CTRL+SHIFT+ENTER-féle tömbfüggvények használatát.

Jelenleg azok számára elérhető ez a hét funkció, akik feliratkoztak az Office 365 Insiders Programjára és bár hivatalos dátumot még nem tett közzé a cég, a közeljövőben minden felhasználó megismerkedhet ezekkel az új lehetőségeket jelentő függvényekkel és a velük együtt érkező új, ún. spill range lehetőségeivel. Hogy miről is van szó, azt a következő példán keresztül mutatom be. Adott a következő tábla, amely néhány várost tartalmaz, de több előfordulással:

Alapesetben, ha azt szerettük volna megmondani, hogy melyek az egyedi értékek, akkor mindenféle függvényes machinációkat alkalmazhattunk vagy akár egy tömbfüggvényt, ahogy én fogom mindjárt tenni a jó öreg INDEX-MATCH baráti páros segítségével.

Azt már ugye betéve tudjuk, hogy az INDEX formulának a segítségével egy tábla megadott sorában, oszlopában vagy sor és oszlop kereszteződésében található értéket kaphatjuk vissza, a MATCH pedig az első paramétereként megadott érték helyzetét fogja a második paramétereként megadott tartományból megadni nekünk (a harmadik paraméter a pontos egyezés, kisebb-nagyobb feltétel megadására ad lehetőséget). Így a következő formula tömbfüggvényként használva segíteni fog nekünk az egyedi értékek meghatározásában:

INDEX($D$21:$D$50,MATCH(0,COUNTIF($F$20:F20,$D$21:$D$50),0))

Az INDEX segítségével megadjuk, hogy a D21:D50-es tartományban keressük az értéket és hogy hányadik sorban van ez az érték (ugye ez az INDEX második, sorra vonatkozó paramétere), azt a MATCH adja meg. De a MATCH sem olyan simán fogja ezt visszaadni, ugyanis a használt tömbhöz beágyazunk egy COUNTIF-et, ami a céltáblánkat fogja vizsgálni soronként és azt nézi meg, hogy beírtuk-e már a céltáblánkba az adott várost, mert ha be, akkor ugyebár a MATCH nem nullát fog találni (hiszen 0-t keresünk) és megy a következő sorra a függvény. Ha viszont nem találja az adott várost, akkor beírja a következő sorba, majd megy tovább a kiinduló tartományunkon és vizsgálja a következő értéket.

Ezt így magyarázatként leírni elég körülményes volt, bár remélhetőleg maga a függvény annyira nem komplikált - a lényeg tehát, hogy erre nyomtunk egy CTRL+SHIFT+ENTER-t és automatikus kitöltés után már látjuk is az eredményünk:

Na viszont, amint elérhető lesz mindenki számára a dinamikus tömbök funkcionalitása, akkor az egész egyetlen könnyű lépésből végrehajtható lesz. Ugyanis szimplán csak beírjuk a funkció nevét (UNIQUE ebben az esetben), majd az eredményeket azonnal látni is fogjuk - tehát azonnal ki is tölti a céltáblát az összes egyedi értékkel.

És ez a funkció még paraméterezhető is, tehát megadhatjuk azt is, hogy csak azokat az értékeket akarjuk visszakapni, amelyek pontosan egyszer fordulnak elő, de akár több oszlopból álló tartományok vizsgálatára is lesz lehetőség. És ez még csak egy, az ígért hét új funkció közül, úgyhogy érdemes a "spill range" fogalmával is megbarátkoznunk gyorsan. A Spill Range az a tartomány, ahova vissza fogja adni a dinamikus tömbfüggvény az eredményt, tehát nincs már több CTRL+SHIFT+ENTER és automatikus kitöltés.

Ha a Spill Rangeben vannak nem üres cellák is, akkor hibaüzenetet kapunk és a funkció lehetőséget ad azon cellák elmozgatására, majd automatikusan folytatja/megcsinálja a kitöltést.

És akkor ismerkedhetünk további új lehetőségekkel is, mint például a spill hivatkozással, amely mondjuk így nézne ki a példánk esetében:

A2#

Ha ezt adjuk meg egy függvényben, akkor az egész spill tartományra fogunk hivatkozni, amelynek első cellája az A2, tehát simán összedolgozhatunk több dinamikus tömbfüggvényt is.

És hogy jelenleg melyik 7 új funkciót tesztelhetik a szerencsések és melyekre várhat az egész Excel-társadalom?

UNIQUE - erről volt szó fentebb
FILTER - a megadott paramétereink mentén szűrhetünk le egy adatbázisban és automatikusan visszakapjuk a szűrés eredményét
RANDARRAY - kitölt nekünk egy tartományt véletlenszerűen generált számokkal (tehát nincs már RANDBETWEEN és automatikus kitöltéssel küszködés)
SINGLE - visszaad egy értéket egy cella sorának/oszlopának kereszteződéséből
SORT - sortolni tudunk egy megadott tartományt
SORTBY - sortolni tudunk egy megadott tartományt egy másik tartomány értékei alapján
SEQUENCE - egymást szekvenciálisan követő számokból tudunk tartományt létrehozni

És ami a legjobb, hogy ezek ugyan új funkcióként jönnek, de a funkcionalitás kiterjesztett a régi funkciókra is, tehát a spill range innentől kezdve napi szinten életünk része lesz. Őszintén mondom, alig várom!