Obsah
Všechny klasické funkce vyhledávání a nahrazování typů VPR (VYHLEDAT), GPR (VYHLEDAT), VÍCE EXPOZOVANĚ (ZÁPAS) a jim podobní mají jednu důležitou vlastnost – hledají od začátku do konce, tedy zleva doprava nebo shora dolů ve zdrojových datech. Jakmile je nalezena první odpovídající shoda, vyhledávání se zastaví a je nalezen pouze první výskyt prvku, který potřebujeme.
Co dělat, když potřebujeme najít ne první, ale poslední výskyt? Například poslední transakce pro klienta, poslední platba, poslední objednávka atd.?
Metoda 1: Nalezení posledního řádku pomocí maticového vzorce
Pokud původní tabulka nemá sloupec s datem nebo pořadovým číslem řádku (objednávka, platba…), pak je naším úkolem ve skutečnosti najít poslední řádek, který splňuje danou podmínku. To lze provést pomocí následujícího maticového vzorce:
Zde:
- funkce IF (LI) zkontroluje postupně všechny buňky ve sloupci Zákazník a zobrazí číslo řádku, pokud obsahuje název, který potřebujeme. Číslo řádku na listu nám dává funkce LINE (ŘÁDEK), ale protože potřebujeme číslo řádku v tabulce, musíme dodatečně odečíst 1, protože máme v tabulce záhlaví.
- Pak funkce MAX (MAX) vybere maximální hodnotu z vytvořené sady čísel řádků, tj. číslo posledního řádku klienta.
- funkce INDEX (INDEX) vrátí obsah buňky s nalezeným posledním číslem z jakéhokoli jiného požadovaného sloupce tabulky (Objednací kód).
To vše musí být zadáno jako maticový vzorec, tj:
- V Office 365 s nainstalovanými nejnovějšími aktualizacemi a podporou dynamických polí stačí stisknout vstoupit.
- Ve všech ostatních verzích budete muset po zadání vzorce stisknout klávesovou zkratku Ctrl+směna+vstoupit, který k němu automaticky přidá složené závorky do řádku vzorců.
Metoda 2: Zpětné vyhledávání s novou funkcí LOOKUP
Už jsem napsal dlouhý článek s videem o nové funkci NÁHLED (XLOOKUP), který se objevil v nejnovějších verzích Office, aby nahradil starý VLOOKUP (VYHLEDAT). S pomocí BROWSE je náš úkol vyřešen zcela elementárně, protože. pro tuto funkci (na rozdíl od funkce VLOOKUP) můžete explicitně nastavit směr vyhledávání: shora dolů nebo zdola nahoru – za to odpovídá její poslední argument (-1):
Metoda 3. Vyhledejte řetězec s nejnovějším datem
Pokud máme ve zdrojových datech sloupec s pořadovým číslem nebo datem, které hraje podobnou roli, pak je úkol upraven – potřebujeme najít nikoli poslední (nejnižší) řádek se shodou, ale řádek s nejnovější ( maximální) datum.
Jak to udělat pomocí klasických funkcí jsem již podrobně rozebral a nyní zkusme využít sílu nových funkcí dynamického pole. Pro větší krásu a pohodlí také převedeme původní stůl na „chytrý“ stůl pomocí klávesové zkratky Ctrl+T nebo příkazy Domů – Formát jako tabulka (Domů — Formátovat jako tabulku).
S jejich pomocí tento „zabijácký pár“ řeší náš problém velmi elegantně:
Zde:
- Nejprve funkce FILTER (FILTR) vybere pouze ty řádky z naší tabulky, které jsou ve sloupci Zákazník – jméno, které potřebujeme.
- Pak funkce GRADE (ŘADIT) seřadí vybrané řádky podle data v sestupném pořadí, přičemž nejnovější nabídka je nahoře.
- funkce INDEX (INDEX) extrahuje první řádek, tj. vrátí poslední obchod, který potřebujeme.
- A nakonec externí funkce FILTER odstraní z výsledků přebytečný 1. a 3. sloupec (Objednací kód и Zákazník) a ponechá pouze datum a částku. K tomu se používá pole konstant. {0;1;0;1}, definující, které sloupce chceme (1) nebo nechceme (0) zobrazovat.
Metoda 4: Vyhledání poslední shody v Power Query
No, pro úplnost se podívejme na řešení našeho problému se zpětným vyhledáváním pomocí doplňku Power Query. S její pomocí se vše velmi rychle a krásně vyřeší.
1. Převedeme naši původní tabulku na „chytrou“ pomocí klávesové zkratky Ctrl+T nebo příkazy Domů – Formát jako tabulka (Domů — Formátovat jako tabulku).
2. Načtěte jej do Power Query pomocí tlačítka Z tabulky/rozsahu Karta Data (Data – z tabulky/rozsahu).
3. Seřadíme (přes rozevírací seznam filtru v záhlaví) naši tabulku v sestupném pořadí podle data, takže nejnovější transakce jsou nahoře.
4… V záložce Proměna vybrat tým Skupina vytvořená (Transformace — Seskupit podle) a nastavte seskupení podle zákazníků a jako agregační funkci vyberte možnost Všechny řádky (Všechny řádky). Nový sloupec můžete pojmenovat libovolně – například Detaily.
Po seskupení získáme seznam unikátních jmen našich klientů a ve sloupci Detaily – tabulky se všemi transakcemi každé z nich, kde první řádek bude poslední transakce, což je to, co potřebujeme:
5. Přidat nový počítaný sloupec tlačítkem Vlastní sloupec Karta Přidat sloupec (Přidat sloupec — Přidat vlastní sloupec)a zadejte následující vzorec:
Zde Detaily – to je sloupec, ze kterého bereme tabulky podle zákazníků, a 0 {} je číslo řádku, který chceme extrahovat (číslování řádků v Power Query začíná od nuly). Získáme sloupec se záznamy (Záznam), kde každá položka je první řádek z každé tabulky:
Zbývá rozbalit obsah všech záznamů tlačítkem s dvojitými šipkami v záhlaví sloupce Poslední nabídka výběr požadovaných sloupců:
… a poté odstraňte sloupec, který již není potřeba Detaily kliknutím pravým tlačítkem myši na jeho název – Odeberte sloupce (Odstranit sloupce).
Po nahrání výsledků na list skrz Domů — Zavřít a načíst — Zavřít a načíst (Domů — Zavřít a načíst — Zavřít a načíst do…) dostaneme takovou pěknou tabulku se seznamem posledních transakcí, jak jsme chtěli:
Při změně zdrojových dat nesmíte zapomenout aktualizovat výsledky kliknutím na ně pravým tlačítkem – příkazem Aktualizovat a uložit (Obnovit) nebo klávesovou zkratkou Ctrl+Další+F5.
- Funkce VYHLEDAT je potomkem funkce VLOOKUP
- Jak používat nové funkce dynamického pole SORT, FILTER a UNIC
- Nalezení poslední neprázdné buňky v řádku nebo sloupci pomocí funkce VYHLEDAT