Mundarija
Biz yaqinda Excelning eng foydali funksiyalaridan biriga maqola bag'ishladik VPR va uning yordamida ma'lumotlar bazasidan kerakli ma'lumotlarni ishchi varaq katakchasiga chiqarish uchun qanday foydalanish mumkinligini ko'rsatdi. Funktsiyadan ikkita foydalanish holatlari mavjudligini ham aytib o'tdik VPR va ulardan faqat bittasi ma'lumotlar bazasi so'rovlari bilan shug'ullanadi. Ushbu maqolada siz funktsiyadan foydalanishning kamroq ma'lum bo'lgan boshqa usulini bilib olasiz VPR Excelda.
Agar siz hali buni qilmagan bo'lsangiz, funktsiya haqidagi oxirgi maqolani o'qing VPR, chunki quyida keltirilgan barcha ma'lumotlar siz birinchi maqolada tasvirlangan tamoyillar bilan allaqachon tanish ekanligingizni nazarda tutadi.
Ma'lumotlar bazalari, funktsiyalari bilan ishlashda VPR noyob identifikator uzatiladi, u biz topmoqchi bo'lgan ma'lumotni aniqlash uchun ishlatiladi (masalan, mahsulot kodi yoki mijozning identifikatsiya raqami). Ushbu noyob kod ma'lumotlar bazasida bo'lishi kerak, aks holda VPR xato haqida xabar beradi. Ushbu maqolada biz funktsiyadan foydalanishning ushbu usulini ko'rib chiqamiz VPRid ma'lumotlar bazasida umuman mavjud bo'lmaganda. Go'yo funktsiya VPR taxminiy rejimga o'tadi va biror narsani topmoqchi bo'lganimizda bizga qanday ma'lumotlarni taqdim etishni tanlaydi. Muayyan sharoitlarda aynan shu narsa kerak bo'ladi.
Hayotdan misol. Biz vazifani qo'ydik
Keling, ushbu maqolani hayotiy misol bilan ko'rsatamiz - keng ko'lamli savdo ko'rsatkichlari asosida komissiyalarni hisoblash. Biz juda oddiy variantdan boshlaymiz, so'ngra muammoning yagona oqilona yechimi funksiyadan foydalanish bo'lmaguncha uni asta-sekin murakkablashtiramiz. VPR. Bizning xayoliy vazifamizning dastlabki stsenariysi quyidagicha: agar sotuvchi bir yilda 30000 30 dollardan ortiq savdo qilsa, uning komissiyasi 20% ni tashkil qiladi. Aks holda, komissiya faqat XNUMX% ni tashkil qiladi. Keling, uni jadval shaklida ko'rsatamiz:
Sotuvchi o'zining savdo ma'lumotlarini B1 katakka kiritadi va B2 katakdagi formula sotuvchi kutishi mumkin bo'lgan to'g'ri komissiya stavkasini aniqlaydi. O'z navbatida, olingan stavka B3 katakchasida sotuvchi olishi kerak bo'lgan umumiy komissiyani hisoblash uchun ishlatiladi (shunchaki B1 va B2 katakchalarini ko'paytirish).
Jadvalning eng qiziqarli qismi B2 katakchasida joylashgan - bu komissiya stavkasini aniqlash formulasi. Ushbu formulada Excel funksiyasi mavjud IF (IF). Ushbu funksiya bilan tanish bo'lmagan o'quvchilar uchun men uning qanday ishlashini tushuntiraman:
IF(condition, value if true, value if false)
ЕСЛИ(условие; значение если ИСТИНА; значение если ЛОЖЬ)
Ahvoli ikkalasining qiymatini qabul qiluvchi funktsiya argumentidir HAQIQIY KOD (TRUE) yoki FALSE (YOLG'ON). Yuqoridagi misolda B1 ifodasi
B1 B5 dan kichik ekanligi rostmi?
Yoki boshqacha aytishingiz mumkin:
Yil davomida sotishning umumiy miqdori chegara qiymatidan kamroq ekanligi rostmi?
Bu savolga javob bersak YES (TRUE), keyin funksiya qaytadi agar rost bo'lsa qiymat (qiymati TRUE bo'lsa). Bizning holatda, bu B6 katakchaning qiymati bo'ladi, ya'ni umumiy savdo chegaradan past bo'lganda komissiya stavkasi. Savolga javob bersak Yo'q (FALSE) keyin qaytadi qiymati noto'g'ri bo'lsa (Qiymat FALSE bo'lsa). Bizning holatda, bu B7 katakchaning qiymati, ya'ni umumiy savdo chegarasidan yuqori bo'lgan komissiya stavkasi.
Ko'rib turganingizdek, agar biz $20000 umumiy savdoni olsak, biz B2 katakchasida 20% komissiya stavkasini olamiz. Agar biz $40000 qiymatini kiritsak, komissiya stavkasi 30% ga o'zgaradi:
Bizning stolimiz shunday ishlaydi.
Biz vazifani murakkablashtiramiz
Keling, ishni biroz qiyinlashtiraylik. Keling, yana bir chegarani belgilaymiz: agar sotuvchi 40000 40 dollardan ortiq daromad olsa, komissiya stavkasi XNUMX% gacha ko'tariladi:
Hamma narsa oddiy va tushunarli ko'rinadi, ammo B2 katakdagi formulamiz sezilarli darajada murakkablashadi. Agar siz formulaga diqqat bilan qarasangiz, funktsiyaning uchinchi argumenti ekanligini ko'rasiz IF (IF) boshqa to'liq funktsiyaga aylandi IF (IF). Ushbu konstruktsiya funktsiyalarni bir-biriga joylashtirish deb ataladi. Excel bu konstruktsiyalarga mamnuniyat bilan ruxsat beradi va ular hatto ishlaydi, lekin ularni o'qish va tushunish ancha qiyinroq.
Biz texnik tafsilotlarni - nima uchun va qanday ishlashini ko'rib chiqmaymiz va ichki o'rnatilgan funktsiyalarni yozishning nuanslariga kirmaymiz. Axir, bu funktsiyaga bag'ishlangan maqola VPR, Excel uchun to'liq qo'llanma emas.
Qanday bo'lmasin, formula yanada murakkablashadi! Savdoda $50 50000 dan ortiq daromad oladigan sotuvchilar uchun 60000% komissiya stavkasi uchun boshqa variantni joriy qilsak nima bo'ladi? Va agar kimdir 60 XNUMX dollardan ortiq sotgan bo'lsa, u XNUMX% komissiya to'laydimi?
Endi B2 katakdagi formula, hatto xatosiz yozilgan bo'lsa ham, butunlay o'qib bo'lmaydigan bo'lib qoldi. Menimcha, o'z loyihalarida 4 darajali uyalar bilan formulalardan foydalanishni istaganlar kam. Buning osonroq yo'li bo'lsa kerak?!
Va bunday yo'l bor! Funktsiya bizga yordam beradi VPR.
Muammoni hal qilish uchun VLOOKUP funksiyasidan foydalanamiz
Keling, stolimiz dizaynini biroz o'zgartiraylik. Biz barcha maydonlar va ma'lumotlarni saqlab qolamiz, lekin ularni yangi, ixchamroq tarzda tartibga solamiz:
Bir oz vaqt ajrating va yangi stolga ishonch hosil qiling Baholar jadvali oldingi chegara jadvali bilan bir xil ma'lumotlarni o'z ichiga oladi.
Asosiy g'oya funktsiyadan foydalanishdir VPR jadvalga muvofiq kerakli tarif stavkasini aniqlash Baholar jadvali sotish hajmiga qarab. E'tibor bering, sotuvchi tovarni jadvaldagi beshta chegaradan biriga teng bo'lmagan miqdorda sotishi mumkin. Misol uchun, u 34988 dollarga sotishi mumkin edi, ammo bunday miqdor yo'q. Funktsiya qanday ishlashini ko'rib chiqaylik VPR bunday vaziyatga dosh bera oladi.
VLOOKUP funksiyasini kiritish
B2 katakchasini tanlang (biz formulamizni kiritmoqchi bo'lgan joyga) va toping KO'RISH Excel funktsiyalari kutubxonasida (VLOOKUP): Formulalar (formulalar) > Funktsiya kutubxonasi (Funktsiyalar kutubxonasi) > Izlash va ma'lumotnoma (Ma'lumotnomalar va massivlar).
Muloqot oynasi paydo bo'ladi Funktsiya argumentlari (Funksiya argumentlari). Biz argumentlarning qiymatlarini birma-bir to'ldiramiz Qiymatni qidirish (Qidiruv_qiymati). Ushbu misolda, bu B1 katakchasidagi sotuvlarning umumiy miqdori. Kursorni maydonga qo'ying Qiymatni qidirish (Qidiruv_qiymati) va B1 katakchasini tanlang.
Keyinchalik, siz funktsiyalarni belgilashingiz kerak VPRma'lumotlarni qaerdan qidirish kerak. Bizning misolimizda bu jadval Baholar jadvali. Kursorni maydonga qo'ying Jadval_massivi (Jadval) va butun jadvalni tanlang Baholar jadvalisarlavhalardan tashqari.
Keyinchalik, formulamizdan ma'lumotlarni olish uchun qaysi ustunni belgilashimiz kerak. Bizni komissiya stavkasi qiziqtiradi, bu jadvalning ikkinchi ustunida joylashgan. Shuning uchun, argument uchun Col_index_num (Ustun_raqami) 2 qiymatini kiriting.
Va nihoyat, biz oxirgi dalilni keltiramiz - Range_lookup (Interval_qidirish).
muhim: funktsiyani qo'llashning ikkita usuli o'rtasidagi farqni bu argumentdan foydalanish hisoblanadi VPR. Ma'lumotlar bazalari bilan ishlashda argument Range_lookup (range_lookup) har doim qiymatga ega bo'lishi kerak FALSE (FALSE) aniq moslikni qidirish uchun. Funktsiyadan foydalanishimizda VPR, biz bu maydonni bo'sh qoldirishimiz yoki qiymat kiritishimiz kerak HAQIQIY KOD (TO'G'RI). Ushbu variantni to'g'ri tanlash juda muhimdir.
Buni aniqroq qilish uchun biz tanishtiramiz HAQIQIY KOD (TRUE) sohasida Range_lookup (Interval_qidirish). Garchi, agar siz maydonni bo'sh qoldirsangiz, bu xato bo'lmaydi, chunki HAQIQIY KOD uning standart qiymati:
Biz barcha parametrlarni to'ldirdik. Endi bosamiz OK, va Excel biz uchun funktsiya bilan formula yaratadi VPR.
Agar biz umumiy savdo miqdori uchun bir nechta turli qiymatlar bilan tajriba o'tkazsak, formulaning to'g'ri ishlashiga ishonch hosil qilamiz.
Xulosa
Funktsiya qachon VPR ma'lumotlar bazalari, argumentlar bilan ishlaydi Range_lookup (range_lookup) qabul qilishi kerak FALSE (YOLG'ON). Va qiymat sifatida kiritilgan Qiymatni qidirish (Lookup_value) ma'lumotlar bazasida mavjud bo'lishi kerak. Boshqacha qilib aytganda, u aniq moslikni qidirmoqda.
Ushbu maqolada biz ko'rib chiqqan misolda aniq moslikni olishning hojati yo'q. Bu funksiya qachon sodir bo'ladi VPR kerakli natijani qaytarish uchun taxminiy rejimga o'tish kerak.
Masalan: Savdo hajmi 34988 dollar bo'lgan sotuvchi uchun komissiyani hisoblashda qanday stavkadan foydalanishni aniqlamoqchimiz. Funktsiya VPR bizga 30% qiymatini qaytaradi, bu mutlaqo to'g'ri. Lekin nima uchun formula 30% yoki 20% emas, aynan 40% ni o'z ichiga olgan qatorni tanladi? Taxminiy qidiruv deganda nima tushuniladi? Keling, aniq aytaylik.
Munozara bo'lganda Range_lookup (interval_lookup) qiymatga ega HAQIQIY KOD (TRUE) yoki o‘tkazib yuborilgan funksiya VPR birinchi ustun bo'ylab takrorlanadi va qidiruv qiymatidan oshmaydigan eng katta qiymatni tanlaydi.
Muhim nuqta: Ushbu sxema ishlashi uchun jadvalning birinchi ustunini o'sish tartibida tartiblash kerak.