Všetky tajomstvá funkcie Excel VLOOKUP na vyhľadanie údajov v tabuľke a ich extrahovanie do inej

2024 Autor: Malcolm Clapton | [email protected]. Naposledy zmenené: 2023-12-17 04:09

Po prečítaní článku sa naučíte nielen nájsť údaje v excelovskej tabuľke a extrahovať ich do inej, ale aj techniky, ktoré možno použiť v spojení s funkciou VLOOKUP.

Pri práci v Exceli je veľmi často potrebné nájsť údaje v jednej tabuľke a extrahovať ich do inej. Ak stále neviete, ako na to, po prečítaní článku sa nielen naučíte, ako to urobiť, ale tiež zistíte, za akých podmienok môžete zo systému vyžmýkať maximálny výkon. Zvažuje sa väčšina veľmi účinných techník, ktoré by sa mali používať v spojení s funkciou VLOOKUP.

Aj keď funkciu VLOOKUP používate už roky, potom s vysokou pravdepodobnosťou bude tento článok pre vás užitočný a nenechá vás ľahostajnými. Napríklad ako IT špecialista a potom vedúci v IT používam VLOOKUP už 15 rokov, no so všetkými nuansami som sa dokázal vysporiadať až teraz, keď som začal učiť ľudí Excel na profesionálnej báze.

VLOOKUP je skratka pre vvertikálne NSinšpekcia. Podobne aj VLOOKUP – Vertikálne VYHĽADÁVANIE. Samotný názov funkcie nám napovedá, že vyhľadáva v riadkoch tabuľky (vertikálne - iteruje cez riadky a opravuje stĺpec), a nie v stĺpcoch (horizontálne - iteruje cez stĺpce a opravuje riadok). Treba si uvedomiť, že VLOOKUP má sestru – škaredé káčatko, ktoré sa nikdy nestane labuťou – to je funkcia HLOOKUP. HLOOKUP, na rozdiel od VLOOKUP, vykonáva horizontálne vyhľadávanie, ale koncept Excelu (a vlastne koncept organizácie údajov) znamená, že vaše tabuľky majú menej stĺpcov a oveľa viac riadkov. Preto potrebujeme hľadať podľa riadkov mnohokrát častejšie ako podľa stĺpcov. Ak v Exceli príliš často používate funkciu HLO, je pravdepodobné, že ste v tomto živote niečomu nerozumeli.

Syntax

Funkcia VLOOKUP má štyri parametre:

= VLOOKUP (;; [;]), tu:

- požadovaná hodnota (zriedkavo) alebo odkaz na bunku obsahujúcu požadovanú hodnotu (prevažná väčšina prípadov);

- odkaz na rozsah buniek (dvojrozmerné pole), v ktorých PRVOM (!) stĺpci sa bude hľadať hodnota parametra;

- číslo stĺpca v rozsahu, z ktorého bude vrátená hodnota;

- toto je veľmi dôležitý parameter, ktorý odpovedá na otázku, či je prvý stĺpec rozsahu zoradený vzostupne. Ak je pole zoradené, zadáme hodnotu TRUE alebo 1, v opačnom prípade - FALSE alebo 0. Ak je tento parameter vynechaný, predvolená hodnota je 1.

Stavím sa, že mnohí z tých, ktorí poznajú funkciu VLOOKUP ako šupinatá, sa po prečítaní popisu štvrtého parametra môžu cítiť nepríjemne, pretože sú zvyknutí vidieť ho v trochu inej podobe: zvyčajne hovoria o presnej zhode, keď vyhľadávanie (FALSE alebo 0) alebo skenovanie rozsahu (TRUE alebo 1).

Teraz sa musíte napnúť a prečítať si nasledujúci odsek niekoľkokrát, kým nepocítite význam toho, čo bolo povedané až do konca. Každé slovo je tam dôležité. Príklady vám pomôžu prísť na to.

Ako presne funguje vzorec VLOOKUP?

• Typ vzorca I. Ak je posledný parameter vynechaný alebo je špecifikovaný rovný 1, potom funkcia VLOOKUP predpokladá, že prvý stĺpec je zoradený vzostupne, takže vyhľadávanie sa zastaví v riadku, ktorý bezprostredne pred riadkom obsahujúcim hodnotu väčšiu ako požadovaná … Ak sa takýto reťazec nenájde, vráti sa posledný riadok rozsahu.

  

• Formula II. Ak je posledný parameter špecifikovaný ako 0, potom VLOOKUP prehľadá prvý stĺpec poľa postupne a okamžite zastaví vyhľadávanie, keď sa nájde prvá presná zhoda s parametrom, inak sa zobrazí chybový kód # N / A (# N / A). sa vráti.

  

Pracovné postupy vzorcov

VPR typu I

VPR typ II

Dôsledky vzorcov tvaru I

1. Vzorce možno použiť na rozdelenie hodnôt do rôznych rozsahov.
2. Ak prvý stĺpec obsahuje duplicitné hodnoty a je zoradený správne, vráti sa posledný z riadkov s duplicitnými hodnotami.
3. Ak hľadáte hodnotu, ktorá je zjavne väčšia, než môže obsahovať prvý stĺpec, potom môžete ľahko nájsť posledný riadok tabuľky, čo môže byť dosť cenné.
4. Toto zobrazenie vráti chybu # N / A iba v prípade, že nenájde hodnotu menšiu alebo rovnú požadovanej hodnote.
5. Je dosť ťažké pochopiť, že vzorec vracia nesprávne hodnoty, ak vaše pole nie je zoradené.

Dôsledky pre vzorce II

Ak sa požadovaná hodnota vyskytuje viackrát v prvom stĺpci poľa, vzorec vyberie prvý riadok na následné načítanie údajov.

Výkon VLOOKUP

Dosiahli ste vrchol článku. Zdalo by sa, aký je rozdiel, ak ako posledný parameter uvediem nulu alebo jednotku? V zásade každý uvádza, samozrejme, nulu, pretože je to celkom praktické: nemusíte sa starať o triedenie prvého stĺpca poľa, okamžite vidíte, či sa hodnota našla alebo nie. Ak však máte na svojom hárku niekoľko tisíc vzorcov VLOOKUP, všimnete si, že VLOOKUP II je pomalý. Zároveň si zvyčajne každý začne myslieť:

• Potrebujem výkonnejší počítač;
• Potrebujem rýchlejší vzorec, napríklad veľa ľudí vie o INDEX + MATCH, ktorý je vraj rýchlejší o úbohých 5-10%.

A len málo ľudí si myslí, že akonáhle začnete používať VLOOKUP typu I a zabezpečíte, aby bol prvý stĺpec zoradený akýmkoľvek spôsobom, rýchlosť VLOOKUP sa zvýši 57-krát. Píšem slovom - PÄŤDESAŤ SEDEM KRÁT! Nie 57 %, ale 5 700 %. Túto skutočnosť som si overil celkom spoľahlivo.

Tajomstvo takejto rýchlej práce spočíva v tom, že na triedené pole možno aplikovať mimoriadne efektívny vyhľadávací algoritmus, ktorý sa nazýva binárne vyhľadávanie (metóda halvingu, metóda dichotómie). VLOOKUP typu I ho teda použije a VLOOKUP typu II vyhľadáva bez akejkoľvek optimalizácie. To isté platí pre funkciu MATCH, ktorá obsahuje podobný parameter, a funkciu LOOKUP, ktorá funguje len na triedených poliach a je zahrnutá v Exceli kvôli kompatibilite s Lotus 1-2-3.

Nevýhody vzorca

Nevýhody funkcie VLOOKUP sú zrejmé: po prvé vyhľadáva iba v prvom stĺpci zadaného poľa a po druhé iba napravo od tohto stĺpca. A ako viete, môže sa stať, že stĺpec obsahujúci potrebné informácie bude naľavo od stĺpca, v ktorom budeme hľadať. Už spomínaná kombinácia vzorcov INDEX + MATCH je zbavená tohto nedostatku, čo z neho robí najflexibilnejšie riešenie na extrakciu dát z tabuliek v porovnaní s VLOOKUP (VLOOKUP).

Niektoré aspekty použitia vzorca v reálnom živote

Hľadanie rozsahu

Klasickou ilustráciou vyhľadávania sortimentu je úloha určiť zľavu podľa veľkosti objednávky.

Vyhľadajte textové reťazce

Samozrejme, VLOOKUP nehľadá len čísla, ale aj text. Treba mať na pamäti, že vzorec nerozlišuje veľkosť písmen. Ak používate zástupné znaky, môžete usporiadať nejasné vyhľadávanie. Existujú dva zástupné znaky: "?" - nahradí ľubovoľný jeden znak v textovom reťazci, "*" - nahradí ľubovoľný počet ľubovoľných znakov.

Bojové priestory

Často sa objavuje otázka, ako vyriešiť problém nadbytočných medzier pri hľadaní. Ak je stále možné z vyhľadávacej tabuľky ich vymazať, potom prvý parameter vzorca VLOOKUP nie je vždy na vás. Preto, ak existuje riziko upchatia buniek nadbytočnými medzerami, môžete použiť funkciu TRIM na jeho vyčistenie.

Iný dátový formát

Ak prvý parameter funkcie VLOOKUP odkazuje na bunku, ktorá obsahuje číslo, ale ktorá je v bunke uložená ako text, a prvý stĺpec poľa obsahuje čísla v správnom formáte, vyhľadávanie zlyhá. Je možná aj opačná situácia. Problém možno jednoducho vyriešiť prekladom parametra 1 do požadovaného formátu:

= VLOOKUP (−− D7; Produkty! $ A $ 2: $ C $ 5; 3; 0) - ak D7 obsahuje text a tabuľka obsahuje čísla;

= VLOOKUP (D7 & ""); Produkty! $ A $ 2: $ C $ 5; 3; 0) - a naopak.

Mimochodom, text môžete preložiť do čísla niekoľkými spôsobmi naraz, vyberte si:

• Dvojitá negácia -D7.
• Násobenie jedným D7 * 1.
• Nulové sčítanie D7 + 0.
• Zvýšenie na prvý výkon D7 ^ 1.

Konverzia čísla na text sa vykonáva pomocou zreťazenia s prázdnym reťazcom, čo núti Excel previesť typ údajov.

Ako potlačiť # N / A

S funkciou IFERROR je to veľmi pohodlné.

Napríklad: = IFERROR (VLOOKUP (D7; Produkty! $ A $ 2: $ C $ 5; 3; 0); "").

Ak funkcia VLOOKUP vráti kód chyby # N / A, IFERROR ho zachytí a nahradí parameter 2 (v tomto prípade prázdny reťazec) a ak sa nevyskytne žiadna chyba, potom bude táto funkcia predstierať, že vôbec neexistuje, ale existuje iba funkcia VLOOKUP, ktorá vrátila normálny výsledok.

Pole

Často zabudnú urobiť referenciu poľa absolútnou a pri rozťahovaní poľa „pláva“. Nezabudnite použiť $ A $ 2: $ C $ 5 namiesto A2: C5.

Je vhodné umiestniť referenčné pole na samostatný hárok zošita. Nedostane sa to pod nohy a bude to bezpečnejšie.

Ešte lepší nápad by bolo deklarovať toto pole ako pomenovaný rozsah.

Mnoho používateľov pri zadávaní poľa používa konštrukciu ako A: C, ktorá určuje celé stĺpce. Tento prístup má právo na existenciu, pretože nemusíte sledovať skutočnosť, že vaše pole obsahuje všetky požadované reťazce. Ak pridáte riadky do hárka s pôvodným poľom, rozsah špecifikovaný ako A:C nie je potrebné upravovať. Samozrejme, táto syntaktická konštrukcia núti Excel urobiť trochu viac práce, než presne špecifikovať rozsah, no túto réžiu možno zanedbať. Hovoríme o stotinách sekundy.

No, na pokraji geniality - usporiadať pole vo forme.

Pomocou funkcie COLUMN zadajte stĺpec, ktorý sa má extrahovať

Ak tabuľka, do ktorej získavate údaje pomocou funkcie VLOOKUP, má rovnakú štruktúru ako vyhľadávacia tabuľka, ale obsahuje len menej riadkov, potom môžete použiť funkciu COLUMN () vo VLOOKUP na automatický výpočet čísiel stĺpcov, ktoré sa majú načítať. V tomto prípade budú všetky vzorce VLOOKUP rovnaké (upravené pre prvý parameter, ktorý sa automaticky zmení)! Všimnite si, že prvý parameter má absolútnu súradnicu stĺpca.

Vytvorenie zloženého kľúča pomocou & "|" &

Ak je potrebné vyhľadávať podľa niekoľkých stĺpcov súčasne, je potrebné vytvoriť zložený kľúč na vyhľadávanie. Ak by návratová hodnota nebola textová (ako je to v prípade poľa „Kód“), ale číselná, potom by sa na to hodil pohodlnejší vzorec SUMIFS a kľúč zloženého stĺpca by nebol vôbec potrebný.

Toto je môj prvý článok pre Lifehackera. Ak sa vám to páčilo, pozývam vás na návštevu a tiež si rád v komentároch prečítate o vašich tajomstvách používania funkcie VLOOKUP a podobne. Vďaka.:)