2 способа изменить цвет заливки ячеек в Excel в зависимости от их значений

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

Каждому известно, что для изменения цвета заливки одной ячейки или целого диапазона в Excel достаточно просто нажать кнопку Fill color (Цвет заливки). Но как быть, если необходимо изменить цвет заливки всех ячеек, содержащих определённое значение? Более того, что если Вы хотите, чтобы цвет заливки каждой ячейки изменялся автоматически вместе с изменением содержимого этой ячейки? Далее в статье Вы найдёте ответы на эти вопросы и получите пару полезных советов, которые помогут выбрать правильный метод для решения каждой конкретной задачи.

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

Цвет заливки будет изменяться в зависимости от значения ячейки.

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

Решение: Используйте условное форматирование в Excel, чтобы выделить значения больше X, меньше Y или между X и Y.

Предположим, есть список цен на бензин в разных штатах, и Вы хотите, чтобы цены, превышающие $3.7, были выделены красным, а меньшие или равные $3.45 – зелёным.

Замечание: Снимки экрана для этого примера были сделаны в Excel 2010, однако, в Excel 2007 и 2013 кнопки, диалоговые окна и настройки будут точно такие же или с незначительными отличиями.

Итак, вот, что нужно сделать по шагам:

1. Выделите таблицу или диапазон, в котором Вы хотите изменить цвет заливки ячеек. В этом примере мы выделяем $B$2:$H$10 (заголовки столбцов и первый столбец, содержащий названия штатов, не выделяем).
2. Откройте вкладку Home (Главная), в разделе Styles (Стили) нажмите Conditional Formatting (Условное форматирование) > New Rule (Создать правило).
3. В верхней части диалогового окна New Formatting Rule (Создание правила форматирования) в поле Select a Rule Type (Выберите тип правила) выберите Format only cells that contain (Форматировать только ячейки, которые содержат).
4. В нижней части диалогового окна в поле Format Only Cells with (Форматировать только ячейки, для которых выполняется следующее условие) настройте условия для правила. Мы выбираем форматировать только ячейки с условием: Cell Value (Значение ячейки) – greater than (больше) – 3.7, как показано на рисунке ниже.Далее нажмите кнопку Format (Формат), чтобы выбрать, какой цвет заливки должен быть применён, если выполняется заданное условие.
5. В появившемся диалоговом окне Format Cells (Формат ячеек) откройте вкладку Fill (Заливка) и выберите цвет (мы выбрали красноватый) и нажмите ОК.
6. После этого Вы вернетесь в окно New Formatting Rule (Создание правила форматирования), где в поле Preview (Образец) будет показан образец Вашего форматирования. Если всё устраивает, нажмите ОК.

Результат Ваших настроек форматирования будет выглядеть приблизительно так:

Так как нам нужно настроить ещё одно условие, позволяющее изменять цвет заливки на зелёный для ячеек со значениями меньшими или равными 3.45, то снова нажимаем кнопку New Rule (Создать правило) и повторяем шаги с 3 по 6, устанавливая нужное правило. Ниже виден образец созданного нами второго правила условного форматирования:

Когда все будет готово – жмите ОК. Теперь у Вас имеется мило отформатированная таблица, которая даёт возможность с первого взгляда увидеть максимальные и минимальные цены на бензин в разных штатах. Хорошо им там, в Техасе!

Совет: Таким же способом Вы можете изменять цвет шрифта в зависимости от значения ячейки. Для этого просто откройте вкладку Font (Шрифт) в диалоговом окне Format Cells (Формат ячеек), как мы это делали на шаге 5, и выберите желаемый цвет шрифта.

Как настроить постоянный цвет ячейки, основываясь на её текущем значении

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

Задача: Вы хотите настроить цвет ячейки, основываясь на её текущем значении, и хотите, чтобы цвет заливки оставался неизменным, даже когда значение ячейки изменяется.

Решение: Найти все ячейки с определённым значением (или значениями) при помощи инструмента

Find All (Найти все), а затем изменить формат найденных ячеек, используя диалоговое окно Format Cells (Формат ячеек).

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

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

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

Если Вы хотите раскрасить ячейки с конкретным значением, например, 50, 100 или 3.4 – то на вкладке Home (Главная) в разделе Editing (Редактирование) нажмите Find Select (Найти и выделить) > Find (Найти).

Введите нужное значение и нажмите Find All (Найти все).

Совет: В правой части диалогового окна Find and Replace (Найти и заменить) есть кнопка Options (Параметры), нажав которую Вы получите доступ к ряду продвинутых настроек поиска, таких как Match Case

(Учитывать регистр) и Match entire cell content (Ячейка целиком). Вы можете использовать символы подстановки, такие как звёздочка (*), чтобы найти любую строку символов, или знак вопроса (?), чтобы найти один любой символ.

Что касается предыдущего примера, если нам нужно найти все цены на бензин от 3.7 до 3.799, то мы зададим такие критерии поиска:

Теперь кликните любой из найденных элементов в нижней части диалогового окна Find and Replace (Найти и заменить) и нажмите Ctrl+A

, чтобы выделить все найденные записи. После этого нажмите кнопку Close (Закрыть).

Вот так можно выделить все ячейки с заданным значением (значениями) при помощи опции Find All (Найти все) в Excel.

Однако, в действительности нам нужно найти все цены на бензин, превышающие $3.7. К сожалению, инструмент Find and Replace (Найти и заменить) в этом не сможет нам помочь.

Измененяем цвета заливки выделенных ячеек при помощи диалогового окна «Формат ячеек»

Теперь у Вас выделены все ячейки с заданным значением (или значениями), мы сделали это только что при помощи инструмента Find and Replace (Найти и заменить).

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

Откройте диалоговое окно Format Cells (Формат ячеек) любым из 3-х способов:

• нажав Ctrl+1.
• кликнув по любой выделенной ячейке правой кнопкой мыши и выбрав в контекстном меню пункт Format Cells (Формат ячеек).
• на вкладке Home (Главная) > Cells (Ячейки) > Format (Формат) > Format Cells (Формат ячеек).

Далее настройте параметры форматирования так, как Вам угодно. На этот раз мы установим оранжевый цвет заливки, просто для разнообразия

Если Вы хотите изменить только цвет заливки, не касаясь остальных параметров форматирования, то можете просто нажать кнопку

Fill color (Цвет заливки) и выбрать понравившийся цвет.

Вот результат наших изменений форматирования в Excel:

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

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

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

Используем формулу для изменения цвета заливки особых ячеек в Excel

Цвет ячейки будет изменяться автоматически в зависимости от значения ячейки.

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

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

1. На вкладке Home (Главная) в разделе Styles (Стили) нажмите Conditional Formatting (Условное форматирование) > New Rule (Создать правило). Точно также, как на 2-м шаге примера Как динамически изменять цвет ячейки, основываясь на её значении.
2. В диалоговом окне New Formatting Rule (Создание правила форматирования) выберите вариант Use a formula to determine which cells to format (Использовать формулу для определения форматируемых ячеек). Далее в поле Format values where this formula is true (Форматировать значения, для которых следующая формула является истинной) введите одну из формул:

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

   =ISBLANK(B2:h22)
=ЕПУСТО(B2:h22)

3. Нажмите кнопку Format (Формат), выберите нужный цвет заливки на вкладке Fill (Заливка), а затем нажмите ОК. Подробные инструкции даны на шаге 5 примера “Как динамически изменять цвет ячейки, основываясь на её значении”. Образец настроенного Вами условного форматирования будет выглядеть приблизительно так:
4. Если Вы довольны цветом, жмите ОК. Вы увидите, как созданное правило немедленно будет применено к таблице.

Изменяем цвет заливки особых ячеек статически

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

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

1. Выделите таблицу или диапазон и нажмите F5, чтобы открыть диалоговое окно Go To (Переход), затем нажмите кнопку Special (Выделить).
2. В диалоговом окне Go to Special (Выделить группу ячеек) отметьте вариант Blanks (Пустые ячейки), чтобы выделить все пустые ячейки.Если Вы хотите выделить ячейки, содержащие формулы с ошибками, отметьте вариант Formulas (Формулы) > Errors (Ошибки). Как видно на рисунке выше, Вам доступно множество других настроек.
3. И наконец, измените заливку выделенных ячеек или настройте любые другие параметры форматирования при помощи диалогового окна Format Cells (Формат ячеек), как это описано в разделе Изменение заливки выделенных ячеек.

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

Оцените качество статьи. Нам важно ваше мнение:

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

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

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

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

Предположим, у нас есть вот такая таблица заказов компании:

Мы хотим раскрасить различными цветами строки в зависимости от заказанного количества товара (значение в столбце Qty.), чтобы выделить самые важные заказы. Справиться с этой задачей нам поможет инструмент Excel – «Условное форматирование».

1. Первым делом, выделим все ячейки, цвет заливки которых мы хотим изменить.
2. Чтобы создать новое правило форматирования, нажимаем Главная > Условное форматирование > Создать правило (Home > Conditional Formatting > New rule).
3. В появившемся диалоговом окне Создание правила форматирования (New Formatting Rule) выбираем вариант Использовать формулу для определения форматируемых ячеек (Use a formula to determine which cells to format), и ниже, в поле Форматировать значения, для которых следующая формула является истинной (Format values where this formula is true), вводим такое выражение:

   =$C2>4

   Вместо C2 Вы можете ввести ссылку на другую ячейку Вашей таблицы, значение которой нужно использовать для проверки условия, а вместо 4 можете указать любое нужное число. Разумеется, в зависимости от поставленной задачи, Вы можете использовать операторы сравнения меньше (<) или равно (=), то есть записать формулы в таком виде:

   =$C2<4
=$C2=4

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

4. Нажимаем кнопку Формат (Format) и переходим на вкладку Заливка (Fill), чтобы выбрать цвет фона ячеек. Если стандартных цветов недостаточно, нажмите кнопку Другие цвета (More Colors), выберите подходящий и дважды нажмите ОК.Таким же образом на остальных вкладках диалогового окна Формат ячеек (Format Cells) настраиваются другие параметры форматирования, такие как цвет шрифта или границы ячеек.
5. В поле Образец (Preview) показан результат выполнения созданного правила условного форматирования:
6. Если всё получилось так, как было задумано, и выбранный цвет устраивает, то жмём ОК, чтобы увидеть созданное правило в действии.Теперь, если значение в столбце Qty. больше 4, то соответствующая строка таблицы целиком станет голубой.

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

Как создать несколько правил условного форматирования с заданным приоритетом

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

=$C2>9

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

1. На вкладке Главная (Home) в разделе Стили (Styles) нажмите Условное форматирование (Conditional Formatting) > Управление правилами (Manage Rules)
2. В выпадающем списке Показать правила форматирования для (Show formatting rules for) выберите Этот лист (This worksheet). Если нужно изменить параметры только для правил на выделенном фрагменте, выберите вариант Текущий фрагмент (Current Selection).
3. Выберите правило форматирования, которое должно быть применено первым, и при помощи стрелок переместите его вверх списка. Должно получиться вот так:Нажмите ОК, и строки в указанном фрагменте тут же изменят цвет, в соответствии с формулами в обоих правилах.

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

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

• Если срок доставки заказа находится в будущем (значение Due in X Days), то заливка таких ячеек должна быть оранжевой;
• Если заказ доставлен (значение Delivered), то заливка таких ячеек должна быть зелёной;
• Если срок доставки заказа находится в прошлом (значение Past Due), то заливка таких ячеек должна быть красной.

И, конечно же, цвет заливки ячеек должен изменяться, если изменяется статус заказа.

С формулой для значений Delivered и Past Due всё понятно, она будет аналогичной формуле из нашего первого примера:

=$E2="Delivered"
=$E2="Past Due"

Сложнее звучит задача для заказов, которые должны быть доставлены через Х дней (значение Due in X Days). Мы видим, что срок доставки для различных заказов составляет 1, 3, 5 или более дней, а это значит, что приведённая выше формула здесь не применима, так как она нацелена на точное значение.

В данном случае удобно использовать функцию ПОИСК (SEARCH) и для нахождения частичного совпадения записать вот такую формулу:

=ПОИСК("Due in";$E2)>0
=SEARCH("Due in",$E2)>0

В данной формуле E2 – это адрес ячейки, на основании значения которой мы применим правило условного форматирования; знак доллара $ нужен для того, чтобы применить формулу к целой строке; условие “>0” означает, что правило форматирования будет применено, если заданный текст (в нашем случае это “Due in”) будет найден.

Подсказка: Если в формуле используется условие “>0“, то строка будет выделена цветом в каждом случае, когда в ключевой ячейке будет найден заданный текст, вне зависимости от того, где именно в ячейке он находится. В примере таблицы на рисунке ниже столбец Delivery (столбец F) может содержать текст “Urgent, Due in 6 Hours” (что в переводе означает – Срочно, доставить в течение 6 часов), и эта строка также будет окрашена.

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

=ПОИСК("Due in";$E2)=1
=SEARCH("Due in",$E2)=1

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

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

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

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

Например, мы можем настроить три наших правила таким образом, чтобы выделять цветом только ячейки, содержащие номер заказа (столбец Order number) на основании значения другой ячейки этой строки (используем значения из столбца Delivery).

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

Если нужно выделить строки одним и тем же цветом при появлении одного из нескольких различных значений, то вместо создания нескольких правил форматирования можно использовать функции И (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<=10)
=AND($D2>=5,$D2<=10)

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

=ИЛИ($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 посчитать количество, сумму и настроить фильтр для ячеек определённого цвета.

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

Оцените качество статьи. Нам важно ваше мнение:

Добавление и изменение цвета фона ячеек

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

1. Выберите ячейки, которые нужно выделить.

   Советы: 

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

2. Щелкните Главная > стрелку рядом с кнопкой Цвет заливки или нажмите клавиши ALT+H, H.

3. Выберите нужный цвет в группе Цвета темы или Стандартные цвета.

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

   Совет: Чтобы применить последний выбранный цвет, достаточно нажать кнопку Цвет заливки . Кроме того, в группе Последние цвета доступны до 10 цветов, которые вы выбирали в последнее время.

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

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

1. Выделите ячейку или диапазон ячеек, которые нужно отформатировать.

2. На вкладке Главная нажмите кнопку вызова диалогового окна Формат ячеек или просто нажмите клавиши CTRL+SHIFT+F.

3. На вкладке Заливка выберите в разделе Цвет фона нужный цвет.

4. Чтобы использовать двухцветный узор, выберите цвет в поле Цвет узора, а затем выберите сам узор в поле Узор.

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

   Совет: В поле Образец можно просмотреть выбранный фон, узор и способ заливки.

Удаление цвета, узора и способа заливки из ячеек

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

Цветная печать ячеек, включая цвет фона, узор и способ заливки

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

1. Откройте вкладку Разметка страницы и нажмите кнопку вызова диалогового окна Параметры страницы.

2. На вкладке Лист в группе Печать снимите флажки черно-белая и черновая.

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

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

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

2. На вкладке Главная нажмите кнопку Цвет заливкии выберите нужный цвет.

Примечание: Эффекты узорной заливки для цветов фона недоступны для Excel в Интернете. Если применить любой из Excel на компьютере, он не будет отображаться в браузере.

Удаление цвета заливки

Если вы решите, что цвет заливки не нужен сразу после его вжатия, просто нажмите кнопку Отменить.

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

Изменение цвета текста — Excel

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

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

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

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

2. На вкладке Главная щелкните стрелку рядом с кнопкой Цвет шрифта .

3. Выберите цвет в группе Цвета темы или Стандартные цвета.

   Совет: Чтобы применить последний выбранный цвет текста, на вкладке Главная нажмите кнопку Цвет текста.

Применение дополнительного цвета

Если вам нужен определенный цвет текста, вот как можно его получить:

1. На вкладке Главная щелкните стрелку рядом с кнопкой Цвет текста и выберите команду Другие цвета.

2. На вкладке Спектр в поле Цвета выберите нужный цвет.

   Если вы знаете числовые значения составляющих нужного цвета, в поле Цветовая модель выберите модель RGB (Red, Green, Blue — красный, зеленый, синий) или HSL (Hue, Sat, Lum — тон, насыщенность, яркость), а затем введите числа, в точности соответствующие искомому цвету.

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

Форматирование цвета ярлычка листа

1. Щелкните правой кнопкой мыши ярлычок листа, цвет которого вы хотите изменить.

2. Щелкните Цвет ярлычка, а затем выберите нужный цвет.

   Изменится цвет ярлычка, но не цвет шрифта. При выборе темного цвета ярлычка цвет шрифта меняется на белый, а при выборе светлого цвета — на черный.

См. также

Изменение внешнего вида листа

Применение условного форматирования с помощью формулы

VBA Excel. Цвет ячейки (заливка, фон)

Заливка ячейки цветом в VBA Excel. Фон ячейки. Свойства .Interior.Color и .Interior.ColorIndex. Цветовая модель RGB. Стандартная палитра. Очистка фона ячейки.

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

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

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

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

Sub ColorTest1() 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 End Sub

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

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

Sub ColorTest11() Cells(1, 1). Interior.Color = -12207890 Cells(2, 1).Interior.Color = 16777215 + (-12207890) Cells(3, 1).Interior.Color = 4569325 End Sub

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

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

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

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

Sub ColorTest2() 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 End Sub

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

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

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

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

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

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

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

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

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

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

Палитра Excel

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

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

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

Очистка ячейки (диапазона) от заливки

Для очистки ячейки (диапазона) от заливки используется константа xlNone:

Range(«A1»). Interior.Color = xlNone

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

До появления Excel 2007 существовала только ограниченная палитра для заливки ячеек фоном, состоявшая из 56 цветов, которая сохранилась и в настоящее время. Каждому цвету в этой палитре присвоен индекс от 1 до 56. Присвоить цвет ячейке по индексу или вывести сообщение о нем можно с помощью свойства .Interior.ColorIndex:

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

Range(«A1»).Interior.ColorIndex = 8 MsgBox Range(«A1»).Interior.ColorIndex

Просмотреть ограниченную палитру для заливки ячеек фоном можно, запустив в VBA Excel простейший макрос:

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

Sub ColorIndex() Dim i As Byte For i = 1 To 56 Cells(i, 1).Interior.ColorIndex = i Next End Sub

Номера строк активного листа от 1 до 56 будут соответствовать индексу цвета, а ячейка в первом столбце будет залита соответствующим индексу фоном.

Готовую стандартную палитру из 56 цветов можете посмотреть здесь.

Смотрите как добавить узор в ячейку.

---

excel цвет текста в формуле

На чтение 3 мин. Просмотров 118 Опубликовано 15.12.2019

Описание работы

Функция =ЦВЕТШРИФТА(ЯЧЕЙКА) возвращает код цвета шрифта указанной ячейки. Функция имеет один обязательный аргумент.

• ЯЧЕЙКА — ссылка на ячейку, цвет шрифта которой необходимо определить.

Ниже представлен пример, демонстрирующий работу функции.

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

Зачем это нужно?

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

Код на VBA

Вы можете самостоятельно внедрить данную функцию в Excel или установить надстройку.

Для Excel — 2007 на главной панели выбираете «Условное форматирование» — «Правила выделения ячеек» — «Другие правила» — «Использовать формулу для определения форматируемых ячеек» — «Форматировать значения, для которых следующая формула является истинной»

Далее задаёте формулу и формат (в Вашем случае — цвет выделения текста) .

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

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

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

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

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

На вкладке Главная щелкните стрелку рядом с кнопкой Цвет шрифта .

Выберите цвет в группе Цвета темы или Стандартные цвета.

Совет: Чтобы применить последний выбранный цвет текста, на вкладке Главная нажмите кнопку Цвет текста.

Применение дополнительного цвета

Если вам нужен определенный цвет текста, вот как можно его получить:

На вкладке Главная щелкните стрелку рядом с кнопкой Цвет текста и выберите команду Другие цвета.

На вкладке Спектр в поле Цвета выберите нужный цвет.

Если вы знаете числовые значения составляющих нужного цвета, в поле Цветовая модель выберите модель RGB (Red, Green, Blue — красный, зеленый, синий) или HSL (Hue, Sat, Lum — тон, насыщенность, яркость), а затем введите числа, в точности соответствующие искомому цвету.

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

Форматирование цвета ярлычка листа

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

Щелкните Цвет ярлычка, а затем выберите нужный цвет.

Изменится цвет ярлычка, но не цвет шрифта. При выборе темного цвета ярлычка цвет шрифта меняется на белый, а при выборе светлого цвета — на черный.

Заливка ячеек в зависимости от значения в Microsoft Excel.

Untitled Document Упражнения и Самостоятельные

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

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

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

Предположим, у нас есть вот такая таблица заказов компании:

Мы хотим раскрасить различными цветами строки в зависимости от заказанного количества товара (значение в столбце Qty. ), чтобы выделить самые важные заказы. Справиться с этой задачей нам поможет инструмент Excel – «Условное форматирование ».

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

Как создать несколько правил условного форматирования с заданным приоритетом

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

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

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

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

• Если срок доставки заказа находится в будущем (значение Due in X Days ), то заливка таких ячеек должна быть оранжевой;
• Если заказ доставлен (значение Delivered ), то заливка таких ячеек должна быть зелёной;
• Если срок доставки заказа находится в прошлом (значение Past Due ), то заливка таких ячеек должна быть красной.

И, конечно же, цвет заливки ячеек должен изменяться, если изменяется статус заказа.

С формулой для значений Delivered и Past Due всё понятно, она будет аналогичной формуле из нашего первого примера:

=$E2=»Delivered»
=$E2=»Past Due»

Сложнее звучит задача для заказов, которые должны быть доставлены через Х дней (значение Due in X Days ). Мы видим, что срок доставки для различных заказов составляет 1, 3, 5 или более дней, а это значит, что приведённая выше формула здесь не применима, так как она нацелена на точное значение.

В данном случае удобно использовать функцию ПОИСК (SEARCH) и для нахождения частичного совпадения записать вот такую формулу:

ПОИСК(«Due in»;$E2)>0
=SEARCH(«Due in»,$E2)>0

В данной формуле E2 – это адрес ячейки, на основании значения которой мы применим правило условного форматирования; знак доллара $ нужен для того, чтобы применить формулу к целой строке; условие “>0 ” означает, что правило форматирования будет применено, если заданный текст (в нашем случае это “Due in”) будет найден.

Подсказка: Если в формуле используется условие “>0 “, то строка будет выделена цветом в каждом случае, когда в ключевой ячейке будет найден заданный текст, вне зависимости от того, где именно в ячейке он находится. В примере таблицы на рисунке ниже столбец Delivery (столбец F) может содержать текст “Urgent, Due in 6 Hours” (что в переводе означает – Срочно, доставить в течение 6 часов), и эта строка также будет окрашена.

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

ПОИСК(«Due in»;$E2)=1
=SEARCH(«Due in»,$E2)=1

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

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

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

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

Например, мы можем настроить три наших правила таким образом, чтобы выделять цветом только ячейки, содержащие номер заказа (столбец Order number ) на основании значения другой ячейки этой строки (используем значения из столбца Delivery ).

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

Если нужно выделить строки одним и тем же цветом при появлении одного из нескольких различных значений, то вместо создания нескольких правил форматирования можно использовать функции И (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 посчитать количество, сумму и настроить фильтр для ячеек определённого цвета .

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

Заливка ячейки цветом в VBA Excel. Фон ячейки. Свойства.Interior.Color и.Interior.ColorIndex. Цветовая модель RGB. Стандартная палитра из 56 цветов. Предопределенные константы.

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

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

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

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

Sub ColorTest1() 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 End Sub

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

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

Sub ColorTest11() Cells(1, 1).Interior.Color = -12207890 Cells(2, 1).Interior.Color = 16777215 + (-12207890) Cells(3, 1).Interior.Color = 4569325 End Sub

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

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

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

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

Sub ColorTest2() 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 End Sub

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

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

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

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

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

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

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

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

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

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

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

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

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

До появления Excel 2007 существовала только ограниченная палитра для заливки ячеек фоном, состоявшая из 56 цветов, которая сохранилась и в настоящее время. Каждому цвету в этой палитре присвоен индекс от 1 до 56. Присвоить цвет ячейке по индексу или вывести сообщение о нем можно с помощью свойства.Interior.ColorIndex:

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

Range(«A1»).Interior.ColorIndex = 8 MsgBox Range(«A1»).Interior.ColorIndex

Просмотреть ограниченную палитру для заливки ячеек фоном можно, запустив в VBA Excel простейший макрос:

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

Sub ColorIndex() Dim i As Byte For i = 1 To 56 Cells(i, 1).Interior.ColorIndex = i Next End Sub

Номера строк активного листа от 1 до 56 будут соответствовать индексу цвета, а ячейка в первом столбце будет залита соответствующим индексу фоном.

Готовую стандартную палитру из 56 цветов можете посмотреть .

Привет, уважаемые читатели. Когда-нибудь вам доводилось работать с огромными данными в таблице? Знаете, с ними гораздо удобнее будет работать, если знать, как выделить несколько ячеек Excel различным цветом при определенном условии. Хотели бы вы узнать, как это делается? В этом уроке мы сделаем так, чтобы менялся цвет ячейки в зависимости от значения Excel, а также окрасим все ячейки с помощью поиска.

Цвет заливки меняется вместе со значением

Для примера мы потренируемся на том, чтобы ячейка меняла цвет в данной таблице при определенном условии. Да ни одна, а все со значением в диапазоне от 60 до 90. Для этого мы воспользуемся функцией «Условное форматирование».

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

Далее находим на вкладке «Главная» кнопку «Условное форматирование» и в списке выбираем «Создать правило».

У нас открылось окно «Создание правил форматирования». В этом окне выбираем тип правила: «Форматировать только ячейки, которые содержат».

Далее, переходим к разделу «Измените описание правила», где нужно указать те условия, по которым будет выполнена заливка. В этом разделе можно выставить самые различные условия, при которых она будет меняться.

В нашем случае необходимо поставить следующие: «значения ячейки» и «между». Так же мы обозначаем диапазон, что при условии значения от 60 до 90 будет применена заливка. Посмотрите на скриншоте, как это сделал я.

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

Если вы заполнили, то не спешите кликать по кнопке «ОК». Прежде необходимо нажать на кнопку «Формат», как на скриншоте, и перейти к настройке заливки.

Хорошо, как видите, у вас открылось окно «Формат ячейки». Здесь вам нужно перейти на вкладку «Заливка», где вы выбираете нужную, и нажать на «ОК» в этом окне и в предыдущем. Я выбрал зеленую заливку.

Посмотрите на свой результат. Думаю, у вас все получилось. У меня точно получилось. Взгляните на скриншот:

Окрасим ячейку в определенный цвет, если она равна чему-то

Давайте вернемся к нашей таблице в изначальном виде. И теперь мы поменяем цвет там, где содержится цифра 40 на красный цвет, а с цифрой 50 на желтый. Конечно, для этого дела можно воспользоваться первым способом, но мы же хотим знать больше возможностей Excel.

В этот раз мы воспользуемся функцией «Найти и заменить».

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

Теперь время открыть окно поиска. На вкладке «Главная» в разделе «Редактирование» нажмите на кнопку «Найти и выделить».

Можно же и горячими клавишами пользоваться: CTRL + F

В поле «Найти» мы указываем то, что ищем. В данном случае пишем «40», а затем жмем кнопку «Найти все».

Теперь, когда ниже были показаны результаты поиска, выберите одно из них и нажмите на сочетание CTRL + A, чтобы выбрать их все сразу. А затем нажмите на «Закрыть», чтобы убрать окно «Найти и заменить».

Когда у нас выбраны все, содержащие цифру 40, на вкладке «Главная» в разделе «Шрифт» выберите окраску ячейки. У нас это красный. И, как вы видите у себя на экране, так и у меня на скриншоте, они окрасились в красный.

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

У вас получилось? А посмотрите, что вышло у меня.

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

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

Пусть в диапазоне А6:С16 имеется таблица с перечнем работ, сроками выполнения и статусом их завершения (см. файл примера ).

Задача1 — текстовые значения

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

Решение1

Создадим небольшую табличку со статусами работ в диапазоне Е6:Е9 .

Выделим диапазон ячеек А7:С17 , содержащий перечень работ, и установим через меню Главная/ Цвет заливки фон заливки красный (предполагаем, что все работы изначально находятся в статусе Не начата ).

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

• в поле «Форматировать значения, для которых следующая формула является истинной » нужно ввести =$C7=$E$8 (в ячейке Е8 находится значение В работе ). Обратите внимание на использоване ;
• нажать кнопку Формат ;
• выбрать вкладку Заливка ;
• выбрать серый цвет;
• Нажать ОК.

ВНИМАНИЕ : Еще раз обращаю внимание на формулу =$C7=$E$8 . Обычно пользователи вводят =$C$7=$E$8 , т.е. вводят лишний символ доллара.

Нужно проделать аналогичные действия для выделения работ в статусе Завершена . Формула в этом случае будет выглядеть как =$C7=$E$9 , а цвет заливки установите зеленый.

В итоге наша таблица примет следующий вид.

Чтобы быстро расширить правила Условного форматирования на новую строку в таблице, выделите ячейки новой строки (А17:С17 ) и нажмите . Правила будут скопированы в строку 17 таблицы.

Задача2 — Даты

Предположим, что ведется журнал посещения сотрудниками научных конференций (см. файл примера лист Даты ).

К сожалению, столбец Дата посещения не отсортирован и необходимо выделить дату первого и последнего посещения каждого сотрудника. Например, сотрудник Козлов первый раз поехал на конференцию 24.07.2009, а последний раз — 18.07.2015.

Сначала создадим формулу для условного форматирования в столбцах В и E. Если формула вернет значение ИСТИНА, то соответствующая строка будет выделена, если ЛОЖЬ, то нет.

В столбце D создана =МАКС(($A7=$A$7:$A$16)*$B$7:$B$16)=$B7 , которая определяет максимальную дату для определенного сотрудника.

Примечание: Если нужно определить максимальную дату вне зависимости от сотрудника, то формула значительно упростится =$B7=МАКС($B$7:$B$16) и формула массива не понадобится.

Теперь выделим все ячейки таблицы без заголовка и создадим правило . Скопируем формулу в правило (ее не нужно вводить как формулу массива!).

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

Для этого используйте формулу =И($B23>$E$22;$B23

Для ячеек Е22 и Е23 с граничными датами (выделены желтым) использована $E$22 и $E$23. Т.к. ссылка на них не должна меняться в правилах УФ для всех ячеек таблицы.

Для ячейки В22 использована смешанная адресация $B23, т.е. ссылка на столбец В не должна меняться (для этого стоит перед В знак $), а вот ссылка на строку должна меняться в зависимости от строки таблицы (иначе все значения дат будут сравниваться с датой из В23 ).

Таким образом, правило УФ например для ячейки А27 будет выглядеть =И($B27>$E$22;$B27А27 будет выделена, т.к. в этой строке дата из В27 попадает в указанный диапазон (для ячеек из столбца А выделение все равно будет производиться в зависимости от содержимого столбца В из той же строки — в этом и состоит «магия» смешанной адресации $B23).

А для ячейки В31 правило УФ будет выглядеть =И($B31>$E$22;$B31В31 не попадает в указанный диапазон.

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

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

Всем известно, что изменить цвет фона отдельной ячейки или диапазона данных в Excel просто, нажав кнопку « Цвет заливки ».Но что, если вы хотите изменить цвет фона всех ячеек с определенным значением? Более того, что, если вы хотите, чтобы цвет фона изменялся автоматически вместе с изменением значения ячейки? Далее в этой статье вы найдете ответы на эти вопросы и узнаете пару полезных советов, которые помогут вам выбрать правильный метод для каждой конкретной задачи.

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

Цвет фона будет меняться в зависимости от значения ячейки.

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

Решение : необходимо использовать условное форматирование Excel, чтобы выделить значения больше X , меньше Y или между X и Y .

Предположим, у вас есть список цен на бензин в разных штатах, и вы хотите, чтобы цены превышали 3 доллара США.7 должна быть красного цвета и равна или меньше 3,45 доллара США, чтобы быть зеленого цвета.

Примечание : снимки экрана для этого примера были сделаны в Excel 2010, однако кнопки, диалоговые окна и настройки такие же или почти такие же в Excel 2016 и Excel 2013.

Хорошо, вот что вы делаете, шаг за шагом:

1. Выберите таблицу или диапазон, в котором вы хотите изменить цвет фона ячеек. В этом примере мы выбрали $ B $ 2: $ H $ 10 (имена столбцов и первый столбец, в котором перечислены имена состояний, исключены из выбора).
2. Перейдите на вкладку «Главная» в группе «Стили» и выберите Условное форматирование > Новое правило… .
   
3. В диалоговом окне Новое правило форматирования выберите « Форматировать только ячейки, содержащие » в поле « Выберите тип правила » в верхней части диалогового окна.
4. В нижней части диалогового окна в разделе « Форматировать только ячейки с разделом » задайте условия правила. Мы выбираем форматировать только ячейки со значением Cell Value — больше, чем — 3.7 , как видно на скриншоте ниже.
   

   Затем нажмите кнопку Format… , чтобы выбрать, какой цвет фона применять при выполнении вышеуказанного условия.

5. В диалоговом окне Format Cells перейдите на вкладку Fill и выберите цвет по вашему выбору, в нашем случае красноватый цвет, и нажмите OK .
   
6. Теперь вы вернулись в окно Новое правило форматирования , и предварительный просмотр ваших изменений формата отображается в поле Preview .Если все в порядке, нажмите кнопку ОК .
   

   Результат вашего форматирования будет выглядеть примерно так:
   

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

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

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

Как навсегда изменить цвет ячейки в зависимости от ее текущего значения

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

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

Решение : найдите все ячейки с определенным значением или значениями с помощью функции Excel Find All или надстройки Select Special Cells , а затем измените формат найденных ячеек с помощью функции Format Cells .

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

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

В зависимости от того, какие ценности вы ищете, может быть несколько возможных сценариев.

Если вам нужно раскрасить ячейки определенным значением, например 50, 100 или 3.4, перейдите на вкладку Home , группу Editing и щелкните Find Select > Find… .

Введите необходимые значения и нажмите кнопку Найти все .

Совет : нажмите кнопку Options в правой части диалогового окна Найти и заменить , чтобы получить ряд расширенных параметров поиска, таких как « Match Case » и « Match all cell content ». «. Вы можете использовать подстановочные знаки, такие как звездочка (*), чтобы найти любую строку символов, или вопросительный знак (?), Чтобы найти любой отдельный символ.

В нашем предыдущем примере, если нам нужно было найти все цены на газ от 3,7 до 3,799, мы должны указать следующие критерии поиска:

Теперь выберите любой из найденных элементов в нижней части диалогового окна «Найти и заменить» , щелкнув его, а затем нажмите Ctrl + A, чтобы выбрать все найденные записи. После этого нажмите кнопку Закрыть .

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

Однако на самом деле нам нужно найти все цены на бензин выше 3,7, и, к сожалению, диалоговое окно Excel Find and Replace не позволяет таких вещей.

К счастью, есть еще один инструмент, который может справиться с такими сложными условиями. Надстройка Select Special Cells позволяет находить все значения в указанном диапазоне, например от -1 до 45, получить максимальное / минимальное значение в столбце, строке или диапазоне, найти ячейки по цвету шрифта, цвету заливки и многому другому.

Вы нажимаете кнопку Выбрать по значению на ленте, а затем указываете критерии поиска на панели надстройки, в нашем примере мы ищем значения больше 3.7. Нажмите кнопку Select , и через секунду вы получите следующий результат:

Если вы хотите попробовать надстройку Select Special Cells , вы можете загрузить ознакомительную версию здесь.

Изменить цвет фона выбранных ячеек с помощью диалогового окна «Формат ячеек»

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

Откройте диалоговое окно Формат ячеек , нажав Ctrl + 1 (вы также можете щелкнуть правой кнопкой мыши любую из выбранных ячеек и выбрать « Форматировать ячейки… » во всплывающем меню или перейти на вкладку «Главная» > Группа Ячейки > Format > Format Cells… ) и внесите все необходимые изменения формата. На этот раз мы выберем оранжевый цвет фона, просто для разнообразия 🙂

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

Вот результат наших изменений формата в Excel:

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

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

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

Используйте формулу Excel для изменения цвета фона специальных ячеек

Цвет ячейки будет автоматически меняться в зависимости от значения ячейки.

Этот метод предоставляет решение, которое вам, скорее всего, понадобится в 99% случаев, т. Е. Цвет фона ячеек будет меняться в соответствии с заданными вами условиями.

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

1. На вкладке Home в группе Styles щелкните Conditional Formatting > New Rule… (пошаговые инструкции см. В шаге 2 раздела Как динамически изменять цвет ячейки в зависимости от значения) .
2. В диалоговом окне « Новое правило форматирования » выберите параметр « Использовать формулу для определения ячеек для форматирования ». Затем введите одну из следующих формул в поле «Значения формата , где эта формула истинна »:
   • = IsBlank () — для изменения цвета фона пустых ячеек.
   • = IsError () — для изменения цвета фона ячеек с формулами, возвращающими ошибки.

   Поскольку нас интересует изменение цвета пустых ячеек, введите формулу = IsBlank () , затем поместите курсор между круглыми скобками и нажмите кнопку Свернуть диалог в правой части окна, чтобы выбрать диапазон ячеек, или вы можете ввести диапазон вручную, например.грамм. = IsBlank (B2: h22) .
   

3. Нажмите кнопку Format… и выберите нужный цвет фона на вкладке Fill (подробные инструкции см. В шаге 5 «Как динамически изменять цвет ячейки в зависимости от значения»), а затем нажмите OK .

   Предварительный просмотр вашего правила условного форматирования будет выглядеть примерно так:
   

4. Если вас устраивает цвет, нажмите кнопку OK , и вы увидите, что изменения сразу же применяются к вашей таблице.
   

Статическое изменение цвета фона специальных ячеек

После изменения цвет фона останется прежним, независимо от изменения значений ячеек.

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

1. Выберите таблицу или диапазон и нажмите F5, чтобы открыть диалоговое окно « Перейти к », а затем нажмите кнопку « Special… ».
   
2. В диалоговом окне « Перейти к специальному » установите переключатель Пробелы , чтобы выбрать все пустые ячейки .
   

   Если вы хотите выделить ячейки, содержащие формул с ошибками , выберите Формулы > Ошибки . Как вы можете видеть на скриншоте выше, вам доступно несколько других опций.

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

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

Как максимально эффективно использовать Excel и упрощать сложные задачи

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

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

Именно по этой причине команда наших лучших разработчиков Excel разработала и создала более 60 надстроек, которые мы называем Ultimate Suite for Excel. Эти интеллектуальные инструменты справляются с самыми изнурительными, кропотливыми и подверженными ошибкам задачами в Excel и обеспечивают быстрых, точных и безупречных результатов .Ниже приведен краткий список лишь некоторых задач, с которыми могут помочь надстройки:

Просто попробуйте эти надстройки, и вы увидите, что ваша продуктивность в Excel увеличится, по крайней мере, до 50%!

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

Вас также может заинтересовать

Узнайте, как заполнить ячейку цветом в зависимости от условия

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

Заполните ячейку цветом в соответствии с условием

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

Статический формат ячейки для цветов

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

В приведенном выше примере цвет ячейки E3 был изменен с цвета без заливки на синий, и обратите внимание, что значение в ячейке E3 равно 6, и если мы изменим значение в этой ячейке с 6 на любое другое значение, цвет ячейки будет не изменится, и он всегда останется синим. Что это значит?

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

Цвет ячейки условного форматирования на основе значения ячейки

А что, если мы хотим изменить цвет ячейки в зависимости от значения ячейки? Предположим, мы хотим, чтобы цвет ячейки E3 изменялся вместе с изменением значения в ней. Скажем, мы хотим закрасить ячейку E3 следующим образом:

• 0-10: мы хотим, чтобы цвет ячейки был синим
• 11-20: Мы хотим, чтобы цвет ячейки был красным
• 21-30: мы хотим, чтобы ячейка была желтого цвета.
• Любое другое значение или Пусто: Без цвета или Без заливки.

Этого можно добиться с помощью условного форматирования . На вкладке Home в подгруппе Style щелкните Conditional Formatting → New Rule .

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

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

Затем в первом поле введите 0, а во втором поле введите 10, затем нажмите кнопку Format и перейдите на вкладку Fill , выберите синий цвет, нажмите Ok и снова нажмите Ok .Теперь введите значение от 0 до 10 в ячейку E3, и вы увидите, что цвет ячейки изменится на синий, и если есть какое-либо другое значение или нет значения, цвет ячейки станет прозрачным.

Повторите тот же процесс для 11-20 и 21-30, и вы увидите, что это число изменится в соответствии со значением ячейки.

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

Точно так же мы можем проделать тот же процесс для текстовых значений вместо числовых, используя « Specific Text » в первом раскрывающемся списке, а во втором раскрывающемся списке выберите одно из 4 значений, содержащих, не содержащих, начало с, заканчивая, а затем введите конкретный текст в текстовое поле.

Например:

Сначала выберите ячейку, к которой вы хотите применить условный формат, здесь нам нужно выбрать ячейку B1. На главной вкладке в подгруппе Styles щелкните Conditional Formatting → New Rule .

Теперь выберите Форматировать только ячейки, содержащие параметр, затем в первом раскрывающемся списке выберите « Specific Text » и во втором раскрывающемся списке выберите один из 4 вариантов: содержащий, не содержащий, начиная с, заканчивая на .В приведенном ниже примере мы начинаем с буквы «J», а затем нажимаем кнопку «Формат», чтобы выбрать Синий в качестве цвета заливки.

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

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

Теперь предположим, что если мы хотим изменить цвет ячейки E3 на синий, если значение D3 больше 3, и на зеленый, если значение D3 больше 5, и на красный, если значение D3 больше 10, мы можем сделать это с помощью условный формат с использованием формулы.

Снова выполните ту же процедуру.

Сначала выберите ячейку, к которой вы хотите применить условный формат, здесь нам нужно выбрать ячейку E3. На главной вкладке в подгруппе Styles щелкните Conditional Formatting → New Rule .

Теперь выберите Используйте формулу, чтобы определить, какие ячейки нужно отформатировать, опцию, и в поле введите формулу: D3> 5 ; затем нажмите кнопку Format , чтобы выбрать зеленый цвет в качестве цвета заливки.

Имейте в виду, что мы меняем формат ячейки E3 на основе значения ячейки D3, обратите внимание, что курсор теперь указывает на E3, которая является ячейкой, которую мы используем для установки условного формата. Формула «= D3> 5» означает, что если D3 больше 5, значение E3 изменится на зеленый.Нажмите ОК, и вы увидите, что цвет ячейки E3 изменится на зеленый, поскольку D3 сейчас содержит 6.

Теперь применим условное форматирование к E3, если D3 больше 3. Это означает, что если D3> 3, то цвет ячейки должен стать «синим», а если D3> 5, то цвет ячейки должен оставаться зеленым, как мы делали это на предыдущем шаге.

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

Итак, мы должны упорядочить правила, которые мы применили для каждой конкретной ячейки, мы можем сделать это, перейдя в опцию условного форматирования Manage Rules .

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

Теперь, когда вы вводите 6 в D3, цвет ячейки E3 станет зеленым, а когда вы введете 4, цвет ячейки станет синим.

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

Вам все еще нужна помощь с условным форматированием? Ознакомьтесь с нашим исчерпывающим обзором руководств по условному форматированию здесь.

Формула для изменения цвета ячейки

Привет, эксперты,

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

Условия:
Текущий ток> Мин. По региону = зеленый
Текущий ток = Нет заполнения
Текущий ток <Мин. По региону = Красный

< TD class = xl64> 383

SKU	Текущий запас	Мин по региону
PH-XT898 < / TD>	2	100
PH-XT899	471	2,500
PH-XT900	4,900
PH-XT901	126	7,300
PH-XT902	187	9,700
PH-XT903	36	12,100
PH-XT904	192	14,500
PH-XT905	25	16,900
PH-XT906	80	19,300
PH-XT907	145	21,700
PH -XT908	444	24,100
PH-XT909	49	26 500

Заранее спасибо!

С уважением,
Энтони

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

---

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

Вопрос: В Microsoft Excel 2010 я пытаюсь применить цвет заливки к ячейке на основе значения в соседней ячейке. Как я могу это сделать?

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

В этом примере у нас есть таблица в ячейках с Q1 по S10, в которой отображаются цвета заливки, которые мы хотим применить для каждого типа камня. Итак, в этом примере мы хотим отобразить желтый цвет заливки, если Rock Type — Shale, оранжевый цвет заливки, если Rock Type — Quartz, и так далее.

Мы используем ВПР в ячейках с F14 по F16, чтобы извлечь соответствующий тип камня из таблицы Q1: S10. Теперь самое сложное. Мы хотим отобразить соответствующий цвет заливки в ячейках с E14 по E16 на основе возвращенного типа камня. Мы не можем использовать ВПР для получения цвета заливки, поэтому нам придется использовать условное форматирование.

Для этого выберите диапазон ячеек, к которому вы хотите применить условное форматирование. В этом примере мы выбрали ячейки с E14 по E16. Затем выберите вкладку «Главная страница» на панели инструментов в верхней части экрана.Затем в группе стилей щелкните раскрывающийся список Условное форматирование и выберите Управление правилами .

Когда появится окно «Диспетчер правил условного форматирования», нажмите кнопку «Новое правило», чтобы ввести первое условие.

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

Затем введите следующую формулу:

 = F14 = «Сланец» 

Далее нам нужно выбрать, какое форматирование применять при выполнении этого условия.Для этого нажмите кнопку «Форматировать».

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

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

Вы вернетесь в окно «Диспетчер правил условного форматирования».Теперь вы можете просмотреть первое условие условного форматирования. Нам нужно будет добавить условие для каждого цвета заливки, нажав кнопку «Новое правило» и повторив предыдущие шаги.

После ввода всех условий вы должны увидеть что-то вроде рисунка ниже. Щелкните по кнопке ОК.

Теперь, когда вы вернетесь к электронной таблице, условное форматирование будет применено к ячейкам с E14 по E16.

Изменить цвет шрифта на основе значения в ячейке

---

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

См. Решение в других версиях Excel:

Вопрос: В Microsoft Excel 2010 я помещаю сумму трех ячеек в четвертую. Если сумма больше 10, я бы хотел, чтобы сумма была красного цвета. Если сумма меньше 10, я бы хотел, чтобы сумма была синего цвета. Это возможно?

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

Для этого выберите ячейку, к которой вы хотите применить форматирование.В этом примере мы выбрали ячейку B8.

Выберите вкладку «Главная» на панели инструментов в верхней части экрана. Затем в группе стилей щелкните раскрывающийся список «Условное форматирование» и выберите Управление правилами .

Когда появится окно «Диспетчер правил условного форматирования», нажмите кнопку «Новое правило», чтобы ввести первое условие.

Когда появится окно «Новое правило форматирования», выберите Форматировать только ячейки, содержащие в качестве типа правила.

Затем выберите Значение ячейки в первом раскрывающемся списке, больше во втором раскрывающемся списке и введите 10 в последнем поле. В нашем примере мы выбрали, когда значение ячейки больше 10.

Далее нам нужно выбрать, какое форматирование применять при выполнении этого условия. Для этого нажмите кнопку «Форматировать».

Когда появится окно «Форматирование ячеек», выберите условия форматирования, которые вы хотите применить. Мы изменили цвет на красный.Затем нажмите кнопку ОК.

Когда вы вернетесь в окно «Новое правило форматирования», вы должны увидеть предварительный просмотр форматирования в поле «Предварительный просмотр». В этом примере в поле предварительного просмотра текст отображается красным цветом. Затем нажмите кнопку ОК.

Вы вернетесь в окно «Диспетчер правил условного форматирования».

Если вы хотите применить более одного условия, вам нужно будет снова нажать кнопку «Новое правило».

Когда появится окно «Новое правило форматирования», нам нужно настроить второе условие.

Выберите Форматировать только ячейки, содержащие в качестве типа правила.

Затем выберите Значение ячейки в первом раскрывающемся списке, меньше во втором раскрывающемся списке и введите 10 в последнем поле. В нашем примере мы выбрали, когда значение ячейки меньше 10.

Затем нам нужно выбрать, какое форматирование применять при выполнении этого второго условия. Для этого нажмите кнопку «Форматировать».

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

Когда вы вернетесь в окно «Новое правило форматирования», вы должны увидеть предварительный просмотр форматирования в поле «Предварительный просмотр». В этом примере предварительный просмотр показывает текст синим цветом. Затем нажмите кнопку ОК.

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

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

Excel: цветовая маркировка значений — стратегические финансы

На недавнем семинаре IMA ^® участник задал сложный, по его мнению, вопрос: как можно раскрасить столбец статуса пятью разными цветами? Начиная с Excel 97 и заканчивая Excel 2003, инструменты условного форматирования были ограничены тремя разными цветами.Но с момента выхода Excel 2007 у вас могут быть сотни правил условного форматирования.

СОЗДАНИЕ ПРАВИЛ

У участника был рабочий лист с кодом состояния в столбце D, который содержит возможные значения от 1 до 5. Элементы со статусом 1 должны быть зеленого цвета, 2 должны быть синими, 3 желтыми, 4 оранжевыми и 5 красными. Он также очень конкретно указывал, какие оттенки синего и зеленого необходимо использовать.

Вот шаги, чтобы получить показанные результаты:

1. Выберите ячейки с кодами состояния.

2. На вкладке «Главная» выберите «Условное форматирование», «Выделить правила для ячеек», «Равно…».

3. В диалоговом окне «Равно» введите 1 в левом поле. Откройте раскрывающийся список и выберите «Пользовательский формат…

».

4.На вкладке «Шрифт» открывается диалоговое окно «Формат ячеек». Вы можете использовать эту вкладку, чтобы изменить цвет чисел в ячейке. Но чтобы изменить цвет заливки ячейки, перейдите на вкладку «Заливка».

5. 60 доступных цветов менялись с каждой версией Excel и будут меняться в зависимости от темы, используемой для документа. Чтобы получить более точную цветовую палитру, нажмите «Другие цвета». Он предлагает 163 цвета, включая множество оттенков популярных цветов, таких как красный, оранжевый, желтый, синий и зеленый. Но если ни один из этих цветов не является точным правильным оттенком, вы можете использовать вкладку Custom, чтобы выбрать один из 16.7 миллионов цветов.

6. После выбора цвета щелкните OK в каждом из трех открытых диалоговых окон, чтобы вернуться в Excel.

7. Повторите шаги 2–6 для каждого из четырех оставшихся цветов, изменив 1 на шаге 3 на другие значения.

Чтобы просмотреть правила, перейдите на главную, Условное форматирование, Управление правилами.

ВЫДЕЛЕНИЕ ВСЕГО РЯДА

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

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

1.Выберите весь диапазон данных (A4: D27).

2. На вкладке «Главная» выберите «Условное форматирование», «Новое правило».

3. Выберите «Использовать формулу, чтобы определить, какие ячейки выделять».

4. Введите = $ D4 = 1 в качестве формулы.

5. Нажмите кнопку «Форматировать…» и выберите цвет.

6. Нажимайте ОК, пока все диалоговые окна не закроются.

7. Повторите шаги 2–6 для остальных правил, изменив 1 на шаге 4 на другие значения.

ВЫБОР ЦВЕТА НА ОСНОВЕ ДИАПАЗОНОВ

Теперь предположим, что вы хотите задать цветовой код на основе диапазонов значений. Например, значения 90–100 будут зелеными, 80–89 — оранжевыми, 70–79 — желтыми, 60–69 — розовыми, а все остальное — красными.

Лучший способ добиться этого — использовать Home, Conditional Formatting, Highlight Cells Rules, Between. Таким образом, каждое правило является взаимоисключающим. Значение не может одновременно находиться между 60–69 и 70–79.

Другой способ построения правил — определить правило для значений больше 0, чтобы они были красными. Затем определите правило, согласно которому значения больше или равные 60 должны быть розовыми. Продолжайте по порядку с каждым диапазоном, от наименьшего к наибольшему. Это очень важно, потому что каждое новое правило условного форматирования, которое вы создаете, идет вверху списка. Если последнее определенное вами правило выделено красным для значений больше нуля, оно появится в верхней части Диспетчера правил условного форматирования.И поскольку каждое значение больше нуля, все значения будут красными. Однако, если у вас уже есть правила в неправильном порядке, вы можете выбрать правило и использовать значки вверх / вниз, чтобы изменить порядок правил в списке.

БЫСТРО НАНЕСЕНИЕ ЦВЕТОВ

В

Excel 2007 появилась новая опция условного форматирования, называемая «Цветовая шкала». Эта опция избавляет от необходимости устанавливать множество правил. Выберите диапазон чисел, а затем выберите «Главная», «Условное форматирование», «Цветовая шкала».Выберите один из встроенных трехцветных вариантов. Используя цветовую шкалу, номерам присваиваются различные оттенки красного, желтого и зеленого в зависимости от выбранного числа.

Сохранить

Сохранить

Сохранить

Вам также может понравиться

Примеры условного форматирования Excel

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

Советы менеджера правил

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

• На ленте Excel щелкните вкладку Главная
• Щелкните команду условного форматирования
• Щелкните Управление правилами
• В верхней части Диспетчера правил условного форматирования выберите «Этот лист» из раскрывающегося списка.

Каждое правило перечислено и показывает его формулу, формат, диапазон, к которому оно применяется, и флажок «Остановить, если истина».

Быстрая проверка формул

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

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

Однако, если вам не нужно нажимать кнопку «Изменить правило», чтобы увидеть полную формулу.

• Чтобы увидеть полную формулу, укажите на одно из правил
• Полная формула появится во всплывающем окне.

После просмотра формулы, если вам нужно отредактировать ее, нажмите кнопку «Изменить правило» и внесите изменения.

Скрыть ошибки

Вы можете использовать условное форматирование Excel для проверки ошибок и измените цвет шрифта, чтобы он соответствовал цвету ячейки. В этом примере если столбец A содержит ноль, # DIV / 0! ошибка отображается в столбце С.

1. Выбрать ячейки C2: C5
2. На вкладке Главная ленты щелкните Условное форматирование, затем щелкните Новое правило
3. Щелкните значок Используйте формулу для определения ячеек для форматирования
   • ПРИМЕЧАНИЕ. В Excel 2003 выберите Формат | Условное форматирование
   • Затем из первого раскрывающегося списка выберите Formula Is
4. В поле правила введите формулу, которая относится к активной ячейке. в выборе.В этом примере мы выбрали C2: C5 и ячейку C2. — активная ячейка, поэтому мы проверим наличие ошибки в ячейке C2.
   = ISERROR (C2)
   или, чтобы скрыть только ошибки # N / A: = ISNA (C2)

5. Нажмите кнопку «Форматировать».
6. Выберите цвет шрифта, соответствующий цвету ячейки.
7. Нажмите ОК, нажмите ОК

Видео: Скрыть дубликат Значения

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

Скрыть повторяющиеся значения

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

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

Вы можете увидеть текст, если выделите ячейки.

Выполните следующие действия, чтобы скрыть дубликаты, и вы увидите шаги в видео выше.

1. Выбрать диапазон A2: A5
2. На вкладке Главная ленты щелкните Условное форматирование, затем щелкните Новое правило
3. Щелкните значок Используйте формулу для определения ячеек для форматирования

• ПРИМЕЧАНИЕ. В Excel 2003 выберите Формат | Условное форматирование
• Затем из первого раскрывающегося списка выберите Formula Is

В качестве формулы введите
= A2 = A1

Нажмите кнопку «Форматировать».

Выберите цвет шрифта, соответствующий цвету ячейки.

Нажмите ОК, нажмите ОК

Выделить дубликаты в столбце

Используйте условное форматирование Excel, чтобы выделить повторяющиеся записи в конкретный столбец или диапазон ячеек (несколько строк и столбцов):

В Excel 2007 или более поздней версии:

1. Выберите ячейки для форматирования — диапазон A2: A11 в этом примере
2. На вкладке «Главная» ленты щелкните «Условное форматирование».
3. Щелкните «Выделить правила ячеек», затем щелкните «Повторяющиеся значения».
4. Выберите один из вариантов форматирования и нажмите ОК.

Для Excel 2003:

1. Выберите ячейки для форматирования — диапазон A2: A11 в этом примере
2. Выберите Формат | Условное форматирование
3. В первом раскрывающемся списке выберите Formula Is
4. Для формулы введите
   = СЧЁТЕСЛИ ($ A $ 2: $ A $ 11, A2)> 1
5. Нажмите кнопку «Форматировать».
6. Выберите шрифт или цвет заливки для выделения.
7. Нажмите ОК, нажмите ОК

Видео: выделение дубликата Записи в списке

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

Вы можете увидеть шаги в этом видео и письменные инструкции находятся под видео.

Выделить повторяющиеся записи в списке

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

Создайте формулу для объединения данных:

1. В этом примере данные находятся в ячейках A2: F8
2. В ячейке G1 добавьте заголовок столбца «AllData».
3. В ячейке G2 введите формулу для объединения всех данных:
   = СЦЕПИТЬ (A2, B2, C2, D2, E2, F2)
4. Скопируйте формулу до последней строки данных

Добавьте условное форматирование:

1. Выберите ячейки для форматирования — диапазон A2: F8 в этом примере
2. На вкладке Главная ленты щелкните Условное форматирование, затем щелкните Новое правило
3. Щелкните значок Используйте формулу для определения ячеек для форматирования
   • ПРИМЕЧАНИЕ. В Excel 2003 выберите Формат | Условное форматирование
   • Затем из первого раскрывающегося списка выберите Formula Is
4. В качестве формулы введите
   = СЧЁТЕСЛИ ($ G $ 2: $ G $ 8, $ G2)> 1
   СЧЁТЕСЛИ функция будет подсчитывать вхождения объединенного текста каждой строки.Если их несколько, строка будет выделена.
   ПРИМЕЧАНИЕ : Если вы хотите выделить только повторяющиеся записи, а не первый экземпляр дублированной записи, используйте следующие формула:
   = СЧЁТЕСЛИ ($ G $ 2: $ G2, $ G2)> 1
   ПРИМЕЧАНИЕ : функция СЧЁТЕСЛИ работает только для 255 символов или меньше. Для более длинных строк используйте следующую формулу:
   = COUNT (FIND ($ G5, $ G $ 5: $ G $ 11))> 1
5. Нажмите кнопку «Форматировать».
6. Выберите цвет шрифта для выделения.
7. Нажмите ОК, нажмите ОК

Выделение ячеек с помощью формул

Используйте условное форматирование Excel, чтобы выделить ячейки, содержащие формулу. В этом примере есть значения в ячейках A2: B8 и итоги в ячейках C2: C8 и в A9: C9

.

1. Выберите все ячейки, в которых вы хотите форматировать — диапазон A2: C9
2. На вкладке Главная ленты щелкните Условное форматирование, затем щелкните Новое правило
3. Щелкните Используйте формулу для определения ячеек для форматирования
4. В качестве формулы введите: = ISFORMULA (A2)
5. Нажмите кнопку «Форматировать».
6. Выберите цвет шрифта для выделения.
7. Нажмите ОК, нажмите ОК

Выделить элементы в списке

Используйте условное форматирование Excel для выделения элементов, находящихся в список на листе.

1. Создайте список элементов, которые нужно выделить. Если предметы на листе, отличном от условного форматирования, имя список.
2. Выберите диапазон A2: A7
3. На вкладке Главная ленты щелкните Условное форматирование, затем щелкните Новое правило
4. Щелкните значок Используйте формулу для определения ячеек для форматирования
   • ПРИМЕЧАНИЕ. В Excel 2003 выберите Формат | Условное форматирование
   • Затем из первого раскрывающегося списка выберите Formula Is
5. Для формулы введите
   = СЧЁТЕСЛИ ($ C $ 2: $ C $ 4, A2)
   или, если список имеет имя, используйте имя в формуле:
   = СЧЁТЕСЛИ (CodeList, A2)
   
6. Нажмите кнопку «Форматировать».
7. Выберите цвет шрифта для выделения.
8. Нажмите ОК, нажмите ОК

Видео: Выделите лотерею Числа

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

Выделите номера лотереи

Вы можете использовать условное форматирование Excel, чтобы выделить билет номера, которые были разыграны в лотерее, или билеты с 3 или более выигрышных оцепенения. В этом примере номера билетов указаны в ячейки C6: H8, а выпавшие числа заносятся в ячейки C3: h4.

Для выделения выигрышных номеров:

1. Выбрать ячейки C6: H8
2. На вкладке Главная ленты щелкните Условное форматирование, затем щелкните Новое правило
3. Щелкните значок Используйте формулу для определения ячеек для форматирования
4. Для формулы используйте функцию CountIf:
   = COUNTIF ($ C $ 3: $ H $ 3, C6)> = 1
5. Нажмите кнопку «Форматировать».
6. Выберите параметры форматирования (в данном примере — зеленая заливка), нажмите ОК
7. Нажмите ОК

Чтобы выделить выигрышные билеты:

1. Выбрать ячейки B6: B8
2. На вкладке Главная ленты щелкните Условное форматирование, затем щелкните Новое правило
3. Щелкните значок Используйте формулу для определения ячеек для форматирования
4. Для формулы используйте функции СУММ и СЧЁТЕСЛИ:
   = СУММ (СЧЁТЕСЛИ ($ C6: $ H6, $ C $ 3: $ H $ 3))> = 3
5. Нажмите кнопку «Форматировать».
6. Выберите параметры форматирования (в данном примере желтая заливка), щелкните ОК
7. Нажмите ОК

Выделить верхние или нижние значения

В этих двух примерах посмотрите, как выделить:

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

В этом видео показаны шаги, а письменные инструкции приведены под видео.

Первые 3 значения

В этом примере мы выделим 3 самых высоких числа в списке ежемесячных продаж.

Правило условного форматирования будет использовать функцию НАИБОЛЬШИЙ, которая имеет 2 аргумента:

• список номеров для проверки — $ 2 канадских доллара: 13 канадских долларов
• n-е наибольшее число для возврата — 3

Правило сравнивает каждое число в списке, чтобы увидеть, больше ли оно или равно этому n-му числу

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

1. Выберите числа в столбце Продажи (C2: C13)
2. На вкладке Главная ленты щелкните Условное форматирование, затем щелкните Новое правило
3. Щелкните Используйте формулу для определения ячеек для форматирования
4. В поле формулы введите формулу НАИБОЛЬШИЙ,
   = C2> = БОЛЬШОЙ ($ C $ 2: $ C $ 13,3)
5. Нажмите кнопку «Форматировать».
6. Выбрать параметры форматирования (светло-зеленая заливка), нажмите ОК
7. Нажмите ОК

Нижние значения X

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

Сначала установите номер ячейки:

• В ячейке E1 введите заголовок: Сколько?
• В ячейке E2 введите число 2 — которое можно изменить в любое время.
• Поместите границу вокруг 2 ячеек, отцентрируйте по горизонтали и добавьте желтый цвет заливки в E2

СОВЕТ: позже вы можете ввести ноль, чтобы временно убрать выделение

Чтобы выделить нижние значения, правило условного форматирования будет использовать функцию МАЛЕНЬКИЙ, которая имеет 2 аргумента:

• список номеров для проверки — $ 2 канадских доллара: 13 канадских долларов
• n-е наименьшее число для возврата — ссылка на ячейку $ E $ 2

Правило сравнивает каждое число в списке, чтобы увидеть, меньше ли оно или равно этому n-му числу

• C2 <= МАЛЕНЬКИЙ (2 доллара США: 13 канадских долларов, 2 доллара США )

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

1. Выберите числа в столбце Продажи (C2: C13)
2. На вкладке Главная ленты щелкните Условное форматирование, затем щелкните Новое правило
3. Щелкните Используйте формулу для определения ячеек для форматирования
4. В поле формулы введите формулу МАЛЕНЬКИЙ ,
   = C2 <= МАЛЕНЬКИЙ ($ C $ 2: $ C $ 13, $ E $ 2)
5. Нажмите кнопку «Форматировать».
6. Выберите параметры форматирования (светло-оранжевая заливка), нажмите ОК
7. Нажмите ОК

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

• Введите другое число в ячейку E2, чтобы изменить количество выделенных ячеек.
• Удалите число в ячейке E2 или введите ноль, чтобы ни одно из чисел не выделялось

Выделить метеоданные

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

В таблице журнала погоды,

• В столбце Температура используется цветовая шкала красный-белый-синий.
• В столбце погоды установлено 4 правила, чтобы раскрашивать ячейки в зависимости от типа погоды — Солнце, Облако, Дождь или Снег.

На другом листе есть две именованные таблицы Excel — одна для типов погоды, а другая для описания погоды.

Также есть 3 именованных диапазона, показанных на снимке экрана под

.

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

Именованные диапазоны также используются в формулах ИНДЕКС / ПОИСКПОЗ в правилах условного форматирования. На этом снимке экрана показана формула в ячейках листа, где она использовалась для проверки формулы перед созданием правила.

Вот 5 правил, перечисленных в диспетчере правил.

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

Показать температуры С цветовой шкалой

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

Отображение температуры с помощью цветовой шкалы

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

1. Введите температуру в ячейку B3
2. В ячейках F6: F25 введите числа от 140 до -50 с шагом 10
3. В ячейке G6 введите формулу, которая покажет пустую строку, если текущая температура равна температуре в этой строке, или между этой температурой и указанной выше:

   = ЕСЛИ ($ B $ 3 = F6, «», ЕСЛИ (И ($ B $ 3> F6, $ B $ 3

4. Скопируйте формулу в строку 25
5. Выберите ячейку B3, затем нажмите клавишу Ctrl и выберите ячейки G6: G25. (ячейки с формулами)
6. На вкладке «Главная» ленты щелкните «Условное форматирование».
7. Щелкните Цветовые шкалы, затем щелкните шкалу Красный — Белый — Синий
8. Измените температуру в ячейке B3, и цвет ячейки изменится, исходя из цветовой гаммы.
9. (необязательно) Скрыть столбцы F: G, чтобы цветовая шкала не была видна.

Отметьте предстоящие даты истечения срока действия

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

1. Выбрать ячейки A2: A4
2. На вкладке Главная ленты щелкните Условное форматирование, затем щелкните Новое правило
3. Щелкните значок Используйте формулу для определения ячеек для форматирования
   • ПРИМЕЧАНИЕ. В Excel 2003 выберите Формат | Условное форматирование
   • Затем из первого раскрывающегося списка выберите Formula Is
4. Для формулы используйте функцию Today для подсчета дней:
   = AND (A2-TODAY ()> = 0, A2-TODAY () <= 30)
5. Нажмите кнопку «Форматировать».
6. Выберите параметры форматирования (в данном примере полужирный, синий шрифт), нажмите ОК
7. Нажмите ОК

Выделите истекшие даты

Условное форматирование Excel можно использовать для выделения политик с помощью даты, срок действия которых истек. В этом примере сроки сдачи вводятся в ячейки B2: B7.

1. Выбрать ячейки B2: B7
2. На вкладке Главная ленты щелкните Условное форматирование, затем щелкните Новое правило
3. Щелкните значок Используйте формулу для определения ячеек для форматирования
   • ПРИМЕЧАНИЕ. В Excel 2003 выберите Формат | Условное форматирование
   • Затем из первого раскрывающегося списка выберите Formula Is
4. Для формулы используйте функцию Сегодня, чтобы найти истекшие даты:
   = B2 <СЕГОДНЯ ()
5. Нажмите кнопку «Форматировать».
6. Выберите параметры форматирования (в данном примере — красный цвет заливки), нажмите ОК
7. Нажмите ОК

Основные даты выходных

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

Вы можете увидеть шаги в этом видео.

Скрыть содержимое ячейки при печати

Вы можете использовать условное форматирование Excel, чтобы скрыть ячейки при печати. В этом примере содержимое ячеек B2: F4 изменено на белый цвет. font, если ячейка h2 содержит x.

Для печати со скрытым содержимым ячейки введите x в ячейку h2. К отобразите содержимое ячейки, удалите x в ячейке h2.

1. Выбрать ячейки B2: F4
2. На вкладке Главная ленты щелкните Условное форматирование, затем щелкните Новое правило
3. Щелкните значок Используйте формулу для определения ячеек для форматирования
   • ПРИМЕЧАНИЕ. В Excel 2003 выберите Формат | Условное форматирование
   • Затем из первого раскрывающегося списка выберите Formula Is
4. В качестве формулы введите = $ H $ 1 = «x»
5. Нажмите кнопку «Форматировать».
6. Выберите параметры форматирования (белый шрифт и белый узор в этом пример)
7. Нажмите ОК, нажмите ОК

Скрыть дополнительные вопросы

В этом примере есть краткая анкетаi, а некоторые вопросы содержат дополнительный элемент. На основании первого ответа может появиться дополнительный вопрос.

Сначала видны только основные вопросы. Последующие элементы выделены белым шрифтом с белой заливкой.

Например, «Есть ли у вас иждивенцы?»

• Если выбрать Нет, ничего не произойдет.
• Если вы выберете Да, появится следующий вопрос.

Для настройки:

• Последующие ячейки имеют простые условные правила форматирования, например: = E2 = «Да»
• При условном форматировании добавляются черный шрифт, цвет заливки и граница.

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

Вычеркнуть завершенные позиции

Если у вас есть список задач, над которыми вы должны работать, используйте условное форматирование, чтобы вычеркнуть завершенные элементы. В этом примере завершенные задачи отмечены знаком X в столбце «Готово».

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

Чтобы настроить это условное форматирование, выполните следующие действия:

1. Выбрать ячейки A2: C4
2. На вкладке Главная ленты щелкните Условное форматирование, затем щелкните Новое правило
3. Щелкните Используйте формулу для определения ячеек для форматирования
4. В качестве формулы введите = $ C2 <> «»
.

• В каждой ячейке формула проверяет, пуст ли столбец C в этой строке

Нажмите кнопку «Форматировать».

На вкладке Шрифт выберите Зачеркнутый и выберите светло-серый цвет в качестве цвета

Нажмите ОК, нажмите ОК

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

Этот пример находится на зачеркнутом листе в файле примера №1.

Цветных ячеек на основе 2 условий

Используйте условное форматирование Excel для раскрашивания ячеек, если выполняются 2 условия.В этом примере код страны вводится в ячейку B2. Если введен код «США», ячейки, содержащие «США», будут окрашены в красный цвет.

Введите условия

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

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

.

Для настройки условий:

• В ячейке E1 введите Cond01
• В ячейке B2 введите US .
• В ячейке D1 введите Cond02
• В ячейке D2 введите США

Добавьте ячейку кода страны

Затем настройте ячейку, в которой можно ввести код страны:

• В ячейке B1 введите Код
• В ячейке B2 введите US .(Это может быть изменено позже)
• Отформатируйте ячейку B2 желтым цветом, чтобы показать, что это ячейка ввода данных

Добавить условное форматирование

Затем добавьте условное форматирование к ячейкам страны в диапазоне данных. Формула объясняется ниже.

1. Выберите ячейки D5: D14, в которых указаны названия стран для заказов
2. На вкладке Главная ленты щелкните Условное форматирование, затем щелкните Новое правило
3. Щелкните Используйте формулу для определения ячеек для форматирования
4. Для формулы введите = И ($ B $ 2 = $ E $ 2, D5 = $ F $ 2)
5. Нажмите кнопку «Форматировать».
6. Выберите параметры форматирования (красный цвет заливки, в этом пример)
8. Нажмите ОК, нажмите ОК

Если в ячейку B2 введено США, а ячейка в D5: D14 содержит «Соединенные Штаты», она окрашивается в красный цвет.

Как это работает

Формула условного форматирования: = И ($ B $ 2 = $ E $ 2, D5 = $ F $ 2)

Функция И проверяет 2 условия:

1. Соответствует ли ячейка B2 условию, указанному в ячейке E2
2. Соответствует ли ячейка ввода данных условию, указанному в ячейке F2

Некоторые примечания о ссылках на ячейки в формуле:

• Ячейка D5 используется в формуле, потому что это была активная ячейка, когда применялось условное форматирование.
• Относительная ссылка используется для ячейки ввода данных (D5), потому что она должна соответствовать каждой ячейке, к которой применяется условное форматирование.
• Абсолютные ссылки используются для $ B $ 2, $ E $ 2 и $ F $ 2, потому что независимо от того, где применяется условное форматирование, он всегда должен проверять эти ячейки.

Чередование рядов оттенков

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

1. Нажмите кнопку «Выбрать все» над кнопкой «Строка 1», чтобы выбрать все ячейки на листе.
2. На вкладке Главная ленты щелкните Условное форматирование, затем щелкните Новое правило
3. Щелкните значок Используйте формулу для определения ячеек для форматирования
   • ПРИМЕЧАНИЕ. В Excel 2003 выберите Формат | Условное форматирование
   • Затем из первого раскрывающегося списка выберите Formula Is
4. В качестве формулы введите = MOD (СТРОКА (), 2)
5. Нажмите кнопку «Форматировать».
6. На вкладке «Узоры» выберите цвет для штриховки
7. Нажмите ОК, нажмите ОК

Растушевка рядов

Вы можете использовать условное форматирование Excel, чтобы затенять полосы строк на рабочий лист. В этом примере 3 ряда заштрихованы светло-серым цветом, а 3 остались без штриховки. В функции MOD общее количество рядов в набор полосатых рядов (6).

1. Нажмите кнопку «Выбрать все» над кнопкой «Строка 1», чтобы выбрать все ячейки на листе.
2. На вкладке Главная ленты щелкните Условное форматирование, затем щелкните Новое правило
3. Щелкните значок Используйте формулу для определения ячеек для форматирования
   • ПРИМЕЧАНИЕ. В Excel 2003 выберите Формат | Условное форматирование
   • Затем из первого раскрывающегося списка выберите Formula Is
4. В качестве формулы введите = MOD (СТРОКА (), 6) <3
5. Нажмите кнопку «Форматировать».
6. На вкладке «Узоры» выберите цвет для штриховки
7. Нажмите ОК, нажмите ОК

Цветовые полосы по группам

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

Во-первых, чтобы подготовить таблицу для групп оттенков, выполните следующие действия:

• В ячейке D1 введите заголовок для нового столбца — ИСТИНА
• В ячейке D2 введите эту формулу, которая возвращает ИСТИНА или ЛОЖЬ.

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

• Выбрать все ячейки данных в таблице
• На вкладке Главная щелкните Условное форматирование, Новое правило
• Нажмите «Использовать формулу, чтобы определить, какие ячейки нужно форматировать».
• В поле формулы введите эту формулу, ссылаясь на активную ячейку данных: = $ D2 = TRUE
• Нажмите кнопку «Формат» и выберите цвет заливки.
• Дважды щелкните OK, чтобы применить форматирование.
• (Необязательно) Скройте столбец ИСТИНА / ЛОЖЬ, чтобы привести в порядок рабочий лист.

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

Оттенок с чередованием фильтрованных рядов

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

1. Выберите ячейки в списке (A2: B29 в этом примере).
2. На вкладке Главная ленты щелкните Условное форматирование, затем щелкните Новое правило
3. Щелкните значок Используйте формулу для определения ячеек для форматирования
   • ПРИМЕЧАНИЕ. В Excel 2003 выберите Формат | Условное форматирование
   • Затем из первого раскрывающегося списка выберите Formula Is
4. В качестве формулы введите = МОД (ПРОМЕЖУТОЧНЫЙ ИТОГ (3; $ A $ 1: $ A2), 2)
5. Нажмите кнопку «Форматировать».
6. На вкладке «Узоры» выберите цвет для штриховки
7. Нажмите ОК, нажмите ОК
8. Отфильтруйте список, и затемнение будет чередоваться в видимом ряды.

Создание цветных фигур

Вы можете использовать условное форматирование Excel и шрифт Wingding для создавать цветные фигуры в ячейке. В этом примере цветные фигуры появится в ячейках C3: C7, в зависимости от значения в соседнем ячейка в столбце B.

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

1. В ячейке C3 введите формулу:
   = IF (B3 = «», «», IF (B3 <10, "l", IF (B3> 30, «n», «t»)))
2. Скопируйте формулу в ячейку C7
3. Отформатируйте ячейки C3: C7 шрифтом Wingding и желтым цветом шрифта
4. Выбрать ячейки C3: C7
5. На вкладке Главная ленты щелкните Условное форматирование, затем щелкните Новое правило
6. Щелкните значок Используйте формулу для определения ячеек для форматирования
   • ПРИМЕЧАНИЕ. В Excel 2003 выберите Формат | Условное форматирование
   • Затем из первого раскрывающегося списка выберите Formula Is
7. В качестве формулы введите: = $ B3 <10
8. Нажмите кнопку «Формат» и выберите «Красный» в качестве цвета шрифта, затем нажмите ОК.
9. Щелкните Новое правило, затем щелкните Используйте формулу для определения Ячейки в формат
10. В качестве формулы введите: = $ B3> 30
11. Нажмите кнопку «Формат» и выберите «Зеленый» в качестве цвета шрифта, затем щелкните ОК.
12. Нажмите ОК

Создание цветных значков

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

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

Или вы можете использовать собственные числовые форматы, как показано в образце файла, на листе ColorIconsNum.

Сначала настройте таблицу поиска в ячейках G3: I5

1. В G3: G5 введите проценты: 67%, 33%, 0%
2. В h4 введите формулу: = ПРОЦЕНТИЛЬ ($ D $ 2: $ D $ 11, G3)
3. Скопируйте формулу в строки 4 и 5
4. Форматировать ячейки h4: H5 шрифтом Wingding3
5. Используйте функцию Excel «Вставить символы», чтобы сложить верхнюю, правую и нижнюю стрелки в этих ячейках из шрифта Wingdings 3.

• ПРИМЕЧАНИЕ. Вы можете использовать разные проценты или просто вводить значения в h4: H5

Затем создайте значки в столбце C:

1. В ячейке C2 введите формулу, которая создает значок:
   = IF (D2 = «», «», IF (D2> = $ H $ 3, $ I $ 3, IF (D2> = $ H $ 4, $ I $ 4) , $ I $ 5)))
2. Скопируйте формулу в ячейку C11
3. Форматировать ячейки C2: C11 шрифтом Wingding3 и желтым цветом шрифта
4. Выбрать ячейки C2: C11
5. На вкладке Главная ленты щелкните Условное форматирование, затем щелкните Новое правило
6. Щелкните значок Используйте формулу для определения ячеек для форматирования
7. В качестве формулы введите: = $ B2 <$ H $ 4
8. Нажмите кнопку «Формат» и выберите «Зеленый» в качестве цвета шрифта, затем щелкните ОК.
9. Щелкните Новое правило, затем щелкните Используйте формулу для определения Ячейки в формат
10. В качестве формулы введите: = $ B2> $ H $ 3
11. Нажмите кнопку «Формат» и выберите «Красный» в качестве цвета шрифта, затем нажмите ОК.
12. Нажмите ОК
13. Чтобы значки отображались в той же ячейке, что и номер, вы можете создать внешнюю границу вокруг двух ячеек.

Получить файл образца

1. Примеры условного форматирования : Для Excel 2007 и более поздних версий: заархивированный образец Условное форматирование для Excel Форматирование файла
2. Примеры условного форматирования 2003 : Для Excel 2003 — заархивированный образец Условное форматирование для Excel Форматирование файла
3. Предупреждение о скрытых данных : В этом образце файла отображаются предупреждения, если строки или столбцы скрыты.Формула проверяет наличие скрытых строк, а условное форматирование отмечает скрытый столбец. Заархивированный файл не содержит макросов.
4. Условное форматирование данных о погоде : В этом примере файла используется цветовая шкала для температур и 4 правила формул для погодных условий — Солнце, Облако, Дождь и Снег. Заархивированный файл имеет формат xlsx и не содержит макросов
5. Скрытые вопросы : основные вопросы видны, а условное форматирование скрывает дополнительные вопросы.Заархивированный файл имеет формат xlsx и не содержит макросов

Дополнительные уроки

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

Условное форматирование нескольких ячеек

Условное форматирование — валюта

Документация по условному форматированию

Строки данных условного форматирования

Не пропустите наши советы по Excel

Не пропустите мои последние советы и видео по Excel! Щелкните OK, чтобы получать мой еженедельный информационный бюллетень с советами по Excel и ссылками на другие новости и ресурсы Excel.

Leave a comment Cancel reply