Отслеживание и исправление ошибок в функции ВПР в Google Таблицах
Как сделать vlookup без ошибок в Google Sheets? Рассмотрим подробнее наиболее распространенные ошибки, допускаемые при работе с функцией ВПР.
Google Sheets VLOOKUP — одна из самых полезных, но сложных функций. Это помогает находить совпадающие данные на нескольких листах. Однако бывают случаи, когда ваша формула может возвращать такие ошибки, как #Н/Д, #ИМЯ?, #ЦЕННОСТЬ!или же #ССЫЛКА!.
Давайте попробуем вместе ответить на следующий вопрос: «Почему моя функция ВПР в Google Таблицах не работает?»
Наиболее распространенные ошибки ВПР
Первая причина в том, что в указанном вами диапазоне просто нет совпадений. Вы здесь бессильны и не можете повлиять на результат.
Другие случаи означают, что вы где-то ошиблись в формуле. Давайте подробнее рассмотрим, что могло пойти не так.
Неправильные разделители
Разделители — это те символы, которые используются в формулах в качестве разделителей. Например, если вы находитесь в Великобритании или США, скорее всего, ваши формулы требуют использования запятых в качестве разделителей:
=ВПР(A2,$F$2:$G$10,2,ЛОЖЬ)
И выбор разделителя контролируется вашим языковым стандартом электронной таблицы. Разные локали требуют разных разделителей.
Чтобы проверить свою локаль в Google Таблицах, перейдите по ссылке Файл > Настройки. Вы увидите локаль под Общий вкладка:
Чтобы понять, какие разделители нужны вашей функции ВПР в зависимости от вашего региона, просто начните вводить функцию. Google Sheets покажет вам, как именно должна выглядеть ваша формула и какие разделители нужно использовать:
Если все разделители указаны правильно, но функция ВПР по-прежнему выдает ошибки, проверьте другие возможные проблемы и их решения ниже.
Неверные данные – ошибка #Н/Д
Формула ничего не находит и возвращает #Н/Д ошибка? Но ты уверен, что должно быть совпадение?
Самое простое решение здесь самое лучшее — скорее всего, вы неправильно ввели данные в формулу. Это может произойти, особенно если вы вводите формулу непосредственно в строку формул, а не в ячейку.
Исправьте эту ошибку #Н/Д
Дважды проверьте все аргументы, убедитесь, что все ссылки на ячейки и значения верны.
Или же добавить свой собственный текст когда действительно нет совпадений.
Неверное название функции — #ИМЯ? ошибка
Если вы видите #ИМЯ? ошибка, вы либо неправильно написали имя функции (пропустили или написали с ошибкой букву или две), либо перепутали с синтаксисом.
Исправить #ИМЯ? ошибка
Проверьте имя функции и синтаксис ВПР. Исправьте все опечатки, и все будет работать.
Неверные ссылки на ячейки ВПР — ошибка #Н/Д
Формула работала нормально, пока вы не добавили несколько строк или столбцов в таблицу и #Н/Д ошибка появилась на ровном месте?
Исправьте эту ошибку #Н/Д
Проверьте, правильно ли введены диапазон поиска и ячейка с искомым значением — они не написаны с ошибками и не отсутствуют на листе.
Примечание. Если вы используете относительные ссылки на ячейки (например, A1) вместо абсолютных (например, $A$1), а затем изменяете таблицу (например, добавляете столбец), данные сместятся, ссылки изменятся, и формула будет ссылаться на неправильные ячейки. :
Я добавил столбец «ID». Столбец «Цена» больше не входит в диапазон, поэтому цену найти невозможно.
Если это произойдет, просто Отменить внесите изменения в таблицу и исправьте ссылки.
Кончик. Вы можете использовать Формулы инструмент из Электроинструменты надстройка для преобразования всех относительных ссылок в абсолютные за пару кликов:
Неверный аргумент «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 удаляет все непечатаемые символы.
Кончик. Вы всегда можете использовать эту комбинацию функций «на всякий случай», чтобы избежать ошибок.
Исправьте эту ошибку #Н/Д — используйте специальный инструмент для обрезки пробелов
Или используйте Удалять инструмент из Электроинструменты чтобы удалить все нежелательные символы сразу:
Замените ошибки ВПР своим текстом
Я думаю, вы согласитесь, что ошибки не делают вашу таблицу красивой. Есть ли способ избавиться от них?
Ну да, но сначала не помешает перепроверить, правильно ли построена ваша формула ВПР в Google Таблицах:
- Начните с диапазона поиска. Вы правильно выразились? Отображается ли ваше значение в крайних левых столбцах этого диапазона?
- Убедитесь, что «is_sorted» аргумент правильный: ИСТИНА или ЛОЖЬ, в зависимости от нужного вам совпадения и сортировки вашего диапазона.
- Проверьте все возможные несоответствия числовых форматов и лишние пробелы или непечатаемые символы.
Если все выглядит нормально, но формула не возвращает значение, добавьте туда функцию ЕСЛИОШИБКА:
=ЕСЛИОШИБКА(ВПР(D9,$A$3:$B$21,2,ЛОЖЬ),»Нет совпадений»)
Синтаксис IFERROR легко понять:
- ценность – что-то проверяем на ошибки. Если все в порядке, возвращается результат нашего расчета.
- [value_if_error] – что-то, что возвращает функция, если первый аргумент перехватывает ошибку.
В моем случае F9 показывает «Не совпадает«, потому что функция ВПР не может найти совпадений.
Вот как вы проверяете свою функцию ВПР на наличие ошибок в Google Таблицах. Если вы перепроверили все это, но ваша формула по-прежнему не работает, сообщите нам об этом в разделах комментариев ниже.
