Varat izmantot Excel MATCH funkciju, lai noteiktu konkrētas vērtības relatīvo pozīciju šūnu diapazonā vai masīvā.
Funkcija MATCH ir līdzīga funkcijai VLOOKUP, jo tās abas ir klasificētas sadaļā Excel meklēšanas/atsauces funkcijas. VLOOKUP meklē noteiktu vērtību kolonnā un atgriež vērtību tajā pašā rindā, savukārt funkcija MATCH meklē noteiktu vērtību diapazonā un atgriež šīs vērtības pozīciju.
Funkcija Excel MATCH meklē noteiktu vērtību šūnu diapazonā vai masīvā un atgriež šīs vērtības pirmās parādīšanās relatīvo pozīciju diapazonā. Funkciju MATCH var izmantot arī, lai meklētu noteiktu vērtību un atgrieztu tai atbilstošo vērtību ar funkcijas INDEX palīdzību (tāpat kā Vlookup). Apskatīsim, kā izmantot Excel MATCH funkciju, lai noteiktu uzmeklēšanas vērtības pozīciju šūnu diapazonā.
Excel MATCH funkcija
Funkcija MATCH ir programmā Excel iebūvēta funkcija, un to galvenokārt izmanto, lai kolonnā vai rindā noteiktu uzmeklēšanas vērtības relatīvo pozīciju.
Funkcijas MATCH sintakse:
=ATBILDĪBA(uzmeklēšanas_vērtība,uzmeklēšanas_masīvs,[atbilstības_veids})Kur:
lookup_value — Vērtība, kuru vēlaties meklēt noteiktā šūnu diapazonā vai masīvā. Tā var būt skaitliska vērtība, teksta vērtība, loģiskā vērtība vai šūnas atsauce, kurai ir vērtība.
uzmeklēšanas_masīvs – šūnu masīvi, kuros meklējat vērtību. Tam ir jābūt vienai kolonnai vai vienai rindai.
atbilstības_veids – Tas ir izvēles parametrs, ko var iestatīt uz 0,1 vai -1, un noklusējuma vērtība ir 1.
- 0 meklē precīzu atbilstību, ja tā netiek atrasta, atgriež kļūdu.
- -1 meklē mazāko vērtību, kas ir lielāka vai vienāda ar lookup_value, kad uzmeklēšanas masīvs aug augošā secībā.
- 1 meklē lielāko vērtību, kas ir mazāka par look_up vērtību vai vienāda ar to, kad uzmeklēšanas masīvs dilstošā secībā.
Atrodiet precīzas atbilstības pozīciju
Pieņemsim, ka mums ir šāda datu kopa, kurā mēs vēlamies atrast noteiktas vērtības pozīciju.
Šajā tabulā mēs vēlamies atrast pilsētas nosaukuma pozīciju (Memfisa) kolonnā (A2:A23), tāpēc mēs izmantojam šo formulu:
=MATCH("memfisa",A2:A23,0)Trešais arguments ir iestatīts uz “0”, jo mēs vēlamies atrast precīzu pilsētas nosaukuma atbilstību. Kā redzat, pilsētas nosaukums “memfisa” formulā ir ar mazo burtu, savukārt tabulā pilsētas nosaukuma pirmais burts ir ar lielo burtu (Memfisa). Tomēr formula spēj atrast norādītās vērtības pozīciju dotajā diapazonā. Tas ir tāpēc, ka funkcija MATCH nav reģistrjutīga.
Piezīme: Ja uzmeklēšanas_vērtība uzmeklēšanas diapazonā netiek atrasta vai ja norādāt nepareizu uzmeklēšanas diapazonu, funkcija atgriezīs kļūdu #N/A.
Funkcijas pirmajā argumentā varat izmantot šūnas atsauci, nevis tiešo vērtību. Tālāk sniegtā formula atrod vērtības pozīciju šūnā F2 un atgriež rezultātu šūnā F3.
Atrodiet aptuvenas atbilstības pozīciju
Ir divi veidi, kā meklēt aptuvenu vai precīzu uzmeklēšanas vērtības atbilstību un atgriezt tās pozīciju.
- Viens veids ir atrast mazāko vērtību, kas ir lielāka vai vienāda ar norādīto vērtību (nākamā lielākā atbilstība). To var panākt, iestatot funkcijas pēdējo argumentu (match_type) kā “-1”
- Vēl viens veids ir lielākā vērtība, kas ir mazāka par doto vērtību vai vienāda ar to (nākamā mazākā atbilstība). To var panākt, iestatot funkcijas match_type kā “1”
Nākamais mazākais mačs
Ja funkcija nevar atrast precīzu atbilstību norādītajai vērtībai, kad atbilstības veids ir iestatīts uz “1”, tā atrod lielāko vērtību, kas ir nedaudz mazāka par norādīto vērtību (kas nozīmē nākamo mazāko vērtību), un atgriež savu pozīciju. . Lai tas darbotos, jums ir jākārto masīvs augošā secībā, pretējā gadījumā radīsies kļūda.
Piemērā mēs izmantojam tālāk norādīto formulu, lai atrastu nākamo mazāko atbilstību:
=MATCH(F2,D2:D23,1)Ja šī formula nevar atrast precīzu atbilstību vērtībai šūnā F2, tā norāda uz nākamās mazākās vērtības pozīciju (16), t.i., 98.
Nākamais lielākais mačs
Ja atbilstības veids ir iestatīts uz “-1” un funkcija MATCH nevar atrast precīzu atbilstību, tā atrod mazāko vērtību, kas ir lielāka par norādīto vērtību (kas nozīmē nākamo lielāko vērtību), un atgriež savu pozīciju. Šai metodei uzmeklēšanas masīvs ir jāsakārto dilstošā secībā, pretējā gadījumā tiks parādīta kļūda.
Piemēram, ievadiet šo formulu, lai atrastu nākamo lielāko atbilstību uzmeklēšanas vērtībai:
=MATCH(F2,D2:D23,-1)Šī funkcija MATCH meklē vērtību F2 (55) uzmeklēšanas diapazonā D2:D23 un, ja tā nevar atrast precīzu atbilstību, tā atgriež nākamās lielākās vērtības pozīciju (16), t.i., 58.
Aizstājējzīmju atbilstība
Aizstājējzīmes var izmantot funkcijā MATCH tikai tad, ja match_type ir iestatīts uz “0” un uzmeklēšanas vērtība ir teksta virkne. Funkcijā MATCH varat izmantot aizstājējzīmes: zvaigznīti (*) un jautājuma zīmi (?).
- Jautājuma zīme (?) tiek izmantots, lai saskaņotu jebkuru rakstzīmi vai burtu ar teksta virkni.
- Zvaigznīte (*) tiek izmantots, lai atbilstu virknei jebkuram rakstzīmju skaitam.
Piemēram, funkcijas MATCH laukā lookup_value (Lo??n) mēs izmantojām divas aizstājējzīmes “?”, lai atrastu vērtību, kas atbilst teksta virknei ar jebkurām divām rakstzīmēm (aizstājējzīmju vietās). Un funkcija atgriež atbilstošās vērtības relatīvo pozīciju šūnā E5.
=MATCH("Lo??n",A2:A22,0)
Varat izmantot aizstājējzīmi (*) tāpat kā (?), taču zvaigznīte tiek izmantota, lai atbilstu jebkuram rakstzīmju skaitam, savukārt jautājuma zīme tiek izmantota, lai atbilstu jebkurai atsevišķai rakstzīmei.
Piemēram, ja izmantojat “sp*”, funkcija var atbilst skaļrunim, ātrumam vai spīlbergam utt. Taču, ja funkcija atrod vairākas/dublētās vērtības, kas atbilst uzmeklēšanas vērtībai, tā atgriezīs tikai pirmās vērtības pozīciju.
Piemērā argumentā lookup_value ievadījām “Kil*o”. Tātad funkcija MATCH() meklē tekstu, kura sākumā ir “Kil”, beigās – “o” un starp tām ir jebkāds rakstzīmju skaits. “Kil*o” atbilst Kilimandžaro masīvam, un tāpēc funkcija atgriež Kilimandžaro relatīvo pozīciju, kas ir 16.
INDEX un MATCH
MATCH funkcijas reti tiek izmantotas atsevišķi. Tie bieži tiek savienoti pārī ar citām funkcijām, lai izveidotu spēcīgas formulas. Ja funkcija MATCH ir apvienota ar funkciju INDEX, tā var veikt papildu meklēšanu. Daudzi cilvēki joprojām izvēlas izmantot VLOOKUP, lai meklētu vērtību, jo tas ir vienkāršāk, taču INDEX MATCH ir elastīgāks un ātrāks nekā VLOOKUP.
VLOOKUP var meklēt vērtību tikai vertikāli, t.i., kolonnās, savukārt INDEX MATCH kombinācija var veikt gan vertikālu, gan horizontālu meklēšanu.
Funkcija INDEX, ko izmanto, lai izgūtu vērtību noteiktā tabulas vai diapazona vietā. Funkcija MATCH atgriež vērtības relatīvo pozīciju kolonnā vai rindā. Apvienojot, MATCH atrod noteiktas vērtības rindas vai kolonnas numuru (atrašanās vietu), un funkcija INDEX izgūst vērtību, pamatojoties uz šīs rindas un kolonnas numuru.
Funkcijas INDEX sintakse:
=INDEKSS(masīvs,rindas_numurs,[kolonnas_numurs],)Jebkurā gadījumā apskatīsim, kā darbojas INDEX MATCH, izmantojot piemēru.
Tālāk esošajā piemērā mēs vēlamies izgūt “Quiz2” rezultātu studentam “Anne”. Lai to izdarītu, mēs izmantosim šādu formulu:
=INDEKSS(B2:F20,MATCH(H2,A2:A20,0),3)INDEX ir nepieciešams rindas un kolonnas numurs, lai izgūtu vērtību. Iepriekš minētajā formulā ligzdotā funkcija MATCH atrod vērtības “Anne” (H2) rindas numuru (pozīcija). Pēc tam mēs piegādājam šīs rindas numuru funkcijai INDEX ar diapazonu B2:F20 un kolonnas numuru (3), ko mēs norādām. Un funkcija INDEX atgriež rezultātu “91”.
Divvirzienu meklēšana ar INDEX un MATCH
Varat arī izmantot funkcijas INDEX un MATCH, lai meklētu vērtību divdimensiju diapazonā (divvirzienu meklēšana). Iepriekš minētajā piemērā mēs izmantojām funkciju MATCH, lai atrastu vērtības rindas numuru, bet kolonnas numuru ievadījām manuāli. Taču mēs varam atrast gan rindu, gan kolonnu, ligzdojot divas funkcijas MATCH, vienu funkcijas INDEX argumentā row_num un otru kolonnas_nummā.
Izmantojiet šo formulu divvirzienu meklēšanai ar INDEX un MATCH:
=INDEKSS(A1:F20,MATCH(H2,A2:A20,0),ATKRĪT(H3,A1:F1,0))Kā zināms, funkcija MATCH var meklēt vērtību gan horizontāli, gan vertikāli. Šajā formulā otrā funkcija MATCH argumentā colum_num atrod Quiz2 (4) pozīciju un piegādā to funkcijai INDEX. Un INDEKSS izgūst rezultātu.
Tagad jūs zināt, kā programmā Excel izmantot Match funkciju.