Cum să căutați mai multe valori în Excel: pas cu pas

Când lucrați cu seturi mari de date în Microsoft Excel, poate fi dificil să găsiți mai multe valori care corespund mai multor condiții. Unele funcții încorporate, de exemplu CĂUTARE V, au fost concepute inițial pentru a funcționa cu valori individuale.

Mai multe funcții Excel pot fi combinate pentru a căuta mai multe valori. Acestea includ funcțiile CĂUTARE V, INDEX, MATCH și IF. Versiunile actuale de Excel pot folosi matrice dinamice, în timp ce versiunile mai vechi folosesc formule matrice.

Acest articol vă va arăta exact cum să utilizați aceste funcții în formule care găsesc mai multe valori în datele dvs.

Să mergem!

Cuprins

• Cum să utilizați VLOOKUP cu mai multe valori
  • Ce sunt formulele de matrice?
  • Exemplu de CĂUTARE V
  • Cele mai recente versiuni de Excel
  • Versiuni mai vechi
  • Potrivire exactă vs. Potrivire aproximativă
  • Mai multe despre VLOOKUP
• Cum să utilizați INDEX și MATCH pentru a căuta mai multe valori
  • Cum să utilizați INDEX și MATCH împreună în Excel 365
  • Lucrul cu versiuni mai vechi de Excel
• Cum să utilizați funcția IF pentru a găsi mai multe valori
  • Versiunile actuale de Excel
  • Versiuni mai vechi de Excel
• Cum să utilizați SUMPRODUCT pentru mai multe criterii
• 3 erori frecvente cu funcțiile de căutare
  • 1. #N/A Erori
  • 2. #REF! Erori
  • 3. Erori circulare
• Cum să utilizați IFERROR cu funcțiile de căutare
• 7 Tehnici avansate de căutare
  • 1. Căutarea poziţiei relative
  • 2. Funcția MIC
  • 3. Funcția RÂND
  • 4. Funcția FILTER
  • 5. Coloane de ajutor
  • 6. Matrice dinamice
  • 7. Power Query
• Gânduri finale

Cum să utilizați VLOOKUP cu mai multe valori

Funcția VLOOKUP este adesea folosită pentru a găsi valori individuale într-un interval de date. Cu toate acestea, puteți căuta și mai multe potriviri în Excel cu această formulă de căutare.

În mod implicit, va returna doar prima valoare potrivită pe care o găsește. Cu toate acestea, puteți modifica funcția pentru a returna mai multe valori utilizând o formulă matrice.

Ce sunt formulele de matrice?

O formulă matrice este o formulă care poate efectua calcule pe matrice de date. Se numește formulă matrice, deoarece poate returna o matrice de rezultate, mai degrabă decât o singură valoare.

Există mai mulți pași simpli atunci când creați o formulă matrice:

1. Selectați un interval de celule pentru a căuta.

2. Introduceți formula în bara de formule.

3. Apăsați Ctrl + Shift + Enter pentru a finaliza.

Sintaxa unei formule matrice este similară cu cea a unei formule obișnuite, dar include acolade {} în jurul formulei. Acoladele indică faptul că formula este o formulă matrice și că va returna o matrice de valori.

Exemplele din acest articol vă vor arăta cum să utilizați corect formulele matrice.

Exemplu de CĂUTARE V

Exemplul nostru are cinci elemente în coloana A a unei foi de lucru:

• Măr

• Banană

• Pâine

• Morcov

• Cireașă

Sarcina este de a verifica dacă trei fructe specifice sunt în această listă: măr, banană și cireș.

Cele mai recente versiuni de Excel

Sintaxa VLOOKUP depinde de versiunea de Excel pe care o utilizați.

Cele mai recente versiuni ( Excel 365 sau Excel 2021 ) acceptă matrice dinamice. Această caracteristică permite formulelor să returneze mai multe rezultate care se „vărsează” în celulele adiacente.

Aceasta este sintaxa (semnul egal începe formula):

=CĂUTAREV(valoare_căutare, matrice_tabelă, număr_index_col, [căutare_gamă])

• lookup_value: valoarea pe care doriți să o căutați.

• table_array: întregul tabel de date pe care doriți să îl căutați.

• col_index_num: numărul coloanei tabelului din table_array care conține datele pe care doriți să le returnați.

• range_lookup: opțional. Specifică dacă doriți o potrivire exactă sau o potrivire aproximativă.

Exemplul nostru specific folosește această formulă:

=CĂUTARE V({„măr”,”banană”,”cireș”},A1:A5,1,FALSE)

Când formula este introdusă în celula B1, rezultatele se revarsă în celulele C1 și D1. Această imagine arată exemplul în acțiune:

Versiuni mai vechi

Dacă utilizați o versiune mai veche de Excel care nu acceptă matrice dinamice (de exemplu, Excel 2019 sau o versiune anterioară), va trebui să utilizați o abordare ușor diferită cu o formulă de matrice.

Urmați acești pași:

1. Faceți clic pe celula în care doriți să afișați rezultatele pentru primul articol (de exemplu, coloana B).

2. Introdu următoarea formulă fără a apăsa încă Enter: =VLOOKUP({„apple”,”banana”,”cherry”}, A1:A5, 1, FALSE)

3. Apăsați Ctrl + Shift + Enter pentru a transforma aceasta într-o formulă matrice.

4. Copiați celula B1 și inserați-o în celula de mai jos (sau utilizați mânerele de umplere).

Când utilizați Ctrl + Shift + Enter, Excel adaugă acolade în jurul formulei. Aceasta indică faptul că este o formulă matrice.

Potrivire exactă vs. Potrivire aproximativă

În mod implicit, funcția CĂUTARE VU folosește o potrivire aproximativă. Aceasta înseamnă că va returna cea mai apropiată potrivire pe care o poate găsi, chiar dacă valorile celulelor nu sunt o potrivire exactă.

Dacă doriți să efectuați o potrivire exactă, puteți seta argumentul range_lookup la FALSE.

Rețineți că potrivirile aproximative funcționează cel mai bine cu valori numerice ordonate. De obicei, nu este adecvat când valoarea celulei este text.

Mai multe despre VLOOKUP

Dacă doriți să aflați mai multe despre această funcție versatilă, consultați aceste articole:

• cum se compară două coloane în Excel cu VLOOKUP

• cum să găsiți discrepanțe cu VLOOKUP

Acum că sunteți setat cu funcția CĂUTARE V, să aruncăm o privire la alte două funcții care pot face ceea ce face într-un mod diferit: INDEX și MATCH.

Cum să utilizați INDEX și MATCH pentru a căuta mai multe valori

Puteți combina funcțiile INDEX și MATCH împreună pentru a găsi mai multe valori în mai multe rânduri.

Funcția INDEX din Excel returnează o valoare sau o referință la o celulă dintr-un interval specificat.

=INDEX(matrice, row_num, [column_num])

• matrice: intervalul de celule care trebuie căutat pentru valoare.

• row_num: numărul rândului din cadrul matricei din care se returnează o valoare.

• column_num: (Opțional) numărul coloanei din cadrul matricei din care se returnează o valoare. Dacă este omisă, funcția returnează întregul rând.

Funcția MATCH din Excel returnează poziția unei valori într-un interval specificat.

=MATCH(valoare_căutare, matrice_căutare, [tip_potrivire])

• lookup_value: valoarea care trebuie căutată în secțiunea lookup_array.

• lookup_array: intervalul de celule care trebuie căutat pentru căutarea_valoare.

• match_type: (Opțional) Tipul de potrivire care trebuie efectuată. Dacă este omisă, funcția realizează o potrivire exactă.

Cum să utilizați INDEX și MATCH împreună în Excel 365

Pentru a utiliza INDEX și MATCH împreună pentru a căuta mai multe valori în Excel, trebuie să utilizați o formulă matrice.

Lucrând cu datele eșantionului anterioare, aceasta este formula în Excel 365:

=INDEX(A1:A5, MATCH({„măr”,”banană”,”cireș”}, A1:A5, 0))

Exemplul de mai sus se descompune astfel:

• INDEX: returnează valoarea unei celule dintr-un interval specificat pe baza unui număr dat de rând și coloană. În acest caz, va returna valoarea din intervalul A1:A5.

• A1:A5: Acesta este intervalul definit de tabel în care căutați valoarea și din care va fi returnat rezultatul.

• MATCH: aceasta caută un articol specificat într-un interval de celule și returnează poziția relativă a acelui articol în interval.

• {“apple”,”banana”,”cherry”}: aceasta este constanta matrice care conține valorile pe care doriți să le căutați.

• A1:A5: acesta este intervalul în care MATCH va căuta valorile din constanta matricei.

• 0: acesta este tipul de potrivire pentru funcția MATCH. În acest caz, este 0, ceea ce înseamnă că cauți o potrivire exactă în loc de o potrivire apropiată.

 Această imagine arată formula în acțiune:

Lucrul cu versiuni mai vechi de Excel

Dacă utilizați un fișier Excel mai vechi care nu acceptă matrice dinamice (de exemplu, Excel 2019 sau o versiune anterioară), va trebui să utilizați o abordare diferită.

Deoarece versiunile mai vechi nu acceptă formulele „vărsate” în celulele adiacente, va trebui să împărțiți utilizarea în trei formule separate.

Urmați acești pași:

1. Faceți clic pe celula în care doriți rezultatul pentru primul articol (de exemplu, celula B1)

2. Introduceți formula de mai jos:

3. =INDEX(A1:A5, MATCH(„măr”, A1:A5, 0))

4. Apăsați Enter pentru a executa formula.

5. Introdu această formulă în celula B2: =INDEX(A1:A5, MATCH(„banană”, A1:A5, 0))

6. Introdu această formulă în celula B3: =INDEX(A1:A5, MATCH(„cireș”, A1:A5, 0))

Această imagine arată referința de celulă B3:

Funcțiile INDEX și MATCH nu sunt singurele care vă pot permite să găsiți mai multe valori. În secțiunea următoare, ne uităm la modul în care puteți utiliza funcția IF ca alternativă.

Cum să utilizați funcția IF pentru a găsi mai multe valori

O altă modalitate de a căuta mai multe valori de celule pe baza anumitor criterii este utilizarea funcției IF cu alte funcții.

Funcția IF vă permite să testați mai multe condiții și să returnați rezultate diferite în funcție de rezultatul acestor teste.

De exemplu, să presupunem că aveți un tabel cu date despre vânzări cu coloane pentru Produs și Vânzări. Doriți să căutați și să totalizați valoarea vânzărilor pentru două dintre cele trei produse.

Versiunile actuale de Excel

Pentru a găsi suma coloanei Vânzări în care produsul este fie „Mer” fie „Banana” utilizând funcția IF, puteți utiliza o formulă matrice cu funcțiile IF, SUM și SAU.

Presupunând că datele dvs. încep în celula A1, utilizați următoarea formulă:

=SUM(DACĂ((A2:A4=”Mere”)+(A2:A4=”Banană”), B2:B4, 0))

Secțiunea (A2:A4=”Apple”)+(A2:A4=”Banana”) creează o matrice care are valoarea 1 dacă celula din intervalul A2:A4 conține „Apple” sau „Banana”, iar 0 în caz contrar .

Instrucțiunea IF verifică fiecare element al argumentului matricei. Dacă valoarea este 1 (adică produsul este fie „Mer” fie „Banana”), aceasta ia valoarea corespunzătoare în coloana Vânzări (interval B2:B4); altfel, este nevoie de 0.

Funcția SUM adună valorile din funcția IF, însumând efectiv valorile vânzărilor atât pentru „Mere” cât și pentru „Banana”.

Această imagine arată formula în acțiune în intervalul de căutare:

Versiuni mai vechi de Excel

În Excel 2019 sau o versiune anterioară, trebuie să utilizați o formulă matrice. Urmați acești pași:

1. Introduceți formula, dar nu apăsați Enter.

2. Apăsați Ctrl + Shift + Enter pentru a face din aceasta o formulă matrice.

Excel va adăuga paranteze {} în jurul formulei, indicând că este o formulă matrice.

În continuare, ne uităm la modul în care puteți utiliza SUMPRODUCT pentru a căuta mai multe valori pe baza criteriilor dvs. Să mergem!

Cum să utilizați SUMPRODUCT pentru mai multe criterii

Funcția SUMPRODUCT vă permite, de asemenea, să căutați mai multe valori pe baza mai multor criterii.

Deoarece nu necesită utilizarea unei formule matrice, sintaxa este aceeași indiferent de versiunea de Excel.

Folosind aceleași date ca în exemplul anterior, formula arată astfel:

=SUMPRODUS((A2:A4=”Mere”)+(A2:A4=”Banana”), B2:B4)

Secțiunea (A2:A4=”Apple”)+(A2:A4=”Banana”) creează o matrice care are valoarea 1 dacă celula din intervalul A2:A4 conține „Apple” sau „Banana”, iar 0 în caz contrar .

Funcția SUMPRODUS înmulțește elementele matricei cu elementele corespunzătoare din coloana Vânzări (interval B2:B4). Apoi adună valorile rezultate, însumând efectiv valorile vânzărilor atât pentru „Mere” cât și pentru „Banana”.

Formula de mai jos o arată în acțiune:

Funcțiile Excel sunt uimitoare atunci când funcționează conform așteptărilor, dar uneori s-ar putea să întâlniți erori. În secțiunea următoare, vom acoperi câteva dintre erorile comune și cum le puteți face față.

3 erori frecvente cu funcțiile de căutare

Funcțiile de căutare pot returna uneori erori care pot fi frustrante și consumatoare de timp pentru depanare. Cele mai frecvente trei erori pe care le veți întâlni sunt:

1. #N/A Erori

2. #REF! Erori

3. Erori circulare

1. #N/A Erori

Eroarea #N/A apare atunci când valoarea de căutare nu poate fi găsită în matricea de căutare.

Există mai multe motive pentru care poate apărea această eroare, inclusiv:

• valoarea de căutare este scrisă greșit sau incorectă.

• matricea de căutare nu este sortată în ordine crescătoare.

• valoarea de căutare nu se află în setul de date.

Când valoarea de căutare nu este în setul de date, aceasta este o informație utilă. Cu toate acestea, utilizatorii Excel fără experiență pot crede că #N/A înseamnă că ceva nu a mers prost cu formula. Următoarea secțiune arată cum să faceți acest lucru mai ușor de utilizat.

2. #REF! Erori

#REF! eroarea apare atunci când matricea de căutare sau matricea de returnare este ștearsă sau mutată.

Această eroare poate fi remediată prin actualizarea referințelor de celule în funcția de căutare.

3. Erori circulare

Pe măsură ce combinați funcții în formule complexe, Excel vă poate spune că aveți o referință circulară.

Acestea le veți găsi mai ușor de investigat utilizând ghidul nostru pentru a găsi referințe circulare în Excel .

Cum să utilizați IFERROR cu funcțiile de căutare

Funcția IFERROR este un instrument util pentru gestionarea erorilor în funcțiile de căutare. Vă permite să specificați o valoare sau o formulă de returnat dacă funcția de căutare returnează o eroare.

Sintaxa pentru funcția IFERROR este următoarea:

=IFEROARE(valoare, valoare_dacă_eroare)

· valoare: valoarea sau formula pe care doriți să o evaluați.

· Value_if_error: valoarea sau formula de returnat dacă primul argument returnează o eroare.

De exemplu, să presupunem că aveți o funcție VLOOKUP care caută mai multe valori într-un tabel. În imaginea de mai jos, una dintre valori nu există în intervalul de date căutat.

După cum puteți vedea, este afișată eroarea #N/A, care poate fi confuză pentru utilizatorii Excel fără experiență.

În schimb, puteți utiliza IFERROR pentru a afișa o celulă goală sau un mesaj care spune „Nu a fost găsit” cu această sintaxă:

=IFEROARE(CĂUTAREV(valoare_căutare, matrice_tabel, index_coloană, FALSE), „Nu a fost găsit”)

În acest exemplu, dacă funcția VLOOKUP returnează o eroare, funcția IFERROR va returna mesajul „Nu a fost găsit”.

Această imagine arată formula în acțiune. Coloana B are valoarea lipsă, în timp ce coloana C și coloana D au găsit potriviri.

Am acoperit o mulțime de teren până acum și sunteți în sfârșit gata să învățați tehnici mai avansate pentru căutări, care este subiectul secțiunii următoare.

7 Tehnici avansate de căutare

Căutarea mai multor valori în Excel poate fi o sarcină dificilă, mai ales atunci când aveți de-a face cu seturi de date mari. Este posibil să întâmpinați probleme de performanță cu procesarea lentă.

Există șapte tehnici avansate de căutare pe care le puteți utiliza pentru a face procesul mai ușor și mai eficient.

• Căutarea poziţiei relative

• Funcție MICĂ

• Funcția ROW

• Funcția FILTER

• Coloane de ajutor

• Matrice dinamice

• Power Query

1. Căutarea poziţiei relative

Una dintre cele mai simple moduri de a căuta mai multe valori în Excel este prin utilizarea căutării poziției relative. Aceasta implică specificarea decalajelor de rând și coloane dintr-o celulă dată pentru a localiza valoarea dorită.

De exemplu, dacă aveți un tabel de date și doriți să căutați o valoare cu două rânduri în jos și trei coloane în dreapta unei celule date, puteți utiliza următoarea formulă:

=OFFSET(celula, 2, 3)

2. Funcția MIC

O altă tehnică utilă pentru căutarea mai multor valori este utilizarea funcției SMALL. Această funcție returnează a n-a cea mai mică valoare dintr-un interval de celule.

Combinând această funcție cu alte funcții de căutare precum INDEX și MATCH, puteți căuta mai multe valori pe baza unor criterii specifice.

De exemplu, următoarea formulă caută a doua cea mai mică valoare dintr-un interval de celule care îndeplinesc o anumită condiție:

=INDEX(date, MATCH(SMALL(DAC(criterii, interval), 2), interval, 0))

3. Funcția RÂND

Funcția ROW poate fi utilizată și pentru căutări de valori multiple în Excel. Această funcție returnează numărul de rând al unei celule date, care poate fi folosit pentru a face referire la celule dintr-un tabel de date.

De exemplu, următoarea formulă caută o valoare într-un tabel pe baza unui identificator unic:

=INDEX(date, MATCH(identificator_unic, date[identificator_unic], 0), număr_index_coloană)

4. Funcția FILTER

Funcția FILTER nu este disponibilă în versiunile mai vechi de Excel.

În Excel 365, îl puteți folosi pentru a filtra o serie de celule pe baza anumitor criterii și pentru a returna numai valorile care îndeplinesc acele criterii. Aceasta este sintaxa și trei argumente:

=FILTER(matrice, include, [dacă_gol])

• matrice: datele specifice pe care doriți să le filtrați.

• include: argumentul include este criteriile sau condițiile pe care doriți să le aplicați matricei.

• [if_empty] (opțional): Valoarea de returnat dacă niciun rând sau coloană nu îndeplinește criteriile specificate în argumentul include.

De exemplu, următoarea formulă funcționează pe datele exemplu pentru a găsi potriviri pentru două dintre cele trei elemente din prima coloană și pentru a însuma valorile corespunzătoare din a doua coloană.

=SUMA(FILTRUL(B2:B4, (A2:A4=”Mere”)+(A2:A4=”Banana”)))

Această imagine arată câte rânduri sunt potrivite și suma diferitelor valori:

5. Coloane de ajutor

Puteți utiliza o coloană de ajutor pentru a uni mai multe câmpuri împreună în cadrul unei funcții precum CĂUTARE V.

Să presupunem că lucrați cu numele și prenumele într-o foaie separată. Le puteți concatena într-o coloană de ajutor la care se face referire în formula finală.

6. Matrice dinamice

După cum ați învățat în exemplele anterioare, utilizatorii Microsoft 365 pot profita de matricele dinamice pentru căutarea mai multor valori în Excel.

Matricele dinamice vă permit să returnați mai multe valori dintr-o singură formulă, facilitând căutarea unor cantități mari de date.

7. Power Query

Power Query este un instrument puternic în Excel care poate fi folosit pentru a returna valori pe baza mai multor criterii.

De exemplu, acest videoclip găsește date dezordonate într-o foaie de calcul și le curăță.

Gânduri finale

Asta încheie scufundarea noastră profundă în arta de a căuta mai multe valori în Excel! Odată ce ați stăpânit formulele de CĂUTARE V, INDEX, POTRIVIRE și matrice, vă veți trezi cu ușurință prin seturi complexe de date ca un cuțit fierbinte prin unt.

Cheia este să înțelegeți sintaxa și logica din spatele fiecărei formule. Rețineți că aceste formule pot fi complexe, așa că este important să vă faceți timp și să testați formulele cu atenție înainte de a vă baza pe ele pentru analiza importante a datelor.

Excel este un instrument puternic, dar este la fel de bun ca utilizatorul. Așa că continuă să perfecționezi aceste abilități, continuă să experimentezi și vei fi în curând stăpânul căutărilor cu mai multe valori. Până data viitoare, continuă să scrii acele numere și fă ca Excel să facă munca grea pentru tine!