Potřebujete v Excelu dohledat poslední fakturu zákazníka, poslední objednávku produktu nebo jednoduše poslední výskyt určité hodnoty v seznamu? Pokud máte nejnovější Excel, pravděpodobně sáhnete po funkci XLOOKUP, která jako jediná vyhledávací funkce umí vyhledávat i od konce tabulky. Co ale dělat, pokud používáte Excel 2016, 2019 nebo jinou verzi, kde XLOOKUP není k dispozici?
V tomto videu si ukážeme dva způsoby, jak vyhledávat poslední položku, bez funkce XLOOKUP. Zvláště zajímavý je druhý způsob. Jedná se o jednoduchý trik s jednou z nejstarších funkcí v Excelu. Dejte mi vědět v komentáři pod videem, zda jste tento trik znali.
Pojďme si tedy ukázat, jak vyhledávat od konce tabulky bez funkce XLOOKUP.
Excelový soubor ke stažení:
V příkladu máme tabulku faktur a hodnot. K zadané faktuře potřebujeme dohledat hodnotu z tabulky, ale zajímá nás hodnota poslední faktury. Předpokladem je, že máte tabulku seřazenou dle datumů nebo jakéhokoli kritéria, podle kterého určujete poslední fakturu. Naše tabulka je seřazená podle datumů, to znamená, že úplně dole máme nejnovější faktury, jejichž hodnota nás zajímá.
Takže teď se můžeme pustit do vyhledávání.
Řešení s funkcí XLOOKUP
Nejprve si rychle ukážeme, jak bychom příklad vyřešili s funkcí XLOOKUP. Funkce XLOOKUP je totiž jediná vyhledávací funkce, která umí přepínat mezi vyhledáváním první a poslední položky v seznamu.
Napíšeme funkci XLOOKUP. Ve funkci XLOOKUP nejprve označíme, co hledáme, následně kde to hledáme a co chceme vrátit a pro najití poslední položky vyplníme i poslední parametr -1. Tedy že chceme, aby funkce XLOOKUP vyhledávala od konce tabulky. Funkce XLOOKUP jako jediná vyhledávací funkce nabízí tuto možnost.
Ale co když tuto funkci nemáte?
Řešení s funkcí AGGREGATE
První možností je použít funkci AGGREGATE. Funkce AGGREGATE má tu výhodu, že pro její potvrzení nepotřebujete kombinaci kláves CSE. Víme, že pro vyhledání poslední položky použijeme funkci AGGREGATE, ve které máme možnost ignorovat chybové hlášky. Takže musíme docílit toho, že ve funkci AGGREGATE rozpoznáme řádky, na kterých se vyskytuje hledaná faktura a na ostatních řádcích budou chybové hlášky. To uděláme tak, že ověříme podmínku. Označíme sloupec s fakturami a porovnáme, zda se položka na řádku rovná hledané faktuře. Tuto funkci nebudeme nikam přetahovat, takže buňky nemusíme fixovat.
Tato podmínka vrátí sérii pravd a nepravd. Pravda se vrátí na řádku, kde je hledaná faktura a na ostatních řádcích se vrátí nepravda. Na řádky, kde máme nepravdu, potřebujeme dostat chybové hlášky.
Nejjednodušší způsob, jak to udělat je, vydělit tuto podmínku jedničkou. Pravda a nepravda se v Excelu totiž překládá na jedničky a nuly. Jednička pro pravdu a nula pro nepravdu. A když celou tuto podmínku vydělíme jedničkou, tak jedna děleno jednou je pořád jedna, ale jednička vydělená nulou vrátí v Excelu chybovou hlášku. A to je přesně to, co potřebujeme.
Základ máme. Teď potřebujeme k řádkům, na kterých máme hledaný produkt přidat pořadová čísla řádků z tabulky. To znamená, že u prvního výskytu faktury potřebujeme pořadový řádek z tabulky, to samé u druhého atd. Na to použijeme trik s určením pořadových čísel řádků pomocí dvou funkcí ŘÁDEK.
Vynásobíme tuto podmínku funkcí ŘÁDEK, kde označíme sloupec z tabulky. Opět, jelikož funkci neplánujeme nikam přetahovat, tak buňky nemusíme fixovat. A od této funkce ŘÁDEK odečteme druhou funkci ŘÁDEK, kde označíme buňku záhlaví. Tento trik vrátí pořadová čísla řádků z tabulky. Když se na funkci opět podíváme, tak zjistíme, že první hledaná faktura se vyskytuje na prvním řádku a poslední faktura se vyskytuje na patnáctém řádku.
Teď to konečně můžeme zabalit do funkce AGGREGATE. Podmínku zabalíme do funkce AGGREGATE, kde jako funkci musíme použít funkci LARGE. Funkce LARGE vrací nejvyšší hodnoty podle zadání. Ve druhém parametru vybereme, že chceme ignorovat chybové hlášky. Matice je celá tato podmínka. A jelikož pracujeme s funkcí LARGE, tak musíme vyplnit i poslední nepovinný parametr k. A jelikož chceme vrátit poslední řádek zadané faktury, tedy nejvyšší hodnotu řádku, tak vybereme jedničku. Funkci potvrdíme a vrátí se nejvyšší pořadové číslo řádku, na kterém je zadaná faktura.
A teď k ní jen doplníme hodnotu. Takže funkce INDEX, kde označíme, co chceme vrátit. Chceme k zadanému řádku vrátit hodnotu.
A máme dohledanou poslední položku.
Řešení s funkcí VYHLEDAT / LOOKUP
Druhou možností, jak vyhledat poslední položku v seznamu je použít trik s funkcí VYHLEDAT neboli funkce LOOKUP. Jedná se o starší funkci, která je v podstatě ve všech verzích Excelů.
Začneme opět nejprve logickou podmínkou. Takže ověříme, že se ve sloupci nachází hledaná faktura. Tato podmínka vrátí sérii pravd a nepravd. Na řádcích, kde jsou pravdy se nachází hledaná faktura.
Stejně jako ve funkci AGGREGATE vydělíme tuto podmínku jedničkou. Tím na řádcích, kde je hledaná faktura, zůstanou jedničky a na ostatních řádcích budou chybové hlášky.
A teď přichází na řadu trik s funkcí VYHLEDAT. Tuto celou podmínku zabalíme do funkce VYHLEDAT. Prvním parametrem je co, tedy co hledáme. V tuto chvíli zde můžete použít jakékoliv číslo vyšší, než číslo jedna. Takže třeba dvojka. To znamená, že hledáme číslo dva. A kde ho hledáme? Na toto místo patří naše podmínka. A v parametru výsledek označíme sloupec, kde se nachází naše odpovědi. Takže sloupec s hodnotami.
A proč hledáme dvojku? Dvojka se v našem sloupci teď nenachází. Vzpomínáte, že na řádcích, kde je hledaná faktura jsou jedničky a na ostatních řádcích jsou chybové hodnoty.
Když tuto funkci potvrdíme, tak se vrátí poslední položka v seznamu pro zadanou fakturu. A proč tato funkce funguje? Abychom to pochopili, tak musíme chápat chování funkce VYHLEDAT neboli funkce LOOKUP. Přesuňme se na chvíli k vedlejšímu příkladu, kde máme čísla a k nim odpovídající písmena. Řekněme, že chceme vyhledat písmeno, které odpovídá číslu 6. Napíšeme funkci VYHLEDAT a funkce najde správné písmeno.
Ale co, když číslo šest v seznamu nebude? Smažeme číslo šest a přeskočíme ho.
V tu chvíli funkce vrátí písmeno, které odpovídá číslu pět. A je to proto, protože funkce VYHLEDAT pracuje ve svém nastavení s přibližnou shodou. Funkce jde řádek po řádku a porovnává, zda je hledaná hodnota shodná nebo menší než hodnota na řádku. Ve chvíli, kdy zadaná položka existuje, tak najde odpovídající hodnotu. Pokud ale položka neexistuje, tak vyhledává podle přibližné shody. Takže když narazí na hodnotu, která je vyšší, tak vrátí položku o řádek výše.
Zde si tedy musíte dát pozor na to, že máte data správně seřazená. Kdybychom čísla v příkladu přeházeli, tak se vrátí jiné písmeno, které bude spojené s prvním číslem menším, než je hledaná hodnota.
A právě proto funguje tento trik pro vyhledání poslední hodnoty v seznamu.
Naše funkce VYHLEDAT hledá dvojku, dvojka se ve sloupci nevyskytuje, jelikož ve sloupci máme jen jedničky a chyby. Takže funkce dvojku nenajde. Takže dojde na konec seznamu a vrátí poslední hodnotu menší než 2, což je poslední jednička. A protože funkce VYHLEDAT ignoruje chyby, tak funkce projde celý sloupec až na konec a vrátí poslední jedničku. Což je poslední hodnota v seznamu.
Trik by fungoval s jakýmkoliv číslem vyšším než 1. Kdybychom hledali jedničku, tak se najde první hodnota.
