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 detitan Widgets într-o bază de date eșantion. Urmați de-a lungul timpului pentru a afla cât de ușor este să creați propriile formule de căutare personalizate.
Pregătiți-vă cartea de lucru Excel cu date
Pentru a urma pașii din acest tutorial, va trebui să începeți prin introducerea datelor esantionului în următoarele celule, după cum se arată în imaginea de mai sus. Rândurile 3 și 4 sunt lăsate goale pentru a se potrivi cu formula de matrice creată în timpul acestui tutorial.
- Introduceți domeniul maxim de date în celule D1 la F2
- Introduceți a doua gamă în celule D5 la F11
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 08Creați o funcție INDEX în Excel
INDEX funcția este una dintre puținele din Excel care are mai multe formulare. Funcția are o funcție Array Form și a Formularul 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, și nu referința celulară la acest furnizor în baza noastră de date. Urmați acești pași pentru a vă crea funcția INDEX:
- Faceți clic pe celulă F3 pentru a deveni celula activă - aici vom intra în funcția imbricată.
- Faceți clic pe Formulele din meniul panglicii.
- Alege Căutare și referință din bara de panglică pentru a deschide meniul derulant al funcției.
- Click pe INDEX în listă pentru a afișa Selectați Argumente căsuță de dialog.
- Alege array, row_num, col_num opțiune din caseta de dialog.
- Clic O.K pentru a deschide caseta de dialog INDEX; acest lucru va deschide Formula Builder în Excel.
- În Formula constructor, faceți clic pe mulțime linia.
- Evidențiați celulele D6 la F11, făcând clic și tragând în foaia de lucru pentru a introduce intervalul în constructor.
În versiunile mai vechi ale programului Excel Formula constructor va fi înlocuit cu Argumente funcționale fereastră. Aplicați aceleași pași ca în acest tutorial în fereastră.
03 din 08Porniți funcția MATCH INCHAT
Atunci când cuibăriți o funcție în interiorul celeilalte, nu este posibilă deschiderea constructorului de formulare al celei de-a doua funcții sau a funcției imbricate o introduceți argumentele necesare. Funcția imbricată trebuie tastată ca una dintre cele sortate în prima funcție.
La introducerea manuală a funcțiilor, argumentele funcției sunt separate una de alta printr-o virgulă.
Primul pas în introducerea funcției MATCH imbricate este să introduceți Lookup_value argument. 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; Tse realizează prin concatenarea sau îmbinarea a două sau mai multe referințe de celule împreună folosind simbolul ampersand - &.
- În Formula constructor, faceți clic pe ROW_NUM linia.
- Introduceți numele funcției MECI urmată de o deschizătură rotundă deschisă.
- Faceți clic pe celulă D3 pentru a introduce respectiva referință de celule în caseta de dialog.
- Introduceți o ampersand după referința celulei D3 pentru a adăuga o a doua referință de celule.
- Faceți clic pe celulă E3 pentru a introduce această a doua referință de celule.
- Introduceți o virgulă după referința celulei E3 pentru a completa intrarea funcției MATCH Lookup_value argument.
În ultima etapă a tutorialului Lookup_values vor fi introduse în celulele D3 și E3 ale foii de lucru.
04 din 08Finalizați funcția Matched INCHET
Această etapă acoperă adăugarea funcției matrice_căutare argument pentru imbricate MECI funcţie. 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 un singur matrice pentru fiecare termen specificat, astfel că pentru a introduce mai multe matrice vom folosi din nou ampersand pentru a concatena matricele împreună.
Acești pași trebuie să fie introduse după virgula introdusă în pasul precedent pe ROW_NUM linie în INDEX funcţie.
- Faceți clic pe ROW_NUM linie după virgulă pentru a plasa punctul de inserție la sfârșitul intrării curente.
- Evidențiați celulele D6 la D11 în foaia de lucru pentru a intra în intervalul - aceasta este prima matrice pe care o căutați.
- Introduceți o ampersand după referințele de celule D6: D11 deoarece dorim ca funcția să caute două matrice.
- Evidențiați celulele E6 la E11 în foaia de lucru pentru a intra în intervalul - aceasta este a doua matrice funcția este de a căuta.
- Introduceți o virgulă după referința celulei E3 pentru a completa înregistrarea MECI funcţia lui matrice_căutare argument.
- Paraseste INDEX caseta de dialog pentru deschiderea următoarei etape a tutorialului.
Adăugați argumentul Tip de MATCH
Al treilea și ultimul argument al funcției MATCH este match_type argument; el spune Excel cum să se potrivească cu Lookup_value cu valori în Lookup_array - opțiunile disponibile sunt 1, 0 sau -1.
Acest argument este opțional. Dacă este omis funcția utilizează valoarea implicită de 1.
- În cazul în care Match_type = 1 sau este omisă: MATCH găsește cea mai mare valoare care este mai mică sau egală cu valoarea Lookup_value. Datele Lookup_array trebuie să fie sortate în ordine ascendentă.
- În cazul în care Match_type = 0: MATCH găsește prima valoare care este exact egală cu valoarea Lookup_value. Datele Lookup_array pot fi sortate în orice ordine.
- În cazul în care Match_type = -1: MATCH găsește cea mai mică valoare care este mai mare sau egală cu valoarea Lookup_value. Datele Lookup_array trebuie sortate în ordine descrescătoare.
Acești pași trebuie să fie introduse după virgula introdusă în pasul precedent pe ROW_NUM linie în INDEX funcţie.
- Urmând virgula din ROW_NUM line, tastați un zero - 0 - deoarece dorim ca funcția imbricată să returneze potrivirile exacte cu termenii pe care i-am introdus în celule D3 și E3.
- Introduceți o bandă rotundă de închidere - ) - pentru a finaliza MECI funcţie.
- Paraseste Formula constructor deschideți pentru următorul pas în tutorial.
Finalizarea funcției INDEX
Acum, că MECI se face trecerea la a treia linie a formulei formularelor și introducerea ultimului argument pentru INDEX funcţie. Acest al treilea și ultimul argument este Column_num argument care spune Excel numărul coloanei din interval D6 la F11; acesta este locul în care va găsi informațiile pe care vrem să le returnăm prin funcție. În acest caz, un furnizor pentru titan widgets.
- Faceți clic pe Column_num linia.
- Introduceți numărul trei - 3 - pe această linie, deoarece căutăm date în a treia coloană a intervalului D6 la F11.
- Din nou, părăsiți Formula constructor deschideți pentru următorul pas în tutorial.
Crearea formulei de matrice
Înainte de a închide Formula constructor, trebuie să transformăm funcția noastră imbricată într-o formulă de matrice; aceasta este ceea ce permite funcției noastre să caute mai mulți termeni în tabelul de date. În acest tutorial, urmărim să potriviți doi termeni: Widget-uri din coloanele 1 și 2 Titan din coloana 2.
Crearea unei formule de matrice în Excel se face prin apăsarea butonului CTRL, SCHIMB, și INTRODUCE tastele de pe tastatură în același timp. Odată apăsat, funcția va fi înconjurată de niște bretele curbate, indicând faptul că funcția este acum o matrice.
- Inchide Formula constructor dând clic pe Terminat buton.
- Apoi, selectați celula F4 și apăsați tasta introduce pentru a vedea formula.
- Pentru a converti formulele într-o matrice, apăsați simultan CTRL + SHIFT + ENTER pe tastatură.
- Dacă ați făcut corect a #N / A va apărea eroare în celula F3 - celula în care am intrat în funcție.
- #N / A apare eroarea în celulă F3 pentru că celulele D3 și E3 sunt goale. D3 și E3 sunt celulele în care am spus funcția de a găsi Lookup_values. Odată ce datele sunt adăugate la aceste două celule, eroarea va fi înlocuită cu informații din baza de date.
Adăugarea criteriilor de căutare
În final, vom adăuga termenii de căutare în foaia noastră de lucru. După cum sa menționat în pasul anterior, căutăm să corespundem termenilor Widget-uri din coloanele 1 și 2 Titan 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ă.
- Faceți clic pe celulă D3.
- Tip Widget-uri și apăsați tasta introduce cheie de pe tastatură.
- Faceți clic pe celulă E3.
- Tip Titan și apăsați tasta introduce cheie de pe tastatură.
- Numele furnizorului Widgets Inc. ar trebui să apară în celula F3 - locația funcției deoarece este singurul furnizor enumerat care vinde Titanium Widgets.
- Când faceți clic pe celula F3, funcția completă apare în bara de formula de deasupra foii de lucru.
{= INDEX (D6: F11, MATCH (D3 & E3, D6: D11 & E6: E11, 0), 3)}
În exemplul nostru, a existat un singur furnizor pentru widget-uri de titan. Dacă ar exista mai mult de un furnizor, furnizorul care este listat primul în baza de date este returnat de funcție.
