Kengaytirilgan VLOOKUP misollari: Ko'p mezonli qidiruv

Funktsiya bo'yicha o'quv qo'llanmamizning ikkinchi qismida VPR Excelda (VLOOKUP) biz barcha kuchlarni boshqarishga yordam beradigan bir nechta misollarni tahlil qilamiz VPR eng ambitsiyali Excel vazifalarini hal qilish uchun. Misollar, siz ushbu xususiyat qanday ishlashi haqida asosiy bilimga ega ekanligingizni taxmin qiladi. Agar yo'q bo'lsa, siz ushbu qo'llanmaning sintaksis va asosiy foydalanishni tushuntiradigan birinchi qismidan boshlashni xohlashingiz mumkin. VPR. Xo'sh, keling, boshlaylik.

• Excelda bir nechta mezonlar bo'yicha qidiring
• VLOOKUP yordamida 2, 3 va hokazo qiymatlarni chiqaramiz
• Istalgan qiymatning barcha takrorlarini oling
• Ma'lum qator va ustunlar bo'yicha XNUMXD qidiruv
• Bitta formulada bir nechta VLOOKUPlardan foydalanish
• Turli jadvallardagi ma'lumotlarni dinamik almashtirish

Excelda bir nechta mezonlar bo'yicha qidiring

vazifa VPR Excelda ma'lumotlar bazasida ma'lum bir qiymat uchun qidiruvlarni amalga oshirish uchun juda kuchli vositadir. Biroq, muhim cheklov mavjud - uning sintaksisi faqat bitta qiymatni qidirishga imkon beradi. Agar bir nechta shartlar bo'yicha qidirmoqchi bo'lsangiz-chi? Siz quyida yechim topasiz.

1-misol: 2 xil mezon bo'yicha qidirish

Aytaylik, bizda buyurtmalar ro'yxati bor va biz topmoqchimiz Tovarlar miqdori (Miqdor), ikkita mezon asosida - Mijoz nomi (mijoz) i mahsulot nomi (Mahsulot). Quyidagi jadvaldan ko'rinib turibdiki, xaridorlarning har biri bir necha turdagi tovarlarga buyurtma berganligi sababli masala murakkablashadi:

muntazam funktsiya VPR bu stsenariyda ishlamaydi, chunki u berilgan qidiruv qiymatiga mos keladigan birinchi qiymatni qaytaradi. Misol uchun, agar siz buyumning miqdorini bilmoqchi bo'lsangiz Shirinliklarxaridor tomonidan buyurtma qilingan Jeremi Hill, quyidagi formulani yozing:

=VLOOKUP(B1,$A$5:$C$14,3,FALSE)

=ВПР(B1;$A$5:$C$14;3;ЛОЖЬ)

– bu formula natijani qaytaradi 15mahsulotga mos keladi Olmalar, chunki bu mos keladigan birinchi qiymat.

Oddiy vaqtinchalik echim bor - barcha kerakli mezonlarni birlashtiradigan qo'shimcha ustun yarating. Bizning misolimizda bu ustunlar Mijoz nomi (mijoz) i mahsulot nomi (Mahsulot). Shuni unutmangki, birlashtirilgan ustun har doim qidiruv oralig'idagi eng chap ustun bo'lishi kerak, chunki bu funktsiya chap ustundir. VPR qiymat qidirganda yuqoriga qaraydi.

Shunday qilib, siz jadvalga yordamchi ustun qo'shasiz va uning barcha kataklariga quyidagi formuladan nusxa ko'chirasiz: =B2&C2. Agar siz satrning o'qilishi mumkin bo'lishini istasangiz, birlashtirilgan qiymatlarni bo'sh joy bilan ajratishingiz mumkin: =B2&» «&C2. Shundan so'ng siz quyidagi formuladan foydalanishingiz mumkin:

=VLOOKUP("Jeremy Hill Sweets",$A$7:$D$18,4,FALSE)

=ВПР("Jeremy Hill Sweets";$A$7:$D$18;4;ЛОЖЬ)

or

=VLOOKUP(B1,$A$7:$D$18,4,FALSE)

=ВПР(B1;$A$7:$D$18;4;ЛОЖЬ)

Hujayra qayerda B1 argumentning birlashtirilgan qiymatini o'z ichiga oladi qidiruv_qiymati (qidiruv_qiymati) va 4 - Dalil col_index_num (ustun_raqami), ya'ni olinadigan ma'lumotlarni o'z ichiga olgan ustun raqami.

2-misol: Jadval boshqa varaqda ko'rilgan holda ikkita mezon bo'yicha VLOOKUP

Agar siz boshqa varaqda yoki boshqa Excel ish kitobida joylashgan ikkinchi jadvaldan (jadvalni qidirish) ma'lumotlarni qo'shish orqali asosiy jadvalni (Asosiy jadval) yangilashingiz kerak bo'lsa, kerakli qiymatni to'g'ridan-to'g'ri kiritgan formulada to'plashingiz mumkin. asosiy jadvalga.

Oldingi misolda bo'lgani kabi, Qidiruv jadvalida birlashtirilgan qiymatlarga ega yordamchi ustun kerak bo'ladi. Ushbu ustun qidiruv oralig'idagi eng chap ustun bo'lishi kerak.

Shunday qilib, formula bilan VPR shunday bo'lishi mumkin:

=VLOOKUP(B2&" "&C2,Orders!$A&$2:$D$2,4,FALSE)

=ВПР(B2&" "&C2;Orders!$A&$2:$D$2;4;ЛОЖЬ)

Bu erda B va C ustunlari mos ravishda mijozlar nomlari va mahsulot nomlarini va havolani o'z ichiga oladi Buyurtmalar!$A&$2:$D$2 boshqa varaqda qidirish uchun jadvalni belgilaydi.

Formulani o'qish uchun qulayroq qilish uchun siz ko'rish diapazoniga nom berishingiz mumkin, keyin formula ancha sodda ko'rinadi:

=VLOOKUP(B2&" "&C2,Orders,4,FALSE)

=ВПР(B2&" "&C2;Orders;4;ЛОЖЬ)

Formulaning ishlashi uchun siz ko'rib chiqayotgan jadvalning eng chap ustunidagi qiymatlar qidiruv mezonlarida bo'lgani kabi bir xil tarzda birlashtirilishi kerak. Yuqoridagi rasmda biz u2bu2band qiymatlarini birlashtirdik, ular orasiga bo'sh joy qo'ydik, xuddi shu tarzda siz funktsiyaning birinchi argumentida qilishingiz kerak (BXNUMX& "" & CXNUMX).

Yodingizda bo'lsin! vazifa VPR 255 belgi bilan cheklangan, u 255 belgidan ortiq qiymatni qidira olmaydi. Buni yodda tuting va kerakli qiymatning uzunligi ushbu chegaradan oshmasligiga ishonch hosil qiling.

Men yordamchi ustunni qo'shish eng oqlangan va har doim ham maqbul echim emasligiga qo'shilaman. Yordamchi ustunsiz ham xuddi shunday qilishingiz mumkin, ammo bu funksiyalar kombinatsiyasi bilan ancha murakkab formulani talab qiladi INDEKS (INDEX) va MATh (KO'PROQ FOYDALANGAN).

VLOOKUP yordamida 2, 3 va hokazo qiymatlarni chiqaramiz

Siz buni allaqachon bilasiz VPR faqat bitta mos qiymatni, aniqrog'i, topilgan birinchisini qaytarishi mumkin. Ammo bu qiymat ko'rilgan massivda bir necha marta takrorlansa va siz ulardan 2 yoki 3-chi qismini ajratib olishni xohlasangiz-chi? Agar barcha qiymatlar bo'lsa-chi? Muammo murakkab ko'rinadi, ammo yechim mavjud!

Faraz qilaylik, jadvalning bir ustunida mijozlarning nomlari (mijoz nomi), ikkinchi ustunida esa ular sotib olgan mahsulotlar (Mahsulot) mavjud. Keling, xaridor tomonidan sotib olingan 2, 3 va 4-chi narsalarni topishga harakat qilaylik.

Eng oson yo'li - ustundan oldin yordamchi ustun qo'shish mijoz nomi va uni har bir ismning takroriy raqami bilan mijozlar nomlari bilan to'ldiring, masalan, Jon Doe1, Jon Doe2 va hokazo. Funktsiyadan foydalanib raqamlash bilan hiyla qilamiz COUNTIF (COUNTIF), mijoz nomlari B ustunida joylashganligi sababli:

=B2&COUNTIF($B$2:B2,B2)

=B2&СЧЁТЕСЛИ($B$2:B2;B2)

Shundan so'ng siz oddiy funktsiyadan foydalanishingiz mumkin VPRkerakli tartibni topish uchun. Masalan:

• topish 2-chi mijoz tomonidan buyurtma qilingan mahsulot Dan Brown:

  =VLOOKUP("Dan Brown2",$A$2:$C$16,3,FALSE)

  =ВПР("Dan Brown2";$A$2:$C$16;3;ЛОЖЬ)

• topish 3-chi mijoz tomonidan buyurtma qilingan mahsulot Dan Brown:

  =VLOOKUP("Dan Brown3",$A$2:$C$16,3,FALSE)

  =ВПР("Dan Brown3";$A$2:$C$16;3;ЛОЖЬ)

Aslida, quyidagi rasmda ko'rsatilganidek, matn o'rniga qidiruv qiymati sifatida hujayra havolasini kiritishingiz mumkin:

Agar siz faqat izlayotgan bo'lsangiz 2-e takrorlash, siz murakkabroq formulani yaratib, yordamchi ustunsiz qilishingiz mumkin:

=IFERROR(VLOOKUP($F$2,INDIRECT("$B$"&(MATCH($F$2,Table4[Customer Name],0)+2)&":$C16"),2,FALSE),"")

=ЕСЛИОШИБКА(ВПР($F$2;ДВССЫЛ("$B$"&(ПОИСКПОЗ($F$2;Table4[Customer Name];0)+2)&":$C16");2;ИСТИНА);"")

Ushbu formulada:

• $F$2 – xaridor nomini o‘z ichiga olgan katak (u o‘zgarmagan, diqqat qiling – havola mutlaq);
• $ B $ - ustun mijoz nomi;
• Table4 – Sizning stolingiz (bu joy oddiy diapazon ham bo'lishi mumkin);
• 16 dollar – jadvalingiz yoki diapazoningizning oxirgi katakchasi.

Bu formula faqat ikkinchi mos qiymatni topadi. Qolgan takrorlashlarni chiqarib olishingiz kerak bo'lsa, oldingi yechimdan foydalaning.

Agar sizga barcha mosliklar ro'yxati kerak bo'lsa - funktsiya VPR bu yordamchi emas, chunki u bir vaqtning o'zida faqat bitta qiymatni qaytaradi - davr. Lekin Excelda funksiya bor INDEKS (INDEX), bu vazifani osongina engish mumkin. Bunday formula qanday ko'rinishini quyidagi misolda bilib olasiz.

Istalgan qiymatning barcha takrorlarini oling

Yuqorida aytib o'tilganidek VPR skanerlangan diapazondan barcha takroriy qiymatlarni chiqarib ololmaydi. Buni amalga oshirish uchun sizga bir nechta Excel funktsiyalaridan tashkil topgan biroz murakkabroq formula kerak bo'ladi, masalan INDEKS (INDEKS), Kichik (KICHIK) va Row (LINE)

Masalan, quyidagi formula F2 katakchadagi qiymatning barcha takrorlanishini B2:B16 oralig'ida topadi va C ustunidagi bir xil qatorlar natijasini qaytaradi.

{=IFERROR(INDEX($C$2:$C$16,SMALL(IF($F$2=B2:B16,ROW(C2:C16)-1,""),ROW()-3)),"")}

{=ЕСЛИОШИБКА(ИНДЕКС($C$2:$C$16;НАИМЕНЬШИЙ(ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;"");СТРОКА()-3));"")}

Ushbu massiv formulasini hujayralar kabi bir nechta qo'shni hujayralarga kiriting F4: F8quyidagi rasmda ko'rsatilganidek. Hujayralar soni qidirilayotgan qiymatning maksimal mumkin bo'lgan takroriy soniga teng yoki undan ko'p bo'lishi kerak. Bosishni unutmang Ctrl + Shift + Enter tugmalarini bosingmassiv formulasini to'g'ri kiritish uchun.

Agar siz uning qanday ishlashini tushunmoqchi bo'lsangiz, keling, formulaning tafsilotlariga biroz to'xtalib o'tamiz:

Part 1:

IF($F$2=B2:B16,ROW(C2:C16)-1,"")

ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;"")

$F$2=B2:B16 - F2 katakchadagi qiymatni B2:B16 diapazonidagi qiymatlarning har biri bilan solishtiring. Agar moslik topilsa, u holda ifoda STRING(C2:C16)-1 mos keladigan qatorning raqamini qaytaradi (qiymat -1 sarlavha chizig'ini kiritmaslikka imkon beradi). Agar mos keladiganlar bo'lmasa, funktsiya IF (IF) bo'sh qatorni qaytaradi.

Funktsiya natijasi IF (IF) shunday gorizontal massiv bo'ladi: {1,"",3,"",5,"","","","","","",12,"","",""}

Part 2:

ROW()-3

СТРОКА()-3

Mana funktsiya Row (LINE) qo'shimcha hisoblagich vazifasini bajaradi. Formula F4:F9 yacheykalarga ko'chirilganligi sababli, sonni ayirib tashlaymiz 3 funktsiya natijasidan qiymat olish uchun 1 hujayrada F4 (4-qator, ayirish 3) olish uchun 2 hujayrada F5 (5-qator, ayirish 3) va hokazo.

Part 3:

SMALL(IF($F$2=B2:B16,ROW(C2:C16)-1,""),ROW()-3))

НАИМЕНЬШИЙ(ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;"");СТРОКА()-3))

vazifa Kichik (KICHIK) qaytaradi n-oh ma'lumotlar massividagi eng kichik qiymat. Bizning holatda, qaysi pozitsiyani (eng kichikdan) qaytarish funktsiya bilan belgilanadi Row (LINE) (2-qismga qarang). Shunday qilib, hujayra uchun F4 Funktsiyasi KICHIK({massiv},1) qaytadi 1-chi (eng kichik) massiv elementi, ya'ni 1. Hujayra uchun F5 qaytadi 2-chi massivdagi eng kichik element, ya'ni 3Va boshqalar

Part 4:

INDEX($C$2:$C$16,SMALL(IF($F$2=B2:B16,ROW(C2:C16)-1,""),ROW()-3))

ИНДЕКС($C$2:$C$16;НАИМЕНЬШИЙ(ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;"");СТРОКА()-3))

vazifa INDEKS (INDEX) oddiygina massivdagi muayyan katakning qiymatini qaytaradi C2: C16. Hujayra uchun F4 Funktsiyasi INDEX($C$2:$C$16) qaytib keladi Olmalaruchun F5 Funktsiyasi INDEX($C$2:$C$16) qaytib keladi Shirinliklar va hokazo.

Part 5:

IFERROR()

ЕСЛИОШИБКА()

Nihoyat, formulani funksiya ichiga joylashtiramiz XATO (IFERROR), chunki xato xabari sizni xursand qilishi dargumon #DA (#N/A) agar formuladan koʻchiriladigan katakchalar soni koʻrilayotgan diapazondagi takroriy qiymatlar sonidan kam boʻlsa.

Ma'lum qator va ustunlar bo'yicha XNUMXD qidiruv

Excelda XNUMXD qidiruvni amalga oshirish ma'lum satr va ustun raqami bo'yicha qiymatni qidirishni o'z ichiga oladi. Boshqacha qilib aytganda, siz ma'lum bir satr va ustunning kesishmasida hujayra qiymatini chiqarasiz.

Shunday qilib, jadvalimizga murojaat qilamiz va funktsiyali formula yozamiz VPR, unda mart oyida sotilgan limonlarning narxi haqida ma'lumot topiladi.

XNUMXD qidiruvni amalga oshirishning bir necha yo'li mavjud. Variantlarni ko'rib chiqing va sizga eng mosini tanlang.

VLOOKUP va MATCH funksiyalari

Siz bir qator funktsiyalardan foydalanishingiz mumkin VPR (VLOOKUP) va KO'PROQ (MATCH) maydonlar kesishmasidagi qiymatni topish uchun mahsulot nomi (string) va oy ko'rib chiqilayotgan massivning (ustun):

=VLOOKUP("Lemons",$A$2:$I$9,MATCH("Mar",$A$1:$I$1,0),FALSE)

=ВПР("Lemons";$A$2:$I$9;ПОИСКПОЗ("Mar";$A$1:$I$1;0);ЛОЖЬ)

Yuqoridagi formula oddiy funktsiyadir VPR, bu A2 dan A9 gacha bo'lgan kataklardagi "Limonlar" qiymatining aniq mosligini qidiradi. Ammo mart oyidagi sotuvlar qaysi ustunda ekanligini bilmasligingiz sababli, uchinchi funktsiya argumenti uchun ustun raqamini o'rnatolmaysiz. VPR. Buning o'rniga funktsiya ishlatiladi KO'PROQUshbu ustunni aniqlash uchun.

MATCH("Mar",$A$1:$I$1,0)

ПОИСКПОЗ("Mar";$A$1:$I$1;0)

Inson tiliga tarjima qilingan ushbu formula quyidagilarni anglatadi:

• Biz "Mar" belgilarini qidirmoqdamiz - argument qidiruv_qiymati (qidiruv_qiymati);
• A1 dan I1 gacha bo'lgan hujayralarni ko'rish - argument qidirish_massivi (qidiruv_massivi);
• Aniq moslikni qaytarish - argument moslik_turi (moslik_turi).

foydalanish 0 uchinchi argumentda siz funksiyalarni aytasiz KO'PROQ siz izlayotgan qiymatga to'liq mos keladigan birinchi qiymatni qidiring. Bu qiymatga teng FALSE (FALSE) to'rtinchi dalil uchun VPR.

Ikki o'lchovli qidiruv yoki ikki yo'nalishli qidiruv sifatida ham tanilgan Excelda ikki tomonlama qidiruv formulasini shunday yaratishingiz mumkin.

SUMPRODUCT funksiyasi

vazifa SUMPRODUCT (SUMPRODUCT) tanlangan massivlar mahsuloti yig‘indisini qaytaradi:

=SUMPRODUCT(($A$2:$A$9="Lemons")*($A$1:$I$1="Mar"),$A$2:$I$9)

=СУММПРОИЗВ(($A$2:$A$9="Lemons")*($A$1:$I$1="Mar");$A$2:$I$9)

INDEX va MATCH funksiyalari

Keyingi maqolada men ushbu funktsiyalarni batafsil tushuntiraman, shuning uchun hozircha siz ushbu formuladan nusxa ko'chirishingiz mumkin:

=INDEX($A$2:$I$9,MATCH("Lemons",$A$2:$A$9,0),MATCH("Mar",$A$1:$I$1,0))

=ИНДЕКС($A$2:$I$9;ПОИСКПОЗ("Lemons";$A$2:$A$9;0);ПОИСКПОЗ("Mar";$A$1:$I$1;0))

Nomlangan diapazonlar va kesishish operatori

Agar siz ushbu murakkab Excel formulalarini yaxshi bilmasangiz, sizga ushbu vizual va esda qolarli usul yoqadi:

1. Jadvalni tanlang, yorliqni oching Formulalar (Formulalar) va bosing Tanlovdan yaratish (Tanlovdan yaratish).
2. Qutilarni belgilang Yuqori qator (yuqoridagi qatorda) va Chap ustun (chapdagi ustunda). Microsoft Excel elektron jadvalingizning yuqori satri va chap ustunidagi qiymatlardan diapazonlarga nomlar beradi. Endi siz formulalar yaratmasdan bevosita ushbu nomlar yordamida qidirishingiz mumkin.
3. Har qanday bo'sh katakchaga yozing =satr_nomi ustun_nomi, masalan, shunday:

   = Limonlar mart

   ... yoki aksincha:

   = Mar Limonlar

   Esda tutingki, satr va ustun nomlari bo'sh joy bilan ajratilishi kerak, bu holda u kesishma operatori kabi ishlaydi.

Ismni kiritganingizda, Microsoft Excel xuddi formulani kiritganingizda bo'lgani kabi, mos keladigan nomlar ro'yxati bilan maslahatchini ko'rsatadi.

4. matbuot kiriting va natijani tekshiring

Umuman olganda, yuqoridagi usullardan qaysi birini tanlasangiz, ikki o'lchovli qidiruv natijasi bir xil bo'ladi:

Bitta formulada bir nechta VLOOKUPlardan foydalanish

Shunday bo'ladiki, asosiy jadval va qidiruv jadvalida bitta umumiy ustun bo'lmaydi va bu odatiy funktsiyadan foydalanishingizga xalaqit beradi. VPR. Biroq, bizni qiziqtirgan ma'lumotni o'z ichiga olmaydi, lekin asosiy jadval va qidirish jadvali bilan umumiy ustunga ega bo'lgan yana bir jadval mavjud.

Keling, quyidagi misolni ko'rib chiqaylik. Bizda ustunli asosiy jadval mavjud SKU (yangi), bu erda siz boshqa jadvaldan tegishli narxlar bilan ustun qo'shmoqchisiz. Bundan tashqari, bizda 2 ta qidirish jadvali mavjud. Birinchisi (1-jadvalni qidirish) yangilangan raqamlarni o'z ichiga oladi SKU (yangi) va mahsulot nomlari, ikkinchisi (2-jadvalni qidirish) - mahsulot nomlari va eski raqamlar SKU (eski).

Ikkinchi qidirish jadvalidagi narxlarni asosiy jadvalga qo'shish uchun siz double deb nomlanuvchi amalni bajarishingiz kerak VPR yoki o'rnatilgan VPR.

1. Funktsiyani yozing VPR, bu jadvaldagi mahsulot nomini topadi Qidiruv jadvali 1foydalanish Navi, kerakli qiymat sifatida:

   =VLOOKUP(A2,New_SKU,2,FALSE)

   =ВПР(A2;New_SKU;2;ЛОЖЬ)

   shu yerda Yangi_SKU - nomli diapazon $A:$B jadvalda Qidiruv jadvali 1, 2 - bu tovarlarning nomlarini o'z ichiga olgan B ustuni (yuqoridagi rasmga qarang)

2. Jadvaldan narxlarni kiritish formulasini yozing Qidiruv jadvali 2 taniqli mahsulot nomlari asosida. Buning uchun avval yaratgan formulani yangi funksiya uchun qidiruv qiymati sifatida joylashtiring VPR:

   =VLOOKUP(VLOOKUP(A2,New_SKU,2,FALSE),Price,3,FALSE)

   =ВПР(ВПР(A2;New_SKU;2;ЛОЖЬ);Price;3;ЛОЖЬ)

   shu yerda narx - nomli diapazon $A:$C jadvalda Qidiruv jadvali 2, 3 narxlarni o'z ichiga olgan C ustunidir.

Quyidagi rasmda biz yaratgan formula bo'yicha qaytarilgan natija ko'rsatilgan:

VLOOKUP va INDIRECT yordamida turli jadvallardagi ma'lumotlarni dinamik almashtirish

Birinchidan, bir-birimizni to'g'ri tushunishimizga ishonch hosil qilish uchun "Turli jadvallardagi ma'lumotlarni dinamik almashtirish" iborasi nimani anglatishini aniqlab olaylik.

Xuddi shu formatdagi ma'lumotlarga ega bir nechta varaqlar mavjud bo'lgan holatlar mavjud va ma'lum bir varaqdan kerakli ma'lumotlarni, ma'lum bir katakka kiritilgan qiymatga qarab ajratib olish kerak bo'ladi. Menimcha, buni misol bilan tushuntirish osonroq.

Tasavvur qiling-a, sizda bir xil mahsulot va bir xil formatdagi bir nechta mintaqalar uchun savdo hisobotlari mavjud. Muayyan mintaqa uchun savdo ko'rsatkichlarini topmoqchisiz:

Agar sizda faqat ikkita bunday hisobot bo'lsa, unda siz funktsiyalari bilan sharmandali oddiy formuladan foydalanishingiz mumkin VPR и IF (IF) qidirish uchun kerakli hisobotni tanlash uchun:

=VLOOKUP($D$2,IF($D3="FL",FL_Sales,CA_Sales),2,FALSE)

=ВПР($D$2;ЕСЛИ($D3="FL";FL_Sales;CA_Sales);2;ЛОЖЬ)

qaerda:

• $D$2 mahsulot nomini o'z ichiga olgan katakdir. Esda tutingki, formulani boshqa kataklarga nusxalashda qidiruv qiymatini o'zgartirmaslik uchun bu yerda mutlaq havolalardan foydalanamiz.
• $D3 hudud nomi yozilgan katakdir. Biz mutlaq ustun ma'lumotnomasidan va nisbiy satr ma'lumotnomasidan foydalanmoqdamiz, chunki biz formulani xuddi shu ustundagi boshqa kataklarga nusxalashni rejalashtirmoqdamiz.
• FL_Sales и CA_Sales - tegishli savdo hisobotlarini o'z ichiga olgan jadvallar (yoki nomli diapazonlar) nomlari. Albatta, siz, masalan, odatdagi varaq nomlari va hujayra diapazoniga havolalardan foydalanishingiz mumkin 'FL Sheet'!$A$3:$B$10, lekin nomli diapazonlar ancha qulayroq.

Biroq, bunday jadvallar ko'p bo'lganda, funktsiya IF eng yaxshi yechim emas. Buning o'rniga siz funktsiyadan foydalanishingiz mumkin BILVOSIT (INDIRECT) kerakli qidiruv diapazonini qaytarish uchun.

Siz bilganingizdek, funktsiya BILVOSIT matn satri orqali berilgan havolani qaytarish uchun ishlatiladi, bu bizga hozir kerak bo'lgan narsadir. Shunday qilib, yuqoridagi formuladagi ifodani funksiya bilan jasorat bilan almashtiring IF funktsiyasi bilan bog'lash BILVOSIT. Mana kombinatsiya VPR и BILVOSIT bilan juda yaxshi ishlaydi:

=VLOOKUP($D$2,INDIRECT($D3&"_Sales"),2,FALSE)

=ВПР($D$2;ДВССЫЛ($D3&"_Sales");2;ЛОЖЬ)

qaerda:

• $D$2 - bu mahsulot nomiga ega bo'lgan katak, u mutlaq havola tufayli o'zgarmasdir.
• $D3 mintaqa nomining birinchi qismini o'z ichiga olgan katakdir. Bizning misolimizda, bu FL.
• _Sotish - barcha nomlangan diapazonlar yoki jadvallar nomining umumiy qismi. D3 katakdagi qiymat bilan birlashtirilganda, u kerakli diapazonning to'liq malakali nomini hosil qiladi. Funktsiyaga yangi kelganlar uchun quyida ba'zi tafsilotlar mavjud BILVOSIT.

INDIRECT va VLOOKUP qanday ishlaydi

Birinchidan, funktsiyaning sintaksisini eslatib o'taman BILVOSIT (bilvosita):

INDIRECT(ref_text,[a1])

ДВССЫЛ(ссылка_на_текст;[a1])

Birinchi argument hujayraga havola (A1 yoki R1C1 uslubi), diapazon nomi yoki matn qatori bo'lishi mumkin. Ikkinchi argument birinchi argumentda qanday havola uslubi mavjudligini aniqlaydi:

• A1agar argument bo'lsa HAQIQIY KOD (TRUE) yoki ko'rsatilmagan;
• R1C1, agar FAS E (YOLG'ON).

Bizning holatda, havola uslubga ega A1, shuning uchun siz ikkinchi dalilni qoldirib, birinchisiga e'tibor qaratishingiz mumkin.

Keling, savdo hisobotimizga qaytaylik. Esingizda bo'lsa, har bir hisobot alohida varaqda joylashgan alohida jadvaldir. Formulaning to'g'ri ishlashi uchun siz jadvallarni (yoki diapazonlarni) nomlashingiz kerak va barcha nomlar umumiy qismga ega bo'lishi kerak. Masalan, bu kabi: CA_Sales, FL_Sotish, TX_Sotish va hokazo. Ko'rib turganingizdek, "_Sales" barcha nomlarda mavjud.

vazifa BILVOSIT D ustunidagi qiymatni va "_Sotish" matn qatorini bog'laydi, shu bilan aytadi VPR qaysi jadvalda qidirish kerak. Agar D3 katakchasida "FL" qiymati bo'lsa, formula jadvalni qidiradi FL_Sotish, agar "CA" bo'lsa - jadvalda CA_Sales va hokazo.

Funktsiyalarning natijasi VPR и BILVOSIT quyidagilar bo'ladi:

Agar ma'lumotlar turli xil Excel kitoblarida joylashgan bo'lsa, unda siz kitob nomini nomlangan diapazondan oldin qo'shishingiz kerak, masalan:

=VLOOKUP($D$2,INDIRECT($D3&"Workbook1!_Sales"),2,FALSE)

=ВПР($D$2;ДВССЫЛ($D3&"Workbook1!_Sales");2;ЛОЖЬ)

Agar funktsiya BILVOSIT boshqa ish kitobiga ishora qilsa, u ish kitobi ochiq bo'lishi kerak. Agar u yopilsa, funksiya xato haqida xabar beradi. #REF! (#SSYL!).