В этом руководстве вы найдете несколько интересных примеров, показывающих, как использовать функцию ВПР вместе с СУММ или СУММЕСЛИ в Excel для поиска и суммирования значений по одному или нескольким критериям.
Вы пытаетесь создать сводной файл в Excel, который будет идентифицировать все экземпляры определенного значения и суммировать другие значения, связанные с ним? Или вам нужно найти в массиве все значения, удовлетворяющие заданному условию, а затем просуммировать связанные значения с другого листа? Или, может быть, у вас есть еще более сложная задача, например, поиск в таблице всех счетов-фактур вашей компании, поиск среди них счетов-фактур определенного поставщика и их суммирование?
Задания могут отличаться, но смысл у них один: нужно найти и просуммировать значения по одному или нескольким критериям в Excel. Каковы эти ценности? Любой номер. Каковы эти критерии? Любой… От числа или ссылки на ячейку, содержащей нужное значение, до логических операторов и результатов формулы Excel.
Так есть ли в Microsoft Excel какой-либо функционал, способный справиться с описанными задачами? Конечно! Решение состоит в том, чтобы объединить функции ВПР или ПРОСМОТР с функциями СУММ или СУММЕСЛИ. Приведенные ниже примеры формул помогут вам понять, как работают эти функции и как их использовать с реальными данными.
Обратите внимание, что эти примеры предназначены для опытных пользователей, знакомых с основными принципами и синтаксисом функции ВПР. Если вам еще далеко до этого уровня, рекомендуем обратить внимание на первую часть туториала — Функция ВПР в Excel: синтаксис и примеры.
- ВПР и СУММ: суммировать все найденные совпадающие значения
- Другие расчеты с ВПР (СРЗНАЧ, МАКС., МИН)
- SEE и SUM: поиск в массиве и добавление связанных значений
- VLOOKUP и SUMIF: суммируйте значения, удовлетворяющие определенным критериям
ВПР и СУММ в Excel – вычисляем сумму найденных совпадающих значений
При работе с числовыми данными в Excel часто приходится не только извлекать связанные данные из другой таблицы, но и добавлять несколько столбцов или строк. Для этого вы можете комбинировать функции СУММ и ВПР, как показано ниже.
Допустим, у нас есть список продуктов с данными о продажах за несколько месяцев, с отдельным столбцом для каждого месяца. Источник данных — Ежемесячный отчет о продажах:
Теперь нам нужно составить итоговую таблицу с суммами продаж каждого продукта.
Решение этой проблемы заключается в использовании массива констант в аргументе col_index_num (номер_столбца) функции ВПР. Вот пример формулы:
=СУММ(ВПР(значение поиска, диапазон поиска, {2,3,4}, ЛОЖЬ))
=СУММ(ВПР(искомое_значение,таблица,{2,3,4},ЛОЖЬ))
Как видите, мы используем массив {2,3,4} в качестве третьего аргумента для поиска одной и той же функции ВПР несколько раз, чтобы получить сумму значений в столбцах 2, 3 и 4.
Теперь давайте применим эту комбинацию VLOOKUP и SUM к данным нашей таблицы, чтобы найти общую сумму продаж в столбцах от B до M:
=СУММ(ВПР(B2,’Ежемесячные продажи’!$A$2:$M$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 заключит вашу формулу в фигурные скобки:
{=СУММ(ВПР(B2,’Ежемесячные продажи’!$A$2:$M$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};ЛОЖЬ))}
Если ограничиться простым нажатием Enter, то вычисление будет производиться только над первым значением массива, что приведет к неверному результату.
Вам может быть интересно, почему формула на изображении выше показывает [@Product] как значение, которое вы ищете. Это связано с тем, что мои данные были преобразованы в таблицу с помощью команды «Таблица» на вкладке «Вставка». Мне удобнее работать с полнофункциональными электронными таблицами Excel, чем с простыми диапазонами. Например, когда вы вводите формулу в одну из ячеек, Excel автоматически копирует ее по всему столбцу, экономя драгоценные секунды.
Как видите, пользоваться функциями ВПР и СУММ в Excel довольно просто. Однако это далеко не идеальное решение, особенно если вам приходится работать с большими таблицами. Дело в том, что использование формул массива может замедлить работу вашего приложения, так как каждое значение в массиве делает отдельный вызов функции ВПР. Получается, что чем больше значений в массиве, тем больше формул массива в книге и тем медленнее работает Excel.
Эту проблему можно решить, используя комбинацию функций ИНДЕКС и ПОИСКПОЗ вместо функций ВПР и СУММ. Далее в этой статье вы увидите несколько примеров таких формул.
Выполняем другие вычисления, используя функцию ВПР в Excel
Мы только что видели пример того, как можно извлечь значения из нескольких столбцов таблицы и вычислить их сумму. Таким же образом вы можете выполнять другие математические операции над результатами, возвращаемыми функцией ВПР. Вот несколько примеров формул:
Вычисляем среднее:
{=СРЗНАЧ(ВПР(A2,’Таблица поиска’!$A$2:$D$10,{2,3,4},ЛОЖЬ))}
{=СРЗНАЧ(ВПР(A2;’Таблица поиска’!$A$2:$D$10;{2;3;4};ЛОЖЬ))}
Формула ищет значение в ячейке A2 справочной таблицы и вычисляет среднее арифметическое значений, находящихся на пересечении найденной строки и столбцов B, C и D.
Находим максимум:
{=MAX(ВПР(A2,’Таблица поиска’!$A$2:$D$10,{2,3,4},ЛОЖЬ))}
{=MAX(ВПР(A2;’Таблица поиска’!$A$2:$D$10;{2;3;4};ЛОЖЬ))}
Формула ищет значение в ячейке A2 таблицы поиска и возвращает максимальное из значений, найденных на пересечении найденной строки и столбцов B, C и D.
Находим минимум:
{=МИН(ВПР(A2,’Таблица поиска’!$A$2:$D$10,{2,3,4},ЛОЖЬ))}
{=MIN(ВПР(A2;’Таблица поиска’!$A$2:$D$10;{2;3;4};ЛОЖЬ))}
Формула ищет значение в ячейке A2 справочной таблицы и возвращает минимум значений, найденных на пересечении найденной строки и столбцов B, C и D.
Вычисляем % от суммы:
{=0,3*СУММ(ВПР(A2,’Таблица поиска’!$A$2:$D$10,{2,3,4},ЛОЖЬ))}
{=0,3*СУММ(ВПР(A2;’Таблица поиска’!$A$2:$D$10;{2;3;4};ЛОЖЬ))}
Формула ищет значение в ячейке A2 в справочной таблице, затем складывает значения, которые находятся на пересечении найденной строки и столбцов B, C и D, и только потом вычисляет 30% суммы.
Если мы добавим перечисленные выше формулы в таблицу в примере выше, результат будет выглядеть так:
ПРОСМОТР и СУММ – поиск в массиве и сумма связанных значений
Если искомое значение представляет собой массив, функция ВПР становится бесполезной, поскольку она не умеет работать с массивами данных. В такой ситуации вы можете использовать функцию ПРОСМОТР в Excel, которая аналогична ВПР и также работает с массивами так же, как и с одиночными значениями.
Давайте рассмотрим пример, чтобы было понятнее, о чем мы говорим. Предположим, у нас есть таблица, в которой перечислены имена клиентов, купленные товары и их количество (таблица основной таблицы). Кроме того, есть вторая таблица, содержащая цены на товары (справочная таблица). Наша задача — написать формулу, которая находит сумму всех заказов от данного клиента.
Как вы помните, вы не можете использовать функцию ВПР, если нужное значение встречается несколько раз (это массив данных). Вместо этого используйте комбинацию функций SUM и VIEW:
=СУММ(ПРОСМОТР($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, когда закончите печатать.
Таблица поиска — это имя листа, на котором находится просматриваемый диапазон.
Давайте разберем ингредиенты в формуле, чтобы вы поняли, как она работает, и могли настроить ее в соответствии со своими потребностями. Оставим функцию СУММ пока в стороне, так как ее назначение очевидно.
-
ПРОСМОТР($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 таблицы поиска).
- $D$2:$D$10 — это количество товаров, купленных каждым покупателем, чье имя указано в столбце D основной таблицы. Умножая количество товара на цену, возвращаемую функцией ПРОСМОТР, мы получаем стоимость каждого купленного товара.
- $B$2:$B$10=$G$1 — формула сравнивает имена клиентов в столбце B основной таблицы с именем в ячейке G1. Если есть совпадение, возвращается 1, иначе 0. Поэтому имена клиентов, отличающиеся от указанных в ячейке G1, отбрасываются, ведь все мы знаем, что умножение на ноль равно нулю.
Поскольку наша формула является формулой массива, она повторяет описанные выше шаги для каждого значения в массиве поиска. Наконец, функция СУММ вычисляет сумму значений, полученных в результате умножения. Согласитесь, это совсем не сложно?
Комментарий. Для правильной работы функции ПРОСМОТР искомый столбец должен быть отсортирован в порядке возрастания.
ВПР и СУММЕСЛИ – находим и суммируем значения, удовлетворяющие определённому критерию
Функция СУММЕСЛИ в Excel похожа на функцию СУММ, которую мы только что рассмотрели, тем, что она также добавляет значения. Единственное отличие состоит в том, что СУММЕСЛИ суммирует только те значения, которые соответствуют указанным вами критериям. Например, простейшая формула с СУММЕСЛИ:
=СУММЕСЛИ(A2:A10;»>10″)
=СУММЕСЛИ(A2:A10;»>10″)
– суммирует все значения ячеек в диапазоне A2:A10, которые больше 10.
Очень просто, правда? Теперь давайте рассмотрим немного более сложный пример. Предположим, у нас есть таблица, в которой перечислены имена поставщиков и их идентификационные номера (справочная таблица). Также есть еще одна таблица, в которой те же идентификаторы связаны с данными о продажах (основная таблица). Наша задача — найти объем продаж данного продавца. Здесь есть 2 отягчающих обстоятельства:
- Основная таблица содержит множество записей для одного и того же идентификатора в случайном порядке.
- Вы не можете добавить столбец имени поставщика в основную таблицу.
Давайте напишем формулу, которая находит все продажи, сделанные данным продавцом, а также суммирует найденные значения.
Прежде чем мы начнем, напомню синтаксис функции СУММЕСЛИ):
СУММЕСЛИ(диапазон,критерий,[сумма_диапазона])
СУММЕСЛИ(диапазон, критерии, [сумма_диапазон])
- range (диапазон): Аргумент говорит сам за себя. Это просто диапазон ячеек, которые вы хотите оценить на основе заданных критериев.
- критерий (критерий): Условие, которое сообщает формуле, какие значения нужно добавить. Это может быть число, ссылка на ячейку, выражение или другая функция Excel.
- summation_range (диапазон_суммирования) — необязательный, но очень важный для нас аргумент. Определяет диапазон добавляемых связанных ячеек. Если не указано, Excel добавляет значения ячеек в первый аргумент функции.
Собрав все вместе, давайте определим третий аргумент нашей функции СУММЕСЛИ. Как вы помните, мы хотим суммировать все продажи, сделанные определенным продавцом, имя которого находится в ячейке F2 (см рисунок выше).
- range (диапазон): Так как мы ищем по ID продавца, значениями этого аргумента будут значения в столбце B основной таблицы (Main table). Вы можете указать диапазон B:B (весь столбец) или при преобразовании данных в таблицу использовать имя столбца Main_table[ID].
- критерий (критерий): Так как имена продавцов записаны в справочной таблице (Lookup Table), мы используем функцию ВПР, чтобы найти идентификатор, соответствующий данному продавцу. Имя пишется в ячейке F2, поэтому для поиска используем формулу:
ВПР($F$2, таблица поиска, 2, ЛОЖЬ)
ВПР($F$2;таблица_просмотра;2;ЛОЖЬ)Конечно, вы можете ввести имя в качестве значения поиска непосредственно в функцию ВПР, но лучше использовать абсолютную ссылку на ячейку, так как это создает общую формулу, которая будет работать для любого значения, введенного в эту ячейку.
- sum_range — самая простая часть. Поскольку данные о продажах хранятся в столбце C, который называется Sales, мы просто напишем Main_table[Sales].
Все, что вам нужно сделать, это соединить части в одно целое, и формула СУММЕСЛИ + ВПР готова:
=СУММЕСЛИ(Main_Table[ID],VLOOKUP($F$2,Lookup_Table,2,FALSE),Main_Table[Продажи])
=СУММЕСЛИ(Основная_таблица[ID];ВПР($F$2;Справочная_таблица;2;ЛОЖЬ);Основная_таблица[Продажи])
