Kā lietot INDEX un MATCH funkcijas programmā Excel

2024 Autors: Abigail Brown | [email protected]. Pēdējoreiz modificēts: 2023-12-17 06:55

Kas jāzina

Funkciju INDEX var izmantot vienu pašu, taču funkcijas MATCH ievietošana tajā rada izvērstu meklēšanu.
Šī ligzdotā funkcija ir elastīgāka nekā VLOOKUP, un tā var dot rezultātus ātrāk.

Šajā rakstā ir paskaidrots, kā kopā izmantot funkcijas INDEX un MATCH visās Excel versijās, tostarp programmā Excel 2019 un Microsoft 365.

Kas ir INDEX un MATCH funkcijas?

INDEX un MATCH ir Excel uzmeklēšanas funkcijas. Lai gan tās ir divas pilnīgi atsevišķas funkcijas, kuras var izmantot atsevišķi, tās var arī apvienot, lai izveidotu uzlabotas formulas.

Funkcija INDEX atgriež vērtību vai atsauci uz vērtību no noteiktas atlases. Piemēram, to var izmantot, lai atrastu vērtību datu kopas otrajā rindā vai piektajā rindā un trešajā kolonnā.

Lai gan INDEX var ļoti labi izmantot atsevišķi, ligzdošana MATCH formulā padara to nedaudz noderīgāku. Funkcija MATCH meklē noteiktu vienumu šūnu diapazonā un pēc tam atgriež vienuma relatīvo pozīciju diapazonā. Piemēram, to var izmantot, lai noteiktu, ka konkrēts vārds ir trešais vienums vārdu sarakstā.

INDEX un MATCH sintakse un argumenti

Abas funkcijas ir jāraksta šādi, lai Excel tās saprastu:

=INDEKSS(masīvs, rindas_numurs, [kolonnas_numurs])

masīvs ir šūnu diapazons, ko izmantos formula. Tā var būt viena vai vairākas rindas un kolonnas, piemēram, A1:D5. Tas ir obligāti.
row_num ir rinda masīvā, no kuras jāatgriež vērtība, piemēram, 2 vai 18. Tas ir nepieciešams, ja vien nav klāt kolonnas_numurs.
column_num ir kolonna masīvā, no kuras jāatgriež vērtība, piemēram, 1 vai 9. Tas nav obligāti.

=MATCH(meklēšanas_vērtība, uzmeklēšanas_masīvs, [match_type])

lookup_value ir vērtība, kuru vēlaties saskaņot laukā lookup_array. Tas var būt skaitlis, teksts vai loģiska vērtība, kas ir ievadīta manuāli vai uz kuru ir atsauce, izmantojot šūnas atsauci. Tas ir nepieciešams.
lookup_array ir apskatāmo šūnu diapazons. Tā var būt viena rinda vai viena kolonna, piemēram, A2:D2 vai G1:G45. Tas ir nepieciešams.
match_type var būt -1, 0 vai 1. Tas norāda, kā lookup_value tiek saskaņota ar vērtībām lookup_array (skatiet tālāk). 1 ir noklusējuma vērtība, ja šis arguments tiek izlaists.

Kādu atbilstības veidu izmantot
Atbilstības veids	Ko tas dara	Noteikums	Piemērs
1	Atrod lielāko vērtību, kas ir mazāka vai vienāda ar lookup_value.	Uzmeklēšanas_masīva vērtības ir jāievieto augošā secībā (piemēram, -2, -1, 0, 1, 2; vai A-Z; vai FALSE, TRUE.	lookup_value ir 25, taču tās trūkst laukā lookup_array, tāpēc tā vietā tiek atgriezta nākamā mazākā skaitļa pozīcija, piemēram, 22.
0	Atrod pirmo vērtību, kas ir tieši vienāda ar lookup_value.	Uzmeklēšanas_masīva vērtības var būt jebkurā secībā.	lookup_value ir 25, tāpēc tā atgriež pozīciju 25.
-1	Atrod mazāko vērtību, kas ir lielāka vai vienāda ar lookup_value.	Uzmeklēšanas_masīva vērtības ir jāievieto dilstošā secībā (piemēram, 2, 1, 0, -1, -2).	lookup_value ir 25, taču tās trūkst laukā lookup_array, tāpēc tā vietā tiek atgriezta nākamā lielākā skaitļa pozīcija, piemēram, 34.

Izmantojiet 1 vai -1, ja nepieciešams veikt aptuvenu meklēšanu skalā, piemēram, strādājot ar skaitļiem un ja tuvinājumi ir pareizi. Taču atcerieties, ka, ja nenorādīsit match_type, noklusējuma vērtība būs 1, kas var izkropļot rezultātus, ja tiešām vēlaties precīzu atbilstību.

INDEX un MATCH formulas piemērs

Pirms aplūkojam, kā apvienot INDEX un MATCH vienā formulā, mums ir jāsaprot, kā šīs funkcijas darbojas pašas par sevi.

INDEX Piemēri

=INDEKSS(A1:B2, 2, 2)

=INDEKSS(A1:B1, 1)

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

Šajā pirmajā piemērā ir četras INDEX formulas, ko varam izmantot, lai iegūtu dažādas vērtības:

=INDEX(A1:B2, 2, 2) meklē cauri A1:B2, lai atrastu vērtību otrajā kolonnā un otrajā rindā, kas ir Stacy.
=INDEX(A1:B1, 1) apskata A1:B1, lai atrastu vērtību pirmajā kolonnā, kas ir Jon.
=INDEX(2:2, 1) izskata visu otrajā rindā, lai atrastu vērtību pirmajā kolonnā, kas ir Tim.
=INDEX(B1:B2, 1) apskata B1:B2, lai atrastu vērtību pirmajā rindā, kas ir Eimija.

MATCH Piemēri

=ATBILSTĪBA("Stacy", A2:D2, 0)

=MATCH(14, D1:D2)

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

Šeit ir četri vienkārši funkcijas MATCH piemēri:

=MATCH("Stacy", A2:D2, 0) meklē Stacy diapazonā A2:D2 un kā rezultātu atgriež 3.
=MATCH(14, D1:D2) meklē 14 diapazonā D1:D2, bet, tā kā tas nav atrasts tabulā, MATCH atrod nākamo lielāko vērtību tas ir mazāks vai vienāds ar 14, kas šajā gadījumā ir 13, kas atrodas lookup_array 1. pozīcijā.
=MATCH(14, D1:D2, -1) ir identiska virs tās esošajai formulai, taču tā kā masīvs nav dilstošā secībā, piemēram, -1, mēs saņemam kļūdu.
=MATCH(13, A1:D1, 0) meklē 13 lapas pirmajā rindā, kas atgriež 4, jo tas ir ceturtais vienums šajā masīvā.

INDEX-MATCH piemēri

Šeit ir divi piemēri, kur varam apvienot INDEX un MATCH vienā formulā:

Atrodiet šūnas atsauci tabulā

=INDEKSS(B2:B5, ATBILSTĪBA(F1, A2:A5))

Šajā piemērā formula MATCH ir ievietota INDEX formulā. Mērķis ir identificēt preces krāsu, izmantojot preces numuru.

Ja paskatās uz attēlu, rindās "Atdalītās" varat redzēt, kā formulas tiktu rakstītas pašas, taču, tā kā mēs tās ligzdojam, notiek šādi:

MATCH(F1, A2:A5) meklē F1 vērtību (8795) datu kopā A2:A5. Ja mēs saskaitām kolonnu uz leju, mēs varam redzēt, ka tas ir 2, tāpēc funkcija MATCH tikko izdomāja.
INDEX masīvs ir B2:B5, jo mēs galu galā meklējam vērtību šajā kolonnā.
Funkciju INDEX tagad var pārrakstīt šādi, jo MATCH atrada 2: INDEX(B2:B5, 2, [column_num]).
Tā kā column_num nav obligāta, mēs varam to noņemt, lai atstātu šo: INDEX(B2:B5, 2).
Tātad tagad šī ir kā parasta INDEX formula, kurā mēs atrodam B2:B5 otrā vienuma vērtību, kas ir sarkana.

Meklēt pēc rindu un kolonnu virsrakstiem

=INDEKSS(B2:E13, ATBILSTĪBA(G1, A2:A13, 0), ATbilstība(G2, B1:E1, 0))

Šajā MATCH un INDEX piemērā mēs veicam divvirzienu meklēšanu. Ideja ir redzēt, cik daudz naudas mēs nopelnījām no zaļajām precēm maijā. Tas tiešām ir līdzīgs iepriekš sniegtajam piemēram, taču INDEX. ir ligzdota papildu MATCH formula.

MATCH(G1, A2:A13, 0) ir pirmais šajā formulā atrisinātais vienums. Tas meklē G1 (vārds "maijs") A2:A13, lai iegūtu noteiktu vērtību. Mēs to šeit neredzam, bet tas ir 5.
MATCH(G2, B1:E1, 0) ir otrā MATCH formula, un tā patiešām ir līdzīga pirmajai, bet tā vietā meklē G2 (vārds "zaļais").) kolonnu virsrakstos B1:E1. Tas atrisina 3.
Tagad varam pārrakstīt INDEX formulu šādi, lai vizualizētu notiekošo: =INDEX(B2:E13, 5, 3). Visā tabulā B2:E13 tiek meklēta piektā rinda un trešā kolonna, kas atgriež $180.

MATCH un INDEX Noteikumi

Ir vairākas lietas, kas jāpatur prātā, rakstot formulas ar šīm funkcijām:

MATCH nav reģistrjutīga, tāpēc lielie un mazie burti tiek apstrādāti vienādi, saskaņojot teksta vērtības.
MATCH atgriež N/A vairāku iemeslu dēļ: ja match_type ir 0 un uzmeklēšanas_vērtība nav atrasta, ja match_type ir -1 un lookup_array nav dilstošā secībā, ja match_type ir 1 un lookup_array nav augošā secībā. secību, un, ja lookup_array nav viena rinda vai kolonna.
Varat izmantot aizstājējzīmi argumentā lookup_value, ja match_type ir 0 un lookup_value ir teksta virkne. Jautājuma zīme atbilst jebkurai rakstzīmei, un zvaigznīte atbilst jebkurai rakstzīmju secībai (piem.piem., =MATCH("Jo", 1:1, 0)). Lai izmantotu MATCH, lai atrastu faktisko jautājuma zīmi vai zvaigznīti, vispirms ierakstiet ~.
INDEX atgriež REF! ja rindas_numurs un kolonnas_numurs nenorāda uz šūnu masīvā.

Saistītās Excel funkcijas

Funkcija MATCH ir līdzīga funkcijai LOOKUP, taču MATCH atgriež vienuma pozīciju, nevis pašu vienumu.

VLOOKUP ir vēl viena uzmeklēšanas funkcija, ko varat izmantot programmā Excel, taču atšķirībā no MATCH, kam ir nepieciešams INDEX papildu uzmeklēšanai, VLOOKUP formulām ir nepieciešama tikai šī viena funkcija.