Šī apmācība iemācīs jums izmantot kombināciju INDEX & MATCH, lai veiktu meklēšanu programmā Excel un Google izklājlapas.
INDEX & MATCH, ideāls pāris
Apskatīsim tuvāk dažus veidus, kā apvienot funkcijas INDEX un MATCH. Funkcija MATCH ir paredzēta, lai atgrieztu vienuma relatīvo pozīciju masīvā, bet funkcija INDEX var izgūt vienumu no masīva, kuram ir noteikta atrašanās vieta. Šī abu sinerģija ļauj viņiem veikt gandrīz jebkura veida meklēšanu, kas jums varētu būt nepieciešama.
Kombinācija INDEX / MATCH vēsturiski tika izmantota kā funkcijas VLOOKUP aizstājējs. Viens no galvenajiem iemesliem ir spēja veikt kreisā izskata meklēšanu (skat. Nākamo sadaļu).
Piezīme: jaunā funkcija XLOOKUP tagad var veikt kreisā izskata meklējumus.
Meklēt pa kreisi
Izmantosim šo basketbola statistikas tabulu:
Mēs vēlamies atrast Boba spēlētāju #. Tā kā atskaņotāja numurs ir pa kreisi no nosaukuma slejas, mēs nevaram izmantot VLOOKUP.
Tā vietā mēs varētu veikt pamata MATCH pieprasījumu, lai aprēķinātu Boba rindu
= MATCH (H2, B2: B5, 0)
Tas meklēs precīzu vārda “Bobs” atbilstību, un tādējādi mūsu funkcija atgrieztu skaitli 2, jo “Bobs” ir 2nd pozīciju.
Tālāk mēs varam izmantot funkciju INDEX, lai atgrieztu atskaņotāja numuru #, kas atbilst rindai. Pagaidām vienkārši manuāli ievadīsim funkcijā “2”:
= INDEKSS (A2: A5, 2)
Šeit INDEX atsaucas uz A3, jo tas ir 2nd šūnu A2: A5 diapazonā un atgriezt rezultātu 42. Lai sasniegtu mūsu vispārējo mērķi, mēs varam apvienot šos divus veidus:
= INDEKSS (A2: A5, MATCH (H2, B2: B5, 0))
Ieguvums ir tāds, ka mēs varējām atgriezt rezultātu no kolonnas pa kreisi no vietas, kur mēs meklējām.
Divdimensiju uzmeklēšana
Apskatīsim mūsu tabulu iepriekš:
Tomēr šoreiz mēs vēlamies iegūt īpašu statistiku. Mēs esam uzsaukuši, ka vēlamies meklēt atsitienus šūnā H1. Tā vietā, lai rakstītu vairākus IF paziņojumus, lai noteiktu, no kuras kolonnas iegūt rezultātu, varat atkal izmantot funkciju MATCH. Funkcija INDEX ļauj norādīt rindas vērtību un kolonnas vērtību. Mēs šeit pievienosim vēl vienu MATCH funkciju, lai noteiktu, kuru kolonnu mēs vēlamies. Tā izskatīsies
= MATCH (H1, A1: E1, 0)
Mūsu šūna H1 ir nolaižamā izvēlne, kas ļauj mums izvēlēties, kuru kategoriju mēs meklēsim, un pēc tam mūsu MATCH nosaka, kurai tabulas slejai pieder. Pievienosim šo jauno bitu mūsu iepriekšējai formulai. Ņemiet vērā, ka mums ir jāpielāgo pirmais arguments, lai tas būtu divas dimensijas, jo mēs vairs nevēlamies tikai rezultātu no A slejas.
= INDEKSS (A2: E5, MATCH (H2, B2: B5, 0), MATCH (H1, A1: E1, 0))
Mūsu piemērā mēs vēlamies atrast Čārlija atsitienus. Mūsu formula to novērtēs šādi:
= INDEKSS (A2: E5, MATCH ("Čārlijs", B2: B5, 0), MATCH ("Atsitieni", A1: E1, 0)) = INDEKSS (A2: E5, 3, 4) = D4 = 6
Tagad mēs esam izveidojuši elastīgu iestatījumu, kas ļauj lietotājam no mūsu tabulas iegūt jebkuru vēlamo vērtību, bez nepieciešamības rakstīt vairākas formulas vai sazarotus IF paziņojumus.
Vairākas sadaļas
To bieži neizmanto, bet INDEX ir piektais arguments, ko var izmantot, lai noteiktu, kuru apgabalā viena argumenta ietvaros jāizmanto. Tas nozīmē, ka mums ir nepieciešams veids, kā pirmajā argumentā iekļaut vairākas jomas. To var izdarīt, izmantojot papildu iekavu komplektu. Šis piemērs parādīs, kā jūs varat iegūt rezultātus no dažādām tabulām darblapā, izmantojot INDEX.
Šeit ir izkārtojums, kuru mēs izmantosim. Mums ir statistika par trim dažādiem spēles ceturkšņiem.
Šūnās H1: H3 esam izveidojuši nolaižamos datu validācijas sarakstus dažādām izvēlēm. Ceturkšņa nolaižamā izvēlne nāk no J2: J4. Mēs to izmantosim citam MATCH paziņojumam, lai noteiktu, kuru apgabalu izmantot. Mūsu formula H4 izskatīsies šādi:
= INDEKSS ((A3: E6, A10: E13, A17: E20), MATCH (H2, B3: B6, 0), MATCH (H1, A2: E2, 0), MATCH (H3, J2: J4, 0))
Mēs jau esam apsprieduši, kā darbojas divas iekšējās MATCH funkcijas, tāpēc pievērsīsimies pirmajam un pēdējam argumentam:
= INDEKSS ((A3: E6, A10: E13, A17: E20),…, MATCH (H3, J2: J4, 0))
Mēs esam norādījuši funkcijai INDEX vairākus masīvus pirmajā argumentā, iekļaujot tos visus iekavās. Otrs veids, kā to izdarīt, ir izmantot formulas - definēt vārdu. Jūs varētu definēt nosaukumu ar nosaukumu “MyTables” ar definīciju
= INDEKSS (MyTable, MATCH (H2, Table 1347 [Name], 0), MATCH (H1, Table 1347 [#Headers], 0), MATCH (H3, J2: J4,0))
Atgriezīsimies pie visa paziņojuma. Mūsu dažādās MATCH funkcijas norādīs funkcijai INDEX, kur tieši meklēt. Pirmkārt, mēs noteiksim, ka “Čārlijs” ir 3rd rinda. Tālāk mēs vēlamies “Rebounds”, kas ir 4tūkst sleja. Visbeidzot, mēs esam noteikuši, ka vēlamies rezultātu no 2nd tabula. Formula tiks novērtēta šādi:
= INDEKSS ((A3: E6, A10: E13, A17: E20), MATCH (H2, B3: B6, 0), MATCH (H1, A2: E2, 0), MATCH (H3, J2: J4, 0)) = INDEKSS ((A3: E6, A10: E13, A17: E20), 3, 4, 2) = INDEKSS (A10: E13, 3, 4) = D13 = 14
Kā mēs minējām šī piemēra sākumā, tabulas ir pieejamas tikai vienā darblapā. Ja varat uzrakstīt pareizus veidus, kā pateikt INDEX, no kuras rindas, kolonnas un/vai apgabala vēlaties izgūt datus, INDEX jums ļoti noderēs.
Google izklājlapas -INDEKSS UN MATCH
Visi iepriekš minētie piemēri Google izklājlapās darbojas tieši tāpat kā programmā Excel.
