INDEX un MATCH ir vispopulārākais rīks programmā Excel, lai veiktu sarežģītākus meklējumus. Tas ir tāpēc, ka INDEX un MATCH ir neticami elastīgi-jūs varat veikt horizontālos un vertikālos meklējumus, divvirzienu meklējumus, kreisās uzmeklēšanas, reģistrjutīgo meklēšanu un pat meklēšanu, pamatojoties uz vairākiem kritērijiem. Ja vēlaties uzlabot savas Excel prasmes, sarakstā ir jābūt INDEX un MATCH.
Šajā rakstā ir vienkārši izskaidrots, kā izmantot INDEX un MATCH kopā, lai veiktu meklēšanu. Tam nepieciešama pakāpeniska pieeja, vispirms izskaidrojot INDEX, pēc tam MATCH, pēc tam parādot, kā apvienot abas funkcijas kopā, lai izveidotu dinamisku divvirzienu uzmeklēšanu. Tālāk lapas apakšā ir papildu piemēri.
Funkcija INDEX | MATCH funkcija | INDEKSS un MATCH | 2-way lookup | Meklēt kreisajā pusē | Reģistrjutīga | Tuvākā spēle | Vairāki kritēriji | Vairāk piemēru
Funkcija INDEX
Funkcija INDEX programmā Excel ir fantastiski elastīga un jaudīga, un jūs to atradīsiet milzīgā skaitā Excel formulu, īpaši uzlabotajās formulās. Bet ko patiesībā dara INDEX? Īsumā, INDEX iegūst vērtību noteiktā diapazona vietā. Piemēram, pieņemsim, ka mūsu Saules sistēmā ir planētu tabula (skatīt zemāk), un jūs vēlaties iegūt 4. planētas Marsa nosaukumu ar formulu. INDEX varat izmantot šādi:
= INDEX (B3:B11,4)
INDEX atgriež vērtību diapazona 4. rindā.
Video: Kā meklēt lietas, izmantojot INDEX
Ko darīt, ja vēlaties iegūt Marsa diametru, izmantojot INDEX? Tādā gadījumā mēs varam norādīt gan rindas numuru, gan kolonnas numuru un nodrošināt lielāku diapazonu. Tālāk esošajā INDEX formulā tiek izmantots viss datu diapazons B3: D11, ar rindu skaitu 4 un kolonnu skaitu 2:
= INDEX (B3:D11,4,2)
INDEX izgūst vērtību 4. rindas 2. slejā.
Rezumējot, INDEX iegūst vērtību noteiktā vietā šūnu diapazonā, pamatojoties uz skaitlisko stāvokli. Ja diapazons ir viendimensiju, jums jānorāda tikai rindas numurs. Ja diapazons ir divdimensiju, jums jānorāda gan rindas, gan kolonnas numurs.
Šajā brīdī jūs, iespējams, domājat: “Ko tad? Cik bieži jūs patiesībā zināt kaut ko izklājlapā? '
Tieši pareizi. Mums ir nepieciešams veids, kā noteikt meklēto lietu stāvokli.
Ievadiet funkciju MATCH.
Funkcija MATCH
Funkcija MATCH ir paredzēta vienam mērķim: atrodiet vienuma pozīciju diapazonā. Piemēram, mēs varam izmantot MATCH, lai iegūtu vārdu “persiks” pozīciju šajā augļu sarakstā:
= MATCH ('peach',B3:B9,0)
MATCH atgriež 3, jo 'Persiks' ir trešais vienums. MATCH nav reģistrjutīgs.
MATCH vienalga, vai diapazons ir horizontāls vai vertikāls, kā redzams tālāk:
= MATCH ('peach',C4:I4,0)
Tas pats rezultāts ar horizontālu diapazonu, MATCH atgriež 3.
Video: Kā izmantot MATCH precīzām atbilstībām
Svarīgi: pēdējais Funkcijas MATCH arguments ir atbilstības veids. Atbilstības veids ir svarīgs un nosaka, vai atbilstība ir precīza vai aptuvena. Daudzos gadījumos jūs vēlaties izmantot nulli (0), lai piespiestu precīzas atbilstības uzvedību. Atbilstības veids pēc noklusējuma ir 1, kas nozīmē aptuvenu atbilstību, tāpēc ir svarīgi norādīt vērtību. Skatīt MATCH lapa lai iegūtu sīkāku informāciju.
INDEX un MATCH kopā
Tagad, kad esam apskatījuši INDEX un MATCH pamatus, kā mēs varam apvienot abas funkcijas vienā formulā? Apsveriet tālāk sniegtos datus, tabulu, kurā parādīts pārdevēju saraksts un ikmēneša pārdošanas skaitļi trīs mēnešos: janvārī, februārī un martā.
Pieņemsim, ka vēlamies uzrakstīt formulu, kas konkrētam pārdevējam atgriež februāra pārdošanas numuru. No iepriekš minētās diskusijas mēs zinām, ka mēs varam piešķirt INDEX rindas un kolonnas numuru, lai iegūtu vērtību. Piemēram, lai atgrieztu Frantz februāra pārdošanas numuru, mēs piedāvājam diapazonu C3: E11 ar 5. rindu un 2. kolonnu:
= INDEX (C3:E11,5,2) // returns 94
Bet mēs acīmredzot nevēlamies kodēt ciparus. Tā vietā mēs vēlamies a dinamisks uzmeklēšana.
Kā mēs to darīsim? Protams, MATCH funkcija. MATCH lieliski darbosies, lai atrastu mums nepieciešamās pozīcijas. Strādājot soli pa solim, atstāsim kolonnu ar kodu 2 un padarīsim rindas numuru dinamisku. Šeit ir pārskatītā formula, kuras funkcija MATCH ir ievietota INDEX iekšpusē 5 vietā:
= INDEX (C3:E11, MATCH ('Frantz',B3:B11,0),2)
Veicot vienu soli tālāk, MATCH izmantosim vērtību H2:
= INDEX (C3:E11, MATCH (H2,B3:B11,0),2)
MATCH atrod 'Frantz' un rindai atgriež 5 uz INDEX.
Apkopot:
- INDEX ir nepieciešamas skaitliskas pozīcijas.
- MATCH atrod šīs pozīcijas.
- MATCH ir ligzdoja INDEX iekšpusē.
Tagad pievērsīsimies kolonnas numuram.
Divvirzienu meklēšana ar INDEX un MATCH
Iepriekš mēs izmantojām funkciju MATCH, lai dinamiski atrastu rindas numuru, bet kolonnas numuru kodēti. Kā mēs varam padarīt formulu pilnīgi dinamisku, lai mēs varētu atgriezt pārdošanas apjomus jebkuram pārdevējam jebkurā mēnesī? Triks ir izmantot MATCH divas reizes - vienu reizi, lai iegūtu rindas pozīciju, un vienu reizi, lai iegūtu kolonnas pozīciju.
No iepriekš minētajiem piemēriem mēs zinām, ka MATCH darbojas labi gan ar horizontāliem, gan ar vertikāliem blokiem. Tas nozīmē, ka mēs varam viegli atrast konkrētā mēneša pozīciju, izmantojot MATCH. Piemēram, šī formula atgriež marta pozīciju, kas ir 3:
= MATCH ('Mar',C2:E2,0) // returns 3
Bet, protams, mēs nevēlamies kodēt jebkurš vērtības, tāpēc atjaunināsim darblapu, lai atļautu ievadīt mēneša nosaukumu, un izmantojiet MATCH, lai atrastu vajadzīgo kolonnas numuru. Tālāk redzamais ekrāns parāda rezultātu:
Pilnīgi dinamisks, divvirzienu uzmeklējums ar INDEX un MATCH.
= INDEX (C3:E11, MATCH (H2,B3:B11,0), MATCH (H3,C2:E2,0))
Pirmā MATCH formula atgriež 5 uz INDEX kā rindas numuru, otrā MATCH formula atgriež 3 uz INDEX kā kolonnas numuru. Kad MATCH ir izpildīta, formula tiek vienkāršota šādi:
= INDEX (C3:E11,5,3)
un INDEX pareizi atgriež 10 525 USD - Frantz pārdošanas numuru martā.
Piezīme: jūs varētu izmantot Datu validācija lai izveidotu nolaižamās izvēlnes, lai izvēlētos pārdevēju un mēnesi.
Video: Kā veikt divvirzienu meklēšanu, izmantojot INDEX un MATCH
Video: Kā atkļūdot formulu ar F9 (lai redzētu MATCH atgriešanās vērtības)
Meklēt kreisajā pusē
Viena no galvenajām INDEX un MATCH priekšrocībām salīdzinājumā ar funkciju VLOOKUP ir iespēja veikt meklēšanu pa kreisi. Vienkārši sakot, tas nozīmē tikai uzmeklēšanu, kur sleja ID atrodas uz taisnība no vērtībām, kuras vēlaties atgūt, kā redzams zemāk redzamajā piemērā:
Lasiet detalizētu skaidrojumu šeit .
Reģistrjutīgs uzmeklējums
Funkcija MATCH pati par sevi nav reģistrjutīga. Tomēr jūs izmantojat Funkcija EXACT ar INDEX un MATCH, lai meklētu lielos un mazos burtus, kā parādīts zemāk:
Lasiet detalizētu skaidrojumu šeit .
Piezīme: šī ir masīva formula un tas ir jāievada, izmantojot taustiņu kombināciju control + shift + enter, izņemot in Excel 365 .
Tuvākā spēle
Vēl viens piemērs, kas parāda INDEX un MATCH elastību, ir problēma tuvākā atbilstība . Zemāk redzamajā piemērā mēs izmantojam MIN funkcija kopā ar ABS funkcija uz izveidot uzmeklēšanas vērtība un uzmeklēšanas masīvs iekšā funkciju MATCH. Būtībā mēs izmantojam MATCH, lai atrastu mazāko atšķirību. Tad mēs izmantojam INDEX, lai no B slejas izgūtu saistīto ceļojumu.
Lasiet detalizētu skaidrojumu šeit .
Piezīme: šī ir masīva formula un tas ir jāievada, izmantojot taustiņu kombināciju control + shift + enter, izņemot in Excel 365 .
Vairāku kritēriju meklēšana
Viena no sarežģītākajām Excel problēmām ir uzmeklēšana, pamatojoties uz vairākiem kritērijiem. Citiem vārdiem sakot, uzmeklēšana, kas vienlaikus atbilst vairākām kolonnām. Zemāk redzamajā piemērā mēs izmantojam INDEX un MATCH un Būla loģika jāatbilst 3 kolonnām: vienums, krāsa un izmērs:
Lasiet detalizētu skaidrojumu šeit .
Piezīme: šī ir masīva formula un tas ir jāievada, izmantojot taustiņu kombināciju control + shift + enter, izņemot in Excel 365 .
noapaļo līdz tuvākajam tūkstoš excel
Vairāk INDEX + MATCH piemēru
Šeit ir daži pamata INDEX un MATCH piemēri darbībā, katrs ar detalizētu skaidrojumu:
- Pamata INDEX un MATCH precīzs (ietver rotaļlietu stāstu)
- Pamata indekss un atbilstība ir aptuveni (pakāpes)
- Divvirzienu meklēšana ar INDEX un MATCH (aptuvena atbilstība)
exceljet indekss un atbilstība.xlsx
