Условное форматирование в Excel. Примеры, цветовая шкала, наборы значков

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

Работа с большими таблицами Excel

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

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

Для чего нужно условное форматирование ячеек в Excel?

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

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

Где же искать условное форматирование?

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

В старой версии программы Excel 2003 необходимо зайти в меню «Формат» и выбрать строчку «Условное форматирование». Для последующих версий Excel 2007, 2010 и 2013 в верхней панели во вкладке «Главная» следует отыскать раздел «Стили», в котором и присутствует меню «Условное форматирование». Кликнув на него, пользователь увидит весь перечень возможных операций.

Виды условного форматирования

Во всплывающем меню данного инструмента показано, какие существуют основные правила условного форматирования в Excel.

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

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

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

Также условное форматирование в Excel позволяет создать пользователю собственные правила или удалить их, а с помощью пункта меню «Управление правилами» есть возможность просмотреть все правила, созданные на выбранном листе, и совершать с ними разные манипуляции.

Новшества условного форматирования в более поздних версиях Excel

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

Условное форматирование с помощью гистограммы

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

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

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

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

Условное форматирование цветовой шкалой

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

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

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

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

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

Например, используя в работе набор из трех флажков, можно получить следующую картину: красные флажки будут стоять рядом с маленькими значениями (то есть менее 33%), желтые рядом со средними значениями (находящимися в диапазоне между 33 и 67%), а зеленые – с наибольшими значениями (более 67%).

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

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

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

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

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

Использование формул в условном форматировании

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

Для этого необходимо выбрать в меню условного форматирования пункт «Создать правило», затем строку «Использовать формулу» для определения форматируемых ячеек».

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

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

Пример использования формулы в условном форматировании

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

Для этого следует зайти в вышеуказанное меню и в строку формулы ввести следующее: = B5>C5. Здесь B – это столбец марта, С – столбец апреля. Записанное условие можно будет протянуть на весь диапазон, в котором собраны данные. Его можно также протянуть дальше по столбцам при добавлении новых месяцев.

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