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

Обновлено: 05.07.2024

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

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

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

Выделение по условию целиком строки или столбца.

Выделение строки.

В нашем распоряжении – таблица Excel с информацией о продажах в различные страны. Давайте попробуем выделить определенные строки с продажами в Бразилию. То есть, окраска их должна изменяться в связи с тем, что записано в колонке «Страна».

Прежде всего выделяем при помощи мыши весь диапазон интересующих нас данных – A2:D21. Шапку таблицы выделять не нужно. Затем действуем по уже отработанной схеме: вызываем меню функции и выбираем последний пункт – «Использовать формулу для определения форматируемых ячеек» (1). Далее записываем выражение (2):

Мы должны закрасить вторую строку таблицы в зависимости от значения в С2. Здесь есть маленькая хитрость.

Обратите внимание, что абсолютная ссылка (знак $) установлена здесь только на столбец С. То есть, мы проверяем на условие «Бразилия» в выделенном нами диапазоне все позиции в этом столбце, то есть С2, С3, C4 и так далее. А вот закрашивать будем всю строку, так как ранее выделена была вся таблица. Для этого выбираем вариант оформления (3): цвет фона или шрифта, либо оба.

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

Вывод. Условное форматирование строки по значению ячейки основано на грамотном применении абсолютных и относительных ссылок в правиле форматирования. В используемой формуле должна быть абсолютная ссылка на столбец и относительная - на строку ($C2). При этом как область форматирования должна быть обозначена вся таблица (без шапки).

Выделение столбца.

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

Тем не менее, давайте рассмотрим пример с выделением по условию столбцов таблицы.

Итак, у нас есть табель рабочих смен. Нужно красным указать в нем на субботы и воскресенья.

Как и в предыдущем примере, определим для начала диапазон, который мы будем форматировать: =$B$3:$S$7. И вновь будем использовать формулу (2) для определения условия.

Функция ДЕНЬНЕД позволяет определить номер дня недели по указанной дате. Цифра 2 означает, что используется привычный нам порядок, когда первый день недели – это понедельник.

Таким образом, если номер окажется больше 5 (то есть, это будет суббота или воскресенье), то необходимо применить указанный нами формат (3) и закрасить выходной день.

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

Выделение через строку.

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

Итак, возьмем для примера небольшую таблицу.

Выделим диапазон A1:D18.

Затем создаем новое правило при помощи формулы

В чем ее смысл? Если номер, полученный при помощи функции СТРОКА(), делится без остатка на 2, то значит у нее чётный номер, и к ней следует применить правило форматирования. Если же номер не делится на 2 без остатка, то это нечетная. Ее мы оставляем без изменений.

В результате получилась «полосатая» таблица по принципу "четный-нечетный".

Закрасить группу строк.

Зачем это нужно? К примеру, у нас имеются помесячные данные о продажах. Тогда логично было бы отделить каждые 3 месяца, чтобы хорошо видны были результаты по кварталам.

Поясним эти вычисления.В качестве счетчика мы будем использовать номер текущей строки.Поскольку в квартале 3 месяца, то группировать будем тоже по три. Отсчет начинаем с А2.

Счетчик в начале нужно установить в ноль. Для этого служит выражение (СТРОКА()-2). Поскольку начинаем со второй, то обнуляем счетчик, вычитая 2.
Далее нужно определить, к какой по счету группе относится текущее местоположение курсора. Результат п.1 делим на 3.
Отсекаем дробную часть при помощи функции ЦЕЛОЕ и получаем порядковый номер группы: ЦЕЛОЕ((СТРОКА()-2)/3).
Добавляем 1, поскольку результатом для первой группы будет число меньше 1. А нужно, чтобы отсчет групп начинался с 1.
Затем действуем по методике, отработанной в предыдущем примере: производим действия только с нечетными группами. Для этого используем функцию ОСТАТ с аргументом 2. То есть, находим остаток от деления на 2. Если число четное, то остаток будет равен нулю. Ноль равносилен результату ЛОЖЬ, поэтому с такими группами ничего не делаем. Если число нечетное, остаток от деления на 2 будет равен 1, что равноценно ИСТИНА. И вот тут-то мы и закрасим эту группу.

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

Аналогично можно разбить на группы по 4 строки. Тогда в формуле выше просто замените цифру 3 на 4. И все будет работать.

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

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

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

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

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

Для этого сначала выделим весь наш диапазон с данными.

Внимание! Первую шапку таблицы не выделяем, начинаем с данных!

В нашем случае, выделяем A3:G33.

Затем далее действуем по уже отработанной схеме. В меню условного форматирования выбираем использование формулы (1). Далее записываем само правило:

Иначе говоря, мы проверяем, равна ли наша текущая дата предыдущей. Если не равна, значит, мы перешли к новому дню. Соответственно наше текущее положение нужно выделить. Выбираем формат (3). Тип границы – линия (4). Она будет использоваться по верхней границе (5).

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

Условное форматирование для сравнения двух столбцов.

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

Как найти и закрасить совпадающие ячейки в столбцах.

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

На рисунке вы видите, что дубликаты выделены зеленым. Думаю, здесь все довольно просто.

Выделение совпадений двух столбцов построчно.

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

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

Выберите, в каких ячейках вы будете отмечать совпадения – в первой или во второй таблице. Я выделил B3:B25. То есть, в первой таблице мы закрасим ячейки, которые дублируются во второй таблице.

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

Как найти и закрасить совпадения в нескольких столбцах.

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

Давайте закрасим цветом те ячейки в столбце B, которые хотя бы однажды встречаются в G,H и I.

Диапазон форматирования – B3:B25. Выделяем его и в меню – «Создать правило» выбираем «Использовать формулу…»

Запишем правило условного форматирования:

Последовательно двигаемся сверху вниз и сравниваем каждую ячейку колонки B с находящимися в той же горизонтали значениями в G,H и I.

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

Но если столбцов будет не 3, а, предположим, 10? Формула станет слишком громоздкой. Ведь придется указать 10 критериев совпадения.

Есть более простой способ. Изменим правило форматирования и используем функцию СЧЁТЕСЛИ:

СЧЁТЕСЛИ определяет, как часто определенное значение встречается в диапазоне. Считаем, сколько раз значение из B3 встречается в G,H и I таблицы, то есть в $G3:$I3. Если будет более одного совпадения, то срабатывает правило.Функция возвращает 1. А 1 в логическом выражении соответствует ИСТИНА, 0 - ЛОЖЬ. То есть, если счет равен нулю, то в текущей позиции нашего столбца содержится уникальное значение, которое больше нигде в диапазоне поиска не встречается. Согласитесь, так гораздо удобнее, чем писать множество однотипных критериев.

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

Вот это новое правило:

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

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

Как закрасить ячейки при помощи "Найти и выделить".

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

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

Давайте вновь рассмотрим наш пример с продажами шоколада. Выделим цветом продажи меньше и больше 100 единиц, как показано ниже. К сожалению, никакие формулы мы здесь применить не можем, поэтому возможности отбора нужных значений сильно ограничены. Однако, можно использовать уже знакомые нам знаки подстановки — вопросительный знак ? и звездочку *. Напомню, что "?" позволяет заменить собой любой одиночный символ, а "*" - любую последовательность знаков. Как это применить? К примеру, 8? будет означать два символа, первый из которых - 8, а второй - любой. ?? означает два любых символа и т.д.

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

Нажимаем "Найти все" и в открывшемся внизу окошке тыкаем мышкой на любое из найденных значений. Затем нажимаем комбинацию клавиш CTRL+A, чтобы выделить все результаты, соответствующие условию. После этого закрываем окно поиска и видим, что все нужные цифры оказались выделены. Остается только во вкладке "Шрифт" выбрать нужный цвет заливки, или другой вариант оформления по вашему желанию.

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

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

Еще полезные примеры и советы:

Как сделать пользовательский числовой формат в Excel - В этом руководстве объясняются основы форматирования чисел в Excel и предоставляется подробное руководство по созданию настраиваемого пользователем формата. Вы узнаете, как отображать нужное количество десятичных знаков, изменять выравнивание или цвет шрифта,… Как изменить формат даты в Excel и создать собственное форматирование - Это руководство посвящено форматированию даты в Excel и объясняет, как установить вид даты и времени по умолчанию, как изменить их формат и создать собственный. Помимо чисел, наиболее распространенными типами данных,… 7 способов поменять формат ячеек в Excel - Мы рассмотрим, какие форматы данных используются в Excel. Кроме того, расскажем, как можно быстро изменять внешний вид ячеек самыми различными способами. Когда дело доходит до форматирования ячеек в Excel, большинство… Как удалить формат ячеек в Excel - В этом коротком руководстве показано несколько быстрых способов очистки форматирования в Excel и объясняется, как удалить форматы в выбранных ячейках. Самый очевидный способ сделать часть информации более заметной - это… Денежный и финансовый формат — как установить и что предпочесть? - Когда мы хотим, чтобы числа отображались в Excel как денежные значения, мы должны отформатировать их соответствующим образом. В сегодняшней публикации мы подробно объясним, как применить финансовый либо денежный форматы. Рассмотренные в…

Процентный формат ячеек и особенности его применения в Excel - В этом коротком руководстве вы найдете много полезных сведений и узнаете, как показать результаты вычислений в процентах, как в Excel заменить число на процент по мере ввода и многое другое.… 9 способов сравнить две таблицы в Excel и найти разницу - В этом руководстве вы познакомитесь с различными методами сравнения таблиц Excel и определения различий между ними. Узнайте, как просматривать две таблицы рядом, как использовать формулы для создания отчета о различиях, выделить… Как сравнить два столбца на совпадения и различия - На прочтение этой статьи у вас уйдет около 10 минут, а в следующие 5 минут (или даже быстрее) вы легко сравните два столбца Excel на наличие дубликатов и выделите найденные… 6 способов — как безопасно удалить лишние пустые строки в Excel - Это руководство научит вас нескольким простым приемам безопасного удаления нескольких пустых строк в Excel без потери информации. Пустые строки в таблице — это проблема, с которой мы все время от… Как в Excel разделить текст из одной ячейки в несколько - В руководстве объясняется, как разделить ячейки в Excel с помощью формул и стандартных инструментов. Вы узнаете, как разделить текст запятой, пробелом или любым другим разделителем, а также как разбить строки на…

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

Эти правила используются довольно часто, поэтому в EXCEL 2007 они вынесены в отдельное меню Правила выделения ячеек .

Эти правила также же доступны через меню Главная/ Стили/ Условное форматирование/ Создать правило, Форматировать только ячейки, которые содержат .

Рассмотрим несколько задач:

СРАВНЕНИЕ С ПОСТОЯННЫМ ЗНАЧЕНИЕМ (КОНСТАНТОЙ)

Задача1 . Сравним значения из диапазона A1:D1 с числом 4.

введем в диапазон A1:D1 значения 1, 3, 5, 7
выделим этот диапазон;
применим к выделенному диапазону Условное форматирование на значение Меньше ( Главная/ Стили/ Условное форматирование/ Правила выделения ячеек/ Меньше );
в левом поле появившегося окна введем 4 – сразу же увидим результат применения Условного форматирования .
Нажмем ОК.

Результат можно увидеть в файле примера на листе Задача1 .

СРАВНЕНИЕ СО ЗНАЧЕНИЕМ В ЯЧЕЙКЕ (АБСОЛЮТНАЯ ССЫЛКА)

Чуть усложним предыдущую задачу: вместо ввода в качестве критерия непосредственно значения (4), введем ссылку на ячейку, в которой содержится значение 4.

Задача2 . Сравним значения из диапазона A1:D1 с числом из ячейки А2 .

введем в ячейку А2 число 4;
выделим диапазон A1:D1 ;
применим к выделенному диапазону Условное форматирование на значение Меньше ( Главная/ Стили/ Условное форматирование/ Правила выделения ячеек/ Меньше );
в левом поле появившегося окна введем ссылку на ячейку A2 нажав на кнопочку, расположенную в правой части окна (EXCEL по умолчанию использует абсолютную ссылку $А$2 ).

В результате, все значения из выделенного диапазона A 1: D 1 будут сравниваться с одной ячейкой $А$2 . Те значения из A 1: D 1 , которые меньше A 2 будут выделены заливкой фона ячейки.

Результат можно увидеть в файле примера на листе Задача2 .

Чтобы увидеть как настроено правило форматирования, которое Вы только что создали, нажмите Главная/ Стили/ Условное форматирование/ Управление правилами ; затем дважды кликните на правиле или нажмите кнопку Изменить правило . В результате увидите диалоговое окно, показанное ниже.

ПОПАРНОЕ СРАВНЕНИЕ СТРОК/ СТОЛБЦОВ (ОТНОСИТЕЛЬНЫЕ ССЫЛКИ)

Теперь будем производить попарное сравнение значений в строках 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, не поддерживающими одновременное применение нескольких правил условного форматирования. Хотя его можно использовать для отмены одного или нескольких правил при одновременном использовании нескольких правил, установленных для диапазона (когда между правилами нет конфликта). Подробнее можно ]]>прочитать здесь ]]> .

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

УСЛОВНОЕ ФОРМАТИРОВАНИЕ и ФОРМАТ ЯЧЕЕК

Условное форматирование не изменяет примененный к данной ячейке Формат (вкладка Главная группа Шрифт, или нажать CTRL+SHIFT+F ). Например, если в Формате ячейки установлена красная заливка ячейки, и сработало правило Условного форматирования, согласно которого заливкая этой ячейки должна быть желтой, то заливка Условного форматирования "победит" - ячейка будет выделены желтым. Хотя заливка Условного форматирования наносится поверх заливки Формата ячейки, она не изменяет (не отменяет ее), а ее просто не видно.

Через Формат ячеек можно задать пользовательский формат ячейки , который достаточно гибок и иногда даже удобнее, чем Условное форматирование. Подробнее см. статью Пользовательский ЧИСЛОвой формат в MS EXCEL (через Формат ячеек) .

ОТЛАДКА ПРАВИЛ УСЛОВНОГО ФОРМАТИРОВАНИЯ

Чтобы проверить правильно ли выполняется правила Условного форматирования, скопируйте формулу из правила в любую пустую ячейку (например, в ячейку справа от ячейки с Условным форматированием). Если формула вернет ИСТИНА, то правило сработало, если ЛОЖЬ, то условие не выполнено и форматирование ячейки не должно быть изменено.

Вернемся к задаче 3 (см. выше раздел об относительных ссылках). В строке 4 напишем формулу из правила условного форматирования =A1

В тех столбцах, где результат формулы равен ИСТИНА, условное форматирование будет применено, а где ЛОЖЬ - нет.

ИСПОЛЬЗОВАНИЕ В ПРАВИЛАХ ССЫЛОК НА ДРУГИЕ ЛИСТЫ

До MS Excel 2010 для правил Условного форматирования нельзя было напрямую использовать ссылки на другие листы или книги. Обойти это ограничение можно было с помощью использования имен . Если в Условном форматирования нужно сделать, например, ссылку на ячейку А2 другого листа, то нужно сначала определить имя для этой ячейки, а затем сослаться на это имя в правиле Условного форматирования . Как это реализовано См. файл примера на листе Ссылка с другого листа .

ПОИСК ЯЧЕЕК С УСЛОВНЫМ ФОРМАТИРОВАНИЕМ

на вкладке Главная в группе Редактирование щелкните стрелку рядом с командой Найтии выделить ,
выберите в списке пункт Условное форматирование .

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

ДРУГИЕ ПРЕДОПРЕДЕЛЕННЫЕ ПРАВИЛА

В меню Главная/ Стили/ Условное форматирование/ Правила выделения ячеек разработчиками EXCEL созданы разнообразные правила форматирования.

Чтобы заново не изобретать велосипед, посмотрим на некоторые их них внимательнее.

Текст содержит… Приведем пример. Пусть в ячейке имеется слово Дрель . Выделим ячейку и применим правило Текст содержит …Если в качестве критерия запишем ре (выделить слова, в которых содержится слог ре ), то слово Дрель будет выделено.

Теперь посмотрим на только что созданное правило через меню Главная/ Стили/ Условное форматирование/ Управление правилами.

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

Пусть снова в ячейке имеется слово Дрель . Выделим ячейку и применим правило Текст содержит … Если в качестве критерия запишем р?, то слово Дрель будет выделено. Критерий означает: выделить слова, в которых содержатся слога ре, ра, ре и т.д. Надо понимать, что также будут выделены слова с фразами р2, рм, рQ , т.к. знак ? означает любой символ. Если в качестве критерия запишем . (выделить слова, в которых не менее 6 букв), то, соответственно, слово Дрель не будет выделено. Можно, конечно подобного результата добиться с помощью формул с функциями ПСТР() , ЛЕВСИМВ() , ДЛСТР() , но этот подход, согласитесь, быстрее.

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

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

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

Задача4 . Пусть имеется 21 значение, для удобства отсортированных по возрастанию . Применим правило Последние 10 элементов и установим, чтобы было выделено 3 значения (элемента). См. файл примера , лист Задача4 .

Слова "Последние 3 значения" означают 3 наименьших значения. Если в списке есть повторы, то будут выделены все соответствующие повторы. Например, в нашем случае 3-м наименьшим является третье сверху значение 10. Т.к. в списке есть еще повторы 10 (их всего 6), то будут выделены и они.

Соответственно, правила, примененные к нашему списку: "Последнее 1 значение", "Последние 2 значения", . "Последние 6 значений" будут приводить к одинаковому результату - выделению 6 значений равных 10.

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

Применение правила "Последние 7 значений" приведет к выделению дополнительно всех значений равных 11, .т.к. 7-м минимальным значением является первое сверху значение 11.

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

Рассмотрим другое родственное правило Последние 10% .

Обратите внимание, что на картинке выше не установлена галочка "% от выделенного диапазона". Эта галочка устанавливается либо в ручную или при применении правила Последние 10% .

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

Попробуем задать 20% последних в нашем списке из 21 значения: будет выделено шесть значений 10 (См. файл примера , лист Задача4) . 10 - минимальное значение в списке, поэтому в любом случае будут выделены все его повторы.

Задавая проценты от 1 до 33% получим, что выделение не изменится. Почему? Задав, например, 33%, получим, что необходимо выделить 6,93 значения. Т.к. можно выделить только целое количество значений, Условное форматирование округляет до целого, отбрасывая дробную часть. А вот при 34% уже нужно выделить 7,14 значений, т.е. 7, а с учетом повторов следующего за 10-ю значения 11, будет выделено 6+3=9 значений.

ПРАВИЛА С ИСПОЛЬЗОВАНИЕМ ФОРМУЛ

Создание правил форматирования на основе формул ограничено только фантазией пользователя. Здесь рассмотрим только один пример, остальные примеры использования Условного форматирования можно найти в этих статьях: Условное форматирование Дат ; Условное форматирование Чисел ; Условное форматирование Текстовых значений ; другие задачи .

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

Выделите ячейки, к которым нужно применить Условное форматирование (пусть это ячейка А1 ).
Вызовите инструмент Условное форматирование ( Главная/ Стили/ Условное форматирование/ Создать правило )
Выберите Использовать формулу для определения форматируемых ячеек

Того же результата можно добиться по другому:

Вызовите инструмент Условное форматирование ( Главная/ Стили/ Условное форматирование/ Создать правило )
Выделите пункт Форматировать только ячейки, которые содержат ;
В разделе Форматировать только ячейки, для которых выполняется следующее условие: в самом левом выпадающем списке выбрать Ошибки.

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

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

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

Инструмент «Условное форматирование» находится на главной странице в разделе «Стили».

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

Сравним числовые значения в диапазоне Excel с числовой константой. Чаще всего используются правила «больше / меньше / равно / между». Поэтому они вынесены в меню «Правила выделения ячеек».

Введем в диапазон А1:А11 ряд чисел:

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

Введем в левое поле число 15. В правое – способ выделения значений, соответствующих заданному условию: «больше 15». Сразу виден результат:

Выходим из меню нажатием кнопки ОК.

Условное форматирование по значению другой ячейки

Сравним значения диапазона А1:А11 с числом в ячейке В2. Введем в нее цифру 20.

Выделяем исходный диапазон и открываем окно инструмента «Условное форматирование» (ниже сокращенно упоминается «УФ»). Для данного примера применим условие «меньше» («Правила выделения ячеек» - «Меньше»).

В левое поле вводим ссылку на ячейку В2 (щелкаем мышью по этой ячейке – ее имя появится автоматически). По умолчанию – абсолютную.

Результат форматирования сразу виден на листе Excel.

Значения диапазона А1:А11, которые меньше значения ячейки В2, залиты выбранным фоном.

Зададим условие форматирования: сравнить значения ячеек в разных диапазонах и показать одинаковые. Сравнивать будем столбец А1:А11 со столбцом В1:В11.

Выделим исходный диапазон (А1:А11). Нажмем «УФ» - «Правила выделения ячеек» - «Равно». В левом поле – ссылка на ячейку В1. Ссылка должна быть СМЕШАННАЯ или ОТНОСИТЕЛЬНАЯ! , а не абсолютная.

Каждое значение в столбце А программа сравнила с соответствующим значением в столбце В. Одинаковые значения выделены цветом.

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

В нашем примере в момент вызова инструмента была активна ячейка А1. Ссылка $B1. Следовательно, Excel сравнивает значение ячейки А1 со значением В1. Если бы мы выделяли столбец не сверху вниз, а снизу вверх, то активной была бы ячейка А11. И программа сравнивала бы В1 с А11.

Чтобы инструмент «Условное форматирование» правильно выполнил задачу, следите за этим моментом.

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

Выделите первую ячейку диапазона с условным форматированим.
Откройте меню инструмента, нажмите «Управление правилами».

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

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

Исходный диапазон – А1:А11. Необходимо выделить красным числа, которые больше 6. Зеленым – больше 10. Желтым – больше 20.

1 способ. Выделяем диапазон А1:А11. Применяем к нему «Условное форматирование». «Правила выделения ячеек» - «Больше». В левое поле вводим число 6. В правом – «красная заливка». ОК. Снова выделяем диапазон А1:А11. Задаем условие форматирования «больше 10», способ – «заливка зеленым». По такому же принципу «заливаем» желтым числа больше 20.
2 способ. В меню инструмента «Условное форматирование выбираем «Создать правило».

Заполняем параметры форматирования по первому условию:

Нажимаем ОК. Аналогично задаем второе и третье условие форматирования.

Обратите внимание: значения некоторых ячеек соответствуют одновременно двум и более условиям. Приоритет обработки зависит от порядка перечисления правил в «Диспетчере»-«Управление правилами».

То есть к числу 24, которое одновременно больше 6, 10 и 20, применяется условие «=$А1>20» (первое в списке).

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

Выделяем диапазон с датами.

Применим к нему «УФ» - «Дата».

В открывшемся окне появляется перечень доступных условий (правил):

Выбираем нужное (например, за последние 7 дней) и жмем ОК.

Красным цветом выделены ячейки с датами последней недели (дата написания статьи – 02.02.2016).

Условное форматирование в Excel с использованием формул

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

Есть столбец с числами. Необходимо выделить цветом ячейки с четными. Используем формулу: =ОСТАТ($А1;2)=0.

Выделяем диапазон с числами – открываем меню «Условного форматирования». Выбираем «Создать правило». Нажимаем «Использовать формулу для определения форматируемых ячеек». Заполняем следующим образом:

Для закрытия окна и отображения результата – ОК.

Условное форматирование строки по значению ячейки

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

Таблица для примера:

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

Выделяем диапазон со значениями таблицы. Нажимаем «УФ» - «Создать правило». Тип правила – формула. Применим функцию ЕСЛИ.

Порядок заполнения условий для форматирования «завершенных проектов»:

Обратите внимание: ссылки на строку – абсолютные, на ячейку – смешанная («закрепили» только столбец).

Аналогично задаем правила форматирования для незавершенных проектов.

В «Диспетчере» условия выглядят так:

Когда заданы параметры форматирования для всего диапазона, условие будет выполняться одновременно с заполнением ячеек. К примеру, «завершим» проект Димитровой за 28.01 – поставим вместо «Р» «З».

«Раскраска» автоматически поменялась. Стандартными средствами Excel к таким результатам пришлось бы долго идти.

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

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

Как сделать сравнение значений в Excel двух столбцов? Для решения данной задачи рекомендуем использовать условное форматирование, которое быстро выделить цветом позиции, находящиеся только в одном столбце. Рабочий лист с таблицами:

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

Выберите инструмент «ФОРМУЛЫ»-«Определенные имена»-«Присвоить имя».
В появившемся окне в поле «Имя:» введите значение – Таблица_1.
Левой клавишей мышки сделайте щелчок по полю ввода «Диапазон:» и выделите диапазон: A2:A15. И нажмите ОК.

Для второго списка выполните те же действия только имя присвойте – Таблица_2. А диапазон укажите C2:C15 – соответственно.

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

Теперь воспользуемся условным форматированием, чтобы выполнить сравнение двух списков в Excel. Нам нужно получить следующий результат:

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

Выделите диапазон первой таблицы: A2:A15 и выберите инструмент: «ГЛАВНАЯ»-«Условное форматирование»-«Создать правило»- «Использовать формулу для определения форматированных ячеек:».
В поле ввода введите формулу:
Щелкните по кнопке «Формат» и на вкладке «Заливка» укажите зеленый цвет. На всех окнах жмем ОК.
Выделите диапазон первого списка: C2:C15 и снова выберите инструмент: «ГЛАВНАЯ»-«Условное форматирование»-«Создать правило»- «Использовать формулу для определения форматированных ячеек:».
В поле ввода введите формулу:
Щелкните по кнопке «Формат» и на вкладке «Заливка» укажите синий цвет. На всех окнах жмем ОК.

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

При определении условий для форматирования ячеек столбцов мы использовали функцию СЧЕТЕСЛИ. В данном примере эта функция проверяет сколько раз встречается значение второго аргумента (например, A2) в списке первого аргумента (например, Таблица_2). Если количество раз = 0 в таком случае формула возвращает значение ИСТИНА. В таком случае ячейке присваивается пользовательский формат, указанный в параметрах условного форматирования.

Ссылка во втором аргументе относительная, значит по очереди будут проверятся все ячейки выделенного диапазона (например, A2:A15). Например, для сравнения двух прайсов в Excel даже на разных листах. Вторая формула действует аналогично. Этот же принцип можно применять для разных подобных задач.

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