4 Funkce vyhledávání v Excelu pro efektivní vyhledávání tabulek

Většinu času je prohledávání tabulky Microsoft Excel docela snadné. Pokud nemůžete prohledávat pouze řádky a sloupce, můžete jej vyhledat pomocí Ctrl + F. Pokud pracujete s opravdu velkou tabulkou, můžete ušetřit spoustu času při používání jedné z těchto čtyř vyhledávacích funkcí.

Jakmile budete vědět, jak hledat v Excelu pomocí vyhledávání, nezáleží na tom, jak velké jsou vaše tabulky, vždy budete moci něco najít v Excelu!

1. Funkce VLOOKUP

Tato funkce umožňuje určit sloupec a hodnotu a vrátí hodnotu z odpovídajícího řádku jiného sloupce (pokud to nedává smysl, za okamžik to vyjasní). Dva příklady, kde byste to mohli udělat, jsou vyhledání příjmení zaměstnance podle jeho čísla zaměstnance nebo nalezení telefonního čísla zadáním příjmení.

Zde je syntaxe funkce:

= VLOOKUP ([lookup_value], [table_array], [col_index_num], [range_lookup])

• [lookup_value] je informace, kterou již máte. Například, pokud potřebujete vědět, ve kterém státě je město, bylo by to jméno města.
• [table_array] umožňuje určit buňky, ve kterých bude funkce hledat hodnoty vyhledávání a návratu. Při výběru rozsahu se ujistěte, že první sloupec zahrnutý v poli je sloupec, který bude obsahovat hodnotu vyhledávání!
• [col_index_num] je číslo sloupce, který obsahuje návratovou hodnotu.
• [range_lookup] je volitelný argument a trvá 1 nebo 0. Pokud zadáte 1 nebo vynecháte tento argument, funkce vyhledá zadanou hodnotu nebo nejbližší nejnižší číslo. Takže na obrázku níže VLOOKUP hledající SAT skóre 652 vrátí 646, protože je to nejbližší číslo v seznamu, které je menší než 652, a [range_lookup] výchozí 1.

Pojďme se podívat, jak byste to mohli použít. Tato tabulka obsahuje identifikační čísla, jméno a příjmení, město, stát a skóre SAT. Řekněme, že chcete najít skóre SAT osoby s příjmením “Zimy.” VLOOKUP to usnadňuje. Zde je vzorec, který byste použili:

= VLOOKUP ("Winters", C2: F101, 4, 0)

Protože skóre SAT jsou čtvrtým sloupcem nad sloupcem s posledním jménem, ​​argumentem indexu sloupce je 4. Pokud hledáte text, je dobré nastavit [range_lookup] na 0. Bez něj získáte špatné výsledky.

Zde je výsledek:

Vrátilo 651, skóre SAT patřící studentovi jménem Kennedy Winters, který je v řádku 92 (zobrazen ve výše uvedené vložce). Vyhledání jména by trvalo mnohem déle, než kdybychom rychle zadali syntaxi!

Poznámky k VLOOKUP

Když používáte VLOOKUP, je dobré si zapamatovat několik věcí. Ujistěte se, že první sloupec v rozsahu je sloupec, který obsahuje hodnotu vyhledávání. Pokud není v prvním sloupci, funkce vrátí nesprávné výsledky. Pokud jsou sloupce dobře uspořádány, neměl by to být problém.

Také mějte na paměti, že VLOOKUP vždy vrátí pouze jednu hodnotu. Kdybys použil “Gruzie” jako hodnota vyhledávání by to vrátilo skóre prvního studenta z Gruzie a vzhledem k tomu, že neexistuje náznak, že by ve skutečnosti byli dva studenti z Gruzie.

2. Funkce HLOOKUP

Pokud VLOOKUP najde odpovídající hodnoty v jiném sloupci, HLOOKUP najde odpovídající hodnoty v jiném řádku. Protože je obvykle nejjednodušší prohledávat záhlaví sloupců, dokud nenajdete ten pravý a použít filtr k nalezení toho, co hledáte, HLOOKUP se nejlépe používá, když máte opravdu velké tabulky nebo pracujete s hodnotami, které jsou organizovány podle času.

Zde je syntaxe funkce:

= HLOOKUP ([lookup_value], [table_array], [row_index_num], [range_lookup])

• [lookup_value] je hodnota, kterou znáte a chcete najít odpovídající hodnotu.
• [table_array] jsou buňky, ve kterých chcete hledat.
• [row_index_num] Určuje řádek, ze kterého bude vrácená hodnota pocházet.
• [range_lookup] je stejné jako ve verzi VLOOKUP, nechte pole prázdné, abyste získali nejbližší hodnotu, pokud je to možné, nebo zadejte 0 a hledejte pouze přesné shody.

Tato tabulka obsahuje řadu pro každý stát a skóre SAT v letech 2000–2014. Pomocí HLOOKUP můžete najít průměrné skóre v Minnesotě v roce 2013. Postupujeme takto:

= HLOOKUP (2013, A1: P51, 24)

Jak vidíte na obrázku níže, skóre je vráceno:

Minnesotans v roce 2013 průměroval skóre 1014. Všimněte si, že rok 2013 není v uvozovkách, protože je to číslo a ne řetězec. Také 24 pochází z Minnesoty v 24. řadě.

Poznámky k HLOOKUP

Stejně jako v případě VLOOKUP musí být hodnota vyhledávání v prvním řádku pole tabulky. Toto je zřídka problém s HLOOKUP, protože obvykle používáte pro vyhledávací hodnotu název sloupce. HLOOKUP také vrací pouze jednu hodnotu.

3-4. Funkce INDEX a MATCH

INDEX a MATCH jsou dvě různé funkce, ale když se používají společně, mohou prohledávat velkou tabulku mnohem rychleji. Obě funkce mají nevýhody, ale jejich kombinací budujeme na silných stránkách obou.

Nejprve však syntaxe obou funkcí:

= INDEX ([array], [row_number], [column_number])

• [pole] je pole, ve kterém budete hledat.
• [row_number] a [sloupec_čísla] lze použít k zúžení vyhledávání (za chvíli se na to podíváme.)

= MATCH ([lookup_value], [lookup_array], [match_type])

• [lookup_value] je hledaný výraz, který může být řetězec nebo číslo.
• [lookup_array] je pole, ve kterém bude aplikace Microsoft Excel hledat hledaný výraz.
• [match_type] je volitelný argument, který může být 1, 0 nebo -1. 1 vrátí největší hodnotu, která je menší nebo rovna hledanému výrazu. 0 vrátí pouze váš přesný výraz a -1 vrátí nejmenší hodnotu, která je větší nebo rovna hledanému výrazu.

Možná nebude jasné, jak budeme tyto dvě funkce používat společně, takže to rozložím zde. MATCH vezme hledaný výraz a vrátí odkaz na buňku. Na obrázku níže vidíte, že při hledání hodnoty 646 ve sloupci F vrátí MATCH 4.

Na druhou stranu INDEX dělá opak: vezme odkaz na buňku a vrátí v něm hodnotu. Zde vidíte, že když se vrátí šestá buňka sloupce Město, vrátí se INDEX “Ukotvení,” hodnota z řádku 6.

Budeme je kombinovat tak, že MATCH vrátí odkaz na buňku a INDEX použije tento odkaz pro vyhledání hodnoty v buňce. Řekněme, že si pamatujete, že byl student, jehož příjmení bylo Waters, a chcete vidět, jaké bylo skóre tohoto studenta. Zde je vzorec, který použijeme:

= INDEX (F: F, MATCH ("Waters", C: C, 0))

Zde si všimnete, že typ shody je nastaven na 0. Když hledáte řetězec, budete chtít použít. Zde je to, co dostaneme, když spustíme tuto funkci:

Jak můžete vidět z vložky, Owen Waters skóroval 1720, číslo, které se objeví, když spustíme funkci. To se nemusí zdát všechno užitečné, když se můžete podívat jen na několik sloupců, ale představte si, kolik času byste ušetřili, kdybyste to museli udělat 50krát v rozsáhlé databázové tabulce Excel Vs. Přístup - Může tabulku nahradit databázi? Excel Vs. Přístup - Může tabulku nahradit databázi? Jaký nástroj byste měli použít ke správě dat? Aplikace Access a Excel obsahují funkce filtrování dat, řazení a dotazování. Ukážeme vám, který z nich je pro vaše potřeby nejvhodnější. který obsahoval několik stovek sloupů!

Nechte začít vyhledávání v Excelu

Microsoft Excel má mnoho extrémně výkonných funkcí pro manipulaci s daty a výše uvedené čtyři pouze poškrábají povrch. Naučíte se, jak je používat, vám usnadní život.

Pokud opravdu chcete ovládat Microsoft Excel, můžete mít opravdu užitek z udržování Essential Excel Cheat Sheet Essential Microsoft Excel vzorců a funkcí Cheat Sheet Essential Microsoft Excel vzorců a funkcí Cheat Sheet Stáhněte si tento Excel vzorce cheat list, aby se zkratka přes světově nejoblíbenější tabulkový program. po ruce!

Obrázek Kredit: Cico / Shutterstock