Kuidas kasutada Exceli funktsiooni INDEX ja MATCH

2024 Autor: Abigail Brown | [email protected]. Viimati modifitseeritud: 2023-12-17 06:45

Mida teada

Funktsiooni INDEX saab kasutada üksi, kuid funktsiooni MATCH pesastamine selle sees loob täpsema otsingu.
See pesastatud funktsioon on paindlikum kui VLOOKUP ja annab tulemusi kiiremini.

See artikkel selgitab, kuidas kasutada funktsioone INDEX ja MATCH koos kõigis Exceli versioonides, sealhulgas Excel 2019 ja Microsoft 365.

Mis on funktsioonid INDEX ja MATCH?

INDEX ja MATCH on Exceli otsingufunktsioonid. Kuigi need on kaks täiesti erinevat funktsiooni, mida saab kasutada eraldi, saab neid ka kombineerida, et luua täpsemaid valemeid.

Funktsioon INDEX tagastab väärtuse või viite väärtusele konkreetsest valikust. Näiteks saab seda kasutada väärtuse leidmiseks andmekogumi teisest reast või viiendast reast ja kolmandast veerust.

Kuigi INDEXit saab väga hästi kasutada üksi, muudab MATCHi pesastamine valemis selle pisut kasulikumaks. Funktsioon MATCH otsib lahtrivahemikus määratud üksust ja tagastab seejärel üksuse suhtelise asukoha vahemikus. Näiteks saab seda kasutada määramaks, et konkreetne nimi on nimede loendi kolmas üksus.

INDEX ja MATCH süntaks ja argumendid

Nii tuleb mõlemad funktsioonid kirjutada, et Excel neist aru saaks:

=INDEKS(massiiv, rea_number, [veeru_number])

massiiv on lahtrite vahemik, mida valem kasutab. See võib olla üks või mitu rida ja veergu, näiteks A1:D5. See on kohustuslik.
row_num on massiivi rida, millest tagastada väärtus, näiteks 2 või 18. See on nõutav, välja arvatud juhul, kui veeru_nr on olemas.
column_num on massiivi veerg, millest tagastada väärtus, näiteks 1 või 9. See on valikuline.

=MATCH(otsingu_väärtus, otsingu_massiiv, [vaste_tüüp])

lookup_value on väärtus, mida soovite otsingu_massiivis vastendada. See võib olla arv, tekst või loogiline väärtus, mis sisestatakse käsitsi või millele viidatakse lahtri viite kaudu. See on kohustuslik.
lookup_array on lahtrite vahemik, mida läbi vaadata. See võib olla üks rida või üks veerg, näiteks A2:D2 või G1:G45. See on kohustuslik.
match_type võib olla -1, 0 või 1. See määrab, kuidas otsingu_väärtus vastendatakse otsingu_massiivi väärtustega (vt allpool). 1 on vaikeväärtus, kui see argument on välja jäetud.

Millist vastetüüpi kasutada
Sobivuse tüüp	Mida see teeb	Reegel	Näide
1	Leiab suurima väärtuse, mis on väiksem kui lookup_value või sellega võrdne.	Otsimise_massiivi väärtused tuleb paigutada kasvavas järjekorras (nt -2, -1, 0, 1, 2; või A-Z; või FALSE, TRUE.	lookup_value on 25, kuid see puudub loendist lookup_array, seega tagastatakse selle asemel järgmise väikseima arvu asukoht, näiteks 22.
0	Leiab esimese väärtuse, mis on täpselt võrdne otsingu_väärtusega.	Otsimismassiivi väärtused võivad olla mis tahes järjekorras.	lookup_value on 25, seega tagastab positsiooni 25.
-1	Leiab väikseima väärtuse, mis on suurem või võrdne otsingu_väärtusega.	Otsimise_massiivi väärtused tuleb paigutada kahanevas järjekorras (nt 2, 1, 0, -1, -2).	lookup_value on 25, kuid see puudub loendist lookup_array, seega tagastatakse selle asemel suuruselt järgmine arv, näiteks 34.

Kasutage 1 või -1 juhtudel, kui peate tegema skaalal ligikaudset otsingut, näiteks kui käsitlete numbreid ja kui ligikaudsed andmed on korras. Kuid pidage meeles, et kui te ei määra parameetrit match_type, on vaikeväärtus 1, mis võib tulemusi moonutada, kui soovite tõesti täpset vastet.

Näide INDEX ja MATCH valemid

Enne kui vaatame, kuidas ühendada INDEX ja MATCH üheks valemiks, peame mõistma, kuidas need funktsioonid iseseisv alt töötavad.

INDEX Näited

=INDEKS(A1:B2, 2, 2)

=INDEKS(A1:B1, 1)

=INDEKS(2:2, 1)=INDEKS(B1:B2, 1)

Selles esimeses näites on neli INDEXi valemit, mida saame kasutada erinevate väärtuste saamiseks:

=INDEX(A1:B2, 2, 2) vaatab läbi A1:B2, et leida väärtus teisest veerust ja teisest reast, milleks on Stacy.
=INDEX(A1:B1, 1) vaatab läbi A1:B1, et leida esimesest veerust väärtus, milleks on Jon.
=INDEX(2:2, 1) vaatab läbi kõik teises reas, et leida väärtus esimesest veerust, milleks on Tim.
=INDEKS(B1:B2, 1) vaatab läbi B1:B2, et leida väärtus esimesest reast, milleks on Amy.

MATCH Näited

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

=MATCH(14, D1:D2)

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

Siin on neli lihtsat näidet funktsiooni MATCH kohta:

=MATCH("Stacy", A2:D2, 0) otsib Stacyt vahemikus A2:D2 ja tagastab tulemuseks 3.
=MATCH(14, D1:D2) otsib 14 vahemikus D1:D2, kuid kuna seda tabelist ei leitud, leiab MATCH suuruselt järgmise väärtuse see on väiksem või võrdne 14, mis antud juhul on 13, mis on lookup_array positsioonis 1.
=MATCH(14, D1:D2, -1) on identne selle kohal oleva valemiga, kuid kuna massiiv ei ole kahanevas järjekorras nagu -1 nõuab, saame veateate.
=MATCH(13, A1:D1, 0) otsib lehe esimeses reas arvu 13, mis tagastab 4, kuna see on selle massiivi neljas üksus.

INDEX-MATCH Näited

Siin on kaks näidet, kus saame kombineerida INDEX ja MATCH ühes valemis:

Leia lahtriviide tabelist

=INDEKS(B2:B5, MATCH(F1, A2:A5))

See näide pesastab valemi MATCH INDEXi valemis. Eesmärk on tuvastada kauba värv, kasutades kauba numbrit.

Kui vaatate pilti, näete "eraldatud" ridadel, kuidas valemid kirjutatakse eraldi, kuid kuna me pesame neid, siis juhtub see järgmiselt:

MATCH(F1, A2:A5) otsib andmekogust A2:A5 F1 väärtust (8795). Kui loendame veeru allapoole, näeme, et see on 2, nii et funktsioon MATCH arvas just selle välja.
INDEX-massiiviks on B2:B5, kuna me lõpuks otsime väärtust selles veerus.
Funktsiooni INDEX saab nüüd niimoodi ümber kirjutada, kuna MATCH leidis 2: INDEX(B2:B5, 2, [veeru_num]).
Kuna column_num on valikuline, saame selle eemaldada, et jätta see: INDEX(B2:B5, 2).
Nüüd on see nagu tavaline INDEKSI valem, kus leiame teise üksuse väärtuse B2:B5, mis on punane.

Otsi ridade ja veergude pealkirjade järgi

=INDEKS(B2:E13, MATCH(G1, A2:A13, 0), MATCH(G2, B1:E1, 0))

Selles MATCH ja INDEX näites teeme kahesuunalist otsingut. Mõte on näha, kui palju raha me mais roheliste toodete pe alt teenisime. See on tõesti sarnane ül altoodud näitega, kuid loendisse INDEX on pesastatud lisavalem MATCH.

MATCH(G1, A2:A13, 0) on esimene selles valemis lahendatud üksus. Konkreetse väärtuse saamiseks otsib see jaotisest A2:A13 G1 (sõna "mai"). Me ei näe seda siin, kuid see on 5.
MATCH(G2, B1:E1, 0) on teine MATCH valem ja see on tõesti sarnane esimesega, kuid otsib selle asemel G2 (sõna "roheline") veergude pealkirjades B1:E1. See lahendab 3.
Nüüd saame toimuva INDEKSI valemi ümber kirjutada järgmiselt, et visualiseerida, mis toimub: =INDEX(B2:E13, 5, 3). See otsib kogu tabelist B2:E13 viiendat rida ja kolmandat veergu, mis tagastab $180.

MATCH- ja INDEX-reeglid

Nende funktsioonidega valemeid kirjutades tuleb meeles pidada mitmeid asju:

MATCH ei ole tõstutundlik, nii et suuri ja väikeseid tähti käsitletakse tekstiväärtuste sobitamisel samamoodi.
MATCH tagastab N/A mitmel põhjusel: kui vaste_tüüp on 0 ja otsingu_väärtust ei leitud, kui vaste_tüüp on -1 ja otsingu_massiiv ei ole kahanevas järjekorras, kui match_type on 1 ja otsingu_massiiv ei ole kasvavas järjekorras järjestus ja kui otsingu_massiiv ei ole üks rida või veerg.
Saate kasutada argumendis lookup_value metamärki, kui vaste tüüp on 0 ja lookup_value on tekstistring. Küsimärk vastab mis tahes üksikule märgile ja tärn vastab mis tahes märgijadale (nt.nt =MATCH("Jo", 1:1, 0)). Kui soovite kasutada MATCH-i tegeliku küsimärgi või tärni leidmiseks, tippige kõigepe alt ~.
INDEX tagastab REF! kui rea_arv ja veeru_number ei osuta massiivi lahtrile.

Seotud Exceli funktsioonid

Funktsioon MATCH sarnaneb funktsiooniga LOOKUP, kuid MATCH tagastab üksuse asukoha, mitte üksuse enda.

VLOOKUP on veel üks otsingufunktsioon, mida saate Excelis kasutada, kuid erinev alt MATCH-ist, mis nõuab täpsemate otsingute jaoks INDEXit, vajavad VLOOKUP-i valemid ainult seda ühte funktsiooni.