Как закрашивать ячейки в excel в зависимости от значения

© 2023
Сео Пульс — блог об интернет-маркетинге

Как закрашивать ячейки в excel в зависимости от значения

И опять нужна помощь.
Вот такой пример чтобы разобраться.

Как «покрасить» ячейку А1 в синий цвет если ячейка А5 равна 1 и в красный — если А5=9? Красить надо только фон.

23.03.2008 22:29:18

Как «покрасить» ячейку А1 в синий цвет если ячейка А5 равна 1 и в красный — если А5=9? Красить надо только фон.

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

23.03.2008 22:39:17
Разобрался. А еще сложнее? Как покрасить А1 если А5=1 таким же цветом как покрашена С8?
23.03.2008 22:43:36

Разобрался. А еще сложнее? Как покрасить А1 если А5=1 таким же цветом как покрашена С8?

а С8 кто закрасил? Вы или условное форматирование?

23.03.2008 22:51:16
Я. )) На самом деле у меня все сложнее. Я просто примеры простые навожу. Чтобы понятней было
23.03.2008 22:59:01

Я. )) На самом деле у меня все сложнее. Я просто примеры простые навожу. Чтобы понятней было

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

Если закращиваете Вы, почему бы в условном форматировании, когда ячейка = 1 не поставить самостоятельно заливку таким же цветом, что и в С8?

23.03.2008 23:07:03
Я попробую сам пока поэкспериментировать. Если не получиться, напишу. Спасибо.
23.03.2008 23:08:19

Я попробую сам пока поэкспериментировать. Если не получиться, напишу. Спасибо.

Как скажите, хозяин — барин 🙂

23.03.2008 23:16:20

Надо закрасить ячейки B3,C3,D3 цветом в зависимости от значения ячейки O3. А значение ячейки O3 выбирается по формуле =ВПР($B3;’Робочі місця’!$B$2:$E$51;3). Там же находиться цвет ячейки.
Файл удален — велик размер. [Модераторы]

23.03.2008 23:20:10
. но я не хочу рыбу. Я хочу удочку, чтобы эту рыбу наловить. ))) Т.е. я хочу понять как это сделать
23.03.2008 23:28:09

Надо закрасить ячейки B3,C3,D3 цветом в зависимости от значения ячейки O3. А значение ячейки O3 выбирается по формуле =ВПР($B3;’Робочі місця’!$B$2:$E$51;3). Там же находиться цвет ячейки.

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

23.03.2008 23:31:30
Предлагаю при заливке отталкиваться не на столбец O, а на данные в столбце В.
23.03.2008 23:32:56

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

и в условном форматировании на столбце В написать формулу если

24.03.2008 10:48:14

Понемногу получается. Но в Екселе 2003 только три варианта можна задать в условном форматировании. А мне надо хотя бы 4

24.03.2008 10:50:40
24.03.2008 11:03:23

С этого места поподробнее ))

24.03.2008 11:50:35

По ссылке Лузер выкладывал функцию по подсчету ячеек с разным цветом заливки.
http://www.planetaexcel.ru/forum.php?thread_id=2128

24.03.2008 12:18:19

в именах задать диапазон применения и формулу условия
по событию sheet_calculate закрашивать этот диапазон согласно условию

31.03.2010 14:49:38

А я вот так и не понял, как применить условное форматирование к одним ячейкам, на основании значений других ячеек.
Например, я хочу закрасить ячейки красным в диапазоне $A$1:$A$100 и $B$1:$B$100 построчно, если в диапазоне $D$1:$D$100 есть «1».

Никак не могу правильно написать формулу..

Пользователь
Сообщений: 47199 Регистрация: 15.09.2012
31.03.2010 14:57:39
=СЧЕТЕСЛИ($D$1:$D$100;1)
31.03.2010 15:22:09

Сначала я так и написал, но почему-то закрашиваются диапазоны $A$1:$A$100 и $B$1:$B$100 полностью, а не по строчно.

Пользователь
Сообщений: 47199 Регистрация: 15.09.2012
31.03.2010 15:38:20
Построчно — это как? Через строку? Ту же строку, где единица?
31.03.2010 15:42:17
Да, именно.. туже строку, где есть единица
Пользователь
Сообщений: 47199 Регистрация: 15.09.2012
31.03.2010 15:49:10
В условное форматирование — формула:
=$D1=1
31.03.2010 16:04:37

Все гениальное просто!!

Пользователь
Сообщений: 47199 Регистрация: 15.09.2012
31.03.2010 16:10:45
В который раз за последние дни намеки на гениальность там, где все просто и нужно лишь подумать 🙂
Пользователь
Сообщений: 47199 Регистрация: 15.09.2012
31.03.2010 16:12:52
Скоро 2+2=4 назовут гениальным решением 🙂
31.03.2010 16:24:51

А как же выражение: всё гениальное просто?

Главное его рассмотреть.

Пользователь
Сообщений: 3280 Регистрация: 24.12.2012
31.03.2010 16:27:12

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

22.08.2011 14:26:31

Как «покрасить» диапозон ячеек $B$3:$D$3 в зеленый цвет если ячейка Е3 равна 0 и не красить если больше нуля или пустая? Красить надо только фон.

Страницы: 1 2 След.
Читают тему

© Николай Павлов, Planetaexcel, 2006-2023
info@planetaexcel.ru

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

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

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

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

Применение условного форматирования в отчете сводной таблицы

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

• Существует несколько условных форматов, не работающих с полями в области «Значения» в отчете сводной таблицы. Например, невозможно форматировать такие поля на основании того, какие значения в них содержатся, уникальные или повторяющиеся. Эти ограничения описаны в дальнейших разделах этой статьи в применимых случаях.
• При изменении макета отчета сводной таблицы путем фильтрации данных, скрытия, свертывания и развертывания уровней либо перемещения полей условное форматирование сохраняется до тех пор, пока не будут удалены поля базовых данных.
• Область действия условного формата для полей в области «Значения» может быть основана на иерархии данных и определяется всеми видимыми дочерними элементами (нижестоящим уровнем в иерархии) родительского объекта (вышестоящим уровнем в иерархии) в строках для одного или нескольких столбцов или в столбцах для одной или нескольких строк.

Примечание: В иерархии данных дочерние элементы не наследуют условное форматирование от родительских, а родительские — от дочерних.

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

Определение области по выделению

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

Определение области по полю значения.

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

Определение области по соответствующему полю.

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

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

• столбец и поле родительской строки;
• строка и поле родительского столбца.

Использование экспресс-анализа для применения условного форматирования

Примечание: Экспресс-анализ недоступен в Excel 2010 и более ранних версиях.

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

1. Выделите данные, к которым необходимо применить условное форматирование. В правом нижнем углу выделенной области появится кнопка Экспресс-анализ.
2. Нажмите кнопку Экспресс-анализили клавиши CTRL+Q.
3. В появившемся всплывающем окне на вкладке Форматирование наведите указатель мыши на различные варианты для предварительного просмотра данных, затем выберите подходящий вариант форматирования.

• Варианты форматирования, которые отображаются на вкладке Форматирование, зависят от выбранных данных. Если выделенный фрагмент содержит только текст, будут доступны параметры Текст содержит, Повторяющиеся, Уникальные значения, Равно и Очистить формат. Если выделенный диапазон содержит только числа или числа и текст, доступны параметры Гистограмма, Цветовая шкала, Набор значков, Больше, Первые 10 % и Очистить формат.
• Динамический просмотр будет работать только для тех вариантов, которые поддерживаются для данных. Например, если выделенный диапазон не содержат повторяющихся значений и выбрать Повторяющиеся, динамический просмотр не будут работать.

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

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

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

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

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

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

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

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

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

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

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

1. Выделите одну или несколько ячеек в диапазоне, таблице или отчете сводной таблицы.
2. На вкладке Главная в группе Стили щелкните стрелку рядом с кнопкой Условное форматирование и выберите пункт Управление правилами. Откроется диалоговое окно Диспетчер правил условного форматирования.
3. Выполните одно из указанных ниже действий.
   • Чтобы добавить совершенно новый вариант условного форматирования, нажмите кнопку Создать правило. Откроется диалоговое окно Создание правила форматирования.
   • Чтобы создать новый вариант условного форматирования на основе одного из перечисленных, выберите нужное правило и щелкните Дублировать правило. Дубликат правила появится в диалоговом окне. Выберите его и нажмите Изменить правило. Откроется диалоговое окно Изменение правила форматирования.
   • Для изменения условного форматирования выполните указанные ниже действия.
     1. Убедитесь, что в поле со списком Показать правила форматирования для выбран соответствующий лист, таблица или отчет сводной таблицы.
     2. При необходимости вы можете изменить диапазон ячеек. Для этого нажмите кнопку Свернуть диалоговое окно в поле Применяется к, чтобы временно скрыть диалоговое окно. Затем выделите новый диапазон ячеек на листе и нажмите кнопку Развернуть диалоговое окно.
     3. Выберите правило, а затем нажмите кнопку Изменить правило. Откроется диалоговое окно Изменение правила форматирования.
4. В разделе Применить правило выберите один из следующих вариантов, чтобы изменить выбор полей в области значений отчета сводной таблицы:
   • к выделенным ячейкам, чтобы выбрать ячейки по выделению;
   • ко всем ячейкам, содержащим значения , чтобы выбрать ячейки с определенной меткой.
   • ко всем ячейкам, содержащим значения для , чтобы выбрать ячейки с определенной меткой, кроме промежуточных и общих итогов.
5. В разделе Выберите тип правила выберите пункт Форматировать все ячейки на основании их значений (по умолчанию).
6. В группе Измените описание правила в поле со списком Стиль формата выберите пункт Двухцветная шкала.
7. В полях Тип параметров Минимальное значение и Максимальное значение выберите их типы.
   • Форматирование минимального и максимального значений. Выберите минимальное значение и максимальное значение. В этом случае не вводите минимальное и максимальноезначения.
   • Форматирование числового значения, значения даты или времени. Выберите элемент Число, а затем введите минимальное и максимальноезначения.
   • Форматирование процентного значения. Введите минимальное и максимальноезначения. Допустимыми являются значения от 0 (нуля) до 100. Не вводите знак процента. Рекомендуется использовать процентные значения, если необходимо визуализировать все значения пропорционально, поскольку распределение значений является пропорциональным.
   • Форматирование процентиля. Выберите элемент Процентиль, а затем введите значения параметров Минимальное значение и Максимальноезначение. Допустимыми являются значения процентилей от 0 (нуля) до 100. Используйте процентиль, если необходимо визуализировать группу высоких значений (например, верхнюю 20ю процентиль) с одним оттенком, а группу низких значений (например, нижнюю 20ю процентиль) — с другим, поскольку они соответствуют экстремальным значениям, которые могут сместить визуализацию данных.
   • Форматирование результата формулы. Выберите элемент Формула, а затем введите минимальное и максимальное значения.
     • Формула должна возвращать число, дату или время.
     • Начинайте ввод формулы со знака равенства (=).
     • Недопустимая формула не позволит применить форматирование.
     • Проверьте формулу, чтобы убедиться, что она не возвращает значение ошибки.
       • Убедитесь, что минимальное значение меньше, чем максимальное.
       • Для параметров Минимальное значение и Максимальное значение можно выбрать разные типы. Например, можно выбрать числовой тип для минимального значения и процентный тип для максимального значения.

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

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

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

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

       

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

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

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

       

       1. Выделите одну или несколько ячеек в диапазоне, таблице или отчете сводной таблицы.
       2. На вкладке Главная в группе Стили щелкните стрелку рядом с кнопкой Условное форматирование и выберите пункт Управление правилами. Откроется диалоговое окно Диспетчер правил условного форматирования.
       3. Выполните одно из указанных ниже действий.
          • Чтобы добавить новый вариант условного форматирования, нажмите кнопку Создать правило. Откроется диалоговое окно Создание правила форматирования.
          • Чтобы создать новый вариант условного форматирования на основе одного из перечисленных, выберите нужное правило и щелкните Дублировать правило. Скопированный дубликат правила появится в диалоговом окне. Выберите его и нажмите Изменить правило. Откроется диалоговое окно Изменение правила форматирования.
          • Для изменения условного форматирования выполните указанные ниже действия.
            1. Убедитесь, что в поле со списком Показать правила форматирования для выбран соответствующий лист, таблица или отчет сводной таблицы.
            2. При необходимости вы можете изменить диапазон ячеек. Для этого нажмите кнопку Свернуть диалоговое окно в поле Применяется к, чтобы временно скрыть диалоговое окно. Затем выделите новый диапазон ячеек на листе и нажмите кнопку Развернуть диалоговое окно.
            3. Выберите правило, а затем нажмите кнопку Изменить правило. Откроется диалоговое окно Изменение правила форматирования.
       4. В разделе Применить правило выберите один из следующих вариантов, чтобы изменить выбор полей в области значений отчета сводной таблицы:
          • чтобы выбрать поля по выделению, выберите только эти ячейки;
          • чтобы выбрать поля по соответствующему полю, выберите все ячейки с теми же полями;
          • чтобы выбрать поля по полю значения, выберите все ячейки .
       5. В группе Выберите тип правила выберите пункт Форматировать все ячейки на основании их значений.
       6. В группе Измените описание правила в поле со списком Стиль формата выберите пункт Трехцветная шкала.
       7. Выберите тип параметров Минимальное значение, Среднее значение и Максимальное значение. Выполните одно из указанных ниже действий.
          • Форматирование минимального и максимального значений. Выберите элемент Среднее значение. В этом случае не вводите минимальное и максимальноезначения.
          • Форматирование числового значения, значения даты или времени. Выберите элемент Число, а затем введите минимальное, среднее и максимальное значения.
          • Форматирование процентного значения. Выберите элемент Процент, а затем введите минимальное, среднее и максимальное значения. Допустимыми являются значения от 0 (нуля) до 100. Не вводите знак процента. Используйте процентные значения, если необходимо визуализировать все значения пропорционально, поскольку распределение значений в этом случае является пропорциональным.
          • Форматирование процентиля. Выберите элемент Процентиль, а затем введите Минимальное значение, Среднее значение и Максимальное значение. Допустимыми являются значения процентилей от 0 (нуля) до 100. Используйте процентиль, если необходимо визуализировать группу высоких значений (например, верхнюю 20юпроцентиль) с одним оттенком, а группу низких значений (например, нижнюю 20юпроцентиль) — с другим, поскольку они соответствуют экстремальным значениям, которые могут сместить визуализацию данных.
          • Форматирование результата формулы. Выберите элемент Формула, а затем введите минимальное, среднее и максимальное значения. Формула должна возвращать число, дату или время. Начинайте ввод формулы со знака равенства (=). Недопустимая формула не позволит применить форматирование. Проверьте формулу, чтобы убедиться, что она не возвращает значение ошибки.
            • Можно задать минимальное, среднее и максимальное значения для диапазона ячеек. Убедитесь, что минимальное значение меньше среднего, а оно, в свою очередь, меньше максимального.
            • Для параметров Минимальное значение, Среднее значение и Максимальное значение можно выбрать разные типы. Например, можно выбрать числовой тип для минимального значения, процентиль для среднего значения и процентный тип для максимального значения.
            • Во многих случаях среднее значение по умолчанию, равное 50 процентам, оптимально, но его можно изменить с учетом конкретных требований.
            • Чтобы выбрать дополнительные цвета или создать пользовательский цвет, нажмите кнопку Другие цвета.
            • Выбранная цветовая шкала появится в окне предварительного просмотра.

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

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

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

            

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

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

            

            1. Выделите одну или несколько ячеек в диапазоне, таблице или отчете сводной таблицы.
            2. На вкладке Главная в группе Стили щелкните стрелку рядом с кнопкой Условное форматирование и выделите пункт Гистограммы, а затем выберите гистограмму.

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

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

            

            1. Выделите одну или несколько ячеек в диапазоне, таблице или отчете сводной таблицы.
            2. На вкладке Главная в группе Стили щелкните стрелку рядом с кнопкой Условное форматирование и выберите пункт Управление правилами. Откроется диалоговое окно Диспетчер правил условного форматирования.
            3. Выполните одно из указанных ниже действий.
               • Чтобы добавить условное форматирование, нажмите кнопку Создать правило. Откроется диалоговое окно Создание правила форматирования.
               • Чтобы создать новый вариант условного форматирования на основе одного из перечисленных, выберите нужное правило и щелкните Дублировать правило. Скопированный дубликат правила появится в диалоговом окне. Выберите его и нажмите Изменить правило. Откроется диалоговое окно Изменение правила форматирования.
               • Для изменения условного форматирования выполните указанные ниже действия.
               • 1. Убедитесь, что в поле со списком Показать правила форматирования для выбран соответствующий лист, таблица или отчет сводной таблицы.
                 2. При необходимости вы можете изменить диапазон ячеек. Для этого нажмите кнопку Свернуть диалоговое окно в поле Применяется к, чтобы временно скрыть диалоговое окно. Затем выделите новый диапазон ячеек на листе и нажмите кнопку Развернуть диалоговое окно.
                 3. Выберите правило, а затем нажмите кнопку Изменить правило. Откроется диалоговое окно Изменение правила форматирования.
            4. В разделе Применить правило выберите один из следующих вариантов, чтобы изменить выбор полей в области значений отчета сводной таблицы:
               • чтобы выбрать поля по выделению, выберите только эти ячейки;
               • чтобы выбрать поля по соответствующему полю, выберите все ячейки с теми же полями;
               • чтобы выбрать поля по полю значения, выберите все ячейки .
            5. В группе Выберите тип правила выберите пункт Форматировать все ячейки на основании их значений.
            6. В группе Измените описание правила в поле со списком Стиль формата выберите пункт Гистограмма.
            7. Выберите значения Минимальное и Максимальное для типа. Выполните одно из указанных ниже действий.
               • Форматирование минимального и максимального значений. Выберите Минимальное значение и Максимальное значение. В этом случае не вводите Минимальное значение и Максимальное значение.
               • Форматирование числового значения, значения даты или времени. Выберите элемент Число, а затем введите минимальное и максимальноезначения.
               • Форматирование процентного значения. Выберите элемент Процент, а затем введите минимальное и максимальное значения. Допустимыми являются значения от 0 (нуля) до 100. Не вводите знак процента. Используйте процентные значения, если необходимо визуализировать все значения пропорционально, поскольку распределение значений в этом случае является пропорциональным.
               • Форматирование процентиля. Выберите элемент Процентиль, а затем введите значения параметров Минимальное значение и Максимальное значение. Допустимыми являются значения процентилей от 0 (нуля) до 100. Используйте процентили, если необходимо визуализировать группу высоких значений (например, верхнюю 20ю процентиль) в одной пропорции гистограммы и группу низких значений (например, нижнюю 20ю процентиль) — в другой, поскольку они соответствуют экстремальным значениям, которые могут сместить визуализацию данных.
               • Форматирование результата формулы. Выберите элемент Формула, а затем введите значения параметров Минимальное значение и Максимальное значение.
                 • Формула должна возвращать число, дату или время.
                 • Начинайте ввод формулы со знака равенства (=).
                 • Недопустимая формула не позволит применить форматирование.
                 • Проверьте формулу, чтобы убедиться, что она не возвращает значение ошибки.
               • Убедитесь, что минимальное значение меньше, чем максимальное.
               • Для параметров Минимальное значение и Максимальное значение можно выбрать разные типы. Например, можно выбрать числовой тип для минимального значения и процентный тип для максимального значения.

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

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

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

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

               

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

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

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

               

               1. Выделите ячейки, к которым необходимо применить условное форматирование.
               2. На вкладке Главная в группе Стили щелкните стрелку рядом с кнопкой Условное форматирование и выделите пункт Набор значков, а затем выберите набор значков.

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

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

               

               1. Выделите ячейки, к которым необходимо применить условное форматирование.
               2. На вкладке Главная в группе Стили щелкните стрелку рядом с кнопкой Условное форматирование и выберите пункт Управление правилами. Откроется диалоговое окно Диспетчер правил условного форматирования.
               3. Выполните одно из указанных ниже действий.
                  • Чтобы добавить условное форматирование, нажмите кнопку Создать правило. Откроется диалоговое окно Создание правила форматирования.
                  • Чтобы создать новый вариант условного форматирования на основе одного из перечисленных, выберите нужное правило и щелкните Дублировать правило. Скопированный дубликат правила появится в диалоговом окне. Выберите его и нажмите Изменить правило. Откроется диалоговое окно Изменение правила форматирования.
                  • Для изменения условного форматирования выполните указанные ниже действия.
                    1. Убедитесь, что в поле со списком Показать правила форматирования для выбран соответствующий лист, таблица или отчет сводной таблицы.
                    2. При необходимости вы можете изменить диапазон ячеек. Для этого нажмите кнопку Свернуть диалоговое окно в поле Применяется к, чтобы временно скрыть диалоговое окно. Затем выделите новый диапазон ячеек на листе и нажмите кнопку Развернуть диалоговое окно.
                    3. Выберите правило, а затем нажмите кнопку Изменить правило. Откроется диалоговое окно Изменение правила форматирования.
               4. В разделе Применить правило выберите один из следующих вариантов, чтобы изменить выбор полей в области значений отчета сводной таблицы:
                  • чтобы выбрать поля по выделению, выберите только эти ячейки;
                  • чтобы выбрать поля по соответствующему полю, выберите все ячейки с теми же полями;
                  • чтобы выбрать поля по полю значения, выберите все ячейки .
               5. В группе Выберите тип правила выберите пункт Форматировать все ячейки на основании их значений.
               6. В разделе Измените описание правила в списке Формат стиля выберите пункт Набор значков.
                  1. Выберите набор значков. По умолчанию используется набор 3 сигнала светофора без обрамления. Число значков, операторы сравнения, заданные по умолчанию, и пороговые значения для каждого значка могут быть различны для разных наборов значков.
                  2. Вы можете изменить операторы сравнения и пороговые значения. Размеры диапазонов значений по умолчанию для всех значков одинаковы, но их можно изменить в соответствии с конкретными требованиями. Убедитесь, что пороговые значения находятся в логической последовательности — от высших к низшим, сверху вниз.
                  3. Выполните одно из указанных ниже действий.
                     • Форматирование числового значения, значения даты или времени. Выберите элемент Число.
                     • Форматирование процентного значения. Выберите элемент Процент. Допустимыми являются значения от 0 (нуля) до 100. Не вводите знак процента. Используйте процентные значения, если необходимо визуализировать все значения пропорционально, поскольку распределение значений в этом случае является пропорциональным.
                     • Форматирование процентиля. Выберите элемент Процентиль. Допустимыми являются значения процентилей от 0 (нуля) до 100. Используйте процентили, если необходимо визуализировать группу максимальных значений (например, верхнюю 20ю процентиль) с помощью одного значка, а группу минимальных значений (например, нижнюю 20ю процентиль) — с помощью другого, поскольку они соответствуют экстремальным значениям, которые могут сместить визуализацию данных.
                     • Форматирование результата формулы. Выберите элемент Формула, а затем введите формулы в каждое поле Значение.
                       • Формула должна возвращать число, дату или время.
                       • Начинайте ввод формулы со знака равенства (=).
                       • Недопустимая формула не позволит применить форматирование.
                       • Проверьте формулу, чтобы убедиться, что она не возвращает значение ошибки.
                  4. Чтобы первый значок соответствовал меньшим значениям, а последний — большим, выберите параметр Обратный порядок значков.
                  5. Для отображения только значка, но не значения в ячейке, выберите параметр Показать только значок.
                     • Может потребоваться скорректировать ширину столбцов, чтобы они могли вместить значки.
                     • Отображаемый размер значка зависит от размера шрифта, используемого в данной ячейке. При увеличении размера шрифта пропорционально увеличивается и размер значка.

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

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

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

                     

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

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

                     

                     1. Выделите одну или несколько ячеек в диапазоне, таблице или отчете сводной таблицы.
                     2. На вкладке Главная в группе Стили щелкните стрелку рядом с кнопкой Условное форматирование, а затем — элемент Правила выделения ячеек.
                     3. Выберите нужную команду, например Между, Текст содержит или Дата.
                     4. Введите значения, которые необходимо использовать, и выберите формат.

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

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

                     

                     1. Выделите одну или несколько ячеек в диапазоне, таблице или отчете сводной таблицы.
                     2. На вкладке Главная в группе Стили щелкните стрелку рядом с кнопкой Условное форматирование и выберите пункт Управление правилами. Откроется диалоговое окно Диспетчер правил условного форматирования.
                     3. Выполните одно из указанных ниже действий.
                        • Чтобы добавить условное форматирование, нажмите кнопку Создать правило. Откроется диалоговое окно Создание правила форматирования.
                        • Чтобы создать новый вариант условного форматирования на основе одного из перечисленных, выберите нужное правило и щелкните Дублировать правило. Скопированный дубликат правила появится в диалоговом окне. Выберите его и нажмите Изменить правило. Откроется диалоговое окно Изменение правила форматирования.
                        • Для изменения условного форматирования выполните указанные ниже действия.
                        • 1. Убедитесь, что в поле со списком Показать правила форматирования для выбран соответствующий лист, таблица или отчет сводной таблицы.
                          2. При необходимости вы можете изменить диапазон ячеек. Для этого нажмите кнопку Свернуть диалоговое окно в поле Применяется к, чтобы временно скрыть диалоговое окно, а затем выделите новый диапазон ячеек на этом или других листах и нажмите кнопку Развернуть диалоговое окно.
                          3. Выберите правило, а затем нажмите кнопку Изменить правило. Откроется диалоговое окно Изменение правила форматирования.
                     4. В разделе Применить правило выберите один из следующих вариантов, чтобы изменить выбор полей в области значений отчета сводной таблицы:
                        • чтобы выбрать поля по выделению, выберите только эти ячейки;
                        • чтобы выбрать поля по соответствующему полю, выберите все ячейки с теми же полями;
                        • чтобы выбрать поля по полю значения, выберите все ячейки .
                     5. В списке Выберите тип правила выберите пункт Форматировать только ячейки, которые содержат.
                     6. В группе Измените описание правила в поле со списком Форматировать только ячейки, для которых выполняется следующее условие выполните одно из указанных ниже действий.
                        • Форматирование по числу, дате или времени. Выберите элемент Значение ячейки и задайте оператор сравнения, а затем введите число, дату или время. Например, можно выбрать команду Между, а затем ввести числа 100 и 200 или выбрать команду Равно и ввести дату 01.01.2009. Вы также можете ввести формулу, которая возвращает число, дату или время.
                          • Начинайте ввод формулы со знака равенства (=).
                          • Недопустимая формула не позволит применить форматирование.
                          • Проверьте формулу, чтобы убедиться, что она не возвращает значение ошибки.
                        • Форматирование по тексту. Выберите элемент Текст, задайте оператор сравнения и введите текст. Например, можно выбрать команду Содержит и ввести значение Серебро или выбрать команду Начиная с и ввести значение Три. Кавычки включаются в строку поиска, допускается использование подстановочных знаков. Максимальная длина строки — 255 знаков. Можно также ввести формулу, которая возвращает текст.
                          • Начинайте ввод формулы со знака равенства (=).
                          • Недопустимая формула не позволит применить форматирование.
                          • Проверьте формулу, чтобы убедиться, что она не возвращает значение ошибки.

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

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

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

                          

                          1. Выделите одну или несколько ячеек в диапазоне, таблице или отчете сводной таблицы.
                          2. На вкладке Главная в группе Стили щелкните стрелку рядом с кнопкой Условное форматирование, а затем — элемент Правила отбора первых и последних значений.
                          3. Выберите нужную команду, например Первые 10 элементов или Последние 10 %.
                          4. Введите значения, которые необходимо использовать, и выберите формат.

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

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

                          

                          1. Выделите одну или несколько ячеек в диапазоне, таблице или отчете сводной таблицы.
                          2. На вкладке Главная в группе Стили щелкните стрелку рядом с кнопкой Условное форматирование и выберите пункт Управление правилами. Откроется диалоговое окно Диспетчер правил условного форматирования.
                          3. Выполните одно из указанных ниже действий.
                             • Чтобы добавить условное форматирование, нажмите кнопку Создать правило. Откроется диалоговое окно Создание правила форматирования.
                             • Чтобы создать новый вариант условного форматирования на основе одного из перечисленных, выберите нужное правило и щелкните Дублировать правило. Скопированный дубликат правила появится в диалоговом окне. Выберите его и нажмите Изменить правило. Откроется диалоговое окно Изменение правила форматирования.
                             • Для изменения условного форматирования выполните указанные ниже действия.
                             • 1. Убедитесь, что в поле со списком Показать правила форматирования для выбран соответствующий лист, таблица или отчет сводной таблицы.
                               2. При необходимости вы можете изменить диапазон ячеек. Для этого нажмите кнопку Свернуть диалоговое окно в поле Применяется к, чтобы временно скрыть диалоговое окно. Затем выделите новый диапазон ячеек на листе и нажмите кнопку Развернуть диалоговое окно.
                               3. Выберите правило, а затем нажмите кнопку Изменить правило. Откроется диалоговое окно Изменение правила форматирования.
                          4. В разделе Применить правило выберите один из следующих вариантов, чтобы изменить выбор полей в области значений отчета сводной таблицы:
                             • чтобы выбрать поля по выделению, выберите только эти ячейки;
                             • чтобы выбрать поля по соответствующему полю, выберите все ячейки с теми же полями;
                             • чтобы выбрать поля по полю значения, выберите все ячейки .
                          5. В группе Выберите тип правила выберите пункт Форматировать только первые или последние значения.
                          6. В группе Измените описание правила в поле со списком Форматировать значения, которые относятся к выберите пункт Сверху или Снизу.
                          7. Выполните одно из указанных ниже действий.
                             • Чтобы указать верхнее или нижнее пороговое значение, введите число, а затем снимите флажок % от выделенного диапазона. Допустимыми являются значения от 1 до 1000.
                             • Чтобы указать верхнее или нижнее процентное значение, введите число, а затем установите флажок % от выделенного диапазона. Допустимыми являются значения от 1 до 100.
                          8. Кроме того, можно настроить применение формата к полям в области «Значения» отчета сводной таблицы, область которых определяется соответствующим полем. По умолчанию условное форматирование основано на всех видимых значениях. Однако при определении области по соответствующему полю можно применить условное форматирование не ко всем видимым значениям, а к каждому из следующих сочетаний:
                             • столбец и поле его родительской строки (вариант каждой группы столбцов);
                             • строка и поле ее родительского столбца (вариант каждой группы строк).
                          9. Чтобы задать формат, нажмите кнопку Формат. Отобразится диалоговое окно Формат ячеек.
                          10. Выберите формат чисел, шрифта, рамки или заполнения, который необходимо применять, если значение в ячейке соответствует условию, а затем нажмите кнопку ОК. Можно выбрать несколько форматов. Выбранные форматы появятся в окне предварительного просмотра.

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

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

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

                          

                          1. Выделите одну или несколько ячеек в диапазоне, таблице или отчете сводной таблицы.
                          2. На вкладке Главная в группе Стили щелкните стрелку рядом с кнопкой Условное форматирование, а затем — элемент Правила отбора первых и последних значений.
                          3. Выберите нужную команду, например Выше среднего или Ниже среднего.
                          4. Введите значения, которые необходимо использовать, и выберите формат.

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

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

                          

                          1. Выделите одну или несколько ячеек в диапазоне, таблице или отчете сводной таблицы.
                          2. На вкладке Главная в группе Стили щелкните стрелку рядом с кнопкой Условное форматирование и выберите пункт Управление правилами. Откроется диалоговое окно Диспетчер правил условного форматирования.
                          3. Выполните одно из указанных ниже действий.
                             • Чтобы добавить условное форматирование, нажмите кнопку Создать правило. Откроется диалоговое окно Создание правила форматирования.
                             • Чтобы создать новый вариант условного форматирования на основе одного из перечисленных, выберите нужное правило и щелкните Дублировать правило. Скопированный дубликат правила появится в диалоговом окне. Выберите его и нажмите Изменить правило. Откроется диалоговое окно Изменение правила форматирования.
                             • Для изменения условного форматирования выполните указанные ниже действия.
                             • 1. Убедитесь, что в поле со списком Показать правила форматирования для выбран соответствующий лист, таблица или отчет сводной таблицы.
                               2. При необходимости вы можете изменить диапазон ячеек. Для этого нажмите кнопку Свернуть диалоговое окно в поле Применяется к, чтобы временно скрыть диалоговое окно. Затем выделите новый диапазон ячеек на листе и нажмите кнопку Развернуть диалоговое окно.
                               3. Выберите правило, а затем нажмите кнопку Изменить правило. Откроется диалоговое окно Изменение правила форматирования.
                          4. В разделе Применить правило выберите один из следующих вариантов, чтобы изменить выбор полей в области значений отчета сводной таблицы:
                             • чтобы выбрать поля по выделению, выберите только эти ячейки;
                             • чтобы выбрать поля по соответствующему полю, выберите все ячейки с теми же полями;
                             • чтобы выбрать поля по полю значения, выберите все ячейки .
                          5. В группе Выберите тип правила выберите пункт Форматировать только значения, которые находятся выше или ниже среднего.
                          6. В группе Измените описание правила в поле со списком Форматировать значения, для которых выполняется следующее условие выполните одно из указанных ниже действий.
                             • Для форматирования ячеек, значения в которых больше или меньше среднего значения для всех ячеек в диапазоне, выберите пункт Выше или Ниже.
                             • Для форматирования ячеек, значения в которых больше или меньше одного, двух или трех значений стандартного отклонения для всех ячеек в диапазоне, выберите стандартное отклонение.
                          7. Кроме того, можно настроить применение формата к полям в области «Значения» отчета сводной таблицы, область которых определяется соответствующим полем. По умолчанию условное форматирование основано на всех видимых значениях. Однако при определении области по соответствующему полю можно применить условное форматирование не ко всем видимым значениям, а к каждому из следующих сочетаний:
                             • столбец и поле его родительской строки (вариант каждой группы столбцов);
                             • строка и поле ее родительского столбца (вариант каждой группы строк).
                          8. Нажмите кнопку Формат для отображения диалогового окна Формат ячеек.
                          9. Выберите формат чисел, шрифта, рамки или заполнения, который необходимо применять, если значение в ячейке соответствует условию, а затем нажмите кнопку ОК. Можно выбрать несколько форматов. Выбранные форматы появятся в поле Просмотр.

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

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

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

                          

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

                          

                          1. Выделите ячейки, к которым необходимо применить условное форматирование.
                          2. На вкладке Главная в группе Стили щелкните стрелку рядом с кнопкой Условное форматирование, а затем — элемент Правила выделения ячеек.
                          3. Выберите вариант повторяющиеся значения.
                          4. Введите значения, которые необходимо использовать, и выберите формат.

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

                          

                          1. Выделите ячейки, к которым необходимо применить условное форматирование.
                          2. На вкладке Главная в группе Стили щелкните стрелку рядом с кнопкой Условное форматирование и выберите пункт Управление правилами. Откроется диалоговое окно Диспетчер правил условного форматирования.
                          3. Выполните одно из указанных ниже действий.
                             • Чтобы добавить условное форматирование, нажмите кнопку Создать правило. Откроется диалоговое окно Создание правила форматирования.
                             • Чтобы создать новый вариант условного форматирования на основе одного из перечисленных, выберите нужное правило и щелкните Дублировать правило. Скопированный дубликат правила появится в диалоговом окне. Выберите его и нажмите Изменить правило. Откроется диалоговое окно Изменение правила форматирования.
                             • Для изменения условного форматирования выполните указанные ниже действия.
                             • 1. Убедитесь, что в поле со списком Показать правила форматирования для выбран соответствующий лист или таблица.
                               2. При необходимости вы можете изменить диапазон ячеек. Для этого нажмите кнопку Свернуть диалоговое окно в поле Применяется к, чтобы временно скрыть диалоговое окно. Затем выделите новый диапазон ячеек на листе и нажмите кнопку Развернуть диалоговое окно.
                               3. Выберите правило, а затем нажмите кнопку Изменить правило. Откроется диалоговое окно Изменение правила форматирования.
                          4. В группе Выберите тип правила выберите параметр Форматировать только уникальные или повторяющиеся значения.
                          5. В разделе Измените описание правила в списке Форматировать все выберите значение уникальные или повторяющиеся.
                          6. Нажмите кнопку Формат для отображения диалогового окна Формат ячеек.
                          7. Выберите формат чисел, шрифта, рамки или заполнения, который необходимо применять, если значение в ячейке соответствует условию, а затем нажмите кнопку ОК. Можно выбрать несколько форматов. Выбранные форматы появятся в окне предварительного просмотра.

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

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

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

                          1. Нажмите Главная>Условное форматирование >Управление правилами, затем в диалоговом окне Диспетчер управления правилами условного форматирования выберите одно из перечисленных правил и нажмите Дублировать правило. Дубликат правила появится в списке.
                          2. Выберите дубликат правила и нажмите кнопку Изменить правило.

                          

                          1. Выделите ячейки, которые нужно отформатировать.
                          2. На вкладке Главная нажмите кнопку Условное форматирование и выберите пункт Создать правило.
                          3. Создайте правило и задайте для него параметры форматирования, а затем нажмите кнопку ОК. Если нужные варианты отсутствуют, можно определить форматируемые ячейки с помощью формулы — инструкции см. в следующем разделе.

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

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

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

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

                          1. На вкладке Главная в группе Стили щелкните стрелку рядом с кнопкой Условное форматирование и выберите пункт Управление правилами. Откроется диалоговое окно Диспетчер правил условного форматирования.
                          2. Выполните одно из указанных ниже действий.
                             • Чтобы добавить условное форматирование, нажмите кнопку Создать правило. Откроется диалоговое окно Создание правила форматирования.
                             • Чтобы создать новый вариант условного форматирования на основе одного из перечисленных, выберите нужное правило и щелкните Дублировать правило. Скопированный дубликат правила появится в диалоговом окне. Выберите его и нажмите Изменить правило. Откроется диалоговое окно Изменение правила форматирования.
                             • Для изменения условного форматирования выполните указанные ниже действия.
                             • 1. Убедитесь, что в поле со списком Показать правила форматирования для выбран соответствующий лист, таблица или отчет сводной таблицы.
                               2. При необходимости вы можете изменить диапазон ячеек. Для этого нажмите кнопку Свернуть диалоговое окно в поле Применяется к, чтобы временно скрыть диалоговое окно, а затем выделите новый диапазон ячеек на этом или других листах и нажмите кнопку Развернуть диалоговое окно.
                               3. Выберите правило, а затем нажмите кнопку Изменить правило. Откроется диалоговое окно Изменение правила форматирования.
                          3. В разделе Применить правило выполните одно из следующих действий, чтобы изменить выбор полей в области значений отчета сводной таблицы:
                             • Чтобы определить область по выделенному фрагменту , щелкните Выделенные ячейки.
                             • Чтобы определить область по соответствующему полю , щелкните ко всем ячейкам, содержащим значения .
                             • Чтобы определить область по полю «Значение» , выберите ко всем ячейкам, содержащим значения для .
                          4. В группе Выберите тип правила нажмите кнопку Использовать формулу для определения форматируемых ячеек.
                             1. В группе Измените описание правила введите формулу в поле со списком Форматировать значения, для которых следующая формула является истинной.

                                Можно выбрать несколько форматов. Выбранные форматы появятся в окне предварительного просмотра. Пример 1. Применение двух условных форматов с условиями, использующими проверку с операторами И и ИЛИ В приведенном ниже примере показано использование двух правил условного форматирования. Если не применяется первое правило, то применяется второе. Первое правило: покупатель дома выделил до 75 000 долларов в качестве первого взноса и 1500 в месяц в качестве выплат по закладной. Если сумма первого взноса и ежемесячные платежи отвечают требованиям, ячейки B4 и B5 будут отформатированы зеленым цветом. Второе правило: если первый взнос или ежемесячный платеж не укладываются в бюджет покупателя, ячейки B4 и B5 будут отформатированы красным цветом. Измените некоторые значения, например годовую процентную ставку, срок кредитования, сумму первого взноса или сумму покупки, чтобы увидеть, что произойдет в ячейках, к которым применено условное форматирование.

                                Формула для первого правила (применяется зеленый цвет)	=И(ЕСЛИ($B$4 <=75000,1),ЕСЛИ(ABS($B$5)<=1500,1))
                                Формула для второго правила (применяется красный цвет)	=ИЛИ(ЕСЛИ($B$4>=75000;1);ЕСЛИ(ABS($B$5)>=1500;1))

                                Пример 2. Заливка каждой второй строки с помощью функций ОСТАТ и СТРОКА Применение условного форматирования ко всем ячейкам листа выполняет заливку синим цветом каждой второй строки в диапазоне. Функция ОСТАТ возвращает остаток от деления числа (первый аргумент) на делитель (второй аргумент). Функция СТРОКА возвращает номер текущей строки. При делении номера текущей строки на 2 в остатке всегда будет получаться 0 для четных чисел и 1 для нечетных чисел. Поскольку 0 — это ЛОЖЬ, а 1 — ИСТИНА, форматирование будет применяться к каждой нечетной строке. Правило использует такую формулу: =MOD(ROW(),2)=1.

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

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

                                Как закрасить область цветом при появлении данных в другой ячейке?

                                Как сделать так, чтобы при появлении любых данных в ячейке другая ячейка, или несколько ячеек, окрашивались в цвет?

                                • Вопрос задан более двух лет назад
                                • 122 просмотра

                                Комментировать
                                Решения вопроса 1

                                

                                Григорий Боев @ProgrammerForever Куратор тега Excel
                                Учитель, автоэлектрик, программист, музыкант

                                1. Выделить диапазон в котором менять цвет
                                2. Условное форматирование
                                3. Своя формула

                                Ответ написан более двух лет назад
                                Нравится 1 4 комментария

                                

                                Дмитрий Эгрэгор @EGREGOR_Dmitriy Автор вопроса
                                а как можно протянуть это «Условное форматирование» дальше по ячейкам ?

                                

                                Григорий Боев @ProgrammerForever Куратор тега Excel

                                Ctrl+C — Ctrl+V
                                или поменять диапазон в условном форматировании. Он может быть несвязным, например
                                a:a;b3:c7;d9

                                

                                Дмитрий Эгрэгор @EGREGOR_Dmitriy Автор вопроса

                                не подходит.
                                у меня постоянно растущий список, я добавляю новые строки и «протягиваю» на новые строки правила с старых строк.
                                но если протянуть — =$A$1<>«» то диапазон окраситься в B1,B2, и тд, если есть что-то в ячейке А1.
                                но мне нужно сделать так чтобы появилось в А1 = загорелось В1, -создались новые строки, «протянул» правила- появилось в А25 = загорелось в В25.

                                Читать:

                                Как посмотреть недавние действия на компьютере windows 10