6 причин, почему ваш ВПР не работает
Функция ВПР — самая популярная функция поиска и ссылок в Excel. Он также является одним из самых сложных, и страшное сообщение об ошибке #N/A может быть обычным явлением.
В этой статье будут рассмотрены 6 наиболее распространенных причин, по которым не работает функция ВПР.
Вам нужно точное совпадение
Последний аргумент функции ВПР, известный как range_lookupспрашивает, хотите ли вы приблизительное или точное совпадение.
В большинстве случаев люди ищут конкретный продукт, заказ, сотрудника или клиента и поэтому требуют точного соответствия. При поиске уникального значения следует ввести FALSE для range_lookup аргумент.
Этот аргумент является необязательным, но если оставить его пустым, используется значение TRUE. Значение TRUE зависит от сортировки ваших данных в порядке возрастания для работы.
На изображении ниже показана функция ВПР с опущенным аргументом range_lookup и возвращенным неверным значением.
Решение
Если вы ищете уникальное значение, введите FALSE в качестве последнего аргумента. Приведенный выше ВПР следует вводить как =ВПР(H3,B3:F11,2,ЛОЖЬ).
Заблокировать ссылку на таблицу
Возможно, вы хотите использовать несколько функций ВПР для получения различной информации о записи. Если вы планируете копировать ВПР в несколько ячеек, вам нужно будет заблокировать таблицу.
На изображении ниже показан неверный ввод ВПР. Указаны неверные диапазоны ячеек для искомое_значение а также массив таблиц.
Решение
Таблица, которую функция ВПР использует для поиска и возврата информации, называется таблицей. таблица_массив. На это нужно будет сослаться абсолютно, чтобы скопировать ваш VLOOKUP.
Щелкните ссылку в формуле и нажмите клавишу F4 на клавиатуре, чтобы изменить ссылку с относительной на абсолютную. Формула должна быть введена как =VLOOKUP($H$3,$B$3:$F$11,4,FALSE).
В этом примере оба искомое_значение а также таблица_массив ссылки были сделаны абсолютными. Как правило, это может быть только таблица_массив который нуждается в блокировке.
Столбец был вставлен
Индексный номер столбца или col_index_numиспользуется функцией ВПР для ввода возвращаемой информации о записи.
Поскольку он вводится как порядковый номер, он не очень долговечен. Если в таблицу будет вставлен новый столбец, это может помешать работе функции ВПР. На изображении ниже показан такой сценарий.
Количество было в столбце 3, но после вставки нового столбца оно стало столбцом 4. Однако функция ВПР не обновлялась автоматически.
Решение 1
Одним из решений может быть защита рабочего листа, чтобы пользователи не могли вставлять столбцы. Если пользователям нужно будет это сделать, то это нежизнеспособное решение.
Решение 2
Другим вариантом было бы вставить функцию ПОИСКПОЗ в col_index_num аргумент ВПР.
Функцию ПОИСКПОЗ можно использовать для поиска и возврата нужного номера столбца. Это делает col_index_num динамический, поэтому вставленные столбцы больше не будут влиять на ВПР.
В этом примере можно ввести приведенную ниже формулу, чтобы предотвратить описанную выше проблему.
Стол стал больше
По мере добавления новых строк в таблицу может потребоваться обновление функции ВПР, чтобы обеспечить включение этих дополнительных строк. На изображении ниже показана функция ВПР, которая не проверяет всю таблицу на наличие фруктов.
Решение
Попробуйте отформатировать диапазон как таблицу (Excel 2007+) или как имя динамического диапазона. Эти методы гарантируют, что ваша функция ВПР всегда будет проверять всю таблицу.
Чтобы отформатировать диапазон как таблицу, выберите диапазон ячеек, которые вы хотите использовать для таблица_массив и нажмите «Главная» > «Форматировать как таблицу» и выберите стиль из галереи. Перейдите на вкладку «Дизайн» в разделе «Инструменты для работы с таблицами» и измените имя таблицы в соответствующем поле.
Приведенная ниже функция ВПР показывает таблицу с именем Список фруктов использовался.
ВПР не может смотреть влево
Ограничение функции ВПР состоит в том, что она не может смотреть влево. Он просматривает самый левый столбец таблицы и возвращает информацию справа.
Решение
Решение этой проблемы заключается в том, чтобы вообще не использовать функцию ВПР. Использование комбинации функций ИНДЕКС и ПОИСКПОЗ в Excel является распространенной альтернативой ВПР. Он гораздо более универсален.
В приведенном ниже примере показано, как он используется для возврата информации слева от столбца, в котором вы просматриваете.
Подробнее об использовании ИНДЕКС и ПОИСКПОЗ
Ваша таблица содержит дубликаты
Функция ВПР может возвращать только одну запись. Он вернет первую запись, которая соответствует искомому значению.
Если в вашей таблице есть дубликаты, функция ВПР не справится с этой задачей.
Решение 1
Должен ли ваш список иметь дубликаты? Если нет, подумайте об их удалении. Быстрый способ сделать это — выбрать таблицу и нажать кнопку «Удалить дубликаты» на вкладке «Данные».
Ознакомьтесь с AbleBits Duplicate Remover, чтобы получить более полный инструмент для обработки дубликатов в таблицах Excel.
Решение 2
Итак, ваш список должен иметь дубликаты. В этом случае ВПР не то, что вам нужно. Сводная таблица идеально подходит для выбора значения и вывода результатов.
В таблице ниже представлен список заказов. Допустим, вы хотите вернуть все заказы на определенный фрукт.
Сводная таблица используется для того, чтобы пользователь мог выбрать Fruit ID из фильтра отчета, после чего появляется список всех заказов.
В этой статье продемонстрировано решение 6 наиболее распространенных причин, по которым функция ВПР не работает. Вооружившись этой информацией, вы должны наслаждаться менее хлопотным будущим с этой замечательной функцией Excel.
Алан Мюррей — ИТ-тренер и основатель Компьютергага. Он предлагает онлайн-обучение и последние советы и рекомендации по Excel, Word, PowerPoint и Project.
