Tenhle problém jste v Excelu určitě někdy řešili. Máte tabulku objednávek. Máte ceník. A ceny se v ceníku mění v čase. A vy potřebujete zjistit jednu jedinou věc: jaká cena platila v den objednávky. Ne dnešní cena. Ne první cena v ceníku. Ale poslední platná cena k danému datu. A teď pozor. Pokud tohle dnes pořád řešíte pomocí SVYHLEDAT, tak se na chvíli pohodlně posaďte…protože za chvíli uvidíte, že to jde dělat výrazně jednodušeji.
Excelový soubor ke stažení:
Pojďme si ten problém nejdřív popsat.
Máme dvě tabulky.
V první jsou objednávky – produkt, datum objednávky a množství.
Ve druhé je ceník.
Jenže ne obyčejný ceník, ale ceník včetně historie cen, kde se ceny mění v čase.
A teď přijde ten skutečný problém.
Ke každé objednávce potřebujeme zjistit, jaká cena platila přesně v den, kdy byla objednávka vytvořená.
U prvního řádku máme například produkt 103 a datum objednávky 24. 8. 2024. V ceníku vidíme, že správná cena byla 515 Kč, protože tato cena platila od 1. 7. 2024 do 1. 10. 2024.
Na první pohled to vypadá jako úplně obyčejné vyhledávání. Jenže ve chvíli, kdy to zkusíte řešit pomocí SVYHLEDAT nebo INDEX a POZVYHLEDAT, tak narazíte. Protože nehledáte první hodnotu. A nehledáte ani přesnou shodu pro datum. Vy hledáte správnou hodnotu v čase.
A přesně tenhle typ problému je v Excelu extrémně častý.
Pojďme si nejdřív ukázat, jak se tohle řešilo pomocí funkce SVYHLEDAT.
Nejprve potřebujeme vyřešit to, že musíme vyhledávat podle dvou podmínek. Správný kód produktu a správné časové období ceny. Takže u prvního řádku u produktu 103 máme datum objednávky 24.8.2024, v tabulce ceníku vidíme, že správná cena pro tento produkt a toto datum byla 515 Kč, jelikož zde pracujeme s cenou platnou od 1.7.2024 až do 1.10.2024.
Abychom toto mohli vyřešit s funkcí SVYHLEDAT, tak nejprve do tabulky ceníku musíme přidat pomocný sloupec, kde spojíme kód produktu a datum platné ceny dohromady. A jelikož pracujeme s funkcí SVYHLEDAT, která vyhledává pouze doprava, tak tento pomocný sloupec vložíme před sloupec s cenou. V tomto sloupci spojíme produktový kód a datum. A je na vás, jak hodnoty spojíte. Můžete je jednoduše spojit bez mezery nebo pro lepší přehlednost přidat mezi kód a datum třeba oddělovač svislou čáru. Zároveň se u spojení datum změnilo na pořadové číslo, to nám nemusí vadit, nicméně opět, pokud byste pro větší přehlednost chtěli datum ve formátu datumu, tak to budete muset zabalit do funkce HODNOTA.NA.TEXT neboli funkce TEXT, kde stanovíme správný formát datumu („dd.mm.rrrr“).
To je první pomocný sloupec.
Následně musíme do tabulky objednávek dohledat datum platné ceny. To uděláme pomocí funkce MAXIFS. Ve funkci MAXIFS v parametru max oblast označíme sloupec datum z ceníku, plně zafixujeme, následuje první oblast kritérií, což je sloupec s kódy produktů z ceníku, plně zafixovaná a jako kritérium první kód produktu z objednávek. A jako druhá oblast kritérií je sloupec s datumy z ceníku, sloupec plně zafixovaný a jako kritérium je, že je datum menší nebo rovno prvnímu datumu z objednávek. Funkci potvrdíme a pošleme dolů. A tato funkce doplní správné datumy z ceníku do objednávek. Teď tedy víme, že u prvního produktu v srpnu musíme počítat s cenou platnou k 1.7.2024.
Ti z vás, co nemají funkci MAXIFS, by to museli obejít pomocí kombinace funkce MAX a KDYŽ. Takže funkce MAX, ve které by byla funkce KDYŽ neboli IF, ve které ověříme dvě podmínky, a každá podmínka musí být v samostatných závorkách. První podmínka je, že se sloupec s kódy produktů rovná produktu, a jelikož obě podmínky musí platit zároveň, tak je vynásobíme. A druhá podmínka je, že sloupec s datumy v ceníku je menší nebo roven datumu v objednávkách. Pokud jsou tyto podmínky splněné, tak chceme vrátit sloupec s datumy z ceníku. Ukončíme funkce a jelikož se jedná o maticovou funkci, tak starší verze Excelu ji musí potvrdit klávesami CTRL+SHIFT a ENTER.
To máme další pomocný sloupec.
No a zbývá poslední pomocný sloupec v tabulce objednávky, kde musíme vytvořit stejný sloupec jako v ceníku, tedy spojit kód produktu a datum. A musíme to spojit stejně jako v ceníku.
A teprve teď konečně můžeme pomocí SVYHLEDAT dohledat správnou cenu do objednávek. Takže funkce SVYHLEDAT, kde hledáme sloučenou hodnotu, hledáme ji v tabulce ceníku, kde označíme pouze dva poslední sloupce, jelikož sloupec, dle kterého vyhledáváme musí být první a odpověď se nachází ve druhém sloupci a hledáme přesnou shodu.
Hotovo. Toto sice funguje, ale ruku na srdce, není to intuitivní, a navíc náročné na údržbu, nehledě na to, vysvětlovat existenci pomocných sloupců třeba kolegovi nebo nováčkovi.
A teď si to samé ukážeme pomocí funkce XLOOKUP.
Funkce XLOOKUP, kde hledáme jedničku. Proč jedničku? Protože hledáme pravdu a jednička v Excelu znamená pravda. A v parametru prohledat můžeme ve funkci XLOOKUP ověřit podmínky. Takže první podmínka, že se kód produktu rovná a druhá podmínka vynásobená, že je datum menší nebo rovno datumu z objednávek. Tato část funkce ověří podmínky a vrátí pravdu nebo nepravdu. Podle toho, zda je na řádcích podmínka splněná. A jelikož hledáme pravdu, tak se tím zajistí, že se dohledá správná hodnota. Následuje parametr, co chceme vrátit, což je sloupec s cenou. A pak musíme vyplnit parametr režim vyhledávání a vyhledáváme od konce, takže mínus jedna.
To je celé.
Žádné pomocné sloupce.
Jeden vzorec. Stejný výsledek.
Ještě se narychlo vrátíme k funkci XLOOKUP a ukážeme si, proč tato funkce funguje a proč byste nemohli k vyřešení příkladu použít ani INDEX a POZVYHLEDAT. Když označíme podmínky ve funkci XLOOKUP, tak se vrátí série nul a jedniček a jedničky se vrátily na řádcích, kde jsou splněné obě podmínky. Tyto podmínky byste klidně mohli ověřit ve funkci POZVYHLEDAT neboli funkci MATCH, nicméně tato funkce by spolu s INDEX vrátila hodnotu od první jedničky. Vrátila by tedy hodnotu 590 Kč místo 515 Kč. A důvod, proč jenom funkce XLOOKUP funguje je ten, že zde máme parametr, který nám dovoluje vyhledávat od konce, takže se přiřadí cena od poslední jedničky místo od první.
XLOOKUP tedy není jen rychlejší SVYHLEDAT. Je to úplně jiný způsob, jak v Excelu přemýšlet nad vyhledáváním.
4 komentáře
Terezo, dobrý den a dobrý i tento nový rok. Jako člen Akademie používám nabídku “Excelový soubor ke stažení”, u tohoto videa pouze ‘Lorem ipsun dolor…’. Děkuji ILIAS
Děkuji za upozornění, chybka se vloudila a zapomněla jsem nahrát soubor, už jsem to napravila 🙂
Všechno nejlepší, mnoho osobních a pracovních úspěchů, úspěšnou akademii excelu v roce 2026 přeje senior(77let).
Všechno nejlepší do Nového roku i vám, mému věrnému fanouškovi 😍💕