Как сделать условное форматирование в Excel
Условное форматирование – это очень полезная функция в Excel, которая позволяет отформатировать числовые данные или текст в таблице, в соответствии заданным условиям или правилам. Благодаря ему, взглянув на нужные ячейки, Вы сразу сможете оценить значения, так как все данные будут представлены в удобном наглядном виде.
Кнопка «Условное форматирование» находится на вкладке «Главная» в группе «Стили» .
Кликнув по ней, откроется меню с видами условного форматирования. Давайте разберемся с ними более подробно.
Выделение ячеек
В данном случае мы можем сравнить числовые данные выделенного диапазона ячеек с определенным заданным числом, или с другим диапазоном. Сравнивать можно не только числа, но и текст, и даты.
Пример
Сравним все числа в выбранном диапазоне и если есть повторы, закрасим блоки с ними в определенный цвет. Нажимаем «Условное форматирование» – «Правила выделения ячеек» – «Повторяющиеся значения» . В списке выбираем «повторяющиеся» и тип заливки. Теперь все повторы в столбце выделены цветом. Как видите, в примере несколько раз встречаются шестерки и восьмерки.
Теперь давайте сравним данные в первом диапазоне со вторым, и если число в первом будет меньше, выделим прямоугольничек цветом. Выбираем из списка «Меньше» . Дальше делаем относительную ссылку на второй столбец: кликаем мышкой по первому числу. Доллар перед F значит, что сравнивать будем именно с этим столбцом, но в разных ячейках. В результате, все блоки в первом столбце, где числа записаны меньше, чем во втором, выделены цветом.
Еще можно применить форматирование к текстовым данным. Для этого выберите из списка «Текст содержит» . Например, выделим все имена, которые начинаются с «В» – то есть сам блок и текст будут окрашены в выбранный цвет.
Отбор первых и последних значений
Используя данный пункт можно выделить ячейки, которые относятся к первым или последним элементам, в соответствии заданному числу или проценту.
Пример
Зададим форматирование для тех блоков, значение в которых выше среднего в выделенной области. Выбираем из списка «Выше среднего» . Соответствующие прямоугольнички и текст в них будут выделены.
Гистограммы
Они показывают информацию в блоке в виде гистограммы. Ячейка принимается за 100%, которому соответствует максимальное число в выбранном диапазоне. Если значение в блоке будет отрицательное – гистограмма делится на половину, имеет другую направленность и цвет.
Пример
Отобразим число для каждого выделенного блока в виде гистограммы. Выбираем любой из предложенных способов заливки.
Теперь давайте представим, что минимальное число для построения гистограммы должно быть 5. Выделяем нужный диапазон, кликаем по кнопочке «Условное форматирование» и выбираем из списка «Управление правилами» .
Откроется следующее окно. В нем можно создать новое правило для выделенных ячеек, изменить или удалить нужное из списка. Выбираем «Изменить правило» .
Внизу окна «Изменение правила форматирования» можно изменить описание для него. Ставим «Минимальное значение» – «Число» , и в поле «Значение» пишем «5» . Если Вы не хотите, чтобы в ячейках отображались числа, поставьте галочку в пункте «Показывать только столбец» . Здесь же можно изменить цвет и тип заливки.
В результате минимальное число для выделенных ячеек «5» , а максимальное выбирается автоматически. Как видно в примере, в блоках, где число меньше пяти: 4, -7, -8, или равно ему гистограмма просто не отображается.
Цветовые шкалы
Теперь разберем четвертый пункт. В данном случае, ячейка заливается цветом, который зависит от числа, которое в ней записано.
Если открыть окно «Изменение правила форматирования» , как описано в предыдущем пункте, можно выбрать «Стиль формата» , «Цвет» заливки, максимальное и минимальное значение для выбранного диапазона.
Например, в поле «Минимальное значение» я поставила «3» . Выбранная область будет выглядеть следующим образом – блоки, значения в которых ниже 4-ох будут просто не закрашены.
Наборы значков
В ячейку, в соответствии с ее значением, будет вставлен определенный значок.
Открыв окно «Изменение правила форматирования» , можно выбрать «Значение» и «Тип» для чисел, которым будет соответствовать каждый значок.
Как удалить
Если Вам нужно удалить условное форматирование для определенного диапазона (и не только), кликните по кнопочке «Удалить правила» и выберите нужный пункт из меню.
Как создать новое правило
Кнопка «Создать правило» позволит создать новые необходимые условия для выбранного диапазона.
Пример
Предположим, есть небольшая табличка, которая представлена на рисунке выше. Создадим для нее различные правила. Если числа в диапазоне выше «0» – закрасим блоки в желтый цвет, выше «10» – в зеленый, выше «18» – в красный.
Для начала, нужно выбрать тип – «Форматировать только ячейки, которые содержат» . Теперь в поле «Измените описание правила» задаем значение, выбираем цвет ячейки и нажимаем «ОК» . Создаем, таким образом, три правила для выделенного диапазона.
Таблица в примере отформатирована следующим образом.
Как управлять правилами
Если у вас в документе уже есть условное форматирование и для него заданы определенные условия, но нужно их изменить, то давайте рассмотрим, как управлять ими. Для этого выделяем этот же диапазон и кликаем по кнопочке «Управление правилами» .
Откроется окно «Диспетчер правил условного форматирования» . В нем можно создать новое для выделенного диапазона, изменить или удалить любое из списка.
Все они к выделенному диапазону, применяются в определенном порядке. Давайте рассмотрим такой пример: если значение в ячейке «12» , она может быть закрашена в желтый и зеленый цвет, в соответствии поставленным условиям. Но так как правило «Значение ячейки > 10» имеет больший приоритет, в сравнении с «Значение ячейки > 0» , то ячейка закрасится в зеленый цвет. Изменить порядок правил, можно с помощью стрелочек.
Надеюсь, Вы поняли, как использовать условное форматирование в документе Excel. Оно позволит отформатировать ячейки нужным образом. Причем, если значения в ячейках изменятся, то соответствующим образом изменится и форматирование для них.
Условное форматирование в excel как это работает?
Всем радости на fast-wolker.ru! Сегодня мы познакомимся с такой функцией табличного редактора excel, как «условное форматирование». Что это такое и для чего оно нужно? Условное форматирование предназначено для анализа данных и наглядного представления готовых результатов.
С помощью условного форматирования можно делать такие операции, как изменять шрифт, закрашивать значения различным цветом, а так же задавать формат границ. Применяется это форматирование можно, как на одну, так и на несколько ячеек, а так же строк и столбцов.
Условным такое форматирование называется потому, что формат ячейки задается при помощи условий. То есть, ячейки (или нужные области) меняют свой внешний вид если заданное в екселе условие выполнено (истина) или не выполнено (ложь).
Настройка осуществляется при помощи соответствующей кнопки, которая легко находится во вкладке «Главная». Нажав на нее, в открывшемся меню обнаружим различные варианты условного форматирования.
Когда вы применяете условное форматирование, вы задаете два основных параметра: какие ячейки будут форматироваться и те самые условия для форматирования. Ниже мы и выясним, как проводится такое условное форматирования и какой результат можно получить.
Условное форматирование — если выполняется условие то нужные ячейки в таблице окрашиваются в нужный цвет
Первое, что мы выясним, как в таблице подсветить цветом нужные данные. Например, у нас имеются определенные данные, среди которых нам необходимо выделить значения больше или меньше по отношению к исходному.
Выделяем столбец, который нам нужен, затем идем в пункт «Условное форматирование» и в открывшемся меню выбираем «правила выделения ячеек». Сбоку откроется еще вкладка. Где нам надо выбрать значение «больше» или «меньше». Поскольку нам надо меньшее, то выбираем это значение.
Появляется окно, в котором выбираем необходимое значение, меньше которого надо закрасить ячейки. По умолчанию стоит уже наиболее подходящее, но вы можете выбрать и любое другое, которое необходимо вам.
Одновременно с этим в таблицы все выбранные в этом диапазоне ячейки уже закрашены. Так что изменяя данные, вы теперь можете наглядно видеть готовый результат. В строке рядом, можно выбрать опцию закрашивания. Нажимаем на раскрывающееся меню и видим следующие «расцветки»:
Выбираем наиболее подходящий. Опять-таки, нажимая на каждый, видим готовый результат, так что можно по выбирать, что более приятно взгляду. Если вас не устраивают готовые шаблоны, вы можете нажать на самый последний пункт «пользовательский формат». Далее откроется окно, где вы сможете настроить свою визуализацию.
Когда вы закончите все настройки, нажимаете на кнопку ОК и изменения будут приняты. В результате получим следующую картинку:
В таблице подсвечены все необходимые нам данные и нам уже не нужно взглядом их искать по всему столбцу.
Применение условного форматирования по другому столбцу
Аналогичным образом можно произвести выборку по уже готовому значению, введенному заранее в столбец рядом.
Выделяем столбец, вводим нужную цифру и идем в пункт «Условное форматирование». Здесь, как описано в предыдущем разделе, выбираем «правила выделения ячеек», где так же выбираем больше или меньше, в зависимости от того, что нам нужно. Но пусть будет значение вновь «меньше».
В открывшемся окне в строку «форматировать ячейки, которые меньше…» необходимо внести значение 50. Щелкаем мышью по этой цифре и формула ячейки отобразится в этой строке.
Одновременно подкрасятся и ячейки, которые меньше 50. Выбираем в строке рядом необходимую визуализацию и жмем ОК. В результате получаем готовое форматирование.
Выделение диапазонов несколькими цветами по условию
Другой вариант: нам необходимо в одном столбце выделить разные диапазоны разным цветом.
Например, меньше 30 один диапазон и более 40 – другой диапазон. Выделяем столбец и идем через пункт «условное форматирование» в «правила выделения ячеек» — «меньше». В строке «форматировать ячейки, которые меньше…» ставим значение 30 и выбираем цвет красный текста и выделения ячеек.
Нажимаем ОК. Теперь повторяем все тоже самое, но выбираем уже пункт не «меньше», а «больше».
В строке «форматировать ячейки, которые больше…» ставим значение 40. Цвет выбираем любой другой, например желтый. В результате применения параметров получим следующее.
У меня все значения были больше 40, поэтому желтым цветом закрасился весь столбец. А так, будут выделены те ячейки, которые несут заданный диапазон.
Аналогичным образом можно сделать форматирование по всем тем условиям, которые даны в соответствующем пункте. Однако, бывает ситуация, когда шаблонные условия не удовлетворяют и вам нужно свое особое форматирование. Что делать? Надо просто создать свое условие.
Как создать свое условие для форматирования столбцов?
Начинаем с того, что так же выделяем нужный столбец, для которого хотим создать условие или правило и идем в пункт «условное форматирование», где выбираем «создать правило».
Откроется окно с перечнем всех возможных вариантов. В верхнем окне выбираем тип правила, а в нижнем будут его настройки. Замечу, что для каждого типа правила свои настройки. Выбираем, например, «форматировать только ячейки, которые содержат…».
Ниже увидим данные для изменения этого правила.
Что здесь можно изменить. Первый пункт «значение ячейки». Раскрыв этот пункт увидим, что кроме значения ячеек, здесь есть вариант текст, дата и т.д. Выставляем то значение, которое у нас имеется в столбце. Нам необходимо форматировать данные в ячейках потому оставляем первое значение.
Во втором пункте определяем как будем отбирать данные: между, равно, больше или меньше и т.д. оставляем вариант между.
И в окнах рядом ставим между какими значениями делаем форматирование. Например, больше 45, но меньше 66.
Затем нажимаем на кнопку «форматировать» и в открывшемся окне выбираем графическое значение: цвет, шрифт и т.п.
После применения всех настроек жмем ОК. В результате получим подкрашенные значения в диапазоне от 45 до 66. Все остальные значения окажутся не подсвеченными.
Вот так на простых примерах можно создать условное форматирование по данным в таблице. Если поэкспериментировать с другими настройками, то вы получите полное представление о том, как работает эта функция. Успехов!