Как в 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. В нем мы можем видеть повторяющиеся значения, помещенные в разные ячейки.

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

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

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

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

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

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

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

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

Формула в нашем случае будет следующей.

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

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

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