Cum se creează o formulă de căutare Excel cu mai multe criterii

2024 Autor: Abigail Brown | [email protected]. Modificat ultima dată: 2024-01-07 19:06

Ce trebuie să știți

• Mai întâi, creați o funcție INDEX, apoi porniți funcția MATCH imbricată introducând argumentul Lookup_value.
• În continuare, adăugați argumentul Lookup_array urmat de argumentul Match_type, apoi specificați intervalul coloanei.
• Apoi, transformați funcția imbricată într-o formulă matrice apăsând Ctrl+ Shift+ Enter. În cele din urmă, adăugați termenii de căutare în foaia de lucru.

Acest articol explică cum să creați o formulă de căutare care utilizează mai multe criterii în Excel pentru a găsi informații într-o bază de date sau într-un tabel de date folosind o formulă matrice. Formula matricei implică imbricarea funcției MATCH în cadrul funcției INDEX. Informațiile acoperă Excel pentru Microsoft 365, Excel 2019, Excel 2016, Excel 2013, Excel 2010 și Excel pentru Mac.

Urmăriți împreună cu tutorialul

Pentru a urma pașii din acest tutorial, introduceți datele eșantionului în următoarele celule, așa cum se arată în imaginea de mai jos. Rândurile 3 și 4 sunt lăsate necompletate pentru a se potrivi cu formula matrice creată în timpul acestui tutorial. (Rețineți că acest tutorial nu include formatarea văzută în imagine.)

• Introduceți intervalul superior de date în celulele D1 la F2.
• Introduceți al doilea interval în celulele D5 la F11.

Creați o funcție INDEX în Excel

Funcția INDEX este una dintre puținele funcții din Excel care are mai multe forme. Funcția are o formă de matrice și o formă de referință. Formularul Array returnează datele dintr-o bază de date sau dintr-un tabel de date. Formularul de referință oferă referința celulei sau locația datelor din tabel.

În acest tutorial, formularul de matrice este folosit pentru a găsi numele furnizorului pentru widget-uri de titan, mai degrabă decât referința celulei la acest furnizor în baza de date.

Urmați acești pași pentru a crea funcția INDEX:

1. Selectați celula F3 pentru a deveni celula activă. Această celulă este locul unde va fi introdusă funcția imbricată.

2. Accesați Formule.

   

3. Alegeți Căutare și referință pentru a deschide lista derulantă a funcțiilor.
4. Selectați INDEX pentru a deschide caseta de dialog Selectați argumente.
5. Alegeți matrice, row_num, column_num.
6. Selectați OK pentru a deschide caseta de dialog Argumente ale funcției. În Excel pentru Mac, se deschide Formula Builder.
7. Plasați cursorul în caseta de text Matrice.

8. Evidențiați celulele D6 până la F11 în foaia de lucru pentru a introduce intervalul în caseta de dialog.

   Lăsați deschisă caseta de dialog Argumente ale funcției. Formula nu este terminată. Veți completa formula în instrucțiunile de mai jos.

   

Porniți funcția MATCH imbricată

Când imbricați o funcție în alta, nu este posibil să deschideți generatorul de formule a celei de-a doua, sau imbricate, pentru a introduce argumentele necesare. Funcția imbricată trebuie introdusă ca unul dintre argumentele primei funcție.

La introducerea manuală a funcțiilor, argumentele funcției sunt separate unul de celăl alt printr-o virgulă.

Primul pas pentru a introduce funcția MATCH imbricată este introducerea argumentului Lookup_value. Lookup_value este locația sau celula de referință pentru termenul de căutare care urmează să fie asociat în baza de date.

Lockup_value acceptă un singur criteriu sau termen de căutare. Pentru a căuta mai multe criterii, extindeți Lookup_value prin concatenarea sau unirea a două sau mai multe referințe de celule utilizând simbolul ampersand (&).

1. În caseta de dialog Argumente ale funcției, plasați cursorul în caseta de text Row_num.
2. Introduceți MATCH(.
3. Selectați celula D3 pentru a introduce referința celulei respective în caseta de dialog.
4. Introduceți & (ampersand) după referința celulei D3 pentru a adăuga o a doua referință de celulă.
5. Selectați celula E3 pentru a introduce referința a doua celulă.

6. Introduceți , (o virgulă) după referința celulei E3 pentru a finaliza introducerea argumentului Lookup_value al funcției MATCH.

   

   În ultimul pas al tutorialului, Lookup_values vor fi introduse în celulele D3 și E3 ale foii de lucru.

Completați funcția MATCH imbricată

Acest pas acoperă adăugarea argumentului Lookup_array pentru funcția MATCH imbricată. Lookup_array este intervalul de celule pe care funcția MATCH le caută pentru a găsi argumentul Lookup_value adăugat în pasul anterior al tutorialului.

Deoarece au fost identificate două câmpuri de căutare în argumentul Lookup_array, același lucru trebuie făcut pentru Lookup_array. Funcția MATCH caută doar o matrice pentru fiecare termen specificat. Pentru a introduce mai multe matrice, utilizați ampersand pentru a concatena matricele împreună.

1. Plasați cursorul la sfârșitul datelor în caseta de text Row_num. Cursorul apare după virgulă la sfârșitul intrării curente.
2. Evidențiați celulele D6 până la D11 în foaia de lucru pentru a introduce intervalul. Acest interval este primul tablou pe care funcția îl caută.
3. Introduceți & (un ampersand) după referințele celulei D6:D11. Acest simbol face ca funcția să caute două matrice.
4. Evidențiați celulele E6 până la E11 în foaia de lucru pentru a introduce intervalul. Acest interval este a doua matrice pe care o caută funcția.

5. Introduceți , (o virgulă) după referința celulei E3 pentru a finaliza introducerea argumentului Lookup_array al funcției MATCH.

   

6. Lăsați caseta de dialog deschisă pentru următorul pas din tutorial.

Adăugați argumentul tip MATCH

Al treilea și ultimul argument al funcției MATCH este argumentul Match_type. Acest argument îi spune lui Excel cum să potrivească Lookup_value cu valorile din Lookup_array. Opțiunile disponibile sunt 1, 0 sau -1.

Acest argument este opțional. Dacă este omisă, funcția folosește valoarea implicită de 1.

• Dacă Match_type=1 sau este omis, MATCH găsește cea mai mare valoare care este mai mică sau egală cu Lookup_value. Datele Lookup_array trebuie sortate în ordine crescătoare.
• Dacă Match_type=0, MATCH găsește prima valoare care este egală cu Lookup_value. Datele Lookup_array pot fi sortate în orice ordine.
• Dacă Match_type=-1, MATCH găsește cea mai mică valoare care este mai mare sau egală cu Lookup_value. Datele Lookup_array trebuie sortate în ordine descrescătoare.

Introduceți acești pași după virgula introdusă la pasul anterior pe linia Row_num din funcția INDEX:

1. Introduceți 0 (un zero) după virgulă în caseta de text Row_num. Acest număr face ca funcția imbricată să returneze potriviri exacte la termenii introduși în celulele D3 și E3.

2. Introduceți ) (o paranteză rotundă de închidere) pentru a finaliza funcția MATCH.

   

3. Lăsați caseta de dialog deschisă pentru următorul pas din tutorial.

Terminați funcția INDEX

Funcția MATCH este finalizată. Este timpul să treceți la caseta de text Column_num din caseta de dialog și să introduceți ultimul argument pentru funcția INDEX. Acest argument îi spune lui Excel că numărul coloanei se află în intervalul D6 până la F11. Acest interval este locul în care găsește informațiile returnate de funcție. În acest caz, un furnizor de widget-uri din titan.

1. Plasați cursorul în caseta de text Column_num.

2. Introduceți 3 (numărul trei). Acest număr îi spune formulei să caute date în a treia coloană a intervalului de la D6 la F11.

   

3. Lăsați caseta de dialog deschisă pentru următorul pas din tutorial.

Creați formula matrice

Înainte de a închide caseta de dialog, transformați funcția imbricată într-o formulă matrice. Această matrice permite funcției să caute mai mulți termeni în tabelul de date. În acest tutorial, doi termeni sunt potriviți: Widgeturi din coloana 1 și Titanium din coloana 2.

Pentru a crea o formulă matrice în Excel, apăsați CTRL, SHIFT și ENTERtaste simultan. Odată apăsată, funcția este înconjurată de acolade, ceea ce indică faptul că funcția este acum o matrice.

1. Selectați OK pentru a închide caseta de dialog. În Excel pentru Mac, selectați Terminat.
2. Selectați celula F3 pentru a vedea formula, apoi plasați cursorul la sfârșitul formulei în bara de formule.
3. Pentru a converti formula într-o matrice, apăsați CTRL+ SHIFT+ ENTER.
4. În celula F3 apare o eroare N/A. Aceasta este celula în care a fost introdusă funcția.

5. Eroarea N/A apare în celula F3 deoarece celulele D3 și E3 sunt goale. D3 și E3 sunt celulele în care funcția caută pentru a găsi Lookup_value. După ce datele sunt adăugate în aceste două celule, eroarea este înlocuită cu informații din baza de date.

   

Adăugați criteriile de căutare

Ultimul pas este adăugarea termenilor de căutare în foaia de lucru. Acest pas corespunde termenilor Widgeturi din coloana 1 și Titanium din coloana 2.

Dacă formula găsește o potrivire pentru ambii termeni în coloanele corespunzătoare din baza de date, returnează valoarea din a treia coloană.

1. Selectați celula D3.
2. Introduceți Widget-uri.
3. Selectați celula E3.
4. Tastați Titan și apăsați Enter.
5. Numele furnizorului, Widgets Inc., apare în celula F3. Acesta este singurul furnizor listat care vinde widgeturi Titanium.

6. Selectați celula F3. Funcția apare în bara de formule de deasupra foii de lucru.

   {=INDEX(D6:F11, MATCH(D3&E3, D6:D11&E6:E11, 0), 3)}
   În acest exemplu, există un singur furnizor pentru widget-uri din titan. Dacă au existat mai mulți furnizori, furnizorul listat primul în baza de date este returnat de funcție.