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

Обновлено: 04.07.2024

Для учеников 1-11 классов и дошкольников
Бесплатные сертификаты учителям и участникам

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

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

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

- развивать мышление, познавательные интересы, навыки работы на компьютере, работы с табличными процессорами.

Тип урока: усвоение новых знаний, формирование умений и навыков.

Оборудование: доска, компьютер, программное обеспечение.

Организационный момент. Приветствие, проверка присутствующих. Объяснение хода урока.

Актуализация.

Какие возможности вы уже рассмотрели?

Какие арифметические действия есть в Excel , как они записываются.

Какие существуют правила записи ( = , скобки, не само значение, а адрес ячейки)

Типы данных (общий, числовой, текстовый, денежный, финансовый, дата, время, процентный)

Математические ( ABS , COS , ACOS , LOG , LN , EXP , ОКРУГЛ, КОРЕНЬ, НОД, НОК, СТЕПЕНЬ и т.д.)

Логические функции (И,ИЛИ, ЛОЖЬ, ИСТИНА, НЕ, ЕСЛИ, ЕСЛИОШИБКА)

Размер листа 1 048 576 строк и 16 384 столбца

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

Основная часть.

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

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

- Правила выделения ячеек

- Правила отбора первых и последних значений

- Гистограммы

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

- Наборы значков

Для установки условного форматирования необходимо:

- выделить нужный диапазон ячеек

- выполнить Главная – Стили – Условное форматирование выбрать необходимый тип правил

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

Также в условном форматировании есть команды:

- Создать правило… где мы можем Использовать формулу для определения форматируемых ячеек (если формула истинна, форматирование будет применено к выбранному диапазону ячеек)

- Удалить правила (удалить правила из выделенных ячеек, всего листа)

- Управление правилами где можно создать, изменить, удалить правило.

Практическая часть. Файл Условное форматирование. xlsx

Итоги урока. Домашнее задание.

подготовка к ЕГЭ/ОГЭ и ВПР
по всем предметам 1-11 классов

Курс повышения квалификации

Дистанционное обучение как современный формат преподавания

Курс повышения квалификации

Инструменты онлайн-обучения на примере программ Zoom, Skype, Microsoft Teams, Bandicam

Курс добавлен 31.01.2022
Сейчас обучается 25 человек из 18 регионов

Курс повышения квалификации

Педагогическая деятельность в контексте профессионального стандарта педагога и ФГОС

ЗП до 91 000 руб.
Гибкий график
Удаленная работа

Дистанционные курсы для педагогов

Свидетельство и скидка на обучение каждому участнику

Найдите материал к любому уроку, указав свой предмет (категорию), класс, учебник и тему:

5 602 899 материалов в базе

Самые массовые международные дистанционные

Школьные Инфоконкурсы 2022

Свидетельство и скидка на обучение каждому участнику

Другие материалы

Вам будут интересны эти курсы:

Оставьте свой комментарий

26.08.2015 1821
DOCX 81.4 кбайт
8 скачиваний
Оцените материал:

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

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

Автор материала

40%

Подготовка к ЕГЭ/ОГЭ и ВПР
Для учеников 1-11 классов

Московский институт профессиональной
переподготовки и повышения
квалификации педагогов

Дистанционные курсы
для педагогов

663 курса от 690 рублей

Выбрать курс со скидкой

Выдаём документы
установленного образца!

Учителя о ЕГЭ: секреты успешной подготовки

Время чтения: 11 минут

Университет им. Герцена и РАО создадут портрет современного школьника

Время чтения: 2 минуты

Минобрнауки и Минпросвещения запустили горячие линии по оказанию психологической помощи

Время чтения: 1 минута

В Белгородской области отменяют занятия в школах и детсадах на границе с Украиной

Время чтения: 0 минут

Минпросвещения России подготовит учителей для обучения детей из Донбасса

Время чтения: 1 минута

Инфоурок стал резидентом Сколково

Время чтения: 2 минуты

В Швеции запретят использовать мобильные телефоны на уроках

Время чтения: 1 минута

Подарочные сертификаты

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

Все материалы, размещенные на сайте, созданы авторами сайта либо размещены пользователями сайта и представлены на сайте исключительно для ознакомления. Авторские права на материалы принадлежат их законным авторам. Частичное или полное копирование материалов сайта без письменного разрешения администрации сайта запрещено! Мнение администрации может не совпадать с точкой зрения авторов.

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

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

После этого, открывается меню условного форматирования. Тут представляется три основных вида форматирования:

Гистограммы;
Цифровые шкалы;
Значки.

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

Как видим, гистограммы появились в выделенных ячейках столбца. Чем большее числовое значение в ячейках, тем гистограмма длиннее. Кроме того, в версиях Excel 2010, 2013 и 2016 годов, имеется возможность корректного отображения отрицательных значений в гистограмме. А вот, у версии 2007 года такой возможности нет.

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

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

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

Правила выделения ячеек

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

Больше;
Меньше;
Равно;
Между;
Дата;
Повторяющиеся значения.

В следующем поле, нужно определиться, как будут выделяться ячейки: светло-красная заливка и темно-красный цвет (по умолчанию); желтая заливка и темно-желтый текст; красный текст, и т.д. Кроме того, существует пользовательский формат.

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

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

Правила отбора первых и последних значений

Первые 10 элементов;
Первые 10%;
Последние 10 элементов;
Последние 10%;
Выше среднего;
Ниже среднего.

Создание правил

Выше мы говорили о правилах, которые уже установлены в программе Excel, и пользователь может просто выбрать любое из них. Но, кроме того, при желании, пользователь может создавать свои правила.

Открывается окно, где нужно выбрать один из шести типов правил:

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

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

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

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

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

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

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

Мы рады, что смогли помочь Вам в решении проблемы.

Отблагодарите автора, поделитесь статьей в социальных сетях.

Опишите, что у вас не получилось. Наши специалисты постараются ответить максимально быстро.

Главная Статьи Теория Статьи Интерфейс Условное форматирование

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

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

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

Excel последних версий предоставляет удобный интерфейс для управления условным форматированием как через простой выбор стандартного условия, так и через традиционный ввод формул. В версиях Excel до 2007 (формат рабочей книги xls) свойства условного форматирования были привязаны к каждой ячейке по отдельности. Имелось ограничение – не более 3х форматов на ячейку. В последующих версиях (формат xlsx) это ограничения было снято, к тому же теперь условные форматы хранятся с привязкой к листу независимо от свойств каждой ячейки.

Файл-пример

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

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

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

Использовать условное форматирование для нескольких типов задач:

Выделение цветом или шрифтом текущей ячейки в зависимости от ее же значения.
Окраска текущей ячейки в зависимости от значения другой ячейки.
Разделение блоков информации при помощи рамок.
Скрытие неактуальных данных при помощи форматов.
Графическое отображение данных – аналог диаграмм.

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

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

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

больше 10 – желтый цвет,
больше 20 – синий цвет

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

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

Цвет фона
Цвет шрифта, тип шрифта (но не размер или название)
Тип внешней рамки (ограниченный набор границ)
Числовой формат (не доступно в xls-файлах)

Изменить отступ, выравнивание, наклон текста, некоторые типы рамок и свойства защиты при помощи условного форматирования нельзя.

Неявное условное форматирование

Цвет шрифта

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

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

Блоки в выражении разделяются точкой с запятой, цвет текста заключается в квадратные скобки. Кроме красного цвета, можно использовать другие варианты: Черный, Синий, Голубой, Зеленый, Фиолетовый, Красный, Белый, Желтый.

Условие для цвета шрифта

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

В примере суммарные поступления от клиентов выделяются синим цветом шрифта, только если значение больше 10000руб (см. диапазон ОДДС!B7:Q11)

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

Формат для скрытия данных

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

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

Выделение значения

Один из самых простых вариантов условного форматирования – это цветовое выделение в зависимости от значения числа. Стандартный диалог Excel (лента Главная \ Условное форматирование \ Создать правило \ Форматировать все ячейки на основании их значений) позволяет задать различные логические условия: равно, не равно, больше, меньше, между. Сравнивать можно как с константой (числом), так и со ссылкой на другую ячейку. В файле-примере таким образом отформатирован диапазон Платежи!A3:A22. Выделены даты позже даты начала текущей недели – ячейки ОДДС!C2.

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

Гистограммы

Excel, начиная с версии 2007, предоставил возможность графического условного форматирования ячеек различными вариантами: гистограммы, цветовые шкалы, значки. Это простой, но очень эффектный интерфейс: требуется выделить область ячеек, затем просто выбрать вариант графического условного формата (например, лента Главная \ Условное форматирование \ Гистограммы).

В файле-примере таким образом отформатирован диапазон Платежи!C3:C22 – в виде гистограмм показаны значения платежей, хранящиеся в ячейках.

Повторяющиеся значения

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

Диапазон с гистограммами Платежи!C3:C22 дополнительно отформатирован по условию выделения жирным шрифтом повторяющихся значений:

Такое форматирование можно было организовать и в старых версиях Excel (xls), условие при этом задается формулой (в координатах примера):

Сложное условное форматирование

Скрытие неактуальных данных

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

Этот способ применен при условном форматировании отчета на листе ОДДС примера. Даты ранее текущей недели, которая задается в ячейке B2, выделяются белым фоном, тогда как обычный фон для этих ячеек – светло-коричневый. Ячейки с данными об остатках на начало период скрываются за счет использования одинакового светло-серого цвета для шрифта и заливки.

Формула для ячеек сравнивает ячейку с текущей датой с ячейкой, содержащей дату начала недели. Обратите внимание на применение относительной и смешанной адресации (символ "$" в координатах). Дело в том, что по умолчанию интерфейс Excel предлагает ссылку на абсолютный адрес выделяемой ячейки, и многие ошибочно считают, что условное форматирование надо задавать отдельно для каждой ячейки диапазона. Это неверно, правила копирования адресов работают и при применении условных форматов. Но для этого надо вводить формулу вручную или удалять знаки "$" из координат ячеек.

С нашей точки зрения при использовании условного форматирования для диапазонов зачастую понятнее применение R1C1-адресации Excel. Так, в частности, очевидно, что выражение RC подразумевает текущую ячейку. Та же запись в A1-адресации без использования "$" требует дополнительной привязки к текущей ячейке, что иногда затрудняет понимание всего выражения.

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

Условия для форматов могут содержать сложные многоуровневые выражения. Если результат формулы возвращает значение, отличное от нуля, то условие форматирования считается выполненным. Желательно, чтобы результат принимал логическое значение, т.е. TRUE=1 или FALSE=0. Это упрощает понимание выражения условного форматирования.

В примере для диапазона Поступления!A3:D20 установлено условное форматирование с проверкой на начало текстового значения в столбце C:

Разделение диапазонов при помощи рамок

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

В примере для всего диапазона таблицы Поступления!A3:D20 установлено условное форматирование с проверкой на равенство ячейке сверху:

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

Проверка на корректность формулы

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

Для подобных задач часто предлагается использование UDF-функций (User-defined functions) на VBA (Visual Basic for Applications) с проверкой, хранится ли в ячейке какая-либо формула. Дело в том, что при помощи стандартных функций рабочего листа такую проверку сделать нельзя – формула может проверить только значение в ячейке, но не то, каким образом оно было получено.

Вот пример подобной функции в модуле VBA:

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

Этот метод имеет существенные недостатки.

Главной проблемой при использовании UDF VBA является низкая производительность. При копировании такого условного формата на большой диапазон будет заметно запаздывание прорисовки этих ячеек, что не всегда приемлемо в работе.

Другим вариантом решения данной задачи является проверка на равенство формулы непосредственно в условии форматирования.

В примере для всего диапазона таблицы ОДДС!B20:P20 установлено такое условное форматирование:

Как видно из условия, наличие формулы в данном выражении не проверяется – сравнивается только результат. Если он отличен от заданного в формуле, то ячейка выделяется красным цветом (K20).

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

Условное форматирование - достаточно малоиспользуемый инструмент Excel. Но это как раз тот инструмент, при помощи которого можно изменить форматирование ячеек(цвет заливки, шрифт, границы) в зависимости от заданного условия, не прибегая к помощи Visual Basic for Applications.
Условное форматирование может значительно упростить выделение определенных ячеек или диапазона ячеек и визуализацию данных с помощью гистограммы, цветовых шкал и наборов значков. Оно изменяет внешний вид диапазона ячеек на основе указанного условия (или критерия). Если условие выполняется, то диапазон ячеек форматируется в соответствии с заданным для условия форматом; если условие не выполняется, то диапазон ячеек не форматируется.
Например, можно выделить ячейку с текущей датой; ячейку с числом, входящим в указанный диапазон; ячейка с определенным текстом и т.п.
Условное форматирование можно применить к диапазону ячеек, таблице или отчету сводной таблицы Excel.
Для чего может пригодиться Условное форматирование? Представим, что необходимо в большой таблице данных закрасить красным цветом все ячейки, значение в которых превышает 100. Что делается обычно в таких случаях? Верно. Устанавливается фильтр-Больше 100 и отфильтрованные строки закрашиваются. Но. Если значения этих ячеек формируются при помощи формул или просто изменяются по ходу работы с таблицей - довольно накладно будет каждый раз отыскивать значения больше 100. Установив же Условное форматирование выделять ничего не надо будет - ячейки будут окрашены красным автоматически, без Вашего участия.

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

В статье рассмотрим:

ГДЕ РАСПОЛОЖЕНО УСЛОВНОЕ ФОРМАТИРОВАНИЕ И КАК СОЗДАТЬ
Для создания условного форматирования необходимо:

Выделить ячейки для применения условного форматирования
В меню выбрать
- Excel 2003 : Формат (Format) -Условное форматирование (Conditional formatting) ;
- Excel 2007-2010 : вкладка Главная (Home) -Условное форматирование (Conditional formatting)
Выбрать одно из предустановленных правил (в Excel 2003 это значение (Cell Value Is) ) или создать свое (в Excel 2003 это возможно посредством пункта формула (Formula Is) );
Выбрать способ форматирования ячеек: цвет заливки, цвет шрифта, формат отображения, границы и т.д.
Подтвердить нажатием кнопки ОК

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

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

Условное форматирование в Excel 2007-2010:

Главный недостаток предустановленных правил - их нельзя применять к ячейкам на основании значений других ячеек. Они применяются исключительно для тех ячеек, в которых сами значения. Например, нельзя сделать отображение гистограмм в диапазоне А1:А10 , но значения для гистограмм брать из ячеек В1:В10 .

Для Excel 2003 предустановленные правила ограничиваются списком, имеющемся в пункте значение (Cell Value Is) , который в более поздних версиях называется Правила выделения ячеек:

Правила выделения ячеек (Highlight Cells Rules)

В Excel 2003 эти правила содержат условия:
Между, Вне, Равно, Не равно, Больше, Меньше, Больше или равно, Меньше или равно
between, not between, equal to, not equal to, greater than, less than, greater than or equal to, less than or equal to

В Excel 2007-2010: эти правила содержат условия:
Больше, Меньше, Между, Равно, Текст содержит, Дата, Повторяющиеся значения
Greater Than, Less Than, Between, Equal To, Text that Contains, A Date Occurring, Duplicate Values
Как видно, по большей части названия пунктов говорят за себя названиями, и не нуждаются в подробных описаниях их функционала. Чуть более подробно можно рассмотреть лишь Дата и Повторяющиеся значения из набора правил версий Excel 2007 и новее.
Дата:

Список содержит несколько значений: Вчера, Сегодня, Завтра, За последние 7 дней, На прошлой неделе, На текущей неделе, На следующей неделе, В прошлом месяце, В этом месяце, В следующем месяце
Yesterday, Today, Tomorrow, In the last 7 days, Last week, This week, Next week, Last month, This month, Next month
Соответственно, при выборе необходимого условия даты в указанном диапазоне, соответствующие условию, будут отформатированы.

Повторяющиеся значения:

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

Правила отбора первых и последних значений (Top/Bottom Rules)

Отсутствует в Excel 2003

Содержит условия:
Первые 10 элементов, Первые 10%, Последние 10 элементов, Последние 10%, Выше среднего, Ниже среднего
Top 10 Items, Top 10%, Bottom 10 Items, Bottom 10%, Above Average, Below Average

Гистограммы (Data Bars)

Отсутствует в Excel 2003

Сплошная заливка (Solid fill) и Градиентная заливка (Gradient fill) . Отличаются между собой визуализацией бара. Лично мне визуально больше нравится градиентная. Для чего их можно применять: например, в столбце последовательно записаны данные по продажам за месяц и необходимо наглядно отобразить их разницу между собой.

Что важно знать при применении данных условий. Они работают только при применении к диапазону ячеек с числовыми данными. 100%-му заполнению шкалы соответствует максимальное значение среди выделенных ячеек, 1%-му заполнению - ячейка с минимальным значением. Т.е. ячейка с максимальным значением будет заполнена полностью, ячейка с минимальным - едва будет видна полоска бара, а остальные ячейки будут заполнены относительно процентного отношения данных в самой ячейке к показателям минимального и максимального значения всех ячеек. Например, если выделено 4 ячейки с числами: 1, 25, 50 и 100, то ячейка с 1 будет едва заполнена, ячейка с 25 - заполнена на четверть, ячейка с 50 - на половину, а ячейка с 100 - полностью.

Цветовые шкалы (Color Scales)

Отсутствует в Excel 2003

Работает по тому же принципу, что и Гистограммы (Data Bars) : работает на основе числовых значений выделенных ячеек, но закрашивает не часть ячейки методом шкалы, а всю ячейку, но с различной интенсивностью или цветом. Можно создать условие, при котором ячейка с максимальной продажей будет закрашена самым насыщенным цветом, а минимальная - практически незаметно:

или добавить к этому еще различие по цветам:

В этом случае помимо насыщенности цвета значения будут различаться еще и самим цветом. Среди наборов шкал есть разбивка на два и на три цвета. При этом цвет назначается по принципу деления на кол-во цветов: первые 33% одним цветом, от 34% до 66% другим цветом, а оставшиеся - третьим. Если цвета два - то делится по 50%.

Наборы значков (Icon Sets)

Отсутствует в Excel 2003

Служит все для тех же целей, что и шкалы и гистограммы, но имеет менее гибкую систему отображения различий. Отражает различия между значениями ячеек по 2-х, 3-х, 4-х или 5-ти ступенчатой системе. Это значит, что если выбран набор из 3-х значков, то разница между минимальным и максимальным значением будет поделена на 3 и каждая третья часть будет со своим значком. Более наглядно можно увидеть, применив данное условие к числам от 1 до 9:

Для отражения разницы между значениями так же очень хорошо подходят значки в виде мини-гистограмм:

Выделить ячейки для применения условного форматирования
В меню выбрать
- Excel 2003 : Формат (Format) -Условное форматирование (Conditional formatting) - формула;
- Excel 2007-2010 : вкладка Главная (Home) -Условное форматирование (Conditional formatting) -Создать правило (New rule) -Использовать формулу для определения форматируемых ячеек (Use a formula to determine which cells to format)
Вписать в поле необходимую формулу (Сборник формул для условного форматирования)
Выбрать способ форматирования ячеек: цвет заливки, цвет шрифта, формат отображения, границы и т.д.
ОК

Если необходимо выделять форматированием не только конкретную ячейку, удовлетворяющую условию, а всю строку таблицы на основе ячейки одного столбца, то в пункте 1 выделяем не столбец, а всю таблицу, а ссылку на столбец с критерием закрепляем:
= $A1 =МАКС( $A$1:$A$20 )
при выделенном диапазоне A1:F20 (диапазон применения условного форматирования), будет выделена строка A7:F7 , если в ячейке A7 будет максимальное число.
Так же можно применять не к конкретно одному столбцу, а к полностью диапазону. Но в этом случае надо знать принцип смещения ссылок в формулах, чтобы условия применялись именно к нужным ячейкам. Например, если задать условие для диапазона B1:D10 в виде формулы: = B1 A1 , то цветом будут выделены ячейки столбца B, если значение ячейки столбца А в той же строке меньше( B1, B3). При этом если ячейки столбца D меньше ячеек столбца C в той же строке - они тоже будут выделены( D1 , D5 ).

ПОИСК ЯЧЕЕК С УСЛОВНЫМ ФОРМАТИРОВАНИЕМ
Если к одной или нескольким ячейкам на листе применено условное форматирование, можно быстро найти их для копирования, изменения или удаления условного формата.

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

Выделить любую ячейку на листе;
Нажать F5- Выделить (Special) ; или же перейти на вкладку Главная (Home) - группа Редактирование (Editing) - Найти и выделить (Find & Select) - Выделение группы ячеек (Go To Special) ;
В появившемся окне выбрать Условные форматы (Conditional formats) ;
Нажать ОК.

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

Выделить ячейку с необходимым условным форматированием;
Нажать F5- Выделить (Special) ; или же перейти на вкладку Главная (Home) - группа Редактирование (Editing) - Найти и выделить (Find & Select) - Выделение группы ячеек (Go To Special) ;
В появившемся окне выбрать Условные форматы (Conditional formats) ;
Выбрать пункт этих же (Same) в группе Проверка данных (Data validation) ;
Нажать ОК.

РЕДАКТИРОВАНИЕ УСЛОВИЙ УСЛОВНОГО ФОРМАТИРОВАНИЯ
Excel 2003:

Выделить диапазон ячеек, из которых требуется удалить условное форматирование;
Формат (Format) -Условное форматирование (Conditional formatting) ;
Изменить условие и нажать ОК.

Excel 2007-2010:

Выделить диапазон ячеек, таблицу или сводную таблицу, условное форматирование которых требуется изменить;
Вкладка Главная (Home) - группа Стили- Условное форматирование (Conditional formatting) - Управление правилами (Manage Rules) ;
Выбрать необходимое правило, условное форматирование которого необходимо изменить
Нажать кнопку Изменить правило (Edit Rule)

УДАЛЕНИЕ УСЛОВНОГО ФОРМАТИРОВАНИЯ

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

Вкладка Главная (Home) (Home) - группа Стили (Styles) - Условное форматирование (Conditional formatting) - Удалить правила (Clear Rules) - Удалить правила со всего листа (Clear Rules from Entire Sheet) .

Удаление условного форматирования из диапазона ячеек, таблицы или сводной таблицы
Excel 2003:

Выделить диапазон ячеек, из которых требуется удалить условное форматирование;
Формат (Format) -Условное форматирование (Conditional formatting) - кнопка Удалить (Delete) ;
Отметить галочками условное форматирование, которое необходимо удалить и нажать ОК.

Excel 2007-2010:

Выделить диапазон ячеек, таблицу или сводную таблицу, из которых требуется удалить условное форматирование;
Вкладка Главная (Home) - группа Стили- Условное форматирование (Conditional formatting) - Удалить правила (Clear Rules) ;
Выбрать элемент, условное форматирование из которого необходимо удалить: Удалить правила из выделенных ячеек (Clear Rules from Selected Cells) , Удалить правила из этой таблицы (Clear Rules from This Table) или Удалить правила из этой сводной таблицы (Clear Rules from This PivotTable) .

Так же для Excel 2007-2010 можно удалить только определенное правило из указанных ячеек:

Читайте также: