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

Обновлено: 07.07.2024

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

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

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

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

cellValue
colorScale
custom
dataBar
iconSet
preset
textComparison
topBottom

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Гистограмма

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Условное форматирование
Ребят, такой вопрос: есть ячейка, в которой есть условное форматирование на ввод чисел от 1 до.

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

Условное форматирование
Добрый день господа! Прошу Вас оказать помощь. Как при помощи условного форматирования (при вводе.

. проблема в том, что через условное форматирование можно задать только 3 таких правила. А мне нужно больше.

Самое простое - это установить Экс версией старше 2003. В них такого ограничения нет.

ЗЫ Mawrat, без обид, а какая необходимость раскрашивать лист всеми цветами радуги?
Мало того что наличие большого кол-ва цветов плохо воспринимается визуально, условное форматирование многократно увеличивает размер файла, так ещё и формулы УФа волатильны.
Короче одни минусы.

А здесь, на самом деле, цветов не много, но больше трёх. Нужно 5 цветов. Цвета обосновано введены. Когда данных очень много, оператор не должен вникать, что за крючочки там написаны - он сразу должен ухватить общую картину. А уже потом вникать что как и почему. Т. е., вот он проматывает лист с данными и видит - всё зелёновато-жёлтое такое - это хорошо. А вот тут цвета сместились в сторону красного - значит уже проблемы. А если красный где-то сверкнул - вот здесь нужны неотложные меры. Вот тогда уже начинается разбор - что за числа там у нас.
---
Excel 2007 - это да - хорошо бы. Но по тех. заданию, нужна реализация в MS Excel 2003.
---
Сейчас есть решение на VBA. Как я понимаю, пока так надо всё и оставить. Когда я начал думать, что же всё-таки можно ещё сделать, то решения стали возникать из разряда "приклеить скотчем и подмотать изолентой". Например - вычислять какой диапазон строк, в данный момент виден в окне Excel и красить только эти строки. По мере того, как пользователь прокручивает лист - красить дальше. Но здесь тоже есть проблемы. Если пользователь прекратил прокрутку и решил просто сохранить файл, то сохранение произойдёт с недокрашенным листом. Можно конечно перехватывать событие закрытия книги и докрасить, но тогда будет зависание на этапе сохранения. Можно также привязаться к событию открытия книги и продолжить раскраску в следующем сеансе. - Но это всё очень ненадёжно и весьма надуманно.
---
Сейчас сделано так: на листе есть фложок. Если он сброшен - показывается как есть. Если установлен - производится раскраска.
---
Что касается UpdateScreen = False/True - это в проекте применено.

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

Чтобы при условном форматировании использовать больше трех критериев для данных, можно применить трюк с использованием VВА. В действительности, благодаря этому коду, можно использовать практически неограниченное количество критериев. В Excel есть очень полезная возможность под названием условное форматирование.Хотя это удобно, Excel поддерживает только три условия, которых иногда не хватает. Указать более трех условий можно благодаря коду Excel VBA, который запускается автоматически, когда пользователь изменяет указанный диапазон.
Чтобы увидеть, как это работает, предположим, есть шесть отдельных условий
в диапазоне А1:А10 на определенном рабочем листе. Введите некоторые
данные. Сохраните рабочую книгу, перейдите на рабочий лист, правой кнопкой щелкните
ярлычок с его именем, в контекстном меню выберите команду Исходный текст и введите код:

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

А если при движении активной ячейки по листу будет подсвечиваться текущая строка и столбец? Своего рода координатное выделение примерно такого вида:

Поудобнее, чем линейка, правда?

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

Способ 1. Очевидный. Макрос, выделяющий текущую строку и столбец

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

Откройте лист со таблицей, в которой хотите получить такое координатное выделение. Щелкните правой кнопкой мыши по ярлычку листа и выберите в контекстном меню команду Исходный текст (Source Code). Должно открыться окно редактора Visual Basic. Скопируйте в него этот текст этих трех макросов:

Измените адрес рабочего диапазона на свой - именно в пределах этого диапазона и будет работать наше выделение. Затем закройте редактор Visual Basic и вернитесь в Excel.

Нажмите сочетание клавиш ALT+F8, чтобы открыть окно со списком доступных макросов. Макрос Selection_On, как нетрудно догадаться, включает координатное выделение на текущем листе, а макрос Selection_Off - выключает его. В этом же окне, нажав кнопку Параметры (Options) можно назначить этим макросам сочетания клавиш для удобного запуска.

Плюсы этого способа:

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

Минусы этого способа:

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

Способ 2. Оригинальный. Функция ЯЧЕЙКА + Условное форматирование

Этот способ хотя и имеет пару недостатков, мне представляется весьма изящным. Реализовать что-либо, используя только встроенные средства Excel, минимально влезая в программирование на VBA - высший пилотаж ;)

Способ основан на использовании функции ЯЧЕЙКА (CELL), которая может выдавать массу различной информации по заданной ячейке - высоту, ширину, номер строки-столбца, числовой формат и т.д.. Эта функция имеет два аргумента:

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

Хитрость в том, что второй аргумент не является обязательным. Если он не указан, то берется текущая активная ячейка.

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

Выделяем нашу таблицу, т.е. те ячейки, в которых в будущем должно отображаться координатное выделение.
В Excel 2003 и более старших версиях открываем меню Формат - Условное форматирование - Формула (Format - Conditional Formatting - Formula) . В Excel 2007 и новее - жмем на вкладке Главная (Home) кнопку Условное форматирование - Создать правило (Conditional Formatting - Create Rule) и выбираем тип правила Использовать формулу для определения форматируемых ячеек (Use formula)
Вводим формулу для нашего координатного выделения:

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

Теперь при изменении выделения будет запускаться процесс пересчета формулы с функцией ЯЧЕЙКА в условном форматировании и заливаться текущая строка и столбец.

Плюсы этого способа:

Условное форматирование не нарушает пользовательское форматирование таблицы
Этот вариант выделения корректно работает с объединенными ячейками.
Нет риска удалить целую строку и столбец с данными при случайном нажатии Delete.
Макросы используются минимально

Минусы этого способа:

Формулу для условного форматирования надо вводить вручную.
Нет быстрого способа включить-выключить такое форматирование - оно включено всегда, пока не будет удалено правило.

Способ 3. Оптимальный. Условное форматирование + макросы

Золотая середина. Используем механизм отслеживания выделения на листе при помощи макросов из способа-1 и добавим к нему безопасное выделение цветом с помощью условного форматирования из способа-2.

Не забудьте изменить адрес рабочего диапазона на адрес своей таблицы. Закройте редактор Visual Basic и вернитесь в Excel. Чтобы использовать добавленные макросы, нажмите сочетание клавиш ALT+F8 и действуйте аналогично способу 1.

Способ 4. Красивый. Надстройка FollowCellPointer

Excel MVP Jan Karel Pieterse родом из Нидерландов раздает у себя на сайте бесплатную надстройку FollowCellPointer(36Кб), которая решает ту же задачу, отрисовывая с помощью макросов графические линии-стрелки для подсветки текущей строки и столбца:

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

Суть: в диапазоне ("А5:А100") средствами excel создано условное форматирование (все работает)
Вопрос: Как написать макрос который отключает условное форматирование только в этом диапазое и вкл там же (или 2 макроса вкл выкл)

__________________
Помощь в написании контрольных, курсовых и дипломных работ здесь

Удаление условного форматирования то срабатывает, то нет
Здравствуйте! В макросе использую строку ActiveSheet.Range("checked").FormatConditions.Delete .

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

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

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

Можно где-нибудь в сторонке в одной ячейке сохранить форматирование.
А в основном диапазоне - удалять / потом , если нужно "ВКЛ" - то копировать из ячейки в сторонке..

Добавлено через 10 минут
funk57,

Как вариант , сохраним формат в ячейке "B1"

там стоит формула =СТРОКА(A5)=ЯЧЕЙКА("строка") ' формула выделяет ячейку из первого столбца той же строки, что и активная ячейка

а скопировать макросом не получается.

если прописыю так:

то она вставляется криво, что то типа такго =СТРОКА(A1046745)=ЯЧЕЙКА("строка")

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

там бордюр копирует, а криво встает здесь

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

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

Так можно в самом условном вставить ссылку на выключатель.
И это наверное самое простое.

да, это мне и нужно. пробовал не получается

посмотрите пожалуйста, а то у меня руки кривые наверное)

а чекбокс ставит в эту А1 ложь/истина

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

а можно ли както привязать к самому чек боксу, без дополнительной ячейки?

или может быть макрос которы прописывает в данный диапазон такое условное фрматирование?

Привязать формулу к чекбоксу вряд ли, разве может через UDF.
Макрос написать можно, вон выше что-то уже написано. но я не хочу и вникать - хорошее решение ведь с ячейкой, чем напрягает? funk57, Вечер Добрый. Если правильно понял вопрос то можно и без макроса это сделать. При помощи выпадающего списка выбираете "вкл и выкл". Проверьте.

Программное отключение условного форматирования ячеек Excel
Требуется не удалить условное форматирование ячеек, а временно отключить, а затем включить его.

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

Как работает программное включение и выключение автофильтра?
Здравствуйте все! Объясните, пожалуйста, как работает программное включение и выключение.

Включение/выключение кнопки по выделению элемента ListBox
Добрый день, форумчане! Мне нужно сделать так, чтобы при включении формы, на которой есть.

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

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

Читайте также: