Ako získať dáta z tabuľky pomocou funkcie VLOOKUP, MATCH a INDEX

Slovak (Slovenčina) translation by Ondřej Dokoupil (you can also view the original English article)

Keď potrebujete nájsť a získať stĺpec dát z jednej tabuľky a umiestniť ho do inej, použite funkciu VLOOKUP. Táto funkcia funguje v každej verzii programu Excel v systéme Windows a Mac a tiež v tabuľkách Google. To vám umožní nájsť dáta v jednej tabuľke použitím niektorého identifikátora, ktorý je spoločný s inou tabuľkou. Dve tabuľky môžu byť na rôznych listoch alebo dokonca v rôznych zošitoch. Existuje aj funkcia HLOOKUP, ktorá robí to isté, ale s dátami usporiadané vodorovne, naprieč riadky.

Funkcia MATCH a INDEX je dobré použiť, keď sa máte záujem o umiestnenie konkrétneho údaje, ako je stĺpec alebo riadok, ktorý obsahuje meno osoby.

Prémiové služby

Než sa dostaneme do funkcií programu Excel, vedeli ste, že Envato Market má rad Excel skriptov a pluginov, ktoré umožňujú vykonávať pokročilé funkcie?

Napríklad môžete:

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

Videoukážka

Pokiaľ chcete nasledovať postup tohto tutoriálu pri použití na vaše vlastné súbory v programe Excel, môžete to tak urobiť. Využitie funkcie VLOOKUP

Keď funkcia VLOOKUP nájde identifikátor, ktorý zadáte v zdrojových dátach, môže potom nájsť akúkoľvek bunku v riadku a vráti vám informáciu.

Všimnite si, že v zdrojových dátach, identifikátor musí byť v prvom stĺpci tabuľky. Všimnite si, že v zdrojových dátach, identifikátor musí byť v prvom stĺpci tabuľky.

unique identifiers for vlookup — Jedinečným identifikátorom by malo byť poradové číslo, ktoré nie je dvakrát v rovnakej tabuľke.

Syntax

Zápis funkcie VLOOKUP je:

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

Tu je čo znamenajú tieto argumenty:

Lookup value. Bunka, ktorá má jedinečný identifikátor.
Table range. Oblasť buniek, ktorá má identifikátor v prvom stĺpci, nasledovaná zvyškom dát v iných stĺpcoch.
Column number. Oblasť buniek, ktorá má identifikátor v prvom stĺpci, nasledovaná zvyškom dát v iných stĺpcoch. Nenechajte sa teda zmiasť písmenom stĺpca. Na vyššie uvedenom obrázku sú zoskupené v stĺpci 4.
True/False. Toto tvrdenie je optimálny. True znamená, že približná zhoda je prijateľný a False znamená, že je prijateľné iba presnú zhodu.

Chceme nájsť množstvo predaja z tabuľky na obrázku vyššie, preto použijeme tieto argumenty.

syntax of vlookup — Syntax funkcie VLOOKUP

Definícia Názov oblasti k vytvoreniu absolútneho odkazu

V VLOOKUP example.xlsx sa pozrite na list Sales Amounts. Zadajte vzorec do B5, potom pomocou funkcie AutoFill skopírujte vzorec v listom dole. To znamená, že oblasť tabuľky vo vzorci musí byť absolútnym odkazom. Dobrý spôsob, ako to urobiť, je definovať názov pre oblasť tabuľky.

Definovanie názvu oblasti v programe Excel

Pred zadaním vzorce, prejdite do listu zdroj údajov.
Vyberte všetky bunky od A4 (hlavičky pre Poradie # stĺpec) dole po H203. Rýchly spôsob, ako to urobiť, je kliknúť na A4, stlačte kláves Ctrl-Shift-End (Command-Shift-End na počítačoch Mac).
Kliknite do Name Box nad stĺpcom A (pole Name Box sa teraz zobrazí v A4).
Zadajte dáta a stlačte Enter.
Teraz môžete použiť názov dáta vo vzorci namiesto $ A $ 4: $ H $ 203.

Name box displaying a range name — Pole name box zvyčajne zobrazí aktuálnu adresu bunky. Kliknite naň a zadajte názov pre definíciu rozsahu.

Definícia názvu oblasti v tabuľkách Google

V tabuľkách Google, definícia názvu je trochu iná.

Kliknite na hlavičku prvého stĺpca v zdrojových dátach a stlačte Ctrl-Shift šípka doprava (Command-Shift šípka doprava na počítačoch Mac). Vyberie riadok hlavičky u stĺpcov.
Stlačte Ctrl-Shift-šípka dole šípka (Command-Shift šípka dole na počítačoch Mac). Vyberie aktuálne dáta.
Kliknite na ponuku Dáta a potom vyberte Named and protected ranges.
V Name and protected ranges box vpravo zadajte dáta a kliknite na tlačidlo Done.

Data menu in Google Sheets — Definovanie názvu oblasti v tabuľkách Google

Zadanie vzorca

Pre zadanie vzorca, prejdite dna list Sales Amounts a kliknite na B5.

Zadajte vzorec:

=VLOOKUP(A5,data,8,FALSE)

Stlačte Enter.

entering the vlookup function — Zadanie funkcie VLOOKUP

Výsledok by mal byť 40. Pre vyplnením hodnôt v stĺpci smerom dole, kliknite späť na tlačidlo B5, ak je to nevyhnutné. Umiestnite ukazovateľ myši na bod automatického vypĺňania v pravom dolnom rohu bunky, tak aby sa ukazovateľ myši zmenil na zameriavací kríž.

mouse pointer on the autofill dot — Keď umiestnite kurzor myši na bod v pravom dolnom rohu bunky, stane sa automatický vyplňovacom krížikom.

Dvakrát kliknite na položku vyplniť hodnoty v stĺpci smerom dole.

worksheet with data after using autofill — Dvakrát kliknite Autovyplňovacím zameriavacím krížikom skopírujte vzorec stĺpca smerom nadol

Ak chcete, môžete spustiť funkciu VLOOKUP v nasledujúcich niekoľkých stĺpcoch pre extrakciu ďalšieho poľa, ako je priezvisko alebo stáť.

Použitie funkcie MATCH

Funkcia MATCH nevráti hodnotu dát; zadáte hodnotu, ktorú hľadáte, a funkcia vráti umiestnenie tejto hodnoty. Ked potrebujete najst a zisk stĺpec dať z konaj tabuľky a umiestniť ho do inej, použite funkciám VLOOKUP.

Zápis

Zápis funkcie MATCH je:

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

Argumenty sú:

Lookup value. Bunka, ktorá má jedinečný identifikátor.
Table range. Rozsah buniek, ktoré hľadáte.
Table range. Nepovinné. To je ako určenie ako blízkej kombinácia chcete sledovať:

Ďalší najvyššia hodnota	-1	Hodnoty musia byť v zostupnom poradí.
Target value	0	Hodnoty môžu byť v ľubovoľnom poradí.
Ďalší najnižšia hodnota	1	Predvolený typ. Hodnoty musia byť v vzostupnom poradí.

Podobne ako pri funkcii VLOOKUP, pravdepodobne zistíte, že MATCH je jednoduchšie na použitie, ak použijete názov oblasti. Prejdite na položku Source Data list, vyberte si z B4 (záhlavie stĺpca pre objednávku #) dole, kliknite na tlačidlo Name box nad stĺpci A a vyvolajte hodnotu order_number. Všimnite si, že hodnoty sú vo vzostupnom poradí.

name box with one column defined — Pomenovanú oblastí môže byť len jeden stĺpec, len jeden riadok, alebo dokonca len jedna bunka

Prejdite na kartu Match v zošite. Do B5 zadajte funkciu MATCH:

=MATCH(A5,order_number,1)

entering the match function — Zadanie funkcie MATCH

Ak ste nedefinovali názov oblasti, nepíšte funkciu ako:

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

Či tak alebo onak, môžete vidieť, že je to v 14. pozície (takže 13 poradí).

completed match function — Výsledok funkcie MATCH

Použitie funkcie INDEX

Funkcia INDEX je opakom funkcie MATCH a je obdobou funkcie VLOOKUP. Zadajte funkciu ktorý riadok a stĺpec dát chcete, a povie vám, čo je hodnota v bunke.

Zápis

Zápis funkcie INDEX je:

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

Argumenty sú:

Data range. Rovnako ako ďalšie dve funkcie, to je tabuľka dát.
Row number. Počet riadkov dát, ktoré nie sú nevyhnutne riadky zošita. Ak-že tabuľka začína na riadku 10 listu, potom je to riadok # 1.
Column number. Číslo stĺpce oblasti dát. Začína pokiaľ rozsah hodnotou v stĺpci E, je to stĺpec # 1.

Dokumentácia programu Excel vám povie, že column number argument je nepovinný, ale číslo riadku je tiež nepovinné. Ak má oblasť tabuľky iba jeden riadok alebo jeden stĺpec, nie je nutné použiť ďalší argument.

Prejdite na Index hárok zošita a kliknite na tlačidlo v C6. Najprv chceme zistiť, čo je obsiahnuté v riadku 9, stĺpci 3 v tabuľke. Vo vzorci použijeme názov oblasti, ktorý sme vytvorili skôr.

Zadajte vzorec:

=INDEX(data,A6,B6)

entering the index function — zadania funkcie index

Vráti priezvisko zákazníka: Strevell. Zmení hodnoty A6 a B6 a výsledok v C6 ukáže iné výsledky (Všimnite si, že veľa riadkov má tie isté statusy a názvy produktov).

Záver

Schopnosť listu sa pozrieť na iný list a získať z neho dáta je skvelý nástroj. Touto cestou môžete mať jeden list, ktorý obsahuje všetky údaje potrebné na mnohé účely, potom extrahovať čo potrebujete pre konkrétnu inštancie.