Использование функции ВПР для замены значений

Кому лень или нет времени читать – смотрите видео. Подробности и нюансы – в тексте ниже.

Постановка задачи

Итак, у нас есть две таблицы – таблица заказов и прайс-лист:

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

Решения

В наборе функций Excel в категории Ссылки и массивы (Поиск и ссылка) есть функция ВПР (ВПР).Данная функция ищет заданное значение (в нашем примере это слово «Яблоки») в крайнем левом столбце указанной таблицы (прайс-листа) двигаясь сверху вниз и, найдя его, отображает содержимое соседней ячейки. (23 рубля). Схематично работу этой функции можно представить так:

Для удобства дальнейшего использования функции сразу сделайте одно — дайте диапазону ячеек в прайс-листе собственное имя. Для этого выделите все ячейки прайс-листа кроме «шапки» (G3:H19), выберите в меню Вставить – Имя – Назначить (Вставить — Имя — Определить) или нажмите CTRL + F3 и введите любое имя (без пробелов), например Цена… Теперь, в будущем, вы сможете использовать это имя для ссылки на прайс-лист.

Теперь мы используем функцию ВПР… Выделите ячейку, куда она будет введена (D3) и откройте вкладку Формулы — Вставка функций (Формулы — Функция вставки)… В категории Ссылки и массивы (Поиск и ссылки) найти функцию ВПР (ВПР) и нажмите OK… Появится окно для ввода аргументов функции:

Использование функции ВПР для замены значений

Заполняем их по очереди:

  • Желаемое значение (искомое значение) – название товара, которое функция должна найти в крайнем левом столбце прайс-листа. В нашем случае это слово «Яблоки» из ячейки B3.
  • Настольные (Таблица массива) — таблица, из которой взяты желаемые значения, то есть наш прайс-лист. Для справки мы используем собственное название «Прайс», указанное ранее. Если вы не указали имя, вы можете просто выбрать таблицу, но не забудь нажать кнопку F4закрепить ссылку знаками доллара, потому что в противном случае он сдвинется вниз при копировании нашей формулы в остальные ячейки столбца D3:D30.
  • Номер_столбца (Индекс столбца) – порядковый номер (не буква!) графы прайс-листа, из которой будем брать значения цен. Первый столбец прайс-листа с наименованиями имеет номер 1, поэтому нам нужна цена из столбца с номером 2.
  • интервал_поиска (Поиск диапазона) – в это поле можно ввести только два значения: ЛОЖЬ или ИСТИНА:
      • Если введено значение 0 or ЛЕЖА (ЛОЖНЫЙ), то фактически это означает, что разрешен только поиск точное совпадение, т.е. если функция не находит нестандартный товар, указанный в таблице заказов в прайс-листе (например, если указано «Кокос»), то она выдаст ошибку #Н/Д (нет данных).
      • Если введено значение 1 or ИСТИНА (ИСТИННЫЙ), то это значит, что вы разрешаете поиск не точного, а приблизительное совпадение, т.е. в случае с «кокосом» функция попытается найти товар с названием, максимально близким к «кокосу», и вернет цену для этого названия. В большинстве случаев такая приблизительная подмена может сыграть с пользователем злую шутку, подставив стоимость не того товара, который там был на самом деле! Так что для большинства реальных бизнес-задач приближенный перебор лучше не допускать. Исключением являются случаи, когда мы ищем цифры, а не текст, например, при расчете скидок за шаг.

Все! Осталось нажать OK и скопируйте введенную функцию во весь столбец.

# Н/Д ошибки и их подавление

Функция ВПР (ВПР) возвращает ошибку #Н/Д (#Н/Д) если:

  • Точный поиск включен (аргумент Интервальный просмотр = 0) и нужного имени нет в Настольные.
  • Грубый поиск включен (Интервальный просмотр = 1), но Настольные, в котором происходит поиск, не сортируется по возрастанию имен.
  • Формат ячейки, откуда берется необходимое значение имени (например, B3 в нашем случае) и формат ячеек первого столбца (F3:F19) таблицы различны (например, числовой и текстовый). ). Особенно характерен этот случай при использовании вместо текстовых названий числовых кодов (номеров счетов, идентификаторов, дат и т.п.). В этом случае вы можете использовать функции Ч и ТЕКСТ конвертировать форматы данных. Это будет выглядеть примерно так:

    =ВПР(ТЕКСТ(B3),цена,0)

    Подробнее об этом можно прочитать здесь.

  • Функция не может найти необходимое значение, так как код содержит пробелы или невидимые непечатаемые символы (переносы строк и т.п.). В этом случае вы можете использовать текстовые функции TRIM (ОТДЕЛКА) и ПЕЧАТЬ(ЧИСТЫЙ) чтобы удалить их:

    =ВПР(TRIMSPACES(CLEAN(B3)),цена,0)

    =ВПР(ОТРЕЗАТЬ(ЧИСТИТЬ(B3));цена;0)

Чтобы подавить сообщение об ошибке # N / A (#Н/Д) в случаях, когда функция не может найти точное совпадение, вы можете использовать функцию IFERROR (ЕСЛИОШИБКА)… Так, например, эта конструкция перехватывает любые ошибки, генерируемые ВПР, и заменяет их нулями:

= ЕСЛИОШИБКА (ВПР (B3, цена, 2, 0), 0)

= ЕСЛИОШИБКА (ВПР (B3; цена; 2; 0); 0)

PS

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

 

  • Улучшенная версия функции ВПР (ВПР 2).
  • Быстрый расчет шаговых (диапазонных) скидок с помощью функции ВПР.
  • Как сделать «левый ВПР» с помощью функций ИНДЕКС и ПОИСКПОЗ
  • Как использовать функцию ВПР для заполнения форм данными из списка
  • Как вытащить из таблицы не первое, а все значения сразу
  • Функции VLOOKUP2 и VLOOKUP3 из дополнения PLEX

 

Оставьте комментарий