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

Являясь мощным инструментом для работы с электронными таблицами, программа 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 еще более удобной и мощной программой для работы с таблицами. И пользователи, знающие о плюсах этого инструмента, несомненно, работают в программе с большей легкостью и удовольствием.

Важно: Часть содержимого этого раздела может быть неприменима к некоторым языкам.

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

Гистограммы

Цветовые шкалы

Наборы значков

Форматирование ячеек с использованием гистограмм

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

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

На вкладке Главная щелкните Условное форматирование.

Выберите пункт Гистограммы, а затем выберите градиентную или сплошную заливку.

Совет: При увеличении ширины столбца с гистограммой разница между значениями ячеек становится более заметной.

Форматирование ячеек с использованием цветовых шкал

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

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

На вкладке Главная щелкните Условное форматирование.

Наведите указатель на элемент Цветовые шкалы и выберите нужную шкалу.

Верхний цвет означает наибольшие значения, средний цвет (при его наличии) — средние значения, а нижний — наименьшие.

Форматирование ячеек с использованием наборов значков

Наборы значков используются для представления данных в категориях числом от трех до пяти, разделенных пороговыми значениями. Каждый значок соответствует диапазону значений, а каждая ячейка обозначается значком, представляющим этот диапазон. Например, в наборе из трех значков один значок используется для выделения всех значений, которые больше или равны 67 %, другой значок — для значений меньше 67 % и больше или равных 33 %, а третий — для значений меньше 33 %.

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

На вкладке Главная щелкните Условное форматирование.

Наведите указатель на элемент Наборы значков и выберите набор.

Совет: Наборы значков можно сочетать с другими элементами условного форматирования.

Форматирование ячеек с использованием гистограмм

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

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

На вкладке Главная в разделе Формат щелкните Условное форматирование.

Выберите пункт Гистограммы, а затем выберите градиентную или сплошную заливку.

Совет: При увеличении ширины столбца с гистограммой разница между значениями ячеек становится более заметной.

Форматирование ячеек с использованием цветовых шкал

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

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

На вкладке Главная в разделе Формат щелкните Условное форматирование.

Наведите указатель на элемент Цветовые шкалы и выберите нужную шкалу.

Верхний цвет означает наибольшие значения, средний цвет (при его наличии) — средние значения, а нижний — наименьшие.

Форматирование ячеек с использованием наборов значков

Наборы значков используются для представления данных в категориях числом от трех до пяти, разделенных пороговыми значениями. Каждый значок соответствует диапазону значений, а каждая ячейка обозначается значком, представляющим этот диапазон. Например, в наборе из трех значков один значок используется для выделения всех значений, которые больше или равны 67 %, другой значок — для значений меньше 67 % и больше или равных 33 %, а третий — для значений меньше 33 %.

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

На вкладке Главная в разделе Формат щелкните Условное форматирование.

Наведите указатель на элемент Наборы значков и выберите набор.

Совет: Наборы значков можно сочетать с другими элементами условного форматирования.

Рассмотрим правило Условного форматирования — Набор значков.

Правило Условного форматирования под названием Набор значков упрощает сравнение значений в диапазоне ячеек.

Поясним на примере (см. файл примера ).

Пусть имеется несколько значений в столбце А.

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

Для это выделите ячейки A7:A15 и выберите в меню Условного форматирования набор значков "4 оценки" (см. 1-й рисунок к статье).

Меньшим значениям будут сопоставлены значки с одной закрашенной полоской (0 и 24), а наибольшим — с 4-мя (100, 80, 77). Теперь разберем подробнее, почему значки были присвоены значениям именно так, а не иначе.

Откроем правило Условного форматирования (выделите любую ячейку со значением из нашего диапазона и в меню Главная/ Условное форматирование/ Управление правилами дважды кликните на правило).

Т.е. если значение больше или равно 75%, то ему сопоставляется значок с 4-мя закрашенными полосками, если меньше 25% — то с одной полоской. Что это за 75% и 25%?

Для простоты наши значения в столбце А введены от 0 до 100. Т.е. если значение больше или равно 75, то ему сопоставляется значок с 4-мя закрашенными полосками, если меньше 25 — то с одной полоской. Как видно на 2-м рисунке сверху, в этом случае относительная величина значения, выраженная в % совпадает с самим значением (столбец В). Относительная величина рассчитывается по формуле =(A7-МИН($A$7:$A$15))/$P$5 , где в Р5 находится "длина" диапазона — разница максимального и минимального значения (=100-0=100).

Если в нашем диапазоне вместо 0 мы введем значение 31, то значки изменятся.

Теперь минимальным значением станет 24 (его относительная величина =0%), значение 31 будет соответствовать 9,2% от длины диапазона (=100-24=76), т.е. (31-24)/76=9,2%.

Как и раньше, тем значениям, у которых их относительная величина (см. столбец В) больше 75%, будет соспоставлен значок с 4-мя закрашенными полосками (теперь это только значение 100). Значениям 31, 24, 25, 30 будет сопоставлен значок с одной полоской, т.к. их относительная величина Совет: о базовых настройках Условного орматирования рассказано в статье Условное форматирование в MS EXCEL.