В этой заметке разберем наиболее часто встречающиеся причины того, почему функция ВПР может работать неверно.
1. Незафиксированный диапазон
Пожалуй, самая часто встречающаяся ошибка - это незафиксированный диапазон в аргументах функции.
Например, у нас есть меню и на основании него нужно посчитать стоимость конкретного обеда.
Воспользуемся функцией ВПР и подставим в нее аргументы. Но при выборе диапазона забудем сделать его ссылки абсолютным с помощью клавиши F4.
В итоге первое значение подтягивается верно и если протянуть формулу по диапазону, то мы увидим ошибку только в последнем пункте заказа.
При изучении формул в итоговом чеке видно, что диапазон в каждой формуле смещается. Если искомое значение все еще находится в сместившимся диапазоне, то выводится правильная цена. Однако при поиске цены сока функция ВПР не нашла искомое значение в диапазоне и выдала ошибку.
Если бы в чеке не было сока, то ошибка бы никак себя не проявила, а всплыла бы позже, поставив пользователя в тупик - ведь раньше формула работала.
Поэтому здесь стоит запомнить следующее правило - если функцию ВПР предполагается использовать не в одной конкретной ячейке, а копировать формулу на некоторый диапазон, то обязательно фиксировать диапазон, делая его ссылки абсолютными.
2. Отсутствует искомое значение
Вторая частая проблема - искомое значение отсутствует в диапазоне.
При этом не обязательно, чтобы значение отсутствовало (1 - "компот" вместо "сока"), так сказать, физически. Достаточно, чтобы в названии присутствовали ошибки. Это может быть как грамматическая ошибка (2 - "борш" вместо "борщ"), так и лишний пробел (3 - "гречка ", пробел в конце слова), который невидим пользователю, но воспринимается Excel как отдельный символ и в итоге приводит к ошибке при работе функции ВПР.
Поэтому при появлении ошибки стоит проверить наличие искомого значения в таблице, грамматику и отсутсвие лишних пробелов.
3. Дубликаты в данных
Третья популярная проблема - это неверное значение, которе выдается функцией ВПР. Как правило, объясняется это наличием дубликатов в исходной таблице.
Дело в том, что функция ВПР производит просмотр всегда сверху вниз и останавливается при обнаружении первого совпадения. Это означает, что если у нас в исходной таблице присутствую дубликаты, то ВПР вернет значение, относящееся к первому из них, то есть к тому, который стоит выше в таблице.
Поэтому в случае некорректной работы функции ВПР стоит обратить внимание на наличие дубликатов.
Если они обнаружатся и их можно безболезненно удалить, то сделайте это. Если же важно, чтобы в исходной таблицы дубликаты сохранились, то в таком случае функция ВПР вам не подойдет и придется применить другие инструменты Excel, например, сводные таблицы.
4. Изменился диапазон
Если же функция ВПР работала корректно, но вдруг перестала, то обратите внимание на диапазон значений. Ваша таблица во время работы могла стать больше и зафиксированный ранее диапазон уже не охватывает все нужные вам данные.
Решением проблемы могут стать умные таблицы, которые автоматически подхватят вновь введенные значения и не потребуется изменять аргументы функции.
Также на диапазон может повлиять вставка новых столбцов. Это приедет к смещению данных в диапазоне и указанный в функции ВПР номер столбца будет выводить данные из другой области таблицы.
Поэтому при работе с таблицами, в которых используются формулы с функцией ВПР, нужно очень аккуратно подходить к добавлению или удалению столбцов.
5. Интервальный просмотр
Неверно заданный или не заданный вовсе аргумент интервальный_просмотр также может стать причиной неверной работы функции ВПР.
Напомню, что аргумент интервальный_просмотр определяет, какое совпадение мы хотим в итоге получить – приблизительное или точное.
Данный аргумент не обязательный, но если его не указывать, то он будет по умолчанию принят за ИСТИНУ, то есть за приблизительное совпадение.
В этом случае функция ВПР предполагает, что первый столбец диапазона отсортирован по возрастанию и просмотр будет остановлен на той строке, которая предшествует строке со значением, превышающим искомое. Если такой строки обнаружено не будет, то функция вернет значение из ячейки последней строки диапазона.
Со слов это понять сложно, поэтому давайте рассмотрим действие данного аргумента детально на примере.
Интервальный просмотр
У нас есть перечень, в котором указано наименование и объем приобретаемых товаров. Также есть прайс-лист, цены в котором зависят от объема приобретаемой партии.
Задача состоит из трех частей.
Во-первых, нужно определить, к какой партии отнесем каждую сделку - к розничной, оптовой или к крупному опту. Далее, в зависимости от выбранной партии, подставим цену и рассчитаем сумму заказа.
Во вспомогательной таблице перечислены условия, которые определяют цену товара. То есть партия в диапазоне от 1 до 10 единиц товара будет считаться розничной, от 10 до 50 оптовой, а свыше 50 крупным оптом.
Подставить эти данные в основную таблицу очень просто с помощью ВПР с интервальным просмотром равным 1. Но нам нужно будет создать дополнительный столбец с критерием, в котором укажем нижнюю границу диапазона из столбца "Количество" этой же таблицы.
Данные во вспомогательном столбце расположены по возрастанию, поэтому в формуле можно не указывать последний аргумент или сделать его равным 1.
Функция ВПР при проходе будет сравнивать значение из столбца «Количество» основной таблицы с критерием. Как только будет найдено большее значение, функция вернется к предыдущей ячейке и отсчитает указанное количество столбцов вправо, чтобы вернуть значение ячейки этого столбца. То есть функция ВПР с аргументом приблизительное совпадение будет искать ближайшее меньшее значение для искомого критерия.
В таком формате функция ВПР позволят значительно сократить формулу, избежав нескольких условий с функцией ЕСЛИ, ведь нам бы пришлось проверять, к какому интервалу относится текущее значение.
Итак, первая часть задания выполнена и теперь подставим цену. Опять же можно избежать мороки с функцией ЕСЛИ, добавив вспомогательный столбец в таблицу с критериями.
Для подстановки цены мы будем использовать расширенный прайс-лист, опираясь на наименование товара. Относительно столбца «Товар» столбец с розничными ценами будет вторым, с оптовыми третим, а с крупно-оптовыми четвертым.
Дополним таблицу с критерием этими цифрами (1, рис.ниже), а затем вставим вспомогательный столбец в основную таблицу (2) и с помощью ВПР перенесем номера столбцов в него.
Осталось лишь подтянуть цены. В качестве номера столбца в функции будем использовать ссылку на вспомогательный столбец.
Вспомогательный столбец в основной таблице можно скрыть, чтобы он не привлекал к себе внимание и рассчитаем сумму для каждого заказа.
Задача выполнена.
Согласитесь, «интервальный просмотр» - название малоговорящее и поэтому вносит путаницу в понимание работы функции ВПР. Поэтому можем этот аргумент условно назвать «сделана ли сортировка?», ведь фактически выбор этого аргумента зависит от того, отсортирован ли по возрастанию первый столбец диапазона, в котором происходит поиск, или нет. Если столбец отсортирован, то в аргументах функции указываем 1 или ИСТИНА, иначе 0 или ЛОЖЬ.
Недостатки функции ВПР
Недостатки функции ВПР вытекают из принципов ее работы.
Во-первых, функция ищет значения только в первом (крайнем левом) столбце диапазона и продолжает просмотр только вправо от этого столбца. Однако вполне вероятна ситуация, когда поиск нужно производить влево от столбца, что функция ВПР делать не умеет.
Вторым недостатком является то, что в функции задается целый неразрывный диапазон. То есть если искомое значение находится на расстоянии в 20 столбцов от первого столбца, то нам нужно выделить их все.
Если же в процессе работы какие-то промежуточные столбцы нужно будет удалить, либо наоборот добавить новые, то придется корректировать формулу, так как ВПР будет выдавать неверные значения.
У функции ВПР есть конкурент - связка функция ПОИСКПОЗ (MATCH) и ИНДЕКС (INDEX). В паре эти функции позволяют полностью заменить функцию ВПР и лишены перечисленных ранее недостатков.
И об этих функция речь пойдет в следующей заметке.
Ссылки на мои ресурсы по Excel
★ YouTube-канал по Excel и Word
★ Телеграм
