INDEX-MATCH tapo populiaresniu „Excel“ įrankiu, nes išsprendžia funkcijos VLOOKUP apribojimą ir yra paprastesnis naudoti. Funkcija INDEX-MATCH programoje „Excel“ turi daug pranašumų, palyginti su funkcija VLOOKUP:

1. INDEX ir MATCH yra lankstesni ir greitesni nei „Vlookup“.
2. Galima atlikti horizontalią, vertikalią, dviejų krypčių, kairiąją, didžiųjų ir mažųjų raidžių paiešką ir net paieškas pagal kelis kriterijus.
3. Surūšiuotuose duomenyse INDEX-MATCH yra 30 % greitesnis nei VLOOKUP. Tai reiškia, kad didesniame duomenų rinkinyje 30 % greitesnis yra prasmingesnis.

Pradėkime nuo išsamių kiekvieno INDEX ir MATCH sąvokų.

INDEKSO funkcija

„Excel“ funkcija INDEX yra labai galinga, tuo pat metu lankstus įrankis, nuskaitantis vertę tam tikroje diapazono vietoje. Kitaip tariant, jis grąžina langelio turinį, nurodytą eilutės ir stulpelio poslinkiu.

Sintaksė:

=INDEX(reference, [row], [column]) 

Parametrai:

nuoroda: langelių masyvas, į kurį reikia perkelti. Tai gali būti vienas diapazonas arba visas duomenų rinkinys duomenų lentelėje. eilutė [pasirenkama]: poslinkių eilučių skaičius. Tai reiškia, kad jei pasirinksime lentelės nuorodos diapazoną kaip A1:A5, tada langelis / turinys, kurį norime išgauti, yra vertikaliu atstumu. Čia A1 eilutėje bus 1, A2 eilutėje = ​​2 ir pan. Jei duosime eilutę = 4, tada ji išskirs A4. Kadangi eilutė yra neprivaloma, taigi, jei nenurodome jokio eilutės numerio, ji ištraukia visas nuorodas. Šiuo atveju tai yra nuo A1 iki A5. stulpelis [pasirenkama]: poslinkių stulpelių skaičius. Tai reiškia, kad jei pasirinksime lentelės nuorodos diapazoną kaip A1:B5, tada langelis / turinys, kurį norime išgauti, yra horizontaliu atstumu. Čia A1 eilutėje bus 1, o stulpelyje bus 1, B1 eilutėje bus 1, bet stulpelis bus 2 panašiai, jei A2 eilutė = 2 stulpelis = 1, B2 eilutė = 2 stulpelis = 2 ir pan. Jei duosime eilutę = 5 ir 2 stulpelį, tada išskirsime B5. Kadangi stulpelis yra neprivalomas, jei nenurodome jokios eilutės Nr. tada jis ištrauks visą stulpelį atskaitos diapazone. Pavyzdžiui, jei eilutėje = ​​2, o stulpelis tuščias, tada ji bus ištraukta (A2:B2). Jei nenurodysime ir eilutės, ir stulpelio, tada bus ištraukta visa nuorodų lentelė, kuri yra (A1:B5).

Nuorodų lentelė: Ši lentelė bus naudojama kaip nuorodų lentelė visiems funkcijos INDEX pavyzdžiams. Pirmoji langelis yra ties B3 (MAISTAS), o paskutinė įstrižainė – F10 (180).

Pavyzdžiai: Žemiau pateikiami keli indekso funkcijų pavyzdžiai.

1 atvejis: Eilučių ir stulpelių neminima.

Įvesties komanda: =INDEX(B3:C10)

2 atvejis: Paminėtos tik eilutės.

Įvesties komanda: =INDEKSAS(B3:C10,2)

3 atvejis: Paminėtos ir eilutės, ir stulpeliai.

Įvesties komanda: =INDEKSAS(B3:D10;4;2)

4 atvejis: Minimi tik stulpeliai.

Įvesties komanda: =INDEKSAS(B3 : D10 , , 2)

Problema su INDEX funkcija: Funkcijos INDEX problema yra ta, kad reikia nurodyti duomenų, kurių ieškome, eilutes ir stulpelius. Tarkime, kad susiduriame su mašininio mokymosi duomenų rinkiniu iš 10 000 eilučių ir stulpelių, tada bus labai sunku ieškoti ir išgauti ieškomų duomenų. Čia pateikiama atitikties funkcijos koncepcija, kuri nustatys eilutes ir stulpelius pagal tam tikras sąlygas.

MATCH funkcija

Jis nuskaito elemento / vertės padėtį diapazone. Tai mažiau patobulinta VLOOKUP arba HLOOKUP versija, kuri pateikia tik vietos informaciją, o ne tikruosius duomenis. MATCH neskiriamos didžiosios ir mažosios raidės ir nesvarbu, ar diapazonas yra horizontalus, ar vertikalus.

Sintaksė:

=MATCH(search_key, range, [search_type]) 

Parametrai:

search_key: vertė, kurios reikia ieškoti. Pavyzdžiui, 42, katės arba I24. diapazonas: vienmatis masyvas, kurio reikia ieškoti. Tai gali būti viena eilutė arba vienas stulpelis. pvz.->A1:A10 , A2:D2 ir tt search_type [pasirenkama]: paieškos metodas. = 1 (numatytasis) randa didžiausią reikšmę, mažesnę arba lygią paieškos_raktas, kai diapazonas rūšiuojamas didėjančia tvarka.
• = 0 randa tikslią reikšmę, kai diapazonas nerūšiuojamas.
• = -1 randa mažiausią reikšmę, didesnę nei paieškos_raktas arba lygi jai, kai diapazonas rūšiuojamas mažėjimo tvarka.

Eilutės numerį arba stulpelio numerį galima rasti naudojant atitikimo funkciją ir naudoti jį rodyklės funkcijoje, taigi, jei apie prekę yra kokių nors detalių, visa informacija apie elementą gali būti išgaunama surandant elemento eilutę / stulpelį naudojant atitiktį tada įdėkite jį į indekso funkciją.

Nuorodų lentelė: Ši lentelė bus naudojama kaip nuorodų lentelė visiems funkcijos MATCH pavyzdžiams. Pirmasis langelis yra ties B3 (MAISTAS), o paskutinė įstrižainė yra ties F10 (180)

Pavyzdžiai: Žemiau yra keletas MATCH funkcijos pavyzdžių -

1 atvejis: Paieškos tipas 0, tai reiškia tikslią atitiktį.

Įvesties komanda: =MATCH(Pietų Indijos,C3:C10,0)

2 atvejis: 1 paieškos tipas (numatytasis).

Įvesties komanda: =MATCH (Pietų Indijos, C3:C10)

3 atvejis: Paieškos tipas -1.

Įvesties komanda: =MATCH(Pietų Indijos,C3:C10,-1)

INDEX-MATCH Kartu

Ankstesniuose pavyzdžiuose statinės eilučių ir stulpelių reikšmės buvo pateiktos funkcijoje INDEX. Tarkime, kad nėra išankstinių žinių apie eilutes ir stulpelių padėtį, tada eilučių ir stulpelių padėtį galima pateikti naudojant funkciją MATCH. Tai dinamiškas būdas ieškoti ir išgauti vertę.

Sintaksė:

 =INDEX(Reference Table , [Match(SearchKey,Range,Type)/StaticRowPosition],  [Match(SearchKey,Range,Type)/StaticColumnPosition]) 

Nuorodų lentelė: Bus naudojama toliau pateikta nuorodų lentelė. Pirmasis langelis yra ties B3 (MAISTAS), o paskutinė įstrižainė yra ties F10 (180)

Pavyzdys: Tarkime, užduotis yra rasti Masala Dosa kainą. Yra žinoma, kad 3 stulpelis rodo prekių kainą, tačiau Masala Dosa eilutės padėtis nėra žinoma. Problemą galima suskirstyti į du etapus -

1 žingsnis: Raskite Masala Dosa padėtį pagal formulę:

 =MATCH('Masala Dosa',B3:B10,0) 

Čia B3:B10 reiškia stulpelį Maistas, o 0 reiškia tikslią atitiktį. Bus pateiktas Masala Dosa eilutės numeris.

2 žingsnis: Raskite Masala Dosa kainą. Norėdami sužinoti Masala Dosa kainą, naudokite INDEKSO funkciją. Pakeičiant aukščiau pateiktą MATCH funkcijos užklausą funkcijos INDEX viduje toje vietoje, kur reikalinga tiksli Masala Dosa padėtis, o kainos stulpelio numeris yra 3, kuris jau yra žinomas.

=INDEX(B3:F10, MATCH('Masala Dosa', B3:B10 , 0) ,3) 

Dviejų būdų paieška naudojant INDEX-MATCH kartu

Ankstesniame pavyzdyje išlaidų atributo stulpelio pozicija buvo užkoduota kietuoju kodu. Taigi, tai nebuvo visiškai dinamiška.

1 atvejis: Tarkime, kad nėra žinių ir apie išlaidų stulpelio numerį, tada jį galima gauti naudojant formulę:

 =MATCH('Cost',B3:F3,0) 

Čia B3: F3 reiškia antraštės stulpelį.

2 atvejis: Kai eilutės ir stulpelio reikšmės pateikiamos naudojant MATCH funkciją (nepateikiant statinės reikšmės), tada tai vadinama dvipuse peržvalga. Tai galima pasiekti naudojant formulę:

 =INDEX(B3:F10, MATCH('Masala Dosa',B3:B10, 0) , MATCH('Cost' ,B3:F3 ,0)) 

Kairioji peržiūra

Vienas iš pagrindinių INDEX ir MATCH pranašumų, palyginti su funkcija VLOOKUP, yra galimybė atlikti kairiąją paiešką. Tai reiškia, kad galima išgauti elemento eilutės padėtį naudojant bet kurį atributą dešinėje, o kito atributo kairėje reikšmę galima išgauti.

Pavyzdžiui, tarkime, kad nusipirkite maisto, kurio kaina turėtų būti 140 Rs. Netiesiogiai sakome, kad pirkite Biryani. Šiame pavyzdyje žinoma kaina 140 rupijų, reikia išgauti Maistą. Kadangi išlaidų stulpelis yra dešinėje nuo stulpelio Maistas. Jei taikoma VLOOKUP, ji negalės ieškoti kairiojoje stulpelio Kaina pusėje. Štai kodėl naudojant VLOOKUP neįmanoma gauti maisto pavadinimo.

Norint įveikti šį trūkumą, galima naudoti funkciją INDEX-MATCH.
1 žingsnis: Pirmos ištraukos eilutės padėtis kainuoja 140 Rs, naudojant formulę:

 =MATCH(140, D3:D10,0) 

Čia D3: D10 reiškia išlaidų stulpelį, kuriame atliekama Cost 140 Rs eilutės numerio paieška.

2 žingsnis: Gavus eilutės numerį, kitas žingsnis yra naudoti funkciją INDEX, kad išgautumėte maisto pavadinimą naudodami formulę:

 =INDEX(B3:B10, MATCH(140, D3:D10,0)) 

Čia B3:B10 reiškia maisto stulpelį, o 140 yra maisto prekės kaina.

Skirtingų didžiųjų ir mažųjų raidžių paieška

Pati funkcija MATCH neskiria didžiųjų ir mažųjų raidžių. Tai reiškia, jei yra maisto pavadinimas DHOKLA ir funkcija MATCH naudojama su šiuo paieškos žodžiu:

1. Dhokla
2. dhokla
3. DhOkLA

Visi grąžins DHOKLA eilutės padėtį. Tačiau funkciją EXACT galima naudoti su INDEX ir MATCH, kad būtų galima atlikti paiešką, atsižvelgiant į didžiąsias ir mažąsias raides.

Tiksli funkcija: Funkcija Excel EXACT lygina dvi teksto eilutes, atsižvelgdama į didžiąsias ir mažąsias raides, ir pateikia TRUE, jei jos yra vienodos, ir FALSE, jei ne. EXACT yra didžiosios ir mažosios raidės.

Pavyzdžiai:

EXACT (DHOKLA, DHOKLA): tai grįš True. EXACT (DHOKLA, Dhokla): tai grąžins False. EXACT (DHOKLA, dhokla): tai grąžins False. EXACT (DHOKLA, DhOkLA): tai grąžins False.

Pavyzdys: Tarkime, kad užduotis yra ieškoti maisto tipo Dhokla, bet atsižvelgiant į didžiąsias ir mažąsias raides. Tai galima padaryti naudojant formulę -

 =INDEX(C3:C10, MATCH(TRUE , EXACT('Dhokla', B3:B10) ,0)) 

Čia funkcija EXACT grąžins True, jei B3:B10 stulpelio reikšmė sutampa su Dhokla tuo pačiu atveju, kitu atveju ji grąžins False. Dabar funkcija MATCH bus taikoma stulpelyje B3:B10 ir ieškos eilutės su tikslia reikšme TRUE. Po to funkcija INDEX nuskaitys stulpelio C3:C10 (Maisto tipo stulpelis) reikšmę eilutėje, kurią grąžino funkcija MATCH.

Kelių kriterijų paieška

Viena iš sudėtingiausių „Excel“ problemų yra paieška, pagrįsta keliais kriterijais. Kitaip tariant, peržvalga, atitinkanti daugiau nei vieną stulpelį tuo pačiu metu. Toliau pateiktame pavyzdyje funkcijos INDEX ir MATCH bei loginė logika yra naudojamos suderinti 3 stulpeliuose.

1. Maistas.
2. Kaina.
3. Kiekis.

Norėdami išgauti visas išlaidas.

Pavyzdys: Tarkime, užduotis yra apskaičiuoti bendrą Makaronų kainą, kur

Maistas: makaronai. Kaina: 60. Kiekis: 1.

Taigi šiame pavyzdyje yra trys atitikties kriterijai. Žemiau pateikiami paieškos veiksmai pagal kelis kriterijus -

1 žingsnis: Pirmiausia suderinkite maisto stulpelį (B3:B10) su makaronais, naudodami formulę:

 'PASTA' = B3:B10 

Tai konvertuos B3:B10 (maisto stulpelio) reikšmes į Būlio reikšmę. Tai tiesa, kai maistas yra makaronai, kitur – klaidinga.

2 žingsnis: Po to suderinkite sąnaudų kriterijus tokiu būdu:

 60 = D3:D10 

Tai pakeis D3:D10 (išlaidų stulpelio) reikšmes kaip Būlio vertes. Tai tiesa, kai kaina = 60, kitu atveju klaidinga.

3 veiksmas: Kitas žingsnis yra suderinti trečiąjį kriterijų, kuris yra Kiekis = 1, tokiu būdu:

 1 = E3:E10 

Tai pakeis E3:E10 stulpelį (Kiekybės stulpelį) kaip Tiesa, kur Kiekis = 1, kitu atveju jis bus klaidingas.

4 veiksmas: Padauginkite pirmojo, antrojo ir trečiojo kriterijų rezultatą. Tai bus visų sąlygų sankirta, o Būlio vertė teisinga / klaidinga konvertuojama į 1/0.

5 veiksmas: Dabar rezultatas bus stulpelis su 0 ir 1. Norėdami rasti eilučių skaičių stulpelių, kuriuose yra 1, naudokite MATCH funkciją. Nes jei stulpelio reikšmė yra 1, tai reiškia, kad jis atitinka visus tris kriterijus.

6 veiksmas: Gavę eilutės numerį, naudokite funkciją INDEX, kad gautumėte bendrą tos eilutės kainą.

 =INDEX(F3:F10, MATCH(1, ('Pasta'=B3:B10) * (60=D3:D10) * (1=E3:E10) , 0 )) 

Čia F3:F10 reiškia visų išlaidų stulpelį.