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:

• exportovať dáta z WordPress do Excelu
• analyzovať a načítať dáta z programu Excel pomocou PHP tried
• previesť tabuľku programu Excel na responzívnou HTML tabuľku
• prevádzať súbory programu Excel do .NET DataTable
• a oveľa viac

Videoukážka

How to Extract Data from a Spreadsheet using VLOOKUP, MATCH and INDEX

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.

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.

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.

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.
   

Zadanie vzorca

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

Zadajte vzorec:

=VLOOKUP(A5,data,8,FALSE)

Stlačte Enter.

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íž.

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

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ť:

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í.

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

=MATCH(A5,order_number,1)

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í).

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)

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.