Přibližná shoda v Excelu se využívá ve dvou vyhledávacích funkcích, a to funkci SVYHLEDAT a funkci POZVYHLEDAT. Nejen to, ve funkcích SVYHLEDAT a POZVYHLEDAT si můžete vybrat jako shodu si přejete vyhledat. Na výběr máte z přesné shody a nebo přibližné shody. Ale víte, jaké jsou v nich rozdíly? A víte, kdy použít k vyhledání přibližné shody SVYHLEDAT a kdy je lepší použít POZVYHLEDAT?
Ve většině případů využíváme v Excelu při vyhledávání přesnou shodu, to znamená, že hledaný výraz se musí 100% shodovat s výrazy, mezi kterými prohledáváme.
Dnes se zaměříme na přibližné shody ve dvou vyhledávacích funkcích, kterými jsou SVYHLEDAT a POZVYHLEDAT. Na konci videa vám ještě ukáži i bezva trik, a to jak použít přibližnou shodu v Excelu na neseřazená data.
Pro ty z vás, kteří upřednostňují psaný text před videem, je pod videem detailní návod.
Excelový soubor ke stažení
Přibližná shoda v Excelu
Jak název přibližná shoda napovídá, tak přibližnou shodu ve vyhledávání použijeme tehdy, pokud víme, že hledáme hodnotu, která se v identické podobě nevyskytuje v tabulce, ve které hodnotu hledáme. U textových hodnot k přibližné shodě můžeme využít wildcards, tedy zástupné znaky, u číselných hodnot využijeme přibližné shody.
Přibližná shoda v Excelu ve funkci SVYHLEDAT
Ve funkci SVYHLEDAT máme na výběr pouze mezi přesnou shodou a přibližnou shodou. Pokud nepovinný parametr shoda nevyplníme, funkce SVYHLEDAT použije pro vyhledávání přibližnou shodu. Pro vyhledávání pomocí přesné shody musíme v parametr shoda vyplnit nepravdu.
= SVYHLEDAT(hledat; tabulka; sloupec; [shoda])
0 – Přesná shoda
Nevýhodou přibližné shody u funkce SVYHLEDAT je to, že sloupec dle kterého hledáme, musí být vždy seřazený a to od nejnižšího čísla po nejvyšší. Funkce SVYHLEDAT totiž jde od prvního řádku v tabulce a porovnává, zda je hledaná hodnota nižší než hodnota uvedená na daném řádku. Když dojde k řádku, kde je hodnota vyšší, tak se vrátí o jeden výše, a určí tak správný interval.
Pokud bychom měli sloupec seřazený obráceně, tedy od nejvyšších hodnot po nejnižší, tak už přibližná shoda ve funkci SVYHLEDAT fungovat nebude. V takových případech je tedy lepší použít přibližnou shodu ve funkci POZVYHLEDAT.
Přibližná shoda v Excelu ve funkci POZVYHLEDAT
Zatímco ve funkci SVYHLEDAT máte na výběr pouze z přesné shody a přibližné shody, tak ve funkci POZVYHLEDAT máte o možnost navíc.
= POZVYHLEDAT(co; prohledat; [shoda])
1 – Menší než
0 – Přesná shoda
-1 – Větší než
Na rozdíl od funkce SVYHLEDAT je rovněž jako defaultní hodnota nastavená ve funkci přesná shoda. Pokud tedy nepovinný parametr nevyplníte, tak funkce pracuje s přenou shodou.
Ve zdrojové tabulce máme spodní a horní intervaly pro tržby a příslušné marže. Vedle tabulky máme pole pro marži a tržbu.
Začneme funkcí POZVYHLEDAT. Jako co hledáme označíme hledanou tržbu, kde tuto tržbu hledáme? Označíme první sloupec, tedy spodní hranici tržeb, a jako shodu hledáme? Pokud bychom vyplnili 0, tedy přesnou shodu, tak nám funkce vrátí chybu, jelikož přesná výše tržby se v tabulce nevyskytuje. Nulu tedy použít nemůžeme.
První věc, kterou si u přibližných shod musíte pamatovat je to, že sloupec dle kterého hledáte, musí být seřazený. Druhé pravidlo je, že pokud máte sloupec, dle kterého hledáte seřazený od nejmenší po nejvyšší číslo, tak hledáte podle přibližné shody 1, tedy menší než. Pokud byste sloupec měli seřazený od nejvyššího čísla po nejnižší, tak byste naopak použili přibližnou shodu -1, tedy větší než.
V tabulce hledáme hodnotu 25 000 Kč (buňka F21). Tržby máme v prvním sloupci seřazené od nejnižšího po nejvyšší, takže jako přibližnou shodu vybereme 1, tedy menší než. Funkce POZVYHLEDAT vrátila hodnotu 3. Správná odpověď ve zdrojové tabulce by tedy měla ležet na třetím řádku tabulky. Spojíme to rovnou i s funkcí INDEX, aby se vrátila správná hodnota marže. Před funkci POZVYHLEDAT napíšeme funkci INDEX a jako pole označíme sloupec s maržemi. Potvrdíme a vrátila se správná hodnota marže.
Přibližná shoda ve funkci POZVYHLEDAT pracuje tak, že projde v tabulce řádek po řádku a porovnává, zda je hledaná hodnota menší nebo větší než hodnota, která je uvedená na daném řádku. V tomto prvním příkladu jsme hledali přibližnou shodu menší než, takže když přibližná shoda došla k řádku, kde je uvedeno 30 001 Kč, tak vrátila pravdu, jelikož 25 000 Kč je menší než 30 000 Kč, a vrátila se o jeden řádek nazpět, kde máme správný interval. Právě proto musíte mít sloupce při použití přibližné shody vždy seřazené.
V dalším příkladu máme stejnou tabulku dat, ale sloupec spodního intervalu máme seřazený od nejvyššího čísla po nejnižší. Všimněte si, že horní hranici u přibližné shody vlastně vůbec nepotřebujeme, funkce si vystačí pouze s prvním sloupcem. Tentokrát máme tedy sloupec seřazený obráceně, takže pro vyhledání marže použijeme přibližnou shodu -1.
Rozdíl v přibližných shodách ve funkci POZVYHLEDAT
Funkci potvrdíme a všimněte si zajímavé věci. Přibližná shoda vrátila jiný výsledek než v předešlém příkladu. U přibližné shody menší než se vrátila marže 10 %, ale u přibližné shody větší než se vrátila marže 15 %. Proč?
Důvod si ukážeme vedle tabulky. Jak jsem již říkala, tak přibližná shoda porovnává každý řádek v tabulce vůči hledané hodnotě. Vedle tabulky si tedy uděláme pomocný výpočet. U první tabulky (C21:C24) se zeptáme, zda je buňka F21 menší než hodnota na řádku (F21<A21). Vrátila se série pravd a nepravd. Přibližná shoda menší než projde řádky ve zdrojové tabulce a když narazí na první pravdu, tak se o jeden řádek vrátí nahoru a určí tak správný interval.
Zkusíme to samé u druhé tabulky (C29:C33). Opět se zeptáme, zda je hodnota v buňce F29 větší než hodnota na řádku (F29>A29). Opět se vrátily pravdy a nepravdy. A stejně jako předtím jde funkce řádek po řádku a když narazí na první pravdu, tak se o jeden řádek vrátí nahoru. Proto přibližné shody vrací jiný výsledek.
Je to něco, čeho si musíte být vědomi, když přibližnou shodu ve funkci POZVYHLEDAT používáte. Vždy záleží na to, o jaký výsledek se snažíte.
Chybové hlášky u přibližné shody
Stejně jako u funkce SVYHLEDAT, tak i funkce POZVYHLEDAT vrátí chybovou hlášku NENÍ_K_DISPOZICI, pokud funkce nenajde hledanou hodnotu v intervalu. Akorát u funkce POZVYHLEDAT záleží na tom, jak máte sloupec seřazený. Rozdíl si ukážeme na srovnání na obrázcích č.6 až č.9.
Máme zde limity tržeb od 10 000 Kč do 40 001 Kč. Pokud budeme mít sloupec seřazený od nejnižší hodnoty po nejvyšší a budeme hledat 60 000 Kč, tak přibližná shoda vrátí poslední hodnotu v tabulce (obrázek č.6). Pokud budeme hledat hodnotu 5 000 Kč, tak funkce vrátí chybu, jelikož najde hodnotu 10 000 Kč, vrátí se o jedno pole nahoru, a tam žádná hodnota není (obrázek č.7).
Pokud ale budeme mít sloupec seřazený od nejvyššího čísla po nejnižší, a budeme hledat 60 000 Kč, tak se opět vrátí chyba, jelikož funkce najde 40 000 Kč, vrátí se o jedno pole nahoru a tam opět nic není (obrázek č.8). Pokud bychom v té samé tabulce hledali 5 000 Kč, tak funkce vrátí poslední hodnotu v tabulce (obrázek č.9). Záleží tedy na tom, jak máte sloupce seřazené.
Přibližná shoda pro neseřazená data
Základem pro to, aby přibližná shoda větší než a menší než fungovala je to, že musíte mít data seřazená vzestupně nebo sestupně. Jak ovšem použít přibližnou shodu, pokud data seřazená nemáte a ani je z nějakého důvodu seřadit nemůžete? Tento trik si ukážeme teď. A paradoxně k tomu funkci POZVYHLEDAT ani SVYHLEDAT vůbec nepoužijeme.
Řekněme, že máme intervaly v tabulce přeházené (obrázek č.10) a z nějakého důvodu je nemůžeme seřadit. Funkce POZVYHLEDAT a SVYHLEDAT vrátí na neseřazená data nesprávný výsledek.
Zkusíme to ale obejít pomocí jiných funkcí. Začneme tím, co potřebujeme zjistit. Potřebujeme zjistit, na kterém řádku ve zdrojové tabulce je interval, do kterého spadá hodnota v buňce I65. Vedle tabulky si na ukázku uděláme pomocné sloupce, kde si celou logiku za výpočtem ukážeme. Aby hodnota spadla do správného intervalu, tak bude vždy platit, že bude hodnota I 65 vyšší nebo rovna hodnotě z prvního sloupce (D66:D70). Objevila se série pravd a nepravd, tam kde máme pravdu, tak pro ty řádky platí, že je hodnota I65 vyšší nebo rovna hodnotě na řádku.
To by ale nestačilo, jelikož zde máme i horní interval. Takže se vedle ve sloupci zeptáme, zda je hodnota I65 menší nebo rovna hodnotám ve druhém sloupci (E66:E70). Zase se někde vrátila pravda a někde nepravda. Ale všimněte si, že pouze na jednom řádku máme dvě pravdy vedle sebe, a je to přesně ten interval, kam naše hodnota patří.
Teď stačí hodnoty pravda a nepravda mezi sebou vynásobit (F66:F70). Na řádku se správným intervalem máme jedničku, jelikož pravda v Excelu vždy reprezentuje jedničku a nepravda nulu. Pomocí těchto dvou podmínek jsme tedy schopni identifikovat správný řádek. Co s tím teď?
Teď v podstatě stačí jen přiřadit správnou marži dle řádku, který má u sebe jedničku. Ale ideálně bychom to chtěli bez všech těchto pomocných sloupců. Existuje excelová funkce, která si umí poradit s násobením polí mezi sebou v rámci jedné buňky bez pomocných sloupců?
Existuje, jmenuje se SOUČIN.SKALÁRNÍ.
Takže v buňce I66 napíšeme funkci SOUČIN.SKALÁRNÍ. Ve funkci SOUČIN.SKALÁRNÍ použijeme mezi sebou vynásobené dvě podmínky. Toto celé je prvním polem funkce SOUČIN.SKALÁRNÍ (v samostatných závorkách). Následuje druhé pole ve funkci SOUČIN.SKALÁRNÍ, což je sloupec marže.
Prakticky funkce SOUČIN.SKALÁRNÍ nejdříve vynásobila závorky mezi sebou stejně jako jsme si ukázali na začátku v pomocných sloupcích (D66:F70). Výsledkem prvního pole byla série jedniček a nul. Následně tyto nuly a jedničky vynásobila marží. Nakonec funkce SOUČIN.SKALÁRNÍ výsledek sečetla. Ale jelikož zde byla jen jedna jednička, tak ke správné marži přičetla pouze nuly.
Přibližná shoda na neseřazená data s otevřeným intervalem
Co kdybychom ale měli interval následovně. Přidáme do tabulky ještě poslední otevřený interval, že všechny hodnoty nad 50 001 Kč mají marži 25 %. Bude nám dosavadní funkce SOUČIN.SKALÁRNÍ fungovat? Nebude.
Proč? Protože výsledek druhé závorky je nepravda. Takže se na žádném řádku neobjeví dvakrát pravda, tudíž nejsme schopní identifikovat správný řádek. To ale můžeme obejít tím, že celou funkci SOUČIN.SKALÁRNÍ zabalíme do funkce KDYŽ. Před celou funkci SOUČIN.SKALÁRNÍ napíšeme funkci KDYŽ a podmínka je, že pokud je hodnota F66 nižší než hodnota A71, tak se má počítat funkce SOUČIN.SKALÁRNÍ, a pokud ne, tak chceme vrátit hodnotu marže C71.
