Модуль числа в Excel возвращает функция ABS: =ABS(A2) превратит −15 в 15, а положительные числа оставит без изменений. Чтобы поставить модуль в формуле, оберните в ABS всё выражение: =ABS(B2-A2) — разница без учёта знака, =СУММПРОИЗВ(ABS(B2:B10)) — сумма модулей диапазона. Отдельного знака модуля (|x|) в Excel нет — только функция ABS.
| Раздел функций | Математические |
| Название и описание на английском | ABS |
| Волатильность | Не волатильная |
См. также, и не путать: ABC-анализ в Excel
Что делает функция ABS
Функция ABS возвращает модуль числа. Модуль числа по определению всегда положительное число. Модуль отрицательного числа равен его произведению на -1.
Синтаксис функции ABS
=ABS(ЯчейкаИлиЗначение)
| A | B | |
|---|---|---|
| 1 | Число | Модуль |
| 2 | -15 | 15=ABS(A2) |
| 3 | 15 | 15=ABS(A3) |
| 4 | 0 | 0=ABS(A4) |
| 5 | -3,7 | 3,7=ABS(A5) |
ABS убирает знак минус и не меняет положительные числа и ноль.
В английском Excel: =ABS(A2)
Примеры формул с функцией ABS
Пример 1
Таблица выше была создана руководителем колцентра, для того, чтобы выявить проблемные часы его работы.
Задача менеджера - минимизировать затрачиваемые человеческие ресурсы, при этом сохраняя максимальную скорость ответа на входящие звонки.
Таблица отражает среднюю длительность ожидания клиента на линии. Как резкое увеличение от часа к часу, так и резкое уменьшение показателя - плохой сигнал.
В столбце D используется обычное вычитание значения предыдущего часа.
Руководитель использует функцию ABS, чтобы определить часы, когда разница максимальна, чтобы изменить рабочее расписание сотрудников и "сгладить" график.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Час | Ожидание, сек | Изменение | Изменение по модулю |
| 2 | 14:00 | 40 | ||
| 3 | 15:00 | 55 | 15=B3-B2 | 15=ABS(B3-B2) |
| 4 | 16:00 | 120 | 65=B4-B3 | 65=ABS(B4-B3) |
| 5 | 17:00 | 60 | -60=B5-B4 | 60=ABS(B5-B4) |
Рост на 65 секунд и падение на 60 одинаково важны. Без ABS падение выглядело бы «хорошим» отрицательным числом.
В английском Excel: =ABS(B3-B2)
Условное форматирование помогает быстро выявить, что стоит задерживать часть сотрудников на один час в 16:00 в этот день, а также выводить больше сотрудников в самом начале смены.
Пример 2.
Тот же самый руководитель использует похожую таблицу, но уже по всем дням недели. Чтобы не загромождать таблицу лишними вычислениями, он использует функцию ABS в формуле массива.
Как происходит расчет в формуле? Сначала для каждого значения массива высчитывается его абсолютное значение функцией ABS, а далее уже эти абсолютные значения суммируются.
Как и все формулы массива, такая формула вводится без фигурных скобок, но ввод в ячейку производится сочетанием клавиш Ctrl + Shift + Enter.
| A | B | C | |
|---|---|---|---|
| 1 | Отклонение | Сумма модулей | |
| 2 | -20 | 95=СУММПРОИЗВ(ABS(A2:A5)) | |
| 3 | 15 | ||
| 4 | -40 | ||
| 5 | 20 |
Обычная СУММ дала бы −25: отклонения в разные стороны погасили бы друг друга. СУММПРОИЗВ работает без Ctrl+Shift+Enter во всех версиях; в Excel 365 подойдёт и =СУММ(ABS(A2:A5)).
В английском Excel: =SUMPRODUCT(ABS(A2:A5))
Формула высчитывает кумулятивное значение "проблемных" часов в течение каждого дня и позволяет руководителю выявить дни с самым несбалансированным графиком.
Как поставить модуль в формуле Excel
Знака |x| в Excel нет, поэтому модуль всегда записывают функцией ABS вокруг нужного выражения. Её можно вкладывать в любые формулы: в разницу, в условия, в поиск максимального отклонения.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | План | Факт | Отклонение | Отклонение > 10%? |
| 2 | 100 | 95 | 5=ABS(B2-A2) | ЛОЖЬ=ABS(B2/A2-1)>10% |
| 3 | 200 | 240 | 40=ABS(B3-A3) | ИСТИНА=ABS(B3/A3-1)>10% |
| 4 | 150 | 148 | 2=ABS(B4-A4) | ЛОЖЬ=ABS(B4/A4-1)>10% |
ABS внутри условия позволяет одной формулой поймать отклонение и вверх, и вниз.
В английском Excel: =ABS(B2/A2-1)>10%
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Число | ABS | ЕСЛИ | КОРЕНЬ из квадрата |
| 2 | -8 | 8=ABS(A2) | 8=ЕСЛИ(A2<0;-A2;A2) | 8=КОРЕНЬ(A2^2) |
| 3 | 5 | 5=ABS(A3) | 5=ЕСЛИ(A3<0;-A3;A3) | 5=КОРЕНЬ(A3^2) |
Все три формулы дают одно и то же, но ABS короче и понятнее.
Другие математические функции
ЗНАК, МУМНОЖ, НЕЧЁТ, НОД, НОК, ОКРВВЕРХ, ОКРВНИЗ, ОКРУГЛ, ОКРУГЛВВЕРХ, ОКРУГЛВНИЗ, ОКРУГЛТ, ОСТАТ, ОТБР, ПИ, ПРОИЗВЕД, РИМСКОЕ, СЛУЧМЕЖДУ, СЛЧИС, СТЕПЕНЬ, СУММ, СУММЕСЛИ, СУММПРОИЗВ, ФАКТР, ЦЕЛОЕ, ЧАСТНОЕ, ЧЁТН, ЧИСЛКОМБ
Частые ошибки с ABS
- Минус остался. ABS применили не ко всей разнице: =ABS(B2)-A2 вместо =ABS(B2-A2).
- #ЗНАЧ!. В аргументе текст, который нельзя превратить в число, например «−15» со знаком минус-тире вместо дефиса. Замените символ ПОДСТАВИТЬ.
- Сумма модулей посчитана неверно в Excel 2019. =СУММ(ABS(…)) по диапазону нужно вводить через Ctrl+Shift+Enter — или используйте СУММПРОИЗВ.
Модуль в Excel — частые вопросы
Как сделать модуль в Excel?
Функцией =ABS(A2) — она возвращает число без знака.
Как поставить модуль в формуле?
Оберните выражение в ABS: =ABS(B2-A2). Знака |x| в Excel нет.
Что значит ABS в Excel?
ABS — от английского absolute, «абсолютная величина», то есть модуль числа. В русском Excel функция называется так же.
Как посчитать сумму модулей?
Формулой =СУММПРОИЗВ(ABS(A2:A10)).
Применить модуль, округлить, умножить или прибавить число ко всему столбцу прямо на месте можно в пару кликов с !SEMTools.
!SEMTools решает эту и сотни других задач в пару кликов!
Итоги: модуль числа в Excel
| Задача | Формула |
|---|---|
| Модуль числа | =ABS(A2) |
| Модуль разницы | =ABS(B2-A2) |
| Сумма модулей | =СУММПРОИЗВ(ABS(A2:A10)) |
| Отклонение больше порога | =ABS(B2/A2-1)>10% |
Надеюсь, на этой странице вы нашли то, что искали. Модуль в Excel — это всегда ABS вокруг нужного выражения.