Dobrý deň, milý čitateľ!

V tomto článku chcem popísať asi jeden z najviac užitočné funkcie v Exceli je to funkcia VLOOKUP. O funkcii sa dá povedať veľa, ale hneď prvá vec, ktorá stojí za zmienku, je, že funkcia je jednou z najzložitejších a najmenej zrozumiteľných.

V článku sa budem snažiť, aby to bolo jednoduché a prístupný jazyk popíšte, ako funguje funkcia VLOOKUP, ako aj ukážte jej vlastnosti, popis a syntax na príkladoch.

Takže, čo presne je táto funkcia, čo robí a ako je napísaná. Najjednoduchšie vysvetlenie, o práci funkcie VLOOKUP, hľadá v zozname hodnôt, podľa zadaného kritéria, jedinečný identifikátor, určité informácie, ktoré sú s ním spojené.

Ak dešifrujete názov funkcie VLOOKUP, potom s prvým písmenom bude jasné, ako táto funkcia funguje, B znamená "Vertikálne", to znamená, že hľadá hodnoty v, ale pre horizontálne zoznamy budeme mať. Funkcia VLOOKUP nám bola prvýkrát dostupná s Excelom 2000.
Funkcia VLOOKUP v Exceli má nasledujúcu syntax:

= VLOOKUP (_hľadaná hodnota _; _ tabuľka_; _ číslo stĺpca_; _ [intervalové zobrazenie] _), kde:

• vyhľadávaná_hodnota- toto je presne tá hodnota, ktorú musíme vyhľadať, a môže to byť ľubovoľná hodnota: číslo, dátum, text, odkaz na bunku, ktorá obsahuje požadovanú hodnotu alebo hodnotu získanú iným vzorcom;
• tabuľky- ide o dva alebo viac stĺpcov s rôznymi údajmi, mimochodom, funkcia neberie do úvahy veľkosť písmen pri vyhľadávaní;
• číslo_stĺpca- toto je číslo stĺpca v zadanom rozsahu, z ktorého sa získa hodnota, ktorá je v nájdenom riadku;
• interval_view- tento parameter určuje, čo presne budeme hľadať, pre presnú zhodu bude argument rovnaký FALSE alebo približná zhoda, argument sa stáva PRAVDA... Tento parameter je voliteľný, no napriek tomu dôležitý. V príkladoch nižšie ukážem, ako vytvoriť presné a približné vzorce zhody.

Teraz sa na príklade pozrime na to, ako vyhľadať niektoré údaje na inom hárku, pretože v praxi sa funkcia VLOOKUP používa len veľmi zriedkavo na vyhľadávanie v aktuálnom hárku:

VLOOKUP ("GM"; $ A $ 5: $ B $ 10; 2)

Vzorec hľadá text „GM“ v stĺpci A na aktuálnom hárku.
Poradte! Pri použití argumentu "tabuľka" je žiaduce použiť možnosť ako (toto je adresa bunky so znakom $). V tomto prípade sa rozsah vyhľadávania zafixuje a pri kopírovaní vzorca sa nezmení.

Pozrime sa na príklad hľadania hodnôt, ako funguje funkcia VLOOKUP v inom zošite:

VLOOKUP ("GM"; [Cesta k súboru] Základňa! A2: B10; 2)

Ako vidíte, nič zložité, len sa skomplikovala hádka. "stôl", teraz je k nemu pripojený odkaz na súbor s jeho názvom.

Poradte! Je lepšie zadať vzorec s dvoma súbormi, v jednom zadáte funkciu VLOOKUP a keď sa dostanete k argumentu "tabuľka", prejdite na druhý súbor a vyberte rozsah, ktorý potrebujete vyhľadať pomocou myši.

Môžete tiež zvážiť príklad, kedy presná hodnota nevieme, čo použijeme Vzorec VLOOKUP zástupné znaky. Niekedy potrebujeme nájsť nie niečo konkrétne, ale niečo, čo má spoločné vlastnosti, ako možnosť máme veľa pohoviek rôzne modely, ako ostatný nábytok, no stačí nám ich len vybrať.

Keď je funkcia VLOOKUP nahradená, môže používať nasledujúce znaky:

• "?" (otáznik) - umožňuje nahradiť ľubovoľný jeden znak;
• "*" (hviezdička)- nahradí ľubovoľný počet a postupnosť znakov.

VLOOKUP („A *“; $ A $ 2: $ B $ 10; 1; FALSE)

Poradte! Aby funkcia VLOOKUP fungovala správne, musíte ako štvrtý argument použiť parameter „FALSE“.
No, ak sme sa už dotkli témy presnej alebo približnej zhody v syntaxi funkcie VLOOKUP, tak sa na ňu pozrime bližšie:

• ak argument "Intervalové zobrazenie" rovná sa "KLAMAŤ", v takom prípade vzorec hľadá presnú zhodu s argumentom vyhľadávacej hodnoty. Ak vzorec spĺňa dve alebo viac hodnôt, ktoré zodpovedajú argumentu „požadovaná hodnota“, vyberie sa prvá zo zoznamu; v prípade, že sa nenájde žiadna zhoda, vzorec sa vráti;
• ak argument "Intervalové zobrazenie" má význam "PRAVDA" vzorec vyhľadá približnú zhodu, presnejšie, funkcia najprv vyhľadá presnú zhodu a až potom, keď ju nenájde, vyberie približnú.

Poradte!V prípade, že sa argument "interval view" rovná "TRUE" alebo nie je zadaný, je potrebná hodnota v prvom stĺpci, od najmenšej po najväčšiu. V opačnom prípade existuje možnosť chybného výsledku funkcie VLOOKUP.

Pozrime sa, ako funguje funkcia VLOOKUP, keď sa vykonáva presné vyhľadávanie. Skúsme napríklad zistiť, ktoré auto sa pohybuje rýchlosťou 200 km/h. Myslím, že tento vzorec pre vás nebude ťažký:

= VLOOKUP (200, $ A $ 2: $ B $ 15, 2, FALSE)

Napriek tomu, že máme niekoľko 200 hodnôt, dostali sme len jednu, keďže ak sa funkcia VLOOKUP presne zhoduje, systém použije iba prvú hodnotu, ktorá bola nájdená v zadanom rozsahu.
Teraz si otestujme, ako funguje funkcia VLOOKUP pre približnú zhodu hodnôt. Hľadajme auto, ktoré jazdí rýchlosťou 260 km/h. Prvá vec, ktorú urobíte, keď "Intervalové zobrazenie" rovná sa "PRAVDA"- radíte svoj rozsah hodnôt podľa prvého stĺpca vo vzostupnom poradí. Je to potrebné a dôležité, pretože funkcia VLOOKUP nájde nasledovné najväčšiu hodnotu od daný stav a potom sa vyhľadávanie zastaví. Ak sa nebudete riadiť pokynmi na triedenie, výsledkom bude správa o alebo iné podivné výsledky.

Na vyhľadávanie používame nasledujúce VLOOKUP:

= VLOOKUP (260; $ A $ 2: $ B $ 15; 2; TRUE)

Náš vzorec teda vrátil nájdenú hodnotu 240, aj keď máme aj hodnotu 270 a táto hodnota sa zdá byť bližšia, ale zvláštnosťou fungovania funkcie VLOOKUP je, že pri použití presnej zhody hľadá najviac veľký význam na zozname, ale nepresahuje ho.
Myslím si, že príklady, na ktoré som sa pozrel v článku, vám pomôžu pochopiť všetko o tom, ako funkcia VLOOKUP funguje, ako sa používa a na čo by sa mala používať.

Existuje však mnoho pokročilejších príkladov, ktorým sa budeme venovať v iných článkoch.

A to je z mojej strany všetko! Naozaj dúfam, že všetko vyššie uvedené je vám jasné. Bol by som veľmi vďačný za zanechané komentáre, pretože je to indikátor čítanosti a inšpiruje k písaniu nových článkov! Zdieľajte, čo čítate, so svojimi priateľmi a lajkujte to!

Excel editor je veľmi výkonný program pracovať s tabuľkami. Občas sa stane, že musíte pracovať s veľkým množstvom dát. V takýchto prípadoch sa používajú rôzne nástroje na vyhľadávanie informácií. Funkcia VLOOKUP v Exceli je jednou z najpopulárnejších na tento účel. Poďme sa na to pozrieť bližšie.

Väčšina používateľov nevie, že VLOOKUP znamená Vertikálne zobrazenie. V angličtine sa funkcia nazýva „VLOOKUP“, čo znamená „Vertikálne VYHĽADÁVANIE“

Ako používať funkciu

Aby ste pochopili, ako tento nástroj funguje, musíte postupovať podľa týchto krokov.

1. Vytvorte tabuľku, pomocou ktorej môžete získať nejaký druh informácií.

1. Pridajme niekoľko polí, ktoré použijeme na demonštráciu vzorcov.

1. Do poľa "Hľadá sa priezvisko" zadajte jedno z vami vybraných, ktoré sú v tabuľke.
2. Potom prejdeme do ďalšej bunky a vyvoláme okno "Vložiť funkciu".
3. Vyberieme kategóriu "Úplný abecedný zoznam".
4. Nájdeme funkciu "VLOOKUP", ktorú potrebujeme. Ak chcete pokračovať, kliknite na tlačidlo „OK“.

1. Potom budeme požiadaní, aby sme špecifikovali "Argumenty funkcie":
   • V poli "Hľadaný výraz" uveďte odkaz na bunku, do ktorej sme napísali požadované priezvisko.
   • Aby ste vyplnili pole "Tabuľka", stačí vybrať všetky naše údaje pomocou myši. Odkaz sa vloží automaticky.
   • V stĺpci „Číslo stĺpca“ uvádzame číslo 2, pretože v našom prípade je názov v druhom stĺpci.
   • Posledné pole môže nadobúdať hodnoty „0“ alebo „1“ („FALSE“ a „TRUE“). Ak zadáte „0“, editor vyhľadá presnú zhodu podľa zadaných kritérií. Ak je „1“, potom sa pri vyhľadávaní nebudú brať do úvahy úplné zhody.
2. Uložte kliknutím na tlačidlo „OK“.

1. V dôsledku toho sme dostali meno "Tomara". To znamená, že všetko je správne.

Teraz musíte použiť rovnaký vzorec pre ostatné polia. Jednoduché kopírovanie bunky pomocou Ctrl + C a Ctrl + V nebude fungovať, pretože používame relatívne odkazy a zakaždým sa zmení číslo stĺpca.

Aby všetko fungovalo správne, musíte urobiť nasledovné:

1. Kliknite na bunku s prvou funkciou.
2. Prejdite na riadok na zadávanie vzorcov.
3. Skopírujte text pomocou Ctrl + C.

1. Aktivujte ďalšie pole.
2. Znova prejdite na riadok na zadanie vzorca.
3. Stlačte klávesové skratky Ctrl + V.

Toto je jediný spôsob, ako editor nezmení odkazy v argumentoch funkcie.

1. Potom zmeníme číslo stĺpca na požadované. V našom prípade je to 3. Stlačte kláves Enter.

1. Vďaka tomu vidíme, že údaj zo stĺpca „Rok narodenia“ bol určený správne.

1. Potom zopakujeme rovnaké akcie pre posledné pole, ale s opravou čísla požadovaného stĺpca.

Princíp tejto funkcie spočíva v tom, že Excel vyhľadá fragment vo vami zadanej oblasti a následne môže vrátiť akúkoľvek hodnotu z tohto reťazca. Stačí len uviesť sériové číslo stĺpec v určenom rozsahu.

To znamená, že číslovanie nezačína od začiatku listu, ale od začiatku určenej oblasti buniek.

Ako používať funkciu VLOOKUP na porovnávanie údajov

Túto možnosť si demonštrujeme pomocou dvoch tabuliek. Predstavme si, že na druhom hárku máme aktualizované údaje o zamestnancoch. Našou výzvou je presne zistiť, čo sa zmenilo. Ak to chcete urobiť, budete musieť urobiť nasledovné.

1. Pridajme druhý hárok s presne rovnakou tabuľkou (skopírovaný pomocou klávesových skratiek Ctrl + C a Ctrl + V).
2. Povýšte stážistov na Junior Employee. Tieto informácie budú východiskovým bodom pre porovnanie.

1. Pridajme do našej starej tabuľky ešte jeden stĺpec.

1. Prejdeme do prvej bunky nového stĺpca a zadáme tam nasledujúci vzorec.

= VLOOKUP ($ B $ 3: $ B $ 11; Hárok 2! $ B $ 3: $ E 11 $; 4; FALSE)

To znamená:

• $ B $ 3: $ B $ 11 - na vyhľadávanie sa použijú všetky hodnoty prvého stĺpca (používajú sa absolútne referencie);
• Hárok 2! - tieto hodnoty je potrebné vyhľadať na hárku so zadaným názvom;
• $ B $ 3: $ E $ 11 - tabuľka, v ktorej sa má vyhľadávať (rozsah buniek);
• 4 - číslo stĺpca v špecifikovanej dátovej oblasti;
• FALSE - hľadanie presnej zhody.

1. Nové informácie sa zobrazia na mieste, kde sme zadali vzorec.
2. Výsledok bude nasledovný.

1. Teraz duplikujte tento vzorec vo zvyšku buniek. Ak to chcete urobiť, musíte potiahnuť myšou v pravom dolnom rohu pôvodnej bunky.

1. V dôsledku toho uvidíme, že vzorec, ktorý sme napísali, funguje správne, pretože všetky nové pozície boli skopírované podľa očakávania.

Teraz môžeme ľahko určiť, ktoré záznamy zamestnanca sa zmenili.

Jedinou nevýhodou tejto funkcie je, že funkcia VLOOKUP nemôže pracovať s viacerými podmienkami.

V takýchto prípadoch musíte použiť iné ďalšie stĺpce ktoré kombinujú informácie z viacerých stĺpcov. A vyzerá to škaredo a nie veľmi pohodlne.

Funkcia VLOOKUP a rozbaľovacie zoznamy

Pozrime sa na príklady použitia týchto dvoch nástrojov súčasne. Ak to chcete urobiť, postupujte podľa týchto krokov.

1. Prejdite do bunky, kde je vybraté priezvisko.
2. Kliknite na kartu Údaje.
3. Kliknite na určený nástroj a vyberte „Overenie údajov“.

Podmienené formátovanie (5)
Zoznamy a rozsahy (5)
Makrá (postupy VBA) (63)
Rôzne (39)
Chyby a závady Excelu (3)

Ako nájsť hodnotu v inej tabuľke alebo silu VLOOKUP

V skutočnosti chcem v tomto článku hovoriť o možnostiach nielen Funkcie VLOOKUP, ale chcem sa aj dotknúť VYHĽADÁVANIE, ako funkcia veľmi úzko súvisiaca s VLOOKUP. Každá z týchto funkcií má svoje klady aj zápory. Stručne povedané, VLOOKUP hľadá určitú hodnotu, ktorú sme zadali, medzi súborom hodnôt umiestnených v jednom stĺpci. Možno najčastejšie potreba VLOOKUP vzniká, keď potrebujete porovnať údaje, nájsť údaje v inej tabuľke, pridať údaje z jednej tabuľky do druhej na základe nejakého kritéria atď.
Aby som trochu lepšie pochopil princíp práca VLOOKUP lepšie začať niečím praktický príklad... Existuje takáto tabuľka:
obr

a z prvej tabuľky je potrebné nahradiť v druhom dátume za každé priezvisko. Pre tri nahrávky to nie je problém a urobiť to ručne - všetko je zrejmé. Ale v reálnom živote sú to tabuľky s tisíckami záznamov a manuálne vyhľadávanie náhrad môže trvať viac ako jednu hodinu. Plus pár ďalších muchy: nielenže sú celé mená v oboch tabuľkách usporiadané v úplne inom poradí a počet záznamov v tabuľkách je odlišný, takže tabuľky sa nachádzajú aj na iných listoch/knihách. Verím, že som vás presvedčil, že náhrada rúk nie je vôbec možná. ale VLOOKUP (VLOOKUP) tu bude nenahraditeľná. V tomto prípade sa nebude musieť robiť prakticky nič - stačí napísať do prvej bunky stĺpca C druhej tabuľky (kde musíte nahradiť dátumy z prvej tabuľky) tento vzorec:
= VLOOKUP ($ A2; Hárok 1! $ A $ 2: $ C $ 4; 3; 0)
Vzorec môžete zapísať buď priamo do bunky, alebo pomocou správcu funkcií výberom v kategórii Referencie a polia VLOOKUP a samostatne špecifikujúce potrebné kritériá. Teraz skopírujte ( Ctrl+C) bunku so vzorcom, vyberte všetky bunky v stĺpci C na koniec údajov a vložte ( Ctrl+V).

Po prvé, základný princíp fungovania: VLOOKUP hľadá v prvom stĺpci argumentu Tabuľka hodnotu určenú argumentom Lookup_value ... Keď sa nájde požadovaná hodnota, funkcia vráti hodnotu oproti nájdenej hodnote, ale zo stĺpca určeného argumentom Číslo_stĺpca ... Intervalovému pozeraniu sa budeme venovať trochu neskôr. Funkcia VLOOKUP môže vrátiť iba jednu hodnotu – prvú, ktorá zodpovedá kritériu. Ak sa nenájde požadovaná hodnota (nie je v tabuľke), potom bude výsledok funkcie # N / A ... Nebojte sa toho - je to dokonca užitočné. Budete presne vedieť, ktoré záznamy chýbajú, a tak môžete obe tabuľky navzájom porovnať. Niekedy sa ukáže, že vidíte: údaje sú v oboch tabuľkách, ale funkcia VLOOKUP dáva # N / A. To znamená, že údaje vo vašich tabuľkách nie sú identické. Niektoré z nich majú extra nenápadné medzery (zvyčajne pred alebo za hodnotou), prípadne sú znaky cyriliky zmiešané so znakmi latinky. Rovnakým spôsobom # N / A bude, ak sú kritériá čísla a v požadovanej tabuľke sú napísané ako text (zvyčajne vľavo horný roh objaví sa zelený trojuholník), a vo finále - ako čísla. Alebo naopak.

Popis argumentov VLOOKUP
$ A2 - argument Lookup_value(nazvime to Kritérium byť krátky). To je to, čo hľadáme. Tie. o prvý rekord druhej tabuľky to bude S.A. Petrov. Tu môžete zadať buď priamo text kritéria (v tomto prípade musí byť v úvodzovkách - = VLOOKUP ("Petrov SA"; Hárok1! $ A $ 2: $ C $ 4; 3; 0), alebo odkaz na bunka s týmto textom (ako v príklade funkcie)... Existuje malá nuansa: môžete použiť aj zástupné znaky: "*" a "?". To je veľmi výhodné, ak potrebujete nájsť hodnoty iba z časti reťazca. Napríklad „Petrov S.A“ nemôžete zadať celé, ale zadať iba priezvisko a hviezdičku – „Petrov *“. Potom sa zobrazí každý záznam, ktorý začína na „Petrov“. Ak potrebujete nájsť záznam, v ktorom sa priezvisko "Petrov" nachádza kdekoľvek v riadku, môžete ho špecifikovať takto: "* Petrov *". Ak chcete nájsť priezvisko Petrov a bez ohľadu na to, aké iniciály bude mať meno a priezvisko (ak je celé meno napísané v tvare Ivanov II), tu je len toto: "Ivanov?.?" ... Často je potrebné, aby každý riadok uviedol svoju vlastnú hodnotu (v stĺpci Priezvisko A a musíte ich nájsť všetky). V tomto prípade sú vždy uvedené odkazy na bunky stĺpca A. Napríklad v bunke A1 je napísané: Ivanov. Je tiež známe, že Ivanov je v inej tabuľke, ale za priezviskom je možné napísať meno aj priezvisko (alebo niečo iné). Musíme však nájsť iba riadok, ktorý začína priezviskom. Potom je potrebné písať nasledujúcim spôsobom: A1 & "*". Tento záznam bude ekvivalentný "Ivanov *". A1 hovorí Ivanov, ampersand (&) sa používa na spojenie dvoch textových hodnôt do jedného reťazca. Hviezdička v úvodzovkách (ako by mal byť text vo vzorci). Tak dostaneme:
A1 & "*" =>
"Ivanov" & "*" =>
"Ivanov*"
Je to veľmi výhodné, ak existuje veľa vyhľadávacích hodnôt.
Ak potrebujete zistiť, či je v reťazci aspoň niekde slovo, vložte hviezdičky na obe strany: "*" & A1 & "*"

Hárok 1! $ A $ 2: $ C $ 4 – argument tabuľky... Určuje rozsah buniek. Iba rozsah by mal obsahovať údaje od prvej dátovej bunky po úplne poslednú. Nemusí to byť rozsah uvedený v príklade. Ak je 100 riadkov, potom Hárok1! $ A $ 2: $ C 100 $. Je dôležité si zapamätať tri veci: po prvé, je to tak tabuľky by mal vždy začínať stĺpcom, ktorý hľadáte Kritérium ... A nič iné. Inak sa nič nenájde alebo výsledok nebude taký, aký očakávate. Po druhé: argument tabuľky musí byť "pripnutý" ... Čo to znamená. Vidieť znaky dolára - $? Toto je kotvenie (presnejšie sa to nazýva absolútna referencia na rozsah)... Ako sa to robí. Zvýraznite text odkazu (iba jeden rozsah – jedno kritérium) a stlačte F4 kým sa pred názvom stĺpca aj pred číslom riadku nezobrazí doláre. Ak sa tak nestane, pri kopírovaní vzorca sa argument Tabuľka „posunie“ a výsledok bude opäť nesprávny. A posledný - tabuľka by mala obsahovať stĺpce od prvého (v ktorom hľadáme) po posledný (z ktorého musíme vrátiť hodnoty). V príklade Hárok 1! $ A $ 2: $ C $ 4- to znamená, že nebude možné vrátiť hodnotu zo stĺpca D (4), od r v tabuľke sú len tri stĺpce.

3 - Číslo_stĺpca... Tu len špecifikujeme číslo stĺpca v argumente tabuľky, hodnoty, z ktorých musíme dosadiť ako výsledok. V príklade ide o Dátum prijatia – t.j. stĺpec číslo 3. Ak by bolo potrebné oddelenie, potom by sme označili 2 a ak by sme len potrebovali porovnať, či sú priezviská jednej tabuľky v inej, môžeme uviesť aj 1. Dôležité: argument Číslo_stĺpca nesmie prekročiť počet stĺpcov v argumente tabuľky ... V opačnom prípade bude výsledkom vzorca chyba. #LINK!... Ak je napríklad zadaný rozsah $ B $ 2: $ C $ 4 a potrebujete vrátiť údaje zo stĺpca C, potom je 2 správne. argument tabuľky($ B $ 2: $ C $ 4) obsahuje iba dva stĺpce - B a C. Ak sa pokúsime zadať číslo stĺpca 3 (aký je počet na hárku), dostaneme chybu #LINK! odkedy v uvedenom rozsahu jednoducho nie je žiadny tretí stĺpec.

Praktický tip: ak má argument Tabuľka príliš veľa stĺpcov a potrebujete vrátiť výsledok z posledného stĺpca, nie je vôbec potrebné počítať ich počet. Môžete to zadať takto: = VLOOKUP ($ A2; Hárok1! $ A $ 2: $ C $ 4; NUMBERCOLUMN (Hárok1! $ A $ 2: $ C $ 4); 0). Mimochodom dnu v tomto prípade Hárok 1! možno tiež odstrániť ako nepotrebné: ​​= VLOOKUP ($ A2; Hárok1! $ A $ 2: $ C $ 4; NUMBERCOLUMN ($ A $ 2: $ C $ 4); 0).

0 - Interval_view je velmi zaujimavy argument. Môže byť buď PRAVDA alebo NEPRAVDA. Okamžite vyvstáva otázka: prečo je v mojom vzorci 0? Všetko je veľmi jednoduché - Excel vo vzorcoch môže vnímať 0 ako NEPRAVDA a 1 ako PRAVDU. Ak zadáte vo VLOOKUP tento parameter rovná 0 alebo FALSE, potom sa vyhľadá presná zhoda so zadaným kritériom. Toto nemá nič spoločné so zástupnými znakmi ("*" a "?"). Ak použijete 1 alebo TRUE (alebo posledný argument nešpecifikujete vôbec, keďže je štandardne TRUE), potom... Je to veľmi dlhý príbeh. V skratke – VLOOKUP vyhľadá najpodobnejšiu hodnotu, ktorá sa zhoduje Kritérium ... Niekedy veľmi užitočné. Ak však použijete tento parameter, zoznam v argumente Tabuľka musí byť zoradený vzostupne. Upozorňujem na skutočnosť, že triedenie je potrebné iba vtedy, ak je argument Interval_View TRUE alebo 1. Ak je 0 alebo FALSE, triedenie nie je potrebné.

Mnohí si asi všimli, že na obrázku mám pokazené oddelenia na celé meno. Toto nie je chyba záznamu. Príklad priložený k článku ukazuje, ako môžete nahradiť ich aj dátumy jedným vzorcom bez ručnej zmeny argumentu Číslo_stĺpca... Zdalo sa mi, že takýto príklad môže prísť vhod.

Ako sa vyhnúť chybe # N / A (# N / A) vo VLOOKUP?
Ešte častý problém- mnohí nechcú vidieť výsledok # N / A, ak sa nenájde žiadna zhoda. Je ľahké obísť:
= IF (UND (VLOOKUP ($ A2; Hárok1! $ A $ 2: $ C $ 4; 3; 0)); ""; VLOOKUP ($ A2; Hárok1! $ A $ 2: $ C $ 4; 3; 0)))
Ak teraz funkcia VLOOKUP nenájde zhodu, bunka bude prázdna.
Používatelia verzií Excelu 2007 a vyšších môžu používať IFERROR:
= IFERROR (VLOOKUP ($ A2; Hárok1! $ A $ 2: $ C $ 4; 3,0); "")

Sľúbené HĽADANIE

Táto funkcia hľadá hodnotu zadanú parametrom Lookup_value v hádke Lookup_array... A výsledkom funkcie je číslo pozície nájdenej hodnoty v Viewable_array... Je to číslo pozície, nie samotná hodnota. V zásade to nebudem opisovať rovnako podrobne, pretože hlavné body sú úplne rovnaké. Ak by sme to chceli použiť na tabuľku vyššie, vyzeralo by to takto:
= HĽADAŤ ($ A2; Hárok1! $ A $ 2: $ A $ 4; 0)
$ A2 – Lookup_value. Tu je všetko úplne rovnaké ako pri VLOOKUP. Zástupné znaky sú tiež povolené a v úplne rovnakom dizajne.

Hárok 1! $ A $ 2: $ A $ 4 – Pole, na ktoré sa treba pozrieť. Hlavný rozdiel od funkcie VLOOKUP je v tom, že môžete zadať pole iba s jedným stĺpcom. Toto by mal byť stĺpec, ktorý budeme hľadať. Lookup_value ... Ak sa pokúsite zadať viac ako jeden stĺpec, funkcia vráti chybu.

Match_type (0) - rovnako ako vo VLOOKUP Interval_view ... S rovnakými vlastnosťami. Líši sa len v možnosti hľadania najmenšieho od žiadaného či najväčšieho. Tomu sa ale v tomto článku nebudem venovať.

S vyriešeným hlavným. Musíme však vrátiť nie číslo pozície, ale samotnú hodnotu. Znamená MATCH in čistej forme nám nevyhovuje. Aspoň jeden sám od seba. Ale ak ju použijete spolu s funkciou INDEX, tak to potrebujeme a ešte viac.
= INDEX (Hárok1! $ A $ 2: $ C $ 4; HĽADAŤ ($ A2; Hárok1! $ A $ 2: $ A $ 4; 0); 2)
Tento vzorec vráti rovnaký výsledok ako VLOOKUP.

Argumenty funkcie INDEX
Hárok 1! $ A $ 2: $ C $ 4 - Pole. Ako tento argument uvádzame rozsah, z ktorého chceme získať hodnoty. Môže existovať jeden alebo niekoľko stĺpcov. Ak existuje iba jeden stĺpec, potom posledný argument funkcie nie je potrebné zadávať. Mimochodom – tento argument sa vôbec nemusí zhodovať s argumentom, ktorý uvádzame v argumente Look-up_array funkcie MATCH.

Ďalej sú RowNumber a ColumnNumber. Práve za Line_Number dosadíme SEARCH, ktoré nám vráti číslo pozície v poli. Na tomto je postavené všetko. INDEX vráti hodnotu z poľa, ktorá je v zadanom riadku (Row_Number) poľa a v zadanom stĺpci (Column_number), ak existuje viac ako jeden stĺpec. Je dôležité vedieť, že v tomto zväzku sa počet riadkov v argumente Array funkcie INDEX a počet riadkov v argumente Look-up_array funkcie MATCH musí zhodovať. A začnite na tej istej linke. To je v bežných prípadoch, ak nesledujete iné ciele.
Rovnako ako v prípade VLOOKUP, INDEX vráti # N / A, ak sa nenájde požadovaná hodnota. A takéto chyby môžete obísť rovnakým spôsobom:
Pre všetky verzie Excelu (vrátane 2003 a starších):
= AK (UND (HĽADAŤ ($ A2; Hárok1! $ A $ 2: $ A $ 4; 0)); ""; INDEX (Hárok1! $ A $ 2: $ C $ 4; HĽADAŤ ($ A2; Hárok1! $ A $ 2: $ A $ 4; 0); 2))
Pre verzie 2007 a vyššie:
= IFERROR (INDEX (Hárok1! $ A $ 2: $ C $ 4; HĽADAŤ ($ A2; Hárok1! $ A $ 2: $ A $ 4; 0); 2); "")

Práca s kritériami dlhšími ako 255 znakov
INDEX-POISKPOS má oproti VLOOKUP ešte jednu výhodu. Ide o to, že VLOOKUP nevie hľadať hodnoty ktorého dĺžka riadku obsahuje viac ako 255 znakov... To sa stáva zriedka, ale stáva sa to. VLOOKUP môžete samozrejme oklamať a obmedziť kritérium:
= VLOOKUP (MID ($ A2; 1; 255); MID (Hárok1! $ A $ 2: $ C $ 4; 1; 255); 3; 0)
ale toto je vzorec poľa. A okrem toho, takýto vzorec nie vždy vráti požadovaný výsledok. Ak je prvých 255 znakov identických s prvými 255 znakmi v tabuľke a potom sa znaky líšia, vzorec to už neuvidí. A vzorec sa vracia výlučne textové hodnoty, čo nie je príliš výhodné v prípadoch, keď by sa mali čísla vrátiť.

Preto je lepšie použiť zložitý vzorec:
= INDEX (Hárok1! $ A $ 2: $ C $ 4; SUMPRODUCT (HĽADAŤ (PRAVDA; Hárok1! $ A $ 2: $ A $ 4 = $ A2; 0)); 2)
Tu som použil rovnaké rozsahy vo vzorcoch na čitateľnosť, ale v príklade sťahovania sa líšia od tých, ktoré sú tu zobrazené.
Samotný vzorec je postavený na schopnosti funkcie SUMPRODUCT transformovať niektoré funkcie vo svojom vnútri na masívne výpočty. V tomto prípade MATCH hľadá pozíciu reťazca, kde sa kritérium rovná hodnote v reťazci. Tu už nebudete môcť používať zástupné znaky.

V príklade priloženom k ​​článku nájdete príklady použitia všetkých opísaných prípadov a príklad, prečo sú niekedy INDEX a MATCH vhodnejšie ako VLOOKUP.

Stiahnite si príklad

(26,0 kiB, 13 776 stiahnutí)

Pomohol článok? Zdieľajte odkaz so svojimi priateľmi! Video lekcie

("Spodná lišta" :( "textstyle": "static", "textpositionstatic": "dole", "textautohide": true, "textpositionmarginstatic": 0, "textpositiondynamic": "bottomleft", "textpositionmarginleft": 24, " textpositionmarginright ": 24," textpositionmargintop ": 24," textpositionmarginbottom ": 24," texteffect ":" slide "," texteffecteasing ":" easyOutCubic "," texteffectduration ": 600," texteffectslidedirection ":" left "," texteffectslidedistance " : 30, "texteffectdelay": 500, "texteffectseparate": nepravda, "texteffect1": "slide", "texteffectslidedirection1": "right", "texteffectslidedistance1": 120, "texteffecteasing1": "easeOutCubic", "texteffectduration1" , "texteffectdelay1": 1000, "texteffect2": "slide", "texteffectslidedirection2": "vpravo", "texteffectslidedistance2": 120, "texteffecteasing2": "easeOutCubic", "texteffectduration2": 600, "texteffectsdelay2 "texteffectsdelay2 " :" display: block; padding: 12px; text-align: left; "," textbgcss ":" display: block; position: absolute; top: 0px; left: 0px; width: 100%; height: 100% ; background - farba: # 333333; nepriehľadnosť: 0,6; filter: a lpha (nepriehľadnosť = 60); "," titlecss ":" zobrazenie: blok; poloha: relatívna; písmo: tučné 14px \ "Lucida Sans Unicode \", \ "Lucida Grande \", bezpätkové, Arial; farba: #fff; "," descriptioncss ":" display: block; poloha: relatívna; písmo: 12px \ "Lucida Sans Unicode \", \ "Lucida Grande \", bezpätkové, Arial; farba: #fff; margin-top: 8px; "," buttoncss ":" display: block; poloha: relatívna; margin-top: 8px; "," texteffectresponsive ": true," texteffectresponsivesize ": 640," titlecssresponsive ":" font-size: 12px; "," descriptioncssresponsive ":" display: none! important; "," buttoncssresponsive ": "", "addgooglefonts": false, "googlefonts": "", "textleftrightpercentforstatic": 40))

Microsoft Excel - skvelý nástroj pracovať s údajmi. Možnosti sú obrovské, potenciál je v skutočnosti málo študovaný, pretože pravidlá pre písanie príkazov spôsobujú ťažkosti aj profesionálom. Ale ak tvrdo pracujete, ukáže sa, že tu dokážete neuveriteľné veci. Funkcia Excel VLOOKUP- jeden z nástrojov na spracovanie dát, o tom si teraz povieme.

Čo je VLOOKUP v Exceli - rozsah

Pri spracovaní viacerých tabuliek, ktoré sa často nachádzajú v rôzne knihy je potrebné prenášať údaje z jedného do druhého, pričom je potrebné zabezpečiť, aby hodnoty nestratili svoj význam a proces bol automatizovaný. Mechanizmus v exceli by mal fungovať jednoducho a rýchlo.

Napríklad máme spoločnosť. Pracuje tam Ivan Ivanovič. Na jednom hárku je uložená hodnota jeho mzdy, na inom suma peňazí, ktorú mu účtovné oddelenie zadrží z platu za pokutu. Je potrebné spojiť všetky hodnoty do jedného dokumentu. Ďalším príkladom sú dve tabuľky: ceny a sklad. V jednej sú uvedené náklady na vreckovky, v druhej ich množstvo. Je potrebné dať dohromady množstvo a cenu na jednom mieste.

V prípadoch, keď sú v podniku iba dvaja alebo traja zamestnanci alebo existuje až tucet tovaru, môžete robiť všetko ručne. S náležitou starostlivosťou bude človek pracovať bez chýb. Ak sú však hodnoty, ktoré sa majú spracovať, napríklad tisíc, je potrebná automatizácia práce. Na tento účel má Excel funkciu VLOOKUP (VLOOKUP).

Príklady pre názornosť: v tabuľkách 1, 2 - počiatočné údaje, tabuľka 3 - čo treba získať.

Tabuľka počiatočných údajov 1

Tabuľka kombinovaných údajov 3

CELÉ MENO.	Z.P.	Dobre
Ivanov	20 000 ₽	38 000 ₽
Petrov	19 000 ₽	12 000 ₽
Sidorov	21 000 ₽	200 ₽

Funkcia VLOOKUP v Exceli - ako používať

Aby sa tabuľka 1 dostala do finálnej podoby, zadáme do nej nadpis stĺpca, napríklad „Penalizácia“. V skutočnosti je to voliteľné, môžete napísať ľubovoľný text alebo ho nechať prázdne. Funkcia bude fungovať aj kliknutím myšou do poľa, kde by sa mala objaviť hodnota nájdená v inej tabuľke.

Teraz musíme zavolať funkciu. Dá sa to urobiť rôznymi spôsobmi:

Je potrebné vyplniť hodnoty pre funkciu VLOOKUP

Výsledok je evidentný – v tabuľke 3 (pozri vyššie).

VLOOKUP - návod na prácu s dvoma podmienkami

V situáciách, keď je potrebné zadať kritériá vyhľadávania nie podľa jednej podmienky, ale podľa niekoľkých - dvoch stĺpcov naraz, možno na prácu použiť aj funkciu VLOOKUP. Na tento účel musia byť dokončené počiatočné údaje.

Príklad, do tabuľky 4 je potrebné vložiť cenu z tabuľky 5.

Tabuľka vlastností telefónu 4

Príklad je vybraný na telefónoch, ale je zrejmé, že údaje môžu byť úplne akékoľvek. Ako môžete vidieť z tabuliek, značky telefónov sa nelíšia, líšia sa však RAM a Fotoaparát. Na vytvorenie súhrnných údajov musíme vybrať telefóny podľa značky a pamäte RAM. Aby funkcia VLOOKUP fungovala na niekoľkých podmienkach, musíte skombinovať stĺpce s podmienkami.

Pridajte stĺpec úplne vľavo. Nazvime to napríklad „Únia“. V prvej bunke hodnôt máme B 2, napíšeme konštrukciu "= B 2 & C 2". Rozmnožujeme sa myšou. Ukázalo sa to ako v tabuľke 6.

Tabuľka charakteristík telefónu 6

únie	názov	RAM	cena
ZTE 0,5	ZTE	0,5	1 990 ₽
ZTE 1	ZTE	1	3 099 ₽
DNS1	DNS	1	3 100 ₽
DNS 0,5	DNS	0,5	2 240 ₽
Alcatel 1	Alcatel	1	4 500 ₽
Alcatel 256	Alcatel	256	450 ₽

Tabuľku 5 spracovávame rovnakým spôsobom. Potom pomocou funkcie VLOOKUP vyhľadáme jednu podmienku. Podmienkou sú údaje z kombinovaných stĺpcov. Nezabudnite, že sa zmení číslo stĺpca, z ktorého pochádzajú údaje vo funkcii VLOOKUP. Po aplikácii funkcie získate výber podľa dvoch podmienok. Namiesto susedných stĺpcov môžete kombinovať stĺpce so značkou telefónu a fotoaparátom.

Pozrite si video návod, ako používať Funkcia VLOOKUP v Exceli pre figuríny:

Funkcia VLOOKUP v Exceli sa ľahko používa a možnosti spracovania údajov sú obrovské.

Funkcia VLOOKUP v Exceli vám umožňuje preusporiadať údaje z jednej tabuľky do zodpovedajúcich buniek druhej tabuľky. Jeho anglický názov je VLOOKUP.

Veľmi praktické a často používané. Pretože manuálne porovnávanie rozsahov s desiatkami tisíc názvov je problematické.

Ako používať funkciu VLOOKUP v Exceli

Povedzme, že určité množstvo materiálu dorazilo do skladu podniku na výrobu kontajnerov a obalov.

Cena materiálu je uvedená v cenníku. Toto je samostatná tabuľka.

Musíte zistiť náklady na materiál prijatý v sklade. Ak to chcete urobiť, musíte nahradiť cenu z druhej tabuľky do prvej. A obyčajným násobením nájdeme to, čo hľadáme.

Algoritmus akcií:

Kliknite na tlačidlo OK. A potom funkciu „vynásobíme“ cez celý stĺpec: myšou uchopíme pravý dolný roh a potiahneme nadol. Dostaneme požadovaný výsledok.

Teraz nebude ťažké nájsť náklady na materiály: množstvo * cena.

Funkcia VLOOKUP prepojila tieto dve tabuľky. Ak sa zmení cenník, zmenia sa aj náklady na materiál prijatý na sklad (prijatý dnes). Aby ste tomu zabránili, použite "Prilepiť špeciálne".

1. Vyberte stĺpec s vloženými cenami.
2. Pravé tlačidlo myši - "Kopírovať".
3. Bez odstránenia výberu, pravé tlačidlo myš - "Prilepiť špeciálne".
4. Začiarknite políčko vedľa položky „Hodnoty“. OK

Vzorec v bunkách zmizne. Zostanú len hodnoty.



Rýchle porovnanie dvoch tabuliek pomocou funkcie VLOOKUP

Funkcia pomáha porovnávať hodnoty v obrovských tabuľkách. Povedzme, že cena sa zmenila. Musíme porovnať staré ceny s novými cenami.

Takto prezentované údaje je možné porovnávať. Nájdite číselný a percentuálny rozdiel.

Funkcia VLOOKUP v Exceli s viacerými podmienkami

Doteraz sme na rozbor ponúkli len jednu podmienku – názov materiálu. V praxi sa často vyžaduje porovnanie viacerých rozsahov s údajmi a výber hodnoty pre 2., 3. atď. kritériá.

Príklad tabuľky:

Predpokladajme, že musíme nájsť cenu, za ktorú bola vlnitá lepenka privezená z OJSC Vostok. Pre vyhľadávanie podľa názvu materiálu a podľa dodávateľa je potrebné nastaviť dve podmienky.

Vec je komplikovaná tým, že viacero položiek pochádza od jedného dodávateľa.

Pozrime sa podrobne na vzorec:

1. čo hľadáme.
2. Kde hľadáme.
3. Aké údaje berieme.

Funkcia VLOOKUP a rozbaľovací zoznam

Povedzme, že máme nejaké údaje vytvorené vo forme rozbaľovacieho zoznamu. V našom príklade - "Materiály". Funkciu je potrebné nakonfigurovať tak, aby sa pri výbere názvu zobrazila cena.

Najprv urobme rozbaľovaciu ponuku:

Teraz sa musíte uistiť, že keď vyberiete určitý materiál, v stĺpci ceny sa zobrazí zodpovedajúca hodnota. Umiestnime kurzor do bunky E9 (kde by sa mala objaviť cena).

1. Otvorte „Sprievodcu funkciami“ a vyberte VLOOKUP.
2. Prvý argument - "Hľadaná hodnota" - bunka s rozbaľovacím zoznamom. Tabuľka - rozsah s názvami materiálov a cenami. Stĺpec, respektíve 2. Funkcia nadobudla nasledujúcu podobu: .
3. Stlačíme ENTER a vychutnáme si výsledok.

Meníme materiál - mení sa cena:

Takto funguje rozbaľovací zoznam v Exceli s funkciou VLOOKUP. Všetko sa deje automaticky. V priebehu niekoľkých sekúnd. Všetko funguje rýchlo a efektívne. Musíte len zistiť túto funkciu.