Как в Excel сделать заливку чередующихся строк с помощью условного форматирования
При создании таблицы (Вставка → Таблицы → Таблица) ее можно отформатировать, чтобы строки с двумя вариантами заливки чередовались. Благодаря такому форматированию, таблицы получаются более удобочитаемыми.
В этой статье расскажем, как создать в любом диапазоне данных чередующуюся окраску строк с помощью условного форматирования. Этот метод — динамический: если вставлять или удалять строки в области условного форматирования, то распределение заливки также автоматически обновляется.
Подcвечивать максимум (минимум) автоматически — EXCEL СПб
- Выберите диапазон ячеек для форматирования.
- Выполните команду Главная → Условное форматирование → Создать правило. Откроется диалоговое окно Создание правила форматирования.
- В разделе Выберите тип правила укажите вариант Использовать формулу для определения форматируемых ячеек.
- Введите следующую формулу в поле Форматировать значения, для которых следующая формула является истиной: =ОСТАТ(СТРОКА();2)=0 .
Как видим, гистограммы появились в выделенных ячейках столбца. Чем большее числовое значение в ячейках, тем гистограмма длиннее. Кроме того, в версиях Excel 2010, 2013 и 2016 годов, имеется возможность корректного отображения отрицательных значений в гистограмме. А вот, у версии 2007 года такой возможности нет.
Условное форматирование в сводных таблицах
Сначала выделите все ячейки, которые подлежат условному форматированию; в нашем примере С4:С17. Перейдите на вкладку ленты Главная в группу Стили и щелкните на кнопке Условное форматирование (рис. 2).
Рис. 2. Для значений сводной таблицы выберите условное форматирование в виде гистограммы
В области сводной таблицы появятся гистограммы наряду со значениями в поле Сумма по полю Объем продаж2. Чтобы отобразить одну лишь гистограмму, выполните следующие действия.
- Щелкните на кнопке раскрывающегося меню Условное форматирование, и выберите пункт Управление правилами.
- В диалоговом окне Диспетчер правил условного форматирования выберите только что созданное правило Гистограмма и щелкните на кнопке Изменить правило.
- Установите флажок Показывать только столбец (рис. 3).
Рис. 3. Установите флажок Показывать только столбец, чтобы отображать только гистограммы
В следующем списке приведены готовые сценарии условного форматирования:
Чтобы удалить примененное ранее условное форматирование, поместите курсор в сводную таблицу, перейдите на вкладку Главная, щелкните на кнопке Условное форматирование и выберите в раскрывающемся меню команду Удалить правила → Удалить правила из этой сводной таблицы (см. рис. 2).
Создание пользовательских правил условного форматирования
Следует отметить, что в применении условного форматирования вы не ограничены только заранее разработанными сценариями. Вы всегда можете создать собственные правила. Обратите внимание на исходную таблицу, представленную на рис. 5. В ней добавлено вычисляемое поле, определяющее значение выручки за час (подробнее см. Вычисляемые поля и вычисляемые элементы в Excel 2013).
Рис. 6. Диалоговое окно Создание правила форматирования; установки по умолчанию
В этом диалоговом окне определяются ячейки с условным форматированием, задается тип применяемого правила и указываются параметры форматирования. Сначала нужно задать ячейки, в которых будет применяться условное форматирование. У вас небольшой выбор, включающий следующие три варианта:
- к выделенным ячейкам;
- ко всем ячейкам, содержащим значения «Объемпродаж». Условное форматирование применяется ко всем значениям в столбце Объем продаж, а также к промежуточным и общим итогам. Рекомендуется применять этот вариант в ходе анализа данных, для которых определяется среднее, процентное соотношение или другие величины, представляющие различные уровни одной и той же числовой величины.
- ко всем ячейкам, содержащим значения «Объем продаж» для «Рынок сбыта». Условное форматирование применяется ко всем значениям в столбце Объем продаж на уровне Рынок сбыта; при этом исключаются промежуточные и общие итоги. Его лучше всего использовать для анализа отдельных значений.
Названия Объем продаж и Рынок сбыта диалогового окна Создание правила форматирования изменяются от одной таблицы к другой и отражают названия полей, содержащихся в области столбцов и активных элементов данных.
В рассматриваемом примере третий вариант кажется наиболее удачным, поэтому установите переключатель ко всем ячейкам, содержащим значения «Объем продаж» для «Рынок сбыта» (рис. 7). В разделе Выберите тип правила укажите правило, согласно которому будет применяться условное форматирование:
Рис. 7. Диалоговое окно Создание правила форматирования; установки по умолчанию
Учтите, что гистограммы, цветовые шкалы и наборы значков применяются только в случае, если выделенные ячейки форматируются на основе введенных в них значений. Это означает, что для использования указанных индикаторов необходимо установить первый переключатель, Форматировать все ячейки на основании их значений.
В выбранном нами сценарии будем обозначать проблемные области с помощью набора значков. Поэтому в качестве типа форматирования нужно выбрать параметр Форматировать все ячейки на основании их значений.
Рис. 8. Условное форматирование применено к сводной таблице
Теперь примените такое же условное форматирование к полю Средняя выручка за час (рис. 9).
Рис. 9. Условное форматирование позволяет добиться весьма познавательных и важных результатов
[1] Заметка написана на основе книги Джелен, Александер. Сводные таблицы в Microsoft Excel 2013. Глава 6.Как сделать условное форматирование в excel 2010 формула?
Изучая статистику по коронавирусу (актуальному на момент написания статьи), я зашел на информационную страницу Яндекса с данными по заболеваниям и выздоровлениям и обнаружил там довольно интересную диаграмму:
