Kaip naudoti INDEX ir MATCH funkcijas programoje „Excel“

2024 Autorius: Abigail Brown | [email protected]. Paskutinį kartą keistas: 2023-12-17 06:55

Ką žinoti

Funkciją INDEX galima naudoti vieną, tačiau joje įdėjus funkciją MATCH sukuriama išplėstinė paieška.
Ši įdėta funkcija yra lankstesnė nei VLOOKUP ir gali greičiau duoti rezultatų.

Šiame straipsnyje paaiškinama, kaip kartu naudoti funkcijas INDEX ir MATCH visose „Excel“versijose, įskaitant „Excel 2019“ir „Microsoft 365“.

Kas yra INDEX ir MATCH funkcijos?

INDEX ir MATCH yra „Excel“paieškos funkcijos. Nors tai yra dvi visiškai skirtingos funkcijos, kurias galima naudoti atskirai, jas taip pat galima sujungti kuriant išplėstines formules.

Funkcija INDEX grąžina reikšmę arba nuorodą į reikšmę iš tam tikro pasirinkimo. Pavyzdžiui, jis gali būti naudojamas norint rasti reikšmę antroje duomenų rinkinio eilutėje arba penktoje eilutėje ir trečiame stulpelyje.

Nors INDEX gali būti labai gerai naudojamas vienas, formulėje įdėjus MATCH, jis yra šiek tiek naudingesnis. Funkcija MATCH ieško nurodyto elemento langelių diapazone ir grąžina santykinę elemento padėtį diapazone. Pavyzdžiui, jis gali būti naudojamas norint nustatyti, kad konkretus vardas yra trečiasis vardų sąrašo elementas.

INDEX ir MATCH sintaksė ir argumentai

Štai kaip turi būti parašytos abi funkcijos, kad „Excel“jas suprastų:

Nr.

masyvas yra langelių diapazonas, kurį formulė naudos. Tai gali būti viena ar daugiau eilučių ir stulpelių, pvz., A1:D5. Tai būtina.
row_num yra masyvo eilutė, iš kurios grąžinama reikšmė, pvz., 2 arba 18. Tai būtina, nebent yra stulpelio_numas.
column_num yra masyvo stulpelis, iš kurio grąžinama reikšmė, pvz., 1 arba 9. Tai neprivaloma.

=MATCH(lookup_value, lookup_masyvas, [match_type])

lookup_value yra reikšmė, kurią norite atitikti lookup_array. Tai gali būti skaičius, tekstas arba loginė reikšmė, įvedama rankiniu būdu arba nurodyta per langelio nuorodą. Tai būtina.
lookup_array yra langelių diapazonas, kuriame reikia peržiūrėti. Tai gali būti viena eilutė arba vienas stulpelis, pvz., A2:D2 arba G1:G45. Tai būtina.
match_type gali būti -1, 0 arba 1. Nurodoma, kaip lookup_value suderinama su lookup_array reikšmėmis (žr. toliau). 1 yra numatytoji reikšmė, jei šis argumentas praleistas.

Kurį atitikties tipą naudoti
Atitikties tipas	Ką tai daro	Taisyklė	Pavyzdys
1	Suranda didžiausią reikšmę, kuri yra mažesnė arba lygi lookup_value.	Lookup_masyvo reikšmės turi būti pateikiamos didėjančia tvarka (pvz., -2, -1, 0, 1, 2; arba A-Z; arba FALSE, TRUE.	lookup_value yra 25, bet jos nėra lookup_array, todėl vietoj to grąžinama kito mažiausio skaičiaus, pvz., 22, padėtis.
0	Suranda pirmąją reikšmę, kuri tiksliai lygi lookup_value.	Lookup_masyvo reikšmės gali būti bet kokia tvarka.	lookup_value yra 25, todėl grąžinama 25 padėtis.
-1	Suranda mažiausią reikšmę, kuri yra didesnė arba lygi lookup_value.	Lookup_masyvo reikšmės turi būti pateikiamos mažėjančia tvarka (pvz., 2, 1, 0, -1, -2).	lookup_value yra 25, bet jos nėra lookup_array, todėl vietoj to grąžinama kito didžiausio skaičiaus, pvz., 34, padėtis.

Naudokite 1 arba -1, kai reikia atlikti apytikslę paiešką pagal skalę, pvz., kai dirbate su skaičiais ir kai aproksimacijos yra tinkamos. Tačiau atminkite, kad jei nenurodysite match_type, 1 bus numatytasis, o tai gali iškreipti rezultatus, jei tikrai norite tikslios atitikties.

INDEX ir MATCH formulių pavyzdys

Prieš apžvelgdami, kaip sujungti INDEX ir MATCH į vieną formulę, turime suprasti, kaip šios funkcijos veikia pačios.

INDEX Pavyzdžiai

=INDEKSAS(A1:B2, 2, 2)

=INDEKSAS(A1:B1, 1)

=INDEKSAS(2:2, 1)=INDEX(B1:B2, 1)

Šiame pirmame pavyzdyje yra keturios INDEX formulės, kurias galime naudoti skirtingoms reikšmėms gauti:

=INDEX(A1:B2, 2, 2) žiūri per A1:B2, kad surastų reikšmę antrame stulpelyje ir antroje eilutėje, kuri yra Stacy.
=INDEX(A1:B1, 1) peržiūri A1:B1, kad surastų reikšmę pirmame stulpelyje, kuris yra Jon.
=INDEX(2:2, 1) peržiūri viską antroje eilutėje, kad surastų reikšmę pirmame stulpelyje, kuris yra Tim.
=INDEX(B1:B2, 1) žiūri per B1:B2, kad surastų reikšmę pirmoje eilutėje, kuri yra Amy.

MATCH Pavyzdžiai

=MATCH("Stacy", A2:D2, 0)

=MATCH(14, D1:D2)

=MATCH(14, D1:D2, -1)=MATCH(13, A1:D1, 0)

Štai keturi paprasti MATCH funkcijos pavyzdžiai:

=MATCH("Stacy", A2:D2, 0) ieško Stacy diapazone A2:D2 ir kaip rezultatą pateikia 3.
=MATCH(14, D1:D2) ieško 14 diapazone D1:D2, bet kadangi jo nerasta lentelėje, MATCH randa kitą didžiausią reikšmę tai yra mažesnė arba lygi 14, kuri šiuo atveju yra 13, kuri yra lookup_array 1 padėtyje.
=MATCH(14, D1:D2, -1) yra identiška aukščiau esančiai formulei, bet kadangi masyvas nėra mažėjančia tvarka, kaip reikalauja -1, gauname klaidą.
=MATCH(13, A1:D1, 0) ieško 13 pirmoje lapo eilutėje, kuri grąžina 4, nes tai ketvirtas elementas šiame masyve.

INDEX-MATCH pavyzdžiai

Štai du pavyzdžiai, kai galime sujungti INDEX ir MATCH vienoje formulėje:

Raskite langelio nuorodą lentelėje

=INDEKSAS(B2:B5, ATITIKTIS(F1, A2:A5))

Šiame pavyzdyje MATCH formulė įdėta į INDEX formulę. Tikslas yra nustatyti prekės spalvą naudojant prekės numerį.

Jei pažvelgsite į vaizdą, eilutėse „Atskirtos“pamatysite, kaip formulės būtų parašytos atskirai, bet kadangi mes jas sudedame, tai vyksta taip:

MATCH(F1, A2:A5) ieško F1 reikšmės (8795) duomenų rinkinyje A2:A5. Jei skaičiuosime stulpelį žemyn, pamatysime, kad jis yra 2, taigi, funkcija MATCH ką tik išsiaiškino.
INDEX masyvas yra B2:B5, nes galiausiai ieškome reikšmės tame stulpelyje.
Funkciją INDEX dabar galima perrašyti taip, nes MATCH rado 2: INDEX(B2:B5, 2, [column_num]).
Kadangi column_num yra neprivalomas, galime jį pašalinti, kad liktų ši: INDEX(B2:B5, 2).
Taigi dabar tai yra kaip įprasta INDEKSO formulė, kurioje randame antrojo elemento reikšmę B2:B5, kuri yra raudona.

Ieškoti pagal eilutes ir stulpelių antraštes

=INDEKSAS(B2:E13, ATITIKTIS(G1, A2:A13, 0), ATITIKTIS(G2, B1:E1, 0))

Šiame MATCH ir INDEX pavyzdyje atliekame dvipusę paiešką. Idėja yra pamatyti, kiek pinigų mes uždirbome iš žaliųjų prekių gegužės mėn. Tai tikrai panašu į anksčiau pateiktą pavyzdį, bet papildoma MATCH formulė yra įdėta į INDEX.

MATCH(G1, A2:A13, 0) yra pirmasis elementas, išspręstas šioje formulėje. Ji ieško G1 (žodis „gegužė“) A2:A13, kad gautų konkrečią reikšmę. Čia jo nematome, bet 5.
MATCH(G2, B1:E1, 0) yra antroji MATCH formulė ir ji tikrai panaši į pirmąją, bet vietoj jos ieško G2 (žodis „Žalia“).) stulpelių antraštėse B1:E1. Tai išsprendžia 3.
Dabar galime perrašyti INDEKSO formulę taip, kad įsivaizduotume, kas vyksta: =INDEX(B2:E13, 5, 3). Visoje lentelėje B2:E13 ieškoma penktoje eilutėje ir trečioje stulpelyje, kuri grąžina 180 USD.

MATCH ir INDEX taisyklės

Rašant formules su šiomis funkcijomis reikia atsiminti keletą dalykų:

MATCH neskiria didžiųjų ir mažųjų raidžių, todėl derinant teksto reikšmes didžiosios ir mažosios raidės traktuojamos vienodai.
MATCH grąžina N/A dėl kelių priežasčių: jei match_type yra 0 ir lookup_value nerasta, jei match_type yra -1 ir lookup_masyvo nėra mažėjančia tvarka, jei match_type yra 1 ir lookup_masyvo nėra didėjančia tvarka tvarka, o jei lookup_masyvas nėra viena eilutė arba stulpelis.
Argumente lookup_value galite naudoti pakaitos simbolį, jei match_type yra 0, o lookup_value yra teksto eilutė. Klaustukas atitinka bet kurį vieną simbolį, o žvaigždutė – bet kurią simbolių seką (pvz.,pvz., =MATCH("Jo", 1:1, 0)). Jei norite naudoti MATCH, kad surastumėte tikrą klaustuką arba žvaigždutę, pirmiausia įveskite ~.
INDEX grąžina REF! jei eilutės_numeris ir stulpelio_numeris nenurodo į langelį masyve.

Susijusios „Excel“funkcijos

Funkcija MATCH yra panaši į LOOKUP, bet MATCH grąžina elemento padėtį, o ne patį elementą.

VLOOKUP yra dar viena paieškos funkcija, kurią galite naudoti programoje „Excel“, tačiau skirtingai nei MATCH, kuriai reikalingas INDEX išplėstinėms peržvalgoms, VLOOKUP formulėms reikia tik tos vienos funkcijos.