содржина
Оваа лекција објаснува како брзо да се справите со ситуација кога некоја функција VPR (VLOOKUP) не сака да работи во Excel 2013, 2010, 2007 и 2003 година и како да ги идентификува и поправи вообичаените грешки и да ги надмине ограничувањата VPR.
Во неколку претходни написи, ги истраживме различните аспекти на функцијата VPR во Excel. Ако сте ги прочитале внимателно, сега треба да бидете експерт во оваа област. Сепак, не без причина веруваат многу експерти на Excel VPR една од покомплексните карактеристики. Има еден куп ограничувања и карактеристики кои стануваат извор на многу проблеми и грешки.
Во оваа статија ќе најдете едноставни објаснувања за грешките #AT (# N/A), #NAME? (#ИМЕ?) и #VALUE! (#VALUE!) кои се појавуваат при работа со функцијата VPR, како и техники и методи за справување со нив. Ќе започнеме со најчестите случаи и најочигледните причини зошто. VPR не функционира, па затоа е подобро да ги проучите примерите по редоследот по кој се дадени во статијата.
Поправање на #N/A грешка во функцијата VLOOKUP во Excel
Во формули со VPR грешка порака #AT (# N/A) значи не се достапни (без податоци) – се појавува кога Excel не може да ја најде вредноста што ја барате. Ова може да се случи поради неколку причини.
1. Посакуваната вредност е погрешно напишана
Добра идеја прво да ја проверите оваа ставка! Грешките често се појавуваат кога работите со многу големи количини на податоци, кои се состојат од илјадници линии, или кога вредноста што ја барате е запишана во формула.
2. #N/A грешка при пребарување на приближна совпаѓање со VLOOKUP
Ако користите формула со приближна состојба за пребарување на совпаѓање, т.е. аргумент опсег_преглед (range_lookup) е ТОЧНО или не е наведено, вашата формула може да пријави грешка # N / A во два случаи:
- Вредноста што треба да се бара е помала од најмалата вредност во низата што се бара.
- Колоната за пребарување не е подредена по растечки редослед.
3. #N/A грешка кога барате точно совпаѓање со VLOOKUP
Ако барате точна совпаѓање, односно аргумент опсег_преглед (range_lookup) е FALSE и точната вредност не е пронајдена, формулата исто така ќе пријави грешка # N / A. Дознајте повеќе за тоа како да пребарувате точни и приближни совпаѓања со функција VPR.
4. Колоната за пребарување не е најлево
Како што веројатно знаете, едно од најзначајните ограничувања VPR тоа е дека не може да биде свртена налево, па затоа колоната за пребарување во вашата табела мора да биде најлево. Во пракса, често забораваме на ова, што доведува до неработна формула и грешка. # N / A.
Одлука: Ако не е можно да се промени структурата на податоците така што колоната за пребарување е најлевата, можете да користите комбинација од функции Индекс (ИНДЕКС) и ПОИЗЛОЖЕНИ (MATCH) како пофлексибилна алтернатива за VPR.
5. Броевите се форматирани како текст
Друг извор на грешка # N / A во формули со VPR се броеви во формат на текст во главната табела или табела за пребарување.
Ова обично се случува кога внесувате информации од надворешни бази на податоци или кога пишувате апостроф пред број за да ја задржите првата нула.
Најочигледните знаци на број во формат на текст се прикажани на сликата подолу:
Покрај тоа, броевите може да се зачуваат во формат Генерално (Генерал). Во овој случај, има само една забележлива карактеристика - броевите се порамнети на левиот раб на ќелијата, додека стандардно тие се порамнети на десниот раб.
Одлука: Ако е една вредност, само кликнете на иконата за грешка и изберете Конвертирај во број (Конвертирај во број) од контекстното мени.
Ако ова е ситуацијата со многу броеви, изберете ги и кликнете со десното копче на избраната област. Во контекстното мени што се појавува, изберете Форматирај ќелии (Форматирајте ги ќелиите) > таб Број (Број) > формат Број (Нумерички) и притиснете OK.
6. Има празно место на почетокот или на крајот
Ова е најмалку очигледната причина за грешката. # N / A во функција VPR, бидејќи визуелно е тешко да се видат овие дополнителни простори, особено кога работите со големи табели, кога повеќето податоци се надвор од екранот.
Решение 1: Дополнителни празни места во главната табела (каде што е функцијата VLOOKUP)
Ако се појават дополнителни празни места во главната табела, можете да се осигурате дека формулите работат правилно со приложување на аргументот вредност на пребарување (lookup_value) во функција TRIM (TRIM):
=VLOOKUP(TRIM($F2),$A$2:$C$10,3,FALSE)
=ВПР(СЖПРОБЕЛЫ($F2);$A$2:$C$10;3;ЛОЖЬ)
Решение 2: Дополнителни празни места во табелата за пребарување (во колоната за пребарување)
Ако има дополнителни празни места во колоната за пребарување - едноставни начини # N / A во формулата со VPR не може да се избегне. Наместо VPR Можете да користите формула за низа со комбинација на функции Индекс (ИНДЕКС), ПОИЗЛОЖЕНИ (натпревар) и 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! (#VALUE!) кога вредноста што се користи во формулата не се совпаѓа со типот на податоци. Во врска со VPR, тогаш обично има две причини за грешката #VALUE!.
1. Вредноста што ја барате е подолга од 255 знаци
Бидете внимателни: функционира VPR не може да бара вредности што содржат повеќе од 255 знаци. Ако вредноста што ја барате ја надмине оваа граница, ќе добиете порака за грешка. #VALUE!.
Одлука: Користете еден куп функции ИНДЕКС+СОПРЕДУВАЊЕ (ИНДЕКС + НАТПРЕВАР). Подолу е формулата што ќе биде добро за оваа задача:
=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. Аргументот Column_num е помал од 1
Тешко е да се замисли ситуација кога некој внесува вредност помала од 1да се означи колоната од која се извлекува вредноста. Иако е можно ако вредноста на овој аргумент се пресметува со друга функција на Excel вгнездена внатре VPR.
Значи, ако се случи дека аргументот коло_индекс_број (број_колона) помалку од 1функција VPR исто така ќе пријави грешка #VALUE!.
Доколку аргументот коло_индекс_број (колона_број) е поголем од бројот на колони во дадената низа, VPR ќе пријави грешка # РЕФ! (#SSYL!).
Грешка #NAME? во VLOOKUP
Наједноставниот случај е грешка #NAME? (#NAME?) – ќе се појави ако случајно напишете име на функција со грешка.
Решението е очигледно - проверете го вашиот правопис!
VLOOKUP не работи (ограничувања, предупредувања и одлуки)
Покрај прилично комплицираната синтакса, VPR има повеќе ограничувања од која било друга функција на Excel. Поради овие ограничувања, навидум едноставни формули со VPR често доведува до неочекувани резултати. Подолу ќе најдете решенија за неколку вообичаени сценарија каде VPR погрешно е.
1. VLOOKUP не е осетлив на букви
функција VPR не прави разлика помеѓу големи и мали букви и ги прифаќа малите и големите знаци како исти. Затоа, ако има неколку елементи во табелата кои се разликуваат само по букви, функцијата VLOOKUP ќе го врати првиот пронајден елемент, без оглед на буквата.
Одлука: Користете друга функција на Excel што може да изврши вертикално пребарување (ПРЕБАРУВАЊЕ, SUMPRODUCT, INDEX и MATCH) во комбинација со ТокмуА што го разликува случајот. За повеќе детали, можете да научите од лекцијата – 4 начини да го направите VLOOKUP чувствителен на букви во Excel.
2. VLOOKUP ја враќа првата пронајдена вредност
Како што веќе знаете, VPR ја враќа вредноста од дадената колона што одговара на првото пронајдено совпаѓање. Сепак, можете да го извлечете 2-то, 3-то, 4-то или кое било друго повторување на вредноста што ја сакате. Ако треба да ги извлечете сите дупликати вредности, ќе ви треба комбинација од функции Индекс (ИНДЕКС), ЛИСТ (МАЛО) и ЛИНИЈА (ROW).
3. Колона е додадена или отстранета на табелата
За жал, формулите VPR престанете да работи секогаш кога нова колона се додава или отстранува во табелата за пребарување. Ова се случува затоа што синтаксата VPR бара од вас да го наведете целиот опсег на пребарувањето и конкретниот број на колона за екстракција на податоци. Секако, и дадениот опсег и бројот на колоната се менуваат кога ќе избришете колона или ќе вметнете нова.
Одлука: И повторно функциите брзаат да помогнат Индекс (ИНДЕКС) и ПОИЗЛОЖЕНИ (натпревар). Во формулата ИНДЕКС+СОПРЕДУВАЊЕ Вие одделно ги дефинирате колоните за пребарување и преземање, и како резултат на тоа, можете да избришете или вметнете онолку колони колку што сакате без да се грижите дека ќе мора да ги ажурирате сите поврзани формули за пребарување.
4. Референците на ќелиите се погрешни при копирање формула
Овој наслов ја објаснува суштината на проблемот исцрпно, нели?
Одлука: Секогаш користете апсолутни референци на ќелиите (со симболот $) на записите на опсегот, на пример 2 $ A $: $ C $ 100 or $A: $C. Во лентата со формула, можете брзо да го префрлите типот на врската со кликнување F4.
VLOOKUP – работа со функциите IFERROR и ISERROR
Ако не сакате да ги плашите корисниците со пораки за грешка # N / A, #VALUE! or #NAME?, можете да прикажете празна ќелија или своја сопствена порака. Можете да го направите ова со поставување VPR во функција ИФЕРОР (IFERROR) во Excel 2013, 2010 и 2007 година или користете еден куп функции IF+ISERROR (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, слично на формулата IFERROR+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 и направете ги вашите формули да работат правилно.