Как найти отрицательные значения в эксель
Функция ABS Excel также известна как функция Absolute, которая используется для вычисления абсолютных значений заданного числа, отрицательные числа, заданные в качестве входных данных, изменяются на положительные числа, и если аргумент, предоставленный этой функции, является положительным, он остается неизменным.
ABS — это встроенная функция, относящаяся к функции Math / Trig, которая дает абсолютное значение числа. Он всегда возвращает положительное число.
Синтаксис
Аргументы, используемые в формуле ABS в excel
Как использовать функцию ABS в Excel? (с примерами)
Предположим, у вас есть список значений, указанный в B3: B10, и вам нужны абсолютные значения этих чисел.
Для первой ячейки вы можете ввести формулу ABS в Excel:
= ABS (B3)
и нажмите ввод. Он вернется к 5.
Теперь вы можете перетащить его для остальных ячеек и получить их абсолютные значения в Excel.
Все числа в C3: C10 абсолютны.
Предположим, у вас есть данные о доходах для семи отделов вашей компании, и вы хотите рассчитать разницу между прогнозируемым и фактическим доходом.
Для 1 ул во-первых, вы используете формулу ABS в Excel:
и нажмите ввод. Это даст 0,1667
Вы можете перетащить его в остальные ячейки, чтобы получить дисперсию для оставшихся шести отделов.
Предположим, у вас есть данные в B3: B8, и вы хотите проверить, какие из этих чисел положительные, а какие отрицательные. Для этого вы можете использовать функцию ABS, чтобы найти абсолютное значение в Excel.
Вы можете использовать формулу ABS в Excel:
= ЕСЛИ (ABS (B3) = B3, «Положительный», «Отрицательный»)
Если B3 — положительное число, тогда ABS (B3) и B3 будут одинаковыми.
Здесь B3 = -168. Таким образом, он вернет «Отрицательный» для B3. Аналогичным образом можно поступить и с остальными значениями.
Предположим, у вас есть список прогнозируемых и фактических данных эксперимента. Теперь вы хотите сравнить, какие из них находятся в пределах допуска 0,5. Данные представлены в C3: D10, как показано ниже.
Чтобы проверить, какие из них находятся в пределах допустимого диапазона, вы можете использовать Формулу ABS в Excel:
= ЕСЛИ (ABS (C4-D4) 1; ЕСЛИ (B3 1, то
Если B3 Ближайшее четное значение ЧЕТНОЕ (B3) + 2
Если B3 не отрицательно => Ближайшее четное значение ЧЕТНОЕ (B3) — 2
Если ABS (EVEN (B3) — B3) ≤ 1, то EVEN (B3) является ближайшим четным значением B3.
ABS ((- 4) — (-4,8)) дает 0,8
ABS (EVEN (B3) — B3)> 1 — FALSE, поэтому он вернет EVEN (B3).
Предположим, вы хотите определить ближайшее значение списка значений к заданному значению, вы можете сделать это с помощью функции ABS в Excel.
Список значений, в которых вы хотите выполнить поиск, представлен в B3: B9, а значение для поиска указано в ячейке F3.
Вы можете использовать следующую формулу ABS в Excel:
= ИНДЕКС (B3: B9; ПОИСКПОЗ (МИН (ABS (F3 — B3: B9)); ABS (F3 — B3: B9); 0))
и нажмите CTRL + SHIFT + ENTER (или COMMAND + SHIFT + ENTER для MAC)
Обратите внимание, что синтаксис представляет собой формулу массива, и простое нажатие ENTER приведет к ошибке.
Давайте подробно рассмотрим формулу ABS:
- (F3 — B3: B9) вернет массив значений
- ABS (F3 — B3: B9) даст абсолютные значения в Excel и вернет
- MIN (ABS (F3 — B3: B9)) вернет минимальное значение в массиве то есть 27.
- MATCH (27, ABS (F3 — B3: B9), 0)) будет искать позицию «27» в и возвращать 4.
- ИНДЕКС (B3: B9; 4) даст значение 4 th элемент в B3: B9.
Он вернет ближайшее значение из предоставленного списка значений B3: B9, т.е. 223
Вы можете заметить, что фигурные скобки были автоматически добавлены к введенной формуле ABS. Это происходит, когда вы вводите формулу массива.
Как найти наибольшее отрицательное значение (меньше 0) в Excel?
Найти наибольшее значение из диапазона очень легко для большинства пользователей Excel, но как насчет поиска наибольшего отрицательного значения (меньше 0) из диапазона данных, смешанного с отрицательными и положительными значениями? Здесь я представляю формулу массива и удобный инструмент, который поможет вам найти наибольшее значение, которое должно быть меньше 0 в Excel.
Найдите максимальное / минимальное отрицательное значение с помощью Kutools for Excel
Найдите максимальное отрицательное значение с помощью формулы
Найдите максимальное / минимальное отрицательное значение с помощью Kutools for Excel
Если у вас есть Kutools for Excel установлен, вы можете использовать Выбрать определенные ячейки полезности и Выберите ячейки с максимальными и минимальными ячейками утилита для поиска и выделения минимального отрицательного значения из диапазона данных.
После бесплатная установка Kutools for Excel, сделайте следующее:
Примечание: вам лучше сохранить копию ваших данных, прежде чем выполнять шаги.
1. Выберите диапазон данных и нажмите Кутулс > Выберите > Выбрать определенные ячейки. Смотрите скриншот:
2. в Выбрать определенные ячейки диалог, проверьте Ячейка вариант под Выберите тип раздел, затем выберите Больше из списка под Конкретный тип раздел и введите 0 в следующее текстовое поле. И нажмите Ok > OK закрыть диалоги. Теперь все положительные клетки выбраны.
3. Затем нажмите Удалить ключ для удаления положительных значений. Смотрите скриншот:
4. Нажмите Кутулс > Выберите > Выберите ячейки с максимальным и минимальным значением . Смотрите скриншот:
5. В появившемся диалоговом окне вы можете указать нужные вам параметры. Смотрите скриншот:
Microsoft Excel использует положительные и отрицательные числа при выполнении математических вычислений. В Excel отрицательные числа по умолчанию отображаются со знаком минус. Это числа, значение которых меньше 0. Excel позволяет легко распознавать отрицательные числа, используя различные передовые методы.
При выполнении вычислений ему необходимо распознавать отрицательные числа. В некоторых сценариях необходимо преобразовать отрицательные числа в положительные числа или представить их в соответствующем формате для надлежащего использования отрицательных значений, например, для определения дисперсии. Требуется представить их, применив определенный условный формат в зависимости от требования.
Настоящая статья направлена на выявление способов управления отрицательными числами в Excel.
Объяснение отрицательных чисел в Excel
Обработка отрицательных чисел в Excel очень проста. Excel может эффективно обрабатывать как положительные, так и отрицательные числа. Но отрицательные числа могут иногда вводить пользователей в заблуждение. Есть вероятность возникновения проблем при обработке отрицательных чисел, если для них выбран неподходящий формат. Это приводит к отображению ошибок, когда пользователь пытается выполнить вычисления на негативах.
Ввести отрицательные числа со знаком минус очень просто, но анализировать их сложно, когда представлена смесь положительных и отрицательных значений. Чтобы избежать этой проблемы, необходимо изменить формат ячеек Excel, содержащих отрицательные значения, чтобы получить точные результаты.
Как показать отрицательные числа в Excel?
Отрицательные числа имеют большое значение при работе с различными приложениями в Excel. Применение набора форматов важно в различных областях.
- Лучшие практики используются финансовыми аналитиками при финансовом моделировании, когда отрицательные значения отображаются красным цветом.
- Отрицательные числа возводятся в квадрат при определении дисперсии в анализе данных для устранения проблемы, представленной стандартным отклонением.
- Когда значение дисперсии отрицательное, оно умножается на -1, чтобы сделать его положительным.
- В VBA отрицательные числа используются для обозначения дат до 1900 года.
- Когда нам нужно единственное абсолютное значение отрицательного числа, для получения положительного значения применяется абсолютная функция.
3 основных способа показать / выделить отрицательные числа в Excel
В этом разделе будут описаны различные способы отображения / выделения отрицательных чисел в Excel.
Вы можете скачать этот шаблон Excel с отрицательными числами здесь — Шаблон Excel с отрицательными числами
Пример №1 — с условным форматированием
Представление отрицательных чисел с условным форматированием
Этот пример предназначен для иллюстрации условного форматирования Excel для выделения отрицательных чисел на различных этапах. В этом примере нам нужно использовать числа меньше 0.
Следующие данные рассматриваются для иллюстрации этого примера.
Шаг 1: Выберите весь диапазон данных, как показано на рисунке.
Перейдите на главную и выберите условное форматирование. Выберите «правила выделения ячеек» и выберите «меньше чем» из представленных вариантов.
Он открывает диалоговое окно «меньше чем», как показано на рисунке.
Шаг 2: Введите значение в пустое поле, представленное в разделе «Форматирование ячеек МЕНЬШЕ, ЧЕМ», и щелкните раскрывающийся список справа до «с», чтобы применить желаемое форматирование к числам. Убедитесь, что выделено «Светло-красная заливка с темно-красным текстом». Затем нажмите «ОК».
Результат показан как,
Из приведенного выше рисунка видно, что ячейки со значениями меньше 0 выделяются красной заливкой и красным цветом текста. Этот тип условного форматирования также применяется к значениям больше, между, равным и повторяющимся значениям с отрицательными значениями.
Представление отрицательных чисел с помощью встроенного форматирования чисел
Этот пример иллюстрирует встроенное форматирование чисел в Excel для выделения отрицательных чисел красным цветом. В этом примере нам нужно использовать числа ниже нуля. Ниже приведены необходимые шаги.
Следующие данные служат иллюстрацией этого примера.
Шаг 1: Выберите данные, к которым вы хотите применить встроенное настраиваемое форматирование, красным цветом.
Шаг 2: Перейдите на вкладку «Главная», затем нажмите «Числовой формат» и щелкните небольшой наклонный значок в правом углу.
Это помогает открыть диалоговое окно «Формат ячеек».
Шаг 3: В диалоговом окне «Формат ячеек» убедитесь, что выделена вкладка «Число». Перейдите в «Номер» на вкладке «Номер». В правой части параметров выберите красный текстовый вариант со скобками для отрицательных чисел. И нажмите «ОК».
Шаг 4: Отформатированные отрицательные числа в Excel будут выглядеть следующим образом:
Когда применяется форматирование, к числам автоматически добавляются две десятичные точки.
Пример № 3 — С произвольным форматированием чисел
Представление отрицательных чисел с произвольным форматированием чисел
Этот пример предназначен для иллюстрации настраиваемого форматирования чисел для выделения отрицательных чисел в Excel. В этом примере нам нужно использовать числа меньше 0.
Следующие данные служат иллюстрацией этого примера.
Шаг 1: Выберите данные, к которым вы хотите применить встроенное настраиваемое форматирование, красным цветом.
Шаг 2: Перейдите на вкладку «Главная», затем нажмите «Числовой формат» и щелкните небольшой наклонный значок в правом углу.
Это помогает открыть диалоговое окно «Формат ячеек».
Шаг 3: В ячейках формата диалоговое окно обеспечивает выделение вкладки «Число». Перейдите к «настраиваемой опции» на вкладке «Число». Выберите подходящий формат, как показано на рисунке. Нажмите ОК, чтобы применить форматирование.
Шаг 4: Отрицательные числа в Excel будут выглядеть следующим образом.
Часто случается, что в одном диапазоне ячеек находятся вместе положительные и отрицательные числа. Необходимо определить экстремальные значения, но с определенными условиями. Следует получить наименьшее значение для положительных чисел и отдельно для отрицательных. Для решения данной задачи не достаточно просто использовать функции =МИН(), =МАКС() или =НАИМЕНЬШИЙ(), =НАИБОЛЬШИЙ(). Иначе при обращении функций к диапазону с перемешанными положительными и отрицательными числами, будет возвращено только отрицательное наименьшее значение. Поэтому следует использовать специальную формулу с функциями.
Наименьшее положительное число
Допустим у нас есть статистика доходов и расходов сети магазинов за месяц. А нам нужно узнать какие из торговых точек наименее затратные по расходам и наименее прибыльны по доходам. Для презентации решения будем использовать пример таблицы с доходами и расходами магазинов как показано на рисунке:
В первую очередь следует найти наименьший доход. Для этого формула должна сначала рассмотреть группу, состоящую только из положительных. После чего найдите наименьшее положительное значение функции. Для этого:
- В ячейке D2 введем следующую формулу:
- После ввода формулы для подтверждения необходимо нажать комбинацию горячих клавиш: CTRL+SHIFT+Enter, так как она должна выполняться в массиве. Если все сделано правильно тогда в строке формул будут видны фигурные скобки.
Разбор принципа действия формулы для поиска наименьшего положительного числа:
Логическая функция =ЕСЛИ(), которая выполняется в массиве формул, проверяет каждую ячейку диапазона C2:C13 какая из них содержит число меньше чем 0 или равно нулю (<=0). Таким образом создается условная таблица в памяти с логическими значениями:
- ЛОЖЬ – для положительных чисел (в данном случаи и для нуля);
- ИСТИНА – для отрицательных чисел.
После чего в условной таблице функция ЕСЛИ заменяет все значения ИСТИНА на пустые (””). А вместо значения ЛОЖЬ подставляются те числа положительные, которые и были раньше в диапазоне C2:C13. И завершает вычисления формулы функция =МИН(), которая из условной таблицы (уже отфильтрованной от отрицательных значений и нулей) возвращает нам наименьшее положительное число.
Наибольшее отрицательное число в диапазоне ячеек
Чтобы найти наименьшие расходы среди отрицательных значений укажите наибольшее. Для этого следует выполнить следующие действия:
- В ячейке E2 введите формулу: =0;””;C2:C13))' >
- Так же, как и предыдущей формуле после ввода необходимо нажать комбинацию клавиш: CTRL+SHIFT+Enter, так как она должна выполняться в массиве. Если все сделано правильно в строке формул появятся фигурные скобки.
В результате мы быстро и легко переделили самый слабый магазин по доходам – это №4. А наименее затратная торговая точка по расходам – это магазин №2.
В процессе работы с большим объемом числовых данных в таблицах очень удобно различать цветами положительные и отрицательные значения. Таким образом таблица приобретает более чит Принцип выделения цветом отрицательных и положительных числовых значений:
Значение каждой ячейки проверено в соответствии с первым критерием, а потом с другим. Оба условия можно выразить формулой:
Введите данную формулу в ячейку D2, а потом скопируйте ее в остальные ячейки диапазона D2:D8.
Формула проверила соответствие оформления ячеек с критериями и наглядно отобразила принцип действия автоматического выделения цветом данных.
Как выделить строку с отрицательным значением в Excel
Достаточно часто нужно выделить цветом целую строку, а не только ячейку в зависимости от того какое значение содержит эта же ячейка. Для решения данной задачи нельзя использовать упрощенные правила выделения ячеек. Следует использовать формулы в условном форматировании с правильной адресацией ссылок на ячейки.
Рассмотрим, как выделить строку в Excel цветом автоматически:
- Удалим ранее созданное условное форматирование: «ГЛАВНАЯ»-«Условное форматирование»-«Удалить правила»-«Удалить правила со всего листа».
- Выделите диапазон ячеек A2:D8 и выберите инструмент: «ГЛАВНАЯ»-«Условное форматирование»-«Создать правило».
- В разделе данного окна «Выберите тип правила:» выберите опцию «Использовать формулу для определения форматированных ячеек».
- В поле ввода введите формулу: =$C2
- Щелкните по кнопке «Формат» и в паявшемся окне перейдите на вкладку «Шрифт», в разделе «Цвет:» выберите красный. После на всех открытых окнах нажмите «ОК».
Результат действия формулы с условным форматированием, которая сделала таблицу еще более читабельной.
Примечание. Таким же самым образом можно присвоить диапазону A2:D8 новое правило для выделения строк на против ячеек с положительным значением другим цветом. Только нужно указать другую формулу: =$C2>0 и задать другой желаемый формат.
Полезный совет! При редактировании формул в поле окна «Создания правила форматирования» не используйте стрелки на клавиатуре для перемещения клавиатурного курсора. Это приведет к перемещению по ячейкам курсора Excel для автоматического заполнения ссылками в аргументах формулы. Если же Вы хотите использовать стрелки клавиатуры для редактирования, то сначала нажмите клавишу F2 (она работает как переключатель между режимами редактирования и автозаполнения аргументов).
Разбор принципа действия автоматического выделения строк красным цветом с отрицательным значением:
Если нужно выделить цветом целую строку таблицы в, которой находится ячейка (определенного столбца) с отрицательным числовым значением следует использовать соответственные смешанные адреса ссылок в аргументах формулы. Первое действие, которое мы выполнили при решении данной задачи – это выделение всего диапазона A2:D8. Это значит, что каждая ячейка данного диапазона будет проверена на соответствие с условиями правил форматирования относительно определенного столбца, на который формула ссылается абсолютной ссылкой =$C. Это значит, что формула будет выполнятся, учитывая значения только в определенном столбце $C. Перед номером строки отсутствует символ $ это значит, что формат распространяется и на другие ячейки вдоль конкретной строки.
Читайте также: