Excel является ли число положительным

Обновлено: 05.01.2025

Функция ЕСЛИ позволяет выполнять логические сравнения значений и ожидаемых результатов. Она проверяет условие и в зависимости от его истинности возвращает результат.

=ЕСЛИ(это истинно, то сделать это, в противном случае сделать что-то еще)

Но что делать, если необходимо проверить несколько условий, где, допустим, все условия должны иметь значение ИСТИНА или ЛОЖЬ (И), только одно условие должно иметь такое значение (ИЛИ) или вы хотите убедиться, что данные НЕ соответствуют условию? Эти три функции можно использовать самостоятельно, но они намного чаще встречаются в сочетании с функцией ЕСЛИ.

Используйте функцию ЕСЛИ вместе с функциями И, ИЛИ и НЕ, чтобы оценивать несколько условий.

ЕСЛИ(И()): ЕСЛИ(И(лог_выражение1; [лог_выражение2]; …), значение_если_истина; [значение_если_ложь]))

ЕСЛИ(ИЛИ()): ЕСЛИ(ИЛИ(лог_выражение1; [лог_выражение2]; …), значение_если_истина; [значение_если_ложь]))

ЕСЛИ(НЕ()): ЕСЛИ(НЕ(лог_выражение1), значение_если_истина; [значение_если_ложь]))

Имя аргумента

лог_выражение (обязательно)

Условие, которое нужно проверить.

значение_если_истина (обязательно)

Значение, которое должно возвращаться, если лог_выражение имеет значение ИСТИНА.

значение_если_ложь (необязательно)

Значение, которое должно возвращаться, если лог_выражение имеет значение ЛОЖЬ.

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

И: =ЕСЛИ(И(условие; другое условие); значение, если ИСТИНА; значение, если ЛОЖЬ)

ИЛИ: =ЕСЛИ(ИЛИ(условие; другое условие); значение, если ИСТИНА; значение, если ЛОЖЬ)

НЕ: =ЕСЛИ(НЕ(условие); значение, если ИСТИНА; значение, если ЛОЖЬ)

Примеры

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

Ниже приведены формулы с расшифровкой их логики.

Если A2 (25) больше нуля и B2 (75) меньше 100, возвращается значение ИСТИНА, в противном случае возвращается значение ЛОЖЬ. В этом случае оба условия имеют значение ИСТИНА, поэтому функция возвращает значение ИСТИНА.

Если A3 ("синий") = "красный" и B3 ("зеленый") равно "зеленый", возвращается значение ИСТИНА, в противном случае возвращается значение ЛОЖЬ. В этом случае только одно условие имеет значение ИСТИНА, поэтому возвращается значение ЛОЖЬ.

Если A4 (25) больше нуля или B4 (75) меньше 50, возвращается значение ИСТИНА, в противном случае возвращается значение ЛОЖЬ. В этом случае только первое условие имеет значение ИСТИНА, но поскольку для ИЛИ требуется, чтобы только один аргумент был истинным, формула возвращает значение ИСТИНА.

Если значение A5 ("синий") равно "красный" или значение B5 ("зеленый") равно "зеленый", возвращается значение ИСТИНА, в противном случае возвращается значение ЛОЖЬ. В этом случае второй аргумент имеет значение ИСТИНА, поэтому формула возвращает значение ИСТИНА.

Если A6 (25) НЕ больше 50, возвращается значение ИСТИНА, в противном случае возвращается значение ЛОЖЬ. В этом случае значение не больше чем 50, поэтому формула возвращает значение ИСТИНА.

Если значение A7 ("синий") НЕ равно "красный", возвращается значение ИСТИНА, в противном случае возвращается значение ЛОЖЬ.

Обратите внимание, что во всех примерах есть закрывающая скобка после условий. Аргументы ИСТИНА и ЛОЖЬ относятся ко внешнему оператору ЕСЛИ. Кроме того, вы можете использовать текстовые или числовые значения вместо значений ИСТИНА и ЛОЖЬ, которые возвращаются в примерах.

Вот несколько примеров использования операторов И, ИЛИ и НЕ для оценки дат.

Ниже приведены формулы с расшифровкой их логики.

Если A2 больше B2, возвращается значение ИСТИНА, в противном случае возвращается значение ЛОЖЬ. В этом случае 12.03.14 больше чем 01.01.14, поэтому формула возвращает значение ИСТИНА.

Если A3 больше B2 И меньше C2, возвращается значение ИСТИНА, в противном случае возвращается значение ЛОЖЬ. В этом случае оба аргумента истинны, поэтому формула возвращает значение ИСТИНА.

Если A4 больше B2 ИЛИ меньше B2+60, возвращается значение ИСТИНА, в противном случае возвращается значение ЛОЖЬ. В этом случае первый аргумент равен ИСТИНА, а второй — ЛОЖЬ. Так как для оператора ИЛИ требуется, чтобы один из аргументов был истинным, формула возвращает значение ИСТИНА. Если вы используете мастер вычисления формул на вкладке "Формулы", вы увидите, как Excel вычисляет формулу.

Если A5 не больше B2, возвращается значение ИСТИНА, в противном случае возвращается значение ЛОЖЬ. В этом случае A5 больше B2, поэтому формула возвращает значение ЛОЖЬ.

Использование операторов И, ИЛИ и НЕ с условным форматированием

Вы также можете использовать операторы И, ИЛИ и НЕ в формулах условного форматирования. При этом вы можете опустить функцию ЕСЛИ.

На вкладке Главная выберите Условное форматирование > Создать правило. Затем выберите параметр Использовать формулу для определения форматируемых ячеек, введите формулу и примените формат.

Вот как будут выглядеть формулы для примеров с датами:

Если A2 больше B2, отформатировать ячейку, в противном случае не выполнять никаких действий.

Если A3 больше B2 И меньше C2, отформатировать ячейку, в противном случае не выполнять никаких действий.

Если A4 больше B2 ИЛИ меньше B2 + 60, отформатировать ячейку, в противном случае не выполнять никаких действий.

Если A5 НЕ больше B2, отформатировать ячейку, в противном случае не выполнять никаких действий. В этом случае A5 больше B2, поэтому формула возвращает значение ЛОЖЬ. Если изменить формулу на =НЕ(B2>A5), она вернет значение ИСТИНА, а ячейка будет отформатирована.

Примечание: Распространенная ошибка — ввод формулы в условное форматирование без знака "равно" (=). При этом вы увидите, что в диалоговом оке Условное форматирование в формулу будут добавлены знаки "равно" и кавычка: ="ИЛИ(A4>B2;A4<B2+60)",поэтому вам потребуется удалить кавычка, чтобы формула правильно отреагирует.

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.

Когда вы используете Microsoft Excel для работы с числами, вы можете сделать так, чтобы в Excel всегда было положительное число, чтобы найти абсолютное значение числа. Абсолютное значение всегда положительно. В других случаях вы можете предпочесть отображать финансовые данные, используя все положительные числа, причем отрицательные суммы выделены красным или выделены чем-то, кроме знака минус. Используйте формулу ABS в Excel, чтобы получить абсолютное значение и удалить знак, или используйте пользовательское форматирование, чтобы отрицательные числа выглядели положительными.

кредит: fizkes / iStock / GettyImages

ABS Formula In Excel

В математике абсолютное значение числа - это его расстояние от нуля, то есть его значение без положительного или отрицательного знака. Он часто представлен вертикальными чертами, поэтому | -5 | = | 5 | = 5, например. В Excel вы вычисляете абсолютное значение, используя формулу функции ABS.

С формулой = ABS (-5) Excel преобразует отрицательное значение в положительное и дает результат 5. Вы можете обернуть большее вычисление в функции ABS, чтобы результат всегда был положительным.

Коды пользовательских форматов

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

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

Другие программы для работы с электронными таблицами поддерживают похожие коды.

Условное форматирование

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

Как определить, какой провод динамика является положительным

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

Сделать полосы прокрутки всегда показывать и отключать прозрачность в окнах 10

Fluent Design System от Microsoft по-прежнему будет неотъемлемой частью Windows 10 в будущем. Но если вы не фанат, вы можете управлять некоторыми видами поведения.

Как сделать так, чтобы приложения для Windows 10 всегда запускались с правами администратора

Если вам нужно запускать приложения в Windows 10 с повышенными привилегиями, вот простой способ убедиться, что они всегда работают от имени администратора.

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

Показывать только положительные числа в диапазоне с помощью форматных ячеек

Вы можете применить Формат ячеек функция, чтобы отображать только положительные числа в выделенном фрагменте в Excel. Пожалуйста, сделайте следующее:

1. Выберите диапазон, в котором вы скроете все отрицательные числа, щелкните правой кнопкой мыши и выберите Формат ячеек из контекстного меню. Смотрите скриншот:

2. В Формат ячеек диалоговое окно, щелкните На заказ в Категория список, введите код формата 0; [Белый] 0 в Тип и нажмите OK кнопка. Смотрите скриншот:

Ноты:
(1) Код формата 0; [Белый] 0 покажет все положительные числа как целые числа. Если вам нужно сохранить десятичные разряды, измените код формата на 0.00; [Белый] 0.00.
(2) В коде формата 0; [Белый] 0, Белый означает отображение отрицательных чисел белым цветом. Если цвет заливки ячеек другой, измените Белый к цвету заливки.

Теперь вы увидите, что в выбранном диапазоне отображаются только положительные числа. Смотрите скриншот:

Быстро изменить все отрицательные значения на ноль или положительные значения в Excel

В некоторых случаях отрицательные значения могут быть недопустимыми, и вам необходимо изменить все отрицательные числа на ноль или заменить все отрицательные числа на их абсолютные значения. С Kutools для Excel Изменить знак ценностей утилита, вы можете сделать это легко. Полнофункциональная бесплатная 30-дневная пробная версия!

Kutools for Excel - Включает более 300 удобных инструментов для Excel. Полнофункциональная бесплатная 30-дневная пробная версия, кредитная карта не требуется! Get It Now

Показывать только положительные числа в диапазоне с условным форматированием

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

1. Выберите диапазон, в котором будут отображаться только положительные числа, и нажмите Главная > Условное форматирование > Новое правило. Смотрите скриншот:

2. В диалоговом окне «Новое правило форматирования» выполните следующие действия:
(1) Щелкните, чтобы выделить Используйте формулу, чтобы определить, какие ячейки следует форматировать. в Выберите тип правила список;
(2) Тип = A1 <0 (A1 - первая ячейка в выделенном фрагменте) в Формат значений, где эта формула истинна коробка;
(3) Щелкните значок Формат кнопка. Смотрите скриншот:

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

4, Нажмите OK кнопки, чтобы закрыть оба диалоговых окна.

Теперь вы увидите, что в выбранном диапазоне отображаются только положительные числа. Смотрите скриншот:

Показывать только положительные числа в результатах расчета формул

Например, у меня есть таблица, как показано на скриншоте ниже, теперь я хочу вычислить итоги в столбце E, но скрыть все отрицательные результаты вычислений:

Введите формулу =IF(SUM(A2:D2)<0,"",SUM(A2:D2)) в ячейке E2, а затем перетащите маркер автозаполнения вниз, чтобы заполнить столбец E.

Теперь вы увидите, что все итоги рассчитываются в столбце E, и отображаются только положительные результаты расчетов. Смотрите скриншот:

Большинство людей, работающих с электронными таблицами Excel, имеют дело с числами в больших / малых наборах данных. И когда вы работаете с числами, у вас будут все их типы (положительные, отрицательные, десятичные, дата / время).

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

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

Так что, если вам интересно, продолжайте читать!

Умножьте на (-1), чтобы преобразовать отрицательное число в положительное

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

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

Предположим, у вас есть набор данных, как показано ниже:

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

Вышеупомянутая формула использует функцию IF, чтобы сначала проверить, является ли число положительным или нет. Если оно положительное, знак не меняется, а если оно отрицательное, к ссылке добавляется отрицательный знак, что дает нам только положительное число.

Если у вас также есть текстовые значения в наборе данных, эта функция будет игнорировать это (и будут изменены только отрицательные значения)

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

Используйте функцию ABS, чтобы изменить все отрицательные числа на положительные

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

.. функция ABS

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

Ниже приведена формула, которая сделает это:

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

Умножьте с помощью специальной вставки, чтобы перевернуть знак

Если вы хотите изменить знак числа (т. Е. Изменить отрицательный на положительный и положительный на отрицательный), вы также можете использовать эту специальную технику умножения вставки.

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

Ниже приведены шаги по изменению знака с помощью специальной вставки:

В любой пустой ячейке на листе введите -1.
Скопируйте эту ячейку (со значением -1)
Выберите диапазон, в котором вы хотите перевернуть знак.
Щелкните правой кнопкой мыши любую из выбранных ячеек
Щелкните Специальная вставка. Откроется диалоговое окно Специальная вставка.
В разделе «Вставить» выберите «Значения».
В параметрах операции выберите «Умножить».
Нажмите ОК
Удалить -1 из ячейки

Но что, если вы хотите преобразовать только отрицательные числа в положительные, а не наоборот?

В этом случае вам каким-то образом сначала нужно выбрать все отрицательные числа, а затем выполнить указанные выше шаги.

Вот как выбрать в Excel только отрицательные числа:

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

Этот метод имеет два преимущества по сравнению с методом формул (два метода, описанные до этого):

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

Мигающая заливка для удаления отрицательного знака

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

Это можно использовать, когда у вас есть имена, и вы хотите разделить имя и фамилию. Как только вы несколько раз наберете имя в соседней ячейке, Flash Fill определит шаблон и выдаст вам все имена.

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

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

Ниже приведены шаги по замене отрицательных чисел на положительные с помощью Flash Fill:

В поле рядом с таблицей с данными введите ожидаемый результат вручную. В этом примере я вручную введу 874
В ячейке под ним введите ожидаемый результат (162 в этом примере).
Выберите обе ячейки
Поместите курсор в нижнюю правую часть выделения. Он изменится на значок плюса
Щелкните и перетащите, чтобы заполнить столбец (или дважды щелкните)
Щелкните значок Параметры автозаполнения.
Нажмите на Flash Fill.

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

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

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

Преобразование отрицательных чисел в положительные одним щелчком мыши (VBA)

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

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

В таком случае вы можете создать и сохранить код макроса VBA в личной книге макросов и разместить VBA на панели быстрого доступа. Таким образом, в следующий раз, когда вы получите набор данных, в котором вам нужно это сделать, вы просто выберите данные и щелкните значок в QAT…

Не волнуйтесь, я покажу вам точные шаги, чтобы запустить его.

Ниже приведен код VBA, который преобразует отрицательные значения в положительные значения в выбранном диапазоне:

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

Теперь позвольте мне показать вам, как добавить этот код на панель быстрого доступа (шаги одинаковы, независимо от того, сохраняете ли вы этот код в отдельной книге или в PMW)

Теперь у вас будет значок макроса в QAT.

Чтобы использовать этот макрос одним щелчком мыши, просто сделайте выбор и щелкните значок макроса.

Примечание. Если вы сохраняете код макроса VBA в книге, вам необходимо сохранить книгу в формате с поддержкой макросов (XLSM).

Часто случается, что в одном диапазоне ячеек находятся вместе положительные и отрицательные числа. Необходимо определить экстремальные значения, но с определенными условиями. Следует получить наименьшее значение для положительных чисел и отдельно для отрицательных. Для решения данной задачи не достаточно просто использовать функции =МИН(), =МАКС() или =НАИМЕНЬШИЙ(), =НАИБОЛЬШИЙ(). Иначе при обращении функций к диапазону с перемешанными положительными и отрицательными числами, будет возвращено только отрицательное наименьшее значение. Поэтому следует использовать специальную формулу с функциями.

Наименьшее положительное число

Допустим у нас есть статистика доходов и расходов сети магазинов за месяц. А нам нужно узнать какие из торговых точек наименее затратные по расходам и наименее прибыльны по доходам. Для презентации решения будем использовать пример таблицы с доходами и расходами магазинов как показано на рисунке:

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

В ячейке D2 введем следующую формулу:
После ввода формулы для подтверждения необходимо нажать комбинацию горячих клавиш: CTRL+SHIFT+Enter, так как она должна выполняться в массиве. Если все сделано правильно тогда в строке формул будут видны фигурные скобки.

Разбор принципа действия формулы для поиска наименьшего положительного числа:

Логическая функция =ЕСЛИ(), которая выполняется в массиве формул, проверяет каждую ячейку диапазона C2:C13 какая из них содержит число меньше чем 0 или равно нулю (<=0). Таким образом создается условная таблица в памяти с логическими значениями:

ЛОЖЬ – для положительных чисел (в данном случаи и для нуля);
ИСТИНА – для отрицательных чисел.

После чего в условной таблице функция ЕСЛИ заменяет все значения ИСТИНА на пустые (””). А вместо значения ЛОЖЬ подставляются те числа положительные, которые и были раньше в диапазоне C2:C13. И завершает вычисления формулы функция =МИН(), которая из условной таблицы (уже отфильтрованной от отрицательных значений и нулей) возвращает нам наименьшее положительное число.

Наибольшее отрицательное число в диапазоне ячеек

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

В ячейке E2 введите формулу: =0;””;C2:C13))' >
Так же, как и предыдущей формуле после ввода необходимо нажать комбинацию клавиш: CTRL+SHIFT+Enter, так как она должна выполняться в массиве. Если все сделано правильно в строке формул появятся фигурные скобки.

В результате мы быстро и легко переделили самый слабый магазин по доходам – это №4. А наименее затратная торговая точка по расходам – это магазин №2.

Читайте также: