Функция впр в excel

Как же конкретно работает формула ВПР

• Вид формулы I. Если последний параметр опущен или указан равным 1, то ВПР предполагает, что первый столбец отсортирован по возрастанию, поэтому поиск останавливается на той строке, которая непосредственно предшествует строке, в которой находится значение, превышающее искомое. Если такой строки не найдено, то возвращается последняя строка диапазона.
• Вид формулы II. Если последний параметр указан равным 0, то ВПР последовательно просматривает первый столбец массива и сразу останавливает поиск, когда найдено первое точное соответствие с параметром <�ЧТО>, в противном случае возвращается код ошибки #Н/Д (#N/A).

Используем несколько ВПР в одной формуле

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

Давайте разберем следующий пример. У нас есть основная таблица (Main table) со столбцом SKU (new), куда необходимо добавить столбец с соответствующими ценами из другой таблицы. Кроме этого, у нас есть 2 таблицы поиска. Первая (Lookup table 1) содержит обновленные номера SKU (new) и названия товаров, а вторая (Lookup table 2) – названия товаров и старые номера SKU (old).

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

1. Запишите функцию ВПР, которая находит имя товара в таблице Lookup table 1, используя SKU, как искомое значение:

   Здесь New_SKU – именованный диапазон $A:$B в таблице Lookup table 1, а 2 – это столбец B, который содержит названия товаров (смотрите на рисунке выше)

2. Запишите формулу для вставки цен из таблицы Lookup table 2 на основе известных названий товаров. Для этого вставьте созданную ранее формулу в качестве искомого значения для новой функции ВПР:

   Здесь Price – именованный диапазон $A:$C в таблице Lookup table 2, а 3 – это столбец C, содержащий цены.

На рисунке ниже виден результат, возвращаемый созданной нами формулой:

Сравните 2 столбца и верните значение из третьего

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

Например, чтобы сравнить имена в столбцах A и D в двух таблицах ниже и вернуть время из столбца E, используйте следующую формулу:

=ВПР(A3, $D$3:$E$10, 2, ЛОЖЬ)

Чтобы скрыть ошибки #Н/Д, используйте проверенное решение — функцию IFNA:

=ЕСЛИНА(ВПР(A3, $D$3:$E$10, 2, ЛОЖЬ), «»)

Вместо пробелов вы можете вернуть любой текст для отсутствующих точек данных — просто введите его в последнем аргументе. Например:

=IFNA(ВПР(A3, $D$3:$E$10, 2, ЛОЖЬ), «Недоступно»)

Помимо ВПР, эту задачу можно решить с помощью нескольких других функций поиска.

Лично я бы полагался на более гибкую формулу ИНДЕКС ПОИСКПОЗ:

=ЕСЛИНА(ИНДЕКС($E$3:$E$10, ПОИСКПОЗ(A3, $D$3:$D$10, 0)), «»)

Или используйте современный преемник ВПР — функцию XLOOKUP, доступную в Excel 365 и Excel 2021:

=XLOOKUP(A3, $D$3:$D$10, $E$3:$E$10, «»)

Чтобы получить имена квалифицированных участников из группы A и их результаты, просто отфильтруйте пустые ячейки в столбце B:

=ФИЛЬТР(A3:B15, B3:B15″»)

Поиск в нескольких столбцах или базе Excel с INDIRECT (ДВССЫЛ)

В примере (снимок № 6) имеется 3 таблицы. Диапазоны данных в каждой из них имеют названия «Финансы», «Производство» и «Продажи».

В этом примере вам нужно найти сотрудника из отдела продаж с идентификатором 3. Название диапазона вводится в C19, идентификатор – в E19. Ячейка B22 связана с C19, и B22 (идентификатор) – это Lookup_value. Выражение с ДВССЫЛ находится в таблице – INDIRECT воспринимает содержимое C19 как имя диапазона.

Далее указываете номер колонки в матрице, откуда функция должна извлекать сведения и отображать их в ячейке при помощи VLOOKUP. В Range_lookup введите 0, потому что здесь требуется только точное пересечение.

ВПР – одно из самых полезных и важных средств поиска в Microsoft Excel. Он обычно используется для выполнения запросов к огромным листам с кучей информации, когда ручная работа может занять слишком много времени. Буква «V» в VLOOKUP означает «вертикальный», поэтому ее также иногда называют формулой вертикального отбора.

Особенности функции вертикального поиска в Excel

• Регистр в запросах не учитывается.
• В строке «Порядок сортировки» значение по умолчанию равно 1 (TRUE-ПРАВДА). Поэтому лучше не пропускать этот аргумент, если вам нужны только точные совпадения.
• В первом поле (там, где вы пишете запрос) допускается использование подстановочных знаков.

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

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

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

Вычисляем среднее:

{= СРЕДНЕЕ (ВПР (A2, ‘Таблица поиска’! $ A $ 2: $ D $ 10, {2,3,4}, FALSE))}
{= СРЕДНЕЕ (ВПР (A2; ‘Таблица поиска’! $ A $ 2: $ D $ 10; {2; 3; 4}; ЛОЖЬ))}

Формула ищет значение из ячейки A2 в таблице поиска и вычисляет среднее арифметическое значений, найденных на пересечении найденной строки и столбцов B, C и D.

Находим максимум:

{= МАКС (ВПР (A2, ‘Таблица поиска’! $ A $ 2: $ D $ 10, {2,3,4}, FALSE))}
{= МАКС (ВПР (A2, ‘Таблица поиска’! $ A $ 2: $ D $ 10; {2; 3; 4}; ЛОЖЬ))}

Формула ищет значение из ячейки A2 в таблице поиска и возвращает максимум значений, найденных на пересечении строки и столбцов, найденных B, C и D.

Находим минимум:

{= MIN (ВПР (A2, ‘Таблица поиска’! $ A $ 2: $ D $ 10, {2,3,4}, FALSE))}
{= МИН (ВПР (A2, ‘Таблица поиска’! $ A $ 2: $ D $ 10; {2; 3; 4}; ЛОЖЬ))}

Формула ищет значение из ячейки A2 в таблице поиска и возвращает минимум значений, найденных на пересечении строки и найденных столбцов B, C и D.

Вычисляем % от суммы:

{= 0,3 * СУММ (ВПР (A2, ‘Таблица поиска’! $ A $ 2: $ D $ 10, {2,3,4}, FALSE))}
{= 0,3 * СУММ (ВПР (A2, ‘Таблица поиска’! $ A $ 2: $ D $ 10; {2; 3; 4}; ЛОЖЬ))}

Формула ищет значение из ячейки A2 в поисковой таблице, затем суммирует значения, найденные на пересечении строки и столбцов, найденных B, C и D, и только затем вычисляет 30% от общей суммы.

Если мы добавим приведенные выше формулы в таблицу из предыдущего примера, результат будет выглядеть так:

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

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

Во втором — цены:

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

Поэтому мы не можем прописать формулу умножения и «растянуть» на все позиции.

Сделать? Нужно как-то заменить цены во второй таблице соответствующим количеством в первой, то есть цену товара А на количество товара А, цену товара В на количество В и т.д.

Мне это нравится.

Функция ВПР в Excel легко справляется с поставленной задачей.

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

Для вызова функции с помощью Помощника необходимо активировать ячейку, где будет записана формула, и нажать кнопку f(x) в начале строки формул. Появится диалоговое окно Мастера, в котором нужно выбрать ВПР из списка всех функций.

Щелкаем по надписи «ВПР». Откроется следующий диалог.

Теперь нужно заполнить предложенные поля. В первом поле «Искомое_значение» нужно указать критерий для ячейки, в которую мы вводим формулу. В нашем случае это ячейка с наименованием товара «А».

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

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

Следующее поле «Колонка_номер» — это номер, которым отделяется колонка с требуемыми данными (ценами) от колонки с критериями (название товара) включительно. То есть отсчет начинается со столбца с самим критерием. Если в нашей второй таблице оба столбца стоят рядом, то нужно указать цифру 2 (первый — критерии, второй — цены). Часто бывает, что данные отстают от критериев на 10 или 20 столбцов. Не беда, Excel все рассчитает.

Последнее поле — «Interval_lookup», в котором указывается тип поиска: точное (0) или приблизительное (1) соответствие критериям. Установите его на 0 (или FALSE) на данный момент. Второй вариант рассмотрен ниже.

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

Теперь легко рассчитать стоимость, просто умножив количество на цену.

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

Как на большом предприятии организовать единый справочник Номенклатура в 1С

Очень часто при получении отчетности по конкретной номенклатуре заинтересованное лицо задает много вопросов, ответы на которые неочевидны. По какой причине одна и та же номенклатура продублирована в справочнике или отличается незначительно по написанию? Какую номенклатуру выбрать в роли аналитики? Какую номенклатуру выбрать при создании новых документов?… Очевидно, что при выборе некорректной позиции из справочника номенклатур в 1С пользователь получит некорректные данные, что приводит к потере полноценного контроля над ситуацией (например, состоянием складских запасов).

3

Что такое ВПР в Excel?

ВПР расшифровывается как «вертикальный просмотр». В английском интерфейсе для её обозначения используется термин VLOOKUP, означающий то же самое, или VPR, являющийся калькой русской аббревиатуры.

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

Производительность работы функции ВПР

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

• мне нужен более мощный компьютер;
• мне нужна более быстрая формула, например, многие знают про ИНДЕКС+ПОИСКПОЗ (INDEX+MATCH), которая якобы быстрее на жалкие 5–10%.

И мало кто думает, что стоит только начать использовать ВПР вида I и обеспечить любыми способами сортировку первого столбца, как скорость работы ВПР возрастёт в 57 раз. Пишу прописью — В ПЯТЬДЕСЯТ СЕМЬ РАЗ! Не на 57%, а на 5 700%. Данный факт я проверил вполне надёжно.

Секрет такой быстрой работы кроется в том, что на отсортированном массиве можно применять чрезвычайно эффективный алгоритм поиска, который носит название бинарного поиска (метод деления пополам, метод дихотомии). Так вот ВПР вида I его применяет, а ВПР вида II ищет без какой-либо оптимизации вообще. То же самое относится и к функции ПОИСКПОЗ (MATCH), которая включает в себя аналогичный параметр, а также и к функции ПРОСМОТР (LOOKUP), которая работает только на отсортированных массивах и включена в Excel ради совместимости с Lotus 1-2-3.

Несколько условий ЕСЛИ в Excel

Функция ЕСЛИ в Excel позволяет оценивать ситуацию с двух точек зрения, например, значение больше 0 или меньше, и в зависимости от ответа на этот вопрос, произведи дальнейшие расчеты по той или иной формуле. Однако, не редки ситуации, когда вам приходится работать более, чем с двумя условиями. В сегодняшней статье мы рассмотрим примеры создания формул в Excel с несколькими условиями ЕСЛИ.

Принцип создания формул с несколькими условиями ЕСЛИ заключается в том, что в одном из аргументов формулы (значение_если_ИСТИНА или значение_если_ЛОЖЬ) находится еще одна формула ЕСЛИ.

Вам также могут быть интересны следующие статьи

• Логические функции в Excel
• Изучаем формулу ЕСЛИ — 5 приемов Excel, о которых вы не знали
• Функции СЛЧИС и СЛУЧМЕЖДУ Excel на примере создания имитации игральной кости
• Функция СЖПРОБЕЛЫ в Excel с примерами использования
• Четыре способа использования ВПР с несколькими условиями
• Что такое стандартное отклонение — использование функции СТАНДОТКЛОН для расчета стандартного отклонения в Excel
• Как расчитать дисперсию в Excel с помощью функции ДИСП.В
• Функция СУММПРОИЗВ — как использовать формулу СУММПРОИЗВ в Excel
• Функции НАИБОЛЬШИЙ и НАИМЕНЬШИЙ Excel
• Функция СУММЕСЛИМН в Excel

98 комментариев

Нужно вернуть определенное значение из ячейки и посчитать балл, т.е. например в ячейке D3 может быть значение А, Б, В, Г, надо в ячейку D4 вернуть значение в зависимости от буквы, например А=1, Б=2, В=3 и так далее. Как сделать? Можно ли через формулу ЕСЛИ?

Статья хорошая, спасибо. Но.. вначале статьи планы ставят из минимального расчета 500$, а все дальнейшие расчеты исходят из 400$. Как бы надо стараться следовать тем планам, что ставите.

Данный метод хорош, если у нас немного критериев (2-3), но когда их 10, то в такой формуле потом трудно разобратся «что и откуда». В таком случае можно (и нужно) обойтись без ЕСЛИ. Для этого создаем маленькую табличку с нашими критериями: в первой строке по возрастанию заполняем критерии (в приведенном примере это будут 0, 500, 750, 1000); во второй строчке под каждым критерием заполняем соответствующий процент (7, 10, 12,5, 16). Допустим, в диапазоне A1:D1 у нас заполнены критерии, а в диапазоне A2:D2 — соответствующие проценты. В ячейке А5 имеем цифру продаж; для рассчета комиссии используем следующую формулу: =A5*ИНДЕКС($A$2:$D$2;ПОИСКПОЗ(A5;$A$1:$D$1;1)). ПОИСКПОЗ ищет расположение критерия, который меньше продаж, но наибольший в списке, а ИНДЕКС по полученному номеру выдает нам необходимый процент.

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

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

Посмотреть правильный вид опции можно, открыв табличный процессор:

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

Рис.2 – поиск формул в Эксель

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

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

Общий вид описания для ВПР выглядит так:

Рис.3 – перечень параметров

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

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

Сравните два столбца на разных листах Excel с помощью ВПР

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

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

Предполагая, что список 1 находится в столбце A на Лист1 и список 2 находится в столбце A на Лист2вы можете сравнить два столбца и найти совпадения, используя эту формулу:

=ЕСЛИНА(ВПР(A2, Лист2!$A$2:$A$9, 1, ЛОЖЬ), «»)

Для получения дополнительной информации см.:

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

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

Во второй – цены:

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

Поэтому мы не можем прописать формулу умножения и «протянуть» вниз на все позиции.

Что делать? Надо как-то цены из второй таблицы подставить к соответствующему количеству в первой, т.е. цену товара А к количеству товара А, цену Б к количеству Б и т.д.

Вот так.

Функция ВПР в Эксель легко справится с задачей.

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

Для вызова функции с помощью Мастера нужно активировать ячейку, где будет прописана формула и нажать кнопку f(x) в самом начале строки формул. Появится диалоговое окно Мастера, где из списка всех функций нужно выбрать ВПР.

Кликаем по надписи «ВПР». Открывается следующее диалоговое окно.

Теперь нужно заполнить предлагаемые поля. В первом окошке «Искомое_значение» нужно указать критерий для ячейки, в которую мы вписываем формулу. В нашем случае это ячейка с наименованием товара «А».

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

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

Следующее поле «Номер_столбца» — это число, на которое столбец с искомыми данными (ценами) отстоит от столбца с критерием (наименованием товара) включительно. То есть отсчет идет, начиная с самого столбца с критерием. Если у нас во второй таблице оба столбца находятся рядом, то нужно указать число 2 (первый – критерий, второй — цены). Часто бывает, что данные отстоят от критерия на 10 или 20 столбцов

Это не важно, Excel все сосчитает

Последнее поле «Интервальный_просмотр», где указывается тип поиска: точное (0) или приблизительное (1) совпадение критерия. Пока ставим 0 (или ЛОЖЬ). Второй вариант рассмотрен ниже.

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

Теперь легко рассчитать стоимость простым умножением количества на цену.

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

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

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

1. Переходим в ячейку второй таблицы под названием столбца «Цена».
2. Выберите «Формулы»-«Ссылки и массивы»-«ВПР». Ввести функцию ВПР можно и с помощью «мастера функций». Для этого нажмите на кнопку «fx», которая находиться в начале строки формул. Или нажмите комбинацию горячих клавиш SHIFT+F3. В появившимся диалоговом окне на поле категория, выберите из выпадающего списка: «Ссылки и массивы», а потом ниже укажите на функцию.
3. Заполняем аргументы функции.

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

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

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

Динамический индекс со вложенными IF (ЕСЛИ)

Вы можете использовать формулы IF (ЕСЛИ) в Экселе для создания динамического индекса. В примере (скриншот № 5) премия рассчитывается в зависимости от отдела и категории.

Если сотрудник, работая в отделе продаж, входит в группу 1 бонусной категории, формула VLOOKUP должна вернуть 3%, если в группу 5 бонусной категории – 4%, а если в группу 10 – 5%.

Выражение IF с двумя границами в Col_index_num проверяет информацию в колонке G (категория бонусов), помогая ВПР выбрать значение из массива справа.

Критерий поиска – это отдел, E3. Матрица – диапазон $J$3:$M$7. В поле «Индекс» указывается конструкция по типу IF(G3=«Группа 1»,2,IF(G3=«Группа 5»,3,4)).

С помощью первой функции IF вы сможете узнать, соответствует ли G3 запросу «Группа 1». Если это так, формула VLOOKUP выберет бонус из второго столбца. Если это неправда, второе выражение IF проверяет, соответствует ли G3 запросу «Группа 5».

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

Синтаксис функции

ВПР(искомое_значение; таблица; номер_столбца; интервальный_просмотр)

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

Таблица — ссылка на диапазон ячеек. В левом столбце таблицы ищется Искомое_значение, а из столбцов расположенных правее, выводится соответствующий результат (хотя, в принципе, можно вывести можно вывести значение из левого столбца (в этом случае это будет само искомое_значение)). Часто левый столбец называется ключевым. Если первый столбец не содержит искомое_значение, то функция возвращает значение ошибки #Н/Д.

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

Параметр интервальный_просмотр может принимать 2 значения: ИСТИНА (ищется значение ближайшее к критерию или совпадающее с ним) и ЛОЖЬ (ищется значение в точности совпадающее с критерием). Значение ИСТИНА предполагает, что первый столбец в таблице отсортирован в алфавитном порядке или по возрастанию. Это способ используется в функции по умолчанию, если не указан другой.

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

Что такое функция ВПР в Эксель – область применения

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

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

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

Примеры для наглядности: в таблицах 1,2 – исходные данные, таблице 3 – что должно получиться.