Naudokite funkciją INDEX ir MATCH, kad surastumėte vertę „Excel“

Anonim

Šiame straipsnyje mes sužinosime, kaip naudoti funkciją INDEX ir MATCH, norint ieškoti vertės „Excel“.

INDEX & MATCH funkcija, pakeičianti vlookup funkciją

Paprastai mes naudojame funkciją VLOOKUP, norėdami rasti vertę, žinodami eilutės indeksą ir stulpelio numerį. Norėdami naudoti funkciją VLOOKUP, turime turėti stulpelio numerio ir eilučių atitikimo kriterijus, o svarbiausia - vertę, kad išsiaiškintume, ar ji yra atitinkančios vertės dešinėje. Tačiau duomenys ne visada tai palaiko. Daug kartų turime tiesiog ieškoti verčių nežinodami eilučių ar stulpelių indekso, o tik jas suderinti. Pavyzdžiui: rasti darbuotojo, turinčio darbuotojo ID, atlyginimą. Čia ieškosime darbuotojo ID eilutės indeksui ir atlyginimo stulpelio indeksui. Sužinokime, kaip galime atlikti šią paiešką naudodami INDEX & MATCH.

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. Atitikties funkcija grąžins paieškos vertės1 indeksą eilutės antraštės lauke. Kita MATCH funkcija grąžins 2 paieškos vertės indeksą iš stulpelio antraštės lauko. Indekso numeriai dabar bus įtraukti į funkciją INDEX, kad gautumėte vertes pagal paieškos vertę iš lentelės duomenų.

Bendra formulė:

= INDEX (duomenys, MATCH (lookup_value1, row_headers, 0, MATCH (lookup_value2, column_headers, 0)))

duomenis : lentelės reikšmių masyvas be antraščių

lookup_value1 : vertė, kurios reikia ieškoti eilutės_ antraštėje.

eilutės_ antraštės : Eilučių indeksų masyvas, kurio reikia ieškoti.

lookup_value1 : vertė, kurios reikia ieškoti stulpelio antraštėje.

column_headers : stulpelis Rodyklės masyvas, kurio reikia ieškoti.

Pavyzdys:

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

Čia yra sąrašas balų, kuriuos studentai gavo su savo dalykų sąrašu. Turime rasti konkretaus studento (Gary) ir dalyko (socialinės studijos) balą, kaip parodyta žemiau esančioje nuotraukoje.

„Student value1“ turi atitikti „Row_header“ masyvą, o „Subject value2“ - „Column_header“ masyvą.

Naudokite J6 langelyje esančią formulę:

= INDEX (lentelė, MATCH (J5, eilutė, 0, MATCH (J4, stulpelis, 0)))

Paaiškinimas:

  • Funkcija MATCH suderina studento vertę J4 langelyje su eilutės antraštės masyvu ir grąžina savo poziciją 3 kaip skaičius.
  • Funkcija MATCH atitinka temos reikšmę J5 langelyje su stulpelio antraštės masyvu ir grąžina jos padėtį 4 kaip skaičius.
  • Funkcija INDEX paima eilučių ir stulpelių indekso numerį, suranda lentelės duomenis ir grąžina atitiktį.
  • Argumentas MATCH tipas yra nustatytas į 0. Kadangi formulė išgaus tikslią atitiktį.

Čia formulės reikšmės pateikiamos kaip langelių nuorodos, o eilutės antraštė, lentelė ir stulpelio antraštė - kaip pavadinti diapazonai.

Kaip matote aukščiau esančioje momentinėje nuotraukoje, gavome studento gautą balą Gary temoje Socialiniai mokslai kaip 36. Ir tai įrodo, kad formulė veikia gerai, ir dėl abejonių supraskite toliau pateiktas pastabas.

Dabar kaip skaičių naudosime apytikslę atitiktį su eilučių ir stulpelių antraštėmis. Apytikslė atitiktis ima tik skaičių reikšmes, nes jokiu būdu ji netaikoma teksto reikšmėms

Čia mes turime vertę pagal produkto aukštį ir plotį. Turime rasti konkretaus aukščio (34) ir pločio (21) kainą, kaip parodyta paveikslėlyje žemiau.

Aukščio vertė1 turi atitikti „Row_header“ masyvą, o „Width“ reikšmė2 turi atitikti „Column_header“ masyvą.

Naudokite formulę K6 langelyje:

= INDEKSAS (duomenys, MATCH (K4, aukštis, 1, MATCH (K5, plotis, 1)))

Paaiškinimas:

  • Funkcija MATCH atitinka aukščio reikšmę K4 langelyje su eilutės antraštės masyvu ir grąžina savo poziciją 3 kaip skaičius.
  • Funkcija MATCH atitinka pločio reikšmę K5 langelyje su stulpelio antraštės masyvu ir grąžina savo poziciją 2 kaip skaičius.
  • Funkcija INDEX paima eilučių ir stulpelių indekso numerį, suranda lentelės duomenis ir grąžina atitiktį.
  • Argumentas MATCH tipas yra fiksuotas į 1. Kadangi formulė išgaus apytikslę atitiktį.

Čia formulės reikšmės pateikiamos kaip langelių nuorodos, o eilutės antraštė, duomenys ir stulpelio antraštė - kaip pavadinti diapazonai, kaip minėta aukščiau esančiame momentiniame vaizde.

Taip pat galite atlikti tikslios paieškos atitikmenis naudodami „Excel“ funkciją INDEX ir MATCH. Sužinokite daugiau apie tai, kaip atlikti didžiųjų ir mažųjų raidžių paiešką naudojant funkciją „INDEX & MATCH“ programoje „Excel“. Taip pat galite ieškoti dalinių atitikčių naudodami „Excel“ pakaitos simbolius.

Kaip matote aukščiau esančioje nuotraukoje, mes gavome kainą, gautą pagal aukštį (34) & Plotis (21) kaip 53.10. Ir tai įrodo, kad formulė veikia gerai, ir jei norite abejoti, žiūrėkite toliau pateiktas pastabas, kad geriau suprastumėte.

Pastabos:

  1. Funkcija grąžina #NA klaidą, jei paieškos masyvo argumentas funkcijai MATCH yra 2 D masyvas, kuris yra duomenų antraštės laukas …
  2. Funkcija atitinka tikslią vertę, nes atitikties tipo argumentas su funkcija MATCH yra 0.
  3. Ieškomos vertės gali būti pateiktos kaip langelio nuoroda arba tiesiogiai formulėje kaip argumentai naudojant citatos simbolį (").

Tikimės, kad šis straipsnis apie tai, kaip naudoti „INDEX“ ir „MATCH“ funkciją „Excel“ vertės paieškai, yra aiškinamasis. Čia rasite daugiau straipsnių apie verčių skaičiavimą ir susijusias „Excel“ formules. 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į. Rašykite mums el.

Norėdami ieškoti vertės, naudokite INDEX ir MATCH : INDEX-MATCH formulė naudojama dinamiškai ir tiksliai ieškoti vertės tam tikroje lentelėje. Tai yra alternatyva funkcijai VLOOKUP ir ji pašalina VLOOKUP funkcijos trūkumus.

Naudokite VLOOKUP iš dviejų ar daugiau peržiūros lentelių : Norėdami ieškoti iš kelių lentelių, galime taikyti IFERROR metodą. Jei norite ieškoti iš kelių lentelių, klaida laikoma kitos lentelės jungikliu. Kitas metodas gali būti If metodas.

Kaip atlikti didžiųjų ir mažųjų raidžių paiešką „Excel“ : „Excel“ funkcija VLOOKUP neskiria didžiųjų ir mažųjų raidžių ir grąžins pirmąją atitiktį iš sąrašo. „INDEX-MATCH“ nėra išimtis, tačiau jį galima pakeisti, kad būtų skiriamos didžiosios ir mažosios raidės. Pažiūrėkime, kaip…

Dažnai rodomo teksto paieška naudojant „Excel“ kriterijus : Dažniausiai paieška rodoma tekste diapazone, kuriame naudojame funkciją INDEX-MATCH with MODE. Štai metodas.

Populiarūs straipsniai:

Kaip naudoti „IF“ funkciją „Excel“ : „Excel“ IF sakinys patikrina sąlygą ir grąžina konkrečią reikšmę, jei sąlyga yra TRUE, arba grąžina kitą konkrečią reikšmę, jei FALSE.

Kaip naudotis „VLOOKUP“ funkcija „Excel“ : Tai yra viena iš dažniausiai naudojamų ir populiariausių „Excel“ funkcijų, naudojama vertei iš įvairių diapazonų ir lapų ieškoti.

Kaip naudotis „SUMIF“ funkcija „Excel“ : Tai dar viena esminė prietaisų skydelio funkcija. Tai padeda apibendrinti vertes konkrečiomis sąlygomis.

Kaip naudoti „COUNTIF“ funkciją „Excel“ : Skaičiuokite vertes su sąlygomis naudodami šią nuostabią funkciją. Norint suskaičiuoti konkrečias vertes, nereikia filtruoti duomenų. Skaitiklio funkcija yra būtina norint paruošti prietaisų skydelį.