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

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

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

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

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

Выборочное суммирование – это следующий шаг после изучения стандартной арифметической операции складывания нескольких значений. Если научиться читать и использовать ее, можно приблизиться к могуществу в вопросах работы с 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 рублей.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

ОфисГуру
Adblock
detector