что делает впр в excel
Использование функции ВПР в программе Excel
Во время работы в Эксель нередко требуется перенести или скопировать определенную информацию из одной таблицы в другую. Выполнить подобную процедуру, конечно же, можно вручную, когда речь идет о небольших объемах данных. Но что делать, если нужно обработать большие массивы данных? В программе Microsoft Excel на этот случай предусмотрена специальная функция ВПР, которая автоматически все сделает в считанные секунды. Давайте посмотрим, как это работает.
Описание функции ВПР
ВПР – это аббревиатура, которая расшифровывается как “функция вертикального просмотра”. Английское название функции – VLOOKUP.
С помощью данного инструмента Excel можно отыскать совпадение значения в заданном столбце и “подтянуть” необходимые данные в выбранную ячейку из другой таблицы. Чтобы было понятнее, давайте разберем, как работает функция на практическом примере.
Применение функции ВПР на практике
Допустим, у нас есть таблица с продажами товаров. В ней отсутствуют данные по цене каждой позиции, однако, они есть в другой таблице рядом.
Наша задача – заполнить недостающие в основной таблице цены, чтобы мы могли посчитать итоговые суммы по продажам.
Порядок действий в данном случае следующий:
Заключение
Таким образом, функция ВПР – один из самых полезных инструментов при работе в Excel, когда нужно сопоставить данные двух таблиц и перенести значения из одной в другую при совпадении заданных значений. Правильное использование данной функции позволит сэкономить немало времени и сил, позволив автоматизировать процесс заполнения данных и исключив возможные ошибки из-за опечаток и т.д.
Excel-plus
Пошаговые инструкции, как использовать функции, формулы и другие инструменты табличного редактора Microsoft Excel
Функция ВПР в MS Excel. Описание и примеры использования.
Функция ВПР в MS Excel. Описание и примеры использования.
В данной статье, на простых примерах, описаны варианты использования функции ВПР MS Excel. Важные аспекты и возможные ошибки, которые возникают при использование данной функции. Функция ВПР в Excel.
Как вызвать функцию ВПР. Функция ВПР в Excel
В первую очередь разберемся, как вызвать данную функцию. Выбираем закладку Формулы. Находим кнопку Вставить функцию. И нажимаем ее. Так же, можно вызвать функцию ВПР, сочетанием клавиш Shift + F3.
Появляется диалоговое окно Вставка функции. В строке Поиск функции вводим ВПР. Нажимаем найти. По результатам поиска, в пункте Выберите функцию, появляется ВПР. Нажимаем на нее левой кнопкой мыши два раза или нажимаем ОК. Появляется непосредственно диалоговое окно функции ВПР – Аргументы функции.
Теперь перейдем непосредственно к вариантам применения функции ВПР.
Первый вариант использования функции ВПР.
Для примера возьмем две таблице. В одной Таблице №1 будет перечень с названиями конфет и будет указана их цена за кг. В другой, Таблица №2, тот же перечень, но с указанием их количества в кг. Наша задача добавить в Таблицу №2, в столбец Цена, цену конфет из Таблицы №1, чтобы в итоге получить стоимость. Названия конфет в разных таблицах находятся в разных местах, поэтому просто скопировать цену конфет с одной таблице в другую не получиться.
Перед тем, как вызвать функцию ВПР, выбираем нужную нам ячейку, в которой будет находиться наша формула функции и соответственно значение, которое мы хотим увидеть. В нашем случае это ячейка G3. Эта ячейка находиться в столбце Цена, Таблица №2. Функция ВПР позволит взять из Таблицы №1 цену Конфеты А и вставить эту цену в столбец Цена, Таблицы №2, напротив Конфеты А.
Вызываем функцию ВПР, как описано выше.
Аргументы функции. Функция ВПР в Excel.
Искомое_значение.
Значение поиска, которое должно быть найдена в указанном нами диапазоне, в строке Таблица. В нашем примере мы указываем Конфеты Ж (ячейка Е3, Таблица №2). Так как это значение идет первое в столбце Название конфет, Таблица №2. (Это не принципиально, но удобно). Это значение, которое будет искать наша функция в Таблице №1.
Что бы выбрать нужную нам ячейку с значением, достаточно просто стать курсором в строку Искомое_значение, а потом клацнуть левой кнопкой мыши, по нужной ячейке в таблице ( В нашем примере ячейка Е3).
Таблица.
Можно присвоить нашему диапазону имя, и прописать его в строке Таблица.
Как это сделать. Выбираем нужный нам диапазон. Таблица №1. Выбираем закладку Формулы, кнопка Задать имя. Нажимаем. Появляется диалоговое окно Создание имени. Пишем любое имя. Но нужно его запомнить. Например Конфеты. Нажимаем ОК.
В строке Таблица, вместо диапазона нужно будет ввести имя, которое мы присвоили – Конфеты 
Номер_столбца.
Функция ВПР осуществляет поиск значение в самом левом столбце таблицы указанного диапазона поиска. Функция присваивает этому столбцу номер 1, по умолчанию. В нашем примере самый левый столбце, это Название конфет в Таблице №1. А в строке Номер_столбца, нам нужно указать, какой номер по порядку имеет столбец, из которого нужно перенести данные. В нашем примере это столбце Цена в Таблице №1. Он «второй» по порядку, если считать слева на право, от столбца Название конфет, Таблица №1. Поэтому в строку Номер_столбца мы пишем цифру 2. Если бы столбец Цена, Таблица №1 был бы расположен по порядку не на втором месте, а предположим на десятом, то мы соответственно указывали бы в строке Номер_столбца цифру 10.
Интервальный _просмотр.
В этой строке мы пишем цифру ноль «0». Это значит, что функция ВПР будет осуществлять поиск точных совпадений между значениями поиска (Искомое_значение) и значениями в крайнем левом столбце диапазона поиска (Таблица). В нашем примере поиск точных совпадений будет происходить между столбцом Название конфет, Таблица №1, и столбцом Название конфет в Таблице №2.
Если мы поставим цифру один «1», функция будет осуществлять поиск не точного совпадения, а приближенного к нашему критерию поиска.
Вот как это выглядит все вместе.
Протягиваем формулу по всему столбцу Цена в Таблице №2. Все цены перенесены с Таблице №1 в Таблицу №2.
Второй вариант использования функции ВПР.
У нас есть Таблица №1 и Таблица №2. Каждая таблица состоит из одного столбца. Для понимания алгоритма работы функции ВПР, в данном случае, таких простых таблиц достаточно. Столбцы содержат практически одинаковые данные. При этом, нам нужно сравнить их и узнать, какие данные есть в Таблице №2, но нет в Таблице №1.
Справа от Таблицы 2, в ячейку G3, вставляем функцию ВПР. Это расположение взято в качестве примера, можно использовать любой другой столбец и оформление.
В диалоговом окне, Аргументы функции прописываем следующие данные:
Искомое_значение. Это значение ячейки из Таблицы №2, наличие которой мы проверяем в Таблице №1. В нашем примере, это ячейка F3 (Значение 9).
Таблица. В данном случае мы указываем не диапазон всей таблицы, а только диапазон конкретного столбца, который мы сравниваем. Можно выделять столбец в таблице. А можно выделять весь столбец листа. В том случае, если в нем больше нет других данных. Вместо диапазона можно указать заданное имя столбца (Задаем имя).
Номер_столбца. Поскольку в таблице указан конкретный столбец, то здесь мы ставим цифру 1, так как искомое значение и данные, которые мы хотим перенести совпадают.
Интервальный _просмотр. Здесь ставим ноль «0», так как хотим, чтобы функция искала точные совпадения.
Нажимаем ОК и протягиваем функцию по всему столбцу. В некоторых ячейках, вместо искомого значения появляется ошибка формулы: #Н/Д. В данном случае, это значит, что заданные критерии поиска не были обнаружены в проверяемом столбце.
В нашем примере, в Таблице №2 есть Значение 17 и Значение 10. При проверке, в ячейки с функцией ВПР, вместо искомого значения появилась ошибка #Н/Д. Это значит, что в Таблице №1 нет ячейки с Значением 17 и Значением 10.
Можно проверить с точностью наоборот. И найти какие данные есть в Таблице №1 но нет в Таблице № 2.
Обратите внимание. Функция ВПР в Excel.
Функция ВПР осуществляет поиск значений (это значения, которые указаны в строке Искомое_значение) в первом (самом левом) столбец таблицы, диапазон которой указан в строке Таблица.
Пример, в Таблицу №1, добавили столбец Категория, и теперь столбец Название конфет уже не первый, а второй. Если мы укажем в строке Таблица, в качестве диапазона, все ячейки Таблицы №1, то функция ВПР не сработает (ошибка — #Н/Д), так как она будет осуществлять точный поиск в столбце Категория, Таблицы №1, значений из столбца Название конфет, Таблицы №2. И не найдет точных совпадений.
Это особенность, так же говорит нам о том, что значения, которые мы хотим перенести в нашу таблицу, должны быть справа, от столбца, в котором происходит поиск по заданному критерию.
Если в диапазоне Таблица указан один, конкретный столбец, то функция ВПР проверяет только его. И данное правило не обязательно.
Можно осуществлять поиск на разных Листах. Алгоритм работы такой же. Формула функции будет выгладить вот так: =ВПР(E6;Лист1!$B$1:$C$11;2;0). В нашем примере формулы функция ВПР расположена на Листе 2, а поиск значения и перенос данных с диапазона поиска происходит на Листе 1. Вместо диапазона можно использовать Заданное имя. Например Конфеты. Тогда формула функции будет выглядеть вот так: =ВПР(E6;Конфеты;2;0).
Возможные ошибки.
#Н/Д — столбец таблицы, по которому происходит поиск, не крайний левый в диапазон поиска.
#Н/Д — диапазон таблицы, в которой происходит поиск, не закреплен. Нужно использовать либо абсолютные ссылки ($), либо присвоить диапазону Заданное имя.
#Н/Д – функция ВПР не находить точного совпадения в диапазоне поиска по заданному значению поиска.
#Н/Д – возможно необходимо отсортировать диапазон, в котором происходит поиск, по возрастанию.
#ССЫЛКА! – возможно номер столбца, который указан в строке Номер_столбца, указан неверно, и функция не находит данные, которые должна перенести.
Функция ВПР в Excel: пошаговая инструкция с 5 примерами
Давайте теперь рассмотрим, как сделать поиск с ВПР и как она работает. Рассмотрим приемы ее применения в формулах Excel.
Как сделать ВПР в Excel: понятная пошаговая инструкция.
Для начала на простом примере разберем, как работает функция ВПР в Excel. Предположим, у нас есть две таблицы. Первая – это прайс-лист с наименованиями и ценами. Вторая – это заказ на покупку некоторых из этих товаров. Искать в прайс листе нужный товар и руками вписывать в заказ его цену – занятие очень утомительное. Ведь прайс с ценами может насчитывать сотни строк. Нам необходимо сделать всё автоматически.
Нам необходимо обнаружить интересующее нас наименование в первом столбце и возвратить (то есть показать в ответ на наш запрос) содержимое из желаемого столбца той же строки, где находится наименование.
Наш прайс-лист расположен в столбцах А и В. Список покупок – в E-H. Допустим, первая позиция в списке покупок – бананы. Нам нужно в столбце A, где указаны все наименования, найти этот товар, затем его цену поместить в ячейку G2.
Для этого в 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 не воспримет его как текст.
Затем в таблице с доходами ищем ячейку с именем и фамилией, разделенными пробелом.
Дальше все происходит по уже отработанной схеме.
Можно попробовать подстраховаться на тот случай, если между именем и фамилией введено несколько пробелов. Знак пробела в формуле заменяем на знак подстановки «*».
Но при этом имейте в виду, что совпадение имени и фамилии уже будет не совсем точным. Подобный пример мы рассматривали чуть выше.
Более сложные и точные способы работы с несколькими условиями мы рассмотрим отдельно. Смотрите ссылки в конце.
«Умная» таблица.
И еще одна рекомендация: используйте «умную» таблицу.
Бывает очень удобно сначала преобразовать поисковую таблицу (прайс-лист) в «умную» с помощью команды Главная – Форматировать как таблицу (Home – Format as Table в английской версии Excel), и затем указать во втором аргументе использовать имя созданной таблицы. Кстати, оно ей будет присвоено автоматически.
В этом случае размеры списка товаров с ценами нас уже не будут беспокоить в будущем. При добавлении новых товаров в прайс-лист, либо их удалении, размеры «умной таблицы» сами подстроятся.
Специальные инструменты для ВПР в Excel.
Интерактивный мастер ВПР проведет вас через необходимые параметры конфигурации поиска, чтобы построить идеальную формулу для заданных вами критериев. В зависимости от вашей структуры данных он будет использовать стандартную функцию ВПР или формулу ИНДЕКС+ПОИСКПОЗ, если будет нужно извлечь значения слева от столбца поиска.
Вот что вам нужно сделать, чтобы получить формулу под вашу задачу:
Давайте посмотрим всё в действии.
Стандартный ВПР.
Запускаем мастер Vlookup Wizard. Указываем координаты основной таблицы и таблицы поиска, а также ключевой столбец (из которого будем брать значения для поиска), колонку поиска (в котором будем их искать) и колонку результата (из него в случае успеха берем соответствующее значение и вставляем в основную таблицу). Просто заполняем все нужные поля, как это показано на рисунке ниже. Руками прописываем (или обозначаем при помощи мышки) только диапазоны. Поля же просто выбираем из раскрывающегося списка.
Как и в предыдущих примерах, наша задача — подобрать цену для каждого товара, извлекая ее из прайс-листа. Ежели область поиска (Цена) является крайним левым столбцом в зоне поиска, то вставляется обычная формула ВПР для точного соответствия:
Ничего руками писать не нужно.
После нажатия кнопки Insert справа от колонки с наименованиями товаров будет вставлена дополнительная, которая будет озаглавлена так же, как и столбец результата. Сюда будут записаны все найденные значения цены, причем в виде формулы. При необходимости вы сможете ее подправить либо использовать в других таблицах.
«Левый» ВПР.
Когда колонка результата (Цена) находится слева от области поиска (Прайс), то мастер автоматически вставляет формулу ИНДЕКС+ПОИСКПОЗ:
Мы рассматривали левый ВПР в отдельной статье. Там же вы можете посмотреть и формулы для ручного ввода. Здесь же мы получаем их автоматически, не вникая в тонкости синтаксиса и правильности написания.
Дополнительный бонус! Благодаря грамотному использованию ссылок на ячейки, полученные формулы ВПР можно копировать или перемещать в любой столбец без необходимости обновлять ссылки.
Надеемся, что наши пошаговые инструкции по использованию функции ВПР в таблицах Excel были доступны и понятны даже «чайникам». Безусловно, эти самые несложные рекомендации можно использовать только в самых простых случаях. Все более сложное мы рассмотрим отдельно.
Функция ВПР в Excel. Как использовать?
Функция ВПР (VLOOKUP) в Excel используется для поиска данных из одной таблицы и сопоставления их с данными из другой таблицы.
Кликните по кнопке ниже для загрузки Excel файла с примерами работы функции ВПР :
Видео-урок: Как использовать функцию ВПР в Excel
Функция ВПР в Excel для чайников
На примере ниже, в таблице справа, мы хотим подставить данные результатов экзамена по математике для студента Сидорова. Нам известны данные по всем студентам из таблицы слева. Используя формулу, мы можем подставить необходимые для нас данные.
Синтаксис
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]) – английская версия
=ВПР(искомое_значение, таблица, номер_столбца, [интервальный_просмотр]) – русская версия
Аргументы функции
Дополнительная информация
Примеры использования ВПР в Excel
Пример 1. Ищем результат экзамена для студента
На примере ниже, в левой колонке таблицы указан список студентов. В правой части таблицы указаны итоговые баллы по экзаменам этих студентов.
Перед нами стоит задача узнать, какой балл получил студент Петров по Физике.
Узнать это мы можем с помощью формулы ниже:
=VLOOKUP(“Петров”,$A$3:$E$10,3,0) – английская версия
=ВПР(“Петров”;$A$3:$E$10;3;0) – русская версия
Формула выше состоит из четырех аргументов:
Ниже, наглядно изображено как работает по примеру наша функция.
Сначала, она ищет в крайней левой колонке диапазона данных фамилию “Петров”. Поиск происходит сверху вниз.
Как только Excel нашел точное совпадение “Петров” в диапазоне данных, система “шагает” в право, на третью колонку для отображения искомых данных.
Используя этот подход вы можете получить любые данные по каждому студенту и учебному предмету.
Например, чтобы найти результаты экзамена для студента Пескова по Химии, нам нужна формула:
=VLOOKUP(“Песков”, $A$20:$E$28,4,0) – английская версия
=ВПР(“Песков”;$A$20:$E$28;4;0) – русская версия
В приведенном выше примере фамилия студента указана в двойных кавычках. Также, можно использовать ссылку на ячейку с искомым значением. Ссылка на ячейку делает формулу динамической.
Например, если у вас есть ячейка с фамилией, и вы ищете оценку по математике, результат будет автоматически обновляться при изменении имени учащегося (как показано ниже):
Если в качестве аргумента поиска будет введена фамилия студента, которой нет в диапазоне данных, функция выдаст ошибку.
Пример 2. Двухфакторный поиск данных
На примере выше мы указывали вручную в качестве атрибута “номер_столбца” порядковый номер колонки с данными.
Но что, если мы хотим сделать поиск динамическим? Что если, мы хотим вводить имя студента и название предмета и функция автоматически выведет нужные данные? Для этого нам потребуется использовать двух факторный поиск.
Ниже пример двух факторного поиска:
Чтобы сделать двух факторную формулу поиска, важно сделать выбор данных из колонок динамическим. Так, когда пользователь меняет название предмета: Физика, Математика и т.д., в формуле изменяется соответствующий атрибут столбца с данными.
Чтобы сделать это, нам потребуется функция MATCH (ПОИСКПОЗ) в качестве аргумента отвечающего за порядковый номер колонки.
Так будет выглядеть формула для двухфакторного поиска:
=VLOOKUP($G$5,$A$4:$E$12,MATCH($H$4,$A$3:$E$3,0),0) – английская версия
=ВПР($G$5;$A$4:$E$12;ПОИСКПОЗ($H$4;$A$3:$E$3;0);0) – русская версия
В формуле выше используется функция MATCH (ПОИСКПОЗ) , которая отвечает за порядковый номер колонки с данными. MATCH (ПОИСКПОЗ) принимает название учебного предмета как значение поиска (в ячейке “H4”) и возвращает его позицию в диапазоне ячеек “A2:E2”.
Если указать “Математика”, Excel вернет “2”, поскольку “Математика” находится в ячейке “B2” (вторая по счету в этом массиве).
Пример 3. Используем выпадающий список при двух факторном поиске
Используя способ из примера №2, нам требуется делать много ручной работы. Высок риск допустить ошибку и потратить много времени, особенно, если вы работаете с большим объемом данных.
Здесь лучше использовать выпадающие списки как значения для поиска (в нашем примере это фамилии студентов и учебные предметы).
Основываясь на тех данных, что вы выберете из выпадающего списка, функция автоматически выведет необходимые данные, как это указано на примере ниже:
Такой подход позволяет создать дашборд, с помощью которого вы легко сможете обрабатывать большие списки с данными.
Ниже мы рассмотрим, как сделать такую таблицу.
Для этого нам потребуется формула из Примера №2:
=VLOOKUP($G$5,$A$4:$E$12,MATCH($H$4,$A$3:$E$3,0),0) – английская версия
=ВПР($G$5;$A$4:$E$12;ПОИСКПОЗ($H$4;$A$3:$E$3;0);0) – русская версия
Данные поиска были преобразованы в выпадающие списки.
Чтобы сделать выпадающий список, нужно:
Теперь у вас появится раскрывающийся список в ячейке “G4”. Аналогично, вы можете создать его в “H3” для предметов.
Пример 4. Трех факторный поиск данных ВПР
Что такое трех факторный поиск?
В Примере 2 мы использовали одну таблицу с оценками для студентов по разным предметам. Это пример двухфакторного поиска, поскольку мы используем две переменные для получения оценки (фамилия студента и предмет).
Теперь предположим, что к концу года студент прошел три уровня экзаменов: «Вступительный», «Полугодовой» и «Финальный экзамен».
Трехсторонний поиск – это возможность получить отметки студента по предмету с определенным уровнем экзамена.
Вот пример трехстороннего поиска:
В приведенном выше примере, функция ищет данные в трех разных таблицах с данными («Вступительный», «Полугодовой» и «Финальный экзамен») и возвращает значения оценок студента по определенному предмету по конкретному уровню экзамена.
Для таких расчетов нам поможет формула:
=VLOOKUP(G5,CHOOSE(IF(H3=”Вступительный”,1,IF(H3=”Полугодовой”,2,3)),$A$4:$E$12,$A$16:$E$24,$A$28:$E$36),MATCH(H4,$A$3:$E$3,0),0) – английская версия
=ВПР(G5;ВЫБОР(ЕСЛИ(H3=”Вступительный”;1;ЕСЛИ(H3=”Полугодовой”;2;3));$A$4:$E$12;$A$16:$E$24;$A$28:$E$36);ПОИСКПОЗ(H4;$A$3:$E$3;0);0) – русская версия
Эта формула использует функцию CHOOSE (ВЫБОР), чтобы убедиться, что данные выбраны из правильной таблицы. Давайте проанализируем часть формулы CHOOSE (ВЫБОР):
CHOOSE(IF(H3=”Вступительный”,1,IF(H3=”Полугодовой”,2,3)),$A$4:$E$12,$A$16:$E$24,$A$28:$E$36),MATCH(H4,$A$3:$E$3,0),0) – английская версия
ВЫБОР(ЕСЛИ(H3=”Вступительный”;1;ЕСЛИ(H3=”Полугодовой”;2;3));$A$4:$E$12;$A$16:$E$24;$A$28:$E$36);ПОИСКПОЗ(H4;$A$3:$E$3;0);0) – русская версия
Такой подход делает массив таблиц динамическим и трех факторным.
Пример 5. Получаем последнее значение колонки с помощью функции VLOOKUP (ВПР)
С помощью ВПР вы можете вычислить последнее число из списка.
Я не думаю, что вам когда-нибудь понадобится какой-то расчет, в котором участвует такое большое число. Но с его помощью мы можем получить последнее число в списке.
Предположим, у вас есть набор данных (в диапазоне ячеек A1:A14), как показано ниже, и вы хотите получить число из последней ячейки в списке.
Для этого нам подойдет следующая формула:
=VLOOKUP(9.99999999999999E+307,$A$1:$A$14,TRUE) – английская версия
=ВПР(9.99999999999999E+307;$A$1:$A$14;ИСТИНА)
Обратите внимание, что в формуле выше используется приблизительная точность совпадения данных (несмотря на это нет необходимости упорядочивать список).
Вот как работает функция с приблизительной точностью совпадения данных. Она просматривает левую колонку сверху вниз.
В нашем примере работает третий сценарий.
Число 9.99999999999999E + 307 это самое большое число, которое можно использовать в Excel, когда оно используется как критерий поиска, тогда функция VLOOKUP (ВПР) возвращает последнее число из списка.
Таким же образом вы можете использовать этот принцип для возврата последнего текстового элемента из списка. Вот формула, которая может это сделать:
=VLOOKUP(“яяя”,$A$1:$A$14,1,TRUE) – английская версия
=ВПР(“яяя”;$A$1:$A$14;1;ИСТИНА) – русская версия
Здесь действует та же логика. Система просматривает все названия из списка. Так как “яяя” самое большое текстовое значение => результатом вычисления будет самое крайнее значение из списка данных.
Пример 6. Частичный поиск с использованием символов подстановочных знаков и ВПР
Символы подстановки в Excel могут быть полезными во многих ситуациях.
Частичный поиск необходим, когда вам нужно искать значение в списке, в котором нет точного соответствия искомым данным.
Например, у вас есть набор данных, как показано ниже, и вы хотите найти компанию “ABC” в списке, но в списке есть только “ABC Ltd” вместо “ABC”.
Вы не можете использовать “ABC” в качестве значения поиска, так как в столбце “A” нет точного соответствия. Приблизительное совпадение часто приводит к ошибочным результатам и требует сортировки списка в порядке возрастания.
Однако, вы можете использовать подстановочный знак в VLOOKUP (ВПР) для более точного соответствия данных.
Введите следующую формулу в ячейку “D2” и перетащите ее в другие ячейки:
=VLOOKUP(“*”&C2&”*”,$A$2:$A$8,1,FALSE) – английская версия
=ВПР(“*”&C2&”*”;$A$2:$A$8;1;ЛОЖЬ) – русская версия
В формуле, указанной выше мы добавили к значению поиска “звездочки” с двух сторон. Указывая такие “звездочки” вы даете Excel понять, что готовы осуществить поиск, при котором в искомых данных как в начале так и в конце значения могут быть любые другие слова, буквы или цифры.
Таким образом, осуществляя поиск, Excel будет понимать, что необходимо искать значение, которое содержит слово из ячейки “С2” (в нашем примере).
Например, в ячейке “C2” указано название компании “ABC”, поэтому функция просматривает значения в диапазоне данных “A2:A8” и ищет “ABC”. Она находит соответствие в ячейке “A2”, так как та содержит слово “ABC” в “ABC Ltd”. Не имеет значения, есть ли какие-либо символы слева или справа от “ABC”.
Примечание: ВПР всегда возвращает первое совпадающее значение и перестает искать дальше. Поэтому, если у вас есть название компании “ABC Ltd”. и “ABC Corporation” в списке, она вернет первый по порядку и проигнорирует остальные.
Пример 7. Функция ВПР возвращает ошибку, несмотря на совпадение в значении поиска
Это может свести вас с ума, когда вы увидите, что имеется соответствующее значение поиска, а функция возвращает ошибку.
Например, у вас в таблице есть очевидное соответствие (Иван), но система по-прежнему возвращает ошибку.
Рассматривая значения в таблице глубже, вы можете заметить, что в диапазоне данных значение “Иван “ написано с пробелом. Если в диапазоне данных по которым вы осуществляете поиск есть пробелы до значений, после или между словами, то функция их не сопоставит.
Решение есть, и зовут его – функция TRIM. Она удаляет все лишние пробелы в значениях.
Ниже формула, которая поможет вам сопоставить данные несмотря на лишние пробелы в диапазоне данных.
=VLOOKUP(“Иван”,TRIM($A$2:$A$15),1,0) – английская версия
=ВПР(“Иван”,СЖПРОБЕЛЫ($A$2:$A$15);1;0) – русская версия
Пример 8. Функция ВПР в Excel с несколькими условиями
Функция в своей базовой форме может искать одно значение поиска и возвращать соответствующие данные из указанного диапазона данных.
Но часто нам необходимо использовать VLOOKUP с поиском по несколькими критериям.
Представим, что у вас есть таблица с результатами экзаменов студентов по трем уровням экзамена: «Вступительный», «Полугодовой» и «Финальный экзамен»:
Сопоставить данные по конкретному студенту, предмету и уровню экзамена с помощью функции ВПР – нелегкая задача, так как сопоставляя данные по студенту и предмету, она будет выдавать результат первого совпадения, но далеко не факт, что этот результат будет корректным и соответствовать необходимому уровню экзамена.
Осуществить поиск по студенту + названию предмета + уровню экзамена можно с помощью создания вспомогательного столбца, как показано на примере ниже:
Протяните эту формулу в каждую ячейку столбца данной таблицы. Таким образом мы создадим уникальный ID, по которому мы можем сопоставлять данные.
Благодаря вспомогательному столбцу мы избежали того, что при сопоставлении данных, функция будет выдавать данные по не нужному уровню экзамена.
Теперь вы можете использовать значения вспомогательного столбца в качестве значений поиска.
Ниже формула, с помощью которой удобно сопоставлять данные из вашей таблицы в список студентов, распределенных по уровню экзамена.
=VLOOKUP($F3&”|”&G$2,$C$2:$D$19,2,0) – английская версия
=ВПР($F3&”|”&G$2;$C$2:$D$19;2;0) – русская версия
С помощью этой формулы мы объединили имя студента и уровень экзамена, чтобы получить значение поиска которое мы проверяем его во вспомогательном столбце и получить данные.
Пример 9. Обработка ошибок с помощью функции ВПР
Функция возвращает ошибку каждый раз, когда не может сопоставить значение поиска в заданном диапазоне данных.
В Excel есть возможность заменить сообщения об ошибке на заданное вами текстовое или числовое значение типа “Нет”, “Не доступно”, “Нет данных” и.т.д.
На примере ниже, мы пытаемся сопоставить результаты экзамена студента Ивана из таблицы. Но так как данных по Ивану в таблице нет, функция выдает ошибку.
Для того, чтобы заменить сообщение ошибки на какое-то более формальное значение нам поможет функция ЕСЛИОШИБКА(IFERROR) :
=IFERROR(VLOOKUP(D2,$A$2:$B$7,2,0),”Нет данных”) – английская версия
=ЕСЛИОШИБКА(ВПР(D2;$A$2:$B$7;2;0);”Нет данных”) – русская версия
Функция ЕСЛИОШИБКА(IFERROR) проверяет, если в результате вычислений по формуле выдается ошибка, то она выдает, то значение, которое вы указали в функции ЕСЛИОШИБКА(IFERROR), например “Нет данных”.
Еще больше полезных приемов в работе со списками данных и функциями в Excel вы узнаете в практическом курсе “От новичка до мастера Excel“. Успей зарегистрироваться по ссылке!