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

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

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

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

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

• имя_группы: Ячейка с названием группы, которую вы хотите суммировать;
• ячейка_выше_группы: Ячейка над названием группы;
• диапазон_групп: Диапазон ячеек, содержащих названия групп;
• диапазон_сумм: Диапазон ячеек, соответствующий указанному имени группы для суммирования.

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

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

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

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

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

=ЕСЛИ(A2=A1,"",СУММЕСЛИ($A$2:$A$13,A2,$B$2:$B$13))

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

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

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

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

• Примечание: В формуле:
• A2:A13 – это диапазон ячеек, из которого нужно извлечь все уникальные значения;
• D1 – это ячейка выше той, куда вы ввели формулу.

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

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

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

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

Больше статей: