Evolcom.ru

Бытовая техника
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Как подсчитать / суммировать только положительные или отрицательные числа в Excel?

Формула суммы по условию критерия выборки значений в Excel

Логические функции, такие как ЕСЛИ, обычно используют один столбец или одну ячейку. Помимо агрегирования данных, Excel предоставляет несколько других логических функций. Например, функция СУММЕСЛИ для выборочного суммирования диапазона значений по условию.

Примеры формулы для суммы диапазонов с условием отбора в Excel

В таблице ниже представлен список счетов с балансом каждого счета в виде положительных или отрицательных чисел в таблице. Предположим, нам нужно вычислить сумму всех отрицательных чисел, чтобы рассчитать общий денежный поток. Позже этот результат будет сравнен с суммой положительных чисел для проверки и выведения баланса. Мы увидим, совпадают ли суммы доходов и расходов — совпадают ли суммы дебета и кредита. Excel использует булеву функцию =SUMMESLY() для суммирования числовых значений в соответствии с условием:

сумма диапазонов c условием.

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

В приведенном выше примере сначала проверяется значение в ячейке B2, и поскольку оно больше 0, оно игнорируется. Следующая ячейка, B3, проверяется. Он имеет числовое значение меньше нуля, поэтому условие выполняется, и он добавляется к общему итогу. Этот процесс повторяется для каждой ячейки. В результате значения в ячейках B3, B6, B7, B8 и B10 суммируются, а остальные ячейки не учитываются.

Формула для суммирования положительных чисел отображается ниже результата сложения отрицательных чисел. Отличие заключается в обратном операторе сравнения, указанном во втором аргументе суммирования — вместо «*0» (меньше нуля) используется «>0» (больше нуля). Теперь мы можем убедиться, что дебет и кредит сходятся, баланс будет равен нулю, если мы выполним арифметическое сложение в ячейке B16 по формуле =B15+B14.

Пример логического выражения в формуле для суммы с условием

Другой пример, когда мы хотим суммировать цены отдельно для групп товаров ниже 1000 и отдельно для групп товаров выше 1000. В этом случае одного оператора сравнения (<1000) недостаточно, необходимо использовать знак "меньше или равно". (<=1000), иначе цены ровно 1000 не будут включены в расчет. Более того, в данном случае мы не можем использовать "больше или равно" (>=1000) в другой формуле, иначе мы сложим ровно 1000 — 2 раза, что приведет к неправильным результатам:

Читайте так же:
Как перечислить все имена файлов в папке и создать для них гиперссылки в Excel?

отдельно суммировать цены.

Это очень распространенная ошибка пользователей Excel при работе с булевыми функциями.

Внимание! В первом примере не было необходимости учитывать нулевые значения, так как баланс не пострадал бы, но во втором случае критерий суммирования должен быть написан по-другому, чтобы избежать ошибок в расчетах.

Второй аргумент функции AMMESLY, требование, заключен в двойные кавычки. В данном примере используется символ сравнения «меньше» (<). Чтобы представить такие записи с помощью синтаксиса функции, их необходимо представить в виде строки. в двойных кавычках. В противном случае Excel выдаст предупреждение об ошибке в формуле.

Синтаксис формулы суммирования по условию в Excel

Синтаксис функции SUMMESLY содержит третий необязательный аргумент: диапазон суммы. В предыдущем примере условие проверялось по одному и тому же диапазону, подлежащему суммированию. Третий аргумент позволяет проверить условия одного диапазона и добавить другой.

На рисунке ниже показана таблица с отчетом о продажах по клиентам за один месяц. На другой стороне каждого покупателя вы можете увидеть, сколько он купил в течение месяца. Некоторые клиенты совершили несколько покупок в течение месяца, поэтому их имена указаны дважды. Вам нужно выяснить, сколько «Клиент3» купил в данном месяце. Именно здесь вступает в игру формула для заполнения третьего аргумента функции СУММЕСЛИ:

Синтаксис суммирования по условию.

Как вы можете видеть во всех приведенных примерах, второй аргумент — это текстовая строка с условием, а не логическое выражение, как в функции IF. На практике при использовании функции AMMESLI этот второй логический аргумент с условием может содержать :

  • Выражения с текстовыми значениями;
  • Числовые значения;
  • Булевы выражения;
  • Формулы с функциями;
  • Клеточные резюме.

Далее рассмотрим примеры задания условий для второго логического аргумента в функции SUMMESLI.

Читайте так же:
Как показать комментарии при выделенной ячейке в Excel?

Создание критериев условий для функции СУММЕСЛИ

Второй аргумент функции называется «Критерий». Этот логический аргумент также используется в других подобных логических функциях: СУММЕСЛИМН, СКРАТЕСЛИМН, СКРАТЕСЛИМН и СКРАТЕСЛИМН. В каждом случае аргумент завершается в соответствии с теми же правилами составления логических условий. Другими словами, для всех этих функций вторым аргументом условного критерия является логическое выражение, возвращающее TRUE или FALSE. Это означает, что выражение должно содержать оператор сравнения, например: плюс (>) минус (<) равно (=) неравно (<>), плюс или равно (>=), минус или равно (<=). За исключением оператора равенства (=), который может быть опущен, если необходимо проверить точное совпадение.

Создание сложных критериев условий может запутать. Однако если вы будете следовать простым правилам, описанным в таблице ниже, проблем не возникнет.

Таблица правил разработки критериев состояния :

Чтобы создать условиеПримените правилоПример
Значение равно заданному числу или ячейке с данным адресом.Не используйте знак равенства и двойных кавычек.=СУММЕСЛИ(B1:B10;3)
Значение равно текстовой строке.Не используйте знак равенства, но используйте двойные кавычки по краям.=СУММЕСЛИ(B1:B10;»Клиент5″)
Значение отличается от заданного числа.Поместите оператор и число в двойные кавычки.=СУММЕСЛИ(B1:B10;»>=50″)
Значение отличается от текстовой строки.Поместите оператор и число в двойные кавычки.=СУММЕСЛИ(B1:B10;»<>выплата»)
Значение отличается от ячейки по указанному адресу или от результата вычисления формулы.Поместите оператор сравнения в двойные кавычки и соедините его символом амперсант (&) вместе со ссылкой на ячейку или с формулой.=СУММЕСЛИ(A1:A10;»<«&C1) или =СУММЕСЛИ(B1:B10;»<>»&СЕГОДНЯ())
Значение содержит фрагмент строкиИспользуйте операторы многозначных символов и поместите их в двойные кавычки=СУММЕСЛИ(A1:A10;»*кг*»;B1:B10)

Во втором аргументе можно использовать различные формулы и функции. Ниже приведен список дат и значений, присвоенных каждой из них. Важно отметить, что сегодняшняя дата на момент написания статьи — «03.11.2018». Вычислите сумму числовых значений, приходящихся как раз на сегодняшнюю дату, с помощью следующей формулы:

Суммирование только значений в диапазоне между сегодняшним днем и концом периода требует оператора «больше или равно» (>=) с соответствующей функцией =TODAY(). Операторы формулы c (>=):

Читайте так же:
Как преобразовать дату в другой или определенный формат в Excel?

Создание критериев условий.» src=»https://exceltable.com/formuly/images/formuly109-4.png» >

Суммирование по неточному совпадению в условии критерия отбора

Многосимвольные условия можно использовать во втором логическом аргументе функции СУММЕСЛИ (? )и(*) для составления относительных неточных запросов. Вопросительный знак (?) Звездочка (*) — интерпретируется как любой символ, а — следует читать как любую последовательность любых символов. Только защитные краски-лаки, начинающиеся с английских букв кода 3, следует суммировать следующим образом:

Суммирование по неточному совпадению.

В диапазоне B2:B16 все значения ячеек суммируются в соответствии со значением в A2:A16, где появляется третий символ фрагмента строки «-защищенный».

Поэтому в складском отчете можно суммировать только те товары, которые попали в определенную группу. Фрагмент названия товара должен появиться в определенной позиции — 3 символа от начала строки. Используя функцию =LEVSIMB() и т.д., нет необходимости использовать сложные формулы. Достаточно использовать многозначные символы в качестве операторов в запросе, чтобы сформировать лаконичный и простой запрос с минимальной нагрузкой на системы.

Что такое модуль в Excel – как и какими функциями его можно рассчитать

В Excel есть множество формул и операторов, которые помогают в вычислениях. Excel является полезным инструментом для вычисления модуля числа.

Причины применения функции

В математике модуль числа показывает его абсолютное значение; знак значения не учитывается. В результате использования приведенной выше формулы I-aI = IaI = a. Все значения по модулю положительны.

Excel работает с числами, за которыми часто «скрываются» не абстрактные математические действия, а реальные объекты или бухгалтерские расчеты. Поэтому полученные результаты не могут быть отрицательными.

Использование функции

В одних случаях некоторые товары были проданы сверх плана, а в других — остались на складе. В этом случае проданным товарам будет присвоено положительное значение, а непроданным — отрицательное. При суммировании всех отклонений от плана может получиться отрицательное значение или ноль. Применение модуля покажет абсолютную сумму отклонений.

Использование этого оператора также предполагает вычисление векторных значений, которые могут иметь отрицательные значения, сравнение двух элементов таблицы, а также вычисление процентов.

Использование ABS

Вы можете задать модуль числа в Excel с помощью ABS, который является акронимом, образованным от английского слова Absolute, что означает «абсолютный».

Читайте так же:
Как перечислить все понедельники / пятницы месяца в Excel?

Стандартный вариант

Правильное написание — «ABS(X)» или «ABS(cell_address_with_number)». Здесь «X» — число, модуль которого нужно найти, а «адрес ячейки с номером» — элемент массива, в котором нужно задать абсолютное значение. Формулу можно ввести двумя способами. Простой вариант:

  1. Вписать в ячейку или в строку формул значение, для которого нужно найти модуль. Пусть это будет -8. Тогда надпись будет такова: «=ABS(-8)».Установка модуля
  2. Нажать ввод и получить результат.Результат работы
  1. Выделить необходимую ячейку и кликнуть по опции «Вставить формулу», обозначенной символом fx.Вставка формулы
  2. В появившемся списке найти надпись «ABS», выделить ее и подтвердить.Подтверждение функции
  3. Откроется меню «Аргументы функции». Здесь нужно ввести необходимую цифру в функцию модуля. Можно указать адрес ячейки, в которой находится необходимое значение. Для этого нажать на иконку, находящуюся справа от строки «Число».Аргументы функции
  4. Окно аргументов свернется, в рабочей таблице нужно будет кликнуть по нужному элементу и снова нажать на иконку.Аргументы функции
  5. Опять появится меню с аргументами, где в строке значений будет стоять адрес выбранной ячейки. Подтвердить операцию.Адрес выбранной ячейки
  6. Функция сработает в указанном месте.Результат функции
  7. Можно распространить действие формулы на другие ячейки. Для этого в ячейке с примененной функцией ABS нажать на правый нижний угол мышкой и не отпуская кнопку «потянуть» мышку вниз, выделяя нужные элементы. К ним будет применена функция.Модуль изменения

ABS в формулах

Помимо простого модулора, функция может использоваться с формулами для решения сложных задач. Например, можно определить, на сколько наименьшее отрицательное число меньше наименьшего положительного:

  1. Имеется массив, в котором нужно решить поставленную задачу.Пример массива
  2. В строку формул нужно вписать следующую композицию: .Здесь фигурные скобки показывают, что расчеты ведутся с массивом данных. Вводятся такие скобки не с клавиатуры, а нажатием клавиш Ctrl+Shift+Enter в конце написания формулы.
    Программа в этом случае рассчитывает модуль в каждой из ячеек между B3 и B12. Без применения массивов ABS выдала бы сообщение об ошибке при такой записи.
  3. Результат применения:Результат применения

Примеры расчетов

Ниже приведены возможные варианты использования функции модуля ABS, как самостоятельно, так и в составе формулы.

Математическая задача на нахождение проекции отрезка на ось x и ось y. Отрезок имеет координаты начала A(-17; -14) и конца B(-39;-56). Решение в Excel :

  1. Ввести в таблицу известные данные.Ввод в таблицу
  2. Рассчитывается проекция на ось X с помощью ABS.Ввод аргументов
  3. Аналогично находится проекция на ось ординат. В строке «Число» нужно указать B5-B3.
  4. Результат появится в таблице.Результат в таблице

Подсчет расходов компании — здесь нужно сложить отрицательные значения по модулю.

Существует набор финансовых операций компании за отчетный период. Оценка расходов компании имеет решающее значение. Для обозначения расходов используется знак минус:

  1. Составить массив в Excel.Готовый массив
  2. Написать формулу: <=СУММ(ЕСЛИ(В3:В12<0;ABS(В3:В12);0))>
    В конце нажать Ctrl+Shift+Enter для указания того, что используются массивы данных.
  3. Программа анализирует данные ячеек B3-B12 и если имеются отрицательные числа, то берется их модуль. После проверки всех элементов, отрицательные значения складываются. Положительные данные не учитываются в расчетах. Итоговый результат имеет следующий вид:Анализ данных

Другие варианты получения абсолютного значения

Excel предлагает множество способов нахождения модуля числа, кроме использования ABS.

Функция ЗНАК

Этот принцип основан на том, что отрицательные числа умножаются на -1, а положительные числа умножаются на 1:

Клетка A4 является целью действия на рисунке.

КОРЕНЬ

Функция КОРЕНЬ

Квадратный корень из значения не может быть отрицательным. Поэтому исходное число возводится в квадрат, а затем вычитается корень.

Функция ЕСЛИ

Это практичный способ действий. Если значение отрицательное, оно умножается на -1, в противном случае ничего не происходит. Вот как выглядит ячейка A1:

Язык VBA

Во многих языках программирования доступ к модулю часто осуществляется через ABS. В VBA команда будет выглядеть следующим образом: A=Abs(-7). Значение, которое является абсолютным, получается с помощью Abs. В данном случае A будет присвоено число 7.

Б С и альтернативы просты в использовании. Вы можете сэкономить время и усилия, научившись их использовать.

голоса
Рейтинг статьи
Ссылка на основную публикацию
Adblock
detector