Jak získat data z tabulky pomocí funkce VLOOKUP, MATCH a INDEX

() translation by (you can also view the original English article)

Když potřebujete najít a získat sloupec dat z jedné tabulky a umístit jej do jiné, použijte funkci VLOOKUP. Tato funkce funguje v každé verzi aplikace Excel v systému Windows a Mac a také v tabulkách Google. To vám umožní najít data v jedné tabulce použitím některého identifikátoru, který je společný s jinou tabulkou. Dvě tabulky mohou být na různých listech nebo dokonce v různých sešitech. Existuje také funkce HLOOKUP, která dělá totéž, ale s daty uspořádány vodorovně, napříč řádky.

Funkce MATCH a INDEX je dobré použít, když se máte zájem o umístění konkrétního údaje, jako je sloupec nebo řádek, který obsahuje jméno osoby.

Prémiové služby

Než se dostaneme do funkcí aplikace Excel, věděli jste, že Envato Market má řadu Excel skriptů a pluginů, které umožňují provádět pokročilé funkce?

Například můžete:

Excel scripts and plugins on Envato Market — Excel skripty a pluginy na Envato Marketu

Videoukázka

Pokud chcete následovat postup tohoto tutoriálu při použití na vaše vlastní soubory v aplikaci Excel, můžete to tak udělat. Nebo pokud dáváte přednost, stáhněte si zip soubor pro tento kurz, který obsahuje ukázkový sešit s názvem vlookup example.xlsx.

Využití funkce VLOOKUP

Když funkce VLOOKUP najde identifikátor, který zadáte ve zdrojových datech, může pak najít jakoukoliv buňku v řádku a vrátí vám informaci. Všimněte si, že ve zdrojových datech, identifikátor musí být v prvním sloupci tabulky.

unique identifiers for vlookup — Jedinečným identifikátorem by mělo být pořadové číslo, které není dvakrát ve stejné tabulce.

Syntaxe

Zápis funkce VLOOKUP je:

=VLOOKUP(lookup value, table range, column number, [true/false])

Zde je co znamenají tyto argumenty:

Lookup value. Buňka, která má jedinečný identifikátor.
Table range. Oblast buněk, která má identifikátor v prvním sloupci, následovaná zbytkem dat v jiných sloupcích.
Column number. Číslo sloupce, který obsahuje data, které hledáte. Nenechte se tedy zmást písmenem sloupce. Na výše uvedeném obrázku jsou uskupeny ve sloupci 4.
True/False. Tento argument je optimální. True znamená, že přibližná shoda je přijatelna a False znamená, že je přijatelné pouze přesnou shodu.

Chceme najít množství prodeje z tabulky na obrázku výše, proto použijeme tyto argumenty.

syntax of vlookup — Syntaxe funkce VLOOKUP

Definice názevu oblasti k vytvoření absolutního odkazu

V Vlookup example.xlsx se podívejte na list Sales Amounts . Zadejte vzorec do B5, pak pomocí funkce AutoFill zkopírujte vzorec v listem dolů. To znamená, že oblast tabulky ve vzorci musí být absolutním odkazem. Dobrý způsob, jak to udělat, je definovat název pro oblast tabulky.

Definování názvu oblasti v aplikaci Excel

Před zadáním vzorce, přejděte do listu zdroj dat.
Vyberte všechny buňky od A4 (záhlaví pro Pořadí # sloupec) dolů po H203. Rychlý způsob, jak to udělat, je kliknout na A4, pak stiskněte klávesu Ctrl-Shift-End (Command-Shift-End na počítačích Mac).
Klepněte do Name Box nad sloupcem A (pole Name Box se nyní zobrazí v A4).
Zadejte data a stiskněte klávesu Enter.
Nyní můžete použít název data ve vzorci namísto $A$ 4: $H$ 203.

Name box displaying a range name — Pole name box obvykle zobrazí aktuální adresu buňky. Klepněte na něj a zadejte název pro definici rozsahu.

Definice názvu oblasti v tabulkách Google

V tabulkách Google, definice názvu je poněkud jiná.

Klepněte na záhlaví prvního sloupce ve zdrojových datech a stiskněte Ctrl-Shift šipka doprava (Command-Shift šipka doprava na počítačích Mac). Vybere řádek záhlaví u sloupců.
Stiskněte Ctrl-Shift-šipka dolů šipka (Command-Shift šipka dolů na počítačích Mac). Vybere aktuální data.
Klikněte na nabídku Data a pak vyberte Named and protected ranges.
V Name and protected ranges box vpravo zadejte data a klikněte na tlačítko Done.

Data menu in Google Sheets — Definování názvu oblasti v tabulkách Google

Zadání vzorce

Pro zadání vzorce, přejděte dna list Sales Amounts a klepněte na B5.

Zadejte vzorec:

=VLOOKUP(A5,data,8,FALSE)

Stiskněte Enter.

entering the vlookup function — Zadání funkce VLOOKUP

Výsledek by měl být 40. Pro vyplněním hodnot v sloupci směrem dolů, klepněte zpět na tlačítko B5, je-li to nezbytné. Umístěte ukazatel myši na bod automatického vyplňování v pravém dolním rohu buňky, tak aby se ukazatel myši změnil na zaměřovací kříž.

mouse pointer on the autofill dot — Když umístíte ukazatel myši na bod v pravém dolním rohu buňky, stane se automatický vyplňovacím křížkem.

Poklepejte na položku vyplnit hodnoty v sloupci směrem dolů.

worksheet with data after using autofill — Dvakrát klikněte Autovyplňovacím zaměřovacím křížke zkopírujte vzorec sloupce směrem dolů

Pokud chcete, můžete spustit funkci VLOOKUP v následujících několika sloupcích pro extrakci dalšího pole, jako je příjmení nebo stát.

Použití funkce MATCH

Funkce MATCH nevrátí hodnotu dat; zadáte hodnotu, kterou hledáte, a funkce vrátí umístění této hodnoty. Je to jako se ptát, kde je #135 Main Street a dostat odpověď, že je to 4. budova na ulici.

Zápis

Zápis funkce MATCH je:

=MATCH(lookup value, table range, [match type])

Argumenty jsou:

Lookup value. Buňka, která má jedinečný identifikátor.
Table range. Rozsah buněk, které hledáte.
Table range. Nepovinné. To je jak určení jak blízké kombinace chcete sledovat:

Další nejvyšší hodnota	-1	Hodnoty musí být v sestupném pořadí.
Cílová hodnota	0	Hodnoty mohou být v libovolném pořadí.
Další nejnižší hodnota	1	Výchozí typ. Hodnoty musí být v vzestupném pořadí.

Podobně jako u funkce VLOOKUP, pravděpodobně zjistíte že MATCH je snadnější k použití, pokud použijete název oblasti. Přejděte na položku Source Data list, vyberte si z B4 (záhlaví sloupce pro objednávku #) dolů, klepněte na tlačítko Name box nad sloupci A a vyvolejte hodnotu order_number. Všimněte si, že hodnoty jsou ve vzestupném pořadí.

name box with one column defined — Pojmenovanou oblastí může být jen jeden sloupec, jen jeden řádek, nebo dokonce jen jedna buňka

Přejděte na kartu Match v sešitu. Do B5 zadejte funkci MATCH:

=MATCH(A5,order_number,1)

entering the match function — Zadání funkce MATCH

Pokud jste nedefinovali název oblasti, nepište funkci jako:

=MATCH(A5,'Source Data'!A5:A203,0)

Ať tak či onak, můžete vidět, že je to v 14. pozice (takže 13 pořadí).

completed match function — Výsledek funkce MATCH

Použití funkce INDEX

Funkce INDEX je opakem funkce MATCH a je obdobou funkce VLOOKUP. Zadejte funkci který řádek a sloupec dat chcete, a řekne vám, co je hodnota v buňce.

Zápis

Zápis funkce INDEX je:

=INDEX(data range, row number, [column number])

Argumenty jsou:

Data range. Stejně jako další dvě funkce, to je tabulka dat.
Row number. Počet řádků dat, které nejsou nutně řádky sešitu. Jestli-že tabulka začíná na řádku 10 listu, pak je to řádek #1.
Column number. Číslo sloupce oblasti dat. Začíná-li rozsah hodnotou ve sloupci E, je to sloupec #1.

Dokumentace aplikace Excel vám řekne, že column number argument je nepovinný, ale číslo řádku je také nepovinné. Má-li oblast tabulky pouze jeden řádek nebo jeden sloupec, není nutné použít další argument.

Přejděte na Index list sešitu a klepněte na tlačítko v C6. Nejprve chceme zjistit, co je obsaženo v řádku 9, sloupci 3 v tabulce. Ve vzorci použijeme název oblasti, který jsme vytvořili dříve.

Zadejte vzorec:

=INDEX(data,A6,B6)

entering the index function — zadání funkce index

Vrátí příjmení zákazníka: Strevell. Změní hodnoty A6 a B6 a výsledek v C6 ukáže jiné výsledky (Všimněte si, že mnoho řádků má tytéž statusy a názvy produktů).

Závěr

Schopnost listu se podívat na jiný list a získat z něj data je skvělý nástroj. Touto cestou můžete mít jeden list, který obsahuje všechny údaje potřebné k mnoha účelům, pak extrahovat co potřebujete pro konkrétní instance.