Как в excel изменять цвет строки в зависимости от значения в ячейке

Где находится условное форматирование

Как в экселе менять цвет ячейки в зависимости от значения – да очень просто и быстро. Для выделения ячеек цветом предусмотрена специальная функция «Условное форматирование», находящаяся на вкладке «Главная»:

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

Правила выделения ячеек

С помощью этого набора инструментов делают следующие выборки:

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

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

Обратите внимание, выбрать можно цвет фона и текста из предложенных в списке. Если хочется применить иные оттенки – сделать это можно перейдя в «Пользовательский формат»

Аналогичным образом реализуются все «Правила выделения ячеек».

Очень творчески реализуются «Другие правила»: в шести вариантах сценария придумывайте те, которые наиболее удобны для работы, например, градиент:

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

Правила отбора первых и последних значений.

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

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

Гистограммы

Если заливка ячейки цветом вас не устраивает – применяйте инструмент «Гистограмма». Предлагаемая окраска легче воспринимается на глаз в большом объеме информации, функциональные правила подстраиваются под требования пользователя.

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

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

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

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

Создание, удаление и управление правилами

Функция «Создать правило» полностью дублирует «Другие правила» из перечисленных выше, создает выборку изначально по требованию пользователя.

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

Вызывает интерес инструмент «Управление правилами» – своеобразная история создания и изменения проведенных форматирований. Меняйте подборки, делайте правила неактивными, возвращайте обратно, чередуйте порядок применения. Для работы с большим объемом информации это очень удобно.

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

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

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

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

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

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

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

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

Выберите, в каких ячейках вы будете отмечать совпадения – в первой или во второй таблице. Я выделил 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, а не только в одном из них. Возможно, такой пример вам также будет полезен.

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

Тогда все будет работать.

Изменяем цвет ячеек с числами.

Как закрасить ячейку с числом по условию? Мы должны изменить цвет ячейки в зависимости от значения в определенной клетке таблицы Эксель.

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

Содержимое A1 подставляется в качестве условия в формулу СЧЕТЕСЛИ. Результатом может быть только ноль или единица. А единица соответствует логическому ИСТИНА. Вот тогда-то и применяется установленный нами формат (в нашем случае – белый шрифт на синем фоне). И так перебираем все содержимое нашего диапазона.

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

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

Как создать таблицу в Эксель: Настройка столбцов и строк

Иногда Excel не подстраивает ширину столбцов автоматически, но максимально облегчает их корректировку вручную. Проще всего воспользоваться функцией автоподбора ширины столбцов (AutoFit). В этом случае программа автоматически настраивает ширину столбца так, чтобы отобразить самую длинную запись.

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

1. Поместите указатель мыши над правой границей блока с заголовком столбца.

При этом указатель примет вид горизонтальной двунаправленной стрелки.

1. Дважды щелкните кнопкой мыши.

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

Функцию автоподбора ширины столбца можно применить сразу к нескольким столбцам. Выделите те столбцы, ширину которых нужно настроить (если они находятся рядом, то перетащите над их заголовками столбцов указатель мыши; если нет — удерживая нажатой клавишу , щелкайте на самих заголовках), а затем щелкните на правой границе любого из них.

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

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

• Ширина столбца. Открывает диалоговое окно, в котором нужно ввести необходимое число символов, а затем щелкнуть на кнопке ОК.
• Автоподбор ширины столбца. Программа осуществляет автоподбор ширины на основе самой длинной записи в текущем выделении ячеек.
• Ширина по умолчанию. Открывает диалоговое окно Ширина столбца, в котором установлена стандартная ширина — 8,43 символа; при желании можно изменить это значение и щелкнуть на кнопке ОК.

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

Автоматическая заливка цветом пустых (не заполненных) ячеек.

Предположим такую ситуацию:

Вы создали опросник (чек-лист) в программе «Excel» и хотите, чтобы люди, которым Вы отправили чек-лист для заполнения, внесли данные во все ячейки.

Проверка исправности инженерных систем

Электрика Исправен

Сантехника Неисправен

Кровля

Фасад Не знаю

Двери

Отопление

Окна

Чтобы обратить внимание человека на незаполненные (пустые) ячейки нужно их покрасить в яркий привлекающий внимание цвет (выполнить заливку пустых ячеек). Хотя, такая заливка является частным случаем условного форматирования, не каждый пользователь «Excel» может сразу сориентироваться и выполнить требуемую операцию

Хотя, такая заливка является частным случаем условного форматирования, не каждый пользователь «Excel» может сразу сориентироваться и выполнить требуемую операцию.

Закрасить ячейку по условию или формуле

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

Как это сделать в Excel 2007

ВКЛЮЧИТЕ СУБТИТРЫ! Выделим ячейки с ценами заказов и, нажав на стрелочку рядом с кнопкой «Условное форматирование», выберем «Создать правило». Выберем четвертый пункт, позволяющий сравнивать текущие значения со средним. Нас интересуют значения выше среднего. Нажав кнопку «Формат», зададим цвет ячеек

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

На этот раз используем второй вариант, позволяющий проверять содержимое ячейки. Выберем «Текст», «содержит» и введем слово «Выполнен». Зададим зеленый цвет, подтверждаем, и выполненные работы у нас позеленели. Ну и сделаем еще одно правило, окрашивающее просроченные заказы в красный цвет. Выделяем даты выполнения заказов. При создании правила снова выбираем второй пункт, но на этот раз задаем «Значение ячейки», «меньше», а в следующем поле вводим функцию, возвращающую сегодняшнюю дату. «ОК», и мы получили весело разукрашенную таблицу, позволяющую наглядно отслеживать ход выполнения заказов. Обратили внимание, что статусы задаются выбором из выпадающего списка значений? Как делать такие списки, мы рассказывали в инструкции «Как в Excel сделать выпадающий список».

Как это сделать в Excel 2003

ВКЛЮЧИТЕ СУБТИТРЫ! «Условное форматирование» в меню «Формат». Тут понадобится немного больше ручной работы. Вот так будут выглядеть настройки для нашей первой задачи – закрасить ячейки со значениями больше средних.

Придется вручную ввести функцию «=СРЗНАЧ()», поставить курсор между скобками, нажать на кнопочку рядом и мышкой указать нужный диапазон. Но принцип действий тот же самый. Покоряйте Excel и до новых встреч!

Комментарии:

1. Svetlana — 27.06.2015 21:28

наконец-то узнала, как это можно сделать!

Здравствуйте, а можно сделать условное форматирование столбца А с фразами по условию «Текст —- содержит» по нескольким словам, а лучше по столбцу В, состоящего из слов?

Подскажите как подсвечивать ячеку В2 при условии если ячейка А2 не пустая?

Как задать цвет определенному значению в одной ячейки, например — вожу 5 — она будет красным цветом, вожу 4 — она станет зелёным цветом

подскажите, как заливать в гамме одного цвета с разными оттенками в столбике, если напр., если 100% — зеленый, 95- зеленый но светлее, 75 — еще светлее и т.д. заранее спасибо

а как сделать зависимость заливки ячейки из «срок исполнения» от статуса? например, срок меньше сегодня, но он выполнен, не надо подкрашивать. чтобы лишние красные сроки не отвлекали. HELP! очень надо (((

Ну тут всё предельно ясно. А вот, если пойти от обратно?! Чтобы подставлялось определённое значение (текст), если окрасить ячейку в определённый цвет?! Как задать такое условие или записать формулу.

Как создать таблицу в Эксель: Границы таблицы

Чтобы заключить выделенный диапазон ячеек в рамку, раскройте меню кнопки Границы в группе Шрифт вкладки Главная. В этом меню перечислены все варианты границ, доступные в Excel. Щелкните на том типе, который кажется вам наиболее подходящим для выделенного диапазона.

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

• Чтобы программа отобразила только внешние границы всего выделенного диапазона, выберите вариант Внешние границы или Толстая внешняя граница. Для того чтобы нарисовать собственные внешние границы вокруг невыделенного диапазона ячеек на активном рабочем листе, выберите один из параметров в группе Нарисовать границы. Затем, когда указатель мыши примет вид карандаша, перетащите его по диапазону ячеек и щелкните на кнопке Границы, находящейся в группе Шрифт вкладки Главная.
• Если нужно, чтобы границы обрамляли со всех четырех сторон все ячейки выделенного диапазона, выберите пункт Все границы. Если хотите нарисовать внутренние и внешние границы для невыделенного диапазона ячеек на активном рабочем листе самостоятельно, выберите параметр Сетка по границе рисунка, перетащите по диапазону ячеек указатель мыши (который в этом случае принимает вид карандаша), а затем щелкните на кнопке Границы вкладки Главная.

Чтобы изменить тип линии рамки, ее толщину или цвет, откройте диалоговое окно Формат ячеек и воспользуйтесь параметрами вкладки Граница (выберите пункт Другие границы меню кнопки Границы или нажмите комбинацию клавиш ).

Чтобы выбрать толщину и стиль линии границы, щелкните на соответствующем примере в разделе Тип линии. Чтобы изменить ее цвет, выберите его в раскрывающемся списке Цвет линии. После выбора типа и цвета линии примените границу к диапазону ячеек, щелкнув на соответствующем типе границы в разделе Все или Отдельные, а затем — на кнопке ОК.

Чтобы избавиться от какой-либо границы на рабочем листе, выделите диапазон ячеек, к которому она применена, а затем в контекстном меню кнопки Границы выберите пункт Нет границы.

Как в Excel сделать заливку ячейки в зависимости от значения?

Кликаем по треугольнику справа от кнопки «Цвет заливки». Открывается выбор различных цветов заливки. Выбираем тот цвет, который мы желаем применить к элементам листа, содержащим величины менее 400000 рублей. Как видим, все ячейки столбца, в которых находятся значения менее 400000 рублей, выделены выбранным цветом.

1. Выделите диапазон ячеек, в котором нужно настроить чередование цветов строк.
2. На вкладке Вставка (Insert) кликните Таблица (Table) или нажмите Ctrl+T.
3. Готово! Чётные и нечётные строки созданной таблицы раскрашены в разные цвета.

Как убрать цвет в таблице Эксель? блог про компьютеры и их настройку
Совет: Например, введите «Выделить». Нажмите кнопку Формат. В диалоговом окне «Формат ячеек» на вкладке «Заливка» выберите цвет выделения и нажмите кнопку «ОК». Нажмите кнопку ОК, чтобы закрыть диалоговое окно «Стиль».

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

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

Как видите, возле значений появились значки выбранного набора (светофор). Чтобы понять, как работает это правило, нужно “провалиться” в него (Условное форматирование — Управление правилами — двойной щелчок на привиле).

Что в данном случае означают 67 и 33 процента, если процентов в нашей таблице нет?

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

Красный значок — значениям, величина которых составляет 33% и меньше от максимума.

Желтый — значения между ними.

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

Пороговые значения по умолчанию можно менять. К тому же, вместо процентов можно выбрать другие величины, например число или процентиль.

Как автоматически выделить цветом ячейки по условию?

Как закрасить ячейки по условию?

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

Как сделать так что бы ячейка меняла цвет?

Применение узора или способов заливки

1. Выделите ячейку или диапазон ячеек, которые нужно отформатировать.
2. На вкладке Главная нажмите кнопку вызова диалогового окна Формат ячеек или просто нажмите клавиши CTRL+SHIFT+F.
3. На вкладке Заливка выберите в разделе Цвет фона нужный цвет.

Как выделить определенные ячейки в Excel?

1. Чтобы выделить ячейку, щелкните ее. …
2. Чтобы выбрать диапазон, выберем ячейку, а затем, нажимая левую кнопку мыши, перетащите указатель мыши над другими ячейками. …
3. Чтобы выделить несмежные ячейки и диапазоны ячеек, выберите их, удерживая нажатой клавишу CTRL.

Как найти и выделить цветом в Excel?

Как выделить повторяющиеся ячейки в Excel

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

Как сделать заливку ячеек в Excel по условию?

Как в Excel динамически изменять цвет ячейки, основываясь на её значении

1. Выделите таблицу или диапазон, в котором Вы хотите изменить цвет заливки ячеек. …
2. Откройте вкладку Home (Главная), в разделе Styles (Стили) нажмите Conditional Formatting (Условное форматирование) > New Rule (Создать правило).

Как закрасить строку в Excel по условию?

Как выделить столбец цветом в Excel по условию

1. Выделите диапазон ячеек B2:L15 и выберите инструмент: «ГЛАВНАЯ»-«Стили»-«Условное форматирование»-«Создать правило». …
2. В поле ввода введите формулу:
3. Щелкните на кнопку «Формат» и укажите на вкладке «Заливка» каким цветом будут выделены ячейки актуального месяца.

Как задать цвет в формуле Excel?

1. Выделите диапазон ячеек B2:L15 и выберите инструмент: «ГЛАВНАЯ»-«Стили»-«Условное форматирование»-«Создать правило». …
2. В поле ввода введите формулу:
3. Щелкните на кнопку «Формат» и укажите на вкладке «Заливка» каким цветом будут выделены ячейки актуального месяца.

Как изменить цвет текста в Excel формулой?

Изменение цвета текста в ячейке или диапазоне

1. Выделите ячейку или диапазон ячеек с данными, которые вы хотите отформатировать. Вы также можете выбрать часть текста в ячейке.
2. На вкладке Главная щелкните стрелку рядом с кнопкой Цвет шрифта .
3. Выберите цвет в группе Цвета темы или Стандартные цвета.

Как выделить цветом ячейки по условию Гугл таблицы?

Вот как установить правила:

1. Откройте файл в Google Таблицах на компьютере.
2. Выделите нужные ячейки.
3. Нажмите Формат Условное форматирование. Справа откроется панель инструментов.
4. Создайте правило. Один цвет. В разделе «Форматирование ячеек» укажите, при каких значениях ячейки выделяются цветом. …
5. Нажмите Готово.

Как обвести ячейки в Excel?

Как обвести ячейку в Excel?

1. Обведите ячейку овальной формы
2. Обведите ячейку с помощью VBA.
3. Нажмите Вставить > ФормыИ выберите Овал форма из Основные формы раздел в списке. …
4. Щелкните фигуру правой кнопкой мыши и выберите Форма форматирования из контекстного меню.

Как выделить все строки ниже?

Можно также щелкнуть в любом месте столбца таблицы и нажать клавиши CTRL+ПРОБЕЛ или щелкнуть первую ячейку столбца таблицы, а затем нажать клавиши CTRL+SHIFT+СТРЕЛКА ВНИЗ.

Как выделить в Экселе повторяющиеся значения?

На вкладке Главная выберите Условное форматирование > Правила выделения ячеек > Повторяющиеся значения. В поле рядом с оператором значения с выберите форматирование для применения к повторяющимся значениям и нажмите кнопку ОК.

Как найти и выделить в Экселе?

Например, чтобы выбрать все ячейки с формулами, сделайте следующее:

1. Выделите одну ячейку.
2. На вкладке Home (Главная) кликните по Find & Select (Найти и выделить) и выберите Go To Special (Выделение группы ячеек). …
3. Поставьте галочку напротив Formulas (Формулы) и нажмите ОК.

Как в Excel найти и выделить значения?

Для этого перейдем по вкладке Главная -> Редактирование –> Найти и выделить -> Найти. В появившемся диалоговом окне, ставим точку в поле поиска и жмем Найти далее. При открытом диалоговом окне, нажмите Ctrl+A на клавиатуре.

Как в Excel найти слова и выделить цветом?

Щелкните «ГЛАВНАЯ>условное форматирование > правила выделения ячеек > текст, который содержит. В поле Текст, который содержит в левой части экрана введите текст, который нужно выделить. Выберите формат цвета для текста и нажмите кнопку «ОК».

Как задать несколько условий для изменения цвета строки

Если нужно выделить строки одним и тем же цветом при появлении одного из нескольких различных значений, то вместо создания нескольких правил форматирования можно использовать функции И
(AND), ИЛИ
(OR) и объединить таким образом нескольких условий в одном правиле.

Например, мы можем отметить заказы, ожидаемые в течение 1 и 3 дней, розовым цветом, а те, которые будут выполнены в течение 5 и 7 дней, жёлтым цветом. Формулы будут выглядеть так:

ИЛИ($F2=»Due in 1 Days»;$F2=»Due in 3 Days»)
=OR($F2=»Due in 1 Days»,$F2=»Due in 3 Days»)

ИЛИ($F2=»Due in 5 Days»;$F2=»Due in 7 Days»)
=OR($F2=»Due in 5 Days»,$F2=»Due in 7 Days»)

Для того, чтобы выделить заказы с количеством товара не менее 5, но не более 10 (значение в столбце Qty.
), запишем формулу с функцией И
(AND):

И($D2>=5;$D2
=AND($D2>=5,$D2

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

ИЛИ($F2=»Due in 1 Days»;$F2=»Due in 3 Days»;$F2=»Due in 5 Days»)
=OR($F2=»Due in 1 Days»,$F2=»Due in 3 Days»,$F2=»Due in 5 Days»)

Подсказка:
Теперь, когда Вы научились раскрашивать ячейки в разные цвета, в зависимости от содержащихся в них значений, возможно, Вы захотите узнать, сколько ячеек выделено определённым цветом, и посчитать сумму значений в этих ячейках. Хочу порадовать Вас, это действие тоже можно сделать автоматически, и решение этой задачи мы покажем в статье, посвящённой вопросу Как в Excel посчитать количество, сумму и настроить фильтр для ячеек определённого цвета .

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

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

Пусть в диапазоне А6:С16

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

Отбор ячеек по датам

Чтобы разобраться, как в excel сделать цвет ячейки от значения установленной даты, рассмотрим пример с датами закупок у поставщиков в январе 2019 года. Для применения такого отбора нужны ячейки с установленным форматом «Дата». Для этого перед внесением информации выделите необходимый столбец, щелкните правой кнопкой мыши и в меню «Формат ячеек» найдите вкладку «Число». Установите числовой формат «Дата» и выберите его тип по своему усмотрению.

Для отбора нужных дат применяем такую последовательность действий:

• выделяем столбцы с датами (в нашем случае за январь);
• находим инструмент «Условное форматирование»;
• в «Правилах выделения ячеек» выбираем пункт «Дата»;
• в правой части форматирования открываем выпадающее окно с правилами;
• выбираем подходящее правило (на примере выбраны даты за предыдущий месяц);
• в левом поле устанавливаем готовый цветовой подбор «Желтая заливка и темно-желтый текст»
• выборка окрасилась, жмем «ОК».

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

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

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

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

• выделяем столбец с январскими закупками;
• кликаем инструмент «Условное форматирование»;
• переходим в «Правила выделения ячеек»;
• пункт «Больше…»;
• в правой части форматирования устанавливаем сумму 100 000 рублей;
• в левом поле переходим на вкладку «Пользовательский формат» и выбираем синий цвет;
• необходимая выборка окрасилась в синий цвет, жмем «ОК».

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

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

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

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

Подсветка строки в Excel цветом

Чтобы создать свой широкий курсов на целую строку в Excel, необходимо создать макрос и использовать его вместе с условным форматированием. Для примера создадим таблицу с широкими строками и большим количеством столбцов. Нам нужно выделить строку цветом по условию Excel. Например, если активная ячейка находится в строке 2, тогда выделить цветом всю строку 2 (в пределах таблицы или на весь лист). Для примера создадим таблицу с широкими строками и большим количеством столбцов:

Подсветка текущей строки в Excel цветом:

1. Откройте редактор VBA для этого выберите инструмент: «РАЗРАБОТЧИК»-«Код»-«Visual Basic». Или нажмите комбинацию горячих клавиш ALT+F11.
2. В правом окне к активному проекту добавьте новый модуль. Для того в редакторе VBA выберите опцию: «Insert»-«Module»
3. В окне модуля вставьте этот VBA-код макроса:
4. Не закрывая окно VBA-редактора перейдите на рабочий лист с исходной таблицей. Выделите целую вторую строку таблицы A2:H2 и выберите инструмент: «ГЛАВНАЯ»-«Условное форматирование»-«Создать правило»-«Использовать формулу для определения форматированных ячеек:».
5. В полю ввода введите формулу:
6. Щелкните по кнопке «Формат» и на вкладке «Заливка» укажите зеленый цвет. На всех окнах жмем ОК.
7. Скопируйте вторую строку таблицы.
8. Выделите остальные строки и выберите инструмент: «ГЛАВНАЯ»-«Вставить»-«Специальная вставка»-«Форматы» (или нажмите комбинацию клавиш CTRL+ALT+V, потом отметить форматы и нажать ОК). Или воспользуйтесь инструментом: «ГЛАВНАЯ»-«Буфер обмена»-«Формат по образцу».
9. Вернитесь к редактору VBA и в окне «Project» дважды щелкните по «Лист1». В появившемся окне вставьте следующий VBA-код макроса:
10. Сохраните и закройте редактор VBA.

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

Полезный вопрос! А как же выделить активный столбец цветом? Аналогичным методом реализовывается с помощью макроса не только подсветка цветом активной строки, но и столбца. Для этого:

1. В модуль где находится код подсветки строки VBA-макроса, добавим код новой функции АктивныйСтолбец:
2. Добавим новое правило в условном форматировании для нашей таблицы в диапазоне A1:A7. Теперь в новом правиле пишем функцию АктивныйСтолбец(), а вместо функции СТРОКА – СТОЛБЕЦ. И указываем синий цвет форматирования заливки ячейки.
3. Проверим все созданные правила для таблицы. Перейдите в ячейку A2 и ввыберите инструмент: «ГЛАВНАЯ»-«Условное форматирование»-«Управление правилами». Диспетчер правил условного форматирования должен выглядеть так:
4. Скопируйте формат столбца A1:A7 в остальные столбцы таблицы диапазона B1:H7. Например, с помощью инструмента: «ГЛАВНАЯ»-«Буфер обмена»-«Формат по образцу».
5. Результат превышает ожидания. Идеальный курсор для просмотра огромных таблиц с большим количеством данных на множестве строк и столбцов:

Принцип действия создания пользовательского курсора для таблицы

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

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

Свойство .Interior.Color объекта Range

Начиная с Excel 2007 основным способом заливки диапазона или отдельной ячейки цветом (зарисовки, добавления, изменения фона) является использование свойства .Interior.Color объекта Range путем присваивания ему значения цвета в виде десятичного числа от 0 до 16777215 (всего 16777216 цветов).

Заливка ячейки цветом в VBA Excel

Пример кода 1:

1 2 3 4 5 6

SubColorTest1() Range(«A1»).Interior.Color=31569 Range(«A4:D8»).Interior.Color=4569325 Range(«C12:D17»).Cells(4).Interior.Color=568569 Cells(3,6).Interior.Color=12659 EndSub

Поместите пример кода в свой программный модуль и нажмите кнопку на панели инструментов «Run Sub» или на клавиатуре «F5», курсор должен быть внутри выполняемой программы. На активном листе Excel ячейки и диапазон, выбранные в коде, окрасятся в соответствующие цвета.

Есть один интересный нюанс: если присвоить свойству .Interior.Color отрицательное значение от -16777215 до -1, то цвет будет соответствовать значению, равному сумме максимального значения палитры (16777215) и присвоенного отрицательного значения. Например, заливка всех трех ячеек после выполнения следующего кода будет одинакова:

1 2 3 4 5

SubColorTest11() Cells(1,1).Interior.Color=-12207890 Cells(2,1).Interior.Color=16777215+(-12207890) Cells(3,1).Interior.Color=4569325 EndSub

Проверено в Excel 2016.

Вывод сообщений о числовых значениях цветов

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

Пример кода 2:

1 2 3 4 5 6

SubColorTest2() MsgBox Range(«A1»).Interior.Color MsgBox Range(«A4:D8»).Interior.Color MsgBox Range(«C12:D17»).Cells(4).Interior.Color MsgBox Cells(3,6).Interior.Color EndSub

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

Использование предопределенных констант

В VBA Excel есть предопределенные константы часто используемых цветов для заливки ячеек:

Предопределенная константа	Наименование цвета
vbBlack	Черный
vbBlue	Голубой
vbCyan	Бирюзовый
vbGreen	Зеленый
vbMagenta	Пурпурный
vbRed	Красный
vbWhite	Белый
vbYellow	Желтый
xlNone	Нет заливки

Присваивается цвет ячейке предопределенной константой в VBA Excel точно так же, как и числовым значением:

Пример кода 3:

1	Range(«A1»).Interior.Color=vbGreen

Цветовая модель RGB

Цветовая система RGB представляет собой комбинацию различных по интенсивности основных трех цветов: красного, зеленого и синего. Они могут принимать значения от 0 до 255. Если все значения равны 0 — это черный цвет, если все значения равны 255 — это белый цвет.

Выбрать цвет и узнать его значения RGB можно с помощью палитры Excel:

Палитра Excel

Чтобы можно было присвоить ячейке или диапазону цвет с помощью значений RGB, их необходимо перевести в десятичное число, обозначающее цвет. Для этого существует функция VBA Excel, которая так и называется — RGB.

Пример кода 4:

1	Range(«A1»).Interior.Color=RGB(100,150,200)

Список стандартных цветов с RGB-кодами смотрите в статье: HTML. Коды и названия цветов.