В этом руководстве вы найдете несколько интересных примеров, которые показывают, как использовать функцию ВПР вместе с СУММ или СУММЕСЛИ в Excel для поиска и суммирования значений по одному или нескольким критериям.
Вы пытаетесь создать сводной файл в Excel, который будет идентифицировать все вхождения определенного значения и суммировать другие значения, связанные с ним? Или вам нужно найти в массиве все значения, удовлетворяющие заданному условию, а затем просуммировать связанные значения с другого листа? А может быть, перед вами стоит еще более сложная задача, например, просмотреть таблицу всех счетов-фактур вашей компании, найти среди них счета-фактуры конкретного продавца и суммировать их?
Задания могут различаться, но смысл один - вы должны найти и просуммировать значения по одному или нескольким критериям в Excel. Каковы эти ценности? Любой номер. Каковы эти критерии? Любой... От числа или ссылки на ячейку, содержащей нужное значение, до логических операторов и результатов формулы Excel.
Итак, есть ли в Microsoft Excel какой-либо функционал, способный справиться с описанными задачами? Конечно! Решение заключается в объединении функций ВПР или ПРОСМОТР с функциями СУММ или СУММЕСЛИ. Приведенные ниже примеры формул помогут вам понять, как работают эти функции и как их использовать с реальными данными.
Обратите внимание, что эти примеры предназначены для опытных пользователей, знакомых с основными принципами и синтаксисом функции ВПР. Если вам еще далеко до этого уровня, рекомендуем обратить внимание на первую часть туториала - функция ВПР в Excel: синтаксис и примеры.
- ВПР и СУММ - суммируют все найденные совпадающие значения
- Другие расчеты с ВПР (СРЗНАЧ, МАКС., МИН)
- DISPLAY и SUM — поиск в массиве и суммирование связанных значений
- ВПР и СУММЕСЛИ - суммирует значения, удовлетворяющие определенному критерию
ВПР и СУММ в Excel – вычисляем сумму найденных совпадающих значений
Если вы работаете с числовыми данными в Excel, довольно часто вам нужно не только извлечь связанные данные из другой таблицы, но и просуммировать несколько столбцов или строк. Для этого вы можете комбинировать функции СУММ и ВПР, как показано ниже.
Допустим, у нас есть список продуктов с данными о продажах за несколько месяцев, с отдельным столбцом для каждого месяца. Источник данных — Ежемесячный отчет о продажах:
Теперь нам нужно создать общую таблицу с суммой продаж для каждого продукта.
Решение этой проблемы заключается в использовании массива констант в аргументе col_index_num (номер_столбца) функции ВПР. Вот пример формулы:
=СУММ(ВПР(значение поиска, диапазон поиска, {2,3,4}, ЛОЖЬ))
=СУММ(ВПР(искомое значение;таблица;{2,3,4};ЛОЖЬ))
Как видите, мы использовали массив {2,3,4} в качестве третьего аргумента для многократного поиска в одной и той же ВПР, чтобы получить сумму значений в столбцах 2, 3 и 4.
Теперь давайте применим эту комбинацию ВПР и СУММ к данным в нашей таблице, чтобы найти общую сумму продаж в столбцах от B до M:
=СУММ(ВПР(B2;'Ежемесячные продажи'!$A$2:$M$9,{2,3,4,5,6,7,8,9,10,11,12,13},ЛОЖЬ))
=СУММ(VOPSPACE(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
Мы только что рассмотрели пример того, как можно извлечь значения из нескольких столбцов таблицы и вычислить их сумму. Точно так же вы можете выполнять другие математические операции над результатами, возвращаемыми функцией OPSLAKK. Вот несколько примеров формул:
Вычисляем среднее:
{=СРЗНАЧ(ВПР(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},ЛОЖЬ))}
{=МИН(ВПР(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 основной таблицы. Умножение количества продукта на цену, возвращаемую функцией OPSLAKK, дает нам стоимость каждого купленного продукта.
- $B$2:$B$10=$G$1 — формула сравнивает имена клиентов в столбце B основной таблицы с именем в ячейке G1. Если есть совпадение, возвращается 1, иначе 0. Таким образом, имена клиентов, которые отличаются от указанных в ячейке G1, отбрасываются, потому что все мы знаем, что умножение на ноль дает ноль.
Поскольку наша формула является формулой массива, она повторяет описанные выше шаги для каждого значения в массиве поиска. Наконец, функция СУММ вычисляет сумму значений, полученных в результате умножения. Согласитесь, совсем не сложно?
Комментарий. Для правильной работы функции ПРОСМОТР искомый столбец должен быть отсортирован в возрастающем порядке.
ВПР и СУММЕСЛИ – находим и суммируем значения, удовлетворяющие определённому критерию
Функция СУММЕСЛИ в Excel похожа на функцию СУММ, которую мы только что рассмотрели, тем, что она также суммирует значения. Единственное отличие состоит в том, что СУММЕСЛИ суммирует только те значения, которые соответствуют указанным вами критериям. Например, простейшая формула с СУММЕСЛИ:
=СУММ.ЕСЛИ(A2:A10;">10")
=СУММ.ЕСЛИ(A2:A10;">10")
– суммирует все значения ячеек в диапазоне A2:A10, которые больше 10.
Очень просто, правда? Теперь давайте рассмотрим немного более сложный пример. Предположим, у нас есть таблица, в которой перечислены имена продавцов и их идентификационные номера (справочная таблица). Кроме того, есть еще одна таблица, в которой те же идентификаторы привязаны к данным о продажах (Основная таблица). Наша задача — найти объем продаж для данного продавца. Здесь есть 2 отягчающих обстоятельства:
- Основная таблица содержит множество записей для одного и того же идентификатора в случайном порядке.
- Вы не можете добавить столбец имени поставщика в основную таблицу.
Давайте напишем формулу, которая будет находить все продажи данного продавца, а также суммировать найденные значения.
Прежде чем мы начнем, напомню синтаксис функции СУММЕСЛИ):
СУММЕСЛИ(площадь;критерий;[сумма_площадь])
СУММЕСЛИ(диапазон; критерии; [сумма_диапазон])
- range (диапазон) — аргумент не требует пояснений. Это просто ряд ячеек, которые вы хотите оценить в соответствии с заданными критериями.
- критерии (criterion) - условие, которое сообщает формуле, какие значения суммировать. Может быть числом, ссылкой на ячейку, выражением или другой функцией Excel.
- summation_range (диапазон_суммирования) — необязательный, но очень важный для нас аргумент. Он определяет диапазон связанных ячеек для суммирования. Если не указано, Excel суммирует значения ячеек в первом аргументе функции.
С учетом всего сказанного давайте определим третий аргумент нашей функции СУММЕСЛИ. Как вы помните, мы хотим суммировать все продажи конкретного продавца, имя которого указано в ячейке F2 (см рисунок выше).
- area (область) — так как мы ищем ID продавца, значениями этого аргумента будут значения в столбце B основной таблицы (main table). Вы можете указать диапазон B:B (весь столбец) или при преобразовании данных в таблицу использовать имя столбца Main_table[ID].
- критерии (criterion) - так как имена продавцов записаны в справочной таблице (Lookup table), используем функцию ВПР для нахождения ID, соответствующего данному продавцу. Имя пишется в ячейке F2, поэтому для поиска используем формулу:
ПРОСМОТР($F$2,Таблица поиска,2,ЛОЖЬ)
ПРОСМОТР($F$2;Таблица поиска;2;ЛОЖЬ)Конечно, вы можете ввести имя как значение поиска непосредственно в функцию ВПР, но лучше использовать абсолютную ссылку на ячейку, так как это создает общую формулу, которая будет работать для любого значения, введенного в эту ячейку.
- sum_range — самая простая часть. Поскольку данные о продажах хранятся в столбце C, который называется Sales, мы будем просто писать MainTable[Sales].
Все, что вам нужно сделать, это соединить части в одно целое, и формула СУММЕСЛИ+ВПР будет готова:
=СУММЕСЛИ(MainTable[ID],VLOOKUP($F$2,LookupTable;2,FALSE),MainTable[Продажи])
=СУММ ЕСЛИ(ОсновнаяТаблица[ID];ПРОСМОТР($F$2;ТаблицаПоиска;2;ЛОЖЬ);ОсновнаяТаблица[Продажи])
Урок подготовила для вас команда сайта office-guru.ru
Источник: https://www.ablebits.com/office-addins-blog/2014/08/05/excel-vlookup-sum-sumif/
Перевод: Антон Андронов
Правила перепечатки
Дополнительные руководства по Microsoft Excel
