Юу мэдэх вэ
- INDEX функцийг дангаар нь ашиглаж болох ч дотор нь MATCH функцийг оруулснаар ахисан хайлтыг бий болгоно.
- Энэ үүрлэсэн функц нь VLOOKUP-аас илүү уян хатан бөгөөд илүү хурдан үр дүнг гаргах боломжтой.
Энэ нийтлэлд Excel 2019 болон Microsoft 365 зэрэг Excel-ийн бүх хувилбарт INDEX болон MATCH функцийг хэрхэн хамтад нь ашиглах талаар тайлбарласан болно.
INDEX болон TATCH функц гэж юу вэ?
INDEX болон MATCH нь Excel хайлтын функцууд юм. Эдгээр нь бие даасан байдлаар хэрэглэгдэх бүрэн тусдаа хоёр функц боловч тэдгээрийг нэгтгэж, дэвшилтэт томъёо үүсгэх боломжтой.
INDEX функц нь тодорхой сонголт доторх утга эсвэл утгын лавлагааг буцаана. Жишээлбэл, үүнийг өгөгдлийн багцын хоёр дахь эгнээ эсвэл тав дахь мөр, гурав дахь баганын утгыг олоход ашиглаж болно.
INDEX-г дангаар нь ашиглах боломжтой ч томьёонд MATCH-г оруулах нь үүнийг арай илүү ашигтай болгодог. MATCH функц нь нүднүүдийн муж дахь тодорхой зүйлийг хайж, дараа нь тухайн зүйлийн муж дахь харьцангуй байрлалыг буцаана. Жишээлбэл, тодорхой нэр нь нэрсийн жагсаалтын гурав дахь зүйл гэдгийг тодорхойлоход ашиглаж болно.
INDEX ба MATCH синтакс ба аргументууд
Excel-д ойлгохын тулд хоёр функцийг ингэж бичих шаардлагатай:
=INDEX(массив, мөрийн_тоо, [баганын_тоо])
- массив нь томьёоны ашиглах нүдний муж юм. Энэ нь A1:D5 гэх мэт нэг буюу хэд хэдэн мөр, багана байж болно. Энэ шаардлагатай.
- row_num нь массив дахь 2 эсвэл 18 гэх мэт утгыг буцаах мөр юм. Баганын_тоо байхгүй л бол шаардлагатай.
- баганын_тоо нь массив дахь 1 эсвэл 9 гэх мэт утгыг буцаах багана юм. Энэ нь сонголттой.
=MATCH(хайх_утга, хайлтын_массив, [тохирох_төрөл])
- хайлтын_утга нь хайлтын_массив дотор тааруулахыг хүсэж буй утга юм. Энэ нь гараар бичсэн тоо, текст эсвэл логик утга байж болно. Энэ шаардлагатай.
- lookup_array нь харах нүднүүдийн муж юм. Энэ нь A2:D2 эсвэл G1:G45 гэх мэт нэг мөр эсвэл нэг багана байж болно. Энэ шаардлагатай.
- тохирох_төрөл нь -1, 0 эсвэл 1 байж болно. Энэ нь хайлтын_утга нь хайлтын_массив дахь утгуудтай хэрхэн таарч байгааг тодорхойлдог (доороос харна уу). Энэ аргументыг орхигдуулсан тохиолдолд өгөгдмөл утга нь 1 байна.
| Ямар тохирох төрлийг ашиглах вэ | |||
|---|---|---|---|
| Тохирох төрөл | Энэ юу хийдэг вэ | Дүрэм | Жишээ |
| 1 | Хайлтын_утгаас бага буюу тэнцүү хамгийн том утгыг олно. | Хайлтын_массивийн утгуудыг өсөх дарааллаар байрлуулах ёстой (жишээ нь: -2, -1, 0, 1, 2; эсвэл A-Z; эсвэл ХУДАЛ, ҮНЭН. | хайлтын_утга нь 25 боловч хайлтын_массив -д байхгүй байгаа тул оронд нь 22 гэх мэт дараагийн хамгийн бага тооны байрлалыг буцаана. |
| 0 | Хайлтын_утгатай яг тэнцүү эхний утгыг олно. | Хайлтын_массивын утгууд ямар ч дарааллаар байж болно. | хайлтын_утга 25 тул 25-ын байрлалыг буцаана. |
| -1 | Хайлтын_утгаас их буюу тэнцүү хамгийн бага утгыг олно. | Хайлтын_массивийн утгуудыг буурах дарааллаар байрлуулах ёстой (жишээ нь: 2, 1, 0, -1, -2). | хайлтын_утга 25 боловч хайлтын_массив дээр дутуу байгаа тул оронд нь 34 гэх мэт дараагийн хамгийн том тооны байрлалыг буцаана. |
Тоонуудтай харьцах, ойролцоо тоонууд хэвийн байх зэрэг масштабын дагуу ойролцоогоор хайлт хийх шаардлагатай үед 1 эсвэл -1-ийг ашиглана уу. Гэхдээ хэрэв та тохирох_төрлийг заагаагүй бол өгөгдмөл нь 1 байх бөгөөд хэрэв та яг таарахыг үнэхээр хүсч байвал үр дүнг гажуудуулж болзошгүйг санаарай.
Жишээ INDEX болон TATCH томьёо
Бид INDEX болон MATCH-ийг нэг томьёо болгон хэрхэн нэгтгэх талаар үзэхийн өмнө эдгээр функцууд хэрхэн бие даан ажилладгийг ойлгох хэрэгтэй.
INDEX Жишээ
=ИНДЕКС(A1:B2, 2, 2)
=ИНДЕКС(A1:B1, 1)
=ИНДЕКС(2:2, 1)=ИНДЕКС(B1:B2, 1)
Image Энэ эхний жишээнд бид өөр өөр утгыг авахын тулд ашиглаж болох дөрвөн INDEX томьёо байна:
- =INDEX(A1:B2, 2, 2) хоёр дахь багана болон хоёр дахь эгнээний утгыг олохын тулд A1:B2-г харна. Энэ нь Stacy.
- =INDEX(A1:B1, 1) эхний баганад байгаа утгыг олохын тулд A1:B1-ийг харна. Энэ нь Jon.
- =INDEX(2:2, 1) эхний баганад байгаа утгыг олохын тулд хоёр дахь эгнээний бүх зүйлийг харна.
- =INDEX(B1:B2, 1) эхний эгнээний утгыг B1:B2-г харна. Энэ нь Эми.
MAÇ-ийн жишээ
=MATCH("Stacy", A2:D2, 0)
=МАТЧ(14, D1:D2)
=МАТЧ(14, D1:D2, -1)=ТОХИРУУЛАХ(13, A1:D1, 0)
Image Энд MATCH функцийн дөрвөн хялбар жишээ байна:
- =MATCH("Stacy", A2:D2, 0) нь A2:D2 мужид Stacy-г хайж байгаа бөгөөд үр дүнд нь 3-ыг буцаана.
- =MATCH(14, D1:D2) D1:D2 мужаас 14-ийг хайж байгаа боловч хүснэгтэд олдоогүй тул MATCH дараагийн хамгийн том утгыг олно энэ нь 14-ээс бага буюу тэнцүү, энэ тохиолдолд хайлтын_массивын 1-р байрлалд байгаа 13 байна.
- =MATCH(14, D1:D2, -1) нь дээрх томьёотой ижил боловч массив нь -1-ийн шаардагддаг шиг буурах дарааллаар байхгүй тул, бидэнд алдаа гарлаа.
- =MATCH(13, A1:D1, 0) хуудасны эхний мөрөнд 13-ыг хайж байгаа бөгөөд энэ нь энэ массивын дөрөв дэх зүйл учраас 4-ийг буцаана.
ИНДЕКС-ТОХИРУУЛАХ Жишээ
Бид INDEX болон MATCH-ийг нэг томьёонд нэгтгэж болох хоёр жишээг энд үзүүлэв:
Хүснэгтээс нүдний лавлагаа олох
=ИНДЕКС(B2:B5, ТААРУУЛАХ(F1, A2:A5))
Энэ жишээ нь MATCH томьёог INDEX томьёо дотор оруулж байна. Зорилго нь барааны дугаарыг ашиглан зүйлийн өнгийг тодорхойлох явдал юм.
Хэрэв та зургийг харвал "Тусгаарлагдсан" мөрүүдээс томьёо нь дангаараа хэрхэн бичигдэхийг харж болно, гэхдээ бид тэдгээрийг үүрлэж байгаа тул ийм зүйл болж байна:
- MATCH(F1, A2:A5) нь A2:A5 өгөгдлийн багцаас F1 утгыг (8795) хайж байна. Хэрэв бид баганыг доош нь тоолвол энэ нь 2 байгааг харж болно, тиймээс MATCH функц саяхан үүнийг олж мэдсэн.
- Бид эцсийн дүндээ тухайн баганаас утгыг хайж байгаа тул INDEX массив нь B2:B5 байна.
- INDEX функцийг одоо ингэж дахин бичиж болно, учир нь 2 нь MATCH олсон: INDEX(B2:B5, 2, [баганын_тоо]).
- баганын_тоо нь сонголттой тул бид үүнийг хасаж дараах байдлаар үлдээж болно: INDEX(B2:B5, 2).
- Тиймээс одоо энэ нь B2:B5-ийн улаан 2 дахь зүйлийн утгыг олж байгаа энгийн INDEX томьёо шиг байна.
Мөр болон баганын гарчигаар хайх
=ИНДЕКС(B2:E13, ТААРУУЛАХ(G1, A2:A13, 0), ТААРУУЛАХ(G2, B1:E1, 0))
MATCH болон INDEX-н энэ жишээн дээр бид хоёр талын хайлт хийж байна. 5-р сард бид ногоон бүтээгдэхүүнээс хэр их мөнгө олсныг харах санаа юм. Энэ нь дээрх жишээтэй үнэхээр төстэй боловч INDEX-д нэмэлт MATCH томьёо оруулсан байна.
- MATCH(G1, A2:A13, 0) нь энэ томьёогоор шийдэгдсэн эхний зүйл юм. Энэ нь тодорхой утгыг авахын тулд A2:A13-аас G1 ("5-р сар" гэсэн үг) хайж байна. Бид энд харагдахгүй байна, гэхдээ энэ нь 5.
- MATCH(G2, B1:E1, 0) нь хоёр дахь TATCH томьёо бөгөөд энэ нь эхнийхтэй үнэхээр төстэй боловч оронд нь G2 ("Ногоон" гэсэн үг) хайж байна.) баганын гарчигт B1:E1. Энэ нь 3 болж шийдэгддэг.
- Бид одоо юу болж байгааг төсөөлөхийн тулд INDEX томьёог ингэж дахин бичиж болно: =INDEX(B2:E13, 5, 3). Энэ нь хүснэгтийг бүхэлд нь, B2:E13, тав дахь мөр ба гурав дахь баганыг хайж байгаа бөгөөд $180. буцаана.
ТАЙЛБАР болон ИНДЕКСИЙН дүрэм
Эдгээр функцээр томьёо бичихдээ хэд хэдэн зүйлийг анхаарах хэрэгтэй:
- MATCH том жижиг үсгийг том жижиг үсэг харгалздаггүй тул текстийн утгыг тааруулах үед том, жижиг үсгүүдийг ижилхэн авч үзнэ.
- MATCH нь олон шалтгааны улмаас Үгүй гэж буцаана: хэрэв тохирох_төрөл 0 бол хайлтын_утга олдохгүй бол тохирох_төрөл -1 ба хайлтын_массив буурах дарааллаар биш бол, тохирох_төрөл 1 ба хайлтын_массив өсөхгүй байвал эрэмбэлэх, хэрэв хайлтын_массив нь нэг мөр эсвэл багана биш бол.
- Хэрэв тохирох_төрөл 0, хайлтын_утга нь текстийн тэмдэгт бол хайлтын_утга аргументад орлуулагч тэмдэгт ашиглаж болно. Асуултын тэмдэг нь дурын тэмдэгттэй таарч, од нь тэмдэгтүүдийн дарааллаар таарч байна (жишээ нь.ж., =МАТЧ("Jo", 1:1, 0)). MATCH-г ашиглан асуултын тэмдэг эсвэл одоор олохын тулд эхлээд ~ гэж бичнэ үү.
- INDEX нь REF! хэрэв мөрийн_тоо болон баганын_тоо нь массив доторх нүд рүү заагаагүй бол.
Холбогдох Excel функцууд
MATCH функц нь LOOKUP-тай төстэй боловч MATCH нь тухайн зүйлийн оронд тухайн зүйлийн байрлалыг буцаана.
VLOOKUP бол Excel-д ашиглаж болох өөр хайлтын функц боловч нарийн хайлт хийхэд INDEX шаарддаг MATCH-аас ялгаатай нь VLOOKUP томьёо нь зөвхөн нэг функцийг шаарддаг.
