Excelda VLOOKUP harfini katta harflarga sezgir qilishning 4 usuli

Ushbu kichik qo'llanma funktsiyani qanday yaratishni tushuntiradi VPR (VLOOKUP) katta-kichik harflarni hisobga olgan holda, Excel katta harflarni hisobga olgan holda qidirishi mumkin bo'lgan bir nechta boshqa formulalarni ko'rsatadi va har bir funktsiyaning kuchli va zaif tomonlarini ko'rsatadi.

O'ylaymanki, har bir Excel foydalanuvchisi qaysi funktsiyani vertikal qidiruvni amalga oshirishini biladi. To'g'ri, bu funktsiya VPR. Biroq, buni kam odam biladi VPR harf sezgir emas, ya'ni kichik va katta harflar uning uchun bir xil.

Bu qobiliyatsizlikni ko'rsatadigan tezkor misol VPR registrni tanib olish. Aytaylik, hujayrada A1 "hisob-kitob" qiymati va katakchani o'z ichiga oladi A2 – “Hisob-kitob”, formula:

=VLOOKUP("Bill",A1:A10,2)

=ВПР("Bill";A1:A10;2)

… “hisob-kitob” boʻyicha qidiruvni toʻxtatadi, chunki bu qiymat roʻyxatda birinchi oʻrinda turadi va yacheykadan qiymat chiqaradi B1.

Keyinchalik ushbu maqolada men sizga qanday qilishni ko'rsataman VPR harflar katta-kichikligiga sezgir. Bundan tashqari, biz Excelda katta-kichik harflarni hisobga olgan holda qidirishni amalga oshiradigan yana bir nechta funktsiyalarni o'rganamiz.

Biz eng oddiyidan boshlaymiz - VIEW (LOOKUP) va SUMPRODUCT (SUMPRODUCT), afsuski, bir nechta muhim cheklovlarga ega. Keyinchalik, biroz murakkabroq formulani batafsil ko'rib chiqamiz INDEX+MATCH (INDEX+MATCH), u har qanday vaziyatda va har qanday ma'lumotlar to'plami bilan mukammal ishlaydi.

• Katta-kichik kichik VLOOKUP - yordamchi ustunni talab qiladi
• Katta-kichik harflarni hisobga oladigan QIDIRISh funksiyasi – ma’lumotlarni saralashni talab qiladi
• SUMPRODUCT - faqat raqamli qiymatlarni qaytaradi
• INDEX+MATCH – barcha ma’lumotlar turlari uchun katta-kichik harflarni hisobga olgan holda qidiruv

VLOOKUP funksiyasi katta-kichik harflarga sezgir

Siz allaqachon bilganingizdek, odatiy funktsiya VPR katta-kichik harf sezgir emas. Biroq, uni katta-kichik harflarga sezgir qilishning bir usuli bor. Buning uchun quyidagi misolda ko'rsatilganidek, jadvalga yordamchi ustun qo'shishingiz kerak.

Faraz qilaylik, ustunda B mahsulot identifikatorlari (element) mavjud va siz mahsulot narxini va ustunlardan tegishli sharhni olishni xohlaysiz C и D. Muammo shundaki, identifikatorlar kichik va katta harflarni o'z ichiga oladi. Masalan, hujayra qiymatlari B4 (001Tvci3u) va B5 (001Tvci3U) faqat oxirgi belgi holatida farqlanadi, u и U mos ravishda.

Siz tasavvur qilganingizdek, odatiy qidiruv formulasi

=VLOOKUP("001Tvci3U",$A$2:$C$7,2,FALSE)

=ВПР("001Tvci3U";$A$2:$C$7;2;ЛОЖЬ)

qaytib keladi $ 90, qiymatdan beri 001Tvci3u dan oldin qidiruv oralig'ida 001Tvci3U. Ammo bu bizga kerak emas, shunday emasmi?

Funktsiya bilan qidirish uchun VPR Excelda katta-kichik registrda yordamchi ustun qo'shishingiz va uning katakchalarini quyidagi formula bilan to'ldirishingiz kerak bo'ladi (bu erda B - qidirish ustuni):

=CODE(MID(B2,1,1)) & CODE(MID(B2,2,1)) & CODE(MID(B2,3,1)) & CODE(MID(B2,4,1)) & CODE(MID(B2,5,1)) & CODE(MID(B2,6,1)) & CODE(MID(B2,7,1)) & CODE(MID(B2,8,1)) & IFERROR(CODE(MID(B2,9,1)),"")

=КОДСИМВ(ПСТР(B2;1;1)) & КОДСИМВ(ПСТР(B2;2;1)) & КОДСИМВ(ПСТР(B2;3;1)) & КОДСИМВ(ПСТР(B2;4;1)) & КОДСИМВ(ПСТР(B2;5;1)) & КОДСИМВ(ПСТР(B2;6;1)) & КОДСИМВ(ПСТР(B2;7;1)) & КОДСИМВ(ПСТР(B2;8;1)) & ЕСЛИОШИБКА(КОДСИМВ(ПСТР(B2;9;1));"")

Ushbu formula kerakli qiymatni alohida belgilarga ajratadi, har bir belgini o'z kodi bilan almashtiradi (masalan, o'rniga A o'rniga 65 da a kod 97) va keyin bu kodlarni noyob raqamlar qatoriga birlashtiradi.

Shundan so'ng biz oddiy funktsiyadan foydalanamiz VPR katta harf sezgir qidiruv uchun:

=VLOOKUP($G$3,$A$2:$C$8,3,FALSE)

=ВПР($G$3;$A$2:$C$8;3;ЛОЖЬ)

Funktsiyaning to'g'ri ishlashi VPR katta-kichik harf sezgirligi ikki omilga bog'liq:

1. Yordamchi ustun ko'rish mumkin bo'lgan diapazondagi eng chap ustun bo'lishi kerak.
2. Siz izlayotgan qiymat haqiqiy qiymat o'rniga belgilar kodini o'z ichiga olishi kerak.

CODE funksiyasidan qanday qilib to'g'ri foydalanish kerak

Yordamchi ustunning katakchalariga kiritilgan formula sizning barcha qidiruv qiymatlaringiz bir xil belgilar soniga ega ekanligini taxmin qiladi. Agar yo'q bo'lsa, unda siz eng kichik va eng katta raqamlarni bilishingiz va ko'plab xususiyatlarni qo'shishingiz kerak XATO (IFERROR) eng qisqa va eng uzun qidirilgan qiymat o'rtasidagi farq qancha belgidan iborat.

Masalan, eng qisqa qidiruv qiymati 3 ta belgi va eng uzuni 5 ta belgidan iborat bo'lsa, quyidagi formuladan foydalaning:

=CODE(MID(B2,1,1)) & CODE(MID(B2,2,1)) & CODE(MID(B2,3,1)) & IFERROR(CODE(MID(B2,3,1)),"") & IFERROR(CODE(MID(B2,4,1)),"")

=КОДСИМВ(ПСТР(B2;1;1)) & КОДСИМВ(ПСТР(B2;2;1)) & КОДСИМВ(ПСТР(B2;3;1)) & ЕСЛИОШИБКА(КОДСИМВ(ПСТР(B2;3;1));"") & ЕСЛИОШИБКА(КОДСИМВ(ПСТР(B2;4;1));"")

Funktsiya uchun PSTR (MID) Siz quyidagi dalillarni keltirasiz:

• 1-argument - matn (matn) - ajratib olinadigan belgilarni o'z ichiga olgan matn yoki katakcha havolasi (bizning holimizda bu B2)
• 2-argument - boshlang'ich_raqam (start_position) - bu ajratib olinadigan birinchi belgilarning pozitsiyasi. kirasiz 1 birinchi funktsiyada PSTR, 2 - ikkinchi funktsiyada PSTR va hokazo
• 3-argument - belgilar_raqam (belgilar_soni) - matndan ajratib olinadigan belgilar sonini belgilaydi. Bizga har doim faqat 1 ta belgi kerak bo'lganligi sababli, barcha funktsiyalarda biz yozamiz 1.

Cheklovlar: vazifa VPR Excelda katta-kichik harflarni hisobga olgan holda qidirish uchun eng yaxshi yechim emas. Birinchidan, yordamchi ustunni qo'shish talab qilinadi. Ikkinchidan, agar ma'lumotlar bir hil bo'lsa yoki qidirilayotgan qiymatlardagi belgilarning aniq soni ma'lum bo'lsa, formula yaxshi ishlaydi. Agar bu sizning holatingiz bo'lmasa, quyida ko'rsatadigan echimlardan birini ishlatish yaxshiroqdir.

LOOKUP funksiyasi katta harflarni hisobga olgan holda qidirish uchun

vazifa VIEW (LOOKUP) bilan bog'liq VPR, ammo uning sintaksisi yordamchi ustun qo'shmasdan katta-kichik harflarni hisobga olgan holda qidirish imkonini beradi. Buning uchun foydalaning VIEW funktsiyasi bilan birlashtirilgan MAQOLA (Aniq).

Agar biz oldingi misoldan ma'lumotlarni olsak (yordamchi ustunsiz), unda quyidagi formula vazifani bajaradi:

=LOOKUP(TRUE,EXACT($A$2:$A$7,$F$2),$B$2:$B$7)

=ПРОСМОТР(ИСТИНА;СОВПАД($A$2:$A$7;$F$2);$B$2:$B$7)

Formulalarni diapazonda qidirish A2: A7 hujayra qiymati bilan aniq moslik F2 katta-kichik harf sezgir va qiymatni bir xil satrning B ustunidan qaytaradi.

kabi VPRFunktsiyasi VIEW Quyidagi skrinshotda ko'rib turganingizdek, matn va raqamli qiymatlar bilan teng ishlaydi:

Muhim! Funktsiyani bajarish uchun VIEW to'g'ri ishlagan bo'lsa, qidirish ustunidagi qiymatlar o'sish tartibida, ya'ni eng kichikdan kattagacha saralanishi kerak.

Funktsiya qanday ishlashini qisqacha tushuntirib beraman MAQOLA yuqorida ko'rsatilgan formulada, chunki bu asosiy nuqta.

vazifa MAQOLA 1 va 2-argumentlardagi ikkita matn qiymatini solishtiradi va agar ular bir xil bo'lsa TRUE yoki bo'lmasa FALSE qaytaradi. Biz uchun bu funktsiya muhim MAQOLA harflar katta-kichikligiga sezgir.

Keling, formulamiz qanday ishlashini ko'rib chiqaylik KOʻRISH+ANIQ:

=LOOKUP(TRUE,EXACT($A$2:$A$7,$F$2),$B$2:$B$7)

=ПРОСМОТР(ИСТИНА;СОВПАД($A$2:$A$7;$F$2);$B$2:$B$7)

• vazifa MAQOLA hujayra qiymatini solishtiradi F2 ustundagi barcha elementlar bilan A (A2:A7). Agar aniq moslik topilsa TRUE, aks holda FALSE qaytaradi.
• Birinchi funktsiya argumentini berganingiz uchun VIEW qiymati TRUE bo'lsa, u ko'rsatilgan ustundan mos qiymatni chiqaradi (bizning holimizda, B ustuni), agar aniq moslik topilsa, katta-kichik registrlarga sezgir.

Umid qilamanki, bu tushuntirish aniq edi va endi siz asosiy fikrni tushunasiz. Agar shunday bo'lsa, unda siz boshqa funktsiyalar bilan hech qanday qiyinchiliklarga duch kelmaysiz, biz ularni batafsilroq tahlil qilamiz, chunki. ularning barchasi bir xil printsip asosida ishlaydi.

Cheklovlar: Qidiruv ustunidagi ma'lumotlar o'sish tartibida saralanishi kerak.

SUMPRODUCT – matn qiymatlarini topadi, katta-kichik harflarni hisobga oladi, lekin faqat raqamlarni qaytaradi

Sarlavhadan allaqachon tushunganingizdek, SUMPRODUCT (SUMPRODUCT) Excelning boshqa funksiyasi boʻlib, u katta-kichik harflarni hisobga olgan holda qidiruvni amalga oshirishga yordam beradi, lekin faqat raqamli qiymatlarni qaytaradi. Agar ushbu parametr sizga mos kelmasa, darhol to'plamga o'tishingiz mumkin INDEX+MATCH, bu har qanday holat va har qanday ma'lumot turlari uchun yechim beradi.

Birinchidan, ushbu funktsiyaning sintaksisini qisqacha tushuntirib beraman, bu sizga keyingi katta-kichik kichik formulani yaxshiroq tushunishga yordam beradi.

vazifa SUMPRODUCT berilgan massivlarning elementlarini ko'paytiradi va natijalar yig'indisini qaytaradi. Sintaksis quyidagicha ko'rinadi:

SUMPRODUCT(array1,[array2],[array3],...)

СУММПРОИЗВ(массив1;[массив2];[массив3];…)

Bizga katta-kichik harflar bo'yicha qidiruv kerak bo'lgani uchun biz funksiyadan foydalanamiz MAQOLA (EXACT) oldingi misoldan ko'paytirgichlardan biri sifatida:

=SUMPRODUCT((EXACT($A$2:$A$7,$F$2)*($B$2:$B$7)))

=СУММПРОИЗВ((СОВПАД($A$2:$A$7;$F$2)*($B$2:$B$7)))

Eslaganingizdek, MAQOLA hujayra qiymatini solishtiradi F2 ustundagi barcha elementlar bilan A. Agar aniq moslik topilsa TRUE, aks holda FALSE qaytaradi. Matematik operatsiyalarda Excel TRUE ni qabul qiladi 1, va FALSE uchun 0Bundan tashqari SUMPRODUCT bu raqamlarni ko'paytiradi va natijalarni jamlaydi.

Nollar hisobga olinmaydi, chunki ko'paytirilganda ular doimo beradi 0. Keling, ustunda aniq mos kelganda nima sodir bo'lishini batafsil ko'rib chiqaylik A topib qaytdi 1… Funktsiya SUMPRODUCT ustundagi sonni ko'paytiradi B on 1 va natijani qaytaradi - aynan bir xil raqam! Buning sababi shundaki, boshqa mahsulotlarning natijalari nolga teng va ular olingan summaga ta'sir qilmaydi.

Afsuski, funktsiya SUMPRODUCT matn qiymatlari va sanalari bilan ishlay olmaydi, chunki ularni ko'paytirish mumkin emas. Bunday holda siz xato xabarini olasiz #QIMMAT! (#VALUE!) hujayradagi kabi F4 quyidagi rasmda:

Cheklovlar: Faqat raqamli qiymatlarni qaytaradi.

INDEX + MATCH - har qanday ma'lumot turi uchun katta-kichik harflarni hisobga olgan holda qidiruv

Nihoyat, biz har qanday ma'lumotlar to'plami bilan ishlaydigan cheksiz va katta-kichik harflarga sezgir qidiruv formulasiga yaqinmiz.

Bu misol, eng yaxshisi shirinlikka qoldirilgani uchun emas, balki oldingi misollardan olingan bilimlar katta-kichik harflarga sezgir formulani yaxshiroq va tezroq tushunishga yordam bergani uchun oxirgi o'rinda turadi. INDEX+MATCH (INDEX+MATCH).

Siz taxmin qilganingizdek, funktsiyalarning kombinatsiyasi KO'PROQ и INDEKS uchun yanada moslashuvchan va kuchli muqobil sifatida Excelda foydalaniladi VPR. VLOOKUP o'rniga INDEX va MATCH dan foydalanish maqolasi ushbu funktsiyalar qanday birgalikda ishlashini mukammal tushuntiradi.

Men faqat asosiy fikrlarni takrorlayman:

• vazifa KO'PROQ (MATCH) berilgan diapazondagi qiymatni qidiradi va uning nisbiy holatini, ya'ni satr va/yoki ustun raqamini qaytaradi;
• Keyingi, funksiya INDEKS (INDEX) belgilangan ustun va/yoki satrdan qiymatni qaytaradi.

Formulaga INDEX+MATCH Katta-kichik harflarni hisobga olgan holda qidirish mumkin, unga faqat bitta funktsiya qo'shishingiz kerak. Yana nima ekanligini taxmin qilish qiyin emas MAQOLA (Aniq):

=INDEX($B$2:$B$7,MATCH(TRUE,EXACT($A$2:$A$7,$F$2),0))

=ИНДЕКС($B$2:$B$7;ПОИСКПОЗ(ИСТИНА;СОВПАД($A$2:$A$7;$F$2);0))

Ushbu formulada MAQOLA funksiya bilan birgalikda ishlaydi VIEW, va bir xil natija beradi:

Formulaga e'tibor bering INDEX+MATCH jingalak qavslar ichiga massiv formulasi berilgan va uni bosish orqali bajarishingiz kerak Ctrl + Shift + Enter tugmalarini bosing.

Nima uchun INDEX+MATCH katta-kichik harflarni hisobga olgan holda qidirish uchun eng yaxshi yechim hisoblanadi?

To'plamning asosiy afzalliklari INDEKS и KO'PROQ:

1. Yordamchi ustun qo'shishni talab qilmaydi, farqli o'laroq VPR.
2. Qidiruv ustunini saralashni talab qilmaydi, farqli o'laroq VIEW.
3. Barcha turdagi ma'lumotlar bilan ishlaydi - raqamlar, matn va sanalar.

Bu formula mukammal ko'rinadi, shunday emasmi? Aslida, unday emas. Va shuning uchun ham.

Qidiruv qiymati bilan bog'langan qaytish qiymati ustunidagi katak bo'sh deb faraz qiling. Formula qanday natija beradi? Yo'qmi? Keling, formula aslida nimani qaytarishini ko'rib chiqaylik:

Voy, formula nolni qaytaradi! Agar siz sof matn qiymatlari bilan ishlayotgan bo'lsangiz, bu katta muammo bo'lmasligi mumkin. Biroq, agar jadvalda raqamlar, jumladan, "haqiqiy" nollar bo'lsa, bu muammoga aylanadi.

Aslida, biz ilgari muhokama qilgan boshqa barcha qidirish formulalari (VLOOKUP, LOOKUP va SUMPRODUCT) xuddi shunday harakat qiladi. Lekin siz mukammal formulani xohlaysiz, to'g'rimi?

Formula katta-kichikligini sezgir qilish uchun INDEX+MATCH mukammal, uni funktsiyaga qo'ying IF Qaytish qiymatiga ega bo'lgan katakchani sinovdan o'tkazadigan va agar u bo'sh bo'lsa, bo'sh natijani qaytaradigan (IF):

=IF(INDIRECT("B"&(1+MATCH(TRUE,EXACT($A$2:$A$7,$G$2),0)))<>"",INDEX($B$2:$B$7, MATCH(TRUE,EXACT($A$2:$A$7,$G$2),0)),"")

=ЕСЛИ(ДВССЫЛ("B"&(1+ПОИСКПОЗ(ИСТИНА;СОВПАД($A$2:$A$7;$G$2);0)))<>"";ИНДЕКС($B$2:$B$7; ПОИСКПОЗ(ИСТИНА;СОВПАД($A$2:$A$7;$G$2);0));"")

Ushbu formulada:

• B qaytariladigan qiymatlarga ega ustundir
• 1+ funktsiya tomonidan qaytarilgan hujayraning nisbiy o'rnini aylantiruvchi raqam KO'PROQ, hujayraning haqiqiy manziliga. Masalan, bizning funktsiyamizda KO'PROQ qidiruv massivi berilgan A2: A7, ya'ni hujayraning nisbiy joylashuvi A2 bo'ladi 1, chunki u massivdagi birinchi. Ammo hujayraning haqiqiy holati A2 ustunda joylashgan 2, shuning uchun biz qo'shamiz 1farqni to'ldirish va funktsiyaga ega bo'lish BILVOSIT (INDIRECT) kerakli katakdan qiymatni oldi.

Quyidagi rasmlarda katta-kichik harflar uchun tuzatilgan formula ko'rsatilgan INDEX+MATCH Harakatda. Qaytarilgan katak bo'sh bo'lsa, u bo'sh natijani qaytaradi.

Men formulani ustunlarga qayta yozdim B:Dskrinshotda formulalar satrini moslash uchun.

Formula qaytariladi 0agar qaytarilgan katakda nol bo'lsa.

Agar siz havolani xohlasangiz INDEKS и KO'PROQ Qaytish qiymati bo'sh bo'lganda ba'zi xabarlar ko'rsatilgan bo'lsa, uni formulaning oxirgi tirnoqlariga ("") yozishingiz mumkin, masalan:

=IF(INDIRECT("D"&(1+MATCH(TRUE,EXACT($B$2:$B$7,$G$2),0)))<>"",INDEX($D$2:$D$7, MATCH(TRUE,EXACT($B$2:$B$7,$G$2),0)),"There is nothing to return, sorry.")

=ЕСЛИ(ДВССЫЛ("D"&(1+ПОИСКПОЗ(ИСТИНА;СОВПАД($B$2:$B$7;$G$2);0)))<>"";ИНДЕКС($D$2:$D$7; ПОИСКПОЗ(ИСТИНА;СОВПАД($B$2:$B$7;$G$2);0));"There is nothing to return, sorry.")