Применение условного форматирования в Excel 2007 -6

Продолжение: стр. № 6

Форматирование значений выше или ниже среднего значения

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

Быстрое форматирование

1. Выделите одну или несколько ячеек в диапа- зоне, таблице или отчете сводной таблицы.
2. На вкладке Главная в группе Стили щелкните стрелку рядом с кнопкой Условное формати- рование, а затем — элемент Правила отбора первых и последних значений.

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

Совет. Изменить способ определения области для полей из области "Значения" в отчете сводной таблицы можно с помощью переключателя Применить правило форматирования к.

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

1. Выделите одну или несколько ячеек в диапазоне, таблице или отчете сводной таблицы.
2. На вкладке Начальная страница в группе Стили щелкните стрелку рядом с пунктом Условное форматирование, а затем уже выберите пункт Управление правилами.

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

3. Выполните одно из указанных ниже действий.
• Для добавления условного форматирования нажмите кнопку Создать пра- вило.

Появится диалоговое окно Создание правила форматирования.

• Для изменения условного форматирования выполните указанные ниже действия.
1. Убедитесь, что в поле со списком Показать правила форматирования для выбран соответствующий лист, таблица или отчет сводной таблицы.
2. При необходимости можно изменить диапазон ячеек. Для этого нажмите кнопку Свернуть диалоговое окно  в поле Применяется к, чтобы временно скрыть диалоговое окно, а затем выделите новый диапазон ячеек на листе и нажмите кнопку Развернуть диалоговое окно .
3. Выберите правило, а затем нажмите кнопку Изменить правило.

Появится диалоговое окно Изменение правила форматирования.

4. В разделе Применить правило к выберите один из следующих вариантов, чтобы изменить выбор полей в области значений отчета сводной таблицы:
• только эти ячейки, чтобы выбрать поля по выделению;
• все ячейки <поле значения> с теми же полями, чтобы выбрать поля по соответствующему полю;
• все ячейки <поле значения>, чтобы выбрать поля по полю значения.
5. В группе Выберите тип правила выберите пункт Форматирование только тех значений, которые выше или ниже среднего значения.
6. В группе Измените описание правила в поле со списком Форматировать значения, для которых выполняется следующее условие выполните одно из указанных ниже действий.
• Для форматирования ячеек, значения в которых больше или меньше среднего значения для всех ячеек в диапазоне, выберите пункт Выше или Ниже.
• Для форматирования ячеек, значения в которых больше или меньше одного, двух или трех значений стандартного отклонения для всех ячеек в диапазоне, выберите стандартное отклонение.
7. Кроме того, можно настроить применение формата к полям в области "Значения" отчета сводной таблицы, область которых определяется соответствующим полем.

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

• столбец и поле его родительской строки (вариант каждой группы столбцов);
• строка и поле ее родительского столбца (вариант каждой группы строк).

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

Можно выбрать несколько форматов. Выбранные форматы отображаются в поле Просмотр.

Форматирование уникальных значений или дубликатов

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

Быстрое форматирование

1. Выделите одну или несколько ячеек в диапазоне, таблице или отчете сводной таблицы.
2. На вкладке Главная в группе Стили щелкните стрелку рядом с кнопкой Услов- ное форматирование, а затем — элемент Правила выделения ячеек.

3. Выберите вариант повторяющиеся значения.
4. Введите значения, которые необходимо использовать, и выберите формат.

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

1. Выделите одну или несколько ячеек в диапазоне, таблице или отчете сводной таблицы.
2. На вкладке Начальная страница в группе Стили щелкните стрелку рядом с пунктом Условное форматирование, а затем выберите пункт Управление пра- вилами.

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

3. Выполните одно из указанных ниже действий.
• Для добавления условного форматирования нажмите кнопку Создать пра- вило.

Появится диалоговое окно Создание правила форматирования.

• Для изменения условного форматирования выполните указанные ниже действия.
1. Убедитесь, что в поле со списком Показать правила форматирования для выбран соответствующий лист или таблица.
2. При необходимости можно изменить диапазон ячеек. Для этого нажмите кнопку Свернуть диалоговое окно  в поле Применяется к, чтобы временно скрыть диалоговое окно, а затем выделите новый диапазон ячеек на листе и нажмите кнопку Развернуть диалоговое окно .
3. Выберите правило, а затем нажмите кнопку Изменить правило.

Появится диалоговое окно Изменение правила форматирования.

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

Можно выбрать несколько форматов. Выбранные форматы отображаются в поле Просмотр.

Выбор ячеек для форматирования с помощью формулы

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

1. На вкладке Главная в группе Стили щелкните стрелку рядом с кнопкой Условное форматирование и выберите пункт Управление правилами.

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

2. Выполните одно из указанных ниже действий.
• Для добавления условного форматирования нажмите кнопку Создать пра- вило.

Появится диалоговое окно Создание правила форматирования.

• Для изменения условного форматирования выполните указанные ниже действия.
1. Убедитесь, что в поле со списком Показать правила форматирования для выбран соответствующий лист, таблица или отчет сводной таблицы.
2. При необходимости можно изменить диапазон ячеек. Для этого нажмите кнопку Свернуть диалоговое окно  в поле Применяется к, чтобы временно скрыть диалоговое окно, а затем выделите новый диапазон ячеек на листе или других листах и нажмите кнопку Развернуть диалоговое окно .
3. Выберите правило, а затем нажмите кнопку Изменить правило.

Появится диалоговое окно Изменение правила форматирования.

3. В разделе Применить правило к выберите один из следующих вариантов, чтобы изменить выбор полей в области значений отчета сводной таблицы:
• к выделенным ячейкам, чтобы выбрать ячейки по выделению;
• ко всем ячейкам, содержащим значения <поле значения>, чтобы выбрать поля по соответствующему полю;
• ко всем ячейкам, содержащим значения <поле значения> для <строка>, чтобы выбрать поля по полю значения.
4. В группе Выберите тип правила нажмите кнопку Использовать формулу для определения форматируемых ячеек.

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

Формула должна начинаться со знака равенства (=) и возвращать логическое значение ИСТИНА (1) или ЛОЖЬ (0).

Пример 1. Использование одного правила условного форматирования с несколькими условиями и ссылками на ячейки вне диапазона ячеек. 

В этой формуле одно правило условного форматирования с несколькими условиями, примененными к диапазону ячеек A1:A5, форматирует ячейки зеленым цветом, если среднее значение для всех ячеек в диапазоне больше значения в ячейке F1 и минимальное значение в любой ячейке в этом диапазоне больше или равно значению в ячейке G1. Ячейки F1 и G1 находятся за пределами диапазона ячеек, к которым применяется условное форматирование. Функция И объединяет несколько условий, а функции СРЗНАЧ и МИН используются для вычисления значений.

Пример 2. Заливка каждой второй строки с помощью функций ОСТАТ и СТРОКА 

Эта формула выполняет заливку синим цветом каждой второй строки в диапазоне. Функция ОСТАТ возвращает остаток от деления числа (первый аргумент) на делитель (второй аргумент). Функция СТРОКА возвращает номер текущей строки. При делении номера текущей строки на 2 в остатке всегда будет получаться 0 для четных чисел и 1 для нечетных чисел. Поскольку 0 — это ЛОЖЬ, а 1 — ИСТИНА, к каждой нечетной строке будет применяться форматирование.

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

Можно выбрать несколько форматов. Выбранные форматы отображаются в поле Просмотр.

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

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

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

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

1. Щелкните любую ячейку без условного форматирования.
2. На вкладке Главная в группе Редактирование щелкните стрелку рядом с кнопкой Найти и выделить, а затем выберите пункт Условное форматирование.

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

1. Щелкните ячейку с условным форматированием, которое необходимо найти.
2. На вкладке Главная в группе Редактирование щелкните стрелку рядом с кнопкой Найти и выделить, а затем выберите пункт Выделение группы ячеек.
3. Выберите пункт Условные форматы.
4. Выберите пункт этих же в группе Проверка данных.

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

• Выполните одну из описанных ниже процедур.

На листе 

1. На вкладке Главная в группе Стили щелкните стрелку рядом с кнопкой Условное форматирование и выберите пункт Удалить правила.
2. Нажмите кнопку Весь лист.

В диапазоне ячеек, таблице или сводной таблице 

1. Выделите диапазон ячеек, таблицу или сводную таблицу, для которых необходимо удалить условное форматирование.
2. На вкладке Главная в группе Стили щелкните стрелку рядом с кнопкой Условное форматирование и выберите пункт Удалить правила.
3. В зависимости от того, какой элемент выделен, нажмите кнопку Выделенные ячейки, Эта таблица или Эта сводная таблица.

Загрузка...