Совет: Попробуйте использовать новую функцию ПРОСМОТРX , улучшенную версию функции ВЛОП, которая работает в любом направлении и по умолчанию возвращает точные совпадения, что упрощает и удобнее в использовании, чем предшественницу.
Если вам нужно найти что-то в таблице или диапазоне по строкам, используйте В ПРОСМОТР. Например, можно найти цену автомобильной части по номеру части или имя сотрудника на основе его ИД.
Самая простая функция ВПР означает следующее:
=В.ПРОСМОТР(то, что вы хотите найти, где ее искать; номер столбца в диапазоне, содержащего возвращаемую величину, возвращает приблизительное или точное совпадение, обозначенные как 1/ИСТИНА или 0/ЛОЖЬ).
Совет: Секрет функции ВПР состоит в организации данных таким образом, чтобы искомое значение (Фрукт) отображалось слева от возвращаемого значения, которое нужно найти (Количество).
Используйте функцию ВПР для поиска значения в таблице.
Значение для поиска. Иного значения должно быть в первом столбце диапазона ячеек, который указан в 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 — вторым и так далее.
При желании вы можете указать слово ИСТИНА, если вам достаточно приблизительного совпадения, или слово ЛОЖЬ, если вам требуется точное совпадение возвращаемого значения. Если вы ничего не указываете, по умолчанию всегда подразумевается вариант ИСТИНА, то есть приблизительное совпадение.
Теперь объедините все перечисленное выше аргументы следующим образом:
=ВLOOKUP(искомые значения, диапазон, содержащий искомые значения, номер столбца в диапазоне, содержащий возвращаемую величину, приблизительное совпадение (ИСТИНА) или Точное совпадение (ЛОЖЬ)).
Примеры
Вот несколько примеров использования функции ВПР.
Пример 1
Пример 2
Пример 3
Пример 4
Пример 5
Функцию ВЛОП можно использовать для объединения нескольких таблиц в одну, если одна из таблиц имеет поля, общие для всех остальных. Это особенно полезно, если вам нужно поделиться книгой с людьми, у которых есть более старые версии Excel, которые не поддерживают функции данных с несколькими таблицами в качестве источников данных, путем объединения источников в одну таблицу и изменения источника данных функции данных в новую таблицу, функцию данных можно использовать в более старых версиях Excel (при условии, что функция данных поддерживается более старой версией).
Здесь столбцы A–F и H имеют значения или формулы, которые используют только значения на этом сайте, а остальные столбцы используют В ПРОСМОТР и значения столбцов A (код клиента) и B (Доверенность) для получения данных из других таблиц.
Скопируйте таблицу с общими полями на новый и придать ей имя.
Чтобы открыть диалоговое окно Управление отношениями, > в > управления отношениями нажмите кнопку Data > Data Tools (Управление отношениями).
Для каждой из указанных связей обратите внимание на следующее:
Поле, которое связывает таблицы (в скобки в диалоговом окне). Это первый lookup_value для формулы ВЛКП.
Имя связанной таблицы подсмотра. Это первый table_array в формуле ВЛИО.
Поле (столбец) в связанной таблице подытовки с данными, которые должны быть в новом столбце. Эта информация не отображается в диалоговом оке Управление связями. Чтобы узнать, какое поле нужно извлечь, необходимо посмотреть в связанной таблице подыска. Обратите внимание на номер столбца (A=1) — это col_index_num формуле.
Чтобы добавить поле в новую таблицу, введите формулу в первом пустом столбце СРОТ, используя сведения, собранные на шаге 3.
В нашем примере в столбце G для получения данных «Ставка выставления счета» из четвертого столбца (col_index_num = 4) из таблицы «Доверенности» используется столбец «Доверенность» lookup_value(table_array) с формулой =ВЛП([@Attorney];tbl_Attorneys;4;ЛОЖЬ).
В формуле также можно использовать ссылку на ячейку и ссылку на диапазон. В нашем примере это будет =ВЛВП(A2;’Поверенные’! A:D,4;ЛОЖЬ).
Продолжайте добавлять поля, пока не будут добавлены все необходимые поля. Если вы пытаетесь подготовить книгу, содержащую функции данных, которые используют несколько таблиц, измените источник данных функции на новую таблицу.
Источник
Функция ВПР в Excel: пошаговая инструкция с 5 примерами
ВПР — это функция Excel для поиска и извлечения данных из определенного столбца в таблице. Она поддерживает приблизительное и точное сопоставление, а также подстановочные знаки (* и ?). Значения поиска должны отображаться в первом столбце таблицы, а столбцы поиска находятся правее.
Давайте теперь рассмотрим, как сделать поиск с ВПР и как она работает. Рассмотрим приемы ее применения в формулах Excel.
Как сделать ВПР в Excel: понятная пошаговая инструкция.
Для начала на простом примере разберем, как работает функция ВПР в Excel. Предположим, у нас есть две таблицы. Первая – это прайс-лист с наименованиями и ценами. Вторая – это заказ на покупку некоторых из этих товаров. Искать в прайс листе нужный товар и руками вписывать в заказ его цену – занятие очень утомительное. Ведь прайс с ценами может насчитывать сотни строк. Нам необходимо сделать всё автоматически.
Нам необходимо обнаружить интересующее нас наименование в первом столбце и возвратить (то есть показать в ответ на наш запрос) содержимое из желаемого столбца той же строки, где находится наименование.
Наш прайс-лист расположен в столбцах А и В. Список покупок – в E-H. Допустим, первая позиция в списке покупок – бананы. Нам нужно в столбце A, где указаны все наименования, найти этот товар, затем его цену поместить в ячейку G2.
Для этого в G2 запишем следующую формулу:
А теперь разберем подробно, как сделать ВПР.
Мы берем значение из E2.
Ищем точное совпадение (поскольку четвертым параметром указан 0) в диапазоне $A$2:$B$7 в первой его колонке (крайней левой). Обратите внимание, что лучше сразу же использовать абсолютные ссылки на прайс-лист, чтобы при копировании этой формулы ссылка не «соскользнула».
Если товар будет найден, то нужно перейти во второй столбец диапазона (на это указывает третий параметр = 2).
Взять из него цену и вставить ее в нашу ячейку G2.
Получилось? Теперь просто скопируйте формулу из G2 в G3:G8.
Отчет о продажах готов.
Также чтобы понять, что такое точное совпадение, попробуйте в A5 или в E2 изменить наименование товара. К примеру, добавьте пробел в конце. Внешне ничего не изменилось, но вы сразу же получите ошибку #Н/Д. То есть, товар не был обнаружен. В то же время, таких случайных ошибок можно легко избежать, о чем мы поговорим отдельно.
Особо остановимся на четвертом параметре. Мы указали ноль (можно было написать ЛОЖЬ), что означает «точный поиск». А что, если забыть его указать и закончить номером столбца, из которого извлекаются нужные данные?
Давайте еще раз шаг за шагом разберем, что в этом случае будет происходить.
К сожалению, «бананы» были в нашем прайс-листе ниже, но до них просто «не дошел ход». И в список покупок теперь записана неправильная цена.
При помощи этой инструкции мы рассмотрели только основы. А как реально этим можно пользоваться?
Как работает функция ВПР в Excel: несколько примеров для «чайников».
Предположим, нам необходимо из списка сотрудников выбрать данные определенного человека. Посмотрим, какие здесь есть тонкости.
Во-первых, нужно сразу определиться: точный либо приблизительный поиск нам нужен. Ведь они предъявляют разные требования к подготовке исходных данных.
Использование точного и приблизительного поиска.
Посмотрите, какие результаты выборки цен мы получаем с использованием приблизительного поиска на неупорядоченном массиве данных.
Обратите внимание, что четвертый параметр равен 1.
Кое-что из результатов определено верно, но в большинстве случаев – ошибки. Функция продолжает просматривать данные столбца D с наименованиями товаров до тех пор, пока не встретит значение больше, чем заданное ей в качестве критерия поиска. Тогда она останавливается и возвращает цену.
Поиск цены на египетские бананы закончился на первой же позиции, так как во второй записаны сливы. А это слово по правилам алфавита стоит ниже, чем «Бананы Египет». Значит, дальше искать не нужно. Получили 145. И не важно, что это цена абрикосов. Поиск цены на сливы происходил до тех пор, пока в D15 не встретилось слово, которое по алфавиту стоит ниже: яблоки. Остановились и взяли цену из предыдущей строки.
А теперь взгляните, как должно было все происходить, если все делать верно. Всего лишь делаем сортировку, как указано стрелкой.
Вы спросите: «А зачем тогда этот неточный просмотр, если с ним столько проблем?»
Он отлично подходит для выбора значений из определенных интервалов.
Допустим, у нас установлена скидка для покупателей в зависимости от количества приобретенного товара. Нужно быстро рассчитать, сколько процентов положено на совершенную покупку.
Если у нас количество товара 11 единиц, то мы просматриваем столбец D до тех пор, пока не встретим число, большее 11. Это 20 и находится оно в 4-й строке. Останавливаемся здесь. Значит, наша скидка расположена в 3-й строке и равна 3%.
При работе с интервалами вида «от – до» такая методика вполне пригодна.
И еще один небольшой совет.
Применяйте именованный диапазон.
Для упрощения работы с формулами можно создать именованный диапазон и в дальнейшем ссылаться на него. В нашем случае назовем его «ДанныеСотрудников» (помните, что пробелы здесь недопустимы).
В ячейке B2 мы будем вводить нужную фамилию, а в ячейках С2:F2 запишем формулы:
Как видите, отличаются они только номером столбца, из которого будет извлечена нужная информация. Вместо ЛОЖЬ можно использовать 0.
Какие здесь преимущества?
У вас не рябит в глазах от букв, цифр и знаков доллара в обычных адресах диапазонов?
Формула с именованным диапазоном выглядит намного более дружественно, наглядно и понятно. Вместо скучных и безликих координат вы видите идентификаторы, которые рождают у вас некоторые ассоциации. Согласитесь, “price” или «цена» – это наверняка информация о ценах.
Если по каким-то причинам вам необходимо будет изменить координаты диапазона поиска, который вы использовали в большом количестве формул – вам нужно корректировать каждую формулу или пользоваться функцией “Найти и заменить”? Согласитесь, это очень долго, трудоемко, возможны ошибки.
Используя именованный диапазон, просто нажмите
Меню – Формула – Диспетчер имён.
Затем в списке диапазонов найдите нужный вам диапазон и откорректируйте его. Изменения автоматически будут применены во всех формулах.
Используя обычные адреса, мы всегда должны думать, какую адресацию применить – относительную либо абсолютную. При использовании именованных диапазонов этой проблемы не существует.
Использование символов подстановки и другие тонкости критерия поиска.
Как и в предыдущих примерах, при вводе фамилии происходит точный поиск. Но есть несколько моментов, о которых мы ранее не упоминали.
Регистр символов не влияет на результат. Можно все вводить прописными буквами – ничего не изменится. Пример вы видите чуть ниже.
Если в списке есть люди с одинаковыми фамилиями, то найден будет только первый из них. Как и мы говорили ранее, как только найдено что-то подходящее, процесс останавливается.
Можно использовать символы подстановки * и ?. Напомню, что вопросительный знак заменяет собой любой символ, а звездочка – любое количество символов (в том числе и ноль). О них мы упоминали в начале.
Это целесообразно делать, если мы знаем только часть значения аргумента.
Но при этом будьте внимательны – снова будет найдено только первое подходящее совпадение, как это показано на скриншоте. Это очень важное ограничение, которое нужно обязательно учитывать.
Теперь давайте посмотрим, как можно работать с символами подстановки, если условия отбора не вводятся вручную, а берутся из таблицы Excel.
Формула в ячейке F2 выглядит следующим образом:
Здесь мы используем оператор «склеивания» строк &.
Конструкция «*»&D2&»*» означает, что к содержимому ячейки D2 добавляются с обоих сторон звездочки *. То есть, мы ищем любое вхождение этого слова – перед ним и после него могут быть любые другие слова и символы. Как, например, произошло с товаром «персики». Первый параметр будет в нашем случае выглядеть как «*персики*». При поиске такой конструкции приемлемым вариантом будут определены «Консервированные персики (Турция)».
Использование нескольких условий.
Еще один простой пример для «чайников» — как использовать при выборе нужного значения несколько условий?
Предположим, у нас есть список имен и фамилий. Нам нужно найти нужного человека и вывести сумму его дохода.
В F2 используем следующую формулу:
Разберем пошагово, как в этом случае работает ВПР.
В начале мы формируем условие. Для этого при помощи оператора & «склеиваем» вместе имя и фамилию, а между ними вставляем пробел.
Не забываем при этом пробел заключить в кавычки, иначе Excel не воспримет его как текст.
Затем в таблице с доходами ищем ячейку с именем и фамилией, разделенными пробелом.
Дальше все происходит по уже отработанной схеме.
Можно попробовать подстраховаться на тот случай, если между именем и фамилией введено несколько пробелов. Знак пробела в формуле заменяем на знак подстановки «*».
Приметно так — D2&»*»&E2
Но при этом имейте в виду, что совпадение имени и фамилии уже будет не совсем точным. Подобный пример мы рассматривали чуть выше.
Более сложные и точные способы работы с несколькими условиями мы рассмотрим отдельно. Смотрите ссылки в конце.
«Умная» таблица.
И еще одна рекомендация: используйте «умную» таблицу.
Бывает очень удобно сначала преобразовать поисковую таблицу (прайс-лист) в «умную» с помощью команды Главная – Форматировать как таблицу (Home – Format as Table в английской версии Excel), и затем указать во втором аргументе использовать имя созданной таблицы. Кстати, оно ей будет присвоено автоматически.
В этом случае размеры списка товаров с ценами нас уже не будут беспокоить в будущем. При добавлении новых товаров в прайс-лист, либо их удалении, размеры «умной таблицы» сами подстроятся.
Специальные инструменты для ВПР в Excel.
Несомненно, ВПР — одна из самых мощных и полезных функций Excel, но она также одна из самых запутанных. Чтобы сделать работу с ней проще, можно использовать надстройку Ultimate Suite for Excel с инструментом «Мастер ВПР», позволяющим значительно сэкономить время на поиск нужных данных.
Мастер ВПР — простой способ писать сложные формулы
Интерактивный мастер ВПР проведет вас через необходимые параметры конфигурации поиска, чтобы построить идеальную формулу для заданных вами критериев. В зависимости от вашей структуры данных он будет использовать стандартную функцию ВПР или формулу ИНДЕКС+ПОИСКПОЗ, если будет нужно извлечь значения слева от столбца поиска.
Вот что вам нужно сделать, чтобы получить формулу под вашу задачу:
Запустите мастер кнопкой Vlookup Wizard на ленте Ablebits Data.
Выберите свою основную таблицу (Your table) и таблицу поиска (Lookup table).
Укажите следующие столбцы (во многих случаях они выбираются автоматически):
Ключевой столбец (Key column) — находится в вашей основной таблице, содержит значения для поиска.
Столбец поиска (Lookup column) — в кот ором будем искать.
Возвращаемый столбец (Return column) — из него будем получать значения.
Нажмите кнопку Вставить (Insert).
Давайте посмотрим всё в действии.
Стандартный ВПР.
Запускаем мастер Vlookup Wizard. Указываем координаты основной таблицы и таблицы поиска, а также ключевой столбец (из которого будем брать значения для поиска), колонку поиска (в котором будем их искать) и колонку результата (из него в случае успеха берем соответствующее значение и вставляем в основную таблицу). Просто заполняем все нужные поля, как это показано на рисунке ниже. Руками прописываем (или обозначаем при помощи мышки) только диапазоны. Поля же просто выбираем из раскрывающегося списка.
Как и в предыдущих примерах, наша задача — подобрать цену для каждого товара, извлекая ее из прайс-листа. Ежели область поиска (Цена) является крайним левым столбцом в зоне поиска, то вставляется обычная формула ВПР для точного соответствия:
Ничего руками писать не нужно.
После нажатия кнопки Insert справа от колонки с наименованиями товаров будет вставлена дополнительная, которая будет озаглавлена так же, как и столбец результата. Сюда будут записаны все найденные значения цены, причем в виде формулы. При необходимости вы сможете ее подправить либо использовать в других таблицах.
«Левый» ВПР.
Когда колонка результата (Цена) находится слева от области поиска (Прайс), то мастер автоматически вставляет формулу ИНДЕКС+ПОИСКПОЗ:
Мы рассматривали левый ВПР в отдельной статье. Там же вы можете посмотреть и формулы для ручного ввода. Здесь же мы получаем их автоматически, не вникая в тонкости синтаксиса и правильности написания.
Дополнительный бонус! Благодаря грамотному использованию ссылок на ячейки, полученные формулы ВПР можно копировать или перемещать в любой столбец без необходимости обновлять ссылки.
Надеемся, что наши пошаговые инструкции по использованию функции ВПР в таблицах Excel были доступны и понятны даже «чайникам». Безусловно, эти самые несложные рекомендации можно использовать только в самых простых случаях. Все более сложное мы рассмотрим отдельно.
