tartalom
Minden klasszikus keresési és típushelyettesítő funkció VPR (VLOOKUP), GPR (HLOOKUP), KIÉPÍTETTebb (MÉRKŐZÉS) a hozzájuk hasonlóknak pedig van egy fontos tulajdonságuk – az elejétől a végéig, azaz balról jobbra vagy fentről lefelé keresnek a forrásadatokban. Amint megtaláljuk az első egyezést, a keresés leáll, és csak a szükséges elem első előfordulását találjuk meg.
Mit tegyünk, ha nem az első, hanem az utolsó előfordulást kell megkeresnünk? Például az ügyfél utolsó tranzakciója, az utolsó fizetés, a legutóbbi rendelés stb.?
1. módszer: Az utolsó sor megkeresése tömbképlettel
Ha az eredeti táblában nincs dátummal vagy sorszámmal ellátott oszlop (megrendelés, fizetés…), akkor tulajdonképpen az a feladatunk, hogy megkeressük az adott feltételnek megfelelő utolsó sort. Ezt a következő tömbképlettel lehet megtenni:
Itt:
- Funkció IF (HA) egyenként ellenőrzi az oszlop összes celláját Vevő és megjeleníti a sor számát, ha tartalmazza a szükséges nevet. A lapon lévő sorszámot a függvény adja meg nekünk LINE (SOR), de mivel a táblázatban szükségünk van a sorszámra, ezen felül ki kell vonnunk 1-et, mert van egy fejléc a táblázatban.
- Aztán a függvény MAX (Max) a kialakított sorszámkészletből kiválasztja a maximális értéket, azaz a kliens legutóbbi sorának számát.
- Funkció INDEX (INDEX) visszaadja a cella tartalmát a talált utolsó számmal bármely más szükséges táblázatoszlopból (Rendelési kód).
Mindezt így kell megadni tömbképlet, azaz:
- A legfrissebb frissítésekkel és a dinamikus tömbök támogatásával rendelkező Office 365-ben egyszerűen megnyomhatja a gombot belép.
- Minden más verzióban a képlet beírása után meg kell nyomnia a billentyűparancsot Ctrl+műszak+belép, amely automatikusan göndör kapcsos zárójeleket ad hozzá a képletsorban.
2. módszer: Fordított keresés az új LOOKUP funkcióval
Már írtam egy hosszú cikket egy videóval egy új funkcióról Megtekintés (XLOOKUP), amely az Office legújabb verzióiban jelent meg a régi VLOOKUP helyére (VLOOKUP). A BROWSE segítségével elég elementárisan megoldódik a feladatunk, mert. ennél a függvénynél (a VLOOKUP-tól eltérően) kifejezetten beállíthatja a keresési irányt: felülről lefelé vagy lentről felfelé – az utolsó argumentum (-1) felelős ezért:
3. módszer. Keressen egy karakterláncot a legutóbbi dátummal
Ha a forrásadatokban van egy sorszámú vagy hasonló szerepet betöltő dátumú oszlop, akkor a feladat módosul – nem az utolsó (legalacsonyabb) egyezésű sort kell megkeresni, hanem a legutolsó ( maximum) dátum.
Már részletesen taglaltam, hogyan kell ezt megtenni klasszikus függvényekkel, most pedig próbáljuk meg kihasználni az új dinamikus tömbfüggvények erejét. A nagyobb szépség és kényelem érdekében az eredeti táblázatot egy billentyűparancs segítségével „okos” táblázattá alakítjuk. Ctrl+T vagy parancsokat Kezdőlap – Táblázat formázása (Főoldal — Táblázat formázása).
Segítségükkel ez a „gyilkos házaspár” nagyon kecsesen oldja meg a problémánkat:
Itt:
- Első a funkció FILTER (SZŰRŐ) csak azokat a sorokat választja ki a táblázatunkból, ahol az oszlopban van Vevő – a név, amire szükségünk van.
- Aztán a függvény GRADE (FAJTA) dátum szerint, csökkenő sorrendbe rendezi a kiválasztott sorokat úgy, hogy a legfrissebb ügylet kerül felfelé.
- Funkció INDEX (INDEX) kivonja az első sort, azaz visszaadja az utolsó kereskedést, amelyre szükségünk van.
- És végül a külső FILTER funkció eltávolítja az extra 1. és 3. oszlopot az eredményekből (Rendelési kód и Vevő), és csak a dátum és az összeg marad meg. Ehhez egy konstans tömböt használnak. 0;1;0;1}, meghatározva, hogy mely oszlopokat szeretnénk (1) vagy nem (0) megjeleníteni.
4. módszer: Az utolsó egyezés megkeresése a Power Queryben
Nos, a teljesség kedvéért nézzünk meg egy megoldást fordított keresési problémánkra a Power Query bővítmény segítségével. Segítségével minden nagyon gyorsan és szépen megoldódik.
1. Alakítsuk át az eredeti táblázatunkat „okossá” egy billentyűparancs segítségével Ctrl+T vagy parancsokat Kezdőlap – Táblázat formázása (Főoldal — Táblázat formázása).
2. A gombbal töltse be a Power Querybe Táblázatból/Tartományból lap dátum (Adatok – táblázatból/tartományból).
3. A táblázatot (a fejlécben lévő szűrő legördülő listáján keresztül) dátum szerinti csökkenő sorrendbe rendezzük úgy, hogy a legfrissebb tranzakciók kerüljenek felül.
4… A lapon Átalakítás válassz csapatot Csoportosít (Átalakítás – Csoportosítás) és állítsa be az ügyfelek szerinti csoportosítást, és összesítő funkcióként válassza ki a lehetőséget Minden sor (Minden sor). Bármilyen nevet adhat az új oszlopnak – például Részletek.
Csoportosítás után kapunk egy listát ügyfeleink egyedi neveiről és a rovatban Részletek – táblázatok mindegyikük összes tranzakciójával, ahol az első sor a legutóbbi tranzakció lesz, amire szükségünk van:
5. Új számított oszlop hozzáadása a gombbal Egyéni oszlop lap Oszlop hozzáadása (Oszlop hozzáadása – Egyéni oszlop hozzáadása)és írja be a következő képletet:
Itt Részletek – ebből az oszlopból veszünk táblázatokat vásárlók szerint, ill 0 {} annak a sornak a száma, amelyet ki szeretnénk bontani (a sorszámozás a Power Queryben nulláról indul). Kapunk egy oszlopot rekordokkal (Rekord), ahol minden bejegyzés az egyes táblázatok első sora:
Továbbra is ki kell bontani az összes rekord tartalmát az oszlopfejlécben lévő dupla nyíl gombbal Utolsó üzlet a kívánt oszlopok kiválasztása:
… majd törölje azt az oszlopot, amelyre már nincs szükség Részletek jobb gombbal a címére kattintva – Távolítsa el az oszlopokat (Oszlopok eltávolítása).
Miután feltöltötte az eredményeket a lapra keresztül Kezdőlap — Zárja be és töltse be — Zárja be és töltse be (Főoldal – Bezárás és betöltés – Bezárás és betöltés…) kapunk egy ilyen szép táblázatot a legutóbbi tranzakciók listájával, ahogy szerettük volna:
Amikor módosítja a forrásadatokat, ne felejtse el frissíteni az eredményeket a jobb gombbal kattintva – a parancsot Frissítés és mentés (Frissítés) vagy billentyűparancsot Ctrl+más+F5.
- A LOOKUP függvény a VLOOKUP leszármazottja
- Az új dinamikus tömbfüggvények SORT, FILTER és UNIC használata
- Egy sor vagy oszlop utolsó nem üres cellájának megkeresése a LOOKUP funkcióval