Как прописать в экселе формулу с условием: Функции Excel ЕСЛИ (IF) и ЕСЛИМН (IFS) для нескольких условий

Excel 2016: условное форматирование

Урок 24: условное форматирование

/en/excel2016/charts/content/

Введение

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

Дополнительно: загрузите нашу рабочую тетрадь.

Посмотрите видео ниже, чтобы узнать больше об условном форматировании в Excel.

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

Условное форматирование позволяет автоматически применять форматирование — например, цветов , значков и гистограмм — к одной или нескольким ячейкам на основе значения ячейки 900. Для этого вам нужно создать правило условного форматирования . Например, правило условного форматирования может быть таким: Если значение меньше 2000 долларов, закрасьте ячейку красным цветом . Применяя это правило, вы сможете быстро увидеть, какие ячейки содержат значения меньше 2000 долларов США.

Чтобы создать правило условного форматирования:

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

  1. Выберите нужных ячеек для правила условного форматирования.
  2. На вкладке Главная щелкните команду Условное форматирование . Появится раскрывающееся меню.
  3. Наведите указатель мыши на нужный тип условного форматирования , затем выберите нужное правило в появившемся меню. В нашем примере мы хотим выделить ячеек , которые на больше, чем 4000 долларов.
  4. Появится диалоговое окно. Введите нужное значение(я) в пустое поле. В нашем примере мы введем 4000 в качестве значения.
  5. Выберите форматирование стиль из раскрывающегося меню. В нашем примере мы выберем Зеленая заливка с темно-зеленым текстом , затем нажмем OK .
  6. Условное форматирование будет применено к выбранным ячейкам. В нашем примере легко увидеть, какие продавцы достигли цели продаж в 4000 долларов за каждый месяц.

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

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

В Excel есть несколько предопределенных стилей — или предустановок — которые можно использовать для быстрого применения условного форматирования к вашим данным. Они сгруппированы в три категории:

  • Панели данных — это горизонтальные полосы, добавляемые к каждой ячейке, очень похожие на гистограмму .
  • Цветовые шкалы меняют цвет каждой ячейки в зависимости от ее значения. Каждая цветовая шкала использует двух- или трехцветный градиент . Например, в Зелено-Желто-Красный Цветовая шкала , самые высокие значения — зеленые, средние значения — желтые, а самые низкие значения — красные.
  • Наборы значков добавляют определенный значок к каждой ячейке в зависимости от ее значения.
Чтобы использовать предустановленное условное форматирование:
  1. Выберите нужных ячеек для правила условного форматирования.
  2. Щелкните команду Условное форматирование . Появится раскрывающееся меню.
  3. Наведите указатель мыши на нужный пресет , затем выберите стиль пресета в появившемся меню.
  4. Условное форматирование будет применено к выбранным ячейкам.

Удаление условного форматирования

Чтобы удалить условное форматирование:
  1. Щелкните команду Условное форматирование . Появится раскрывающееся меню.
  2. Наведите указатель мыши на Clear Rules, и выберите, какие правила вы хотите очистить. В нашем примере мы выберем Удалить правила со всего листа , чтобы удалить все условное форматирование с рабочего листа.
  3. Условное форматирование будет удалено.

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

Вызов!

  1. Откройте нашу рабочую тетрадь.
  2. Щелкните вкладку рабочего листа Challenge в левом нижнем углу рабочей книги.
  3. Выберите ячейки B3:J17 .
  4. Допустим, вы учитель и хотите легко увидеть все оценки ниже проходного. Примените Условное форматирование , чтобы оно Выделяло ячейки , содержащие значения Меньше чем 70 со светло-красной заливкой .
  5. Теперь вы хотите сравнить оценки друг с другом. На вкладке Условное форматирование выберите набор значков под названием 3 Символы (обведены) . Подсказка : Имена наборов значков будут отображаться при наведении на них курсора.
  6. Ваша электронная таблица должна выглядеть следующим образом:
  7. Используя функцию «Управление правилами», удалите светло-красную заливку , но оставьте набор значков .

Продолжать

Предыдущий: Карты

Далее:Отслеживание изменений и комментариев

/en/excel2016/track-changes-and-comments/content/

Условное форматирование: операторы If в Excel

Тепинг Крокер

Категории: Условное форматирование, Excel®
Метки: If/Then Условное форматирование

*Действия, описанные в этой статье, относятся к Excel 2007–2016. Изображения были сделаны с использованием Excel 2016.

Если вы являетесь поклонником функции условного форматирования Excel, вы, вероятно, найдете все больше и больше способов выделить полезную информацию в ваших данных. Среди этих «зависимых от условного форматирования» часто возникает вопрос: Могу ли я использовать формулу If/Then для форматирования ячейки?

Ответ и да и нет. Любой аргумент условного форматирования должен генерировать результат TRUE, а это означает, что на буквальном уровне ваше правило условного форматирования представляет собой оператор If/Then в соответствии со строками «Если это условие TRUE, THEN отформатируйте ячейку таким образом».

Что условное форматирование не может сделать в одном правиле , так это условие IF/THEN/ELSE, такое как «Если # больше 10, форматировать красным, иначе форматировать зеленым». Вместо этого для этого потребуются ДВА правила: одно для «больше 10» и одно для «меньше 10».

Давайте рассмотрим несколько сценариев, чтобы понять, как мы можем создать эффект условного форматирования IF/THEN, даже если мы не можем использовать его в самой функции:

Чтобы следовать нашим примерам, загрузите 04-If-Then Conditional Formatting .xlsx

Сценарий 1 (вкладка «Дни рождения») : Вы хотите выделить всех сотрудников вашего отдела, у которых день рождения в этом месяце, красным цветом, а все остальные отделы — синим.

Решение: Создайте два правила — одно для вашего отдела, одно для всех остальных

Шаг 1. Выделите дни рождения в вашем отделе

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

=МЕСЯЦ(C2)=МЕСЯЦ(СЕГОДНЯ())

Чтобы создать формулу, которая генерирует оператор ИСТИНА/ЛОЖЬ, который выделяет дни рождения только в одном отделе, вы должны использовать формулу:
= И(МЕСЯЦ(C2)=МЕСЯЦ(СЕГОДНЯ()),D2=»Продажи»)

Этот пример был создан в апреле, поэтому дни рождения в апреле будут выделены. Если вы воспроизведете упражнение в другом месяце, вы увидите другие результаты!  

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

=И(МЕСЯЦ(C1)=МЕСЯЦ(СЕГОДНЯ()),D1<>«Продажи»)

БОНУС! В этом примере мы применили правило к ячейке отдела, чтобы показать связь с формулой. Однако, изменив диапазон Применимо к , вы можете легко выделить другую ячейку, например дату рождения, или всю строку. Дополнительные идеи см. в статье . Получите максимальную отдачу от условного форматирования Excel .

Сценарий 2 (вкладка «Наемные работники»): У вас есть таблица количества часов, отработанных вашими сотрудниками для конкретных клиентов, и у вас есть таблица, указывающая, сколько часов каждый клиент имеет в своем бюджете на авансовые платежи. Вы хотите выделить клиентов, у которых закончился гонорар.

Решение 1 : Создайте вспомогательный столбец, используя формулу ЕСЛИ/ТО, чтобы узнать, превышает ли клиент свой бюджет на авансовые платежи. Если на вашем листе уже есть логика IF/THEN/ELSE, встроенная в ячейку, условное форматирование может действовать на основе этих результатов. Вам не обязательно воспроизводить логику в самом правиле.

В этом примере у нас уже есть формула ЕСЛИ/ТО, которая возвращает результат «ДА», если наш клиент превысил свой бюджет на авансовые платежи. Наше правило условного форматирования затем должно только искать текстовую строку «YES» и применять форматирование, когда оно истинно.

Выделите диапазон ячеек, нажмите Условное форматирование > Выделите правила ячеек > Текст, содержащий , чтобы создать правило, затем введите ДА в диалоговом окне Текст, содержащий .

Решение 2 : Создайте формулу для расчета бюджета гонорара.

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

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