Один мой коллега однажды спросил меня, как использовать формулы 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 были автоматически суммированы, и результат отображался в отдельной строке.
Функция ПРОМЕЖУТОЧНЫЙ ИТОГ используется для вычисления суммы в итоговой строке. Это универсальная функция, которая может выполнять суммирование так же, как функция СУММ. Но есть и существенные различия, например, SUBTOTAL полностью игнорирует числа, которые были скрыты вручную или с помощью фильтрации. Есть еще несколько отличий, но они не имеют ничего общего с предметом этой статьи.
Преимущество метода вспомогательного столбца заключается в том, что он дает вам большую гибкость, если вам понадобится использовать данные позже, например, в форме таблицы или сводной таблицы. Кроме того, вспомогательный столбец можно использовать для сортировки номеров форм.
Это, несомненно, отличный способ, но что делать, если вам нужно поместить все в одну формулу без дополнительных столбцов?
Вариант 2 — Использование функции СУММ в формуле массива или СУММПРОИЗВ
Использование формулы массива или СУММПРОИЗВ для решения такой проблемы — очень грубый подход!
Функция СУММ в формуле массива:
= СУММ (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», «
Если мы возьмем первый вариант формулы, мы получим следующее:
= 60 — (- 120) = 180
Для полноты посчитаем сумму по второму варианту:
= СУММ ({60, -120} * {1, -1}) = СУММ ({60,120}) = 180
Думаю, теперь вы знаете все основные способы вычисления суммы абсолютных значений в Excel. Если вы используете другой подход к добавлению номеров модулей, поделитесь им в комментариях. Удачи!