4 funkce vyhledávání v Excelu k efektivnímu vyhledávání v tabulkách

4 funkce vyhledávání v Excelu k efektivnímu vyhledávání v tabulkách

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





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



1. Funkce VLOOKUP

Tato funkce vám umožňuje zadat sloupec a hodnotu a vrátí hodnotu z odpovídajícího řádku jiného sloupce (pokud to nedává smysl, bude to za chvíli jasné). Dva příklady, kde to můžete udělat, jsou vyhledat příjmení zaměstnance podle jeho čísla nebo najít telefonní číslo 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. Pokud například potřebujete vědět, ve kterém městě se město nachází, bude to název města.
  • [tabulka_pole] umožňuje určit buňky, ve kterých bude funkce hledat vyhledávací a návratové hodnoty. Při výběru rozsahu se ujistěte, že první sloupec zahrnutý ve vašem poli je ten, který bude obsahovat vaši vyhledávací hodnotu!
  • [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 tento argument vynecháte, 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 toho mohli využít. Tato tabulka obsahuje ID čísla, jména a příjmení, město, stát a výsledky SAT. Řekněme, že chcete zjistit SAT skóre osoby s příjmením „Winters“. VLOOKUP to usnadňuje. Zde je vzorec, který byste použili:



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

Protože skóre SAT je čtvrtým sloupcem nad sloupcem příjmení, 4 je argument indexu sloupce. Všimněte si toho, že když hledáte text, je vhodné nastavit [range_lookup] na 0. Bez toho můžete dosáhnout špatných výsledků.

Zde je výsledek:



Vrátilo 651, SAT skóre studenta jménem Kennedy Winters, který je v řádku 92 (zobrazeno na vložce výše). Hledání jména by trvalo mnohem déle, než rychlé zadání syntaxe!

Poznámky k VLOOKUP

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



Mějte také na paměti, že VLOOKUP vrátí pouze jednu hodnotu. Pokud byste jako vyhledávací hodnotu použili „Gruzii“, vrátilo by to skóre prvního studenta z Gruzie a bez jakéhokoli náznaku, že ve skutečnosti jsou dva studenti z Gruzie.

2. Funkce HLOOKUP

Kde 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 správný, a pomocí filtru najít to, 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 pro kterou chcete najít odpovídající hodnotu.
  • [tabulka_pole] jsou buňky, ve kterých chcete hledat.
  • [row_index_num] určuje řádek, ze kterého bude pocházet návratová hodnota.
  • [range_lookup] je stejný jako ve VLOOKUP, ponechte jej prázdný, abyste získali nejbližší hodnotu, pokud je to možné, nebo zadejte 0, abyste hledali pouze přesné shody.

Tato tabulka obsahuje řádek pro každý stát spolu se skóre SAT v letech 2000–2014. K vyhledání průměrného skóre v Minnesotě v roce 2013 můžete použít HLOOKUP. Uděláme to takto:

=HLOOKUP(2013, A1:P51, 24)

Jak vidíte na obrázku níže, skóre se vrací:

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

Tady je jak vypočítat vážený průměr v aplikaci Excel .

Poznámky k HLOOKUP

Stejně jako u VLOOKUP musí být vyhledávací hodnota v prvním řádku pole tabulky. Toto je zřídka problém s HLOOKUP, protože pro vyhledávací hodnotu obvykle používáte 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ž jsou použity společně, mohou prohledávat velkou tabulku mnohem rychleji. Obě funkce mají nevýhody, ale jejich kombinací navážeme na silné stránky obou.

Nejprve však syntaxe obou funkcí:

=INDEX([array], [row_number], [column_number])
  • [pole] je pole, ve kterém budete hledat.
  • [číslo_řádku] a [číslo_sloupce] lze použít ke 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.
  • [typ_ shody] je volitelný argument, který může být 1, 0 nebo -1. 1 vrátí největší hodnotu, která je menší nebo rovna vašemu 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 vašemu hledanému výrazu.

Možná nebude jasné, jak budeme tyto dvě funkce používat společně, a tak to vysvětlí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 vrací MATCH 4.

INDEX na druhé straně dělá opak: vezme odkaz na buňku a vrátí v ní hodnotu. Zde můžete vidět, že když je INDEX vyzván, aby vrátil šestou buňku sloupce Město, vrátí hodnotu „Anchorage“, hodnotu z řádku 6.

Co uděláme, je zkombinovat, aby MATCH vrátil odkaz na buňku a INDEX použil tento odkaz k vyhledání hodnoty v buňce. Řekněme, že si pamatujete, že tam byl student, jehož příjmení bylo Waters, a chcete vidět, jaké bylo skóre tohoto studenta. Zde je vzorec, který použijeme:

jak stáhnout webovou stránku offline
=INDEX(F:F, MATCH('Waters', C:C, 0))

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

Jak můžete vidět z vložky, Owen Waters zaznamenal 1720, číslo, které se objeví při spuštění funkce. To se nemusí zdát tak užitečné, když se stačí podívat na několik sloupců, ale představte si, kolik času byste ušetřili, kdybyste to museli udělat 50krát za velká databázová tabulka který obsahoval několik stovek sloupců!

Nechte hledání v Excelu začít

Microsoft Excel má mnoho extrémně výkonné funkce pro manipulaci s daty a výše uvedené čtyři jen poškrábají povrch. Naučit se je používat vám velmi usnadní život.

Pokud opravdu chcete ovládat Microsoft Excel, můžete mít prospěch z toho, že budete mít cheat list Essential Excel na dosah ruky!

Uznání: Cico/ Shutterstock

Podíl Podíl tweet E-mailem Canon vs. Nikon: Která značka fotoaparátu je lepší?

Canon a Nikon jsou dvě největší jména v oboru fotoaparátů. Ale která značka nabízí lepší řadu fotoaparátů a objektivů?

Číst dále Související témata
  • Produktivita
  • Tabulkový procesor
  • Microsoft Excel
  • Hledat triky
O autorovi Ian Buckley(216 publikovaných článků)

Ian Buckley je nezávislý novinář, hudebník, performer a video producent žijící v Berlíně v Německu. Když nepíše nebo není na jevišti, pohrává si s elektronikou nebo kódem pro kutily v naději, že se stane šíleným vědcem.

Více od Iana Buckleyho

Přihlaste se k odběru našeho zpravodaje

Připojte se k našemu zpravodaji a získejte technické tipy, recenze, bezplatné elektronické knihy a exkluzivní nabídky!

Kliknutím sem se přihlásíte k odběru