Как задать правило в excel

Содержание страницы:

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

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

Располагается эта полезная возможность на вкладке «Главная» в области «Стили» под одноименной пиктограммой:

Создать правило

Для создания правила условного форматирования в Excel кликните по соответствующей кнопке на ленте, раскрыв следующее меню:

Выбрав пункт «Создать правило…», приложение отобразит окно:

В нем Вы можете выбрать тип правила и настроить его описание (подробнее читайте далее в статье).

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

Форматировать все ячейки на основании их значений

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

Гистограмма

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

Ширина ячейки принимается за 100%, что соответствует максимальному значению диапазона правила. Т.е. ячейка, содержащая максимальное значение будет залита полностью, а ячейка со значением в 2 раза меньшим максимальному – наполовину. В случае отрицательного значения, столбец будет окрашен другим цветом и иметь другую направленность (это можно изменить).

  • Показывать только столбец – установив флажок на данном поле, Вы сообщаете, что для диапазона ячеек правила необходимо скрывать содержимое и оставлять только формат;
  • Параметры значений – здесь устанавливаются максимальные и минимальные значения и их типы. В качестве типа может выступать число, процент, формула, процентиль либо по умолчанию (авто). Значение может быть только числовым. Все числа, меньше минимального (включая отрицательные), приравниваются к нулю, т.е. не содержат столбца. А те, которые больше максимального, приравниваются к 100% и закрашиваются полностью.
  • Внешний вид столбца – устанавливает способ заливки (сплошной или градиентный), границу и их цвета;
  • Направление столбца – определяет способ направленности (слева направо либо наоборот);
  • Кнопка «Отрицательные значения и ось…» – настройки отображения столбцов для отрицательных чисел. Что они позволяют:
    • Установить свой цвет заливки столбца и его границу или сделать их одинаковыми для всех значений (положительных и отрицательных. По умолчанию они различаются);
    • Задать положение оси или одинаковую направленность для всех значений.

Цветовые шкалы

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

В качестве примера, рассмотрим настройку трехцветной шкалы, хотя она мало чем отличается от настройки двухцветной.

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

  • Минимальным числом задан ноль, а значения меньше его, будут иметь такие же цвет и насыщенность;
  • Средним значением указана единица и желтый цвет. Это значит, что переход шкалы от красного к желтому будет осуществлен между 0 и 1;
  • 4 является максимальным значением. Все, что превышает его, получает те же установки. Переход от желтого к зеленому происходит между 1 и 4.

Наборы значков (флажков)

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

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

Форматировать только ячейки, которые содержат

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

Рассмотрим правила, которые имеются в этом пункте:

  • Значение ячейки. Предполагает работу с числами и текстом. Сравнение производится по шкале сортировки.
  • Текст. Позволяет проверить наличие или отсутствие подстроки в тексте.
  • Даты. С его помощью легко создать правила типа «вчера», «сегодня», «завтра», «на прошлой неделе», «в следующем месяце» и т.п.
  • Пустые. Форматирует пустые ячейки. Пробелы не учитываются.
  • Непустые. Противоположное предыдущему правилу.
  • Ошибки. Истинно, когда значением ячейки является ошибка.
  • Без ошибки. Противоположное предыдущему правилу.

Форматировать только первые и последние значения

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

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

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

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

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

Используем 2 условия со следующими формулами:

  • Если на складе нет товара, т.е. равен 0, то подсвечиваем позицию заказа красным – =ВПР(D3;A:B;2;ЛОЖЬ)=0;
  • Если на складе есть товар, но его количество меньше, чем указано в позиции заказа, то последнюю подсвечиваем желтым – =И(ВПР(D3;$A:$B;2;ЛОЖЬ) 0).

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

В функции, в качестве первого аргумента используется ссылка всего на одну ячейку. Вас это не должно смущать, так как приложение «понимает», что ее нужно сместить в соответствии с диапазоном правила. Главное, чтобы она была относительной, т.е. не закреплена символами доллара – $.

Остальные правила

Ничего не было сказано о еще двух видах правил, а именно:

  • Форматирование на основе среднего значения – полное название «Форматировать только значения, которые находятся выше или ниже среднего»;
  • Форматирование уникальных или повторяющихся значений.

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

Управление правилами

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

Диспетчер правил условного форматирования отображает список, состоящий из условия, формата и диапазона, к которому применено правило.

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

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

На изображение приведено 2 правила: значение равно трем и значение больше двух. Представьте, что они применены к ячейке со числом 3. Какое из них сработает? В этом случае оба, так как между ними нет конфликта в форматировании, одно отвечает за заливку, а второе за границу. Но если бы они оба отвечали за один и тот же стиль, то выполнилось правило, которое стоит выше, потому что имеет больший приоритет.

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

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

Если материалы office-menu.ru Вам помогли, то поддержите, пожалуйста, проект, чтобы мы могли развивать его дальше.

У Вас недостаточно прав для комментирования.

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

Условное форматирование – это инструмент в Excel, который служит для присвоения особенного формата ячейкам или целым диапазонам ячеек на основе критериев условий определенных пользователем. Вы познакомитесь с примерами использования условий основанных на сложных формулах. А так же научитесь управлять такими функциями как:

  • поля данных;
  • цветовая палитра;
  • настройка шрифтов.

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

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

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

Допустим столбец, содержит диапазон ячеек с числовыми значениями. Если определить их соответствующим условием форматирования, то все значения с числом более чем 100 будут отображаться красным цветом. Чтобы реализовать эту задачу данный инструмент Excel проанализирует в соответствии с условиями критериев значение каждой ячейки заданного диапазона. Результаты анализа дают положительный результат, например (A2>100=ИСТИНА), тогда будет присвоен предварительно заданный новый формат (красный цвет). В противоположном результате (A2>100=ЛОЖЬ), формат ячеек не изменяется.

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

Как создать правило условного форматирования в Excel

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

Допустим в ячейке A1 находиться числовое значение 50:

Определим следующие условия для формата отображения значений в A1:

  1. Если число больше чем 15, то шрифт будет отображаться зеленым цветом.
  2. Если число больше чем 30, то шрифт будет отображаться желтым цветом.
  3. Если число больше чем 40, то шрифт будет отображаться красным.

Определенно вы заметили, что значение 50 в ячейке A1 соответствует всем условиям (A1>15, A1>30 и A1>40 = ИСТИНА). Каким же цветом шрифта Excel отобразит числовое значение 50?

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

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

Создать второе правило

Второй пример. Допустим нам нужно отформатировать расходы в колонке C следующим образом:

Все суммы в пределах 300$-600$ должны закрасить свои ячейки желтым фоном, а суммы меньше чем 500$ должны еще иметь и красный цвет шрифта.

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

  1. Выделите диапазон ячеек C4:C7 и выберите инструмент: «Главная»-«Условное форматирование»-«Управление правилами».
  2. В появившемся окне нажмите на кнопку «Создать правило».
  3. В окне «Создание правила форматирования», выберите тип правила «Форматировать только ячейки, которые содержат».
  4. В первом выпадающем списке укажите «Значение ячейки», во втором «между». А в остальных двух введите границы придела 300 и 600.
  5. Нажмите на кнопку «Формат» и на вкладке «Заливка» укажите желтый цвет.
  6. Нажмите «ОК» для возврата к окну «Диспетчер правил условного форматирования».
  7. Снова нажмите на кнопку «Создать правило» и снова же выберите тип правила «Форматировать только ячейки, которые содержат».
  8. Первый выпадающий список оставляем без изменений «Значение ячейки», а во втором выбираем опцию «меньше». В поле ввода указываем значение 500.
  9. Нажмите на кнопку «Формат» и на вкладке «Шрифт» укажите красный цвет.
  10. На всех окнах нажмите «ОК».

Обратите внимание, в какой способ Excel применил форматирование. Суммы в ячейках C10, С13 и С15 соответствуют обоим условиям. Поэтому к ним применены оба стиля форматирования. А там где значение соответствует только одному из условий, они отображаются соответственным форматам.

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

Пользоваться условным форматированием очень просто! Вкратце можно описать этапами:

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

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

Как применить условное форматирование к столбцу

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

В первую очередь отобразим цветом тех, кто выполнил месячный план > 25000. Для этого:

  1. Выделим диапазон ячеек который содержит суммы продаж (C2:C10).
  2. Выберите инструмент: «Главная»-«Условное форматирование»-«Правила выделения ячеек»-«Больше».
  3. В появившемся окне введите в поле значение 25000 и выберите из выпадающего списка «Зеленая заливка и темно-зеленый текст».
  4. Нажмите ОК.

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

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

При желании можно настроить свой формат отображения выделенных цветом значений. Для этого:

  1. Перейдите курсором на любую ячейку диапазона с условным форматированием.
  2. Выберите инструмент: «Главная»-«Условное форматирование»-«Управление правилами».
  3. В появившемся окне нажмите на кнопку «Изменить правило».
  4. В следующем окне нажмите на кнопку «Формат».
  5. Теперь перейдите на закладку «Шрифт» и выберите в опции «Цвет:» синий для шрифта вместо зеленого.
  6. На всех трех окнах нажмите «ОК».

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

Условное форматирование по нескольким условиям

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

  1. Снова выделите диапазон ячеек C2:C10 (несмотря на то, что он уже отформатирован в соответствии с условиями).
  2. Выберите инструмент: «Главная»-«Условное форматирование»-«Правила выделения ячеек»-«Меньше».
  3. В появившемся окне введите в поле значение 21000 и выберите из выпадающего списка выберите «Светло-красная заливка и темно-красный текст».
  4. Нажмите ОК.

Теперь у нас таблица экспонирует два типа показателей благодаря использованию двух условий. Чтобы посмотреть и при необходимости изменить эти условия, снова выберите инструмент: «Главная»-«Условное форматирование»-«Управление правилами».

Несколько правил условного форматирования в одном столбце

Еще раз усложним задачу, добавив третье условие для экспонирования важных данных в таблице. Теперь выделим жирным шрифтом те суммы, которые находятся в пределах от 21800 и до 22000.

  1. Снова выделите диапазон ячеек C2:C10.
  2. Выберите инструмент: «Главная»-«Условное форматирование»-«Правила выделения ячеек»-«Между».
  3. В появившемся окне введите в первое поле значение 21800, во второе поле 22000. В выпадающем списке укажите на опцию «Пользовательский формат».
  4. В окне «Формат ячеек» перейдите на закладку «Шрифт» и в разделе «Начертание» укажите «полужирный». После чего нажмите ОК на всех окнах.

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

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

Ссылки в условиях условного форматирования

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

  1. Введите в ячейку D1 число 25000, а в D2 введите 21000. В ячейки D3 и E3 введите 21800 и 22000 соответственно.
  2. Выделите диапазон ячеек C2:C10 и снова выберите опцию: «Главная»-«Условное форматирование»-«Управление правилами».
  3. В окне «Диспетчер правил условного форматирования» откройте каждое из правил и поменяйте значения их критериев на соответствующие ссылки.

Внимание! Все ссылки должны быть абсолютными. Например, критерий со значением 25000 должен теперь содержать ссылку на =$D$1.

Примечание. Необязательно вручную вводить каждую ссылку с клавиатуры. Щелкните левой кнопкой мышки по соответствующему полю (чтобы придать ему фокус курсора клавиатуры), а потом щелкните по соответствующей ячейке. Excel вставит адрес ссылки автоматически.

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

Применение условного форматирования с помощью формулы

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

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

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

С помощью условного форматирования, которое определяется двумя правилами с формулой, на этом листе отображаются необходимые вам сведения. Правило в столбце A форматирует предстоящие дни рождения, а правило в столбце C форматирует ячейки после ввода символа «Y», обозначающего отправленную поздравительную открытку.

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

Выделите ячейки от A2 до A7 (для этого щелкните и перетащите указатель мыши с ячейки A2 в ячейку A7).

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

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

В разделе Форматировать значения, для которых следующая формула является истинной: введите формулу: =A2>СЕГОДНЯ().

Функция СЕГОДНЯ используется в формуле для определения значения дат в столбце A, превышающих значение сегодняшней даты (будущих дат). Ячейки, удовлетворяющие этому условию, форматируются.

Нажмите кнопку Формат.

В раскрывающемся списке Цвет выберите Красный. В списке раздела Начертание выберите пункт Полужирный.

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

Теперь форматирование применено к столбцу A.

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

Выделите ячейки от C2 до C7.

Повторите действия 2–4, указанные выше, и введите следующую формулу: =C2=»Y».

Формула определяет ячейки в столбце C, содержащие символ «Y» (прямые кавычки вокруг символа «Y» указывают, что это текст). Ячейки, удовлетворяющие этому условию, форматируются.

В раскрывающемся списке Цвет выберите Белый. В списке раздела Начертание выберите пункт Полужирный.

Перейдите на вкладку Заливка и выберите Зеленый.

Теперь форматирование применено к столбцу C.

Попробуйте попрактиковаться

Вы можете скопировать приведенную ниже таблицу на лист в Excel — обязательно вставьте ее в ячейку A1. Затем выделите ячейки D2: D11 и создайте правило условного форматирования, в котором используется приведенная ниже формула.

При создании правила убедитесь в том, что оно применяется к ячейкам D2:D11. Задайте цветовое форматирование, которое должно применяться к ячейкам, удовлетворяющим условию (т. е. если название города встречается в столбце D более одного раза, а это — Москва и Мурманск).

Блог о программе Microsoft Excel: приемы, хитрости, секреты, трюки

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

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

Условное форматирование – это простой способ определить ячейки с ошибочными записями или значениями определённого типа. Вы можете использовать формат (например, красная заливка), чтобы легко идентифицировать определенные ячейки.

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

Когда вы нажимаете на кнопку Условное форматирование, которая находится в группе Стили вкладки Главная, вы увидите выпадающее меню со следующими опциями:

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

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

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

Цветовые шкалы позволяет задавать двух- и трехцветовые шкалы для цвета фона ячейки на основе ее значения относительно других ячеек в диапазоне

Наборы значков отображает значок в ячейке. Какой именно значок отображается, зависит от значения ячейки относительно других ячеек. Excel 2013 предоставляет 20 наборов значков на выбор (при этом вы можете смешивать и сочетать значки из разных наборов). Количество значков в наборах колеблется от трех до пяти.

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

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

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

Графическое условное форматирование

Вероятно, самое крутое (и конечно, простое) условное форматирование, которое можно применить к диапазону ячеек – это форматирование с применением графических элементов – Гистограммы, Цветовые шкалы и Наборы значков.

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

Определение конкретных значений в диапазоне ячеек

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

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

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

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

Выделяем диапазон ячеек, к которому мы хотим применить условное форматирование. Переходим по вкладке Главная в группу Стили, щелкаем кнопку Условное форматирование -> Правила выделения ячеек -> Текст содержит.

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

Щелкаем ОК, чтобы наше правило вступило в силу.

Несколько условных форматирований для одного диапазона

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

Выделяем диапазон ячеек, к которому мы хотим применить три различных правила условного форматирования. Начнем с создания правила для ячеек, содержащих значение равное 95. Переходим по вкладке Главная в группу Стили, щелкаем кнопку Условное форматирование -> Правила выделения ячеек -> Равно. Excel откроет диалоговое окно Равно, где в левом текстовом поле необходимо указать условие 95, а в правом выпадающем списке выбрать формат для этого условия Желая заливка с темно-желтым текстом.

Далее задаем условное форматирование для значений больше 95. Из меню Условное форматирование -> Правила выделения ячеек выбираем Больше, в появившемся диалоговом окне Больше указываем значение, выше которого ячейка будет закрашиваться в зеленый цвет, и сам формат.

Аналогичную операцию проделываем для ячеек со значениями меньше 95. На этот раз из списка правил необходимо выбрать Меньше и задать формат с красной заливкой.

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

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

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

Выделяем таблицу с данными, к которой мы хотим применить условное форматирование. Переходим по вкладке Главная в группу Стили, щелкаем кнопку Условное форматирование -> Создать правило. В появившемся диалоговом окне Создание правила форматирования в поле Выберите тип правила выбираем Использовать формулу для определения форматируемых ячеек.

В поле Измените описание правила задаем условия и формат для нашего правила. В нашем случае, условием будет формула =ИЛИ(ДЕНЬНЕД($A2;2)=6;ДЕНЬНЕД($A2;2)=7). В качестве формата я выбрал темно красную заливку.

Послесловие

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

Вам также могут быть интересны следующие статьи

31 комментарий

А можно ли сделать так чтобы значение в ячейке менялось в зависимости от цвета другой ячейки? Например если ячейка залита красным цветом то 0, если зеленым цветом то 1.

Эльнур, такую штуку можно реализовать с помощью создания пользовательской функции, например, такой:

Еще по теме:

  • Образец заявление на установление отцовства после смерти отца Заявление об установлении факта признания отцовства: как доказать признание отцом ребенка в судебном порядке Уважаемые посетители!Статьи размещенные на нашем сайте носят информационный характер о решении тех или иных юридических вопросов.Вместе с тем каждая ситуация индивидуальна.Для […]
  • Уплата налогов ип реквизиты Какие реквизиты для уплаты УСН нужны и где их найти Предприниматели и организации, применяющие в качестве системы налогообложения «упрощёнку», в течение года несколько раз перечисляют платежи по УСН. В соответствии с принятыми отчётными периодами (квартал, полугодие и 9 месяцев) по […]
  • Нужно ли писать заявление на продление отпуска в связи с больничным Продление отпуска в связи с больничным: заявление не нужно Отпуск может быть продлен или перенесен в случае болезни Болезнь работника, приключившаяся во время ежегодного отпуска, служит основанием для его продления или переноса. Такая норма предусмотрена трудовым законодательством (ст. […]
  • Суммы транспортного налога в налогообложении Транспортный налог Налоги и налоговая система Налоговый кодекс Российской Федерации Транспортный налог До 2003 г. налогообложение транспортных средств базировалось на двух налогах — налоге с владельцев транспортных средств и налоге на имущество физических лиц в отношении водных […]
  • Об основах социального обслуживания в рф федеральный закон Федеральный закон «Об основах социального обслуживания граждан в Российской Федерации» от 28.12.2013 N 442-ФЗ ст 15 (ред. от 07.03.2018) Статья 15. Признание гражданина нуждающимся в социальном обслуживании 1. Гражданин признается нуждающимся в социальном обслуживании в случае, если […]
  • Регрессивный подоходный налог это «Грозит» ли россиянам прогрессивное налогообложение? В 2015 году и последние 14 лет в России действует единая система взимания налогов. Применяется ставка 13%, причем она равна для граждан с любым уровнем дохода. Большинство же стран сегодня использует прогрессивное (иногда даже […]
  • Можно-ли ездить без страховки с дкп Езда без страховки по ДКП. Преимущества и недостатки, срок разрешенной езды и особенности езды без страховки. Автолюбителям хорошо известен тот факт, что при покупке транспортного средства новый владелец определённое количество времени имеет право эксплуатировать его в дорожных условиях […]
  • Разрешение телевизоров виды Разрешение телевизора Разрешение экрана является очень важной характеристикой телевизора. Производители вкладывают большие деньги в разработку экранов с высоким разрешением. Такие разработки дорого стоят и не все производители телевизоров являются и производителями жк матриц. Многие из […]