V dnešním videu se podíváme na excelovou funkci DZÍSKAT, anglicky funkci DGET. Jedná se o excelovou funkci, která je dostupná ve všech verzích Excelů od verze 2010. Databázová funkce DZÍSKAT vrátí hodnotu ze seznamu neboli databáze, na základě zvoleného kritéria nebo více kritérií. Na rozdíl od jiných databázových funkcí tato funkce neagreguje hodnoty, ale vyhledává je. Lze ji tedy použít jako náhražku vyhledávacích funkcí SVYHLEDAT nebo kombinace funkcí INDEX & POZVYHLEDAT. Dnes si ukážeme, jak tuto funkci použít a jaké jsou její hlavní výhody oproti funkci SVYHLEDAT.
Excelový soubor ke stažení
Excelová funkce DZÍSKAT odstraní nevýhody funkce SVYHLEDAT
Funkce DZÍSKAT vám v některých případech skvěle nahradí vyhledávací funkci SVYHLEDAT, anglicky VLOOKUP. Funkce DZÍSKAT totiž odstraňuje dvě hlavní nevýhody funkce SVYHLEDAT, kterými jsou neschopnost funkce SVYHLEDAT vyhledávat nalevo od hledaných hodnot a vyhledávání na základě více kritérií. Funkce DZÍSKAT totiž vyhledává na základě více kritérií bez nutnosti spojovat hledané hodnoty nebo pomocného sloupce.
Použití funkce DZÍSKAT si ukážeme na prvním příkladu. V prvním příkladu máme zdrojovou tabulku (A3:D13), ze které chceme vyhledat několik údajů k produktu. Produkt si můžete zvolit pomocí rozbalovacího seznamu v buňce F4. V tomto případě by použití funkce SVYHLEDAT nebylo vhodné, jelikož se několik hodnot, které chceme vyhledat ve zdrojové tabulce nachází nalevo od sloupce s produkty. Zde bychom spíše uplatnili vyhledávací funkce INDEX & POZVYHLEDAT, ale i to by mělo své limity. Zkusíme vyhledat hodnoty pomocí funkce DZÍSKAT.
= DZÍSKAT (databáze; pole; kritéria)
Do buňky G4 napíšeme funkci DZÍSKAT. Prvním parametrem funkce DZÍSKAT je databáze, což je zdrojová tabulka. U funkce DZÍSKAT musíme vždy označit celou zdrojovou tabulku včetně záhlaví tabulky.
Druhým parametrem funkce je pole. U parametru pole máme na výběr ze dvou způsobů zápisu. Můžeme stejně jako u funkce SVYHLEDAT napsat pořadové číslo sloupce, ze kterého chceme vrátit vyhledávanou hodnotu. Chceme vrátit hodnoty z prvního sloupce, takže napíšeme jedničku. Posledním parametrem funkce je kritérium. Zde se označuje hodnota, kterou hledáme, opět včetně záhlaví. Zároveň musí být shoda mezi názvem v záhlaví u hledané hodnoty a ve zdrojové tabulce. Proto mám nad hledaným produktem nadepsáno Produkt (F3), což je stejný nadpis v záhlaví jako ve zdrojové tabulce (C3). Označíme tedy obě buňky. Funkce je hotová. Ukončíme závorku a funkci potvrdíme.
A vrátila se správná odpověď.
Vrátíme se k funkci a ukážeme si druhý zápis parametru pole. Kromě pořadového čísla sloupce můžeme označit i název v záhlaví. Opět se musí název v záhlaví u hledané hodnoty shodovat s názvem ve zdrojové tabulce. Místo jedničky bychom tedy označili název v záhlaví. Funkci potvrdíme a vidíme, že se opět vrací správná hodnota.
Co by se stalo, kdyby nadpis v záhlaví u kritéria nebo u pole nebyl ve stejném formátu jako ve zdrojové tabulce? Přepíšeme záhlaví z produktu na produkty a vidíme, že funkce vrací chybu. Název v záhlaví tedy musí být u parametrů pole a kritérium identický se zdrojovou tabulkou.
Hlavní výhodou funkce DZÍSKAT je ovšem to, že nemusíme řešit z kolikátého sloupce chceme vrátit hodnoty. Tím, že funkce DZÍSKAT vyhledává podle názvů v záhlaví, tak nám stačí pouze funkci přetáhnout doprava. Musíme ale nejprve správně zafixovat buňky. Vrátíme se k funkci a nejprve plně klávesou F4 zafixujeme rozsah databáze, parametr pole fixovat nemusíme, ale ještě plně zafixujeme parametr kritéria. Teď funkci přetáhneme doprava a vidíme, že se správně doplní všechny hledané hodnoty, a to bez nutnosti cokoliv měnit ve funkci.
Vyhledávání na základě více kritérií (logická podmínka A)
Funkce DZÍSKAT si u vyhledávání poradí i s více kritérii. V dalším příkladu máme zdrojovou tabulku, ze které potřebujeme vyhledat tržbu a ID produktu podle produktu a oblasti. Nejprve si rychle ukážeme, jak bychom tento úkol řešili pomocí funkce SVYHLEDAT nebo INDEX & POZVYHLEDAT.
U funkce SVYHLEDAT bychom si museli pomoci pomocným sloupcem. Tento pomocný sloupec bychom museli ideálně vložit jako první sloupec tabulky. V tomto pomocném sloupci bychom nejprve spojili produkt a oblast a to pomocí ampersandu. Tím by vznikl jedinečný seznam hodnot. Následně bychom použili funkci SVYHLEDAT, kde bychom nejprve označili hledané hodnoty a opět bychom je museli spojit. V pomocném sloupci máme hodnoty spojené nejprve oblast a pak produkt, tak to ve funkci SVYHLEDAT spojíme pomocí ampersandu stejně. Hodnoty zafixujeme klávesou F4 pro sloupce. Středník a následuje tabulka, označíme celou zdrojovou tabulku včetně pomocného sloupce, plně zafixovanou pomocí klávesy F4. Následuje pořadové číslo sloupce, v tomto případě jsou tržby ve čtvrtém sloupci tabulky. A hledáme přesnou shodu, takže nulu. Funkci potvrdíme. Pro ID produktu bychom buď funkci museli napsat znovu nebo ji přetáhneme. Akorát teď musíme nahradit pořadové číslo sloupce pátým sloupcem.
U vyhledávacích funkcí INDEX & POZVYHLEDAT bychom nemuseli tvořit pomocný sloupec. Jednotlivá pole můžeme spojit přímo ve funkcích. Začneme s funkcí INDEX, kde označíme sloupec, ve kterém se nacházejí odpovědi, tedy sloupec s tržbami. Sloupec plně zafixujeme klávesou F4. Následuje funkce POZVYHLEDAT, kde nejprve označíme co hledáme. Opět musíme spojit produkt a oblast, a to pomocí ampersandu. Obě hodnoty plně zafixované klávesou F4. Následuje oblast, kde tyto hodnoty hledáme. I tyto sloupce musíme spojit. Takže ve zdrojové tabulce nejprve označíme sloupec s oblastí, zafixujeme, ampersand a sloupec s produkty, opět zafixovaný. Posledním parametrem je přesná shoda. Funkci potvrdíme a máme správný výsledek. Přetáhneme funkci doprava a musíme udělat změnu. Ve funkci INDEX teď hledáme ve sloupci ID produktu, takže sloupec změníme.
A jak by to bylo s funkcí DZÍSKAT? Napíšeme funkci DZÍSKAT, kde nejprve označíme celou zdrojovou tabulku včetně záhlaví a plně ji zafixujeme klávesou F4. Následuje parametr pole, takže označíme záhlaví pro hodnotu, kterou hledáme. Středník a následuje parametr kritérium, zde na rozdíl od předešlých funkcí stačit pouze označit všechna kritéria včetně záhlaví. Označíme tedy jak produkt, tak oblast a funkci ukončíme a potvrdíme. To je celé. Takto jednoduše funkce DZÍSKAT vyhledá hodnoty na základě několika kritérií.
Funkce DZÍSKAT / DGET a vyhledávání podle více než dvou kritérií
A co kdyby těch kritérií bylo více? Stejně jako více hodnot, které hledáme? Postup bude stejný. V další tabulce napíšeme funkci DZÍSKAT, kde nejprve označíme celou zdrojovou tabulku včetně záhlaví, plně zafixovanou. Následuje první název záhlaví pro hodnotu, kterou hledáme a pak jen označit kritéria včetně jejich záhlaví, opět plně zafixované. Funkci potvrdíme, přetáhneme ji doprava a máme správně doplněné všechny hodnoty.
Vyhledávání na základě částečné shody
Skvělé je, že funkce DZÍSKAT vyhledává i na základě částečné shody. V dalším příkladu máme v kritériích pouze část slova. Funkce DZÍSKAT si s tím poradí. Napíšeme funkci DZÍSKAT, označíme zdrojovou tabulku plně zafixovanou. Následuje první název v záhlaví a pak už jen kritéria včetně záhlaví, plně zafixované. Potvrdíme a funkci přetáhneme doprava. Všechny hodnoty se správně doplnily i na základě částečné shody.
Nevýhody funkce DZÍSKAT / DGET
Funkce DZÍSKAT je skvělá v tom, že odstraní hlavní nevýhody funkce SVYHLEDAT. Sama má ovšem taky několik limitů. Pomocí funkce DZÍSKAT lze vyhledat vždy jen jednu hodnotu, tedy dokáže vyhledávat jen podle jednoho řádku. Kvůli parametru kritérium nelze funkci DZÍSKAT stahovat dolů. Pokud pod hledanou hodnotu napíšeme další a stáhneme funkci DZÍSKAT dolů, tak se vrátí chyba. A je to proto, že se rozšířila oblast kritéria a funkce neví, co má hledat. Dalším omezení funkce DZÍSKAT je stejné jako u ostatních vyhledávacích funkcí SVYHLEDAT, XLOOKUP nebo INDEX & POZVYHLEDAT. A to je to, že neumí vyhledávat, pokud máte v tabulce duplicitní hodnoty.
Funkce DZÍSKAT / DGET a vyhledávání s duplicitami
V dalším příkladu máme v tabulce dva stejné záznamy produktu, oblasti a manažera. Zkusíme funkci DZÍSKAT. Označíme zdrojovou tabulku, nadpis tržba a všechna kritéria. Funkci potvrdíme a vrátila se chyba. Zde je rozdíl oproti ostatním funkcím, zatímco ostatní vyhledávací funkce vrátí u duplicity vždy první záznam, tak funkce DZÍSKAT vrátí chybu.
Vyhledávání s funkcí DZÍSKAT / DGET s logickou podmínkou NEBO
Posledním co si ukážeme, je to, že funkce DZÍSKAT zvládne vyhledávat i pomocí logiky NEBO. V poslední tabulce máme několik jmen manažerů. Chceme podle manažera vyhledat ostatní údaje do tabulky. Nejsme si ale jistí, zda se Eva jmenuje Nováková nebo Novotná. Nedávno se vdala a nevíme, zda ji máme v databázi s novým příjmením nebo ne. Druhou možnost jména tedy napíšeme pod první kritérium. Napíšeme funkci DZÍSKAT, kde označíme zdrojovou tabulku, následuje název v záhlaví a v parametru kritérium označíme obě varianty jména včetně záhlaví. Funkci potvrdíme a vrátil se správný výsledek. Pokud Evu Novákovou přepíšeme na Evu Novotnou, tak funkce stejně vrátí správný výsledek. Pozor si ale musíte dát na to, že nesmíte mít obě jména v tabulce zároveň. Pokud někoho jiného teď změním na Evu Novákovou, tak funkce DZÍSKAT vrátí chybu.
2 komentáře
😊
nekdy to zbytecne vysvetlujete slozite