Kim juda dangasa yoki o'qishga vaqti yo'q - videoni tomosha qiling. Tafsilotlar va nuanslar quyidagi matnda.
Muammoni shakllantirish
Shunday qilib, bizda ikkita jadval bor - buyurtma jadvali и narxlar ro'yxati:
Vazifa shundan iboratki, narxlar ro'yxatidan narxlarni avtomatik ravishda buyurtmalar jadvaliga almashtirish, mahsulot nomiga e'tibor qaratish, keyinroq narxni hisoblashingiz mumkin.
qaror
Excel funktsiyasi to'plamida, kategoriya ostida Malumot va massivlar (Qidiruv va ma'lumotnoma) funksiya mavjud VPR (KO'RISH).Ushbu funktsiya yuqoridan pastgacha harakatlanadigan jadvalning (narxlar ro'yxati) eng chap ustunida berilgan qiymatni (bizning misolimizda, bu "Olma" so'zi) qidiradi va uni topib, qo'shni katakning tarkibini ko'rsatadi. (23 rubl) .Sxematik ravishda ushbu funktsiyaning ishlashini ifodalash mumkin:
Funktsiyadan keyingi foydalanish qulayligi uchun bir vaqtning o'zida bitta narsani bajaring - narxlar ro'yxatidagi hujayralar diapazoniga o'z nomingizni bering. Buni amalga oshirish uchun narxlar ro'yxatining "sarlavha" (G3: H19) dan tashqari barcha katakchalarini tanlang, menyudan tanlang. Qo'shish - Ism - tayinlash (Qo'shish - Ism - Aniqlash) yoki tugmasini bosing CTRL + F3 va shunga o'xshash har qanday nomni (bo'shliqsiz) kiriting narx… Endi, kelajakda siz ushbu nomdan narxlar ro'yxatiga havola qilish uchun foydalanishingiz mumkin.
Endi biz funktsiyadan foydalanamiz VPR… U kiritiladigan katakchani tanlang (D3) va yorliqni oching Formulalar - Funktsiyani kiritish (Formulalar - Funktsiyani kiritish)... Turkumda Malumot va massivlar (Qidiruv va ma'lumotnoma) funksiyasini toping VPR (KO'RISH) va matbuot OK… Funktsiya uchun argumentlarni kiritish oynasi paydo bo'ladi:
Biz ularni navbat bilan to'ldiramiz:
- Istalgan qiymat (Qidiruv qiymati) – funktsiya narxlar ro'yxatining eng chap ustunida topishi kerak bo'lgan mahsulot nomi. Bizning holatda, B3 hujayradan "Olma" so'zi.
- stol (Jadval massivi) - uXNUMXbuXNUMX kerakli qiymatlari olingan jadval, ya'ni bizning narxlar ro'yxatimiz. Ma'lumot uchun, biz ilgari berilgan "Narx" nomini ishlatamiz. Agar siz nom bermagan bo'lsangiz, faqat jadvalni tanlashingiz mumkin, lekin tugmasini bosishni unutmang F4dollar belgilari bilan bog'lanish uchun, chunki aks holda, formulamizni D3:D30 ustunidagi qolgan hujayralarga nusxalashda u pastga siljiydi.
- Ustun_raqami (Ustun indeks raqami) Narxlar ro'yxatidagi ustunning seriya raqami (harf emas!) Biz narx qiymatlarini olamiz. Narxlar ro'yxatining nomlari bilan birinchi ustuni 1 raqami bilan raqamlangan, shuning uchun bizga 2-sonli ustundan narx kerak.
- intervalli_qidirish (Diapazonni qidirish) - bu maydonga faqat ikkita qiymat kiritish mumkin: FALSE yoki TRUE:
- Agar qiymat kiritilsa 0 or YOLG'ON (YOLG'ON), keyin aslida bu faqat qidiruvga ruxsat berilganligini anglatadi aniq o'yin, ya'ni funksiya narxlar ro'yxatida buyurtmalar jadvalida ko'rsatilgan nostandart elementni topmasa (masalan, "Kokos" kiritilsa), u #N/A (ma'lumot yo'q) xatosini keltirib chiqaradi.
- Agar qiymat kiritilsa 1 or HAQ (TO'G'RI), keyin bu siz aniq emas, balki qidiruvga ruxsat berishingizni anglatadi taxminiy moslik, ya'ni "kokos" holatida funktsiya "kokos" ga imkon qadar yaqin bo'lgan nomga ega mahsulotni topishga harakat qiladi va bu nom uchun narxni qaytaradi. Aksariyat hollarda bunday taxminiy almashtirish, aslida mavjud bo'lgan noto'g'ri mahsulotning qiymatini almashtirish orqali foydalanuvchini hiyla-nayrang qilishi mumkin! Shunday qilib, ko'pgina haqiqiy biznes muammolari uchun taxminiy qidiruvga ruxsat bermaslik yaxshiroqdir. Istisno biz matnni emas, balki raqamlarni izlayotganimizda - masalan, qadam chegirmalarini hisoblashda.
Hammasi! Bosish qoladi OK va kiritilgan funksiyani butun ustunga nusxalash.
# N / A xatolar va ularni bostirish
vazifa VPR (KO'RISH) #N/A xatosini qaytaradi (#N/A) agar a:
- Aniq qidiruv yoqilgan (argument Intervalli ko'rinish = 0) va kerakli nom mavjud emas stol.
- Qo'pol qidiruv kiritilgan (Intervalli ko'rinish = 1), lekin stol, qidiruv amalga oshirilayotgan ismlarning ortib borish tartibida tartiblanmagan.
- Ismning talab qilinadigan qiymati kelgan katakning formati (masalan, bizning holatlarimizda B3) va jadvalning birinchi ustunidagi katakchalar formati (F3: F19) har xil (masalan, raqamli va matn). ). Bu holat, ayniqsa, matn nomlari o'rniga raqamli kodlardan (hisob raqamlari, identifikatorlar, sanalar va boshqalar) foydalanilganda odatiy holdir. Bunday holda siz funktsiyalardan foydalanishingiz mumkin Ч и TEXT ma'lumotlar formatlarini aylantirish uchun. Bu shunday ko'rinadi:
=VLOOKUP(MATN(B3),narxi,0)
Bu haqda ko'proq ma'lumotni bu yerda o'qishingiz mumkin.
- Funktsiya kerakli qiymatni topa olmaydi, chunki kodda bo'shliqlar yoki ko'rinmas bosma belgilar (satr uzilishlari va boshqalar) mavjud. Bunday holda siz matn funksiyalaridan foydalanishingiz mumkin TRIM (TRIM) и BOSIB(TOZA) ularni olib tashlash uchun:
=VLOOKUP(TRIMSPACELAR(TOZA(B3)),narxi,0)
=VLOOKUP(TRIM(TOZA(B3));narxi;0)
Xato xabarini bostirish uchun # Yo'q (#N/A) funksiya aniq moslikni topa olmasa, funksiyadan foydalanishingiz mumkin XATO (XATO)… Shunday qilib, masalan, ushbu konstruktsiya VLOOKUP tomonidan yaratilgan har qanday xatolarni to'xtatadi va ularni nol bilan almashtiradi:
= XATOQ (VLOOKUP (B3, narx, 2, 0), 0)
= XATOQ (VLOOKUP (B3; narx; 2; 0); 0)
PS
Agar siz bir vaqtning o'zida bitta qiymatni emas, balki butun to'plamni ajratib olishingiz kerak bo'lsa (agar bir nechta turli xil bo'lsa), unda siz massiv formulasi bilan shamanizatsiya qilishingiz kerak bo'ladi. yoki Office 365-ning yangi XLOOKUP funksiyasidan foydalaning.
- VLOOKUP funksiyasining takomillashtirilgan versiyasi (VLOOKUP 2).
- VLOOKUP funksiyasidan foydalangan holda qadam (diapazon) chegirmalarini tezkor hisoblash.
- INDEX va MATCH funksiyalaridan foydalanib, “chap VLOOKUP”ni qanday qilish mumkin
- Shakllarni ro'yxatdagi ma'lumotlar bilan to'ldirish uchun VLOOKUP funksiyasidan qanday foydalanish kerak
- Jadvaldan birdaniga birinchi emas, balki barcha qiymatlarni qanday chiqarish mumkin
- VLOOKUP2 va VLOOKUP3 funksiyalari PLEX plaginidan ishlaydi