Excel Abs Function
The ABS function takes just one argument, number, which must be a numeric value. If number is not numeric, ABS returns a #VALUE! error.
Basic example#
Negative numbers become positive, while positive numbers and zero (0) are unaffected:
Absolute Variance#
Calculating the variance between two numbers is a common problem. For example, with a forecast value in A1 and an actual value in B1, you might calculate variance like this: When B1 is greater than A1, variance is a positive number. However, when A1 is greater than B1, the result will be negative. To ensure the result is a positive number, you can use ABS like this: See a detailed example here.
Counting absolute variances with conditions#
The ABS function can be used together with the SUMPRODUCT function to count absolute variances that meet specific conditions. For example, to count absolute variances greater than 100, you can use a formula like this: This formula is explained in more detail here.
Square root of negative number#
The SQRT function calculates the square root of a number. If you give SQRT a negative number, it will return a #NUM! error: To handle a negative number like a positive number, you can use the ABS function like this:
Calculating tolerance#
To calculate whether a value is within tolerance or not, you can use a formula like this: See a detailed explanation here.
Dave Bruns#
Hi — I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
Excel works!
Рассчитать модуль в Excel, функция ABS и ее применение
Продолжаем серию статей о математических формулах в Excel. Сегодня разберем формулу записи «модуль в Excel». Модуль числа применяется для определения абсолютной величины числа, например, длины отрезка. Ниже мы приводим несколько способов расчета модуля числа в Эксель, основная функция — ABS, а дополнительный расчет при помощи функций ЕСЛИ и КОРЕНЬ.
Как следует из определения, модуль числа — это неотрицательное число, значение самого числа. Т.е. если у нас есть отрицательное число -7, то по модулю оно будет равняться 7. Записывается модуль как две вертикальные линии:
Для чего применяется? Если у нас есть значение вектора равное -7, где минус обозначает его обратное направление, то, чтобы найти длину самого вектора, нам необходимо высчитать модуль числа (т.к. длина не может быть отрицательной величиной).
Так же довольно часто использование модуля можно встретить при расчете отрицательного значения времени, но про этом у нас есть отдельная статья .
Рассчитать модуль в Excel, функция ABS
В большинстве языков программиро вания модуль числа находится функцией ABS (от абсолютное значение, Absolute). Наш любимый Excel не исключение.
В ячейке введем формулу:
Как следует из описания функции, значение такой формулы будет равно 7.
Как посчитать модуль через ЕСЛИ, альтернативный способ
По сути, действие функции «Модуль» — это определение отрицательное число или нет и передача его значения. То есть легко справится основная функция условия =ЕСЛИ().
Я лично про ABS всегда забываю и пишу через ЕСЛИ.
Модуль числа и КОРЕНЬ
Как мне подсказали, есть особенно хитрый способ найти модуль числа через функцию КОРЕНЬ. Возводим число во вторую степень и находим из выражения корень.
Сам бы не додумался.
Пример файла с 3мя расчетами прикладываю тут .
Модуль числа в VBA
Как мы уже сказали, в большинстве языков программирования нахождение модуль проходит при помощи функции ABS. VBA не исключение.
Чтобы записать расчет в коде, пропишите примерно следующее:
Здесь А будет равно 7.
В целом вот так, если что-то нужно пояснить пишите в комментарии и не стесняйтесь.
Функция ABS в Excel: за что отвечает и что считает
Excel
Модуль числа в Excel
Давайте узнаем, как посчитать модуль числа в Excel с помощью функции АБС, а также рассмотрим несколько различных вариантов вычисления формулы модуля.
Модуль (или абсолютное значение) числа в математике — это неотрицательное число, значение которого зависит от типа числа.
Если число a неотрицательно, модуль равен самому числу (a для a ≥ 0), если отрицательно, модуль равен его положительному значению (-a для a
АБС (число)
Возвращает модуль (абсолютное значение) числа.
- Число (обязательно) — это действительное число, модуль которого необходимо вычислить.
В качестве аргумента функции ставим произвольное вещественное число, в результате получаем его абсолютное значение:
Хотя знак по модулю в математике представлен вертикальной линией |, попытка использовать знак по модулю в Excel для поиска значения по модулю приведет к ошибке, например, при вводе формулы =|-29| Excel выдаст ошибку.
Причины применения функции
В математике модуль числа показывает его абсолютное значение, знак значения не учитывается. С помощью формулы определение записывается так: I-aI = IaI = а.Все значения по модулю положительны.
Программа Excel оперирует числами, за которыми зачастую «прячутся» не абстрактные математические операции, а реальные объекты или бухгалтерские расчеты. Таким образом, достигнутые результаты не могут быть отрицательными.
Например, одни товары были проданы сверх плана, а другие, наоборот, остались на складе. Тогда проданная продукция будет иметь положительную стоимость, а не проданная – отрицательную. Если нужно сложить все отклонения от плана, результатом может быть отрицательное значение или ноль. Использование модуля покажет абсолютную сумму отклонений.
Также оператор находит применение при вычислении значений векторов, которые могут иметь отрицательное значение, сравнении двух элементов в таблице и расчете процентов по кредиту.
Как работает функция ABS
Для начала выясним, зачем нужна функция АБС, если из справки вам это было непонятно. Если вы введете любое число в аргументе ABS, оно будет отображаться как положительное, то есть вычисляется модуль или абсолютное значение, в зависимости от того, что вам больше подходит.
Еще на уроках математики учителя говорили, что модуль числа всегда положителен, даже если само число отрицательно. В общем, для этого и используется ABS в Microsoft Excel — взять модуль числа и отобразить его на экране или использовать в других формулах.
Синтаксис функции ABS
Остановимся на этой теме лишь для того, чтобы вы понимали, какие исходные данные вводятся при заполнении строки функции АБС. На самом деле есть только один аргумент — число, которое нужно вернуть модулю. Вы можете просто ввести любое число или сослаться на ячейку, как и при использовании других функций в Excel.
Выше вы видите окно аргумента функции, где видно, что при использовании АБС достаточно ввести число или ссылку на ячейку, то есть число, где хранится само число, чтобы вернуть его абсолютное значение.
Особенности использования функции ABS в Excel
Функция имеет следующую синтаксическую запись:
Единственным аргументом этой функции (обязательным) является число. Он характеризует некоторое действительное число, которое будет иметь абсолютное значение.
- Функция ABS принимает аргументы, которые являются числовыми значениями, текстовыми представлениями чисел или логическими данными (ИСТИНА, ЛОЖЬ).
- Если в качестве аргумента рассматриваемой функции была передана текстовая строка, которая не может быть преобразована в числовой тип данных (например, =ABS(«текст»)), результатом выполнения будет код ошибки #ЗНАЧ!.
- Эту функцию можно использовать как формулу массива для работы с большими диапазонами данных.
- Модуль числа позволяет определить любую физическую величину, которая математически может быть задана как отрицательное число, но в реальном мире представлена только абсолютным (неотрицательным) значением, например длиной отрезка.
- При написании макроса также может потребоваться вычисление абсолютных значений некоторых чисел. VBA использует одноименную функцию ABS() (как и во многих других языках программирования).
- В Excel есть два альтернативных способа получить абсолютное значение количества:
- с помощью функций КОРЕНЬ и СТЕПЕНЬ с показателем степени 2. Например, результатом вычислений =КОРЕНЬ(СТЕПЕНЬ(A1;2)) всегда будет число из диапазона положительных значений (при условии, что ячейка A1 содержит данные числовой тип);
- с помощью функции ЕСЛИ. Например, чтобы вернуть модуль числа -5, можно использовать запись =ЕСЛИ(A1<0;A1*(-1);A1), где A1 — это ячейка, в которой хранится значение -5.
Использование функции ABS
С синтаксисом и направлением рассматриваемой функции все понятно, так что теперь разберемся на практике. Давайте сначала рассмотрим ситуацию, когда вам просто нужен модуль числа без каких-либо дополнительных вычислений.
- Вы можете объявить функцию =ABS в любом удобном месте, а в скобках написать отрицательное или положительное число, нужный вам модуль.
- В результате вы всегда будете получать положительное число, как вы можете видеть на изображении ниже.
- Таким же образом выбирается ячейка, модуль с номером, который необходимо получить. В этом случае расчеты также проходят успешно и можно продолжать использовать результаты дальше
- .
Как видите, ничего сложного в использовании этой функции нет. У него только один аргумент, так что написать тоже не составит труда. Формула расширяется и может быть применена сразу к группе ячеек. Для этого нужно перетащить уже подготовленную ячейку с результатом вниз в правом нижнем углу, чтобы растянуть ее на все последующие ячейки в матрице данных.
Стандартный вариант
Правильное написание выглядит так: «ABS(X)» или «ABS(ячейка_адрес_с_номером)». Здесь «Х» — число, модуль которого нужно найти, «адрес ячейки с числом» — элемент таблицы, где будет указано абсолютное значение. Существует два способа вставки формулы. Простой вариант:
- Введите значение, для которого вы хотите найти модуль, в ячейку или строку формул. Пусть будет -8. Тогда надпись становится такой: «=ABS(-8)».
- Нажмите Enter и получите результат.
- Выберите нужную ячейку и нажмите на опцию «Вставить формулу», обозначенную символом fx.
- В появившемся списке найдите надпись «ABS», выберите ее и подтвердите.
- Откроется меню Аргумент функции. Здесь вы должны ввести желаемый номер в функцию модуля. Вы можете указать адрес ячейки, в которой находится искомое значение. Для этого нажмите на иконку справа от строки «Число».
- Окно аргумента свернется, в электронной таблице нужно нажать на нужный элемент и еще раз нажать на иконку.
- Снова появится меню с аргументами, где в строке значения будет адрес выбранной ячейки. Подтвердите операцию.
- Функция будет работать в указанном месте.
- Вы можете распространить действие формулы на другие ячейки. Для этого в ячейке с используемой функцией АБС щелкните мышью в правом нижнем углу и, не отпуская кнопку, «потяните» мышь вниз, выделите нужные элементы. К ним будет применена функция.
ABS в формулах
Наряду с простым приведением к модулю функцию можно использовать совместно с формулами и решать сложные задачи. Чтобы узнать, насколько наименьшее отрицательное число меньше наименьшего положительного числа, можно сделать так:
- Есть матрица, где вы должны решить задачу.
- Введите следующий состав в строку формул:
Программа в этом случае вычисляет модуль в каждой из ячеек между B3 и B12. Без использования массивов ABS выдавала бы сообщение об ошибке при такой регистрации. - Результат применения:
Примеры расчетов
Ниже приведены возможные случаи использования функции модуля АБС, как отдельно, так и в составе формулы.
Задача по математике на определение проекции отрезка на оси абсцисс (X) и ординат (Y). По условиям задачи отрезок имеет координаты начала А(-17;-14) и конца В(-39;-56). Решение в Экселе:
- Внесите известные данные в таблицу.
- Проекция на ось X рассчитывается с использованием ABS.
- Таким же образом находится проекция на ось Y. В строке «Число» необходимо указать B5-B3.
- Результат будет показан в таблице.
Расчет расходов компании — здесь нужно сложить отрицательные значения по модулю.
Он представляет собой совокупность финансовых операций в компании за отчетный период. Необходимо определить размер расходов предприятия. По условию расходы указывают со знаком минус:
- Создайте матрицу в Excel.
- Напишите формулу: <=СУММ(ЕСЛИ(B3:B12<0,ABS(B3:B12),0))>
В конце нажмите Ctrl+Shift+Enter, чтобы указать, что используются массивы данных. - Программа анализирует данные ячеек B3-B12, и если есть отрицательные числа, берется их модуль. После проверки всех пунктов отрицательные значения складываются. Положительные данные в расчеты не включаются. Окончательный результат выглядит так:
Как посчитать среднее арифметическое в Excel — все доступные способы
Использование ABS вместе с другими функциями
Остается выяснить, можно ли комбинировать ABS с другими функциями, формируя более сложные формулы, которые могут быть полезны в математических расчетах. Ничто не мешает вставить модуль практически в любой расчет, но в этой статье мы разберемся с этим на самом простом примере.
- Объявим функцию СУММ, которая будет вычислять сумму нескольких чисел в разных ячейках. Сделаем так, чтобы модуль каждого числа брался и после этого происходило сложение. В результате формула будет выглядеть так =СУММ(АБС(А1)+АБС(А3)). Поэтому замените номера ячеек на свои, если хотите использовать эту формулу.
- Учтем тот факт, что все выбранные числа в этой формуле отрицательные, поэтому мы берем их модуль для сложения абсолютных значений.
- В результате мы получаем сложение двух положительных чисел и соответствующей им суммы.
Этот простой способ позволяет игнорировать отрицательные числа, если вам нужно преобразовать их в положительные, а также включить их в свои расчеты. На самом деле ситуаций, когда может понадобиться функция АБС, довольно много, их невозможно охватить в рамках одной статьи и даже не смоделировать, так как у каждого пользователя свои задачи, которые ему необходимо решить в Excel.
Надеюсь, общей информации вам было достаточно, чтобы понять, для чего нужна функция ABS и как вы можете использовать ее в своих целях.
Принцип основан на умножении отрицательных чисел на -1, а положительных чисел на 1. Это выглядит так:
На изображении действие выполняется над ячейкой A4.
КОРЕНЬ
Квадратный корень значения не может быть отрицательным. Следовательно, исходное число возводится в квадрат, а затем извлекается корень.
Практический способ. Если значение отрицательное, оно умножается на -1, иначе ничего не происходит. Вот как это выглядит для ячейки A1:
Язык VBA
Во многих языках программирования модуль находится через ABS. В VBA команда будет выглядеть так: A=Abs(-7). Вот команда Abs для получения абсолютного значения. В этом случае объекту А будет присвоен номер 7.
ABS и опции просты в реализации. Умение ими пользоваться облегчит работу и сэкономит время.
Другие способы
Если вы забыли, как установить модуль с помощью стандартной функции, есть несколько альтернативных способов, которые основаны на математических законах и реализуются в редакторе Microsoft отдельными инструментами:
- Использование формулы ЗНАК, которая возвращает значение, равное единице, нулю или минус единице, в зависимости от исходного числа. Умножая результат на исходную цифру, получаем абсолютное значение. Для отрицательных чисел произойдет умножение, и конечное число будет положительным. Напишем формулу для первой ячейки и воспользуемся функцией автозаполнения.
- Используя функцию ROOT, вы можете создать модуль, извлекая арифметический квадратный корень из квадрата числа, полученного путем умножения того же значения само на себя. Поскольку квадратный корень в Excel всегда возвращает неотрицательное число, этот метод также возвращает абсолютное значение.
Как видите, найти модуль числа в excel всех поколений несложно. Помимо специальной функции, вы можете использовать и другие инструменты, принцип работы которых основан на математических законах. С полученными номерами можно выполнять все операции. Более опытные пользователи могут интегрировать функцию ABS в более крупную формулу, избегая промежуточных шагов, но для этого требуется строгий синтаксис.
Процедура
Процедуры в VBA Excel делятся на 3 типа:
- Sub (подпрограмма),
- Функция (функция),
- Свойство (настраиваемые свойства).
Основное различие между функцией и подпрограммой заключается в том, что функция возвращает результат вычисления, а подпрограмма — нет. Процедура свойств предназначена для создания и управления пользовательскими свойствами (используется нечасто).
В редакторе VBA Excel перейдите к стандартному модулю и нажмите на пункт меню «Вставить». В открывшемся списке выберите «Процедура…». Появится окно с просьбой ввести название процедуры, выбрать тип и диапазон видимости. Создайте пару процедур с разными областями действия, пусть одна будет Sub, а другая Function. В промежутке между началом и концом процедуры записывается программный код.
MsgBox представляет собой окно для отображения информации и возвращает числовое значение кнопки, нажатой пользователем.
InputBox — это окно, которое предлагает пользователю ввести текстовую информацию. Так же есть расширенная версия этого окна — которую можно использовать для записи адреса выбранного диапазона ячеек и не только для этого.
В Excel есть множество формул и операторов, облегчающих вычисления. Одной из самых востребованных функций является вычисление модуля числа в Excel.
Что такое модуль в Excel – как и какими функциями его можно рассчитать
В Excel есть множество формул и операторов, облегчающих расчеты. Одной из востребованных функций является вычисление модуля числа в Экселе.
Причины применения функции
В математике модуль числа показывает его абсолютное значение, знак величины не учитывается. С помощью формулы определение записывается так: I-aI = IaI = a. Все значения по модулю являются положительными.
Программа Excel оперирует числами, под которыми часто «скрываются» не абстрактные математические действия, а реальные предметы или бухгалтерские расчеты. Поэтому полученные результаты не могут быть отрицательными.
Например, какие-то товары были проданы сверх плана, другие, наоборот, остались на складе. Тогда реализованные изделия будут иметь положительное значения, а не проданные — отрицательные. Если понадобится сложить все отклонения от плана, то результатом может стать отрицательное значение или ноль. Применение модуля покажет абсолютную сумму отклонений.
Оператор также находит применение при подсчете векторных величин, которые могут иметь отрицательное значение, сравнении двух элементов таблицы, расчете процентов по кредиту.
Использование ABS
Поставить модуль числа в Excel можно функцией ABS, аббревиатура образовалась от английского слова Absolute, что означает «абсолютный».
Стандартный вариант
Правильное написание выглядит так: «ABS(X)» или «ABS(адрес_ячейки_с_числом)». Здесь «X» — число, модуль которого нужно найти, «адрес ячейки с числом» — элемент таблицы, в котором будет указано абсолютное значение. Вставить формулу можно двумя способами. Простой вариант:
- Вписать в ячейку или в строку формул значение, для которого нужно найти модуль. Пусть это будет -8. Тогда надпись будет такова: «=ABS(-8)».
- Нажать ввод и получить результат.
- Выделить необходимую ячейку и кликнуть по опции «Вставить формулу», обозначенной символом fx.
- В появившемся списке найти надпись «ABS», выделить ее и подтвердить.
- Откроется меню «Аргументы функции». Здесь нужно ввести необходимую цифру в функцию модуля. Можно указать адрес ячейки, в которой находится необходимое значение. Для этого нажать на иконку, находящуюся справа от строки «Число».
- Окно аргументов свернется, в рабочей таблице нужно будет кликнуть по нужному элементу и снова нажать на иконку.
- Опять появится меню с аргументами, где в строке значений будет стоять адрес выбранной ячейки. Подтвердить операцию.
- Функция сработает в указанном месте.
- Можно распространить действие формулы на другие ячейки. Для этого в ячейке с примененной функцией ABS нажать на правый нижний угол мышкой и не отпуская кнопку «потянуть» мышку вниз, выделяя нужные элементы. К ним будет применена функция.
ABS в формулах
Наряду с простым приведением к модулю, функция может использоваться в связке с формулами и решать комплексные задачи. Например, определить на сколько минимальное отрицательное число меньше минимального положительного можно так:
- Имеется массив, в котором нужно решить поставленную задачу.
- В строку формул нужно вписать следующую композицию: .Здесь фигурные скобки показывают, что расчеты ведутся с массивом данных. Вводятся такие скобки не с клавиатуры, а нажатием клавиш Ctrl+Shift+Enter в конце написания формулы.
Программа в этом случае рассчитывает модуль в каждой из ячеек между B3 и B12. Без применения массивов ABS выдала бы сообщение об ошибке при такой записи. - Результат применения:
Примеры расчетов
Ниже приведены возможные случаи использования функции модуля ABS, как отдельно, так и в составе формулы.
Задача по математике на определение проекции отрезка на ось абсцисс (X) и ось ординат(Y). По условиям задания отрезок имеет координаты начала A(-17; -14) и конца B(-39;-56). Решение в Excel:
- Ввести в таблицу известные данные.
- Рассчитывается проекция на ось X с помощью ABS.
- Аналогично находится проекция на ось ординат. В строке «Число» нужно указать B5-B3.
- Результат появится в таблице.
Подсчет расходов компании — здесь нужно сложить отрицательные значения по модулю.
Имеется набор финансовых операции компании за отчетный период. Нужно определить сумму трат предприятия. По условию расходы указываются со знаком минус:
- Составить массив в Excel.
- Написать формулу: <=СУММ(ЕСЛИ(В3:В12<0;ABS(В3:В12);0))>
В конце нажать Ctrl+Shift+Enter для указания того, что используются массивы данных. - Программа анализирует данные ячеек B3-B12 и если имеются отрицательные числа, то берется их модуль. После проверки всех элементов, отрицательные значения складываются. Положительные данные не учитываются в расчетах. Итоговый результат имеет следующий вид:
Другие варианты получения абсолютного значения
Кроме ABS возможны и другие способы получения модуля числа в Эксель при помощи альтернативных функций.
Принцип основан на том, что отрицательные числа умножаются на -1, а положительные на 1. Выглядит это так:
На картинке действие производится над ячейкой A4.
КОРЕНЬ
Квадратный корень величины не может быть отрицательным. Поэтому исходное число возводится в квадрат, а потом извлекается корень.
Удобный способ. Если значение отрицательное, то оно умножается на -1, в противном случае ничего не происходит. Вот как это выглядит для ячейки A1:
Язык VBA
Во многих языках программирования модуль находится через ABS. В VBA команда будет выглядеть следующим образом: A=Abs(-7). Здесь Abs — команда для получения абсолютного значения. В данном случае объекту A будет присвоено число 7.
ABS и альтернативные варианты просты в исполнении. Умение применять их, облегчит работу и сэкономит время.