В этой статье вы узнаете, как закрасить ячейку по условию, выделять целые строки и столбцы в Excel 2020, 2013 и 2010 на основе какого-то критерия, а также найдете несколько советов и примеров формул, которые будут работать с числовыми значениями и значениями текстовых ячеек.
Узнайте, как в Экселе быстро закрасить всю строку или столбец на основе значения отдельной ячейки в ваших таблицах Excel. Советы и примеры формул для числовых и текстовых значений.
- Выделение по условию целиком строки или столбца.
- Выделение строки.
- Выделение столбца.
- Как найти и закрасить совпадающие ячейки в столбцах.
- Выделение совпадений двух столбцов построчно.
- Как найти и закрасить совпадения в нескольких столбцах.
Мы уже обсуждали, что такое условное форматирование и как изменить цвет фона ячейки в зависимости от ее значения. Для этого рекомендуем ссылки с конце этого материала. Сейчас же мы рассмотрим более сложные вещи.
Выделение по условию целиком строки или столбца.
Выделение строки.
В нашем распоряжении – таблица Excel с информацией о продажах в различные страны. Давайте попробуем выделить определенные строки с продажами в Бразилию. То есть, окраска их должна изменяться в связи с тем, что записано в колонке «Страна».
Прежде всего выделяем при помощи мыши весь диапазон интересующих нас данных – A2:D21. Шапку таблицы выделять не нужно. Затем действуем по уже отработанной схеме: вызываем меню функции и выбираем последний пункт – «Использовать формулу для определения форматируемых ячеек» (1). Далее записываем выражение (2):
=$C2 = «Бразилия»
Мы должны закрасить вторую строку таблицы в зависимости от значения в С2. Здесь есть маленькая хитрость.
Обратите внимание, что абсолютная ссылка (знак $) установлена здесь только на столбец С. То есть, мы проверяем на условие «Бразилия» в выделенном нами диапазоне все позиции в этом столбце, то есть С2, С3, C4 и так далее. А вот закрашивать будем всю строку, так как ранее выделена была вся таблица. Для этого выбираем вариант оформления (3): цвет фона или шрифта, либо оба.
Напомню, что знак $, стоящий перед буквой столбца, означает абсолютную ссылку на этот столбец. А если знак $ находится перед цифрой, то абсолютная ссылка установлена на строку.
Вывод. Условное форматирование строки по значению ячейки основано на грамотном применении абсолютных и относительных ссылок в правиле форматирования. В используемой формуле должна быть абсолютная ссылка на столбец и относительная — на строку ($C2). При этом как область форматирования должна быть обозначена вся таблица (без шапки).
Выделение столбца.
Аналогичную операцию можно произвести и с выделением отдельных столбцов. Естественно, при этом формула будет выглядеть несколько иначе: знак доллара будет находиться перед цифрой. Но, конечно, выделение горизонтальных линий в таблице встречается намного чаще.
Тем не менее, давайте рассмотрим пример с выделением по условию столбцов таблицы.
Итак, у нас есть табель рабочих смен. Нужно красным указать в нем на субботы и воскресенья.
Как и в предыдущем примере, определим для начала диапазон, который мы будем форматировать: =$B$3:$S$7. И вновь будем использовать формулу (2) для определения условия.
=ДЕНЬНЕД(B$2;2)>5
Функция ДЕНЬНЕД позволяет определить номер дня недели по указанной дате. Цифра 2 означает, что используется привычный нам порядок, когда первый день недели – это понедельник.
Таким образом, если номер окажется больше 5 (то есть, это будет суббота или воскресенье), то необходимо применить указанный нами формат (3) и закрасить выходной день.
Все просто, но обратите внимание на одну важную деталь: знаком $ в ссылке мы фиксируем номер. Тем самым мы указываем программе, что надо последовательно перемещаться по второй строке в пределах указанного диапазона, и определять номер дня недели. И после этого применять формат к столбцу.
Условное форматирование в Excel 2003
156204 23.10.2012
Основы
Все очень просто. Хотим, чтобы ячейка меняла свой цвет (заливка, шрифт, жирный-курсив, рамки и т.д.) если выполняется определенное условие. Отрицательный баланс заливать красным, а положительный — зеленым. Крупных клиентов делать полужирным синим шрифтом, а мелких — серым курсивом. Просроченные заказы выделять красным, а доставленные вовремя — зеленым. И так далее — насколько фантазии хватит.
Чтобы сделать подобное, выделите ячейки, которые должны автоматически менять свой цвет, и выберите в меню Формат — Условное форматирование
(Format — Conditional formatting)
.
В открывшемся окне можно задать условия и, нажав затем кнопку Формат
(Format), параметры форматирования ячейки, если условие выполняется. В этом примере отличники и хорошисты заливаются зеленым, троечники — желтым, а неуспевающие — красным цветом:
Кнопка А также>>
(Add)
позволяет добавить дополнительные условия. В Excel 2003 их количество ограничено тремя, в Excel 2007 и более новых версиях — бесконечно.
Если вы задали для диапазона ячеек критерии условного форматирования, то больше не сможете отформатировать эти ячейки вручную. Чтобы вернуть себе эту возможность надо удалить условия при помощи кнопки Удалить
(Delete)
в нижней части окна.
Другой, гораздо более мощный и красивый вариант применения условного форматирования — это возможность проверять не значение выделенных ячеек, а заданную формулу:
Если заданная формула верна (возвращает значение ИСТИНА), то срабатывает нужный формат. В этом случае можно задавать на порядок более сложные проверки с использованием функций и, кроме того, проверять одни ячейки, а форматировать — другие.
Выделение цветом всей строки
Главный нюанс заключается в знаке доллара ($) перед буквой столбца в адресе — он фиксирует столбец, оставляя незафиксированной ссылку на строку — проверяемые значения берутся из столбца С, по очереди из каждой последующей строки:
Выделение максимальных и минимальных значений
Ну, здесь все достаточно очевидно — проверяем, равно ли значение ячейки максимальному или минимальному по диапазону — и заливаем соответствующим цветом:
В англоязычной версии это функции MIN и MAX, соответственно.
Выделение всех значений больше(меньше) среднего
Аналогично предыдущему примеру, но используется функция СРЗНАЧ (AVERAGE) для вычисления среднего:
Скрытие ячеек с ошибками
Чтобы скрыть ячейки, где образуется ошибка, можно использовать условное форматирование, чтобы сделать цвет шрифта в ячейке белым (цвет фона ячейки) и функцию ЕОШ
(ISERROR)
, которая выдает значения ИСТИНА или ЛОЖЬ в зависимости от того, содержит данная ячейка ошибку или нет:
Скрытие данных при печати
Аналогично предыдущему примеру можно использовать условное форматирование, чтобы скрывать содержимое некоторых ячеек, например, при печати — делать цвет шрифта белым, если содержимое определенной ячейки имеет заданное значение («да», «нет»):
Заливка недопустимых значений
Сочетая условное форматирование с функцией СЧЁТЕСЛИ (COUNTIF)
, которая выдает количество найденных значений в диапазоне, можно подсвечивать, например, ячейки с недопустимыми или нежелательными значениями:
Проверка дат и сроков
Поскольку даты в Excel представляют собой те же числа (один день = 1), то можно легко использовать условное форматирование для проверки сроков выполнения задач. Например, для выделения просроченных элементов красным, а тех, что предстоят в ближайшую неделю — желтым:
P.S.
Счастливые обладатели последних версий Excel 2007-2010 получили в свое распоряжение гораздо более мощные средства условного форматирования — заливку ячеек цветовыми градиентами, миниграфики и значки:
Вот такое форматирование для таблицы сделано, буквально, за пару-тройку щелчков мышью…
Ссылки по теме
- Выделение дубликатов в списке цветом
- Сравнение двух списков и выделение цветом совпадающих элементов.
- Создание графиков проектов (дежурств, отпусков и т.д.) с помощью условного форматирования
Светлана Ч.
12.04.2013 15:12:26
Коллеги, подскажите, как задать правило для форматирования ячейки, на которую перехожу по гиперссылке???? Ссылка
Николай Павлов
03.07.2013 09:27:32
Вы хотите подсвечивать цветом ячейку, куда приводит гиперссылка? Не думаю, что это возможно. Родитель Ссылка
Надежда Германенко
02.07.2013 15:59:56
Доброго времени суток. Подскажите пожалуйста, с помощью условного форматирования задаю след. условия: — если значение меньше 14г.00мес.00дн — выделяем зеленым (например) цветом, если значение больше либо равно 14г.00меч.00дн — выделяем красным (например). Значение на которое создаю условное форматирование — расчетное. Получается следующее: 1. если значение равно формату 00г.00мес.00дн. (10г.03мес.15дн.) — все условия выполняются 2. если значение равно формату 0г.00мес.00дн. (3г.03мес.15дн.) — выделяет красным Как сделать так, чтобы формат расчетного значения получался всегда 00г.00мес.00дн. (03г.03мес.15дн.) Спасибо. Ссылка
Николай Павлов
03.07.2013 09:26:48
Не видя вашего файла — трудно что-то определенное сказать. Лучше сделайте тему на форуме (предварительно прочитав правила), приложите свой файл — поможем! Родитель Ссылка
Анна Вишневская
01.08.2013 11:06:07
Добрый день, подскажите делаю все так как написано в примере (Проверка дат и сроков Поскольку даты в Excel представляют собой те же числа (один день = 1), то можно легко использовать условное форматирование для проверки сроков выполнения задач. Например, для выделения просроченных элементов красным, а тех, что предстоят в ближайшую неделю — желтым:) все равно не выходит((((((((( подскажите как добавить сюда файл, чтоб показать как у меня получается? Ссылка
Николай Павлов
10.08.2013 01:28:42
Сюда — никак, лучше создайте тему на Форумеи приложите ваш файл. Родитель Ссылка
Art Key
07.08.2013 13:04:32
не могу в 10 офисе найти позиции, что бы при одном условии окрашено было в один цвет, а при невыполнении условия в другой цвет Ссылка
Николай Павлов
10.08.2013 01:27:52
Нужно создавать два разных правила с двумя разными цветами — одно на выполнение, и другое — на невыполнение условия. Родитель Ссылка
Art Key
12.08.2013 12:10:43
Спасибо, Николай А можете еще в таком вопросе помочь: в условном форматировании задаю Гистограммы, с помощью которых хочу сделать заливку ячеек (например столбец А) цветом в зависимости от сравнения данных в этой ячейке с данными ячеек из другого столбца (назовем столбец В).
Для первой ячейки срабатывает (назовем ее А1) , но для других ячеек (А2, А3…) в этом же столбце идет сравнение только ячейкой, с которой сравнивается самая первая (то есть В1). Убираю в А1 знак доллара перед единицей, а Excel мне отвечает, что в гистограммах работать с относительными ссылками нельзя
Подскажите как выйти из ситуации, как сделать заливку в ячейки в зависимости от ячейки, находящейся на той же строке
Родитель Ссылка
Тимур Макамбетов
14.09.2013 16:32:16
Николай добрый день! помогите мне! мне надо в excel сделать вот так ФИО столбец, ДАТА столбец и еще ДАТА !столбец! на втором столбце где дата например сегодня дата 14,09,2013, на третьем столбце где дата например 30 дней прошла (или 7 дней) и она высвечивался или цветом красным выделялось ячейка полностью! как это можно сделать? помогите !!!
Ссылка
Николай Павлов
24.11.2013 10:53:49
Тимур, последний пункт прочитайте («Проверка дат и сроков») — это ж оно один в один. Родитель Ссылка
Андрей Мицкевич
19.09.2013 08:06:05
День добрый! Подскажите, пожалуйста, как правильно задать правила условного форматирования для выделения минимального значения в каждой строке «умной таблицы»? Ссылка
Николай Павлов
24.11.2013 10:53:02
Также, как и обычной. Только используйте обычные адреса ячеек (вводите их с клавиатуры) вместо названий столбцов (типа [@цена]), которые Excel будет пытаться подставить в формулу, т.к. это умная таблица. Родитель Ссылка
Jelena Ivanova
26.11.2013 14:09:19
Dobrij denj. mne nuwno na neskolkih sheetah zakrasit opredelenuju datu vezde odnim cvetom mowet ( naprimer 27.11. krasnim,28.11. sinim podskawite kak jeto sdelatj? Ссылка
Николай Павлов
18.01.2014 19:28:32
Посмотрите это видео для начала. Думаю, что вопрос отпадет Родитель Ссылка
Алексей Вяткин
18.01.2014 16:51:19
Добрый День. не получается Спрятать ошибку (#Н/Д) формулы ВПР. Ссылка
Николай Павлов
18.01.2014 19:26:17
Как вам помочь, не видя вашего файла? Телепатически? Создайте тему на форуме, приложите ваш файл — поможем. Родитель Ссылка
Googlogmob
11.02.2014 18:25:57
если просто спрятать — то функция =ЕСЛИОШИБКА(ВПР(……);»—«), в таком случае при возврате ошибки отобразится «—» а если нужно разобраться почему возникает ошибка, то без примера — никак Родитель Ссылка
Андрей
28.08.2015 14:40:30
в очередной раз облегчили жизнь! Спасибо! Ссылка
Andy Ya
20.03.2016 12:15:35
Коллеги, подскажите, как использовать логические ИСТИНА и ЛОЖЬ для условного форматирования значками? CheckBox (флажок) генерирует ИСТИНА и ЛОЖЬ. Считал тривиальным действием связав CheckBox с А1 показать вместо ИСТИНА И ЛОЖЬ графический значок. Но это не работает. Ссылка
Рената Крвицкая
03.01.2017 16:39:50
подскажите, а как сделать так чтобы закрашивалось количество ячеек в строке в зависимости от числа ?? ну например, число 5 — закрасилось пять ячеек в сторке, 1 — одна ?? Ссылка
Николай Павлов
04.01.2017 09:27:47
Выделить ячейки, Главная — Условное форматирование — Создать правило — Использовать формулу
и ввести что-то похожее:
Т.е. используем функцию
СТОЛБЕЦ (COLUMN)
, чтобы определить номер очередной ячейки, и если он меньше заданного — заливаем. Родитель Ссылка
Рената Крвицкая
04.01.2017 12:28:42
:(спасибо за ответ, но что-то у меня ничего не меняется Родитель Ссылка
Катя П.
24.05.2017 09:48:39
Добрый день! Подскажите как можно выделить всю строку в зависимости от того, содержит ли ячейка в первом столбце ЧАСТЬ текста или нет. Например, когда вся ячейка должна была содержать текст sales, то было просто — =$A1=»sales». А как быть если текст разный и совпадает только слово sales? Спасибо заранее! Ссылка
Николай Павлов
26.07.2017 09:02:34
Можно использовать функцию ПОИСК для проверки вхождения одного текста в другой. Будет примерно так: =ПОИСК(«sales»;$A1)>0 Родитель Ссылка
Мурад Алихуджаев
23.07.2017 20:42:31
Здравствуйте, как сделать так, чтобы при значении в ячейке сегодняшней даты, строка окрашивалась в красный цвет, при значении меньше -в сирий, а больше в зелёный? Есть четыре столбца, ФИО телефон, время и дата, вот как сделать чтобы строка опрашивалась, а не только дата? Ссылка
Николай Павлов
26.07.2017 08:58:01
Мурад, посмотрите статью про подсветку дат- как раз ответ на ваш вопрос Родитель Ссылка
Дмитрий
01.09.2017 17:36:43
Добрый день. Помогите пожалуйста, есть ряд в котором указаны названия дней сокращенно ( деньв каждой ячейке отдельно — пн вт ср чт пт сб вс пн вт ср чт пт сб вс…….) и так далее. Как с помощью формулы в условном форматировании выделить нужные мне 3 дня (например вс пн вт). Я хочу чтобы все эти дни в ряду были выделены одним цветом. Ссылка
Юрий Владимиров
26.02.2019 14:12:02
Отличная статья! СПАСИБО! Ссылка
Юрий Владимиров
21.04.2020 12:59:41
Как сделать форматирование ячейки которая содержит текст — понятно. Подскажите пожалуйста, как сделать форматирование ячейки которая содержит формулы? Ссылка
Андрей Се
04.06.2020 13:25:11
Думаю подобное у вас и школяры уже не спрашивают..Вопрос такой при условном форматировании нужно чтобы заливка производилась по двум условиям 1 содержимое ячейки в промежутке от 5 до 10 . 2 значение ячейки K1 Вкл Методом научного тыка пришел к заключению что при помощи функции И данную затею реализовать реально. На вкладке условное форматирование выбрал правило «Использовать формулу для определения форматируемых ячеек» Вписываем =И(>5;<10;K1=»Вкл»;) Задаем цвет заливки …и ничего не происходит,ну или ошибку выдает я уже по разному пробовал.Просто не сталкивался никогда а тут пришлось Изображение Ссылка
Выделение через строку.
Думаю, вам часто встречалось красивое оформление таблицы, когда строчки через одну были выделены. Конечно, такое оформление легко доступно, если преобразовать данные в «умную» таблицу. Но такое возможно только в Excel 2007 и более поздних версиях. Если же у вас старая версия программы, то наш способ вам очень даже может пригодиться.
Итак, возьмем для примера небольшую таблицу.
Выделим диапазон A1:D18.
Затем создаем новое правило при помощи формулы
=ОСТАТ(СТРОКА();2)=0
В чем ее смысл? Если номер, полученный при помощи функции СТРОКА(), делится без остатка на 2, то значит у нее чётный номер, и к ней следует применить правило форматирования. Если же номер не делится на 2 без остатка, то это нечетная. Ее мы оставляем без изменений.
В результате получилась «полосатая» таблица по принципу «четный-нечетный».
Окрасим ячейку в определенный цвет, если она равна чему-то
Давайте вернемся к нашей таблице в изначальном виде. И теперь мы поменяем цвет там, где содержится цифра 40 на красный цвет, а с цифрой 50 на желтый. Конечно, для этого дела можно воспользоваться первым способом, но мы же хотим знать больше возможностей Excel.
В этот раз мы воспользуемся функцией «Найти и заменить».
Выделите тот участок таблицы, в который будем вносить изменения. Если это весь лист, то выделять нет смысла.
Теперь время открыть окно поиска. На вкладке «Главная» в разделе «Редактирование» нажмите на кнопку «Найти и выделить».
Можно же и горячими клавишами пользоваться: CTRL + F
В поле «Найти» мы указываем то, что ищем. В данном случае пишем «40», а затем жмем кнопку «Найти все».
Теперь, когда ниже были показаны результаты поиска, выберите одно из них и нажмите на сочетание CTRL + A, чтобы выбрать их все сразу. А затем нажмите на «Закрыть», чтобы убрать окно «Найти и заменить».
Когда у нас выбраны все, содержащие цифру 40, на вкладке «Главная» в разделе «Шрифт» выберите окраску ячейки. У нас это красный. И, как вы видите у себя на экране, так и у меня на скриншоте, они окрасились в красный.
Теперь те же самые действия нужно выполнить, чтобы окрасить те, где указано число 50. Думаю, теперь вам понятно, как сделать это.
У вас получилось? А посмотрите, что вышло у меня.
На этом все. Спасибо, друзья. Подписывайтесь, комментируйте, вступайте в группу, делитесь в соц сетях и будьте всегда в курсе новых статей. А также, не забывайте изучать и другие статьи на этом сайте.
Закрасить группу строк.
Зачем это нужно? К примеру, у нас имеются помесячные данные о продажах. Тогда логично было бы отделить каждые 3 месяца, чтобы хорошо видны были результаты по кварталам.
Используем выражение
=ОСТАТ(ЦЕЛОЕ((СТРОКА()-2)/3)+1;2)
Поясним эти вычисления.В качестве счетчика мы будем использовать номер текущей строки.Поскольку в квартале 3 месяца, то группировать будем тоже по три. Отсчет начинаем с А2.
В результате мы разбили нашу таблицу на тройки, определили каждой тройке свой порядковый номер, и с нечетными номерами произвели действия по изменению формата их представления.
Аналогично можно разбить на группы по 4 строки. Тогда в формуле выше просто замените цифру 3 на 4. И все будет работать.
А если в шапке вашей таблицы больше, чем одна строка, просто замените -2 на большую цифру, соответственно высоте шапки таблицы.
Как видите, подход достаточно универсальный. Надеюсь, вам пригодится.
Как задать правила для форматирования
Возьмем реальный пример: список магазинов и продажи каждого по месяцам. Скачайте файл-заготовку, в котором можно будет экспериментировать, или используйте свои данные.
Кнопка «Условное форматирование» находится в главном меню (блок Стили), нажав на которую мы видим 7 основных правил.
Попробуем создать первое правило и выбираем 6-й пункт.
Обратите внимание
Этот пункт дублирует первые пять, с тем отличием, что здесь мы можем детально проработать дизайн выделения (например, цвет шрифта или фона), а в остальных пунктах придется довольствоваться предустановленными режимами.
В наших примерах мы будем пользоваться быстрыми способами, предустановленные режимы которых отлично справляются с задачей.
Вставляем отделяющие линии между группами строк.
Если у вас есть большой отсортированный по какому-либо столбцу список, то было бы неплохо автоматически отделять получившиеся группы разделительными горизонтальными линиями для наглядности.
Давайте красиво оформим смету работ, упорядоченных по дням. Отделим горизонтальными линиями каждый новый день, чтобы визуально отделить их друг от друга.
Для этого сначала выделим весь наш диапазон с данными.
Внимание! Первую шапку таблицы не выделяем, начинаем с данных!
В нашем случае, выделяем A3:G33.
Затем далее действуем по уже отработанной схеме. В меню условного форматирования выбираем использование формулы (1). Далее записываем само правило:
=$B3<>$B2
Иначе говоря, мы проверяем, равна ли наша текущая дата предыдущей. Если не равна, значит, мы перешли к новому дню. Соответственно наше текущее положение нужно выделить. Выбираем формат (3). Тип границы – линия (4). Она будет использоваться по верхней границе (5).
В результате каждый новый день будет отделен от предыдущего горизонтальной линией. Естественно, вы можете выбрать и иной стиль форматирования – например, цветом.
Как создать правило
Если пред настроенные условия не подходят, вы можете создавать свои правила. Для настройки проделаем следующие шаги:
- Выделим диапазон данных. Кликнем на пункт “Условное форматирование” в панели инструментов. В выпадающем списке выберем пункт “Новое правило”:
- Во всплывающем окне нам нужно выбрать тип применяемого правила. В нашем примере нам подойдет тип “Форматировать только ячейки, которые содержат”. После этого зададим условие выделять данные, значения которых больше “57”, но меньше “59”:
- Кликнем на кнопку “Формат” и зададим формат, как мы это делали в примере выше. Нажмите кнопку “ОК”:
Условное форматирование для сравнения двух столбцов.
Когда необходимо произвести сравнение двух столбцов в таблице, очень хороший способ указать на имеющиеся совпадения и различия – выделить их.
Как найти и закрасить совпадающие ячейки в столбцах.
Можно использовать специальный пункт вкладки «Условное форматирование» — «Повторяющиеся значения».
На рисунке вы видите, что дубликаты выделены зеленым. Думаю, здесь все довольно просто.
Выделение совпадений двух столбцов построчно.
Если у нас есть несколько копий одной и той же таблицы, может возникнуть необходимость найти и показать их различия и совпадения. В этом случае попробуем сравнить столбцы таблиц построчно.
Чтобы сравнить данные в каждой строке двух столбцов таблицы, воспользуемся формулами условия.
Выберите, в каких ячейках вы будете отмечать совпадения – в первой или во второй таблице. Я выделил B3:B25. То есть, в первой таблице мы закрасим ячейки, которые дублируются во второй таблице.
Обратите внимание, что в формуле используется абсолютная адресация на колонку. Это необходимо для того, чтобы происходило последовательный перебор значений, двигаясь вниз начиная с B3 до B25.
Как найти и закрасить совпадения в нескольких столбцах.
Представим, что наша задача – найти и выделить в столбце таблицы те значения, которые совпадают хотя бы с одним столбцом второй таблицы. В нашем случае – мы последовательно будем брать данные из столбца B и определять, нет ли такого же значения в этой же строке в нескольких колонках второй таблицы.
Давайте закрасим цветом те ячейки в столбце B, которые хотя бы однажды встречаются в G,H и I.
Диапазон форматирования – B3:B25. Выделяем его и в меню – «Создать правило» выбираем «Использовать формулу…»
Запишем правило условного форматирования:
=ИЛИ($B3=$G3;$B3=$H3;$B3=$I3)
Последовательно двигаемся сверху вниз и сравниваем каждую ячейку колонки B с находящимися в той же горизонтали значениями в G,H и I.
То есть, необходимо, чтобы выполнялось хотя бы одно из условий, достаточно одного совпадения.
Но если столбцов будет не 3, а, предположим, 10? Формула станет слишком громоздкой. Ведь придется указать 10 критериев совпадения.
Есть более простой способ. Изменим правило форматирования и используем функцию СЧЁТЕСЛИ:
=ЕСЛИ(СЧЁТЕСЛИ($G3:$I3;$B3)>0;1;0)
СЧЁТЕСЛИ определяет, как часто определенное значение встречается в диапазоне. Считаем, сколько раз значение из B3 встречается в G,H и I таблицы, то есть в $G3:$I3. Если будет более одного совпадения, то срабатывает правило.Функция возвращает 1. А 1 в логическом выражении соответствует ИСТИНА, 0 — ЛОЖЬ. То есть, если счет равен нулю, то в текущей позиции нашего столбца содержится уникальное значение, которое больше нигде в диапазоне поиска не встречается. Согласитесь, так гораздо удобнее, чем писать множество однотипных критериев.
И теперь при помощи такого подхода мы можем решить более сложную задачу: выделить в B те данные, которые хотя бы раз встречаются в одном из нескольких столбцов.
Вот это новое правило:
=ЕСЛИ(СЧЁТЕСЛИ($G$3:$I$25;$B3)>0;1;0)
Теперь совпадения мы ищем во всех столбцах таблицы 2, а не только в одном из них. Возможно, такой пример вам также будет полезен.
Обратите еще раз внимание на то, как определены абсолютные ссылки. Суть в том, что должен меняться номер строки, но не номер столбца. Тогда все будет работать.
Как это работает?
В файле примера для пояснения работы механизма выделения строк, создана дополнительная таблица с формулой =$C7=$E$9 из правила Условного форматирования для зеленого цвета. Формула введена в верхнюю левую ячейку и скопирована вниз и вправо.
Как видно из рисунка, в строках таблицы, которые выделены зеленым цветом, формула возвращает значение ИСТИНА.
В формуле использована относительная ссылка на строку ($C7, перед номером строки нет знака $). Отсутствие знака $ перед номером строки приводит к тому, что при копировании формулы вниз на 1 строку она изменяется на =$C8=$E$9 , затем на =$C9=$E$9 , потом на =$C10=$E$9 и т.д. до конца таблицы (см. ячейки G8 , G9 , G10 и т.д.). При копировании формулы вправо или влево по столбцам, изменения формулы не происходит, именно поэтому цветом выделяется вся строка.
В случае затруднений можно потренироваться на примерах, приведенных в статье Условное форматирование в MS EXCEL .
Прием с дополнительной таблицей можно применять для тестирования любых формул Условного форматирования .
Выделение ячейки цветом, если значение равно
Допустим, нам нужно выбрать все ячейки с фамилией Петров или цифрой 50. Самый простой способ – использовать «Правила выделения ячеек» и пункт «Равно».
Итог – все ячейки со значением 50 становятся цветными.
Подсветка цветом строки активной ячейки в Excel
Как всем хорошо известно, с помощью условного форматирования вы можете изменить (выделить) формат ячеек, которые соответствуют определенным критериям. В нашем случае должны быть выделены только те ячейки, которые расположены в той же строке, что и активная ячейка. Исходные данные для примера будут выглядеть так, как показано на рисунке ниже:
Выберите диапазон ячеек B2:K23 так, чтобы ячейка B2 оставалась активной.
Затем выберите инструмент: «ГЛАВНАЯ»-«Условное форматирование»-«Создать правило»-«Использовать формулу для форматируемых ячее». В поле «Форматировать значения, для которых следующая формула является истинной:» введите следующую формулу.
Внимание: Адрес ячейки в левой части формулы B2 должен соответствовать АКТИВНОЙ ЯЧЕЙКИ выбранного диапазона в момент выделения! Посмотрите на рисунок ниже. На нем вы можете видеть, что в выбранной области одна ячейка (если вы ничего не напутали, это будет ячейка в верхнем левом углу диапазона) не окрашена (отсутствует синий фон). Эта ячейка является активной ячейкой в момент выделения диапазона. Адрес именно этой ячейки вы должны использовать при условном форматировании. Как вы можете видеть, в нашем примере это ячейка B2.
Запись СТРОКА(B2) = АктивнаяСтрока означает то, что ячейка выбранного диапазона будет отформатирована, если номер строки этой ячейки равен значению, хранящемуся в созданном имени диапазона АктивнаяСтрока.
То, что сейчас вы должны сделать, так это создать это имя и найти способ присвоить ему номер строки активной ячейки. Для начала создайте новое имя (CTRL+F3) и присвойте ему для начала просто значение ноль.
Далее вам нужно каким-то способом изменять значение, присвоенное этому имени, в зависимости от строки, в которой находится активная ячейка. Для этой цели мы будем использовать код VBA.
Запустите редактор VBA (Alt+F11) и для листа (Например, «Пример2»), в котором вы хотите подсвечивать любые строки и где создано условное форматирование , введите код:
Private Sub Worksheet_SelectionChange( ByVal Target As Range) ActiveWorkbook.Names( «АктивнаяСтрока» ).RefersTo = «=» & ActiveCell.Row End Sub
Если вы бы хотели, чтобы строки подсвечивались цветом только при перемещении в пределах определенного диапазона, вы можете немного изменить код, который мог бы выглядеть так (для нашего Примера 1):
Private Sub Worksheet_SelectionChange( ByVal Target As Range) If Not Intersect(Target, Range( «B2:K23» )) Is Nothing Then ActiveWorkbook.Names( «АктивнаяСтрока» ).RefersTo = «=» & ActiveCell.Column End If End Sub
Выделение диапазонов несколькими цветами по условию
Другой вариант: нам необходимо в одном столбце выделить разные диапазоны разным цветом.
Например, меньше 30 один диапазон и более 40 – другой диапазон. Выделяем столбец и идем через пункт «условное форматирование» в «правила выделения ячеек» — «меньше». В строке «форматировать ячейки, которые меньше…» ставим значение 30 и выбираем цвет красный текста и выделения ячеек.
Нажимаем ОК. Теперь повторяем все тоже самое, но выбираем уже пункт не «меньше», а «больше».
В строке «форматировать ячейки, которые больше…» ставим значение 40. Цвет выбираем любой другой, например желтый. В результате применения параметров получим следующее.
У меня все значения были больше 40, поэтому желтым цветом закрасился весь столбец. А так, будут выделены те ячейки, которые несут заданный диапазон.
Аналогичным образом можно сделать форматирование по всем тем условиям, которые даны в соответствующем пункте. Однако, бывает ситуация, когда шаблонные условия не удовлетворяют и вам нужно свое особое форматирование. Что делать? Надо просто создать свое условие.
Как подсветить сроки и даты в ячейках в Excel?
Добрый день уважаемый пользователь Microsoft Excel!
Темой статьи будет возможность подсветить сроки и даты в ячейках Excel, которые будут указаны в условиях как критические. Это необходимо когда выполнение любых условий привязанных к датам являются очень важными.
Статья будет полезна тем, кому важны сроки окончания действий договоров, для их перезаключения, проведения тендеров, тут вообще необходимо жёсткое соответствие срокам, отслеживания дат отгрузок товаров согласно соглашениям, ведение строгой и простой отчётности и т.п… Как видите, сфера применения очень обширна и данная возможность пригодится многим юристам, работникам кадровых служб, экономистам и бухгалтерам.