Kodėl INDEX ir MATCH yra geresni nei VLOOKUP programoje „Excel“.

Anksčiau pradedantiesiems paaiškinome, kaip naudotis pagrindinėmis VLOOKUP funkcijomis (angl. VLOOKUP, santrumpa reiškia „vertikalios paieškos funkcija“). O patyrusiems vartotojams buvo parodytos kelios sudėtingesnės formulės.

Ir šiame straipsnyje mes stengsimės pateikti informaciją apie kitą darbo su vertikalia paieška metodą.

Jums gali kilti klausimas: „Kodėl to reikia? Ir tai būtina norint parodyti visus galimus paieškos būdus. Be to, daugybė VLOOKUP apribojimų dažnai neleidžia pasiekti norimo rezultato. Šiuo atžvilgiu INDEX( ) MATCH( ) yra daug funkcionalesnis ir įvairesnis, be to, jie turi mažiau apribojimų.

Pagrindai INDEX MATCH

Kadangi šio vadovo tikslas yra parodyti, kokia gera ši funkcija, mes Pažvelkime į pagrindinę informaciją apie jo veikimo principus. Ir mes parodysime pavyzdžius, taip pat apsvarstysime, kodėl tai geriau nei VLOOKUP ().

INDEX Funkcijos sintaksė ir naudojimas

Ši funkcija padeda rasti norimą reikšmę tarp nurodytų paieškos sričių pagal stulpelio arba eilutės numerį. Sintaksė:

=INDEX(masyvas, eilutės numeris, stulpelio numeris):

• masyvas – sritis, kurioje bus atliekama paieška;
• eilutės numeris – eilutės, kurios bus ieškoma nurodytame masyve, numeris. Jei eilutės numeris nežinomas, reikia nurodyti stulpelio numerį;
• stulpelio numeris – nurodytame masyve randamo stulpelio numeris. Jei reikšmė nežinoma, reikia nurodyti eilutės numerį.

Paprastos formulės pavyzdys:

=INDEX(A1:S10,2,3;XNUMX;XNUMX)

Funkcija ieškos diapazone nuo A1 iki C10. Skaičiai rodo, iš kurios eilutės (2) ir stulpelio (3) rodoma norima reikšmė. Rezultatas bus langelis C2.

Gana paprasta, tiesa? Tačiau kai dirbate su tikrais dokumentais, mažai tikėtina, kad turėsite informacijos apie stulpelių numerius ar langelius. Tam skirta funkcija MATCH().

MATCH funkcijos sintaksė ir naudojimas

Funkcija MATCH() ieško norimos reikšmės ir parodo apytikslį jos skaičių nurodytoje paieškos srityje.

Searchpos() sintaksė atrodo taip:

=MATCH(reikšmė į paiešką, masyvas į paiešką, atitikties tipas)

• paieškos reikšmė – skaičius arba tekstas, kurį reikia rasti;
• searched array – sritis, kurioje bus atliekama paieška;
• atitikties tipas – nurodo, ar ieškoti tikslios reikšmės, ar arčiausiai jai esančių reikšmių:
  • 1 (arba nenurodyta reikšmė) – pateikia didžiausią reikšmę, kuri yra lygi arba mažesnė už nurodytą reikšmę;
  • 0 – rodo tikslų atitikimą su ieškoma reikšme. Derinyje INDEX() MATCH() beveik visada reikės tikslios atitikties, todėl rašome 0;
  • -1 – rodo mažiausią reikšmę, kuri yra didesnė arba lygi formulėje nurodytai reikšmei. Rūšiavimas atliekamas mažėjančia tvarka.

Pavyzdžiui, diapazone B1:B3 yra registruoti Niujorkas, Paryžius, Londonas. Žemiau pateiktoje formulėje bus rodomas skaičius 3, nes Londonas yra trečias sąraše:

=EXPOSE(Londonas,B1:B3,0)

Kaip dirbti su funkcija INDEX MATCH 

Tikriausiai jau pradėjote suprasti, kokiu principu kuriamas bendras šių funkcijų darbas. Trumpai tariant, tada INDEX() ieško norimos reikšmės tarp nurodytų eilučių ir stulpelių. O MATCH() rodo šių reikšmių skaičius:

=INDEX(stulpelis, iš kurio grąžinama reikšmė, MATCH(reikšmė ieškoti, stulpelis ieškoti, 0))

Vis dar sunku suprasti, kaip tai veikia? Galbūt pavyzdys geriau paaiškins. Tarkime, kad turite pasaulio sostinių ir jų gyventojų sąrašą:

Norėdami sužinoti tam tikros sostinės, pavyzdžiui, Japonijos sostinės, gyventojų skaičių, naudojame šią formulę:

=INDEKSAS(C2:C10, ATITIKTIS(Japonija, A2:A10,0))

Paaiškinimas:

• Funkcija MATCH() ieško reikšmės – „Japan“ masyve A2:A10 ir grąžina skaičių 3, nes Japonija yra trečia sąrašo reikšmė. 
• Šis skaičius eina įeilės numeris“ formulėje INDEX() ir nurodo funkcijai atspausdinti reikšmę iš šios eilutės.

Taigi aukščiau pateikta formulė tampa standartine INDEKSS(C2:C10,3). Formulė ieško nuo langelių C2 iki C10 ir pateikia duomenis iš trečiojo šio diapazono langelio, tai yra, C4, nes atgalinis skaičiavimas prasideda nuo antrosios eilutės.

Nenorite formulėje nurodyti miesto pavadinimo? Tada įrašykite jį bet kuriame langelyje, tarkime, F1, ir naudokite jį kaip nuorodą MATCH() formulėje. Ir jūs gaunate dinaminę paieškos formulę:

=ИНДЕКС(С2:С10, ПОИСКПОЗ( )(F1,A2:A10,0))

Svarbu! Eilučių skaičius masyvas INDEX() turi būti toks pat kaip eilučių skaičius laikomas masyvu MATCH(), kitaip gausite neteisingą rezultatą.

Palaukite minutę, kodėl gi nepasinaudojus VLOOKUP() formule?

=ŽIŪROS (F1, A2: C10, 3, klaidinga)

 Kokia prasmė gaišti laiką bandant išsiaiškinti visus šiuos INDEX MATCH sudėtingumus?

Šiuo atveju nesvarbu, kurią funkciją naudoti. Tai tik pavyzdys, kaip suprasti, kaip funkcijos INDEX() ir MATCH() veikia kartu. Kiti pavyzdžiai parodys, ką šios funkcijos gali atlikti situacijose, kai VLOOKUP yra bejėgis. 

INDEX MATCH arba VLOOKUP

Sprendžiant, kurią paieškos formulę naudoti, daugelis sutinka, kad INDEX() ir MATCH() yra daug pranašesnės už VLOOKUP. Tačiau daugelis žmonių vis dar naudoja VLOOKUP(). Pirma, VLOOKUP() yra paprastesnis, antra, vartotojai iki galo nesupranta visų darbo su INDEX() ir MATCH() privalumų. Be šių žinių niekas nesutiks leisti laiko sudėtingos sistemos studijoms.

Štai pagrindiniai INDEX() ir MATCH() pranašumai, palyginti su VLOOKUP():

• Ieškokite iš dešinės į kairę. VLOOKUP() negali ieškoti iš dešinės į kairę, todėl ieškomos reikšmės visada turi būti kairiuosiuose lentelės stulpeliuose. Tačiau INDEX() ir MATCH() gali tai išspręsti be problemų. Šis straipsnis jums pasakys, kaip tai atrodo praktiškai: kaip rasti norimą reikšmę kairėje pusėje.

1. Saugus stulpelių pridėjimas arba pašalinimas. Formulė VLOOKUP() rodo neteisingus rezultatus pašalinant arba pridedant stulpelius, nes VLOOKUP() reikalingas tikslus stulpelio numeris, kad jis būtų sėkmingas. Natūralu, kad pridėjus ar pašalinus stulpelius keičiasi ir jų numeriai. 

O INDEX() ir MATCH() formulėse nurodomas stulpelių diapazonas, o ne atskiri stulpeliai. Dėl to galite saugiai pridėti ir pašalinti stulpelius, kiekvieną kartą neatnaujindami formulės.

1. Paieškos apimtis neribojama. Kai naudojate VLOOKUP(), bendras paieškos kriterijų skaičius neturi viršyti 255 simbolių arba gausite #VALUE! Taigi, jei jūsų duomenyse yra daug simbolių, INDEX() ir MATCH() yra geriausias pasirinkimas.
2. Didelis apdorojimo greitis. Jei jūsų lentelės yra palyginti mažos, vargu ar pastebėsite skirtumą. Bet jei lentelėje yra šimtai ar tūkstančiai eilučių ir, atitinkamai, yra šimtai ir tūkstančiai formulių, INDEX () ir MATCH () susidoros daug greičiau nei VLOOKUP (). Faktas yra tas, kad „Excel“ apdoros tik formulėje nurodytus stulpelius, o ne visą lentelę. 

VLOOKUP() našumo poveikis bus ypač pastebimas, jei darbalapyje yra daug formulių, pvz., VLOOKUP() ir SUM(). Norint išanalizuoti kiekvieną masyvo reikšmę, reikia atskirai patikrinti VLOOKUP() funkcijas. Taigi „Excel“ turi apdoroti didžiulį informacijos kiekį, o tai gerokai sulėtina darbą.

Formulių pavyzdžiai 

Jau išsiaiškinome šių funkcijų naudingumą, todėl galime pereiti prie įdomiausios dalies – žinių pritaikymo praktikoje.

Formulė paieškai iš dešinės į kairę

Kaip jau minėta, VLOOKUP negali atlikti šios formos paieškos. Taigi, jei norimos reikšmės nėra kairiajame stulpelyje, VLOOKUP() neduos rezultato. Funkcijos INDEX() ir MATCH() yra universalesnės, o reikšmių vieta neturi didelio vaidmens, kad jos veiktų.

Pavyzdžiui, kairėje lentelės pusėje pridėsime reitingo stulpelį ir pabandysime išsiaiškinti, kokį reitingą pagal gyventojų skaičių užima Mūsų šalies sostinė.

G1 langelyje įrašome norimą rasti reikšmę, o tada naudojame šią formulę, norėdami ieškoti diapazone C1:C10 ir grąžinti atitinkamą reikšmę iš A2:A10:

=ИНДЕКС(А2:А10, ПОИСКПОЗ(G1,C1:C10,0))

Raginimas. Jei šią formulę planuojate naudoti keliems langeliams, būtinai pataisykite diapazonus naudodami absoliutų adresavimą (pvz., 2 USD: 10 USD ir 2 USD: 4 C USD 10).

RODYKLĖ DAUGIAU ATSInuoginta DAUGIAU ATSInuoginta  ieškoti stulpeliuose ir eilutėse

Aukščiau pateiktuose pavyzdžiuose šias funkcijas naudojome kaip VLOOKUP() pakaitalą, kad pateiktume reikšmes iš iš anksto nustatyto eilučių diapazono. Bet ką daryti, jei reikia atlikti matricą arba dvipusę paiešką?

Skamba sudėtingai, tačiau tokių skaičiavimų formulė yra panaši į standartinę INDEX() MATCH() formulę, tik su vienu skirtumu: MATCH() formulė turi būti naudojama du kartus. Pirmą kartą norint gauti eilutės numerį, o antrą kartą – stulpelio numerį:

=INDEX(masyvas, ATITIKTIS(vertikali paieškos vertė, paieškos stulpelis, 0), ATITIKTIS(horizontali paieškos reikšmė, paieškos eilutė, 0))

Pažiūrėkime į žemiau esančią lentelę ir pabandykime sudaryti formulę INDEX() EXPRESS() EXPRESS() kad būtų rodomi demografiniai rodikliai konkrečioje šalyje pasirinktais metais.

Tikslinė šalis yra langelyje G1 (vertikali paieška), o tiksliniai metai yra langelyje G2 (horizontali paieška). Formulė atrodys taip:

=ИНДЕКС(B2:D11, ПОИСКПОЗ(G1,A2:A11,0), ПОИСКПОЗ(G2,B1:D1,0))

Kaip veikia ši formulė

Kaip ir visas kitas sudėtingas formules, jas lengviau suprasti suskaidžius jas į atskiras lygtis. Tada galite suprasti, ką atlieka kiekviena atskira funkcija:

• ATITIKTIS (G1,A2:A11,0) – ieško reikšmės (G1) diapazone A2:A11 ir parodo šios reikšmės numerį, mūsų atveju jis yra 2;
• PAIEŠKA(G2,B1:D1,0) – ieško reikšmės (G2) diapazone B1:D1. Šiuo atveju rezultatas buvo 3.

Rasti eilučių ir stulpelių numeriai siunčiami į atitinkamą reikšmę INDEX() formulėje:

=INDEKSAS(B2:D11,2,3;XNUMX;XNUMX)

Dėl to turime reikšmę, esančią langelyje, esančiame 2 eilučių ir 3 stulpelių sankirtoje diapazone B2:D11. Ir formulė rodo norimą reikšmę, kuri yra langelyje D3.

Ieškokite pagal kelias sąlygas naudodami INDEX ir MATCH

Jei perskaitėte mūsų VLOOKUP() vadovą, tikriausiai išbandėte kelias paieškos formules. Tačiau šis paieškos metodas turi vieną reikšmingą apribojimą – poreikį pridėti pagalbinį stulpelį.

Bet gera žinia ta Naudodami INDEX() ir MATCH() galite ieškoti kelių sąlygų neredaguodami ar nekeisdami darbalapio.

Štai bendra kelių sąlygų paieškos formulė INDEX() MATCH():

{=ИНДЕКС(диапазон поиска, ПОИСКПОЗ(1,условие1=диапазон1)*(условвие2=диапазон2),0))}

Pastaba: ši formulė turi būti naudojama kartu su sparčiuoju klavišu CTRL + SHIFT + ENTER.

Tarkime, kad jums reikia rasti vertę, kurios ieškote pagal 2 sąlygas: Pirkėjas и Produktas.

Tam reikia šios formulės:

=ИНДЕКС(С2:С10, ПОИСКПОЗ(1,(F1=A2:A10)*(F2=B1:B10),0))

Šioje formulėje C2:C10 yra diapazonas, kuriame bus atliekama paieška, F1 – ši sąlyga, A2:A10 – yra būklės palyginimo diapazonas, F2 – 2 sąlyga, V2:V10 – 2 būklės palyginimo diapazonas.

Nepamirškite paspausti derinio darbo pabaigoje su formule CTRL + SHIFT + ENTER – „Excel“ automatiškai uždarys formulę su riestiniais skliaustais, kaip parodyta pavyzdyje:

Jei nenorite savo darbui naudoti masyvo formulės, tada į formulę pridėkite kitą INDEX() ir paspauskite ENTER, tai atrodys taip, kaip pavyzdyje:

Kaip šios formulės veikia

Ši formulė veikia taip pat, kaip standartinė INDEX() MATCH() formulė. Norėdami ieškoti kelių sąlygų, tiesiog sukurkite kelias klaidingas ir teisingas sąlygas, kurios atspindi teisingas ir neteisingas individualias sąlygas. Ir tada šios sąlygos taikomos visiems atitinkamiems masyvo elementams. Formulė konvertuoja klaidingus ir teisingus argumentus atitinkamai į 0 ir 1 ir išveda masyvą, kuriame 1 yra atitinkančios reikšmės, kurios buvo rastos eilutėje. MATCH() suras pirmąją reikšmę, atitinkančią 1, ir perduos ją į INDEX() formulę. Ir jis, savo ruožtu, grąžins jau norimą reikšmę nurodytoje eilutėje iš norimo stulpelio.

Formulė be masyvo priklauso nuo INDEX() gebėjimo juos tvarkyti atskirai. Antrasis INDEX() formulėje atitinka klaidingą (0), todėl jis perduoda visą masyvą su tomis reikšmėmis į MATCH() formulę. 

Tai gana ilgas šios formulės logikos paaiškinimas. Norėdami gauti daugiau informacijos, skaitykite straipsnį "INDEX MATCH su keliomis sąlygomis".

AVERAGE, MAX ir MIN INDEX ir MATCH

„Excel“ turi savo specialias funkcijas, skirtas rasti vidurkius, maksimumus ir minimumus. Bet ką daryti, jei norite gauti duomenis iš langelio, susieto su tomis reikšmėmis? Tokiu atveju AVERAGE, MAX ir MIN turi būti naudojami kartu su INDEX ir MATCH.

INDEX MATCH ir MAX

Norėdami rasti didžiausią reikšmę D stulpelyje ir parodyti ją C stulpelyje, naudokite formulę: 

=ИНДЕКС(С2:С10, ПОИСКПОЗ(МАКС(D2:D10),D2:D10,0))

INDEX MATCH ir MIN

Norėdami rasti mažiausią reikšmę D stulpelyje ir parodyti ją C stulpelyje, naudokite šią formulę:

=ИНДЕКС(С2:С10,ПОИСКПОЗ(МИН(D2:D10),D2:D10,0))

PAIEŠKOS INDEKSO ir GYVATĖS

Norėdami rasti vidutinę vertę D stulpelyje ir parodyti šią reikšmę C:

=ИНДЕКС(С2:С10,ПОИСКПОЗ(СРЗНАЧ(D2:D10),D2:D10,-1))

Priklausomai nuo to, kaip parašyti jūsų duomenys, trečiasis MATCH() argumentas yra 1, 0 arba -1:

• jei stulpeliai surūšiuoti didėjimo tvarka, nustatykite 1 (tada formulė apskaičiuos maksimalią reikšmę, kuri yra mažesnė arba lygi vidutinei vertei);
• jei rūšiavimas yra mažėjantis, tada -1 (formulė išves mažiausią reikšmę, kuri yra didesnė arba lygi vidurkiui);
• jei peržvalgos masyve yra reikšmė, lygiai lygi vidurkiui, nustatykite ją į 0. 

 Mūsų pavyzdyje populiacija surūšiuota mažėjančia tvarka, todėl įdedame -1. Ir rezultatas yra Tokijas, nes gyventojų skaičius (13,189 000) yra arčiausiai vidutinės vertės (XNUMX).

VLOOKUP() taip pat gali atlikti tokius skaičiavimus, bet tik kaip masyvo formulę: VLOOKUP su AVERAGE, MIN ir MAX.

INDEX MATCH ir ESND/IFERROR

Tikriausiai jau pastebėjote, kad jei formulė neranda norimos reikšmės, ji pateikia klaidą # N / A. Standartinį klaidos pranešimą galite pakeisti kuo nors informatyvesniu. Pavyzdžiui, formulėje nustatykite argumentą XNUMX-oje:

=ЕСНД(ИНДЕКС(С2:С10,ПОИСКПОЗ(F1,A2:A10,0)),значение не найдено)

Su šia formule įvedus duomenis, kurių lentelėje nėra, forma pateiks nurodytą pranešimą.

Jei norite sugauti visas klaidas, tada išskyrus XNUMX-oje Gali būti naudojamas IFERROR:

=IFERROR(INDEX(C2:C10,MATCH(F1,A2:A10,0)), „Kažkas ne taip!“)

Tačiau atminkite, kad tokiu būdu maskuoti klaidas nėra gera idėja, nes standartinės klaidos praneša apie formulės pažeidimus.

Tikimės, kad mūsų vadovas, kaip naudoti funkciją INDEX MATCH() buvo jums naudingas.