Условное форматирование Excel. Чтобы изменить выравнивание текста по вертикали

Элен Брэдли объясняет некоторые различия в форматировании сводных таблиц в Excel 2010.

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

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

Как работает условное форматирование

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

Как применить условное форматирование к сводной таблице

Мы рассмотрим условное форматирование на примере сводной таблицы, в которой представлен график приема 4-х ветеринаров за год. В таблице отображается число клиентов с разбивкой по кварталам и по месту приема (ферма или хирургия).

Чтобы сделать данные более наглядными, я выбираю значения Farm (Ферма) и Surgery (Хирургия) первого ветеринара с фамилией Browning , т.е. ячейки от B6 до E7 . Выделив этот диапазон, я захожу на вкладку Home (Главная), нажимаю Conditional Formatting > Data Bars (Условное форматирование > Гистограммы) и выбираю, какой использовать цвет. Эти действия форматируют выбранный диапазон таким образом, что в каждой ячейке появляется гистограмма, которая показывает относительное количество клиентов в каждом квартале и для каждого места приема.

На следующем рисунке мы применяем формат Data Bars (Гистограмма) к первому диапазону данных:

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

Таким образом, правило будет применено к аналогичным данным всех остальных ветеринаров в сводной таблице, без необходимости применять это правило к каждому диапазону по отдельности. Эти же опции Вы сможете найти, если решите создать новое правило форматирования в диалоговом окне New Formatting Rule (Создание правила форматирования).

На этом рисунке показано, как применить одинаковое условное форматирование ко всем данным одного типа в нашей сводной таблице:

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

Гистограммы изменяются, когда меняются данные в сводной таблице, длина гистограммы зависит от данных во всех видимых ячейках:

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

Как и в прошлый раз появляется иконка Formatting Options (Параметры форматирования), при помощи которой мы сможем применить это же условное форматирование к итоговым данным по клиентам каждого ветеринара.

Здесь Вы видите гистограммы другого цвета в ячейках с итоговыми значениями, которые можно наглядно сравнить друг с другом:

Другие параметры

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

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

Для этого выделим диапазон B6:E6 , перейдем Conditional Formatting > Data Bars (Условное форматирование > Гистограммы) и выберем зелёный цвет для гистограмм. Далее в параметрах форматирования выберем второй вариант (см. рисунок ниже). Теперь условное форматирование охватывает, как итоговые, так и общие итоговые (Grand Total) значения, которые, как и все данные в сводной таблице, являются средними значениями. Поэтому не будет нарушением сравнить их одинаковым образом.

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

Перемещаем данные

Вернёмся к нашей исходной сводной таблице и начнем перемещать данные. Делая это, Вы заметите, что форматирование сохраняется на нужных местах. Мы передвинули поле Office (Место приема) в область Report Filter (Фильтры) и перенесли поле Quarter (Квартал) в область Row Labels (Строки), и при этом все фиолетовые гистограммы остались на своих местах.

Даже если структура таблицы изменена и поля передвинуты, условное форматирование сохраняет свое положение:

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

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

Мы создадим отдельное правило условного форматирования для квартальных итогов каждого ветеринара, то есть нам нужно выделить и применить форматирование к ячейкам D7:D10 (потом D12:D15, потом D17:D19 и так далее). Затем используем правило Conditional Formatting > Top 10 Items (Условное форматирование > Первые 10 элементов), устанавливаем условие для 1 ячейки , формат оставляем предложенный по умолчанию. Вы можете либо скопировать это правило в соседний диапазон, либо создать для каждого индивидуально.

Чтобы скопировать форматирование, выделите одну или несколько ячеек с нужным форматом и нажмите Copy (Копировать). Затем выделите диапазон, в который нужно скопировать форматирование, и на вкладке Home (Главная) выберите Paste Special > Formats (Специальная вставка > Форматы).

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

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

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

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

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

Условное форматирование месячной записи данных высокой температуры для разных мест, цвета интуитивно соответствуют значениям (значения более высокой температуры - оранжевые и красные, значения низкой температуры - желтые и зеленые)

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

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

Скачивание образца книги

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

Форматирование ячеек с помощью двухцветной шкалы

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

Совет: ЕСТЬ или ЕСЛИОШИБКА для возврата значения, отличного от ошибки.

Быстрое форматирование

Параметры форматирования

Расширенное форматирование

На вкладке Главная в группе Стили и выберите пункт Управление правилами . Откроется диалоговое окно .

Создать правило . Откроется диалоговое окно .
1. Свернуть диалоговое окно в поле Применяется к Развернуть диалоговое окно .
  
  Изменить правило . Откроется диалоговое окно .

В разделе Применить правило

к выделенным ячейкам , чтобы выбрать ячейки по выделению;

<метка значения> , чтобы выбрать ячейки с определенной меткой.

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

В разделе Выберите тип правила выберите пункт (по умолчанию).

В группе Измените описание правила в поле со списком Стиль формата выберите пункт Двухцветная шкала .

В полях Тип параметров Минимальное значение и Максимальное значение выберите их типы.

Выберите минимальное значение и максимальное значение .

В этом случае не вводите минимальное и максимальное значения .

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

Введите минимальное и максимальное значения .

Форматирование процентиля. Выберите элемент Процентиль Минимальное значение и Максимальное значение

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

Выберите элемент Формула , а затем введите минимальное и максимальное значения.
- Примечания:
  - Убедитесь, что минимальное значение меньше, чем максимальное .
    
    Для параметров Минимальное значение и Максимальное значение минимального значения и процентный тип для максимального значения .

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

Другие цвета . Выбранная цветовая шкала появится в окне предварительного просмотра .

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

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

Быстрое форматирование

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

Расширенное форматирование

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

На вкладке Главная в группе Стили щелкните стрелку рядом с кнопкой и выберите пункт Управление правилами . Откроется диалоговое окно Диспетчер правил условного форматирования .

Выполните одно из указанных ниже действий.

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

Для изменения условного форматирования выполните указанные ниже действия.
1. Убедитесь, что в поле со списком Показать правила форматирования для выбран соответствующий лист, таблица или отчет сводной таблицы.
  
  При необходимости вы можете изменить диапазон ячеек. Для этого нажмите кнопку Свернуть диалоговое окно в поле Применяется к , чтобы временно скрыть диалоговое окно. Затем выделите новый диапазон ячеек на листе и нажмите кнопку Развернуть диалоговое окно .
  
  Выберите правило, а затем нажмите кнопку Изменить правило . Откроется диалоговое окно Изменение правила форматирования .

В разделе Применить правило выберите один из следующих вариантов, чтобы изменить выбор полей в области значений отчета сводной таблицы:

Выберите только эти ячейки ;

Выберите все ячейки <поле значения> с теми же полями ;

Выберите все ячейки <поле значения> .

В группе Выберите тип правила выберите пункт Форматировать все ячейки на основании их значений .

В группе Измените описание правила в поле со списком Стиль формата выберите пункт Трехцветная шкала .

Выберите тип параметров Минимальное значение , Среднее значение и Максимальное значение

Форматирование минимального и максимального значений. Выберите элемент Среднее значение .

В этом случае не вводите минимальное и максимальное значения .

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

Форматирование процентного значения. Выберите элемент Процент , а затем введите минимальное , среднее и максимальное значения. Допустимыми являются значения от 0 (нуля) до 100. Не вводите знак процента.

Форматирование процентиля. Выберите элемент Процентиль , а затем введите Минимальное значение , Среднее значение и Максимальное значение .

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

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

Формула должна возвращать число, дату или время. Начинайте ввод формулы со знака равенства (= ). Недопустимая формула не позволит применить форматирование. Проверьте формулу, чтобы убедиться, что она не возвращает значение ошибки.

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

Для выбора цветовой шкалы Минимальное значение , Среднее значение и Максимальное значение щелкните элемент Цвет для каждого из них, а затем выберите цвет.

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

Выбранная цветовая шкала появится в окне предварительного просмотра .

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

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

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

Совет: Если какая-либо из ячеек в диапазоне содержит формулу, возвращающую ошибку, условное форматирование к ней не применяется. Чтобы гарантировать применение условного форматирования к этим ячейкам, воспользуйтесь функцией ЕСТЬ или ЕСЛИОШИБКА

Быстрое форматирование

Расширенное форматирование

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

Выполните одно из указанных ниже действий.

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

Для изменения условного форматирования выполните указанные ниже действия.
1. Убедитесь, что в поле со списком Показать правила форматирования для выбран соответствующий лист, таблица или отчет сводной таблицы.
  
  При необходимости вы можете изменить диапазон ячеек. Для этого нажмите кнопку Свернуть диалоговое окно в поле Применяется к , чтобы временно скрыть диалоговое окно. Затем выделите новый диапазон ячеек на листе и нажмите кнопку Развернуть диалоговое окно .
  
  Выберите правило, а затем нажмите кнопку Изменить правило . Откроется диалоговое окно Изменение правила форматирования .

чтобы выбрать поля по выделению, выберите только эти ячейки ;

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

чтобы выбрать поля по полю значения, выберите все ячейки <поле значения> .

В группе Выберите тип правила выберите пункт Форматировать все ячейки на основании их значений .

В группе Измените описание правила в поле со списком Стиль формата выберите пункт Гистограмма .

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

Форматирование минимального и максимального значений. Выберите Минимальное значение и Максимальное значение .

В этом случае не вводите Минимальное значение и Максимальное значение .

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

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

Допустимыми являются значения от 0 (нуля) до 100. Не вводите знак процента.

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

Форматирование процентиля. Выберите элемент Процентиль , а затем введите значения параметров Минимальное значение и Максимальное значение .

Допустимыми являются значения процентилей от 0 (нуля) до 100.

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

Форматирование результата формулы. Выберите элемент Формула , а затем введите значения параметров Минимальное значение и Максимальное значение .
- Формула должна возвращать число, дату или время.
  
  Начинайте ввод формулы со знака равенства (= ).
  
  Недопустимая формула не позволит применить форматирование.
  
  Проверьте формулу, чтобы убедиться, что она не возвращает значение ошибки.

Примечания:

Убедитесь, что минимальное значение меньше, чем максимальное .

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

Для выбора цветовых шкал Минимальное значение и Максимальное значение выберите пункт Цвет столбца .

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

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

Чтобы применить к столбцам гистограммы сплошную границу, выберите в списке Граница вариант Сплошная граница , а затем выберите цвет границы.

Чтобы сделать столбцы сплошными или градиентными, выберите в списке Заливка вариант Сплошная заливка или Градиентная заливка .

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

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

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

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

В показанном здесь примере в условном форматировании используются несколько наборов значков.

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

Совет: Если какие-либо из выделенных ячеек содержат формулу, возвращающую ошибку, условное форматирование не применяется к этим ячейкам. Чтобы гарантировать применение условного форматирования к этим ячейкам, воспользуйтесь функцией ЕСТЬ или ЕСЛИОШИБКА для возврата значения (например, 0 или "Н/Д"), отличного от ошибки.

Быстрое форматирование

Вы можете изменить способ определения области для полей из области "Значения" в отчете сводной таблицы с помощью переключателя Применить правило форматирования к .

Расширенное форматирование

Выполните одно из указанных ниже действий.

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

Для изменения условного форматирования выполните указанные ниже действия.
1. Убедитесь, что в поле со списком Показать правила форматирования для выбран соответствующий лист, таблица или отчет сводной таблицы.
  
  При необходимости вы можете изменить диапазон ячеек. Для этого нажмите кнопку Свернуть диалоговое окно в поле Применяется к , чтобы временно скрыть диалоговое окно. Затем выделите новый диапазон ячеек на листе и нажмите кнопку Развернуть диалоговое окно .
  
  Выберите правило, а затем нажмите кнопку Изменить правило . Откроется диалоговое окно Изменение правила форматирования .

чтобы выбрать поля по выделению, выберите только эти ячейки ;

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

чтобы выбрать поля по полю значения, выберите все ячейки <поле значения> .

В группе Выберите тип правила выберите пункт Форматировать все ячейки на основании их значений .

В разделе Измените описание правила в списке Формат стиля выберите пункт Набор значков .

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

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

Выполните одно из указанных ниже действий.
- Форматирование числового значения, значения даты или времени. Выберите элемент Число .
  
  Форматирование процентного значения. Выберите элемент Процент .
  
  Допустимыми являются значения от 0 (нуля) до 100. Не вводите знак процента.
  
  Используйте процентные значения, если необходимо визуализировать все значения пропорционально, поскольку распределение значений в этом случае является пропорциональным.
  
  Форматирование процентиля. Выберите элемент Процентиль . Допустимыми являются значения процентилей от 0 (нуля) до 100.
  
  Используйте процентили, если необходимо визуализировать группу максимальных значений (например, верхнюю 20ю процентиль) с помощью одного значка, а группу минимальных значений (например, нижнюю 20ю процентиль) - с помощью другого, поскольку они соответствуют экстремальным значениям, которые могут сместить визуализацию данных.
  
  Форматирование результата формулы. Выберите элемент Формула , а затем введите формулы в каждое поле Значение .
  - Формула должна возвращать число, дату или время.
    
    Начинайте ввод формулы со знака равенства (=).
    
    Недопустимая формула не позволит применить форматирование.
    
    Проверьте формулу, чтобы убедиться, что она не возвращает значение ошибки.

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

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

Примечания:

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

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

Форматирование ячеек, содержащих текст, числовые значения, дату или время

Чтобы удобнее находить определенные ячейки, можно отформатировать их с помощью оператора сравнения. Например, в инвентарном перечне, отсортированном по категориям, можно выделить желтым цветом товары, которых осталось менее 10 штук. В сводном листе розничного магазина можно выделить все магазины с доходом более 10 %, с объемом продаж менее 100 000 долларов и с регионом "Юго-восток".

В приведенных здесь примерах демонстрируются встроенные критерии условного форматирования, такие как "больше чем" и "%_лучших". Города с численностью населения более 2 000 000 выделяются зеленым цветом фона, а 30 % лучших (самых высоких) показателей среднемесячной максимальной температуры - оранжевым.

Примечание: Невозможно применить условное форматирование к полям в области "Значение" отчета сводной таблицы по тексту или по дате (можно только по числам).

Быстрое форматирование

Если вы хотите посмотреть видео об этих методах, см. Видео: условное форматирование текста и Видео: условное форматирование дат .

Расширенное форматирование

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

На вкладке Главная в группе Стили щелкните стрелку рядом с кнопкой Условное форматирование и выберите пункт Управление правилами. Откроется диалоговое окно Диспетчер правил условного форматирования .

Выполните одно из указанных ниже действий.

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

Для изменения условного форматирования выполните указанные ниже действия.
1. Убедитесь, что в поле со списком Показать правила форматирования для выбран соответствующий лист, таблица или отчет сводной таблицы.
  
  При необходимости вы можете изменить диапазон ячеек. Для этого нажмите кнопку Свернуть диалоговое окно в поле Применяется к Развернуть диалоговое окно .
  
  Выберите правило, а затем нажмите кнопку Изменить правило . Откроется диалоговое окно Изменение правила форматирования .

чтобы выбрать поля по выделению, выберите только эти ячейки ;

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

чтобы выбрать поля по полю значения, выберите все ячейки <поле значения> .

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

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

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

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

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

Быстрое форматирование

Расширенное форматирование

Быстрое форматирование

Расширенное форматирование

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

Примечание: Применить условное форматирование к полям в области "Значения" отчета сводной таблицы по уникальным или повторяющимся значениям невозможно.

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

Быстрое форматирование

Расширенное форматирование

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

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

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

Если при создании собственного правила условного форматирования отсутствуют нужные варианты, можно использовать логическую формулу, чтобы задать условия форматирования. Например, можно сравнить значения в выбранных ячейках с результатом, возвращенным функцией, или оценить данные в ячейках за пределами выделенного диапазона, которые могут находиться на другом листе этой же книги. Формула должна возвращать значение Истина или Ложь (1 или 0), но вы можете использовать условную логику для объединения набора соответствующих условных форматов, таких как различные цвета для каждого из небольших наборов текстовых значений (например, названия категорий товаров).

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

Совет: Если какие-либо ячейки содержат формулу, возвращающую ошибку, условное форматирование не применяется к таким ячейкам. Для решения этой проблемы используйте в формуле функции ЯВЛЯЕТСЯ или функцию ЕСЛИОШИБКА , чтобы возвращать указанное вами значение (например, 0 или "Н/П") вместо значения ошибки.

На вкладке Главная в группе Стили щелкните стрелку рядом с кнопкой и выберите пункт Управление правилами .

Откроется диалоговое окно Диспетчер правил условного форматирования .

Выполните одно из указанных ниже действий.

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

Для изменения условного форматирования выполните указанные ниже действия.
1. Убедитесь, что в поле со списком Показать правила форматирования для выбран соответствующий лист, таблица или отчет сводной таблицы.
  
  При необходимости вы можете изменить диапазон ячеек. Для этого нажмите кнопку Свернуть диалоговое окно в поле Применяется к , чтобы временно скрыть диалоговое окно, а затем выделите новый диапазон ячеек на этом или других листах и нажмите кнопку Развернуть диалоговое окно .
  
  Выберите правило, а затем нажмите кнопку Изменить правило . Откроется диалоговое окно Изменение правила форматирования .

В разделе Применить правило выполните одно из следующих действий, чтобы изменить выбор полей в области значений отчета сводной таблицы:

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

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

Чтобы определить область по полю "Значение" , выберите ко всем ячейкам, содержащим значения <поле значения> для <строка> .

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

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

Копирование и вставка условного форматирования

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

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

Поиск ячеек с условным форматированием

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

Поиск всех ячеек с условным форматированием

Поиск ячеек с одинаковым условным форматированием

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

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

Диспетчер правил условного форматирования . (Для этого на вкладке Главная выберите команду и выберите пункт Управление правилами .)

Откроется диалоговое окно Диспетчер правил условного форматирования .

При применении нескольких правил условного форматирования приоритет их проверки определяется порядком их расположения в этом диалоговом окне (сверху вниз).

СЕГОДНЯ =B2<СЕГОДНЯ () Применяется к =B2<СЕГОДНЯ()+60

Вверх и Вниз .

Иногда к диапазону ячеек можно применить несколько правил условного форматирования. Вот примеры применения правил: сначала тех, которые не конфликтуют друг с другом, а затем - конфликтующих.

Если какое-либо правило условного форматирования проверяется с результатом "Истина", оно имеет приоритет перед любым установленным вручную форматом для этих же выделенных ячеек. Это означает, что в случае конфликта условное форматирование применяется, а ручное форматирование не применяется. При удалении правила условного форматирования форматирование, вручную примененное к диапазону ячеек, остается.

Диспетчер правил условного форматирования

Управление правилами установить флажок Остановить, если истина

Остановить, если истина
Установить или снять флажок Остановить, если истина

Если нужно посмотреть видео об управлении правилами условного форматирования, см. Видео: управление условным форматированием .

Очистка условного форматирования

Очистка условного форматирования на листе

На вкладке Главная щелкните > Удалить правила > .

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

На всем листе

На вкладке Главная щелкните > Удалить правила > Удалить правила со всего листа .

В диапазоне ячеек

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

Щелкните ячейку с условным форматированием, которое вы хотите удалить со всего листа.

На вкладке Главная щелкните стрелку рядом с кнопкой Найти и выделить и выберите команду Выделить группу ячеек .

Выберите параметр Условные форматы .

Выберите пункт этих же в группе Проверка данных . Будут выделены все ячейки с одинаковыми правилами условного форматирования.

На вкладке Главная нажмите кнопку , выберите пункт Удалить правила , а затем - .

Форматирование только первых или последних значений

Можно найти максимальное и минимальное значения в диапазоне ячеек на основе указанного порогового значения. Например, можно найти 5 самых популярных продуктов в региональном отчете, 15 % наименее популярных продуктов в клиентском опросе или 25 самых высоких зарплат в отчете по персоналу отдела.

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

На вкладке Главная в группе Таблицы выберите .

Выберите нужную команду, например Первые 10 элементов или Последние 10 % .

Введите значения, которые вы хотите использовать, затем выберите формат (цвет заливки, текста или границы) и нажмите кнопку ОК .

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

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

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

На вкладке Главная в группе Таблицы выберите пункт Правила отбора первых и последних значений .

Копирование и вставка условного форматирования Управление правилами условного форматирования
При использовании условного форматирования вы задаете правила, с помощью которых Excel определяет, когда следует применять условное форматирование. Для управления этими правилами важно понимать последовательность, в которой эти правила проверяются, и действия в случае конфликта нескольких из них. Также следует понимать, как копирование и вставка влияют на проверку правил, следует знать, как изменять последовательность этой проверки и когда ее следует останавливать.
Изменение порядка проверки правил условного форматирования

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

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

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

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community , попросить помощи в сообществе Answers community , а также предложить новую функцию или улучшение на веб-сайте

Работа с VB проектом (12)
Условное форматирование (5)
Списки и диапазоны (5)
Макросы(VBA процедуры) (63)
Разное (39)
Баги и глюки Excel (4)

Сборник формул для условного форматирования

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

Excel 2003 : Формат(Format) -- формула ;
Excel 2007-2010 : вкладка Главная(Home) -Условное форматирование(Conditional formatting) -Создать правило(New rule) -Использовать формулу для определения форматируемых ячеек(Use a formula to determine which cells to format)

Подробнее об условном форматировании можно прочитать в статье: Основные понятия условного форматирования и как его создать

Все условия приведены для диапазона A1:A20 . Это означает, что для корректного выполнения условия необходимо выделить диапазон A1:A20 (столбцов может быть больше), начиная с ячейки A1 , после чего назначить условие.
Если выделять необходимо не с первой строки, а скажем, с 4-ой, то и выделить надо будет диапазон A4:A20 и в формуле для условия указывать в качестве критерия первую ячейку выделенного диапазона - A4 .

Если необходимо выделять форматированием не только конкретную ячейку, удовлетворяющую условию, а всю строку таблицы на основе ячейки одного столбца, то перед установкой правила необходимо выделить всю таблицу, строки которой необходимо форматировать, а ссылку на столбец с критерием закрепить:
= $A1 =МАКС($A$1:$A$20)
при выделенном диапазоне A1:F20 (диапазон применения условного форматирования), будет выделена строка A7:F7 , если в ячейке A7 будет максимальное число.

Так же можно применять не к конкретно одному столбцу, а к полностью диапазону. Но в этом случае надо знать принцип смещения ссылок в формулах, чтобы условия применялись именно к нужным ячейкам. Например, если задать условие для диапазона B1:D10 в виде формулы: =B1

ЧИСЛОВЫЕ ЗНАЧЕНИЯ

Выделение ячеек с числами:
=ЕЧИСЛО(A1)
Выделение ячеек с числами, но не учитывая нули:
=И(ЕЧИСЛО(A1); A1 0)
Выделение строк со значением больше 0:
= A1 >0
Выделение строк со значением в диапазоне от 3 до 10:
=И(A1 >=3; A1
Выделение в диапазоне $A$1:$A$20 ячейки с максимальным значением:
= A1 =МАКС($A$1:$A$20)
Выделение в диапазоне $A$1:$A$20 ячейки с минимальным значением:
=И(ЕЧИСЛО(A1); A1 =МИН($A$1:$A$20))
Выделение в диапазоне $A$1:$A$20 ячейки со вторым по величине числом. Т.е. из чисел 1,2,3,4,5,6,7 будет выделено число 6:
= A1 =НАИБОЛЬШИЙ($A$1:$A$20 ;2)

ТЕКСТОВЫЕ ЗНАЧЕНИЯ

Выделение ячеек с любым текстом:
=ЕТЕКСТ(A1)
Выделение ячеек с текстом Итог :
= A1 ="Итог"
Выделение ячеек, содержащих текст Итог :
=СЧЁТЕСЛИ(A1 ;"*итог*")
=НЕ(ЕОШ(ПОИСК("итог";A1 )))
Выделение ячеек, не содержащих текст Итог :
=СЧЁТЕСЛИ(A1 ;"*итог*")=0
=ЕОШ(ПОИСК("итог";A1 ))
Выделение ячеек, текст которых начинается со слова Итог :
=ЛЕВСИМВ(A1 ;4)="Итог"
Выделение ячеек, текст которых заканчивается на слово Итог :
=ПРАВСИМВ(A1 ;4)="Итог"

ДАТА / ВРЕМЯ

Выделение текущей даты:
=A1 =СЕГОДНЯ()
Выделение ячейки с датой, больше текущей:
=A1 >СЕГОДНЯ()
Выделение ячейки с датой, которая наступит через неделю:
=A1 =СЕГОДНЯ()+7
Выделение ячеек с датами текущего месяца(любого года):
=МЕСЯЦ(A1 )=МЕСЯЦ(СЕГОДНЯ())
Выделение ячеек с датами текущего месяца текущего года:
=И(МЕСЯЦ(A1 )=МЕСЯЦ(СЕГОДНЯ());ГОД(A1)=ГОД(СЕГОДНЯ()))
или
=ТЕКСТ(A1 ;"ГГГГММ")=ТЕКСТ(СЕГОДНЯ();"ГГГГММ")
Выделение ячеек с выходными днями:
=ДЕНЬНЕД(A1 ;2)>5
Выделение ячеек с будними днями:
=ДЕНЬНЕД(A1 ;2)
Выделение ячеек, входящих в указанный период(промежуток) дат:
=И($A1 >ДАТА(2015;9;1); $A1
ДРУГИЕ
Выделение различий в ячейках по условию:
=A1 <>$B1
Выделение ячейки, если ячейка следующего столбца(B) этой же строки меньше:
=A1 >B1
Выделение строк цветом через одну:
=ОСТАТ(СТРОКА();2)
Выделение строк цветом, если значение ячейки столбца A присутствует в диапазоне $F$1:$H$5000 :
=СЧЁТЕСЛИ($F$1:$H$5000 ;A1 )
Выделение строк цветом, если значение ячейки столбца A отсутствует в диапазоне $F$1:$H$5000 :
=СЧЁТЕСЛИ($F$1:$H$5000 ;A1 )=0
Выделение цветом ячейки, если её значение в диапазоне A1:A20 второе по счету:
=СЧЁТЕСЛИ($A$1:$A1 ;A1 )=2
Выделение ячеек, содержащих ошибки (#ЗНАЧ! ; #Н/Д ; #ССЫЛКА! и т.п.). Помимо просто выявления ячеек с ошибками можно применять, когда необходимо скрыть ошибочные значения в ячейках(назначив цвет шрифта таким же, как и цвет заливки):
=ЕОШИБКА(A )
Выделение непустых ячеек в столбце A :
=$A1 ""

Статья помогла? Поделись ссылкой с друзьями! Видеоуроки

{"Bottom bar":{"textstyle":"static","textpositionstatic":"bottom","textautohide":true,"textpositionmarginstatic":0,"textpositiondynamic":"bottomleft","textpositionmarginleft":24,"textpositionmarginright":24,"textpositionmargintop":24,"textpositionmarginbottom":24,"texteffect":"slide","texteffecteasing":"easeOutCubic","texteffectduration":600,"texteffectslidedirection":"left","texteffectslidedistance":30,"texteffectdelay":500,"texteffectseparate":false,"texteffect1":"slide","texteffectslidedirection1":"right","texteffectslidedistance1":120,"texteffecteasing1":"easeOutCubic","texteffectduration1":600,"texteffectdelay1":1000,"texteffect2":"slide","texteffectslidedirection2":"right","texteffectslidedistance2":120,"texteffecteasing2":"easeOutCubic","texteffectduration2":600,"texteffectdelay2":1500,"textcss":"display:block; padding:12px; text-align:left;","textbgcss":"display:block; position:absolute; top:0px; left:0px; width:100%; height:100%; background-color:#333333; opacity:0.6; filter:alpha(opacity=60);","titlecss":"display:block; position:relative; font:bold 14px \"Lucida Sans Unicode\",\"Lucida Grande\",sans-serif,Arial; color:#fff;","descriptioncss":"display:block; position:relative; font:12px \"Lucida Sans Unicode\",\"Lucida Grande\",sans-serif,Arial; color:#fff; margin-top:8px;","buttoncss":"display:block; position:relative; margin-top:8px;","texteffectresponsive":true,"texteffectresponsivesize":640,"titlecssresponsive":"font-size:12px;","descriptioncssresponsive":"display:none !important;","buttoncssresponsive":"","addgooglefonts":false,"googlefonts":"","textleftrightpercentforstatic":40}}

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

Располагается эта полезная возможность на вкладке «Главная» в области «Стили» под одноименной пиктограммой:

Создать правило

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

Выбрав пункт «Создать правило…», приложение отобразит окно:

В нем Вы можете выбрать тип правила и настроить его описание (подробнее читайте далее в статье).

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

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

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

Гистограмма

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

Ширина ячейки принимается за 100%, что соответствует максимальному значению диапазона правила. Т.е. ячейка, содержащая максимальное значение будет залита полностью, а ячейка со значением в 2 раза меньшим максимальному – наполовину. В случае отрицательного значения, столбец будет окрашен другим цветом и иметь другую направленность (это можно изменить).

Настройки стиля:

Показывать только столбец – установив флажок на данном поле, Вы сообщаете, что для диапазона ячеек правила необходимо скрывать содержимое и оставлять только формат;
Параметры значений – здесь устанавливаются максимальные и минимальные значения и их типы. В качестве типа может выступать число, процент, формула, процентиль либо по умолчанию (авто). Значение может быть только числовым. Все числа, меньше минимального (включая отрицательные), приравниваются к нулю, т.е. не содержат столбца. А те, которые больше максимального, приравниваются к 100% и закрашиваются полностью.
Внешний вид столбца – устанавливает способ заливки (сплошной или градиентный), границу и их цвета;
Направление столбца – определяет способ направленности (слева направо либо наоборот);
Кнопка «Отрицательные значения и ось…» – настройки отображения столбцов для отрицательных чисел. Что они позволяют:
- Установить свой цвет заливки столбца и его границу или сделать их одинаковыми для всех значений (положительных и отрицательных. По умолчанию они различаются);
- Задать положение оси или одинаковую направленность для всех значений.

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

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

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

Минимальным числом задан ноль, а значения меньше его, будут иметь такие же цвет и насыщенность;
Средним значением указана единица и желтый цвет. Это значит, что переход шкалы от красного к желтому будет осуществлен между 0 и 1;
4 является максимальным значением. Все, что превышает его, получает те же установки. Переход от желтого к зеленому происходит между 1 и 4.

Наборы значков (флажков)

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

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

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

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

Рассмотрим правила, которые имеются в этом пункте:

Значение ячейки. Предполагает работу с числами и текстом. Сравнение производится по шкале сортировки.
Текст. Позволяет проверить наличие или отсутствие подстроки в тексте.
Даты. С его помощью легко создать правила типа «вчера», «сегодня», «завтра», «на прошлой неделе», «в следующем месяце» и т.п.
Пустые. Форматирует пустые ячейки. Пробелы не учитываются.
Непустые. Противоположное предыдущему правилу.
Ошибки. Истинно, когда значением ячейки является ошибка.
Без ошибки. Противоположное предыдущему правилу.

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

Из названия понятно, что правило срабатывает для тех ячеек, которые идут первыми (наибольшими) или последними (наименьшими) в указанном диапазоне. Количество таких ячеек указывается в виде числа или процента.

Формула в условном форматировании

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

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

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

Используем 2 условия со следующими формулами:

Если на складе нет товара, т.е. равен 0, то подсвечиваем позицию заказа красным – =ВПР(D3;A:B;2;ЛОЖЬ)=0;
Если на складе есть товар, но его количество меньше, чем указано в позиции заказа, то последнюю подсвечиваем желтым – =И(ВПР(D3;$A:$B;2;ЛОЖЬ)0).

Теперь необходимо выделить требуемый диапазон и создать нужные нам правила.

В функции, в качестве первого аргумента используется ссылка всего на одну ячейку. Вас это не должно смущать, так как приложение «понимает», что ее нужно сместить в соответствии с диапазоном правила. Главное, чтобы она была относительной, т.е. не закреплена символами доллара – $.

Остальные правила

Ничего не было сказано о еще двух видах правил, а именно:

Форматирование на основе среднего значения – полное название «Форматировать только значения, которые находятся выше или ниже среднего»;
Форматирование уникальных или повторяющихся значений.

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

Управление правилами

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

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

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

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

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

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

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

< Назад
Вперёд >

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

У Вас недостаточно прав для комментирования.

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

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

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

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

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

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

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

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

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

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

— первые 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 самостоятельно будет добавлять в ячейки значки, при этом следуя функции:

>=67, >=33 и <33.

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

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

Осталось только применить наши правила к таблице. В результате она изменится вот так:

Осталось применить это же правило к столбцу «Выручка за час».

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

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

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