Ieškokite artimiausios atitikties naudodami INDEX & MATCH funkciją

Anonim

Šiame straipsnyje sužinosime, kaip ieškoti artimiausios atitikties naudojant „Excel“ funkciją INDEX & MATCH.

Scenarijus:

Pavyzdžiui, iš lentelės turime rasti skaitiniu požiūriu artimiausią atitiktį ir atitinkamus rezultatus. Mums reikia tam tikros fiksuotos formulės, kuri padėtų rasti artimiausią atitikmenį, kad gautume reikiamą vertę.

Kaip išspręsti problemą?

Kad formulė pirmiausia būtų suprantama, turime šiek tiek peržiūrėti šias funkcijas

  1. INDEX funkcija
  2. MATCH funkcija

Dabar mes sudarysime formulę, naudodami aukščiau pateiktas funkcijas. Funkcija MATCH grąžins artimiausios atitikties indeksą iš diapazono. Funkcija INDEX ima eilutės indeksą kaip argumentą ir grąžina reikiamus rezultatus.

Bendra formulė:

{= INDEKSAS (duomenys, MATCH (MIN (ABS (duomenys - vertė))), ABS (duomenys - vertė), 0))}

duomenis : datos reikšmių masyvas

vertės : nurodyta datos vertė, kai rezultatas yra arčiausiai

match_type : 1 (tikslus arba kitas mažiausias) arba 0 (tiksli atitiktis) arba -1 (tikslus arba kitas didžiausias)

NEREIKIA rankiniu būdu naudokite garbanotas petnešas. Garbanotieji breketai uždėti naudojant Ctrl + Shift + Enter vietoje tik Įveskite.

Pavyzdys:

Aukščiau pateiktus teiginius gali būti sunku suprasti. Taigi supraskime tai naudodami pavyzdyje pateiktą formulę

Čia turime atsitiktines datos reikšmes ir nurodytą datos vertę.

Pavadintas diapazonas, naudojamas duomenims (D4: D12).
Čia mes naudojome pavadintą diapazoną ląstelių nuorodų masyvui, nes tai lengva suprasti. G6 langelyje turime ieškoti datos, kuri yra arčiausiai nurodytos datos.

Norėdami sužinoti artimiausią datą, naudokite aukščiau pateiktą formulę.
Naudokite formulę:

{ = INDEKSAS (duomenys, MATCH (MIN (ABS (duomenys - G6))), ABS (duomenys - G6), 0)) }

NEGALIMA rankiniu būdu naudoti garbanotas petnešas. Garbanotieji breketai uždėti naudojant Ctrl + Shift + Enter vietoje tik Įveskite.

Paaiškinimas:

  • ABS funkcija grąžina teigiamą sveikąjį skaičių po to, kai atimamos datos (duomenys - G6).
  • ABS (duomenys - G6) sukuria masyvą, atimdamas visas datos reikšmes iš nurodytos datos vertės G6 langelyje. Grąžintas masyvas yra.

{ 160 ; 49 ; 105 ; 1048 ; 600 ; 6 ; 702 ; 822 ; 673 }

  • MIN (ABS (duomenys - G6)) pateikia artimiausią reikšmę iš masyvo, grąžinto atlikus aukščiau nurodytą veiksmą.

= INDEKSAS (duomenys, MATCH (6, {160; 49; 105; 1048; 600; 6; 702; 822; 673}, 0))

  • Funkcija MATCH atitinka artimiausią minimalios vertės atitiktį grąžintame masyve ir grąžina jos eilutės indeksą į funkciją INDEX.
  • Funkcija INDEX nustato vertę, kuri grąžino ROW indeksą.


duomenys, pavadinti diapazonu, naudojami datų masyvui. Dabar naudokite Ctrl + Shift + Enter vietoje tik „Enter“ viršuje gaukite rezultatą, nes tai yra masyvo formulė. Masyvo formulė yra formulė, kurioje reikšmių masyvas pereina nuo vienos funkcijos rezultato kaip argumento prie išorinės funkcijos.

Kaip matote iš aukščiau pateiktos nuotraukos, mes gavome artimiausią atitiktį iš nurodytos datos lentelėje. Ištraukite išsamią informaciją iš lentelės naudodami aukščiau pateiktą formulę.

Štai keletas pastebėjimų, naudojant aukščiau pateiktą formulę.

Pastabos:

  1. Funkcija grąžina #NA klaidą, jei paieškos masyvo argumentas funkcijai MATCH nėra vienodo ilgio lentelės masyvo.
  2. Formulė pateikia klaidą, jei „lookup_value“ neatitinka lentelės lookup_array reikšmės.
  3. Funkcija atitinka tikslią reikšmę, nes atitikties tipo argumentas su funkcija MATCH yra 0.
  4. Ieškomos vertės gali būti pateiktos kaip langelio nuoroda arba tiesiogiai formulėje kaip argumentai naudojant citatos simbolį (").
  5. NEGALIMA rankiniu būdu naudoti garbanotas petnešas. Garbanotieji breketai uždėti naudojant Ctrl + Shift + Enter vietoje tik Įveskite arba jis grąžina klaidą.

Tikimės, kad supratote, kaip ieškoti mažiausių ar mažiausių atitikčių naudojant „Excel“ funkciją „INDEX & MATCH“. Čia rasite daugiau straipsnių apie „Excel“ paieškos vertę ir „Excel 2019“ funkcijas. Nedvejodami nurodykite savo klausimus žemiau komentarų laukelyje. Mes tikrai jums padėsime.

Jei jums patiko mūsų tinklaraščiai, pasidalykite jais su draugais „Facebook“. Taip pat galite sekti mus „Twitter“ ir „Facebook“. Mes norėtume išgirsti jūsų nuomonę, praneškite mums, kaip galime patobulinti, papildyti ar naujovinti savo darbą ir padaryti jį geresnį. Parašykite mums el

Norėdami ieškoti vertės, naudokite INDEX ir MATCH : Funkcija INDEX & MATCH, jei reikia, ieškoti vertės.

SUM diapazonas su „INDEX“ programoje „Excel“ : Naudokite funkciją INDEX, kad surastumėte reikiamų verčių SUM.

Kaip naudoti „INDEX“ funkciją programoje „Excel“ : Raskite masyvo INDEX, naudodamiesi INDEX funkcija, paaiškinta pavyzdžiu.

Kaip naudoti funkciją „MATCH“ programoje „Excel“ : Raskite MATCH masyve naudodami INDEX reikšmę MATCH funkcijos viduje, paaiškinta pavyzdžiu.

Kaip naudoti „LOOKUP“ funkciją „Excel“ : Raskite paieškos reikšmę masyve naudodami funkciją LOOKUP, paaiškinta pavyzdžiu.

Kaip naudoti funkciją „VLOOKUP“ programoje „Excel“ : Raskite paieškos reikšmę masyve naudodami funkciją VLOOKUP, paaiškintą pavyzdyje.

Populiarūs straipsniai

50 „Excel“ spartusis klavišas produktyvumui padidinti

Redaguoti išskleidžiamąjį sąrašą

Absoliuti nuoroda „Excel“

Jei su sąlyginiu formatavimu

Jei su pakaitos simboliais

„Excel 2019 CONCAT“ funkcija

Funkcija „Excel 2019 MAXIFS“