Máte tyto možnosti
Nejrychlejší a nejlepší způsob, jak vyhledat konkrétní kritéria, je použít funkci VLOOKUP. S ním je možné najít hodnotu v aplikaci Microsoft Excel v milisekundách.
VLOOKUP - takto funguje s kritériem vyhledávání
Aby uživatelé aplikace Excel pochopili, jak funguje VLOOKUP s více kritérii vyhledávání, musí nejprve pochopit, jak je funkce VLOOKUP v zásadě strukturována. Následující příklad ukazuje počet dočasných zaměstnanců zaměstnaných ve společnosti v různých měsících kalendářního roku.
Měl by být použit VLOOKUP, který by ukázal, kolik brigádníků bylo zaměstnáno v březnu a prosinci.
Vzorec VLOOKUP v příkladu za prosinec je:
VLOOKUP (D8, A: B, 2, 0)
Vzorec hledá odpovídající čísla pro dočasné zaměstnance takto:
- Najděte hodnotu D8 (měsíc prosinec) ve sloupcích A a B.
- Najděte hodnotu sloupce B. To je ve vzorci reprezentováno 2.
- 0 ve vzorci znamená, že je třeba vyhledat přesnou hodnotu.
Funkce VLOOKUP hledá jediné vyhledávací kritérium. Matice vzorce je oblast, ve které se nacházejí příslušné hodnoty. Sloupec VLOOKUP navíc musí obsahovat hodnotu, kterou hledáte.
Důležité:
U funkce VLOOKUP musí být vyhledávací kritérium vždy ve sloupci 1. Vpravo od vyhledávacího kritéria musí být také zobrazeny související hledané hodnoty. Pokud je tabulka strukturována odlišně, VLOOKUP nebude fungovat. V tomto případě může být při vytváření požadovaného dotazu užitečná kombinace funkcí INDEX a COMPARE.
VLOOKUP s více kritérii vyhledávání - jak integrovat funkci IF
Ve výše uvedeném příkladu byl dotaz, kolik brigádníků pracovalo ve společnosti v určitém měsíci. Pomocí funkce VLOOKUP lze konkrétní hodnoty snadno odvodit. V následujícím příkladu můžete vidět rozsáhlý seznam článků s 36 položkami [1], ze kterého chcete rychle a snadno filtrovat vhodná čísla článků na základě několika kritérií.
Kritéria hledání jsou uvedena v tabulce vpravo. Nalezené číslo článku by se mělo objevit v buňce H8.
Prvním krokem je zahrnout vzorec s VLOOKUP, který můžete použít k nalezení čísla položky na základě vyhledávacího kritéria. Tento vzorec pak rozbalíte krok za krokem pro další kritéria vyhledávání.
Pomocí následujícího vzorce v buňce H8 najděte číslo položky pro velikost z buňky H3: [2]
= VLOOKUP (H3, A3: E40, 5
Vzorec hledá odpovídající číslo článku následujícím způsobem:
- Najděte hodnotu H3 (velikost 142) ve sloupcích A až E.
- Najděte hodnotu ve sloupci E (číslo článku). To je ve vzorci reprezentováno 5.
Stručně řečeno, funkce VLOOKUP předává jako první argument úplný seznam, ve kterém hledáte obsah. Kritérium vyhledávání, které má být hledáno v prvním sloupci přenesené oblasti, je zaměřeno jako druhý argument. Třetí argument definuje sloupec, ze kterého má být vrácen výsledek.
Seznam odešlete seřazený vzestupně na základě prvního sloupce, aby Excel našel další nižší hodnotu. Vzorec najde číslo článku 2.253 z buňky E16. Protože velikost 142 z buňky H3 není v seznamu uvedena, je nalezena další nižší hodnota 139.
Zahrnout druhé vyhledávací kritérium
V dalším kroku také chcete vyhledat skupinu v buňce H4 jako druhé kritérium. Mělo by být nalezeno pouze číslo článku, ve kterém se vyskytuje skupina, kterou hledáte.
Chcete -li to provést, přidejte do vzorce v buňce H8 funkci IF a vložte vzorec jako maticový vzorec:
= VLOOKUP (H3; IF (B3: B40 = H4; A3: E40; „“); 5)
Informace:
V aplikaci Microsoft Excel se rozlišuje mezi konvenčními vzorce a výkonnějšími maticovými vzorci. Maticové vzorce umožňují uživatelům Excelu provádět složité výpočty. To nelze provést pomocí standardních vzorců. Protože po zadání vzorce musí být stisknuta kombinace kláves „CTRL + SHIFT + ENTER“, jsou maticové vzorce mezinárodně známé jako nebo CSE vzorce (CTRL + SHIFT + ENTER).
Po zadání vzorce potvrďte kombinaci kláves Ctrl + Shift + Enter jako maticový vzorec. [3]
Výsledkem je číslo položky 1.188. Vzorec najde ve sloupci „Velikost“ hodnotu 126. Jedná se o největší hodnotu v tabulce. Je menší nebo roven hledané hodnotě 142 a má obsah C2 ve vztahu ke skupině.
Funkce IF ve vzorci kontroluje shodu každé buňky v rozsahu B3: B40 s obsahem buňky H4. Jakmile dojde k shodě, je odpovídající řádek v oblasti B3: E40 předán funkci VLOOKUP.
Kdykoli můžete snadno přidat další kritéria vyhledávání
Stejným způsobem můžete přidat další vyhledávací kritéria. To provedete vnořením více funkcí IF do vzorce. Aby vyhovovala všem čtyřem kritériím vyhledávání, zahrňte do vzorce v buňce H8 další dvě vnořené funkce IF:
= VLOOKUP (H3; IF (B3: B40 = H4; IF (C3: C40 = H5; IF (D3: D40 = H6; A3: E40; „“); „“); „“); 5)
Po potvrzení maticového vzorce pomocí kombinace kláves Ctrl + Shift + Enter vrátí funkce VLOOKUP číslo článku 1.748. Toto je číslo z řádku 14 pro velikost 125. Je to řádek s nejvyšší hodnotou ve sloupci „Velikost“, ve kterém jsou splněna všechna tři další kritéria. [4]
Pokud je kontrola pozitivní, první funkce IF neprodá okamžitě odpovídající řádek funkci VLOOKUP. Místo toho jiná funkce IF kontroluje, zda existuje shoda s druhým dodatečným kritériem. Pokud je tato kontrola také pozitivní, použije se třetí funkce IF. Pouze když všechny tři funkce IF vrátí kladnou kontrolu, předá třetí funkce IF odpovídající řádek funkci VLOOKUP. Tímto způsobem můžete vnořit až sedm funkcí IF a zkontrolovat tak až osm kritérií.
Shrnutí: Toto je účinný způsob vyhledávání více kritérií v aplikaci Excel
Uživatelé aplikace Microsoft Excel se při své každodenní práci neustále potýkají s otázkou, jak číst data a informace z rozsáhlých nebo vnořených tabulek. Uživatelé aplikace Excel najdou nejefektivnější referenční funkci ve funkci VLOOKUP. Hlavní výhodou funkce VLOOKUP a dalších referenčních funkcí je, že ve výchozím nastavení vyhledávají další nižší hodnotu, jakmile hledanou hodnotu nenajdou.
Vzhledem k tomu, že funkce VLOOKUP je ve výchozím nastavení omezena na jeden hledaný výraz, je pro více kritérií hledání nutné použít rozšířený vzorec. K vyhledání několika hledaných výrazů pomocí funkce VLOOKUP je vhodný maticový vzorec s IF funkcemi. Kontroluje jednotlivé funkce IF jednu po druhé a pokud je výsledek kladný, odkazuje na VLOOKUP. Tím je zajištěno, že se pro rozsáhlé tabulky s různými hledanými výrazy zobrazí správný výsledek.
FAQ
Co může funkce VLOOKUP dělat?
Funkce VLOOKUP v Microsoft Excelu je jednou z nejdůležitějších a nejefektivnějších funkcí v Excelu. Prostřednictvím VLOOKUP můžete automaticky vyhledávat v rozsáhlé tabulce konkrétní hodnotu. Výsledek je upraven a odeslán do jiné buňky.
Kdy používáte funkci IF v aplikaci EXCEL?
Funkce IF je jednou z nejpoužívanějších funkcí v Microsoft Excelu. V prvním kroku je k definování podmínky použita funkce IF. Ve druhém kroku propojíte IF například s funkcí THEN a určíte, které informace mají být na výstupu. K určení alternativního výsledku lze také použít funkci ELSE. Praktický příklad: POKUD zaměstnanec A odpracoval více než 240 dní v roce POTOM bonus A DALŠÍ bonus B.
Jaký je rozdíl mezi vzorci pole a standardními vzorci v Excelu?
Nejznámější v Excelu jsou konvenční standardní vzorce. V Excelu jsou maticové vzorce výkonné vzorce, pomocí kterých je možné provádět složité výpočty. Aby bylo možné spustit vzorec pole, musí být vzorec pole po zadání potvrzen kombinací kláves „CTRL + SHIFT + ENTER“. Klávesová zkratka ukazuje Excelu, že jde o vzorec pole.
