Яагаад INDEX болон MATCH нь Excel-ийн VLOOKUP-ээс илүү байдаг вэ?

Бид анхлан суралцагчдад VLOOKUP (англи хэлээр VLOOKUP, товчлол нь "босоо хайлтын функц" гэсэн үг)-ийн үндсэн функцуудыг хэрхэн ашиглах талаар тайлбарласан. Туршлагатай хэрэглэгчдэд хэд хэдэн илүү төвөгтэй томъёог харуулсан.

Мөн энэ нийтлэлд бид босоо хайлттай ажиллах өөр аргын талаар мэдээлэл өгөхийг хичээх болно.

Та "Яагаад хэрэгтэй байна вэ?" Гэж гайхаж магадгүй юм. Хайлтын бүх боломжит аргуудыг харуулахын тулд энэ нь зайлшгүй шаардлагатай. Нэмж дурдахад олон тооны VLOOKUP хязгаарлалтууд нь хүссэн үр дүнд хүрэхэд саад болдог. Үүнтэй холбогдуулан INDEX( ) MATCH( ) нь илүү ажиллагаатай, олон талт бөгөөд тэдгээр нь бас бага хязгаарлалттай байдаг.

INDEX TATCH-ийн үндсэн ойлголт

Энэхүү гарын авлагын зорилго нь энэ функц нь хэр сайн болохыг харуулах явдал юм Түүний үйл ажиллагааны зарчмуудын талаархи үндсэн мэдээллийг авч үзье. Мөн бид жишээг үзүүлж, яагаад VLOOKUP () -ээс илүү дээр болохыг авч үзэх болно.

INDEX функцийн синтакс ба хэрэглээ

Энэ функц нь багана эсвэл мөрийн дугаар дээр үндэслэн заасан хайлтын хэсгүүдээс хүссэн утгыг олоход тусална. Синтакс:

=INDEX(массив, мөрийн дугаар, баганын дугаар):

• массив - хайлт хийх газар;
• мөрийн дугаар – заасан массиваас хайх мөрийн дугаар. Хэрэв мөрийн дугаар тодорхойгүй бол баганын дугаарыг зааж өгөх ёстой;
• баганын дугаар – заасан массиваас олох баганын дугаар. Хэрэв утга нь тодорхойгүй бол мөрийн дугаар шаардлагатай.

Энгийн томъёоны жишээ:

=ИНДЕКС(A1:S10,2,3)

Функц нь A1-ээс C10 хүртэлх мужид хайлт хийх болно. Тоонууд нь аль мөр (2) ба баганаас (3) хүссэн утгыг харуулахыг харуулна. Үр дүн нь C2 нүд болно.

Маш энгийн, тийм үү? Гэхдээ та бодит баримт бичигтэй ажиллахдаа баганын дугаар эсвэл нүдний талаархи мэдээлэлтэй байх магадлал багатай. MATCH() функц нь үүнд зориулагдсан юм.

MATCH функцийн синтакс ба хэрэглээ

MATCH() функц нь хүссэн утгыг хайж, заасан хайлтын хэсэгт түүний ойролцоо тоог харуулна.

Searchpos() синтакс дараах байдалтай байна.

=MATCH(хайх утга, хайх массив, тохирох төрөл)

• хайлтын утга - олох тоо эсвэл текст;
• хайсан массив - хайлт хийх газар;
• тохирох төрөл - яг утгыг эсвэл түүнд хамгийн ойр утгыг хайх эсэхийг заана:
  • 1 (эсвэл утга заагаагүй) – заасан утгатай тэнцүү буюу түүнээс бага хамгийн том утгыг буцаана;
  • 0 - хайсан утгатай яг таарч байгааг харуулна. INDEX() MATCH() хослолд танд бараг үргэлж яг таарч байх шаардлагатай тул бид 0 гэж бичнэ;
  • -1 – Томъёонд заасан утгаас их буюу тэнцүү байх хамгийн бага утгыг харуулна. Эрэмбэлэх нь буурах дарааллаар явагдана.

Жишээлбэл, B1:B3 мужид Нью-Йорк, Парис, Лондон зэрэг газрууд бүртгэгдсэн байна. Лондон жагсаалтын гуравдугаарт орсон тул доорх томьёо нь 3-ын тоог харуулна.

=ЭЗҮҮЛЭХ(Лондон,B1:B3,0)

INDEX MATCH функцтэй хэрхэн ажиллах талаар 

Эдгээр функцүүдийн хамтарсан ажил ямар зарчмаар бүтээгдсэнийг та аль хэдийн ойлгож эхэлсэн байх. Товчхондоо тэгвэл INDEX() нь заасан мөр, баганын дундаас хүссэн утгыг хайдаг. MATCH() нь эдгээр утгуудын тоог харуулна:

=INDEX(утга буцаах багана, MATCH(хайх утга, хайх багана, 0))

Энэ нь хэрхэн ажилладагийг ойлгоход хэцүү хэвээр байна уу? Магадгүй жишээ нь илүү сайн тайлбарлах болно. Танд дэлхийн нийслэлүүд болон тэдгээрийн хүн амын жагсаалт байна гэж бодъё:

Тодорхой нийслэл, жишээлбэл, Японы нийслэл хотын хүн амын тоог мэдэхийн тулд бид дараахь томъёог ашиглана.

=INDEX(C2:C10, TATCH(Япон, A2:A10,0))

Тайлбар:

• MATCH() функц нь A2:A10 массиваас “Япон” гэсэн утгыг хайж 3 дугаарыг буцаана, учир нь Япон жагсаалтын гурав дахь утга юм. 
• Энэ тоо гарч байнамөрийн дугаар” гэж INDEX() томьёо доторх ба функцэд энэ мөрөөс утгыг хэвлэхийг хэлнэ.

Тэгэхээр дээрх томьёо нь стандарт томьёо болно ИНДЕКС(C2:C10,3). Томъёо нь C2 нүднээс C10 хүртэл хайлт хийж, энэ муж дахь гурав дахь нүд, өөрөөр хэлбэл С4-ээс өгөгдлийг буцаана, учир нь тооллого хоёр дахь эгнээнээс эхэлдэг.

Томъёонд хотын нэрийг бичихийг хүсэхгүй байна уу? Дараа нь үүнийг дурын нүдэнд бичээд F1 гэж хэлээд MATCH() томьёоны лавлагаа болгон ашиглаарай. Мөн та динамик хайлтын томьёотой болно:

=ИНДЕКС(С2:С10, ПОИСКПОЗ( )(F1,A2:A10,0))

Чухал! Мөрний тоо массив INDEX() нь мөрийн тоотой ижил байх ёстой массив гэж үздэг MATCH() дээр, эс бөгөөс та буруу үр дүнд хүрэх болно.

Түр хүлээнэ үү, яагаад VLOOKUP() томъёог ашиглаж болохгүй гэж?

=VLOOKUP(F1, A2:C10, 3, Худал)

 INDEX MATCH-ийн энэ бүх нарийн төвөгтэй байдлыг олж мэдэх гэж цаг үрэх нь ямар учиртай юм бэ?

Энэ тохиолдолд аль функцийг ашиглах нь хамаагүй. Энэ бол INDEX() болон MATCH() функцууд хэрхэн хамтран ажилладагийг ойлгох жишээ юм. Бусад жишээнүүд нь VLOOKUP хүчингүй болсон тохиолдолд эдгээр функцууд юу болохыг харуулах болно. 

INDEX TATCH эсвэл VLOOKUP

Хайлтын ямар томьёог ашиглахаа шийдэхдээ INDEX() болон MATCH() нь VLOOKUP-аас хамаагүй давуу гэдэгтэй олон хүн санал нийлдэг. Гэсэн хэдий ч олон хүмүүс VLOOKUP()-г ашигладаг хэвээр байна. Нэгдүгээрт, VLOOKUP() нь илүү хялбар, хоёрдугаарт, хэрэглэгчид INDEX() болон MATCH()-тэй ажиллахын бүх давуу талыг бүрэн ойлгодоггүй. Энэ мэдлэггүйгээр хэн ч нарийн төвөгтэй системийг судлахад цаг заваа зориулахыг зөвшөөрөхгүй.

VLOOKUP()-аас INDEX() ба MATCH()-ын гол давуу талууд энд байна:

• Баруунаас зүүн тийш хайх. VLOOKUP() баруунаас зүүн тийш хайх боломжгүй тул таны хайж буй утгууд үргэлж хүснэгтийн зүүн талын баганад байх ёстой. Гэхдээ INDEX() болон MATCH() үүнийг асуудалгүйгээр зохицуулж чадна. Энэ нийтлэл нь практик дээр хэрхэн харагдахыг танд хэлэх болно: зүүн талд хүссэн утгыг хэрхэн олох талаар.

1. Багануудыг аюулгүйгээр нэмэх эсвэл хасах. VLOOKUP() амжилттай ажиллахын тулд VLOOKUP()-д тодорхой баганын дугаар шаардлагатай тул багана хасах эсвэл нэмэх үед VLOOKUP() томьёо нь буруу үр дүнг харуулж байна. Мэдээжийн хэрэг, багана нэмэх эсвэл хасах үед тэдгээрийн тоо өөрчлөгддөг. 

Мөн INDEX() болон MATCH() томьёонд тус тусад нь багана биш харин баганын мужийг зааж өгсөн болно. Үүний үр дүнд та томьёо болгон шинэчлэх шаардлагагүйгээр багана нэмэх, хасах боломжтой.

1. Хайлтын хэмжээ хязгааргүй. VLOOKUP()-г ашиглах үед хайлтын шалгуурын нийт тоо 255 тэмдэгтээс хэтрэхгүй байх ёстой, эс тэгвээс та #VALUE авах болно! Хэрэв таны өгөгдөл олон тооны тэмдэгт агуулсан бол INDEX() болон MATCH() нь хамгийн сайн сонголт юм.
2. Өндөр боловсруулалтын хурд. Хэрэв таны ширээ харьцангуй жижиг бол та ямар ч ялгааг анзаарахгүй байх магадлалтай. Гэхдээ хэрэв хүснэгтэд хэдэн зуун, мянган мөр байгаа бөгөөд үүний дагуу хэдэн зуун, мянган томъёо байгаа бол INDEX () ба MATCH () нь VLOOKUP () -ээс хамаагүй хурдан даван туулах болно. Баримт нь Excel нь хүснэгтийг бүхэлд нь боловсруулахын оронд зөвхөн томъёонд заасан баганыг боловсруулах болно. 

Хэрэв таны ажлын хуудас VLOOKUP() болон SUM() зэрэг олон тооны томьёо агуулж байвал VLOOKUP()-ийн гүйцэтгэлийн нөлөөлөл онцгой мэдэгдэхүйц байх болно. Массив дахь утга тус бүрийг задлан шинжлэхийн тулд VLOOKUP() функцуудыг тусад нь шалгах шаардлагатай. Тиймээс Excel нь асар их хэмжээний мэдээллийг боловсруулах ёстой бөгөөд энэ нь ажлыг ихээхэн удаашруулдаг.

Томъёоны жишээ 

Эдгээр функцүүдийн ашиг тусыг бид аль хэдийн олж мэдсэн тул бид хамгийн сонирхолтой хэсэг болох мэдлэгийг практикт ашиглах руу шилжиж болно.

Баруунаас зүүн тийш хайх томъёо

Өмнө дурьдсанчлан VLOOKUP хайлтын энэ хэлбэрийг гүйцэтгэх боломжгүй. Тиймээс, хэрэв хүссэн утгууд зүүн талын баганад байхгүй бол VLOOKUP() үр дүнг гаргахгүй. INDEX () ба MATCH () функцууд нь илүү уян хатан байдаг бөгөөд утгуудын байршил нь тэдний ажиллахад тийм ч чухал үүрэг гүйцэтгэдэггүй.

Жишээлбэл, бид хүснэгтийнхээ зүүн талд зэрэглэлийн баганыг нэмж, манай улсын нийслэл хүн амын тоогоор хэддүгээр байр эзэлдэг болохыг олж мэдэхийг хичээх болно.

G1 нүдэнд бид олох утгыг бичээд C1:C10 мужаас хайхдаа дараах томьёог ашиглан A2:A10-аас харгалзах утгыг буцаана.

=ИНДЕКС(А2:А10, ПОИСКПОЗ(G1,C1:C10,0))

Шуурхай. Хэрэв та энэ томьёог олон нүдэнд ашиглахаар төлөвлөж байгаа бол үнэмлэхүй хаягжилтыг ашиглан мужуудыг засахаа мартуузай (жишээлбэл, $A$2: $A$10 ба $C$2: 4C$10).

ИНДЕКС ИЛҮҮ ИЛЧИЛСЭН БАЙНА  багана болон мөрөөр хайх

Дээрх жишээнүүдэд бид эдгээр функцийг VLOOKUP()-ийн орлуулалт болгон ашиглаж, урьдчилан тодорхойлсон мөрийн мужаас утгыг буцаана. Гэхдээ матриц эсвэл хоёр талт хайлт хийх шаардлагатай бол яах вэ?

Энэ нь төвөгтэй мэт санагдаж байгаа ч ийм тооцооллын томъёо нь стандарт INDEX() MATCH() томьёотой төстэй бөгөөд зөвхөн нэг ялгаа байна: MATCH() томьёог хоёр удаа ашиглах ёстой. Эхний удаад мөрийн дугаар, хоёр дахь удаагаа баганын дугаар авах:

=INDEX(массив, MATCH(босоо хайлтын утга, хайлтын багана, 0), MATCH(хэвтээ хайлтын утга, хайлтын мөр, 0))

Доорх хүснэгтийг хараад томьёо гаргахыг хичээцгээе INDEX() EXPRESS() EXPRESS() сонгосон жилийн тодорхой улсын хүн ам зүйн мэдээллийг харуулахын тулд.

Зорилтот улс нь G1 нүдэнд (босоо хайлт), зорилтот жил нь G2 нүдэнд (хэвтээ хайх) байна. Томъёо нь дараах байдлаар харагдах болно.

=ИНДЕКС(B2:D11, ПОИСКПОЗ(G1,A2:A11,0), ПОИСКПОЗ(G2,B1:D1,0))

Энэ томъёо хэрхэн ажилладаг

Бусад нарийн төвөгтэй томъёонуудын нэгэн адил тэдгээрийг бие даасан тэгшитгэл болгон задлах замаар ойлгоход хялбар байдаг. Дараа нь та функц бүр юу хийдгийг ойлгох болно:

• ТОХИРУУЛАХ(G1,A2:A11,0) – A1:A2 мужаас (G11) утгыг хайж, энэ утгын тоог харуулна, манай тохиолдолд 2 байна;
• ХАЙХ(G2,B1:D1,0) – B2:D1 мужаас (G1) утгыг хайна. Энэ тохиолдолд үр дүн нь 3 байна.

Олдсон мөр, баганын дугаарыг INDEX() томъёоны харгалзах утга руу илгээнэ.

=ИНДЕКС(B2:D11,2,3)

Үүний үр дүнд бид B2:D3 муж дахь 2 мөр, 11 баганын огтлолцол дахь нүдэнд байгаа утгыг олж авна. Томъёо нь D3 нүдэнд байгаа хүссэн утгыг харуулна.

INDEX болон MATCH ашиглан олон нөхцөлөөр хай

Хэрэв та манай VLOOKUP() гарын авлагыг уншсан бол та олон хайлтын томъёог туршиж үзсэн байх. Гэхдээ энэ хайлтын арга нь нэг чухал хязгаарлалттай байдаг - туслах багана нэмэх шаардлагатай.

Гэхдээ сайн мэдээ гэвэл энэ INDEX() болон MATCH()-ийн тусламжтайгаар та ажлын хуудсаа засварлах, өөрчлөх шаардлагагүйгээр олон нөхцөл хайх боломжтой.

INDEX() MATCH()-ын олон нөхцөлт хайлтын ерөнхий томъёо энд байна:

{=ИНДЕКС(диапазон поиска, ПОИСКПОЗ(1,условие1=диапазон1)*(условвие2=диапазон2),0))}

Тэмдэглэл: Энэ томьёог гарын товчлолтой хамт ашиглах ёстой CTRL+SHIFT+ENTER.

Та 2 нөхцөл дээр үндэслэн хайж буй үнэ цэнийг олох хэрэгтэй гэж бодъё: Худалдан авагч и Бүтээгдэхүүн.

Үүнд дараах томъёо шаардлагатай.

=ИНДЕКС(С2:С10, ПОИСКПОЗ(1,(F1=A2:A10)*(F2=B1:B10),0))

Энэ томъёонд C2:C10 нь хайлт хийх муж, F1 - энэ нөхцөл байдал, A2:A10 - нөхцөлийг харьцуулах муж, F2 - нөхцөл 2, V2: V10 дугаартай – 2-р нөхцөлийг харьцуулах муж.

Ажлын төгсгөлд хослолыг томъёогоор дарахаа бүү мартаарай CTRL+SHIFT+ENTER – Excel нь жишээнд үзүүлсэн шиг буржгар хаалт бүхий томъёог автоматаар хаах болно.

Хэрэв та ажилдаа массивын томьёо ашиглахыг хүсэхгүй байгаа бол томьёонд өөр INDEX() нэмээд ENTER дарвал жишээн дээрх шиг харагдах болно.

Эдгээр томъёо хэрхэн ажилладаг

Энэ томьёо нь стандарт INDEX() MATCH() томьёотой адилхан ажилладаг. Олон нөхцлүүдийг хайхын тулд та зүгээр л зөв, буруу хувь хүний ​​нөхцөлийг илэрхийлэх олон Хуурамч ба Үнэн нөхцөлийг бий болгоно. Дараа нь эдгээр нөхцөл нь массивын харгалзах бүх элементүүдэд хамаарна. Томъёо нь Худал ба Үнэн аргументуудыг 0 ба 1 болгон хувиргаж, 1 нь мөрөнд олдсон тохирох утгууд болох массивыг гаргадаг. MATCH() нь 1-тэй тохирч буй эхний утгыг олоод INDEX() томьёо руу дамжуулна. Мөн энэ нь эргээд хүссэн баганаас заасан мөрөнд аль хэдийн хүссэн утгыг буцаана.

Массивгүй томьёо нь INDEX()-ийн тэдгээрийг өөрөө зохицуулах чадвараас хамаарна. Томъёоны хоёр дахь INDEX() нь худал (0)-тэй таарч байгаа тул массивыг бүхэлд нь эдгээр утгуудын хамт MATCH() томьёо руу дамжуулдаг. 

Энэ бол энэ томъёоны цаадах логикийн талаар нэлээд урт тайлбар юм. Дэлгэрэнгүй мэдээллийг " нийтлэлээс уншина ууОлон нөхцөлтэй INDEX TATCH".

INDEX болон MATCH-д AVERAGE, MAX, MIN

Excel нь дундаж, хамгийн их, минимумыг олох өөрийн гэсэн тусгай функцтэй. Гэхдээ эдгээр утгуудтай холбоотой нүднээс өгөгдөл авахыг хүсвэл яах вэ? Энэ тохиолдолд AVERAGE, MAX, MIN-ийг INDEX болон MATCH-тай хамт ашиглах ёстой.

INDEX MATCH ба MAX

D баганаас хамгийн том утгыг олж, C баганад харуулахын тулд дараах томъёог ашиглана уу. 

=ИНДЕКС(С2:С10, ПОИСКПОЗ(МАКС(D2:D10),D2:D10,0))

INDEX MATCH болон MIN

D баганаас хамгийн бага утгыг олж, C баганад харуулахын тулд дараах томъёог ашиглана уу.

=ИНДЕКС(С2:С10,ПОИСКПОЗ(МИН(D2:D10),D2:D10,0))

SERPENT INDEX болон SERPENT

D баганаас дундаж утгыг олж, энэ утгыг C дээр харуулахын тулд:

=ИНДЕКС(С2:С10,ПОИСКПОЗ(СРЗНАЧ(D2:D10),D2:D10,-1))

Таны өгөгдөл хэрхэн бичигдсэнээс хамааран MATCH()-ын гурав дахь аргумент нь 1, 0 эсвэл -1 байна:

• хэрэв баганыг өсөх дарааллаар эрэмбэлсэн бол 1-ийг тохируулна уу (дараа нь томъёо нь дундаж утгаас бага буюу тэнцүү байх хамгийн их утгыг тооцоолох болно);
• хэрэв эрэмбэ буурч байвал -1 (томъёо нь дунджаас их буюу тэнцүү байх хамгийн бага утгыг гаргана);
• Хэрэв хайлтын массив нь дундажтай яг тэнцүү утгыг агуулж байвал 0 болгож тохируулна уу. 

 Бидний жишээн дээр популяцийг буурах дарааллаар эрэмбэлсэн тул -1-ийг тавьдаг. Хүн амын тоо (13,189) дундаж утгатай (000) хамгийн ойр байгаа тул үр дүн нь Токио юм.

VLOOKUP() нь мөн ийм тооцоолол хийх боломжтой, гэхдээ зөвхөн массивын томъёогоор: ДУНДЖ, MIN болон MAX-тай VLOOKUP.

INDEX TATCH болон ESND/IFERROR

Хэрэв томьёо нь хүссэн утгыг олж чадахгүй бол алдаа гаргадгийг та аль хэдийн анзаарсан байх # Н / А. Та стандарт алдааны мессежийг илүү мэдээлэлтэй зүйлээр сольж болно. Жишээлбэл, аргументыг томъёонд тохируулна уу XNUMX-д:

=ЕСНД(ИНДЕКС(С2:С10,ПОИСКПОЗ(F1,A2:A10,0)),значение не найдено)

Энэ томьёогоор, хэрэв та хүснэгтэд байхгүй өгөгдлийг оруулбал маягт нь танд заасан мессежийг өгөх болно.

Хэрэв та бүх алдааг барихыг хүсч байвал бусад тохиолдолд XNUMX-д ашиглаж болно АЛДАА:

=ТОГТОЛБОР(ИНДЕКС(C2:C10,MATCH(F1,A2:A10,0)), "Ямар нэг алдаа гарлаа!")

Гэхдээ стандарт алдаа нь томъёоны зөрчлийг мэдээлдэг тул алдааг ийм байдлаар далдлах нь тийм ч сайн санаа биш гэдгийг санаарай.

INDEX MATCH() функцийг ашиглах заавар танд тус болсон гэж найдаж байна.