Excel

Kā lietot INDEX un MATCH

How Use Index Match

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)

Izmantojot INDEX, lai iegūtu 4. planētas nosaukumu
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)

Izmantojot INDEX, lai iegūtu 4. planētas diametru
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)

Izmantojot MATCH, lai atrastu pozīciju vertikālā diapazonā
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)

Izmantojot MATCH, lai atrastu pozīciju horizontālā diapazonā
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ā.

Pārdevēja pārdošana pa mēnešiem

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)

INDEX un MATCH, lai atrastu februāra pārdošanu jebkuram nosaukumam
MATCH atrod 'Frantz' un rindai atgriež 5 uz INDEX.

Apkopot:

  1. INDEX ir nepieciešamas skaitliskas pozīcijas.
  2. MATCH atrod šīs pozīcijas.
  3. 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:

Dinamiska meklēšana ar INDEX un MATCH
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ā:

Meklēt kreisajā pusē ar INDEX un MATCH

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:

Reģistrjutīga meklēšana ar INDEX un MATCH

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.

Atrodiet tuvāko atbilstību ar INDEX un MATCH

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:

INDEX un MATCH ar vairākiem kritērijiem

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:

Autors Deivs Bruns Pielikumi Fails exceljet indekss un atbilstība.xlsx


^