Как условно раскрасить ячейку, строку или столбец в Excel?
В этой статье вы узнаете, как условно раскрасить ячейку, выбрать целые строки и столбцы в Excel 2016, 2013 и 2010 на основе некоторых критериев, а также найдете несколько советов и примеров формул, которые будут работать с числовыми и текстовыми ячейками ценности.
Узнайте, как быстро нарисовать всю строку или столбец на основе значения одной ячейки в электронных таблицах Excel в Excel. Предложения и примеры формул для числовых и текстовых значений.
Мы уже обсуждали, что такое условное форматирование и как изменить цвет фона ячейки в зависимости от ее значения. Для этого мы рекомендуем ссылки в конце этого материала. Теперь посмотрим на более сложные вещи.
Выделение по условию целиком строки или столбца.
Выделение строки.
В нашем распоряжении имеется таблица в формате Excel с информацией о продажах в различных странах. Попробуем выделить несколько линий с продажами в Бразилии. То есть их цвет должен измениться относительно того, что написано в графе «Страна».
Сначала мы выделяем мышью весь интересующий нас диапазон данных — A2: D21. Необязательно выбирать заголовок таблицы. Далее действуем по уже выработанной схеме: вызываем меню функций и выбираем последний пункт — «Воспользуйтесь формулой для определения форматируемых ячеек» (1). Далее запишем выражение (2):
= $ C2 = «Бразилия»
Мы должны закрасить вторую строку таблицы в соответствии со значением в C2. Здесь есть небольшая хитрость.
Обратите внимание, что абсолютная ссылка (знак $) установлена здесь только для столбца C. То есть мы проверяем условие «Бразилия» в диапазоне, в котором мы выбрали все позиции в этом столбце, а именно C2, C3, C4 и и т.п. Но закрасим всю строку, так как ранее была выделена вся таблица. Для этого выберите вариант дизайна (3): цвет фона, цвет шрифта или и то, и другое.
Напоминаем, что знак $ перед буквой столбца указывает на абсолютную ссылку на этот столбец. И если знак $ стоит перед цифрой, абсолютная ссылка устанавливается на строку.
Производство. Условное форматирование строки на основе значения ячейки основано на грамотном использовании абсолютных и относительных ссылок в правиле форматирования. Используемая формула должна иметь абсолютную ссылку на столбец и относительную ссылку на строку ($ C2). В этом случае вся таблица (без заголовка) должна быть обозначена как область форматирования).
Выделение столбца.
Аналогичную операцию можно проделать с выделением отдельных столбцов. Конечно, формула будет выглядеть немного иначе: знак доллара будет перед числом. Но, конечно, гораздо чаще встречается выделение горизонтальных линий в таблице.
Однако давайте рассмотрим пример условного выделения столбцов таблицы.
Итак, у нас есть сменный лист. Суббота и воскресенье должны быть обозначены красным.
Как и в предыдущем примере, мы сначала определяем диапазон, который будем форматировать: = $ B $ 3: $ S $ 7. И снова мы будем использовать формулу (2) для определения условия.
= ДЕНЬ НЕДЕЛИ (2,2 B $)> 5
Функция WEEKDAY позволяет вам определять номер дня недели на основе указанной даты. Цифра 2 означает, что мы используем обычный порядок, когда первый день недели — понедельник.
Следовательно, если число окажется больше 5 (то есть будет суббота или воскресенье), то нужно применить указанный формат (3) и раскрасить выходной.
Все просто, но обратите внимание на одну важную деталь — знаком $ в ссылке мы фиксируем число. Поэтому мы говорим программе, что необходимо последовательно перемещаться по второй строке в указанном диапазоне и определять номер дня недели. А затем примените формат к столбцу.
Выделение через строку.
Думаю, вы часто сталкивались с красивым дизайном таблиц, когда одна за другой выделялись строки. Конечно, этот дизайн легко доступен, если вы превратите данные в умную таблицу. Однако это возможно только в Excel 2007 и более поздних версиях. Если у вас старая версия программы, наш метод может быть вам очень полезен.
Итак, возьмем для примера журнальный столик.
Выберите диапазон A1: D18.
Затем мы создаем новое правило по формуле
= ОТДЫХ (СТРОКА (); 2) = 0
В чем его значение? Если число, полученное с помощью функции LINE (), делится на 2 без остатка, то оно имеет четное число и должно применяться правило форматирования. Если число не делится на 2 без остатка, то оно нечетное. Оставляем без изменений.
В результате получается полосатая нечетно-четная таблица».
Закрасить группу строк.
Зачем это нужно? Например, у нас есть данные о продажах за месяц. Так что логично было бы разводить каждые 3 месяца, чтобы отчетливо просматривались результаты по кварталам.
Мы используем выражение
= ОСТАЛОСЬ (ЦЕЛОЕ ((СТРОКА () — 2) / 3) +1; 2)
Чтобы прояснить эти вычисления, мы будем использовать текущий номер строки в качестве счетчика, поскольку в квартале 3 месяца, мы также сгруппируем по трем. Отсчет начинаем с А2.
- В начале счетчик должен быть установлен на ноль. Для этого используется выражение (STRING () — 2). Поскольку мы начинаем со второго, мы сбрасываем счетчик, вычитая 2.
- Далее необходимо определить, к какой группе принадлежит текущая позиция курсора. Разделите результат пункта 1 на 3.
- Мы вырезаем дробную часть с помощью функции ЦЕЛОЕ и получаем порядковый номер группы: ЦЕЛОЕ ((СТРОКА () — 2) / 3).
- Добавьте 1, так как результат для первой группы будет меньше 1. И вы должны посчитать группы на 1.
- Далее действуем по методике, разработанной в предыдущем примере: выполняем действия только с нечетными группами. Для этого воспользуемся функцией OSTAT с аргументом 2. То есть найдем остаток от деления на 2. Если число четное, остаток будет равен нулю. Ноль эквивалентен FALSE, поэтому мы ничего не делаем с такими группами. Если число нечетное, остаток от деления на 2 будет равен 1, что равно ИСТИНА. И вот где мы рассказываем об этой группе.
В результате мы разделили нашу таблицу на триплеты, присвоили каждой триплету свой порядковый номер, а с нечетными числами предприняли действия по изменению формата их представления.
Точно так же его можно разделить на группы по 4 строки. Поэтому в приведенной выше формуле просто замените цифру 3 на 4. И все получится.
А если в заголовке таблицы больше одной строки, просто замените -2 большим числом, соответствующим высоте заголовка таблицы.
Как видите, подход достаточно универсален. Надеюсь, что вы найдете ее полезной.
Вставляем отделяющие линии между группами строк.
Если у вас есть большой список, отсортированный по любому столбцу, было бы неплохо автоматически разделить результирующие группы горизонтальными разделительными линиями для ясности.
Делаем красивую оценку работы, отсортированную по дням. Каждый новый день мы отделяем горизонтальными линиями, чтобы визуально отделить их друг от друга.
Для этого сначала выберите весь диапазон данных.
Внимание! Не будем выделять первый заголовок таблицы, начнем с данных!
В нашем случае выберите A3: G33.
Далее действуем дальше по уже разработанной схеме. В меню условного форматирования выберите использование формулы (1). Далее пишем само правило:
= $ B3 $ B2
Другими словами, мы проверяем, совпадает ли наша текущая дата с предыдущей. Если это не то же самое, значит, мы перешли к новому дню. Следовательно, необходимо выделить нашу текущую позицию. Выберите формат (3). Тип кромки — строчка (4). Он будет использоваться по верхнему краю (5).
Следовательно, каждый новый день будет отделен от предыдущего горизонтальной линией. Конечно, вы можете выбрать другой стиль форматирования, например цвет.
Условное форматирование для сравнения двух столбцов.
Когда вам нужно сравнить два столбца в таблице, отличный способ выделить совпадения и различия — выделить их.
Как найти и закрасить совпадающие ячейки в столбцах.
вы можете воспользоваться специальной вкладкой «Условное форматирование» — «Повторяющиеся значения».
На изображении вы можете видеть, что дубликаты выделены зеленым цветом. Думаю, здесь все довольно просто.
Выделение совпадений двух столбцов построчно.
Если у нас есть несколько копий одной и той же таблицы, нам может потребоваться найти и показать их различия и совпадения. В этом случае попробуем сравнить столбцы таблицы построчно.
Для сравнения данных в каждой строке двух столбцов таблицы используйте формулы условия.
Выберите ячейки, в которых вы будете отмечать совпадения, в первой или второй таблице. Я выделил 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, а не только в одном из них. Возможно, вам будет полезен и такой пример.
Еще раз обратите внимание, как определяются абсолютные ссылки. Суть в том, что должен измениться номер строки, но не номер столбца. Тогда все наладится.
Как закрасить ячейки при помощи «Найти и выделить».
В предыдущих материалах, ссылки на которые вы можете найти в конце этой статьи, мы уже видели, как условно раскрасить ячейку в Excel в зависимости от другой ячейки или собственного содержимого. Условное форматирование позволяет отслеживать изменения в таблице и на основе доступных значений окрашивать ячейку в определенный цвет. Но что делать, если в таблице больше нет изменений и при определенном условии необходимо «раз и навсегда» раскрасить значения? Другими словами, мы говорим о статической таблице.
Вы можете найти более простой способ использования условного форматирования с помощью инструмента «Найти и выбрать».
Давайте еще раз посмотрим на наш пример продажи шоколада. Выделите продажи ниже и выше 100 единиц, как показано ниже. К сожалению, здесь мы не можем применить какие-либо формулы, поэтому возможности выбора требуемых значений сильно ограничены. Однако вы можете использовать знакомые подстановочные знаки: вопросительный знак? и звездочку *. Напомню, что «?» позволяет заменить любой одиночный символ, а «*» — любую последовательность символов. Как это применить? Например, 8? будет означать два символа, первый — 8, а второй — любой. ?? означает любые два символа и т д
Затем выделите мышью область значений, которую мы хотим нарисовать на основе условия, затем используйте инструмент «Найти и выбрать». В поле поиска напишите ??, что в нашем случае означает любое двузначное число в диапазоне E5: E24. Обратите внимание: если вы заранее не укажете диапазон форматирования, поиск будет производиться по всей таблице, что нам совершенно не нужно.
Нажмите «Найти все» и в открывшемся ниже окне наведите указатель мыши на одно из найденных значений. Затем нажмите комбинацию клавиш CTRL + A, чтобы выбрать все результаты, соответствующие условию. Затем закройте окно поиска и убедитесь, что выбраны все необходимые числа. Остается только во вкладке «Шрифт» выбрать желаемый цвет заливки или другой вариант оформления по вашему выбору.
Повторим все те же действия, только сейчас укажем ??? в поиске, то есть будем искать трехзначные числа. Или можно было указать ??? *, то есть выбрать все номера с разрядностью 3 и выше. Как видите, возможности этого инструмента невелики, но с помощью таких доработок можно добиться вполне приемлемых результатов.
И, конечно, не забывайте, что это форматирование «навсегда», оно не изменится автоматически, даже если в таблицу будут внесены некоторые изменения.