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

Ako získať dáta z tabuľky pomocou funkcie 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

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 MarketExcel scripts and plugins on Envato MarketExcel 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 vlookupunique identifiers for vlookupunique 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 vlookupsyntax of vlookupsyntax 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

  1. Pred zadaním vzorce, prejdite do listu zdroj údajov.
  2. 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).
  3. Kliknite do Name Box nad stĺpcom A (pole Name Box sa teraz zobrazí v A4).
  4. Zadajte dáta a stlačte Enter.
  5. Teraz môžete použiť názov dáta vo vzorci namiesto $ A $ 4: $ H $ 203.
Name box displaying a range nameName box displaying a range nameName 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á.

  1. 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.
  2. Stlačte Ctrl-Shift-šípka dole šípka (Command-Shift šípka dole na počítačoch Mac). Vyberie aktuálne dáta.
  3. Kliknite na ponuku Dáta a potom vyberte Named and protected ranges.
  4. V Name and protected ranges box vpravo zadajte dáta a kliknite na tlačidlo Done.
Data menu in Google SheetsData menu in Google SheetsData 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 functionentering the vlookup functionentering 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 dotmouse pointer on the autofill dotmouse 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 autofillworksheet with data after using autofillworksheet 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 definedname box with one column definedname 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 functionentering the match functionentering 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 functioncompleted match functioncompleted 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 functionentering the index functionentering 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.

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.