Условное форматирование: инструмент Microsoft Excel для визуализации данных

Условное форматирование в Microsoft Excel

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

Условное форматирование в MS Excel с примерами

Условное форматирование в Эксель – этот тот инструмент, который делит работу на до и после его изучения. Суть в том, что при наступлении некоторого условия ячейки форматируются автоматически. Например, если число превышает значение 100, шрифт становится красным полужирным курсивом; когда до наступления платежа остается 2 дня, ячейка с датой подсвечивается желтым цветом; перевыполнение плана продаж на 5% и более окрашивается в зеленый цвет и т.д. и т.п.

Вот упрощенный, но реальный пример. Есть отчет о товарных запасах.

Таблица без форматирования

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

Таблица с условным форматированием

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

Для настройки условного формата следует воспользоваться соответствующей командой на вкладке Главная.

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

При ее нажатии открывается меню.

Меню условного форматирования

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

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

Все сценарии разбиты на категории:

– Правило выделения ячеек

– Правило отбора первых и последних значений

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

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

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

Больше.

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

Меньше… Форматируются ячейки, у которых значение меньше заданного порога.

Меньше.

Между… Форматирование наступает, если содержимое ячейки находится внутри заданных границ.

Между.

Равно… если значение или текст в ячейке совпадает с условием.

Равно.

Текст содержит… Если совпадает только часть текста (слово, код, комбинация символов и т.д).

Текст содержит…

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

Дата.

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

Повторяющиеся значения…

Правила отбора первых и последних значений выделяют наибольшие или наименьшие значения. Помогают анализировать данные, показывая приоритеты и «слабые места».

Правила отбора первых и последних значений

Первые 10 элементов… Выделяются первые топ–10 ячеек. Количество регулируется в диалоговом окне (можно сделать топ-5, топ-20 и др.).

Первые 10 элементов…

Первые 10%… Выделяются 10% наибольших значений. Долю можно изменить.

Первые 10%.

Последние 10 элементов… Аналогично с первым пунктом, только форматируются наименьшие значения.

Последние 10 элементов…

Последние 10%… Наименьшие 10% или другая доля от всех элементов.

Последние 10%.

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

Выше среднего…

Ниже среднего… Ниже средней арифметической.

Ниже среднего…

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

Гистограммы

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

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

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

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

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

Цветные шкалы условного форматирования

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

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

В ячейках Excel выглядит так.

Набор значков в условном форматировании

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

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

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

Диалоговое окно управление правилами

После нажатия кнопки «Изменить правило…» откроется окно, вид которого зависит от редактируемого правила.

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

Здесь также есть куча настроек, но мы их пока опустим. В целом там все интуитивно понятно. Нужно только поэкспериментировать. Практика – лучший учитель.

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

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

Условное форматирование – это три шага вперед на пути к профессиональному использованию Excel. Поэтому рекомендую незамедлительно внедрить в практику.

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

Неправильный формат ячеек

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

Читайте также:  Как построить диаграмму в Excel по данным таблицы

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

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

  1. Нельзя ссылаться на данные в других листах или книгах. Но можно ссылаться на имена диапазонов (так же в других листах и книгах), что позволяет обойти данное ограничение.
  2. Существенное значение имеет тип ссылок в аргументах формул. Следует использовать абсолютные ссылки (например, =СУММ($A$1:$A$5) на ячейки вне диапазона условного форматирования. А если нужно ссылаться на несколько ячеек непосредственно внутри диапазона, тогда следует использовать смешанные типы ссылок (например, A$1).
  3. Если в критериях формула возвращает дату или время, то ее результат вычисления будет восприниматься как число. Ведь даты это те же целые числа (например, 01.01.1900 – это число 1 и т.д.). А время это дробные значения части от целых суток (например, 23:15 – это число 0,96875).

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

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

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

Например, в таблице с прогнозом погоды мы хотим закрасить разными цветами показатели температуры. Условия выделения цветом: если температура выше 10 градусов – зеленым цветом, если выше 20 градусов – желтый, если выше 30 градусов – красным.

Для применения нескольких условий к одной ячейке выполним следующие действия:

  • Выделим диапазон с данными, к которым мы хотим применить условное форматирование => кликнем по пункту “Условное форматирование” на панели инструментов => выберем условие выделения “Больше…” и укажем первое условие (если больше 10, то зеленая заливка). Такие же действия повторим для каждого из условий (больше 20 и больше 30). Не смотря на то, что мы применили три правила, данные в таблице закрашены зеленым цветом:

  • Кликнем на любую ячейку с присвоенным форматированием. Затем, снова кликнем по пункту “Условное форматирование” и перейдем в раздел “Управление правилами”. Во всплывающем окне, распределим правила от большего к меньшему и напротив первых двух поставим галочку “Остановить, если истина”. Этот пункт позволяет не применять остальные правила к ячейке, при соответствии первому. Затем кликнем кнопку “Применить” и “ОК”:

Применив их, наша таблица с данными температуры “подсвечена” корректными цветами, в соответствии с нашими условиями.

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

Например, в таблице с прогнозом погоды мы хотим закрасить разными цветами показатели температуры. Условия выделения цветом: если температура выше 10 градусов – зеленым цветом, если выше 20 градусов – желтый, если выше 30 градусов – красным.

Для применения нескольких условий к одной ячейке выполним следующие действия:

  • Выделим диапазон с данными, к которым мы хотим применить условное форматирование => кликнем по пункту “Условное форматирование” на панели инструментов => выберем условие выделения “Больше…” и укажем первое условие (если больше 10, то зеленая заливка). Такие же действия повторим для каждого из условий (больше 20 и больше 30). Не смотря на то, что мы применили три правила, данные в таблице закрашены зеленым цветом:

  • Кликнем на любую ячейку с присвоенным форматированием. Затем, снова кликнем по пункту “Условное форматирование” и перейдем в раздел “Управление правилами”. Во всплывающем окне, распределим правила от большего к меньшему и напротив первых двух поставим галочку “Остановить, если истина”. Этот пункт позволяет не применять остальные правила к ячейке, при соответствии первому. Затем кликнем кнопку “Применить” и “ОК”:

Применив их, наша таблица с данными температуры “подсвечена” корректными цветами, в соответствии с нашими условиями.

Установка стиля для целой строки

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

И применим его ко всему диапазону.

Это позволит раскрасить всю таблицу.

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

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

Добрый день! извините новичка и чайника, в основном работаю с вордом, а тут необходимо было упростить себе жизнь, как могу пытаюсь, не судите строго. буду рада любым замечаниям )))
В данный момент мне необходимо использовать условное форматирование, а именно чтобы выполнялось следующее:
1. если K7<сегодня(), то выделение желтым цветом
2. если, K7>сегодня(), то выделение красным цветом
3. если, E7-L7=0. цвет выделения нет, но ставим значок «зеленой галочки»
4. если, E7-K7>0, цвет выделения нет, но ставим значок «желтый восклицательный знак»

выделять цветом при условии одном научилась, но как совместить несколько уловий и добавить не только цвет. но и знаки, не знаю(((
прикрепила файл. выдедила цветом желтым и краснфм как должно быть, если вдруг я не правильно объяснила, СПАСИБО!!

Добрый день! извините новичка и чайника, в основном работаю с вордом, а тут необходимо было упростить себе жизнь, как могу пытаюсь, не судите строго. буду рада любым замечаниям )))
В данный момент мне необходимо использовать условное форматирование, а именно чтобы выполнялось следующее:
1. если K7<сегодня(), то выделение желтым цветом
2. если, K7>сегодня(), то выделение красным цветом
3. если, E7-L7=0. цвет выделения нет, но ставим значок «зеленой галочки»
4. если, E7-K7>0, цвет выделения нет, но ставим значок «желтый восклицательный знак»

Читайте также:  Функции ИНДЕКС и ПОИСКПОЗ в Excel и примеры их использования

выделять цветом при условии одном научилась, но как совместить несколько уловий и добавить не только цвет. но и знаки, не знаю(((
прикрепила файл. выдедила цветом желтым и краснфм как должно быть, если вдруг я не правильно объяснила, СПАСИБО!! tatazori

Сообщение Добрый день! извините новичка и чайника, в основном работаю с вордом, а тут необходимо было упростить себе жизнь, как могу пытаюсь, не судите строго. буду рада любым замечаниям )))
В данный момент мне необходимо использовать условное форматирование, а именно чтобы выполнялось следующее:
1. если K7<сегодня(), то выделение желтым цветом
2. если, K7>сегодня(), то выделение красным цветом
3. если, E7-L7=0. цвет выделения нет, но ставим значок «зеленой галочки»
4. если, E7-K7>0, цвет выделения нет, но ставим значок «желтый восклицательный знак»

выделять цветом при условии одном научилась, но как совместить несколько уловий и добавить не только цвет. но и знаки, не знаю(((
прикрепила файл. выдедила цветом желтым и краснфм как должно быть, если вдруг я не правильно объяснила, СПАСИБО!! Автор — tatazori
Дата добавления — 02.02.2012 в 14:03

Форматирование части строки в Excel

Пример условного форматирования части строки на листе Excel в зависимости от содержимого одной или двух ячеек в этой строке.

Условие примера

  1. Заливка строки желтым фоном, если в третьей ячейке (столбец «C») этой строки содержится значение «Да».
  2. Заливка строки серым фоном, если в четвертой ячейке (столбец «D») этой строки содержится значение «Нет».
  3. Заливка строки красным фоном, если в третьей ячейке (столбец «C») этой строки содержится значение «Да», а в четвертой ячейке (столбец «D») – значение «Нет».
  4. Заливка применяется к 5 первым ячейкам любой строки.

Решение примера

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

Выделение первых пяти столбцов на листе Excel

2. Создаем первое правило: условие – =$C1=»Да» , цвет заливки – желтый:

Пример создания правил форматирования №3

3. Создаем второе правило: условие – =$D1=»Нет» , цвет заливки – серый:

Пример создания правил форматирования №4

4. Создаем третье правило: условие – =И($C1=»Да»;$D1=»Нет») , цвет заливки – красный:

Пример создания правил форматирования №5

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

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

6. Заполняем ячейки столбцов «C» и «D» словами «Да» и «Нет» и смотрим результаты условного форматирования части строки:

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

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

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

Скачать файл Excel с примерами. На первом листе реализовано условное форматирование всей строки, на втором – ее части.

Выделение диапазонов несколькими цветами по условию

Другой вариант: нам необходимо в одном столбце выделить разные диапазоны разным цветом.

Например, меньше 30 один диапазон и более 40 – другой диапазон. Выделяем столбец и идем через пункт «условное форматирование» в «правила выделения ячеек» — «меньше». В строке «форматировать ячейки, которые меньше…» ставим значение 30 и выбираем цвет красный текста и выделения ячеек.

Нажимаем ОК. Теперь повторяем все тоже самое, но выбираем уже пункт не «меньше», а «больше».

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

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

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

Как создать правило

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

Сначала необходимо найти на ленте на вкладке «Главная» найти пункт «Условное форматирование». Его можно узнать по характерным разноцветным ячейкам разного размера со стоящей рядом картинкой с перечеркнутым знаком равно.

Условное форматирование в Excel - подробно с примерами

21

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

Условное форматирование в Excel - подробно с примерами

22

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

Условное форматирование в Excel - подробно с примерами

23

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

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

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

Условное форматирование в Excel

Как расширить функционал ЕСЛИ, используя операторы “И” и “ИЛИ”

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

Функция ЕСЛИ с условием «И»

Иногда нужно проверить выражение на предмет сразу нескольким условиям. Для этого используется функция И, записанная в первом аргументе функции ЕСЛИ . Работает это так: в случае если а равно единице и а равно 2, значение будет с.

Читайте также:  Как округлять числа в большую и меньшую сторону функциями Excel

Функция ЕСЛИ с условием «ИЛИ»

Функция ИЛИ работает аналогичным образом, но в этом случае достаточно истинности только одного из условий. Максимально так можно осуществить проверку до 30 условий.

Вот варианты, как можно применять функции И и ИЛИ как аргумент функции ЕСЛИ .

Функция ЕСЛИ в Excel. Примеры (с несколькими условиями)5 Функция ЕСЛИ в Excel. Примеры (с несколькими условиями)6

Информационное окно

Использовать будем все тот же диапазон чисел. К примеру, колонка с итоговыми цифрами (в нашем случае диапазон A1:A15) имеет постоянно меняющиеся данные в которых мы должны контролировать определенные параметры. Зададим сразу предельные значения: до 30 – это «норма», свыше 30 необходимо взять под «внимание», ну а свыше 40 – это «перебор» или «сверх нормы», а теперь в ячейке B1 прописываем вот такую формулу: =ЕСЛИ(A1>40;»сверхнормы»;ЕСЛИ(A1>30;»внимание»;ЕСЛИ(A1 Все очень просто. Условное форматирование 3.9

" data-medium-file="https://i1.wp.com/vseochenprosto.ru/wp-content/uploads/2017/02/Vseochenprosto.-Trjuki-v-ekcel.-Uslovnoe-formatirovanie.-3-Urok-9.png?fit=300%2C169&ssl=1" data-large-file="https://i1.wp.com/vseochenprosto.ru/wp-content/uploads/2017/02/Vseochenprosto.-Trjuki-v-ekcel.-Uslovnoe-formatirovanie.-3-Urok-9.png?fit=1024%2C576&ssl=1" /> Нажимаем «Enter» и в окне B1 должны в нашем случае увидеть «норма»,

Все очень просто. Условное форматирование 3.10

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

Все очень просто. Условное форматирование 3.11

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

Все очень просто. Условное форматирование 3.12

Затем в выпавшем окне выбираем правило, создаем условие и задаем формат для значения «норма», как показано на скриншоте.

Все очень просто. Условное форматирование 3.14

Затем повторяем эту операцию для значения «внимание» и «сверх нормы» и в диспетчере задач мы должны получить вот такую картину:

Все очень просто. Условное форматирование 3.13

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

С уважением

Спасибо Вам, что поделились статьей в социальных сетях!

В этом уроке мы рассмотрим основы применения условного форматирования в Excel.

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

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

Для закрашивания в определенный цвет потребуется перейти на «Главную» и выбрать «Условное форматирование»-«Создать правило».

В выпавшем окне выбираем «Использовать формулу для определения форматируемых ячеек» и прописываем саму функцию, в примере это:

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

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

Рассмотрим следующий пример.

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

Именно поэтому интересующее нас значение вынесем в отдельное поле.

Затем выделим диапазон с окладами (1) и создадим собственное правило условного форматирования (3). При задании условия (4) мышью укажем нужные ячейки в таблице (5). Затем зададим само форматирование (6), например, изменим фон на красный.

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

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

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

Итак, скопируем формулу из правила условного форматирования и вставим ее в столбец рядом. Раскопируем формулу по диапазону.

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

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

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

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

Осталось лишь скопировать формулу и изменить правило условного форматирования (1). Сразу можно изменить и само условное форматирование, поменяв фон на менее едкий, а также сделав шрифт полужирным (2).

Ссылка на основную публикацию