Условное форматирование – это очень полезная функция в Excel, которая позволяет отформатировать числовые данные или текст в таблице, в соответствии заданным условиям или правилам. Благодаря ему, взглянув на нужные ячейки, Вы сразу сможете оценить значения, так как все данные будут представлены в удобном наглядном виде.
Кнопка «Условное форматирование» находится на вкладке «Главная» в группе «Стили» .
Задание условного форматирования
Лучшие наборы значков: значки устанавливают значки отображения для разных категорий данных на основе установленных вами пороговых значений. Вы можете применять гладкие границы или заполнять панели данных или выравнивать их справа от баров данных в ячейках. Кроме того, бары данных теперь пропорционально более репрезентативны для фактических значений, а бары данных отрицательного значения отображаются на противоположной стороне оси относительно положительных значений. Другие улучшения. Когда вы указываете критерии условных правил, вы можете ссылаться на значения в других листах в книге.
Кликнув по ней, откроется меню с видами условного форматирования.
Первый пункт – «Правила выделения ячеек» . Здесь мы можем сравнить числовые значения выделенного диапазона ячеек с определенным заданным числом, или с другим диапазоном значений. Сравнивать можно не только числа, но и текст, и даты.
Условное форматирование даты в Excel
Затем выберите интересующую дорожку и выберите «Условное форматирование» и один из типов правил, а затем один из подтипов в группе «Стили» на вкладке «Главная». Поскольку это галерея, эффект можно увидеть в выборе. Выберите подтип, который вы хотите применить. Если вы предпочитаете определять собственный стиль, нажмите «Новое правило», чтобы установить все параметры.
Условное форматирование может использоваться в различных обстоятельствах. Любой дубликат будет быстро расположен в определенном формате. Выберите интересующий диапазон, выберите параметр «Формат как таблица» в группе «Стили» на вкладке «Главная». Если он уже находится в форме таблицы, щелкните один из предложенных макетов в диалоговом окне, которое появляется на экране. Поскольку экранное диалоговое окно представляет собой галерею, выбранный диапазон отображает предварительный просмотр результата при наведении курсора на различные возможности.
Сравним значения в выбранном диапазоне с заданным числом. Выделим все числа в столбце, которые равны 8. Нажимаем «Условное форматирование» – «Правила выделения ячеек» – «Повторяющиеся значения» . В списке выбираем «повторяющиеся» и тип заливки. Теперь все повторы в столбце выделены цветом.
Если диапазон не является листом, диалоговое окно не будет галереей. Нажмите один из доступных макетов, чтобы применить его. Это преобразует выбранный диапазон в рабочий лист, а затем подтвердит целевое местоположение, а первая строка содержит заголовки.
Как назначить имя таблицы
Размер и содержание выбранного диапазона ячеек могут быть не совсем подходящими для выбранного форматирования. Вы не можете изменить один из предложенных макетов: для этого вы должны создать новую форму для электронных таблиц в соответствии с процедурой, аналогичной той, которая была описана ранее для пользовательского стиля ячеек, внося соответствующие изменения в предыдущее форматирование.
Сравним значения в первом диапазоне со вторым, и если значение в первом будет меньше, выделим ячейку цветом. Выбираем из списка «Меньше» . Теперь делаем относительную ссылку на второй столбец: кликаем мышкой по первому числу. Доллар перед F значит, что сравнивать будем значения с этим столбцом, но в разных ячейках. В результате, все значения в первом столбце, которые меньше, чем во втором, выделены цветом.
Форматирование и удобочитаемость рабочего листа
Одна из них - условная функция форматирования, которая автоматически применяет определенный формат. Вы можете, например, сделать ячейку зеленой или красной автоматически в зависимости от полученного результата. Другие цвета, границы и действия могут быть выполнены. Условное форматирование автоматически применяет формат, если выполняется конкретное условие.
В диалоговом окне вы можете установить условия в соответствии с вашим проектом. Форматирование будет применено к активной ячейке. Выберите оператора из списка и введите данные, которые необходимо проверить в правильном поле: вы можете обратиться к альфа-числовым данным или содержимым других ячеек.
Применим форматирование к ячейкам, содержащим текст. Выбираем из списка «Текст содержит» . Например, выделим все имена, которые начинаются с «В» – такие ячейки окрашиваются в выбранный цвет.
Новшества условного форматирования в более поздних версиях Excel
Тип условия «Формула» проверяет логическую функцию. Но вы также можете создавать свои собственные логические функции. В статье также описывается реализация логических формул. Кнопка «Формат» в главном диалоговом окне позволяет вам установить форматирование, которое будет применяться при выполнении условия. Вы можете указать шрифт, границы или шаблоны ячейки в соответствии с вашими предпочтениями. Основная цель остается выделить данные, соответствующие условиям.
Затем вы можете просмотреть форматирование, указанное в главном окне. Количество условий ограничено до 3 на ячейку. Условное форматирование не может ссылаться на другую книгу. Условное форматирование может ссылаться на другой лист, называя ячейки, которые будут использоваться в формуле.
Второй пункт – «Правила отбора первых и последних значений» . Выделяет ячейки, которые относятся к первым или последним элементам, в соответствии заданному значению или проценту.
Зададим форматирование для тех ячеек, значение в которых выше среднего в выделенном диапазоне. Выбираем из списка «Выше среднего» . Соответствующие ячейки будут выделены.
Невозможно применить условное форматирование на основе формата ячейки. Альтернативой является набор функции, которая будет использовать те же критерии, что и условное форматирование. Когда несколько условий истинны, в форматировании применяется только форматирование первого истинного условия.
Невозможно изменить условное форматирование или применить новые, пока рабочая книга является общей. Скопируйте ячейку, содержащую форматирование. Затем выберите ячейку, которая должна получить такое же форматирование. Щелкните правой кнопкой мыши. Выберите опцию «Специальная вставка» в контекстном меню.
Третий пункт – «Гистограммы» . Отображают значение в ячейках в виде гистограммы. Ячейка принимается за 100%, которому соответствует максимальное значение в выбранном диапазоне. Если значение в ячейке будет отрицательное – гистограмма делится на половину, имеет другую направленность и цвет.
Относительная ссылка основана на относительном положении ячейки, которая содержит формулу и ссылки на ячейки, указанные в формуле. Если позиция ячейки, которая содержит формулу, изменяется, ссылка изменяется. Используйте относительные ссылки, если вы хотите адаптировать ссылки к каждой ячейке в выбранном диапазоне.
Абсолютные ссылки
Абсолютная ссылка на ячейку указывает ячейки, которые находятся в фиксированном местоположении. Если позиция ячейки, содержащей формулу, изменяется, абсолютная ссылка остается неизменной. Формулы используют относительные ссылки по умолчанию. Смешанная ссылка включает либо абсолютный столбец, либо относительную строку, либо абсолютную строку, и относительную колонку.
Отобразим выделенный диапазон значений в виде гистограммы. Выбираем любой из предложенных способов заливки.
Теперь давайте представим, что минимальное значение для построения гистограммы должно быть 5. Выделяем нужный диапазон ячеек, кликаем по кнопочке «Условное форматирование» и выбираем из списка «Управление правилами» .
Найти ячейки с условным форматированием
Чтобы найти все ячейки, содержащие условное форматирование, щелкните любую ячейку в листе. Выберите «Тот же» вариант для поиска только для ячеек, которые содержат условное форматирование, которое совпадает с ячейками в выбранной ячейке.
Определите максимальное значение в диапазоне ячеек
Скрыть ошибки в ячейках. Условное форматирование также можно использовать для скрытия значений, например, путем применения одного цвета к тексту и фону ячейки.Поиск ближайшей даты
Найти ячейки между двумя датами
Форматирование по 2 критериям в формуле. Применить цвет к полной строке. Выберите цветную линию. Применить цвет ко всем нечетным строкам в листе. Выберите все ячейки на листе. Этот совет полезен для улучшения удобочитаемости листов, используемых в качестве базы данных.
Откроется следующее окно. В нем можно создать новое правило для выделенных ячеек, изменить или удалить нужное правило из списка. Выбираем «Изменить правило» .
Внизу окна можно изменить описание для правила. Ставим «Минимальное значение» – «Число» , и в поле «Значение» пишем «5» . Если Вы не хотите, чтобы в ячейках отображались числа, поставьте галочку в пункте «Показывать только столбец» . Здесь же можно изменить цвет и тип заливки.
Скрытие неактуальных данных
Условное форматирование может быть настроено и параметризовано макросом. Например, вы можете отобразить основное диалоговое окно условного форматирования. Диалоги. Задает условие «Не более 500»рабочих листов. Следующая процедура извлекает все форматирование ячейки, и вы можете использовать ее для извлечения указанных критериев и, например, для проверки условий макросом.
Копирование условного форматирования
Эта процедура удаляет все условные форматы, содержащиеся в листе. Затем поделитесь им, нажав на следующие кнопки. В этом примере убедитесь, что стиль презентации заполнен красным светом с темно-красным текстом. Правило активируется, поскольку значение ячейки меньше минимального. Вы также можете изменить цвета или презентацию. В этом случае, если значение составляет от 1 до 5 единиц больше минимального порога. В списке стилей презентации выберите Желтый цвет с темно-желтым текстом. Можно скопировать правила условного форматирования, которые вы настроили на одну ячейку другим.
В результате минимальное значение для выделенных ячеек «5» , а максимальное выбирается автоматически.
Четвертый пункт – «Цветовые шкалы» . Ячейка заливается цветом, который зависит от значения, которое в ней записано.
Это удобнее, чем переделать их! На вкладке «Главная» в группе «Буфер обмена» выберите команду «Форматирование воспроизведения». Новая опция условного макета должна представлять значения в виде горизонтальной полосы. Поэтому можно сравнивать содержимое нескольких ячеек, просто сравнивая их бары данных. Следующее упражнение - сравнить средние показатели пяти учеников.
На вкладке «Главная» выберите команду «Условное форматирование» и «Базы данных». Выберите цвет баров, которые вы хотите. Следующее упражнение - поместить карту оттенков цвета по нотам учеников. На вкладке «Главная» выберите команду «Условное форматирование» и «Цветовые оттенки». Другим способом представления значений в ячейках является использование значков. Следующее упражнение - разместить значки в серии чисел.
Если открыть окно «Изменение правила форматирования» , как описано в предыдущем пункте, можно выбрать «Стиль формата» , «Цвет» заливки, максимальное и минимальное значение для выбранного диапазона ячеек.
Условное форматирование в соответствии с содержимым другой ячейки или формулы
На вкладке «Главная» выберите параметры «Условное форматирование» и «Новое правило». Относительные и абсолютные ссылки также применяются в условном форматировании. Нажмите кнопку «Формат». В этом окне вы можете изменить представление значений, шрифта, стиля рамки или цвета и стиля фона ячейки.
Применения для одного диапазона несколько условных форматирований
Условное форматирование быстро выделяет важную информацию в электронной таблице. Однако в некоторых случаях правила форматирования по умолчанию недостаточны. Добавление формулы к условному правилу форматирования может облегчить выполнение задач, которые были бы невозможны с правилами по умолчанию.
Например, в поле «Минимальное значение» я поставила «3» . Выбранный диапазон будет выглядеть следующим образом – ячейки, значение в которых ниже 4-ох будут просто не закрашены.
Пятый пункт – «Наборы значков» . В ячейку, в соответствии с ее значением, будет вставлен определенный значок.
Создание условных правил форматирования с помощью формулы
Предположим, например, что врач хочет отслеживать дни рождения своих пациентов и отмечать их, когда они посылают свои пожелания. Этот рабочий лист показывает необходимую информацию с использованием условного форматирования на основе двух правил, содержащих каждую формулу.
В диалоговом окне «Новое правило форматирования» нажмите «Использовать формулу для определения ячеек для форматирования».
- В поле «Стиль» выберите «Жирный».
- В поле «Цвет» выберите «Белый» и в поле «Стиль» выберите «Жирный».
Открыв окно «Изменение правила форматирования» , можно выбрать «Значение» и «Тип» для чисел, которым будет соответствовать каждый значок.
Если Вам нужно удалить условное форматирование для определенного диапазона ячеек (и не только), кликните по кнопочке «Удалить правила» и выберите нужный пункт из меню.
В тех случаях, когда вы не можете создать определенное форматирование через предопределенные условные форматы, вы всегда можете использовать формулу для применения условного форматирования. Этот аргумент иллюстрирует различные методы применения некоторых из этих предопределенных условных правил форматирования. Принимая во внимание, что возможные комбинации составляют тысячи, рекомендуется экспериментировать, пока вы не найдете наиболее подходящих для ваших нужд. Также будьте осторожны, чтобы не применять чрезмерное количество условных форматов, что сделает общественность менее понятной, что вы хотите сообщить.
Кнопка «Создать правило» позволит создать нужные правила для выбранного диапазона ячеек.
Предположим, есть диапазон ячеек, который представлен на рисунке выше. Создадим для него различные правила. Если значение числа в диапазоне выше «0» – закрасим ячейки в желтый цвет, выше «10» – в зеленый, выше «18» – в красный.
Для начала, нужно выбрать тип правила – «Форматировать только ячейки, которые содержат» . Теперь в поле «Измените описание правила» задаем значение, выбираем цвет ячейки и нажимаем «ОК» . Создаем, таким образом, три правила для выделенного диапазона.
Ячейки отформатированы следующим образом.
Теперь давайте рассмотрим, как управлять созданными правилами. Для этого выделяем этот же диапазон ячеек и кликаем по кнопочке «Управление правилами» .
Откроется окно «Диспетчер правил условного форматирования» . В нем можно создать новое правило для выделенного диапазона ячеек, изменить или удалить любое правило из списка.
Правила к выделенному диапазону, применятся в определенном порядке. Если значение в ячейке «12» , она может быть закрашена в желтый и зеленый цвет, в соответствии поставленным условиям. Но так как правило «Значение ячейки > 10» имеет больший приоритет, в сравнении с «Значение ячейки > 0» , то ячейка закрасится в зеленый цвет. Изменить порядок правил, можно с помощью стрелочек.
Надеюсь, Вы поняли, как использовать условное форматирование в документе Excel. Оно позволит отформатировать ячейки нужным образом. Причем, если значения в ячейках изменятся, то соответствующим образом изменится и форматирование для этих ячеек.
Условное форматирование позволяет автоматически изменять форматирование ячеек в зависимости от их содержимого.
Например, нужно отформатировать диапазон ячеек таким образом, чтобы отрицательные значения отображались на светло-желтом фоне. При вводе или изменении содержимого ячейки Excel проверит введенное значение и сравнит с правилами условного форматирования. Если введенное значение отрицательное, фон поменяется на светло-желтый, в противном случае ничего не произойдет.
Условное форматирование можно использовать определения ячеек, которые содержат недопустимые значения, или ячеек определенного типа. Затем согласно примененному форматированию можно определить, какие ячейки содержат значения определенного типа. Например, к искомым ячейкам можно применить ярко-красный фон.
Задание условного форматирования
Для задания условного форматирования в ячейке или диапазоне выполните ряд действий.
Теперь ячейка или диапазон будут отформатированы по заданным условиям. Такое форматирование является динамическим. Это значит, что, если содержимое ячейки изменится, Excel оценит новое значение и в соответствии с ним изменит форматирование ячейки.
Параметры форматирования
После нажатия на кнопке Формат
диалогового окна Условное форматирование
откроется окно Формат ячеек
(рис.3.4).
Рис. 3.4. Диалоговое окно Формат ячеек, используемое для условного форматирования
Оно несколько отличается от обычного окна Формат - в нем отсутствуют вкладки Число , Выравнивание и Защита , но появилась новая Очистить .
С помощью диалогового окна Найти и заменить можно выделить на рабочем листе ячейки, к которым было применено определенное форматирование. Но с помощью этого окна нельзя определить ячейки, к которым было применено условное форматирование.
Определение условия
В левой части диалогового окна Условное форматирование есть раскрывающийся список (см. рис. 3.3), позволяющий выбрать одну из двух опций.
- значение . Используется для определения простых условий.
- формула . Используется для определения более сложных условий, в которых применяются формулы.
Простые условия
После выбора элемента значение в следующем раскрывающемся списке можно определить перечисленные ниже типы условий.
- между . Указывается два значения.
- вне. Указывается два значения.
- равно. Указывается одно значение.
- не равно. Указывается одно значение.
- больше. Указывается одно значение.
- меньше. Указывается одно значение.
- больше или равно. Указывается одно значение.
- меньше или равно. Указывается одно значение.
Ввести значения можно как вручную, так и с помощью ссылки на ячейку.
Условия с использованием формул
При выборе элемента Формула в поле, расположенном справа, нужно задать формулу. Это можно сделать, просто определив ячейку с формулой на рабочем листе, а затем указав ссылку на нее, или же введя ее непосредственно в диалоговом окне Условное форматирование. Как всегда, перед началом формулы должен стоять знак равенства (=).
Формула должна возвращать только логические значения ИСТИНА или ЛОЖЬ . Если результат соответствует значению ИСТИНА , условие выполняется и к ячейке или диапазону применяется условное форматирование. Иначе (если результат принимает значение ЛОЖЬ ) условное форматирование не применяется.
Если в формуле, введенной в диалоговом окне Условное форматирование , используется ссылка на ячейку, то эта ссылка будет относительной по отношению к верхней левой ячейке выделенного диапазона.
Например, если нужно задать условное форматирование, которое будет менять цвет заливки пустых ячеек в диапазоне С2:С10, выполните ряд действий.
Обычно при вводе формулы условного форматирования для диапазона ячеек используется ссылка на активную ячейку, которой, как правило, является верхняя левая ячейка выделенного диапазона. Исключение составляют случаи, когда нужно использовать ссылку на определенную ячейку.
Например, в диапазоне А2:В10 необходимо применить условное форматирование только к тем ячейкам, значения которых больше значения ячейки С1. Для этого следует ввести формулу =А2>$С$1. В этом случае ссылка на ячейку С1 будет абсолютной и одинаковой во всех ячейках выделенного диапазона. Другими словами, формула условного форматирования, например, для ячейки А3 будет выглядеть следующим образом: =А3>$С$1. Следовательно, для всех ячеек выделенного диапазона относительные ссылки будут изменяться в соответствии с позицией ячейки в диапазоне, а абсолютные всегда будут ссылаться на одну и ту же ячейку.
Множественные условия
Щелкнув на кнопке А также диалогового окна Условное форматирование , можно определить дополнительное условие (трех условий).
Например, для ячейки или диапазона можно определить следующие условия (а также параметры форматирования, которые будут применяться при выполнении тех или иных условий):
Значение ячейки меньше 0
Значение ячейки равно 0
Значение ячейки больше 0
В этом случае форматирование будет зависеть от знака содержащегося в ячейке значения
(отрицательное, положительное или ноль).
Если ни одно из условий не примет значение ИСТИНА, форматирование ячейки не
изменится. Если же будет выполняться более одного условия, Excel применит форматирование в
соответствии с первым выполненным условием. Например, такая ситуация может произойти,
если задать следующие условия:
Значение ячейки лежит между 1 и 12
Значение ячейки меньше 6
В этом случае, если ячейка будет содержать, например, число 4, выполнятся оба условия, но ячейка будет отформатирована в соответствии с параметрами, установленными для первого условия.
Копирование ячеек, содержащих условное форматирование
Информация об условном форматировании сохраняется в ячейке почти так же, как и стандартное форматирование. Это значит, что при копировании ячейки копируется ипримененное к ней условное форматирование (если оно есть). Чтобы копировать только условное форматирование, в диалоговом окне Специальная вставка нужно выбрать опцию форматы . Добавление строк и столбцов в диапазон, в котором не используется условное форматирование, создаст новые ячейки с теми же параметрами условного форматирования. Копирование или вставка ячейки в диапазон, который содержат условное форматирование, приводят к его удалению. Если нужно вставить скопированные данные в ячейку, в которой используется условное форматирование, можно воспользоваться диалоговым окном Специальная вставка и выбрать опцию значения .
Удаление условного форматирования
При удалении содержимого ячейки с помощью нажатия клавиши <Delete > условное форматирование, которое ранее было применено к этой ячейке, не удаляется. Чтобы удалить условное форматирование как и прочее форматирование, выберите команду ПравкаОчиститьФорматы . Для удаления содержимого ячейки (включая форматирование) выберите команду Правка Очистить Все . Чтобы удалить только условное форматирование (и оставить содержимое и другие параметры форматирования ячейки), выполните ряд действий:Выделение ячеек с условным форматированием
Просто посмотрев на ячейки, невозможно сказать, какие из них содержат условное форматирование, а какие - нет. Но выделить ячейки с условным форматирование можно с помощью диалогового окна Переход . Для этого выполните ряд действий.
Использование ссылок на другие рабочие листы
При попытке использовать условное форматирование, которое содержит ссылки на другие листы, Excel выдает сообщение об ошибке.
Чтобы сослаться на ячейку другого рабочего листа, можно использовать два варианта:
Первый вариант. Необходимо создать ссылку на эту ячейку в том рабочем листе, в котором будет использоваться условное форматирование.
Например, если необходимо, чтобы формула условного
форматирования ссылалась на ячейку А1 рабочего листа ЛистЗ, нужно вставить в ячейку активного
листа следующую формулу.
=ЛистЗ!А1
После этого можно использовать ссылку на данную ячейку в формуле условного
форматирования.
Второй вариант. Можно присвоить имя ячейке, на которую нужно создать ссылку. Тогда это имя можно использовать вместо ссылки в диалоговом окне Условное форматирование. Поименованная ячейка может находиться на любом рабочем листе активной рабочей книги.
Примеры использования условного форматирования
Пример1. Определение нечисловых данных
Представленное на рис. 3.7. условное форматирование будет применять форматирование к ячейке А1 только в том случае, если в ней содержится текст. Чтобы применить такую формулу к диапазону, нужно сначала выделить необходимый диапазон. Аргументом функции ЕТЕКСТ должна быть активная ячейка (обычно это верхняя левая ячейка диапазона).
Рис.3.7. окно условно форматирования для примера 1
Пример 2. Выделение ячеек, значения которых выше среднего
В этом примере условное форматирование применяется к диапазону A1:С4, только к тем ячейкам, значения которых выше среднего (рис. 3.8).
Рис.3.8. Окно условно форматирования для примера 2
Рис. 3.9. Результат применения условного форматирования примера 2
