Sisu
Meie funktsiooni õpetuse teises osas VPR (VLOOKUP) Excelis analüüsime mõnda näidet, mis aitavad teil kogu jõudu suunata VPR kõige ambitsioonikamate Exceli ülesannete lahendamiseks. Näidetes eeldatakse, et teil on juba põhiteadmised selle funktsiooni toimimise kohta. Kui ei, võite olla huvitatud selle õpetuse esimese osaga, mis selgitab süntaksit ja põhikasutust. VPR. Noh, alustame.
Otsige Excelis mitme kriteeriumi järgi
funktsioon VPR Excelis on tõeliselt võimas tööriist konkreetse väärtuse otsimiseks andmebaasist. Siiski on oluline piirang – selle süntaks võimaldab otsida ainult ühte väärtust. Mis siis, kui soovite otsida mitme tingimuse järgi? Lahenduse leiate altpoolt.
Näide 1: Otsige 2 erineva kriteeriumi järgi
Oletame, et meil on tellimuste loend ja me tahame leida Kauba kogus (kogus), kahe kriteeriumi alusel – Kliendi nimi (Klient) и Название продукта (Toode). Asja teeb keeruliseks asjaolu, et iga ostja tellis mitut tüüpi kaupu, nagu on näha allolevast tabelist:
regulaarne funktsioon VPR ei tööta selle stsenaariumi korral, sest see tagastab esimese leitud väärtuse, mis vastab antud otsinguväärtusele. Näiteks kui soovite teada kauba kogust maiustusedostja tellitud Jeremy Hill, kirjutage järgmine valem:
=VLOOKUP(B1,$A$5:$C$14,3,FALSE)
=ВПР(B1;$A$5:$C$14;3;ЛОЖЬ)
– see valem tagastab tulemuse 15tootele vastav õunad, sest see on esimene väärtus, mis vastab.
On lihtne lahendus – looge täiendav veerg, kus kõik soovitud kriteeriumid ühendada. Meie näites on need veerud Kliendi nimi (Klient) и Название продукта (Toode). Ärge unustage, et ühendatud veerg peab alati olema otsinguvahemiku vasakpoolseim veerg, kuna funktsioon on vasakpoolsem veerg. VPR vaatab väärtust otsides üles.
Seega lisate tabelisse abiveeru ja kopeerite järgmise valemi kõigisse selle lahtritesse: =B2&C2. Kui soovite, et string oleks paremini loetav, saate kombineeritud väärtused eraldada tühikuga: =B2&» «&C2. Pärast seda saate kasutada järgmist valemit:
=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;ЛОЖЬ)
Kus on rakk B1 sisaldab argumendi ühendatud väärtust lookup_value (otsingu_väärtus) ja 4 – Argument col_index_num (veeru_number) ehk otsitavaid andmeid sisaldava veeru number.
Näide 2: VLOOKUP kahe kriteeriumi alusel, kui tabelit vaadatakse teisel lehel
Kui peate põhitabelit (Põhitabel) värskendama, lisades andmeid teisest tabelist (otsingutabel), mis asub teisel lehel või mõnes muus Exceli töövihikus, saate soovitud väärtuse koguda otse sisestatavasse valemisse. põhitabelisse.
Nagu eelmises näites, vajate kombineeritud väärtustega otsingutabelis lisaveergu. See veerg peab olema otsinguvahemiku vasakpoolseim veerg.
Nii et valem koos VPR võiks olla selline:
=VLOOKUP(B2&" "&C2,Orders!$A&$2:$D$2,4,FALSE)
=ВПР(B2&" "&C2;Orders!$A&$2:$D$2;4;ЛОЖЬ)
Siin sisaldavad veerud B ja C vastavalt klientide ja tootenimesid ning linki Tellimused!$A&$2:$D$2 määrab tabeli, mida teiselt lehelt otsida.
Valemi loetavamaks muutmiseks võite anda vaatevahemikule nime ja siis näeb valem palju lihtsam välja:
=VLOOKUP(B2&" "&C2,Orders,4,FALSE)
=ВПР(B2&" "&C2;Orders;4;ЛОЖЬ)
Valemi toimimiseks tuleb vaadeldava tabeli vasakpoolseimas veerus olevad väärtused kombineerida täpselt samamoodi nagu otsingukriteeriumides. Ülaltoodud joonisel ühendasime väärtused u2bu2band ja panime nende vahele tühiku, samamoodi nagu peate tegema funktsiooni esimeses argumendis (BXNUMX& “” & CXNUMX).
Pidage meeles! funktsioon VPR piiratud 255 tähemärgiga, ei saa see otsida väärtust, mis on pikem kui 255 tähemärki. Pidage seda meeles ja veenduge, et soovitud väärtuse pikkus ei ületaks seda piiri.
Olen nõus, et abisamba lisamine ei ole kõige elegantsem ja mitte alati vastuvõetav lahendus. Sama saab teha ka ilma abiveeruta, kuid selleks oleks vaja palju keerukamat valemit koos funktsioonide kombinatsiooniga INDEX (INDEKS) ja MATCH (ROHKEM AVALDATUD).
2., 3. jne väärtused ekstraheerime VLOOKUP abil
Sa juba tead seda VPR saab tagastada ainult ühe sobiva väärtuse, täpsemalt esimese leitud väärtuse. Aga mis siis, kui seda väärtust korratakse vaadatud massiivis mitu korda ja soovite neist 2. või 3. välja võtta? Mis siis, kui kõik väärtused? Probleem tundub keeruline, kuid lahendus on olemas!
Oletame, et tabeli üks veerg sisaldab klientide nimesid (kliendi nimi) ja teine veerg sisaldab tooteid (toode), mille nad ostsid. Proovime leida antud kliendi poolt ostetud 2., 3. ja 4. kaupa.
Lihtsaim viis on lisada veergu ette abiveerg Kliendi nimi ja täitke see klientide nimedega koos iga nime kordusnumbriga, näiteks John Doe1, John Doe2 jne. Nummerdamisega tegeleme funktsiooni abil COUNTIF (COUNTIF), arvestades, et klientide nimed on veerus B:
=B2&COUNTIF($B$2:B2,B2)
=B2&СЧЁТЕСЛИ($B$2:B2;B2)
Pärast seda saate kasutada tavalist funktsiooni VPRvajaliku tellimuse leidmiseks. Näiteks:
- leidma 2-ndal kliendi poolt tellitud kaup Dan Brown:
=VLOOKUP("Dan Brown2",$A$2:$C$16,3,FALSE)=ВПР("Dan Brown2";$A$2:$C$16;3;ЛОЖЬ) - leidma 3-ndal kliendi poolt tellitud kaup Dan Brown:
=VLOOKUP("Dan Brown3",$A$2:$C$16,3,FALSE)=ВПР("Dan Brown3";$A$2:$C$16;3;ЛОЖЬ)
Tegelikult saate teksti asemel otsinguväärtuseks sisestada lahtri viite, nagu on näidatud järgmisel joonisel:
Kui otsite ainult 2-e kordamine, saate seda teha ilma abiveeruta, luues keerukama valemi:
=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;ИСТИНА);"")
Selles valemis:
- 2 dollarit F$ – lahter, mis sisaldab ostja nime (see on muutumatu, pane tähele – link on absoluutne);
- $ B $ - veerg Kliendi nimi;
- Table4 – Teie laud (see koht võib olla ka tavaline valik);
- C16 dollarit – teie tabeli või vahemiku lõpulahter.
See valem leiab ainult teise sobiva väärtuse. Kui teil on vaja ülejäänud kordused välja tõmmata, kasutage eelmist lahendust.
Kui vajate kõigi vastete loendit – funktsioon VPR see ei ole abimees, kuna tagastab korraga ainult ühe väärtuse – perioodi. Kuid Excelil on funktsioon INDEX (INDEX), mis saab selle ülesandega hõlpsalt hakkama. Kuidas selline valem välja näeb, saate teada järgmisest näitest.
Tooge kõik soovitud väärtuse kordused
Nagu eelnevalt mainitud VPR ei saa skannitud vahemikust välja võtta kõiki dubleerivaid väärtusi. Selleks on vaja veidi keerukamat valemit, mis koosneb mitmest Exceli funktsioonist, nt INDEX (INDEX), VÄIKESED (VÄIKE) ja ROW (LINE)
Näiteks leiab allolev valem kõik lahtri F2 väärtuse kordused vahemikus B2:B16 ja tagastab veerus C samade ridade tulemuse.
{=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));"")}
Sisestage see massiivi valem mitmesse külgnevasse lahtrisse, näiteks lahtritesse F4: F8nagu on näidatud alloleval joonisel. Lahtrite arv peab olema võrdne otsitava väärtuse maksimaalse võimaliku korduste arvuga või sellest suurem. Ärge unustage klõpsata Ctrl + Tõstuklahv + Sisestamassiivi valemi õigeks sisestamiseks.
Kui soovite mõista, kuidas see toimib, sukeldume veidi valemi üksikasjadesse:
Osa 1:
IF($F$2=B2:B16,ROW(C2:C16)-1,"")
ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;"")
$F$2=B2:B16 – võrrelge väärtust lahtris F2 iga väärtusega vahemikus B2:B16. Kui leitakse vaste, siis avaldis STRING(C2:C16)-1 tagastab vastava rea numbri (väärtus -1 võimaldab teil päise rida mitte lisada). Kui vasteid pole, siis funktsioon IF (IF) tagastab tühja stringi.
Funktsiooni tulemus IF (IF) on selline horisontaalne massiiv: {1,"",3,"",5,"","","","","","",12,"","",""}
Osa 2:
ROW()-3
СТРОКА()-3
Siin on funktsioon ROW (LINE) toimib täiendava loendurina. Kuna valem on kopeeritud lahtritesse F4:F9, lahutame arvu 3 funktsiooni tulemusest väärtuse saamiseks 1 lahtris F4 (rida 4, lahuta 3), et saada 2 lahtris F5 (rida 5, lahuta 3) ja nii edasi.
Osa 3:
SMALL(IF($F$2=B2:B16,ROW(C2:C16)-1,""),ROW()-3))
НАИМЕНЬШИЙ(ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;"");СТРОКА()-3))
funktsioon VÄIKESED (VÄIKE) tagastab n-oi andmemassiivi väikseim väärtus. Meie puhul määrab funktsioon, millise positsiooni (väikseimast) tagastada ROW (LINE) (vt 2. osa). Niisiis, raku jaoks F4 funktsioon VÄIKE({massiivi},1) Tulu 1-ndal (väikseim) massiivi element, st 1. Raku jaoks F5 Tulu 2-ndal massiivi väikseim element, st 3Jne
Osa 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))
funktsioon INDEX (INDEX) tagastab lihtsalt massiivi konkreetse lahtri väärtuse C2:C16. Raku jaoks F4 funktsioon INDEKS ($C$2:$C$16) naaseb õunadeest F5 funktsioon INDEKS ($C$2:$C$16) naaseb maiustused ja nii edasi.
Osa 5:
IFERROR()
ЕСЛИОШИБКА()
Lõpuks paneme valemi funktsiooni sisse VIGA (IFERROR), sest tõenäoliselt ei jää te veateatega rahule # AT (#N/A), kui lahtrite arv, millesse valem kopeeritakse, on väiksem kui dubleerivate väärtuste arv vaadeldavas vahemikus.
XNUMXD otsing teadaoleva rea ja veeru järgi
XNUMXD-otsingu sooritamine Excelis hõlmab väärtuse otsimist teadaoleva rea ja veeru numbri järgi. Teisisõnu ekstraheerite lahtri väärtuse konkreetse rea ja veeru ristumiskohas.
Niisiis, pöördume oma tabeli poole ja kirjutame funktsiooniga valem VPR, kust leiab infot märtsis müüdavate sidrunite maksumuse kohta.
XNUMXD-otsingu tegemiseks on mitu võimalust. Tutvu valikutega ja vali endale sobivaim.
Funktsioonid VLOOKUP ja MATCH
Saate kasutada mitmeid funktsioone VPR (VLOOKUP) ja ROHKEM AVALDATUD (MATCH), et leida väärtus väljade ristumiskohas Название продукта (string) ja kuu kõnealuse massiivi (veerg):
=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);ЛОЖЬ)
Ülaltoodud valem on tavaline funktsioon VPR, mis otsib täpset vastet väärtusele „Lemons” lahtrites A2 kuni A9. Kuid kuna te ei tea, millises veerus märtsimüügid on, ei saa te määrata kolmanda funktsiooni argumendi veeru numbrit. VPR. Selle asemel kasutatakse funktsiooni ROHKEM AVALDATUDselle veeru määratlemiseks.
MATCH("Mar",$A$1:$I$1,0)
ПОИСКПОЗ("Mar";$A$1:$I$1;0)
Inimkeelde tõlgituna tähendab see valem:
- Otsime tegelasi “Mar” – argument lookup_value (otsingu_väärtus);
- Lahtrites A1 kuni I1 otsimine – argument otsingu_massiiv (otsingu_massiiv);
- Täpse vaste tagastamine – argument vaste_tüüp (vaste_tüüp).
Kasutamine 0 kolmandas argumendis ütlete funktsioonid ROHKEM AVALDATUD otsige esimest väärtust, mis täpselt vastab teie otsitavale väärtusele. See on samaväärne väärtusega FALSE (FALSE) neljanda argumendi jaoks VPR.
Nii saate Excelis luua kahesuunalise otsingu valemi, mida tuntakse ka kahemõõtmelise või kahesuunalise otsinguna.
SUMPRODUCT funktsioon
funktsioon SUMPRODUCT (SUMPRODUCT) tagastab valitud massiivide korrutiste summa:
=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)
INDEX ja MATCH funktsioonid
Järgmises artiklis selgitan neid funktsioone üksikasjalikult, nii et praegu saate lihtsalt selle valemi kopeerida:
=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))
Nimetatud vahemikud ja ristmiku operaator
Kui teile ei meeldi kõik need keerulised Exceli valemid, võib teile meeldida see visuaalne ja meeldejääv viis:
- Valige tabel, avage vahekaart Valemid (Valemid) ja klõpsake Loo valikust (Loo valikust).
- Märkige ruudud Ülemine rida (ülal real) ja Vasak veerg (vasakul veerus). Microsoft Excel määrab teie arvutustabeli ülemises reas ja vasakpoolses veerus olevate väärtuste vahemikele nimed. Nüüd saate otsida neid nimesid otse ilma valemeid loomata.
- Kirjutage igasse tühja lahtrisse =rea_nimi veeru_nimi, näiteks nii:
=Sidrunid märts
… või vastupidi:
=Mar Lemons
Pidage meeles, et ridade ja veergude nimed tuleb eraldada tühikuga, mis sel juhul toimib nagu ristmiku operaator.
Kui sisestate nime, kuvab Microsoft Excel tööriistaspikri koos sobivate nimede loendiga, täpselt nagu valemi sisestamisel.
- press sisene ja kontrolli tulemust
Üldiselt on kahemõõtmelise otsingu tulemus sama, ükskõik millise ülaltoodud meetoditest valite:
Mitme VLOOKUP-i kasutamine ühes valemis
Juhtub, et põhitabelil ja otsingutabelil pole ühtki ühist veergu ja see takistab tavafunktsiooni kasutamist VPR. Siiski on veel üks tabel, mis ei sisalda meid huvitavat teavet, kuid millel on ühine veerg põhitabeli ja otsingutabeliga.
Vaatame järgmist näidet. Meil on veeruga põhitabel SKU (uus), kuhu soovitakse teisest tabelist lisada vastavate hindadega veerg. Lisaks on meil 2 otsingutabelit. Esimene (otsingutabel 1) sisaldab värskendatud numbreid SKU (uus) ja tootenimed ning teine (Otsingutabel 2) – tootenimed ja vanad numbrid SKU (vana).
Hindade lisamiseks teisest otsingutabelist põhitabelisse peate tegema toimingu, mida nimetatakse topelt VPR või pesastatud VPR.
- Kirjutage funktsioon VPR, mis leiab tabelist toote nimetuse Otsingutabel 1kasutamine SKU, soovitud väärtusena:
=VLOOKUP(A2,New_SKU,2,FALSE)=ВПР(A2;New_SKU;2;ЛОЖЬ)Siin Uus_SKU - nimega vahemik $A:$B laual Otsingutabel 1, 2 – see on veerg B, mis sisaldab kaupade nimetusi (vt ülaltoodud pilti)
- Kirjutage valem hindade sisestamiseks tabelist Otsingutabel 2 tuntud tootenimede põhjal. Selleks kleepige uue funktsiooni otsinguväärtuseks varem loodud valem VPR:
=VLOOKUP(VLOOKUP(A2,New_SKU,2,FALSE),Price,3,FALSE)=ВПР(ВПР(A2;New_SKU;2;ЛОЖЬ);Price;3;ЛОЖЬ)Siin Hind - nimega vahemik $A:$C laual Otsingutabel 2, 3 on veerg C, mis sisaldab hindu.
Allolev joonis näitab meie loodud valemiga tagastatud tulemust:
Erinevate tabelite andmete dünaamiline asendamine VLOOKUP ja INDIRECT abil
Esmalt teeme selgeks, mida me mõistame väljendi „Erinevate tabelite andmete dünaamiline asendamine” all, et olla kindel, et mõistame üksteist õigesti.
On olukordi, kus sama vormingu andmetega on mitu lehte ja sõltuvalt lahtrisse sisestatud väärtusest on vaja teatud lehelt vajalik teave eraldada. Ma arvan, et seda on lihtsam selgitada näitega.
Kujutage ette, et teil on müügiaruanded mitme piirkonna kohta samade toodetega ja samas vormingus. Soovite leida konkreetse piirkonna müüginumbreid:
Kui teil on ainult kaks sellist aruannet, saate kasutada häbiväärselt lihtsat funktsioonidega valemit VPR и IF (IF), et valida otsimiseks soovitud aruanne:
=VLOOKUP($D$2,IF($D3="FL",FL_Sales,CA_Sales),2,FALSE)
=ВПР($D$2;ЕСЛИ($D3="FL";FL_Sales;CA_Sales);2;ЛОЖЬ)
kus:
- $ D $ 2 on lahter, mis sisaldab toote nime. Pange tähele, et kasutame siin absoluutviiteid, et vältida valemi teistesse lahtritesse kopeerimisel otsinguväärtuse muutmist.
- D3 dollarit on lahter piirkonna nimega. Kasutame absoluutset veeruviidet ja suhtelist reaviidet, kuna plaanime kopeerida valemi teistesse sama veeru lahtritesse.
- FL_Sales и CA_Müük – vastavaid müügiaruandeid sisaldavate tabelite (või nimeliste vahemike) nimed. Muidugi saab kasutada näiteks tavalisi lehtede nimesid ja lahtrivahemiku viiteid „FL Sheet”!$A$3:$B$10, kuid nimega vahemikud on palju mugavamad.
Kui aga selliseid tabeleid on palju, siis funktsioon IF ei ole parim lahendus. Selle asemel saate funktsiooni kasutada KAUDSEID (INDIRECT) soovitud otsinguvahemiku tagastamiseks.
Nagu te ilmselt teate, funktsioon KAUDSEID kasutatakse tekstistringi antud lingi tagastamiseks, mis on täpselt see, mida me praegu vajame. Seega asenda julgelt ülaltoodud valemis avaldis funktsiooniga IF funktsiooniga sidumiseks KAUDSEID. Siin on kombinatsioon VPR и KAUDSEID töötab suurepäraselt koos:
=VLOOKUP($D$2,INDIRECT($D3&"_Sales"),2,FALSE)
=ВПР($D$2;ДВССЫЛ($D3&"_Sales");2;ЛОЖЬ)
kus:
- $ D $ 2 – see on lahter toote nimega, see on absoluutse seose tõttu muutumatu.
- D3 dollarit on lahter, mis sisaldab piirkonna nime esimest osa. Meie näites on see FL.
- _Müük – kõigi nimetatud vahemike või tabelite nime ühine osa. Lahtris D3 oleva väärtusega kombineerituna moodustab see nõutava vahemiku täisnimetuse. Allpool on mõned üksikasjad neile, kes on selle funktsiooniga uued KAUDSEID.
Kuidas INDIRECT ja VLOOKUP töötavad
Kõigepealt tuletan teile meelde funktsiooni süntaksit KAUDSEID (KAUDNE):
INDIRECT(ref_text,[a1])
ДВССЫЛ(ссылка_на_текст;[a1])
Esimene argument võib olla lahtriviide (stiil A1 või R1C1), vahemiku nimi või tekstistring. Teine argument määrab, millist lingi stiili esimene argument sisaldab:
- A1kui argument on TÕELINE KOOD (TRUE) või määramata;
- R1C1, Kui FAS E (VALE).
Meie puhul on lingil stiil A1, nii et võite teise argumendi välja jätta ja keskenduda esimesele.
Nii et pöördume tagasi meie müügiaruannete juurde. Kui mäletate, on iga aruanne eraldi tabel, mis asub eraldi lehel. Valemi korrektseks toimimiseks peate oma tabelitele (või vahemikele) nime andma ja kõigil nimedel peab olema ühine osa. Näiteks nii: CA_Müük, FL_Müük, TX_Müük ja nii edasi. Nagu näete, on "_Müük" kõigis nimedes.
funktsioon KAUDSEID ühendab väärtuse veerus D ja tekstistringi "_Sales", andes seeläbi teada VPR millisest tabelist otsida. Kui lahter D3 sisaldab väärtust “FL”, otsib valem tabelist FL_Müük, kui “CA” – tabelis CA_Müük ja nii edasi.
Funktsioonide tulemus VPR и KAUDSEID saab olema järgmine:
Kui andmed asuvad erinevates Exceli raamatutes, siis peate nimelise vahemiku ette lisama raamatu nime, näiteks:
=VLOOKUP($D$2,INDIRECT($D3&"Workbook1!_Sales"),2,FALSE)
=ВПР($D$2;ДВССЫЛ($D3&"Workbook1!_Sales");2;ЛОЖЬ)
Kui funktsioon KAUDSEID viitab teisele töövihikule, peab see töövihik olema avatud. Kui see on suletud, teatab funktsioon veast. #REF! (#SSYL!).