Excelda INDEX va MATCH funksiyasidan qanday foydalanish kerak

2024 Muallif: Abigail Brown | [email protected]. Oxirgi o'zgartirilgan: 2023-12-17 07:04

Nimalarni bilish kerak

• INDEX funksiyasidan yolgʻiz foydalanish mumkin, lekin MATCH funksiyasini uning ichiga joylashtirish kengaytirilgan qidiruvni yaratadi.
• Bu ichki oʻrnatilgan funksiya VLOOKUPga qaraganda ancha moslashuvchan va natijalarni tezroq berishi mumkin.

Ushbu maqola Excelning barcha versiyalarida, jumladan Excel 2019 va Microsoft 365 da INDEX va MATCH funksiyalaridan qanday foydalanishni tushuntiradi.

INDEX va MATCH funksiyalari nima?

INDEX va MATCH Excel qidiruv funksiyalaridir. Ular o'z-o'zidan ishlatilishi mumkin bo'lgan ikkita mutlaqo alohida funksiya bo'lsa-da, ilg'or formulalar yaratish uchun birlashtirilishi ham mumkin.

INDEX funksiyasi ma'lum bir tanlov ichidan qiymat yoki qiymatga havolani qaytaradi. Masalan, undan maʼlumotlar toʻplamining ikkinchi qatori yoki beshinchi qator va uchinchi ustundagi qiymatni topish uchun foydalanish mumkin.

INDEX-dan yolg'iz foydalanish mumkin bo'lsa-da, MATCH-ni formulaga joylashtirish uni biroz foydaliroq qiladi. MATCH funktsiyasi hujayralar oralig'ida belgilangan elementni qidiradi va keyin elementning diapazondagi nisbiy o'rnini qaytaradi. Masalan, ma'lum bir nom ismlar ro'yxatidagi uchinchi element ekanligini aniqlash uchun ishlatilishi mumkin.

INDEX va MATCH sintaksisi va argumentlari

Excel ularni tushunishi uchun ikkala funksiya ham shunday yozilishi kerak:

=INDEX(massiv, satr_raqami, [ustun_raqami])

• massiv - formuladan foydalaniladigan hujayralar diapazoni. Bu bir yoki bir nechta satr va ustunlar bo'lishi mumkin, masalan, A1: D5. Bu shart.
• satr_raqami massivdagi 2 yoki 18 kabi qiymat qaytariladigan qator. Ustun_raqami mavjud boʻlmasa, bu talab qilinadi.
• column_num massivdagi 1 yoki 9 kabi qiymat qaytariladigan ustun. Bu ixtiyoriy.

=MATCH(qidiruv_qiymati, qidirish_massivi, [moslik_turi])

• lookup_value - qidiruv_massivida mos kelmoqchi bo'lgan qiymat. Bu raqam, matn yoki mantiqiy qiymat bo'lishi mumkin, ular qo'lda terilgan yoki hujayra havolasi orqali havola qilingan. Bu talab qilinadi.
• lookup_array - koʻrib chiqiladigan hujayralar diapazoni. Bu bitta satr yoki bitta ustun bo'lishi mumkin, masalan, A2: D2 yoki G1: G45. Bu talab qilinadi.
• match_type -1, 0 yoki 1 boʻlishi mumkin. U qidiruv_qiymati qidiruv_massividagi qiymatlarga qanday mos kelishini belgilaydi (pastga qarang). Agar bu argument o‘tkazib yuborilsa, 1 standart qiymat hisoblanadi.

Qaysi moslik turidan foydalanish kerak
Mos turi	Bu nima qiladi	Qoidalar	Misol
1	Qidiruv_qiymatidan kichik yoki unga teng boʻlgan eng katta qiymatni topadi.	Qidiruv_massivi qiymatlari oʻsish tartibida joylashtirilishi kerak (masalan, -2, -1, 0, 1, 2; yoki A-Z; yoki FALSE, TRUE.	qidiruv_qiymati 25, lekin qidiruv_massivida u yoʻq, shuning uchun uning oʻrniga 22 kabi keyingi eng kichik raqamning oʻrni qaytariladi.
0	Izlash_qiymatiga aynan teng boʻlgan birinchi qiymatni topadi.	Qidiruv_massivi qiymatlari istalgan tartibda boʻlishi mumkin.	qidiruv_qiymati 25, shuning uchun u 25 pozitsiyasini qaytaradi.
-1	Qidiruv_qiymatidan katta yoki teng boʻlgan eng kichik qiymatni topadi.	Qidiruv_massivi qiymatlari kamayish tartibida joylashtirilishi kerak (masalan, 2, 1, 0, -1, -2).	qidiruv_qiymati 25, lekin qidiruv_massivida u yoʻq, shuning uchun uning oʻrniga keyingi eng katta raqamning oʻrni, 34 kabi qaytariladi.

Mashtab boʻyicha taxminiy qidiruvni amalga oshirish kerak boʻlganda, masalan, raqamlar bilan ishlashda va taxminiy maʼlumotlar yaxshi boʻlganda, 1 yoki -1 dan foydalaning. Esda tutingki, agar match_type ni belgilamasangiz, 1 standart bo‘ladi, agar siz haqiqatan ham aynan moslikni xohlasangiz, natijalarni buzib qo‘yishi mumkin.

Misol INDEX va MATCH formulalari

INDEX va MATCHni qanday qilib bitta formulada birlashtirishni koʻrib chiqishdan oldin bu funksiyalar oʻz-oʻzidan qanday ishlashini tushunishimiz kerak.

INDEX misollar

=INDEX(A1:B2, 2, 2)
=INDEX(A1:B1, 1)
=INDEX(2:2, 1)=INDEKS(B1:B2, 1)





Bu birinchi misolda biz turli qiymatlarni olish uchun foydalanishimiz mumkin bo'lgan to'rtta INDEX formulasi mavjud:

=INDEX(A1:B2, 2, 2) A1:B2 orqali ikkinchi ustun va ikkinchi qatordagi qiymatni topadi, ya'ni Stacy.
=INDEX(A1:B1, 1) birinchi ustundagi qiymatni topish uchun A1:B1 orqali qaraydi, bu Jon.
=INDEX(2:2, 1) birinchi ustundagi qiymatni topish uchun ikkinchi qatordagi hamma narsani koʻrib chiqadi, bu Tim.
=INDEX(B1:B2, 1) birinchi qatordagi qiymatni aniqlash uchun B1:B2 orqali qaraydi, ya'ni Emi.

MATCH misollar
=MATCH("Stacy", A2:D2, 0)
=MATCH(14, D1:D2)
=MATCH(14, D1:D2, -1)=MATCH(13, A1:D1, 0)





MATCH funksiyasining toʻrtta oson misoli:

=MATCH("Stacy", A2:D2, 0) A2:D2 diapazonida Stacy-ni qidirmoqda va natijada 3-ni qaytaradi.
=MATCH(14, D1:D2) D1:D2 diapazonida 14 ni qidirmoqda, lekin u jadvalda topilmagani uchun MATCH keyingi eng katta qiymatni topadi bu 14 dan kichik yoki unga teng, bu holda bu 13 ga teng, bu qidiruv_massivining 1-pog'onasida.
=MATCH(14, D1:D2, -1) yuqoridagi formula bilan bir xil, lekin massiv kamayish tartibida emasligi sababli -1 talab qiladi, xatolik oldik.
=MATCH(13, A1:D1, 0) varaqning birinchi qatoridan 13 ni qidirmoqda, bu massivdagi toʻrtinchi element boʻlgani uchun 4 ni qaytaradi.


INDEX-MATCH misollar
Mana, INDEX va MATCHni bitta formulada birlashtira oladigan ikkita misol:
Jadvalda hujayra ma'lumotnomasini toping
=INDEX(B2:B5, MATCH(F1, A2:A5))






Bu misolda MATCH formulasi INDEX formulasi ichiga joylashtirilgan. Maqsad - element raqami yordamida element rangini aniqlash.
Rasmga qarasangiz, “Ajratilgan” qatorlarda formulalar oʻz-oʻzidan qanday yozilishini koʻrishingiz mumkin, lekin biz ularni joylashtirayotganimiz uchun shunday boʻlmoqda:

MATCH(F1, A2:A5) A2:A5 ma'lumotlar to'plamida F1 qiymatini (8795) qidirmoqda. Agar ustunni sanasak, 2 ekanligini ko'ramiz, shuning uchun MATCH funksiyasi shuni aniqladi.
INDEX massivi B2:B5, chunki biz oxir-oqibat ushbu ustundagi qiymatni qidirmoqdamiz.
INDEX funksiyasini endi shunday qayta yozish mumkin, chunki MATCH topilgan 2: INDEX(B2:B5, 2, [ustun_raqami]).
ustun_num ixtiyoriy boʻlgani uchun biz buni oʻchirib tashlashimiz mumkin: INDEX(B2:B5, 2).
Demak, bu oddiy INDEX formulasiga oʻxshaydi, bunda biz B2:B5 dagi ikkinchi elementning qiymatini topamiz, bu qizil.

Satr va ustun sarlavhalari boʻyicha qidirish
=INDEX(B2:E13, MATCH(G1, A2:A13, 0), MATCH(G2, B1:E1, 0))






Ushbu MATCH va INDEX misolida biz ikki tomonlama qidiruvni amalga oshirmoqdamiz. Maqsad may oyida Yashil narsalardan qancha pul ishlaganimizni ko'rishdir. Bu yuqoridagi misolga juda oʻxshaydi, lekin qoʻshimcha MATCH formulasi INDEX ichiga joylashtirilgan.

MATCH(G1, A2:A13, 0) - bu formulada yechilgan birinchi element. Muayyan qiymatni olish uchun u A2:A13 da G1 ("May" so'zi) ni qidirmoqda. Bu yerda ko‘rmayapmiz, lekin soat 5.
MATCH(G2, B1:E1, 0) ikkinchi MATCH formulasi boʻlib, u birinchisiga juda oʻxshaydi, lekin uning oʻrniga G2 (“Yashil” soʻzi) qidirilmoqda) ustun sarlavhalarida B1:E1. Bu 3 ni hal qiladi.
Endi biz nima sodir boʻlayotganini koʻrish uchun INDEX formulasini shunday qayta yozishimiz mumkin: =INDEX(B2:E13, 5, 3). Bu butun jadvalda, B2:E13, beshinchi qator va uchinchi ustunni ko'rib chiqmoqda, bu esa $180 ni qaytaradi.


MATCH va INDEX qoidalari
Bu funksiyalar yordamida formulalar yozishda bir nechta narsalarni yodda tutish kerak:

MATCH katta-kichik harflarga sezgir emas, shuning uchun matn qiymatlari mos kelganda katta va kichik harflar bir xil bo'ladi.
MATCH bir nechta sabablarga koʻra N/A qaytaradi: agar moslik_turi 0 boʻlsa va qidirish_qiymati topilmasa, agar moslik_turi -1 boʻlsa va qidirish_massivi kamayish tartibida boʻlmasa, moslik_turi 1 boʻlsa va qidirish_massivi oʻsish tartibida boʻlmasa tartib va agar qidiruv_massivi bitta satr yoki ustun boʻlmasa.
Agar moslik_turi 0 va qidiruv_qiymati matn qatori boʻlsa, qidiruv_qiymati argumentida joker belgidan foydalanishingiz mumkin. Savol belgisi har qanday bitta belgiga, yulduzcha esa har qanday belgilar ketma-ketligiga mos keladi (masalan.g., =MATCH("Jo", 1:1, 0)). Haqiqiy savol yoki yulduzchani topish uchun MATCH dan foydalanish uchun avval ~ yozing.
INDEX REFni qaytaradi! agar satr_raqami va ustun_raqami massivdagi katakka ishora qilmasa.


Tegishli Excel funksiyalari
MATCH funksiyasi LOOKUP funksiyasiga oʻxshaydi, lekin MATCH elementning oʻrniga elementning oʻrnini qaytaradi.
VLOOKUP Excelda foydalanishingiz mumkin boʻlgan yana bir qidirish funksiyasidir, lekin kengaytirilgan qidiruvlar uchun INDEX talab qiladigan MATCHdan farqli oʻlaroq, VLOOKUP formulalari faqat shu funksiyani talab qiladi.