Príklady rozšíreného VLOOKUP: Vyhľadávanie podľa viacerých kritérií

V druhej časti nášho tutoriálu o funkcii VPR (VLOOKUP) v Exceli rozoberieme niekoľko príkladov, ktoré vám pomôžu nasmerovať všetku silu VPR na riešenie najambicióznejších úloh programu Excel. Príklady predpokladajú, že už máte základné vedomosti o tom, ako táto funkcia funguje. Ak nie, možno vás bude zaujímať, ak začnete s prvou časťou tohto návodu, ktorá vysvetľuje syntax a základné použitie. VPR. Nuž, začnime.

• Hľadajte v Exceli podľa viacerých kritérií
• 2., 3. atď. hodnoty extrahujeme pomocou funkcie VLOOKUP
• Získajte všetky opakovania požadovanej hodnoty
• XNUMXD vyhľadávanie podľa známeho riadku a stĺpca
• Použitie viacerých VLOOKUPs v jednom vzorci
• Dynamická substitúcia údajov z rôznych tabuliek

Hľadajte v Exceli podľa viacerých kritérií

Funkcie VPR v Exceli je skutočne výkonný nástroj na vyhľadávanie konkrétnej hodnoty v databáze. Je tu však výrazné obmedzenie – jeho syntax umožňuje hľadať iba jednu hodnotu. Čo ak chcete vyhľadávať podľa viacerých podmienok? Riešenie nájdete nižšie.

Príklad 1: Vyhľadávanie podľa 2 rôznych kritérií

Predpokladajme, že máme zoznam objednávok a chceme ich nájsť Množstvo tovaru (Množstvo) na základe dvoch kritérií – Meno klienta (Zákazník) a Názov produktu (Produkt). Vec je komplikovaná skutočnosťou, že každý z kupujúcich si objednal niekoľko druhov tovaru, ako je zrejmé z nižšie uvedenej tabuľky:

pravidelná funkcia VPR v tomto scenári nebude fungovať, pretože vráti prvú nájdenú hodnotu, ktorá sa zhoduje s danou hodnotou vyhľadávania. Napríklad, ak chcete vedieť množstvo položky sladkostiobjednané kupujúcim Jeremy Hill, napíšte nasledujúci vzorec:

=VLOOKUP(B1,$A$5:$C$14,3,FALSE)

=ВПР(B1;$A$5:$C$14;3;ЛОЖЬ)

– tento vzorec vráti výsledok 15zodpovedajúce produktu jablká, pretože je to prvá hodnota, ktorá sa zhoduje.

Existuje jednoduché riešenie – vytvorte ďalší stĺpec, v ktorom skombinujete všetky požadované kritériá. V našom príklade sú to tieto stĺpce Meno klienta (Zákazník) a Názov produktu (Produkt). Nezabudnite, že zlúčený stĺpec musí byť vždy stĺpcom úplne vľavo v rozsahu vyhľadávania, pretože je to ľavý stĺpec, ktorý VPR vyhľadáva pri hľadaní hodnoty.

Takže do tabuľky pridáte pomocný stĺpec a skopírujete nasledujúci vzorec cez všetky jeho bunky: = B2 a C2. Ak chcete, aby bol reťazec čitateľnejší, môžete kombinované hodnoty oddeliť medzerou: =B2&» «&C2. Potom môžete použiť nasledujúci vzorec:

=VLOOKUP("Jeremy Hill Sweets",$A$7:$D$18,4,FALSE)

=ВПР("Jeremy Hill Sweets";$A$7:$D$18;4;ЛОЖЬ)

or

=VLOOKUP(B1,$A$7:$D$18,4,FALSE)

=ВПР(B1;$A$7:$D$18;4;ЛОЖЬ)

Kde je bunka B1 obsahuje zreťazenú hodnotu argumentu lookup_value (vyhľadávacia_hodnota) a 4 – Hádka col_index_num (column_number), teda číslo stĺpca obsahujúceho údaje, ktoré sa majú načítať.

Príklad 2: VLOOKUP podľa dvoch kritérií s tabuľkou zobrazenou na inom hárku

Ak potrebujete aktualizovať hlavnú tabuľku (Hlavná tabuľka) pridaním údajov z druhej tabuľky (Vyhľadávacia tabuľka), ktorá sa nachádza na inom hárku alebo v inom excelovom zošite, potom môžete požadovanú hodnotu zhromaždiť priamo vo vzorci, ktorý vložíte. do hlavného stola.

Rovnako ako v predchádzajúcom príklade budete potrebovať pomocný stĺpec vo vyhľadávacej tabuľke s kombinovanými hodnotami. Tento stĺpec musí byť stĺpec úplne vľavo v rozsahu vyhľadávania.

Takže vzorec s VPR môže byť takto:

=VLOOKUP(B2&" "&C2,Orders!$A&$2:$D$2,4,FALSE)

=ВПР(B2&" "&C2;Orders!$A&$2:$D$2;4;ЛОЖЬ)

Tu stĺpce B a C obsahujú mená zákazníkov a názvy produktov a prepojenie Objednávky!$A&$2:$D$2 definuje tabuľku, ktorá sa má vyhľadať v inom hárku.

Ak chcete, aby bol vzorec čitateľnejší, môžete pomenovať rozsah zobrazenia a vzorec potom bude vyzerať oveľa jednoduchšie:

=VLOOKUP(B2&" "&C2,Orders,4,FALSE)

=ВПР(B2&" "&C2;Orders;4;ЛОЖЬ)

Aby vzorec fungoval, hodnoty v ľavom stĺpci tabuľky, ktorú si prezeráte, musia byť kombinované presne rovnakým spôsobom ako v kritériách vyhľadávania. Na obrázku vyššie sme skombinovali hodnoty u2bu2band a medzi ne vložili medzeru rovnakým spôsobom, ako to musíte urobiť v prvom argumente funkcie (BXNUMX& „“ & CXNUMX).

Pamätať! Funkcie VPR obmedzený na 255 znakov, nemôže vyhľadať hodnotu, ktorá je dlhšia ako 255 znakov. Majte to na pamäti a uistite sa, že dĺžka požadovanej hodnoty nepresahuje túto hranicu.

Súhlasím, že pridanie pomocného stĺpika nie je najelegantnejšie a nie vždy prijateľné riešenie. To isté môžete urobiť aj bez pomocného stĺpca, ale to by si vyžadovalo oveľa zložitejší vzorec s kombináciou funkcií INDEX (INDEX) a MATCH (VIAC EXPOZOVANÉ).

2., 3. atď. hodnoty extrahujeme pomocou funkcie VLOOKUP

Už to vieš VPR môže vrátiť iba jednu zodpovedajúcu hodnotu, presnejšie prvú nájdenú. Čo ak sa však táto hodnota v prezeranom poli niekoľkokrát opakuje a vy chcete extrahovať 2. alebo 3. z nich? Čo ak všetky hodnoty? Problém sa zdá byť komplikovaný, ale riešenie existuje!

Predpokladajme, že jeden stĺpec tabuľky obsahuje mená zákazníkov (meno zákazníka) a druhý stĺpec obsahuje produkty (produkt), ktoré si kúpili. Skúsme nájsť 2., 3. a 4. položku zakúpenú daným zákazníkom.

Najjednoduchším spôsobom je pridať pomocný stĺpec pred stĺpec Meno zákazníka a vyplňte ho menami zákazníkov s číslom opakovania každého mena, napr. Ján Doe1, Ján Doe2 atď. Trik s číslovaním urobíme pomocou funkcie COUNTIF (COUNTIF), keďže mená zákazníkov sú v stĺpci B:

=B2&COUNTIF($B$2:B2,B2)

=B2&СЧЁТЕСЛИ($B$2:B2;B2)

Potom môžete použiť normálnu funkciu VPRnájsť požadovanú objednávku. Napríklad:

• nájsť 2-teho tovar objednaný zákazníkom Dan Brown:

  =VLOOKUP("Dan Brown2",$A$2:$C$16,3,FALSE)

  =ВПР("Dan Brown2";$A$2:$C$16;3;ЛОЖЬ)

• nájsť 3-teho tovar objednaný zákazníkom Dan Brown:

  =VLOOKUP("Dan Brown3",$A$2:$C$16,3,FALSE)

  =ВПР("Dan Brown3";$A$2:$C$16;3;ЛОЖЬ)

V skutočnosti môžete namiesto textu zadať odkaz na bunku ako vyhľadávaciu hodnotu, ako je znázornené na nasledujúcom obrázku:

Ak len hľadáte 2-e opakovanie, môžete to urobiť bez pomocného stĺpca vytvorením zložitejšieho vzorca:

=IFERROR(VLOOKUP($F$2,INDIRECT("$B$"&(MATCH($F$2,Table4[Customer Name],0)+2)&":$C16"),2,FALSE),"")

=ЕСЛИОШИБКА(ВПР($F$2;ДВССЫЛ("$B$"&(ПОИСКПОЗ($F$2;Table4[Customer Name];0)+2)&":$C16");2;ИСТИНА);"")

V tomto vzorci:

• $ F $ 2 – bunka obsahujúca meno kupujúceho (je nezmenené, upozorňujeme, že odkaz je absolútny);
• $ B $ – stĺpec Meno zákazníka;
• Table4 – Váš stôl (toto miesto môže byť aj bežný sortiment);
• 16 dolárov – koncová bunka vašej tabuľky alebo rozsahu.

Tento vzorec nájde iba druhú zodpovedajúcu hodnotu. Ak potrebujete extrahovať zostávajúce opakovania, použite predchádzajúce riešenie.

Ak potrebujete zoznam všetkých zhôd – funkcia VPR toto nie je pomocník, pretože vracia vždy len jednu hodnotu – bodku. Ale Excel má funkciu INDEX (INDEX), ktorý sa s touto úlohou ľahko vyrovná. Ako bude takýto vzorec vyzerať, sa dozviete v nasledujúcom príklade.

Získajte všetky opakovania požadovanej hodnoty

Ako je spomenuté vyššie VPR nemôže extrahovať všetky duplicitné hodnoty z naskenovaného rozsahu. Na to potrebujete trochu zložitejší vzorec, tvorený niekoľkými funkciami Excelu, ako napr INDEX (INDEX), SMALL (MALÁ) a ROW (LINE)

Napríklad vzorec uvedený nižšie nájde všetky opakovania hodnoty z bunky F2 v rozsahu B2:B16 a vráti výsledok z rovnakých riadkov v stĺpci C.

{=IFERROR(INDEX($C$2:$C$16,SMALL(IF($F$2=B2:B16,ROW(C2:C16)-1,""),ROW()-3)),"")}

{=ЕСЛИОШИБКА(ИНДЕКС($C$2:$C$16;НАИМЕНЬШИЙ(ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;"");СТРОКА()-3));"")}

Zadajte tento vzorec poľa do viacerých susedných buniek, ako sú bunky F4: F8ako je znázornené na obrázku nižšie. Počet buniek musí byť rovnaký alebo väčší ako maximálny možný počet opakovaní hľadanej hodnoty. Nezabudnite kliknúť Ctrl + Shift + Entersprávne zadať vzorec poľa.

Ak máte záujem pochopiť, ako to funguje, poďme sa trochu ponoriť do podrobností vzorca:

Časť 1:

IF($F$2=B2:B16,ROW(C2:C16)-1,"")

ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;"")

$F$2=B2:B16 – porovnajte hodnotu v bunke F2 s každou z hodnôt v rozsahu B2:B16. Ak sa nájde zhoda, potom výraz STRING(C2:C16)-1 vráti číslo zodpovedajúceho riadku (hodnota -1 umožňuje nezahrnúť riadok hlavičky). Ak neexistujú žiadne zhody, funkcia IF (IF) vráti prázdny reťazec.

Výsledok funkcie IF (IF) bude takéto horizontálne pole: {1,"",3,"",5,"","","","","","",12,"","",""}

Časť 2:

ROW()-3

СТРОКА()-3

Tu je funkcia ROW (LINE) funguje ako dodatočné počítadlo. Keďže vzorec sa skopíruje do buniek F4:F9, číslo odpočítame 3 z výsledku funkcie na získanie hodnoty 1 v cele F4 (riadok 4, odčítanie 3) dostať 2 v cele F5 (riadok 5, odčítanie 3) atď.

Časť 3:

SMALL(IF($F$2=B2:B16,ROW(C2:C16)-1,""),ROW()-3))

НАИМЕНЬШИЙ(ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;"");СТРОКА()-3))

Funkcie SMALL (MALÁ) sa vracia žiadne h najmenšia hodnota v dátovom poli. V našom prípade, ktorá pozícia (od najmenšej) sa má vrátiť, určuje funkcia ROW (LINE) (pozri časť 2). Takže pre bunku F4 funkcie MALÝ({pole};1) Vracia 1-teho (najmenší) prvok poľa, tj 1. Pre bunku F5 Vracia 2-teho najmenší prvok v poli, tj 3, Atď

Časť 4:

INDEX($C$2:$C$16,SMALL(IF($F$2=B2:B16,ROW(C2:C16)-1,""),ROW()-3))

ИНДЕКС($C$2:$C$16;НАИМЕНЬШИЙ(ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;"");СТРОКА()-3))

Funkcie INDEX (INDEX) jednoducho vráti hodnotu konkrétnej bunky v poli C2:C16. Pre bunku F4 funkcie INDEX($C$2:$C$16) vráti sa jablkápre F5 funkcie INDEX($C$2:$C$16) vráti sa sladkosti a tak ďalej.

Časť 5:

IFERROR()

ЕСЛИОШИБКА()

Nakoniec vzorec vložíme do funkcie IFERROR (IFERROR), pretože pravdepodobne nebudete spokojní s chybovým hlásením #AT (#N/A), ak je počet buniek, do ktorých sa vzorec skopíruje, menší ako počet duplicitných hodnôt v prezeranom rozsahu.

XNUMXD vyhľadávanie podľa známeho riadku a stĺpca

Vyhľadávanie XNUMXD v Exceli zahŕňa vyhľadávanie hodnoty podľa známeho čísla riadku a stĺpca. Inými slovami, extrahujete hodnotu bunky v priesečníku konkrétneho riadka a stĺpca.

Poďme teda k našej tabuľke a napíšme vzorec s funkciou VPR, v ktorom nájdete informácie o cene citrónov predaných v marci.

Existuje niekoľko spôsobov, ako vykonať vyhľadávanie XNUMXD. Pozrite si možnosti a vyberte si tú, ktorá vám najviac vyhovuje.

Funkcie VLOOKUP a MATCH

Môžete použiť množstvo funkcií VPR (VLOOKUP) a VIAC EXPOZOVANÉ (MATCH), aby ste našli hodnotu na priesečníku polí Názov produktu (reťazec) a mesiac (stĺpec) predmetného poľa:

=VLOOKUP("Lemons",$A$2:$I$9,MATCH("Mar",$A$1:$I$1,0),FALSE)

=ВПР("Lemons";$A$2:$I$9;ПОИСКПОЗ("Mar";$A$1:$I$1;0);ЛОЖЬ)

Vyššie uvedený vzorec je bežnou funkciou VPR, ktorý hľadá presnú zhodu s hodnotou „Citróny“ v bunkách A2 až A9. Ale keďže neviete, v ktorom stĺpci sú marcové predaje, nebudete môcť nastaviť číslo stĺpca pre tretí argument funkcie. VPR. Namiesto toho sa používa funkcia VIAC EXPOZOVANÉna definovanie tohto stĺpca.

MATCH("Mar",$A$1:$I$1,0)

ПОИСКПОЗ("Mar";$A$1:$I$1;0)

V preklade do ľudského jazyka tento vzorec znamená:

• Hľadáme postavy „Mar“ – argument lookup_value (vyhľadávacia_hodnota);
• Pohľad do buniek od A1 do I1 – argument lookup_array (lookup_array);
• Vrátenie presnej zhody – argument typ_zhody (typ_zhody).

Použitím 0 v treťom argumente hovoríte funkcie VIAC EXPOZOVANÉ vyhľadajte prvú hodnotu, ktorá sa presne zhoduje s hodnotou, ktorú hľadáte. Toto je ekvivalentné hodnote FALSE (FALSE) pre štvrtý argument VPR.

Takto môžete vytvoriť vzorec obojsmerného vyhľadávania v programe Excel, známy aj ako dvojrozmerné vyhľadávanie alebo obojsmerné vyhľadávanie.

Funkcia SUMPRODUCT

Funkcie SUMPRODUCT (SUMPRODUCT) vráti súčet súčinov vybratých polí:

=SUMPRODUCT(($A$2:$A$9="Lemons")*($A$1:$I$1="Mar"),$A$2:$I$9)

=СУММПРОИЗВ(($A$2:$A$9="Lemons")*($A$1:$I$1="Mar");$A$2:$I$9)

Funkcie INDEX a MATCH

V nasledujúcom článku tieto funkcie podrobne vysvetlím, takže zatiaľ môžete iba skopírovať tento vzorec:

=INDEX($A$2:$I$9,MATCH("Lemons",$A$2:$A$9,0),MATCH("Mar",$A$1:$I$1,0))

=ИНДЕКС($A$2:$I$9;ПОИСКПОЗ("Lemons";$A$2:$A$9;0);ПОИСКПОЗ("Mar";$A$1:$I$1;0))

Pomenované rozsahy a operátor križovatky

Ak nemáte radi všetky tie zložité vzorce Excelu, možno sa vám bude páčiť tento vizuálny a zapamätateľný spôsob:

1. Vyberte tabuľku, otvorte kartu vzorca (Vzorce) a kliknite Vytvoriť z výberu (Vytvoriť z výberu).
2. Začiarknite políčka Vrchný rad (na riadku vyššie) a Ľavý stĺpec (v stĺpci vľavo). Microsoft Excel priradí názvy rozsahom z hodnôt v hornom riadku a ľavom stĺpci tabuľky. Teraz môžete vyhľadávať pomocou týchto názvov priamo bez vytvárania vzorcov.
3. Do ľubovoľnej prázdnej bunky napíšte =name_name column_name, napríklad takto:

   = Citróny Mar

   … alebo naopak:

   = Mar Lemons

   Nezabudnite, že názvy riadkov a stĺpcov musia byť oddelené medzerou, ktorá v tomto prípade funguje ako operátor križovatky.

Keď zadáte názov, Microsoft Excel zobrazí popis so zoznamom zodpovedajúcich názvov, rovnako ako pri zadávaní vzorca.

4. lis vstúpiť a skontrolujte výsledok

Vo všeobecnosti platí, že bez ohľadu na to, ktorú z vyššie uvedených metód zvolíte, výsledok dvojrozmerného vyhľadávania bude rovnaký:

Použitie viacerých VLOOKUPs v jednom vzorci

Stáva sa, že hlavná tabuľka a vyhľadávacia tabuľka nemajú spoločný ani jeden stĺpec, čo vám bráni použiť bežnú funkciu VPR. Existuje však ďalšia tabuľka, ktorá neobsahuje informácie, ktoré nás zaujímajú, ale má spoločný stĺpec s hlavnou tabuľkou a vyhľadávacou tabuľkou.

Pozrime sa na nasledujúci príklad. Máme hlavnú tabuľku so stĺpcom SKU (nové), kde chcete pridať stĺpec s príslušnými cenami z inej tabuľky. Okrem toho máme 2 vyhľadávacie tabuľky. Prvá (vyhľadávacia tabuľka 1) obsahuje aktualizované čísla SKU (nové) a názvy produktov a druhá (vyhľadávacia tabuľka 2) – názvy produktov a staré čísla SKU (staré).

Ak chcete pridať ceny z druhej vyhľadávacej tabuľky do hlavnej tabuľky, musíte vykonať akciu známu ako double VPR alebo vnorené VPR.

1. Napíšte funkciu VPR, ktorý v tabuľke nájde názov produktu Vyhľadávacia tabuľka 1a využitím SKU, ako požadovaná hodnota:

   =VLOOKUP(A2,New_SKU,2,FALSE)

   =ВПР(A2;New_SKU;2;ЛОЖЬ)

   Tu New_SKU – pomenovaný rozsah $A:$B v tabulke Vyhľadávacia tabuľka 1, 2 – ide o stĺpec B, ktorý obsahuje názvy tovaru (viď obrázok vyššie)

2. Napíšte vzorec na vloženie cien z tabuľky Vyhľadávacia tabuľka 2 na základe známych názvov produktov. Ak to chcete urobiť, prilepte vzorec, ktorý ste vytvorili predtým, ako vyhľadávaciu hodnotu pre novú funkciu VPR:

   =VLOOKUP(VLOOKUP(A2,New_SKU,2,FALSE),Price,3,FALSE)

   =ВПР(ВПР(A2;New_SKU;2;ЛОЖЬ);Price;3;ЛОЖЬ)

   Tu Cena – pomenovaný rozsah $A:$C v tabulke Vyhľadávacia tabuľka 2, 3 je stĺpec C obsahujúci ceny.

Obrázok nižšie zobrazuje výsledok vrátený vzorcom, ktorý sme vytvorili:

Dynamická substitúcia údajov z rôznych tabuliek pomocou funkcie VLOOKUP a INDIRECT

Najprv si ujasnime, čo rozumieme pod výrazom „Dynamická substitúcia údajov z rôznych tabuliek“, aby sme sa uistili, že si správne rozumieme.

Existujú situácie, keď existuje niekoľko hárkov s údajmi rovnakého formátu a z určitého hárku je potrebné extrahovať potrebné informácie v závislosti od hodnoty, ktorá je zadaná v danej bunke. Myslím, že je to jednoduchšie vysvetliť na príklade.

Predstavte si, že máte prehľady predaja pre niekoľko regiónov s rovnakými produktmi a v rovnakom formáte. Chcete nájsť údaje o predaji pre konkrétny región:

Ak máte len dva takéto prehľady, potom môžete použiť hanebne jednoduchý vzorec s funkciami VPR и IF (IF) na výber požadovaného prehľadu na vyhľadávanie:

=VLOOKUP($D$2,IF($D3="FL",FL_Sales,CA_Sales),2,FALSE)

=ВПР($D$2;ЕСЛИ($D3="FL";FL_Sales;CA_Sales);2;ЛОЖЬ)

kde:

• $D$2 je bunka obsahujúca názov produktu. Všimnite si, že tu používame absolútne odkazy, aby sme sa vyhli zmene vyhľadávacej hodnoty pri kopírovaní vzorca do iných buniek.
• 3 $ je bunka s názvom regiónu. Používame absolútny odkaz na stĺpec a relatívny odkaz na riadok, pretože plánujeme skopírovať vzorec do iných buniek v tom istom stĺpci.
• FL_Sales и CA_Sales – názvy tabuliek (alebo pomenovaných rozsahov), ktoré obsahujú zodpovedajúce výkazy predaja. Môžete samozrejme použiť napríklad obvyklé názvy hárkov a odkazy na rozsah buniek 'FL Sheet'!$A$3:$B$10, ale pomenované rozsahy sú oveľa pohodlnejšie.

Keď je však takýchto tabuliek veľa, funkcia IF nie je najlepším riešením. Namiesto toho môžete použiť funkciu NEPRIAME (NEPRIAME), ak chcete vrátiť požadovaný rozsah vyhľadávania.

Ako asi viete, funkcia NEPRIAME sa používa na vrátenie odkazu daného textovým reťazcom, čo je presne to, čo teraz potrebujeme. Takže smelo nahraďte vo vyššie uvedenom vzorci výraz funkciou IF prepojiť s funkciou NEPRIAME. Tu je kombinácia VPR и NEPRIAME funguje skvele s:

=VLOOKUP($D$2,INDIRECT($D3&"_Sales"),2,FALSE)

=ВПР($D$2;ДВССЫЛ($D3&"_Sales");2;ЛОЖЬ)

kde:

• $D$2 – ide o bunku s názvom produktu, kvôli absolútnemu odkazu sa nemení.
• 3 $ je bunka obsahujúca prvú časť názvu oblasti. V našom príklade toto FL.
• _Predaj – spoločná časť názvu všetkých pomenovaných rozsahov alebo tabuliek. V kombinácii s hodnotou v bunke D3 tvorí úplný názov požadovaného rozsahu. Nižšie sú uvedené niektoré podrobnosti pre tých, ktorí sú s touto funkciou noví NEPRIAME.

Ako fungujú NEPRIAME a VLOOKUP

Najprv mi dovoľte pripomenúť syntax funkcie NEPRIAME (NEPRIAME):

INDIRECT(ref_text,[a1])

ДВССЫЛ(ссылка_на_текст;[a1])

Prvým argumentom môže byť odkaz na bunku (štýl A1 alebo R1C1), názov rozsahu alebo textový reťazec. Druhý argument určuje, aký štýl odkazu je obsiahnutý v prvom argumente:

• A1ak je argument SKUTOČNÝ KÓD (TRUE) alebo neuvedené;
• R1C1, Ak FAS E (NEPRAVDA).

V našom prípade má odkaz štýl A1, takže môžete vynechať druhý argument a zamerať sa na prvý.

Vráťme sa teda k našim správam o predaji. Ak si pamätáte, každá správa je samostatná tabuľka umiestnená na samostatnom hárku. Aby vzorec fungoval správne, musíte pomenovať svoje tabuľky (alebo rozsahy) a všetky názvy musia mať spoločnú časť. Napríklad takto: CA_Sales, FL_Predaj, TX_Sales a tak ďalej. Ako vidíte, „_Sales“ je prítomný vo všetkých názvoch.

Funkcie NEPRIAME spája hodnotu v stĺpci D a textový reťazec „_Sales“, čím hovorí VPR v ktorej tabuľke hľadať. Ak bunka D3 obsahuje hodnotu „FL“, vzorec vyhľadá tabuľku FL_Predaj, ak „CA“ – v tabuľke CA_Sales a tak ďalej.

Výsledok funkcií VPR и NEPRIAME bude nasledovné:

Ak sa údaje nachádzajú v rôznych knihách programu Excel, musíte pred pomenovaný rozsah pridať názov knihy, napríklad:

=VLOOKUP($D$2,INDIRECT($D3&"Workbook1!_Sales"),2,FALSE)

=ВПР($D$2;ДВССЫЛ($D3&"Workbook1!_Sales");2;ЛОЖЬ)

Ak je funkcia NEPRIAME odkazuje na iný zošit, tento zošit musí byť otvorený. Ak je zatvorený, funkcia ohlási chybu. #REF! (#SSYL!).