Mnohí poznajú a často používajú. Ale zároveň má dve významné nevýhody, napríklad ako urobiť „Left VLOOKUP“. Sám VLOOKUP používam len vtedy, keď potrebujem niečo rýchlo urobiť. V súboroch na "bežné" použitie robím konštrukciu INDEX a MATCH. Prečo je lepšia?

1. Pri pridávaní stĺpca do tabuľky údajov nemusíte meniť číslo stĺpca v samotnom vzorci (ako pri VLOOKUP). Stĺpec sa automaticky posunie
2. VLOOKUP môžete urobiť aj naopak, t.j. vyberte si z tabuľky sprava doľava. Vo VLOOKUP by mal byť prvý stĺpec vždy vyhľadávateľný.

Ako to všetko urobiť, prečítajte si nižšie :)

Použijem príklad z . Aby ste lepšie porozumeli aplikácii tohto konštruktu oproti VLOOKUP:

Aké ľahké je vyplniť, ak vzorec vidíte prvýkrát?

Najprv sa rozhodnite, kde je tabuľka pôvodná a kde získať údaje! Krok za krokom, čo vyplniť.

1. Vložte vzorec do požadovanú oblasť tabuľky
2. Namiesto $G:$G vložte bunky, v ktorých sa má hodnota nájsť, a podľa toho by sa mala zobraziť ako výsledok. Hľadáme priezvisko, preto v úvodnej tabuľke hľadajte stĺpec s priezviskami.
3. Nahraďte $J:$J podľa toho, ktoré hodnoty by sa mali vrátiť do bunky. Potrebujeme priezviská v závislosti od vozidla - vložíme tie vozidlá, v blízkosti ktorých by sa mali objaviť hodnoty.
4. Namiesto $H:$H vyplníme stĺpec c, v ktorom potrebujeme nájsť zodpovedajúcu hodnotu. Tie. hľadáme Priezvisko podľa TS, tak do pôvodnej tabuľky vložíme stĺpec s TS.

Ak vás zaujíma, prečo sú vo vzorci znaky $, čítajte

INDEX a MATCH. Aké sú tieto funkcie?

INDEX a MATCH sú veľmi výkonné funkcie, ktoré v kombinácii s ostatnými poskytujú vynikajúce výsledky.

INDEX(pole; číslo_riadka; číslo_stĺpca)

Vráti hodnotu v priesečníku zadaného riadka a stĺpca určitý rozsah. Tie. spočiatku pracuje s dvojrozmernými poľami.

Výberom dátového poľa J1:K4 a nastavením čísla riadku a stĺpca na dva sme dostali zodpovedajúcu hodnotu.

Pri pohľade na pôvodný vzorec

INDEX($G:$G;MATCH($J:$J;$H:$H;0);1)

Uvidíme, že namiesto druhého argumentu (čísla riadku) máme vzorec MATCH. čo tu robí?

MATCH je vyhľadávanie podľa hodnoty. Funkcia hľadá danú hodnotu v riadku alebo stĺpci a vráti ju sériové číslo(od začiatku rozsahu). Tie. v druhom argumente funkcie INDEX nájdeme číslo vozidla, ktoré potrebujeme, dostaneme jeho číslo, napríklad 2.

A už v jednorozmernom poli $G:$G nájdeme bunku s číslom riadku = 2. Toto bude fungovať pre každú bunku v stĺpci J.

Zdá sa teda, že nejde o veľmi komplikovaný dizajn, ale ako som písal vyššie, je veľmi efektívny. Pretože nemusíte neustále meniť číslo požadovanej hodnoty ako vo VLOOKUP a môžete hľadať aj vpravo aj vľavo :)

Ak máte nejaké otázky, píšte komentáre.

Ako vždy!

Zdieľajte náš článok na svojich sociálnych sieťach:

Funkcia VLOOKUP v Exceli sa používa na vyhľadanie údajov z jednej tabuľky a ich porovnanie s údajmi z inej tabuľky.

Pre stiahnutie kliknite na tlačidlo nižšie excelový súbor s príkladmi fungovania funkcie VLOOKUP :

Videonávod: Ako používať funkciu VLOOKUP v Exceli

Funkcia VLOOKUP v Exceli pre figuríny

V nižšie uvedenom príklade v tabuľke vpravo chceme nahradiť výsledky skúšky z matematiky pre študenta Sidorova. Údaje za všetkých žiakov poznáme z tabuľky vľavo. Pomocou vzorca môžeme nahradiť údaje, ktoré potrebujeme.

Syntax

=VLOOKUP(vyhľadávacia_hodnota, tabuľkové_pole, stĺpec_index_num, ) - Anglická verzia

=VLOOKUP(vyhľadávacia_hodnota, tabuľka, číslo_stĺpca, [vyhľadávanie_rozsahu])- ruská verzia

Funkcia Argumenty

• vyhľadávaná_hodnota (hľadaná_hodnota) je hodnota, ktorá sa má vyhľadať v stĺpci úplne vľavo v tabuľke. Môže to byť hodnota, odkaz na bunku alebo textový reťazec. V žiackom príklade sú to ich priezviská;
• table_array (tabuľka) je rozsah údajov, ktoré sa majú vyhľadávať. Môže to byť odkaz na rozsah buniek alebo pomenovaný rozsah. V príklade študentskej tabuľky by to bola celá tabuľka, ktorá obsahuje ročník a priezviská študentov;
• col_index (coll_number) je poradové číslo stĺpca v rozsahu údajov, z ktorého sa získa požadovaná hodnota;
• ([interval_lookup])- Tento argument označuje presnosť zhody údajov počas vyhľadávania. Zadajte „0“ pre presnú zhodu, „1“ pre približnú zhodu.

Ďalšie informácie

• zhoda požadovaných údajov môže byť presná a približná;
• pri porovnávaní približnej presnosti údajov zabezpečte, aby boli údaje v tabuľkách zoradené v zostupnom poradí (od veľkej po malú). V opačnom prípade bude výsledok zhody nesprávny;
• pri porovnávaní údajov s približnou presnosťou:
  - ak funkcia nenájde požadovanú hodnotu, vráti najväčšiu hodnotu, ktorá bude menšia ako hľadané hodnoty;
  – ak funkcia generuje chybu #N/A počas porovnávania, potom je hľadaná hodnota menšia ako najmenšia hodnota v hľadanom rozsahu;
  - pre hodnoty, ktoré hľadáte, môžete použiť zástupné znaky.

Príklady použitia funkcie VLOOKUP v Exceli

Príklad 1. Hľadanie výsledku skúšky pre študenta

V nižšie uvedenom príklade je zoznam študentov zobrazený v ľavom stĺpci tabuľky. Konečné skóre skúšok týchto študentov je uvedené na pravej strane tabuľky.

Stojíme pred úlohou zistiť, aké skóre získal študent Petrov z fyziky.

Môžeme to zistiť pomocou vzorca nižšie:

=VLOOKUP("Petrov",$A$3:$E$10,3;0)- Anglická verzia

=VLOOKUP(“Petrov”,$A$3:$E$10,3,0) - ruská verzia

Vyššie uvedený vzorec má štyri argumenty:

• "Petrov"- priezvisko, údaje, ktoré hľadáme;
• $A$3: $E$10– rozsah údajov s výsledkami skúšok;
• “3” – poradové číslo stĺpca rozsahu údajov s hodnotou stupňa fyziky;
• “0” - Presná zhoda s požadovanou hodnotou.

Nižšie je názorne znázornené, ako funguje naša funkcia podľa príkladu.

Najprv vyhľadá v úplne ľavom stĺpci rozsahu údajov priezvisko „Petrov“. Vyhľadávanie prebieha zhora nadol.

Keď Excel nájde presnú zhodu pre „Petrov“ v rozsahu údajov, systém „prekročí“ doprava, do tretieho stĺpca, aby zobrazil hľadané údaje.

Pomocou tohto prístupu môžete získať akékoľvek údaje pre každého študenta a predmet.

Napríklad, aby sme našli výsledky skúšky pre študenta Peskova z chémie, potrebujeme vzorec:

=VLOOKUP("Piesky", $A$20: $E$28,4.0) - Anglická verzia

=VLOOKUP(“Sands”, $A$20:$E$28,4;0)- ruská verzia

Vo vyššie uvedenom príklade je priezvisko študenta in dvojité úvodzovky. Môžete tiež použiť odkaz na bunku s hodnotou, ktorú hľadáte. Odkaz na bunku robí vzorec dynamickým.

Ak máte napríklad bunku s priezviskom a hľadáte známku z matematiky, výsledok sa automaticky aktualizuje, keď sa zmení meno študenta (ako je uvedené nižšie):

Ak je ako argument vyhľadávania zadané priezvisko študenta, ktoré nie je v rozsahu údajov, funkcia vráti chybu.

Príklad 2: Dvojfaktorové vyhľadávanie údajov

Vo vyššie uvedenom príklade sme manuálne zadali poradové číslo stĺpca údajov ako atribút „číslo_stĺpca“.

Ale čo ak chceme, aby bolo vyhľadávanie dynamické? Čo ak chceme zadať meno študenta a názov predmetu a funkcia automaticky vypíše požadované údaje? Na to musíme použiť dvojfaktorové vyhľadávanie.

Nižšie je uvedený príklad dvojfaktorového vyhľadávania:

Ak chcete vytvoriť dvojfaktorový vzorec vyhľadávania, je dôležité, aby bol výber údajov zo stĺpcov dynamický. Keď teda používateľ zmení názov predmetu: Fyzika, Matematika atď., zodpovedajúci atribút stĺpca údajov sa vo vzorci zmení.

Na to potrebujeme funkciu ako argument zodpovednú za poradové číslo stĺpca.

Takto by vyzeral vzorec pre dvojfaktorové vyhľadávanie:

- Anglická verzia

- ruská verzia

Vyššie uvedený vzorec používa funkciu , ktorá je zodpovedná za poradové číslo stĺpca údajov. prevezme meno subjektu ako hodnotu vyhľadávania (v bunke „H4“) a vráti jeho pozíciu v rozsahu buniek „A2:E2“.

Ak zadáte "Math", Excel vráti "2", pretože "Math" je v bunke "B2" (druhá v tomto poli).

Príklad 3: Použitie rozbaľovacieho zoznamu pre dvojfaktorové vyhľadávanie

Použitím metódy z príkladu č. 2 musíme urobiť veľa ručná práca. Existuje vysoké riziko, že sa pomýlite a stratíte veľa času, najmä ak pracujete s veľkým množstvom údajov.

Tu je lepšie použiť rozbaľovacie zoznamy ako hodnoty vyhľadávania (v našom príklade sú to mená študentov a akademických predmetov).

Na základe údajov, ktoré vyberiete z rozbaľovacieho zoznamu, funkcia automaticky zobrazí potrebné údaje, ako je znázornené v príklade nižšie:

Tento prístup vám umožňuje vytvoriť dashboard, pomocou ktorého môžete jednoducho spracovávať veľké zoznamy údajov.

Nižšie sa pozrieme na to, ako vyrobiť takúto tabuľku.

Na to potrebujeme vzorec z príkladu č. 2:

=VLOOKUP($G$5,$A$4:$E$12,MATCH($H$4,$A$3:$E$3,0);0) - Anglická verzia

=VLOOKUP($G$5,$A$4:$E$12,MATCH($H$4,$A$3:$E$3,0);0)- ruská verzia

Údaje vyhľadávania boli prevedené na rozbaľovacie zoznamy.

Ak chcete vytvoriť rozbaľovací zoznam, potrebujete:

• Vyberte bunku, v ktorej chcete vytvoriť rozbaľovací zoznam. V našom príklade sme do bunky „G4“ zadali mená študentov.
• Prejdite na kartu Údaje -> Overenie údajov.
• V dialógovom okne Overenie údajov na karte Nastavenia vyberte položku Zoznam a z rozbaľovacieho zoznamu vyberte možnosť Povoliť.
• V zdroji vyberte $A$3:$A$10
• Stlačte OK.

Teraz budete mať v bunke „G4“ rozbaľovacie pole. Podobne ho môžete spustiť v "H3" pre položky.

Príklad 4. Trojfaktorové vyhľadávanie údajov VLOOKUP

Čo je to trojfaktorové vyhľadávanie?

V príklade 2 sme použili jednu tabuľku so známkami pre študentov z rôznych predmetov. Toto je príklad dvojfaktorového vyhľadávania, pretože na získanie známky používame dve premenné (priezvisko študenta a predmet).

Teraz predpokladajme, že študent do konca roka zložil tri stupne skúšok: „Vstupné“, „Polročné“ a „Záverečné“.

Trojstranné vyhľadávanie je príležitosťou získať známky študenta z predmetu s určitou úrovňou skúšky.

Tu je príklad trojstranného vyhľadávania:

Vo vyššie uvedenom príklade funkcia vyhľadá údaje v troch rôzne tabuľky s údajmi („Úvodná“, „Polročná“ a „Záverečná skúška“) a vráti hodnoty známok študenta z konkrétneho predmetu na konkrétnej úrovni skúšky.

Pri takýchto výpočtoch nám pomôže vzorec:

=VLOOKUP(G5,CHOOSE(IF(H3=”Úvodná”,1,IF(H3=”Polročná”,2,3)),$A$4:$E$12,$A$16:$E$24,$ A$28 :$E$36),MATCH(H4,$A$3:$E$3,0);0) - Anglická verzia

=VLOOKUP(G5;SELECT(IF(H3=”Úvodná”;1;IF(H3=”Polročná”;2;3));$A$4:$E$12;$A$16:$E$24;$ A$28 :$E$36);MATCH(H4;$A$3:$E$3;0);0)- ruská verzia

Tento vzorec používa funkciu VYBRAŤ, aby ste sa uistili, že údaje sú vybraté z správnu tabuľku. Poďme analyzovať časť vzorca CHOOSE:

VYBRAŤ(AK(H3=”Úvodná”,1,AK(H3=”Polročná”,2,3)),$A$4:$E$12,$A$16:$E$24,$A$28:$E $36) ,MATCH(H4,$A$3:$E$3,0);0) - Anglická verzia

SELECT(IF(H3=”Úvodná”,1,IF(H3=”Polročná”,2,3)),$A$4:$E$12,$A$16:$E$24,$A$28:$E 36 USD) ;MATCH(H4;$A$3:$E$3;0);0)- ruská verzia

Prvý argument vzorca (IF(H3=”Úvodná”,1,IF(H3=”Polročná”,2,3) alebo (IF(H3=”Úvodná”;1;IF(H3=”Polročná”;2;3)) skontroluje bunku "H3" a určí, na ktorú úroveň skúšky sa odkazuje. Ak je "Úvodná", funkcia vráti údaje z rozsahu $A$4:$E$12, ktorý obsahuje známky za prijímaciu skúšku.

Ak je zvolená úroveň "Polročná", potom systém vráti údaje z rozsahu $A$16:$E$24, v opačnom prípade vráti údaje z rozsahu $A$28:$E$36.

Tento prístup robí pole tabuliek dynamickým a trojfaktorovým.

Príklad 5. Získajte poslednú hodnotu stĺpca pomocou funkcie VLOOKUP (VLOOKUP).

Cez VLOOKUP môžete vypočítať posledné číslo zo zoznamu.

Najväčšie kladné číslo, ktoré môžete v Exceli použiť, je 9,99999999999999E+307. To tiež znamená, že najviac veľká izba hľadanie miestnosti VLOOKUP tiež 9,99999999999999E+307 .

Nemyslím si, že budete niekedy potrebovať výpočet, ktorý by to zahŕňal veľké číslo. Ale s ním môžeme získať posledné číslo v zozname.

Povedzme, že máte množinu údajov (v rozsahu buniek A1:A14), ako je uvedené nižšie, a chcete získať číslo z poslednej bunky v zozname.

Na to potrebujeme nasledujúci vzorec:

=VLOOKUP(9,99999999999999E+307,$A$1:$A$14,PRAVDA)- Anglická verzia

=VLOOKUP(9,99999999999999E+307,$A$1:$A$14,PRAVDA)

Upozorňujeme, že vzorec uvedený vyššie používa približnú presnosť zhody údajov (napriek tomu nie je potrebné zoznam objednávať).

Tu je návod, ako funkcia funguje s približnou presnosťou zhody údajov. Ona si prezerá ľavý stĺpec zhora nadol.

• Ak nájde presnú zhodu, vráti túto hodnotu;
• Ak nájde číslo väčšie ako hľadaná hodnota => vráti číslo v bunke nad ním;
• Ak je hľadaná hodnota väčšia ako všetky čísla v zozname, funkcia vráti posledné číslo v zozname.

V našom príklade funguje tretí scenár.

číslo 9,99999999999999E+307 je najväčšie číslo, ktoré možno použiť v Exceli, keď sa použije ako kritérium vyhľadávania, potom funkcia VLOOKUP (VLOOKUP) vráti posledné číslo zo zoznamu.

Rovnakým spôsobom môžete použiť tento princíp na vrátenie posledného textový prvok zo zoznamu. Tu je vzorec, ktorý to dokáže:

=VLOOKUP("yay",$A$1:$A$14,1,PRAVDA) - Anglická verzia

=VLOOKUP("yee",$A$1:$A$14,1,PRAVDA)- ruská verzia

Tu platí rovnaká logika. Systém prehľadá všetky tituly v zozname. Keďže „yay“ je najväčšia textová hodnota => výsledkom výpočtu bude najextrémnejšia hodnota zo zoznamu údajov.

Príklad 6: Čiastočné vyhľadávanie pomocou zástupných znakov a VLOOKUP

Zástupné znaky v Exceli môžu byť užitočné v mnohých situáciách.

Čiastočné vyhľadávanie je potrebné, keď potrebujete v zozname vyhľadať hodnotu, ktorá sa presne nezhoduje s tým, čo hľadáte.

Napríklad máte množinu údajov, ako je uvedené nižšie, a chcete nájsť spoločnosť „ABC“ v zozname, ale zoznam obsahuje iba „ABC Ltd“ namiesto „ABC“.

Ako vyhľadávaciu hodnotu nemôžete použiť „ABC“, pretože v stĺpci „A“ neexistuje presná zhoda. Približná zhoda často vedie k chybným výsledkom a vyžaduje, aby bol zoznam zoradený vo vzostupnom poradí.

Môžete však použiť zástupný znak VLOOKUP (VLOOKUP) pre presnejšie porovnávanie údajov.

Do bunky „D2“ zadajte nasledujúci vzorec a presuňte ho do iných buniek:

=VLOOKUP("*"&C2&"*",$A$2:$A$8,1,NEPRAVDA)- Anglická verzia

=VLOOKUP("*"&C2&"*";$A$2:$A$8;1;NEPRAVDA) - ruská verzia

Ako to funguje?

Vo vzorci vyššie sme k hodnote vyhľadávania pridali hviezdičky na oboch stranách. Zadaním takýchto „hviezdičiek“ dáte Excel vedieť, že ste pripravený vykonať vyhľadávanie, v ktorom hľadané údaje na začiatku aj na konci hodnoty môžu obsahovať akékoľvek iné slová, písmená alebo čísla.

Pri vyhľadávaní teda Excel pochopí, že je potrebné hľadať hodnotu, ktorá obsahuje slovo z bunky „C2“ (v našom príklade).

Napríklad bunka "C2" obsahuje názov spoločnosti "ABC", takže funkcia hľadá hodnoty v rozsahu údajov "A2:A8" a hľadá "ABC". Nájde zhodu v bunke "A2", pretože obsahuje slovo "ABC" v "ABC Ltd". Nezáleží na tom, či sú vľavo alebo vpravo od „ABC“ nejaké znaky.

Poznámka: VLOOKUP vždy vráti prvú zodpovedajúcu hodnotu a prestane hľadať ďalej. Preto, ak máte názov spoločnosti "ABC Ltd". a "ABC Corporation" v zozname, vráti prvú v poradí a ostatné ignoruje.

Príklad 7: Funkcia VLOOKUP vracia chybu napriek zhode vo vyhľadávacej hodnote

Môže vás to priviesť k šialenstvu, keď uvidíte, že existuje zodpovedajúca vyhľadávacia hodnota a funkcia vráti chybu.

Napríklad máte v tabuľke zjavnú zhodu (John), ale systém stále vracia chybu.

Ak sa pozriete hlbšie do hodnôt v tabuľke, môžete si všimnúť, že v rozsahu údajov je hodnota „John“ napísaná s medzerou. Ak má rozsah údajov, ktoré hľadáte, medzery pred slovami, za nimi alebo medzi nimi, funkcia ich nebude zodpovedať.

Existuje riešenie a jeho meno je - Funkcia TRIM. Všetko vymaže priestory navyše vo významoch.

Nižšie je uvedený vzorec, ktorý vám pomôže priradiť údaje napriek ďalším medzerám v rozsahu údajov.

=VLOOKUP("Ján",TRIM($A$2:$A$15);1,0)- Anglická verzia

=VLOOKUP(“John”,FLATSPACES($A$2:$A$15);1,0) - ruská verzia

Aby pri používaní fungovala funkcia TRIM (TRIM). VLOOKUP (VLOOKUP), mali by ste zadať vzorec po napísaní nepoužívajte Zadajte klávesy na klávesnici a pomocou kombinácie Klávesy Ctrl+ Shift + Enter.

Príklad 8: Funkcia VLOOKUP v Exceli s viacerými podmienkami

Funkcia vo svojej základnej forme môže vyhľadať jednu vyhľadávanú hodnotu a vrátiť zodpovedajúce údaje zo zadaného rozsahu údajov.

Ale často musíme použiť VLOOKUP s viacerými kritériami vyhľadávania.

Predstavme si, že máte tabuľku s výsledkami skúšok študentov pre tri stupne skúšok: „Úvodná“, „Polročná“ a „Záverečná“:

Pomocou funkcie spárujte údaje pre konkrétneho študenta, predmet a úroveň skúšky VLOOKUP nie je to ľahká úloha, pretože porovnaním údajov o študentovi a predmete poskytne výsledok prvého zápasu, ale nie je ani zďaleka pravda, že tento výsledok bude správny a korešpondujúci. požadovaná úroveň skúška.

Môžete vyhľadávať podľa študenta + názvu predmetu + úrovne skúšky vytvorením pomocného stĺpca, ako je uvedené v príklade nižšie:

Vďaka pomocnému stĺpcu sme sa vyhli tomu, že pri porovnávaní údajov funkcia vráti údaje za zbytočnú úroveň skúšky.

Teraz môžete použiť hodnoty pomocného stĺpca ako vyhľadávacie hodnoty.

Nižšie je uvedený vzorec, ktorý možno použiť na pohodlné priraďovanie údajov z tabuľky k zoznamu študentov kategorizovaných podľa úrovne skúšky.

=VLOOKUP($F3&”|”&G$2,$C$2:$D$19,2,0)- Anglická verzia

=VLOOKUP($F3&”|”&G$2;$C$2:$D$19;2;0)- ruská verzia

S týmto vzorcom sme spojili meno študenta a úroveň skúšky, aby sme získali vyhľadávaciu hodnotu, ktorú otestujeme v stĺpci pomocníka a získame údaje.

Príklad 9: Spracovanie chýb pomocou funkcie VLOOKUP

Funkcia vráti chybu vždy, keď sa nedokáže zhodovať s vyhľadávanou hodnotou v danom rozsahu údajov.

Excel má možnosť nahradiť chybové hlásenia textom resp číselná hodnota zadajte „Žiadne“, „Nie je k dispozícii“, „Žiadne údaje“ atď.

V nižšie uvedenom príklade sa snažíme porovnať výsledky skúšky študenta Ivana z tabuľky. Ale keďže v tabuľke nie sú žiadne údaje pre Ivana, funkcia vyhodí chybu.

Aby sme chybové hlásenie nahradili nejakou formálnejšou hodnotou, pomôže nám funkcia

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 výkone VLOOKUP

V skutočnosti chcem v tomto článku hovoriť o možnostiach nielen funkcie VLOOKUP, ale chcem sa aj dotknúť ZÁPAS, ako veľmi súvisiacu funkciu s funkciou VLOOKUP. Každá z týchto funkcií má svoje klady aj zápory. Stručne povedané, VLOOKUP hľadá nejakú hodnotu, ktorú sme zadali, medzi súborom hodnôt umiestnených v jednom stĺpci. Možno najčastejšie vzniká potreba VLOOKUP, 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ď.
Pre lepšie pochopenie princípu VLOOKUP je lepšie začať s niektorými prípadová štúdia. Existuje takáto tabuľka:
obr.1

a z prvej tabuľky je potrebné nahradiť v druhom dátume za každé priezvisko. Pri troch vstupoch to nie je problém a robiť to ručne je samozrejmosťou. Ale v reálnom živote sú to tabuľky s tisíckami záznamov a vyhľadávanie s manuálnym nahrádzaním údajov môže trvať viac ako jednu hodinu. Plus pár much: nielenže sú celé mená v oboch tabuľkách v úplne inom poradí a počet záznamov v tabuľkách je iný, ale tabuľky sa nachádzajú aj na iných listoch/knihách. Verím, že som vás presvedčil, že manuálne nahradenie údajov nie je vôbec možné. ale VLOOKUP tu bude nevyhnutné. 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árok1!$A$2:$C$4 ;3;0)
Vzorec môžete napísať buď priamo do bunky, alebo pomocou správcu funkcií výberom v kategórii Referencie a polia VLOOKUP a individuálne špecifikovanie požadovaných kritérií. Teraz skopírujte ( ctrl+C) bunku so vzorcom, vyberte všetky bunky stĺpca 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. VLOOKUP môže vrátiť iba jednu hodnotu – prvú, ktorá vyhovuje kritériám. Ak sa nenájde hodnota, ktorú hľadáte (nie je v tabuľke), potom bude výsledok funkcie #N/A . Netreba sa toho báť – 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. Znamená to, ž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 v takejto bunke sa objaví 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 Petrov S.A. Tu môžete zadať buď priamo text kritéria (v tomto prípade by mal byť v úvodzovkách - =VLOOKUP("Petrov SA" ;Hárok1!$A$2:$C$4;3;0) ), alebo odkaz na bunku 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 užitočné, ak potrebujete nájsť hodnoty iba pre časť reťazca. Nemôžete napríklad zadať „Petrov S.A“ 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 kdekoľvek v riadku vyskytuje priezvisko „Petrov“, môžete ho špecifikovať takto: „*petrov*“ . Ak chcete nájsť priezvisko Petrov a nezáleží na tom, aké iniciály bude mať krstné meno a priezvisko (ak je celé meno napísané v tvare Ivanov II), potom je nasledujúci vzhľad správny: „Ivanov?. ?." . Často je potrebné zadať hodnotu pre každý riadok (v stĺpci A Priezviská 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é zaznamenať 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ý položke „Ivanov*“. V A1 je napísané 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 veľmi výhodné, ak existuje veľa hodnôt na vyhľadávanie.
Ak potrebujete zistiť, či je v riadku aspoň niekde slovo, vložte hviezdičky na obe strany: „*“ & A1 & „*“

Hárok1!$A$2:$C$4 – argument tabuľky. Určuje rozsah buniek. Iba rozsah musí obsahovať údaje od prvej bunky s údajmi až po úplne poslednú. Nemusí to byť rozsah uvedený v príklade. Ak existuje 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 musí vždy začínať stĺpcom, v ktorom hľadáme Kritérium . A nič iné. Inak sa nič nenájde alebo výsledok nebude taký, aký očakávate. Po druhé: argument tabuľky treba "opraviť" . Čo to znamená. Vidieť znaky dolára - $? Toto je zapínanie (presnejšie je to tzv 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 neuvidíte, že pred názvom stĺpca ani pred číslom riadku nie sú 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 musí obsahovať stĺpce od prvého (v ktorom hľadáme) po posledný (z ktorého je potrebné vrátiť hodnoty). V príklade Hárok1!$A$2:$C$4- znamená, že nebude možné vrátiť hodnotu zo stĺpca D (4), pretože Tabuľka má iba tri stĺpce.

3 - Číslo_stĺpca. Tu jednoducho špecifikujeme číslo stĺpca v argumente tabuľky, hodnoty, z ktorých musíme vo výsledku dosadiť. V príklade ide o Dátum prijatia – t.j. stĺpec číslo 3. Ak by sme potrebovali odbor, označili by sme 2 a ak by sme potrebovali len porovnať, či sú priezviská jednej tabuľky v inej, tak 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!. Napríklad, ak je rozsah $B$2:$C$4 a chcete vrátiť údaje zo stĺpca C, potom je správne zadať 2. 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é číslo je na hárku), dostaneme chybu #LINK!, pretože v uvedenom rozsahu jednoducho nie je žiadny tretí stĺpec.

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

0 - interval_lookup je velmi zaujimavy argument. Môže byť buď TRUE alebo FALSE. 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 daný parameter rovná 0 alebo FALSE, potom sa vyhľadá presná zhoda so zadanými kritériami. Nemá to nič spoločné so zástupnými znakmi ("*" a "?"). Ak použijete 1 alebo TRUE (alebo neuvediete posledný argument vôbec, pretože je štandardne TRUE), potom... 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 túto možnosť, potom je potrebné, aby bol zoznam v argumente Tabuľka zoradený vzostupne. Upozorňujem na skutočnosť, že triedenie je potrebné iba vtedy, ak je argument interval_lookup TRUE alebo 1. Ak je 0 alebo FALSE, triedenie nie je potrebné.

Mnohí si asi všimli, že na obrázku som si pomýlil oddelenia na celý názov. Toto nie je chyba zápisu. 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 by mohol prísť vhod.

Ako sa vyhnúť chybe #N/A(#N/A) vo VLOOKUP?
Ešte bežný problém- veľa ľudí nechce vidieť výsledok #N/A, ak sa nenájde žiadna zhoda. Je ľahké obísť:
=AK(KONIEC(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.
A používatelia verzie Excelu 2007 a vyššie môžete použiť IFERROR:
=IFERROR(VLOOKUP($A2 ;Hárok1! $A$2:$C$4 ;3;0);"")

Sľúbené HĽADANIE

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

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

Map_type(0) - rovnako ako vo VPR interval_lookup . s rovnakými vlastnosťami. Líši sa len v možnosti nájsť najmenší od želaného či najväčšieho. Tomu sa ale v tomto článku nebudem venovať.

Pochopil hlavné. Ale koniec koncov musíme vrátiť nie číslo pozície, ale samotnú hodnotu. Takže MATCH in čistej forme nám nevyhovuje. Aspoň jeden, sám od seba. Ale ak sa používa spolu s funkciou INDEX, tak to potrebujeme a ešte viac.
=INDEX(Hárok1! $A$2:$C$4 ;MATCH($A2 ;Hárok1! $A$2:$A$4 ;0);2)
Takýto 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 to byť jeden stĺpec alebo niekoľko. 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ý špecifikujeme v argumente Viewed_array funkcie MATCH.

Ďalej nasleduje číslo_riadka a číslo_stĺpca. Ako Line_Number nahradíme MATCH, čo 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 (Číslo riadku) poľa a zadanom stĺpci (Číslo_stĺpca), 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 Lookup_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 pri VLOOKUP, INDEX vráti #N/A, ak sa nenájde hľadaná 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(KONIEC(ZHODA($A2 ;Hárok1! $A$2:$A$4 ;0)),"";INDEX(Hárok1! $A$2:$C$4 ;ZHODA($A2 ;Hárok1! $A$2: $A$4 ;0);2))
Pre verzie 2007 a vyššie:
=IFERROR(INDEX(Hárok1! $A$2:$C$4 ;MATCH($A2 ;Hárok1! $A$2:$A$4 ;0);2);"")

Práca s kritériami dlhšími ako 255 znakov
INDEX-MATCH má oproti VLOOKUP ešte jednu výhodu. Faktom je, že funkcia VLOOKUP nedokáže vyhľadať hodnoty, ktorých dĺžka reťazca obsahuje viac ako 255 znakov. To sa stáva zriedka, ale stáva sa to. Môžete samozrejme podvádzať VLOOKUP a skrátiť kritérium:
=VLOOKUP(MID($A2 ;1;255);MID(Hárok1!$A$2:$C$4 ;1;255);3;0)
ale je to vzorec poľa. A okrem toho, nie vždy takýto vzorec 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 znamienka líšia, vzorec to už neuvidí. Áno, a vzorec sa vráti výlučne textové hodnoty, čo nie je veľmi výhodné v prípadoch, keď by sa mali čísla vrátiť.

Preto je lepšie použiť takýto zložitý vzorec:
=INDEX(Hárok1!$A$2:$C$4 ;SUMPRODUKT(ZHODA(PRAVDA;Hárok1!$A$2:$A$4 =$A2 ;0));2)
Tu som použil rovnaké rozsahy vo vzorcoch pre čitateľnosť, ale v príklade sťahovania sa líšia od tých, ktoré sú tu uvedené.
Samotný vzorec je postavený na schopnosti funkcie SUMPRODUCT transformovať sa do masívnych výpočtov niektorých funkcií v ňom. V tomto prípade MATCH hľadá pozíciu riadku, kde je kritérium sa rovná hodnote v rade. Tu už nie je možné použiť 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":"bottom","textautohide":true,"textpositionmarginstatic":0,"textpositiondynamic":"vľavo","textpositionmarginleft":24," textpositionmarginright":24,"textpositionmargintop":24,"textpositionmarginbottom":24,"texteffect":"slide","texteffecteasing":"easeOutCubic","texteffectduration":600,"texteffectslidedirection":"left","texteffectslidedistance" :30,"texteffectdelay":500,"texteffectseparate":false,"texteffect1":"slide","texteffectslidedirection1":"right","texteffectslidedistance1":120,"texteffecteasing1":"easeOutCubic","texteffectduration1":600 ,"texteffectdelay1":1000,"texteffect2":"slide","texteffectslidedirection2":"right","texteffectslidedistance2":120,"texteffecteasing2":"easeOutCubic","texteffectduration2":600,"texteffectdelay2":150," textcss":"display:block; padding:12px; text-align:left;","textbgcss":"display:block; position:absolute; top:0px; left:0px; width:100%; height:100% ; farba pozadia:#333333; nepriehľadnosť:0,6; filter:a lpha(opacity=60);","titlecss":"display:blok; poloha:relatívna; font:bold 14px \"Lucida Sans Unicode\",\"Lucida Grande\",sans-serif,Arial; color:#fff;","descriptioncss":"displej:blok; poloha:relatívna; font:12px \"Lucida Sans Unicode\",\"Lucida Grande\",sans-serif,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))

Všetci vieme, aké je to užitočné. Pravdepodobne polovica všetkých akcií v Exceli sa vykonáva pomocou neho. ale danú funkciu má množstvo obmedzení. Napríklad funkcia VLOOKUP sa pozerá iba na stĺpec úplne vľavo v tabuľke alebo hľadá iba jednu podmienku. Čo ak však potrebujeme vrátiť hodnotu, ktorá spĺňa dve podmienky. V tomto prípade sa budeme musieť uchýliť k niektorým trikom. O týchto trikoch si povieme v dnešnom článku.

Takže zvážime štyri možnosti na vytvorenie substitučnej funkcie s dvoma podmienkami:

1. Pomocou funkcie SUMPRODUCT

No, začnime tým najjednoduchším.

Použitie dodatočného stĺpca

Väčšinou ťažké problémy stanú sa ľahšie a lepšie zvládnuteľné, keď sa rozdelia na menšie kúsky. To isté platí pre vytváranie vzorcov v Exceli.

Zvážte klasický príklad. Máme tabuľku s predajom podľa mesiaca a mesta. A musíme určiť hodnotu predaja zodpovedajúcu dvom podmienkam: mesiac je február a mesto je Samara.

Použitie funkcie VLOOKUP v jej klasickej podobe nám nepomôže, keďže dokáže vrátiť hodnotu, ktorá vyhovuje len jednej podmienke. Zo situácie nám pomôže ďalší stĺpec, v ktorom skombinujeme hodnoty stĺpcov Mesiac a Mesto. Ak to chcete urobiť, do bunky A2 napíšte vzorec =B2&C2 a rozšírte tento vzorec na bunku A13. Teraz môžeme použiť hodnoty stĺpca A na vrátenie požadovanej hodnoty. Do bunky G3 zapíšeme vzorec:

VLOOKUP(G1A2:D13;4;0)

Tento vzorec kombinuje dve podmienky buniek G1 a G2 do jedného riadku a vyhľadá ho v stĺpci A. Po nevyhnutná podmienka bol nájdený, vzorec vráti hodnotu zo štvrtého stĺpca tabuľky A1:D13, t.j. stĺpec Predaj.

Pomocou funkcie SELECT vytvorte novú vyhľadávaciu tabuľku

Ak z akéhokoľvek dôvodu dôjde k použitiu dodatočný stĺpec nie je pre nás možnosťou, môžeme použiť .

Použitie funkcie SELECT znamená vytváranie nový stôl pre zobrazenie, v ktorom sú hodnoty stĺpca mesiac A Mesto už zlúčené. Náš vzorec bude vyzerať takto:

VLOOKUP(G1SELECT((1;2);B2:B13&C2:C13;D2:D13);2;0)

Vrcholom tohto vzorca je časť SELECT((1;2);B2:B13&C2:C13;D2:D13), ktorá robí dve veci:

1. Spája hodnoty stĺpcov mesiac A Mesto do jedného poľa: JanvMoskva, FebMoskva ...
2. Zlúči dve polia do tabuľky s dvoma stĺpcami.

Výsledkom tejto funkcie bude tabuľka, ktorá vyzerá takto:

Vzorec je teraz jasnejší.

DÔLEŽITÉ: Keďže sme použili vzorec poľa, po dokončení zadávania vzorca stlačte Ctrl+Shift+Enter, aby program vedel o našich zámeroch. Po stlačení tejto kombinácie kláves sa program automaticky nainštaluje rovnátka na začiatku a na konci vzorca.

Používanie funkcií INDEX a MATCH

Tretia metóda, ktorú zvážime, tiež zahŕňa použitie maticového vzorca a používa funkcie INDEX a MATCH.

Vzorec bude vyzerať takto.

INDEX(D2:D13;ZHODA(1,(B2:B13=G1)*(C2:C13=G2);0))

Poďme sa pozrieť na to, čo robí každá časť tohto vzorca.

Najprv zvážte funkciu MATCH(1;(B2:B13=G1)*(C2:C13=G2);0). V tomto prípade sa hodnota bunky G1 postupne porovnáva s každou hodnotou buniek v rozsahu B2:B13 a vráti sa TRUE, ak sa hodnoty zhodujú, a FALSE, ak nie. Rovnaké porovnanie sa vykoná s hodnotou bunky G2 a rozsahom C2:C13. Ďalej porovnáme obe tieto polia pozostávajúce z TRUE a FALSE. Kombinácia TRUE * TRUE nám dáva výsledok 1 (TRUE). Pozrime sa na obrázok nižšie, ktorý pomôže jasnejšie vysvetliť princíp fungovania.

Teraz môžeme povedať, kde je čiara, ktorá spĺňa obe podmienky. Funkcia MATCH vyhľadá pozíciu 1 vo výslednom poli a vráti 6, pretože 1 sa nachádza v šiestom riadku. Funkcia INDEX potom vráti hodnotu šiesteho riadku v rozsahu D2:D13.

Pomocou SUMPRODUCT

Jeden z najmocnejších Excel vzorce. Dokonca mám samostatný článok venovaný tomuto vzorcu. Náš štvrtý spôsob využitia viacerých podmienok je napísanie vzorca pomocou funkcie SUMPRODUCT. A bude to vyzerať takto:

SUMPRODUCT((B2:B13=G1)*(C2:C13=G2);D2:D13)

Princíp fungovania tohto vzorca je podobný princípu fungovania predchádzajúceho prístupu. Vytvorí sa virtuálna tabuľka, v ktorej sa porovnávajú hodnoty buniek G1 a G2 s rozsahmi B2:B13 a C2:C13. Ďalej sa obe tieto polia porovnajú a získa sa pole jednotiek a núl, pričom jedna je priradená k riadku, v ktorom sa obe podmienky zhodujú. Ďalej je toto virtuálne pole vynásobené rozsahom D2:D13. Keďže naše virtuálne pole bude mať iba jednu jednotku v šiestom riadku, vzorec vráti výsledok 189.

Táto funkcia nebude fungovať, ak sú textové hodnoty v rozsahu D2:D13.

Aby ste pochopili, ako to funguje daný vzorec, odporúčam prečítať si článok o funkcii SUMPRODUCT.

CELKOM

Aký spôsob teda použiť? Hoci všetky fungujú stabilne, preferujem prvý spôsob. Vo svojej každodennej práci uprednostňujem prácu so súbormi, ktoré sú ľahko zrozumiteľné a ľahko sa menia. Obe tieto požiadavky spĺňajú podmienky prvého prístupu.

Pre lepšie pochopenie vzorcov si ich môžete rozobrať v dnešnom článku.

Povedzme, že máme tabuľku s cenníkom tovaru. Úlohou je vyplniť tabuľku Objednávok.

Na vyriešenie tohto problému použijeme Funkcia VLOOKUP. V Exceli sú ďalšie funkcie, pomocou ktorých môžete riešiť túto úlohu, ale rozumieme si najobľúbenejšia a najčastejšie používaná funkcia VLOOKUP v Exceli.

Takže, aby sme vyriešili problém v našom príklade, musíme najprv vyplniť stĺpec "OD" v tabuľke objednávok, t.j. nájdite cenu tovaru v tabuľke „Cenník“ a následne pre zistenie nákladov vynásobte cenu počtom tovaru.

Syntax funkcie VLOOKUP v Exceli

VLOOKUP(vyhľadávacia_hodnota, tabuľka, číslo_stĺpca, [vyhľadávanie])

Funkcia VLOOKUP vyhľadá hodnotu v ľavom stĺpci tabuľky a vráti hodnotu bunky v určenom stĺpci toho istého riadku.

Analyzujme funkciu na našom príklade. Vložte sadzby do bunky C3 a zadajte vzorec =VLOOKUP(A3;$F$2:$H$22;3;0)

v Anglická verzia=VLOOKUP(A3;$F$2:$H$22;3;0)

V tomto vzorci s VLOOKUP (eng. VLOOKUP):

A3- vyhľadávaná_hodnota. V našom prípade je to "Bucket", t.j. musíme nájsť "vedro" v tabuľke "Cenník"

$F$2:$H$22 - tabuľky. V našom príklade ide o tabuľku Cenník (F2:H22). Do rozsahu tejto tabuľky sa vkladajú znaky $, aby sa zabezpečilo, že sa nepohne nadol, keď pretiahneme vzorec. Znak dolára v Exceli zmení relatívny rozsah na absolútny.

3 – číslo stĺpca. V našom prípade je to číslo „3“, keďže cena je v našej tabuľke „Cenník“ v treťom stĺpci.

0 - intervalové zobrazenie. Môže nadobudnúť iba dve hodnoty 0 alebo 1: 0 - hľadá presnú zhodu, 1 - približnú. V 99% prípadov treba hľadať presná hodnota(v našom prípade musíme hľadať slovo "Bucket"). Preto je takmer vždy uvedené číslo 0.

Touto cestou, Logika funkcie Excel VLOOKUP v našom príklade je nasledujúci. Funkcia hľadá požadovanú hodnotu („vedro“) v ľavom stĺpci tabuľky („Cenník“), po jej nájdení vráti hodnotu bunky umiestnenej v zadanom stĺpci. ten istý riadok, t.j. cena je 120 rubľov.

Potom choďte do cely D3 a zistí cenu tovaru. Predpíšeme vzorec =C3*B3, t.j. cenu tovaru vynásobíme množstvom.

Ďalej, aby ste automaticky vložili vzorce pre zvyšok tovaru, musíte vzorec potiahnuť nadol. Ak to chcete urobiť, vyberte obe bunky, ktoré je potrebné roztiahnuť, a potiahnite nadol pravý dolný roh (pozri obrázok 3)