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

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

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

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

Хорошо, что в Excel есть формулы в условном форматировании. Все формулы должны быть логичными; результат будет ИСТИНА или ЛОЖЬ. Если логический тест пройден, мы получим условное форматирование, а если логический тест в Excel не пройден, мы ничего не получим.

Условное форматирование Excel с помощью формул

Обзор

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

Формулы условного форматирования

В следующем разделе статьи мы увидим множество примеров применения условного форматирования с формулами.

Как использовать условное форматирование Excel с формулами?

Вы можете скачать этот шаблон условного форматирования Excel с шаблоном формул здесь — Условное форматирование Excel с шаблоном формул

# 1 — Выделите ячейки со значениями меньше 500

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

Формулы условного форматирования, пример 1

  • Шаг 1: Перейдите в раздел «Условное форматирование» и нажмите «Новое правило».

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

  • Шаг 2: Теперь выберите «Используйте формулу, чтобы определить, какие ячейки нужно форматировать ».

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

  • Шаг 3: Теперь в разделе «Форматировать значения, в которых формула верна, » примените формулу как A2 <500, а затем нажмите Формат для применения форматирования Excel.

Формулы условного форматирования, пример 1-3

  • Шаг 4: Выберите формат по своему желанию.

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

  • Шаг 5: Теперь вы можете увидеть предварительный просмотр формата в окне предварительного просмотра.

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

  • Шаг 6: Теперь нажмите ОК, чтобы завершить форматирование. Теперь он выделил все ячейки с числом <500.

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

# 2 — Выделите одну ячейку на основе другой ячейки

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

Формулы условного форматирования, пример 2

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

  • Шаг 1: Выберите ассортимент продукции, затем перейдите к условному форматированию и нажмите «Новое правило».

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

  • Шаг 2: В формуле примените формулу B2> 220.

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

  • Шаг 3: Нажмите на кнопку «Формат» и примените формат по своему желанию.

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

  • Шаг 5: Щелкните ОК; у нас готово форматирование.

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

# 3 — Выделите все пустые ячейки в диапазоне

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

Формулы условного форматирования, пример 3

В приведенных выше данных вы должны выделить все пустые или пустые ячейки. Нам нужно использовать формулу ISBLANK в условном форматировании Excel.

Примените приведенную ниже формулу в разделе формул.

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

Выберите форматирование по своему желанию.

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

Нажмите ОК, это выделит все пустые ячейки в выбранном диапазоне.

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

# 4 — Используйте функцию И для выделения ячеек

Рассмотрим приведенные ниже данные для этой формулы.

Формулы условного форматирования, пример 4

Если регион Центральная и значение продаж> 500, нам нужно выделить всю строку.

  • Шаг 1: Сначала выберите все данные.

Пример 4-1

  • Шаг 2: Здесь нам нужно использовать функцию AND excel, потому что здесь нам нужно проверить два условия, и оба условия должны быть ИСТИНА. Примените формулу ниже.

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

  • Шаг 3: Щелкните Форматировать.

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

  • Шаг 4: Перейдите в Заливку и выберите нужный цвет и эффекты.

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

  • Шаг 5: Щелкните, ОК. Мы увидим выделенные строки, если оба условия ИСТИННЫ.

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

# 5- Используйте функцию ИЛИ для выделения ячеек

В качестве примера рассмотрим данные ниже.

Формулы условного форматирования, пример 5

В приведенном выше диапазоне данных я хочу выделить названия городов Бангалор и Майсур.

  • Шаг 1: Сначала выберите данные и перейдите в раздел «Условное форматирование», затем нажмите «Новое правило».

Пример 5-1

  • Шаг 2: Поскольку нам нужно выделить одну из двух ячеек значений, примените функцию OR excel.

Пример 5-2

  • Шаг 3: Нажмите на формат и выберите нужный формат.

Пример 5-3

  • Шаг 4: После применения форматирования нажмите ОК, чтобы завершить задачу. Формула выделит все ячейки со значениями Bangalore & Mysore.

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

# 6 — Используйте функцию COUNTIF для выделения ячеек

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

Формулы Пример 6

Выберите данные и примените формулу СЧЁТЕСЛИ в разделе формул.

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

Щелкните формат и примените необходимое форматирование.

Формулы Пример 6-2

Теперь формула будет выделять все имена, если число больше 5.

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

Янтарь — единственное имя, которое встречается в списке более пяти раз.

# 7 — Выделите каждую альтернативную строку

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

Формулы условного форматирования, пример 7

Выберите данные и примените приведенную ниже формулу.

= МОД (СТРОКА (); 2)

Формулы Пример 7-1

Примените форматирование по своему желанию и нажмите ОК; каждая альтернативная строка будет выделена формулой.

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

# 8 — Выделите каждую альтернативную колонку

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

Формулы Пример 8

Примените формулу ниже.

= МОД (КОЛОНКА (); 2)

Скопируйте приведенную выше формулу и примените ее в разделе формул условного форматирования.

Формулы Пример 8-1

Щелкните ОК; он выделит альтернативную строку.

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

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

Формулы Пример 8-3

Он выделит альтернативный столбец, начиная со второго столбца.

Формулы Пример 8-4

То, что нужно запомнить

  • При условном форматировании принимаются только логические формулы, результаты которых имеют значение ИСТИНА или ЛОЖЬ.
  • Предварительный просмотр условного форматирования — это всего лишь показатель того, как выглядит форматирование.
  • Никогда не используйте абсолютную ссылку в формуле. Когда вы применяете формулу и выбираете ячейку напрямую, она становится абсолютной ссылкой, вам нужно сделать ее относительной ссылкой в ​​excel.

УЗНАТЬ БОЛЬШЕ >>

Похожие записи

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *