VLOOKUP medzi dvoma zošitmi: jednoduchý sprievodca krok za krokom

Ak v Exceli pracujete s viacerými zošitmi, možno budete musieť použiť funkciu VLOOKUP na načítanie údajov z jedného zošita do druhého.

Funkcia VLOOKUP vyhľadá konkrétnu hodnotu v tabuľke v samostatnom zošite a vráti zodpovedajúcu hodnotu z rovnakého riadku. Syntax musí obsahovať úplný odkaz na druhý zošit a názov pracovného hárka.

Správne získanie referencií môže byť náročné, keď funkciu používate prvýkrát. Tento článok používa vzorové tabuľky, ktoré vám presne ukážu, ako zadávať vzorce.

Dozviete sa tiež niektoré vylepšenia funkcie VLOOKUP a niekoľko alternatív tejto funkcie.

Začnime!

Obsah

• Čo je VLOOKUP?
• Ako používať funkciu VLOOKUP medzi dvoma zošitmi
  • Vzorové údaje
  • Krok 1: Nechajte oba zošity otvorené
  • Krok 2: Začnite zadávať vzorec VLOOKUP
  • Krok 3: Ako odkazovať na druhý zošit
• 3 bežné chyby VLOOKUP a ako ich opraviť
  • 1. #N/A Chyba
  • 2. #Ref! Chyba čísla stĺpca
  • 3. Chyba úvodzoviek a hranatých zátvoriek
• 4 tipy na správu rôznych zošitov
• Ako aktualizovať údaje automaticky
• Pokročilé použitie funkcie VLOOKUP
• 2 hlavné alternatívy k VLOOKUP
  • 1. ZHODA INDEXU
  • 2. XLOOKUP
• Ako používať funkciu VLOOKUP vo viacerých hárkoch
  • Krok 1: Identifikujte komponenty
  • Krok 2: Nastavte svoje údaje
  • Krok 3: Použite funkciu VLOOKUP
  • (Voliteľné) Krok 4: Riešenie problémov s chybami #N/A
• Záverečné myšlienky

Čo je VLOOKUP?

Predpokladajme, že máte pracovný zošit predaja produktov a samostatný zošit s cenami každého produktu. Chcete získať prístup k cenám z druhého zošita a použiť ich vo výpočtoch v prvom zošite.

VLOOKUP je jedným z riešení tejto úlohy. Tento výraz znamená „Vertikálne vyhľadávanie . “

Je to vstavaná funkcia, ktorá vám umožňuje vyhľadať konkrétnu hodnotu v stĺpci a vrátiť zodpovedajúcu hodnotu v inom stĺpci na základe umiestnenia.

Dá sa použiť s:

• jednu tabuľku

• viac listov v tom istom zošite

• dva excelové zošity

Ak nemáte dodatočnú zložitosť viacerých zošitov, pozrite si tento návod na stránke .

Ako používať funkciu VLOOKUP medzi dvoma zošitmi

Funkcia VLOOKUP je užitočná najmä vtedy, keď potrebujete vyhľadať údaje vo viacerých hárkoch alebo zošitoch.

Tri hlavné kroky, ako to urobiť, sú:

1. Otvorte oba zošity.

2. Zadajte vzorec VLOOKUP v prvom zošite programu Excel.

3. Poskytnite plne kvalifikovaný odkaz na druhý zošit.

Vzorové údaje

Tento jednoduchý návod funguje na príkladoch založených na vzorových údajoch. Ak chcete pokračovať, vytvorte nový zošit s názvom „Predaj“ a zadajte tieto údaje do stĺpcov A a B:

• Jablká, 40

• Pomaranče, 30

• Hrušky, 70

• Banány, 20

Pridajte tretí stĺpec, ktorý bude obsahovať informácie o cene:

Vytvorte druhý excelový zošit s názvom „Produkt“ a premenujte Hárok1 na „Ceny“. Zadajte tieto údaje do stĺpca A a stĺpca B:

• Jablká, 1,50

• Pomaranče, 2:30

• Hrušky, 3,60

• Banány, 2.10

Údaje vyzerajú takto:

Na výpočet výnosu z predaja v prvej tabuľke musíme vyhľadať cenu v druhej tabuľke. VLOOKUP na záchranu!

Krok 1: Nechajte oba zošity otvorené

Pri použití vzorca VLOOKUP medzi dvoma zošitmi musia byť otvorené oba zošity. Ak je druhý zošit zatvorený, funkcia vráti chybu.

Môžete ich otvoriť samostatne alebo pomocou príkazu „Otvoriť“ v programe Excel ich otvoríte naraz. Existujú dva spôsoby, ako otvoriť ktorýkoľvek zošit:

• Prejdite na kartu Súbor a vyberte možnosť „Otvoriť“.

• Použite klávesovú skratku „Ctrl + O“ .

Keď máte oba zošity otvorené, môžete medzi nimi prepínať kliknutím na príslušnú kartu v spodnej časti okna Excelu.

Krok 2: Začnite zadávať vzorec VLOOKUP

Tu je základná syntax funkcie VLOOKUP:

=VLOOKUP(vyhľadávacia_hodnota, tabuľkové_pole, stĺpec_index_num, [vyhľadávanie_rozsahu])

• lookup_value : Hodnota, ktorú chcete vyhľadať v inom zošite.

• table_array : Rozsah buniek v inom zošite, ktorý obsahuje údaje, ktoré chcete načítať.

• col_index_num : Číslo stĺpca v rozsahu, ktorý obsahuje údaje, ktoré chcete načítať.

• range_lookup : Tento posledný parameter je voliteľný. Ak ho nastavíte na hodnotu TRUE alebo ho vynecháte, funkcia VLOOKUP vyhľadá približnú zhodu. Ak nastavíte hodnotu FALSE, funkcia VLOOKUP bude hľadať iba presnú zhodu.

Povedzme, že chcete vyhľadať cenu jabĺk v druhom zošite našich vzorových údajov. Takto sa vzorec rozpadne:

• Lookup_value je v bunke A2 prvého zošita.

• Pole tabuľky je v rozsahu buniek A1:B5 druhého zošita.

• Col_index_num je druhý stĺpec, ktorý obsahuje cenu.

Najkomplikovanejšou časťou vzorca VLOOKUP je správne odkazovanie na druhý zošit.

Krok 3: Ako odkazovať na druhý zošit

Na dokončenie vzorca VLOOKUP pre dva zošity budete musieť použiť plne kvalifikovaný odkaz, ktorý obsahuje tieto podrobnosti z druhého zošita:

• názov zošita

• názov listu

• rozsah buniek

To zaisťuje, že Excel presne vie, kde nájsť údaje, ktoré hľadáte. Plne kvalifikovaný odkaz sa používa pre argument table_array funkcie VLOOKUP.

Toto sú hodnoty pre vzorové údaje:

• názov zošita: Product.xlsx

• názov listu: Ceny

• rozsah buniek: $A1:$B5

Ak chcete vyhľadať cenu prvej položky, zadajte nasledujúci vzorec do bunky C2 prvého zošita:

=VLOOKUP(A2; [Product.xlsx]Ceny!$A$1:$B$5; 2; NEPRAVDA)

Tento obrázok ukazuje funkciu v akcii v riadku vzorcov pre prvú bunku:

Všimnite si, že názov zošita je uzavretý v hranatých zátvorkách a za názvom hárka nasleduje výkričník.

Ak názov súboru programu Excel obsahuje medzeru alebo špeciálne znaky, potom musíte celý zošit a hárok uzavrieť do jednoduchých úvodzoviek.

Vzorec používa absolútne hodnoty pre rozsah buniek (predpona $). To vám umožní skopírovať vzorec do zvyšku riadkov kliknutím na rukoväť výplne.

Tu sú výsledky VLOOKUP:

A je to! Teraz viete, ako efektívne používať VLOOKUP. Aby sme vám pomohli v prípade, že narazíte na nejaké chyby, v nasledujúcej časti sme zostavili tri z najbežnejších chýb funkcie VLOOKUP.

3 bežné chyby VLOOKUP a ako ich opraviť

Teraz, keď ste sa naučili používať funkciu VLOOKUP z iného zošita, môžete naraziť na niektoré bežné chyby.

Tu sú tri najčastejšie chyby a ako ich opraviť.

1. #N/A Chyba

Chyba #N/A sa vyskytne, keď sa vyhľadávacia hodnota nenájde v poli tabuľky. Ak chcete túto chybu opraviť, môžete vyskúšať nasledovné:

• Skontrolujte pravopis vyhľadávanej hodnoty.

• Uistite sa, že vyhľadávaná hodnota je v prvom stĺpci poľa tabuľky.

• Skontrolujte, či vyhľadávaná hodnota nie je naformátovaná ako text na dvoch hárkoch.

• Uistite sa, že argument range_lookup je nastavený na FALSE, aby ste našli presnú zhodu.

Vyššie uvedený príklad nedokázal nájsť zodpovedajúcu hodnotu pre Čerešne v druhom zošite.

2. #Ref! Chyba čísla stĺpca

# Ref! Chyba nastane, keď je argument col_index_num väčší ako počet stĺpcov v poli tabuľky.

Ak chcete túto chybu opraviť, skontrolujte, či je argument číslo_col_indexu menší alebo rovný počtu stĺpcov v zdrojovej tabuľke.

Na obrázku nižšie bol col_index_num nastavený na 3, ale v referenčnom rozsahu sú len dva stĺpce.

3. Chyba úvodzoviek a hranatých zátvoriek

Chyba úvodzoviek a hranatých zátvoriek nastane, keď vykonáte VLOOKUP s hodnotou obsahujúcou úvodzovky alebo hranaté zátvorky.

Ak chcete túto chybu opraviť, môžete vyskúšať nasledovné:

• Okolo hodnoty vyhľadávania použite dvojité úvodzovky.

• Na vloženie dvojitých úvodzoviek použite funkciu CHAR(34) .

• Ak hľadaná hodnota obsahuje úvodzovky alebo hranaté zátvorky, použite funkciu IFERROR na vrátenie prázdnej bunky.

Keď sa s týmito chybami zoznámite, je jednoduché ich opraviť! Aby sme vám pomohli stať sa profesionálom VLOOKUP, v ďalšej časti sme vám poskytli štyri tipy na správu rôznych zošitov.

4 tipy na správu rôznych zošitov

Pri práci s viacerými zošitmi je dôležité sledovať, ktorý zošit obsahuje aké údaje.

Tu sú naše štyri najlepšie tipy, ako si zachovať zdravý rozum:

1. Použite konvencie pomenovania, ktoré vám pomôžu zapamätať si, ktoré tabuľky programu Excel obsahujú aké údaje.

2. Vo vzorcoch použite absolútne odkazy, aby ste zabezpečili, že rozsah vyhľadávania bude odkazovať na správne bunky, aj keď vzorec presuniete alebo skopírujete na iné miesto alebo hárok programu Excel.

3. Použite funkciu IFERROR na spracovanie akýchkoľvek chýb #N/A, ktoré sa môžu vyskytnúť, ak sa vyhľadávacie hodnoty nenájdu v zdrojových údajoch.

4. Zvážte, aby sa nedali prepísať.

Ako aktualizovať údaje automaticky

Pri práci s viacerými súbormi programu Excel môže byť časovo náročné.

Ak chcete, aby sa váš vzorec VLOOKUP automaticky aktualizoval pri pridávaní alebo odstraňovaní údajov z iných zošitov, môžete použiť funkciu NEPRIAME.

Tu je postup:

• Vo vzorci VLOOKUP nahraďte argument poľa tabuľky funkciou INDIRECT.

• Vo funkcii NEPRIAME použite odkaz na bunku, ktorá obsahuje pole tabuľky v druhom zošite.

Ak to chcete dosiahnuť pomocou našich vzorových zošitov, postupujte podľa týchto krokov.

1. Použite jednu bunku v stĺpci D na zadanie odkazu na hárok „Ceny“ zošita „Produkty“ a rozsah buniek A1:B5 takto: [Produkt.xlsx]Ceny!A:B

2. V bunke C2 zošita „Predaj“ použite tento vzorec: =VLOOKUP(A2, NEPRIAME($D$1), 2, FALSE)

3. Skopírujte vzorec nadol (absolútne odkazy zabezpečujú, že bunka s tabuľkou zostane zachovaná).

Tieto akcie zabezpečia, že zmeny v pôvodnom hárku sa automaticky aktualizujú v pripojených bunkách.

Tento obrázok zobrazuje funkciu NEPRIAME v kombinácii s funkciou VLOOKUP:

Upozorňujeme, že tento príklad používa rozsah „A:B“ na odkazovanie na dva stĺpce. To znamená, že keď pridáte ďalšie produkty a ceny, vzorec bude naďalej fungovať v aktuálnom zošite.

Teraz, keď ste okúsili silu funkcie VLOOKUP, poďme sa pozrieť na niektoré pokročilé možnosti využitia funkcie v tejto sekcii!

Pokročilé použitie funkcie VLOOKUP

Príklady v tomto článku vrátia jednu zhodu pre každú vyhľadávanú hodnotu. Ak chcete pracovať s viacerými zhodami z iného zošita, môžete namiesto toho použiť vzorec poľa.

VLOOKUP môžete tiež kombinovať s ďalšími funkciami. Môžete ho napríklad vložiť do funkcie IF pre dodatočnú komplexnú analýzu.

Komplexná analýza zahŕňa techniky ako chí-kvadrát testy a párové T-testy. Tu je príklad použitia testu chí-kvadrát v Exceli:

Pokročilí používatelia využívajú funkciu VLOOKUP na porovnanie súborov údajov a identifikáciu nezhôd. Ďalšie spôsoby nájdete v tomto článku na .

Čo ak však z akéhokoľvek dôvodu nechcete používať funkciu VLOOKUP? Máte šťastie! Existuje niekoľko alternatív, ktorým sa budeme venovať v ďalšej časti.

2 hlavné alternatívy k VLOOKUP

Pokiaľ ide o vyhľadávanie hodnôt v rôznych hárkoch, často ide o funkciu VLOOKUP. Existuje však niekoľko alternatív, ktoré môžu byť v určitých situáciách užitočné.

Dva najbežnejšie sú:

• INDEX MATCH

• XLOOKUP

1. ZHODA INDEXU

INDEX a MATCH je výkonná kombinácia dvoch funkcií, ktoré možno použiť na vyhľadávanie hodnôt v tabuľke. Na rozdiel od funkcie VLOOKUP, ktorá vyžaduje, aby bola vyhľadávaná hodnota v stĺpci úplne vľavo, môže INDEX MATCH vyhľadať hodnoty v ľubovoľnom stĺpci.

Funguje to takto:

1. Funkcia INDEX vracia hodnotu zo zadaného rozsahu na základe čísla riadka a stĺpca.

2. Funkcia MATCH vráti polohu hľadanej hodnoty v určenom rozsahu.

Ak chcete použiť INDEX MATCH, musíte zadať rozsah, ktorý chcete prehľadávať, riadok, v ktorom chcete nájsť hodnotu vyhľadávania, a stĺpec, do ktorého chcete vrátiť výsledok.

Tu je príklad vzorca:

=INDEX(TableRange,MATCH(LookupHalue,LookupRange,0),ColumnIndex)

Ak to chcete použiť so vzorovými tabuľkami, použite tento vzorec:

=INDEX([Product.xlsx]Ceny!$B$1:$B$5, MATCH(A2, [Product.xlsx]Ceny!$A$1:$A$5, 0)))

Toto sa delí takto:

• Funkcia MATCH hľadá hodnotu v bunke A2 (názov produktu) v rozsahu A1:A5 v hárku „Ceny“ zošita „Produkt“.

• Funkcia INDEX vráti hodnotu z rozsahu B1:B5 v hárku „Ceny“ v zošite „Produkty“ na základe čísla riadku poskytnutého funkciou MATCH.

Tento obrázok ukazuje vzorec v akcii:

2. XLOOKUP

XLOOKUP je novšia funkcia, ktorá bola predstavená v Exceli 365 . Je to podobné ako VLOOKUP, ale s niektorými ďalšími funkciami.

Jednou z najväčších výhod XLOOKUP je, že dokáže vyhľadať hodnoty naľavo od vyhľadávacieho stĺpca.

Funguje to takto:

1. Funkcia XLOOKUP vracia hodnotu zo zadaného rozsahu na základe vyhľadávanej hodnoty.

2. Môžete určiť, či chcete presnú alebo približnú zhodu.

3. Môžete tiež určiť, čo sa má vrátiť, ak sa nenájde žiadna zhoda.

Ak chcete použiť XLOOKUP, musíte zadať hodnotu vyhľadávania, rozsah, ktorý chcete prehľadávať, a stĺpec, do ktorého chcete vrátiť výsledok.

Tu je príklad vzorca:

=XLOOKUP(LookupValue,LookupRange,ReturnRange,”Nenájdené”,0)

Ak to chcete použiť so vzorovými tabuľkami, použite tento vzorec:

=XLOOKUP(A2, [Product.xlsx]Ceny!$A$1:$A$5, [Product.xlsx]Ceny!$B$1:$B$5)

Toto sa delí takto:

• A2: Hodnota, ktorú chcete vyhľadať (názov produktu).

• [Product.xlsx]Ceny!$A$1:$A$5: Rozsah, v ktorom chcete vyhľadať hodnotu vyhľadávania (názvy produktov v hárku „Ceny“ v zošite „Produkty“).

• [Product.xlsx]Ceny!$B$1:$B$5: Rozsah, ktorý obsahuje vrátené hodnoty (ceny v hárku „Ceny“ v zošite „Produkty“).

Toto je vzorec v akcii:

Ako používať funkciu VLOOKUP vo viacerých hárkoch

Ak potrebujete vykonať VLOOKUP medzi dvoma zošitmi, možno budete musieť použiť VLOOKUP aj vo viacerých hárkoch.

Našťastie je postup veľmi podobný vykonaniu VLOOKUP medzi dvoma zošitmi.

Existujú štyri kroky.

Krok 1: Identifikujte komponenty

Namiesto zahrnutia poľa tabuľky ako pri jednom hárku budete musieť zahrnúť aj názov hárku.

Ak chcete napríklad vyhľadať hodnotu v rozsahu A2:B6 na hárku Jan v zošite Sales_reports.xlsx, váš vzorec bude vyzerať takto:

=VLOOKUP(A2,[Sales_reports.xlsx]Jan!$A$2:$B$6,2;FALSE)

Krok 2: Nastavte svoje údaje

Uistite sa, že sú vaše údaje usporiadané tak, aby sa dali ľahko odkazovať na viacero hárkov.

Možno budete chcieť zvážiť vytvorenie rôznych kariet pre špecifické referenčné údaje.

Krok 3: Použite funkciu VLOOKUP

Po nastavení údajov môžete použiť funkciu VLOOKUP na vyhľadávanie a získavanie údajov z iných hárkov.

Nezabudnite do vzorca zahrnúť názov hárka, ako je uvedené vyššie.

(Voliteľné) Krok 4: Riešenie problémov s chybami #N/A

Ak sa pri používaní funkcie VLOOKUP na viacerých hárkoch stretnete s chybami #N/A, dvakrát skontrolujte svoj vzorec, aby ste sa uistili, že ste správne odkázali na všetky potrebné hárky a rozsahy.

Môžete tiež skontrolovať, či vykonávate presnú zhodu, nie vyhľadávanie rozsahu.

Záverečné myšlienky

Naučili ste sa používať funkciu VLOOKUP na vyhľadávanie a získavanie údajov v dvoch tabuľkách. Táto všestranná funkcia uľahčuje vyhľadávanie hodnôt v jednom zošite na základe zodpovedajúcich hodnôt v inom zošite.

Sila funkcie VLOOKUP ďaleko presahuje rámec jednotlivých pracovných hárkov, siaha do rôznych zošitov na prepojenie a analýzu údajov.

Osvojenie si tejto funkcie je významným krokom k ovládnutiu Excelu, pretože otvára nové možnosti pre správu a interpretáciu vašich údajov.

Začlenením funkcií ako INDIRECT, INDEX-MATCH alebo XLOOKUP môžete ďalej zvýšiť flexibilitu a efektivitu vyhľadávania údajov.

Pokračujte v precvičovaní a skúmaní rozsiahlych možností Excelu; toto je len špička ľadovca. Šťastný lov údajov!