Cuprins
- Căutați în Excel după mai multe criterii
- Extragem valorile a 2-a, a 3-a etc. folosind VLOOKUP
- Recuperați toate repetările valorii dorite
- XNUMXD căutare după rând și coloană cunoscute
- Folosind mai multe VLOOKUP într-o singură formulă
- Înlocuirea dinamică a datelor din diferite tabele folosind VLOOKUP și INDIRECT
În a doua parte a tutorialului nostru despre funcție VPR (CĂUTARE V) în Excel, vom analiza câteva exemple care vă vor ajuta să direcționați toată puterea VPR pentru a rezolva cele mai ambițioase sarcini Excel. Exemplele presupun că aveți deja cunoștințe de bază despre cum funcționează această caracteristică. Dacă nu, ați putea fi interesat să începeți cu prima parte a acestui tutorial, care explică sintaxa și utilizarea de bază. VPR. Ei bine, să începem.
Căutați în Excel după mai multe criterii
Funcţie VPR în Excel este un instrument foarte puternic pentru a efectua căutări pentru o anumită valoare într-o bază de date. Cu toate acestea, există o limitare semnificativă - sintaxa sa vă permite să căutați o singură valoare. Ce se întâmplă dacă doriți să căutați după mai multe condiții? Soluția o vei găsi mai jos.
Exemplul 1: Căutați după 2 criterii diferite
Să presupunem că avem o listă de comenzi și vrem să găsim Cantitatea de bunuri (Cant.), pe baza a două criterii – Numele clientului (Client) и Название продукта (Produs). Problema este complicată de faptul că fiecare dintre cumpărători a comandat mai multe tipuri de mărfuri, după cum se poate observa din tabelul de mai jos:
funcție obișnuită VPR nu va funcționa în acest scenariu, deoarece va returna prima valoare pe care o găsește care se potrivește cu valoarea de căutare dată. De exemplu, dacă doriți să aflați cantitatea unui articol Dulciuri'comandat de cumparator Jeremy Hill, scrie următoarea formulă:
=VLOOKUP(B1,$A$5:$C$14,3,FALSE)
=ВПР(B1;$A$5:$C$14;3;ЛОЖЬ)
– această formulă va returna rezultatul 15corespunzătoare produsului mere, pentru că este prima valoare care se potrivește.
Există o soluție simplă - creați o coloană suplimentară în care să combinați toate criteriile dorite. În exemplul nostru, acestea sunt coloanele Numele clientului (Client) и Название продукта (Produs). Nu uitați că coloana îmbinată trebuie să fie întotdeauna coloana din stânga din intervalul de căutare, deoarece funcția este coloana din stânga VPR ridică privirea când caută o valoare.
Deci, adăugați o coloană auxiliară la tabel și copiați următoarea formulă peste toate celulele sale: =B2&C2. Dacă doriți ca șirul să fie mai lizibil, puteți separa valorile combinate cu un spațiu: =B2&» «&C2. După aceea, puteți utiliza următoarea formulă:
=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;ЛОЖЬ)
Unde este celula B1 conține valoarea concatenata a argumentului lookup_value (lookup_value) și 4 - Argument col_index_num (număr_coloană), adică numărul coloanei care conține datele de preluat.
Exemplul 2: CĂUTARE V după două criterii, tabelul fiind vizualizat pe o altă foaie
Dacă trebuie să actualizați tabelul principal (Tabelul principal) adăugând date din al doilea tabel (Tabelul de căutare), care se află pe o altă foaie sau într-un alt registru de lucru Excel, atunci puteți colecta valoarea dorită direct în formula pe care o introduceți în masa principală.
Ca și în exemplul anterior, veți avea nevoie de o coloană auxiliară în tabelul de căutare cu valorile combinate. Această coloană trebuie să fie coloana din stânga din intervalul de căutare.
Deci formula cu VPR ar putea fi asa:
=VLOOKUP(B2&" "&C2,Orders!$A&$2:$D$2,4,FALSE)
=ВПР(B2&" "&C2;Orders!$A&$2:$D$2;4;ЛОЖЬ)
Aici, coloanele B și C conțin numele clienților și, respectiv, numele produselor și linkul Comenzi!$A&$2:$D$2 definește un tabel de căutat într-o altă foaie.
Pentru a face formula mai lizibilă, puteți da un nume intervalului de vizualizare, iar apoi formula va arăta mult mai simplă:
=VLOOKUP(B2&" "&C2,Orders,4,FALSE)
=ВПР(B2&" "&C2;Orders;4;ЛОЖЬ)
Pentru ca formula să funcționeze, valorile din coloana din stânga a tabelului la care te uiți trebuie combinate exact în același mod ca și în criteriile de căutare. În figura de mai sus, am combinat valorile u2bu2band și punem un spațiu între ele, așa cum trebuie să faceți în primul argument al funcției (BXNUMX& “” & CXNUMX).
Tine minte! Funcţie VPR limitat la 255 de caractere, nu poate căuta o valoare care are mai mult de 255 de caractere. Țineți cont de acest lucru și asigurați-vă că lungimea valorii dorite nu depășește această limită.
Sunt de acord că adăugarea unei coloane auxiliare nu este soluția cea mai elegantă și nu întotdeauna acceptabilă. Puteți face același lucru fără coloana de ajutor, dar asta ar necesita o formulă mult mai complexă, cu o combinație de funcții INDEX (INDEX) și MECI (MAI EXPUS).
Extragem valorile a 2-a, a 3-a etc. folosind VLOOKUP
Știi deja asta VPR poate returna o singură valoare potrivită, mai exact prima găsită. Dar dacă această valoare se repetă de mai multe ori în matricea vizualizată și doriți să extrageți a doua sau a treia dintre ele? Dacă toate valorile? Problema pare complicată, dar soluția există!
Să presupunem că o coloană a tabelului conține numele clienților (Numele clientului), iar cealaltă coloană conține produsele (Produs) pe care aceștia le-au cumpărat. Să încercăm să găsim al 2-lea, al 3-lea și al 4-lea articol achiziționat de un anumit client.
Cel mai simplu mod este să adăugați o coloană auxiliară înaintea coloanei Numele clientului și completați-l cu numele clienților cu numărul de repetare al fiecărui nume, de exemplu, John Doe1, John Doe2 etc. Vom face trucul cu numerotarea folosind funcția COUNTIF (COUNTIF), dat fiind că numele clienților sunt în coloana B:
=B2&COUNTIF($B$2:B2,B2)
=B2&СЧЁТЕСЛИ($B$2:B2;B2)
După aceea, puteți utiliza funcția normală VPRpentru a găsi comanda necesară. De exemplu:
- Găsi 2-lea articol comandat de client Dan Brown:
=VLOOKUP("Dan Brown2",$A$2:$C$16,3,FALSE)=ВПР("Dan Brown2";$A$2:$C$16;3;ЛОЖЬ) - Găsi 3-lea articol comandat de client Dan Brown:
=VLOOKUP("Dan Brown3",$A$2:$C$16,3,FALSE)=ВПР("Dan Brown3";$A$2:$C$16;3;ЛОЖЬ)
De fapt, puteți introduce o referință de celulă ca valoare de căutare în loc de text, așa cum se arată în figura următoare:
Dacă doar cauți 2 e- repetare, o puteți face fără coloana auxiliară creând o formulă mai complexă:
=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;ИСТИНА);"")
In aceasta formula:
- $ F $ 2 – o celulă care conține numele cumpărătorului (este neschimbat, vă rugăm să rețineți – linkul este absolut);
- $ B $ – coloana Numele clientului;
- Table4 – Masa ta (acest loc poate fi și o gamă obișnuită);
- $ C16 – celula finală a tabelului sau intervalului dvs.
Această formulă găsește doar a doua valoare de potrivire. Dacă trebuie să extrageți repetările rămase, utilizați soluția anterioară.
Dacă aveți nevoie de o listă cu toate potrivirile - funcția VPR acesta nu este un ajutor, deoarece returnează doar o valoare la un moment dat - perioadă. Dar Excel are o funcție INDEX (INDEX), care poate face față cu ușurință acestei sarcini. Cum va arăta o astfel de formulă, veți afla în exemplul următor.
Recuperați toate repetările valorii dorite
Așa cum sa menționat mai sus VPR nu poate extrage toate valorile duplicate din intervalul scanat. Pentru a face acest lucru, aveți nevoie de o formulă ceva mai complexă, formată din mai multe funcții Excel, precum INDEX (INDEX), MIC (MIC) și RÂND (LINIA)
De exemplu, formula de mai jos găsește toate repetările valorii din celula F2 în intervalul B2:B16 și returnează rezultatul din aceleași rânduri din coloana 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));"")}
Introduceți această formulă matrice în mai multe celule adiacente, cum ar fi celulele F4: F8după cum se arată în figura de mai jos. Numărul de celule trebuie să fie egal sau mai mare decât numărul maxim posibil de repetări ale valorii căutate. Nu uitați să faceți clic Ctrl + Shift + Enterpentru a introduce corect formula matricei.
Dacă sunteți interesat să înțelegeți cum funcționează, să ne aprofundăm puțin în detaliile formulei:
Partea 1:
IF($F$2=B2:B16,ROW(C2:C16)-1,"")
ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;"")
$F$2=B2:B16 – comparați valoarea din celula F2 cu fiecare dintre valorile din intervalul B2:B16. Dacă se găsește o potrivire, atunci expresia ȘIR (C2:C16)-1 returnează numărul liniei corespunzătoare (valoarea -1 vă permite să nu includeți linia antetului). Dacă nu există potriviri, funcția IF (IF) returnează un șir gol.
Rezultatul funcției IF (DACĂ) va exista o astfel de matrice orizontală: {1,"",3,"",5,"","","","","","",12,"","",""}
Partea 2:
ROW()-3
СТРОКА()-3
Aici funcția RÂND (LINE) acționează ca un numărător suplimentar. Deoarece formula este copiată în celulele F4:F9, scădem numărul 3 de la rezultatul funcției pentru a obține valoare 1 în celulă F4 (linia 4, scade 3) pentru a obține 2 în celulă F5 (linia 5, scade 3) și așa mai departe.
Partea 3:
SMALL(IF($F$2=B2:B16,ROW(C2:C16)-1,""),ROW()-3))
НАИМЕНЬШИЙ(ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;"");СТРОКА()-3))
Funcţie MIC (MIC) revine n-oh cea mai mică valoare din matricea de date. În cazul nostru, ce poziție (de la cea mai mică) să revină este determinată de funcție RÂND (LINIE) (vezi partea 2). Deci, pentru o celulă F4 funcţie MIC({matrice},1) Returnează 1-lea (cel mai mic) element de matrice, de ex 1. Pentru celulă F5 Returnează 2-lea cel mai mic element din matrice, adică 3, Etc
Partea 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))
Funcţie INDEX (INDEX) returnează pur și simplu valoarea unei anumite celule dintr-o matrice C2:C16. Pentru celulă F4 funcţie INDEX($C$2:$C$16) va reveni merePentru F5 funcţie INDEX($C$2:$C$16) va reveni Dulciuri' şi aşa mai departe.
Partea 5:
IFERROR()
ЕСЛИОШИБКА()
În cele din urmă, punem formula în interiorul funcției DACA EROARE (IFEROARE), deoarece este puțin probabil să fiți mulțumit de mesajul de eroare #LA (#N/A) dacă numărul de celule în care este copiată formula este mai mic decât numărul de valori duplicate din intervalul care este vizualizat.
XNUMXD căutare după rând și coloană cunoscute
Efectuarea unei căutări XNUMXD în Excel implică căutarea unei valori după un număr cunoscut de rând și coloană. Cu alte cuvinte, extrageți valoarea celulei la intersecția unui anumit rând și coloană.
Deci, să ne întoarcem la tabelul nostru și să scriem o formulă cu o funcție VPR, care va găsi informații despre costul lămâilor vândute în martie.
Există mai multe moduri de a efectua o căutare XNUMXD. Verifică opțiunile și alege-o pe cea care ți se potrivește cel mai bine.
Funcțiile VLOOKUP și MATCH
Puteți folosi o mulțime de funcții VPR (CĂUTARE V) și MAI EXPUSĂ (MATCH) pentru a găsi valoarea la intersecția câmpurilor Название продукта (șir) și Lună (coloana) a matricei în cauză:
=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 de mai sus este o funcție obișnuită VPR, care caută o potrivire exactă a valorii „Lămâi” în celulele de la A2 la A9. Dar, deoarece nu știți în ce coloană sunt vânzările din martie, nu veți putea seta numărul coloanei pentru al treilea argument al funcției. VPR. În schimb, se folosește funcția MAI EXPUSĂpentru a defini această coloană.
MATCH("Mar",$A$1:$I$1,0)
ПОИСКПОЗ("Mar";$A$1:$I$1;0)
Tradusă în limbajul uman, această formulă înseamnă:
- Căutăm personajele „Mar” – argument lookup_value (valoare_căutare);
- Privind în celulele de la A1 la I1 – argument matrice_căutare (matrice_căutare);
- Se returnează potrivirea exactă – argument tip_potrivire (tip_potrivire).
Utilizarea 0 în al treilea argument, spui funcții MAI EXPUSĂ căutați prima valoare care se potrivește exact cu valoarea pe care o căutați. Aceasta este echivalentă cu valoarea FALS (FALSE) pentru al patrulea argument VPR.
Acesta este modul în care puteți crea o formulă de căutare bidirecțională în Excel, cunoscută și sub numele de căutare bidimensională sau căutare bidirecțională.
Funcția SUMPRODUCT
Funcţie SUMPRODUCT (SUMPRODUCT) returnează suma produselor matricelor selectate:
=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)
Funcțiile INDEX și MATCH
În articolul următor voi explica aceste funcții în detaliu, așa că deocamdată puteți doar să copiați această formulă:
=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))
Intervalele denumite și operatorul de intersecție
Dacă nu vă plac toate acele formule Excel complexe, s-ar putea să vă placă acest mod vizual și memorabil:
- Selectați tabelul, deschideți fila Formule (Formulele) și faceți clic Creați din selecție (Creează din selecție).
- Verificați casetele Rândul de sus (pe linia de mai sus) și Coloana stângă (în coloana din stânga). Microsoft Excel va atribui nume intervalelor din valorile din rândul de sus și coloana din stânga a foii de calcul. Acum puteți căuta folosind aceste nume direct, fără a crea formule.
- În orice celulă goală, scrieți =row_name column_name, de exemplu astfel:
=Lămâi Mar
… sau vice versa:
=Mar Lemons
Rețineți că numele rândurilor și coloanelor trebuie separate printr-un spațiu, care în acest caz funcționează ca operatorul de intersecție.
Când introduceți un nume, Microsoft Excel va afișa un tooltip cu o listă de nume care se potrivesc, la fel ca atunci când introduceți o formulă.
- Anunturi Intrați si verifica rezultatul
În general, oricare dintre metodele de mai sus alegeți, rezultatul unei căutări bidimensionale va fi același:
Folosind mai multe VLOOKUP într-o singură formulă
Se întâmplă ca tabelul principal și tabelul de căutare să nu aibă o singură coloană în comun, iar acest lucru vă împiedică să utilizați funcția obișnuită VPR. Totuși, există un alt tabel care nu conține informațiile care ne interesează, dar are o coloană comună cu tabelul principal și tabelul de căutare.
Să aruncăm o privire la următorul exemplu. Avem un tabel principal cu o coloană SKU (nou), unde doriți să adăugați o coloană cu prețurile corespunzătoare dintr-un alt tabel. În plus, avem 2 tabele de căutare. Primul (Tabelul de căutare 1) conține numere actualizate SKU (nou) și numele produselor, iar al doilea (Tabelul de căutare 2) – numele produselor și numerele vechi SKU (vechi).
Pentru a adăuga prețuri din al doilea tabel de căutare la tabelul principal, trebuie să efectuați o acțiune cunoscută sub numele de dublu VPR sau imbricate VPR.
- Scrieți o funcție VPR, care găsește numele produsului în tabel Tabelul de căutare 1folosind SKU, ca valoare dorită:
=VLOOKUP(A2,New_SKU,2,FALSE)=ВПР(A2;New_SKU;2;ЛОЖЬ)Aici New_SKU – interval denumit $A:$B in masa Tabelul de căutare 1, 2 – aceasta este coloana B, care conține numele mărfurilor (vezi imaginea de mai sus)
- Scrieți o formulă pentru a introduce prețuri dintr-un tabel Tabelul de căutare 2 bazate pe nume cunoscute de produse. Pentru a face acest lucru, lipiți formula pe care ați creat-o mai devreme ca valoare de căutare pentru noua funcție VPR:
=VLOOKUP(VLOOKUP(A2,New_SKU,2,FALSE),Price,3,FALSE)=ВПР(ВПР(A2;New_SKU;2;ЛОЖЬ);Price;3;ЛОЖЬ)Aici Preț – interval denumit $ A: $ C in masa Tabelul de căutare 2, 3 este coloana C care conține prețuri.
Figura de mai jos arată rezultatul returnat de formula creată de noi:
Înlocuirea dinamică a datelor din diferite tabele folosind VLOOKUP și INDIRECT
Mai întâi, să clarificăm ce înțelegem prin expresia „Înlocuirea dinamică a datelor din diferite tabele” pentru a ne asigura că ne înțelegem corect.
Există situații când există mai multe foi cu date de același format și este necesară extragerea informațiilor necesare dintr-o anumită foaie, în funcție de valoarea care este introdusă într-o celulă dată. Cred că este mai ușor să explic asta cu un exemplu.
Imaginați-vă că aveți rapoarte de vânzări pentru mai multe regiuni cu aceleași produse și în același format. Doriți să găsiți cifrele de vânzări pentru o anumită regiune:
Dacă aveți doar două astfel de rapoarte, atunci puteți utiliza o formulă rușinos de simplă cu funcții VPR и IF (IF) pentru a selecta raportul dorit de căutat:
=VLOOKUP($D$2,IF($D3="FL",FL_Sales,CA_Sales),2,FALSE)
=ВПР($D$2;ЕСЛИ($D3="FL";FL_Sales;CA_Sales);2;ЛОЖЬ)
Unde:
- $D$2 este o celulă care conține numele produsului. Rețineți că folosim referințe absolute aici pentru a evita modificarea valorii de căutare atunci când copiați formula în alte celule.
- $ D3 este o celulă cu numele regiunii. Folosim o referință de coloană absolută și o referință de rând relativă, deoarece intenționăm să copiem formula în alte celule din aceeași coloană.
- FL_Sales и CA_Vânzări – denumirile tabelelor (sau intervalelor denumite) care conțin rapoartele de vânzări corespunzătoare. Desigur, puteți utiliza numele obișnuite ale foilor și referințele la intervalul de celule, de exemplu „Foaie FL”!$A$3:$B$10, dar intervalele denumite sunt mult mai convenabile.
Cu toate acestea, atunci când există multe astfel de tabele, funcția IF nu este cea mai buna solutie. În schimb, puteți utiliza funcția INDIRECT (INDIRECT) pentru a reveni la intervalul de căutare dorit.
După cum probabil știți, funcția INDIRECT este folosit pentru a returna un link dat de un șir de text, care este exact ceea ce avem nevoie acum. Deci, înlocuiți cu îndrăzneală în formula de mai sus expresia cu funcția IF pentru a lega cu funcția INDIRECT. Iată o combinație VPR и INDIRECT funcționează excelent cu:
=VLOOKUP($D$2,INDIRECT($D3&"_Sales"),2,FALSE)
=ВПР($D$2;ДВССЫЛ($D3&"_Sales");2;ЛОЖЬ)
Unde:
- $D$2 – aceasta este o celulă cu numele produsului, este neschimbată din cauza legăturii absolute.
- $ D3 este celula care conține prima parte a numelui regiunii. În exemplul nostru, aceasta FL.
- _Vânzări – partea comună a numelui tuturor intervalelor sau tabelelor denumite. Când este combinat cu valoarea din celula D3, formează numele complet calificat al intervalului necesar. Mai jos sunt câteva detalii pentru cei care sunt noi în funcție INDIRECT.
Cum funcționează INDIRECT și VLOOKUP
Mai întâi, permiteți-mi să vă reamintesc sintaxa funcției INDIRECT (INDIRECT):
INDIRECT(ref_text,[a1])
ДВССЫЛ(ссылка_на_текст;[a1])
Primul argument poate fi o referință de celulă (stil A1 sau R1C1), un nume de interval sau un șir de text. Al doilea argument determină ce stil de legătură este conținut în primul argument:
- A1dacă argumentul este COD ADEVĂRAT (ADEVĂRAT) sau nespecificat;
- R1C1, dacă FAS E (FALS).
În cazul nostru, linkul are stilul A1, astfel încât să puteți omite cel de-al doilea argument și să vă concentrați pe primul.
Deci, să revenim la rapoartele noastre de vânzări. Dacă vă amintiți, atunci fiecare raport este un tabel separat situat pe o foaie separată. Pentru ca formula să funcționeze corect, trebuie să vă denumiți tabelele (sau intervalele), iar toate numele trebuie să aibă o parte comună. De exemplu, așa: CA_Vânzări, FL_Vânzări, TX_Vânzări si asa mai departe. După cum puteți vedea, „_Sales” este prezent în toate numele.
Funcţie INDIRECT conectează valoarea din coloana D și șirul de text „_Sales”, spunând astfel VPR în ce tabel să caute. Dacă celula D3 conține valoarea „FL”, formula va căuta în tabel FL_Vânzări, dacă „CA” – în tabel CA_Vânzări şi aşa mai departe.
Rezultatul funcțiilor VPR и INDIRECT vor fi urmatoarele:
Dacă datele se află în diferite cărți Excel, atunci trebuie să adăugați numele cărții înainte de intervalul numit, de exemplu:
=VLOOKUP($D$2,INDIRECT($D3&"Workbook1!_Sales"),2,FALSE)
=ВПР($D$2;ДВССЫЛ($D3&"Workbook1!_Sales");2;ЛОЖЬ)
Dacă funcţia INDIRECT se referă la un alt registru de lucru, acel registru de lucru trebuie să fie deschis. Dacă este închis, funcția va raporta o eroare. #REF! (#SSIL!).