Форматирование ячеек в excel по условию

Форматирование ячеек в excel по условию

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

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

Инструмент «Условное форматирование» находится на главной странице в разделе «Стили».

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

Сравним числовые значения в диапазоне Excel с числовой константой. Чаще всего используются правила «больше / меньше / равно / между». Поэтому они вынесены в меню «Правила выделения ячеек».

Введем в диапазон А1:А11 ряд чисел:

Выделим диапазон значений. Открываем меню «Условного форматирования». Выбираем «Правила выделения ячеек». Зададим условие, например, «больше».

Введем в левое поле число 15. В правое – способ выделения значений, соответствующих заданному условию: «больше 15». Сразу виден результат:

Выходим из меню нажатием кнопки ОК.

Условное форматирование по значению другой ячейки

Сравним значения диапазона А1:А11 с числом в ячейке В2. Введем в нее цифру 20.

Выделяем исходный диапазон и открываем окно инструмента «Условное форматирование» (ниже сокращенно упоминается «УФ»). Для данного примера применим условие «меньше» («Правила выделения ячеек» — «Меньше»).

В левое поле вводим ссылку на ячейку В2 (щелкаем мышью по этой ячейке – ее имя появится автоматически). По умолчанию – абсолютную.

Результат форматирования сразу виден на листе Excel.

Значения диапазона А1:А11, которые меньше значения ячейки В2, залиты выбранным фоном.

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

Выделим исходный диапазон (А1:А11). Нажмем «УФ» — «Правила выделения ячеек» — «Равно». В левом поле – ссылка на ячейку В1. Ссылка должна быть СМЕШАННАЯ или ОТНОСИТЕЛЬНАЯ! , а не абсолютная.

Каждое значение в столбце А программа сравнила с соответствующим значением в столбце В. Одинаковые значения выделены цветом.

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

В нашем примере в момент вызова инструмента была активна ячейка А1. Ссылка $B1. Следовательно, Excel сравнивает значение ячейки А1 со значением В1. Если бы мы выделяли столбец не сверху вниз, а снизу вверх, то активной была бы ячейка А11. И программа сравнивала бы В1 с А11.

Чтобы инструмент «Условное форматирование» правильно выполнил задачу, следите за этим моментом.

Проверить правильность заданного условия можно следующим образом:

  1. Выделите первую ячейку диапазона с условным форматированим.
  2. Откройте меню инструмента, нажмите «Управление правилами».

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

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

Исходный диапазон – А1:А11. Необходимо выделить красным числа, которые больше 6. Зеленым – больше 10. Желтым – больше 20.

  • 1 способ. Выделяем диапазон А1:А11. Применяем к нему «Условное форматирование». «Правила выделения ячеек» — «Больше». В левое поле вводим число 6. В правом – «красная заливка». ОК. Снова выделяем диапазон А1:А11. Задаем условие форматирования «больше 10», способ – «заливка зеленым». По такому же принципу «заливаем» желтым числа больше 20.
  • 2 способ. В меню инструмента «Условное форматирование выбираем «Создать правило».

Заполняем параметры форматирования по первому условию:

Нажимаем ОК. Аналогично задаем второе и третье условие форматирования.

Обратите внимание: значения некоторых ячеек соответствуют одновременно двум и более условиям. Приоритет обработки зависит от порядка перечисления правил в «Диспетчере»-«Управление правилами».

То есть к числу 24, которое одновременно больше 6, 10 и 20, применяется условие «=$А1>20» (первое в списке).

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

Выделяем диапазон с датами.

Применим к нему «УФ» — «Дата».

В открывшемся окне появляется перечень доступных условий (правил):

Выбираем нужное (например, за последние 7 дней) и жмем ОК.

Красным цветом выделены ячейки с датами последней недели (дата написания статьи – 02.02.2016).

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

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

Есть столбец с числами. Необходимо выделить цветом ячейки с четными. Используем формулу: =ОСТАТ($А1;2)=0.

Выделяем диапазон с числами – открываем меню «Условного форматирования». Выбираем «Создать правило». Нажимаем «Использовать формулу для определения форматируемых ячеек». Заполняем следующим образом:

Для закрытия окна и отображения результата – ОК.

Условное форматирование строки по значению ячейки

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

Таблица для примера:

Необходимо выделить красным цветом информацию по проекту, который находится еще в работе («Р»). Зеленым – завершен («З»).

Читайте также:  Mutex created launching testdrive2 exe

Выделяем диапазон со значениями таблицы. Нажимаем «УФ» — «Создать правило». Тип правила – формула. Применим функцию ЕСЛИ.

Порядок заполнения условий для форматирования «завершенных проектов»:

Обратите внимание: ссылки на строку – абсолютные, на ячейку – смешанная («закрепили» только столбец).

Аналогично задаем правила форматирования для незавершенных проектов.

В «Диспетчере» условия выглядят так:

Когда заданы параметры форматирования для всего диапазона, условие будет выполняться одновременно с заполнением ячеек. К примеру, «завершим» проект Димитровой за 28.01 – поставим вместо «Р» «З».

«Раскраска» автоматически поменялась. Стандартными средствами Excel к таким результатам пришлось бы долго идти.

В этом уроке мы рассмотрим основы применения условного форматирования в Excel.

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

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

Используя условное форматирование, мы можем:

  • закрашивать значения цветом
  • менять шрифт
  • задавать формат границ

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

Где находится условное форматирование в Эксель?

Кнопка “Условное форматирование” находится на панели инструментов, на вкладке “Главная”:

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

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

  • Каким ячейкам вы хотите задать формат;
  • По каким условиям будет присвоен формат.

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

  • В таблице с данными выделим диапазон, для которого мы хотим применить выделение цветом:

  • Перейдем на вкладку “Главная” на панели инструментов и кликнем на пункт “Условное форматирование”. В выпадающем списке вы увидите несколько типов формата на выбор:
  • Правила выделения
  • Правила отбора первых и последних значений
  • Гистограммы
  • Цветовые шкалы
  • Наборы значков
  • В нашем примере мы хотим выделить цветом данные с отрицательным значением. Для этого выберем тип “Правила выделения ячеек” => “Меньше”:
  • Также, доступны следующие условия:

    1. Значения больше или равны какому-либо значению;
    2. Выделять текст, содержащий определенные буквы или слова;
    3. Выделять цветом дубликаты;
    4. Выделять определенные даты.
    • Во всплывающем окне в поле “Форматировать ячейки которые МЕНЬШЕ” укажем значение “0”, так как нам нужно выделить цветом отрицательные значения. В выпадающем списке справа выберем формат отвечающих условиям:

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

    • Во всплывающем окне формата укажите:
    • цвет заливки
    • цвет шрифта
    • шрифт
    • границы ячеек

    • По завершении настроек нажмите кнопку “ОК”.

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

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

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

    • Выделим диапазон данных. Кликнем на пункт “Условное форматирование” в панели инструментов. В выпадающем списке выберем пункт “Новое правило”:

    • Во всплывающем окне нам нужно выбрать тип применяемого правила. В нашем примере нам подойдет тип “Форматировать только ячейки, которые содержат”. После этого зададим условие выделять данные, значения которых больше “57”, но меньше “59”:

    • Кликнем на кнопку “Формат” и зададим формат, как мы это делали в примере выше. Нажмите кнопку “ОК”:

    Условное форматирование по значению другой ячейки

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

    Для создания условия по значению другой ячейки выполним следующие шаги:

    • Выделим первую ячейку для назначения правила. Кликнем на пункт “Условное форматирование” на панели инструментов. Выберем условие “Меньше”.
    • Во всплывающем окне указываем ссылку на ячейку, с которой будет сравниваться данная ячейка. Выбираем формат. Нажимаем кнопку “ОК”.

    • Повторно выделим левой клавишей мыши ячейку, которой мы присвоили формат. Кликнем на пункт “Условное форматирование”. Выберем в выпадающем меню “Управление правилами” => кликнем на кнопку “Изменить правило”:
    Читайте также:  Анекдот про одно и тоже

    • В поле слева всплывающего окна “очистим” ссылку от знака “$”. Нажимаем кнопку “ОК”, а затем кнопку “Применить”.

    • Теперь нам нужно присвоить настроенный формат на остальные ячейки таблицы. Для этого выделим ячейку с присвоенным форматом, затем в левом верхнем углу панели инструментов нажмем на “валик” и присвоим формат остальным ячейкам:

    На скриншоте ниже цветом выделены данные, в которых курс валюты стал ниже к предыдущему периоду:

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

    Возможно применять несколько правил к одной ячейке.

    Например, в таблице с прогнозом погоды мы хотим закрасить разными цветами показатели температуры. Условия выделения цветом: если температура выше 10 градусов – зеленым цветом, если выше 20 градусов – желтый, если выше 30 градусов – красным.

    Для применения нескольких условий к одной ячейке выполним следующие действия:

    • Выделим диапазон с данными, к которым мы хотим применить условное форматирование => кликнем по пункту “Условное форматирование” на панели инструментов => выберем условие выделения “Больше…” и укажем первое условие (если больше 10, то зеленая заливка). Такие же действия повторим для каждого из условий (больше 20 и больше 30). Не смотря на то, что мы применили три правила, данные в таблице закрашены зеленым цветом:

    • Кликнем на любую ячейку с присвоенным форматированием. Затем, снова кликнем по пункту “Условное форматирование” и перейдем в раздел “Управление правилами”. Во всплывающем окне, распределим правила от большего к меньшему и напротив первых двух поставим галочку “Остановить, если истина”. Этот пункт позволяет не применять остальные правила к ячейке, при соответствии первому. Затем кликнем кнопку “Применить” и “ОК”:

    Применив их, наша таблица с данными температуры “подсвечена” корректными цветами, в соответствии с нашими условиями.

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

    Для редактирования присвоенного правила выполните следующие шаги:

    • Выделить левой клавишей мыши ячейку, правило которой вы хотите отредактировать.
    • Перейдите в пункт меню панели инструментов “Условное форматирование”. Затем, в пункт “Управление правилами”. Щелкните левой клавишей мыши по правилу, которое вы хотите отредактировать. Кликните на кнопку “Изменить правило”:

    • После внесения изменений нажмите кнопку “ОК”.

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

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

    • Выделим диапазон данных с примененным условным форматированием. Кликнем по пункту на панели инструментов “Формат по образцу”.
    • Левой клавишей мыши выделим диапазон, к которому хотим применить скопированные правила формата:

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

    Для удаления формата проделайте следующие действия:

    • Выделите ячейки;
    • Нажмите на пункт меню “Условное форматирование” на панели инструментов. Кликните по пункту “Удалить правила”. В раскрывающемся меню выберите метод удаления:

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

    • Excel 2003 : Формат (Format)Условное форматирование (Conditional formatting)формула;
    • Excel 2007-2010 : вкладка Главная (Home)Условное форматирование (Conditional formatting)Создать правило (New rule)Использовать формулу для определения форматируемых ячеек (Use a formula to determine which cells to format)

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

    Все условия приведены для диапазона A1:A20 . Это означает, что для корректного выполнения условия необходимо выделить диапазон A1:A20 (столбцов может быть больше), начиная с ячейки A1 , после чего назначить условие.
    Если выделять необходимо не с первой строки, а скажем, с 4-ой, то и выделить надо будет диапазон A4:A20 и в формуле для условия указывать в качестве критерия первую ячейку выделенного диапазона — A4 .

    Если необходимо выделять форматированием не только конкретную ячейку, удовлетворяющую условию, а всю строку таблицы на основе ячейки одного столбца, то перед установкой правила необходимо выделить всю таблицу, строки которой необходимо форматировать, а ссылку на столбец с критерием закрепить:
    = $A1 =МАКС( $A$1:$A$20 )
    при выделенном диапазоне A1:F20 (диапазон применения условного форматирования), будет выделена строка A7:F7 , если в ячейке A7 будет максимальное число.

    Так же можно применять не к конкретно одному столбцу, а к полностью диапазону. Но в этом случае надо знать принцип смещения ссылок в формулах, чтобы условия применялись именно к нужным ячейкам. Например, если задать условие для диапазона B1:D10 в виде формулы: = B1 A1 , то цветом будут выделены ячейки столбца B, если значение ячейки столбца А в той же строке меньше( B1, B3). При этом если ячейки столбца D меньше ячеек столбца C в той же строке — они тоже будут выделены( D1 , D5 ).

    Читайте также:  Как изменить код пароль на айфоне
  • Выделение ячеек с числами:
    =ЕЧИСЛО( A1 )
  • Выделение ячеек с числами, но не учитывая нули:
    =И(ЕЧИСЛО( A1 ); A1 <>0)
  • Выделение строк со значением больше 0:
    = A1 >0
  • Выделение строк со значением в диапазоне от 3 до 10:
    =И( A1 >=3; A1
  • Выделение в диапазоне $A$1:$A$20 ячейки с максимальным значением:
    = A1 =МАКС( $A$1:$A$20 )
  • Выделение в диапазоне $A$1:$A$20 ячейки с минимальным значением:
    =И(ЕЧИСЛО( A1 ); A1 =МИН( $A$1:$A$20 ))
  • Выделение в диапазоне $A$1:$A$20 ячейки со вторым по величине числом. Т.е. из чисел 1,2,3,4,5,6,7 будет выделено число 6:
    = A1 =НАИБОЛЬШИЙ( $A$1:$A$20 ;2)
  • Выделение ячеек с любым текстом:
    =ЕТЕКСТ( A1 )
  • Выделение ячеек с текстом Итог:
    = A1 ="Итог"
  • Выделение ячеек, содержащих текст Итог:
    =СЧЁТЕСЛИ( A1 ;"*итог*")
    =НЕ(ЕОШ(ПОИСК("итог"; A1 )))
  • Выделение ячеек, не содержащих текст Итог:
    =СЧЁТЕСЛИ( A1 ;"*итог*")=0
    =ЕОШ(ПОИСК("итог"; A1 ))
  • Выделение ячеек, текст которых начинается со слова Итог:
    =ЛЕВСИМВ( A1 ;4)="Итог"
  • Выделение ячеек, текст которых заканчивается на слово Итог:
    =ПРАВСИМВ( A1 ;4)="Итог"
  • Выделение текущей даты:
    = A1 =СЕГОДНЯ()
  • Выделение ячейки с датой, больше текущей:
    = A1 >СЕГОДНЯ()
  • Выделение ячейки с датой, которая наступит через неделю:
    = A1 =СЕГОДНЯ()+7
  • Выделение ячеек с датами текущего месяца(любого года):
    =МЕСЯЦ( A1 )=МЕСЯЦ(СЕГОДНЯ())
  • Выделение ячеек с датами текущего месяца текущего года:
    =И(МЕСЯЦ( A1 )=МЕСЯЦ(СЕГОДНЯ());ГОД( A1 )=ГОД(СЕГОДНЯ()))
    или
    =ТЕКСТ( A1 ;"ГГГГММ")=ТЕКСТ(СЕГОДНЯ();"ГГГГММ")
  • Выделение ячеек с выходными днями:
    =ДЕНЬНЕД( A1 ;2)>5
  • Выделение ячеек с будними днями:
    =ДЕНЬНЕД( A1 ;2)
  • Выделение ячеек, входящих в указанный период(промежуток) дат:
    =И( $A1 >ДАТА(2015;9;1); $A1
  • Выделение различий в ячейках по условию:
    = A1 <> $B1
  • Выделение ячейки, если ячейка следующего столбца(B) этой же строки меньше:
    = A1 > B1
  • Выделение строк цветом через одну:
    =ОСТАТ(СТРОКА();2)
  • Выделение строк цветом, если значение ячейки столбца A присутствует в диапазоне $F$1:$H$5000 :
    =СЧЁТЕСЛИ( $F$1:$H$5000 ; A1 )
  • Выделение строк цветом, если значение ячейки столбца A отсутствует в диапазоне $F$1:$H$5000 :
    =СЧЁТЕСЛИ( $F$1:$H$5000 ; A1 )=0
  • Выделение цветом ячейки, если её значение в диапазоне A1:A20 второе по счету:
    =СЧЁТЕСЛИ( $A$1:$A1 ; A1 )=2
  • Выделение ячеек, содержащих ошибки (#ЗНАЧ!; #Н/Д; #ССЫЛКА! и т.п.). Помимо просто выявления ячеек с ошибками можно применять, когда необходимо скрыть ошибочные значения в ячейках(назначив цвет шрифта таким же, как и цвет заливки):
    =ЕОШИБКА( A )
  • Выделение непустых ячеек в столбце A :
    = $A1 <>""
  • Статья помогла? Поделись ссылкой с друзьями!

    Поиск по меткам

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

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

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

    А если нужно исключить нули в этом диапазоне?

    Значит надо подучить формулы массива 🙂
    =МИН(ЕСЛИ(A1:A10<>0;A1:A10))

    Добрый день!
    Пробую выделить УФ в сводной таблице строку, если итог по строке больше из связанной сводной таблицы ниже 4х.
    Выделяю ячейку в сводной, пишу формулу УФ
    =SUM(B2405:BM2405)>=5
    копирую форматирование на всю сводную. Работает.

    Проблема: при использовании срезов (или фильтров), УФ пропадает.
    Я что-то не так делаю, или такой функционал не доступен?

    Спасибо заранее за помощь!

    Добрый день. Подскажите, а если нужно сделать условное форматирование максимального и минимального значения для такого случая:
    A B C D E
    1 559980 — 606000 — 824000
    2 559980 — — — —
    Если в первой строке понятно, что Максим это Е1, а миним А1, то как быть со второй строкой. Можно ли сделать такое условие, если в строке, например более 4 штук "-" форматирование не проставлялось? Спасибо

    Добрый день.
    В ячейке А1 стоит условие УФ- окрашивать ячейку А1, если в этой ячейке стоит число, большее, чем в ячейке В1. Но, нужно, чтобы не окрашивалась ячейка А1, если в ячейке В1 будет ноль или пустая ячейка. Подскажите, как решить такую проблему? Можно ли так сделать без макросов?
    Спасибо.

    Ссылка на основную публикацию
    Adblock detector