Paano Gamitin ang INDEX at MATCH Function sa Excel
2024 May -akda: Abigail Brown | [email protected]. Huling binago: 2023-12-17 07:03
Ano ang Dapat Malaman
Ang INDEX function ay maaaring gamitin nang mag-isa, ngunit ang paglalagay ng MATCH function sa loob nito ay lumilikha ng isang advanced na paghahanap.
Ang nested function na ito ay mas flexible kaysa sa VLOOKUP at maaaring magbunga ng mga resulta nang mas mabilis.
Ipinapaliwanag ng artikulong ito kung paano gamitin ang INDEX at MATCH function nang magkasama sa lahat ng bersyon ng Excel, kabilang ang Excel 2019 at Microsoft 365.
Ano ang mga function ng INDEX at MATCH?
Ang INDEX at MATCH ay mga function ng paghahanap ng Excel. Bagama't ang mga ito ay dalawang ganap na magkahiwalay na function na maaaring gamitin nang mag-isa, maaari din silang pagsamahin upang lumikha ng mga advanced na formula.
Ang INDEX function ay nagbabalik ng value o ang reference sa isang value mula sa loob ng isang partikular na seleksyon. Halimbawa, maaari itong gamitin upang mahanap ang value sa pangalawang row ng isang data set, o sa ikalimang row at ikatlong column.
Habang ang INDEX ay maaaring gamitin nang mag-isa, ang paglalagay ng MATCH sa formula ay ginagawa itong mas kapaki-pakinabang. Ang MATCH function ay naghahanap ng isang tinukoy na item sa isang hanay ng mga cell at pagkatapos ay ibinabalik ang kaugnay na posisyon ng item sa hanay. Halimbawa, maaari itong gamitin upang matukoy na ang isang partikular na pangalan ay ang ikatlong item sa isang listahan ng mga pangalan.
Image
INDEX at MATCH Syntax & Argument
Ganito kailangang isulat ang parehong function upang maunawaan ng Excel ang mga ito:
=INDEX(array, row_num, [column_num])
Ang
array ay ang hanay ng mga cell na gagamitin ng formula. Maaari itong maging isa o higit pang mga row at column, gaya ng A1:D5. Kinakailangan ito.
Ang
row_num ay ang row sa array kung saan magbabalik ng value, gaya ng 2 o 18. Kinakailangan ito maliban kung may column_num.
Ang
column_num ay ang column sa array kung saan magbabalik ng value, gaya ng 1 o 9. Opsyonal ito.
=MATCH(lookup_value, lookup_array, [match_type])
Ang
lookup_value ay ang value na gusto mong itugma sa lookup_array. Maaari itong isang numero, teksto, o lohikal na halaga na manu-manong na-type o tinutukoy sa pamamagitan ng isang cell reference. Kinakailangan ito.
Ang
lookup_array ay ang hanay ng mga cell na titingnan. Maaari itong maging isang row o isang column, gaya ng A2:D2 o G1:G45. Kinakailangan ito.
match_type ay maaaring -1, 0, o 1. Tinutukoy nito kung paano itinutugma ang lookup_value sa mga value sa lookup_array (tingnan sa ibaba). 1 ang default na value kung aalisin ang argument na ito.
Aling Uri ng Tugma ang Gagamitin
Uri ng Tugma
Ano ang Ginagawa Nito
Panuntunan
Halimbawa
1
Hinahanap ang pinakamalaking value na mas mababa o katumbas ng lookup_value.
Ang mga value ng lookup_array ay dapat ilagay sa pataas na pagkakasunud-sunod (hal., -2, -1, 0, 1, 2; o A-Z;, o FALSE, TRUE.
lookup_value ay 25 ngunit nawawala ito sa lookup_array, kaya ang posisyon ng susunod na pinakamaliit na numero, tulad ng 22, ay ibinalik sa halip.
0
Hinahanap ang unang value na eksaktong katumbas ng lookup_value.
Ang mga value ng lookup_array ay maaaring nasa anumang pagkakasunud-sunod.
Ang lookup_value ay 25, kaya ibinabalik nito ang posisyong 25.
-1
Hinahanap ang pinakamaliit na value na mas malaki o katumbas ng lookup_value.
Ang mga value ng lookup_array ay dapat ilagay sa pababang pagkakasunod-sunod (hal., 2, 1, 0, -1, -2).
lookup_value ay 25 ngunit nawawala ito sa lookup_array, kaya ang posisyon ng susunod na pinakamalaking numero, tulad ng 34, ay ibinalik sa halip.
Gumamit ng 1 o -1 para sa mga pagkakataong kailangan mong magpatakbo ng tinatayang paghahanap sa isang sukatan, tulad ng kapag nakikitungo sa mga numero at kapag okay ang mga pagtatantya. Ngunit tandaan na kung hindi mo tutukuyin ang match_type, 1 ang magiging default, na maaaring masira ang mga resulta kung talagang gusto mo ng eksaktong tugma.
Mga Halimbawang INDEX at MATCH Formula
Bago natin tingnan kung paano pagsamahin ang INDEX at MATCH sa isang formula, kailangan nating maunawaan kung paano gumagana ang mga function na ito nang mag-isa.
INDEX Halimbawa
=INDEX(A1:B2, 2, 2)
=INDEX(A1:B1, 1)
=INDEX(2:2, 1)=INDEX(B1:B2, 1)
Image
Sa unang halimbawang ito, may apat na INDEX formula na magagamit namin para makakuha ng iba't ibang value:
=INDEX(A1:B2, 2, 2) tinitingnan ang A1:B2 upang mahanap ang value sa pangalawang column at pangalawang row, na si Stacy.
=INDEX(A1:B1, 1) tinitingnan ang A1:B1 upang mahanap ang value sa unang column, na Jon.
=INDEX(2:2, 1) tinitingnan ang lahat ng nasa pangalawang row para mahanap ang value sa unang column, na Tim.
=INDEX(B1:B2, 1) tinitingnan ang B1:B2 upang mahanap ang value sa unang row, na si Amy.
Mga Halimbawa ng MATCH
=MATCH("Stacy", A2:D2, 0)
=MATCH(14, D1:D2)
=MATCH(14, D1:D2, -1)=MATCH(13, A1:D1, 0)
Image
Narito ang apat na madaling halimbawa ng MATCH function:
=MATCH("Stacy", A2:D2, 0) ay naghahanap kay Stacy sa hanay na A2:D2 at nagbabalik ng 3 bilang resulta.
=MATCH(14, D1:D2) ay naghahanap ng 14 sa hanay na D1:D2, ngunit dahil hindi ito makikita sa talahanayan, hinahanap ng MATCH ang susunod na pinakamalaking value iyon ay mas mababa sa o katumbas ng 14, na sa kasong ito ay 13, na nasa posisyon 1 ng lookup_array.
=MATCH(14, D1:D2, -1) ay kapareho ng formula sa itaas nito, ngunit dahil ang array ay wala sa pababang pagkakasunud-sunod tulad ng kailangan ng -1, nakakakuha kami ng error.
=MATCH(13, A1:D1, 0) ay naghahanap ng 13 sa unang row ng sheet, na nagbabalik ng 4 dahil ito ang ikaapat na item sa array na ito.
INDEX-MATCH Examples
Narito ang dalawang halimbawa kung saan maaari nating pagsamahin ang INDEX at MATCH sa isang formula:
Hanapin ang Cell Reference sa Talahanayan
=INDEX(B2:B5, MATCH(F1, A2:A5))
Image
Ang halimbawang ito ay naglalagay ng MATCH formula sa loob ng INDEX formula. Ang layunin ay tukuyin ang kulay ng item gamit ang numero ng item.
Kung titingnan mo ang larawan, makikita mo sa mga row na "Hiwalay" kung paano isusulat nang mag-isa ang mga formula, ngunit dahil nilalagay namin ang mga ito, ito ang nangyayari:
Hinahanap ng
MATCH(F1, A2:A5) ang halaga ng F1 (8795) sa set ng data A2:A5. Kung bibilangin natin ang column, makikita natin na ito ay 2, kaya iyon ang naisip ng MATCH function.
Ang INDEX array ay B2:B5 dahil sa huli ay hinahanap namin ang value sa column na iyon.
Maaari na ngayong muling isulat ang INDEX function na tulad nito dahil 2 ang nakitang MATCH: INDEX(B2:B5, 2, [column_num]).
Dahil ang column_num ay opsyonal, maaari naming alisin iyon upang maiwan dito: INDEX(B2:B5, 2).
Kaya ngayon, ito ay tulad ng isang normal na formula ng INDEX kung saan hinahanap namin ang halaga ng pangalawang item sa B2:B5, na pula.
Sa halimbawang ito ng MATCH at INDEX, gumagawa kami ng two-way lookup. Ang ideya ay upang makita kung gaano karaming pera ang nakuha namin mula sa mga Green item noong Mayo. Ito ay talagang katulad ng halimbawa sa itaas, ngunit may dagdag na MATCH na formula ay naka-nest sa INDEX.
MATCH(G1, A2:A13, 0) ang unang item na nalutas sa formula na ito. Hinahanap nito ang G1 (ang salitang "Mayo") sa A2:A13 upang makakuha ng partikular na halaga. Hindi namin ito nakikita dito, ngunit ito ay 5.
Ang
MATCH(G2, B1:E1, 0) ay ang pangalawang MATCH formula, at ito ay talagang katulad ng una ngunit sa halip ay naghahanap ng G2 (ang salitang "Green") sa mga heading ng column sa B1:E1. Ang isang ito ay lumulutas sa 3.
Maaari na nating muling isulat ang INDEX formula na tulad nito upang mailarawan kung ano ang nangyayari: =INDEX(B2:E13, 5, 3). Ito ay tumitingin sa buong talahanayan, B2:E13, para sa ikalimang row at ikatlong column, na nagbabalik ng $180.
Mga Panuntunan sa MATCH at INDEX
May ilang bagay na dapat tandaan kapag nagsusulat ng mga formula na may ganitong mga function:
Ang MATCH ay hindi case sensitive, kaya ang uppercase at lowercase na mga titik ay itinuturing na pareho kapag tumutugma sa mga text value.
MATCH ay nagbabalik ng N/A para sa maraming dahilan: kung ang match_type ay 0 at lookup_value ay hindi nahanap kung ang match_type ay -1 at lookup_array ay wala sa pababang pagkakasunod-sunod, kung ang match_type ay 1 at lookup_array ay wala sa pataas order, at kung ang lookup_array ay hindi isang solong row o column.
Maaari kang gumamit ng wildcard na character sa lookup_value argument kung ang match_type ay 0 at lookup_value ay isang text string. Ang isang tandang pananong ay tumutugma sa anumang solong karakter at isang asterisk ay tumutugma sa anumang pagkakasunud-sunod ng mga character (hal.g., =MATCH("Jo", 1:1, 0)). Para magamit ang MATCH para maghanap ng aktwal na tandang pananong o asterisk, i-type muna ang ~.
INDEX ay nagbabalik ng REF! kung ang row_num at column_num ay hindi tumuturo sa isang cell sa loob ng array.
Mga Kaugnay na Excel Function
Ang MATCH function ay katulad ng LOOKUP, ngunit ibinabalik ng MATCH ang posisyon ng item sa halip na ang item mismo.
Ang VLOOKUP ay isa pang lookup function na magagamit mo sa Excel, ngunit hindi tulad ng MATCH na nangangailangan ng INDEX para sa mga advanced na paghahanap, ang VLOOKUP formula ay nangangailangan lamang ng isang function na iyon.
Ang Microsoft Excel ISBLANK function ay maaaring maging isang mahusay na tool para sa paghahanap ng mga butas sa iyong database. Narito kung paano ito gamitin nang buo, na may kondisyong pag-format at hindi
Microsoft Excel ay nagbibigay ng mga function hindi lamang para sa mga numero, kundi pati na rin para sa text. Tutulungan ka ng Excel MID function na makakuha ng text mula sa gitna ng mga nilalaman ng cell. Narito kung paano ito gamitin
Ang Excel INDEX function ay isang formula na kumukuha ng value mula sa isang listahan o talahanayan batay sa data ng lokasyon na iyong inilagay sa formula. Narito kung paano gamitin ang INDEX formula, kasama ang mga halimbawa
Alamin kung paano gamitin ang Excel CONCATENATE function at operator upang mabilis na pagsamahin ang maraming cell ng data sa isa. Na-update upang isama ang Excel 2019
