Obsah
Všetky klasické funkcie vyhľadávania a nahrádzania typu VPR (VLOOKUP), GPR (VYHĽADAŤ), VIAC EXPOZOVANÉ (ZÁPAS) a im podobní majú jednu dôležitú vlastnosť – hľadajú od začiatku do konca, teda zľava doprava alebo zhora nadol v zdrojových dátach. Hneď ako sa nájde prvá zhoda, vyhľadávanie sa zastaví a nájde sa len prvý výskyt prvku, ktorý potrebujeme.
Čo robiť, ak potrebujeme nájsť nie prvý, ale posledný výskyt? Napríklad posledná transakcia pre klienta, posledná platba, posledná objednávka atď.?
Metóda 1: Nájdenie posledného riadku pomocou vzorca poľa
Ak pôvodná tabuľka nemá stĺpec s dátumom alebo poradovým číslom riadku (objednávka, platba...), našou úlohou je v skutočnosti nájsť posledný riadok, ktorý spĺňa danú podmienku. To možno vykonať pomocou nasledujúceho vzorca poľa:
Tu:
- Funkcie IF (AK) skontroluje postupne všetky bunky v stĺpci Zákazník a zobrazí číslo riadku, ak obsahuje názov, ktorý potrebujeme. Číslo riadku na hárku nám dáva funkcia LINE (RIADOK), ale keďže potrebujeme číslo riadku v tabuľke, musíme dodatočne odčítať 1, pretože v tabuľke máme hlavičku.
- Potom funkcia MAX (MAX) vyberie maximálnu hodnotu z vytvorenej množiny čísel riadkov, tj číslo posledného riadku klienta.
- Funkcie INDEX (INDEX) vráti obsah bunky s nájdeným posledným číslom z akéhokoľvek iného požadovaného stĺpca tabuľky (Objednávkový kód).
Toto všetko treba zadať ako maticový vzorec, tj:
- V Office 365 s nainštalovanými najnovšími aktualizáciami a podporou dynamických polí môžete jednoducho stlačiť vstúpiť.
- Vo všetkých ostatných verziách budete musieť po zadaní vzorca stlačiť klávesovú skratku ctrl+smena+vstúpiť, ktorý k nemu automaticky pridá zložené zátvorky do riadka vzorcov.
Metóda 2: Spätné vyhľadávanie s novou funkciou LOOKUP
Už som napísal dlhý článok s videom o novej funkcii ZOBRAZIŤ (XLOOKUP), ktorý sa objavil v najnovších verziách balíka Office, aby nahradil starý VLOOKUP (VLOOKUP). Pomocou BROWSE je naša úloha vyriešená celkom elementárne, pretože. pre túto funkciu (na rozdiel od funkcie VLOOKUP) môžete explicitne nastaviť smer vyhľadávania: zhora nadol alebo zdola nahor – zodpovedá za to jej posledný argument (-1):
Metóda 3. Vyhľadajte reťazec s najnovším dátumom
Ak máme v zdrojových údajoch stĺpec s poradovým číslom alebo dátumom, ktorý hrá podobnú úlohu, potom sa úloha upraví – musíme nájsť nie posledný (najnižší) riadok so zhodou, ale riadok s najnovšou ( maximálny) dátum.
Ako to urobiť pomocou klasických funkcií som už podrobne rozobral a teraz skúsme využiť silu nových funkcií dynamického poľa. Pre väčšiu krásu a pohodlie prevádzame aj pôvodný stôl na „inteligentný“ stôl pomocou klávesovej skratky ctrl+T alebo príkazy Domov – Formátovať ako tabuľku (Domov – Formátovať ako tabuľku).
S ich pomocou tento „vrahový pár“ vyrieši náš problém veľmi elegantne:
Tu:
- Najprv funkcia FILTER (FILTER) vyberie len tie riadky z našej tabuľky, ktoré sú v stĺpci Zákazník – meno, ktoré potrebujeme.
- Potom funkcia GRADE (TRIEDENIE) zoradí vybraté riadky podľa dátumu v zostupnom poradí, pričom najnovšia ponuka je navrchu.
- Funkcie INDEX (INDEX) extrahuje prvý riadok, teda vráti posledný obchod, ktorý potrebujeme.
- A nakoniec, externá funkcia FILTER odstráni z výsledkov extra 1. a 3. stĺpec (Objednávkový kód и Zákazník) a ponechá len dátum a sumu. Na tento účel sa používa pole konštánt. {0;1;0;1}, ktorá definuje, ktoré stĺpce chceme (1) alebo nechceme (0) zobraziť.
Metóda 4: Vyhľadanie poslednej zhody v Power Query
No, pre úplnosť sa pozrime na riešenie nášho problému so spätným vyhľadávaním pomocou doplnku Power Query. S jej pomocou sa všetko veľmi rýchlo a krásne vyrieši.
1. Premeňme našu pôvodnú tabuľku na „inteligentnú“ pomocou klávesovej skratky ctrl+T alebo príkazy Domov – Formátovať ako tabuľku (Domov – Formátovať ako tabuľku).
2. Načítajte ho do Power Query pomocou tlačidla Z tabuľky/rozsahu pútko dátum (Údaje – z tabuľky/rozsahu).
3. Zoraďujeme (cez rozbaľovací zoznam filtra v hlavičke) našu tabuľku v zostupnom poradí podľa dátumu, takže najnovšie transakcie sú navrchu.
4… Na karte Premena vybrať tím Skupina podľa (Transformácia — Zoskupiť podľa) a nastavte zoskupenie podľa zákazníkov a ako funkciu agregácie vyberte možnosť Všetky riadky (všetky riadky). Nový stĺpec môžete pomenovať ľubovoľne – napríklad podrobnosti.
Po zoskupení dostaneme zoznam jedinečných mien našich klientov a v stĺpci podrobnosti – tabuľky so všetkými transakciami každého z nich, kde prvý riadok bude posledná transakcia, čo potrebujeme:
5. Pridajte nový vypočítaný stĺpec pomocou tlačidla Vlastný stĺpec pútko Pridať stĺpec (Pridať stĺpec — Pridať vlastný stĺpec)a zadajte nasledujúci vzorec:
Tu podrobnosti – to je stĺpec, z ktorého berieme tabuľky podľa zákazníkov, a 0 {} je číslo riadku, ktorý chceme extrahovať (číslovanie riadkov v Power Query začína od nuly). Dostaneme stĺpec so záznamami (Záznam), kde každá položka je prvý riadok z každej tabuľky:
Zostáva rozbaliť obsah všetkých záznamov tlačidlom s dvojitými šípkami v hlavičke stĺpca Posledná ponuka výber požadovaných stĺpcov:
… a potom odstráňte stĺpec, ktorý už nie je potrebný podrobnosti kliknutím pravým tlačidlom myši na jeho názov - Odstráňte stĺpce (Odstrániť stĺpce).
Po nahratí výsledkov do hárku cez Domov — Zavrieť a načítať — Zavrieť a načítať (Domov — Zavrieť a načítať — Zavrieť a načítať do...) dostaneme takú peknú tabuľku so zoznamom posledných transakcií, ako sme chceli:
Pri zmene zdrojových údajov nesmiete zabudnúť na aktualizáciu výsledkov kliknutím na ne pravým tlačidlom myši – príkaz Aktualizovať a uložiť (Obnoviť) alebo klávesová skratka ctrl+ostatné+F5.
- Funkcia LOOKUP je potomkom funkcie VLOOKUP
- Ako používať nové funkcie dynamického poľa SORT, FILTER a UNIC
- Nájdenie poslednej neprázdnej bunky v riadku alebo stĺpci pomocou funkcie VYHĽADAŤ