мазмуну
Бул сабак функция болгон кырдаалды кантип тез чечүү керектигин түшүндүрөт VPR (VLOOKUP) Excel 2013, 2010, 2007 жана 2003-жылы иштегиси келбейт жана жалпы каталарды кантип аныктоо жана оңдоо жана чектөөлөрдү жеңүү VPR.
Мурунку бир нече макалаларда биз функциянын ар кандай аспектилерин изилдедик VPR Excelде. Эгер сиз аларды кунт коюп окуп чыккан болсоңуз, анда сиз азыр бул тармакта адис болушуңуз керек. Бирок, көптөгөн Excel эксперттери бекеринен ишенбейт VPR татаал өзгөчөлүктөрүнүн бири. Ал көптөгөн көйгөйлөрдүн жана каталардын булагы болуп калган бир топ чектөөлөр жана өзгөчөлүктөргө ээ.
Бул макалада сиз каталардын жөнөкөй түшүндүрмөлөрүн таба аласыз #AT (#Жок), #NAME? (#NAME?) жана #VALUE! Функция менен иштөөдө пайда болгон (#VALUE!). VPR, ошондой эле алар менен күрөшүүнүн ыкмалары жана ыкмалары. Биз эң кеңири тараган учурларды жана эмне үчүн эң айкын себептерден баштайбыз. VPR иштебейт, андыктан мисалдарды макалада келтирилген ырааттуулук менен изилдеген жакшы.
Excelдеги VLOOKUP функциясындагы #N/A катасын оңдоо
менен формулаларда VPR ката жөнүндө билдирүү #AT (#N/A) билдирет жеткиликтүү эмес (маалымат жок) – Excel сиз издеп жаткан маанини таба албаганда пайда болот. Бул бир нече себептерден улам болушу мүмкүн.
1. Керектүү маани туура эмес жазылган
Алгач бул нерсени текшерүү жакшы идея! Миңдеген саптардан турган өтө чоң көлөмдөгү маалыматтар менен иштегениңизде же сиз издеп жаткан маани формулага жазылганда тамга каталары көп кездешет.
2. VLOOKUP менен болжолдуу дал келүүнү издөөдө #N/A катасы
Эгер сиз болжолдуу дал келүүчү издөө шарты бар формуланы колдонсоңуз, б.а. аргумент диапазонду_издөө (аралык_издөө) ЧЫНДЫК же көрсөтүлгөн эмес, формулаңыз ката жөнүндө кабарлашы мүмкүн # N / A эки учурда:
- Карала турган маани изделип жаткан массивдеги эң кичине мааниден азыраак.
- Издөө тилкеси өсүү тартибинде иреттелген эмес.
3. VLOOKUP менен так дал келүүнү издеп жатканда #N/A катасы
Эгер сиз так дал келүүнү, башкача айтканда аргументти издеп жатсаңыз диапазонду_издөө (аралык_издөө) ЖАЛГАН жана так маани табылган жок, формула да ката жөнүндө кабарлайт # N / A. Функция менен так жана болжолдуу дал келүүлөрдү кантип издөө керектиги жөнүндө көбүрөөк билип алыңыз VPR.
4. Издөө тилкеси эң сол жакта эмес
Белгилүү болгондой, эң маанилүү чектөөлөрдүн бири VPR ал сол тарапка каралышы мүмкүн эмес, ошондуктан таблицаңыздагы издөө тилкеси эң сол жакта болушу керек. Иш жүзүндө биз муну көп учурда унутуп калабыз, бул иштебеген формулага жана катага алып келет. # N / A.
Чечим: Эгер маалымат түзүмүн издөө тилкеси эң сол жакта тургандай өзгөртүү мүмкүн болбосо, функциялардын айкалышын колдонсоңуз болот. INDEX (ИНДЕКС) жана КӨБҮРӨГҮ АЧЫРЫЛГАН (MATCH) үчүн ийкемдүү альтернатива катары VPR.
5. Сандар текст катары форматталат
Дагы бир ката булагы # N / A менен формулаларда VPR негизги таблицада же издөө таблицасында текст форматындагы сандар.
Бул адатта тышкы маалымат базаларынан маалыматты импорттоодо же алдыңкы нөлдү сактап калуу үчүн сандын алдына апостроф койгондо болот.
Текст форматындагы сандын эң айкын белгилери төмөндөгү сүрөттө көрсөтүлгөн:
Мындан тышкары, сандар форматта сакталышы мүмкүн жалпы (Жалпы). Бул учурда, бир гана байкаларлык өзгөчөлүк бар - сандар уячанын сол четине тегизделген, ал эми демейки боюнча алар оң четине тегизделген.
Чечим: Эгер бул жалгыз маани болсо, жөн гана ката сөлөкөтүн чыкылдатып, тандаңыз Санга айландыруу (Санга айландыруу) контексттик менюдан.
Эгер бул көп сандагы абал болсо, аларды тандап, тандалган аймакты оң баскыч менен чыкылдатыңыз. Пайда болгон контексттик менюдан тандаңыз Клеткаларды форматтоо (Формат ячейка) > өтмөк сан (Сан) > формат сан (Сандык) жана басыңыз OK.
6. Башында же аягында боштук бар
Бул катанын эң аз айкын себеби. # N / A функцияда VPR, анткени бул кошумча мейкиндиктерди көрүү визуалдык жактан кыйынга турат, айрыкча чоң таблицалар менен иштөөдө, маалыматтардын көбү экрандан сырткары болгондо.
Чечим 1: Негизги таблицада кошумча боштуктар (VLOOKUP функциясы жайгашкан жерде)
Негизги таблицада кошумча боштуктар пайда болсо, аргументти тиркеп, формулалардын туура иштешин камсыздай аласыз. search_value (издөө_маани) функцияга кесүү (TRIM):
=VLOOKUP(TRIM($F2),$A$2:$C$10,3,FALSE)
=ВПР(СЖПРОБЕЛЫ($F2);$A$2:$C$10;3;ЛОЖЬ)
Чечим 2: Издөө таблицасында кошумча боштуктар (издөө тилкесинде)
кошумча боштуктар издөө тилкесинде болсо - жөнөкөй жолдору # N / A менен формулада VPR качуу мүмкүн эмес. Ордуна VPR Функциялардын айкалышы менен массив формуласын колдонсоңуз болот INDEX (INDEX), КӨБҮРӨГҮ АЧЫРЫЛГАН (МАТЧ) и кесүү (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! (#VALUE!) формулада колдонулган маани берилиштердин түрүнө дал келбегенде. байланыштуу VPR, анда адатта катанын эки себеби бар #VALUE!.
1. Сиз издеп жаткан маани 255 белгиден узунураак
Сак болуңуз: функция VPR 255 белгиден ашкан маанилерди издей албайт. Эгер сиз издеп жаткан маани бул чектен ашып кетсе, сиз ката кабарын аласыз. #VALUE!.
Чечим: Бир топ функцияларды колдонуңуз ИНДЕКС+МАТЧ (INDEX + МАТЧ). Төмөндө бул тапшырманы аткара турган формула келтирилген:
=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 баракта Sheet1 эмгек китепчесинде Жаңы баалар жана тилкеден тиешелүү маанини чыгарыңыз D.
Таблица жолунун кайсы бир бөлүгү алынып салынса, сиздин функцияңыз VPR иштебейт жана ката жөнүндө кабарлайт #VALUE! (издөө таблицасы бар жумушчу китеп учурда ачык болсо да).
Функция жөнүндө көбүрөөк маалымат алуу үчүн VPRбашка Excel файлына шилтеме берүү менен, сабакты караңыз: VLOOKUP аркылуу башка иш китебин издөө.
3. Аргумент тилкесинин_саны 1ден аз
Кимдир бирөө андан азыраак мааниге кирген жагдайды элестетүү кыйын 1маанини алуу үчүн тилкени көрсөтүү. Бул аргументтин мааниси ичинде уя салынган башка Excel функциясы менен эсептелсе, мүмкүн болсо да VPR.
Демек, бул аргумент болуп калса col_index_num (мамыча_саны) аз 1милдети VPR ката жөнүндө да кабарлайт #VALUE!.
Аргумент болсо col_index_num (колонна_саны) берилген массивдеги тилкелердин санынан чоңураак, VPR ката жөнүндө кабарлайт #РЕФ! (#SSYL!).
Ката #NAME? VLOOKUP ичинде
Эң жөнөкөй учур бул ката #NAME? (#NAME?) – кокусунан ката менен функциянын атын жазсаңыз пайда болот.
Чечим айкын – орфографияңызды текшериңиз!
VLOOKUP иштебейт (чектөөлөр, эскертүүлөр жана чечимдер)
бир кыйла татаал синтаксистен тышкары, VPR башка Excel функцияларына караганда көбүрөөк чектөөлөр бар. Бул чектөөлөрдөн улам, жөнөкөй көрүнгөн формулалар VPR көбүнчө күтүлбөгөн натыйжаларга алып келет. Төмөндө сиз бир нече жалпы сценарийлер үчүн чечимдерди таба аласыз VPR туура эмес.
1. VLOOKUP регистрге сезимтал эмес
милдети VPR регистрди ажыратпайт жана кичине жана чоң тамгаларды бирдей кабыл алат. Ошондуктан, эгерде таблицада учурда гана айырмаланган бир нече элементтер бар болсо, VLOOKUP функциясы жагдайга карабастан биринчи табылган элементти кайтарып берет.
Чечим: менен айкалышта вертикалдуу издөөнү (ИЗДӨӨ, СУПРОДУКТ, ИНДЕКС жана МАТЧА) аткара турган башка Excel функциясын колдонуңуз. постА бул ишти айырмалайт. Көбүрөөк маалымат алуу үчүн, сиз сабактан үйрөнө аласыз - Excelде VLOOKUP регистрди сезгич кылуунун 4 ыкмасы.
2. VLOOKUP табылган биринчи маанини кайтарат
Сиз буга чейин билгендей, VPR табылган биринчи дал келген маанини берилген тилкеден кайтарат. Бирок, сиз каалаган маанинин 2, 3, 4 же башка кайталоосун чыгара аласыз. Эгер сиз бардык кайталанган маанилерди чыгарып алышыңыз керек болсо, сизге функциялардын айкалышы керек болот INDEX (INDEX), эң кичине (КИЧИ) жана LINE (КАП).
3. Таблицага тилке кошулду же алынып салынды
Тилекке каршы, формулалар VPR издөө таблицасына жаңы тилке кошулган же алынып салынган сайын иштөөнү токтотуу. Бул синтаксис болгондуктан болот VPR издөөнүн толук диапазонун жана маалыматтарды алуу үчүн атайын тилке номерин көрсөтүүнү талап кылат. Албетте, тилкени жок кылганда же жаңысын салганда берилген диапазон да, мамычанын номери да өзгөрөт.
Чечим: Жана дагы функциялар жардам берүүгө шашышат INDEX (ИНДЕКС) жана КӨБҮРӨГҮ АЧЫРЫЛГАН (МАТЧ). Формулада ИНДЕКС+МАТЧ Сиз издөө жана издөө тилкелерин өзүнчө аныктайсыз жана натыйжада бардык тиешелүү издөө формулаларын жаңыртуудан кабатыр болбостон, каалагандай көп тилкелерди жок кылып же киргизе аласыз.
4. Формула көчүрүүдө клетка шилтемелери бузулат
Бул рубрика маселенин маңызын толук түшүндүрөт, туурабы?
Чечим: Ар дайым абсолюттук уяча шилтемелерин колдонуңуз (белгиси менен $) боюнча, мисалы, диапазонун жазуу $A$2:$C$100 or $A:$C. Формула тилкесинде сиз чыкылдатуу менен шилтеменин түрүн тез алмаштыра аласыз F4.
VLOOKUP – IFERROR жана ISERROR функциялары менен иштөө
Эгер сиз колдонуучуларды ката билдирүүлөрү менен коркутууну каалабасаңыз # N / A, #VALUE! or #NAME?, сиз бош уячаны же өз билдирүүңүздү көрсөтө аласыз. Сиз муну жайгаштыруу менен кыла аласыз VPR функцияга КАТА (IFERROR) Excel 2013, 2010 жана 2007де же бир топ функцияларды колдонуңуз ЭГЕР+КАТА (ЭГЕР+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 формула)
=ЕСЛИ(ЕОШИБКА(ВПР формула);"Ваше сообщение при ошибке";ВПР формула)
Мисалы, формула ЭГЕР+КАТА+ВLOOKUP, формулага окшош КАТА+ВLOOKUPжогоруда көрсөтүлгөн:
=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 жана формулаларыңыздын туура иштешин камсыз кылыңыз.