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

Всем привет! Большинству пользователей, когда они просто смотрят на сухие данные в табличном документе наверняка сложно уловить информацию, поскольку таким образом её сложнее воспринимать. Но в табличном редакторе «Майкрософт Эксель» присутствует несколько инструментов для графической визуализации данных, при помощи которых можно значительно упросить способ восприятия информации ячеек и строк из таблицы.

Называется данная функция «Условное форматирование». Итак, сегодня мы в подробностях разберём принцип работы с данным инструментом и расскажем, как сделать вашу таблицу более привлекательной. Давайте приступим.


Наиболее простые методы условного форматирования

Для того, чтобы отформатировать определённый радиус ячеек (наиболее частым вариантом является форматирование целого столбца) необходимо просто выделить эту область и кликнуть по инструменту «Условное форматирование», который располагается во вкладке «Главная» в разделе инструментов «Стили».

Далее вы попадёте во вкладку с настройкой условного форматирования. Здесь вы сможете увидеть три основных параметра. Это:

• Гистограммы;

• Цифровые шкалы;

• Значки.


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

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

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

Также в версиях табличного редактора Excel 2010, 2013 и 2016 присутствует корректная поддержка отрицательных значений гистограммы. А вот в более старых версиях, табличного редактора «Microsoft Excel» (2007 год и старше) такая функция не предусмотрена.


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

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

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

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

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

Если указатель повёрнут вверх, то величина является одной из больших, если влево – то диапазон величины средний, а если вниз, то, соответственно, один из самых меньших.

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



Правила выделения объектов

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

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

Переходим в меню и кликаем по клавише «Правила выделения ячеек». Как вы можете заметить, здесь расположено всего 7 основных правил.

• Больше;

• Меньше;

• Равно;

• Между;

• Дата;

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


Итак, мы будем рассматривать применение данных правил на конкретных примерах. Для начала выделяем необходимое количество ячеек и кликаем по клавише «Больше».

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

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

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

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

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

Теперь, когда мы полностью персонализировали условное форматирование, для сохранения внесённых коррективов кликаем по клавише «ОК».

Как вы можете заметить, теперь нужные ячейки будут обозначены так, как вы указали ранее.

Приблизительно похожий принцип работы и у форматов «Меньше», «Между» и «Равно».

Но здесь присутствует небольшое различие – в случае, если вы выберите формат «Меньше», то выделяться будут только те ячейки, которые имеют меньшее значение, чем указанное вами число.

В случае с «Между» устанавливается числовой интервал, и если ячейка в него входит то она так же выделяется. В случае с «Равно» будут выделятся только ячейки, значение которых равно выставленному вами значению.

Правило «Текст содержит» применяется только к ячейкам, в которых присутствует какой-либо текст.

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

Нетрудно догадаться, что правило «Дата» применяется только к ячейкам, в которых содержится определённая дата.

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

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

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



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

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

Здесь вы можете указать – выделять самые большие или самые меньшие значения относительно всех выделенных ячеек.

Также здесь присутствует возможность «отбирать» ячейки не только относительно порядковых номер, но и «процентовки». Здесь присутствует несколько критериев, относительно которых будут выделяться определённые ячейки. Это:

• Первые 10 элементов;

• Первые 10%;

• Последние 10 элементов;

• Последние 10%;

• Выше среднего;

• Ниже среднего.


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

К примеру, мы выбрали формат «Первые 10 элементов», после перехода во вкладку «Форматировать первые ячейки» у нас присутствует возможность заменить число 10 на другое, к примеру, 7.

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



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

Ранее мы уже говорили, что табличный редактор «Microsoft Excel» располагает всего семёркой базовых правил, которые вы можете задать. Но следует отметить, что при желании у вас присутствует возможность создания собственного пользовательского правила.

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

Также вы можете просто кликнуть по клавише «Создать правило», которая располагается в нижней части списка создания условного форматирования ячеек.


Откроется окно, в котором вам необходимо указать тип правила. Всего их 6. Это:

• Форматировать все ячейки на основании их значений;

• Форматировать только ячейки, которые содержат;

• Форматировать только первые и последние значения;

• Форматировать только значения, которые находятся выше или ниже среднего;

• Форматировать только уникальные или повторяющиеся значения;

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


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

Здесь же, в данном меню вы можете задать параметры обозначение ячеек при помощи тех же инструментов, о которых мы так же ранее рассказывали. После того, как все необходимые коррективы внесены кликните по клавише «ОК» и новое правило будет создано.



Как управлять правилами

Табличный редактор «Microsoft Excel» позволяет пользователю применять одновременно несколько правил к одним и тем же ячейкам. Однако здесь есть небольшой минус – в списке правил будет отображаться только последний использованный вами параметр.

В случае, если вы хотите изменить некоторые параметры уже применённых правил к определённому табличному диапазону, или же просто хотите посмотреть все примененные к нему правила, то вам необходимо выделить диапазон и перейти в раздел «Управление правилами».

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

То есть, если выставленные правила каким-либо образом «конфликтуют» между собой, то будет применено только последнее правило, которое выставлено в данном списке.

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

Присутствует и другой метод. Вы можете просто установить галочку напротив нужного правила в поле «Остановить, если истина».

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

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

Для удаления ненужного правила достаточно просто кликнуть по нему, а потом нажать на клавишу «Удалить правило».

Так же есть и другой метод удаления правил. Для этого переходим в меню с настройкой условного форматирования таблицы и переключаемся в меню «Удалить правила».

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



Заключение

Итак, как вы видите, отформатировать таблицу в табличном редакторе «Microsoft Excel» достаточно просто. Надеемся, что данная статья смогла помочь вам. Всем мир!

Рейтинг:(голосов:1)