În acest articol, vom învăța cum să căutați a n-a potrivire în tabel utilizând funcția INDEX & MATCH.
Scenariu:
De exemplu, trebuie să găsim din tabel cea de-a n-a potrivire numerică și rezultatele corespunzătoare. Avem nevoie de o formulă generală care să ajute la găsirea celei de-a n-a potriviri pentru a obține valoarea cerută.
Cum se rezolvă problema?
Pentru ca formula să înțeleagă mai întâi, trebuie să revizuim puțin despre următoarele funcții
- Funcția INDEX
- Funcția MATCH
Acum vom face o formulă folosind funcțiile de mai sus. Funcția MATCH va returna indexul celui mai mic meci din interval. Funcția INDEX ia indexul rândului ca argument și returnează rezultatele necesare corespunzătoare. Această funcție găsește
Formula generică:
= INDEX (date, MATCH (SMALL (interval, n), interval, match_type), col_num)
date : matrice de valori din tabel fără anteturi
gamă : lookup_array pentru cea mai mică potrivire
n : numărul, al n-lea meci
match_type : 1 (exact sau următorul cel mai mic) sau 0 (potrivire exactă) sau -1 (exact sau următorul ca mărime)
col_num: numărul coloanei, valoarea necesară pentru a extrage din coloana tabelului.
Exemplu:
Afirmațiile de mai sus pot fi confuze pentru a înțelege. Deci, să înțelegem acest lucru folosind formula dintr-un exemplu
Aici avem un tabel cu detalii despre continentul mondial. Trebuie să găsim codul de țară din tabelul dat din valoarea de căutare ca nume de țară.
Gama denumită utilizată pentru tabelul (E5: I10) și gama (I5: I10).
Aici am folosit intervalul numit în schimb pentru matricea de referință a celulei, deoarece este ușor de înțeles. Trebuie să căutăm toate detaliile în cazul în care valoarea prețului este minimă sau cea mai mică din interval.
Folosiți formula de mai sus pentru a obține primul detaliu care este capital
Folosiți formula din celula G6:
= INDEX (tabel, MATCH ( MIC (interval, 1) ,interval, 0 ) , 4 )
Intervalele denumite
tabel (E5: I10)
interval (I5: I10)
Explicaţie:
- Funcția SMALL găsește prima potrivire cea mai mică din interval și returnează valoarea la funcția MATCH.
- Funcția MATCH se potrivește exact cu valoarea minimă potrivită în intervalul de prețuri și returnează indexul rândului său la funcția INDEX.
- Funcția INDEX găsește valoarea de capital având index ROW și a 4-a coloană în tabelul denumit interval.
Formula returnează toate rezultatele pentru prima valoare cea mai mică. Acum copiați formula pentru a obține al doilea cel mai mic sau al treilea cel mai mic, trebuie doar să schimbați argumentul n sub funcția SMALL pentru a obține rezultatele diferite.
Acum, doar schimbați valoarea n și obțineți rezultatele așa cum se arată mai jos.
După cum puteți vedea din instantaneul de mai sus, am obținut toate detaliile care se potrivesc cu a n-a valoare în tabel. Extrageți detalii din tabel folosind formula menționată mai sus.
Exemplu:
Puteți utiliza, de asemenea, funcția LARGE pentru a obține cea de-a n-a potrivire din partea de sus sau a n-a cea mai mare potrivire din interval și returnează rezultatele corespunzătoare.
Formula generică:
= INDEX (date, MATCH (LARGE (interval, n), interval, match_type), col_num)
date : matrice de valori din tabel fără anteturi
gamă : lookup_array pentru cea mai mare potrivire
n : numărul, al n-lea meci
match_type : 1 (exact sau următorul cel mai mic) sau 0 (potrivire exactă) sau -1 (exact sau următorul ca mărime)
col_num: numărul coloanei, valoarea necesară pentru a extrage din coloana tabelului.
Exemplu:
Afirmațiile de mai sus pot fi confuze pentru a înțelege. Deci, să înțelegem acest lucru folosind formula dintr-un exemplu
Aici avem un tabel cu detalii despre continentul mondial. Trebuie să găsim codul de țară din tabelul dat din valoarea de căutare ca nume de țară.
Gama denumită utilizată pentru tabelul (E5: I10) și gama (I5: I10).
Aici am folosit intervalul numit în schimb pentru matricea de referință a celulei, deoarece este ușor de înțeles. Trebuie să căutăm toate detaliile în cazul în care valoarea prețului este maximă sau cea mai mare din gamă.
Utilizați formula de mai sus pentru a obține primul detaliu care este capital
Folosiți formula din celula G6:
= INDEX (tabel, MATCH ( MARE (interval, 1) ,interval, 0 ) , 4 )
Intervalele denumite
tabel (E5: I10)
interval (I5: I10)
Explicaţie:
- Funcția LARGE găsește prima potrivire cea mai mare din interval și returnează valoarea la funcția MATCH.
- Funcția MATCH se potrivește cu cea de-a n-a potrivire exactă din gama Price și returnează indexul rândului său la funcția INDEX.
- Funcția INDEX găsește valoarea de capital având index ROW și a 4-a coloană în tabelul denumit interval.
Formula returnează toate rezultatele pentru prima valoare cea mai mică. Acum copiați formula pentru a obține al doilea cel mai mic sau al treilea cel mai mic, trebuie doar să schimbați argumentul n sub funcția SMALL pentru a obține rezultatele diferite.
Acum, doar schimbați valoarea n și obțineți rezultatele așa cum se arată mai jos.
După cum puteți vedea din instantaneul de mai sus, am obținut toate detaliile care se potrivesc cu a n-a valoare în tabel. Extrageți detalii din tabel folosind formula menționată mai sus.
Iată câteva note observaționale în timp ce utilizați formula de mai sus.
Note:
- Valoarea lui n nu poate fi mai mică de 1 sau mai mare decât lungimea intervalului
- Funcția returnează eroarea #NA dacă argumentul matricei de căutare la funcția MATCH nu are aceeași lungime a matricei de tabel.
- Formula returnează o eroare dacă lookup_value nu se potrivește cu valoarea din tabelul lookup_array.
- Funcția se potrivește cu valoarea exactă, deoarece argumentul tipului de potrivire cu funcția MATCH este 0.
- Valorile de căutare pot fi date ca referință de celulă sau direct folosind simbolul ghilimelei (") în formulă ca argumente.
Sper că ați înțeles cum să căutați al n-lea meci în tabel utilizând funcția INDEX & MATCH. Explorați aici mai multe articole despre valoarea de căutare Excel. Vă rugăm să nu ezitați să vă expuneți întrebările de mai jos în caseta de comentarii. Cu siguranță vă vom ajuta.
Dacă ți-au plăcut blogurile noastre, împărtășește-le prietenilor tăi de pe Facebook. Și, de asemenea, ne puteți urmări pe Twitter și Facebook. Ne-ar plăcea să aflăm de la dvs., să ne anunțați cum putem îmbunătăți, completa sau inova munca noastră și să o îmbunătățim. Scrieți-ne pe site-ul de e-mail
Utilizați INDEX și MATCH pentru a căuta valoarea : Funcția INDEX & MATCH pentru a căuta valoarea după cum este necesar.
Gama SUMĂ cu INDEX în Excel : Utilizați funcția INDEX pentru a găsi SUMA valorilor, după cum este necesar.
Cum se utilizează funcția INDEX în Excel : Găsiți INDEX-ul matricei folosind funcția INDEX explicată cu un exemplu.
Cum se utilizează funcția MATCH în Excel : Găsiți MATCH-ul în matrice folosind valoarea INDEX din funcția MATCH explicată cu un exemplu.
Cum se utilizează funcția de căutare în Excel : Găsiți valoarea de căutare în matrice folosind funcția LOOKUP explicată cu un exemplu.
Cum se utilizează funcția VLOOKUP în Excel : Găsiți valoarea de căutare în matrice utilizând funcția VLOOKUP explicată cu un exemplu.
Articole populare
50 Comandă rapidă Excel pentru a vă crește productivitatea
Editați o listă derulantă
Referință absolută în Excel
Dacă cu formatare condiționată
Dacă cu wildcards
Vizualizați după dată
Alăturați-vă prenumelui și prenumelui în Excel