Nima uchun INDEX va MATCH Excelda VLOOKUP dan yaxshiroq

Biz avval yangi boshlanuvchilarga VLOOKUP (inglizcha VLOOKUP, qisqartma “vertikal qidiruv funksiyasi” degan maʼnoni anglatadi) asosiy funksiyalaridan qanday foydalanishni tushuntirib berdik. Tajribali foydalanuvchilarga yana bir nechta murakkab formulalar ko'rsatildi.

Va ushbu maqolada vertikal qidiruv bilan ishlashning yana bir usuli haqida ma'lumot berishga harakat qilamiz.

Siz hayron bo'lishingiz mumkin: "Bu nima uchun kerak?". Va bu barcha mumkin bo'lgan qidiruv usullarini ko'rsatish uchun kerak. Bundan tashqari, ko'p VLOOKUP cheklovlari ko'pincha kerakli natijaga erishishga to'sqinlik qiladi. Shu nuqtai nazardan, INDEX( ) MATCH( ) ancha funktsional va xilma-xil bo'lib, ularda ham kamroq cheklovlar mavjud.

INDEX MATCH

Ushbu qo'llanmaning maqsadi bu xususiyat qanchalik yaxshi ekanligini ko'rsatish bo'lgani uchun, biz Keling, uning ishlash tamoyillari haqidagi asosiy ma'lumotlarni ko'rib chiqaylik. Va biz misollarni ko'rsatamiz va nima uchun bu VLOOKUP () dan yaxshiroq ekanligini ko'rib chiqamiz.

INDEX funksiyasi sintaksisi va ishlatilishi

Bu funksiya ustun yoki satr raqami asosida belgilangan qidiruv maydonlari orasidan kerakli qiymatni topishga yordam beradi. Matnni sintaksisi:

=INDEX(massiv, satr raqami, ustun raqami):

• massiv - qidiruv amalga oshiriladigan maydon;
• satr raqami - belgilangan massivda qidiriladigan qatorning raqami. Agar qator raqami noma'lum bo'lsa, ustun raqami ko'rsatilishi kerak;
• ustun raqami - belgilangan massivda topiladigan ustunning raqami. Agar qiymat noma'lum bo'lsa, qator raqami talab qilinadi.

Oddiy formulaga misol:

=INDEX(A1:S10,2,3)

Funktsiya A1 dan C10 oralig'ida qidiradi. Raqamlar qaysi qatordan (2) va ustundan (3) kerakli qiymatni ko'rsatishni ko'rsatadi. Natijada C2 hujayra bo'ladi.

Juda oddiy, to'g'rimi? Ammo haqiqiy hujjatlar bilan ishlaganingizda, ustun raqamlari yoki kataklari haqida ma'lumotga ega bo'lishingiz dargumon. MATCH() funksiyasi aynan shu maqsadda.

MATCH funksiyasi sintaksisi va ishlatilishi

MATCH() funksiyasi kerakli qiymatni qidiradi va belgilangan qidiruv sohasida uning taxminiy sonini ko'rsatadi.

Searchpos() sintaksisi quyidagicha ko'rinadi:

=MATCH(qidiriladigan qiymat, izlash uchun massiv, moslik turi)

• qidiruv qiymati - topiladigan raqam yoki matn;
• qidiruv massivi - qidiruv amalga oshiriladigan maydon;
• moslik turi - aniq qiymatni yoki unga eng yaqin qiymatlarni izlash kerakligini belgilaydi:
  • 1 (yoki qiymat belgilanmagan) - belgilangan qiymatga teng yoki undan kichik bo'lgan eng katta qiymatni qaytaradi;
  • 0 - qidirilayotgan qiymat bilan aniq moslikni ko'rsatadi. INDEX() MATCH() kombinatsiyasida sizga deyarli har doim aniq moslik kerak bo'ladi, shuning uchun biz 0 yozamiz;
  • -1 - formulada ko'rsatilgan qiymatdan katta yoki unga teng bo'lgan eng kichik qiymatni ko'rsatadi. Saralash kamayish tartibida amalga oshiriladi.

Masalan, B1:B3 oralig'ida Nyu-York, Parij, London ro'yxatga olingan. Quyidagi formula 3 raqamini ko'rsatadi, chunki London ro'yxatda uchinchi o'rinda turadi:

=EXPOSE(London,B1:B3,0)

INDEX MATCH funksiyasi bilan qanday ishlash kerak 

Ehtimol, siz ushbu funktsiyalarning birgalikdagi ishi qurilgan printsipni allaqachon tushunishni boshlagansiz. Qisqasi, keyin INDEX() belgilangan satr va ustunlar orasidan kerakli qiymatni qidiradi. Va MATCH() ushbu qiymatlarning raqamlarini ko'rsatadi:

=INDEX(qiymat qaytariladigan ustun, MATCH(qidiriladigan qiymat, qidirish uchun ustun, 0))

Hali ham uning qanday ishlashini tushunish qiyinmi? Ehtimol, misol yaxshiroq tushuntiradi. Aytaylik, sizda dunyo poytaxtlari va ularning aholisi ro'yxati bor:

Muayyan poytaxt, masalan, Yaponiya poytaxti aholisining sonini bilish uchun biz quyidagi formuladan foydalanamiz:

=INDEX(C2:C10, MATCH(Yaponiya, A2:A10,0))

Izoh:

• MATCH() funksiyasi A2:A10 massivida “Yaponiya” qiymatini qidiradi va 3 raqamini qaytaradi, chunki Yaponiya ro‘yxatdagi uchinchi qiymatdir. 
• Bu raqam ketadiqator raqami” INDEX() formulasida va funksiyaga ushbu qatordan qiymatni chop etishni aytadi.

Shunday qilib, yuqoridagi formula standart formulaga aylanadi INDEX(C2:C10,3). Formula C2 yacheykalaridan C10 gacha izlaydi va ma'lumotlarni ushbu diapazondagi uchinchi katakchadan, ya'ni C4 dan qaytaradi, chunki ortga hisoblash ikkinchi qatordan boshlanadi.

Formulada shahar nomini yozishni xohlamaysizmi? Keyin uni istalgan katakka yozing, F1 deb ayting va uni MATCH() formulasida havola sifatida ishlating. Va siz dinamik qidiruv formulasiga ega bo'lasiz:

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

Muhim! Chiziqlar soni Qator INDEX() qatorlar soni bilan bir xil bo'lishi kerak massiv deb hisoblanadi MATCH() da, aks holda siz noto'g'ri natijaga erishasiz.

Bir daqiqa kutib turing, nima uchun VLOOKUP() formulasidan foydalanmaysiz?

=VLOOKUP(F1, A2:C10, 3, False)

 INDEX MATCH ning barcha murakkabliklarini aniqlashga vaqt sarflashning nima keragi bor?

Bunday holda, qaysi funktsiyadan foydalanish muhim emas. Bu INDEX() va MATCH() funksiyalarining birgalikda qanday ishlashini tushunish uchun faqat bir misol. Boshqa misollar VLOOKUP quvvatsiz bo'lgan holatlarda ushbu funktsiyalar nimaga qodirligini ko'rsatadi. 

INDEX MATCH yoki VLOOKUP

Qaysi qidiruv formulasidan foydalanishni tanlashda ko'pchilik INDEX() va MATCH() VLOOKUP dan ancha ustun ekanligiga qo'shiladi. Biroq, ko'p odamlar hali ham VLOOKUP() dan foydalanadilar. Birinchidan, VLOOKUP() oddiyroq, ikkinchidan, foydalanuvchilar INDEX() va MATCH() bilan ishlashning barcha afzalliklarini to‘liq tushunmaydilar. Bu bilimsiz, hech kim o'z vaqtini murakkab tizimni o'rganishga sarflashga rozi bo'lmaydi.

INDEX() va MATCH() ning VLOOKUP() ga nisbatan asosiy afzalliklari:

• O'ngdan chapga qidiring. VLOOKUP() o'ngdan chapga qidira olmaydi, shuning uchun siz izlayotgan qiymatlar har doim jadvalning eng chap ustunlarida bo'lishi kerak. Lekin INDEX() va MATCH() buni muammosiz hal qila oladi. Ushbu maqola amalda qanday ko'rinishini aytib beradi: chap tomonda kerakli qiymatni qanday topish mumkin.

1. Ustunlarni xavfsiz qo'shish yoki olib tashlash. VLOOKUP() formulasi ustunlarni olib tashlash yoki qo'shishda noto'g'ri natijalarni ko'rsatadi, chunki VLOOKUP() muvaffaqiyatli bo'lishi uchun aniq ustun raqami kerak. Tabiiyki, ustunlar qo'shilganda yoki olib tashlanganda ularning raqamlari ham o'zgaradi. 

INDEX() va MATCH() formulalarida esa alohida ustunlar emas, ustunlar oralig‘i ko‘rsatilgan. Natijada, har safar formulani yangilamasdan ustunlarni xavfsiz qo'shishingiz va olib tashlashingiz mumkin.

1. Qidiruv hajmida cheklovlar yo'q. VLOOKUP() funksiyasidan foydalanganda qidiruv mezonlarining umumiy soni 255 belgidan oshmasligi kerak, aks holda siz #VALUE olasiz! Shunday qilib, agar sizning ma'lumotlaringiz ko'p sonli belgilarni o'z ichiga olsa, INDEX() va MATCH() eng yaxshi variantdir.
2. Yuqori ishlov berish tezligi. Agar sizning jadvallaringiz nisbatan kichik bo'lsa, unda siz hech qanday farqni sezmaysiz. Ammo, agar jadvalda yuzlab yoki minglab qatorlar bo'lsa va shunga mos ravishda yuzlab va minglab formulalar mavjud bo'lsa, INDEX () va MATCH () VLOOKUP () ga qaraganda tezroq ishlaydi. Gap shundaki, Excel butun jadvalni qayta ishlash o'rniga faqat formulada ko'rsatilgan ustunlarni qayta ishlaydi. 

VLOOKUP() ning ishlashga ta'siri, ayniqsa, agar ish sahifangizda VLOOKUP() va SUM() kabi ko'p sonli formulalar mavjud bo'lsa, sezilarli bo'ladi. Massivdagi har bir qiymatni tahlil qilish uchun VLOOKUP() funksiyalarining alohida tekshiruvlari talab qilinadi. Shunday qilib, Excel juda katta hajmdagi ma'lumotlarni qayta ishlashga majbur bo'ladi va bu ishni sezilarli darajada sekinlashtiradi.

Formulalarga misollar 

Biz bu funktsiyalarning foydaliligini allaqachon aniqlab oldik, shuning uchun biz eng qiziqarli qismga o'tishimiz mumkin: bilimlarni amaliyotda qo'llash.

O'ngdan chapga qidirish uchun formula

Yuqorida aytib o'tilganidek, VLOOKUP qidiruvning ushbu shaklini bajara olmaydi. Shunday qilib, agar kerakli qiymatlar eng chap ustunda bo'lmasa, VLOOKUP() natija bermaydi. INDEX() va MATCH() funksiyalari koʻp qirrali boʻlib, ularning ishlashi uchun qiymatlarning joylashuvi katta rol oʻynamaydi.

Masalan, biz jadvalimizning chap tomoniga daraja ustunini qo'shamiz va mamlakatimiz poytaxti aholi soni bo'yicha qaysi o'rinni egallashini aniqlashga harakat qilamiz.

G1 katakchaga topiladigan qiymatni yozamiz, so'ngra C1:C10 oralig'ida qidirish uchun quyidagi formuladan foydalanamiz va A2:A10 dan mos qiymatni qaytaramiz:

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

Tezkor. Agar siz ushbu formuladan bir nechta hujayralar uchun foydalanishni rejalashtirmoqchi bo'lsangiz, mutlaq adreslash yordamida diapazonlarni tuzatganingizga ishonch hosil qiling (masalan, $A$2: $A$10 va $C$2: 4C$10).

INDEX KO'PROQ FO'SHIRILGAN KO'PROQ  ustunlar va satrlarda qidirish uchun

Yuqoridagi misollarda biz ushbu funktsiyalardan VLOOKUP() ni o'rniga oldindan belgilangan qatorlar oralig'idan qiymatlarni qaytarish uchun foydalandik. Ammo matritsa yoki ikki tomonlama qidiruvni amalga oshirish kerak bo'lsa-chi?

Bu murakkab tuyuladi, lekin bunday hisob-kitoblar uchun formula standart INDEX() MATCH() formulasiga oʻxshaydi, faqat bitta farqi bor: MATCH() formulasini ikki marta ishlatish kerak. Birinchi marta satr raqamini, ikkinchi marta esa ustun raqamini olish uchun:

=INDEX(massiv, MATCH(vertikal qidiruv qiymati, qidiruv ustuni, 0), MATCH(gorizontal qidiruv qiymati, qidiruv qatori, 0))

Keling, quyidagi jadvalni ko'rib chiqamiz va formulani tuzishga harakat qilamiz INDEX() EXPRESS() EXPRESS() tanlangan yil uchun ma'lum bir mamlakatda demografiyani ko'rsatish uchun.

Maqsadli mamlakat G1 yacheykada (vertikal qidirish) va maqsadli yil G2 yacheykada (gorizontal qidirish). Formula quyidagicha ko'rinadi:

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

Ushbu formula qanday ishlaydi

Boshqa har qanday murakkab formulalar singari, ularni alohida tenglamalarga bo'lish orqali tushunish osonroq. Va keyin har bir alohida funktsiya nima qilishini tushunishingiz mumkin:

• MATCH(G1,A2:A11,0) – A1:A2 diapazonida qiymatni (G11) qidiradi va bu qiymatning sonini ko'rsatadi, bizning holatlarimizda u 2 ga teng;
• QIDIRISH(G2,B1:D1,0) – B2:D1 oralig‘ida qiymatni (G1) qidiradi. Bunday holda, natija 3 bo'ldi.

Topilgan satr va ustun raqamlari INDEX() formulasidagi mos qiymatga yuboriladi:

=INDEX(B2:D11,2,3)

Natijada, biz B2: D3 oralig'ida 2 satr va 11 ustunning kesishmasidagi katakchadagi qiymatga ega bo'lamiz. Va formula kerakli qiymatni ko'rsatadi, bu D3 katakchada joylashgan.

INDEX va MATCH bilan bir nechta shartlar bo'yicha qidiring

Agar siz VLOOKUP() boʻyicha qoʻllanmamizni oʻqigan boʻlsangiz, ehtimol siz bir nechta qidiruv formulalarini sinab koʻrgansiz. Ammo bu qidiruv usuli bitta muhim cheklovga ega - yordamchi ustun qo'shish zarurati.

Ammo yaxshi yangilik shu INDEX() va MATCH() yordamida siz ish varag'ingizni tahrirlash yoki o'zgartirmasdan bir nechta shartlarni qidirishingiz mumkin.

Mana INDEX() MATCH() uchun umumiy koʻp shartli qidiruv formulasi:

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

Eslatma: bu formula klaviatura yorlig'i bilan birga ishlatilishi kerak CTRL+SHIFT+ENTER.

Aytaylik, siz izlayotgan qiymatni ikkita shart asosida topishingiz kerak: xaridor и Mahsulot.

Bu quyidagi formulani talab qiladi:

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

Ushbu formulada C2:C10 qidiruv amalga oshiriladigan diapazondir, F1 - bu holat, A2: A10 - shartni solishtirish uchun diapazon, F2 - 2-shart, V2: V10 – 2-shartni taqqoslash uchun diapazon.

Formula bilan ish oxirida kombinatsiyani bosishni unutmang CTRL+SHIFT+ENTER – Excel misolda ko'rsatilganidek, formulani jingalak qavslar bilan avtomatik ravishda yopadi:

Ishingiz uchun massiv formulasidan foydalanishni istamasangiz, formulaga boshqa INDEX() qo‘shing va ENTER tugmasini bosing, u misoldagi kabi ko‘rinadi:

Ushbu formulalar qanday ishlaydi

Bu formula standart INDEX() MATCH() formulasi bilan bir xil ishlaydi. Bir nechta shartlarni qidirish uchun siz to'g'ri va noto'g'ri individual shartlarni ifodalovchi bir nechta False va True shartlarni yaratasiz. Va keyin bu shartlar massivning barcha mos keladigan elementlariga tegishli. Formula False va True argumentlarini mos ravishda 0 va 1 ga aylantiradi va qatorni chiqaradi, bunda 1 qatorda topilgan mos qiymatlardir. MATCH() 1 ga mos keladigan birinchi qiymatni topadi va uni INDEX() formulasiga uzatadi. Va u, o'z navbatida, kerakli ustundan ko'rsatilgan satrda allaqachon kerakli qiymatni qaytaradi.

Massivsiz formula INDEX() ning o'zi ularni boshqarish qobiliyatiga bog'liq. Formuladagi ikkinchi INDEX() noto'g'ri (0) ga mos keladi, shuning uchun u ushbu qiymatlar bilan butun massivni MATCH() formulasiga o'tkazadi. 

Bu formula ortidagi mantiqning ancha uzoq tushuntirishidir. Qo'shimcha ma'lumot olish uchun maqolani o'qing "Bir nechta shartlarga ega INDEX MATCH".

INDEX va MATCHda AVERAGE, MAX va MIN

Excel o'rtacha, maksimal va minimumlarni topish uchun o'zining maxsus funktsiyalariga ega. Ammo, agar siz ushbu qiymatlar bilan bog'langan hujayradan ma'lumotlarni olishni istasangiz nima bo'ladi? Ushbu holatda AVERAGE, MAX va MIN INDEX va MATCH bilan birgalikda ishlatilishi kerak.

INDEX MATCH va MAX

D ustunidagi eng katta qiymatni topish va uni C ustunida ko'rsatish uchun quyidagi formuladan foydalaning: 

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

INDEX MATCH va MIN

D ustunidagi eng kichik qiymatni topish va uni C ustunida ko'rsatish uchun quyidagi formuladan foydalaning:

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

SERPENT INDEX va SERPENT

D ustunidagi o'rtacha qiymatni topish va bu qiymatni C da ko'rsatish uchun:

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

Ma'lumotlaringiz qanday yozilganiga qarab, MATCH() ning uchinchi argumenti 1, 0 yoki -1 bo'ladi:

• agar ustunlar o'sish tartibida tartiblangan bo'lsa, 1 ni o'rnating (keyin formula o'rtacha qiymatdan kichik yoki unga teng bo'lgan maksimal qiymatni hisoblab chiqadi);
• agar tartib kamayib borayotgan bo'lsa, u holda -1 (formula o'rtachadan katta yoki teng bo'lgan minimal qiymatni chiqaradi);
• agar qidiruv massivida o'rtachaga teng qiymat bo'lsa, uni 0 ga o'rnating. 

 Bizning misolimizda aholi kamayish tartibida tartiblangan, shuning uchun biz -1 qo'yamiz. Va natija Tokio, chunki aholi soni (13,189) o'rtacha qiymatga (000) eng yaqin.

VLOOKUP() ham shunday hisob-kitoblarni amalga oshirishi mumkin, lekin faqat massiv formulasi sifatida: OʻRTA, MIN va MAX bilan VLOOKUP.

INDEX MATCH va ESND/IFERROR

Agar formula kerakli qiymatni topa olmasa, xatoga yo'l qo'yishini allaqachon payqadingiz # Yo'q. Siz standart xato xabarini ko'proq ma'lumot beruvchi narsa bilan almashtirishingiz mumkin. Masalan, formulada argumentni o'rnating XNUMX-da:

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

Ushbu formula yordamida, agar siz jadvalda bo'lmagan ma'lumotlarni kiritsangiz, forma sizga ko'rsatilgan xabarni beradi.

Agar siz barcha xatolarni qo'lga olishni istasangiz, bundan mustasno XNUMX-da foydalanish mumkin XATO:

=XATO(INDEX(C2:C10,MATCH(F1,A2:A10,0)), "Nimadir xato ketdi!”)

Ammo esda tutingki, xatolarni bu tarzda maskalash yaxshi fikr emas, chunki standart xatolar formuladagi buzilishlar haqida xabar beradi.

Umid qilamizki, INDEX MATCH() funksiyasidan foydalanish bo‘yicha qo‘llanmamiz sizga foydali bo‘ldi.