Функция впр в excel с примером: Как пользоваться функцией ВПР в Excel: пример с двумя таблицами

Как пользоваться функцией ВПР в Excel: пример с двумя таблицами

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

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

Как работает функция ВПР в Excel: пример

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

  1. Переходим в ячейку второй таблицы под названием столбца «Цена».
  2. Выберите «Формулы»-«Ссылки и массивы»-«ВПР».

    Ввести функцию ВПР можно и с помощью «мастера функций». Для этого нажмите на кнопку «fx», которая находиться в начале строки формул. Или нажмите комбинацию горячих клавиш SHIFT+F3.

    В появившимся диалоговом окне на поле категория, выберите из выпадающего списка: «Ссылки и массивы», а потом ниже укажите на функцию.

  3. Заполняем аргументы функции.

В поле «Исходное значение» вводим ссылку на ячейку под наименованием товара второй таблицы D3. В поле «Таблица» вводим диапазон всех значений первой таблицы A2:B7. В поле «Номер столбца» вводим значение 2, так как во втором столбце у нас находиться цена, которую мы хотим получить при поиске товара. И нажимаем ОК.

Теперь под заголовком столбца второй таблицы «Товар» введите наименования того товара по котором нам нужно узнать его цену. И нажмите Enter.

Функция позволяет нам быстро находить данные и получать по ним все необходимые значения из больших таблиц. Это похоже на работу с базами данных. Когда к базе создается запрос, а в ответ выводятся результаты, которые являются ответом на критерии запроса.



Функция ВПР в Excel и две таблицы

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

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

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

  1. Исходное значение: G3.
  2. Таблица: A2:E7. Диапазон нашей таблицы расширен.
  3. Номер столбца: {3;4;5}. Нам нужно с помощью функции обращаться одновременно к нескольким столбцам, поэтому значение данного аргумента будет взято в массив фигурными скобками. А номера столбцов следует перечислять через точку с запятой.
  4. Интервальный просмотр: ЛОЖЬ.
  5. Чтобы значения в выбранных столбцах суммировались, тогда всю функцию нужно поместить внутрь функции СУММ(). Вся формула в целом выглядит следующим образом: =СУММ(ВПР(G3;A3:E6;{3;4;5};ЛОЖЬ)).
  6. После ввода данной формулы следует нажать комбинацию клавиш: CTRL+SHIFT+ENTER. Внимание! Если не нажать комбинацию этих клавиш формула будет работать ошибочно. В Excel иногда приходиться выполнять функции в массиве для этого нужно обязательно использовать клавиши: CTRL+SHIFT+ENTER при вводе функций. Тогда в строке формул все содержимое будет взято в фигурные скобки «{}», что свидетельствует о выполнении формулы в массиве.

Теперь вводите в ячейку G3 наименование товара, в ячейке h4 получаем сумму продаж в первом квартале по данному товару.

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

Примечание. Если ввести вручную крайние фигурные скобки в строку формул то это не приведет ни ка какому результату. Выполнить функцию циклическим массивом можно только через комбинацию горячих клавиш: CTRL+SHIFT+ENTER.

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

Скачать пример функции ВПР с двумя таблицами

Другими словами если в нашей таблице повторяются значения «груши», «яблока» мы не сможем просуммировать всех груш и яблок. Для этого нужно использовать функцию ПРОСМОТР(). Она очень похожа на ВПР но умеет хорошо работать с массивами в исходных значениях.

Функция ПРОСМОТРX (XLOOKUP) вместо ВПР, ГПР и других функций в Excel

Содержание статьи

  • 1 Пару слов про функцию ПРОСМОТРX
  • 2 Левый ВПР? Я могу орудовать, где угодно!
  • 3 Иди домой, ГПР!
  • 4 Кажется, на нём были очки…
  • 5 «Двойной ПРОСМОТРХ» или «Зависимые выпадающие списки»
  • 6 Как насчёт подсветки выбираемых имён?
  • 7 ПРОСМОТРХ вместо задания условий

 

КУРС

EXCEL ACADEMY

Научитесь использовать все прикладные инструменты из функционала MS Excel.

 

Функция ВПР (VLOOKUP) скоро отпразднует юбилей 10 лет, что для временного цикла программного обеспечения сопоставимо с выходом на пенсию.

Перед разработчиками из Microsoft стояла задача подобрать достойную замену, и они не ударили в грязь лицом, выпустив функцию ПРОСМОТРX (XLOOKUP), которая представляет собой не только улучшенную версию ВПР, но ещё и замену ГПР, ИНДЕКС и ПОИСКПОЗ. Если хотите узнать о других полезных функциях Excel, то рекомендуем записаться на бесплатный онлайн-курс «Аналитика в Excel».

Пару слов про функцию ПРОСМОТРX

Прежде чем приступать к работе с функцией ПРОСМОТРХ, нужно учесть 3 момента:

1. Версию Excel: на данный момент, осень 2020 года, ПРОСМОТРX доступен только пользователям Office 365, хотя поговаривают, что эту брешь залатают в Standalone-версии Microsoft Office 2021.

2. Буква Х в названии функции – английская, поэтому велика вероятность ошибки при дотошном вводе; лучше всего использовать автозавершение ввода функций с помощью клавиши Tab.

3. Перебирать аргументы и диапазоны (не только в ПРОСМОТРХ, но и в других функциях) намного быстрее с зажатой клавишей Ctrl, чем с помощью ручного ввода точек с запятой.

Про атрибуты и возможности функции мы поговорим уже в боевых условиях, разбираясь с тем, что же всё-таки умеет делать ПРОСМОТРХ.

Левый ВПР? Я могу орудовать, где угодно!

Наверняка большинство из вас знает, что ВПР выдаёт ошибку, когда нужно забрать данные левее искомой ячейки. Для ПРОСМОТРХ это не проблема, ведь функция считает везде, где только заблагорассудится.

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

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

«Загвоздка?!» – удивляется ПРОСМОТРХ. «Да ещё чего!».

Специально используем «Мастер функций» комбинацией Shift+F3 (или кнопочка Fx слева от строки ввода формул), чтобы показать аргументы.

Искомое значение – это ячейка А2, то есть Gary Miller, с него мы начнём выводить бонусы.

=ПРОСМОТРX(A2;Лист2!$C$2:$C$26;Лист2!$B$2:$B$26;0;0)

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

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

Если ничего не найдено – уникальный и необязательный аргумент, позволяющий задать любое значение при неудачных результатах поиска, например, пробел (“ “) или 0, можно пропустить.

Режим сопоставления – классическая указка Excel для поиска точных или приблизительных значения. В данном случае выбираем 0 – точный поиск.

Протягиваем результат в ячейке ниже и сразу обращаем внимание на 0 – это те сотрудники, которых ПРОСМОТРХ не обнаружил в списке.

Кстати, если изначально функция везде выдаёт вам нули, то нужно проверить формат ячеек и проставить там числовой

Чтобы посчитать годовой оклад, можно умножить наши результаты на показатели столбца В, то есть дописываем в формулу *B2:

=ПРОСМОТРX(A2;Лист2!$C$2:$C$26;Лист2!$B$2:$B$26;0;0)*B2

Протягиваем и получаем максимальный бонус сотрудников.

Иди домой, ГПР!

Раз мы сказали о том, что ПРОСМОТРХ умеет работать в любых направлениях, то и с заменой ГПР, которая считает по строкам, тоже проблем не будет.

Заберём аналогичные данные для бонуса по сотрудникам, как делали ранее, но теперь обратимся к горизонтально развёрнутой табличке на другом листе. Естественно, строка с бонусом находится выше строки с сотрудниками, что в случае с обычным ГПР нам принесло бы ошибку. Сейчас же мы действуем по накатанной схеме:

=ПРОСМОТРX(A2;Лист3!$B$3:$Z$3;Лист3!$B$2:$Z$2;0;0)

Указываем ячейку с сотрудником. Затем выбираем диапазон с сотрудниками, в котором ПРОСМОТРХ находит искомое значение. Фиксируем по нажатию F4.

Далее указываем строку с бонусами, тоже фиксируем через F4.

Если ничего не найдено, ставим 0.

Точность поиска – тоже 0, точный поиск.

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

=ПРОСМОТРX(A2;Лист3!$B$3:$Z$3;Лист3!$B$2:$Z$2;0;0)*B2

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

Кажется, на нём были очки…

Представим, что необходимо достать все реквизиты сотрудника, но мы знаем лишь его фамилию (или какие-то другие отличительные знаки). Попробуем извлечь данные человека по фамилии Willard в отдельной ячейке.

Если мы начнём поиск с ячейки, содержащей одну только фамилию Willard, выберем столбец с сотрудниками в качестве искомого и возвращаемого массива, то… получим ошибку. В идеале наша формула должна выглядеть следующим образом, смотрите:

В первый аргумент, перед искомой ячейкой G2, дописываем “*”. Звёздочка – это служебный символ, которого мы сцепляем амперсандом (&) с ячейкой G2 и таким образом говорим программе, чтобы осуществлялся поиск не только Willard, но и всего остального содержимого ячейки до Willard. То есть первый аргумент у нас примет вид: «*»&G2

Второй аргумент – искомый массив, то есть столбец с именами: A2:A19

Третий аргумент – возвращаемый массив, то есть опять столбец с именами: A2:A19

Четвёртый аргумент – пропускаем

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

=ПРОСМОТРX("*"&G2;A2:A19;A2:A19;;2)

Вот мы и получили James Willard.

«Двойной ПРОСМОТРХ» или «Зависимые выпадающие списки»

Для создания зависимых выпадающих списков, то есть, когда по значению из одного списка открываются соответствующие значения другого, больше не нужно хитрых комбинаций с формулами. Хватит ПРОСМОТРХ и парочки списков.

Сперва добавим в нашу таблицу новый столбец под названием «Итоговый платёж», в котором будет осуществляться суммирование столбцов B и C.

Теперь добавим выпадающий список в ячейку G7.

Вкладка «Данные» – «Проверка данных». Тип данных – список.

В поле «Источник» указываем диапазон с нашими сотрудниками из столбца А. Жмём ОК.

Проверяем. Список работает.

Ниже, в ячейке G8, создаём второй выпадающий список, вот только в поле «Источник» указываем заголовки столбцов из шапки таблицы, то есть протягиваем строку от Годового оклада до Итогового платежа. Нажимаем ОК.

Тоже работает. Едем дальше.

Всё готово для встраивания двойного ПРОСМОТРХ.

Переходим в ячейку G9, хотя вы можете выбрать абсолютно любую ячейку.

Начинаем вводить формулу.

 

КУРС

EXCEL ACADEMY

Научитесь использовать все прикладные инструменты из функционала MS Excel.

 

Мы ищем имя из выпадающего списка выше, то есть ячейка G7 – это первый аргумент.

Затем выбираем диапазон сотрудников – это второй аргумент.

В качестве возвращаемого массива выбираем все остальные столбцы, потому что нам понадобятся все данные. И здесь – внимание!

На место третьего аргумента первой функции ПРОСМОТРХ мы пишем ещё один ПРОСМОТРХ. Уже в нём указываем первым аргументом ячейку F8 (там перебираются заголовки столбцов).

Второй аргумент – это шапка таблицы.

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

Обойдёмся без поразительной точности и закрываем формулу.

Готово. Теперь, в зависимости от выбранного сотрудника в первом списке, переключаются его показатели в различных столбцах из второго. Шикарно!

Как насчёт подсветки выбираемых имён?

В качестве бонуса можете прикрутить сюда условное форматирование, чтобы оно подсвечивало строку по выбранному имени. Мы же смотрим отчётность, это было бы крайне удобно!

Если мы должны найти строку по определённому показателю, то, естественно, выбор падёт на функцию ПОИСКПОЗ. Искать мы будем позицию по имени сотрудника в ячейке F7 и подсвечивать ту строку таблицы, в которой это имя/фамилия находится. Получится очень эффектно.

Обводим всю нашу таблицу. Переходим на вкладку «Главная», потом «Условное форматирование», затем «Создать правило».

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

Сперва зададим формат. Зальём каким-нибудь цветом. Окрасим шрифт. Должно выйти неплохо. Теперь переходим к прописыванию формулы.

=ПОИСКПОЗ($A1;$F$7;0)

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

$F$7 – наша зафиксированная ячейка с именем из выпадающего списка.

0 – просматриваемый массив, то есть ПОИСКПОЗ находит первое значение, равное искомому.

 Нажимаем ОК и ОК далее.

Переключаем имена и любуемся подсветкой.

ПРОСМОТРХ вместо задания условий

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

Если зарплата равна 10 000 – никакого бонуса, 30 000 – 5% бонусом, 50 000 – 8% бонусом и так далее. Определим, кому какой бонус полагается с помощью ПРОСМОТРХ.

Создадим новый столбец «Новый бонус» на основном листе.

Пишем в первой ячейке нового столбца формулу:

Первым аргументом выбираем ячейку В2 из столбца с окладами сотрудников.

В качестве второго аргумента указываем столбец с жалованием из бонусной таблички с другого листа, то есть у нас будет Лист2!F13:$F$17.

Возвращаемый массив – уже бонусный проценты, то есть бонусный столбец из бонусной таблички с другого листа – Лист2!G13:$G$17.

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

И самое главное – пятый аргумент, который отвечает за точность поиска. Указываем там -1, то есть точное совпадение или следующее меньшее значение.

=ПРОСМОТРX(B2;Лист2!$F$13:$F$17;Лист2!$G$13:$G$17;;-1)

Теперь нужно проверить результаты.

Гари Миллеру досталось 10% бонусов. Идём на бонусную табличку и смотрим.

Его оклад составляет 60 000 – это 10%. Следующий оклад для бонусов уже 100 000 и 15% соответственно. Что сделал Excel: он нашёл 60 000 и затем отобрал следующее минимальное значение, то есть 10%.

Никаких условий и никаких подборов. Одна только функция ПРОСМОТРХ помогла нам решить целую вереницу задач.

 

КУРС

EXCEL ACADEMY

Научитесь использовать все прикладные инструменты из функционала MS Excel.

 

Вставка для двойных регулирующих и пусковых клапанов HydroControl VTR, VFC, VPR и MTR и запорных клапанов HydroControl ATR DN 32

Описание

Размеры

Картинки
Документы

ЭТИМ

Вставка

для двойных регулирующих и пусковых клапанов HydroControl VTR, VFC, VPR и MTR и запорных клапанов HydroControl ATR DN 32

  • Основная функция: Oberteil
  • Номинальный размер: DN 32

Номер артикула: 1069010
Код EAN: 4026755163919
Продавец: Oventrop GmbH & Co. KG
Technische Änderungen vorbehalten

Скачать ГАЭБ

Скачать текст тендера

Является запасной частью

HydroControl VTR Двойной регулирующий и пусковой клапан с внутренней резьбой PN 25 DN 32, Rp 1 ¼, с заглушками

№ арт. 1060110

HydroControl VTR Двойной регулирующий и пусковой клапан с внутренней резьбой PN 25 DN 32, Rp 1 ¼, с двумя установленными измерительными клапанами Classic

№ арт. 1060210

HydroControl VTR Двойной регулирующий и пусковой клапан с внутренней резьбой PN 25 DN 32, Rp 1 ¼, с установленным измерительным клапаном Classic и шаровым краном наполнения и слива

№ арт. 1060310

HydroControl MTR Двойной регулирующий и пусковой клапан с внутренней резьбой PN 25 с фиксированным измерительным отверстием, DN 32, Rp 1¼, с двумя установленными измерительными клапанами Classic

№ арт. 1060410

HydroControl Двойной регулирующий и пусковой клапан с наружной резьбой PN 16 DN 32, G 1 ½, с заглушками

№ арт. 1060510

HydroControl Двойной регулирующий и пусковой клапан с фланцами по EN 1092-2 PN 16 DN 32, с двумя установленными измерительными клапанами Classic, чугун (GG 25)

№ арт. 1062648

HydroControl VFC Двойной регулирующий и пусковой клапан с фланцами согласно EN 1092-2 PN 6 DN 32, с двумя установленными измерительными клапанами Classic, чугун

№ арт. 1062678

HydroControl VFC Двойной регулирующий и пусковой клапан с фланцами PN 16 с круговыми отверстиями по ANSI класс 150 DN 32 — 1 ¼, с двумя установленными измерительными клапанами Classic, чугун

№ арт. 1062948

HydroControl VTR Двойной регулирующий и пусковой клапан с внутренней резьбой PN 25 DN 32, Rp 1 ¼, с двумя установленными измерительными клапанами Classic

№ арт. 1688710

HydroControl VFC Двойной регулирующий и пусковой клапан с фланцами согласно EN 1092-2 PN 16 DN 32, с двумя установленными измерительными клапанами Classic, чугун

№ арт. 1688748

Размеры

Размеры статьи
Длина Ширина Высота Вес
138 мм 70 мм 70 мм 0,54 кг
Упаковочные единицы
Тип шт. Длина Ширина Высота Вес
Неизвестно 1 0 мм 0 мм 0 мм 0 кг

images-content» role=»button»>
Картинки

Изображения продуктов

Чертежи

Документы

  Имя Размер

L_106
1. pdf

больше языков

L_106
1.pdf

ЭИНБАУАНЛЕЙТУНГ

Л_106
1.pdf

Л_106
1. pdf

Л_106
1.pdf

 1,74 МБ

Классификация
ETIM 7.0 EC011319

Аксессуары/запасные части для регулятора расхода и давления
Собственность Значение
Аксессуар для комбинированного измерительно-регулирующего клапана Другой