Мой коллега однажды спросил меня, как использовать формулы Excel, чтобы вычислить сумму абсолютных значений в указанном диапазоне. Этот вопрос довольно регулярно всплывает на форумах, и многие пользователи часто испытывают большие трудности при работе с этим, казалось бы, простым действием.
К сожалению, в Microsoft Excel не существует встроенной функции способной выполнить суммирование по модулю, поэтому Вы должны немного поработать, чтобы получить правильный ответ.
Вот наши данные:
Мы видим, что сумма чисел в диапазоне A2:A8 дает результат −60:
= -10 + 10 + 20 + -20 + 30 + -40 + -50 = -60
= 10 + 10 + 20 + 20 + 30 + 40 + 50 = 180
Вариант 1 — Использование вспомогательного столбца
На мой взгляд, лучший способ подсчета суммы абсолютных значений в Excel — использовать вспомогательный столбец. В ячейку B2 вводим формулу:
=ABS(A2)
=СУММ(B2:B8) =SUM(B2:B8)
Для подсчета суммы в строке итогов используется функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ (SUBTOTAL). Это универсальная функция, которая может выполнять суммирование, так же как и функция СУММ (SUM). Но есть и существенные отличия, например, ПРОМЕЖУТОЧНЫЕ.ИТОГИ (SUBTOTAL) полностью игнорирует числа, которые были скрыты вручную или с помощью фильтрации. Есть еще несколько отличий, но это мало относится к теме данной статьи.
Это без сомнения очень хороший способ, но что же делать, когда необходимо все уместить в одну формулу без каких-либо вспомогательных столбцов?
Вариант 2 — Использование функции СУММ в формуле массива или СУММПРОИЗВ
Использовать формулу массива или СУММПРОИЗВ (SUMPRODUCT) для решения такой задачи — очень грубый подход!
Функция СУММ в формуле массива:
=СУММ(ABS(A2:A8)) =SUM(ABS(A2:A8))
Формула с СУММПРОЗВ:
=СУММПРОИЗВ(ABS(A2:A8)) =SUMPRODUCT(ABS(A2:A8))
Вариант 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
