Для каких типов данных можно использовать указанный способ условного форматирования excel 2010

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

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

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

Приведем простейший пример сводной таблицы, описывающий объемы продаж какого-то товара по регионам:

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

Самый простой вариант – использовать цветовые шкалы. Для этого выделяем поле «Объем продаж», охватывая все периоды. Осталось открыть вкладку «Главная», где нажимаем кнопку «Условное форматирование» (если вы вдруг используете английскую версию, то данная функция называется «Conditional Formatting»). Наведите курсор на «Гистограммы».

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

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

Итак – готовые сценарии, способные помочь в большинстве ситуаций:

— первые 10 элементов;

— последние 10 элементов;

Удаление уже используемого условного форматирования в Excel 2010 происходит по следующей схеме: в сводной таблице переходим на вкладку «Главная», нажимаем на «Условное форматирование», далее «Стили», и в выпадающем меню используем команду «Удалить правила» — «Удалить правила из этой сводной таблицы» (в английском варианте – «Clear Rules» и «Clear Rules from this PivotTable»).

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

Данная таблица – усложненный вариант первой, так что сразу переходим к примеру. Давайте отследим объем продаж и выручку за час. Мы будем использовать условное форматирование Excel 2010 для ускорения поиска совпадений и различий. Выделяем «Объем продаж». Далее по стандартной процедуре активируем сценарий («Главная» — «Условное форматирование»), но выбираем не готовый вариант, а функцию «Создать правило» (или «New Rule»).

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

— выделенные («Selected Cells»);

— входящие в столбец «Объем продаж» («All Cells Showing «Sales_Amount» Values»), включая промежуточные и общие итоги. Данный вариант, кстати, хорошо подходит для анализа тех данных, которые требуют определения среднего, процентного соотношения или иными величинами, так или иначе являющимися разными уровнями одной величины;

— входящие в категорию «Объем продаж» только для «Рынка сбыта» («All Cells Showing «Sales_Amount» Values for «Market»»). Данный вариант полностью исключает общие и промежуточные итоги, что удобно для анализа некоторых отдельных значений.

Отметим, что команды «Объем продаж», «Рынок сбыта» при создании правил меняются в зависимости от имеющихся рабочих таблиц.

По нашему примеру самым выгодным является вариант «3», поэтому используется такой вариант:

При выборе правила (раздел «Выбрать правило» или «Select a Rule Туре») указываем именно то, которое и отвечает нашим требованиям.

— «Форматирование ячеек на основании значений» («Format All Cells Based on Their Values»). Используется для форматирования ячеек, которые соответствуют используемому диапазону значений. Лучше всего подходит для определения самых разных отклонений, если приходиться работать с огромным набором данных.

— «Форматирование ячеек содержащих» («Format Only Cells That Contain»). Форматирует ячейки, отвечающие подходящим условиям. В данном случае сравнение значений форматированных ячеек с обычными не происходит. Используется для сравнения общего набора данных с указанной ранее характеристикой.

— «Форматирование первых и последних значений» («Format Only Top or Bottom Ranked Values»).

— «Форматировать значения ниже или выше среднего» («Format Only Values That Are Above or Below the Average»).

— «Использовать формулу определения форматируемых ячеек» («Use a Formula to Determine Which Cells to Format»). Здесь уже условия условного форматирования опираются на формулу, заданную самим пользователем. Если значение ячейки (из подставленных в формулу) приходит со значением «true», то к ячейке применяют форматирование. В случае со значением «false» форматирование не применяется.

Применение гистограмм, наборов значков и цветовых шкал возможно только тогда, когда форматирование выделенных ячеек происходит на основании значений, занесенных в них. Для этого устанавливаем первый переключатель на «Форматирование всех ячеек на основании значений» («Format All Cells Based on Their Values»). Для обозначения проблемных областей можно использовать набор значков, что также хорошо подходит для данного сценария.

Ну и осталось определить точные параметры нашего форматирования. Здесь пригодится раздел «Изменения описания правила» («Edit the Ruie Description»). Для добавления значков в проблемные ячейки, мы используем выпадающее меню «Стиль формата» («Format Style») и выбираем «Наборы значков» («Icon Sets»).

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

При такой конфигурации Excel самостоятельно будет добавлять в ячейки значки, при этом следуя функции:

Источник

Применение условного форматирования к диапазонам Excel

Библиотека JavaScript Excel предоставляет API для применения условного форматирования к диапазонам данных в книгах. Эта функция упрощает визуальный анализ больших наборов данных. Форматирование также динамически обновляется с учетом изменений в диапазоне.

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

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

Свойство Range.conditionalFormats — это коллекция объектов ConditionalFormat, применяемых к диапазону. Объект ConditionalFormat содержит несколько свойств, определяющих применяемый формат на основе ConditionalFormatType.

• cellValue
• colorScale
• custom
• dataBar
• iconSet
• preset
• textComparison
• topBottom

У каждого из этих свойств форматирования есть соответствующий вариант *OrNullObject . Дополнительные статьи об этом шаблоне в разделе * Методы OrNullObject.

Для объекта ConditionalFormat можно установить только один тип формата. Это определено свойством type , которое является значением перечисления объекта ConditionalFormatType. Параметр type устанавливается при добавлении условного форматирования к диапазону.

Создание правил условного форматирования

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

Значение ячейки

При условном форматировании значения ячейки применяется пользовательский формат на основе результатов одной или двух формул в ConditionalCellValueRule. Свойство operator является оператором ConditionalCellValueOperator, который определяет, как итоговое выражение связано с форматированием.

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

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

При условном форматировании с использованием цветовой шкалы применяется цветовой градиент в диапазоне данных. Свойство criteria в ColorScaleConditionalFormat определяет три точки ConditionalColorScaleCriterion: minimum , maximum и (при желании) midpoint . У каждой точки условия есть три свойства:

• color — HTML-код цвета для конечной точки.
• formula — число или формула, представляющая значение конечной точки. Оно будет равным null , если type имеет значение lowestValue или highestValue .
• type — способ оценки формулы. highestValue и lowestValue относятся к значениям в форматируемом диапазоне.

В приведенном ниже примере показан диапазон, окрашенный с переходом от синего к желтому и красному цвету. Обратите внимание, что minimum и maximum являются минимальным и максимальным значением соответственно, и для них используются формулы null . Для значения midpoint используется тип percentage с формулой «=50» , чтобы самая желтая ячейка соответствовала среднему значению.

Пользовательское

При пользовательском условном форматировании применяется пользовательский формат к ячейкам на основе формулы произвольной сложности. Объект ConditionalFormatRule позволяет определять формулу в разных нотациях:

• formula — стандартная нотация.
• formulaLocal — Локализовано на основе языка пользователя.
• formulaR1C1 — нотация R1C1.

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

Гистограмма

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

В приведенном ниже примере используется форматирование с помощью гистограмм с заполнением слева направо.

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

При условном форматировании с набором значков используются значки Excel для выделения ячеек. Свойство criteria — это массив объекта ConditionalIconCriterion, определяющий добавляемый символ и условия для добавления. Этот массив автоматически заполняется элементами условия со свойствами по умолчанию. Отдельные свойства не могут быть перезаписаны. Вместо этого необходимо заменить весь объект условия.

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

Готовые условия

При условном форматировании с готовыми условиями применяется пользовательский формат к диапазону на основе выбранного стандартного правила. Эти правила определяются с помощью ConditionalFormatPresetCriterion в ConditionalPresetCriteriaRule.

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

Сравнение текста

При условном форматировании со сравнением текста используется сравнение строк в качестве условия. Свойство rule является объектом ConditionalTextComparisonRule, определяющим строку для сравнения с ячейкой и оператор для указания типа сравнения.

В следующем примере форматы цвета шрифта красный, когда текст ячейки содержит «Задержки».

Верхнее или нижнее значение

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

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

Разные форматирования и приоритет

К диапазону можно применять несколько типов условного форматирования. Если форматы содержат конфликтующие элементы, например разный цвет шрифта, только один формат применяет этот конкретный элемент. Приоритет определяется свойством ConditionalFormat.priority . Приоритет — это число (равное индексу в ConditionalFormatCollection ), которое можно установить при создании формата. Чем ниже значение priority , тем выше приоритет формата.

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

Взаимоисключающие условные форматирования

Свойство stopIfTrue объекта ConditionalFormat не позволяет применять к диапазону условное форматирование с более низким приоритетом. Если при сопоставлении с диапазоном применяется условное форматирование со свойством stopIfTrue === true , последующие условные форматирования не применяются, даже если их элементы не вступают в противоречие.

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

Источник