sadržaj
U drugom dijelu našeg tutorijala o funkciji VPR (VLOOKUP) u Excelu, analizirat ćemo nekoliko primjera koji će vam pomoći da usmjerite svu snagu VPR za rješavanje najambicioznijih Excel zadataka. Primjeri pretpostavljaju da već imate osnovno znanje o tome kako ova funkcija funkcionira. Ako ne, možda ćete biti zainteresirani da počnete s prvim dijelom ovog vodiča, koji objašnjava sintaksu i osnovnu upotrebu. VPR. Pa, hajde da počnemo.
Pretražujte u Excelu po više kriterijuma
funkcija VPR u Excelu je zaista moćan alat za izvođenje pretraživanja određene vrijednosti u bazi podataka. Međutim, postoji značajno ograničenje – njegova sintaksa vam omogućava da pretražujete samo jednu vrijednost. Šta ako želite da pretražujete po više uslova? Rješenje ćete pronaći u nastavku.
Primjer 1: Pretraga po 2 različita kriterija
Pretpostavimo da imamo listu narudžbi i želimo je pronaći Količina robe (količina), na osnovu dva kriterijuma – Ime klijenta (Kupac) i Название продукта (Proizvod). Stvar se komplikuje činjenicom da je svaki od kupaca naručio nekoliko vrsta robe, što se može vidjeti iz donje tabele:
redovna funkcija VPR neće raditi u ovom scenariju jer će vratiti prvu vrijednost koju pronađe koja odgovara datoj vrijednosti pretraživanja. Na primjer, ako želite znati količinu artikla Slatkiši'naručio kupac Jeremy Hill, napišite sljedeću formulu:
=VLOOKUP(B1,$A$5:$C$14,3,FALSE)
=ВПР(B1;$A$5:$C$14;3;ЛОЖЬ)
– ova formula će vratiti rezultat 15koji odgovaraju proizvodu jabuke, jer je to prva vrijednost koja se podudara.
Postoji jednostavno rješenje – napravite dodatnu kolonu u kojoj ćete kombinirati sve željene kriterije. U našem primjeru, ovo su kolone Ime klijenta (Kupac) i Название продукта (Proizvod). Ne zaboravite da spojena kolona uvijek mora biti krajnja lijeva kolona u rasponu pretraživanja, jer je to lijeva kolona koju funkcija VPR gleda gore kada traži vrijednost.
Dakle, dodajete pomoćnu kolonu u tablicu i kopirate sljedeću formulu preko svih njenih ćelija: =B2&C2. Ako želite da niz bude čitljiviji, možete razdvojiti kombinovane vrijednosti razmakom: =B2&» «&C2. Nakon toga možete koristiti sljedeću formulu:
=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;ЛОЖЬ)
Gdje je ćelija B1 sadrži konkateniranu vrijednost argumenta lookup_value (vrijednost_traživanja) i 4 – Argument col_index_num (broj_kolone), tj. broj kolone koja sadrži podatke koji se preuzimaju.
Primer 2: VLOOKUP po dva kriterijuma sa tabelom koja se gleda na drugom listu
Ako trebate ažurirati glavnu tablicu (Main table) dodavanjem podataka iz druge tablice (Lookup table), koja se nalazi na drugom listu ili u drugoj Excel radnoj knjizi, tada možete prikupiti željenu vrijednost direktno u formuli koju umetnete u glavni sto.
Kao iu prethodnom primjeru, trebat će vam pomoćna kolona u tabeli za traženje s kombiniranim vrijednostima. Ova kolona mora biti krajnja lijeva kolona u rasponu pretraživanja.
Dakle, formula sa VPR može biti ovako:
=VLOOKUP(B2&" "&C2,Orders!$A&$2:$D$2,4,FALSE)
=ВПР(B2&" "&C2;Orders!$A&$2:$D$2;4;ЛОЖЬ)
Ovdje kolone B i C sadrže imena kupaca i imena proizvoda, odnosno vezu Narudžbe!$A&$2:$D$2 definira tabelu za traženje u drugom listu.
Da biste formulu učinili čitljivijom, rasponu prikaza možete dati ime i tada će formula izgledati mnogo jednostavnije:
=VLOOKUP(B2&" "&C2,Orders,4,FALSE)
=ВПР(B2&" "&C2;Orders;4;ЛОЖЬ)
Da bi formula radila, vrijednosti u krajnjoj lijevoj koloni tablice koju gledate moraju se kombinirati na potpuno isti način kao u kriterijima pretraživanja. Na gornjoj slici kombinirali smo vrijednosti u2bu2band stavili razmak između njih, na isti način koji trebate učiniti u prvom argumentu funkcije (BXNUMX& “” & CXNUMX).
Zapamti! funkcija VPR ograničen na 255 znakova, ne može tražiti vrijednost dužu od 255 znakova. Imajte to na umu i pazite da dužina željene vrijednosti ne prelazi ovu granicu.
Slažem se da dodavanje pomoćne kolone nije najelegantnije i nije uvijek prihvatljivo rješenje. Isto možete učiniti i bez pomoćnog stupca, ali to bi zahtijevalo mnogo složeniju formulu s kombinacijom funkcija INDEX (INDEX) i MATCH (IZLOŽENIJI).
Izvlačimo 2., 3. itd. vrijednosti koristeći VLOOKUP
To već znate VPR može vratiti samo jednu odgovarajuću vrijednost, tačnije, prvu pronađenu. Ali šta ako se ova vrijednost ponovi nekoliko puta u prikazanom nizu, a želite izdvojiti 2. ili 3. od njih? Šta ako sve vrijednosti? Problem se čini komplikovanim, ali rješenje postoji!
Pretpostavimo da jedna kolona tabele sadrži imena kupaca (ime kupca), a druga kolona sadrži proizvode (proizvod) koje su kupili. Pokušajmo pronaći 2., 3. i 4. artikle koje je kupio određeni kupac.
Najlakši način je da dodate pomoćnu kolonu prije kolone ime kupca i ispunite ga imenima kupaca s brojem ponavljanja svakog imena, na primjer, John Doe1, John Doe2 itd. Izradit ćemo trik s numeriranjem koristeći funkciju COUNTIF (COUNTIF), s obzirom da su imena kupaca u koloni B:
=B2&COUNTIF($B$2:B2,B2)
=B2&СЧЁТЕСЛИ($B$2:B2;B2)
Nakon toga možete koristiti normalnu funkciju VPRda pronađete traženu narudžbu. Na primjer:
- pronaći 2. artikla po narudžbi kupca Dan Brown:
=VLOOKUP("Dan Brown2",$A$2:$C$16,3,FALSE)=ВПР("Dan Brown2";$A$2:$C$16;3;ЛОЖЬ) - pronaći 3. artikla po narudžbi kupca Dan Brown:
=VLOOKUP("Dan Brown3",$A$2:$C$16,3,FALSE)=ВПР("Dan Brown3";$A$2:$C$16;3;ЛОЖЬ)
Zapravo, možete unijeti referencu ćelije kao traženu vrijednost umjesto teksta, kao što je prikazano na sljedećoj slici:
Ako samo tražite 2-e ponavljanja, možete to učiniti bez pomoćnog stupca kreiranjem složenije formule:
=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;ИСТИНА);"")
U ovoj formuli:
- $F$2 – ćelija u kojoj se nalazi ime kupca (nepromijenjeno, imajte na umu – veza je apsolutna);
- $ B $ – kolona ime kupca;
- Table4 – Vaš sto (ovo mjesto može biti i običan raspon);
- $ C16 – krajnja ćelija vaše tabele ili opsega.
Ova formula pronalazi samo drugu odgovarajuću vrijednost. Ako trebate izvući preostala ponavljanja, koristite prethodno rješenje.
Ako vam je potrebna lista svih podudaranja – funkcija VPR ovo nije pomoćnik, jer vraća samo jednu po jednu vrijednost – period. Ali Excel ima funkciju INDEX (INDEX), koji se lako nosi sa ovim zadatkom. Kako će takva formula izgledati, naučit ćete u sljedećem primjeru.
Dohvati sva ponavljanja željene vrijednosti
Kao što je gore pomenuto VPR ne može izdvojiti sve duple vrijednosti iz skeniranog raspona. Da biste to učinili, potrebna vam je malo složenija formula, sastavljena od nekoliko Excel funkcija, kao npr INDEX (INDEX), SMALL (MALA) i RED (LINE)
Na primjer, formula ispod pronalazi sva ponavljanja vrijednosti iz ćelije F2 u rasponu B2:B16 i vraća rezultat iz istih redova u stupcu 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));"")}
Unesite ovu formulu niza u više susjednih ćelija, kao što su ćelije F4: F8kao što je prikazano na donjoj slici. Broj ćelija mora biti jednak ili veći od maksimalnog mogućeg broja ponavljanja tražene vrijednosti. Ne zaboravite kliknuti Ctrl + Shift + Enterda pravilno unesete formulu niza.
Ako ste zainteresirani za razumijevanje kako to funkcionira, zaronimo malo u detalje formule:
Dio 1:
IF($F$2=B2:B16,ROW(C2:C16)-1,"")
ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;"")
$F$2=B2:B16 – uporedite vrijednost u ćeliji F2 sa svakom od vrijednosti u rasponu B2:B16. Ako se pronađe podudaranje, onda izraz STRING(C2:C16)-1 vraća broj odgovarajuće linije (vrijednost -1 omogućava vam da ne uključite liniju zaglavlja). Ako nema podudaranja, funkcija IF (IF) vraća prazan niz.
Rezultat funkcije IF (IF) postojat će takav horizontalni niz: {1,"",3,"",5,"","","","","","",12,"","",""}
Dio 2:
ROW()-3
СТРОКА()-3
Evo funkcije RED (LINE) djeluje kao dodatni brojač. Pošto je formula kopirana u ćelije F4:F9, oduzimamo broj 3 iz rezultata funkcije za dobivanje vrijednosti 1 u ćeliji F4 (red 4, oduzmi 3) da dobijete 2 u ćeliji F5 (red 5, oduzmi 3) i tako dalje.
Dio 3:
SMALL(IF($F$2=B2:B16,ROW(C2:C16)-1,""),ROW()-3))
НАИМЕНЬШИЙ(ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;"");СТРОКА()-3))
funkcija SMALL (SMALL) vraća n-oh najmanja vrijednost u nizu podataka. U našem slučaju, koja pozicija (od najmanjeg) za povratak je određena funkcijom RED (LINIJA) (vidi dio 2). Dakle, za ćeliju F4 Funkcija MALE ({niz},1) povraćaj 1. (najmanji) element niza, tj 1. Za ćeliju F5 povraćaj 2. najmanji element u nizu, tj 3, Itd
Dio 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) jednostavno vraća vrijednost određene ćelije u nizu C2:C16. Za ćeliju F4 Funkcija INDEX($C$2:$C$16) Će se vratiti jabukeza F5 Funkcija INDEX($C$2:$C$16) Će se vratiti Slatkiši' i tako dalje.
Dio 5:
IFERROR()
ЕСЛИОШИБКА()
Konačno, formulu stavljamo unutar funkcije IFERROR (IFGREŠKA), jer je malo vjerovatno da ćete biti zadovoljni porukom o grešci #AT (#N/A) ako je broj ćelija u koje je formula kopirana manji od broja dupliciranih vrijednosti u rasponu koji se pregledava.
XNUMXD pretraga po poznatom redu i koloni
Izvođenje XNUMXD pretrage u Excel-u uključuje traženje vrijednosti prema poznatom broju reda i stupca. Drugim riječima, izvlačite vrijednost ćelije na sjecištu određenog reda i stupca.
Dakle, okrenimo se našoj tabeli i napišemo formulu sa funkcijom VPR, koji će pronaći informacije o cijeni prodatih limuna u martu.
Postoji nekoliko načina da izvršite XNUMXD pretragu. Provjerite opcije i odaberite onu koja vam najviše odgovara.
VLOOKUP i MATCH funkcije
Možete koristiti gomilu funkcija VPR (VLOOKUP) i IZLOŽENIJI (MACH) da biste pronašli vrijednost na sjecištu polja Название продукта (string) i Mjesec (kolona) dotičnog niza:
=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);ЛОЖЬ)
Formula iznad je redovna funkcija VPR, koji traži tačnu podudarnost vrijednosti "Limuni" u ćelijama od A2 do A9. Ali pošto ne znate u kojoj se koloni nalaze martovske prodaje, nećete moći postaviti broj kolone za treći argument funkcije. VPR. Umjesto toga, koristi se funkcija IZLOŽENIJIda definišete ovu kolonu.
MATCH("Mar",$A$1:$I$1,0)
ПОИСКПОЗ("Mar";$A$1:$I$1;0)
Prevedeno na ljudski jezik, ova formula znači:
- Tražimo likove “Mar” – argument lookup_value (lookup_value);
- Gledanje u ćelije od A1 do I1 – argument lookup_array (lookup_array);
- Vraćanje tačnog podudaranja – argument match_type (tip_podudaranja).
korišćenje 0 u trećem argumentu kažete funkcije IZLOŽENIJI potražite prvu vrijednost koja točno odgovara vrijednosti koju tražite. Ovo je ekvivalentno vrijednosti FALSE (FALSE) za četvrti argument VPR.
Ovako možete kreirati formulu dvosmjernog pretraživanja u Excelu, također poznatu kao dvodimenzionalna pretraga ili dvosmjerna pretraga.
Funkcija SUMPRODUCT
funkcija SUMPRODUCT (SUMPRODUCT) vraća zbroj proizvoda odabranih nizova:
=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 i MATCH
U sljedećem članku ću detaljno objasniti ove funkcije, tako da za sada možete samo kopirati ovu formulu:
=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 rasponi i operator sjecišta
Ako ne volite sve te složene Excel formule, možda će vam se svidjeti ovaj vizualni i nezaboravan način:
- Odaberite tabelu, otvorite karticu formule (Formule) i kliknite Kreirajte iz odabira (Kreiraj iz odabira).
- Označite polja Gornji red (na liniji iznad) i Lijeva kolona (u koloni lijevo). Microsoft Excel će dodijeliti imena rasponima iz vrijednosti u gornjem redu i lijevoj koloni vaše proračunske tablice. Sada možete pretraživati koristeći ove nazive direktno bez kreiranja formula.
- U bilo koju praznu ćeliju upišite =ime_reda ime_kolone, na primjer ovako:
=Limuni mar
… ili obrnuto:
=Mar Lemons
Zapamtite da nazivi redova i stupaca moraju biti odvojeni razmakom, koji u ovom slučaju funkcionira kao operator presjeka.
Kada unesete ime, Microsoft Excel će prikazati opis alata sa listom podudarnih imena, baš kao kada unesete formulu.
- pritisnuti ući i provjerite rezultat
Općenito, koju god od gore navedenih metoda odaberete, rezultat dvodimenzionalne pretrage bit će isti:
Korištenje više VLOOKUP-a u jednoj formuli
Dešava se da glavna tabela i tabela za pretraživanje nemaju ni jednu zajedničku kolonu, a to vas sprečava da koristite uobičajenu funkciju VPR. Međutim, postoji još jedna tabela koja ne sadrži informacije koje nas zanimaju, ali ima zajedničku kolonu sa glavnom tabelom i tabelom za pretraživanje.
Pogledajmo sljedeći primjer. Imamo glavnu tabelu sa kolonom SKU (novo), gde želite da dodate kolonu sa odgovarajućim cenama iz druge tabele. Osim toga, imamo 2 tabele za pretraživanje. Prvi (Tabela za pretraživanje 1) sadrži ažurirane brojeve SKU (novo) i nazivi proizvoda, a drugi (Lookup table 2) – nazivi proizvoda i stari brojevi SKU (stari).
Da biste dodali cijene iz druge tabele za pretraživanje u glavnu tabelu, morate izvršiti radnju poznatu kao duplo VPR ili ugniježđeni VPR.
- Napišite funkciju VPR, koji pronalazi naziv proizvoda u tabeli Pregledna tabela 1korišćenje SKU, kao željenu vrijednost:
=VLOOKUP(A2,New_SKU,2,FALSE)=ВПР(A2;New_SKU;2;ЛОЖЬ)ovdje New_SKU – imenovani opseg $A:$B u tabeli Pregledna tabela 1, 2 – ovo je kolona B koja sadrži nazive robe (vidi sliku iznad)
- Napišite formulu za umetanje cijena iz tabele Pregledna tabela 2 na osnovu poznatih imena proizvoda. Da biste to učinili, zalijepite formulu koju ste ranije kreirali kao vrijednost traženja za novu funkciju VPR:
=VLOOKUP(VLOOKUP(A2,New_SKU,2,FALSE),Price,3,FALSE)=ВПР(ВПР(A2;New_SKU;2;ЛОЖЬ);Price;3;ЛОЖЬ)ovdje Cijena – imenovani opseg $A:$C u tabeli Pregledna tabela 2, 3 je stupac C koji sadrži cijene.
Na slici ispod je prikazan rezultat koji daje formula koju smo kreirali:
Dinamička zamjena podataka iz različitih tablica korištenjem VLOOKUP i INDIRECT
Prvo, hajde da razjasnimo šta podrazumevamo pod izrazom „Dinamička zamena podataka iz različitih tabela“ da bismo bili sigurni da se pravilno razumemo.
Postoje situacije kada postoji više listova sa podacima istog formata, a potrebno je izdvojiti potrebne informacije iz određenog lista, ovisno o vrijednosti koja je unesena u datu ćeliju. Mislim da je to lakše objasniti na primjeru.
Zamislite da imate izvještaje o prodaji za nekoliko regija sa istim proizvodima i u istom formatu. Želite pronaći podatke o prodaji za određenu regiju:
Ako imate samo dva takva izvještaja, onda možete koristiti sramotno jednostavnu formulu sa funkcijama VPR и IF (IF) za odabir željenog izvještaja za pretraživanje:
=VLOOKUP($D$2,IF($D3="FL",FL_Sales,CA_Sales),2,FALSE)
=ВПР($D$2;ЕСЛИ($D3="FL";FL_Sales;CA_Sales);2;ЛОЖЬ)
gdje:
- $D$2 je ćelija koja sadrži naziv proizvoda. Imajte na umu da ovdje koristimo apsolutne reference kako bismo izbjegli promjenu vrijednosti pretraživanja prilikom kopiranja formule u druge ćelije.
- $D3 je ćelija sa imenom regije. Koristimo apsolutnu referencu kolone i relativnu referencu reda jer planiramo kopirati formulu u druge ćelije u istoj koloni.
- FL_Sales и CA_Sales – imena tabela (ili imenovanih raspona) koje sadrže odgovarajuće izvještaje o prodaji. Možete, naravno, koristiti uobičajena imena listova i reference raspona ćelija, na primjer 'FL Sheet'!$A$3:$B$10, ali imenovani rasponi su mnogo praktičniji.
Međutim, kada postoji mnogo takvih tablica, funkcija IF nije najbolje rješenje. Umjesto toga, možete koristiti funkciju INDIREKTNO (INDIREKTNO) za vraćanje željenog raspona pretraživanja.
Kao što verovatno znate, funkcija INDIREKTNO koristi se za vraćanje veze date tekstualnim nizom, što je upravo ono što nam sada treba. Dakle, hrabro zamijenite izraz u gornjoj formuli funkcijom IF za povezivanje sa funkcijom INDIREKTNO. Evo kombinacije VPR и INDIREKTNO odlično radi sa:
=VLOOKUP($D$2,INDIRECT($D3&"_Sales"),2,FALSE)
=ВПР($D$2;ДВССЫЛ($D3&"_Sales");2;ЛОЖЬ)
gdje:
- $D$2 – ovo je ćelija s nazivom proizvoda, nepromijenjena je zbog apsolutne veze.
- $D3 je ćelija koja sadrži prvi dio imena regije. U našem primjeru, ovo FL.
- _Sales – zajednički dio imena svih imenovanih raspona ili tablica. Kada se kombinuje sa vrijednošću u ćeliji D3, formira potpuno kvalificirano ime potrebnog raspona. Ispod su neki detalji za one koji su novi u ovoj funkciji INDIREKTNO.
Kako rade INDIRECT i VLOOKUP
Prvo, da vas podsjetim na sintaksu funkcije INDIREKTNO (INDIREKTNO):
INDIRECT(ref_text,[a1])
ДВССЫЛ(ссылка_на_текст;[a1])
Prvi argument može biti referenca ćelije (stil A1 ili R1C1), ime raspona ili tekstualni niz. Drugi argument određuje koji stil veze je sadržan u prvom argumentu:
- A1ako je argument TRUE CODE (TRUE) ili nije navedeno;
- R1C1, ako FAS E (LAŽNO).
U našem slučaju, veza ima stil A1, tako da možete izostaviti drugi argument i fokusirati se na prvi.
Dakle, vratimo se našim izvještajima o prodaji. Ako se sjećate, onda je svaki izvještaj zasebna tabela koja se nalazi na posebnom listu. Da bi formula radila ispravno, morate imenovati svoje tabele (ili opsege), a sva imena moraju imati zajednički deo. Na primjer, ovako: CA_Sales, FL_Sales, TX_Sales i tako dalje. Kao što vidite, “_Sales” je prisutno u svim nazivima.
funkcija INDIREKTNO povezuje vrijednost u koloni D i tekstualni niz “_Sales”, što govori VPR u kojoj tabeli pretraživati. Ako ćelija D3 sadrži vrijednost “FL”, formula će pretražiti tabelu FL_Sales, ako je “CA” – u tabeli CA_Sales i tako dalje.
Rezultat funkcija VPR и INDIREKTNO bit će sljedeće:
Ako se podaci nalaze u različitim Excel knjigama, tada morate dodati naziv knjige ispred imenovanog raspona, na primjer:
=VLOOKUP($D$2,INDIRECT($D3&"Workbook1!_Sales"),2,FALSE)
=ВПР($D$2;ДВССЫЛ($D3&"Workbook1!_Sales");2;ЛОЖЬ)
Ako je funkcija INDIREKTNO se odnosi na drugu radnu svesku, ta radna sveska mora biti otvorena. Ako je zatvorena, funkcija će prijaviti grešku. #REF! (#SSYL!).