Почему впр выдает ошибку перенос - Не ошибается лишь тот, кто ничего не делает!

Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel для iPad Excel Web App Excel для iPhone Excel для планшетов с Android Excel для телефонов с Android Еще…Меньше

Ошибки #ПЕРЕНОС! возникают, когда формула возвращает несколько результатов, а Excel не может вернуть результаты в сетку. Дополнительные сведения об этих типах ошибок см. в следующих разделах справки.

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

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

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

Excel не удалось определить размер массива с переносом, так как он является переменным и меняет размер между этапами вычислений. Например, следующая формула вызывает эту ошибку #ПЕРЕНОС! :

=ПОСЛЕДОВ(СЛУЧМЕЖДУ(1;1000))

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

Это значение ошибки обычно связано с использованием функций СЛЧИС, СЛМАССИВи СЛУЧМЕЖДУ. Другие переменные функции, например СМЕЩ, ДВССЫЛ и СЕГОДНЯ, не возвращают разные значения на каждом этапе вычисления.

Например, при размещении в ячейке E2 формулы =ВПР(A:A;A:C;2;ЛОЖЬ) (как в примере ниже) ранее выполнялся поиск ИД только в ячейке A2. Однако в динамическом массиве Excel формула вызывает ошибку #ПЕРЕНОС!, так как Excel выполняет поиск во всем столбце, возвращает 1 048 576 результатов и достигает конца сетки Excel.

Эту проблему можно устранить 3 простыми способами:

#	Способ	Формула
1	Ссылайтесь только на нужные значения поиска. Этот стиль формулы возвращает динамический массив, но не работает с таблицами Excel.	=ВПР(A2:A7;A:C;2;ЛОЖЬ)
2	Сошлитесь на значение в той же строке, а затем скопируйте формулу. Этот традиционный стиль формулы работает в таблицах, но не возвращает динамический массив.	=ВПР(A2;A:C;2;ЛОЖЬ)
3	Создайте в Excel запрос неявного пересечения с помощью оператора @, а затем скопируйте формулу. Этот стиль формулы работает в таблицах, но не возвращает динамический массив.	=ВПР(@A:A;A:C;2;ЛОЖЬ)

Способ

Формула

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

=ВПР(A2:A7;A:C;2;ЛОЖЬ)

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

=ВПР(A2;A:C;2;ЛОЖЬ)

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

=ВПР(@A:A;A:C;2;ЛОЖЬ)

Формулы массивов с переносом не поддерживаются в таблицах Excel. Попробуйте переместить формулу из таблицы или преобразовать таблицу в диапазон (нажмите Конструктор таблиц > Инструменты > Преобразовать в диапазон).

Формула массива с переносом, которую вы пытаетесь ввести, привела к нехватке памяти в Excel. Попробуйте сослаться на меньший массив или диапазон.

Формулы массива с переносом не могут выполнять перенос в объединенные ячейки. Отмените объединение ячеек или переместите формулу в другой диапазон, который не пересекается с объединенными ячейками.

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

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

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

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

См. также

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

Функция ФИЛЬТР

Функция СЛУЧМАССИВ

Функция ПОСЛЕДОВ

Функция СОРТ

Функция СОРТПО

Функция УНИК

Динамические массивы и поведение массива с переносом

Оператор неявного пересечения: @

Нужна дополнительная помощь?

Нужны дополнительные параметры?

Изучите преимущества подписки, просмотрите учебные курсы, узнайте, как защитить свое устройство и т. д.

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

Источник

Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel для iPad Excel Web App Excel для iPhone Excel для планшетов с Android Excel для телефонов с Android Еще…Меньше

Формула с пролитой массивом, в который вы попытались ввести формулу, выходит за пределы диапазона. Попробуйте еще раз с небольшим диапазоном или массивом.

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

Распространенные причины: полные ссылки на столбцы

При создании формул в lookup_value в них часто неправильно засоряются. Перед динамическим массивом Приложение Excel учитывало только значение в той же строке, что и формулу, и игнорировало все остальные, так как при функции ВЛОП ожидалось только одно значение. При вводе динамических массивов Excel рассматривает все значения, предоставляемые lookup_value. Это означает, что если в качестве аргумента lookup_value столбец, Excel попытается найти все 1 048 576 значений в столбце. Когда все будет готово, он попытается пролить их на сетку и, скорее всего, зажмет #SPILL! ошибка «#ЗНАЧ!».

Например, при размещении в ячейке E2, как по примеру ниже, формула =ВЛОЖЕННАЯ(A:A;A:C;2;ЛОЖЬ) ранее только подыскала бы ИД в ячейке A2. Однако в динамическом массиве Excel формула приведет к #SPILL! из-за того, что Excel подытнет весь столбец, отвернет результат с результатом 1 048 576 и пожмет в конце сетки Excel.

Существует три простых способа решения этой проблемы:

#	Подход	Формула
1	Ссылаясь только на значения подпапок, которые вас интересуют. Этот стиль формулы возвращает динамический массив, ноне работает с таблицами Excel.	=В ПРОСМОТР(A2:A7;A:C;2;ЛОЖЬ)
2	Ссылаясь только на значение в той же строке, скопируйте формулу вниз. Этот традиционный стиль формул работает в таблицах,но не возвращаетдинамический массив.	=В ПРОСМОТР(A2;A:C;2;ЛОЖЬ)
3	Запрос на выполнение неявного пересечения с помощью оператора @ и копирование формулы вниз. Этот стиль формулы работает в таблицах,но не возвращаетдинамический массив.	=ВЛ.В.@A:A;A:C;2;ЛОЖЬ)

Подход

Формула

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

=В ПРОСМОТР(A2:A7;A:C;2;ЛОЖЬ)

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

=В ПРОСМОТР(A2;A:C;2;ЛОЖЬ)

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

=ВЛ.В.@A:A;A:C;2;ЛОЖЬ)

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

Вы всегда можете задать вопрос специалисту Excel Tech Community, попросить помощи в сообществе Answers community, а также предложить новую функцию или улучшение на веб-сайте Excel User Voice.

См. также

Функция ФИЛЬТР

Функция СЛУЧМАССИВ

Функция ПОСЛЕДОВ

Функция СОРТ

Функция СОРТПО

Функция УНИК

Ошибки #ПЕРЕНОС! в Excel

Динамические массивы и поведение рассеянного массива

Неявное пересечение: @

Нужна дополнительная помощь?

Нужны дополнительные параметры?

Изучите преимущества подписки, просмотрите учебные курсы, узнайте, как защитить свое устройство и т. д.

Источник

Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 for Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel для iPad Excel Web App Excel для iPhone Excel для планшетов с Android Excel для телефонов с Android Еще…Меньше

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

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

=ПОСЛЕДОВ(СЛУЧМЕЖДУ(1;1000))

Эту проблему можно устранить 3 простыми способами:

#	Способ	Формула
1	Ссылайтесь только на нужные значения поиска. Этот стиль формулы возвращает динамический массив, но не работает с таблицами Excel.	=ВПР(A2:A7;A:C;2;ЛОЖЬ)
2	Сошлитесь на значение в той же строке, а затем скопируйте формулу. Этот традиционный стиль формулы работает в таблицах, но не возвращает динамический массив.	=ВПР(A2;A:C;2;ЛОЖЬ)
3	Создайте в Excel запрос неявного пересечения с помощью оператора @, а затем скопируйте формулу. Этот стиль формулы работает в таблицах, но не возвращает динамический массив.	=ВПР(@A:A;A:C;2;ЛОЖЬ)

Способ

Формула

=ВПР(A2:A7;A:C;2;ЛОЖЬ)

=ВПР(A2;A:C;2;ЛОЖЬ)

=ВПР(@A:A;A:C;2;ЛОЖЬ)

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

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

См. также

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

Функция ФИЛЬТР

Функция СЛУЧМАССИВ

Функция ПОСЛЕДОВ

Функция СОРТ

Функция СОРТПО

Функция УНИК

Динамические массивы и поведение массива с переносом

Оператор неявного пересечения: @

Нужна дополнительная помощь?

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

В нашем учебном примере показаны продажи по менеджерам Иванов, Петров, Сидоров за январь – июнь. Мы хотим продублировать продажи Петрова при помощи ссылки на диапазон ячеек:

Обратите внимание на формулу в ячейке B6, мы в ней указываем тот диапазон, который хотим повторить. В предыдущих версиях Excel такая функция вернула бы только значение из первой ячейки, а мы получили ошибку #ПЕРЕНОС! вместо требуемого нам результата. Что делать? В чем причина такой ошибки?

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

Готово, ошибку #ПЕРЕНОС! мы исправили. Запомните общее правило, которое пригодится вам в таких ситуациях: Проверьте, что содержится в ячейках, где вы хотите отобразить данные. Возможно, что информация может быть специфично оформлена, к примеру белым цветом, что не позволяет быстро найти заполненные ячейки. Если вы уверены, что в ячейках нет нужных вам данных – выделите их все и нажмите Del для удаления содержимого.

Если вы сталкиваетесь в Excel и с другими ошибками, рекомендую прочитать наши статьи:

Как исправить ошибку #ССЫЛКА! в Excel
Как исправить ошибку #ЗНАЧ в Excel
Как исправить ошибку #Н/Д в Excel
Как исправить ошибку #ДЕЛ/0! в Excel
Как убрать ошибку #### в Excel

Спасибо за внимание.

Содержание

Как исправить ошибку #ПЕРЕНОС! в Excel
Как исправить ошибку #ЗНАЧ! в Excel
Как исправить ошибку #ПУСТО! в Excel
Как исправить ошибку #ИМЯ? в Excel

Как исправить ошибку #ПЕРЕНОС! в Excel

Прежде чем рассмотреть ошибку #ПЕРЕНОС! (#SPILL), рассмотрим, что такое перенос. В Excel это означает, что формула возвращает несколько значений (массив), и они автоматически переносятся в соседние ячейки.

Диапазон переноса и значения внутри будут изменяться при обновлении источника данных.

Ошибка #ПЕРЕНОС! возникает, когда формула возвращает несколько значений, но Excel не может вывести один или несколько результатов.

У ошибки может быть множество причин. Поиск решения будет зависеть от используемой версии Excel.

Веб-версия: наведите мышь на зеленый треугольник в верхнем левом углу ячейки с ошибкой #ПЕРЕНОС!. Появится сообщение с описанием ошибки.
Десктопная версия: щелкните по ячейке с ошибкой #ПЕРЕНОС!. Нажмите на треугольник, который появится слева от ячейки. Причина ошибки будет указана в верхней части меню справки.

Рассмотрим решения, которые подойдут для наиболее распространенных вариаций этой ошибки.

Диапазон для переноса содержит одну или более ячеек с значениями

Решение: очистить диапазон для переноса.

Диапазон для переноса находится внутри таблицы

Решение 1: преобразовать таблицу в диапазон данных.

Для этого выполните следующие шаги:

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

Обратите внимание, что расположение меню и списка команд могут отличаться в зависимости от версии.

Решение 2: переместить формулу за границы таблицы.

Диапазон для переноса содержит объединенные ячейки

Решение: разделить ячейки внутри диапазона.

Как исправить ошибку #ЗНАЧ! в Excel

Ошибка #ЗНАЧ! (#VALUE) возникает в следующих случаях:

что-то не так с ячейкой (ячейками), на которую ссылается формула,
что-то не так с самой формулой.

Иногда найти источник проблемы не просто. Ниже — о самых распространенных случаях и как и их исправить.

Математическая формула ссылается на текст

Формулы с математическими операторами могут вычислять только числа. Если одна или несколько ячеек, на которые ссылается формула, содержит текст, формула будет возвращать ошибку.

Решение: использовать вместо формулы (уравнения, составленного пользователем) функцию (формулу, заранее заданную Excel). Функции по умолчанию игнорируют большую часть текстовых значений и производят расчеты лишь с числами.

Например, на примере ниже функция =СУММ(B2,B11) будет игнорировать текст в ячейке B11.

Скриншот: Zapier.com

Стоит помнить, что эта функция не оповещает автоматически о пропусках ячеек, и ее результат может вводить в заблуждение.

Одна или более ячеек содержат пробелы

В этих случаях ячейка выглядит пустой.

Решение: найти и заменить пробелы. Вот как это сделать:

выделите диапазон ячеек, к которому обращается формула;
нажмите на иконку с биноклем, нажмите «Найти и выделить»;
в окне «Найти и заменить» выберите «Заменить»;
в поле «Найти» вставьте пробелы;
поле «Заменить на» оставьте пустым;
нажмите «Заменить все».

Как исправить ошибку #ПУСТО! в Excel

Ошибка #ПУСТО! (#REF) возникает, когда формулы ссылается на ячейку, которая уже не существует. Разберем наиболее распространенные причины возникновения ошибки и как их исправить.

Ячейка, на которую ссылается формула, удалена

Формула использует прямые ссылки на ячейки (каждая ячейка отделена запятой), но одна или несколько ячеек удалены. Это основная причина, почему Excel не рекомендует использовать прямые ссылки на ячейки.

Решение 1: Если ячейки удалены случайно, отмените это действие.

Решение 2: Обновите формулу, чтобы она ссылалась на диапазон ячеек. В этом случае Excel сможет произвести вычисления, даже если одна из ячеек была удалена.

Формула содержит относительные ссылки

Относительная ссылка означает, что используемые данные привязаны к ячейке, в которую вставлена формула. Например, если формулу =СУММ(B2:E2) скопировать из ячейки G2 в G3, Excel предположит, что должен суммировать все ячейки из колонок B-E в ряду 3.

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

Например, если формулу =СУММ(G2:G7) перенести в ячейку I4, Excel предположит, что пользователю требуется сложить шесть ячеек над клеткой I4. В данном случае это невозможно, так как доступны лишь три ячейки.

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

Например, формула =СУММ(G2:G7) с абсолютными ссылками будет выглядеть как: =СУММ($G$2:$G$7).

Как исправить ошибку #ИМЯ? в Excel

Ошибка #ИМЯ? (#NAME) возникает, если название формулы неверно написано. Рассмотрим основные решения проблемы.

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

Решение: обновить название формулы. Лучший способ избегать опечаток — использовать встроенный редактор формул Excel. Когда пользователь начинает печатать название формулы, программа автоматически предложит список названий, содержащих те же буквы.

Неверное название формулы

Иногда пользователи вводят название несуществующей формулы или формулы, которая называется иначе (например, ПЛЮС вместо реальной функции СУММ). В этом случае формула вернет ошибку #ИМЯ?.

Решение: найти правильное имя формулы и обновить ее.

Вот как это сделать:

выберите в меню раздел «Формулы»;
нажмите на «Вставить функцию» в левой части панели инструментов, в открывшемся окне «Вставка функции» просмотрите недавно использовавшиеся функции или полный список всех доступных функций;
выберите и вставьте нужную функцию;
в редакторе формул обновите ячейки, к которым будет отсылаться формула (поля зависят от выбранной функции).

Эти советы применимы и к ошибкам в Google Таблицах (за исключением #ПЕРЕНОС!, которая часто отображается как #REF).

Источник.

Фото на обложке: Aihr.com

Подписывайтесь на наш Telegram-канал, чтобы быть в курсе последних новостей и событий!

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

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

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

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

Содержание

Исправляем ошибку #Н/Д функции ВПР в Excel
1. Искомое значение написано с опечаткой
2. Ошибка #Н/Д при поиске приближённого совпадения с ВПР
3. Ошибка #Н/Д при поиске точного совпадения с ВПР
4. Столбец поиска не является крайним левым
5. Числа форматированы как текст
6. В начале или в конце стоит пробел
Ошибка #ЗНАЧ! в формулах с ВПР
1. Искомое значение длиннее 255 символов
2. Не указан полный путь к рабочей книге для поиска
3. Аргумент Номер_столбца меньше 1
Ошибка #ИМЯ? в ВПР
ВПР не работает (ограничения, оговорки и решения)
1. ВПР не чувствительна к регистру
2. ВПР возвращает первое найденное значение
3. В таблицу был добавлен или удалён столбец
4. Ссылки на ячейки исказились при копировании формулы
ВПР – работа с функциями ЕСЛИОШИБКА и ЕОШИБКА
ВПР: работа с функцией ЕСЛИОШИБКА
ВПР: работа с функцией ЕОШИБКА

Исправляем ошибку #Н/Д функции ВПР в 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?

Функция ВПР – это очень мощный инструмент поиска. Но если он по каким-то причинам завершился неудачно, то вы получите сообщение об ошибке #Н/Д (#N/A в английском варианте).

Давайте постараемся вместе ответим на вопрос: «Почему функция ВПР не работает?»

В первую очередь, сообщение об ошибке вы увидите, если данные, которое вы ищете, действительно отсутствуют в указанном диапазоне поиска. Здесь мы бессильны.

Все остальные случаи связаны с тем, что мы что-то не учли или сделали неверно.

Итак, почему ВПР не находит значение, хотя оно есть?

Неточность при вводе данных.
Опечатка при вводе функции.
Неверные ссылки.
Неверно указан параметр «интервальный просмотр».
Столбец поиска не является первым слева столбцом диапазона поиска.
Несовпадение форматов данных.
- Преобразуем число в текст.
- Преобразуем текст в число.
- Если числовой и текстовый форматы беспорядочно перемешаны?
Лишние пробелы и непечатаемые знаки.
Неправильно указан номер столбца.
Неверная ссылка на данные из другой таблицы
После изменения таблицы функция ВПР перестала работать
Как убрать сообщение #Н/Д в ВПР?

Неточность при вводе данных.

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

Это вполне может случиться, особенно если вы записываете его прямо в формулу.

Опечатка при вводе функции.

Если вы видите ошибку #ИМЯ?, то это означает, что при записи названия самой функции вы допустили неточность – перепутали или добавили лишнюю букву.

Проверьте синтаксис, и всё будет в порядке.

Неверные ссылки.

Проверьте, верно ли вы указали диапазон поиска, а также координаты значения, которое мы будем искать.

Случается, что в формуле вы указали обычные (относительные) ссылки, забыв заменить их на абсолютные ($), а затем произвели какие-то действия с таблицей. К примеру, добавили столбец. Ваши ссылки в формуле теперь будут указывать на неверные координаты.

Важно! При таком изменении ссылок вы можете и не знать, что найдено не то, что нужно. Ведь какое-то значение все же возвращено, хоть и неверное. Поэтому будьте внимательны в использовании относительных и абсолютных ссылок! Рекомендуем по этой причине стараться использовать «умные» таблицы или именованные диапазоны.

Неверно указан параметр «интервальный просмотр».

Включен поиск до первого приблизительного совпадения в отсортированном диапазоне (параметр = ИСТИНА или вовсе опущен), но на самом деле данные не отсортированы.

Об этой проблеме мы подробно говорили ранее.

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

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

Если ваши просматриваемые данные находятся не в первом, а во втором или другом столбце, то она не сможет найти ни одного совпадения, и вы вновь увидите сообщение #Н/Д.

Это важное ограничение нельзя забывать.

Несовпадение форматов данных.

Формат ячейки, откуда берется искомое значение наименования (например D3 в нашем случае), и формат ячеек первого столбца (A3:A21) из диапазона поиска отличаются (например, числовой и текстовый). Этот случай особенно часто встречается при использовании вместо текстовых наименований числовых кодов (номера счетов, артикулы, идентификаторы, даты и т.п.). Ведь число, записанное в таблицу Excel, может быть в двух принципиально разных состояниях — как число и как текст. И визуально их отличить практически невозможно.

Преобразуем число в текст.

Как видите, с виду записи одинаковы, однако в D6 артикул сохранен как число, а в A10 – как текст (на таких ячейках обычно видна пометка — зелёный уголок). Текст не может быть равнозначен числу, поэтому получаем #Н/Д. ВПР не находит значение, хотя оно с виду есть.

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

Используем формулу

=ВПР(ТЕКСТ(D6;»#»);$A$3:$B$21;2;ЛОЖЬ)

Как видно, обычную ссылку на D6 мы заменяем конструкцией

TEXT(D6,»#»)

Первый аргумент – это адрес, а второй аргумент означает формат, в который мы будем преобразовывать значение (в нашем случае – текстовый).

Можно поступить и проще — «приклеить» к числу пустую строку при помощи оператора склейки &.

=ВПР(D6&»»;$A$3:$B$21;2;ЛОЖЬ)

Запомните простое правило:

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

Если несовпадение форматов встречается редко, то можно просто исправить формат записи данных. Измените формат ячейки на текстовый и пересохраните ее содержание (F2 и затем ENTER). Содержимое будет преобразовано в текст.

Как видите, ошибка исчезла, поскольку теперь текстовое значение сравнивается с таким же текстом.

Преобразуем текст в число.

А если нужно сделать обратное преобразование? Преобразовать число как текст в обычное число, чтобы все данные в таблице были одного типа? Давайте рассмотрим на примере.

Добавьте перед проблемным значением двойной минус. Это означает — дважды умножить на минус 1. А еще со школы мы помним, что минус на минус дает плюс. В результате ничего не изменится. Но есть важный момент.

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

Так же и в нашем случае. Текст преобразуется в число. Итак, наша формула выглядит следующим образом:

=ВПР(—A2;$D$2:$E$8;2;0)

Если числовой и текстовый форматы беспорядочно перемешаны?

А теперь — самый сложный случай. И здесь тоже есть простое решение.

Если поиск с преобразованием значения в число не удался, то будем искать его как текст. Объединяем оба варианта при помощи функции ЕСЛИОШИБКА.

=ЕСЛИОШИБКА(ВПР(—O2;$R$2:$S$8;2;0);ВПР(O2&»»;$R$2:$S$8;2;0))

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

Лишние пробелы и непечатаемые знаки.

Формула не работает, потому что в артикуле присутствуют пробелы или невидимые непечатаемые знаки (перенос строки и т.п.).

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

Такая неточность может встречаться довольно часто, и обнаружить её визуально практически невозможно. К примеру, если наименование состоит из 2 слов, то вполне возможно случайное появление лишнего пробела между этими словами.

Могут быть также случайно добавлены символы, невидимые на экране и на печати (табуляция, перевод строки и т.д.). Визуально определить такие неточности ввода вы не сможете.

В этом случае можно использовать функции СЖПРОБЕЛЫ (TRIM) и ПЕЧСИМВ (CLEAN) для удаления лишних пробелов либо других невидимых символов. Вместо

=ВПР(D8;$A$3:$B$21;2;ЛОЖЬ)

вводим

=ВПР(СЖПРОБЕЛЫ(ПЕЧСИМВ(D8));$A$3:$B$21;2;ЛОЖЬ)

или

=VLOOKUP(TRIM(CLEAN(D8)),$A$3:$B$21,2,FALSE)

Функция СЖПРОБЕЛЫ убирает пробелы, а ПЕЧСИМВ удаляет все непечатаемые и невидимые символы.

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

Неправильно указан номер столбца.

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

Он не может быть меньше 1 и не может быть больше, чем количество столбцов в указанном для поиска диапазоне. Если он указан неверно, то получим ошибку #ЗНАЧ!

Когда вы видите такое, пересчитайте количество колонок в диапазоне, который вы указали, и убедитесь, что оно не меньше, чем номер, указанный третьим аргументом функции. Быть может, вы указали 4, а колонок всего 3.

И в любом случае – это число должно быть больше 1. Вряд ли кто-то из вас будет руками вводить 0 или -1, но если вы получаете его при помощи каких-то вычислений, вложенных в формулу, то здесь-то и может быть проблема.

Неверная ссылка на данные из другой таблицы

Мы с вами говорили о том, что диапазон поиска может быть расположен в другой таблице или даже в другом файле Excel.

Если вы случайно допустили неточность при указании ссылки на эти данные, то увидите сообщение #ССЫЛКА!.

Это будет означать, что вы ссылаетесь на неверный диапазон, который Excel найти не может.

После изменения таблицы функция ВПР перестала работать

Вы сделали все правильно и до тех пор, пока вы не вставили несколько строк или столбцов в вашу таблицу, все работало верно.

Теперь же появилась ошибка #Н/Д либо результаты стали явно неверны. В чем дело?

А дело всё в том, что вы не использовали абсолютные ссылки, не добавляли к адресам ячеек, строк и столбцов знак $. В результате после добавления (или удаления) строк или столбцов ваши ссылки изменились, и все сломалось.

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

Постарайтесь отменить сделанные изменения, благо Excel хранит всю историю изменений вашей таблицы.

Важно! Всегда используйте абсолютные ссылки, где это необходимо, чтобы застраховать себя от подобных неожиданных изменений.

Как убрать сообщение #Н/Д в ВПР?

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

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

Можно легко перехватить ошибки и заменить их на любое другое подходящее нам значение (например, на ноль) с помощью функции ЕСЛИОШИБКА.

Синтаксис её подобен ЕСЛИ. В качестве условия используется выражение, которое потенциально может возвратить сообщение об ошибке.

=ЕСЛИОШИБКА([выражение];[значение_если_ошибка])

Задача – заменить его числом или выражением, указанным во втором аргументе. Рассмотрим это на примере.

В нашем случае маракуйя отсутствует в прайсе. Но вместо #Н/Д мы выведем ноль.

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

=ЕСЛИОШИБКА(ВПР(A4;$D$2:$E$7;2;0);0)

Естественно, вместо нуля можно вписать «не найдено» либо любое другое сообщение, а можно просто поставить пробел. Но в случае, когда на основе этих ячеек затем производятся какие-то вычисления, ноль, возможно, будет предпочтительнее.

Надеемся, теперь вы сможете ответить на вопрос: «Почему не работает функция ВПР в Excel?».

[the_ad_group id=»48″]

Еще полезные примеры:

Содержание

1 Функция ВПР в Экселе – быстрый перенос данных
- 1.1 Критерий поиска
- 1.2 Диапазон данных
- 1.3 Номер столбца для итогового значения
- 1.4 Условие для поиска
2 Функция ВПР в Экселе: пошаговая инструкция
- 2.1 ВПР пример 1
- 2.2 ВПР пример 2
- 2.3 ВПР пример 3
3 Особенности использования формулы ВПР в Excel
4 Примеры функции ВПР в Excel
- 4.1 Конструкция с функцией ЕСЛИОШИБКА
- 4.2 Разные форматы критерия в таблицах
- 4.3 Функция СЖПРОБЕЛЫ для чистки текстового критерия
- 4.4 Подсчет номера столбца в большой таблице
5 Видео-урок: Как использовать функцию ВПР в Excel
6 Функция ВПР в Excel для чайников
- 6.1 С одним условием
- 6.2 С несколькими условиями
7 Синтаксис
8 Аргументы функции
9 Дополнительная информация

Функция ВПР в Экселе – быстрый перенос данных

Самое простое применение функция ВПР это быстрый перенос данных из одной таблицы в другую.

Например, у вас есть большой прайс на 500 позиций и запрос от покупателя, скажем на 50 позиций (в реальности и прайс и запрос могут быть гораздо больше, но принцип от этого не меняется).

Вам нужно быстро найти цены на эти 50 позиций. Разумеется, можно отдельно искать каждую позицию в большом прайсе и потратить на это 30 – 60 минут, а можно сделать это менее чем за минуту при помощи функции ВПР.

Итак, у нас в прайсе 500 позиций. Позиции обозначаются следующим образом, буквами обозначается вид позиции, а цифрами модификация.

Например, «Стул_1» и «Стул_21» это два совершенно разных стула.

Цены в прайсе указаны для примера и вряд ли имеют отношение к реальным ценам.

Обозначим задачу.

В ООО «ЫкэА» пришел запрос от «Петровича».

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

Однако это нас не страшит, во-первых, у нас есть ВПР, во-вторых мы и не такое видали.

Вот собственно и сам запрос:

Петрович требует, чтобы мы очень быстро проставили цены в его запросе. Ждать он намерен максимум 5 минут. Ведь другие поставщики уже завалили его предложениями.

Нам не хочется терять такого клиента и мы практически мгновенно открываем прайс:

Получается у нас должно быть открыто два файла (две книги в Эксель). Запрос от Петровича и Прайс.

Это как раз то что необходимо, осталось только перенести цены из прайса в запрос.

Для этого перейдем в таблицу запроса и в первой ячейке столбца «Цены» (D4) введем «=впр» и два раза кликнем на значок функции:

Сразу же после этого, в строке формулы нужно поставить курсор внутри надписи ВПР и нажать Fx, перед вами появится окно с аргументами функции ВПР:

В аргументах функции вы говорите Экселю что и где нужно искать:

Искомое значение — это значение (в данном случае наименование), цену которого вы хотите найти в прайсе. Соответственно кликайте на первую ячейку столбца «Наименование».

Далее, сразу переходите в «Прайс»:

Теперь в аргументах функции заполните следующие поля:

Таблица — выделяете столбцы, которые содержат искомые наименования и цены, таким образом, чтобы наименования были крайним левым столбцом.

Так работает функция ВПР — ищет искомые значения в крайнем левом столбце (для ВПР это столбец №1). Когда ВПР находит искомое значение он начинает смотреть правее, в тот столбец, который вы указали в «Номере столбца».

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

Интервальный просмотр — ставьте 0. Ноль обозначает точное соответствие.

После заполнения аргументов функции нажимайте «Ок» и если всё сделано верно, то в столбце «Цена» (файл «Запрос от Петровича»), появится цена.

Вам нужно протянуть цены на оставшиеся ячейки:

Коллеги, вот и всё, вы овладели функцией ВПР.

Очень важное замечание!

Обратите внимание на то, что сейчас мы работали в двух разных файлах (книгах).

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

Делает это он при помощи значка $, который проставляет перед столбцами и строками таблицы.

Это позволяет не съезжать формуле когда вы протягиваете её вниз. Это очень актуально когда вы работаете в рамках одного листа или одной книги (в этом случае Эксель автоматически Не закрепляет ячейки).

Давайте посмотрим что получиться если протянуть формулу «без закрепления»:

Обратите внимание, что для первой ячейки всё в порядке и диапазон B3:C502 точно соответствует той таблице, которую мы выделили для поиска данных, однако (без закрепления) так будет не всегда, по мере «протягивания» формулы ВПР вниз, будет смещаться и таблица, пока однажды, мы не увидим вот такую странную надпись #Н/Д:

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

Посему, если не хотите чтобы от вас уезжали, закрепляйте диапазон.

Очень важное замечание №2

Как вы заметили, формулы ссылаются на определённые ячейки, другими словами между формулами и исходными данными есть связь. Стоит изменить исходные данные и значения в формулах сразу изменятся.

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

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

Для этого нужно выделить столбец с формулами, нажать Ctrl+C и в левом верхнем углу выбрать «Вставить» — «Вставить значения».

Для тех кто не любит изучать картинки, я записал небольшое видео в котором показываю всё то, что мы проговорили выше (кроме вставки значений):

Критерий поиска

Адрес ячейки листа Excel, в которой указываются данные для осуществления поиска в таблице.

Диапазон данных

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

Номер столбца для итогового значения

Номер столбца, откуда будет браться значение при найденном совпадении.

Условие для поиска

Логическое значение (истина/1 или ложь/0), которое указывает приблизительное совпадение искать (1) или точное (0).

Функция ВПР в Экселе: пошаговая инструкция

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

Во второй – цены:

Если перечень товаров в обеих таблицах совпадает, то, зная магическое сочетание Ctrl+C и Ctrl+V, данные о ценах можно легко подставить к данным о количестве. Однако очередность позиций в обеих таблицах не совпадает. Тупо скопировать цены и подставить к количеству не получится.

Поэтому мы не можем прописать формулу умножения и «протянуть» вниз на все позиции.

Что делать? Надо как-то цены из второй таблицы подставить к соответствующему количеству в первой, т.е. цену товара А к количеству товара А, цену Б к количеству Б и т.д.

Вот так.

Функция ВПР в Эксель легко справится с задачей.

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

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

Кликаем по надписи «ВПР». Открывается следующее диалоговое окно.

Теперь нужно заполнить предлагаемые поля. В первом окошке «Искомое_значение» нужно указать критерий для ячейки, в которую мы вписываем формулу. В нашем случае это ячейка с наименованием товара «А».

Следующее поле «Таблица». В нем нужно указать диапазон данных, где будет осуществляться поиск нужных значений. В нашем случае это вторая таблица с ценой. При этом крайний левый столбец выделяемого диапазона должен содержать те самые критерии, по которым осуществляется поиск (столбец с наименованиями товаров). Затем таблица выделяется вправо минимум до того столбца, где находятся искомые значения (цены). Можно и дальше вправо выделить, но это уже ни на что не влияет. Главное, чтобы выделенная таблица начиналась со столбца с критериями и захватывала нужный столбец с данными. Также следует обратить внимание на тип ссылок, они должны быть абсолютными, т.к. формула будет копироваться в другие ячейки.

Следующее поле «Номер_столбца» — это число, на которое столбец с искомыми данными (ценами) отстоит от столбца с критерием (наименованием товара) включительно. То есть отсчет идет, начиная с самого столбца с критерием. Если у нас во второй таблице оба столбца находятся рядом, то нужно указать число 2 (первый – критерий, второй — цены). Часто бывает, что данные отстоят от критерия на 10 или 20 столбцов. Это не важно, Excel все сосчитает.

Последнее поле «Интервальный_просмотр», где указывается тип поиска: точное (0) или приблизительное (1) совпадение критерия. Пока ставим 0 (или ЛОЖЬ). Второй вариант рассмотрен ниже.

Нажимаем ОК. Если все правильно и значение критерия есть в обеих таблицах, то на месте только что введенной формулы появится некоторое значение. Остается только протянуть (или просто скопировать) формулу вниз до последней строки таблицы.

Теперь легко рассчитать стоимость простым умножением количества на цену.

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

ВПР пример 1

После создания отсортированного учебного файла щёлкнуть любую свободную ячейку (например, F9): в ней будет возвращён результат.

На строке формул щёлкнуть fx.

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

4. Выделить функцию ВПР и кликнуть OK.

5. В появившемся окне «Аргументы функции» обратите внимание на рекомендацию, подчёркнутую синим цветом. Её игнорирование делает выдаваемые функцией результаты непредсказуемыми. Правильной будет только часть результатов, поэтому перед началом работы сортировка ключевого столбца обязательна.

В окне аргументов необходимо корректно заполнить три обязательных параметра, выделенных жирным шрифтом (последний необязательный параметр будет рассмотрен в примере 3).

6. Как видно, заданное или ключевое значение в окне аргументов именуется «Искомым». Такое название не кажется мне удачным: оно задаётся изначально, а не ищется, и используется как ключ к поиску. Предположим, что нас интересует зарплата, получаемая сотрудницей Ириной Соколовой. Самый простой способ задать верхний параметр – щёлкнуть на ячейке A8, после чего – кликнуть на строке параметра «Таблица».

7. В качестве параметра «Таблица» выделяется весь заполненный участок Excel, исключая заголовки столбцов. А на строке «Номер столбца» следует ввести номер столбца, содержащего список зарплат, т. е. 2. Как видно, правильное значение зарплаты сотрудницы Ирины Соколовой появляется прямо в окне аргументов.

8. После клика на OK возвращённое функцией значение появится и в ячейке, зарезервированной для результата.

ВПР пример 2

Естественно, что аналогичным образом могут быть извлечены данные из других информационных столбцов. Предположим, что нам нужно узнать дату рождения всё той же Ирины Соколовой. Аргументы функции ВПР претерпят единственное изменение на строке «Номер столбца». Как видно, функция возвращает, на первый взгляд, непонятное число 29025.

После щелчка на ОК оно появится и в ячейке результата.

Опытные пользователи Excel знают, что в этой программе даты (и время) хранятся в числовом формате, начиная с условно принятой даты 0 января 1900 года. Для отображения полученного значения в привычном человеку виде достаточно придать ячейке результата формат даты. Для этого сначала открыть её контекстное меню, и щёлкнуть пункт «Формат ячеек».

В появившемся одноимённом окне:

щёлкнуть пункт «Дата»;

в правом списке при необходимости поменять вариант отображения;
щёлкнуть OK.

После этого отображаемая в ячейке результата дата примет «человеческий» вид.

ВПР пример 3

Этот пример отличается от первого двумя аргументами – первым и заполненным последним. Как видно, в первом аргументе вместо имени использован подстановочный знак звёздочки *. Если не заполнять последний аргумент, то возвращаемое функцией значение будет ошибочным. Ввод логического значения ЛОЖЬ сделает результат правильным.

Из последнего примера вытекают два важных следствия, связанных с необязательным аргументом.

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

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

Функция ВПР имеет свои особенности, о которых следует знать.

1. Первую особенность можно считать общей для функций, которые используются для многих ячеек путем прописывания формулы в одной из них и дальнейшим копированием в остальные. Здесь нужно обращать внимание на относительность и абсолютность ссылок. Конкретно в ВПР критерий (первое поле) должно иметь относительную ссылку (без знаков $), так как у каждой ячейки свой собственный критерий. А вот поле «Таблица» должно иметь абсолютную ссылку (адрес диапазона прописывается через $). Если этого не сделать, то при копировании формулы диапазон «поедет» вниз и многие значения просто не найдутся, так как искать будет негде.

2. Номер столбца, указываемый в третьем поле «Номер_столбца» при использовании Мастера функций, должен отсчитываться, начиная с самого критерия.

3. Функция ВПР из диапазона с искомыми данными выдает первое сверху значение. Это значит, что, если во второй таблице, откуда мы пытаемся «подтянуть» некоторые данные, присутствуют несколько ячеек с одинаковым критерием, то в рамках выделенного диапазона ВПР захватит первое сверху значение. Об этом следует помнить. К примеру, если мы хотим к цене товара подтянуть количество из другой таблицы, а там этот товар встречается несколько раз (в нескольких строках), то к цене подтянется первое сверху количество.

4. Последний параметр формулы, который 0 (нуль), ставить нужно обязательно. Иначе формула может криво работать.

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

После вызова функции разрывания внешних связей появится диалоговое окно, где нужно нажать кнопку «Разорвать связь» и затем «Закрыть».

Это позволит удалить сразу все внешние ссылки.

Примеры функции ВПР в Excel

Для следующих примеров использования функции ВПР возьмем немного другие данные.

Требуется цены из второй таблицы подтянуть в первую. В качестве критерия здесь используется код. Ниже показаны этапы вычисления ВПР.

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

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

Конструкция с функцией ЕСЛИОШИБКА

Вместе с функцией ВПР часто используют функцию ЕСЛИОШИБКА, которая «заглушает» ошибки #Н/Д и вместо них возвращает некоторое значение. Обычно это 0 или пусто.

Как видно, ошибок больше нет, а вместо них пустые ячейки.

Разные форматы критерия в таблицах

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

Первый случай, когда критерии в первой таблице сохранены как числа, а критерии во второй таблице – как текст.

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

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

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

Две ошибки по-прежнему связаны с тем, что эти товары отсутствую во второй таблице. Чтобы их заглушить, можно вновь воспользоваться функцией ЕСЛИОШИБКА.

Вторая ситуация, заключается в том, что «текстом» являются критерий из первой таблицы. Форматы снова не совпадают.

Как и в прошлый раз, будем вносить коррективы в функцию ВПР. Преобразовать «текст» в «число» еще проще. Достаточно к ссылке на «текстовый» критерий добавить 0 или умножить на 1.

Бывает еще и третья, смешанная ситуация. Она встречается гораздо реже. Это когда в первой и второй таблице критерии сохранены и как число, и как текст, вперемешку. Здесь потребуется задействовать сразу все описанные выше функции: ЕСЛИОШИБКА, ТЕКСТ и +0. Вначале прописываем ЕСЛИОШИБКА и в качестве первого аргумента этой функции записываем ВПР с какой-либо конструкцией для изменения формата. Например, ВПР с формулой ТЕКСТ. В качестве второго аргумента (т.е. того, что должно быть в случае ошибки) записываем вторую конструкцию ВПР с +0. Таким образом, если ВПР с функцией ТЕКСТ не выдает ошибку, значит все ОК. Но если первая конструкция возвращает ошибку #Н/Д, то функция ЕСЛИОШИБКА подставляет вторую конструкцию – ВПР с +0. Другими словами, мы вначале принудительно делаем все критерии текстовыми, а затем, числовыми. Таким образом, ВПР проверяет оба формата. Один из них совпадет с форматом во второй таблице. Немного громоздко получается, но в целом все работает.

Отсутствующие критерии по-прежнему вызывают ошибку #Н/Д. В таком случае всю формулу можно еще раз «обернуть» в ЕСЛИОШИБКА.

Функция СЖПРОБЕЛЫ для чистки текстового критерия

В качестве критерия рекомендуется брать уникальный код, в котором опечатки, характерные для текста, маловероятны. Но иногда все-таки кода нет и критерием выступает текст (названия организаций, фамилии людей и т.д.). В этом случае возможны случайные ошибки в написании. Одна из распространенных ошибок – лишние пробелы. Проблема решается просто с помощью функции СЖПРОБЕЛЫ для всех критериев. Сделать это можно внутри формулы ВПР, а можно и предварительно пройтись по всем критериям в обеих таблицах. Кому как удобней.

Подсчет номера столбца в большой таблице

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

Здорово экономит время.

Видео-урок: Как использовать функцию ВПР в Excel

Функция ВПР в Excel для чайников

На примере ниже, в таблице справа, мы хотим подставить данные результатов экзамена по математике для студента Сидорова. Нам известны данные по всем студентам из таблицы слева. Используя формулу, мы можем подставить необходимые для нас данные.

С одним условием

Рассмотрим функцию на простом примере поиска сотрудника по присвоенному ему коду. Таблицу нужно отсортировать в порядке возрастания.

Кликнуть по ячейке справа от таблицы или под ней и вписать искомый номер.
В ячейке рядом ввести функцию ВПР через знак «=», либо же использовать опцию «Вставить функцию» в разделе «Формулы». Проще использовать именно опцию – там есть подсказки, какие именно данные нужны.
Задать параметры поиска, заполнив соответствующие поля. Интервальный просмотр в данном случае пропустим.
Нажать «Enter» – и появится результат поиска.
Когда таблица не отсортирована и данные введены в хаотичном порядке, результат будет неправильный – программа найдет ближайшее соответствие («ИСТИНА»). Но можно не сортировать таблицу, а указать интервальный просмотр «ЛОЖЬ».
Чтобы найти другой элемент, следует просто изменить первый аргумент, и результат автоматически преобразится.

С несколькими условиями

Часто на практике требуется сравнить данные нескольких диапазонов и выбрать значение с учетом 2-х и более критериев. Здесь задействована также функция ЕСЛИ, которая отвечает как раз за условия.

Из таблицы нужно найти показатель выручки по конкретному менеджеру в определенный день:

В первую выделенную ячейку вписать дату, во вторую – фамилию менеджера. В третьей ячейке будет происходить поиск.
Кликнуть по пустой ячейке и ввести формулу =ВПР(G1;ЕСЛИ(C2:C12=G2;A2:D12;»»);4;0).
Для подтверждения действия зажать комбинацию клавиш Ctrl+Shift+Enter. Нажатие на «Enter» не сработает в этом случае, потому что формула должна быть выполнена в массиве (об этом свидетельствуют скобки «{}», в которые взята вся формула).

Синтаксис

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]) – английская версия

=ВПР(искомое_значение, таблица, номер_столбца, [интервальный_просмотр]) – русская версия

Аргументы функции

lookup_value (искомое_значение) – это величина для поиска, из крайнего левого столбца таблицы. Это может быть значение, ссылка на ячейку или текстовая строка. В примере со студентами это их фамилии;
table_array (таблица) – это диапазон данных, в котором будет осуществлен поиск. Это может быть ссылка на диапазон ячеек или именованный диапазон. В примере с таблицей со студентами, это будет вся таблица, которая содержит оценку и фамилии студентов;
col_index (номер_столбца) – это порядковый номер столбца в диапазоне с данными, из которого будет получена искомая величина;
[range lookup] ([интервальный_просмотр]) – этот аргумент указывает на точность совпадения данных при поиске. Укажите “0” – если точное, “1” – если приблизительное совпадение.

Дополнительная информация

совпадение искомых данных может быть точным и приблизительным;
при сопоставлении по приблизительной точности данных убедитесь, что данные в таблицах отсортированы в убывающем порядке (от большого к малому). Иначе, результат сопоставления будет некорректным;
при сопоставлении данных по приблизительной точности:
– если функция не найдет искомое значение, она выдаст наибольшую величину, которая будет меньше чем значения поиска;
– если функция при сопоставлении выдает ошибку #N/A, то искомое значение меньше чем самая маленькая величина в искомом диапазоне;
– вы можете использовать подстановочные знаки для искомых значений.

Источники

https://pavel-istomin.ru/funkciya-vpr-v-eksele/
https://WindowsTips.ru/vpr-v-excel
https://statanaliz.info/excel/funktsii-i-formuly/funktsiya-excel-vpr-vlookup-i-gpr-hlookup/
https://gospodaretsva.com/excel-vlookup.html
https://excelhack.ru/funkciya-vlookup-vpr-v-excel/
https://sysadmin-note.ru/article/funkciya-vpr-v-excel/

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

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

Содержание

1 Столбец поиска искомого значения не является крайним левым.
2 Функция ВПР возвращает ошибку #Н/Д (#N/A)
- 2.1 1. Не закреплен диапазон таблицы
- 2.2 2. Искомое значение и значение в просматриваемом диапазоне не совпадает
3 Функция ВПР возвращает ошибку #ЗНАЧ! (#VALUE!)
- 3.1 1. Искомое значение больше 255 символов.
- 3.2 2. Неправильно указан полный путь на другую книгу.
- 3.3 3. Аргумент номер_столбца в функции ВПР меньше 1
4 Другие важные особенности работы функции ВПР
- 4.1 1. ВПР не чувствительна к регистру
- 4.2 2. ВПР возвращает первое найденное значение
- 4.3 3. Работа функции ВПР и добавлении или удалении столбца

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

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

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

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

Решение

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-м столбце, но формуле это автоматически не поменяется, мы должны помнить об этом и менять информацию.

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

Как работает ВПР. Для чайников
- Синтаксис функции ВПР
- Интервальный просмотр — 0 или 1, ЛОЖЬ или ИСТИНА
- Тайна четвертого параметра ВПР
Как сделать ВПР — понятная пошаговая инструкция
ВПР по вхождению подстроки
Не работает ВПР — ошибки и причины
- ВПР выдает ошибку #Н/Д
- ВПР не подтягивает значение, хотя искомое в таблице есть

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

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

Как работает ВПР. Для чайников

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

Синтаксис функции ВПР

Расшифровка синтаксиса ВПР

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

Когда я думаю о функции, я вспоминаю знакомую всем телепередачу «Что? Где? Когда?».

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

А четвертый аргумент («интервальный просмотр») вроде бы даже не обязательный, но на поверку очень важен. Ведь от его значения зависит, как будет работать функция. Жаль, вопроса «Как?» в телепередаче не было.

Интервальный просмотр — 0 или 1, ЛОЖЬ или ИСТИНА

Скажу заранее. Если нужно просто найти искомое значение и вернуть то, что напротив — используйте в этом параметре 0 (или ЛОЖЬ, но 0 напечатать проще) и не читайте дальше.

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

Тот, кто перевел последний параметр range lookup с английского как «интервальный просмотр», испортил жизнь многим новичкам в Excel.

Потому что там нет ничего интервального, все вполне себе сплошное. А корректно перевести параметр следовало бы «способ просмотра диапазона«.

Также справочный текст говорит следующее:

«Приблизительное» и «точное» совпадение в описании четвертого параметра ВПР

ЛОЖЬ или 0 обозначает точное совпадение;
ИСТИНА, 1 или эквивалентный им пропуск параметра (т.к. это значение по умолчанию) обозначает приблизительное совпадение

И вот с этим приблизительным совпадением сразу возникает ряд вопросов. Что значит приблизительное? Насколько? Каким алгоритмом считается «приблизительность»? Зачем сортировать? И почему по возрастанию?

Тайна четвертого параметра ВПР

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

Когда четвертый параметр ЛОЖЬ или 0 — используется линейный поиск;
Когда он ИСТИНА, 1 или не указан явно, но используется по умолчанию — используется бинарный поиск.

В чем разница?

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

Бинарный же поиск в Excel позволяет находить данные практически мгновенно, так как выполняет четыре основных шага:

Длина диапазона данных делится пополам и позиция чтения перемещается в середину.
Найденное значение (пусть n) сравнивается с тем, которое мы ищем (пусть m).
Если m > n, то берется вторая часть массива, если m < n — первая часть.
Далее шаги 1-3 повторяются на выбранной части диапазона.

Простыми словами, это похоже на поиск по словарю. Открываем его посередине, смотрим, в какой половине нужное нам значение. В первой? Тогда открываем первую часть посередине, и так продолжаем делить напополам, пока не находим искомое.

В сравнении с линейным поиском, на целиком заполненном столбце Excel (1048576 строк, или 2 в 20 степени) бинарный будет в ~52.000 раз быстрее! (1048576 поделить на 20).

Но есть несколько моментов, которые нужно знать для работы с ВПР в этом режиме:

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

=ЕСЛИ(ВПР(искомое;диапазон;1;1)<>искомое;"";ВПР(искомое;диапазон;n;1))

Так будет выглядеть формула, которая будет возвращать пустоту, если искомое не найдено, при этом будет оставаться столь же быстрой (практически мгновенной даже при работе с сотнями тысяч строк!).

Важный момент — в Excel 2019 и выше даже при использовании параметра 0 используется оптимальный быстрый алгоритм, поэтому большого прироста скорости в последних версиях такая формула не даст (зато даст в Excel 2016 и всех версий что раньше). Вероятнее всего, в Microsoft решили, что пришло время упростить жизнь пользователям, которые не хотят во всём этом разбираться

Как сделать ВПР — понятная пошаговая инструкция

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

Ниже пошаговая инструкция по использованию функции ВПР с полезными лайфхаками и картинками. Итак:

Если читали выше, я упоминал, как просто запомнить синтаксис функции ВПР. 3 вопроса в указанном порядке. Что? Где? Когда? И потом уже «Как?»

Что?
Что будет искать формула? Как правило, здесь указывается адрес ячейки, в которой находится искомое значение.
Где?
Где будет искать формула значение из первого шага? Обычно тут указывается диапазон ячеек, состоящий из нескольких столбцов. Лайфхак: выделяйте столбцы целиком (например, B:D), а не диапазон ($B$2:$D$250). Это быстрее и не требует закреплять диапазоны (а закреплять их нужно, иначе при протягивании формулы вниз адрес будет меняться). Выделить столбцы можно, проведя по буквам столбцов левой кнопкой мыши (как показывает стрелка на скриншоте).
Когда?
Когда остановиться, считая слева направо для выбора возвращаемого формулой значения? В данном случае нам нужна сумма, а это второй столбец таблицы.
Как?
Как искать? О разнице способов поиска ВПР читайте выше: Интервальный просмотр — 0 или 1, ЛОЖЬ или ИСТИНА? Обычно выбирается линейный поиск, так что тут вводим «0».

И на всякий случай, пройдем все шаги подряд и посмотрим, как это выглядит на практике.

Ввод формулы с функцией ВПР — пошаговая простейшая инструкция.

ВПР по вхождению подстроки

Помимо поиска по точному совпадению, ВПР умеет искать данные и с неточным совпадением, т.к. поддерживает подстановочные символы (? и *). Важно, что их учёт возможен только в режиме линейного поиска (четвертый параметр 0 или ЛОЖЬ). Ниже пример с использованием знака вопроса, обозначающего один любой символ:

Использование ВПР с подстановочными символами для поиска по неточному совпадению

Не работает ВПР — ошибки и причины

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

ВПР выдает ошибку #Н/Д

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

Хотите избавиться от ошибки, когда искомое не найдено? Почитайте, как сделать это с помощью функции ЕСЛИОШИБКА в соответствующей статье. А я лишь приведу формулы оттуда:

=ЕСЛИОШИБКА(ВПР(ячейка-или-значение;диапазон;номер-столбца;0);"ошибка")
=ЕСЛИОШИБКА(ВПР(ячейка-или-значение;диапазон;номер-столбца;0);"")

ВПР не подтягивает значение, хотя искомое в таблице есть

Иногда такое бывает — вы видите, что в таблице есть данные, которые вы ищете, но ВПР будто не срабатывает. Не находит данные. Абсолютно всегда проблема не в ВПР, а в самих данных, и они действительно не совпадают. Самые распространенные проблемы, когда визуально в ячейках одинаковы, но на поверку — нет:

В таблице искомых значений или в диапазоне поиска присутствуют лишние пробелы, которые нужно удалить.
Вместо обычных пробелов в ячейках используются неразрывные пробелы, визуально между ними нет никакой разницы, но программа её видит.
В данных присутствуют переносы строк, символы табуляции, возврат каретки и другие виды «невидимых» символов
Вместо кириллицы используется латиница или наоборот, для букв с идентичным написанием (для компьютера русская А и английская A — разные символы!). Тогда нужно заменить кириллицу на похожую латиницу (или наоборот).

Рекомендация в таких случаях следующая. Попробуйте сравнить ячейки, которые видите как одинаковые, отдельно (Например, скопируйте их в ячейки A1 и A2 на другой лист). Сначала сравните целиком (=A1=A2, должно вернуть «ИСТИНА»), если конструкция возвращает «ЛОЖЬ», сравните их уже посимвольно (первый символ с первым, второй со вторым и т.д.), здесь вам поможет функция ПСТР.

Понравилась статья? Поддержите ее автора!
Набор инструментов !SEMTools поможет решить множество задач без ввода сложных формул и сэкономит ваше время. И вам полезно, и автору приятно!

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

Синтаксис и описание функции ВПР в Excel

Функция ВПР в Excel примеры

Функция ВПР с несколькими условиями

Почему не работает функция ВПР

Синтаксис

=ВПР(элемент_который_нужно_найти;адрес_таблицы_где_находится_искомое_значение;порядковый_номер_столбца;интервальный_просмотр)

Элемент – может быть числовым (адрес ячейки) или текстовым («текст»).

Адрес таблицы – диапазон ячеек, где примерно находится значение.

Номер столбца – принимает целое число из диапазона от 1 до n, из него будет извлечен результат.

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

Важно! Искомое значение должно находиться слева (в первом столбце) от возвращаемого элемента.

В русифицированной версии Excel аргументы вводятся через знак «;», в англоязычной – через запятую.

Видео — «Быстрый перенос данных с помощью функции ВПР в Экселе»

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

Это очень актуально для тех кто работает в закупках и отправляет заказы поставщику.

Обычно происходит следующая ситуация. Вы отправляете заказ поставщику, через некоторое время получаете ответ в виде счёта и сверяете заказ с счётом.

Всё ли есть в счёте, в нужном ли количестве, по правильным ли ценам и т.д.

Ошибки

Когда пользователь ошибается при вводе данных или выборе диапазона, вместо результата отображаются различные ошибки: #Н/Д, #ЗНАЧ, #ССЫЛКА.

Ошибка #Н/Д появляется, если:

Указанный диапазон не содержит искомый элемент.
Искомый элемент меньше, чем минимальный в массиве.
Задан точный поиск (аргумент «ЛОЖЬ» или 0), а искомого нет в диапазоне.
Задан приблизительный поиск (аргумент «ИСТИНА» или 1), но данные не отсортированы по возрастанию.
Разный формат (числовой и текстовый) у ячейки, откуда берется искомое, и ячейки с данными первого столбца.
В коде есть пробелы или невидимые непечатаемые знаки.
Используются значения времени или большие десятичные числа.

Во избежание ошибки #Н/Д, когда ВПР не находит значение, рекомендуется использовать следующую формулу: =ЕСЛИОШИБКА(ВПР(C2;A1:B12;2;ЛОЖЬ);0) – вместо 0 можно написать «не найдено».

Ошибка #ЗНАЧ появляется, если:

В качестве номера столбца указано число 0.
Длина первого аргумента превышает 255 знаков.

Ошибка #ССЫЛКА появляется, если третий аргумент больше количества столбцов в таблице.

Функция ВПР в Экселе – быстрый перенос данных

Самое простое применение функция ВПР это быстрый перенос данных из одной таблицы в другую.

Например, «Стул_1» и «Стул_21» это два совершенно разных стула.

Цены в прайсе указаны для примера и вряд ли имеют отношение к реальным ценам.

Обозначим задачу.

В ООО «ЫкэА» пришел запрос от «Петровича».

Однако это нас не страшит, во-первых, у нас есть ВПР, во-вторых мы и не такое видали.

Вот собственно и сам запрос:

Нам не хочется терять такого клиента и мы практически мгновенно открываем прайс:

Получается у нас должно быть открыто два файла (две книги в Эксель). Запрос от Петровича и Прайс.

Это как раз то что необходимо, осталось только перенести цены из прайса в запрос.

В аргументах функции вы говорите Экселю что и где нужно искать:

Далее, сразу переходите в «Прайс»:

Теперь в аргументах функции заполните следующие поля:

Интервальный просмотр — ставьте 0. Ноль обозначает точное соответствие.

Вам нужно протянуть цены на оставшиеся ячейки:

Коллеги, вот и всё, вы овладели функцией ВПР.

Очень важное замечание!

Обратите внимание на то, что сейчас мы работали в двух разных файлах (книгах).

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

Делает это он при помощи значка $, который проставляет перед столбцами и строками таблицы.

Давайте посмотрим что получиться если протянуть формулу «без закрепления»:

Посему, если не хотите чтобы от вас уезжали, закрепляйте диапазон.

Очень важное замечание №2

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

Использование нескольких условий для формулы ВПР

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

Вот небольшая таблица для наглядности.

Допустим, перед нами стоит задача определить, по какой стоимости был завезен гофрированный картон от . В этом случае нам требуется два условия:

Материал.
Производитель.

Но это еще не все, потому что каждый производитель завозит сразу несколько товаров. Как можно выкрутиться в этой ситуации? А вот, как:

К таблице присоединяется крайний левый столбец, чтобы поставщики и материалы были в одной группе.
Критерии также нужно объединить. 13
Курсор устанавливается в требуемом месте, и в скобках указываются аргументы функции (или же через соответствующее диалоговое окно). =ВПР(I6;$A$2:$D$15;4;ЛОЖЬ)
. После этого Excel определит необходимую стоимость. 14

Функция ВПР в Экселе – сравнение двух таблиц

Итак, у вас есть «Заказ поставщику» (1) и ответ поставщика в виде «Счёта на оплату» (2).

Для удобства восприятия я разместил их на одном листе:

Ваша задача сверить количество позиций и их цены.

Для начала проверим все ли позиции и по правильной ли цене указал в счёте поставщик.

Для этого нужно из Счёта перетянуть данные в Заказ при помощи функции ВПР.

Перед «перетяжкой», в таблицу «Заказ поставщику» нужно добавить два «сравнительных» столбца:

«Кол/во в счёте» и «Цена в счёте»

После добавления столбцов, нужно перетянуть соответствующие данные при помощи ВПР:

Обратите внимание, я закрепил диапазоны ячеек.

Теперь когда данные перенесены, нужно их сравнить, для это необходимо добавить еще два столбца (Разница 1 и Разница 2):

В столбце «Разница 1» нужно вычесть от исходного количества (D4) количество в счёте (E4).

В столбце «Разница 2» нужно вычесть от исходной цены (G4) цену в счёте (H4).

Таким образом мы сможем увидеть разницу и в количестве и в цене.

Если значение «0», то значит всё хорошо и данные одинаковые.

Если значение плюсовое (например «+3»), то это значит что в счёте не хватает 3 штук.

Если значение отрицательное, это значит, что нам пытаются «впихнуть» лишнее.

Если значение #Н/Д — это значит, что в счёте вообще нет такой позиции.

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

Таким образом нужно просканировать всю таблицу и выяснить о причинах расхождения у поставщика:

Друзья, вот так мы проверили насколько соответствует Заказ, полученному Счёту и казалось бы что это всё что необходимо для счастливой жизни. Однако это не совсем так.

Нужно еще проверить соответствие Счёта, отправленному заказу, на предмет лишних позиций.

Вдруг хитрый поставщик, среди сотни позиций решил нам скрытно что-то «допродать».

Для этого в «Счёт на оплату» нужно добавить столбец «Кол/во в заказе» и «отвепээрить» туда значения из столбца «Количество» Заказа поставщику.

И если в столбце «Количество в заказе» мы вдруг увидим значения #Н/Д это значит, что позиции с таким наименование не было в нашем заказе поставщику.

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

Теперь всё тоже самое продемонстрирую в небольшом видео.

Источник

Если вы сталкиваетесь в Excel и с другими ошибками, рекомендую прочитать наши статьи:

Как исправить ошибку #ССЫЛКА! в Excel
Как исправить ошибку #ЗНАЧ в Excel
Как исправить ошибку #Н/Д в Excel
Как исправить ошибку #ДЕЛ/0! в Excel
Как убрать ошибку #### в Excel

Спасибо за внимание.

Источник

Содержание

Как исправить ошибку #ПЕРЕНОС! в Excel
Как исправить ошибку #ЗНАЧ! в Excel
Как исправить ошибку #ПУСТО! в Excel
Как исправить ошибку #ИМЯ? в Excel

Как исправить ошибку #ПЕРЕНОС! в Excel

Диапазон переноса и значения внутри будут изменяться при обновлении источника данных.

У ошибки может быть множество причин. Поиск решения будет зависеть от используемой версии Excel.

Веб-версия: наведите мышь на зеленый треугольник в верхнем левом углу ячейки с ошибкой #ПЕРЕНОС!. Появится сообщение с описанием ошибки.
Десктопная версия: щелкните по ячейке с ошибкой #ПЕРЕНОС!. Нажмите на треугольник, который появится слева от ячейки. Причина ошибки будет указана в верхней части меню справки.

Рассмотрим решения, которые подойдут для наиболее распространенных вариаций этой ошибки.

Диапазон для переноса содержит одну или более ячеек с значениями

Решение: очистить диапазон для переноса.

Диапазон для переноса находится внутри таблицы

Решение 1: преобразовать таблицу в диапазон данных.

Для этого выполните следующие шаги:

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

Обратите внимание, что расположение меню и списка команд могут отличаться в зависимости от версии.

Решение 2: переместить формулу за границы таблицы.

Диапазон для переноса содержит объединенные ячейки

Решение: разделить ячейки внутри диапазона.

Как исправить ошибку #ЗНАЧ! в Excel

Ошибка #ЗНАЧ! (#VALUE) возникает в следующих случаях:

что-то не так с ячейкой (ячейками), на которую ссылается формула,
что-то не так с самой формулой.

Иногда найти источник проблемы не просто. Ниже — о самых распространенных случаях и как и их исправить.

Математическая формула ссылается на текст

Например, на примере ниже функция =СУММ(B2,B11) будет игнорировать текст в ячейке B11.

Скриншот: Zapier.com

Одна или более ячеек содержат пробелы

В этих случаях ячейка выглядит пустой.

Решение: найти и заменить пробелы. Вот как это сделать:

выделите диапазон ячеек, к которому обращается формула;
нажмите на иконку с биноклем, нажмите «Найти и выделить»;
в окне «Найти и заменить» выберите «Заменить»;
в поле «Найти» вставьте пробелы;
поле «Заменить на» оставьте пустым;
нажмите «Заменить все».

Как исправить ошибку #ПУСТО! в Excel

Ячейка, на которую ссылается формула, удалена

Решение 1: Если ячейки удалены случайно, отмените это действие.

Формула содержит относительные ссылки

Например, формула =СУММ(G2:G7) с абсолютными ссылками будет выглядеть как: =СУММ($G$2:$G$7).

Как исправить ошибку #ИМЯ? в Excel

Ошибка #ИМЯ? (#NAME) возникает, если название формулы неверно написано. Рассмотрим основные решения проблемы.

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

Неверное название формулы

Решение: найти правильное имя формулы и обновить ее.

Вот как это сделать:

выберите в меню раздел «Формулы»;
нажмите на «Вставить функцию» в левой части панели инструментов, в открывшемся окне «Вставка функции» просмотрите недавно использовавшиеся функции или полный список всех доступных функций;
выберите и вставьте нужную функцию;
в редакторе формул обновите ячейки, к которым будет отсылаться формула (поля зависят от выбранной функции).

Эти советы применимы и к ошибкам в Google Таблицах (за исключением #ПЕРЕНОС!, которая часто отображается как #REF).

Источник.

Фото на обложке: Aihr.com

Подписывайтесь на наш Telegram-канал, чтобы быть в курсе последних новостей и событий!

Источник

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

Содержание

Исправляем ошибку #Н/Д функции ВПР в Excel
1. Искомое значение написано с опечаткой
2. Ошибка #Н/Д при поиске приближённого совпадения с ВПР
3. Ошибка #Н/Д при поиске точного совпадения с ВПР
4. Столбец поиска не является крайним левым
5. Числа форматированы как текст
6. В начале или в конце стоит пробел
Ошибка #ЗНАЧ! в формулах с ВПР
1. Искомое значение длиннее 255 символов
2. Не указан полный путь к рабочей книге для поиска
3. Аргумент Номер_столбца меньше 1
Ошибка #ИМЯ? в ВПР
ВПР не работает (ограничения, оговорки и решения)
1. ВПР не чувствительна к регистру
2. ВПР возвращает первое найденное значение
3. В таблицу был добавлен или удалён столбец
4. Ссылки на ячейки исказились при копировании формулы
ВПР – работа с функциями ЕСЛИОШИБКА и ЕОШИБКА
ВПР: работа с функцией ЕСЛИОШИБКА
ВПР: работа с функцией ЕОШИБКА

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

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

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

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

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

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

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

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

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

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

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

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

=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))

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

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

=INDEX(C2:C7,MATCH(TRUE,INDEX(B2:B7=F$2,0),0))
=ИНДЕКС(C2:C7;ПОИСКПОЗ(ИСТИНА;ИНДЕКС(B2:B7=F$2;0);0))

2. Не указан полный путь к рабочей книге для поиска

Вот полная структура функции ВПР для поиска в другой книге:

Настоящая формула может выглядеть так:

=VLOOKUP($A$2,'[New Prices.xls]Sheet1'!$B:$D,3,FALSE)
=ВПР($A$2;'[New Prices.xls]Sheet1'!$B:$D;3;ЛОЖЬ)

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

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

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

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

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

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

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

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

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

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

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

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

Синтаксис функции ЕСЛИОШИБКА (IFERROR) прост и говорит сам за себя:

IFERROR(value,value_if_error)
ЕСЛИОШИБКА(значение;значение_если_ошибка)

Например, вот такая формула возвращает пустую ячейку, если искомое значение не найдено:

=IFERROR(VLOOKUP($F$2,$B$2:$C$10,2,FALSE),"")
=ЕСЛИОШИБКА(ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ);"")

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

Например, формула ЕСЛИ+ЕОШИБКА+ВПР, аналогична формуле ЕСЛИОШИБКА+ВПР, показанной выше:

=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?

Давайте постараемся вместе ответим на вопрос: «Почему функция ВПР не работает?»

Все остальные случаи связаны с тем, что мы что-то не учли или сделали неверно.

Итак, почему ВПР не находит значение, хотя оно есть?

Неточность при вводе данных.
Опечатка при вводе функции.
Неверные ссылки.
Неверно указан параметр «интервальный просмотр».
Столбец поиска не является первым слева столбцом диапазона поиска.
Несовпадение форматов данных.
- Преобразуем число в текст.
- Преобразуем текст в число.
- Если числовой и текстовый форматы беспорядочно перемешаны?
Лишние пробелы и непечатаемые знаки.
Неправильно указан номер столбца.
Неверная ссылка на данные из другой таблицы
После изменения таблицы функция ВПР перестала работать
Как убрать сообщение #Н/Д в ВПР?

Неточность при вводе данных.

Это вполне может случиться, особенно если вы записываете его прямо в формулу.

Опечатка при вводе функции.

Проверьте синтаксис, и всё будет в порядке.

Неверные ссылки.

Проверьте, верно ли вы указали диапазон поиска, а также координаты значения, которое мы будем искать.

Неверно указан параметр «интервальный просмотр».

Об этой проблеме мы подробно говорили ранее.

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

Это важное ограничение нельзя забывать.

Несовпадение форматов данных.

Преобразуем число в текст.

Используем формулу

=ВПР(ТЕКСТ(D6;»#»);$A$3:$B$21;2;ЛОЖЬ)

Как видно, обычную ссылку на D6 мы заменяем конструкцией

TEXT(D6,»#»)

Можно поступить и проще — «приклеить» к числу пустую строку при помощи оператора склейки &.

=ВПР(D6&»»;$A$3:$B$21;2;ЛОЖЬ)

Запомните простое правило:

Как видите, ошибка исчезла, поскольку теперь текстовое значение сравнивается с таким же текстом.

Преобразуем текст в число.

Так же и в нашем случае. Текст преобразуется в число. Итак, наша формула выглядит следующим образом:

=ВПР(—A2;$D$2:$E$8;2;0)

Если числовой и текстовый форматы беспорядочно перемешаны?

А теперь — самый сложный случай. И здесь тоже есть простое решение.

=ЕСЛИОШИБКА(ВПР(—O2;$R$2:$S$8;2;0);ВПР(O2&»»;$R$2:$S$8;2;0))

Лишние пробелы и непечатаемые знаки.

=ВПР(D8;$A$3:$B$21;2;ЛОЖЬ)

вводим

=ВПР(СЖПРОБЕЛЫ(ПЕЧСИМВ(D8));$A$3:$B$21;2;ЛОЖЬ)

или

=VLOOKUP(TRIM(CLEAN(D8)),$A$3:$B$21,2,FALSE)

Функция СЖПРОБЕЛЫ убирает пробелы, а ПЕЧСИМВ удаляет все непечатаемые и невидимые символы.

Неправильно указан номер столбца.

Неверная ссылка на данные из другой таблицы

Если вы случайно допустили неточность при указании ссылки на эти данные, то увидите сообщение #ССЫЛКА!.

Это будет означать, что вы ссылаетесь на неверный диапазон, который Excel найти не может.

После изменения таблицы функция ВПР перестала работать

Теперь же появилась ошибка #Н/Д либо результаты стали явно неверны. В чем дело?

Постарайтесь отменить сделанные изменения, благо Excel хранит всю историю изменений вашей таблицы.

Как убрать сообщение #Н/Д в ВПР?

=ЕСЛИОШИБКА([выражение];[значение_если_ошибка])

Задача – заменить его числом или выражением, указанным во втором аргументе. Рассмотрим это на примере.

В нашем случае маракуйя отсутствует в прайсе. Но вместо #Н/Д мы выведем ноль.

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

=ЕСЛИОШИБКА(ВПР(A4;$D$2:$E$7;2;0);0)

Надеемся, теперь вы сможете ответить на вопрос: «Почему не работает функция ВПР в Excel?».

[the_ad_group id=»48″]

Еще полезные примеры:

Поиск ВПР нескольких значений по нескольким условиям — В статье показаны способы поиска (ВПР) нескольких значений в Excel на основе одного или нескольких условий и возврата нескольких результатов в столбце, строке или в отдельной ячейке. При использовании Microsoft…

Функция ЕСЛИОШИБКА – примеры формул — В статье описано, как использовать функцию ЕСЛИОШИБКА в Excel для обнаружения ошибок и замены их пустой ячейкой, другим значением или определённым сообщением. Покажем примеры, как использовать функцию ЕСЛИОШИБКА с функциями визуального…

Как объединить две или несколько таблиц в Excel — В этом руководстве вы найдете некоторые приемы объединения таблиц Excel путем сопоставления данных в одном или нескольких столбцах. Как часто при анализе в Excel вся необходимая информация собирается на одном…

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

4 способа, как сделать левый ВПР в Excel. — Функция ВПР – одна из самых популярных, когда нужно найти и извлечь из таблицы какие-либо данные. Но при этом она имеет один существенный недостаток. Поиск она производит в крайнем левом…

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

Источник

svetazag

Пользователь

Сообщений: 5
Регистрация: 29.08.2013

Добрый день.
При попытке с помощью функции ВПР подгрузить в одну книгу по искомому значению данные из другой книги, возникает следующая проблема.
В мастере функций в аргумент Таблица не подтягивается выделенный диапазон из другой книги.
В первый раз сталкиваюсь с данной проблемой.
Во вложении скриншот, где в одной книге Excel в ячейку столбца Остаток введена функция ВПР и в мастере функций в Аргументе Искомое значение указана ячейка RC[-3], но при переходе в Аргумент Таблица курсором мыши и выделении диапазона в другой книге, по скрину видно, что Аргумент Таблица остается пустым.
То есть функция ВПР не воспринимает диапазон из другой книги.
Пожалуйста, подскажите, в чем может быть причина.

Источник

В этом руководстве мы рассмотрим, как использовать ЕОШИБКУ с ВПР в Excel для продуктивной обработки всех видов ошибок.

ВПР — одна из самых запутанных функций Excel, из-за которой возникает множество проблем. В какой бы таблице вы ни искали, ошибки #N/A являются обычным явлением, а #NAME и #VALUE также появляются время от времени. Использование функции ВПР с ЕОШИБКА может помочь вам обнаружить все возможные ошибки и обработать их наиболее подходящим для вашей ситуации способом.

Почему ВПР выдает ошибку?

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

Значение поиска не существует в массиве поиска.
Искомое значение написано с ошибкой.
В искомом значении или столбце поиска есть начальные или конечные пробелы.
Столбец подстановки не является самым левым столбцом массива таблиц.

Кроме того, вы можете нарваться на #ЗНАЧ! ошибка, например, когда искомое значение содержит более 255 символов. Если в имени функции есть орфографическая ошибка, #NAME? появится ошибка.

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

ЕСЛИ ОШИБКА Формула VLOOKUP для замены ошибок пользовательским текстом

Чтобы скрыть все возможные ошибки, которые могут быть вызваны функцией ВПР, вы можете поместить ее в формулу ЕСЛИ ОШИБКА следующим образом:

ЕСЛИ(ОШИБКА(ВПР(…)), «text_if_error«, ВПР(…))

В качестве примера вытянем названия предметов, по которым ученики группы А провалили тесты:

=ВПР(A3, $D$3:$E$9, 2, ЛОЖЬ)

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

На самом деле эти ошибки просто указывают на то, что некоторые значения поиска (A3:A14) не найдены в списке поиска (D3:D9). Чтобы четко передать эту мысль, вложите формулу ВПР в конструкцию ЕСЛИ ОШИБКА:

=ЕСЛИ(ЕОШИБКА(ВПР(A3, $D$3:$E$9, 2, ЛОЖЬ)), «Нет», ВПР(A3, $D$3:$E$9, 2, ЛОЖЬ))

Это уловит ошибки и вернет ваше собственное текстовое сообщение:
ЕСЛИ ОШИБКА формула ВПР

Советы и примечания:

Основное преимущество этой формулы в том, что она прекрасно работает во всех версиях Excel 2000 по Excel 365. В современных версиях проще и компактнее альтернативы доступны.
Функция ЕОШИБКА перехватывает абсолютно все ошибки, такие как #Н/Д, #ИМЯ, #ЗНАЧ и т. д. ЕСЛИ ВПР ЕСЛИ ЕСТЬ (во всех версиях) или ЕСЛИ ВПР (в Excel 2013 и более поздних версиях).

ISERROR VLOOKUP для возврата пустой ячейки в случае ошибки

Чтобы иметь пустую ячейку при возникновении ошибки, заставьте формулу возвращать пустую строку («») вместо пользовательского текста:

ЕСЛИ(ЕОШИБКА(ВПР(…)), «», ВПР(…))

В нашем случае формула принимает такой вид:

=ЕСЛИ(ЕОШИБКА(ВПР(A3, $D$3:$E$9, 2, ЛОЖЬ)), «», ВПР(A3, $D$3:$E$9, 2, ЛОЖЬ))

Результат именно такой, как и ожидалось — пустая ячейка, если имя учащегося не найдено в таблице поиска.
Формула IERROR VLOOKUP для возврата пустой ячейки в случае ошибки

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

ЕСЛИ ОШИБКА ВПР Формула Да/Нет

В какой-то ситуации вы можете что-то искать, но вместо того, чтобы тянуть спички, просто хотите вернуться Да (или другой текст, если искомое значение найдено) и Нет (если искомое значение не найдено). Чтобы это сделать, вы можете использовать эту общую формулу:

ЕСЛИ(ОШИБКА(ВПР(…)), «text_if_not_found«, «text_if_found«)

Предположим, что в нашем образце данных вы хотите узнать, какие учащиеся провалили тест, а какие нет. Для этого подайте уже знакомую формулу ВПР ЕОШИБКА для логической проверки ЕСЛИ и скажите ей выводить «Нет», если значение не найдено (ВПР ЕОШИБКА возвращает ИСТИНА), и «Да», если найдено (ВПР ЕОШИБКА возвращает ЛОЖЬ):

=ЕСЛИ(ЕОШИБКА(ВПР(A3, $D$3:$E$9, 2, ЛОЖЬ)), «Нет», «Да»)
ЕСЛИ ОШИБКА ВПР Формула Да/Нет

IERROR VLOOKUP альтернативы

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

ЕСЛИ ОШИБКА ВПР

Доступно в Excel 2007 и выше

Начиная с версии 2007, в Excel есть специальная функция ЕСЛИОШИБКА, которая проверяет формулу на наличие ошибок и возвращает собственный текст (или запускает альтернативную формулу) при обнаружении какой-либо ошибки.

ЕСЛИОШИБКА(ВПР(…), «text_if_error«)

Реальная формула выглядит следующим образом:

=ЕСЛИОШИБКА(ВПР(A3, $D$3:$E$9, 2, ЛОЖЬ), «Нет»)
ЕСЛИ ОШИБКА формула ВПР

На первый взгляд это выглядит как укороченный аналог формулы ЕСЛИ ОШИБКА ВПР. Однако есть существенное отличие:

ЕСЛИОШИБКА ВПР предполагает, что вам всегда нужен результат ВПР, если это не ошибка.
ЕСЛИ ОШИБКА VLOOKUP позволяет указать, что возвращать, если ошибка и что, если ошибки нет.

Дополнительные сведения см. в разделе Использование ЕСЛИОШИБКА с функцией ВПР в Excel.

ЕСЛИ ISNA ВПР

Работает в Excel 2000 и более поздних версиях

В ситуации, когда вы хотите перехватывать только #N/A, не перехватывая никаких других ошибок, вам пригодится функция ISNA. Синтаксис такой же, как у IF IERROR VLOOKUP:

ЕСЛИ(ИСНА(ВПР(…)), «text_if_error«, ВПР(…))

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

=ЕСЛИ(ИСНА(ВПР(A3, $D$3:$E$9, 2, ЛОЖЬ)), «Нет», ВПР(A3, $D$3:$E$9, 2, ЛОЖЬ))

На изображении ниже ячейка A13 содержит много пробелов в конце, из-за чего общая длина искомого значения превышает 255 символов. В результате формула вызывает ошибку #ЗНАЧ! ошибка, привлекая ваше внимание к этой ячейке и призывая разобраться в причинах. IERROR VLOOKUP в этом случае вернет «Нет», что только скроет проблему и даст абсолютно неверный результат.
ЕСЛИ ISNA формула ВПР

Когда использовать:

Эта формула прекрасно работает в ситуации, когда вы хотите отобразить некоторый текст только тогда, когда значение поиска не найдено, и не хотите маскировать основные проблемы с самой формулой ВПР, например, когда имя функции введено с ошибкой (#ИМЯ?) или не указан полный путь к книге поиска (#ЗНАЧ!).

Для получения дополнительной информации см. функцию ISNA в Excel с примерами формул.

IFNA VLOOKUP

Доступно в Excel 2013 и выше

Это современная замена комбинации IF ISNA, которая упрощает обработку ошибок #N/A.

ЕСЛИНА(ВПР(…), «text_if_error«)

Вот сокращенный эквивалент нашей формулы IF ISNA VLOOKUP:

=ЕСЛИНА(ВПР(A3, $D$3:$E$9, 2, ЛОЖЬ), «Нет»)
Формула IFNA VLOOKUP

Когда использовать:

Это идеальное решение для перехвата и обработки ошибок #N/A в современных версиях Excel (2013–365).

Для получения полной информации см. Функция Excel IFNA.

XLOOKUP

Поддерживается в Excel 2021 и Excel 365.

Благодаря встроенной функции «если ошибка» функция XLOOKUP — это самый простой способ поиска без ошибок #N/A в Excel. Просто введите удобный для пользователя текст в необязательный 4-й аргумент с именем если_не_найдено.

Например:

=XLOOKUP(A3, $D$3:$D$9, $E$3:$E$9, «Для»)
Формула XLOOKUP для поиска без ошибок #N/A

Ограничение: он перехватывает только ошибки #N/A, игнорируя другие типы.

Для получения дополнительной информации ознакомьтесь с функцией XLOOKUP в Excel.

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

Доступные загрузки

IERROR с примерами VLOOKUP (файл .xlsx)

Вас также могут заинтересовать

Источник

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Источник