Учимся правильно выделять ячейки в exel. Заливка ячеек в зависимости от значения в Microsoft Excel Изменение заливки в ручную

Условное форматирование в Excel позволяет выделить не только ячейку, но и всю строку сразу. Чтобы в большой таблице выделить сразу всю строку, нужно установить в таблице наши условия форматирования таблицы. Что такое условное форматирование, читайте в статье « Условное форматирование в Excel ».
У нас есть таблица с данными посетителей. Нам нужно выделить посетителей, которые окончили курс обучения, лечения, работу, др. Таблица такая. В отдельных ячейках создаем небольшую вспомогательную таблицу. Например, сделаем табличку в ячейках G2, G3 и G4.
Если мы в графе «Статус» напишем - «Завершен» или «1 этап», «2 этап», вся строка окрасится цветом.
Вместо вспомогательной таблицы, можно сделать во вспомогательной ячейке «выпадающий список».
Теперь устанавливаем условное форматирование в ячейки таблицы.
Выделяем всю таблицу с данными и дополнительно внизу таблицы ещё одну пустую строку. Пустую строку таблицы будем копировать, если нужно будет добавить строки в таблице. Копироваться будут сразу и условия форматирования.
Итак, мы выделили таблицу диапазона A2:E7.
На закладке «Главная» нажимаем кнопку «Условное форматирование» и выбираем функцию «Создать правила».
Нажимаем на строку «Сравнить столбцы таблицы для определения форматируемых ячеек». В строке "Формат" пишем формулу. =$E2=$G$2
Обратите внимание – ссылка на ячейку Е2 смешанная.
Нажимаем на кнопку с «0». Здесь выбираем и устанавливаем цвет заливки строки, цвет шрифта этой строки. Мы выбрали зеленый цвет заливки ячеек. Нажимаем во всех трех диалоговых окнах «ОК». Всё.
Теперь пишем в таблице в столбце «Статус» - «Завершен» и наша строка окрасилась в зеленый цвет, который мы установили в правилах условного форматирования.
Внимание! В ячейках столбца «Статус» писать слова так же, как написаны во вспомогательной таблице. Например, у нас написано слово «Завершен» с большой буквы. Если мы напишем в ячейке столбца слово «завершен» с маленькой буквы, то условное форматирование не сработает. Поэтому в столбце «Статус» лучше установить выпадающий список. Как установить выпадающий список, смотрите в статье « Выпадающий список в Excel ». Получилось так.

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

Инструкция для Excel 2010

ВКЛЮЧИТЕ СУБТИТРЫ!

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

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

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


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


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


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


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


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

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

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


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

Одним из основных вопросов при работе с таблицами считается, как выделить цветом ячейку в Еxcel по условию?

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

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

Как делается окрашивание ячейки в Еxcel

Чтобы отметить определенную строку или столбец в таблице или изменить уже окрашенную, следует нажать на вкладке раздел – «Главная». Далее после нажатия ведерка с краской нужно выбрать соответственный цвет.

Когда с представленной палитры нет подходящего оттенка, то можно нажать на вкладку «Другие цвета» и выбрать какой необходим на свое усмотрение.

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

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

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

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

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

1. Следует определить тип, то есть, выбирается строка, где написано «Форматировать только ячейки, которые содержат». В этом пункте нужно прописать какое условие, а именно слово или текст, который будет задавать поставленные параметры.
2. Следующим этапом будет выбор кнопки «Формат». В нем надо выбрать соответствующий цвет и нажать «Ок». Тем самым сохранив и созданное правило, и обозначив избранную краску.

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

• на панели задач выбрать пункт «Управление правилами»;
• далее «Текущий фрагмент»;
• и после «создать правило»;
• затем – «Форматировать только ячейки, для которых выполняется следующее условие»;
• следом нажать кнопку «Меньше или равно»;
• выбрать оттенок заливки;
• когда все пункты будут отмечены следует кликнуть «Применить» и «Ок».

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

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

Есть возможность окрасить определенные строки или столбцы одним цветом, но разных оттенков. Для этого в пункте диспетчера правил выбрать значение «Форматировать все ячейки на основании значений». После чего выбрать в поле «Цвет» нужную краску и нажать кнопку «Ок». Изменения оттенков будут меняться в зависимости от внесения данных.

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

Изменение заливки в ручную

Самым простым способом изменить палитру в таблице Еxcel, это использовать такой инструмент, как «Цвет заливки». Здесь понадобиться выбрать необходимый цвет и окрасить им отмеченные столбцы или строки.

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

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

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

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

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

Чтобы проверить заданные параметры условий перед тем, как нажать клавишу «Применить», следует пройтись по всем пунктам, а именно:

• главная панель задач (Home);
• условное форматирование (Conditional Formatting);
• управление правилами (New rule).

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

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

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

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

Ручная установка цвета ячейки

Наиболее простым способом установки цвета ячейки в excel является инструмент «Цвет заливки» находящийся на главной панели инструментов.

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

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

Откроется диалоговое окно формата ячейки. После этого нужно будет нажать закладку «Заливка».

В данном диалоговом окне нужно выбрать цвет и все.

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

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

Иногда случается так, что в зависимости от того какое значение записано в ячейке нужно установить цвет определенного фона. Рассмотрим как это сделать на примере. У нас есть торговые агенты, которым поставлена норма плана по определенным группам продуктов. Если норма не выполнена, то ячейка с продажами должна «загореться» красным. В качестве первичного условия скажем что продажи по недельно не должны быть ниже 185 тысяч рублей.

Для выполнения поставленной задачи нужно выделить диапазон ячеек и нажать на панели инструментов «Условное форматирование»

В открывшемся меню нас интересует пункт «Правила выделения ячеек». Если нажать на указанный пункт, то можно увидеть, что разработчики excel реализовали множество возможностей выделения ячеек. Выделить фон можно по следующим критериям: больше, меньше, между, равно, текст содержит, дата, повторяющиеся значения. Для решения нашей задачи нам нужно использовать «Меньше».

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

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

Установка цвета ячейки при помощи VBA

Способ выделения ячеек в excel о котором пойдет речь далее является самым универсальным. Так как с его помощью можно построить самые сложные правила для выделения ячеек. Но к сожалению его могут использовать только продвинутые пользователи. Причина проста – нужно программировать, а значит обладать более сложными навыками.

Установить цвет ячейки при помощи VBA достаточно просто, программный код выделения будет выглядеть так:

Range("O6").Select
Selection.Interior.Color = QBColor(10)

После выполнения данного кода ячейка О6 станет салатовой.

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

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

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

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

Простая заливка блока

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

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

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

В зависимости от введенных данных

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

Текстовых

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

Выделяем данные, с которыми будем работать, в моем случае – это названия фруктов. Потом нажимаем «Условное форматирование» , которое будем постоянно использовать дальше. Из списка кликните по «Создать правило» .

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

Отличие в том, что поставив ссылку на ячейку (=$B$4 ), условие будет меняться в зависимости от того, что в ней набрано. Например, вместо яблока в В4 укажу смородину, соответственно поменяется правило, и будут закрашены блоки с таким же текстом. А если именно в поле вписать яблоко, то искаться будет конкретно это слово, и оно ни от чего зависеть не будет.

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

Правило создано и сохраняем его, нажатием кнопки «ОК» .

В результате, все блоки, в которых был указанный текст, закрасились в красный.

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

Числовых

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

Выделяем столбец, создаем правило, указываем его тип. Дальше прописываем – «Значение» «больше» «15» . Последнее число можете или ввести вручную, или указать адрес ячейки, откуда будут браться данные. Определяемся с заливкой, жмем «ОК» .

Блоки, где введены числа больше выбранного, закрасились.

Давайте для выделенных ячеек укажем еще правила – выберите «Управление правилами» .

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

Когда все будет готово, нажимайте «Применить» и «ОК» .

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

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

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

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

Выбираем нужные пункты в открывшемся окошке. Я залью темно зеленым все значения, что больше 90. Поскольку в последнем поле я указала адрес (=$F$15 ), то при изменении в ячейке числа 90, например, на 110, правило также поменяется. Сохраните изменения, кликнув по кнопке «ОК» .

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

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

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

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

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