No Image

Сумма отрицательных чисел в excel

СОДЕРЖАНИЕ
4 036 просмотров
10 марта 2020

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

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

Ниже на рисунке представлен в таблице список счетов вместе с состоянием по каждому счету в виде положительных или отрицательных чисел. Допустим нам необходимо посчитать сумму всех отрицательных чисел для расчета суммарного расхода по движению финансовых средств. Этот результат будет позже сравниваться вместе с сумой положительных чисел с целью верификации и вывода балансового сальдо. Узнаем одинаковые ли суммы доходов и расходов – сойдется ли у нас дебит с кредитом. Для суммирования числовых значений по условию в Excel применяется логическая функция =СУММЕСЛИ():

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

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

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

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

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

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

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

Читайте также:  В яндекс браузере пропала панель задач

Второй аргумент функции СУММЕСЛИ, то есть условие, которое должно быть выполнено, записывается между двойными кавычками. В данном примере используется символ сравнения – «меньше» ( ) меньше ( ), больше или равно (>=), меньше или равно ( Таблица правил составления критериев условий:

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

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

Чтобы суммировать только значения от сегодняшнего дня включительно и до конца периода времени воспользуйтесь оператором «больше или равно» (>=) вместе с соответственной функцией =СЕГОДНЯ(). Формула c операторам (>=):

="&СЕГОДНЯ();B2:B10)/B11′ >

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

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

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

Таким образом удалось суммировать только определенную группу товаров в общем списке отчета по складу. Данный фрагмент наименования товара должен встречаться в определенном месте – 3 символа от начала строки. Нет необходимости использовать сложные формулы с функцией =ЛЕВСИМВ() и т.д. Достаточно лишь воспользоваться операторами многозначных символов чтобы сформулировать простой и лаконичный запрос к базе данных с минимальными нагрузками на системные ресурсы.

Читайте также:  Сяоми с камерой 20 мегапикселей

трюки • приёмы • решения

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

Формулы основаны на таблице, показанной на рис. 118.1 и настроенной на отслеживание счетов. Столбец F содержит формулу, которая отнимает дату из столбца Е от даты в столбце D. Отрицательное число в столбце F означает, что платеж является просроченным. Лист использует именованные диапазоны, соответствующие меткам в строке 1.

Рис. 118.1. Отрицательное значение в столбце F указывает на просроченную оплату Суммирование только отрицательных значений

Следующая формула возвращает сумму отрицательных значений в столбце F. Другими словами, она возвращает общее количество просроченных дней для всех счетов (для таблицы на рис. 118.1 формула вернет -63):
=СУММЕСЛИ(Разница;" .

Функция СУММЕСЛИ может использовать три аргумента. Поскольку вы опускаете третий аргумент, второй аргумент ( " ) относится к значениям диапазона Разница.

Суммирование значений на основе разных диапазонов

Следующая формула возвращает сумму объемов просроченных счетов (в столбце С): =СУММЕСЛИ(Разница;" . Эта формула использует значения диапазона Разница, чтобы определить, вносят ли вклад в сумму соответствующие значения диапазона Сумма.

Суммирование значений на основе сравнения текста

Следующая формула возвращает общую сумму счетов для офиса Орегон: =СУММЕСЛИ(Офис;"=Орегон";Сумма) .

Использовать знак равенства необязательно. Следующая формула дает такой же результат:
=СУММЕСЛИ(Офис;"Орегон";Сумма) .

Чтобы суммировать счета для всех офисов, кроме Орегона, задайте такую формулу:
=СУММЕСЛИ(Офис;"<>Орегон";Сумма) .

Суммирование значений на основе сравнения дат

Следующая формула возвращает общую сумму счетов, срок оплаты которых — 10 мая 2010 года или позже:
=СУММЕСЛИ(Дата;">="&ДАТА(2010:5;10);Сумма) .

Обратите внимание, что второй аргумент для функции СУММЕСЛИ является выражением. Выражение использует функцию ДАТА, которая возвращает дату. Кроме того, оператор сравнения, заключенный в кавычки, объединяется (с помощью оператора &) с результатом функции ДАТА.

Следующая формула возвращает общую сумму счетов, срок оплаты которых еще не настал (включая сегодняшние счета):
=СУММЕСЛИ(Дата;">="&СЕГОДНЯ();Сумма) .
Острый Стоматит у взрослых — заболевание вирусной этиологии, возникает как у взрослых, так и у детей. В последнее время острый герпетический стоматит рассматривают как проявление первичной герпетической инфекции вирусом простого герпеса в полости рта. Инкубационный период заболевания продолжается 1-4 сут. Для продромального периода характерно увеличение поднижнечелюстных (в тяжелых случаях – шейных) лимфатических узлов. Температура тела повышается до 37-41°С, отмечаются слабость, бледность кожных покровов, головная боль и другие симптомы.

Читайте также:  Как построить восьмиугольник в окружности

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

Например, ваша организация предоставляет какие-либо услуги или продает какие-либо товары в больших количествах, на поставку товаров или услуг имеется рамочный договор, в котором прописаны договорные объемы, в случае недобора договорных объемов или перебора сверх договора в расчетный период предусмотрены оплаты неустоек. В таблице представлен перечень организаций и отклонение от договорных величин по каждой, со знаком «минус» представлены недоборы объемов, с плюсом переборы сверх лимита. Если Вы попробуете суммировать все значения отклонений, то получите в итоге ноль. Как посчитать сумму отклонений без учета знака перед числом?

Существует несколько способов расчета. Два из них приведем далее.

Первый способ: использование дополнительного столбца/строки.

Пример таблицы для расчета суммы отклонений от договорных величин:

Контрагент Отклонения от договорных величин Абсолютные отклонения от договорных величин
ИП МАКАРОВ 5 5
ИП САРОВъ 6 6
Старлайтмануалтехнолоджес 4 4
Moonlight Company -15 15
Державино LTD 3 3
Райпромторгсервис 3 3
Гордомселпо 2 2
ИП Стариков -6 6
ООО Желтый Снег -2 2
АО «Зима рядом» 4 4
Матвеевкастар LTD -4 4
Итого суммарно: 54

Необходимо добавить дополнительный столбец, в ячейках которого прописать функцию нахождения модуля числа – ABS (). Эта функция присваивает ячейке абсолютные значения чисел.

Далее произвести суммирование чисел в дополнительном столбце.

Последовательность действий для использования функции ABS ():

  • Установить курсор в ячейку;
  • Выбрать в мастере функций функцию ABS ();
  • Выбрать ячейку или число, из которого будет определен модуль;
  • Нажать «ENTER»

Второй способ: система из двух функций в одной ячейке.

Использование системы функций СУММ(ABS ()). Для такой системы нет необходимости вводить дополнительные столбцы. Достаточно в ячейку внести СУММ(ABS ()), выбрать диапазон суммирования и нажать сочетание клавиш ctrl+shift+enter. Если Вы нажмете только кнопку «Enter» функция обработает только первую ячейку в диапазоне (столбце/строке).

Похожее:

  1. Как сделать связанный выпадающий список в «Эксель», зависящий от значения в соседней ячейке.При создании какой-либо формы для заполнения самый.
  2. Определение наличия товара на складах магазинов при помощи таблицы ExcelДля примера представим, что у нас есть.
  3. Преобразование арабских чисел в РИМСКИЕ.Преобразование арабских чисел в РИМСКИЕ. Функция «РИМСКОЕ».

Как суммировать значения ячеек без учета знака — по модулю. Абсолютные величины — ABS.: 2 комментария

Надо наоборот: не СУММ(ABS ()), а ABS (СУММ())

Добрый день. Все зависит от того, какой результат Вам нужен.
Если сумма модулей, то СУММ(ABS (); ABS (); и т.д.). Если модуль суммы, то ABS (СУММ())

Комментировать
4 036 просмотров
Комментариев нет, будьте первым кто его оставит

Это интересно
Adblock
detector