Почему иногда не работает функция ВПР в Excel
Функция ВПР или VLOOKUP — один из самых эффективных поисковых инструментов в программе MS Excel. С его помощью можно выполнить вертикальный поиск значения в выделенном диапазоне или в целой таблице. При использовании данной функции можно столкнуться с ошибками, которые чаще всего связаны с некорректным указанием аргументов. В данном материале рассмотрим, почему ВПР не работает в Эксель, причины появления ошибки «#Н/Д» и основные варианты решения данной проблемы.
Общие сведения о функции
Чтобы разобраться, почему не работает функция ВПР в программе, рассмотрим ее главные особенности. Данный инструмент используется для поиска значения по строкам. При записи функции в ячейке указывается несколько аргументов:
- Искомое значение — то, что необходимо найти;
- Диапазон — ячейки, в которых осуществляется поиск;
- Номер столбца — колонка, в которой ищется возвращаемое значение;
- Интервальный просмотр — определяет поиск точного или приблизительного совпадения.
Главная причина, по которой не работает функция ВПР в Excel — неправильно заданные аргументы. Реже ошибки возникают из-за опечаток или специфики форматирования таблицы. Далее рассмотрим все причины более детально и определим самые простые и быстрые способы их решения.
Ошибки при использовании функции
Неопытным пользователям может быть довольно сложно разобраться с синтаксисом функции ВПР в Эксель. Еще сложнее обнаружить ошибки, которые могли быть допущены при использовании данной формулы. Программа не может автоматически обнаружить причину, из-за которой результат построчного поиска получается некорректным. Поэтому придется искать ошибки самостоятельно. В этом помогут простые примеры, рассмотренные далее.
Некорректно указано искомое значение
Очевидно, что результат поиска будет отличаться от ожидаемого в том случае, если искомого значения в таблице попросту не существует. В этом легко убедиться на примере. В нашем случае это таблица с перечнем товаров и их стоимостью. Попробуем отыскать значение, которого нет в столбце с наименованиями.
В результате выполнения данной функции программа подтягивает поиск под заданные параметры и в конечном итоге все равно выдается ошибка «Нет данных». Это объясняется, во-первых, тем, что в таблице нет позиции, совпадающей с искомым значением. Во-вторых, для аргумента «Интервальный просмотр» указано значение «Ложь». Это означает, что программа осуществляет поиск точного совпадения.
Оптимальное решение данной проблемы — убрать необязательный параметр «Интервальный просмотр» или поменять значение на «Правда». Тогда программа найдет неточное совпадение, а ошибка «#Н/Д» не будет отображаться. Однако здесь также следует учитывать, что, если искомого значения в таблице нет, будет найдено приблизительное значение, что не всегда целесообразно.
Отметим, что еще одна причина неправильной работы функции может заключаться в наличии лишних пробелов или других знаков в ячейках со значениями. По этой причине рекомендуется искать неточные соответствия, так как подобные ошибки будут игнорироваться программой.
Неправильный диапазон
Основные аргументы функции ВПР — искомое значение и диапазон поиска. Значение, которое ищется, должно находиться в левом столбце указанного диапазона. В ином случае возникнет ошибка «Нет данных». Для примера, попробуем выполнить поиск цены для позиции «Абрикос». Как видно, наименования товаров записаны в 3 столбце, тогда как стоимость и категория находятся в колонках слева.
Так как формула ВПР в Excel ищет значения слева направо, а не наоборот, возникнет ошибка. Такое чаще всего случается в тех случаях, когда документ содержит большое количество столбцов.
В данном случае необходимо заменить первый и третий столбец местами. Тогда искомое значение будет находиться в левой части диапазона поиска. В результате записанная конструкция с формулой ВПР сработает корректно. В ячейке будет отображена стоимость искомого товара.
Неправильно указан столбец
При неправильном указании аргументов в ВПР не всегда возникает ошибка «#Н/Д». Бывает и так, что формула срабатывает, но при этом ищет не то что нужно. Чаще всего это связано с тем, что неправильно указан вводный столбец, то есть тот, в котором ищется значение.
Для примера, в нашей таблице необходимо отыскать стоимость товара с наименованием «Банан». Используя функцию ВПР, в качестве третьего аргумента указываем номер столбца, в котором ищется значение — колонка C. В результате вместо стоимости получаем значение из столбика «Категория».
Сама функция при этом сработала правильно. Ошибка заключается только в том, что вместо столбца, в котором перечислены цены, в качестве аргумента был указан другой — с категориями. Такая опечатка довольно распространенное явление, из-за которого формула не находит нужные значения. Достаточно заменить номер колонки в списке аргументов, чтобы получить необходимый результат.
Искомое значение меньше минимального в диапазоне
Подобная ошибка может возникнуть при поиске ячеек с числовыми значениями. Для примера, попробуем в таблице отыскать товар, стоимость которого составляет 9.99.
В результате выполнения функция не находит нужное наименование, а в ячейке с записью остается ошибка «Нет данных». Это связано с тем, что цифра, которая указана в ячейке, слишком маленькая — она меньше минимальной в столбце, указанной в аргументах. По этой причине Excel не может выполнить поиск значения корректно.
Изменение размеров таблицы
Почему функция ВПР не работает в Excel или она отображает некорректный результат, может быть связан с тем, что сбился сам диапазон из-за расширения таблицы. При смещении ячеек искомое значение может менять свое положение. Подобным образом может изменяться расположение колонки, по которой ведется поиск.
Если в качестве искомого значения указывается ссылка, ее необходимо зафиксировать, используя символы «$». Также, если после применения формулы таблица была изменена, рекомендуется переписать конструкцию, добавив расширенный диапазон. То же самое следует делать при изменении содержания ячеек или смене форматов, чтобы исключить риск возникновения ошибок.
Повторение записей
Определив выше, почему не работает формула ВПР, рассмотрим еще одну ситуацию, при которой формула может давать некорректный результат. Функция по умолчанию будет искать только одно значение, которое соответствует заданным поисковым условиям. Однако бывает так, что в таблице есть несколько ячеек, значения которых одинаковы. В данном случае формула вернет результат для последнего соответствия, в то время как остальные будут игнорироваться.
Для примера, добавим еще один товар «Абрикос» в таблицу. Таким образом в столбце «Наименование» появится дубликат с другой стоимостью. Применяя формулу ВПР для определения цены, программа будет отображать найденную во второй строчке величину. Дубликат, который есть выше, будет просто проигнорирован программой.
В том случае, если дублируются целые строки, их можно убрать. Для этого используется инструмент «Удалить дубликаты», расположенный во вкладке «Данные».
Несоответствие форматов
Ошибка может возникать тогда, когда формат записи искомого значения и ячейки в заданном диапазоне не соответствуют друг другу. Чаще всего такое происходит при работе с числовыми данными и датами, а также при использовании специальных символов, которые не интерпретируются программой.
В таких случаях возникает ошибка «Нет данных». Также при неправильной записи аргумента «Искомое значение» может возникать ошибка «Имя?». Она указывает на то, что параметр прописан неверно.
В данном примере причина появления такой ошибки заключается в том, что искомое значение записано без кавычек. Поэтому программа не интерпретирует данный аргумент в качестве текста. В результате несоответствия форматов функция не срабатывает.
Необходимо следить за тем, чтобы числа, записанные в таблице, были отформатированы должным образом. Иногда при заполнении ячеек пользователи форматируют весь документ как текст, в результате чего некоторые функции не работают. Поэтому при использовании чисел необходимо выставлять числовой формат данных, чтобы вычислительные операции работали исправно.
Заключение
Функция ВПР в Excel — удобный инструмент, который выполняет построчный поиск значений в заданном диапазоне. Однако несмотря на множество преимуществ, данная формула не всегда работает корректно. Чаще всего это связано с неправильным указанием аргументов, некорректным форматирование таблицы или наличием ошибок в самих ячейках. Выяснив в статье, почему не работает формула ВПР, можно беспрепятственно устранить причину и получить корректный результат поиска.
Загрузка...
