Excel сумма абсолютных значений

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

Найти сумму модулей значений в определенном диапазоне (например, в

А1:А10

) не представляет труда, создав дополнительный столбец с формулой

=ABS(A1)

. Затем функцией

СУММ()

можно подсчитать их сумму.

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

=СУММПРОИЗВ(ABS(A1:A10))

.

Другой вариант суммирования предполагает использование

формулы массива

=СУММ(ABS(A1:A10))

. После ввода формулы, нужно вместо

ENTER

нажать

CTRL

+

SHIFT

+

ENTER

.

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

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

Вот наши данные:

Мы видим, что сумма чисел в диапазоне A2:A8 дает результат -60:

= -10 + 10 + 20 + -20 + 30 + -40 + -50 = -60

Если бы мы рассматривали абсолютные значения (числа без знака «-«), то результат был бы 180:

= 10 + 10 + 20 + 20 + 30 + 40 + 50 = 180

Вариант 1 — Использование вспомогательного столбца

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

=ABS(A2)

Затем протянем ее до ячейки B8. Функция ABS возвращает модуль числа. Так что теперь мы можем просто просуммировать диапазон B2:B8 и это даст нам результат 180.

=СУММ(B2:B8)
=SUM(B2:B8)

В моем примере диапазон A1:A8 – это полноценная таблица данных. Поэтому при добавлении формулы =ABS(A2) в ячейку В2, Excel расширил таблицу и автоматически заполнил все ячейки в столбце. Далее я перешел на вкладку Конструктор (Design), которая находится в группе вкладок Работа с таблицами (Table tools), и поставил галочку возле опции Строка итогов (Total Row). Все значения в столбце B автоматически просуммировались, а результат отобразился в отдельной строке.

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

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

Это без сомнения очень хороший способ, но что же делать, когда необходимо все уместить в одну формулу без каких-либо вспомогательных столбцов?

Вариант 2 — Использование функции СУММ в формуле массива или СУММПРОИЗВ

Использовать формулу массива или СУММПРОИЗВ (SUMPRODUCT) для решения такой задачи – очень грубый подход!

Функция СУММ в формуле массива:

=СУММ(ABS(A2:A8))
=SUM(ABS(A2:A8))

При вводе формулы массива не забудьте нажать Ctrl+Shift+Enter.

Формула с СУММПРОЗВ:

=СУММПРОИЗВ(ABS(A2:A8))
=SUMPRODUCT(ABS(A2:A8))

Учитывая, что можно использовать более эффективную функцию СУММЕСЛИ (SUMIF), чтобы получить тот же результат (см. вариант 3), путь с использованием этих двух формул становится непредпочтительным. Обе формулы прекрасно работают с небольшими диапазонами данных, уверен, вы даже не ощутите разницу. Если же понадобится просуммировать большое количество значений, скорость работы станет заметно замедляться.

Вариант 3 — Использование СУММЕСЛИ

Думаю, что данный подход самый удобный из всех ранее перечисленных. С помощью функции СУММЕСЛИ (SUMIF) значения делятся на 2 массива: с положительными и отрицательными числами и суммируются. Ноль по понятным причинам игнорируется. Затем мы просто вычитаем из положительной суммы отрицательную (т.е. суммируем их). Формула выглядит вот так:

=СУММЕСЛИ(A2:A8,">0")-СУММЕСЛИ(A2:A8,"<0")
=SUMIF(A2:A8,">0")-SUMIF(A2:A8,"<0")

Можно записать и в таком виде:

=СУММ(СУММЕСЛИ(A2:A8,{">0","<0"})*{1,-1})
=SUM(SUMIF(A2:A8,{">0","<0"})*{1,-1})

Если мы возьмем первый вариант формулы, получим следующее:

= 60-(-120) = 180

Для полноты картины, подсчитаем сумму с помощью второго варианта:

= СУММ({60,-120}*{1,-1}) = СУММ({60,120}) = 180

Думаю, что теперь вы знаете все основные способы подсчета суммы абсолютных значений в Excel. Если вы используете другой подход для суммирования чисел по модулю, поделитесь им в комментариях. Удачи!

Перейти к содержимому