Как задать в excel условие: Использование ЕСЛИ с функциями И, ИЛИ и НЕ

Как изменить положительные числа на отрицательные и наоборот в Excel

Виктор Бухтеев

07 дек в 2021

10K

Обсудить

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

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

Способ 1: Формула + вспомогательный столбец

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

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

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

1. Напротив текущей таблицы в пустой клетке введите =ЕСЛИ(), объявив тем самым интересующую нас функцию.

2. Затем впишите условие A1<>»». Это означает, что действие нужно выполнить, когда в ячейке нет пустоты.

3. После этого введите само действие, то есть умножьте ячейку на -1, записав это как A1*-1;. После единицы обязательно поставьте точку с запятой.

4. Если же условие не удовлетворяется, то есть ячейка пустая, ее нужно оставить пустой. Укажите это в формуле как «».

5. Нажмите Enter и ознакомьтесь с результатом. Вы видите, что получили значение без минуса или с ним, в зависимости от изначального числа.

6. Растяните функцию на необходимое число клеток в сторону и вниз, чтобы покрыть все ячейки таблицы, после чего скопируйте ее, нажав сочетание клавиш Ctrl + C.

7. Щелкните правой кнопкой мыши по первому значению изначальной таблицы и из контекстного меню выберите параметр вставки «Значение». Иконку вы видите на следующем скриншоте.

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

Внимательно ознакомьтесь со скриншотами, и у вас все получится! Если хотите, просто вставьте готовую формулу, заменив номер ячейки на свой. Выглядит она так: =ЕСЛИ(A1<>»»;A1*-1;»»).

Комьюнити теперь в Телеграм

Подпишитесь и будьте в курсе последних IT-новостей

Подписаться

Способ 2: Умножение на значение

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

1. В любой пустой ячейке напишите -1, затем скопируйте ее (именно ячейку, а не значение).

2. Выделите таблицу с данными и нажмите Ctrl + G для вызова необходимого меню.

3. В окне «Переход» кликните по кнопке «Выделить».

4. Отметьте маркером пункт «Константы» и примените изменения.

5. Сейчас вы снимаете выделение с тех ячеек, в которых ничего не содержится, что и видно на скриншоте ниже.

6. Остается кликнуть по любой ячейке ПКМ и выбрать пункт «Специальная вставка».

7. В блоке «Вставить» отметьте маркером «Значения».

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

9. После нажатия на «ОК» в окне вы увидите, что изменения вступили в силу, а в пустых ячейках 0 не появился.

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

Личный опыт

Наши постоянные авторы и читатели делятся лайфхаками, основанными на личном опыте. Полная свобода самовыражения.

Рекомендуем

Автоматическое применение условного форматирования на основе правил

Применить к

Smartsheet

• Pro
• Business
• Enterprise

Возможности

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

PLANS

• Smartsheet
• Pro
• Business
• Enterprise

Права доступа

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

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

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

Условное форматирование связанных ячеек не сохраняется в целевой таблице.

Чтобы применить условное форматирование, выполните следующие действия:

1. Задайте условия для правила.
2. Определите форматирование, которое будет применяться при выполнении условий.
3. Укажите, где будет применяться форматирование.

Как задать условия для правила

1. Выберите значок Условное форматирование  в верхней панели.
2. Выберите Добавить новое правило.
3. Нажмите <задать условие>.

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

Важная информация о настройке условий

• Доступные критерии зависят от типа столбца:
  • Для столбца с флажком критерием может быть Отмечено или Без флага.
  • Для столбца с датой критерием может быть в прошлые или в следующие [x] дн.
• Числа в столбцах с процентными значениями (например, в столбце «% выполнения») рассматриваются как значения от 0 до 1. Чтобы система правильно сравнила такие значения, используйте десятичные дроби (например, 0,25 для 25 % и 0,5 для 50 %).
• Чтобы дополнительно настроить правило, нажмите определить настраиваемые критерии в верхней части окна критериев.
• Чтобы задать форматирование для строк, не соответствующих критериям, установите флажок Применить формат, если условие НЕ выполняется.

Определение форматирования

Необходимо задать формат, который приобретёт текст, если строка станет соответствовать указанным критериям. Для этого выполните следующие действия.

1. В диалоговом окне Условное форматирование выберите этот формат.  
2. Укажите параметры форматирования в открывшемся окне.
   • Шрифт. Выберите начертание шрифта.
   • Размер шрифта. Настройте размер шрифта.
   • Панель задач. Если у вас имеется диаграмма Ганта, используйте этот параметр, чтобы автоматически менять цвет панели задач, когда строка начинает соответствовать указанным условиям.
     Выбранные цвета будут применены к панели Ганта и панели календаря. 
   • Другие кнопки форматирования. Настройте дополнительные параметры текста. 

Настройка области применения форматирования

1. По умолчанию форматирование применяется ко всей строке. Эту область можно изменить, нажав ко всей строке и выбрав отдельные столбцы.
   Если для нескольких правил задано одно и то же условие и форматирование, такие правила будут объединены в одно.
2. Чтобы закрыть диалоговое окно Условное форматирование, нажмите OK.

Этот Справочная статья есть в следующих учебных курсах:

Учебный курс

Эта статья представлена в Уровень 1. Начало работы учебный курс Получите максимум от этого учебного курса, начав с самого начала.

Связанный контент

Справочная статья

Применение форматирования к данным

Для применения форматирования к цифрам и тексту в таблице используйте кнопки на панели инструментов в верхней части окна Smartsheet….

Вам помогла эта статья?

ДаНет

Выделение паттернов и трендов с условным форматированием

Excel для Microsoft 365 для Mac Excel 2021 для Mac Excel 2019 для Mac Excel 2016 для Mac Excel для Mac 2011 Больше…Меньше

Некоторые материалы в этом разделе могут быть неприменимы к некоторым языкам.

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

Информация о температуре с примененным условным форматированием, которая показывает верхние 10 % и нижние 10 % значений

Информация о температуре с применением условного форматирования по трехцветной шкале

Применить условное форматирование

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

2. org/ListItem»>

   На вкладке Главная щелкните Условное форматирование .

3. Выполните одно из следующих действий:

   Чтобы выделить	Сделай это
   Значения в определенных ячейках. Примерами могут служить даты после этой недели, числа от 50 до 100 или последние 10 % результатов.	Укажите на Выделите правила ячеек или Верхние/нижние правила , а затем щелкните соответствующий параметр.
   Связь значений в диапазоне ячеек. Расширяет полосу цвета по всей ячейке. Примерами являются сравнения цен или населения в крупнейших городах.	Наведите указатель мыши на Гистограммы данных и щелкните нужную заливку.
   Связь значений в диапазоне ячеек. Применяет цветовую шкалу, в которой интенсивность цвета ячейки отражает размещение значения в верхней или нижней части диапазона. Например, распределение продаж по регионам.	Укажите на Color Scales , а затем щелкните нужную шкалу.
   Диапазон ячеек, содержащий от трех до пяти групп значений, где каждая группа имеет собственное пороговое значение. Например, вы можете назначить набор из трех значков для выделения ячеек, отражающих продажи ниже 80 000, ниже 60 000 и ниже 40 000 долларов. Или вы можете назначить 5-балльную рейтинговую систему для автомобилей и применить набор из пяти значков.	Укажите Наборы значков , а затем щелкните набор.

Дополнительные опции

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

2. На вкладке Главная щелкните Условное форматирование .

3. Укажите на Выделите правила ячеек и щелкните Текст, содержащий .

4. Введите текст, который хотите выделить, и нажмите OK .

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

2. На вкладке Главная щелкните Условное форматирование .

3. org/ListItem»>

   Щелкните Новое правило .

4. Выберите стиль, например, 3-цветная шкала, выберите нужные условия и нажмите OK .

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

2. На вкладке Главная щелкните Условное форматирование .

3. Укажите на Правила выделения ячеек , а затем щелкните Повторяющиеся значения .

4. Рядом с значениями в выбранном диапазоне щелкните уникальный или дубликат .

1. Выберите ячейку с условным форматированием, которое вы хотите скопировать.

2. На вкладке Главная (или стандартной панели инструментов в Excel для Mac 2011) нажмите Формат   , а затем выберите ячейки, в которые вы хотите скопировать условное форматирование.

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

1. Щелкните любую ячейку.

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

2. В меню Редактировать выберите Найти > Перейти к , а затем щелкните Специальный .

3. Нажмите Условные форматы .

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

org/ItemList»>

1. Выберите ячейки с условным форматированием, которое вы хотите удалить.

2. На вкладке Главная щелкните Условное форматирование .

3. Укажите на Clear Rules , а затем выберите нужный параметр.

   Совет:  Чтобы удалить все условные форматы и все другие форматы ячеек для выбранных ячеек, в меню Изменить выберите Очистить , а затем нажмите Форматы .

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

1. Щелкните диапазон, содержащий правило условного форматирования, которое вы хотите изменить.

2. На вкладке Главная щелкните Условное форматирование .

3. Щелкните Управление правилами .

4. Выберите правило и нажмите Изменить правило .

5. org/ListItem»>

   Внесите необходимые изменения, нажмите OK , а затем еще раз нажмите OK .

Вы можете удалить условные форматы, которые вам больше не нужны.

1. Щелкните диапазон, содержащий правило условного форматирования, которое вы хотите изменить.

2. На вкладке Главная щелкните Условное форматирование .

3. Щелкните Управление правилами .

4. org/ListItem»>

   Выберите правило и щелкните .

5. Нажмите OK .

См. также

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

Применение проверки данных к ячейкам

Коды форматов чисел

Видео: использование условного форматирования — служба поддержки Майкрософт

Используйте условное форматирование

Обучение Эксель 2013.

Используйте условное форматирование

Используйте условное форматирование

Используйте условное форматирование

• Используйте условное форматирование
  

  видео

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

  видео

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

  видео

• Скопируйте и удалите
  

  видео

Следующий:

ВПР — когда и как использовать

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

Хотите больше?

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

Добавление, изменение, поиск или удаление условных форматов

Поднимите условное форматирование на новый уровень

Условное форматирование предоставляет визуальные подсказки, помогающие разобраться в данных.

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

Я хочу узнать, сколько продал каждый продавец.

Я выбираю диапазон ячеек в Продажи, нажимаю кнопку Быстрый анализ , использую вкладку по умолчанию ФОРМАТИРОВАНИЕ .