Excel

23 vecí, ktoré by ste mali vedieť o VLOOKUP

23 Things You Should Know About Vlookup

Ak chcete získať informácie z tabuľky, Excel VLOOKUP je skvelým riešením. Schopnosť dynamicky vyhľadávať a získavať informácie zo stola je pre mnohých používateľov zásadným faktorom a VLOOKUP nájdete všade.



A napriek tomu, aj keď sa VLOOKUP používa relatívne ľahko, existuje veľa vecí, ktoré sa môžu pokaziť. Jedným z dôvodov je, že VLOOKUP má veľkú chybu v dizajne - v predvolenom nastavení predpokladá, že ste v poriadku s približnou zhodou. Čo asi nie si.

To môže spôsobiť výsledky, ktoré vyzerať úplne normálne , aj keď sú úplne nesprávne . Ver mi, toto NIE je to, čo by si chcel skúsiť vysvetliť svojmu šéfovi, potom, čo už tvoju tabuľku poslala vedeniu :)





Nižšie si prečítajte, ako zvládnuť túto výzvu, a objavte ďalšie tipy na zvládnutie Funkcia Excel VLOOKUP .

1. Ako funguje VLOOKUP

VLOOKUP je funkcia na vyhľadávanie a získavanie údajov v tabuľke. Písmeno „V“ vo VLOOKUP znamená vertikálne, čo znamená, že údaje v tabuľke musia byť usporiadané vertikálne s údajmi v riadkoch. (Horizontálne štruktúrované údaje nájdete v časti HLOOKUP ).



Ak máte dobre štruktúrovanú tabuľku s vertikálne usporiadanými informáciami a stĺpec vľavo, pomocou ktorého môžete priradiť riadok, pravdepodobne môžete použiť funkciu VLOOKUP.

VLOOKUP vyžaduje, aby bola tabuľka štruktúrovaná tak, aby sa vyhľadávacie hodnoty zobrazovali v stĺpci úplne vľavo. Údaje, ktoré chcete získať (výsledné hodnoty), sa môžu zobraziť v ľubovoľnom stĺpci napravo. Keď používate funkciu VLOOKUP, predstavte si, že každý stĺpec v tabuľke je očíslovaný, začínajúc zľava. Ak chcete získať hodnotu z konkrétneho stĺpca, zadajte požadované číslo ako „index stĺpca“. V nižšie uvedenom príklade chceme vyhľadať e -mailovú adresu, takže pre index stĺpcov používame číslo 4:

Prehľad toho, ako VLOOKUP funguje

Vo vyššie uvedenej tabuľke sú ID zamestnancov v stĺpci 1 vľavo a e -mailové adresy v stĺpci 4 vpravo.

Ak chcete používať VLOOKUP, dodáte 4 informácie alebo „argumenty“:

  1. Hodnota, ktorú hľadáte ( lookup_value )
  2. Rozsah buniek, ktoré tvoria tabuľku ( table_array )
  3. Číslo stĺpca, z ktorého sa má získať výsledok ( stĺpec_index )
  4. Režim zápasu ( range_lookup , TRUE = približné, FALSE = presné)

Video: Ako používať VLOOKUP (3 min)

Ak vám stále nie je jasná základná myšlienka programu VLOOKUP, Jon Acampora z Excelu Campus má skvelé vysvetlenie na základe ponuky kávy Starbucks.

2. VLOOKUP iba ​​vyzerá správne

Asi najväčším obmedzením VLOOKUP je, že na načítanie údajov sa môže pozerať iba napravo.

To znamená, že VLOOKUP môže získať údaje iba zo stĺpcov napravo od prvého stĺpca v tabuľke. Keď sa vyhľadávacie hodnoty zobrazia v prvom (úplne vľavo) stĺpci, toto obmedzenie neznamená veľa, pretože všetky ostatné stĺpce sú už vpravo. Ak sa však vyhľadávací stĺpec niekde nachádza v tabuľke, budete môcť vyhľadávať hodnoty iba zo stĺpcov napravo od tohto stĺpca. Budete tiež musieť dodať menšiu tabuľku pre VLOOKUP, ktorá začína stĺpcom vyhľadávania.

VLOOKUP sa môže pozerať iba napravo od stĺpca vyhľadávanej hodnoty

Toto obmedzenie môžete prekonať pomocou INDEX a MATCH namiesto VLOOKUP.

3. VLOOKUP nájde prvý zápas

Ak v režime presnej zhody obsahuje vyhľadávací stĺpec duplicitné hodnoty, funkcia VLOOKUP bude zodpovedať iba prvej hodnote. V nižšie uvedenom príklade používame VLOOKUP na nájdenie krstného mena a VLOOKUP je nastavený tak, aby vykonával presnú zhodu. Aj keď sú v zozname dve „Janetiny“, VLOOKUP sa zhoduje iba s prvou:

VLOOKUP vždy nájde prvú zhodu

Poznámka: správanie sa môže zmeniť, ak je VLOOKUP použitý v režime približnej zhody. Tento článok podrobne vysvetľuje tému.

4. VLOOKUP nerozlišuje malé a veľké písmená

Pri vyhľadávaní hodnoty VLOOKUP nespracúva text veľkých a malých písmen inak. Pre VLOOKUP je kód produktu ako „PQRF“ identický s „pqrf“. V nižšie uvedenom príklade hľadáme veľké písmeno „JANET“, ale funkcia VLOOKUP nerozlišuje veľké a malé písmena, takže sa jednoducho zhoduje s výrazom „Janet“, pretože je to prvá zhoda, ktorú nájde:

VLOOKUP NIE JE citlivý na veľkosť písmen

Ponúkame tiež platené školenie pre VLOOKUP a INDEX/MATCH

5. VLOOKUP má dva režimy zhody

VLOOKUP má dva režimy prevádzky: presnú a približnú zhodu. Vo väčšine prípadov budete pravdepodobne chcieť použiť VLOOKUP v režime presnej zhody. To dáva zmysel, keď chcete vyhľadávať informácie na základe jedinečného kľúča nejakého druhu, napríklad informácie o produkte na základe kódu produktu alebo údaje o filme na základe názvu filmu:

VLOOKUP príklad presnej zhody - zodpovedajúce filmy

Vzorec v H6 na vyhľadanie roku na základe presnej zhody názvu filmu je:

 
= VLOOKUP (H4,B5:E9,2,FALSE) // FALSE = exact match

Približný režim však budete chcieť použiť v prípadoch, keď sa nezhodujete s jedinečným ID, ale skôr hľadáte „najlepšiu zhodu“ alebo „najlepšiu kategóriu“. Môžete napríklad hľadať poštovné podľa hmotnosti, daňové sadzby podľa príjmu alebo sadzbu provízie podľa mesačného predajného čísla. V týchto prípadoch pravdepodobne v tabuľke nenájdete presnú vyhľadávaciu hodnotu. Namiesto toho chcete, aby vám VLOOKUP poskytol najlepšiu zhodu pre danú hodnotu vyhľadávania.

VLOOKUP približný príklad zhody - provízie

Vzorec v D5 robí približnú zhodu, aby získal správnu províziu:

 
= VLOOKUP (C5,$G:$H,2,TRUE) // TRUE = approximate match

6. Upozornenie: VLOOKUP predvolene používa približnú zhodu

Presné a približné párovanie vo VLOOKUP je riadené štvrtým argumentom, nazývaným „vyhľadávanie rozsahu“. Tento názov nie je intuitívny, takže si budete musieť zapamätať, ako funguje.

Na presnú zhodu použite FALSE alebo 0. Na približnú zhodu nastavte range_lookup na TRUE alebo 1:

 
= VLOOKUP (value,table,column,TRUE) // approximate match = VLOOKUP (value,table,column,FALSE) // exact match

Štvrtý argument range_lookup je bohužiaľ voliteľný a predvolene je nastavený na hodnotu TRUE, čo znamená, že funkcia VLOOKUP v predvolenom nastavení vykoná približnú zhodu. Keď robíte približnú zhodu, VLOOKUP predpokladá, že tabuľka je zoradená a vykoná binárne vyhľadávanie. Ak počas binárneho vyhľadávania VLOOKUP nájde hodnotu presnej zhody, vráti hodnotu z tohto riadka. Ak však VLOOKUP narazí na hodnotu väčšiu ako je hodnota vyhľadávania, vráti hodnotu z predchádzajúceho riadka.

Je to nebezpečné predvolené nastavenie, pretože mnoho ľudí nevedomky necháva VLOOKUP v predvolenom režime, čo môže spôsobiť nesprávny výsledok keď tabuľka nie je zoradená.

Aby ste sa tomuto problému vyhli, použite ako 4. argument FALSE alebo nulu, ak chcete presnú zhodu.

7. Môžete prinútiť VLOOKUP, aby vykonal presnú zhodu

Ak chcete, aby VLOOKUP prinútil nájsť presnú zhodu, nastavte argument 4 (range_lookup) na hodnotu FALSE alebo nulu. Tieto dva vzorce sú ekvivalentné:

 
= VLOOKUP (value, data, column, FALSE) = VLOOKUP (value, data, column, 0)

V režime presnej zhody, keď VLOOKUP nemôže nájsť hodnotu, vráti #N/A. Toto je jasný dôkaz, že hodnota sa v tabuľke nenachádza.

8. Môžete povedať VLOOKUP, aby urobil približnú zhodu

Ak chcete použiť funkciu VLOOKUP v režime približnej zhody, vynechajte 4. argument (range_lookup) alebo ho zadajte ako PRAVDU alebo 1. Tieto 3 vzorce sú ekvivalentné:

 
= VLOOKUP (value, data, column) = VLOOKUP (value, data, column, 1) = VLOOKUP (value, data, column, TRUE)

Odporúčame, aby ste vždy nastavili argument range_lookup explicitne, aj keď to VLOOKUP nevyžaduje. Vďaka tomu budete mať vždy vizuálnu pripomienku očakávaného režimu zápasu.

Video: Ako používať VLOOKUP na približné zhody

9. Na približné zhody je potrebné údaje zoradiť

Ak používate zhodu v približnom režime, vaše údaje treba triediť vo vzostupnom poradí podľa vyhľadávacej hodnoty. V opačnom prípade môžete dostať nesprávne výsledky . Uvedomte si tiež, že niekedy môžu byť textové údaje pozri zoradené, aj keď nie je.

Felienne Hermans tu je skvelým príkladom tohto problému z chladnej analýzy, ktorú urobila v skutočnosti Enron tabuľky!

10. VLOOKUP môže zlúčiť údaje do rôznych tabuliek

Bežným prípadom použitia funkcie VLOOKUP je spájanie údajov z dvoch alebo viacerých tabuliek. Môžete mať napríklad údaje o objednávke v jednej tabuľke a údaje o zákazníkoch v druhej tabuľke a chcete do tabuľky objednávok preniesť niektoré údaje o zákazníkoch na analýzu:

VLOOKUP zlúči údaje spojením tabuliek -predtým

Pretože ID zákazníka existuje v oboch tabuľkách, môžete túto hodnotu použiť na vloženie požadovaných údajov pomocou funkcie VLOOKUP. Stačí nakonfigurovať VLOOKUP tak, aby používal hodnotu id v tabuľke 1 a údaje v tabuľke 2 s požadovaným stĺpcovým indexom. V nižšie uvedenom príklade používame dva vzorce VLOOKUP. Jeden vytiahne meno zákazníka a druhý vtiahne stav zákazníka.

VLOOKUP zlúči údaje spojením tabuliek -po

Odkaz: Príklad zlúčenia s VLOOKUP .

Video: Ako používať VLOOKUP na zlúčenie tabuliek .

11. VLOOKUP môže údaje klasifikovať alebo kategorizovať

Ak niekedy budete potrebovať použiť ľubovoľné kategórie na údajové záznamy, môžete to ľahko urobiť pomocou funkcie VLOOKUP pomocou tabuľky, ktorá slúži ako „kľúč“ na priradenie kategórií.

Klasickým príkladom sú známky, kde je potrebné priradiť známku na základe skóre:

VLOOKUP slúži na kategorizáciu - priradenie známok

V tomto prípade je VLOOKUP nakonfigurovaný na približnú zhodu, takže je dôležité, aby bola tabuľka zoradená vzostupne.

VLOOKUP však môžete použiť aj na priradenie ľubovoľných kategórií. V nižšie uvedenom príklade používame VLOOKUP na výpočet skupiny pre každé oddelenie pomocou malej tabuľky (s názvom „kľúč“), ktorá definuje zoskupenie.

VLOOKUP slúži na kategorizáciu - priradenie ľubovoľných skupín

12. Absolútne referencie robia VLOOKUP prenosnejším

V situáciách, keď plánujete získať informácie z viac ako jedného stĺpca v tabuľke, alebo ak potrebujete skopírovať a prilepiť VLOOKUP, môžete ušetriť čas a zhoršenie použitím absolútnych odkazov na vyhľadávaciu hodnotu a pole tabuľky. To vám umožní skopírovať vzorec a potom zmeniť iba indexové číslo stĺpca, aby ste pomocou rovnakého vyhľadávania získali hodnotu z iného stĺpca.

Pretože je napríklad hodnota vyhľadávania a pole tabuľky absolútne, môžeme vzorec skopírovať do stĺpcov, potom sa vrátiť a podľa potreby zmeniť index stĺpcov.

Absolútne referencie robia vzorce VLOOKUP prenosnejšími

13. Pomenované rozsahy uľahčujú čítanie VLOOKUPU (a sú prenosnejšie)

Absolútne rozsahy vyzerajú dosť škaredo, takže môžu byť vzorce VLOOKUP oveľa čistejšie a čitateľnejšie, pretože nahradíte absolútne referencie pomenovanými rozsahmi, ktoré sú automaticky absolútne.

Napríklad vo vyššie uvedenom príklade údajov o zamestnancovi môžete pomenovať vstupnú bunku „id“ a potom pomenovať údaje v tabuľke „údaje“. Vzorec môžete napísať takto:

Pomenované rozsahy uľahčujú čítanie vzorcov VLOOKUP

Tento vzorec je nielen ľahšie čitateľný, ale je aj prenosnejší, pretože pomenované rozsahy sú automaticky absolútne.

14. Vloženie stĺpca môže narušiť existujúce vzorce VLOOKUP

Ak máte v hárku existujúce vzorce VLOOKUP, vzorce sa môžu vložiť, ak do tabuľky vložíte stĺpec. Dôvodom je, že pevne vložené hodnoty indexov stĺpcov sa pri vkladaní alebo odstraňovaní stĺpcov nemenia automaticky.

V tomto prípade boli vyhľadávania poradia a predaja prerušené, keď bol medzi rok a poradie vložený nový stĺpec. Rok pokračuje v práci, pretože je vľavo od vloženého stĺpca:

Vloženie stĺpca do tabuľky môže prerušiť VLOOKUP

Aby ste sa tomuto problému vyhli, môžete vypočítať stĺpcový index podľa popisu v ďalších dvoch tipoch.

15. Na výpočet stĺpcového indexu môžete použiť ROW alebo COLUMN

Ak ste typ, ktorému po kopírovaní vzorca prekáža množstvo úprav, na generovanie dynamických stĺpcových indexov môžete použiť buď riadok alebo stĺpček. Ak získavate údaje z po sebe idúcich stĺpcov, tento trik vám umožní nastaviť jeden vzorec VLOOKUP a potom ho skopírovať bez akýchkoľvek zmien.

Napríklad pomocou nižšie uvedených údajov o zamestnancoch môžeme použiť funkciu COLUMN na generovanie dynamického stĺpcového indexu. Pre prvý vzorec v bunke C3 vráti funkcia COLUMN sama 3 (pretože stĺpec C je v pracovnom hárku tretí), takže jednoducho musíme jeden odčítať a skopírovať vzorec do:

Príklad použitia stĺpca na výpočet stĺpcového indexu pre VLOOKUP

Všetky vzorce sú zhodné a nie sú potrebné žiadne dodatočné úpravy.

Vzorec, ktorý používame, je tento:

 
= VLOOKUP (id,data, COLUMN ()-1,0)

16. Na plne dynamický index stĺpcov použite príkaz VLOOKUP + MATCH

Ak vezmeme vyššie uvedený tip o krok ďalej, môžete pomocou MATCH vyhľadať pozíciu stĺpca v tabuľke a vrátiť plne dynamický index stĺpca.

Toto sa niekedy nazýva obojsmerné vyhľadávanie, pretože hľadáte v riadku aj stĺpci.

Príkladom môže byť vyhľadanie predajov pre predajcu v konkrétnom mesiaci alebo vyhľadanie ceny za konkrétny produkt od konkrétneho dodávateľa.

Predpokladajme napríklad, že máte predaje za mesiac, rozdelené podľa predajcov:

VLOOKUP obojsmerné vyhľadávanie - ako vyhľadávať mesiac?

VLOOKUP môže ľahko nájsť predajcu, ale nemôže automaticky spracovať názov mesiaca. Ide o to, použiť funkciu MATCH namiesto statického stĺpcového indexu.

VLOOKUP obojsmerné vyhľadávanie pomocou MATCH na získanie stĺpcového indexu

Všimnite si toho, že dávame zhody rozsahu, ktorý zahŕňa všetky stĺpce v tabuľke, aby sa „synchronizovali“ čísla stĺpcov používaných VLOOKUP.

 
= VLOOKUP (H2,data, MATCH (H3,months,0),0)

Poznámka: Často uvidíte obojsmerné vyhľadávanie pomocou nástrojov INDEX a MATCH, čo je prístup, ktorý ponúka väčšiu flexibilitu a lepší výkon vo veľkých množinách dát. Pozrite sa, ako v tomto krátkom videu: Ako vykonať obojsmerné vyhľadávanie s INDEX a MATCH .

17. VLOOKUP umožňuje zástupné znaky pre čiastočnú zhodu

Kedykoľvek používate VLOOKUP v režime presnej zhody, máte možnosť použiť vo vyhľadávacej hodnote zástupné znaky. Môže sa to zdať neintuitívne, ale zástupné znaky vám umožňujú urobiť presnú zhodu na základe čiastočnej zhody :)

Excel poskytuje dva zástupné znaky: hviezdička (*) zodpovedá jednému alebo viacerým znakom a otáznik (?) Sa zhoduje s jedným znakom.

Môžete napríklad zadať hviezdičku priamo do bunky a označiť ju ako hodnotu vyhľadávania pomocou funkcie VLOOKUP. Na nižšie uvedenej obrazovke sme zadali „Mon*“ do H3, čo je pomenovaný rozsah nazývaný „val“. To spôsobí, že sa VLOOKUP bude zhodovať s názvom „Monet“.

VLOOKUP so zástupnými znakmi - priamo pomocou hviezdičky

V tomto prípade je vzorec jednoduchý:

 
= VLOOKUP (val,data,1,0)

Ak chcete, môžete upraviť vzorec VLOOKUP tak, aby používal vstavaný zástupný znak, ako v príklade nižšie, kde jednoducho spojíme hodnotu v H3 s hviezdičkou.

VLOOKUP so zástupnými znakmi - hviezdička je spojená s hodnotou vyhľadávania

V tomto prípade zreťazíme hviezdičku na hodnotu vyhľadávania vo funkcii VLOOKUP:

 
= VLOOKUP (val&'*',data,1,0)

Poznámka: Buďte opatrní pri zástupných znakoch a VLOOKUP. Poskytujú vám jednoduchý spôsob vytvorenia „lenivého zápasu“, ale tiež uľahčujú nájdenie nesprávneho zápasu.

18. Môžete zachytiť #N/A chyby a zobraziť priateľskú správu

V režime presnej zhody VLOOKUP zobrazí chybu #N/A, ak sa nenájde žiadna zhoda. Jedným spôsobom je to užitočné, pretože vám to definitívne povie, že vo vyhľadávacej tabuľke nie je žiadna zhoda. Chyby #N/A však nie sú zábavné, a preto existuje niekoľko spôsobov, ako môžete túto chybu zachytiť a namiesto toho zobraziť niečo iné.

Akonáhle začnete používať VLOOKUP, určite narazíte na chybu #N/A, ktorá nastane, keď VLOOKUP nedokáže nájsť zhodu.

Excel spočítať počet buniek

Je to užitočná chyba, pretože VLOOKUP vám jasne hovorí, že nemôže nájsť vyhľadávaciu hodnotu. V tomto prípade „Latte“ neexistuje ako nápoj v tabuľke, takže funkcia VLOOKUP vyvolá chybu #N/A

VLOOKUP zobrazuje chybu #N/A

Vzorec v tomto prípade je úplne štandardná presná zhoda:

 
= VLOOKUP (E6,data,2,0)

Chyby #N/A však nie sú zábavné, preto by ste sa tejto chyby mali chytiť a zobraziť priateľskejšiu správu.

Najľahší spôsob, ako zachytiť chyby pomocou funkcie VLOOKUP, je zabaliť VLOOKUP do funkcie IFERROR. IFERROR vám umožňuje „zachytiť“ akúkoľvek chybu a vrátiť výsledok podľa vášho výberu.

Aby ste túto chybu uviedli do pasce a namiesto chyby sa zobrazila správa „nenájdené“, môžete jednoducho zabaliť pôvodný vzorec do IFERROR a nastaviť požadovaný výsledok:

VLOOKUP #N/A Chyba v pasci s IFERROR

Ak sa nájde hodnota vyhľadávania, nevyskytne sa žiadna chyba a funkcia VLOOKUP vráti normálny výsledok. Tu je vzorec:

 
= IFERROR ( VLOOKUP (E6,data,2,0),'Not found')

19. Čísla ako text môžu spôsobiť chybu zhody

Niekedy môže tabuľka, s ktorou pracujete vo VLOOKUP, obsahovať čísla zadané ako text. Ak jednoducho získavate čísla ako text zo stĺpca v tabuľke, nevadí. Ak však prvý stĺpec tabuľky obsahuje čísla zadané ako text, zobrazí sa chyba #N/A, ak hodnota vyhľadávania nie je tiež text.

V nasledujúcom príklade sú ID pre tabuľku planét čísla zadané ako text , čo spôsobí, že VLOOKUP vráti chybu, pretože vyhľadávacia hodnota je číslo 3:

Čísla zadané ako textový príklad chyby VLOOKUP

Na vyriešenie tohto problému je potrebné zaistiť, aby vyhľadávacia hodnota a prvý stĺpec tabuľky boli rovnakého typu údajov (obe čísla alebo obidva texty).

Jedným zo spôsobov, ako to dosiahnuť, je previesť hodnoty vo vyhľadávacom stĺpci na čísla. Jednoduchý spôsob, ako to urobiť, je pridať nulu pomocou pasty special.

Ak nemáte jednoduchú kontrolu nad zdrojovou tabuľkou, môžete tiež upraviť vzorec VLOOKUP a skonvertovať hodnotu vyhľadávania na text zreťazením „“ na hodnotu takto:

 
= VLOOKUP (id&'',planets,2,0)

Čísla zadané ako riešenie chyby VLOOKUP pre text

Ak si nemôžete byť istí, kedy budete mať čísla a kedy budete mať text, môžete vyhovieť obom možnostiam tak, že zalamujete VLOOKUP do IFERROR a napíšete vzorec na zvládnutie oboch prípadov:

 
= IFERROR ( VLOOKUP (id,planets,3,0), VLOOKUP (id&'',planets,3,0))

20. VLOOKUP môžete použiť na nahradenie vnorených príkazov IF

Jedným zo zaujímavejších použití VLOOKUP je nahradiť vnorené príkazy IF. Ak ste niekedy postavili sériu vnorených IF, viete, že fungujú dobre, ale vyžadujú trochu hádky v zátvorkách. Musíte si tiež dávať pozor na poradie, v ktorom pracujete, aby nedošlo k zavedeniu logickej chyby.

Bežným použitím vnorených IF je napríklad priradenie známok na základe skóre nejakého druhu. V nižšie uvedenom príklade vidíte, že vzorec bol zostavený s vnorenými IF na to, aby to urobil, pričom ako sprievodcu použijete kláves známky vpravo.

Priradenie ročníkov dlhým vnoreným vzorcom IF

Úplne vnorený vzorec IF vyzerá takto:

 
= IF (C5<64,'F', IF (C5<73,'D', IF (C5<85,'C', IF (C5<95,'B','A'))))

To funguje dobre, ale všimnite si, že logika aj skutočné skóre sú zapracované priamo do vzorca. Ak sa z akéhokoľvek dôvodu zmení bodovanie, budete musieť starostlivo aktualizovať jeden vzorec a potom ho skopírovať do celej tabuľky.

Naproti tomu VLOOKUP môže priradiť rovnaké známky jednoduchým vzorcom. Všetko, čo musíte urobiť, je uistiť sa, že tabuľka kľúčov známky je nastavená pre VLOOKUP (t. J. Väčšinou je zoradená podľa skóre a obsahuje zátvorky na spracovanie všetkých skóre).

Po definovaní kľúča pomenovaného rozsahu pre tabuľku kľúčov stupňa je vzorec VLOOKUP veľmi jednoduchý a generuje rovnaké známky ako pôvodný vnorený vzorec IF:

Priradenie známok jednoduchým vzorcom VLOOKUP

S tabuľkou kľúčov triedy s názvom „kľúč“ máme veľmi jednoduchý vzorec VLOOKUP:

 
= VLOOKUP (C5,key,2,TRUE)

Príjemným bonusom tohto prístupu je, že logika aj skóre sú integrované priamo do tabuľky kľúčov ročníka. Ak sa niečo zmení, môžete jednoducho aktualizovať tabuľku priamo a vzorce VLOOKUP sa aktualizujú automaticky - nie sú potrebné žiadne úpravy.

Video: Ako nahradiť vnorené IF za VLOOKUP

21. VLOOKUP dokáže spracovať iba jedno kritérium

VLOOKUP môže podľa návrhu nájsť hodnoty iba na základe jedného kritéria, ktoré sa dodáva ako vyhľadávacia hodnota v prvom stĺpci tabuľky (vyhľadávací stĺpec).

To znamená, že nemôžete ľahko vykonávať činnosti, ako napríklad vyhľadať zamestnanca s priezviskom „Smith“ v časti „Účtovníctvo“, alebo vyhľadať zamestnanca na základe krstného mena a priezviska v samostatných stĺpcoch.

Existujú však spôsoby, ako toto obmedzenie prekonať. Riešením je vytvorenie pomocného stĺpca, ktorý zreťazí hodnoty z rôznych stĺpcov a vytvorí vyhľadávacie hodnoty, ktoré sa správajú ako viacero podmienok. Tu napríklad chceme nájsť oddelenie a skupinu pre zamestnanca, ale meno a priezvisko sa zobrazia v samostatných stĺpcoch. Ako môžeme vyhľadať oba naraz?

VLOOKUP Problém s viacerými kritériami - ako vyhľadať krstné meno a priezvisko?

Najprv pridajte pomocný stĺpec, ktorý jednoducho spojí mená a priezviská dohromady:

VLOOKUP viac kritérií krok 2 - pridajte pomocný stĺpec, ktorý spája viacero kritérií

Potom nakonfigurujte VLOOKUP tak, aby používal tabuľku obsahujúcu tento nový stĺpec a spojte mená a priezviská pre vyhľadávaciu hodnotu:

VLOOKUP viac kritérií, krok 3 - spojte kritériá a vytvorte vyhľadávaciu hodnotu

Konečný vzorec VLOOKUP vyhľadá mená a priezviská spoločne pomocou stĺpca pomocníka ako kľúča:

 
= VLOOKUP (C3&D3,data,4,0)

22. Dva VLOOKUPS sú rýchlejšie ako jeden VLOOKUP

Môže sa to zdať úplne šialené, ale keď máte veľký súbor údajov a potrebujete urobiť presnú zhodu, môžete VLOOKUP veľmi urýchliť pridaním ďalšieho VLOOKUP do vzorca!

Pozadie: predstavte si, že máte veľa údajov o objednávkach, povedzme viac ako 10 000 záznamov, a pomocou funkcie VLOOKUP vyhľadávate celkový počet objednávok na základe ID objednávky. Používate teda niečo také:

 
= VLOOKUP (order_id,order_data, 5, FALSE)

FALSE na konci prinúti VLOOKUP urobiť presnú zhodu. Chcete presnú zhodu, pretože existuje šanca, že sa číslo objednávky nenájde. V takom prípade spôsobí nastavenie presnej zhody chybu #N/A.

Problém je v tom, že presné zhody sú skutočne pomalé, pretože Excel musí postupovať lineárne cez všetky hodnoty, kým nenájde zhodu alebo nie.

Naopak, približné zhody sú bleskové, pretože Excel dokáže to, čo sa nazýva a binárne vyhľadávanie .

Problém s binárnym vyhľadávaním (t.j. VLOOKUP v režime približnej zhody) je, že VLOOKUP môže vrátiť nesprávny výsledok, ak sa nenájde hodnota. Horšie je, že výsledok môže vyzerať úplne normálne, takže jeho odhalenie môže byť veľmi ťažké.

Riešením je použiť VLOOKUP dvakrát, oba razy v režime približnej zhody. Prvá inštancia jednoducho skontroluje, či hodnota skutočne existuje. Ak je to tak, spustí sa ďalšie VLOOKUP (opäť v režime približnej zhody) na načítanie požadovaných údajov. Ak nie, môžete vrátiť akúkoľvek hodnotu, ktorou chcete označiť, že sa výsledok nenašiel.

Konečný vzorec vyzerá takto:

 
= IF ( VLOOKUP (order_id,order_data,1,TRUE)=order_id,  VLOOKUP (order_id,order_data,5,TRUE), 'Missing')

Tento prístup som sa naučil od Charlesa Williamsa z FastExcel, ktorý má fantastický a podrobný článok tu: Prečo sú 2 VLOOKUPS lepšie ako 1 VLOOKUP .

Poznámka: Aby ste mohli použiť tento trik, musia byť vaše údaje zoradené. Je to jednoducho spôsob, ako sa chrániť pred chýbajúcou hodnotou vyhľadávania pri zachovaní rýchleho vyhľadávania.

23. INDEX a MATCH spoločne dokážu všetko, čo dokáže VLOOKUP, a ďalšie

Ak sledujete Excel online, pravdepodobne narazíte na Diskusia VLOOKUP vs. INDEX/MATCH. Hádka sa môže prekvapivo vyhrotiť :)

Podstata je v tom: INDEX + MATCH môže robiť všetko, čo VLOOKUP (a HLOOKUP) dokáže, s oveľa väčšou flexibilitou a za cenu trochu väčšej zložitosti. Tí, ktorí sú za INDEX + MATCH, budú tvrdiť (veľmi rozumne), že by ste sa mohli začať učiť INDEX a MATCH, pretože vám to na konci poskytne lepšiu sadu nástrojov.

Argument proti INDEX + MATCH je, že vyžaduje dve funkcie namiesto jednej, takže je pre používateľov (obzvlášť nových používateľov) vo svojej podstate zložitejšie učiť sa a ovládať ich.

Moje dva centy sú, že ak často používate Excel, budete sa chcieť naučiť používať INDEX a MATCH. Je to veľmi silná kombinácia.

Ale tiež si myslím, že by ste sa mali naučiť VLOOKUP, s ktorým sa stretnete všade, často v pracovných listoch, ktoré dedíte po ostatných. V jednoduchých situáciách VLOOKUP zvládne prácu bez problémov a bez problémov.

Ak sa chcete dozvedieť viac o INDEX a MATCH, pozri tento článok .

Autor Dave Bruns


^