Excel Lookup Formula cu multiple criterii

Utilizând o formulă de matrice în Excel, putem crea o formulă de căutare care utilizează mai multe criterii pentru a găsi informații într-o bază de date sau într-o tabelă de date.

Formula matricei implică inserarea funcției MATCH în interiorul funcției INDEX .

Acest tutorial include un exemplu pas cu pas de a crea o formulă de căutare care utilizează mai multe criterii pentru a găsi un furnizor de titan Widgeturi într-o bază de date de probă.

Urmând pașii din subiectele de instrucțiuni de mai jos vă uitați prin crearea și folosirea formulei văzute în imaginea de mai sus.

01 din 09

Introducerea datelor Tutorial

Primul pas în tutorial este să introduceți datele într-o foaie de lucru Excel.

Pentru a urma pașii din tutorial introduceți datele din imaginea de mai sus în următoarele celule .

• Introduceți domeniul maxim de date în celulele D1 până la F2
• Introduceți a doua gamă în celulele D5-F11

Rândurile 3 și 4 sunt lăsate goale pentru a se potrivi cu formula de matrice creată în timpul acestui tutorial.

Tutorialul nu include formatul vazut in imagine, dar acest lucru nu va afecta felul in care functioneaza formula de cautare.

Informații despre opțiunile de formatare similare cu cele observate mai sus sunt disponibile în acest Tutorial de formatare de bază pentru Excel.

02 din 09

Pornirea funcției INDEX

Funcția INDEX este una dintre puținele din Excel care are mai multe formulare. Funcția are o formă de matrice și un formular de referință .

Formularul Array returnează datele reale dintr-o bază de date sau o tabelă de date, în timp ce formularul de referință vă oferă referința celulară sau locația datelor din tabel.

În acest tutorial vom folosi formularul Array, deoarece dorim să cunoaștem numele furnizorului pentru widget-uri de titan, în loc de referința celulară la acest furnizor în baza noastră de date.

Fiecare formular are o listă diferită de argumente care trebuie selectate înainte de a începe funcția.

Tutoriale Pași

1. Faceți clic pe celula F3 pentru a deveni celula activă . Aici intrăm în funcția imbricată.
2. Faceți clic pe fila Formule din meniul de panglică .
3. Selectați Lookup and Reference din panglică pentru a deschide lista verticală de funcții.
4. Faceți clic pe INDEX din listă pentru a afișa caseta de dialog Selectare argumente .
5. Alegeți opțiunea array, row_num, col_num în caseta de dialog.
6. Faceți clic pe OK pentru a deschide caseta de dialog Funcție INDEX.

03 din 09

Introducerea argumentului array al funcției INDEX

Primul argument necesar este argumentul Array. Acest argument specifică intervalul de celule care trebuie căutate pentru datele dorite.

Pentru acest tutorial acest argument va fi baza noastră de date .

Tutoriale Pași

1. În caseta de dialog Funcție INDEX, faceți clic pe linia Array .
2. Evidențiați celulele D6 până la F11 din foaia de lucru pentru a introduce intervalul în caseta de dialog.

04 din 09

Pornirea funcției MATCH INSERT

Când cuiburiți o funcție în interiorul celeilalte, nu este posibilă deschiderea casetei de dialog a doua sau a unei funcții imbricate pentru a introduce argumentele necesare.

Funcția imbricată trebuie introdusă ca unul dintre argumentele primei funcții.

În acest tutorial, funcția MATCH imbricată și argumentele ei vor fi introduse în a doua linie a casetăi de dialog INDEX - linia Row_num .

Este important să rețineți că atunci când introduceți funcții manual, argumentele funcției sunt separate una de alta printr-o virgulă "," .

Introducerea argumentului Lookup_value al funcției MATCH

Primul pas în introducerea funcției MATCH imbricate este să introduceți argumentul Lookup_value .

Lookup_value va fi locația sau referința celulară pentru termenul de căutare pe care dorim să îl potriviți în baza de date.

În mod normal, valoarea Lookup_value acceptă numai un singur criteriu sau un termen de căutare. Pentru a căuta mai multe criterii, trebuie să extindem valoarea Lookup_value .

Acest lucru se face prin concatenarea sau îmbinarea a două sau mai multe referințe de celule împreună folosind simbolul " & ".

Tutoriale Pași

1. În caseta de dialog Funcție INDEX, faceți clic pe linia Row_num .
2. Introduceți potrivirea numelui funcției, urmată de o coloană rotundă deschisă " ( "
3. Faceți clic pe celula D3 pentru a introduce respectiva referință de celule în caseta de dialog.
4. Introduceți un amperaj " & " după referința celulei D3 pentru a adăuga o a doua referință de celule.
5. Faceți clic pe celula E3 pentru a introduce această a doua referință de celule în caseta de dialog.
6. Introduceți o virgulă "," după referința celulei E3 pentru a completa intrarea argumentului Lookup_value al funcției MATCH.
7. Lăsați caseta de dialog pentru funcțiile INDEX deschise pentru pasul următor din tutorial.

În ultima etapă a tutorialului, Lookup_values ​​vor fi introduse în celulele D3 și E3 ale foii de lucru.

05 din 09

Adăugarea Lookup_array pentru funcția MATCH

Acest pas cuprinde adăugarea argumentului Lookup_array pentru funcția MATCH imbricată.

Lookup_array este gama de celule pe care funcția MATCH va căuta pentru a găsi argumentul Lookup_value adăugat în etapa anterioară a tutorialului.

Deoarece am identificat două câmpuri de căutare în argumentul Lookup_array , trebuie să facem același lucru și pentru Lookup_array . Funcția MATCH caută numai o matrice pentru fiecare termen specificat.

Pentru a introduce mai multe matrice, folosim din nou ampersand " & " pentru a concatena matricele împreună.

Tutoriale Pași

Acești pași se introduc după virgul introdus în pasul anterior pe linia Row_num din caseta de dialog INDEX.

1. Faceți clic pe linia Row_num după virgulă pentru a plasa punctul de inserție la sfârșitul intrării curente.
2. Evidențiați celulele D6 până la D11 din foaia de lucru pentru a intra în interval. Aceasta este prima matrice a cărei funcție este de a căuta.
3. Tastați un ampersand " & " după referința celulei D6: D11 deoarece dorim ca funcția să caute două tablouri.
4. Evidențiați celulele E6 până la E11 din foaia de lucru pentru a intra în interval. Aceasta este a doua matrice funcția este de a căuta.
5. Introduceți o virgulă "," după referința celulei E3 pentru a completa intrarea argumentului Lookup_array al funcției MATCH.
6. Lăsați caseta de dialog pentru funcțiile INDEX deschise pentru pasul următor din tutorial.

06 din 09

Adăugarea tipului de potrivire și finalizarea funcției MATCH

Al treilea și ultimul argument al funcției MATCH este argumentul Match_type.

Acest argument spune Excel cum să se potrivească cu Lookup_value cu valori în Lookup_array. Alegerile sunt: ​​1, 0 sau -1.

Acest argument este opțional. Dacă este omis funcția utilizează 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 să fie sortate în ordine ascendentă.
• dacă match_type = 0: MATCH găsește prima valoare care este exact 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.

Tutoriale Pași

Acești pași se introduc după virgul introdus în pasul anterior pe linia Row_num din caseta de dialog INDEX.

1. În urma virgulei de pe linia Row_num , tastați un zero " 0 " deoarece dorim ca funcția imbricată să returneze potrivirile exacte cu termenii pe care îi introducem în celulele D3 și E3.
2. Introduceți un braț rotund de închidere " ) " pentru a finaliza funcția MATCH.
3. Lăsați caseta de dialog pentru funcțiile INDEX deschise pentru pasul următor din tutorial.

07 din 09

Înapoi la funcția INDEX

Acum că funcția MATCH este terminată, vom trece la a treia linie a casetei de dialog deschise și vom introduce ultimul argument pentru funcția INDEX.

Acest al treilea și ultimul argument este argumentul Column_num care spune Excel numărul coloanei în intervalul D6 până la F11 unde va găsi informațiile pe care le vrem returnate de funcție. În acest caz, un furnizor pentru widget-uri de titan .

Tutoriale Pași

1. Faceți clic pe linia Column_num din caseta de dialog.
2. Introduceți numărul trei " 3 " (fără citate) pe această linie, deoarece căutăm date în a treia coloană din intervalul D6 - F11.
3. Nu faceți clic pe OK sau închideți caseta de dialog Funcție INDEX. Acesta trebuie să rămână deschis pentru următorul pas în tutorial - crearea unei formule de matrice .

08 din 09

Crearea formulei de matrice

Înainte de a închide caseta de dialog, trebuie să transformăm funcția noastră imbricată într-o formulă de matrice .

O formulă de matrice este ceea ce îi permite să caute mai mulți termeni în tabelul de date. În acest tutorial căutăm să potriviți doi termeni: Widgeturi din coloana 1 și titan din coloana 2.

Crearea unei formule de matrice în Excel se face prin apăsarea simultană a tastelor CTRL , SHIFT și ENTER de pe tastatură.

Efectul de a apăsa simultan aceste chei este acela de a înconjura funcția cu brațele curbate: {} indicând că este acum o formulă de matrice.

Tutoriale Pași

1. Cu caseta de dialog completă deschisă încă din pasul anterior al acestui tutorial, țineți apăsată tasta CTRL și SHIFT de pe tastatură, apoi apăsați și eliberați tasta ENTER .
2. Dacă este făcută corect, caseta de dialog se va închide și în celula F3 va apărea o eroare # N / A - celula în care am intrat în funcție.
3. Eroarea # N / A apare în celula F3, deoarece celulele D3 și E3 sunt goale. D3 și E3 sunt celulele în care am spus funcția de a găsi valoarea Lookup_values ​​în pasul 5 al tutorialului. Odată ce datele sunt adăugate la aceste două celule, eroarea va fi înlocuită cu informații din baza de date .

09 din 09

Adăugarea criteriilor de căutare

Ultimul pas în tutorial este să adăugați termenii de căutare în foaia noastră de lucru.

După cum sa menționat în pasul anterior, căutăm să se potrivească termenii Widgets din coloana 1 și Titanium din coloana 2.

Dacă, și numai dacă, formula noastră găsește o potrivire pentru ambii termeni în coloanele corespunzătoare din baza de date, va returna valoarea din a treia coloană.

Tutoriale Pași

1. Faceți clic pe celula D3.
2. Introduceți Widget-uri și apăsați tasta Enter de pe tastatură.
3. Faceți clic pe celula E3.
4. Introduceți Titan și apăsați tasta Enter de pe tastatură.
5. Numele furnizorului Widgets Inc. ar trebui să apară în celula F3 - locația funcției deoarece este singurul furnizor listat care vinde Titanium Widgets.
6. Când faceți clic pe celula F3 funcția completă
   {= INDEX (D6: F11, MATCH (D3 & E3, D6: D11 & E6: E11, 0), 3)}
   apare în bara de formula de deasupra foii de lucru .

Notă: în exemplul nostru a existat un singur furnizor pentru widget-urile de titan. Dacă ar exista mai mult de un furnizor, furnizorul care este listat primul în baza de date este returnat de funcție.