Unlimited WordPress themes, graphics, videos & courses! Unlimited asset downloads! From $16.50/m
Advertisement
  1. Computer Skills
  2. Office
Computers

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

by
Difficulty:IntermediateLength:LongLanguages:
This post is part of a series called Spreadsheets for Finance.
Spreadsheets for Finance: Calculating Internal Rate of Return
Getting Started With Numbers on OS X

Czech (Čeština) translation by Ondřej Dokoupil (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

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

  1. Před zadáním vzorce, přejděte do listu zdroj dat.
  2. 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).
  3. Klepněte do Name Box nad sloupcem A (pole Name Box se nyní zobrazí v A4).
  4. Zadejte data a stiskněte klávesu Enter.
  5. 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á.

  1. 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ů.
  2. Stiskněte Ctrl-Shift-šipka dolů šipka (Command-Shift šipka dolů na počítačích Mac). Vybere aktuální data.
  3. Klikněte na nabídku Data a pak vyberte Named and protected ranges.
  4. 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.

Advertisement
Advertisement
Advertisement
Advertisement
Looking for something to help kick start your next project?
Envato Market has a range of items for sale to help get you started.