Формула впр для чего нужна
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. Как использовать?
Функция ВПР (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«. Успей зарегистрироваться по ссылке!