Semenalidery.com

IT Новости из мира ПК
5 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Excel впр интервальный просмотр

Использование функции ВПР (VLOOKUP) для подстановки значений

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

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

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

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

Решение

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

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

Теперь используем функцию ВПР. Выделите ячейку, куда она будет введена (D3) и откройте вкладку Формулы — Вставка функции (Formulas — Insert Function) . В категории Ссылки и массивы (Lookup and Reference) найдите функцию ВПР (VLOOKUP) и нажмите ОК. Появится окно ввода аргументов для функции:

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

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

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

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

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

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

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

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

Функция ВПР() в EXCEL

Функция ВПР() , английский вариант VLOOKUP(), ищет значение в первом (в самом левом) столбце таблицы и возвращает значение из той же строки, но другого столбца таблицы.

Функция ВПР() является одной из наиболее используемых в EXCEL, поэтому рассмотрим ее подробно.

В этой статье выбран нестандартный подход: акцент сделан не на саму функцию, а на те задачи, которые можно решить с ее помощью.

Синтаксис функции

ВПР(искомое_значение; таблица; номер_столбца; интервальный_просмотр)

Искомое_значение — это значение, которое Вы пытаетесь найти в столбце с данными. Искомое_значение может быть числом или текстом, но чаще всего ищут именно число. Искомое значение должно находиться в первом (самом левом) столбце диапазона ячеек, указанного в таблице .

Таблица — ссылка на диапазон ячеек. В левом столбце таблицы ищется Искомое_значение , а из столбцов расположенных правее, выводится соответствующий результат (хотя, в принципе, можно вывести можно вывести значение из левого столбца (в этом случае это будет само искомое_значение )). Часто левый столбец называется ключевым . Если первый столбец не содержит искомое_значение , то функция возвращает значение ошибки #Н/Д.

Номер_столбца — номер столбца Таблицы , из которого нужно выводить результат. Самый левый столбец (ключевой) имеет номер 1 (по нему производится поиск).

Параметр интервальный_просмотр может принимать 2 значения: ИСТИНА (ищется значение ближайшее к критерию или совпадающее с ним) и ЛОЖЬ (ищется значение в точности совпадающее с критерием). Значение ИСТИНА предполагает, что первый столбец в таблице отсортирован в алфавитном порядке или по возрастанию. Это способ используется в функции по умолчанию, если не указан другой.

Ниже в статье рассмотрены популярные задачи, которые можно решить с использованием функции ВПР() .

Задача1. Справочник товаров

Пусть дана исходная таблица (см. файл примера лист Справочник ).

Задача состоит в том, чтобы, выбрав нужный Артикул товара, вывести его Наименование и Цену .

Примечание . Это «классическая» задача для использования ВПР() (см. статью Справочник ).

Для вывода Наименования используйте формулу =ВПР($E9;$A$13:$C$19;2;ЛОЖЬ) или = ВПР($E9;$A$13:$C$19;2;ИСТИНА) или = ВПР($E9;$A$13:$C$19;2) (т.е. значение параметра Интервальный_просмотр можно задать ЛОЖЬ или ИСТИНА или вообще опустить). Значение параметра номер_столбца нужно задать =2, т.к. номер столбца Наименование равен 2 (Ключевой столбец всегда номер 1).

Для вывода Цены используйте аналогичную формулу =ВПР($E9;$A$13:$C$19;3;ЛОЖЬ) (значение параметра номер_столбца нужно задать =3).

Читать еще:  Диаграмма ганта в excel скачать

Ключевой столбец в нашем случае содержит числа и должен гарантировано содержать искомое значение (условие задачи). Если первый столбец не содержит искомый артикул , то функция возвращает значение ошибки #Н/Д. Это может произойти, например, при опечатке при вводе артикула. Чтобы не ошибиться с вводом искомого артикула можно использовать Выпадающий список (см. ячейку Е9 ).

Понятно, что в нашей задаче ключевой столбец не должен содержать повторов (в этом смысл артикула, однозначно определяющего товар). В противном случае будет выведено самое верхнее значение.

При решении таких задач ключевой столбец лучше предварительно отсортировать (это также поможет сделать Выпадающий список нагляднее). Кроме того, в случае несортированного списка, ВПР() с параметром Интервальный_просмотр ИСТИНА (или опущен) работать не будет.

В файле примера лист Справочник также рассмотрены альтернативные формулы (получим тот же результат) с использованием функций ИНДЕКС() , ПОИСКПОЗ() и ПРОСМОТР() . Если ключевой столбец (столбец с артикулами) не является самым левым в таблице, то функция ВПР() не применима. В этом случае нужно использовать альтернативные формулы. Связка функций ИНДЕКС() , ПОИСКПОЗ() образуют так называемый «правый ВПР»: =ИНДЕКС(B13:B19;ПОИСКПОЗ($E$9;$A$13:$A$19;0);1)

В файле примера лист Справочник показано, что формулы применимы и для ключевых столбцов содержащих текстовые значения, т.к. артикул часто бывает текстовым значением. Также задача решена для несортированного ключевого столбца.

Примечание . Для удобства, строка таблицы, содержащая найденное решение, выделена Условным форматированием . (см. статью Выделение строк таблицы в MS EXCEL в зависимости от условия в ячейке ).

Примечание . Никогда не используйте ВПР() с параметром Интервальный_просмотр ИСТИНА (или опущен) если ключевой столбец не отсортирован по возрастанию, т.к. результат формулы непредсказуем (если функция ВПР() находит значение, которое больше искомого, то она выводит значение, которое расположено на строку выше его).

Задача2. Поиск ближайшего числа

Предположим, что нужно найти товар, у которого цена равна или наиболее близка к искомой.

Чтобы использовать функцию ВПР() для решения этой задачи нужно выполнить несколько условий:

  1. Ключевой столбец, по которому должен производиться поиск, должен быть самым левым в таблице;
  2. Ключевой столбец должен быть обязательно отсортирован по возрастанию;
  3. Значение параметра Интервальный_просмотр нужно задать ИСТИНА или вообще опустить.

Для вывода Наименования товара используйте формулу =ВПР($A7;$A$11:$B$17;2;ИСТИНА)

Для вывода найденной цены (она не обязательно будет совпадать с заданной) используйте формулу: =ВПР($A7;$A$11:$B$17;1;ИСТИНА)

Как видно из картинки выше, ВПР() нашла наибольшую цену, которая меньше или равна заданной (см. файл примера лист «Поиск ближайшего числа» ). Это связано следует из того как функция производит поиск: если функция ВПР() находит значение, которое больше искомого, то она выводит значение, которое расположено на строку выше его. Как следствие, если искомое значение меньше минимального в ключевом столбце, то функцию вернет ошибку #Н/Д.

Найденное значение может быть далеко не самым ближайшим. Например, если попытаться найти ближайшую цену для 199, то функция вернет 150 (хотя ближайшее все же 200). Это опять следствие того, что функция находит наибольшее число, которое меньше или равно заданному.

Если нужно найти по настоящему ближайшее к искомому значению, то ВПР() тут не поможет. Такого рода задачи решены в разделе Ближайшее ЧИСЛО . Там же можно найти решение задачи о поиске ближайшего при несортированном ключевом столбце.

Примечание . Для удобства, строка таблицы, содержащая найденное решение, выделена Условным форматированием . Это можно сделать с помощью формулы =ПОИСКПОЗ($A$7;$A$11:$A$17;1)=СТРОКА()-СТРОКА($A$10) .

Примечание : Если в ключевом столбце имеется значение совпадающее с искомым, то функция с параметром Интервальный_просмотр =ЛОЖЬ вернет первое найденное значение, равное искомому, а с параметром =ИСТИНА — последнее (см. картинку ниже).

Если столбец, по которому производится поиск не самый левый, то ВПР() не поможет. В этом случае нужно использовать функции ПОИСКПОЗ() + ИНДЕКС() или ПРОСМОТР() .

Функция ВПР в Excel

Данная статья посвящена функции ВПР. В ней будет рассмотрена пошаговая инструкция функции ВПР, под названием «Функция ВПР в Excel для чайников». В данной статье мы подробно рассмотрим описание, синтаксис и примеры функции ВПР в Excel. А также рассмотрим, как использовать функцию ВПР с несколькими условиями и разберем основные ошибки, почему не работает функция ВПР.

Синтаксис и описание функции ВПР в Excel

Итак, так как второе название этой статьи «Функция ВПР в Excel для чайников», начнем с того что узнаем, что же такое функция ВПР и что она делает? Функция ВПР на английском VLOOKUP, ищет указанное значение и возвращает соответствующее значение из другого столбца.

Как работает функция ВПР? Функция ВПР в Excel выполняет поиск по вашим спискам данных на основе уникального идентификатора и предоставляет вам часть информации, связанную с этим уникальным идентификатором.

Буква «В» в ВПР означает «вертикальный». Она используется для дифференциации функции ВПР и ГПР, которая ищет значение в верхней строке массива («Г» обозначает «горизонтальный»).

Функция ВПР доступна во всех версиях Excel 2016, Excel 2013, Excel 2010, Excel 2007, Excel 2003.

Синтаксис функции ВПР выглядит следующим образом:

Как видите, функция ВПР имеет 4 параметра или аргумента. Первые три параметра обязательные, последний — необязательный.

  1. искомое_значение – это значение для поиска.

Это может быть либо значение (число, дата или текст), либо ссылка на ячейку (ссылка на ячейку, содержащую значение поиска), или значение, возвращаемое некоторой другой функцией Excel. Например:

  • Поиск числа : =ВПР(40; A2:B15; 2) — формула будет искать число 40.
  • Поиск текста : =ВПР(«яблоки»; A2:B15; 2) — формула будет искать текст «яблоки». Обратите внимание, что вы всегда включаете текстовые значения в «двойные кавычки».
  • Поиск значения из другой ячейки : =ВПР(C2; A2:B15; 2) — формула будет искать значение в ячейке C2.
  1. таблица — это два или более столбца данных.

Помните, что функция ВПР всегда ищет искомое значение в первом столбце таблицы. Ваш таблица может содержать различные значения, такие как текст, дата, числа или логические значения. Значения нечувствительны к регистру , что означает, что прописные и строчные буквы считаются идентичными.

Итак, наша формула =ВПР(40; A2:B15; 2) будет искать «40» в ячейках от A2 до A15, потому что A — это первый столбец таблицы A2: B15.

  1. номер_столбца — номер столбца в таблице, из которой должно быть возвращено значение в соответствующей строке.

Самый левый столбец в указанной таблице равен 1, второй столбец — 2, третий — 3 и т. д.

Итак, теперь вы можете прочитать всю формулу =ВПР(40; A2:B15; 2). Формула ищет «40» в ячейках от A2 до A15 и возвращает соответствующее значение из столбца B (потому что B является вторым столбцом в указанной таблице A2:B15).

4. интервальный_просмотр определяет, ищете ли вы точное соответствие (ЛОЖЬ) или приблизительное соответствие (ИСТИНА или опущено). Этот последний параметр является необязательным, но очень важным.

Функция ВПР в Excel примеры

Теперь давайте рассмотрим несколько примеров использования функции ВПР для реальных данных.

Функция ВПР на разных листах

На практике формулы ВПР редко используются для поиска данных на одном листе. Чаще всего вам придется искать и вытаскивать соответствующие данные с другого листа.

Читать еще:  Формула среднего значения в excel

Чтобы использовать функцию ВПР с другого листа Excel, вы должны ввести имя рабочего листа и восклицательный знак в аргументе таблица перед диапазоном ячеек, например, =ВПР(40;Лист2!A2:B15;2). Формула указывает, что диапазон поиска A2:B15 находится в Лист2.

Конечно, вам не нужно вводить имя листа вручную. Просто начните вводить формулу, и когда дело дойдет до аргумента таблица, переключитесь на рабочий лист поиска и выберите диапазон с помощью мыши.

Формула, которую вы видите на изображении ниже, ищет текст в ячейке А2 (« Продукт 3 ») в столбце A (1-й столбец диапазона поиска A2:B9) на листе « Цены »:

Функция ВПР в Excel – Функция ВПР на разных листах

Обратите внимание, что рекомендуется использовать абсолютные ссылки на ячейки (со знаком $) в аргументе таблица. Это предотвратит изменение диапазона поиска при копировании формулы ВПР в другие ячейки.

Как использовать именованный диапазон или таблицу в формулах ВПР

Если вы собираетесь использовать один и тот же диапазон поиска в нескольких формулах ВПР, вы можете создать именованный диапазон для него и ввести имя непосредственно в аргументе таблица вашей формулы ВПР.

Чтобы создать именованный диапазон, просто выберите ячейки и введите любое имя в поле « Имя », слева от панели « Формула ».

Функция ВПР в Excel – Присвоение имени диапазону

Теперь вы можете написать следующую формулу ВПР, чтобы получить цену Продукта 1:

Функция ВПР в Excel – Пример функции ВПР с именем диапазона

Большинство имен диапазонов в Excel применяются ко всей книге, поэтому вам не нужно указывать имя рабочего листа, даже если ваш диапазон поиска находится на другом листе. Такие формулы гораздо более понятны. Кроме того, использование именованных диапазонов может быть хорошей альтернативой абсолютным ссылкам на ячейки. Поскольку именованный диапазон не изменяется, когда формула копируется в другие ячейки, и вы можете быть уверены, что ваш диапазон поиска всегда останется верным.

Если вы преобразовали диапазон ячеек в полнофункциональную таблицу Excel (вкладка «Вставка» —> «Таблица»), вы можете выбрать диапазон поиска с помощью мыши, а Microsoft Excel автоматически добавит имена колонок или имя таблицы в формулу:

Функция ВПР в Excel – Пример функции ВПР с именем таблицы

Полная формула может выглядеть примерно так:

или даже =ВПР(«Продукт 1»;Таблица6;2).

Как и именованные диапазоны, имена столбцов являются постоянными, а ссылки на ячейки не изменятся независимо от того, где копируется формула ВПР.

Функция ВПР с несколькими условиями

Рассмотрим пример функции ВПР с несколькими условиями. У нас есть следующие исходные данные:

Функция ВПР в Excel – Таблица исходных данных

Пусть нам необходимо использовать функцию ВПР с несколькими условиями. Например, для поиска цены товара по двумя критериями: названию продукта и его типу.

  1. Для того чтобы использовать функцию ВПР с несколькими условиями необходимо вставить в начало дополнительный столбец, который будет хранить информацию с названием и типом товара.

Итак на листе « Цены » вставляем столбец и в ячейке А2 вводим следующую формулу:

При помощи этой формулы мы сцепляем значение столбца « Продукт » и « Тип ». Заполняем все ячейки.

Теперь таблица для поиска выглядит следующим образом:

Функция ВПР в Excel – Добавление вспомогательного столбца
  1. Теперь в ячейке С2 на листе « Продажи » напишем следующую формулу ВПР:

Заполняем для остальных ячеек и в результате получаем цены для каждого продукта в соответствии с типом:

Функция ВПР в Excel – Пример ВПР с несколькими условиями

Теперь разберем ошибки функции ВПР.

Почему не работает функция ВПР

В этой части статьи мы рассмотрим почему не работает функция ВПР и возможные ошибки функции ВПР.

ШКОЛА ПРОГРАММИРОВАНИЯ

Вы здесь

Сообщение об ошибке

Функции Excel. Функция ВПР(), ГПР() (+видео)

Продолжаю серию уроков про встроенные функции Excel. На этот раз рассмотрим пару очень необходимых функций ВПР и ГПР, которые очень полезны при переносе данных из разных таблиц или поиска значений в больших таблицах. Например, у Вас имеется первая таблица со столбцами «Код товара», «Название товара», во второй таблице данные с продажами, в которых «Дата продажи», «Продавец», «Код товара», «Сумма товара». Нам необходимо во вторую таблицу подставить название товара по его коду. Если таблица с десятком строк, то ничего страшного, можно и вручную, а вот если в таблице несколько десятков тысяч строк, да еще и наименований товара несколько тысяч, то тут уж вручную очень долго придется мучиться, а ошибок будет еще сколько. Для Excel есть пара функций ВПР и ГПР. По принципу работы эти функции идентичны с той лишь разницей что, ВПР работает по вертикали, справа налево, а ГПР по горизонтали, сверху вниз.

Синтаксис функции ВПР и ГПР

Синтаксис функции ВПР и ГПР одинаков:
ВПР(искомое_значение; таблица; номер_столбца; интервальный_просмотр)

Искомое значение — поиск этого аргумента производится в первом столбце «таблица». Искомое значение может ссылаться на ячейку(и) или быть текстовой строкой.

Таблица содержит информацию, в которой ведется поиск и из которой производится подстановка значения. Можно указывать ссылки на диапазоны ячеек листа.

Примечание: Значения, которые находятся в первом столбце аргумента «таблица», являются числовыми, логическими или текстовыми. При сравнении текстовых строк регистр букв не учитывается.

Номер_столбца — аргумент указывает номер столбца «Таблица» из которого будет возвращено значение.

Интервальный_просмотр — имеет параметры ИСТИНА или ЛОЖЬ. Если ИСТИНА(или пропущен), то будет возвращаться приблизительно-похожее значение. Если ЛОЖЬ, то будет поиск точного совпадения значения. Если значение не найдено, то функция возвратит значение #Н/Д.

Примеры работы функции ВПР() и ГПР()

Давайте теперь рассмотрим на примерах работу функций ВПР и ГПР!?

Пример 1. Функция ВПР

В таблице «Журнал продаж», содержится несколько полей: «Дата», «Код Товара», «Кол-во». В таблице «Номенклатура» поля: «Код Товара» и «Наименование товара». Необходимо в таблицу «Журнал продаж», по коду товара, добавить наименование товара.

Таблица имеет следующие значения:

Добавим пустой столбец в таблицу «Журнал продаж» между столбцами «Код Товара» и «Кол-во». Ставим в курсор в новый столбец, ячейка С2 и жмем добавить функцию. Функция ВПР находится в категории «Ссылки и массивы», выбираем функцию ВПР и задаем ей следующие параметры:

Обратите внимание на аргумент «Таблица», для того чтобы при «протяжке» формулы ниже по столбцу, адрес на диапазон в таблице «Номенклатура» не «съехал», его необходимо зафиксировать. Фиксация ячеек осуществляется знаком «$». Мы фиксируем и столбцы и строки. Все. Жмем ОК и протягиваем формулу.

Работа функции ГПР аналогична ВПР. Ознакомиться с ее принципом работы Вы можете в приложенном файле, или просмотрев видео-демонстрацию урока. А на этом все. До встреч.

Прикрепленный файл: func_vpr_gpr.zip

Видео: Работа с функцией ВПР и ГПР в Excel

Функция ВПР в Excel

Функция ВПР в Excel — одна из самых интересных и полезных функций для нахождения определенных значений в одной таблице и подстановки этих значений в другую таблицу. Однако, функция имеет некоторые ограничения, а также, при неправильном использовании, может выдавать некорректные результаты или приводить к ошибкам.

Читать еще:  Как добавить гиперссылку в excel

Формула ВПР

Функция ВПР предназначена для поиска и подстановки значений из одной таблицы в другую на основании какого-либо признака, объединяющего обе эти таблицы. Находится функция в категории «Ссылки и массивы».

Свое название ВПР функция получила от первых букв в словах Вертикальное Первое Равенство. Смысл этой функции заключается в поиске значения в крайнем левом столбце таблицы и определении значения ячейки, находящейся в заданном столбце той же строки.

Допустим, существует две таблицы. В одной таблице содержатся табельные номера работников и их имена, другая таблица состоит из табельных номеров и фамилий. Задача заключается в том, чтобы объединить эти таблицы, или другими словами, подставить в одну из этих таблиц данные из другой таблицы.

Функция ВПР имеет четыре аргумента:

  1. Искомое значение, в рассматриваемом примере, это табельный номер, ячейка с табельным номером выделена рамкой синего цвета;
  2. Таблица, в приведенном примере это таблица с табельными номерами и именами, выделена рамкой зеленого цвета;
  3. Номер столбца, в используемой для примера таблице, столбец с именами имеет порядковый номер два;
  4. Интервальный просмотр. Это необязательный аргумент, о нем чуть позже.

Результат вычисления функции виден на изображении ниже.

В примере два работника с именами Иван и Сидор умышленно имеют одинаковый табельный номер. Если их поменять местами, то и результат вычисления функции ВПР будет другим, что может привести к неожиданным результатам. Функция определяет ПЕРВОЕ равенство. При использовании формул с функцией ВПР рекомендуется сделать сортировку таблицы по искомому значению, то есть, по табельному номеру в рассматриваемом примере.

Ошибки #Н/Д, #ССЫЛКА! и #ЗНАЧ!

Достаточно часто функция ВПР вместо ожидаемого результата выдает ошибки вида #Н/Д (значение недоступно формуле или функции). Появление таких ошибок, как правило, связано с отсутствием искомого значения в таблице, либо с неправильным вводом формулы и незнанием некоторых особенностей функции ВПР.

Неправильный ввод формулы

При распространении формулы на другие ячейки, выделенные диапазоны могут быть смещены таким образом, что искомое значение оказывается за пределами таблицы.

Результатом вычисления функции будет ошибка вида #Н/Д.

В нашем простом примере таблица состоит всего из двух столбцов, на практике же столбцов может быть больше. Важно, чтобы, во-первых, столбец, в котором будет производиться поиск искомых значений был крайним левым, а во-вторых правильно определить номер столбца. Если в таблице два столбца, а при вводе формулы в третьем аргументе указано число, которое больше двух, то результатом вычисления функции будет ошибка типа #ССЫЛКА!, а если меньше единицы, то #ЗНАЧ!

Число отформатировано как текст

В первой таблице, в ячейке с адресом «A2» число 2551 отформатировано как текст, а во второй таблице, в ячейке «D3» записано числовое значение 2551. Поскольку число 2551 не равно тексту 2551, функция выдает ошибку #Н/Д.

При поиске числовых значений, необходимо позаботиться о том, чтобы и в таблице эти значения были также числовыми.

Различие в написании текстовых значений

В примере, который приведен выше, поиск в таблице осуществляется по табельному номеру, то есть по числовому значению. На практике же, вместо числовых значений могут использоваться значения текстовые. При работе с текстом нужно учитывать тот факт, что ВПР различает буквы кириллицы и латиницы, а регистр не имеет значения.

В ячейке с адресом «A2» написана буква кириллицы, а в «D3» — буква латиницы, в результате ошибка.

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

ЛОЖЬ и ИСТИНА

Аргумент «Интервальный_просмотр» не является обязательным для функции ВПР. Этот аргумент может принимать два значения ЛОЖЬ (если необходимо найти точное совпадение) и ИСТИНА (если необходимо найти приблизительное совпадение).

Этот момент не всегда понятен пользователям, поэтому требует уточнения, которое проще всего показать на примере.

В последнем аргументе формулы установлено значение «ИСТИНА», что соответствует приблизительному поиску. Искомым значением является число 2552, но в таблице (той, которая расположена слева) искомое значение отсутствует, точного совпадения нет, поэтому функция ищет ближайшее меньшее число, то есть 2551 и возвращает значение «Иван».

Если искомое значение – это текст, то при интервальном просмотре, соответствующем значению «ИСТИНА» и при отсутствии точного совпадения, функция будет искать ближайшее меньшее значение по алфавиту.

Искомое значение – Петр, но в таблице нет значения Петр, поэтому функция ищет ближайшее меньшее значение по алфавиту. Буква «Ф» в алфавите находится после буквы «П», а вот «И» — до буквы «П», поэтому она и является меньшим значением. Соответственно результатом функции будет значение «Иванов».

В случае, когда необходимо найти неточное совпадение с текстовым значением, необходимо использовать интервальный просмотр «ЛОЖЬ», а к искомому значению подставлять специальные символы совпадения (?-одиночный символ и *-произвольная последовательность символов). Тильда (

) ставится в том случае, если необходимо найти сами знаки (?) и (*).

Несколько условий в ВПР

Встречаются случаи, когда поиск необходимо осуществить по нескольким значениям. Для этого необходимо в обоих таблицах произвести сцепку нужных значений при помощи текстовой функции «Сцепить» и уже сцепленное значение указывать в качестве искомого значения.

Ограничения в функции ВПР

Функция «ВПР» позволяет решать широкий круг задач, однако имеет ряд ограничений. В случаях, когда в таблице несколько совпадений с искомым значением функция позволяет определить только первое (либо последнее, в зависимости от того как отсортирована таблица) совпадение.

VBA-аналог функции ВПР

Когда использование функции ВПР невозможно по каким-то причинам, либо функция выдает не тот результат, который нужен, можно воспользоваться надстройкой для Excel, которая по аналогии с формулой ВПР позволяет производить поиск и подстановку значений из одной таблицы в другую.

Аналогично функции ВПР, в диалоговом окне надстройки необходимо задать несколько параметров:

  1. Номер столбца с искомыми значениями;
  2. Таблица;
  3. Номер столбца в таблице;
  4. Условие (тип) поиска;
  5. Номер столбца для вставки результатов.

При выборе опции «Точное совпадение», программа находит в таблице точное равенство, при выборе опции «Неточное совпадение», программа приводит сравниваемые значения к нижнему регистру, удаляет лишние пробелы и определяет не равенство, а содержание искомого значения в значениях таблицы. При необходимости можно определять не только первое, а любое заданное равенство, если таковых несколько, либо вообще все совпадения через запятую.

Для удобства определения номеров столбцов в диалоговом окне предусмотрена возможность переключения с текстовых названий столбцов на числовые и обратно. Кроме того, реализована возможность сохранения настроек диалогового окна для следующих сеансов работы.

Ссылка на основную публикацию
Adblock
detector