Отслеживание и исправление ошибок в функции ВПР в Google Таблицах

Как сделать vlookup без ошибок в Google Sheets? Рассмотрим подробнее наиболее распространенные ошибки, допускаемые при работе с функцией ВПР.

Google Sheets VLOOKUP — одна из самых полезных, но сложных функций. Это помогает находить совпадающие данные на нескольких листах. Однако бывают случаи, когда ваша формула может возвращать такие ошибки, как #Н/Д, #ИМЯ?, #ЦЕННОСТЬ!или же #ССЫЛКА!.

Давайте попробуем вместе ответить на следующий вопрос: «Почему моя функция ВПР в Google Таблицах не работает?»

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

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

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

Неправильные разделители

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

=ВПР(A2,$F$2:$G$10,2,ЛОЖЬ)

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

Чтобы проверить свою локаль в Google Таблицах, перейдите по ссылке Файл > Настройки. Вы увидите локаль под Общий вкладка:
Проверьте региональные настройки электронных таблиц.

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

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

Неверные данные – ошибка #Н/Д

Формула ничего не находит и возвращает #Н/Д ошибка? Но ты уверен, что должно быть совпадение?

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

Исправьте эту ошибку #Н/Д

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

Или же добавить свой собственный текст когда действительно нет совпадений.

Неверное название функции — #ИМЯ? ошибка

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

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

Проверьте имя функции и синтаксис ВПР. Исправьте все опечатки, и все будет работать.

Неверные ссылки на ячейки ВПР — ошибка #Н/Д

Формула работала нормально, пока вы не добавили несколько строк или столбцов в таблицу и #Н/Д ошибка появилась на ровном месте?

Исправьте эту ошибку #Н/Д

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

Примечание. Если вы используете относительные ссылки на ячейки (например, A1) вместо абсолютных (например, $A$1), а затем изменяете таблицу (например, добавляете столбец), данные сместятся, ссылки изменятся, и формула будет ссылаться на неправильные ячейки. :

Относительные ссылки на ячейки извлекают неверные значения.

Я добавил столбец «ID». Столбец «Цена» больше не входит в диапазон, поэтому цену найти невозможно.

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

Кончик. Вы можете использовать Формулы инструмент из Электроинструменты надстройка для преобразования всех относительных ссылок в абсолютные за пару кликов:
Преобразуйте относительные значения в абсолютные значения с помощью Power Tools.

Неверный аргумент “is_sorted”

Ваша формула ВПР Google Таблиц настроена на возврат приблизительного совпадения (ИСТИНА), а столбец подстановки не отсортирован.

Чтобы исправить это, либо отсортируйте столбец поиска от A до Z, либо измените TRUE на FALSE.

Неверный диапазон поиска – ошибка #Н/Д

Возможно, вы забыли, что функция ВПР в Google Таблицах всегда выполняет поиск в первом (крайнем левом) столбце диапазона поиска. Если значения для search_key аргумент находится во втором или каком-либо другом столбце, функция ВПР не будет работать и вернет #Н/Д ошибка:
Обязательно укажите правильный диапазон поиска.

Исправьте эту ошибку #Н/Д

Примечание. Помните: ВПР не может смотреть слева.

Имея это в виду, скорректируйте свою таблицу или формулу. Или используйте вместо этого ИНДЕКС/ПОИСКПОЗ.

Неверный номер столбца – #ЗНАЧ! ошибка

Иногда третий аргумент Google Sheets VLOOKUP указывается неправильно.

Оно не может быть меньше 1 и больше общего количества столбцов в диапазоне поиска. Если число неверно, функция ВПР в Google Таблицах вернет #ЦЕННОСТЬ! ошибка.

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

Исправьте #ЗНАЧ! ошибка

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

Недопустимая ссылка на другую таблицу — #REF! ошибка

Диапазон поиска может быть в другой таблице или даже в другой электронной таблице Google.

Вы заметите, что что-то не так, если увидите #ССЫЛКА! ошибка.

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

Неверный формат номера — ошибка #Н/Д

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

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

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

Исправьте эту ошибку #Н/Д — измените формат

Если есть только одна подобная ошибка, вы можете быстро исправить ее, изменив числовой формат ячейки. Выберите ячейку и перейдите к Формат > Число > Обычный текст в меню Google Таблиц. Его содержимое будет изменено на текст:
Измените числовой формат ячейки.

Сделайте это, и ошибка исчезнет, ​​так как вы будете искать текстовую строку среди других текстовых строк.

Исправьте эту ошибку #Н/Д – используйте функцию ТЕКСТ

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

Я использую следующую формулу:

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

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

Я заменил обычную ссылку на ячейку D6 другой функцией:

ТЕКСТ(D6,”#”)

Аргументы функции ТЕКСТ очень просты:

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

Лишние пробелы и непечатаемые символы — ошибка #Н/Д

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

В моем примере Google Sheets VLOOKUP не работает, потому что в D4 случайно введены два конечных пробела. А так как функция сравнивает символы, поиск не проходит:
Завершающие пробелы заставляют функцию возвращать ошибку.

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

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

Исправьте эту ошибку #N/A – используйте функции TRIM и CLEAN

Функции TRIM и CLEAN, предназначенные для текстовых строк, помогут удалить нежелательные невидимые символы:

=VLOOKUP(TRIM(CLEAN(D4)),$A$3:$B$21,2,ЛОЖЬ)

Используйте TRIM и CLEAN, чтобы удалить все лишние символы.

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

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

Исправьте эту ошибку #Н/Д — используйте специальный инструмент для обрезки пробелов

Или используйте Удалять инструмент из Электроинструменты чтобы удалить все нежелательные символы сразу:
Избавьтесь от лишних пробелов и непечатаемых символов с помощью Power Tools.

Замените ошибки ВПР своим текстом

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

Ну да, но сначала не помешает перепроверить, правильно ли построена ваша формула ВПР в Google Таблицах:

  1. Начните с диапазона поиска. Вы правильно выразились? Отображается ли ваше значение в крайних левых столбцах этого диапазона?
  2. Убедитесь, что “is_sorted” аргумент правильный: ИСТИНА или ЛОЖЬ, в зависимости от нужного вам совпадения и сортировки вашего диапазона.
  3. Проверьте все возможные несоответствия числовых форматов и лишние пробелы или непечатаемые символы.

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

=ЕСЛИОШИБКА(ВПР(D9,$A$3:$B$21,2,ЛОЖЬ),”Нет совпадений”)

Google Sheets VLOOKUP и IFERROR отлавливают возможные ошибки.

Синтаксис IFERROR легко понять:

  • ценность – что-то проверяем на ошибки. Если все в порядке, возвращается результат нашего расчета.
  • [value_if_error] – что-то, что возвращает функция, если первый аргумент перехватывает ошибку.

В моем случае F9 показывает “Не совпадает“, потому что функция ВПР не может найти совпадений.

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

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

Похожие записи

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *