Как суммировать повторяющиеся значения в Excel

Суммирование — популярная арифметическая операция в Excel. Предположим, у нас есть список товаров в таблице, и нам нужно получить их общую стоимость. Для этого нужно использовать функцию SUM. Или компания хочет определить общий объем потребления электроэнергии за определенный период. Опять же, вам необходимо обобщить эти данные.

Функция SUM может использоваться не только самостоятельно, но и как компонент других функций.

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

Выборочное суммирование в Excel

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

Функция СУММЕСЛИ

Предположим, у нас есть такой набор данных.

Это отчет, предоставленный складом овощехранилища. На основании этой информации нам необходимо сделать следующее:

1. Определите, сколько осталось на складе определенного товара.
2. Рассчитайте остатки запасов вместе с ценой, соответствующей определенным пользователем правилам.

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

1. Диапазон. Это набор ячеек, который необходимо проанализировать на соответствие определенному критерию. В этом диапазоне могут быть не только числовые, но и текстовые значения.
2. Состояние. Этот аргумент определяет правила, по которым будут выбираться данные. Например, только значения, соответствующие слову «Груша» или числам больше 50.
3. диапазон суммирования. Если в этом нет необходимости, вы можете опустить эту опцию. Его следует использовать, если в качестве диапазона проверки условия используется набор текстовых значений. В этом случае вам необходимо указать дополнительный диапазон с числовыми данными.

Для выполнения первой поставленной нами цели необходимо выделить ячейку, в которую будет записан результат вычислений, и записать туда следующую формулу: =СУММЕСЛИ(A2:A9;”Белый виноград”;B2:B9).

Результатом будет значение 42. Если бы у нас было несколько ячеек со значением «Белый виноград», то формула вернула бы сумму всех позиций этого плана.

СУММА функция

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

1. диапазон суммирования. Здесь этот аргумент означает то же, что и в предыдущем примере.
2. Диапазон условий 1 — это набор ячеек, в которых можно выбрать те, которые соответствуют критериям, описанным в аргументе ниже.
3. Условие 1. Правило для предыдущего рассуждения. Функция выберет только те ячейки из диапазона 1, которые соответствуют условию 1.
4. Диапазон условий 2, условие 2 и т. д.

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

Предположим, нам необходимо определить, какова общая масса оставшихся на складе яблок стоимостью более 100 рублей. Для этого в ячейку, в которой должен быть конечный результат, запишите следующую формулу: =СУММЕСЛИМН(B2:B9;A2:A9;»яблоки*»;C2:C9;»>100″)

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

Обратите внимание на звездочку (*) в качестве условия поиска. Это указывает на то, что за ним могут следовать любые другие значения.

Как суммировать повторяющиеся строки в таблице с помощью умной таблицы

Предположим, у нас есть такая таблица. Это было сделано с помощью инструмента Smart Table. В нем мы можем увидеть повторяющиеся значения, размещенные в разных ячейках.

В третьем столбце указаны цены на эти товары. Допустим, мы хотим знать, сколько в общей сложности будут стоить повторяющиеся продукты. Что мне нужно делать? Сначала вам нужно скопировать все повторяющиеся данные в другой столбец.

После этого нужно перейти на вкладку «Данные» и нажать кнопку «Удалить дубликаты».

После этого появится диалоговое окно, в котором необходимо подтвердить удаление повторяющихся значений.

Специальное преобразование вставки

Тогда у нас останется список только тех значений, которые не повторяются.

Нам нужно их скопировать и перейти на вкладку «Главная». Там вам нужно открыть меню, расположенное под кнопкой «Вставить». Для этого нажимаем на стрелочку и в появившемся списке находим пункт «Специальная вставка». Появится такое диалоговое окно.

Транспонирование строк в столбцы

Установите флажок «Транспонировать» и нажмите «ОК». Этот элемент меняет местами столбцы и строки. После этого запишем в произвольную ячейку функцию СУММЕСЛИ.

Формула в нашем случае будет выглядеть так.

Затем с помощью маркера автозаполнения заполните оставшиеся ячейки. Вы также можете использовать функцию ПРОМЕЖУТОЧНЫЕ ИТОГИ для суммирования табличных значений. Но сначала необходимо установить фильтр для смарт-таблицы, чтобы функция учитывала только повторяющиеся значения. Для этого нажмите на значок стрелки в заголовке столбца, а затем установите флажки напротив тех значений, которые вы хотите отобразить.

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

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