6 vzorců k vyhledávání v aplikaci Excel

Všichni známe slavnou hvězdu funkcí Excel VLOOKUP. Běžně se používá k vyhledávání hodnot s jedinečným ID. Ale to není jediná funkce, kterou lze použít k vyhledávání hodnot v aplikaci Excel. K vyhledávání hodnoty lze použít mnoho dalších funkcí a vzorců. V tomto článku vám představím všechny tyto funkce a vzorce pro vyhledávání v Excelu. Některé jsou dokonce lepší než funkce VLOOKUP v Excelu. Čtěte tedy do konce.

1. Funkce VLOOKUP aplikace Excel

První funkcí vyhledávání v Excelu je samozřejmě funkce VLOOKUP. Tato funkce je známá z nějakého důvodu. Tuto funkci můžeme použít k více než jen vyhledávání. Základním úkolem této funkce je však vyhledávat hodnoty v tabulce zleva doprava.

Syntaxe funkce VLOOKUP:

= VLOOKUP (lookup_value, tabulka_pole, col_index_number, [range_lookup])

Lookup_value: Hodnota, podle které chcete hledat v prvním sloupci pole tabulky.

Tabulka_pole: Tabulka, ve které chcete vyhledat/hledat

col_index_number: Číslo sloupce v poli Tabulka, ze kterého chcete načíst výsledky.

[range_lookup]: FALSE, pokud chcete hledat přesnou hodnotu, TRUE, pokud chcete přibližnou shodu.

Výhody VLOOKUP:

• Snadné použití.
• Rychle
• Vícenásobné použití
• Nejlepší pro vyhledávání hodnot ve svislém pořadí.

Nevýhody:

• Používá se pouze pro vertikální vyhledávání
• Vrací pouze první odpovídající hodnotu.
• Statický, dokud není použit s funkcí MATCH.
• Nelze vyhledávat hodnoty zleva od vyhledávací hodnoty.

Zde si můžete podrobně přečíst o tomto vzorci pro vyhledávání v Excelu.

2. Funkce HLOOKUP aplikace Excel

Funkce HLOOKUP je chybějící částí funkce VLOOKUP. Funkce HLOOKUP slouží k horizontálnímu vyhledávání hodnot. Jinými slovy, pokud chcete vyhledat hodnotu v aplikaci Excel porovnáním hodnoty ve sloupcích a získat hodnoty z řádků, použijeme funkci HLOOKUP. To je přesně výhoda funkce VLOOKUP.

Syntaxe HLOOKUP

=HLOOKUP(vyhledávací hodnota, pole tabulky, číslo indexu řádku, [look_rozsahu])

• vyhledávací hodnota: Hodnota, kterou hledáte.
• Tabulka Array: Tabulka, ve které hledáte hodnotu.
• Indexové číslo řádku: Číslo řádku v tabulce, ze kterého chcete načíst data.
• [range_lookup]: je to typ shody. 0 pro přesnou shodu a 1 pro přibližnou shodu.

Výhody funkce HLOOKUP:

• Může vyhledávat hodnoty horizontálně.
• Snadné použití.
• Vícenásobné použití
• Rychle

Nevýhody:

• Používá se pouze pro horizontální vyhledávání
• Vrací pouze první odpovídající hodnotu.
• Statický, dokud není použit s funkcí MATCH.
• Nelze vyhledat hodnoty nad vyhledávacími hodnotami v tabulce.

Zde se můžete dozvědět o této funkci vyhledávání v Excelu.

3. Vzorec pro vyhledávání INDEX-MATCH

Tam, kam se VLOOKUP a HLOOKUP nedostanou, může dosáhnout tento vzorec. Toto je nejlepší vzorec pro vyhledávání v Excelu do Excelu 2016 (XLOOKUP je na cestě).

Obecný vzorec INDEX MATCH

= INDEX (Result_Range, MATCH (lookup_value, lookup range, 0))

Výsledek_Range: Je to rozsah rozsahu, odkud chcete získat hodnotu.

Lookup_value: Je to hodnota, které chcete přiřadit.

Rozsah vyhledávání:Je to rozsah, ve kterém chcete, aby odpovídal vyhledávací hodnotě.

Výhody vyhledávacího vzorce INDEX-MATCH:

• Lze vyhledávat ve čtyřech směrech. Může vyhledávat hodnoty nalevo a nahoru od vyhledávací hodnoty.
• Dynamický.
• Není nutné definovat index řádku nebo sloupce.

Nevýhody:

• Pro nové uživatele to může být obtížné.
• Kombinuje dvě funkce Excelu. Uživatelé musí porozumět fungování funkcí INDEX a MATCH.

O tomto vzorci se můžete dozvědět zde.

4: Vzorec pro vyhledávání Excel OFFSET-MATCH

Toto je další vzorec, který lze použít k dynamickému vyhledávání hodnot. Tento vzorec pro vyhledávání v Excelu používá funkci OFFSET jako funkci ukotvení a MATCH jako funkci podavače. Pomocí tohoto vzorce můžeme dynamicky získávat hodnoty z tabulky hledáním v řádcích a sloupcích.

Obecný vzorec,

= OFFSET (StartCell, MATCH (RowLookupValue, RowLookupRange, 0), MATCH (ColLookupValue, ColLookupRange, 0))

StartCell:Toto je počáteční buňka vyhledávací tabulky. Řekněme, že pokud chcete vyhledat v rozsahu A2: A10, pak StartCell bude A1.

RowLookupValue: Toto je vyhledávací hodnota, kterou chcete najít v řádcích podStartCell.

Řádek vyhledávání:Toto je rozsah, ve kterém chcete vyhledat RowLookupValue. Je to níže uvedený rozsah StartCell (A2: A10).

ColLookupValue: Toto je vyhledávací hodnota, kterou chcete najít ve sloupcích (záhlaví).

Rozsah ColLookup:Toto je rozsah, ve kterém chcete ColLookupValue vyhledat. Je to rozsah na pravé straně StartCell (jako B1: D1).

Výhody této vyhledávací techniky v Excelu:

• Rychle
• Lze vyhledávat vodorovně i svisle.
• Dynamický

Nevýhody:

• Pro některé lidi složité.
• Potřebujete porozumět fungování funkce OFFSET a funkce MATCH.

Zde se můžete podrobně dozvědět o tomto vyhledávacím vzorci.

5: Excel LOOKUP Formula více hodnot

Všechny výše uvedené vzorce vyhledávání vrátí první nalezenou hodnotu z pole. Pokud je více než jeden zápas, nevrátí další zápasy. V takovém případě se tento vzorec aktivuje, aby zachránil den. Tento vzorec vrátí všechny odpovídající hodnoty ze seznamu, namísto pouze první shody.

Tento vzorec používá jako hlavní funkce funkce INDEX, ROW a IF. Funkci IFERROR lze volitelně použít ke zpracování chyb.

Obecný vzorec

{= INDEX (pole, SMALL (IF (lookup_value = lookup_value_range, ROW (lookup_value_range) -ROW (první buňka lookup_value_range) +1), ROW (1: 1)))}}

Pole: Rozsah, odkud chcete načíst data.
lookup_value: Vaše vyhledávací_hodnota, kterou chcete filtrovat.
lookup_value_range: Rozsah, ve kterém chcete filtrovat lookup_value.
První buňka v rozsahu lookup_value: pokud je váš rozsah lookup_value $ A $ 5: $ A $ 100, pak jeho $ A $ 5.
Důležité: Všechno by mělo být absolutně odkazovaný. lookup_value může být relativní podle požadavku.
Zadejte jej jako vzorec pole. Po napsání vzorce stiskněte CTRL+SHIFT+ENTER, aby z něj byl vzorec pole.

Jak vidíte na gifu, vrací všechny shody z excelové tabulky.

Výhody:

• Vrátí s více shodnými hodnotami z tabulky aplikace Excel.
• Dynamický

Nevýhody:

• Je to příliš složité na to, aby to nový uživatel pochopil.
• Používá vzorec pole
• Je třeba definovat možný počet výstupů a použít tento vzorec jako vzorec vícebunkového pole (ne v Excelu 2019 a 365).
• Pomalý.

Zde se můžete podrobně seznámit s tímto vzorcem.

6: Vzorec VLOOKUP-CHOOSE Lookup Excel

Většina lidí tedy tvrdí, že není možné v aplikaci Excel vyhledávat hodnoty zleva od vyhledávací hodnoty pomocí funkce VLOOKUP. Omlouvám se, že to říkám, ale mýlí se. Můžeme vyhledat nalevo od vyhledávací hodnoty v Excelu pomocí funkce VLOOKUP pomocí funkce CHOOSE.

Obecný vzorec:

= VLOOKUP (lookup_value, CHOOSE ({1, 2}, lookup_range, req_range), 2, 0)

lookup_value : hodnota, kterou je třeba hledat

rozsah vyhledávání : rozsah, kde hledat lookup_value

req_range : rozsah, kde je požadována odpovídající hodnota

2 : druhý sloupec, číslo představující rozsah req

0 : hledejte přesnou shodu

V tomto vzorci v podstatě vytváříme virtuální tabulku uvnitř vzorce pomocí funkce CHOOSE. Funkce CHOOSE vytvoří tabulku dvou sloupců. První sloupec obsahuje rozsah vyhledávání a druhý sloupec obsahuje rozsah výsledků.

Výhody vyhledávacího vzorce VLOOKUP-CHOOSE:

• Můžete vyhledávat nalevo od vyhledávací hodnoty
• Rychle
• Snadný

Nevýhody:

• Funkce CHOOSE se používá jen zřídka. Uživatelé musí pochopit, jak funguje.

Zde se můžete dozvědět o tomto vyhledávacím vzorci.

Takže ano, lidi, toto jsou různé vyhledávací funkce a vzorce. To není vše. V Excelu může být mnohem více vyhledávacích vzorců, které lze vytvořit pomocí různých kombinací vzorců Excelu. Pokud máte nějaké speciální vyhledávací techniky, podělte se prosím v sekci komentáře níže. Zahrneme to do našeho článku s vaším jménem.

Doufám, že to bylo užitečné a informativní. Pokud máte ohledně tohoto článku jakékoli pochybnosti, zeptejte se mě v sekci komentáře níže.

10+ nových funkcí v Excelu 2019 a 365: Ačkoli funkce dostupné v Excelu 2016 a starších jsou dostačující pro zpracování jakéhokoli druhu výpočtu a automatizace, někdy jsou vzorce složité. Tyto druhy drobných, ale důležitých věcí řeší aplikace Excel 2019 a 365.

17 Věcí o VYHLEDÁVÁNÍ Excelu: VLOOKUP prostě není vyhledávací vzorec, je to víc než to. VLOOKUP má mnoho dalších schopností a lze jej použít k více účelům. V tomto článku prozkoumáme všechna možná použití funkce VLOOKUP.

10+ kreativních pokročilých grafů aplikace Excel k rozbalení vašeho řídicího panelu: Excel je účinný nástroj pro vizualizaci dat, který lze použít k vytváření ohromujících grafů v Excelu. Těchto 15 pokročilých grafů Excelu lze použít k okouzlení vašich kolegů a šéfů.

4 grafy úspěchů kreativního cíle vs. v aplikaci Excel: Grafy cíl vs úspěch jsou nejzákladnější a nejdůležitější grafy v každém podnikání. Proto je lepší dát je co nejtvořivěji. Tyto 4 kreativní grafy mohou vaši prezentaci rozhýbat.

Oblíbené články:

50 zkratek aplikace Excel pro zvýšení produktivity | Získejte rychleji svůj úkol. Těchto 50 klávesových zkratek vám umožní pracovat v Excelu ještě rychleji.

Jak používat funkci Excel VLOOKUP| Toto je jedna z nejpoužívanějších a nejoblíbenějších funkcí Excelu, která slouží k vyhledávání hodnot z různých rozsahů a listů.

Jak používat Excel Funkce COUNTIF| Pomocí této úžasné funkce spočítejte hodnoty s podmínkami. K počítání konkrétních hodnot nemusíte data filtrovat. Funkce Countif je nezbytná pro přípravu řídicího panelu.

Jak používat funkci SUMIF v aplikaci Excel | Toto je další základní funkce řídicího panelu. To vám pomůže shrnout hodnoty za konkrétních podmínek.