Как сложить только отрицательные числа в excel
Заранее благодарю! Автор - ars1734
Дата добавления - 31.12.2017 в 02:15
Благодарю Вас за быстрые ответы!
Есть у меня к вам еще вопрос:
Есть два столбца с положительными и отрицательными числами. В третий столбец (голубой) закладывается формула вычитания из большего значения- меньшее. Выглядит она так: =МАКС(F5:G5)-МИН(F5:G5)
При этой формуле, программа вычитает из большего значения - меньшее и это правильный расчет, но проблема заключается в том, что ответ выдает всегда положительный, когда как в некоторых ячейках отрицательное значение больше чем положительное.
ВОПРОС: как сделать так, чтобы в случае большего значения с отрицательным знаком, ответ так же был со знаком минус
Файл с таблицей приложил, репутацию поднял
С Уважением и наступающим Новым годом!
Благодарю Вас за быстрые ответы!
Есть у меня к вам еще вопрос:
Есть два столбца с положительными и отрицательными числами. В третий столбец (голубой) закладывается формула вычитания из большего значения- меньшее. Выглядит она так: =МАКС(F5:G5)-МИН(F5:G5)
При этой формуле, программа вычитает из большего значения - меньшее и это правильный расчет, но проблема заключается в том, что ответ выдает всегда положительный, когда как в некоторых ячейках отрицательное значение больше чем положительное.
ВОПРОС: как сделать так, чтобы в случае большего значения с отрицательным знаком, ответ так же был со знаком минус
Файл с таблицей приложил, репутацию поднял
С Уважением и наступающим Новым годом! ars1734
ars1734, Другой вопрос - другая тема, и не забудьте в вашем пример показать что хотите получить, вписав руками результат. Ну и конечно подусайте на формулировкой, ну не может отрицательное быть больше положительного. По модулю -может, просто - нет.
ars1734, Другой вопрос - другая тема, и не забудьте в вашем пример показать что хотите получить, вписав руками результат. Ну и конечно подусайте на формулировкой, ну не может отрицательное быть больше положительного. По модулю -может, просто - нет. bmv98rus
Замечательный Временно просто медведь , процентов на 20.
Как суммировать / усреднять игнорировать отрицательные значения в Excel?
Обычно мы можем применять функции СУММ и СРЕДНЕЕ для вычисления результата диапазона ячеек, включая положительные и отрицательные, но как мы можем суммировать или усреднять числа, игнорируя отрицательные значения в Excel? Здесь я расскажу о некоторых быстрых приемах решения этой проблемы.
Сумма / Среднее игнорировать отрицательные значения с формулами
Чтобы суммировать или усреднить все значения, исключая отрицательные числа, примените следующие формулы:
Сумма игнорировать отрицательные значения:
1. Введите эту формулу в пустую ячейку, в которую вы хотите поместить результат, = СУММЕСЛИ (A1: D9; "> = 0") , см. снимок экрана:
2, Затем нажмите Enter ключ для получения результата, см. снимок экрана:
Среднее без учета отрицательных значений:
Чтобы усреднение игнорировать отрицательные значения, используйте эту формулу: =SUMIF(A1:D9,">=0")/COUNTIF(A1:D9,">=0") .
Внимание: В приведенных выше формулах A1: D9 - это диапазон данных, который вы хотите использовать, вы можете изменить ссылку на ячейку по своему усмотрению.
Сумма / среднее игнорировать отрицательные значения с помощью Kutools for Excel
Kutools for ExcelАвтора Выбрать определенные ячейки Функция может помочь вам выбрать все неотрицательные числа сразу и получить вычисления, такие как сумма, среднее значение, количество в строке состояния.
После установки Kutools for Excel, пожалуйста, сделайте следующее:
1. Выберите ячейки, которые хотите использовать.
2. Нажмите Кутулс > Выберите > Выбрать определенные ячейки, см. снимок экрана:
3. В Выбрать определенные ячейки диалоговом окне выполните следующие операции:
(1.) Выберите Ячейка из Тип выбора;
(2.) Выберите Больше или равно из Конкретный тип, и введите 0 в текстовом поле.
4. Затем нажмите Ok or Применить все числа, исключая отрицательные, были выбраны, и вы можете просмотреть результат вычисления среднего, подсчета и суммы в нижней части строки состояния, см. снимок экрана:
Демо: сумма / среднее игнорировать отрицательные значения с помощью Kutools for Excel
Kutools for Excel: с более чем 300 удобными надстройками Excel, которые можно попробовать бесплатно без ограничений в течение 30 дней. Загрузите и бесплатную пробную версию прямо сейчас!
Как в Excel разрешить ввод только отрицательных чисел? Эта статья познакомит вас с некоторыми полезными и быстрыми приемами для решения этой задачи.
Разрешить только отрицательные числа в Excel с проверкой данных
Как правило, проверка достоверности данных функция может помочь вам, пожалуйста, сделайте следующее:
1. Выделите ячейки или столбцы, в которых нужно разрешить ввод только отрицательных чисел, а затем щелкните Данные > проверка достоверности данных > проверка достоверности данных, см. снимок экрана:
2. В проверка достоверности данных диалоговое окно под Настройки Вкладка, выполните следующие действия:
(1.) В Разрешить раздел, выбрать Десятичная дробь из выпадающего списка;
(2.) В Данные раздел, пожалуйста, выберите меньше или равно вариант;
(3.) Наконец, введите номер 0 в максимальная текстовое окно.
Разрешить только отрицательные числа в Excel с кодом VBA
Вот код VBA, который также может вам помочь, со следующим кодом, когда вы вводите положительное число, оно будет автоматически преобразовано в отрицательное, сделайте следующее:
1. Щелкните правой кнопкой мыши вкладку листа, на которой вы хотите разрешить только отрицательные числа, и выберите Просмотреть код из контекстного меню во всплывающем Microsoft Visual Basic для приложений окна, скопируйте и вставьте следующий код в пустое Модули:
Код VBA: разрешены только отрицательные числа на листе:
Внимание: В приведенном выше коде A1: A1000 - это ячейки, в которые вы хотите вводить только отрицательные числа.
2. Затем сохраните и закройте этот код, вернитесь на рабочий лист, и теперь, когда вы вводите некоторые положительные числа в ячейки, указанные в коде, положительные числа будут автоматически преобразованы в отрицательные.
Предположим, что у нас имеется вот такая таблица с данными по выручке и выполнению плана для нескольких городов:
Необходимо просуммировать выручку по всем городам или, что примерно то же самое, подсчитать средний процент выполнения плана по всем городам.
Если решать проблему "в лоб", то придется вводить длинную формулу с перебором всех ячеек, т.к. сразу весь диапазон одним движением выделить не получится:
Если количество городов в таблице больше пары десятков, то такой способ начинает нагонять тоску, да и ошибиться при вводе формулы можно запросто. Как же поступить? Есть способы сделать это изящнее.
Способ 1. Функция СУММЕСЛИ (SUMIF) и ее аналоги для выборочного суммирования по условию
Если в таблице есть столбец с признаком, по которому можно произвести выборочное суммирование (а у нас это столбец В со словами "Выручка" и "План"), то можно использовать функцию СУММЕСЛИ (SUMIF) :
Первый аргумент этой функции - диапазон проверяемых ячеек, второй - критерий отбора (слово "Выручка"), третий - диапазон ячеек с числами для суммирования.
Начиная с версии Excel 2007 в базовом наборе появилась еще и функция СРЗНАЧЕСЛИ (AVERAGEIF) , которая подсчитывает не сумму, а среднее арифметическое по условию. Ее можно использовать, например, для вычисления среднего процента выполнения плана. Подробно про все функции выборочного суммирования можно почитать в этой статье с видеоуроком. Минус этого способа в том, что в таблице должен быть отдельный столбец с признаком, а это бывает не всегда.
Способ 2. Формула массива для суммирования каждой 2-й, 3-й . N-й строки
Если удобного отдельного столбца с признаком для выборочного суммирования нет или значения в нем непостоянные (где-то "Выручка", а где-то "Revenue" и т.д.), то можно написать формулу, которая будет проверять номер строки для каждой ячейки и суммировать только те из них, где номер четный, т.е. кратен двум:
Давайте подробно разберем формулу в ячейке G2. "Читать" эту формулу лучше из середины наружу:
- Функция СТРОКА (ROW) выдает номер строки для каждой по очереди ячейки из диапазона B2:B15.
- Функция ОСТАТ (MOD) вычисляет остаток от деления каждого полученного номера строки на 2.
- Функция ЕСЛИ (IF) проверяет остаток, и если он равен нулю (т.е. номер строки четный, кратен 2), то выводит содержимое очередной ячейки или, в противном случае, не выводит ничего.
- И, наконец, функция СУММ (SUM) суммирует весь набор значений, которые выдает ЕСЛИ, т.е. суммирует каждое 2-е число в диапазоне.
- Данная формула должна быть введена как формула массива, т.е. после ее набора нужно нажать не Enter, а сочетание Ctrl+Alt+Enter. Фигурные скобки набирать с клавиатуры не нужно, они добавятся к формуле автоматически.
Для ввода, отладки и общего понимания работы подобных формул можно использовать следующий трюк: если выделить фрагмент сложной формулы и нажать клавишу F9, то Excel прямо в строке формул вычислит выделенное и отобразит результат. Например, если выделить функцию СТРОКА(B2:B15) и нажать F9, то мы увидим массив номеров строк для каждой ячейки нашего диапазона:
А если выделить фрагмент ОСТАТ(СТРОКА(B2:B15);2) и нажать на F9, то мы увидим массив результатов работы функции ОСТАТ, т.е. остатки от деления номеров строк на 2:
И, наконец, если выделить фрагмент ЕСЛИ(ОСТАТ(СТРОКА(B2:B15);2)=0;B2:B15) и нажать на F9, то мы увидим что же на самом деле суммирует функция СУММ в нашей формуле:
Значение ЛОЖЬ (FALSE) в данном случае интерпретируются Excel как ноль, так что мы и получаем, в итоге, сумму каждого второго числа в нашем столбце.
Легко сообразить, что вместо функции суммирования в эту конструкцию можно подставить любые другие, например функции МАКС (MAX) или МИН (MIN) для вычисления максимального или минимального значений и т.д.
Если над таблицей могут в будущем появляться новые строки (шапка, красивый заголовок и т.д.), то лучше слегка модернизировать формулу для большей универсальности:
Способ 3. Функция БДСУММ и таблица с условием
Формулы массива из предыдущего способа - штука красивая, но имеют слабое место - быстродействие. Если в вашей таблице несколько тысяч строк, то подобная формула способна заставить ваш Excel "задуматься" на несколько секунд даже на мощном ПК. В этом случае можно воспользоваться еще одной альтернативой - функцией БДСУММ (DSUM) . Перед использованием эта функция требует небольшой доработки, а именно - создания в любом подходящем свободном месте на нашем листе миниатюрной таблицы с условием отбора. Заголовок этой таблицы может быть любым (слово "Условие" в E1), лишь бы он не совпадал с заголовками из таблицы с данными. После ввода условия в ячейку E2 появится слово ИСТИНА (TRUE) или ЛОЖЬ (FALSE) - не обращайте внимания, нам нужна будет сама формула из этой ячейки, выражающая условие, а не ее результат. После создания таблицы с условием можно использовать функцию БДСУММ (DSUM) :
Способ 4. Суммирование каждой 2-й, 3-й. N-й строки
Если нужно просуммировать не отдельные ячейки, а целые строки, то можно это легко реализовать формулой массива, аналогичной способу 2:
Поскольку функция СУММПРОИЗВ (SUMPRODUCT) автоматически преобразует свои аргументы в массивы, то в этом случае нет необходимости даже нажимать Ctrl+Shift+Enter.
Имеем таблицу по продажам, например, следующего вида:
Задача: просуммировать все заказы, которые менеджер Григорьев реализовал для магазина "Копейка".
Способ 1. Функция СУММЕСЛИ, когда одно условие
Если бы в нашей задаче было только одно условие (все заказы Петрова или все заказы в "Копейку", например), то задача решалась бы достаточно легко при помощи встроенной функции Excel СУММЕСЛИ (SUMIF) из категории Математические (Math&Trig) . Выделяем пустую ячейку для результата, жмем кнопку fx в строке формул, находим функцию СУММЕСЛИ в списке:
Жмем ОК и вводим ее аргументы:
- Диапазон - это те ячейки, которые мы проверяем на выполнение Критерия. В нашем случае - это диапазон с фамилиями менеджеров продаж.
- Критерий - это то, что мы ищем в предыдущем указанном диапазоне. Разрешается использовать символы * (звездочка) и ? (вопросительный знак) как маски или символы подстановки. Звездочка подменяет собой любое количество любых символов, вопросительный знак - один любой символ. Так, например, чтобы найти все продажи у менеджеров с фамилией из пяти букв, можно использовать критерий . . А чтобы найти все продажи менеджеров, у которых фамилия начинается на букву "П", а заканчивается на "В" - критерий П*В. Строчные и прописные буквы не различаются.
- Диапазон_суммирования - это те ячейки, значения которых мы хотим сложить, т.е. нашем случае - стоимости заказов.
Способ 2. Функция СУММЕСЛИМН, когда условий много
Если условий больше одного (например, нужно найти сумму всех заказов Григорьева для "Копейки"), то функция СУММЕСЛИ (SUMIF) не поможет, т.к. не умеет проверять больше одного критерия. Поэтому начиная с версии Excel 2007 в набор функций была добавлена функция СУММЕСЛИМН (SUMIFS) - в ней количество условий проверки увеличено аж до 127! Функция находится в той же категории Математические и работает похожим образом, но имеет больше аргументов:
При помощи полосы прокрутки в правой части окна можно задать и третью пару (Диапазон_условия3-Условие3), и четвертую, и т.д. - при необходимости.
Если же у вас пока еще старая версия Excel 2003, но задачу с несколькими условиями решить нужно, то придется извращаться - см. следующие способы.
Способ 3. Столбец-индикатор
Добавим к нашей таблице еще один столбец, который будет служить своеобразным индикатором: если заказ был в "Копейку" и от Григорьева, то в ячейке этого столбца будет значение 1, иначе - 0. Формула, которую надо ввести в этот столбец очень простая:
=(A2="Копейка")*(B2="Григорьев")
Логические равенства в скобках дают значения ИСТИНА или ЛОЖЬ, что для Excel равносильно 1 и 0. Таким образом, поскольку мы перемножаем эти выражения, единица в конечном счете получится только если оба условия выполняются. Теперь стоимости продаж осталось умножить на значения получившегося столбца и просуммировать отобранное в зеленой ячейке:
Способ 4. Волшебная формула массива
Если вы раньше не сталкивались с такой замечательной возможностью Excel как формулы массива, то советую почитать предварительно про них много хорошего здесь. Ну, а в нашем случае задача решается одной формулой:
=СУММ((A2:A26="Копейка")*(B2:B26="Григорьев")*D2:D26)
После ввода этой формулы необходимо нажать не Enter , как обычно, а Ctrl + Shift + Enter - тогда Excel воспримет ее как формулу массива и сам добавит фигурные скобки. Вводить скобки с клавиатуры не надо. Легко сообразить, что этот способ (как и предыдущий) легко масштабируется на три, четыре и т.д. условий без каких-либо ограничений.
Способ 4. Функция баз данных БДСУММ
В категории Базы данных (Database) можно найти функцию БДСУММ (DSUM) , которая тоже способна решить нашу задачу. Нюанс состоит в том, что для работы этой функции необходимо создать на листе специальный диапазон критериев - ячейки, содержащие условия отбора - и указать затем этот диапазон функции как аргумент:
Читайте также: