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