Poiščite n -to ujemanje v tabeli s funkcijo INDEX & MATCH

Anonim

V tem članku se bomo naučili, kako poiščemo n -to ujemanje v tabeli s funkcijo INDEX & MATCH.

Scenarij:

Na primer, moramo iz tabele poiskati številčno n -to ujemanje in ustrezne rezultate. Potrebujemo neko splošno formulo, ki pomaga pri iskanju n -ega ujemanja, da dobimo zahtevano vrednost.

Kako rešiti problem?

Da bi formula najprej razumela, moramo nekoliko pregledati naslednje funkcije

  1. INDEX funkcija
  2. Funkcija MATCH

Zdaj bomo naredili formulo z uporabo zgornjih funkcij. Funkcija MATCH vrne indeks najnižjega ujemanja iz območja. Funkcija INDEX vzame argument vrstice kot argument in vrne ustrezne zahtevane rezultate. Ta funkcija najde

Splošna formula:

= INDEX (podatki, MATCH (SMALL (obseg, n), obseg, vrsta ujemanja), stolpec_število)

podatkov : niz vrednosti v tabeli brez glav

obseg : lookup_array za najnižje ujemanje

n : število, n -to ujemanje

match_type : 1 (natančno ali naslednje najmanjše) oz 0 (natančno ujemanje) oz -1 (natančno ali naslednje največje)

col_num: številka stolpca, zahtevana vrednost za pridobivanje iz stolpca tabele.

Primer:

Zgornje izjave so lahko zmedene za razumevanje. Zato razumejmo to z uporabo formule v primeru

Tukaj imamo tabelo s podrobnostmi o svetovni celini. Kodo države iz podane tabele moramo poiskati iz iskalne vrednosti kot ime države.


Imenovano območje, ki se uporablja za matriko tabel (E5: I10) in obseg (I5: I10).
Tu smo namesto referenčne matrike celic uporabili imenovano območje, saj je to enostavno razumljivo. Poiskati moramo vse podrobnosti, kjer je vrednost cene najnižja ali najnižja v razponu.
Uporabite zgornjo formulo, da dobite prvo podrobnost, ki je kapital
Uporabite formulo v celici G6:

= INDEX (tabela, MATCH ( MALO (obseg, 1) ,obseg, 0 ) , 4 )

Imenovani obsegi

miza (E5: I10)

obseg (I5: I10)

Pojasnilo:

  • SMALL funkcija najde prvo najnižje ujemanje v razponu in vrne vrednost funkciji MATCH.
  • Funkcija MATCH se natančno ujema z najmanjšo vrednostjo v cenovnem razponu in vrne indeks vrstice v funkcijo INDEX.
  • Funkcija INDEX najde vrednost kapitala z indeksom ROW in 4. stolpcem v imenovanem obsegu tabele.


Formula vrne vse rezultate za prvo najnižjo vrednost. Zdaj kopirajte formulo, da dobite drugo najmanjšo ali tretjo najnižjo vrednost, samo spremenite argument n pod funkcijo MALO, da dobite različne rezultate.

Zdaj samo spremenite vrednost n in dobite rezultate, kot je prikazano spodaj.


Kot lahko vidite iz zgornjega posnetka, smo v tabeli dobili vse podrobnosti, ki ustrezajo n -ti vrednosti. Iz zgornje formule izvlecite podrobnosti iz tabele.

Primer:

S funkcijo LARGE lahko dobite n -to ujemanje od vrha ali n -to najvišje ujemanje iz območja in vrne ustrezne rezultate.

Splošna formula:

= INDEX (podatki, MATCH (LARGE (obseg, n), obseg, vrsta ujemanja), stolpec)

podatkov : niz vrednosti v tabeli brez glav

obseg : lookup_array za najvišje ujemanje

n : število, n -to ujemanje

match_type : 1 (natančno ali naslednje najmanjše) oz 0 (natančno ujemanje) oz -1 (natančno ali naslednje največje)

col_num: številka stolpca, zahtevana vrednost za pridobivanje iz stolpca tabele.

Primer:

Zgornje izjave so lahko zmedene za razumevanje. Zato razumejmo to z uporabo formule v primeru

Tukaj imamo tabelo s podrobnostmi o svetovni celini. Kodo države iz podane tabele moramo poiskati iz iskalne vrednosti kot ime države.

Imenovano območje, ki se uporablja za matriko tabel (E5: I10) in obseg (I5: I10).
Tu smo namesto referenčne matrike celic uporabili imenovano območje, saj je to enostavno razumljivo. Poiskati moramo vse podrobnosti, kjer je vrednost cene najvišja ali najvišja v razponu.

Uporabite zgornjo formulo, da dobite prvo podrobnost, ki je kapital
Uporabite formulo v celici G6:

= INDEX (tabela, MATCH ( VELIKO (obseg, 1) ,obseg, 0 ) , 4 )

Imenovani obsegi

miza (E5: I10)

obseg (I5: I10)

Pojasnilo:

  • LARGE funkcija najde prvo najvišje ujemanje v razponu in vrne vrednost funkciji MATCH.
  • Funkcija MATCH se ujema z natančnim n -tim ujemanjem vrednosti v cenovnem razponu in vrne indeks vrstice v funkcijo INDEX.
  • Funkcija INDEX najde vrednost kapitala z indeksom ROW in 4. stolpcem v imenovanem obsegu tabele.

Formula vrne vse rezultate za prvo najnižjo vrednost. Zdaj kopirajte formulo, da dobite drugo najmanjšo ali tretjo najnižjo vrednost, samo spremenite argument n pod funkcijo MALO, da dobite različne rezultate.

Zdaj samo spremenite vrednost n in dobite rezultate, kot je prikazano spodaj.

Kot lahko vidite iz zgornjega posnetka, smo v tabeli dobili vse podrobnosti, ki ustrezajo n -ti vrednosti. Iz zgornje formule izvlecite podrobnosti iz tabele.

Tukaj je nekaj opazovalnih opomb pri uporabi zgornje formule.

Opombe:

  1. Vrednost n ne sme biti manjša od 1 ali večja od dolžine razpona
  2. Funkcija vrne napako #NA, če argument matrike za iskanje funkciji MATCH ni enake dolžine matrike tabel.
  3. Formula vrne napako, če se lookup_value ne ujema z vrednostjo v tabeli lookup_array.
  4. Funkcija se ujema z natančno vrednostjo, saj je argument vrste ujemanja s funkcijo MATCH 0.
  5. Iskalne vrednosti so lahko podane kot referenca celice ali pa neposredno uporabljeni navednik (") v formuli kot argumenti.

Upam, da ste razumeli, kako poiščete n -to ujemanje v tabeli s funkcijo INDEX & MATCH. Raziščite več člankov o iskalni vrednosti Excel tukaj. Prosimo, da svoja vprašanja navedete spodaj v polju za komentarje. Zagotovo vam bomo pomagali.

Če so vam bili naši blogi všeč, jih delite s prijatelji na Facebooku. Prav tako nas lahko spremljate na Twitterju in Facebooku. Radi bi slišali od vas, nam sporočite, kako lahko izboljšamo, dopolnimo ali inoviramo svoje delo in ga izboljšamo. Pišite nam na spletni strani elektronske pošte

Za iskanje vrednosti uporabite INDEX in MATCH : Funkcija INDEX & MATCH za iskanje vrednosti po potrebi.

Obseg SUM z INDEX v Excelu : S funkcijo INDEX poiščite SUM vrednosti, kot je potrebno.

Kako uporabljati funkcijo INDEX v Excelu : Poiščite INDEX matrike s funkcijo INDEX, razloženo s primerom.

Kako uporabljati funkcijo MATCH v Excelu : Poiščite MATCH v matriki z uporabo vrednosti INDEX znotraj funkcije MATCH, razložene s primerom.

Kako uporabljati funkcijo LOOKUP v Excelu : Poiščite iskalno vrednost v matriki s funkcijo LOOKUP, razloženo s primerom.

Kako uporabljati funkcijo VLOOKUP v Excelu : Poiščite iskalno vrednost v matriki s funkcijo VLOOKUP, razloženo s primerom.

Priljubljeni članki

50 Bližnjica v Excelu za večjo produktivnost

Uredite spustni seznam

Absolutna referenca v Excelu

Če s pogojnim oblikovanjem

Če z nadomestnimi znaki

Vpogled po datumu

Pridružite se imenu in priimku v Excelu