Telefonszámunk: 1-472-0679

Utolsó előfordulás keresése

2016-02-22 - horvimi - Kategória: Tömbképletek
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

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.

keres-fv-argumentumai

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

KERES-vf-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!

rendszam-utolso-elofordulas

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.

talalatok

Í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)

Letölthető munkafüzet gyakorláshoz

utolso_elofordulas_keresese

A videóban lekövetheted mindkét megoldás működését, összeállítását.

1 Comment
  1. 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!

Vélemény, hozzászólás?