Агуулга
Энэ хичээл нь функц байгаа нөхцөл байдлыг хэрхэн хурдан шийдвэрлэхийг тайлбарладаг VPR (VLOOKUP) нь Excel 2013, 2010, 2007, 2003 дээр ажиллахыг хүсэхгүй байгаа бөгөөд нийтлэг алдааг хэрхэн тодорхойлж, засах, хязгаарлалтыг даван туулах талаар VPR.
Өмнөх хэд хэдэн нийтлэлд бид функцийн янз бүрийн талыг судалж үзсэн VPR Excel дээр. Хэрэв та тэдгээрийг анхааралтай уншсан бол одоо энэ чиглэлээр мэргэшсэн мэргэжилтэн байх ёстой. Гэсэн хэдий ч Excel-ийн олон мэргэжилтнүүд үүнд итгэдэггүй VPR илүү төвөгтэй шинж чанаруудын нэг. Энэ нь олон асуудал, алдааны эх үүсвэр болдог олон хязгаарлалт, онцлог шинж чанартай байдаг.
Энэ нийтлэлд та алдааны энгийн тайлбарыг олох болно #AT (#Үгүй), #NAME? (#NAME?) болон # ҮНЭЛГЭЭ! (#VALUE!) функцтэй ажиллах үед гарч ирдэг VPR, түүнчлэн тэдгээртэй харьцах техник, арга. Бид хамгийн түгээмэл тохиолдлууд болон хамгийн тодорхой шалтгаануудаас эхлэх болно. VPR ажиллахгүй байгаа тул жишээнүүдийг өгүүлэлд өгөгдсөн дарааллаар нь судлах нь дээр.
Excel-ийн VLOOKUP функцийн #N/A алдааг засаж байна
-тэй томъёогоор VPR алдааны мессеж #AT (#Үгүй) гэсэн үг боломжгүй (өгөгдөл байхгүй) – Excel таны хайж буй утгыг олж чадахгүй үед гарч ирнэ. Энэ нь хэд хэдэн шалтгааны улмаас тохиолдож болно.
1. Хүссэн утгыг буруу бичсэн байна
Эхлээд энэ зүйлийг шалгах нь сайхан санаа! Олон мянган мөрөөс бүрдэх маш их хэмжээний өгөгдөлтэй ажиллах эсвэл хайж буй утгыг томьёо болгон бичих үед үсгийн алдаа ихэвчлэн гардаг.
2. VLOOKUP-тай ойролцоо тохирохыг хайхад алдаа гарлаа
Хэрэв та ойролцоо хайлтын нөхцөл бүхий томъёог ашигладаг бол, өөрөөр хэлбэл аргумент хүрээний_хайлт (муж_хайл) ҮНЭН эсвэл тодорхойлогдоогүй, таны томьёо алдаа гаргаж болзошгүй # Н / А хоёр тохиолдолд:
- Хайх утга нь хайж буй массивын хамгийн бага утгаас бага байна.
- Хайлтын баганыг өсөх дарааллаар эрэмбэлсэнгүй.
3. VLOOKUP-тай яг таарч тохирохыг хайхад #Үгүй
Хэрэв та яг таарч, өөрөөр хэлбэл аргумент хайж байгаа бол хүрээний_хайлт (муж_хайлт) ХУДАЛ бөгөөд яг тодорхой утга олдоогүй тул томьёо нь мөн алдаа мэдээлнэ # Н / А. Функцтэй яг таарч тохирохыг хэрхэн хайх талаар дэлгэрэнгүй үзнэ үү VPR.
4. Хайлтын багана хамгийн зүүн талд биш
Хамгийн чухал хязгаарлалтуудын нэг гэдгийг та мэдэж байгаа байх VPR Энэ нь зүүн тийшээ харж болохгүй тул таны хүснэгтийн хайлтын багана хамгийн зүүн талд байх ёстой. Практикт бид үүнийг ихэвчлэн мартдаг бөгөөд энэ нь ажиллахгүй томъёолол, алдаа гаргахад хүргэдэг. # Н / А.
Шийдвэр: Хэрэв хайлтын баганыг хамгийн зүүн талд байрлуулахаар өгөгдлийн бүтцийг өөрчлөх боломжгүй бол та функцүүдийн хослолыг ашиглаж болно. INDEX (INDEX) ба ИЛҮҮ ИЛҮҮ (MATCH) нь илүү уян хатан хувилбар юм VPR.
5. Тоонууд нь текст хэлбэрээр форматлагдсан
Алдааны өөр нэг эх сурвалж # Н / А -тэй томъёогоор VPR үндсэн хүснэгт эсвэл хайлтын хүснэгт дэх текст форматтай тоонууд юм.
Энэ нь ихэвчлэн гадны өгөгдлийн сангаас мэдээлэл импортлох үед, эсвэл тооноос өмнө таслах тэмдэг бичиж, тэгийг хадгалахад тохиолддог.
Текст хэлбэрийн тооны хамгийн тод шинж тэмдгийг доорх зурагт үзүүлэв.
Үүнээс гадна тоонуудыг форматаар хадгалах боломжтой Ерөнхий (Ерөнхий). Энэ тохиолдолд зөвхөн нэг мэдэгдэхүйц онцлог байдаг - тоонууд нь нүдний зүүн ирмэгтэй зэрэгцсэн байдаг бол анхдагч байдлаар баруун ирмэг дээр зэрэгцдэг.
Шийдвэр: Хэрэв энэ нь нэг утга бол алдааны дүрс дээр товшоод сонгоно уу Тоо руу хөрвүүлэх Контекст цэснээс (Тоо руу хөрвүүлэх).
Хэрэв олон тооны тоонууд ийм байвал тэдгээрийг сонгоод сонгосон талбар дээр хулганы баруун товчийг дарна уу. Гарч ирэх контекст цэснээс сонгоно уу Нүднүүдийг форматлах (Формат нүд) > таб тоо (Тоо) > формат тоо (Тоон) болон дарна уу OK.
6. Эхэндээ эсвэл төгсгөлд нь зай байна
Энэ бол алдааны хамгийн бага тодорхой шалтгаан юм. # Н / А функцэд VPR, учир нь эдгээр нэмэлт зайг харахад хэцүү байдаг, ялангуяа том хүснэгтүүдтэй ажиллах үед ихэнх өгөгдөл нь дэлгэцээс гадуур байх үед.
Шийдэл 1: Үндсэн хүснэгтэд нэмэлт зай (VLOOKUP функц байгаа газарт)
Хэрэв үндсэн хүснэгтэд нэмэлт зай гарч ирвэл аргументыг хавсаргаснаар томъёонууд зөв ажиллаж байгаа эсэхийг шалгах боломжтой. хайх_ үнэлгээ (хайх_утга) функц руу оруулна TRIM (TRIM):
=VLOOKUP(TRIM($F2),$A$2:$C$10,3,FALSE)
=ВПР(СЖПРОБЕЛЫ($F2);$A$2:$C$10;3;ЛОЖЬ)
Шийдэл 2: Хайлтын хүснэгт дэх нэмэлт зай (хайлтын баганад)
Хайлтын баганад нэмэлт зай байгаа бол энгийн аргууд # Н / А -тэй томъёогоор VPR зайлсхийх боломжгүй. Оронд нь VPR Та функцүүдийн хослол бүхий массивын томъёог ашиглаж болно INDEX (INDEX), ИЛҮҮ ИЛҮҮ (ТОГЛОЛТ) и 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))
Энэ бол массивын томьёо тул дарахаа бүү мартаарай Ctrl + Shift + Enter ердийн оронд оруулна уутомъёог зөв оруулах.
Алдаа #VALUE! VLOOKUP бүхий томъёонд
Ихэнх тохиолдолд Microsoft Excel алдааг мэдээлдэг # ҮНЭЛГЭЭ! Томъёонд ашигласан утга нь өгөгдлийн төрөлтэй тохирохгүй үед (#VALUE!) тухай VPR, дараа нь ихэвчлэн алдааны хоёр шалтгаан байдаг # ҮНЭЛГЭЭ!.
1. Таны хайж буй утга 255 тэмдэгтээс урт байна
Болгоомжтой байгаарай: функц VPR 255-аас дээш тэмдэгт агуулсан утгыг хайх боломжгүй. Хэрэв таны хайж буй утга энэ хязгаараас хэтэрсэн бол алдааны мэдэгдэл хүлээн авах болно. # ҮНЭЛГЭЭ!.
Шийдвэр: Олон тооны функцийг ашигла INDEX+TACH (INDEX + TABLO). Энэ даалгаврыг гүйцэтгэхэд тохиромжтой томъёог доор харуулав.
=INDEX(C2:C7,MATCH(TRUE,INDEX(B2:B7=F$2,0),0))
=ИНДЕКС(C2:C7;ПОИСКПОЗ(ИСТИНА;ИНДЕКС(B2:B7=F$2;0);0))
2. Хайлтын ажлын дэвтэрт хүрэх бүрэн замыг заагаагүй байна
Хэрэв та өөр ажлын номноос мэдээлэл авч байгаа бол тухайн файл руу орох замыг бүрэн зааж өгөх ёстой. Тодруулбал, та ажлын дэвтрийн нэрийг (өргөтгөлийг оруулаад) дөрвөлжин хаалтанд [ ], хуудасны нэр, ард нь анхаарлын тэмдэг оруулах ёстой. Ном эсвэл хуудасны нэрэнд хоосон зай байгаа тохиолдолд энэ бүх бүтээцийг апострофоор бичсэн байх ёстой.
Функцийн бүрэн бүтэц энд байна VPR өөр номноос хайх:
=VLOOKUP(lookup_value,'[workbook name]sheet name'!table_array, col_index_num,FALSE)
=ВПР(искомое_значение;'[имя_книги]имя_листа'!таблица;номер_столбца;ЛОЖЬ)
Жинхэнэ томъёо дараах байдалтай байж болно.
=VLOOKUP($A$2,'[New Prices.xls]Sheet1'!$B:$D,3,FALSE)
=ВПР($A$2;'[New Prices.xls]Sheet1'!$B:$D;3;ЛОЖЬ)
Энэ томъёо нь нүдний утгыг хайх болно A2 баганад B хуудсан дээр Хуудас1 ажлын дэвтэрт Шинэ үнэ ба баганаас харгалзах утгыг гаргаж авна D.
Хүснэгтийн замын аль нэг хэсгийг орхигдуулсан бол таны функц VPR ажиллахгүй бөгөөд алдаа мэдээлэх болно # ҮНЭЛГЭЭ! (хайх хүснэгттэй ажлын ном одоогоор нээлттэй байгаа ч гэсэн).
Функцийн талаар дэлгэрэнгүй мэдээлэл авахыг хүсвэл VPRӨөр Excel файлын тухай хичээлийг үзнэ үү: VLOOKUP ашиглан өөр ажлын ном хайх.
3. Аргументын баганын тоо 1-ээс бага
Хэн нэгэн түүнээс доогуур үнэ цэнийг оруулдаг нөхцөл байдлыг төсөөлөхөд хэцүү байдаг 1утгыг гаргаж авах баганыг заана. Хэдийгээр энэ аргументын утгыг Excel-ийн дотор байрлуулсан өөр функцээр тооцоолсон бол боломжтой VPR.
Тиймээс, хэрвээ маргаан гарах юм бол col_index_num (баганын_тоо) -аас бага 1үйл ажиллагаа VPR мөн алдаа мэдээлэх болно # ҮНЭЛГЭЭ!.
Хэрэв маргаан бол col_index_num (баганын_тоо) нь өгөгдсөн массив дахь баганын тооноос их, VPR алдаа мэдээлэх болно #REF! (#SSYL!).
Алдаа #NAME? VLOOKUP дотор
Хамгийн энгийн тохиолдол бол алдаа юм #NAME? (#NAME?) – функцийн нэрийг санамсаргүйгээр алдаатай бичвэл гарч ирнэ.
Шийдэл нь ойлгомжтой - зөв бичгийн дүрмийг шалгаарай!
VLOOKUP ажиллахгүй байна (хязгаарлалт, анхааруулга, шийдвэр)
Нэлээд төвөгтэй синтаксаас гадна VPR бусад Excel функцээс илүү хязгаарлалттай. Эдгээр хязгаарлалтын улмаас энгийн мэт санагдах томъёонууд VPR ихэвчлэн гэнэтийн үр дүнд хүргэдэг. Доор та хэд хэдэн нийтлэг хувилбаруудын шийдлийг олох болно VPR буруу байна.
1. VLOOKUP нь том жижиг жижиг харгалзахгүй
Чиг үүрэг VPR жижиг үсгийн хооронд ялгаагүй, жижиг, том үсгийг адилхан хүлээн авдаг. Тиймээс, хүснэгтэд зөвхөн тохиолдлоор ялгаатай хэд хэдэн элемент байгаа бол VLOOKUP функц нь тохиолдлоос үл хамааран эхний олдсон элементийг буцаана.
Шийдвэр: Босоо хайлт (LOOKUP, SUMPRODUCT, INDEX, MATCH) хийх боломжтой өөр Excel функцийг ашиглана уу. ЯГА нь тохиолдлыг ялгадаг. Дэлгэрэнгүй мэдээллийг та хичээлээс мэдэж болно - Excel дээр VLOOKUP-г том жижиг үсгээр харуулдаг болгох 4 арга.
2. VLOOKUP нь эхний олдсон утгыг буцаана
Та аль хэдийн мэдэж байсан шиг, VPR өгөгдсөн баганаас эхний олдсон тохирох утгыг буцаана. Гэсэн хэдий ч та үүнийг хүссэн утгынхаа 2, 3, 4, эсвэл өөр давталтыг гаргаж авах боломжтой. Хэрэв та бүх давхардсан утгыг задлах шаардлагатай бол танд функцүүдийн хослол хэрэгтэй болно INDEX (INDEX), ҮНЭГҮЙ (ЖИЖИГ) ба LINE (РАВ).
3. Хүснэгтэнд багана нэмж эсвэл хассан
Харамсалтай нь томъёонууд VPR Хайлтын хүснэгтэд шинэ багана нэмэх эсвэл хасах болгонд ажиллахаа болино. Энэ нь синтаксийн улмаас тохиолддог VPR Хайлтын бүрэн хүрээ болон өгөгдөл задлах тусгай баганын дугаарыг зааж өгөхийг шаарддаг. Мэдээжийн хэрэг, та баганыг устгах эсвэл шинээр оруулах үед өгөгдсөн муж болон баганын дугаар хоёулаа өөрчлөгддөг.
Шийдвэр: Мөн дахин функцууд туслахаар яарч байна INDEX (INDEX) ба ИЛҮҮ ИЛҮҮ (ТОГЛОЛТ). Томъёонд INDEX+TACH Та хайлт, хайлт хийх баганыг тусад нь тодорхойлдог бөгөөд үүний үр дүнд та холбогдох хайлтын бүх томъёог шинэчлэхээс санаа зовохгүйгээр хүссэн хэмжээгээрээ устгах эсвэл оруулах боломжтой.
4. Томьёог хуулах үед эсийн лавлагаа эвдэрсэн
Энэ гарчиг нь асуудлын мөн чанарыг бүрэн тайлбарлаж байна, тийм ээ?
Шийдвэр: Үргэлж үнэмлэхүй нүдний лавлагаа ашиглана уу (тэмдэглэгээтэй $) дээр жишээ нь мужийг бүртгэдэг $A$2:$C$100 or $A:$C. Томъёоны мөрөнд та холбоосын төрлийг товшиж хурдан солих боломжтой F4.
VLOOKUP – IFERROR ба ISERROR функцуудтай ажиллах
Хэрэв та алдааны мессежээр хэрэглэгчдийг айлгахыг хүсэхгүй байгаа бол # Н / А, # ҮНЭЛГЭЭ! or #NAME?, та хоосон нүд эсвэл өөрийн мессежийг харуулах боломжтой. Та үүнийг байрлуулах замаар хийж болно VPR функц болгон хувиргана АЛДАА Excel 2013, 2010, 2007 дээр (IFERROR) эсвэл хэд хэдэн функцийг ашиглана уу. ХЭРВЭЭ+АЛДАА (IF+ISERROR) өмнөх хувилбаруудад.
VLOOKUP: IFERROR функцтэй ажиллах
Функцийн синтакс АЛДАА (IFERROR) нь энгийн бөгөөд өөрөө ярьдаг:
IFERROR(value,value_if_error)
ЕСЛИОШИБКА(значение;значение_если_ошибка)
Өөрөөр хэлбэл, эхний аргументын хувьд та алдаа байгаа эсэхийг шалгах утгыг оруулж, хоёр дахь аргументын хувьд алдаа олдвол юу буцаахыг зааж өгнө.
Жишээлбэл, хэрэв таны хайж буй утга олдохгүй бол энэ томъёо нь хоосон нүдийг буцаана:
=IFERROR(VLOOKUP($F$2,$B$2:$C$10,2,FALSE),"")
=ЕСЛИОШИБКА(ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ);"")
Хэрэв та функцийн стандарт алдааны мессежийн оронд өөрийн мессежийг харуулахыг хүсвэл VPR, үүнийг хашилтанд бичнэ үү:
=IFERROR(VLOOKUP($F$2,$B$2:$C$10,2,FALSE),"Ничего не найдено. Попробуйте еще раз!")
=ЕСЛИОШИБКА(ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ);"Ничего не найдено. Попробуйте еще раз!")
VLOOKUP: ISERROR функцтэй ажиллаж байна
Функцээс хойш АЛДАА Excel 2007 дээр гарч ирсэн бөгөөд өмнөх хувилбарууд дээр ажиллахдаа та хослолыг ашиглах хэрэгтэй болно IF (Хэрэв) ба ЕОШИБКА (ISERROR) иймэрхүү:
=IF(ISERROR(VLOOKUP формула),"Ваше сообщение при ошибке",VLOOKUP формула)
=ЕСЛИ(ЕОШИБКА(ВПР формула);"Ваше сообщение при ошибке";ВПР формула)
Жишээлбэл, томъёо ХЭРВЭЭ+АЛДАА+ДАВАН ХИЙХ, томъёотой төстэй ХЭРЭГЛЭЭ+ДАВАН ХИЙХдээр үзүүлсэн:
=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;ЛОЖЬ))
Өнөөдрийн хувьд энэ л байна. Энэхүү богино заавар нь танд гарч болзошгүй бүх алдаануудыг шийдвэрлэхэд тусална гэж найдаж байна. VPR мөн томъёогоо зөв ажиллуулаарай.