Mundarija
Ushbu dars funktsiya mavjud bo'lgan vaziyatni qanday tezda hal qilishni tushuntiradi VPR (VLOOKUP) Excel 2013, 2010, 2007 va 2003-da ishlashni istamaydi, shuningdek, umumiy xatolarni qanday aniqlash va tuzatish hamda cheklovlarni yengish VPR.
Oldingi bir nechta maqolalarda biz funktsiyaning turli tomonlarini ko'rib chiqdik VPR Excelda. Agar siz ularni diqqat bilan o'qib chiqqan bo'lsangiz, endi siz ushbu sohada mutaxassis bo'lishingiz kerak. Biroq, ko'pgina Excel mutaxassislari bejiz ishonishmaydi VPR murakkab xususiyatlaridan biri. U ko'plab muammolar va xatolar manbai bo'lgan bir qator cheklovlar va xususiyatlarga ega.
Ushbu maqolada siz xatolarning oddiy tushuntirishlarini topasiz #DA (#N/A), #NAME? (#NAME?) va #QIMMAT! Funktsiya bilan ishlashda paydo bo'ladigan (#VALUE!). VPR, shuningdek, ular bilan ishlash texnikasi va usullari. Biz eng ko'p uchraydigan holatlar va eng aniq sabablari bilan boshlaymiz. VPR ishlamaydi, shuning uchun misollarni maqolada keltirilgan tartibda o'rganish yaxshiroqdir.
Excelda VLOOKUP funksiyasida #N/A xatosini tuzatish
Formulalarda VPR xato xabari #DA (#N/A) degani mavjud emas (ma'lumot yo'q) - Excel siz qidirayotgan qiymatni topa olmasa paydo bo'ladi. Bu bir necha sabablarga ko'ra sodir bo'lishi mumkin.
1. Kerakli qiymat xato yozilgan
Avval ushbu elementni tekshirish yaxshi fikr! Ko'pincha minglab satrlardan iborat juda katta hajmdagi ma'lumotlar bilan ishlaganingizda yoki siz izlayotgan qiymat formulaga yozilganda matn terish xatolari yuzaga keladi.
2. VLOOKUP bilan taxminiy moslikni qidirishda #N/A xatosi
Agar siz taxminiy mos keladigan qidiruv sharti bilan formuladan foydalansangiz, ya'ni argument diapazonni_qidirish (diapazon_qidiruv) TRUE yoki belgilanmagan, formulangiz xato haqida xabar berishi mumkin # Yo'q ikki holatda:
- Qidiriladigan qiymat izlanayotgan massivdagi eng kichik qiymatdan kichik.
- Qidiruv ustuni o'sish tartibida tartiblanmagan.
3. VLOOKUP bilan aniq moslikni izlashda #N/A xatosi
Agar siz aniq moslikni, ya'ni argumentni qidirsangiz diapazonni_qidirish (range_lookup) FALSE va aniq qiymat topilmadi, formula ham xato haqida xabar beradi # Yo'q. Funktsiya bilan aniq va taxminiy mosliklarni qanday qidirish haqida ko'proq bilib oling VPR.
4. Qidiruv ustuni eng chap tomonda emas
Siz bilganingizdek, eng muhim cheklovlardan biri VPR u chapga qaray olmaydi, shuning uchun jadvalingizdagi qidirish ustuni eng chap tomonda bo'lishi kerak. Amalda biz ko'pincha bu haqda unutamiz, bu ishlamaydigan formulaga va xatolikka olib keladi. # Yo'q.
Qaror: Agar ma'lumotlar strukturasini qidirish ustuni eng chap tomonda bo'lishi uchun o'zgartirish imkoni bo'lmasa, siz funktsiyalar kombinatsiyasidan foydalanishingiz mumkin. INDEKS (INDEX) va KO'PROQ (MATCH) uchun yanada moslashuvchan muqobil sifatida VPR.
5. Raqamlar matn sifatida formatlanadi
Yana bir xato manbai # Yo'q bilan formulalarda VPR Asosiy jadval yoki qidirish jadvalidagi matn formatidagi raqamlar.
Bu odatda tashqi ma'lumotlar bazalaridan ma'lumotlarni import qilganda yoki bosh nolni saqlash uchun raqam oldiga apostrof qo'yganingizda sodir bo'ladi.
Matn formatidagi raqamning eng aniq belgilari quyidagi rasmda ko'rsatilgan:
Bundan tashqari, raqamlar formatda saqlanishi mumkin umumiy (Umumiy). Bunday holda, faqat bitta sezilarli xususiyat mavjud - raqamlar hujayraning chap chetiga to'g'ri keladi, sukut bo'yicha ular o'ng chetiga to'g'ri keladi.
Qaror: Agar u bitta qiymat bo'lsa, shunchaki xato belgisini bosing va tanlang Raqamga aylantirish (Raqamga aylantirish) kontekst menyusidan.
Agar bu ko'plab raqamlar bilan bog'liq bo'lsa, ularni tanlang va tanlangan maydonni o'ng tugmasini bosing. Ko'rsatilgan kontekst menyusida tanlang Hujayralarni formatlash (Uyachalarni formatlash) > yorlig'i soni (Raqam) > format soni (Raqamli) va bosing OK.
6. Boshida yoki oxirida bo'sh joy mavjud
Bu xatoning eng kam aniq sababi. # Yo'q funktsiyada VPR, chunki bu qo'shimcha bo'shliqlarni ko'rish vizual jihatdan qiyin, ayniqsa katta jadvallar bilan ishlashda, ma'lumotlarning aksariyati ekrandan tashqarida bo'lganda.
Yechim 1: Asosiy jadvalda qo‘shimcha bo‘shliqlar (VLOOKUP funksiyasi joylashgan joyda)
Agar asosiy jadvalda qo'shimcha bo'shliqlar paydo bo'lsa, argumentni qo'shish orqali formulalar to'g'ri ishlashiga ishonch hosil qilishingiz mumkin. qidiruv_qiymati (qidiruv_qiymati) funksiyaga kiritiladi TRIM (TRIM):
=VLOOKUP(TRIM($F2),$A$2:$C$10,3,FALSE)
=ВПР(СЖПРОБЕЛЫ($F2);$A$2:$C$10;3;ЛОЖЬ)
Yechim 2: Qidiruv jadvalidagi qo‘shimcha bo‘shliqlar (qidiruv ustunida)
Qidiruv ustunida qo'shimcha joylar bo'lsa - oddiy usullar # Yo'q bilan formulada VPR oldini olish mumkin emas. O'rniga VPR Funktsiyalar kombinatsiyasi bilan massiv formulasidan foydalanishingiz mumkin INDEKS (INDEKS), KO'PROQ (MATCH) i TRIM (TRIM):
=INDEX($C$2:$C$10,MATCH(TRUE,TRIM($A$2:$A$10)=TRIM($F$2),0))
=ИНДЕКС($C$2:$C$10;ПОИСКПОЗ(ИСТИНА;СЖПРОБЕЛЫ($A$2:$A$10)=СЖПРОБЕЛЫ($F$2);0))
Bu massiv formulasi bo'lgani uchun bosishni unutmang Ctrl + Shift + Enter tugmalarini bosing odatdagi o'rniga kiritingformulani to'g'ri kiritish uchun.
#VALUE xato! VLOOKUP bilan formulalarda
Aksariyat hollarda Microsoft Excel xato haqida xabar beradi #QIMMAT! (#VALUE!) formulada ishlatiladigan qiymat maʼlumotlar turiga mos kelmasa. Haqida VPR, keyin odatda xatoning ikkita sababi bor #QIMMAT!.
1. Siz izlayotgan qiymat 255 belgidan uzunroq
Ehtiyot bo'ling: funktsiya VPR 255 dan ortiq belgidan iborat qiymatlarni qidira olmaydi. Agar siz izlayotgan qiymat ushbu chegaradan oshsa, siz xato xabarini olasiz. #QIMMAT!.
Qaror: Bir qator xususiyatlardan foydalaning INDEX+MATCH (INDEX + MATCH). Quyida ushbu vazifani bajaradigan formula mavjud:
=INDEX(C2:C7,MATCH(TRUE,INDEX(B2:B7=F$2,0),0))
=ИНДЕКС(C2:C7;ПОИСКПОЗ(ИСТИНА;ИНДЕКС(B2:B7=F$2;0);0))
2. Qidiruv ish kitobiga to'liq yo'l ko'rsatilmagan
Agar siz boshqa ish kitobidan ma'lumotlarni olayotgan bo'lsangiz, ushbu faylga to'liq yo'lni ko'rsatishingiz kerak. Aniqroq qilib aytadigan bo'lsak, ish kitobi nomini (shu jumladan kengaytmani) kvadrat qavsga [ ], keyin varaq nomini va undan keyin undov belgisini kiritishingiz kerak. Agar kitob yoki varaq nomida bo'sh joy bo'lsa, bu konstruktsiyalarning barchasi apostroflar bilan o'ralgan bo'lishi kerak.
Bu erda funktsiyaning to'liq tuzilishi VPR boshqa kitobda qidirish uchun:
=VLOOKUP(lookup_value,'[workbook name]sheet name'!table_array, col_index_num,FALSE)
=ВПР(искомое_значение;'[имя_книги]имя_листа'!таблица;номер_столбца;ЛОЖЬ)
Haqiqiy formula quyidagicha ko'rinishi mumkin:
=VLOOKUP($A$2,'[New Prices.xls]Sheet1'!$B:$D,3,FALSE)
=ВПР($A$2;'[New Prices.xls]Sheet1'!$B:$D;3;ЛОЖЬ)
Ushbu formula hujayra qiymatini qidiradi A2 ustunda B varaqda Jadval1 ish kitobida Yangi Narxlar va ustundan tegishli qiymatni chiqarib oling D.
Jadval yo'lining biron bir qismi o'tkazib yuborilsa, sizning funktsiyangiz VPR ishlamaydi va xato haqida xabar beradi #QIMMAT! (Izlash jadvali bilan ish kitobi hozirda ochiq bo'lsa ham).
Funktsiya haqida ko'proq ma'lumot olish uchun VPRboshqa Excel fayliga murojaat qilish uchun darsga qarang: VLOOKUP yordamida boshqa ish kitobini qidirish.
3. Argument ustuni_num 1 dan kichik
Kimdir kamroq qiymatga kirsa, vaziyatni tasavvur qilish qiyin 1qiymat chiqarish uchun ustunni ko'rsatish. Garchi bu argumentning qiymati ichkariga kiritilgan boshqa Excel funksiyasi tomonidan hisoblangan bo'lsa ham mumkin VPR.
Shunday qilib, agar bu argument sodir bo'lsa col_index_num (ustun_raqami) dan kichik 1Funktsiyasi VPR xato haqida ham xabar beradi #QIMMAT!.
Agar argument bo'lsa col_index_num (ustun_raqami) berilgan massivdagi ustunlar sonidan kattaroq, VPR xato haqida xabar beradi #REF! (#SSYL!).
#NAME xatomi? VLOOKUP ichida
Eng oddiy holat - bu xato #NAME? (#NAME?) – funksiya nomini tasodifan xatolik bilan yozsangiz paydo bo‘ladi.
Yechim aniq - imloni tekshiring!
VLOOKUP ishlamaydi (cheklovlar, ogohlantirishlar va qarorlar)
Juda murakkab sintaksisga qo'shimcha ravishda, VPR boshqa Excel funksiyalariga qaraganda ko'proq cheklovlarga ega. Bu cheklovlar tufayli, oddiy ko'rinadigan formulalar bilan VPR ko'pincha kutilmagan natijalarga olib keladi. Quyida siz bir nechta umumiy stsenariylar uchun echimlarni topasiz VPR noto'g'ri.
1. VLOOKUP katta harflarga sezgir emas
vazifa VPR registrni ajratmaydi va kichik va katta harflarni bir xil qabul qiladi. Shuning uchun, agar jadvalda faqat holatlar bo'yicha farq qiladigan bir nechta elementlar mavjud bo'lsa, VLOOKUP funksiyasi katta-kichik holatlardan qat'iy nazar topilgan birinchi elementni qaytaradi.
Qaror: Vertikal qidiruvni (QIDIRISH, XUSUSIY MAHSULOT, INDEX va MATCH) amalga oshiradigan boshqa Excel funksiyasidan foydalaning. MAQOLAHolatni ajratib turuvchi A. Batafsil ma'lumot olish uchun siz darsdan o'rganishingiz mumkin - Excelda VLOOKUPni katta-kichik harflarga sezgir qilishning 4 usuli.
2. VLOOKUP birinchi topilgan qiymatni qaytaradi
Siz allaqachon bilganingizdek, VPR berilgan ustundan topilgan birinchi moslikka mos keladigan qiymatni qaytaradi. Biroq, siz uni 2-, 3-, 4-chi yoki o'zingiz xohlagan qiymatning boshqa takrorlanishini chiqarib olishingiz mumkin. Agar barcha takroriy qiymatlarni chiqarib olishingiz kerak bo'lsa, sizga funktsiyalar kombinatsiyasi kerak bo'ladi INDEKS (INDEKS), Eng kam (KICHIK) va LINE (QAT).
3. Jadvalga ustun qo'shildi yoki o'chirildi
Afsuski, formulalar VPR Qidiruv jadvaliga har safar yangi ustun qo'shilganda yoki o'chirilganda ishlashni to'xtating. Bu sintaksis tufayli sodir bo'ladi VPR qidiruvning to'liq diapazonini va ma'lumotlarni olish uchun maxsus ustun raqamini ko'rsatishni talab qiladi. Tabiiyki, ustunni o'chirish yoki yangisini qo'shganda berilgan diapazon ham, ustun raqami ham o'zgaradi.
Qaror: Va yana funktsiyalar yordam berishga shoshilmoqda INDEKS (INDEX) va KO'PROQ (MATCH). Formulada INDEX+MATCH Qidiruv va qidirish ustunlarini alohida belgilaysiz va natijada barcha tegishli qidiruv formulalarini yangilashdan tashvishlanmasdan xohlagancha koʻp ustunlarni oʻchirishingiz yoki kiritishingiz mumkin.
4. Formuladan nusxa olishda hujayra havolalari buziladi
Ushbu sarlavha muammoning mohiyatini to'liq tushuntiradi, to'g'rimi?
Qaror: Har doim mutlaq hujayra havolalaridan foydalaning (belgi bilan $), masalan, diapazonni yozadi $A$2:$C$100 or $A:$C. Formulalar satrida havola turini bosish orqali tezda almashtirishingiz mumkin F4.
VLOOKUP – IFERROR va ISERROR funksiyalari bilan ishlash
Agar siz foydalanuvchilarni xato xabarlari bilan qo'rqitmoqchi bo'lmasangiz # Yo'q, #QIMMAT! or #NAME?, siz bo'sh katakni yoki o'zingizning xabaringizni ko'rsatishingiz mumkin. Buni joylashtirish orqali qilishingiz mumkin VPR funksiyaga aylanadi XATO (IFERROR) Excel 2013, 2010 va 2007-da yoki bir qator funktsiyalardan foydalaning AGAR+XATO Oldingi versiyalarda (IF+ISERROR).
VLOOKUP: IFERROR funksiyasi bilan ishlash
Funktsiya sintaksisi XATO (IFERROR) oddiy va o'zi uchun gapiradi:
IFERROR(value,value_if_error)
ЕСЛИОШИБКА(значение;значение_если_ошибка)
Ya'ni, birinchi argument uchun xatolik tekshirilishi kerak bo'lgan qiymatni kiritasiz va ikkinchi argument uchun xato topilsa, nima qaytarish kerakligini ko'rsatasiz.
Misol uchun, agar siz izlayotgan qiymat topilmasa, ushbu formula bo'sh katakchani qaytaradi:
=IFERROR(VLOOKUP($F$2,$B$2:$C$10,2,FALSE),"")
=ЕСЛИОШИБКА(ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ);"")
Funktsiyaning standart xato xabari o'rniga o'z xabaringizni ko'rsatishni istasangiz VPR, uni qo'shtirnoq ichiga qo'ying, shunday qilib:
=IFERROR(VLOOKUP($F$2,$B$2:$C$10,2,FALSE),"Ничего не найдено. Попробуйте еще раз!")
=ЕСЛИОШИБКА(ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ);"Ничего не найдено. Попробуйте еще раз!")
VLOOKUP: ISERROR funksiyasi bilan ishlash
Funktsiyadan beri XATO Excel 2007 da paydo bo'ldi, oldingi versiyalarda ishlashda siz kombinatsiyadan foydalanishingiz kerak bo'ladi IF (IF) va EOSHIBKA (ISERROR) shunday:
=IF(ISERROR(VLOOKUP формула),"Ваше сообщение при ошибке",VLOOKUP формула)
=ЕСЛИ(ЕОШИБКА(ВПР формула);"Ваше сообщение при ошибке";ВПР формула)
Masalan, formula AGAR+XATO+VLOOKUP, formulaga o'xshash XATO+VLOOKUPyuqorida ko'rsatilgan:
=IF(ISERROR(VLOOKUP($F$2,$B$2:$C$10,2,FALSE)),"",VLOOKUP($F$2,$B$2:$C$10,2,FALSE))
=ЕСЛИ(ЕОШИБКА(ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ));"";ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ))
Bugun hammasi shu. Umid qilamanki, bu qisqa qo'llanma barcha mumkin bo'lgan xatolarni engishga yordam beradi. VPR va formulalaringiz to'g'ri ishlashini ta'minlang.