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

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

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

Решение: Найти все ячейки с определённым значением (или значениями) при помощи инструмента 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 (Формат ячеек), как это описано в разделе Изменение заливки выделенных ячеек.

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

Урок подготовлен для Вас командой сайта office-guru.ru
Источник: https://www.ablebits.com/office-addins-blog/2013/10/18/change-background-color-excel-based-on-cell-value/
Перевел: Антон Андронов
Правила перепечатки
Еще больше уроков по Microsoft Excel

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

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

Excel для Microsoft 365 Excel для Интернета Excel 2021 Excel 2019 Excel 2016 Excel 2013 Excel 2010 Excel Starter 2010 Дополнительно. ..Меньше

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

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

   Советы: 

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

2. Нажмите Главная > стрелку рядом с Цвет заливки или нажмите Alt+H, H.

3. В разделе Тематические цвета или Стандартные цвета выберите нужный цвет.

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

   Совет: Чтобы применить последний выбранный цвет, просто нажмите Цвет заливки . Вы также найдете до 10 последних выбранных пользовательских цветов в разделе Recent Colors .

Применение узора или эффектов заливки

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

org/ItemList»>

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

2. Нажмите Главная > Формат ячеек диалоговое окно запуска или нажмите Ctrl+Shift+F.

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

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

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

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

Удалить цвета ячеек, узоры или эффекты заливки

Чтобы удалить любые фоновые цвета, узоры или эффекты заливки из ячеек, просто выделите ячейки. Затем щелкните Главная > стрелку рядом с Цвет заливки , а затем выберите No Fill .

Печать цветов ячеек, узоров или эффектов заливки цветом

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

1. Щелкните Макет страницы > Параметры страницы Запуск диалогового окна.

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

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

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

org/ItemList»>

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

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

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

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

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

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

Нужна дополнительная помощь?

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

См. также

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

Применение или удаление затенения ячеек

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

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

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

2. На вкладке Home в группе Font выполните одно из следующих действий:

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

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

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

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

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

Заполнить ячейки узорами

org/ItemList»>

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

2. На вкладке Home в группе Font нажмите кнопку Формат ячеек Запуск диалогового окна.

   Сочетание клавиш  Можно также нажать CTRL+SHIFT+F.

3. В диалоговом окне Формат ячеек на вкладке Заливка в разделе Цвет фона щелкните цвет фона, который вы хотите использовать.

4. org/ListItem»>

   Выполните одно из следующих действий:

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

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

Проверка параметров печати для печати заливки ячеек в цвете

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

1. На вкладке Макет страницы в группе Параметры страницы щелкните кнопку запуска диалогового окна Параметры страницы .

2. На вкладке Лист в разделе Печать убедитесь, что флажки Черно-белое и Черновое качество сняты.

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

Удалить затенение ячеек

org/ItemList»>

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

2. На вкладке Главная в группе Шрифт щелкните стрелку рядом с Цвет заливки и выберите Нет заливки .

Установить цвет заливки по умолчанию для всех ячеек на листе

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

1. Создайте новый пустой рабочий лист.

2. Нажмите кнопку Select All , чтобы выбрать весь рабочий лист.

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

   Совет     При изменении цвета заливки ячеек на листе линии сетки могут быть плохо видны. Чтобы линии сетки выделялись на экране, можно поэкспериментировать со стилями границ и линий. Эти настройки находятся на вкладке Home , в группе Font . Чтобы применить границы к рабочему листу, выберите весь рабочий лист, щелкните стрелку рядом с Borders , а затем щелкните All Borders .

4. На вкладке Файл щелкните Сохранить как .

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

6. В поле Сохранить как тип щелкните Шаблон Excel , щелкните Сохранить , а затем закройте лист.

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