Как использовать функцию ВПР вместе с СУММ или СУММЕСЛИ в Excel

Автор: | 30.12.2021

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

Вы пытаетесь создать сводный файл в Excel, который будет определять все экземпляры определенного значения и суммировать другие значения, связанные с ним? Или вам нужно найти все значения в массиве, которые удовлетворяют заданному условию, а затем просуммировать связанные значения с другого листа? А может быть, перед вами стоит еще более сложная задача, например, просмотреть таблицу всех счетов-фактур вашей компании, найти среди них счета-фактуры конкретного продавца и суммировать их?

Задачи могут быть разными, но смысл их один — вам нужно найти и просуммировать значения на основе одного или нескольких критериев в Excel. Что это за значения? Любое числовое. Что это за критерии? Любой… От числа или ссылки на ячейку, содержащей желаемое значение, до логических операторов и результатов формул Excel.

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

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

  • ВПР и СУММ: суммируйте все найденные совпадающие значения
  • Прочие вычисления ВПР (СРЕДНЕЕ, МАКС., МИН)
  • ПОИСК И СУММА: поиск в массиве и суммирование связанных значений
  • ВПР и СУММЕСЛИ: суммирует значения, соответствующие определенному критерию

ВПР и СУММ в Excel – вычисляем сумму найденных совпадающих значений

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

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

 

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

Решение этой проблемы — использовать массив констант в аргументе col_index_num (column_number) функции ВПР. Вот пример формулы:

= СУММ (ВПР (значение поиска; диапазон поиска; {2,3,4}; ЛОЖЬ))
= СУММ (ВПР (найти_значение; таблица; {2; 3; 4}; ЛОЖЬ))

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

Теперь давайте применим эту комбинацию ВПР и СУММ к данным в нашей таблице, чтобы найти общие продажи в столбцах от B до M:

= СУММ (ВПР (B2; «Ежемесячные продажи»! 2 доллара США: 9 млн долларов США; {2,3,4,5,6,7,8,9,10,11,12,13}; ЛОЖЬ))
= СУММ (ВПР (B2; ‘Ежемесячные продажи’! $ A $ 2: $ M $ 9; {2; 3; 4; 5; 6; 7; 8; 9; 10; 11; 12; 13}; ЛОЖЬ))

Важно! Если вы вводите формулу массива, обязательно нажмите Ctrl + Shift + Enter вместо обычного нажатия Enter. Microsoft Excel заключит вашу формулу в фигурные скобки:

{= SUM (VLOOKUP (B2, ‘Ежемесячные продажи’! $ A $ 2: $ M $ 9, {2,3,4,5,6,7,8,9,10,11,12,13}, FALSE))}
{= СУММ (ВПР (B2, ‘Ежемесячные продажи’! $ A $ 2: $ M $ 9; {2; 3; 4; 5; 6; 7; 8; 9; 10; 11; 12; 13}; FALSE))}

Использование ВПР и СУММ в Excel

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

Вам может быть интересно, почему в приведенной выше формуле в качестве значения поиска отображается [@Product]. Это потому, что мои данные были преобразованы в таблицу с помощью команды «Таблица» на вкладке «Вставка». Мне удобнее работать с полными таблицами Excel, чем с диапазонами. Например, когда вы вводите формулу в одну из ячеек, Excel автоматически копирует ее во весь столбец, экономя вам несколько драгоценных секунд.

Как видите, использовать функции ВПР и СУММ в Excel довольно просто. Однако это далеко не идеально, особенно если вам приходится работать с большими таблицами. Дело в том, что использование формул массива может замедлить работу приложения, поскольку каждое значение в массиве делает отдельный вызов функции ВПР. Оказывается, чем больше значений в массиве, тем больше формул массива в книге и тем медленнее работает 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% от общей суммы.

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

Использование ВПР и СУММ в Excel

ПРОСМОТР и СУММ – поиск в массиве и сумма связанных значений

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

Давайте рассмотрим пример, чтобы было понятнее, о чем идет речь. Предположим, у нас есть таблица, в которой перечислены имена клиентов, приобретенные товары и их количество (основная таблица). Кроме того, есть вторая таблица, содержащая цены на товары (Таблица поиска). Наша задача — написать формулу, которая находит сумму всех заказов для конкретного клиента.

Использование ВПР и СУММ в Excel

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

= СУММ (ПОИСК ($ C $ 2: $ C $ 10, ‘Таблица поиска’! $ A $ 2: $ A $ 16, ‘Таблица поиска’! $ B $ 2: $ B $ 16) * $ D $ 2 : $ D $ 10 * ($ B $ 2: $ B $ 10 = $ G $ 1))
= СУММ (ПОИСК ($ C $ 2: $ C $ 10; ‘Таблица поиска’! $ A $ 2: $ A $ 16; ‘Таблица поиска’! $ B $ 2: $ B $ 16) * $ D $ 2 : $ D $ 10 * ($ B $ 2: $ B $ 10 = $ G $ 1))

Поскольку это формула массива, не забудьте нажать Ctrl + Shift + Enter, когда закончите вводить текст.

Таблица поиска — это имя листа, на котором находится диапазон поиска.

Использование ВПР и СУММ в Excel

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

  1. ПОИСК ($ C $ 2: $ C $ 10, «Таблица поиска»! $ A $ 2: $ A $ 16, «Таблица поиска»! $ B $ 2: $ B $ 16)
    ПОИСК ($ C $ 2: $ C $ 10; «Таблица поиска»! $ A $ 2: $ A $ 16; «Таблица поиска»! $ B $ 2: $ B $ 16)

    ПРОСМОТР проверяет элементы, перечисленные в столбце C основной таблицы, и возвращает соответствующую цену из столбца B таблицы поиска).

  2. $ D $ 2: $ D $ 10 — это количество товаров, приобретенных каждым покупателем, имя которого указано в столбце D основной таблицы. Умножая количество товара на цену, возвращаемую функцией ПОИСК, мы получаем стоимость каждого купленного товара.
  3. $ B $ 2: $ B $ 10 = $ G $ 1 — формула сравнивает имена клиентов в столбце B основной таблицы с именем в ячейке G1. Если есть совпадение, возвращается 1, в противном случае — 0. Следовательно, имена покупателей, которые отличаются от имени, указанного в ячейке G1, отбрасываются, потому что все мы знаем, что умножение на ноль дает ноль.

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

Комментарий. Для правильной работы ПРОСМОТРА отображаемый столбец должен быть отсортирован в порядке возрастания.

ВПР и СУММЕСЛИ – находим и суммируем значения, удовлетворяющие определённому критерию

Функция СУММЕСЛИ в Excel похожа на СУММ, которую мы только что видели, потому что она также складывает значения. Единственное отличие состоит в том, что СУММЕСЛИ суммирует только те значения, которые соответствуют указанным критериям. Например, простейшая формула с СУММЕСЛИ:

= СУММЕСЛИ (LA2: LA10; «> 10»)
= СУММЕСЛИ (LA2: LA10; «> 10»)

— суммирует все значения ячеек в диапазоне A2: A10 больше 10.

Очень просто, правда? Теперь давайте посмотрим на чуть более сложный пример. Предположим, у нас есть таблица, в которой перечислены названия поставщиков и их идентификационные номера (таблица поиска). Кроме того, есть еще одна таблица, в которой те же идентификаторы связаны с данными продаж (основная таблица). Наша задача — найти сумму продаж для конкретного продавца. Здесь 2 отягчающих обстоятельства:

  • Основная таблица содержит множество записей для идентификатора в случайном порядке.
  • Невозможно добавить столбец с названиями продавцов в основную таблицу.

Использование ВПР и СУММ в Excel

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

Прежде чем мы начнем, позвольте мне напомнить вам синтаксис функции СУММЕСЛИ):

СУММЕСЛИ (диапазон; критерий; [диапазон_суммы])
СУММЕСЛИ (диапазон; критерий; [диапазон_суммы])

  • range (диапазон) — аргумент не требует пояснений. Это просто диапазон ячеек, который вы хотите оценить по определенному критерию.
  • критерии — условие, указывающее формуле, какие значения следует добавить. Это может быть число, ссылка на ячейку, выражение или другая функция Excel.
  • sum_range (диапазон_суммы) — необязательный, но очень важный для нас аргумент. Определяет диапазон связанных ячеек для подведения итогов. Если не указано иное, Excel добавляет значения ячеек в первый аргумент функции.

Собирая все вместе, давайте определим третий аргумент для нашей функции СУММЕСЛИ. Как вы помните, мы хотим суммировать все продажи, сделанные конкретным продавцом, имя которого указано в ячейке F2 (см. Изображение выше).

  1. диапазон — поскольку мы ищем идентификатор продавца, значения этого аргумента будут значениями в столбце B основной таблицы. Вы можете указать диапазон B: B (весь столбец) или после преобразования данных в таблицу использовать имя столбца Main_table [ID].
  2. критерии — поскольку имена продавцов зарегистрированы в таблице поиска, мы используем функцию ВПР, чтобы найти идентификатор, соответствующий указанному продавцу. Имя написано в ячейке F2, поэтому для поиска используем формулу:

    ВПР ($ F $ 2; Таблица_просмотр; 2; ЛОЖЬ)
    ВПР ($ F $ 2; Lookup_table; 2; FALSE)

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

  3. sum_range (диапазон_суммы) — самая простая часть. Поскольку данные о продажах записываются в столбец C с именем «Продажи», мы просто пишем Main_table [Sales].

Все, что вам нужно сделать, это соединить части вместе, и формула СУММЕСЛИ + ВПР будет готова:

= СУММЕСЛИ (Главная_таблица [ID], ВПР ($ F $ 2, Lookup_table, 2, FALSE), Main_table [Продажи])
= СУММЕСЛИ (Главная_таблица [ID]; ВПР ($ F $ 2; Lookup_table; 2; FALSE); Main_table [Sales])

Использование ВПР и СУММ в Excel

 

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *