Решение
Выбор Типа поиска организуем с помощью Элементов управления формы Группа и Переключатель. Свяжем все Переключатели с ячейкой B9 . Теперь при выборе Типа поиска Точно совпадает в этой ячейке будет содержаться число 1, при выборе Содержит — 2, Начинается — 3, Заканчивается — 4 (см. файл примера Лист 1 критерий (текст) ).
Настроим Условное форматирование для выделения ячеек, удовлетворяющих критерию. Для этого:
- выделите ячейки, в которые введен перечень инструментов ( А9:A17 );
- вызовите инструмент Условное форматирование ( Главная/ Стили/ Условное форматирование/ Создать правило )
- выберите Использовать формулу для определения форматируемых ячеек;
- в поле « Форматировать значения, для которых следующая формула является истинной » введите =ВЫБОР($B$9;$A$6=A9;ПОИСК($A$6;A9);ПОИСК($A$6;A9)=1;$A$6=ПРАВСИМВ(A9;ДЛСТР($A$6)))
- выберите требуемый формат, например, красный цвет фона .
Если в качестве примера Критерия используется текст « Дрель », а Тип поиска выбран Точно совпадает (1), то красным фоном выделится только ячейка A14 , содержащая значение в точности совпадающее с искомым текстом.
Разберем работу формулы из вышеуказанного правила Условного форматирования .
Функция ВЫБОР() позволяет в одной формуле совместить 4 формулы для всех Типов поиска:
- Точно совпадает: $A$6=A9 ;
- Содержит: ПОИСК($A$6;A9) ;
- Начинается: ПОИСК($A$6;A9)=1 ;
- Заканчивается: $A$6=ПРАВСИМВ(A9;ДЛСТР($A$6)) .
В зависимости от значения в ячейке B9 , связанной с Переключателями, функция ВЫБОР() возвращает соответствующую формулу.
Функция ПОИСК() пытается найти первое вхождение Критерия в каждом значении перечня и, в случае успеха, возвращает начальную позицию найденного значения. В противном случае возвращается ошибка #ЗНАЧ!
Формула ПОИСК($A$6;A9) возвращает номер позиции текстовой строки из ячейки А9 , с которой начинается текст, указанный в критерии (если он найден).
Например, для ячейки А14 формула ПОИСК($A$6;A14) вернет 1, а для А15 формула ПОИСК($A$6;A15) вернет 8 и обе ячейки будут выделены. Если в ячейке не содержится искомый текст, то формула возвращает ошибку и ячейка не выделяется.
Примечание : Обратите внимание, что будет выделена ячейка со словом Мотодрель , как ячейка содержащая критерий. Но, если нужно найти ячейки содержащие слово Дрель (т.е
текст, отделенный от других слов пробелами), то нужно обратиться к решению Задачи 2 (см. ниже).
Формула $A$6=ПРАВСИМВ(A9;ДЛСТР($A$6)) возвращает правую часть каждого значения перечня длиной равной критерию, т.е. в нашем случае 5 (т.к. в критерии «Дрель» 5 букв), а затем сравнивает ее с критерием. Если оба выражения совпадают, то это означает, что значение перечня заканчивается на текст, указанный в Критерии.
Примечание : Обратите внимание, что будет выделена ячейка со словом Мотодрель , как ячейка заканчивающаяся на критерий. Но, если нужно найти ячейки содержащие слово Дрель (т.е
текст, отделенный от других слов пробелами), то нужно обратиться к решению Задачи 2 (см. ниже).
Если критерий содержится в начале текстовой строки, то ПОИСК() вернет 1. Следовательно, формула ПОИСК($A$6;A9)=1 вернет ИСТИНА только для ячеек начинающихся с искомого текста.
Совет Для настройки поиска ячеек, значения которых удовлетворяют критерию с учетом РЕгиСТРА, читайте статью Выделение ячеек c ТЕКСТом с применением Условного форматирования (с учетом РЕгиСТРА) .
Управление правилами
Помимо умения создавать правила, условным форматированием также нужно корректно управлять
Особенно это важно, когда для одного диапазона применяется несколько условий. Но обо всем по порядку
Диспетчер правил условного форматирования отображает список, состоящий из условия, формата и диапазона, к которому применено правило.
В самом верху окна можно выбрать, какие правила следует выводить в списке: из текущего диапазона, с этого листа, из любого другого листа открытой книги.
Первые три кнопки диспетчера должны быть понятны без дополнительных пояснений, а вот на последних двух (стрелки вверх и вниз) остановимся подробнее.
На изображение приведено 2 правила: значение равно трем и значение больше двух. Представьте, что они применены к ячейке со числом 3. Какое из них сработает? В этом случае оба, так как между ними нет конфликта в форматировании, одно отвечает за заливку, а второе за границу. Но если бы они оба отвечали за один и тот же стиль, то выполнилось правило, которое стоит выше, потому что имеет больший приоритет.
Так вот, стрелками окна можно менять положение отдельно выделенного правила и, соответственно, его значимость.
Рассмотрим еще один случай, когда требуется выполнить только одно условие. В конце каждого правила имеется флажок «Остановить, если истина». Выставив его, Вы отменяете выполнение всех последующих правил для текущего диапазона, при условии, что это оно выполняется. Исходя из рассматриваемого примера, если ячейка содержит значение 3, то проверка на условие «больше двух» произведена не будет.
- < Назад
- Вперёд >
Новые статьи:
- Критерий Манна-Уитни
- Подключение MySQL в Excel
- Подключение Excel к SQL Server
Формула условного форматирования для игнорирования пустых ячеек
Если вы уже используете формулу условного форматирования, то делать отдельное правило для пробелов особо не нужно. Вместо этого вы можете добавить еще одно условие к существующей формуле, а именно:
- Игнорировать абсолютно пустые ячейки, которые ничего не содержат:
НЕ(ПУСТО(A1))
- Игнорировать визуально пустые ячейки, включая пустые строки:
А1«»
Где A1 — самая левая ячейка выбранного диапазона.
Допустим, в наборе данных ниже вы хотите выделить значения меньше 99,99. Это можно сделать, создав правило с помощью этой простой формулы:
=$B2
Чтобы выделить значения меньше 99,99, игнорируя пустые ячейки, вы можете использовать функцию И с двумя логическими тестами:
=И($B2«», $B2
=И(НЕ(ПУСТО($B2)), $B2
В данном конкретном случае обе формулы игнорируют ячейки с пустыми строками, так как второе условие (
Сборник формул для условного форматирования
В данной статье собран список формул, которые можно использовать в условном форматировании ячеек, заданным при помощи формулы:
- Excel 2003 : Формат (Format) —Условное форматирование (Conditional formatting) — формула;
- Excel 2007-2010 : вкладка Главная (Home) —Условное форматирование (Conditional formatting) —Создать правило (New rule) —Использовать формулу для определения форматируемых ячеек (Use a formula to determine which cells to format)
Подробнее об условном форматировании можно прочитать в статье: Основные понятия условного форматирования и как его создать
Все условия приведены для диапазона A1:A20 . Это означает, что для корректного выполнения условия необходимо выделить диапазон A1:A20 (столбцов может быть больше), начиная с ячейки A1 , после чего назначить условие. Если выделять необходимо не с первой строки, а скажем, с 4-ой, то и выделить надо будет диапазон A4:A20 и в формуле для условия указывать в качестве критерия первую ячейку выделенного диапазона — A4 .
Если необходимо выделять форматированием не только конкретную ячейку, удовлетворяющую условию, а всю строку таблицы на основе ячейки одного столбца, то перед установкой правила необходимо выделить всю таблицу, строки которой необходимо форматировать, а ссылку на столбец с критерием закрепить: = $A1 =МАКС( $A$1:$A$20 ) при выделенном диапазоне A1:F20 (диапазон применения условного форматирования), будет выделена строка A7:F7 , если в ячейке A7 будет максимальное число.
Так же можно применять не к конкретно одному столбцу, а к полностью диапазону. Но в этом случае надо знать принцип смещения ссылок в формулах, чтобы условия применялись именно к нужным ячейкам. Например, если задать условие для диапазона B1:D10 в виде формулы: = B1 , то цветом будут выделены ячейки столбца B, если значение ячейки столбца А в той же строке меньше( B1, B3). При этом если ячейки столбца D меньше ячеек столбца C в той же строке — они тоже будут выделены( D1 , D5 ).
Выделение ячеек с числами: =ЕЧИСЛО( A1 )
Выделение ячеек с числами, но не учитывая нули: =И(ЕЧИСЛО( A1 ); A1 0)
Выделение строк со значением больше 0: = A1 >0
Выделение строк со значением в диапазоне от 3 до 10: =И( A1 >=3; A1
Выделение в диапазоне $A$1:$A$20 ячейки с максимальным значением: = A1 =МАКС( $A$1:$A$20 )
Выделение в диапазоне $A$1:$A$20 ячейки с минимальным значением: =И(ЕЧИСЛО( A1 ); A1 =МИН( $A$1:$A$20 ))
Выделение в диапазоне $A$1:$A$20 ячейки со вторым по величине числом. Т.е. из чисел 1,2,3,4,5,6,7 будет выделено число 6: = A1 =НАИБОЛЬШИЙ( $A$1:$A$20 ;2)
Выделение ячеек с любым текстом: =ЕТЕКСТ( A1 )
Выделение ячеек с текстом Итог: = A1 =»Итог»
Выделение ячеек, содержащих текст Итог: =СЧЁТЕСЛИ( A1 ;»*итог*») =НЕ(ЕОШ(ПОИСК(«итог»; A1 )))
Выделение ячеек, не содержащих текст Итог: =СЧЁТЕСЛИ( A1 ;»*итог*»)=0 =ЕОШ(ПОИСК(«итог»; A1 ))
Выделение ячеек, текст которых начинается со слова Итог: =ЛЕВСИМВ( A1 ;4)=»Итог»
Выделение ячеек, текст которых заканчивается на слово Итог: =ПРАВСИМВ( A1 ;4)=»Итог»
Выделение текущей даты: = A1 =СЕГОДНЯ()
Выделение ячейки с датой, больше текущей: = A1 >СЕГОДНЯ()
Выделение ячейки с датой, которая наступит через неделю: = A1 =СЕГОДНЯ()+7
Выделение ячеек с датами текущего месяца(любого года): =МЕСЯЦ( A1 )=МЕСЯЦ(СЕГОДНЯ())
Выделение ячеек с датами текущего месяца текущего года: =И(МЕСЯЦ( A1 )=МЕСЯЦ(СЕГОДНЯ());ГОД( A1 )=ГОД(СЕГОДНЯ())) или=ТЕКСТ( A1 ;»ГГГГММ»)=ТЕКСТ(СЕГОДНЯ();»ГГГГММ»)
Выделение ячеек с выходными днями: =ДЕНЬНЕД( A1 ;2)>5
Выделение ячеек с будними днями: =ДЕНЬНЕД( A1 ;2)
Выделение ячеек, входящих в указанный период(промежуток) дат: =И( $A1 >ДАТА(2015;9;1); $A1
Выделение различий в ячейках по условию: = A1 $B1
Выделение ячейки, если ячейка следующего столбца(B) этой же строки меньше: = A1 > B1
Выделение строк цветом через одну: =ОСТАТ(СТРОКА();2)
Выделение строк цветом, если значение ячейки столбца A присутствует в диапазоне $F$1:$H$5000 : =СЧЁТЕСЛИ( $F$1:$H$5000 ; A1 )
Выделение строк цветом, если значение ячейки столбца A отсутствует в диапазоне $F$1:$H$5000 : =СЧЁТЕСЛИ( $F$1:$H$5000 ; A1 )=0
Выделение цветом ячейки, если её значение в диапазоне A1:A20 второе по счету: =СЧЁТЕСЛИ( $A$1:$A1 ; A1 )=2
Выделение ячеек, содержащих ошибки (#ЗНАЧ!; #Н/Д; #ССЫЛКА! и т.п.). Помимо просто выявления ячеек с ошибками можно применять, когда необходимо скрыть ошибочные значения в ячейках(назначив цвет шрифта таким же, как и цвет заливки): =ЕОШИБКА( A )
Выделение непустых ячеек в столбце A : = $A1 «»
Статья помогла? Поделись ссылкой с друзьями!
ПОПАРНОЕ СРАВНЕНИЕ СТРОК/ СТОЛБЦОВ (ОТНОСИТЕЛЬНЫЕ ССЫЛКИ)
Теперь будем производить попарное сравнение значений в строках 1 и 2.
Задача3 . Сравнить значения ячеек диапазона A 1: D 1 со значениями из ячеек диапазона A 2: D 2 . Для этого будем использовать относительную ссылку.
- введем в ячейки диапазона A2:D2 числовые значения (можно считать их критериями);
- выделим диапазон A1:D1 ;
- применим к выделенному диапазону Условное форматирование на значение Меньше ( Главная/ Стили/ Условное форматирование/ Правила выделения ячеек/ Меньше )
- в левом поле появившегося окна введем относительную ссылку на ячейку A2 (т.е. просто А2 или смешанную ссылку А$2 ). Убедитесь, что знак $ отсутствует перед названием столбца А.
Теперь каждое значение в строке 1 будет сравниваться с соответствующим ему значением из строки 2 в том же столбце! Выделены будут значения 1 и 5, т.к. они меньше соответственно 2 и 6, расположенных в строке 2.
Результат можно увидеть в файле примера на листе Задача3 .
Внимание! В случае использования относительных ссылок в правилах Условного форматирования необходимо следить, какая ячейка является активной в момент вызова инструмента Условное форматирование
Примечание-отступление : О важности фиксирования активной ячейки при создании правил Условного форматирования с относительными ссылками
Примечание-отступление : О важности фиксирования активной ячейки при создании правил Условного форматирования с относительными ссылками
При создании относительных ссылок в правилах Условного форматирования , они «привязываются» к ячейке, которая является активной в момент вызова инструмента Условное форматирование.
СОВЕТ : Чтобы узнать адрес активной ячейки (она всегда одна на листе) можно посмотреть в поле Имя (находится слева от Строки формул ). В задаче 3, после выделения диапазона A1:D1 (клавиша мыши должна быть отпущена), в поле Имя , там будет отображен адрес активной ячейки A1 или D 1 . Почему возможно 2 вырианта и в чем разница для правил условного форматирования?
Посмотрим внимательно на второй шаг решения предыдущей задачи3 — выделение диапазона A 1: D 1 . Указанный диапазон можно выделить двумя способами: выделить ячейку А1 , затем, не отпуская клавиши мыши, выделить весь диапазон, двигаясь вправо к D1 ; либо, выделить ячейку D1 , затем, не отпуская клавиши мыши, выделить весь диапазон, двигаясь влево к А1 . Разница между этими двумя способами принципиальная: в первом случае, после завершения выделения диапазона, активной ячейкой будет А1 , а во втором D 1 !
Теперь посмотрим как это влияет на правило условного форматирования с относительной ссылкой.
Если мы выделили диапазон первым способом, то, введя в правило Условного форматирования относительную ссылку на ячейку А2 , мы тем самым сказали EXCEL сравнивать значение активной ячейки А1 со значением в А2 . Т.к. правило распространяется на диапазон A 1: D 1 , то B 1 будет сравниваться с В2 и т.д. Задача будет корректно решена.
Если при создании правила Условного форматирования активной была ячейка D1 , то именно ее значение будет сравниваться со значением ячейки А2 . А значение из A 1 будет теперь сравниваться со значением из ячейки XFB2 (не найдя ячеек левее A 2 , EXCEL выберет самую последнюю ячейку XFD для С1 , затем предпоследнюю для B 1 и, наконец XFB2 для А1 ). Убедиться в этом можно, посмотрев созданное правило:
выделите ячейку A1 ; нажмите Главная/ Стили/ Условное форматирование/ Управление правилами ; теперь видно, что применительно к диапазону $A$1:$D$1 применяется правило Значение ячейки 6 (задан формат: красный фон) и Значение ячейки >7 (задан формат: зеленый фон), см. рисунок выше. Т.к. правило Значение ячейки >6 (задан формат: красный фон) располагается выше, то оно имеет более высокий приоритет, и поэтому ячейка со значением 9 будет иметь красный фон
На Флажок Остановить, если истина можно не обращать внимание, он устанавливается для обеспечения обратной совместимости с предыдущими версиями EXCEL, не поддерживающими одновременное применение нескольких правил условного форматирования. Хотя его можно использовать для отмены одного или нескольких правил при одновременном использовании нескольких правил, установленных для диапазона (когда между правилами нет конфликта)
Подробнее можно ]]>прочитать здесь ]]> .
Если к диапазону ячеек применимо правило форматирования, то оно обладает приоритетом над форматированием вручную. Форматирование вручную можно выполнить при помощи команды Формат из группы Ячейки на вкладке Главная . При удалении правила условного форматирования форматирование вручную остается.
Выделение через строку.
Думаю, вам часто встречалось красивое оформление таблицы, когда строчки через одну были выделены. Конечно, такое оформление легко доступно, если преобразовать данные в «умную» таблицу. Но такое возможно только в Excel 2007 и более поздних версиях. Если же у вас старая версия программы, то наш способ вам очень даже может пригодиться.
Итак, возьмем для примера небольшую таблицу.
Выделим диапазон A1:D18.
Затем создаем новое правило при помощи формулы
В чем ее смысл? Если номер, полученный при помощи функции СТРОКА(), делится без остатка на 2, то значит у нее чётный номер, и к ней следует применить правило форматирования. Если же номер не делится на 2 без остатка, то это нечетная. Ее мы оставляем без изменений.
В результате получилась «полосатая» таблица по принципу «четный-нечетный».
Как применить несколько правил условного форматирования к одной ячейке
Возможно применять несколько правил к одной ячейке.
Например, в таблице с прогнозом погоды мы хотим закрасить разными цветами показатели температуры. Условия выделения цветом: если температура выше 10 градусов – зеленым цветом, если выше 20 градусов – желтый, если выше 30 градусов – красным.
Для применения нескольких условий к одной ячейке выполним следующие действия:
Выделим диапазон с данными, к которым мы хотим применить условное форматирование => кликнем по пункту “Условное форматирование” на панели инструментов => выберем условие выделения “Больше…” и укажем первое условие (если больше 10, то зеленая заливка). Такие же действия повторим для каждого из условий (больше 20 и больше 30). Не смотря на то, что мы применили три правила, данные в таблице закрашены зеленым цветом:
Смотря на сухие цифры таблиц, трудно с первого взгляда уловить общую картину, которую они представляют. Но, в программе Microsoft Excel имеется инструмент графической визуализации, с помощью которого можно наглядно представить данные, содержащиеся в таблицах. Это позволяет более легко и быстро усвоить информацию. Данный инструмент называется условным форматированием. Давайте разберемся, как использовать условное форматирование в программе Microsoft Excel.
Для того, чтобы произвести форматирование определенной области ячеек, нужно выделить эту область (чаще всего столбец), и находясь во вкладке «Главная», кликнуть по кнопке «Условное форматирование», которая расположена на ленте в блоке инструментов «Стили».
После этого, открывается меню условного форматирования. Тут представляется три основных вида форматирования:
- Гистограммы;
- Цифровые шкалы;
- Значки.
Для того, чтобы произвести условное форматирование в виде гистограммы, выделяем столбец с данными, и кликаем по соответствующему пункту меню. Как видим, представляется на выбор несколько видов гистограмм с градиентной и сплошной заливкой. Выберете ту, которая, на ваш взгляд, больше всего соответствует стилю и содержанию таблицы.
Как видим, гистограммы появились в выделенных ячейках столбца. Чем большее числовое значение в ячейках, тем гистограмма длиннее. Кроме того, в версиях Excel 2010, 2013 и 2016 годов, имеется возможность корректного отображения отрицательных значений в гистограмме. А вот, у версии 2007 года такой возможности нет.
При использовании вместо гистограммы цветовой шкалы, также существует возможность выбрать различные варианты данного инструмента. При этом, как правило, чем большее значение расположено в ячейке, тем насыщеннее цвет шкалы.
Наиболее интересным и сложным инструментом среди данного набора функций форматирования являются значки. Существует четыре основные группы значков: направления, фигуры, индикаторы и оценки. Каждый выбранный пользователем вариант предполагает использование разных значков при оценке содержимого ячейки. Вся выделенная область сканируется Excel, и все значения ячеек разделяются на части, согласно величинам, указанным в них. К самым большим величинам применяются значки зеленого цвета, к величинам среднего диапазона – желтого, и величины, располагающиеся в самой меньшей трети – помечаются значками красного цвета.
При выборе стрелок, в качестве значков, кроме цветового оформления, используется ещё сигнализирование в виде направлений. Так, стрелка, повернутая указателем вверх, применяется к большим величинам, влево – к средним, вниз – к малым. При использовании фигур, кругом помечаются самые большие величины, треугольником – средние, ромбом – малые.
Управление правилами
В программе Excel можно применять сразу несколько правил к одному и тому же диапазону ячеек, но отображаться на экране будет только последнее введенное правило. Для того, чтобы регламентировать выполнение различных правил относительно определенного диапазона ячеек, нужно выделить этот диапазон, и в основном меню условного форматирования перейти по пункту управление правилами.
Открывается окно, где представлены все правила, которые относятся к выделенному диапазону ячеек. Правила применяются сверху вниз, так как они размещены в списке. Таким образом, если правила противоречат друг другу, то по факту на экране отображается выполнение только самого последнего из них.
Чтобы поменять правила местами, существуют кнопки в виде стрелок направленных вверх и вниз. Для того, чтобы правило отображалось на экране, нужно его выделить, и нажать на кнопку в виде стрелки направленной вниз, пока правило не займет самую последнюю строчу в списке.
Есть и другой вариант. Нужно установить галочку в колонке с наименованием «Остановить, если истина» напротив нужного нам правила. Таким образом, перебирая правила сверху вниз, программа остановится именно на правиле, около которого стоит данная пометка, и не будет опускаться ниже, а значит, именно это правило будет фактически выполнятся.
В этом же окне имеются кнопки создания и изменения выделенного правила. После нажатия на эти кнопки, запускаются окна создания и изменения правил, о которых мы уже вели речь выше.
Для того, чтобы удалить правило, нужно его выделить, и нажать на кнопку «Удалить правило».
Кроме того, можно удалить правила и через основное меню условного форматирования. Для этого, кликаем по пункту «Удалить правила». Открывается подменю, где можно выбрать один из вариантов удаления: либо удалить правила только на выделенном диапазоне ячеек, либо удалить абсолютно все правила, которые имеются на открытом листе Excel.
Как видим, условное форматирование является очень мощным инструментом для визуализации данных в таблице. С его помощью, можно настроить таблицу таким образом, что общая информация на ней будет усваиваться пользователем с первого взгляда. Кроме того, условное форматирование придаёт большую эстетическую привлекательность документу.
Мы рады, что смогли помочь Вам в решении проблемы.
Помогла ли вам эта статья?
Да Нет
Условное форматирование в новых версиях Excel мы рассматривали в видео уроке. Стандартные приемы очень удобны и наглядны. Но иногда требуется применять формат ячеек, в зависимости от каких-нибудь условий в соседних ячейках.
К нам поступил вопрос:
Давайте и рассмотрим на этом примере условное форматирование с помощью формул. Оно так и называется, потому, что без формул тут не обойтись.
Представим себе следующий пример. У нас есть таблицам с ФИО, по каждому сотруднику есть результат в процентах и информация о наличии льгот. Нам необходимо выделить с помощью условного форматирования только тех сотрудников, которые имеют результат выше 75 и имеют льготы.
При соблюдении данных условий, нам необходимо закрасить ячейку в желтый цвет. Для начала нам необходимо выделить все фамилии, далее выбрать пункт «Условное форматирование», «Создать правило», из типа правил выбрать «Использовать формулу для определения форматируемых ячеек» и нажать «Ок».
В открывшемся диалоговом окне настраиваем правило. Необходимо прописать формулу, которая при возвращении истины будет закрашивать наши ячейки.
Важно! Формула прописывается к первой ячейке (строке). Формула обязательно должна быть с относительными ссылками (без долларов), если мы хотим, чтобы она распространилась на все последующие строки
Мы прописываем формулу:
=И(B2>75;C2="Да")
И — это означает, что мы проверяем два условия и они должны обе выполняться. Если бы нужно было, чтобы выполнялось одно из условий (либо результат больше 75 либо сотрудник — льготник), то нужно было бы использовать функцию ИЛИ, еще проще если условие одно.
Вот так будет выглядеть формулу в нашем примере.
Не забудьте выбрать формат, в который необходимо закрашивать наши ячейки. Нажимаем «Ок» и проверяем.
Были закрашены Петров и Михайлов, у обоих результат выше 75 и они являются льготниками, что нам и требуется.
Надеюсь, что ответили на ваш вопрос по условному форматирования. Ставьте лайки и подписывайтесь на нашу группу в ВК.
Задача 3 (поиск нескольких слов в списке)
Рассмотрим списки, элементами которых являются отдельные слова (не фразы). Выделять будем только ту ячейку, которая содержит любое из слов-критериев (см. файл примера Лист Неск критериев (слово точно) ).
Это можно сделать несколькими способами.
Вариант с использованием Массива констант и имени. В этом случае в правиле Условного форматирования нужно ввести формулу =ИЛИ($A2=Слова) , где Слова — это Имя массива констант .
Этот способ не очень удобен, т.к. при изменении критериев придется изменять массив констант в Диспетчере имен.
Вариант с использованием именованного диапазона. Формула =ИЛИ($B2=Слова_в_диапазоне) аналогична предыдущей, но имя Слова_в_диапазоне теперь ссылается на диапазон ячеек листа (см. статью Именованный диапазон ). При добавлении новых слов в ячейки в столбце F, придется переопределять именованный диапазон.
Вариант с использованием динамического диапазона . Динамический диапазон позволяет добавлять новые слова-критерии в столбец F и при этом не требуется модифицировать формулы и имена.
Вариант без использования имен . Формула =ИЛИ($D2=»слово1″;$D2=»слово2″;$D2=»слово3″) позволяет отказаться от использования имен. Платой за это станет необходимость изменения правила Условного форматирования при каждом изменении слов-критериев.
Применить условное форматирование для каждой строки в Excel
Как мы знаем, условное форматирование создаст правило, определяющее, какие ячейки будут отформатированы. Иногда вам может потребоваться применить условное форматирование для каждой строки, как показано на скриншоте ниже. За исключением многократной установки одних и тех же правил для каждой строки, есть несколько уловок для решения этой задачи.
Метод A Изменение применимости к в диспетчере правил условного форматирования
Например, здесь вы хотите применить цвет фона заливки к ячейкам, если A2> B2, A3> B3,…, An> Bn с условным форматированием.
1. Во-первых, примените условное форматирование к A2: B2. Выберите A2: B2, затем щелкните Главная > Условное форматирование > Новое правило. Если в строке уже есть правила, просто переходите к шагу 4.
2. в Новое правило форматирования диалоговое окно, выберите Используйте формулу, чтобы определить, какие ячейки следует форматировать. от Выберите тип правила раздел, затем введите = $ A2> $ B2 в текстовое поле под Формат значений, где эта формула истинна.
3. Нажмите Формат кнопку, чтобы перейти к Формат ячеек диалоговое окно, а затем вы можете выбрать нужный тип форматирования. Например, заливка цветом фона. Нажмите OK > OK закрыть диалоги.
Теперь к строке A2: B2 применяется условное форматирование.
4. Не снимая выделения с A2: B2, нажмите Главная > Условное форматирование > Управление правилами.
5. в Диспетчер правил условного форматирования найдите правило, которое вы применили к A2: B2, измените диапазон, как вам нужно, в Относится к раздел и нажмите OK.
Затем это правило условного форматирования будет применяться к каждой строке в новом диапазоне.
Метод B Перетащите маркер автозаполнения
Если вы используете Excel 2013 или более поздние версии, вы можете использовать дескриптор автозаполнения, чтобы применить условное правило к соседним строкам.
Предположим, в строке A2: B2 применено два условного форматирования, если A2> B2, заливка красным цветом фона, если A2
Теперь вы хотите применить эти два правила к A3: B9.
1. Во-первых, вам нужно применить правила условного форматирования к строке A2: B2. Если в строке есть правила, просто перейдите к шагу 4. Выберите диапазон A2: B2, щелкните Главная > Условное форматирование > Новое правило. Если в строке уже есть правила, просто переходите к шагу 4.
2. в Новое правило форматирования диалоговое окно, выберите Используйте формулу, чтобы определить, какие ячейки нужно отформатировать, из Выберите тип правила раздел, затем введите = $ A2> $ B2 в текстовое поле под Формат значений, где эта формула истинна. Затем нажмите Формат кнопку в Формат ячеек диалога под Заполнять на вкладке выберите красный цвет. Нажмите OK > OK.
3. Не снимая выделения с строки A2: B2, включите Диалоговое окно «Новое правило форматирования» снова выберите Используйте формулу, чтобы определить, какие ячейки следует форматировать. от Выберите тип правила раздел, затем введите = $ A2 <$ B2 в текстовое поле под Формат значений, где эта формула истинна. Затем нажмите Формат кнопку в Формат ячеек диалога под Заполнять на вкладке выберите зеленый цвет. Нажмите OK > OK.
4. Затем перетащите Автозаполнение дескриптор соседних строк, к которым вы хотите применить условное правило, затем выберите Только форматирование заливки из Параметры автозаполнения.
Другие операции (статьи), связанные с форматированием Conditioanl
Подсчет / суммирование ячеек по цветам с условным форматированием в ExcelТеперь это руководство расскажет вам о некоторых удобных и простых методах быстрого подсчета или суммирования ячеек по цвету с условным форматированием в Excel.
создать диаграмму с условным форматированием в Excel Например, у вас есть таблица оценок для класса, и вы хотите создать диаграмму для цветных оценок в разных диапазонах. В этом руководстве представлен метод решения этой задачи.
Гистограмма с накоплением условного форматирования в ExcelВ этом руководстве показано, как создать столбчатую диаграмму с условным форматированием, как показано на скриншоте ниже, шаг за шагом в Excel.
Условное форматирование строк или ячеек, если два столбца равны в ExcelВ этой статье я представляю метод условного форматирования строк или ячеек, если два столбца равны в Excel.
Поиск и выделение результатов поиска в ExcelВ Excel вы можете использовать функцию «Найти и заменить», чтобы найти определенное значение, но знаете ли вы, как выделить результаты поиска после поиска? В этой статье я расскажу о двух различных способах облегчения поиска и выделения результатов поиска в Excel.
Формула для определения ячеек для форматирования
Вы также можете использовать условное форматирование для форматирования любого значения, к которому применяется определенная формула. В этом примере все данные Excel, которые еще находятся в будущем, отмечены красным цветом.
Выделите область таблицы, к которой должно применяться форматирование, и создайте новое правило. Для «Выбрать тип правила» выберите пункт «Использовать формулу для определения форматируемых ячеек».
Теперь введите нужную формулу. В нашем примере: «=A1☻TODAY()».
Нажмите на кнопку «Формат…» и выберите красный цвет в новом окне.
Подтвердите, дважды нажав на «OK».
Глава 20. Условное форматирование и формулы массива
Это глава из книги: Майкл Гирвин. Ctrl+Shift+Enter. Освоение формул массива в Excel.
Несколько важных фактов об условном форматировании на основе формул:
- Проверка условия возвращает значения ИСТИНА/ЛОЖЬ и применяется к диапазону ячеек.
- Условие проверяется для каждой ячейки в диапазоне. Если условие выполнено, ячейка получает форматирование. Если условие не выполнено, ячейка не форматируется.
- Для создания условия подходит любая формула, возвращающая значения ИСТИНА/ЛОЖЬ (если формула возвращает число, то любое число, кроме нуля приравнивается к значению ИСТИНА, а значение ноль – ЛОЖЬ).
- Условное форматирование можно использовать, как с обычными формулами, так и с формулами массива.
- Условное форматирование является волатильным (летучим): оно пересчитывается довольно часто, даже, когда вы напрямую не задействуете диапазон, в отношении которого применено условное форматирование (см. главу 13). Это замедляет работу.
Скачать заметку в формате Word или pdf, примеры в формате Excel
Чтобы создать условное форматирование с помощью формул:
Выделите диапазон ячеек
Обратите внимание, активна левая верхняя ячейка диапазона (она светлее других).
Пройдите по меню ГЛАВНАЯ –>Условное форматирование –>Создать правило, или нажав, и удерживая клавишу Alt, последовательно нажмите Я, Л, С. Откроется диалоговое окно Создание правила форматирования.
Выберите опцию Использовать формулу для определения форматируемых ячеек.
Поместите курсор в поле Форматировать значения, для которых следующая формула является истинной.
Наберите формулу, которая будет применяться к активной ячейке выделенного диапазона. Примените абсолютные и относительные ссылки, как если бы вы копировали формулу на весь диапазон (подробнее см
Относительные, абсолютные и смешанные ссылки на ячейки в Excel). Помните, что условие, которое вы создаете, будет оцениваться отдельно для каждой ячейки, чтобы определить, должна ли эта ячейка получить форматирование.
Нажмите кнопку Формат и выберите любую комбинацию форматирования на четырех вкладках: Число, Шрифт, Граница и Заливка.
Нажмите несколько раз кнопку ОК, закрывая все диалоговые окна.
Примените абсолютные и относительные ссылки, как если бы вы копировали формулу на весь диапазон (подробнее см. Относительные, абсолютные и смешанные ссылки на ячейки в Excel). Помните, что условие, которое вы создаете, будет оцениваться отдельно для каждой ячейки, чтобы определить, должна ли эта ячейка получить форматирование.
Нажмите кнопку Формат и выберите любую комбинацию форматирования на четырех вкладках: Число, Шрифт, Граница и Заливка.
Нажмите несколько раз кнопку ОК, закрывая все диалоговые окна.
Рассмотрим два примера условного форматирования на основе формул массива. Первый пример (рис. 20.1) показывает, как выделить строку, содержащую наименьшее значение для города, введенного в ячейку A11. Выделимте диапазон А3:В8 и откройте диалоговое окно Создание правила форматирования, как описано выше. (Если вы выполняете пример в приложенном файле Excel, у вас откроется окно Изменение правила форматирования, так как правило уже создано)
Обратите внимание на смешанную ссылку на ячейку $В3 в формуле. Она позволяет каждой ячейки в диапазоне при поиске минимального значения для города, оставаться в столбце В. Обратите внимание, что формула массива возвращает одно и тоже значение для каждой ячейки в диапазоне
Но только в ячейках А5 и В5 это значение равно проверяемому. Если ячеек много волатильный характер вычислений может замедлить работу. Второй пример (рис. 20.2), обходит эту трудность, используя вспомогательную ячейку
Обратите внимание, что формула массива возвращает одно и тоже значение для каждой ячейки в диапазоне. Но только в ячейках А5 и В5 это значение равно проверяемому
Если ячеек много волатильный характер вычислений может замедлить работу. Второй пример (рис. 20.2), обходит эту трудность, используя вспомогательную ячейку.
Рис. 20.1. Формула массива в диалоговом окне Изменение правила форматирования; Чтобы увеличить изображение кликните на нем правой кнопкой мыши и выберите Открыть картинку в новой вкладке
Цель форматирования (рис. 20.2) – выделить строки с N лучшими результатами гонщика, указанного в ячейке А17. Число N указано в ячейке В17. Формулы массива, вычисляющие второе лучшее время для гонщика, приведены в ячейках D18 и D19 (используйте любую из них). Формула =И($A3=$A$17;$B3 Архивы