Ошибка знач в excel как исправить при впр

Если вы работаете с ВЛКП, то, возможно, вы уже не #VALUE! несколько раз. В этой теме перечислены наиболее распространенные проблемы, которые могут возникнуть при наступлении ВЛИО, и возможные решения.

Проблема: длина аргумента искомое_значение превышает 255 символов.

Решение: Сократите значение или используйте сочетание функций ИНДЕКС и ПОИСКПОЗ в качестве обходного пути.

Это формула массива. Поэтому нажмите ввод (только при Microsoft 365 ) или CTRL+SHIFT+ВВОД.

Примечание: Если у вас есть текущая версия Microsoft 365 ,можно просто ввести формулу в выходную ячейку, а затем нажать ввод, чтобы подтвердить формулу как формулу динамического массива. В противном случае формулу необходимо ввести как формулу массива прежних вариантов: сначала выберем ячейку, введите формулу в ячейку вывода, а затем нажимая CTRL+SHIFT+ВВОД, чтобы подтвердить ее. Excel автоматически вставляет фигурные скобки в начале и конце формулы. Дополнительные сведения о формулах массива см. в статье Использование формул массива: рекомендации и примеры.

Проблема: аргумент номер_столбца содержит текст или значение меньше 0.

Номер столбца — это индекс столбца, который нужно вернуть, отсчитываемый вправо от столбца поиска.

Причиной может быть то, что вы допустили ошибку при вводе аргумента номер_столбца или случайно указали число менее 1 в качестве значения индекса (такое часто происходит, если другая функция Excel, вложенная в функцию ВПР, возвращает число, например 0, в качестве значения аргумента номер_столбца).

Минимальное значение аргумента col_index_num — 1, где 1 — столбец поиска, 2 — первый столбец справа от столбца поиска и так далее. Таким образом, если поиск ведется в столбце A, то 1 — это ссылка, 2 — столбец B, 3 — столбец C и так далее.

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.

См. также

Исправление ошибки #ЗНАЧ! #BUSY!

Все, что вам нужно знать о ВЛИО

Полные сведения о формулах в Excel

Рекомендации, позволяющие избежать появления неработающих формул

Обнаружение ошибок в формулах

Все функции Excel (по алфавиту)

Функции Excel (по категориям)

Перейти к содержимому

Этот урок объясняет, как быстро справиться с ситуацией, когда функция ВПР (VLOOKUP) не хочет работать в Excel 2013, 2010, 2007 и 2003, а также, как выявить и исправить распространённые ошибки и преодолеть ограничения ВПР.

В нескольких предыдущих статьях мы изучили различные грани функции ВПР в Excel. Если Вы читали их внимательно, то сейчас должны быть экспертом в этой области. Однако не без причины многие специалисты по Excel считают ВПР одной из наиболее сложных функций. Она имеет кучу ограничений и особенностей, которые становятся источником многих проблем и ошибок.

В этой статье Вы найдёте простые объяснения ошибок #N/A (#Н/Д), #NAME? (#ИМЯ?) и #VALUE! (#ЗНАЧ!), появляющихся при работе с функцией ВПР, а также приёмы и способы борьбы с ними. Мы начнём с наиболее частых случаев и наиболее очевидных причин, почему ВПР не работает, поэтому лучше изучать примеры в том порядке, в каком они приведены в статье.

• Исправляем ошибку #Н/Д
• Исправляем ошибку #ЗНАЧ! в формулах с ВПР
• Ошибка #ИМЯ? в ВПР
• ВПР не работает (проблемы, ограничения и решения)
• ВПР – работа с функциями ЕСЛИОШИБКА и ЕОШИБКА

Исправляем ошибку #Н/Д функции ВПР в Excel

В формулах с ВПР сообщение об ошибке #N/A (#Н/Д) – означает not available (нет данных) – появляется, когда Excel не может найти искомое значение. Это может произойти по нескольким причинам.

1. Искомое значение написано с опечаткой

Хорошая мысль проверить этот пункт в первую очередь! Опечатки часто возникают, когда Вы работаете с очень большими объёмами данных, состоящих из тысяч строк, или когда искомое значение вписано в формулу.

2. Ошибка #Н/Д при поиске приближённого совпадения с ВПР

Если Вы используете формулу с условием поиска приближённого совпадения, т.е. аргумент range_lookup (интервальный_просмотр) равен TRUE (ИСТИНА) или не указан, Ваша формула может сообщить об ошибке #Н/Д в двух случаях:

• Искомое значение меньше наименьшего значения в просматриваемом массиве.
• Столбец поиска не упорядочен по возрастанию.

3. Ошибка #Н/Д при поиске точного совпадения с ВПР

Если Вы ищете точное совпадение, т.е. аргумент range_lookup (интервальный_просмотр) равен FALSE (ЛОЖЬ) и точное значение не найдено, формула также сообщит об ошибке #Н/Д. Более подробно о том, как искать точное и приближенное совпадение с функцией ВПР.

4. Столбец поиска не является крайним левым

Как Вы, вероятно, знаете, одно из самых значительных ограничений ВПР это то, что она не может смотреть влево, следовательно, столбец поиска в Вашей таблице должен быть крайним левым. На практике мы часто забываем об этом, что приводит к не работающей формуле и появлению ошибки #Н/Д.

Решение: Если нет возможности изменить структуру данных так, чтобы столбец поиска был крайним левым, Вы можете использовать комбинацию функций ИНДЕКС (INDEX) и ПОИСКПОЗ (MATCH), как более гибкую альтернативу для ВПР.

5. Числа форматированы как текст

Другой источник ошибки #Н/Д в формулах с ВПР – это числа в текстовом формате в основной таблице или в таблице поиска.

Это обычно случается, когда Вы импортируете информацию из внешних баз данных или когда ввели апостроф перед числом, чтобы сохранить стоящий в начале ноль.

Наиболее очевидные признаки числа в текстовом формате показаны на рисунке ниже:

Кроме этого, числа могут быть сохранены в формате General (Общий). В таком случае есть только один заметный признак – числа выровнены по левому краю ячейки, в то время как стандартно они выравниваются по правому краю.

Решение: Если это одиночное значение, просто кликните по иконке ошибки и выберите Convert to Number (Конвертировать в число) из контекстного меню.

Если такая ситуация со многими числами, выделите их и щелкните по выделенной области правой кнопкой мыши. В появившемся контекстном меню выберите Format Cells (Формат ячеек) > вкладка Number (Число) > формат Number (Числовой) и нажмите ОК.

6. В начале или в конце стоит пробел

Это наименее очевидная причина ошибки #Н/Д в работе функции ВПР, поскольку зрительно трудно увидеть эти лишние пробелы, особенно при работе с большими таблицами, когда большая часть данных находится за пределами экрана.

Решение 1: Лишние пробелы в основной таблице (там, где функция ВПР)

Если лишние пробелы оказались в основной таблице, Вы можете обеспечить правильную работу формул, заключив аргумент lookup_value (искомое_значение) в функцию TRIM (СЖПРОБЕЛЫ):

=VLOOKUP(TRIM($F2),$A$2:$C$10,3,FALSE)
=ВПР(СЖПРОБЕЛЫ($F2);$A$2:$C$10;3;ЛОЖЬ)

Решение 2: Лишние пробелы в таблице поиска (в столбце поиска)

Если лишние пробелы оказались в столбце поиска – простыми путями ошибку #Н/Д в формуле с ВПР не избежать. Вместо ВПР Вы можете использовать формулу массива с комбинацией функций ИНДЕКС (INDEX), ПОИСКПОЗ (MATCH) и СЖПРОБЕЛЫ (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 вместо привычного Enter, чтобы правильно ввести формулу.

Ошибка #ЗНАЧ! в формулах с ВПР

В большинстве случаев, Microsoft Excel сообщает об ошибке #VALUE! (#ЗНАЧ!), когда значение, использованное в формуле, не подходит по типу данных. Что касается ВПР, то обычно выделяют две причины ошибки #ЗНАЧ!.

1. Искомое значение длиннее 255 символов

Будьте внимательны: функция ВПР не может искать значения, содержащие более 255 символов. Если искомое значение превышает этот предел, то Вы получите сообщение об ошибке #ЗНАЧ!.

Решение: Используйте связку функций ИНДЕКС+ПОИСКПОЗ (INDEX+MATCH). Ниже представлена формула, которая отлично справится с этой задачей:

=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 в рабочей книге New Prices и извлекать соответствующее значение из столбца D.

Если любая часть пути к таблице пропущена, Ваша функция ВПР не будет работать и сообщит об ошибке #ЗНАЧ! (даже если рабочая книга с таблицей поиска в данный момент открыта).

Для получения дополнительной информации о функции ВПР, ссылающейся на другой файл Excel, обратитесь к уроку: Поиск в другой рабочей книге с помощью ВПР.

3. Аргумент Номер_столбца меньше 1

Трудно представить ситуацию, когда кто-то вводит значение меньше 1, чтобы обозначить столбец, из которого нужно извлечь значение. Хотя это возможно, если значение этого аргумента вычисляется другой функцией Excel, вложенной в ВПР.

Итак, если случилось, что аргумент col_index_num (номер_столбца) меньше 1, функция ВПР также сообщит об ошибке #ЗНАЧ!.

Если же аргумент col_index_num (номер_столбца) больше количества столбцов в заданном массиве, ВПР сообщит об ошибке #REF! (#ССЫЛ!).

Ошибка #ИМЯ? в ВПР

Простейший случай – ошибка #NAME? (#ИМЯ?) – появится, если Вы случайно напишите с ошибкой имя функции.

Решение очевидно – проверьте правописание!

ВПР не работает (ограничения, оговорки и решения)

Помимо достаточно сложного синтаксиса, ВПР имеет больше ограничений, чем любая другая функция Excel. Из-за этих ограничений, простые на первый взгляд формулы с ВПР часто приводят к неожиданным результатам. Ниже Вы найдёте решения для нескольких распространённых сценариев, когда ВПР ошибается.

1. ВПР не чувствительна к регистру

Функция ВПР не различает регистр и принимает символы нижнего и ВЕРХНЕГО регистра как одинаковые. Поэтому, если в таблице есть несколько элементов, которые различаются только регистром символов, функция ВПР возвратит первый попавшийся элемент, не взирая на регистр.

Решение: Используйте другую функцию Excel, которая может выполнить вертикальный поиск (ПРОСМОТР, СУММПРОИЗВ, ИНДЕКС и ПОИСКПОЗ) в сочетании с СОВПАД, которая различает регистр. Более подробно Вы можете узнать из урока — 4 способа сделать ВПР с учетом регистра в Excel.

2. ВПР возвращает первое найденное значение

Как Вы уже знаете, ВПР возвращает из заданного столбца значение, соответствующее первому найденному совпадению с искомым. Однако, Вы можете заставить ее извлечь 2-е, 3-е, 4-е или любое другое повторение значения, которое Вам нужно. Если нужно извлечь все повторяющиеся значения, Вам потребуется комбинация из функций ИНДЕКС (INDEX), НАИМЕНЬШИЙ (SMALL) и СТРОКА (ROW).

3. В таблицу был добавлен или удалён столбец

К сожалению, формулы с ВПР перестают работать каждый раз, когда в таблицу поиска добавляется или удаляется новый столбец. Это происходит, потому что синтаксис ВПР требует указывать полностью весь диапазон поиска и конкретный номер столбца для извлечения данных. Естественно, и заданный диапазон, и номер столбца меняются, когда Вы удаляете столбец или вставляете новый.

Решение: И снова на помощь спешат функции ИНДЕКС (INDEX) и ПОИСКПОЗ (MATCH). В формуле ИНДЕКС+ПОИСКПОЗ Вы раздельно задаёте столбцы для поиска и для извлечения данных, и в результате можете удалять или вставлять сколько угодно столбцов, не беспокоясь о том, что придётся обновлять все связанные формулы поиска.

4. Ссылки на ячейки исказились при копировании формулы

Этот заголовок исчерпывающе объясняет суть проблемы, правда?

Решение: Всегда используйте абсолютные ссылки на ячейки (с символом $) при записи диапазона, например $A$2:$C$100 или $A:$C. В строке формул Вы можете быстро переключать тип ссылки, нажимая F4.

ВПР – работа с функциями ЕСЛИОШИБКА и ЕОШИБКА

Если Вы не хотите пугать пользователей сообщениями об ошибках #Н/Д, #ЗНАЧ! или #ИМЯ?, можете показывать пустую ячейку или собственное сообщение. Вы можете сделать это, поместив ВПР в функцию ЕСЛИОШИБКА (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;ЛОЖЬ);"Ничего не найдено. Попробуйте еще раз!")

ВПР: работа с функцией ЕОШИБКА

Так как функция ЕСЛИОШИБКА появилась в 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;ЛОЖЬ))

На сегодня всё. Надеюсь, этот короткий учебник поможет Вам справиться со всеми возможными ошибками ВПР и заставит Ваши формулы работать правильно.

Этот урок объясняет, как быстро справиться с ситуацией, когда функция ВПР (VLOOKUP) не хочет работать в Excel 2013, 2010, 2007 и 2003, а также, как выявить и исправить распространённые ошибки и преодолеть ограничения ВПР.

В нескольких предыдущих статьях мы изучили различные грани функции ВПР в Excel. Если Вы читали их внимательно, то сейчас должны быть экспертом в этой области. Однако не без причины многие специалисты по Excel считают ВПР одной из наиболее сложных функций. Она имеет кучу ограничений и особенностей, которые становятся источником многих проблем и ошибок.

В этой статье Вы найдёте простые объяснения ошибок #N/A (#Н/Д), #NAME? (#ИМЯ?) и #VALUE! (#ЗНАЧ!), появляющихся при работе с функцией ВПР, а также приёмы и способы борьбы с ними. Мы начнём с наиболее частых случаев и наиболее очевидных причин, почему ВПР не работает, поэтому лучше изучать примеры в том порядке, в каком они приведены в статье.

• Исправляем ошибку #Н/Д
• Исправляем ошибку #ЗНАЧ! в формулах с ВПР
• Ошибка #ИМЯ? в ВПР
• ВПР не работает (проблемы, ограничения и решения)
• ВПР – работа с функциями ЕСЛИОШИБКА и ЕОШИБКА

Исправляем ошибку #Н/Д функции ВПР в Excel

В формулах с ВПР сообщение об ошибке #N/A (#Н/Д) – означает not available (нет данных) – появляется, когда Excel не может найти искомое значение. Это может произойти по нескольким причинам.

1. Искомое значение написано с опечаткой

Хорошая мысль проверить этот пункт в первую очередь! Опечатки часто возникают, когда Вы работаете с очень большими объёмами данных, состоящих из тысяч строк, или когда искомое значение вписано в формулу.

2. Ошибка #Н/Д при поиске приближённого совпадения с ВПР

Если Вы используете формулу с условием поиска приближённого совпадения, т.е. аргумент range_lookup (интервальный_просмотр) равен TRUE (ИСТИНА) или не указан, Ваша формула может сообщить об ошибке #Н/Д в двух случаях:

• Искомое значение меньше наименьшего значения в просматриваемом массиве.
• Столбец поиска не упорядочен по возрастанию.

3. Ошибка #Н/Д при поиске точного совпадения с ВПР

Если Вы ищете точное совпадение, т.е. аргумент range_lookup (интервальный_просмотр) равен FALSE (ЛОЖЬ) и точное значение не найдено, формула также сообщит об ошибке #Н/Д. Более подробно о том, как искать точное и приближенное совпадение с функцией ВПР.

4. Столбец поиска не является крайним левым

Как Вы, вероятно, знаете, одно из самых значительных ограничений ВПР это то, что она не может смотреть влево, следовательно, столбец поиска в Вашей таблице должен быть крайним левым. На практике мы часто забываем об этом, что приводит к не работающей формуле и появлению ошибки #Н/Д.

Решение: Если нет возможности изменить структуру данных так, чтобы столбец поиска был крайним левым, Вы можете использовать комбинацию функций ИНДЕКС (INDEX) и ПОИСКПОЗ (MATCH), как более гибкую альтернативу для ВПР.

5. Числа форматированы как текст

Другой источник ошибки #Н/Д в формулах с ВПР – это числа в текстовом формате в основной таблице или в таблице поиска.

Это обычно случается, когда Вы импортируете информацию из внешних баз данных или когда ввели апостроф перед числом, чтобы сохранить стоящий в начале ноль.

Наиболее очевидные признаки числа в текстовом формате показаны на рисунке ниже:

Кроме этого, числа могут быть сохранены в формате General (Общий). В таком случае есть только один заметный признак – числа выровнены по левому краю ячейки, в то время как стандартно они выравниваются по правому краю.

Решение: Если это одиночное значение, просто кликните по иконке ошибки и выберите Convert to Number (Конвертировать в число) из контекстного меню.

Если такая ситуация со многими числами, выделите их и щелкните по выделенной области правой кнопкой мыши. В появившемся контекстном меню выберите Format Cells (Формат ячеек) > вкладка Number (Число) > формат Number (Числовой) и нажмите ОК.

6. В начале или в конце стоит пробел

Это наименее очевидная причина ошибки #Н/Д в работе функции ВПР, поскольку зрительно трудно увидеть эти лишние пробелы, особенно при работе с большими таблицами, когда большая часть данных находится за пределами экрана.

Решение 1: Лишние пробелы в основной таблице (там, где функция ВПР)

Если лишние пробелы оказались в основной таблице, Вы можете обеспечить правильную работу формул, заключив аргумент lookup_value (искомое_значение) в функцию TRIM (СЖПРОБЕЛЫ):

=VLOOKUP(TRIM($F2),$A$2:$C$10,3,FALSE)
=ВПР(СЖПРОБЕЛЫ($F2);$A$2:$C$10;3;ЛОЖЬ)

Решение 2: Лишние пробелы в таблице поиска (в столбце поиска)

Если лишние пробелы оказались в столбце поиска – простыми путями ошибку #Н/Д в формуле с ВПР не избежать. Вместо ВПР Вы можете использовать формулу массива с комбинацией функций ИНДЕКС (INDEX), ПОИСКПОЗ (MATCH) и СЖПРОБЕЛЫ (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 вместо привычного Enter, чтобы правильно ввести формулу.

Ошибка #ЗНАЧ! в формулах с ВПР

В большинстве случаев, Microsoft Excel сообщает об ошибке #VALUE! (#ЗНАЧ!), когда значение, использованное в формуле, не подходит по типу данных. Что касается ВПР, то обычно выделяют две причины ошибки #ЗНАЧ!.

1. Искомое значение длиннее 255 символов

Будьте внимательны: функция ВПР не может искать значения, содержащие более 255 символов. Если искомое значение превышает этот предел, то Вы получите сообщение об ошибке #ЗНАЧ!.

Решение: Используйте связку функций ИНДЕКС+ПОИСКПОЗ (INDEX+MATCH). Ниже представлена формула, которая отлично справится с этой задачей:

=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 в рабочей книге New Prices и извлекать соответствующее значение из столбца D.

Если любая часть пути к таблице пропущена, Ваша функция ВПР не будет работать и сообщит об ошибке #ЗНАЧ! (даже если рабочая книга с таблицей поиска в данный момент открыта).

Для получения дополнительной информации о функции ВПР, ссылающейся на другой файл Excel, обратитесь к уроку: Поиск в другой рабочей книге с помощью ВПР.

3. Аргумент Номер_столбца меньше 1

Трудно представить ситуацию, когда кто-то вводит значение меньше 1, чтобы обозначить столбец, из которого нужно извлечь значение. Хотя это возможно, если значение этого аргумента вычисляется другой функцией Excel, вложенной в ВПР.

Итак, если случилось, что аргумент col_index_num (номер_столбца) меньше 1, функция ВПР также сообщит об ошибке #ЗНАЧ!.

Если же аргумент col_index_num (номер_столбца) больше количества столбцов в заданном массиве, ВПР сообщит об ошибке #REF! (#ССЫЛ!).

Ошибка #ИМЯ? в ВПР

Простейший случай – ошибка #NAME? (#ИМЯ?) – появится, если Вы случайно напишите с ошибкой имя функции.

Решение очевидно – проверьте правописание!

ВПР не работает (ограничения, оговорки и решения)

Помимо достаточно сложного синтаксиса, ВПР имеет больше ограничений, чем любая другая функция Excel. Из-за этих ограничений, простые на первый взгляд формулы с ВПР часто приводят к неожиданным результатам. Ниже Вы найдёте решения для нескольких распространённых сценариев, когда ВПР ошибается.

1. ВПР не чувствительна к регистру

Функция ВПР не различает регистр и принимает символы нижнего и ВЕРХНЕГО регистра как одинаковые. Поэтому, если в таблице есть несколько элементов, которые различаются только регистром символов, функция ВПР возвратит первый попавшийся элемент, не взирая на регистр.

Решение: Используйте другую функцию Excel, которая может выполнить вертикальный поиск (ПРОСМОТР, СУММПРОИЗВ, ИНДЕКС и ПОИСКПОЗ) в сочетании с СОВПАД, которая различает регистр. Более подробно Вы можете узнать из урока — 4 способа сделать ВПР с учетом регистра в Excel.

2. ВПР возвращает первое найденное значение

Как Вы уже знаете, ВПР возвращает из заданного столбца значение, соответствующее первому найденному совпадению с искомым. Однако, Вы можете заставить ее извлечь 2-е, 3-е, 4-е или любое другое повторение значения, которое Вам нужно. Если нужно извлечь все повторяющиеся значения, Вам потребуется комбинация из функций ИНДЕКС (INDEX), НАИМЕНЬШИЙ (SMALL) и СТРОКА (ROW).

3. В таблицу был добавлен или удалён столбец

К сожалению, формулы с ВПР перестают работать каждый раз, когда в таблицу поиска добавляется или удаляется новый столбец. Это происходит, потому что синтаксис ВПР требует указывать полностью весь диапазон поиска и конкретный номер столбца для извлечения данных. Естественно, и заданный диапазон, и номер столбца меняются, когда Вы удаляете столбец или вставляете новый.

Решение: И снова на помощь спешат функции ИНДЕКС (INDEX) и ПОИСКПОЗ (MATCH). В формуле ИНДЕКС+ПОИСКПОЗ Вы раздельно задаёте столбцы для поиска и для извлечения данных, и в результате можете удалять или вставлять сколько угодно столбцов, не беспокоясь о том, что придётся обновлять все связанные формулы поиска.

4. Ссылки на ячейки исказились при копировании формулы

Этот заголовок исчерпывающе объясняет суть проблемы, правда?

Решение: Всегда используйте абсолютные ссылки на ячейки (с символом $) при записи диапазона, например $A$2:$C$100 или $A:$C. В строке формул Вы можете быстро переключать тип ссылки, нажимая F4.

ВПР – работа с функциями ЕСЛИОШИБКА и ЕОШИБКА

Если Вы не хотите пугать пользователей сообщениями об ошибках #Н/Д, #ЗНАЧ! или #ИМЯ?, можете показывать пустую ячейку или собственное сообщение. Вы можете сделать это, поместив ВПР в функцию ЕСЛИОШИБКА (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;ЛОЖЬ);"Ничего не найдено. Попробуйте еще раз!")

ВПР: работа с функцией ЕОШИБКА

Так как функция ЕСЛИОШИБКА появилась в 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;ЛОЖЬ))

На сегодня всё. Надеюсь, этот короткий учебник поможет Вам справиться со всеми возможными ошибками ВПР и заставит Ваши формулы работать правильно.

Оцените качество статьи. Нам важно ваше мнение:

Не работает ВПР в Excel н д? Причина в том, что формула не может найти необходимое значение, к примеру, из-за отсутствия искомого параметра в файле. Убедитесь, что такой показатель имеется в первоначальных данных, проверьте тип значений, удалите лишние пробелы, используйте способы точного / ориентировочного совпадения, задействуйте правильные аргументы и т. д. Ниже рассмотрим, в чем могут быть причины, и как действовать для восстановления работоспособности Эксель.

Причины и пути решения в Excel

Существует много причин, почему не работает ВПР в Excel и появляется надпись Н / Д. В каждой из ситуаций необходимо индивидуально подходить к решению вопроса с учетом возникшей неисправности.

Наиболее эффективный метод

В ситуации, когда не работает функция ВПР в Excel, проверьте наличие элемента на листе или задействуйте в формуле функцию обработки ошибок, к примеру, =ЕСЛИОШИБКА(ФОРМУЛА();0). В таком случае при появлении сбоев в расчете показывается ноль, а в ином случае — результат формулы. Можно дополнить запись “”, чтобы ничего не показывалось, или внести в скобки какую-либо запись.

Ошибка в типе параметров

Характерная причина, почему не работает ВПР в Excel — нахождение исходных / искомых данных к различным типам. К примеру, если вы используете ВПР в виде числа, а исходные данные сохраняются в качестве текста. Для решения вопроса убедитесь, что типы информации идентично. Для проверки формата сделайте следующее:

• Выберите ячейку (одну или несколько).
• Жмите правой кнопкой мышки.
• Выберите формат ячеек, а дальше Число.

• Измените формат.

Для принудительного внесения изменений нужно изменить формат для всего столбца. Для начала примените требуемое форматирование, а после выберите «Данные», «Текст по столбцам» и «Готово». После этого проверьте, появляется Н Д или нет.

Лишние пробелы

Распространенная причина, почему не работает формула ВПР в Excel, состоит в наличии пробелов. Для их удаления используйте функцию СЖПРОБЕЛЫ.

Ошибки метода поиска совпадения

Следующее объяснение, почему не срабатывает ВПР в Excel  и возвращается Н Д — ошибки в применении метода совпадения. По умолчанию у опции ВПР имеется аргумент «интервальный просмотр», который дает команду на поиск точного совпадения даже при отсутствии сортировки данных в таблице.

Для поиска точного совпадения введите для аргумента «интервальный_просмотр» показатель ЛОЖЬ.

При этом учтите, что ИСТИНА, которое дает возможность поиска приблизительного параметра, может вернуть ошибку Н / Д. При использовании опции ПОИСКПОЗ попробуйте поменять параметр аргумента «тип_сопоставления» для указания порядке сортировки таблицы.

Не соответствие числа строк / столбцов заданному диапазону

В ситуации, когда не работает ВПР в Excel и вылетает ошибка Н / Д, сделайте дополнительную проверку. Вам нужно убедиться, что диапазон, в отношении которого ссылается формула, правильный. Как вариант, можно ввести формулу массива в меньшее / большее количество ячеек с учетом ссылки на диапазон.

В ячейке введена надпись Н / Д или Н Д

В Эксель ВПР часто не работает, если пользователь вручную ввел в ячейку параметр #Н / Д или НД (). Для решения проблем его нужно поменять на фактические данные, как только они будут доступны. До этого момента формулы, в которых содержатся ссылки на эти ячейки, не смогут вычислить этот параметр. При этом будет возвращаться ошибка Н Д.

Другие ошибки

Дополнительно стоит выделить и ряд других ситуаций, когда ВПР в Excel по какой-то причине не работает:

1. В используемой формуле нет одного или более аргументов. Для исправления проблемы введите все необходимые документы и проверьте, работает опция или нет. Для контроля можно использовать Visual Basic.
2. Пользовательская опция недоступна. Для исправления проблемы убедитесь, что документ Excel с пользовательской функцией открыт, а опция работает корректно.
3. Макрос имеет функцию, которая возвращает Н Д. Если ВПР не работает по этой причине, для исправления ошибки убедитесь в правдивости аргументов и их нахождении в нужных местах.
4. Изменение защищенного файл с опцией ЯЧЕЙКА. Для исправления ситуации, когда ВПР в Excel не работает, жмите на комбинацию Ctrl+Alt+F9.
5. Столбец не является первым слева дли поискового диапазона. Для решения проблемы нужно ввести соответствующий параметр и проверить, появляется ли Н Д. Как вариант, можно использовать функции ИНДЕКС и ПОИСКПОК в качестве гибкой альтернативы для ВПР.
6. Неправильное форматирование числа. Бывают ситуации, когда цифры указаны в текстовом формате. Это часто происходит при импортировании сведений из внешней базы данных или при вводе апострофа перед числом для сохранения нуля в начале. Для решения проблемы жмите по ошибке и укажите Convert to Number. При появлении Н Д для многих чисел выделите их и жмите правой кнопкой мышки, а после выберите Format Cells и вкладку Число и Числовой.

Выше рассмотренные основные причины, почему не работает ВПР в Excel, а также описаны базовые шаги для устранения ошибки Н Д. Всегда начинайте с проверки правильности ввода параметра, а после переходите к другим вариантам.

Что за функция

В завершение кратко рассмотрим, что это за опция ВПР в Excel, и как она работает. Простыми словами, это опция, позволяющая переставлять данные из одной таблицы в соответствующие параметры другой. Английское название опции звучит как VLOOKUP. Это очень полезная опция, позволяющая сэкономить время и одновременно обработать большое количество параметров.

К примеру, в вас есть две таблицы. Первая — цены и названиями, вторая — заказ на покупку продукции. Осуществлять поиск в первом документе и пытаться вписать цену в заказ трудно. Необходимо, чтобы работа проходила автоматически. Для этого достаточно найти нужное значение в 1-м столбце и вернуть его содержимое из столбца той же строки, где находится название.

В комментариях расскажите, приходилось ли вам пользовался опцией ВПР, и случалась ли ситуация, когда она не работает в Excel, и появляется ошибка Н Д. Отдельно поделитесь, какие варианты решения вопроса можно использовать.

Отличного Вам дня!

Функция ВПР самая популярная и часто используемая функция. Знание данной функции очень поможет вам в вашей работе. Описание работы функции ВПР с примером вы можете посмотреть в специальной статье, а в данном уроке мы рассмотрим примеры, когда функция ВПР не работает или выдает ошибки.

Если вы научились использовать функцию, но у вас возникает ошибка, то в этой статье мы рассмотрим возможные ошибки и пути их решения.

Столбец поиска искомого значения не является крайним левым.

Самая распространенная ошибка, когда ВПР не работает. Давайте рассмотрим тот же пример, что мы рассматривали при описании функции ВПР. У нас есть таблица заказов и Прайс лист.

Обратите внимание, что в данном примере таблица Прайс лист отличается. Названия товаров теперь находятся не в первом столбце, а в третьем, после цены товара.

В текущем виде нельзя будет воспользоваться функцией ВПР, так как функция находит искомое значение в Крайнем левом столбце и возвращает значение из указанного вами номера столбца который находится правее крайнего левого столбца.
В нашем случае правее ничего нет, поэтому ВПР не будет работать.

Решение

1. Если есть возможность, то перенесите столбец, где ищется искомое значение в крайнее левое положение (либо просто левее столбца из которого необходимо возвращать значение, в нашем примере относительно столбца с ценой) как указано на рисунке выше.

2. Обычно переносить столбец не очень удобно, например в случаях, когда эту таблицу нам присылаются постоянно в таком формате, поэтому как правило применяют прием добавления слева таблицы дублирующего вспомогательного столбца.

Выделяем первый столбец прайс листа, выбираем «Главная», «Ячейка», «Вставить», «Вставить столбцы на лист». Либо используйте пустой столбец, если он у вас уже есть.
Встаем в первую ячейку вспомогательного столбца и просто делаем ссылку на нужный нам столбец и протягиваем формулу вниз.

Получили точную копию столбца с наименованием товара левее цены, теперь можно использовать функцию ВПР. Таблицу теперь необходимо брать начиная с нашего вспомогательного столбца.

3. Можно использовать комбинацию функций ИНДЕКС (INDEX) и ПОИСКПОЗ (MATCH), как более гибкую альтернативу для ВПР.

Функция ВПР возвращает ошибку #Н/Д (#N/A)

#Н/Д (#N/A) означает «Нет данных» («not available»). При протягивании формулы ВПР у вас появляется #Н/Д. Вопрос даже не в том, что ВПР не работает, а почему появляются данные значения.

Решение

1. Не закреплен диапазон таблицы

Если при протягивании формулы ВПР у вас первое значение подтянулось а в остальных отобразилось #Н/Д то скорее всего вы не закрепили диапазон таблицы с помощью $ и при протягивании у вас таблица начала съезжать вниз. Еще раз прочитайте внимательно описание функции ВПР с примером.

2. Искомое значение и значение в просматриваемом диапазоне не совпадает

На рисунке выше есть #Н/Д два раза. Один раз напротив товара «Савок» и другой напротив слова «Стол».

В первом случае понятно — товара «Савок» у нас нет в прайс листе, отсюда и ошибка.

Во втором случае непонятно, товар «Стол» у нас есть, но все равно Н/Д#. Как правило ошибка заключается в написании слов и форматах либо в таблице заказов либо на странице Прайс лист.

В нашем случае у нас после слова «Стол » стоит пробел, поэтому данное слово не находится в прайс листе. Для начала проверьте содержимое ячеек на невидимые пробелы. Особенно часто они встречаются в конце строки. Кликните на ячейку и переместитесь в конец формулы. Удалите лишние пробелы, если они есть.

В данном примере можно так же использовать функцию СЖПРОБЕЛЫ

=ВПР(СЖПРОБЕЛЫ(A3);$F$2:$I$16;3;0)

то есть искомое значение сразу пропускается через данную функцию, чтобы удалить в искомом значении лишние пробелы.

В нашем случаем мы ищем текстовые значения — «Наименование товара», иногда поиск идет по числовым значениям. В данном случае #Н/Д может появляться, когда искомое значение, например в формате числа, а в таблице в виде текста. Для исправления ошибку потребуется перевести формат текста в числа или наоборот, то есть сделать одинаковый формат.

Еще более редкий случай, это когда в слове может быть вместо русских слов латинские и наоборот. Например A и А это разные буквы разных алфавитов, поэтому и слова будут разными для ВПР. Чтобы проверить, просто сделайте в отдельном столбце ссылку одной ячейки на другую =A4=F6 в нашем примере эта формула вернет «Ложь» так как эти ячейки не равны (в одном слове есть лишний пробел), аналогичная ситуация будет если вместо русской буквы «С» будет английская буква «C».

Что интересно, используя #Н/Д можно находить неуникальные значения в списках. Например, у вас есть список сотрудников компании и вам присылают другой список сотрудников и просят из этого списка найти тех людей, которые не работают в вашей компании. Применяем ВПР одного списка к другому и там где #Н/Д и будут люди, которые не работают в вашей компании.

Функция ВПР возвращает ошибку #ЗНАЧ! (#VALUE!)

Обычно Excel сообщает об ошибке #VALUE! (#ЗНАЧ!), когда значение, использованное в формуле, не подходит по типу данных. Если брать функцию ВПР, то обычно стоит рассмотреть две основные причины ошибки #ЗНАЧ!.

1. Искомое значение больше 255 символов.

В функции ВПР есть ограничение на длину искомого значения – оно не должно быть более 255 символов. Если вы столкнулись с такой проблемой, то рекомендуем использовать для этих целей все ту же связку ИНДЕКС+ПОИСКПОЗ (INDEX+MATCH).

2. Неправильно указан полный путь на другую книгу.

Эта ошибка может возникнуть, если вы извлекаете данных из другой книги Excel прописали неполный путь до нее. Чтобы не ошибиться при написании формулы не пишите путь к другой книге от руки, а откройте эту книгу и выделяйте нужный диапазон.

3. Аргумент номер_столбца в функции ВПР меньше 1

Это может произойти если номер столбца вы ищите с помощью какой-то другой функции. Просто проверьте и убедитесь, что функция работает корректно.

Другие важные особенности работы функции ВПР

Если вы хотите разбить не саму ячейку, а текст в ячейке по столбцам, то тогда вам нужно смотреть другую статью «Как разбить текст по столбцам в Excel»

1. ВПР не чувствительна к регистру

Функция ВПР не чувствительна  к регистру и для нее все символы нижнего и верхнего регистра будут одинаковые. То есть, слово «Стол», «СТОЛ» и «стол» для функции ВПР будут одинаковыми.

Если вам необходимо учитывать регистр при использовании ВПР, то используйте другую функцию Excel, наприме (ПРОСМОТР, СУММПРОИЗВ, ИНДЕКС и ПОИСКПОЗ) в сочетании с СОВПАД, которая различает регистр и возвращается ИСТИНУ или ЛОЖЬ при совпадении или не совпадении с искомым значением.

2. ВПР возвращает первое найденное значение

Всегда помните, что ВПР возвращает первое найденное значение, поэтому если в нашем примере в прайс листе по ошибке будет два одинаковых товара с разной ценой, то возвращаться будет только первый цена.

3. Работа функции ВПР и добавлении или удалении столбца

Как правило при написании функции ВПР мы в ручную отсчитываем столбец и указываем его в виде числа. В нашем примере с прайс листом. Вы визуально определили, что вместе с вспомогательным столбцом, цена товара находится в третьем столбце и указали цифру 3 в функцию.

Если кто-то добавит перед столбцом с ценой какую-нибудь информацию, например об остатках товара, то теперь в третьем столбце будет остаток товаров, а цена будет в 4-м столбце, но формуле это автоматически не поменяется, мы должны помнить об этом и менять информацию.

Для решения этой проблемы можно использовать функцию ПОИСКПОЗ для поиска номера столбца. В качестве искомого значения мы можем указать текст «Цена» и искать в строке — шапке таблице Прайс лист. Теперь если добавлять столбцы в таблицу Прайс лист, то функция ПОИСКПОЗ все равно найдет ячейку со словом «Цена» и вернет на номер позиции данной ячейки.