Суммирование значений по группам в Excel

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

• Суммирование значений по группам — промежуточные итоги в исходной таблице данных
• Суммирование значений по группам — промежуточные итоги в другом месте

Суммирование значений по группам — промежуточные итоги в исходной таблице данных

Общий синтаксис для суммирования значений по группам в Excel должен выглядеть следующим образом:

• group_name: ячейка со значением Имя группы, которое требуется просуммировать;
• cell_above_group_name: ячейка над Имя группы;
• group_range: диапазон ячеек, содержащий Имя группы;
• sum_range: диапазон ячеек, соответствующий указанному имени группы, значения которых необходимо просуммировать.

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

1. Скопируйте и вставьте приведённую ниже формулу в пустую ячейку рядом с вашими данными:

• Примечание: в формуле:
• A1— это ячейка заголовка, а A2— первая ячейка, содержащая название товара, которое вы хотите использовать;
• A2:A13— список, содержащий названия товаров, по которым требуется выполнить суммирование;
• B2:B13 — столбец данных, по которому нужно получить промежуточный итог.

2. Затем перетащите маркер заполнения вниз до ячеек, к которым нужно применить формулу, — и промежуточные итоги для каждого наименования товара будут рассчитаны автоматически (см. скриншот):

Объяснение формулы:

=IF(A2=A1,«»,SUMIF($A$2:$A$13,A2,$B$2:$B$13))

• SUMIF($A$2:$A$13,A2,$B$2:$B$13): эта функция СУММЕСЛИ суммирует только те значения в диапазоне B2:B13, для которых соответствующие ячейки в диапазоне A2:A13 совпадают с критерием A2.
• IF(A2=A1,«»,SUMIF($A$2:$A$13,A2,$B$2:$B$13)): эта функция ЕСЛИ проверяет, совпадает ли значение в каждой ячейке столбца A со значением в ячейке над ней. Например, если значение в ячейке A2 совпадает со значением в A1, возвращается пустая строка («»); если значения различаются — выполняется функция СУММЕСЛИ и возвращается её результат.

Суммирование значений по группам — промежуточные итоги в другом месте

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

1. Сначала извлеките уникальные названия групп с помощью следующей формулы массива, а затем одновременно нажмите клавиши Ctrl + Shift + Enter, чтобы получить первый результат.

• Примечание: в формуле:
• A2:A13— диапазон ячеек, из которого необходимо извлечь все уникальные значения;
• D1 — ячейка над введённой вами формулой.

2. Затем выделите ячейку с формулой и перетащите маркер заполнения вниз, пока не появятся все названия товаров (см. скриншот):

3. Теперь вы можете суммировать значения по имени группы, которые вы извлекли. Для этого используйте функцию СУММЕСЛИ: введите приведённую ниже формулу в пустую ячейку — в нашем примере это ячейка E2.

4. Затем перетащите маркер заполнения вниз, чтобы скопировать формулу и получить общее количество заказов для остальных групп (см. скриншот):

Используемая относительная функция:

Дополнительные статьи: