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

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

Инструмент “Условное форматирование” находится на главной странице в разделе “Стили”.

При нажатии на стрелочку справа открывается меню для условий форматирования.

Выделите диапазон значений. Откройте меню “Условного форматирования”. Выберите то правило, которое нужно задать.

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

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

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

Если при создании собственного правила условного форматирования отсутствуют нужные варианты, можно использовать логическую формулу, чтобы задать условия форматирования. Например, можно сравнить значения в выбранных ячейках с результатом, возвращенным функцией, или оценить данные в ячейках за пределами выделенного диапазона, которые могут находиться на другом листе этой же книги. Формула должна возвращать значение Истина или Ложь (1 или 0), но вы можете использовать условную логику для объединения набора соответствующих условных форматов, таких как различные цвета для каждого из небольших наборов текстовых значений.

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

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

1. Выполните одно из указанных ниже действий.

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

2. В разделе Применить правило выполните одно из следующих действий, чтобы изменить выбор полей в области значений отчета сводной таблицы:

• Чтобы определить область по выделенному фрагменту, щелкните Выделенные ячейки.
• Чтобы определить область по соответствующему полю, щелкните по всем ячейкам, содержащим значения <поле значения>.
• Чтобы определить область по полю "Значение" , выберите ко всем ячейкам, содержащим значения <поле значения> для <строка>.

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

1. В группе Измените описание правила введите формулу в поле со списком Форматировать значения, для которых следующая формула является истинной.
2. Формула должна начинаться со знака равенства (=) и возвращать логическое значение ИСТИНА (1) или ЛОЖЬ (0).
3. Нажмите кнопку Формат для отображения диалогового окна Формат ячеек.
4. Выберите формат чисел, шрифта, рамки или заполнения, который необходимо применять, если значение в ячейке соответствует условию, а затем нажмите кнопку ОК.

Для закрепления информации предлагаем выполнить задание.

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

Задание:

1.Скопируйте на Лист 3 таблицу и создайте правило форматирования к столбцу "Количество" вашей таблицы.

Тип правила выберите "Форматировать все ячейки на основании их значений".

В описании правил укажите:

Стиль формата: набор значков

Стиль значка: выберите 1-й вариант светофора

Тип: Число

Зеленый значок присвойте числам значение которых >=40

Желтый значок числам, которые >=30

Красный значок числам <30

2.Создайте правило форматирования к столбцу "Цена за шт.".

Тип правила выберите "Форматировать все ячейки на основании их значений".

В описании правил укажите:

Стиль формата: трехцветная шкала

Тип: во всех трех ячейках - Число

Минимальное значение укажите 3000, среднее значение 10000,

максимальное значение 25000.

Цвета выберите красный - минимальное значение, желтый - среднее значение, зеленый - максимальное значение.

Должна получиться следующая таблица:

* В некоторых роликах вы услышите о том, что информация относится к Microsoft Excel 2010г., эти же функции также работают и в версиях 2013 и 2016 гг.