V tem članku se bomo naučili, kako poiskati najbližje ujemanje s funkcijo INDEX & MATCH v Excelu.
Scenarij:
Na primer, iz tabele moramo najti številčno najbližje ujemanje in njegove ustrezne rezultate. Potrebujemo določeno formulo, ki pomaga najti najbližje ujemanje, da dobimo zahtevano vrednost.
Kako rešiti problem?
Da bi formula najprej razumela, moramo nekoliko pregledati naslednje funkcije
- INDEX funkcija
- Funkcija MATCH
Zdaj bomo naredili formulo z uporabo zgornjih funkcij. Funkcija MATCH vrne indeks najbližjega ujemanja iz obsega. Funkcija INDEX vzame argument vrstice kot argument in vrne ustrezne zahtevane rezultate.
Splošna formula:
{= INDEX (podatki, MATCH (MIN (ABS (podatki - vrednost)), ABS (podatki - vrednost), 0))}}
podatkov : niz vrednosti datuma
vrednost : podana vrednost datuma, kjer je rezultat najbližji
match_type : 1 (natančno ali naslednje najmanjše) oz 0 (natančno ujemanje) oz -1 (natančno ali naslednje največje)
NE kodraste naramnice uporabite ročno. Kodraste naramnice, nanesene z uporabo Ctrl + Shift + Enter namesto samo Vnesite.
Primer:
Zgornje izjave je lahko težko razumeti. Zato razumejmo to z uporabo formule v primeru
Tu imamo nekaj naključnih vrednosti datuma in dano vrednost datuma.
Imenovano območje, ki se uporablja za podatke (D4: D12).
Tu smo namesto referenčne matrike celic uporabili imenovano območje, saj je to enostavno razumljivo. V celici G6 moramo poiskati datum, ki je najbližji datumu.
Uporabite zgornjo formulo, da dobite najbližji datum.
Uporabite formulo:
{ = INDEX (podatki, MATCH (MIN (ABS (podatki - G6)), ABS (podatki - G6), 0)) }
NE uporabljajte kodrastih naramnic ročno. Kodraste naramnice, nanesene z uporabo Ctrl + Shift + Enter namesto samo Vnesite.
Pojasnilo:
- Funkcija ABS vrne pozitivno celo število po odštevanju nad datumskimi vrednostmi (podatki - G6).
- ABS (podatki - G6) naredi matriko tako, da od dane vrednosti datuma v celici G6 odšteje vse datumske vrednosti. Vrnjeni niz je.
{ 160 ; 49 ; 105 ; 1048 ; 600 ; 6 ; 702 ; 822 ; 673 }
- MIN (ABS (podatki - G6)) vrne najbližjo vrednost iz matrike, vrnjene iz zgornjega koraka.
= INDEX (podatki, MATCH (6, {160; 49; 105; 1048; 600; 6; 702; 822; 673}, 0))
- Funkcija MATCH se ujema z najbližjim najmanjšim ujemanjem vrednosti v vrnjenem nizu in vrne indeks vrstice funkciji INDEX.
- Funkcija INDEX najde vrednost, ki vrne indeks ROW.
imenovan obseg podatkov, ki se uporablja za matriko datumov. Zdaj uporabite Ctrl + Shift + Enter na mestu za samo Enter top dobite rezultat, saj je to matrična formula. Formula niza je formula, kjer niz vrednosti gre od enega rezultata funkcije kot argumenta do zunanje funkcije.
Kot lahko vidite iz zgornjega posnetka, smo dobili najbližje ujemanje z datumom v tabeli. Iz zgornje formule izvlecite podrobnosti iz tabele.
Tukaj je nekaj opazovalnih opomb pri uporabi zgornje formule.
Opombe:
- Funkcija vrne napako #NA, če argument matrike za iskanje funkciji MATCH ni enake dolžine matrike tabel.
- Formula vrne napako, če se lookup_value ne ujema z vrednostjo v tabeli lookup_array.
- Funkcija se ujema z natančno vrednostjo, saj je argument vrste ujemanja s funkcijo MATCH 0.
- Iskalne vrednosti so lahko podane kot referenca celice ali pa neposredno uporabljeni navednik (") v formuli kot argumenti.
- NE uporabljajte kodrastih naramnic ročno. Kodraste naramnice, nanesene z uporabo Ctrl + Shift + Enter namesto samo Vnesite ali pa vrne napako.
Upam, da ste razumeli, kako poiščete najnižje ali najmanjše ujemanje s funkcijo INDEX & MATCH v Excelu. Raziščite več člankov o iskalni vrednosti Excel in funkcijah Excel 2019 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
Funkcija Excel 2019 CONCAT
Funkcija Excel 2019 MAXIFS