Napredni primeri VLOOKUP: iskanje po več kriterijih

V drugem delu naše vadnice o funkciji VPR (VLOOKUP) v Excelu bomo analizirali nekaj primerov, ki vam bodo pomagali usmeriti vso moč VPR za reševanje najambicioznejših Excelovih nalog. Primeri predvidevajo, da že imate osnovno znanje o delovanju te funkcije. Če ne, bi vas morda zanimalo, da začnete s prvim delom te vadnice, ki pojasnjuje sintakso in osnovno uporabo. VPR. No, pa začnimo.

• Iščite v Excelu po več kriterijih
• Z VLOOKUP izvlečemo 2., 3. itd
• Pridobite vse ponovitve želene vrednosti
• XNUMXD iskanje po znani vrstici in stolpcu
• Uporaba več VLOOKUP-ov v eni formuli
• Dinamična zamenjava podatkov iz različnih tabel

Iščite v Excelu po več kriterijih

funkcija VPR v Excelu je resnično zmogljivo orodje za izvajanje iskanja določene vrednosti v bazi podatkov. Vendar pa obstaja pomembna omejitev – njegova sintaksa omogoča iskanje samo ene vrednosti. Kaj pa, če želite iskati po več pogojih? Spodaj boste našli rešitev.

Primer 1: Iskanje po 2 različnih kriterijih

Recimo, da imamo seznam naročil in želimo najti Količina blaga (kol.), na podlagi dveh kriterijev – Ime stranke (Stranka) и Название продукта (Izdelek). Zadeva je zapletena zaradi dejstva, da je vsak od kupcev naročil več vrst blaga, kar je razvidno iz spodnje tabele:

redna funkcija VPR v tem scenariju ne bo delovala, ker bo vrnila prvo najdeno vrednost, ki se ujema z dano iskalno vrednostjo. Na primer, če želite izvedeti količino artikla sladkarijepo naročilu kupca Jeremy Hill, napišite naslednjo formulo:

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

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

– ta formula bo vrnila rezultat 15ustreza izdelku Jabolka, ker je to prva vrednost, ki se ujema.

Obstaja preprosta rešitev – ustvarite dodaten stolpec, v katerem združite vse želene kriterije. V našem primeru so to stolpci Ime stranke (Stranka) и Название продукта (Izdelek). Ne pozabite, da mora biti združeni stolpec vedno skrajno levi stolpec v iskalnem obsegu, saj je levi stolpec, v katerem funkcija VPR pogleda navzgor, ko išče vrednost.

Torej dodate pomožni stolpec v tabelo in kopirate naslednjo formulo čez vse celice: =B2&C2. Če želite, da je niz bolj berljiv, lahko kombinirane vrednosti ločite s presledkom: =B2&» «&C2. Po tem lahko uporabite naslednjo formulo:

=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;ЛОЖЬ)

Kje je celica B1 vsebuje združeno vrednost argumenta lookup_value (iskana_vrednost) in 4 - Prepir col_index_num (številka_stolpca), tj. številka stolpca, ki vsebuje podatke, ki jih je treba pridobiti.

Primer 2: VLOOKUP po dveh kriterijih s pregledovanjem tabele na drugem listu

Če morate posodobiti glavno tabelo (Main table) z dodajanjem podatkov iz druge tabele (Lookup table), ki se nahaja na drugem listu ali v drugem Excelovem delovnem zvezku, potem lahko zberete želeno vrednost neposredno v formuli, ki jo vstavite v glavno tabelo.

Tako kot v prejšnjem primeru boste potrebovali pomožni stolpec v iskalni tabeli s kombiniranimi vrednostmi. Ta stolpec mora biti skrajno levi stolpec v iskalnem obsegu.

Torej formula z VPR lahko bi bilo takole:

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

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

Tukaj stolpca B in C vsebujeta imena strank oziroma izdelkov ter povezavo Naročila!$A&$2:$D$2 definira tabelo za iskanje na drugem listu.

Da bo formula bolj berljiva, lahko obseg pogleda poimenujete in potem bo formula videti veliko preprostejša:

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

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

Da formula deluje, morajo biti vrednosti v skrajnem levem stolpcu tabele, ki jo gledate, združene na popolnoma enak način kot v iskalnih kriterijih. Na zgornji sliki smo združili vrednosti u2bu2band in med njimi postavili presledek, na enak način, kot morate storiti v prvem argumentu funkcije (BXNUMX& "" & CXNUMX).

Ne pozabite! funkcija VPR omejen na 255 znakov, ne more iskati vrednosti, ki je daljša od 255 znakov. Upoštevajte to in pazite, da dolžina želene vrednosti ne preseže te omejitve.

Strinjam se, da dodajanje pomožnega stolpca ni najbolj elegantna in ne vedno sprejemljiva rešitev. Enako lahko storite brez pomožnega stolpca, vendar bi to zahtevalo veliko bolj zapleteno formulo s kombinacijo funkcij INDEX (KAZALO) in MATCH (BOLJ IZPOSTAVLJENO).

Z VLOOKUP izvlečemo 2., 3. itd

To že veste VPR lahko vrne samo eno ujemajočo se vrednost, natančneje prvo najdeno. Kaj pa, če se ta vrednost večkrat ponovi v prikazanem nizu in želite izvleči drugo ali tretjo od njih? Kaj če vse vrednote? Problem se zdi zapleten, a rešitev obstaja!

Recimo, da en stolpec tabele vsebuje imena strank (Customer Name), drugi stolpec pa vsebuje izdelke (Product), ki so jih kupili. Poskusimo poiskati 2., 3. in 4. artikle, ki jih je kupila določena stranka.

Najlažji način je, da dodate pomožni stolpec pred stolpec Ime stranke in ga napolnite z imeni strank s številko ponovitve vsakega imena, na primer, Janez Doe1, Janez Doe2 itd. Trik s številčenjem bomo izvedli s funkcijo COUNTIF (COUNTIF), glede na to, da so imena strank v stolpcu B:

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

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

Po tem lahko uporabljate običajno funkcijo VPRnajti zahtevano naročilo. Na primer:

• Najdi 2-ti izdelek, ki ga je naročila stranka Dan Brown:

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

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

• Najdi 3-ti izdelek, ki ga je naročila stranka Dan Brown:

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

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

Pravzaprav lahko kot iskalno vrednost namesto besedila vnesete sklic na celico, kot je prikazano na naslednji sliki:

Če iščete samo 2-e ponovitev, lahko to storite brez pomožnega stolpca, tako da ustvarite bolj zapleteno formulo:

=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 tej formuli:

• $ F $ 2 – celica, ki vsebuje ime kupca (je nespremenjeno, upoštevajte – povezava je absolutna);
• $ B $ – stolpec Ime stranke;
• Tabela4 – Vaša miza (to mesto je lahko tudi običajna ponudba);
• C16 $ – končna celica tabele ali obsega.

Ta formula najde samo drugo ujemajočo se vrednost. Če morate izvleči preostale ponovitve, uporabite prejšnjo rešitev.

Če potrebujete seznam vseh ujemanj – funkcija VPR to ni pomočnik, saj vrne le eno vrednost naenkrat – piko. Toda Excel ima funkcijo INDEX (INDEX), ki se zlahka spopade s to nalogo. Kako bo takšna formula izgledala, boste izvedeli v naslednjem primeru.

Pridobite vse ponovitve želene vrednosti

Kot smo že omenili VPR ne more izvleči vseh podvojenih vrednosti iz skeniranega obsega. Za to potrebujete nekoliko bolj zapleteno formulo, sestavljeno iz več Excelovih funkcij, kot npr INDEX (KAZALO), SMALL (MAJHNO) in ROW (VRSTICA)

Na primer, spodnja formula najde vse ponovitve vrednosti iz celice F2 v obsegu B2:B16 in vrne rezultat iz istih vrstic v stolpcu 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));"")}

Vnesite to matrično formulo v več sosednjih celic, kot so celice F4: F8kot je prikazano na spodnji sliki. Število celic mora biti enako ali večje od največjega možnega števila ponovitev iskane vrednosti. Ne pozabite klikniti Ctrl + Shift + Enterda pravilno vnesete matrično formulo.

Če vas zanima, kako deluje, se nekoliko poglobimo v podrobnosti formule:

Del 1:

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

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

$F$2=B2:B16 – primerjajte vrednost v celici F2 z vsako od vrednosti v obsegu B2:B16. Če se najde ujemanje, izraz STRING(C2:C16)-1 vrne številko ustrezne vrstice (vrednost -1 omogoča, da ne vključite naslovne vrstice). Če ni zadetkov, funkcija IF (IF) vrne prazen niz.

Rezultat funkcije IF (ČE) obstaja tak vodoravni niz: {1,"",3,"",5,"","","","","","",12,"","",""}

Del 2:

ROW()-3

СТРОКА()-3

Tukaj je funkcija ROW (LINE) deluje kot dodatni števec. Ker se formula prekopira v celice F4:F9, odštejemo število 3 iz rezultata funkcije za pridobitev vrednosti 1 v celici F4 (vrstica 4, odštej 3), da dobiš 2 v celici F5 (vrstica 5, odštej 3) in tako naprej.

Del 3:

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

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

funkcija SMALL (MAJHNO) vrne n-oh najmanjša vrednost v podatkovnem nizu. V našem primeru, kateri položaj (od najmanjšega) vrniti, določa funkcija ROW (LINE) (glej 2. del). Torej za celico F4 funkcija MALO ({polje},1) vrne 1-ti (najmanjši) element polja, tj 1. Za celico F5 vrne 2-ti najmanjši element v nizu, tj 3, Itd

Del 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))

funkcija INDEX (INDEX) preprosto vrne vrednost določene celice v matriki C2:C16. Za celico F4 funkcija INDEKS($C$2:$C$16) se bo vrnil Jabolkaza F5 funkcija INDEKS($C$2:$C$16) se bo vrnil sladkarije in tako naprej.

Del 5:

IFERROR()

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

Končno formulo postavimo v funkcijo NAPAKA (IFERROR), ker verjetno ne boste zadovoljni s sporočilom o napaki #AT (#N/A), če je število celic, v katere je kopirana formula, manjše od števila podvojenih vrednosti v obsegu, ki si ga ogledujete.

XNUMXD iskanje po znani vrstici in stolpcu

Izvajanje XNUMXD iskanja v Excelu vključuje iskanje vrednosti po znani številki vrstice in stolpca. Z drugimi besedami, izvlečete vrednost celice na presečišču določene vrstice in stolpca.

Torej, poglejmo našo tabelo in napišimo formulo s funkcijo VPR, ki bo našel podatke o stroških prodanih limon v marcu.

Obstaja več načinov za izvedbo XNUMXD iskanja. Preverite možnosti in izberite tisto, ki vam najbolj ustreza.

Funkciji VLOOKUP in MATCH

Uporabite lahko kup funkcij VPR (VLOOKUP) in BOLJ IZPOSTAVLJENO (MATCH), da poiščete vrednost na presečišču polj Название продукта (niz) in mesec (stolpec) zadevne matrike:

=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);ЛОЖЬ)

Zgornja formula je običajna funkcija VPR, ki išče natančno ujemanje vrednosti »Limone« v celicah od A2 do A9. Ker pa ne veste, v katerem stolpcu so marčevske razprodaje, ne boste mogli nastaviti številke stolpca za tretji argument funkcije. VPR. Namesto tega se uporablja funkcija BOLJ IZPOSTAVLJENOda definirate ta stolpec.

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

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

Prevedeno v človeški jezik ta formula pomeni:

• Iščemo znake "Mar" - argument lookup_value (iskana_vrednost);
• Iskanje v celicah od A1 do I1 – argument iskalni_array (matrika_iskanje);
• Vrne natančno ujemanje – argument match_type (vrsta_ujemanja).

Uporaba 0 v tretjem argumentu rečete funkcije BOLJ IZPOSTAVLJENO poiščite prvo vrednost, ki se natančno ujema z vrednostjo, ki jo iščete. To je enakovredno vrednosti FALSE (FALSE) za četrti argument VPR.

Tako lahko ustvarite dvosmerno iskalno formulo v Excelu, znano tudi kot dvodimenzionalno iskanje ali dvosmerno iskanje.

Funkcija SUMPRODUCT

funkcija SUMPRODUCT (SUMPRODUCT) vrne vsoto zmnožkov izbranih nizov:

=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)

Funkcije INDEX in MATCH

V naslednjem članku bom podrobno razložil te funkcije, tako da lahko za zdaj samo kopirate to formulo:

=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))

Imenovani obsegi in operator presečišča

Če niste navdušeni nad vsemi temi zapletenimi Excelovimi formulami, vam bo morda všeč ta vizualni in nepozaben način:

1. Izberite tabelo, odprite zavihek Formule (Formule) in kliknite Ustvari iz izbora (Ustvari iz izbora).
2. Označite polja Zgornja vrstica (v zgornji vrstici) in Levi stolpec (v stolpcu na levi). Microsoft Excel bo obsegom dodelil imena iz vrednosti v zgornji vrstici in levem stolpcu vaše preglednice. Zdaj lahko iščete s temi imeni neposredno brez ustvarjanja formul.
3. V poljubno prazno celico vpišite =ime_vrstice ime_stolpca, na primer takole:

   =Limone mar

   … ali obratno:

   =Mar Limone

   Ne pozabite, da morajo biti imena vrstic in stolpcev ločena s presledkom, kar v tem primeru deluje kot operator presečišča.

Ko vnesete ime, bo Microsoft Excel prikazal opis orodja s seznamom ujemajočih se imen, tako kot pri vnosu formule.

4. Pritisnite Vnesite in preverite rezultat

Na splošno bo rezultat dvodimenzionalnega iskanja enak, ne glede na to, katero od zgornjih metod izberete:

Uporaba več VLOOKUP-ov v eni formuli

Zgodi se, da glavna tabela in iskalna tabela nimata niti enega skupnega stolpca, kar vam onemogoča uporabo običajne funkcije VPR. Obstaja pa še ena tabela, ki ne vsebuje informacij, ki nas zanimajo, ima pa skupni stolpec z glavno tabelo in iskalno tabelo.

Oglejmo si naslednji primer. Imamo glavno tabelo s stolpcem SKU (novo), kamor želite dodati stolpec s pripadajočimi cenami iz druge tabele. Poleg tega imamo 2 iskalni tabeli. Prva (Iskalna tabela 1) vsebuje posodobljene številke SKU (novo) in imena izdelkov, drugo (Iskalna tabela 2) pa imena izdelkov in stare številke SKU (staro).

Če želite dodati cene iz druge iskalne tabele v glavno tabelo, morate izvesti dejanje, imenovano dvojno VPR ali ugnezdeni VPR.

1. Napišite funkcijo VPR, ki v tabeli najde ime izdelka Iskalna tabela 1uporabo SKU, kot želeno vrednost:

   =VLOOKUP(A2,New_SKU,2,FALSE)

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

   Tukaj New_SKU – imenovan obseg $A:$B v tabeli Iskalna tabela 1, 2 – to je stolpec B, v katerem so imena blaga (glej sliko zgoraj)

2. Napišite formulo za vstavljanje cen iz tabele Iskalna tabela 2 na podlagi znanih imen izdelkov. Če želite to narediti, prilepite formulo, ki ste jo ustvarili prej, kot iskalno vrednost za novo funkcijo VPR:

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

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

   Tukaj Cena – imenovan obseg $ A: $ C v tabeli Iskalna tabela 2, 3 je stolpec C, ki vsebuje cene.

Spodnja slika prikazuje rezultat, ki ga vrne formula, ki smo jo ustvarili:

Dinamična zamenjava podatkov iz različnih tabel z uporabo VLOOKUP in INDIRECT

Najprej razjasnimo, kaj mislimo z izrazom "Dinamična zamenjava podatkov iz različnih tabel", da se prepričamo, da se pravilno razumemo.

Obstajajo situacije, ko obstaja več listov s podatki istega formata in je treba iz določenega lista izvleči potrebne informacije, odvisno od vrednosti, ki je vnesena v določeno celico. Mislim, da je to lažje razložiti s primerom.

Predstavljajte si, da imate prodajna poročila za več regij z istimi izdelki in v isti obliki. Želite najti podatke o prodaji za določeno regijo:

Če imate samo dve takšni poročili, potem lahko uporabite sramotno preprosto formulo s funkcijami VPR и IF (IF), da izberete želeno poročilo za iskanje:

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

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

Kje:

• $D$2 je celica, ki vsebuje ime izdelka. Upoštevajte, da tukaj uporabljamo absolutne reference, da se izognemo spreminjanju iskalne vrednosti pri kopiranju formule v druge celice.
• $ D3 je celica z imenom regije. Uporabljamo absolutni sklic na stolpec in relativni sklic na vrstico, ker nameravamo kopirati formulo v druge celice v istem stolpcu.
• FL_Sales и CA_Sales – imena tabel (ali imenovanih obsegov), ki vsebujejo ustrezna prodajna poročila. Seveda lahko na primer uporabite običajna imena listov in sklice na obseg celic 'FL list'!$A$3:$B$10, vendar so imenovani obsegi veliko bolj priročni.

Ko pa je takih tabel veliko, funkcija IF ni najboljša rešitev. Namesto tega lahko uporabite funkcijo INDIRECT (POSREDNO), da vrnete želeno območje iskanja.

Kot verjetno veste, funkcija INDIRECT se uporablja za vrnitev povezave, podane z besedilnim nizom, kar je točno to, kar zdaj potrebujemo. Torej, pogumno zamenjajte izraz v zgornji formuli s funkcijo IF povezati s funkcijo INDIRECT. Tukaj je kombinacija VPR и INDIRECT odlično deluje z:

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

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

Kje:

• $D$2 – to je celica z imenom izdelka, ki je nespremenjena zaradi absolutne povezave.
• $ D3 je celica, ki vsebuje prvi del imena regije. V našem primeru to FL.
• _Prodaja – skupni del imena vseh imenovanih obsegov ali tabel. V kombinaciji z vrednostjo v celici D3 tvori popolnoma kvalificirano ime zahtevanega obsega. Spodaj je nekaj podrobnosti za tiste, ki so novi v funkciji INDIRECT.

Kako delujeta INDIRECT in VLOOKUP

Najprej naj vas spomnim na sintakso funkcije INDIRECT (POSREDNO):

INDIRECT(ref_text,[a1])

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

Prvi argument je lahko sklic na celico (slog A1 ali R1C1), ime obsega ali besedilni niz. Drugi argument določa, kakšen slog povezave vsebuje prvi argument:

• A1če je argument PRAVA KODA (TRUE) ali ni določeno;
• R1C1Če FKOT E (LAŽNO).

V našem primeru ima povezava slog A1, tako da lahko izpustite drugi argument in se osredotočite na prvega.

Pa se vrnimo k našim prodajnim poročilom. Če se spomnite, je vsako poročilo ločena tabela, ki se nahaja na ločenem listu. Za pravilno delovanje formule morate poimenovati tabele (ali obsege) in vsa imena morajo imeti skupni del. Na primer takole: CA_Sales, FL_Sales, TX_Sales in tako naprej. Kot lahko vidite, je »_Sales« prisoten v vseh imenih.

funkcija INDIRECT poveže vrednost v stolpcu D in besedilni niz “_Sales”, s čimer pove VPR v kateri tabeli iskati. Če celica D3 vsebuje vrednost »FL«, bo formula preiskala tabelo FL_Sales, če je »CA« – v tabeli CA_Sales in tako naprej.

Rezultat funkcij VPR и INDIRECT bo naslednje:

Če se podatki nahajajo v različnih Excelovih knjigah, morate pred imenovani obseg dodati ime knjige, na primer:

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

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

Če funkcija INDIRECT nanaša na drug delovni zvezek, mora biti ta odprt. Če je zaprta, bo funkcija javila napako. #REF! (#SSYL!).