какую функцию нужно использовать для построения графика отпусков с помощью условного форматирования
Обучение условному форматированию в Excel с примерами
Условное форматирование – удобный инструмент для анализа данных и наглядного представления результатов. Умение им пользоваться сэкономит массу времени и сил. Достаточно бегло взглянуть на документ – нужная информация получена.
Как сделать условное форматирование в Excel
Инструмент «Условное форматирование» находится на главной странице в разделе «Стили».
При нажатии на стрелочку справа открывается меню для условий форматирования.
Сравним числовые значения в диапазоне Excel с числовой константой. Чаще всего используются правила «больше / меньше / равно / между». Поэтому они вынесены в меню «Правила выделения ячеек».
Введем в диапазон А1:А11 ряд чисел:
Выделим диапазон значений. Открываем меню «Условного форматирования». Выбираем «Правила выделения ячеек». Зададим условие, например, «больше».
Введем в левое поле число 15. В правое – способ выделения значений, соответствующих заданному условию: «больше 15». Сразу виден результат:
Выходим из меню нажатием кнопки ОК.
Условное форматирование по значению другой ячейки
Сравним значения диапазона А1:А11 с числом в ячейке В2. Введем в нее цифру 20.
В левое поле вводим ссылку на ячейку В2 (щелкаем мышью по этой ячейке – ее имя появится автоматически). По умолчанию – абсолютную.
Результат форматирования сразу виден на листе Excel.
Значения диапазона А1:А11, которые меньше значения ячейки В2, залиты выбранным фоном.
Зададим условие форматирования: сравнить значения ячеек в разных диапазонах и показать одинаковые. Сравнивать будем столбец А1:А11 со столбцом В1:В11.
Каждое значение в столбце А программа сравнила с соответствующим значением в столбце В. Одинаковые значения выделены цветом.
Внимание! При использовании относительных ссылок нужно следить, какая ячейка была активна в момент вызова инструмента «Условного формата». Так как именно к активной ячейке «привязывается» ссылка в условии.
Чтобы инструмент «Условное форматирование» правильно выполнил задачу, следите за этим моментом.
Проверить правильность заданного условия можно следующим образом:
В открывшемся окне видно, какое правило и к какому диапазону применяется.
Условное форматирование – несколько условий
Исходный диапазон – А1:А11. Необходимо выделить красным числа, которые больше 6. Зеленым – больше 10. Желтым – больше 20.
Заполняем параметры форматирования по первому условию:
Нажимаем ОК. Аналогично задаем второе и третье условие форматирования.
Обратите внимание: значения некоторых ячеек соответствуют одновременно двум и более условиям. Приоритет обработки зависит от порядка перечисления правил в «Диспетчере»-«Управление правилами».
То есть к числу 24, которое одновременно больше 6, 10 и 20, применяется условие «=$А1>20» (первое в списке).
Условное форматирование даты в Excel
Выделяем диапазон с датами.
В открывшемся окне появляется перечень доступных условий (правил):
Выбираем нужное (например, за последние 7 дней) и жмем ОК.
Красным цветом выделены ячейки с датами последней недели (дата написания статьи – 02.02.2016).
Условное форматирование в Excel с использованием формул
Если стандартных правил недостаточно, пользователь может применить формулу. Практически любую: возможности данного инструмента безграничны. Рассмотрим простой вариант.
Есть столбец с числами. Необходимо выделить цветом ячейки с четными. Используем формулу: =ОСТАТ($А1;2)=0.
Выделяем диапазон с числами – открываем меню «Условного форматирования». Выбираем «Создать правило». Нажимаем «Использовать формулу для определения форматируемых ячеек». Заполняем следующим образом:
Для закрытия окна и отображения результата – ОК.
Условное форматирование строки по значению ячейки
Задача: выделить цветом строку, содержащую ячейку с определенным значением.
Таблица для примера:
Необходимо выделить красным цветом информацию по проекту, который находится еще в работе («Р»). Зеленым – завершен («З»).
Порядок заполнения условий для форматирования «завершенных проектов»:
Обратите внимание: ссылки на строку – абсолютные, на ячейку – смешанная («закрепили» только столбец).
Аналогично задаем правила форматирования для незавершенных проектов.
В «Диспетчере» условия выглядят так:
Когда заданы параметры форматирования для всего диапазона, условие будет выполняться одновременно с заполнением ячеек. К примеру, «завершим» проект Димитровой за 28.01 – поставим вместо «Р» «З».
«Раскраска» автоматически поменялась. Стандартными средствами Excel к таким результатам пришлось бы долго идти.
Условное форматирование в EXCEL
history 26 октября 2012 г.
Условное форматирование – один из самых полезных инструментов EXCEL. Умение им пользоваться может сэкономить пользователю много времени и сил.
Начнем изучение Условного форматирования с проверки числовых значений на больше /меньше /равно /между в сравнении с числовыми константами.
Рассмотрим несколько задач:
СРАВНЕНИЕ С ПОСТОЯННЫМ ЗНАЧЕНИЕМ (КОНСТАНТОЙ)
СРАВНЕНИЕ СО ЗНАЧЕНИЕМ В ЯЧЕЙКЕ (АБСОЛЮТНАЯ ССЫЛКА)
Чуть усложним предыдущую задачу: вместо ввода в качестве критерия непосредственно значения (4), введем ссылку на ячейку, в которой содержится значение 4.
ПОПАРНОЕ СРАВНЕНИЕ СТРОК/ СТОЛБЦОВ (ОТНОСИТЕЛЬНЫЕ ССЫЛКИ)
Теперь будем производить попарное сравнение значений в строках 1 и 2.
Теперь каждое значение в строке 1 будет сравниваться с соответствующим ему значением из строки 2 в том же столбце! Выделены будут значения 1 и 5, т.к. они меньше соответственно 2 и 6, расположенных в строке 2.
Внимание! В случае использования относительных ссылок в правилах Условного форматирования необходимо следить, какая ячейка является активной в момент вызова инструмента Условное форматирование .
Примечание-отступление : О важности фиксирования активной ячейки при создании правил Условного форматирования с относительными ссылками
Теперь посмотрим как это влияет на правило условного форматирования с относительной ссылкой.
УСЛОВНОЕ ФОРМАТИРОВАНИЕ и ФОРМАТ ЯЧЕЕК
ОТЛАДКА ПРАВИЛ УСЛОВНОГО ФОРМАТИРОВАНИЯ
Чтобы проверить правильно ли выполняется правила Условного форматирования, скопируйте формулу из правила в любую пустую ячейку (например, в ячейку справа от ячейки с Условным форматированием). Если формула вернет ИСТИНА, то правило сработало, если ЛОЖЬ, то условие не выполнено и форматирование ячейки не должно быть изменено.
Вернемся к задаче 3 (см. выше раздел об относительных ссылках). В строке 4 напишем формулу из правила условного форматирования =A1
ИСПОЛЬЗОВАНИЕ В ПРАВИЛАХ ССЫЛОК НА ДРУГИЕ ЛИСТЫ
ПОИСК ЯЧЕЕК С УСЛОВНЫМ ФОРМАТИРОВАНИЕМ
Будут выделены все ячейки для которых заданы правила Условного форматирования.
ДРУГИЕ ПРЕДОПРЕДЕЛЕННЫЕ ПРАВИЛА
В меню Главная/ Стили/ Условное форматирование/ Правила выделения ячеек разработчиками EXCEL созданы разнообразные правила форматирования.
Чтобы заново не изобретать велосипед, посмотрим на некоторые их них внимательнее.
Теперь посмотрим на только что созданное правило через меню Главная/ Стили/ Условное форматирование/ Управление правилами.
Советую также обратить внимание на следующие правила из меню Главная/ Стили/ Условное форматирование/ Правила отбора первых и последних значений.
Слова «Последние 3 значения» означают 3 наименьших значения. Если в списке есть повторы, то будут выделены все соответствующие повторы. Например, в нашем случае 3-м наименьшим является третье сверху значение 10. Т.к. в списке есть еще повторы 10 (их всего 6), то будут выделены и они.
К сожалению, в правило нельзя ввести ссылку на ячейку, содержащую количество значений, можно ввести только значение от 1 до 1000.
В этом правиле задается процент наименьших значений от общего количества значений в списке. Например, задав 20% последних, будет выделено 20% наименьших значений.
Задавая проценты от 1 до 33% получим, что выделение не изменится. Почему? Задав, например, 33%, получим, что необходимо выделить 6,93 значения. Т.к. можно выделить только целое количество значений, Условное форматирование округляет до целого, отбрасывая дробную часть. А вот при 34% уже нужно выделить 7,14 значений, т.е. 7, а с учетом повторов следующего за 10-ю значения 11, будет выделено 6+3=9 значений.
ПРАВИЛА С ИСПОЛЬЗОВАНИЕМ ФОРМУЛ
Предположим, что необходимо выделять ячейки, содержащие ошибочные значения:
Того же результата можно добиться по другому:
Условное форматирование в excel как это работает?
Всем радости на fast-wolker.ru! Сегодня мы познакомимся с такой функцией табличного редактора excel, как «условное форматирование». Что это такое и для чего оно нужно? Условное форматирование предназначено для анализа данных и наглядного представления готовых результатов.
С помощью условного форматирования можно делать такие операции, как изменять шрифт, закрашивать значения различным цветом, а так же задавать формат границ. Применяется это форматирование можно, как на одну, так и на несколько ячеек, а так же строк и столбцов.
Условным такое форматирование называется потому, что формат ячейки задается при помощи условий. То есть, ячейки (или нужные области) меняют свой внешний вид если заданное в екселе условие выполнено (истина) или не выполнено (ложь).
Настройка осуществляется при помощи соответствующей кнопки, которая легко находится во вкладке «Главная». Нажав на нее, в открывшемся меню обнаружим различные варианты условного форматирования.
Когда вы применяете условное форматирование, вы задаете два основных параметра: какие ячейки будут форматироваться и те самые условия для форматирования. Ниже мы и выясним, как проводится такое условное форматирования и какой результат можно получить.
Условное форматирование — если выполняется условие то нужные ячейки в таблице окрашиваются в нужный цвет
Первое, что мы выясним, как в таблице подсветить цветом нужные данные. Например, у нас имеются определенные данные, среди которых нам необходимо выделить значения больше или меньше по отношению к исходному.
Выделяем столбец, который нам нужен, затем идем в пункт «Условное форматирование» и в открывшемся меню выбираем «правила выделения ячеек». Сбоку откроется еще вкладка. Где нам надо выбрать значение «больше» или «меньше». Поскольку нам надо меньшее, то выбираем это значение.
Появляется окно, в котором выбираем необходимое значение, меньше которого надо закрасить ячейки. По умолчанию стоит уже наиболее подходящее, но вы можете выбрать и любое другое, которое необходимо вам.
Одновременно с этим в таблицы все выбранные в этом диапазоне ячейки уже закрашены. Так что изменяя данные, вы теперь можете наглядно видеть готовый результат. В строке рядом, можно выбрать опцию закрашивания. Нажимаем на раскрывающееся меню и видим следующие «расцветки»:
Выбираем наиболее подходящий. Опять-таки, нажимая на каждый, видим готовый результат, так что можно по выбирать, что более приятно взгляду. Если вас не устраивают готовые шаблоны, вы можете нажать на самый последний пункт «пользовательский формат». Далее откроется окно, где вы сможете настроить свою визуализацию.
Когда вы закончите все настройки, нажимаете на кнопку ОК и изменения будут приняты. В результате получим следующую картинку:
В таблице подсвечены все необходимые нам данные и нам уже не нужно взглядом их искать по всему столбцу.
Применение условного форматирования по другому столбцу
Аналогичным образом можно произвести выборку по уже готовому значению, введенному заранее в столбец рядом.
Выделяем столбец, вводим нужную цифру и идем в пункт «Условное форматирование». Здесь, как описано в предыдущем разделе, выбираем «правила выделения ячеек», где так же выбираем больше или меньше, в зависимости от того, что нам нужно. Но пусть будет значение вновь «меньше».
В открывшемся окне в строку «форматировать ячейки, которые меньше…» необходимо внести значение 50. Щелкаем мышью по этой цифре и формула ячейки отобразится в этой строке.
Одновременно подкрасятся и ячейки, которые меньше 50. Выбираем в строке рядом необходимую визуализацию и жмем ОК. В результате получаем готовое форматирование.
Выделение диапазонов несколькими цветами по условию
Другой вариант: нам необходимо в одном столбце выделить разные диапазоны разным цветом.
Например, меньше 30 один диапазон и более 40 – другой диапазон. Выделяем столбец и идем через пункт «условное форматирование» в «правила выделения ячеек» — «меньше». В строке «форматировать ячейки, которые меньше…» ставим значение 30 и выбираем цвет красный текста и выделения ячеек.
Нажимаем ОК. Теперь повторяем все тоже самое, но выбираем уже пункт не «меньше», а «больше».
В строке «форматировать ячейки, которые больше…» ставим значение 40. Цвет выбираем любой другой, например желтый. В результате применения параметров получим следующее.
У меня все значения были больше 40, поэтому желтым цветом закрасился весь столбец. А так, будут выделены те ячейки, которые несут заданный диапазон.
Аналогичным образом можно сделать форматирование по всем тем условиям, которые даны в соответствующем пункте. Однако, бывает ситуация, когда шаблонные условия не удовлетворяют и вам нужно свое особое форматирование. Что делать? Надо просто создать свое условие.
Как создать свое условие для форматирования столбцов?
Начинаем с того, что так же выделяем нужный столбец, для которого хотим создать условие или правило и идем в пункт «условное форматирование», где выбираем «создать правило».
Откроется окно с перечнем всех возможных вариантов. В верхнем окне выбираем тип правила, а в нижнем будут его настройки. Замечу, что для каждого типа правила свои настройки. Выбираем, например, «форматировать только ячейки, которые содержат…».
Ниже увидим данные для изменения этого правила.
Что здесь можно изменить. Первый пункт «значение ячейки». Раскрыв этот пункт увидим, что кроме значения ячеек, здесь есть вариант текст, дата и т.д. Выставляем то значение, которое у нас имеется в столбце. Нам необходимо форматировать данные в ячейках потому оставляем первое значение.
Во втором пункте определяем как будем отбирать данные: между, равно, больше или меньше и т.д. оставляем вариант между.
И в окнах рядом ставим между какими значениями делаем форматирование. Например, больше 45, но меньше 66.
Затем нажимаем на кнопку «форматировать» и в открывшемся окне выбираем графическое значение: цвет, шрифт и т.п.
После применения всех настроек жмем ОК. В результате получим подкрашенные значения в диапазоне от 45 до 66. Все остальные значения окажутся не подсвеченными.
Вот так на простых примерах можно создать условное форматирование по данным в таблице. Если поэкспериментировать с другими настройками, то вы получите полное представление о том, как работает эта функция. Успехов!