Как Работать с Впр в Excel Примеры Видео • Относительный поиск

Функция ВПР (VLOOKUP)

Функция ВПР является одной из наиболее часто используемых и универсальных при работе с данными в Excel. Она не лишена недостатков, но ее обязательно следует освоить для понимания механизма работы с данными в Excel. Внимание. В августе 2019 Microsoft представила замену для ВПР — функцию ПРОСМОТРX, которая лучше во всем но требует поддержки динамических массивов

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

    Функция ссылается на идентификатор «40», т.е. значение, которое необходимо найти в первом столбце диапазона A3:D9 и возвращает значение из 4-го столбца той же строки, в которой находится идентификатор 40, т.е. это значение зарплаты 87000

В обоих случаях, в качестве четвертого параметра стоял «0» — этот параметр называется «интервальный_просмотр» . Его можно записывать как «0» или «1», а также как «ЛОЖЬ» или «ИСТИНА». Более подробно об этом аргументе ниже в разделе «Синтаксис»

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

Буква «В» в названии функции ВПР означает «вертикально». Функция ВПР используется вместо функции ГПР, если сравниваемые значения расположены в столбце слева от искомых данных.

Вот другой пример, который наглядно демонстрирует принцип работы функции ВПР:

Принцип работы функции ВПР из категории

Принцип работы функции ВПР из категории «Ссылки и массивы»

Функция ВПР в Excel примеры использования советы (2019)
Поскольку, если ВПР не находит точное совпадение, то функция вернет максимальное значение, которое меньше искомого. Значит нам просто нужно ввести очень большое число, которое заведомо больше зарплаты любого сотрудника. Главное не забыть сортировку по зарплате сделать.
специалист
Мнение эксперта
Витальева Анжела, консультант по работе с офисными программами
Со всеми вопросами обращайтесь ко мне!
Задать вопрос эксперту
Благодаря этому, процедура поиска будет остановлена только когда будет достигнута строка со значением, номер которого уже превышает искомый объект. Если же вам нужны дополнительные объяснения, обращайтесь ко мне!
Три первых аргумента являются обязательными. Главное требование к организации данных – искомое значение должно находится в первом столбце таблицы для поиска. (Если речь идет о ГПР, то в первой строке диапазона).

ВПР в Excel (эксель) – что это за функция, пошаговая инструкция для чайников, формула ВПР, примеры использования

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

Как выглядит синтаксис ВПР?

Синтаксис функции в Excel – это набор параметров, с помощью которых её можно вызвать и задать. Запись аналогична с методом записи математических функций.

  • Используйте уже созданный документ, либо откройте новый пустой лист;
  • Кликните на клавишу «Формулы» , как показано на рисунке ниже;
  • В строке поиска напечатайте «ВПР» или «VLOOKUP» в зависимости от языка программы;
  • Настройте категорию «Полный перечень» ;
  • Кликните на «Найти» .

впр в excel примеры

В результате поиска формулы вы увидите её нахождение в перечне. Нажав на элемент, внизу экрана отобразится его формула.

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

впр в excel примеры

Рассмотрим детальнее каждое из значений, которое описывается в скобках:

  • — первый элемент. Вместо него вам нужно прописать именно то значение, которое вы хотите найти в таблице. Также, можно вписывать адрес ячейки в таблице;
  • — тут нужно напечатать номер столбика, в рамках которого будет осуществляться перебор данных.
  • — здесь юзер определяет количество ячеек, задавая их размерность в виде двумерного массива данных. Первый столбик – это элемент «ЧТО»;
  • — этот элемент функции ВПР отвечает за сортировку первого столбца по возрастанию (первый столбик для «ГДЕ»). В результате успешной сортировки, значение становится истинным (единица). Если возникают какие-либо неточности или ошибки во время ввода параметров – появляется ложное значение сортировки (ноль). Стоит заметить, что во время задания ВПР можно пропустить, и тогда его значение по умолчанию принимается как истина.
специалист
Мнение эксперта
Витальева Анжела, консультант по работе с офисными программами
Со всеми вопросами обращайтесь ко мне!
Задать вопрос эксперту
Указывая такие звездочки вы даете Excel понять, что готовы осуществить поиск, при котором в искомых данных как в начале так и в конце значения могут быть любые другие слова, буквы или цифры. Если же вам нужны дополнительные объяснения, обращайтесь ко мне!
В строке «Таблица» необходимо выделить ту, из которой будут браться данные – шапку не выделяйте. Учтите, что крайний левый столбец должен состоять из значений, которые мы ищем. То есть, мне нужно яблоко и другие фрукты, значит, их перечень должен быть в левом столбце выделяемой области.

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

  • Когда необходимо разделить значения объекта табличного процессора по его диапазонам;
  • Для тех таблиц, в которых параметр ГДЕ может содержать несколько идентичных значений. В таком случае, формула вернет только то, которое находится в последней строчке относительно массива;
  • Когда нужно искать значения, которые больше того, которое может содержаться в первом столбике. Так вы найдете последнюю строку таблицы практическим мгновенно.

Эта комбинация делает формулу ВПР более устойчивой. Эта функция может изменяться, когда столбцы вставляются или удаляются из таблицы, но все же сочетание ВПР + ПОИСКПОЗ обеспечит плавную работу даже при внесении изменений в столбцы.

Как сравнить две таблицы: пошаговая инструкция для «чайников»

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

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

Как Работать с Впр в Excel Примеры Видео • Относительный поиск

Как Работать с Впр в Excel Примеры Видео • Относительный поиск

Как Работать с Впр в Excel Примеры Видео • Относительный поиск

Как Работать с Впр в Excel Примеры Видео • Относительный поиск

Как Работать с Впр в Excel Примеры Видео • Относительный поиск

– то, что важно будет найти во второй таблице. Чтобы значение появилось в строке, нужно выделить первый столбик с наименованиями товаров (А2 – А5).

– с чем программа будет сравнивать. Для заполнения нужно перейти на вторую страницу и отметить два наименования – предметы и цена (А2 – В5). И зафиксировать результат кнопкой F4.

Номер столбца – второй, так как именно стоимость переносится в новую.

Как Работать с Впр в Excel Примеры Видео • Относительный поиск

После нажатия кнопки «ОК» новые значения появятся в таблице. Чтобы ценовая информация появилась у всех предметов нужно растянуть ячейку.

Как Работать с Впр в Excel Примеры Видео • Относительный поиск

Теперь администратор может работать с данными стандартными функциями Excel, благодаря инструкции.

специалист
Мнение эксперта
Витальева Анжела, консультант по работе с офисными программами
Со всеми вопросами обращайтесь ко мне!
Задать вопрос эксперту
Основываясь на тех данных, что вы выберете из выпадающего списка, функция автоматически выведет необходимые данные, как это указано на примере ниже. Если же вам нужны дополнительные объяснения, обращайтесь ко мне!
В формуле, указанной выше мы добавили к значению поиска “звездочки” с двух сторон. Указывая такие “звездочки” вы даете Excel понять, что готовы осуществить поиск, при котором в искомых данных как в начале так и в конце значения могут быть любые другие слова, буквы или цифры.
Как Работать с Впр в Excel Примеры Видео • Относительный поиск

Функция ВПР в Excel с примерами

  • Выбрать ячейку, в которой вы хотите создать выпадающий список. В нашем примере в ячейке “G4” мы указали фамилии студентов.
  • Перейти к вкладке Данные -> Проверка данных.
  • В диалоговом окне «Проверка данных» на вкладке «Настройки» выбрать «Список» и в раскрывающемся списке выберите «Разрешить».
  • В источнике выбрать $A$3:$A$10
  • Нажать «ОК».

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

Функция ВПР в Excel с примерами

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

Нажмите по верхней ячейке в первой таблице в столбце Цена, а потом кнопочку «fx» в строке формул, чтобы открыть окно мастера функций.

Открываем окно

Там, где написано категория выбираем «Ссылки и массивы» . В списке выделите ее и нажимайте «ОК» .

Первый шаг

Следующее, что мы делаем – прописываем аргументы в предложенные поля.

Ставьте курсив в поле «Искомое_значение» и выделяйте в первой таблице то значение, которое будем искать. У меня это яблоко.

А2

В строке «Таблица» необходимо выделить ту, из которой будут браться данные – шапку не выделяйте. Учтите, что крайний левый столбец должен состоять из значений, которые мы ищем. То есть, мне нужно яблоко и другие фрукты, значит, их перечень должен быть в левом столбце выделяемой области.

Выделение

Чтобы после того, как мы напишем формулу и растянем ее по всему столбцу, выбранный диапазон не смещался вниз, нужно сделать ссылки абсолютными: выделите данные в поле и нажмите «F4» . Теперь адрес на ячейки стал абсолютным, к ним добавился знак «$» , и диапазон смещаться не будет.

Знак $

Там, где номер столбца, поставьте цифру, соответствующую во второй таблице тому столбцу, данные откуда нужно переносить. У меня прайс состоит из фруктов и цены, мне нужно второе, поэтому ставлю цифру «2» .

Второй столбец

В «Интервальный_просмотр» пишем «ЛОЖЬ» – если искать нужно точные совпадения, или «Истина» – если значения могут быть приближенные. Для нашего примера выбираем первое. Если ничего не указать в данном поле, то по умолчанию выберется второе. Потом нажимайте «ОК» .

Здесь обратите внимание на следующее, если работаете с числами и указываете «Истина» , то вторая таблица (это наш прайс) обязательно должна быть отсортирована по возрастанию. Например, при поиске 5,25 найдется 5,27 и возьмутся данные с этой строки, хотя ниже может еще быть и число 5,2599 – но формула дальше смотреть не будет, поскольку она думает, что ниже числа только больше.

Аргументы заполнены

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

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

Перенесено число

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

Цены на фрукты

Теперь можем рассчитать столбец Стоимость. Растянем формулу на необходимое количество строчек.

Стоимость

Если у Вас в первой таблице есть названия продуктов, которых нет в прайсе, у меня это овощи, то напротив данных пунктов формула ВПР выдаст ошибку #Н/Д .

Нет данных

При добавлении столбцов на лист, данные для аргумента «Таблица» функции автоматически изменятся. В примере прайс сдвинут на 2 столбца вправо. Выделим любую ячейку с формулой и видим, что вместо $G$2:$H$12 теперь $I$2:$J$14 .

Переместили

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

Создание

В открывшемся окне «Тип данных» будет «Список» , ниже указываем область источника – это названия фруктов, то есть тот столбец, который есть и в первой и во второй таблице. Нажимайте «ОК» .

Выбор Источника

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

Выбор фрукта

Выделяю F2 и вставляю функцию ВПР. Аргумент первый – это сделанный список ( F1 ).

F1

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

Диапазон

Дальше указываем столбец ( 2 ), данные из которого нужно вытянуть, пишем ЛОЖЬ , для поиска точных совпадений, и нажимаем «ОК» .

Аргументы заполнены 2

Получилось что-то вроде поиска: выбираем фрукт и ВПР находит в прайсе его цену.

Поиск

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

Жмем по любой ячейке в столбце D и вставляем один новый.

Добавление

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

Разные листы

Вставляем функцию и указываем аргументы. Сначала то, что будем искать, в примере яблоко ( А2 ). Для выбора диапазона из нового прайса, поставьте курсор в поле «Таблица» и перейдите на нужный лист, у меня «Лист1» .

Выбор листа

Выделяем мышкой необходимые столбцы и строки, без заголовков.

Без шапки

Дальше делаем абсолютные ссылки на ячейки: «Лист1!$A$2:$B$12» . Выделите строчку и нажмите «F4» , чтобы к адресам ячеек добавился знак доллара. Указываем столбец ( 2 ) и пишем «ЛОЖЬ» .

Заполненные поля

Кнопка ок

Теперь два столбца с новой и старой ценой расположены рядом и можно сделать или визуальное сравнение, или применив определенные формулы, или условное форматирование.

Данные рядом

Надеюсь, у меня получилась пошаговая инструкция по использованию и применению функции ВПР в Excel, и Вам теперь все понятно.

Функция ВПР (VLOOKUP) в 6 различных сценариях - Блог SF Education
Число 9.99999999999999E + 307 это самое большое число, которое можно использовать в Excel, когда оно используется как критерий поиска, тогда функция VLOOKUP (ВПР) возвращает последнее число из списка.
специалист
Мнение эксперта
Витальева Анжела, консультант по работе с офисными программами
Со всеми вопросами обращайтесь ко мне!
Задать вопрос эксперту
Ниже формула, с помощью которой удобно сопоставлять данные из вашей таблицы в список студентов, распределенных по уровню экзамена. Если же вам нужны дополнительные объяснения, обращайтесь ко мне!
Для решения этой задачи мы воспользуемся классической функцией ВПР, но преобразуем первый аргумент – искомое значение. В этом случае мы предоставляем значение поиска как part_name&”*”. Part_name — это тот кусочек, по которому нужно найти соответствие, а «*» — подстановочный знак. Машина переведет выражение part_name&”*” как «начинается с part_name». В нашем случае part_name = Ил.

Функция ВПР (VLOOKUP) — Справочник

Как видим в полученной таблице для количества, например, 110, партию выдало Мелкая (ближайшее меньшее 100) и т.д. Обязательно сортировка критериев должна быть от большего к меньшему, иначе ВПР не сработает.

Примеры использования

Использование фильтра

Первый простой пример – имеются 2 таблицы. В одной указываются Товары и их идентификаторы (ID). Во второй, с помощью фильтра по ID, мы хотим получить наименование товара.

Вводим функцию

После знака равно вводим ВПР, затем Enter и Fx для ввода аргументов.

Мастер настроек

Аргументы также можно вводить в соответствующей строке, перечисляя их через точку с запятой.

Вводим искомые значения

Искомыми будут значения, по которым будем искать совпадения. В данном случае это ячейка E1.

Задаем диапазон

Для второго аргумента выделяем диапазон таблицы.

Делаем абсолютные ссылки

Обязательно необходимо зафиксировать (указать знаки доллара или клавиша F4 для всего диапазона) и сделать абсолютные ссылки для того чтобы диапазон не «сползал», так как в данном случае не указывается имя таблица.

Вводим номер столбца

Номер столбца – то, что нужно вернуть. В этом примере требуется вернуть 2 столбец (Товар). Для точного поиска 4 аргумент – .

Изменение наименования

Теперь при изменении в фильтре номера ID будет изменяться наименование товара.

Пример таблица

Теперь нужно получить партию для каждого наименования товара по критерию Количество.

Например, для мелкой партии количество должно быть от 100 до 200, средней200-300 и т.д.

Мастер функций

Искомым значением в данном случае будет количество, Таблицу выбираем диапазон КритерийПартия (фиксируем F4). Номер столбца 2, интервальный просмотр в этом случае должен быть 1 (позволит получить ближайшее меньшее значение к искомому значению).

Готовый результат

Как видим в полученной таблице для количества, например, 110, партию выдало Мелкая (ближайшее меньшее 100) и т.д. Обязательно сортировка критериев должна быть от большего к меньшему, иначе ВПР не сработает.

специалист
Мнение эксперта
Витальева Анжела, консультант по работе с офисными программами
Со всеми вопросами обращайтесь ко мне!
Задать вопрос эксперту
ВПР искомое значение; таблица для поиска; номер столбца, содержащего возвращаемое значение; интервальный просмотр 1 истина или 0 ложь. Если же вам нужны дополнительные объяснения, обращайтесь ко мне!
Обратите внимание, что в функции ВПР в качестве номера столбца, из которого берется результат мы указываем 2, несмотря на то, в таблице, которая у нас перед глазами, столбец ID является первым. Это все потому, что функция ВЫБОР меняет местами столбцы.

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

Там, где номер столбца, поставьте цифру, соответствующую во второй таблице тому столбцу, данные откуда нужно переносить. У меня прайс состоит из фруктов и цены, мне нужно второе, поэтому ставлю цифру «2» .

Как найти частичное совпадение в Excel с VLOOKUP

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

В таком случае можно сделать это с помощью подстановочных знаков в VLOOKUP.

Важное примечание: при частичном сопоставлении (4-тый аргумент — интервальный просмотр) всегда должен быть равен 0, чтобы совпадение с подстановочными знаками работало должным образом.

Как Работать с Впр в Excel Примеры Видео • Относительный поиск

Мы хотим узнать подробности о сотруднике, чье имя начинается с «Ил».

Для решения этой задачи мы воспользуемся классической функцией ВПР, но преобразуем первый аргумент – искомое значение. В этом случае мы предоставляем значение поиска как part_name&”*”. Part_name — это тот кусочек, по которому нужно найти соответствие, а «*» — подстановочный знак. Машина переведет выражение part_name&”*” как «начинается с part_name». В нашем случае part_name = Ил.

В SQL такую задачу можно решить с помощью оператора LIKE “Ил%”

Как Работать с Впр в Excel Примеры Видео • Относительный поиск

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

Как видите, мы получили все нужные нам значения, соответствующие нашему частичному поисковому значению, без указания полного имени.

Вы также можете найти значение, которое заканчивается определенным символом или имеет их в середине.

Если присоединить подстановочный знак (“*”), то функция будет искать ячейку со значением, заканчивающимся на part_name.

Если Вам нужно совпадение «содержит part_name», то нужно использовать два подстановочных знака, например, ”*” & part_name & ”*”.

специалист
Мнение эксперта
Витальева Анжела, консультант по работе с офисными программами
Со всеми вопросами обращайтесь ко мне!
Задать вопрос эксперту
Что бы выбрать нужную нам ячейку с значением, достаточно просто стать курсором в строку Искомое_значение , а потом клацнуть левой кнопкой мыши, по нужной ячейке в таблице В нашем примере ячейка Е3. Если же вам нужны дополнительные объяснения, обращайтесь ко мне!
Начнем с того, что значение поиска может отсутствовать в вашем диапазоне данных, или вы, возможно, использовали неправильное значение. Если вы видите ошибку N / A, дважды проверьте значение в формуле VLOOKUP.

Интервальный _просмотр

Вставляем функцию и указываем аргументы. Сначала то, что будем искать, в примере яблоко ( А2 ). Для выбора диапазона из нового прайса, поставьте курсор в поле «Таблица» и перейдите на нужный лист, у меня «Лист1» .

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

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

  • Не указали символ доллара в диапазоне (аргумент 2).В этом случае первая формула, введённая вручную, будет верной, а скопированные в другие ячейки — нет, поскольку диапазон поиска смещается в соответствии с правилами Эксель при копировании формул.
  • Неверно указан диапазон поиска.Опять же это ошибка в аргументе 2. Диапазон ячеек нужно указывать так, чтобы столбец, в котором ищется совпадение, был первым в выделенном диапазоне. Иначе конечно ничего найдено не будет.
  • Неверно указан номер столбца (аргумент 3).Например, можно указать столбец с таким номером, которого нет в выделенном диапазоне. Или номер столбца указан так, что в этом столбце находятся не те данные (напоминаем, что столбцы нумеруются с единицы).

Если у Вас не работает функция ВПР, то значит скорее всего Вы неверно задали её параметры. Это бывает чаще всего, а не «глючит Excel».

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

специалист
Мнение эксперта
Витальева Анжела, консультант по работе с офисными программами
Со всеми вопросами обращайтесь ко мне!
Задать вопрос эксперту
Новая таблица у меня находится на другом листе, чтобы Вам стало понятно, как использовать ВПР, если данные расположены на разных листах. Если же вам нужны дополнительные объяснения, обращайтесь ко мне!
Три первых аргумента являются обязательными. Главное требование к организации данных – искомое значение должно находится в первом столбце таблицы для поиска. (Если речь идет о ГПР, то в первой строке диапазона).

Автономный ВПР

Как это сделать. Выбираем нужный нам диапазон. Таблица №1. Выбираем закладку Формулы, кнопка Задать имя. Нажимаем. Появляется диалоговое окно Создание имени. Пишем любое имя. Но нужно его запомнить. Например Конфеты. Нажимаем ОК.

Понравилась статья? Поделиться с друзьями:
Добавить комментарий

;-) :| :x :twisted: :smile: :shock: :sad: :roll: :razz: :oops: :o :mrgreen: :lol: :idea: :grin: :evil: :cry: :cool: :arrow: :???: :?: :!: