Меню

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

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

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

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

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

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

tsvet stroki excel znachenie yacheiki 1

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

tsvet stroki excel znachenie yacheiki 3

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

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

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

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

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

tsvet stroki excel znachenie yacheiki 8

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

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

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

С формулой для значений 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

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

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

tsvet stroki excel znachenie yacheiki 9

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

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

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

Читайте также:  Как сделать ту на электричество на земельный участок

tsvet stroki excel znachenie yacheiki 10

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

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

tsvet stroki excel znachenie yacheiki 11

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

tsvet stroki excel znachenie yacheiki 12

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

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

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

Источник

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

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

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

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

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

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

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

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

izmenit tsvet zalivki excel 1

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

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

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

izmenit tsvet zalivki excel 6

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

izmenit tsvet zalivki excel 7

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

izmenit tsvet zalivki excel 8

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

izmenit tsvet zalivki excel 9

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

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

Читайте также:  Как сделать на айфоне чтобы показывала батарейка в процентах

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

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

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

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

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

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

izmenit tsvet zalivki excel 10

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

izmenit tsvet zalivki excel 11

Совет: В правой части диалогового окна Find and Replace (Найти и заменить) есть кнопка Options (Параметры), нажав которую Вы получите доступ к ряду продвинутых настроек поиска, таких как Match Case (Учитывать регистр) и Match entire cell content (Ячейка целиком). Вы можете использовать символы подстановки, такие как звёздочка (*), чтобы найти любую строку символов, или знак вопроса (?), чтобы найти один любой символ.

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

izmenit tsvet zalivki excel 12

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

izmenit tsvet zalivki excel 13

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

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

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

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

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

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

izmenit tsvet zalivki excel 14

Если Вы хотите изменить только цвет заливки, не касаясь остальных параметров форматирования, то можете просто нажать кнопку Fill color (Цвет заливки) и выбрать понравившийся цвет.

izmenit tsvet zalivki excel 15

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

izmenit tsvet zalivki excel 16

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

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

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

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

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

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

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

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

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

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

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

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

Читайте также:  Как сделать покрытие гель лаком близко к кутикуле

Источник

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

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

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

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

Izmenenie cveta yachejki v zavisimosti ot znacheniya Excel 1

Как выбрать функцию, изменяющую цвет ячейки согласно значению

Для выбора функции заливки нужно определить тип таблицы и ее содержание:

Определившись с типом таблицы, выбираем функцию:

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

Справка! В статье мы работаем в Эксель версии 2016. Для версий 2007, 2010 и 2013 описываемые действия будут аналогичными, возможно незначительное отличие в названиях опций.

Работа с функцией «Условное форматирование»

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

Tablica v kotoroj primenim nastrojki funkcii Uslovnoe formatirovanie

Закрасим ячейки с цифрами:

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

Vydelim s pomoshhju levoj knopki myshki yachejki s dannymi

Шаг 2. Во вкладке «Главная» ищем группу «Стили».

Vo vkladke Glavnaya nahodim gruppu Stili

Шаг 3. Нажимаем левой кнопкой мышки по иконке «Условное форматирование».

Nazhimaem levoj knopkoj myshki po ikonke Uslovnoe formatirovanie

Шаг 4. В раскрывшемся меню щелкаем по строке с названием «Создать правило».

Shhelkaem po stroke s nazvaniem Sozdat pravilo

Шаг 5. Выделим левым кликом мышки функцию «Форматировать только ячейки…».

Vydelim levym klikom myshki funkciju Formatirovat tolko yachejki.%20

Шаг 6. В части окна под названием «Измените описание правила» выставляем нужные параметры, согласно описанным выше условиям:

Grafu Znachenie yachejki ostavlyaem po umolchaniju

Klikaem po strelochke vniz vybiraem variant bolshe ili ravno

V poslednej grafe vvodim cifru 39

Шаг 7. Нажимаем по кнопке «Формат».

Nazhimaem po knopke Format

Шаг 8. В открытом окне «Формат ячеек» активируем щелчком мышки вкладку «Заливка», если она не открылась по умолчанию.

Aktiviruem shhelchkom myshki vkladku Zalivka

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

Levoj knopkoj myshki nazhimaem po podhodyashhemu cvetu v palitre

Шаг 10. Если цвет устраивает, кликаем по «ОК».

Klikaem po OK

Шаг 11. Проверяем выставленные параметры, кликаем по «ОК», чтобы настройки отобразились в таблице.

Proveryaem vystavlennye parametry klikaem po OK

Шаг 12. Создаем другое правило, согласно второму условию, перейдя в «Условное форматирование».

Klikaem po ikonke Uslovnoe formatirovanie zatem po stroke s nazvaniem Sozdat pravilo

Шаг 13. Выбираем второй тип правила.

Vybiraem vtoroj tip pravila

Шаг 14. В графе «между» выставляем «меньше или равно», вводим цифру 36,5.

V grafe mezhdu vystavlyaem menshe ili ravno vvodim cifru 365

Шаг 15. Жмем по кнопке «Формат».

Zhmem po knopke Format

Шаг 16. Выбираем зеленый цвет, кликаем по «ОК».

Vybiraem zelenyj cvet klikaem OK

Шаг 17. Проверяем все настройки, жмем по «ОК».

Proveryaem vse nastrojki zhmem po OK

Таким образом мы создали в одной таблице два правила с разной заливкой.

Tablica s sozdannymi dvumya pravila raznoj zalivki

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

Pri korrektirovke dannyh v yachejkah avtomaticheski izmenitsya i ih cvet

Применим функцию заливки одним цветом разных оттенков.

Vydelim s pomoshhju levoj knopki myshki yachejki s dannymi

Raskroem menju funkcii Uslovnoe formatirovanie shhelknem Sozdat pravilo

Ostavlyaem po umolchaniju vydelennym parametr Formatirovat vse yachejki.%20

V grafe Cvet vybiraem podhodyashhij cvet kotoryj budet okrashivat vse yachejki s dannymi

Nazhimaem OK

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

Yachejki v tablice okrasheny v odin cvet tolko raznogo ottenka v zavisimosti ot znacheniya

Работа с функцией «Найти и выделить»

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

Vydelim yachejki s dohodom 1

Nahodim gruppu Redaktirovanie v glavnoj vkladke shhelkaem levoj knopkoj na ikonke Najti i vydelit

Klikaem po stroke s nazvaniem Najti

Pechataem v pole Najti kombinaciju 3 klikaem po opcii Najti vse

Vnizu okna shhelkaem po ljubomu znacheniju levoj klavishej myshki

Zazhimaem odnovremenno na klaviature knopki CtrlA dlya vydeleniya vseh strok v bloke najdennyh rezultatov

Peremeshhaemsya k gruppe instrumentov Shrift nahodim ikonku Cvet zalivki i shhelkaem v nej po malenkomu treugolniku levoj knopkoj myshki

Opredelyaemsya s cvetom i klikaem po ego znachku v palitre

Pechataem v stroke Najti kombinaciju 4 nazhimaem Najti vse

Pechataem kombinaciju 5 nazhimaem Najti vse vydelyaem vse stroki vybiraem cvet

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

В итоге данные в таблице окрашены в разные цвета согласно значениям.

Yachejki so znacheniem okrasheny v raznye cveta

Инструкция по закрашиванию пустых ячеек и с ошибками

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

Vydelyaem vse stroki s dannymi

Otkryvaem menju Uslovnoe formatirovanie zhmem po stroke s nazvaniem Sozdat pravilo

Vydelyaem levym klikom myshki Formatirovat tolko yachejki.%20

Raskryvaem spisok shhelknuv po strelochke vniz v grafe s nazvaniem Znachenie yachejki vybiraem variant Pustye

Zhmem na knopku Format

Opredelyaemsya s cvetom nazhimaem po OK

Shhelkaem po OK

Пустые ячейки закрасятся в обозначенный цвет.

Pustye yachejki okrasilis v oboznachennyj cvet

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

Vybiraem Oshibki opredelyaemsya s cvetom v bloke Format nazhimaem po funkcii OK

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

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

Nazhimaem levoj knopkoj myshki po ikonke Uslovnoe formatirovanie

Shhelkaem levoj knopkoj myshki po stroke s nazvaniem Upravlenie pravilami

Shhelkaem po pravilu kotoroe nuzhno otkorrektirovat levoj knopkoj myshki zatem po funkcii Izmenit pravilo

Menyaem znacheniya cvet po neobhodimosti v bloke Format nazhimaem OK

Klikaem po opciyam Primenit i OK

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

V okne Dispetcher pravil uslovnogo formatirovaniya mozhno udalit sozdannoe pravilo ili dobavit novoe shhelknuv po sootvetstvujushhim knopkam 1

Заключение

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

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

Понравилась статья?
Сохраните, чтобы не потерять!

Источник

Adblock
detector