A napokban kérdezte tőlem valaki ezt a szokás szerint egyszerűnek tűnő kérdést, amelyre persze azonnal meg is volt a kézenfekvő megoldás és a válasz, de utána elgondolkodtam, hogy egy ilyen felvetés megoldható lenne-e formulával és ahogy mindjárt ki fog derülni, a válasz egyértelműen igen.
Szóval a kérdés szerint van egy táblázatunk, amely számokat, szövegeket és üres mezőket is tartalmaz, hogy találhatjuk meg benne mondjuk például az első vagy az utolsó olyan mezőt, amely tartalmaz értéket?
Nem kérdés, hogy ilyen esetekben nem a függvény a legjobb/egyetlen megoldás, egyrészt nem túl gyors és nem is annyira egyszerű, ám ha állandóan ilyen problémával küzdünk és el akarjuk kerülni a folytonos manuális munkát, akkor akár egy formula is segíthet nekünk - persze továbbra is egyszerűen gondolhatunk az üres cellák eltávolítására vagy VLOOKUP-ra is, mégis ha egy állandó, dinamikus megoldásban gondolkodunk, akkor tömbképlethez kell nyúlnunk.
Maguk a felhasznált függvények egyébként nem túlságosan ismeretlenek vagy komplikáltak (javaslom is, hogy a megoldás elolvasása előtt próbálkozzon mindenki maga a megfejtéssel), ISBLANK-re, INDEX-re és MATCH-re van szükségünk.
Első lépésként egy ISBLANK függvényt vetünk be, ami nagyon egyszerűen a paramétereként megadott cellát vagy tömböt vizsgálja meg és TRUE vagy FALSE értéket ad vissza nekünk, ergó az =ISBLANK(A2) megmondja, hogy A2 üres vagy nem üres cella-e.
Mivel most egy tartományról beszélünk, ezért tömbképletünk első része a példában a következő lesz:
=ISBLANK(A2:A14)
Miután tehát már tudjuk, hogy hol vannak üres és nem üres celláink, meg kell keresnünk az első FALSE értéket, hiszen itt fogunk először értéket találni, ezt pedig a már megismert MATCH függvénnyel tehetjük meg. A MATCH az első paramétereként megadott értéket keresi meg a második paramétereként megadott tartományban és egyezés esetén visszaadja eme érték pozícióját. Például =MATCH(TRUE,B2:B14,0) megnézi, hogy hányadik cella a B2:B14-ben az a cella, amelyik az első TRUE értéket tartalmazza.
Ergó ebbe kell beleintegrálnunk azt a tömböt, amit az előbbi ISBLANK formulával hoztunk létre:
=MATCH(FALSE,ISBLANK(A2:A14),0)
Ez vissza fogja nekünk adni annak a sornak a számát az A2:A14 tartományból, amely elsőként tartalmaz FALSE értéket.
Ahogy a képen látszik, már tudjuk is, hogy jelenlegi listánkban az első nem üres cella az első cella, de ebben a jó az, hogy bárhogy módosíthatjuk listánkat, a függvénykombináció által visszaadott pozíció mindig változik. Fontos, hogy tömbképletekről beszélünk, tehát a CTRL+SHIFT+ENTER létfontosságú.
Ha a pozíció megvan, akkor már csak az értékre van szükségünk, amit egy INDEX függvény segítségével szerezhetünk meg, amely az első paramétereként megadott tartományban adja vissza a második paraméterként megadott sorban szereplő értéket, illetve ha megadjuk harmadik paraméterként az oszlopot is, akkor a metszetben lévő érték jön vissza. Tehát az alábbi
=INDEX(A2:A14,1)
formula az A2:A14 tartomány első sorában szereplő értéket adja eredményül. Innen pedig már gyerekjáték a megoldás, egyszerűen olvasszuk össze ezt a három függvényt:
=INDEX(A2:A14,MATCH(FALSE,ISBLANK(A2:A14),0))
CTRL+SHIFT+ENTER lenyomása után pedig máris visszakapjuk a kívánt eredményt. Ha az utolsó olyan mezőt keressük, ami még tartalmaz értéket, akkor kicsit más megközelítésre van szükségünk, de szintén nem földtől elrugaszkodott a megoldás (szintén tömbképlet persze). INDEX függvényt tehát ismerjük fentebből, ismerjük már a MAX függvényt is (a legnagyobb értéket adja vissza egy tartományból) és már korábban volt szó a ROW függvényről is, ez utóbbi a megadott cella sorszámát dobja vissza nekünk, értelemszerűen tartomány esetén a tartomány sorszámait kapjuk.
Ergó megnézzük, hogy A:A oszlopunkban mely cellák tartalmaznak értéket, ehhez hozzávesszük szépen ezen cellák sorszámait a ROW függvénnyel és a sorszámok közül kiválasztjuk a legnagyobbat, majd az INDEX segítségével visszaadjuk a kívánt értéket az A:A tartományból egy CTRL+SHIFT+ENTER kíséretében valahogy így:
=INDEX(A:A,MAX((A:A<>"")*(ROW(A:A))))
De tudom, hogy erre a kérdésre is rengeteg megoldás létezik, ha valakinek van kedve, ossza meg a saját verzióját - és persze ismételten lássuk be, hogy ez valójában csak játék, mert ezeket a problémákat az esetek legkisebb százalékában oldjuk meg ilyen tömbképletekkel.
