Мазмұны
Бұл сабақ функция орын алған жағдайда қалай тез әрекет ету керектігін түсіндіреді 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 (ИНДЕКС) және КӨБІРЕК АШЫҚ (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 (ИНДЕКС), КӨБІРЕК АШЫҚ (МАТЧ) и 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 қате туралы хабарлайды # МАҢЫЗ! (#МӘН!) формулада пайдаланылатын мән деректер түріне сәйкес келмегенде. қатысты VPR, онда әдетте қатенің екі себебі бар # МАҢЫЗ!.
1. Сіз іздеген мән 255 таңбадан ұзын
Абайлаңыз: функция VPR 255 таңбадан астам мәндерді іздей алмайды. Егер сіз іздеген мән осы шектен асып кетсе, қате туралы хабар аласыз. # МАҢЫЗ!.
Шешім: Көптеген мүмкіндіктерді пайдаланыңыз ИНДЕКС+СӘйкестік (INDEX + MATCH). Төменде осы тапсырма үшін өте жақсы болатын формула берілген:
=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 функциясы регистрге қарамастан бірінші табылған элементті қайтарады.
Шешім: Тігінен іздеуді (ІЗДЕУ, ҚОРЫТЫНДЫ, КӨРСЕТКІШ және СӘйкестендіру) орындай алатын басқа Excel функциясын пайдаланыңыз. ДӘЛІсті ерекшелендіретін A. Қосымша мәліметтер алу үшін сіз сабақтан біле аласыз – Excel бағдарламасында VLOOKUP регистрін есепке алудың 4 әдісі.
2. VLOOKUP табылған бірінші мәнді қайтарады
Өздеріңіз білетіндей, VPR табылған бірінші сәйкестікке сәйкес берілген бағандағы мәнді қайтарады. Дегенмен, оның 2-ші, 3-ші, 4-ші немесе қалаған мәннің кез келген басқа қайталануын шығарып алуына болады. Барлық қайталанатын мәндерді шығарып алу қажет болса, сізге функциялар тіркесімі қажет болады INDEX (ИНДЕКС), Ең аз (КІШІ) және ТҮЗУ (ҚАТАР).
3. Кестеге баған қосылды немесе жойылды
Өкінішке орай, формулалар VPR іздеу кестесіне жаңа баған қосылған немесе жойылған сайын жұмысын тоқтатыңыз. Бұл синтаксис болғандықтан орын алады VPR іздеудің толық ауқымын және деректерді шығару үшін арнайы баған нөмірін көрсетуді талап етеді. Әрине, берілген ауқым да, баған нөмірі де бағанды жойғанда немесе жаңасын кірістіргенде өзгереді.
Шешім: Және тағы да функциялар көмектесуге асығады INDEX (ИНДЕКС) және КӨБІРЕК АШЫҚ (МАТЧ). Формулада ИНДЕКС+СӘйкестік Іздеу және іздеу бағандарын бөлек анықтайсыз және нәтижесінде барлық қатысты іздеу формулаларын жаңартуға алаңдамай, қалағаныңызша көптеген бағандарды жоюға немесе енгізуге болады.
4. Формулаларды көшіру кезінде ұяшық сілтемелері бұзылады
Бұл айдар мәселенің мәнін жан-жақты түсіндіреді, солай емес пе?
Шешім: Әрқашан абсолютті ұяшық сілтемелерін пайдаланыңыз (таңбасымен $) бойынша диапазонды жазады, мысалы $A$2:$C$100 or $A:$C. Формула жолағында сілтеме түрін басу арқылы жылдам ауыстыруға болады F4.
VLOOKUP – IFERROR және ISERROR функцияларымен жұмыс істеу
Пайдаланушыларды қате туралы хабарлармен қорқытқыңыз келмесе # Жоқ, # МАҢЫЗ! or #NAME?, бос ұяшықты немесе өз хабарламаңызды көрсете аласыз. Сіз мұны орналастыру арқылы жасай аласыз VPR функцияға айналдырады ҚАТЕЛІК (IFERROR) Excel 2013, 2010 және 2007 нұсқаларында немесе бірқатар функцияларды пайдаланыңыз ЕГЕР+ҚАТЕ (Егер+ҚАТЕ) алдыңғы нұсқаларда.
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 және формулаларыңыздың дұрыс жұмыс істеуін қамтамасыз етіңіз.