Как определить цвет заливки ячейки. Заливка ячеек в зависимости от значения в Microsoft Excel Как привязать цвет ячейки к данным excel
Для того чтобы в документе, сделанном в MS Excel, правильно расставить акценты, можно увеличить размеры ячеек, выбрать другой шрифт или размер букв, а можно закрасить квадратики, на которые точно нужно обратить внимание.
Вот сейчас мы разберемся, как выделять ячейки цветом в Экселе, или изменить цвет тех, которые уже закрашены. Как сделать так, чтобы ячейка изменяла цвет по заданному условию, в зависимости от вписанного в нее значения, и как работать с созданными правилами.
Простая заливка блока
Закрасить один или несколько блоков в Экселе не сложно. Сначала выделите их и на вкладке «Главная» нажмите на стрелку возле ведерка с краской, чтобы развернуть список. Выберите оттуда подходящий цвет, а если ничего не подойдет, нажимайте «Другие цвета» .
Кстати, таким образом можно залить целую строку, просто предварительно кликните по ее номеру, чтобы выделить. Про выделение ячеек в Экселе можете прочесть отдельную статью.
Если же Вы работаете с таблицей, в которой уже что-то закрашено, тогда поменять цвет блоков, или вовсе его убрать можно так. Нажмите по нему и из цветов или выберите новый, или кликните по варианту «Нет» .
В зависимости от введенных данных
Теперь рассмотрим, как сделать так, чтобы ячейка меняла цвет исходя из заданного условия. Используется для этого условное форматирование , про которое на сайте есть отдельная статья.
Текстовых
Возьмем для примера следующую таблицу. Давайте в ней сделаем так, чтобы красный соответствовал яблоку, желтый – груше, а оранжевый – апельсину.
Выделяем данные, с которыми будем работать, в моем случае – это названия фруктов. Потом нажимаем «Условное форматирование» , которое будем постоянно использовать дальше. Из списка кликните по «Создать правило» .
Открывается вот такое окно. Вверху выбираем тип – «Форматировать только ячейки, которые содержат» , дальше тоже будем отмечать именно его. Чуть ниже указываем условия: у нас текст, который содержит определенные слова. В последнем поле или нажмите на кнопку и укажите ячейку, или впишите текст.
Отличие в том, что поставив ссылку на ячейку (=$B$4 ), условие будет меняться в зависимости от того, что в ней набрано. Например, вместо яблока в В4 укажу смородину, соответственно поменяется правило, и будут закрашены блоки с таким же текстом. А если именно в поле вписать яблоко, то искаться будет конкретно это слово, и оно ни от чего зависеть не будет.
Здесь выберите цвет заливки и нажмите «ОК» . Для просмотра всех вариантов кликните по кнопке «Другие» .
Правило создано и сохраняем его, нажатием кнопки «ОК» .
В результате, все блоки, в которых был указанный текст, закрасились в красный.
Также создаются правила и для остальных фруктов, выбирается только другой вариант заливки.
Числовых
Теперь разберемся с числовыми данными. Давайте числа в столбце D разместим на определенный фон по тому условию, которое зададим.
Выделяем столбец, создаем правило, указываем его тип. Дальше прописываем – «Значение» «больше» «15» . Последнее число можете или ввести вручную, или указать адрес ячейки, откуда будут браться данные. Определяемся с заливкой, жмем «ОК» .
Блоки, где введены числа больше выбранного, закрасились.
Давайте для выделенных ячеек укажем еще правила – выберите «Управление правилами» .
Здесь все выбирайте, как я описывала выше, только нужно изменить цвет и поставить условие «меньше или равно» .
Когда все будет готово, нажимайте «Применить» и «ОК» .
Все работает, значения равные и ниже 15 закрашены бледно голубым.
Здесь хорошо была бы видна и разница между завезенным и проданным товаром. Выделяем два столбца и нажимаем «Правила выделения ячеек» – «Повторяющиеся значения» . Выберите подходящий цвет. После этого, если две ячейки рядом будут цветные – значит, те фрукты, которые завезли, все продали.
Давайте на примере последнего столбца покажу, как сделать, чтобы ячейка меняла цвет в зависимости от указанного значения. Выделяем их и заходим в «Управление правилами» .
Создаем новое для текущего фрагмента, если нужно в данном выпадающем списке можно выбрать или для всего листа целиком, или для других листов.
Выбираем нужные пункты в открывшемся окошке. Я залью темно зеленым все значения, что больше 90. Поскольку в последнем поле я указала адрес (=$F$15 ), то при изменении в ячейке числа 90, например, на 110, правило также поменяется. Сохраните изменения, кликнув по кнопке «ОК» .
Создам еще одно правило, но в нем подсвечу светло зеленым все, что меньше или равняется 90. Не забывайте все сохранять, нажав на кнопочки, справа внизу.
Вот, что получилось. Из последнего столбца, можно визуально быстро определить прибыль от продажи какого товара была больше.
Например, если изменится цена, то будут пересмотрены и правила. Если значение станет больше или меньше заданного, то и цвет ячейки автоматически поменяется.
Думаю, Вы обратили внимание, что правило создается не только для текста и чисел, но и дат, и в зависимости от заполнения и наличия ошибок в ячейках. Указывайте условие, выбирайте цвет и сохраняйте.
Чтобы посмотреть, что Вы подобавляли, выберите диапазон и в окне «Управление правилами» будет полный список. Используя кнопки вверху их можно добавлять, изменять или удалять.
В Excel есть полезные функции, о которых многие не знают. Одна из них – функция условного форматирования, которая автоматически применяет определенный формат, например, закрашивает красным цветом ячейки, соответствующие определенному условию, установленному пользователем.
Здесь мы расскажем, как устанавливать условия в Excel , чтобы затем изменять цвет ячеек в соответствии с ним автоматически.
Условия закрашивания ячеек Excel в Windows
Закрасить ячейки на условиях очень просто. Выберите ячейки, столбцы или строки, цвет которых Вы хотите изменить. Затем на вкладке Главная перейдите в меню Стили и выберите Условное форматирование > Условия выделения ячейки . Выберите подходящее Вам условие. Введите контрольное значение, откройте раскрывающееся меню и выберите Пользовательский формат . Нажмите на вкладку Заливка и выберите цвет ячеек. Наконец, нажмите ОК .Условия закрашивания ячеек Excel в Mac
На Mac операция выделения ячеек цветим на условиях – точно такая же. Единственное различие заключается в более широком диапазоне вариантов условий для настройки.Изображение: © Mika Baumeister – Unsplash.com
Человек так уж устроен, что цвета воспринимает лучше, чем голые цифры — недаром наиболее важные участки диаграмм или строки таблицы на которые стоит обратить внимание, принято выделять контрастным цветом. Однако отмечать «хорошие» и «плохие» ячейки таблицы в Excel вручную, занятие не самое веселое. Вот бы MS Excel умел делать это самостоятельно, да ещё и автоматически менять эти самые цвета, в случае изменения ситуации…
А ведь он итак прекрасно умеет это делать — нам с вами остается только ему слегка помочь!
Давайте решим такую вот прикладную задачу: в нашей таблице «фрукты» указан вес того или иного наименования в килограммах. Чтобы было проще ориентироваться в том, чего у нас не хватает, а чего наоборот — в избытке, мы раскрасим все значения меньше 20 красным цветом, а все, что выше 50 — зеленым. При этом всё, что осталось в этом диапазоне цветом помечаться не будет совсем. А чтобы усложнить задачу пойдем ещё дальше и сделаем присвоение цвета динамическим — при изменении значения в соответствующей ячейке, будет меняться и её цвет.
Сначала выделяем диапазон данных, то есть содержимое второго столбца таблицы MS Excel, а затем идем на вкладку «Главная «, где в группе «Стили» активируем инструмент «Условное форматирование «, и в раскрывшемся списке выбираем «Создать правило «.
В появившемся окне «Создание правила форматирования» выбираем Тип правила: «Форматировать только ячейки которые содержат», а в конструкторе ниже, устанавливаем параметры: «Значение ячейки», «Меньше» и вручную вписываем наш «край»: число 20.
Нажимаем кнопку «Формат» ниже, переходим на вкладку «Заливка» и выбираем красный цвет. Нажимаем «Ок».
Примечание: вы, конечно, можете помечать ячейки не цветом их фона, а, например, размером или цветом шрифта — для этого в том же окне нужно воспользоваться соответствующими вкладками.
Посмотрите на таблицу — яблок и мандаринов у нас явно осталось совсем мало, пора делать новый закуп!
Отлично, данные уже выделяются цветом!
Теперь, по аналогии, создадим ещё одно правило — только на этот раз с параметрами «Значение ячейки», «Больше», 20. В качестве заливки укажем зеленый цвет. Готово.
Мне этого показалось мало — черный текст на красном и зеленом фоне читается плохо, поэтому я решил немного украсить наши правила, и заменить цвет текста на белый. Чтобы проделать это, откройте инструмент «Условное форматирование», но выберите не пункт «Создать правило», а «Управление правилами «, ниже.
В появившемся окне выделяем оба правила по очереди, и нажимаем кнопку «Изменить «, после чего, в уже знакомом окне «Формат » переходим на вкладку «Шрифт » и меняем цвет текста на белый. Вот что у меня получилось в итоге:
Теперь я изменил не только фон ячеек таблицы, но и цвет шрифта
Попробуем изменить «плохие» значения на «хорошие»? Раз и готово — цвет автоматически изменился, как только в соответствующих ячейках появились значения, попадающие под действие одного из правил.
Меняем в нашей excel-таблице значения… все работает!
Для выполнения этой задачи будем использовать возможности условного форматирования.
Возьмем таблицу, содержащую список заказов, сроки их исполнения, текущий статус и стоимость. Попробуем сделать так, чтобы ее ячейки раскрашивались сами, в зависимости от их содержимого.
Инструкция для Excel 2010
ВКЛЮЧИТЕ СУБТИТРЫ!
Как это сделать в Excel 2007
ВКЛЮЧИТЕ СУБТИТРЫ!
Выделим ячейки с ценами заказов и, нажав на стрелочку рядом с кнопкой «Условное форматирование», выберем «Создать правило».
Выберем четвертый пункт, позволяющий сравнивать текущие значения со средним. Нас интересуют значения выше среднего. Нажав кнопку «Формат», зададим цвет ячеек.
Подтверждаем наш выбор, и ячейки с ценой выше средней окрасились в голубой цвет, привлекая наше внимание к дорогим заказам.
Выделим ячейки со статусами заказов и создадим новое правило. На этот раз используем второй вариант, позволяющий проверять содержимое ячейки. Выберем «Текст», «содержит» и введем слово «Выполнен». Зададим зеленый цвет, подтверждаем, и выполненные работы у нас позеленели.
Ну и сделаем еще одно правило, окрашивающее просроченные заказы в красный цвет. Выделяем даты выполнения заказов. При создании правила снова выбираем второй пункт, но на этот раз задаем «Значение ячейки», «меньше», а в следующем поле вводим функцию, возвращающую сегодняшнюю дату.
«ОК», и мы получили весело разукрашенную таблицу, позволяющую наглядно отслеживать ход выполнения заказов.
Обратили внимание, что статусы задаются выбором из выпадающего списка значений? Как делать такие списки, мы рассказывали в инструкции .
Как это сделать в Excel 2003
ВКЛЮЧИТЕ СУБТИТРЫ!
«Условное форматирование» в меню «Формат». Тут понадобится немного больше ручной работы. Вот так будут выглядеть настройки для нашей первой задачи – закрасить ячейки со значениями больше средних.
Придется вручную ввести функцию «=СРЗНАЧ()», поставить курсор между скобками, нажать на кнопочку рядом и мышкой указать нужный диапазон.
Но принцип действий тот же самый.
Покоряйте Excel и до новых встреч!
Для того чтобы в документе, сделанном в MS Excel, правильно расставить акценты, можно увеличить размеры ячеек, выбрать другой шрифт или размер букв, а можно закрасить квадратики, на которые точно нужно обратить внимание.
Вот сейчас мы разберемся, как выделять ячейки цветом в Экселе, или изменить цвет тех, которые уже закрашены. Как сделать так, чтобы ячейка изменяла цвет по заданному условию, в зависимости от вписанного в нее значения, и как работать с созданными правилами.
Простая заливка блока
Закрасить один или несколько блоков в Экселе не сложно. Сначала выделите их и на вкладке «Главная» нажмите на стрелку возле ведерка с краской, чтобы развернуть список. Выберите оттуда подходящий цвет, а если ничего не подойдет, нажимайте «Другие цвета» .
Кстати, таким образом можно залить целую строку, просто предварительно кликните по ее номеру, чтобы выделить. Про выделение ячеек в Экселе можете прочесть отдельную статью.
Если же Вы работаете с таблицей, в которой уже что-то закрашено, тогда поменять цвет блоков, или вовсе его убрать можно так. Нажмите по нему и из цветов или выберите новый, или кликните по варианту «Нет» .
В зависимости от введенных данных
Теперь рассмотрим, как сделать так, чтобы ячейка меняла цвет исходя из заданного условия. Используется для этого условное форматирование , про которое на сайте есть отдельная статья.
Текстовых
Возьмем для примера следующую таблицу. Давайте в ней сделаем так, чтобы красный соответствовал яблоку, желтый – груше, а оранжевый – апельсину.
Выделяем данные, с которыми будем работать, в моем случае – это названия фруктов. Потом нажимаем «Условное форматирование» , которое будем постоянно использовать дальше. Из списка кликните по «Создать правило» .
Открывается вот такое окно. Вверху выбираем тип – «Форматировать только ячейки, которые содержат» , дальше тоже будем отмечать именно его. Чуть ниже указываем условия: у нас текст, который содержит определенные слова. В последнем поле или нажмите на кнопку и укажите ячейку, или впишите текст.
Отличие в том, что поставив ссылку на ячейку (=$B$4 ), условие будет меняться в зависимости от того, что в ней набрано. Например, вместо яблока в В4 укажу смородину, соответственно поменяется правило, и будут закрашены блоки с таким же текстом. А если именно в поле вписать яблоко, то искаться будет конкретно это слово, и оно ни от чего зависеть не будет.
Здесь выберите цвет заливки и нажмите «ОК» . Для просмотра всех вариантов кликните по кнопке «Другие» .
Правило создано и сохраняем его, нажатием кнопки «ОК» .
В результате, все блоки, в которых был указанный текст, закрасились в красный.
Также создаются правила и для остальных фруктов, выбирается только другой вариант заливки.
Числовых
Теперь разберемся с числовыми данными. Давайте числа в столбце D разместим на определенный фон по тому условию, которое зададим.
Выделяем столбец, создаем правило, указываем его тип. Дальше прописываем – «Значение» «больше» «15» . Последнее число можете или ввести вручную, или указать адрес ячейки, откуда будут браться данные. Определяемся с заливкой, жмем «ОК» .
Блоки, где введены числа больше выбранного, закрасились.
Давайте для выделенных ячеек укажем еще правила – выберите «Управление правилами» .
Здесь все выбирайте, как я описывала выше, только нужно изменить цвет и поставить условие «меньше или равно» .
Когда все будет готово, нажимайте «Применить» и «ОК» .
Все работает, значения равные и ниже 15 закрашены бледно голубым.
Здесь хорошо была бы видна и разница между завезенным и проданным товаром. Выделяем два столбца и нажимаем «Правила выделения ячеек» – «Повторяющиеся значения» . Выберите подходящий цвет. После этого, если две ячейки рядом будут цветные – значит, те фрукты, которые завезли, все продали.
Давайте на примере последнего столбца покажу, как сделать, чтобы ячейка меняла цвет в зависимости от указанного значения. Выделяем их и заходим в «Управление правилами» .
Создаем новое для текущего фрагмента, если нужно в данном выпадающем списке можно выбрать или для всего листа целиком, или для других листов.
Выбираем нужные пункты в открывшемся окошке. Я залью темно зеленым все значения, что больше 90. Поскольку в последнем поле я указала адрес (=$F$15 ), то при изменении в ячейке числа 90, например, на 110, правило также поменяется. Сохраните изменения, кликнув по кнопке «ОК» .
Создам еще одно правило, но в нем подсвечу светло зеленым все, что меньше или равняется 90. Не забывайте все сохранять, нажав на кнопочки, справа внизу.
Вот, что получилось. Из последнего столбца, можно визуально быстро определить прибыль от продажи какого товара была больше.
Например, если изменится цена, то будут пересмотрены и правила. Если значение станет больше или меньше заданного, то и цвет ячейки автоматически поменяется.
Думаю, Вы обратили внимание, что правило создается не только для текста и чисел, но и дат, и в зависимости от заполнения и наличия ошибок в ячейках. Указывайте условие, выбирайте цвет и сохраняйте.
Чтобы посмотреть, что Вы подобавляли, выберите диапазон и в окне «Управление правилами» будет полный список. Используя кнопки вверху их можно добавлять, изменять или удалять.
