Hivatkozott Excel verzio: Excel 2007 Excel 2010 Excel 2013
Az FKERES (VLOOKUP) és a HOL.VAN (MATCH) függvények egy keresett érték első előfordulását keresik meg egy tartományban. De mit tehetünk, ha nekünk épp az utolsó kell? Van egy korábbi cikk, ami az N-dik előfordulást keresi. Most azonban keressük az utolsót!
Adott a probléma
Mikor volt egy adott rendszámú autó utoljára szervizben?
Megoldás
Elsőként rendezzük a listát dátum szerint növekvő sorrendbe! Ha megvan, hogy melyik rendszámhoz keressük a dátumot, akkor jöhet a gondolkodás.
Egyik lehetőségünk, hogy kigyűjtjük minden előfordulás sorszámát, majd ezekből vesszük a legnagyobbat. Ha ez megvan, akkor az ugyanennyiedik sorból ki tudjuk venni a hozzá tartozó dátumot. Ezt a megoldást csak a videóban mutatom meg.
Másik lehetőségünk egy jó öreg függvény zseniális alkalmazása!
KERES (LOOKUP) függvény
Ez a függvény egy oszlopban megkeres egy értéket, és ha megtalálja, akkor egy másik oszlopból visszaadja az ugyanannyiadik elemet. Tulajdonképpen pont úgy működik, mint a népszerű HOL.VAN/INDEX páros.
Na jó, azért nem teljesen, mert a keresési vektornak, azaz amiben keres, monoton növekvőnek kell lennie. Ha pontos értéket nem talál, akkor a hozzá legközelebb eső, de még kisebb számot tekinti találatnak. Magyarul keresi a legnagyobb kisebbet. Pont úgy, mint az FKERES vagy a HOL.VAN, amikor az utolsó argumentumuk 1-es.
Ebből következően remekül kiváltható az összes kategorizáló típusú megoldás, amit leggyakrabban FKERES-el, rosszabb esetben többszörösen egymásba ágyazott HA függvényekkel szoktak megoldani.
Keres függvény akcióban
Hogy kapjuk meg mindig az utolsót?
Úgy, hogy egy garantáltan nagyobb számot keresünk, mint ami a keresési vektor maximuma.
Nézzük a konkrét példánkat!
A bal oldali táblában vannak a forrás adataink. Elneveztem a két oszlopot a fejléc szerint. A képlet közepén látható, hogy az E3-ban lévő rendszámot összehasonlítjuk a rendszám oszlop minden elemével. Eredményül egy IGAZ/HAMIS tömböt kapunk. Ha ennek vesszük a reciprokát, akkor ahol IGAZ volt, azaz egyezés, ott egyes lesz, ahol nulla volt, ott pedig hibaüzenet a nullával való osztás miatt.
Így most a KERES függvény a kettest keresi az egyesek között. Az előzőekből következően az utolsó egyest fogja találatnak tekinteni, és ezután a Szerviz_datum nevű tartományból kiveszi az ugyanennyiedik dátumot.
Szerintem fantasztikus!!!
Itt a képlet, amit másolhatsz és testre szabhatsz.
=KERES(2;1/(Rendszám=E3);Szerviz_dátum)
Nagyon tetszett, és apró módosítással meg is oldotta a problémámat.
A módosítás annyi volt, hogy a „keresendő értékekből” nekem a mostanit megelőző utolsó előfordulásra volt szükségem. Ezért a keresési tartományt a teljes sor helyett (melyet a szerző elnevezett a példában „Rendszám”-nak, egy S1Oxx:S[-1]Oxx tartományt használtam – és így is ment.
Az én célom az volt, hogy egy ismeretlen értékekből álló függőleges tömböt értékenként, egymástól függetlenül növekvő sorszámmal lássak el. Ehhez kellett a mostani érték megelőző (vagyis az S1Oxx:S[-1]Oxx tartományban utolsó) előfordulásának megkeresése.
Köszönöm!