Letysite.ru

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

Описание функции впр в excel

Функция ВПР в 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, вы должны ввести имя рабочего листа и восклицательный знак в аргументе таблица перед диапазоном ячеек, например, =ВПР(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 – Пример ВПР с несколькими условиями

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

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

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

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

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

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

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

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

Решение

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

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

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

Читать еще:  Автоматический перенос текста в excel

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

  • Искомое значение (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) . Так, например, вот такая конструкция перехватывает любые ошибки создаваемые ВПР и заменяет их нулями:

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

ВПР (функция ВПР)

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

ФУНКЦИЯ ВПР используется, если нужно найти элементы в таблице или диапазоне по строкам. Например, можно найти цену на автомобильную часть по номеру детали или получить имя сотрудника на основе его кода сотрудника.

Совет: Чтобы получить дополнительную справку о функции ВПР, просмотрите эти видео с YouTube от экспертов сообщества Excel!

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

= ВПР (необходимые условия для поиска, номер столбца в диапазоне, содержащий возвращаемое значение, возвращаемое приближенное или точное соответствие — обозначено как 1/истина или 0/ложь).

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

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

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

= ВПР (A2; A10: C20; 2; ИСТИНА)

= ВПР (a2; сведения о клиенте)! А:Ф, 3, ЛОЖЬ)

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

Например, если Таблица-массив охватывает ячейки B2: D7, то lookup_value должен находиться в столбце B.

Искомое_значение может являться значением или ссылкой на ячейку.

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

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

Номер столбца (начиная с 1 для самого левого столбца table_array), содержащего возвращаемое значение.

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

Приближенное соответствие: 1/истина предполагает, что первый столбец в таблице отсортирован либо в числовом формате, либо в алфавитном порядке, а затем будет искать ближайшее значение. Это способ по умолчанию, если не указан другой. Например, = ВПР (90; a1: B100; 2; истина).

Точное совпадение: 0/ложь осуществляет поиск точного значения в первом столбце. Например, = ВПР («Смит»; a1: B100; 2; ложь).

Начало работы

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

Значение, которое вам нужно найти, то есть искомое значение.

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

Номер столбца в диапазоне, содержащий возвращаемое значение. Например, если в качестве диапазона задано значение B2: D11, число B должно быть первым столбцом, а в качестве второго — «C» и т. д.

При желании вы можете указать слово ИСТИНА, если вам достаточно приблизительного совпадения, или слово ЛОЖЬ, если вам требуется точное совпадение возвращаемого значения. Если вы ничего не указываете, по умолчанию всегда подразумевается вариант ИСТИНА, то есть приблизительное совпадение.

Теперь объедините все перечисленное выше аргументы следующим образом:

= ВПР (искомое значение; диапазон с искомым значением; номер столбца в диапазоне с возвращаемым значением, приближенное соответствие (истина) или точное совпадение (ложь)).

Примеры

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

Пример 1

Пример 2

Пример 3

Пример 4

Пример 5

Вы можете использовать функцию ВПР для объединения нескольких таблиц в одну, если одна из них имеет общие поля. Это может быть особенно удобно, если вам нужно предоставить доступ к книге пользователям более ранних версий Excel, которые не поддерживают функции работы с данными с несколькими таблицами в качестве источников данных — путем объединения источников в одну таблицу и изменения источника данных компонента данных на новый. Таблица, функция данных может использоваться в более ранних версиях Excel (при условии, что сама функция данных поддерживается в более ранней версии).

Здесь в столбцах A-F и H есть значения или формулы, которые используют только значения на листе, а остальные столбцы используют функцию ВПР и значения столбца A (клиентский код) и столбец B (юрист) для получения данных из других таблиц.

Скопируйте таблицу с общими полями на новый лист и присвойте ей имя.

Чтобы открыть диалоговое окно Управление связями, нажмите кнопку данные > работа с данными > связей .

Для каждой из перечисленных связей обратите внимание на следующее:

Поле, связывающее таблицы (в диалоговом окне отображаются в круглых скобках). Это lookup_value формулы функции ВПР.

Имя связанной таблицы подстановки. Это table_array формулы функции ВПР.

Поле (столбец) в связанной таблице подстановки с данными, которые вы хотите добавить в новый столбец. Эти сведения не отображаются в диалоговом окне Управление связями. чтобы узнать, какое поле вы хотите извлечь, вам потребуется ознакомиться со связанной таблицей подстановки. Вы хотите отметить номер столбца (A = 1) – это col_index_num в формуле.

Чтобы добавить поле в новую таблицу, введите формулу ВПР в первом пустом столбце с помощью сведений, собранных в действии 3.

В нашем примере столбец G использует юрист ( lookup_value) для получения данных о тарифных курсах из четвертого столбца (col_index_num = 4) из таблицы судебные тблатторнэйс ( table_array) с формулой = ВПР ([@Attorney], tbl_Attorneys, 4, ложь).

Читать еще:  Печать листа excel на одной странице

В формуле также могут использоваться ссылки на ячейки и ссылки на диапазоны. В нашем примере это было бы = ВПР (a2; «Юристы»! А:Д, 4, ложь).

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

Функция ВПР() в 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).

Ключевой столбец в нашем случае содержит числа и должен гарантировано содержать искомое значение (условие задачи). Если первый столбец не содержит искомый артикул , то функция возвращает значение ошибки #Н/Д. Это может произойти, например, при опечатке при вводе артикула. Чтобы не ошибиться с вводом искомого артикула можно использовать Выпадающий список (см. ячейку Е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-функции ВПР (VLOOKUP) для поиска данных в таблице и извлечения их в другую

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

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

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

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

ВПР — это аббревиатура от вертикального просмотра. Аналогично и VLOOKUP — Vertical LOOKUP. Уже само название функции намекает нам, что она производит поиск в строках таблицы (по вертикали — перебирая строки и фиксируя столбец), а не в столбцах (по горизонтали — перебирая столбцы и фиксируя строку). Надо заметить, что у ВПР есть сестра — гадкий утёнок, которая никогда не станет лебедем, — это функция ГПР (HLOOKUP). ГПР, в противоположность ВПР, производит горизонтальный поиск, однако концепция Excel (да и вообще концепция организации данных) подразумевает, что ваши таблицы имеют небольшое количество столбцов и гораздо большее количество строк. Именно поэтому поиск по строкам нам требуется во много раз чаще, чем по столбцам. Если вы в Excel слишком часто пользуетесь функцией ГПР, то, вполне вероятно, что вы чего-то не поняли в этой жизни.

Читать еще:  Как разблокировать документ excel

Синтаксис

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

=ВПР( ; ; [; ] ), тут:

— искомое значение (редко) или ссылка на ячейку, содержащую искомое значение (подавляющее большинство случаев);

— ссылка на диапазон ячеек (двумерный массив), в ПЕРВОМ (!) столбце которого будет осуществляться поиск значения параметра ;

— номер столбца в диапазоне, из которого будет возвращено значение;

это очень важный параметр, который отвечает на вопрос, а отсортирован ли по возрастанию первый столбец диапазона . В случае, если массив отсортирован, мы указываем значение ИСТИНА (TRUE) или 1, в противном случае — ЛОЖЬ (FALSE) или 0. В случае, если данный параметр опущен, он по умолчанию становится равным 1.

Держу пари, что многие из тех, кто знает функцию ВПР как облупленную, прочитав описание четвёртого параметра, могут почувствовать себя неуютно, так как они привыкли видеть его в несколько ином виде: обычно там идёт речь о точном соответствии при поиске (ЛОЖЬ или 0) либо же о диапазонном просмотре (ИСТИНА или 1).

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

Как же конкретно работает формула ВПР

  • Вид формулы I. Если последний параметр опущен или указан равным 1, то ВПР предполагает, что первый столбец отсортирован по возрастанию, поэтому поиск останавливается на той строке, которая непосредственно предшествует строке, в которой находится значение, превышающее искомое. Если такой строки не найдено, то возвращается последняя строка диапазона.
  • Вид формулы II. Если последний параметр указан равным 0, то ВПР последовательно просматривает первый столбец массива и сразу останавливает поиск, когда найдено первое точное соответствие с параметром , в противном случае возвращается код ошибки #Н/Д (#N/A).

Схемы работы формул

ВПР тип I

ВПР тип II

Следствия для формул вида I

  1. Формулы можно использовать для распределения значений по диапазонам.
  2. Если первый столбец содержит повторяющиеся значения и правильно отсортирован, то будет возвращена последняя из строк с повторяющимися значениями.
  3. Если искать значение заведомо большее, чем может содержать первый столбец, то можно легко находить последнюю строку таблицы, что бывает довольно ценно.
  4. Данный вид вернёт ошибку #Н/Д, только если не найдёт значения меньше или равное искомому.
  5. Понять, что формула возвращает неправильные значения, в случае если ваш массив не отсортирован, довольно затруднительно.

Следствия для формул вида II

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

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

Вы добрались до кульминационного места статьи. Казалось бы, ну какая разница, укажу ли я в качестве последнего параметра ноль или единицу? В основном все указывают, конечно же, ноль, так как это довольно практично: не надо заботиться о сортировке первого столбца массива, сразу видно, найдено значение или нет. Но если у вас на листе несколько тысяч формул ВПР (VLOOKUP), то вы заметите, что ВПР вида II работает медленно. При этом обычно все начинают думать:

  • мне нужен более мощный компьютер;
  • мне нужна более быстрая формула, например, многие знают про ИНДЕКС+ПОИСКПОЗ (INDEX+MATCH), которая якобы быстрее на жалкие 5–10%.

И мало кто думает, что стоит только начать использовать ВПР вида I и обеспечить любыми способами сортировку первого столбца, как скорость работы ВПР возрастёт в 57 раз. Пишу прописью — В ПЯТЬДЕСЯТ СЕМЬ РАЗ! Не на 57%, а на 5 700%. Данный факт я проверил вполне надёжно.

Секрет такой быстрой работы кроется в том, что на отсортированном массиве можно применять чрезвычайно эффективный алгоритм поиска, который носит название бинарного поиска (метод деления пополам, метод дихотомии). Так вот ВПР вида I его применяет, а ВПР вида II ищет без какой-либо оптимизации вообще. То же самое относится и к функции ПОИСКПОЗ (MATCH), которая включает в себя аналогичный параметр, а также и к функции ПРОСМОТР (LOOKUP), которая работает только на отсортированных массивах и включена в Excel ради совместимости с Lotus 1-2-3.

Недостатки формулы

Недостатки ВПР очевидны: во-первых, она ищет только в первом столбце указанного массива, а во-вторых, только справа от данного столбца. А как вы понимаете, вполне может случиться так, что столбец, содержащий необходимую информацию, окажется слева от столбца, в котором мы будем искать. Этого недостатка лишена уже упомянутая связка формул ИНДЕКС+ПОИСКПОЗ (INDEX+MATCH), что делает её наиболее гибким решением по извлечению данных из таблиц в сравнении с ВПР (VLOOKUP).

Некоторые аспекты применения формулы в реальной жизни

Диапазонный поиск

Классическая иллюстрация к диапазонному поиску — задача определения скидки по размеру заказа.

Поиск текстовых строк

Безусловно, ВПР ищет не только числа, но и текст. При этом надо принимать во внимание, что регистр символов формула не различает. Если использовать символы подстановки, то можно организовать нечёткий поиск. Есть два символа подстановки: «?» — заменяет один любой символ в текстовой строке, «*» — заменяет любое количество любых символов.

Борьба с пробелами

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

Разный формат данных

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

=ВПР(−−D7; Продукты!$A$2:$C$5; 3; 0) — если D7 содержит текст, а таблица — числа;

=ВПР(D7 & «»); Продукты!$A$2:$C$5; 3; 0) — и наоборот.

Кстати, перевести текст в число можно сразу несколькими способами, выбирайте:

  • Двойное отрицание —D7.
  • Умножение на единицу D7*1.
  • Сложение с нулём D7+0.
  • Возведение в первую степень D7^1.

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

Как подавить выдачу #Н/Д

Это очень удобно делать при помощи функции ЕСЛИОШИБКА (IFERROR).

Например: =ЕСЛИОШИБКА( ВПР(D7; Продукты!$A$2:$C$5; 3; 0); «»).

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

Массив

Часто забывают ссылку массива сделать абсолютной, и при протягивании массив «плывёт». Помните, что вместо A2:C5 следует использовать $A$2:$C$5.

Хорошей идеей является размещение справочного массива на отдельном листе рабочей книги. Не путается под ногами, да и сохраннее будет.

Ещё более хорошей идеей будет объявление этого массива в виде именованного диапазона.

Многие пользователи при указании массива используют конструкцию вида A:C, указывая столбцы целиком. Этот подход имеет право на существование, так как вы избавлены от необходимости отслеживать тот факт, что ваш массив включает все необходимые строки. Если вы добавите строки на лист с первоначальным массивом, то диапазон, указанный как A:C, не придётся корректировать. Безусловно, эта синтаксическая конструкция заставляет Excel проводить несколько большую работу, чем при точном указании диапазона, но данными накладными расходами можно пренебречь. Речь идёт о сотых долях секунды.

Ну и на грани гениальности — оформить массив в виде умной таблицы.

Использование функции СТОЛБЕЦ для указания колонки извлечения

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

Создание составного ключа через &»|»&

Если возникает необходимость искать по нескольким столбцам одновременно, то необходимо делать составной ключ для поиска. Если бы возвращаемое значение было не текстовым (как тут в случае с полем «Код»), а числовым, то для этого подошла бы более удобная формула СУММЕСЛИМН (SUMIFS) и составной ключ столбца не потребовался бы вовсе.

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

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