Выполняем поиск в Excel с помощью функции ВПР

Элен Брэдли объясняет тонкости работы с функцией ВПР в Microsoft Excel при поиске данных в таблице.

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

• Основы
• Синтаксис функции ВПР
• ВПР в действии
• Работа с точными совпадениями
• Используем проверку данных
• Сортируем данные

Основы

В Microsoft Excel есть несколько функций поиска, среди них VLOOKUP (ВПР), очень похожая на нее функция HLOOKUP (ГПР) и LOOKUP (ПРОСМОТР). Функция ВПР используется для того, чтобы искать данные в таблице. Она выполняет поиск искомого значения по первому столбцу таблицы и возвращает соответствующее значение из другого столбца.

Когда данные расположены по-другому, используйте ГПР, чтобы найти нужное значение в верхней строке таблицы и возвратить соответствующее значение из заданной строки, расположенной ниже. Функция ПРОСМОТР имеет две формы – векторную и массива, и может возвращать значение из одного столбца, одной строки или из массива (аналог ВПР и ГПР). Из этих трёх функций, вероятнее всего, Вы будете использовать ВПР гораздо чаще, чем остальные. Именно на ней я сфокусируюсь в этой статье. В целом, если Вы поймете и сможете применить функцию ВПР, то сможете справиться и с ГПР.

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

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

Когда Вы указываете диапазон таблицы, Excel ищет указанное Вами искомое значение в первом столбце этого диапазона. Как правило, это заголовки строк Ваших данных. Чтобы указать номер столбца, Вам достаточно указать его порядковый номер в заданном диапазоне. Например, 1 – это первый столбец диапазона, 2 – это следующий за ним вправо и так далее. Если Вы укажете номер, выходящий за границы заданного диапазона, например, меньше 1 или больше количества столбцов в диапазоне, получите сообщение об ошибке.

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

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

ВПР в действии

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

Чтобы для значения из ячейки B11, которое является весом партии товара в фунтах (в данном случае 1.5 фунта), возвратить стоимость обработки из столбца Handling, воспользуемся вот такой формулой:

=VLOOKUP(B11,D2:F7,2)
=ВПР(B11;D2:F7;2)

Формула возвращает стоимость равную $4, т.е. значение из 2-го столбца таблицы, находящееся напротив веса, который ближе всего к искомому, но меньше его.

Если Вы хотите скопировать формулу вниз по столбцу, не забудьте указать в ней абсолютные ссылки вот таким образом:

=VLOOKUP(B11,$D$2:$F$7,2)
=ВПР(B11;$D$2:$F$7;2)

Кроме этого, Вы можете присвоить своей таблице имя, для этого надо выделить ячейки от D1 до F7 и нажать Formulas (Формулы) > Define Name (Присвоить имя), затем ввести имя диапазона и нажать ОК. В нашем примере это имя shipping_and_handling.

Теперь при создании формулы Вы можете использовать имя диапазона. Вот здесь вместо адреса диапазона указано его имя:

=VLOOKUP(B12,shipping_and_handling,2)
=ВПР(B12; shipping_and_handling;2)

Мы можем адаптировать формулу из столбца Handling для расчёта значений в столбце Shipping. В данном случае поменяется только номер столбца. Для Shipping – это значение 3:

=VLOOKUP(B12,shipping_and_handling,3)
=ВПР(B12;shipping_and_handling;3)

Функция ГПР работает точно таким же образом. Точнее говоря, она также использует искомое значение и диапазон данных, но вместо номера столбца Вы задаете ей номер строки. Строки нумеруются 1, 2, 3 и так далее, где 1 – самая первая строка таблицы.

Используя предыдущий пример, мы сможем найти соответствия для весов в 11, 12 или 25 фунтов, хоть они и отсутствуют в таблице. Возможность находить ближайшее значение, которое меньше искомого, выглядит очень привлекательной. Однако, существуют некоторые оговорки при использовании этой формулы. Одна из них – стартовое значение в таблице должно быть равно 0, что собственно и сделано у нас. Это позволяет исключить ошибки, когда используется вес, к примеру, меньше 1 фунта.

Работа с точными совпадениями

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

В данном примере мы ищем название в столбце A и возвращаем цену из столбца 2 или 3, в зависимости от указанного размера ковра – средний (M) или большой (L). В этой ситуации нам необходимо использовать функцию IF (ЕСЛИ), чтобы определить, какой номер столбца нужно использовать. Формула поиска будет выглядеть следующим образом:

=VLOOKUP(A7,A2:C4,IF(B7="M",2,3),FALSE)
=ВПР(A7;A2:C4;ЕСЛИ(B7="M";2;3);ЛОЖЬ)

В данном случае мы ищем название ковра в столбце A и возвращаем цену из столбца B или C, в зависимости от выбранного размера ковра. Если точное совпадение не найдено, т.е. название ковра в заказе не соответствует ни одному из названий в столбце A, тогда будет возвращено сообщение об ошибке #N/A (#Н/Д). Функция IF (ЕСЛИ) составлена так, что если заданный размер ковра не будет соответствовать одному из двух имеющихся вариантов, то по умолчанию будет взят большой размер (L).

Используем проверку данных

На практике желательно сделать так, чтобы пользователь гарантированно вводил правильное название ковра и его размер. Вы можете реализовать это, используя выпадающий список. Для этого выделите ячейки, в которые пользователь будет вводить свои заказы, например, столбец A или B. Перейдите Data > Data Validation > Data Validation (Данные > Проверка данных > Проверка данных). В появившемся диалоговом окне на вкладке Settings (Параметры) в поле Allow (Тип данных) выберите значение List (Список). Кликните в поле Source (Источник) и выделите ячейки от A2 до A4, в которых содержится список с названиями ковров. Нажмите ОК.

Таким же способом Вы можете создать выпадающий список для ввода размеров L или M, используя в качестве источника данных диапазон B1:C1.

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

Сортируем данные

Если Вы работаете с приблизительными совпадениями, то должны выполнить сортировку в таблице. Для этого выделите весь диапазон с данными, включая заголовки строк в первом столбце. Заголовки столбцов (шапку) можно не выделять. На вкладке Data (Данные) нажмите команду Sort (Сортировка), откроется одноименное диалоговое окно.

В строке Sort By (Сортировать по) укажите параметры сортировки. В первом выпадающем списке выберите столбец, по которому необходимо выполнить сортировку, в нашем случае это первый столбец таблицы. Во втором выберите Values (Значения), а в третьем укажите порядок сортировки по возрастанию. Если вместе с данными, Вы выделили шапку таблицы, не забудьте поставить галочку My data has headers (Мои данные содержат заголовки). Нажмите ОК.

Таблица с данными будет отсортирована так, что функция ВПР сможет работать с ней корректно.