Суммирование по модулю в Excel

Автор: | 07.05.2022

Один мой коллега однажды спросил меня, как использовать формулы 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 введите формулу:

= АБС (A2)

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

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

В моем примере диапазон A1: A8 представляет собой полную таблицу данных. Поэтому при добавлении формулы = ABS (A2) в ячейку B2 Excel расширил таблицу и автоматически заполнил все ячейки в столбце. Поэтому я перешел на вкладку «Дизайн», которая находится в группе вкладок «Инструменты для таблиц», и поставил флажок рядом с параметром «Строка итогов». Все значения в столбце B были автоматически суммированы, и результат отображался в отдельной строке.

Форма суммы в Excel

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

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

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

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

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

Форма суммы в Excel

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

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

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

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

= СУММПРОИЗВ (АБС (A2: A8))
= СУММПРОИЗВ (АБС (A2: A8))

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

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

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

= СУММЕСЛИ (LA2: LA8; «> 0″) — СУММЕСЛИ (LA2: LA8; » = СУММЕСЛИ (LA2: LA8; «> 0») — СУММЕСЛИ (LA2: LA8; «

Вы можете написать в этой форме:

= СУММ (СУММЕСЛИ (LA2: LA8; {«> 0″, » = СУММ (СУММЕСЛИ (LA2: LA8; {«> 0», «

Форма суммы в Excel

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

= 60 — (- 120) = 180

Для полноты посчитаем сумму по второму варианту:

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

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