Excel это не только сильный инструмент для проведения расчетов, а и инструмент с помощью которого можно привлекательно представить результаты расчетов. Именно поэтому в этой статье я хочу рассказать о способах придания цвета ячейкам. Цвет ячейки в Excel можно задать несколькими способами. Нельзя сказать какой из способов является наилучшим, ведь каждый является уникальным по-своему и предназначен для использования при определенных условиях и обстоятельствах.
Ручная установка цвета ячейки
Наиболее простым способом установки цвета ячейки в excel является инструмент «Цвет заливки» находящийся на главной панели инструментов.
Выделив определенный диапазон ячеек нужно нажать на инструмент «Цвет заливки» либо стрелочку рядом. Если нажать стрелочку рядом, то откроются цвета темы. Из предложенных цветов можно выбрать нужный цвет или нажать кнопку «Другие цвета». В этом случае откроется еще одно окно с возможностью выбора цвета.
Кроме описанного способа есть второй ручной способ. Нужно сначала выделить диапазон ячеек, а потом вызвать контекстное меню и нажать пункт меню «Формат ячеек».
Откроется диалоговое окно формата ячейки. После этого нужно будет нажать закладку «Заливка».
В данном диалоговом окне нужно выбрать цвет и все.
Следует отметить, что все ручные способы используются только для придания таблицам более читабельного вида. Этими способами можно разукрашивать шапки таблиц и выделять строки таблицы. Но ручные способы не годятся для придания цвета ячейкам в зависимости от записанного значения.
Установка цвета фона ячейки в зависимости от значения
Иногда случается так, что в зависимости от того какое значение записано в ячейке нужно установить цвет определенного фона. Рассмотрим как это сделать на примере. У нас есть торговые агенты, которым поставлена норма плана по определенным группам продуктов. Если норма не выполнена, то ячейка с продажами должна «загореться» красным. В качестве первичного условия скажем что продажи по недельно не должны быть ниже 185 тысяч рублей.
Для выполнения поставленной задачи нужно выделить диапазон ячеек и нажать на панели инструментов «Условное форматирование»
В открывшемся меню нас интересует пункт «Правила выделения ячеек». Если нажать на указанный пункт, то можно увидеть, что разработчики excel реализовали множество возможностей выделения ячеек. Выделить фон можно по следующим критериям: больше, меньше, между, равно, текст содержит, дата, повторяющиеся значения. Для решения нашей задачи нам нужно использовать «Меньше».
После того, как мы указали критерий 185 тысяч, в таблице появятся выделения ячеек.
Предложенный способ широко используется при экономическом анализе различных данных. Является простым и доступным. Позволяет заложить определенные условия выделения еще до явного определения данных.
Установка цвета ячейки при помощи VBA
Способ выделения ячеек в excel о котором пойдет речь далее является самым универсальным. Так как с его помощью можно построить самые сложные правила для выделения ячеек. Но к сожалению его могут использовать только продвинутые пользователи. Причина проста – нужно программировать, а значит обладать более сложными навыками.
Установить цвет ячейки при помощи VBA достаточно просто, программный код выделения будет выглядеть так:
Range("O6").Select
Selection.Interior.Color = QBColor(10)
После выполнения данного кода ячейка О6 станет салатовой.
Использование VBA для выделения цвета ячейки позволяет решать широкий круг задач, например выделять ячейки определенным цветом:
- в зависимости от типа введенного значения;
- в зависимости от времени редактирования документа;
- в зависимости от пользователя компьютера, на котором редактируется документ и т.д.
Нужно выделить повторяющиеся значения в столбце? Надо выбрать первые 5 максимальных ячеек? Необходимо сделать термальную шкалу для наглядности (цвет меняется в зависимости от увеличения/уменьшения значения ячеек)? В Excel выделение цветом ячеек по условиям можно сделать очень быстро и просто. За выделение цветом ячеек отвечает специальная функция «Условное форматирование». Настоятельно рекомендую! Подробнее читаем дальше:
Основные возможности я описал в начале статьи, но на самом деле их масса. Подробнее о самых полезных
Для начала, на ленте задач в главном меню найдите раздел Стили и нажмите на кнопку Условное форматирование.
При нажатии откроется меню, с разными вариантами этого редактирования. Как вы видите, возможностей здесь действительно много.
Теперь подробнее о самых полезных:
Excel выделение цветом ячеек по условиям. Простые условия
Для этого зайдите в пункт Правила выделения ячеек. Если к примеру, вам нужно выделить все ячейки больше 100, нажмите кнопку Больше. В окне:
по умолчанию условия, предлагается выделить красным цветом, но вы можете задать нужное форматирование ячеек нажав в правом окошке и выбрав необходимы вариант.
Выделение повторяющихся значений, в т.ч. по нескольким столбцам
Чтобы выделить все повторяющиеся значения выберите соответствующее меню Повторяющиеся значения.
Что делать если необходимо найти повторения по двум и более столбцам, например когда ФИО в разных столбцах? Сделайте еще один столбец и объедините значения формулой = , т.е. в отдельной ячейке у вас будет написано ИвановИванИваныч. По такому столбцу вы уже легко сможете выделить повторяющиеся значения. Важно понимать, что если порядок слов будет различаться, то Excel сочтет такие строки неповторяющимися (например, ИванИванычИванов).
Выделение цветом первых/последних значений. Опять же условное форматирование
Для этого зайдите в пункт Правила отбора первых и последних ячеек и выберите нужный пункт. Помимо того, что можно выделить первые/последние значения (в том числе и по процентам), можно использовать возможность выделить данные выше и ниже среднего (пользуюсь даже чаще). Очень удобно для просмотра результатов отличающихся от нормы или среднего!
Построение термальной диаграммы и гистограммы
Классная функция для визуализации данных — термальная/температурная диаграмма. Суть в том, что в зависимости от величины значения в столбце или строке, ячейка подсвечивается определенным оттенком цвета, чем больше, тем краснее, например. Таблицы воспринимаются гораздо лучше на глаз, а принимать решение становится проще. Ведь один из лучших анализаторов зачастую — это наш глаз, соответственно, мозг, а не машина!
Гистограмма в ячейке (голубым на рисунке ниже) тоже крайне полезная функция, для выявления изменения значений и сравнения их.
Выделение цветом ячеек, содержащих определенный текст
Очень часто нужно найти ячейки, которые содержат определенный набор символов, можно конечно воспользоваться функцией = , но проще и быстрее применить условное форматирование, пройдите — Правила отбора ячеек — Текст содержит
Очень полезно при работе с текстом. Пример, когда в столбце у вас записаны ФИО сотрудников, а надо отобрать всех коллег Ивановых. Выделяем ячейки заходим в нужный пункт и выделяем содержащий текст Иванов, после чего фильтруем таблицу по цвету
Excel выделение цветом. Фильтр по цвету
Помимо вышеперечисленных возможностей вы можете отфильтровать выделенные ячейки по цвету обычном фильтром. К моему удивлению об этом очень мало кто знает — видимо отголоски версии 2003 — там этой возможности не было.
Проверка условий форматирования
Чтобы проверить какие условные форматирования у Вас заданы, пройдите Главная — Условное форматирование — Управление правилами. Здесь вы сможете отредактировать уже заданные условия, диапазон применения, а также выбрать приоритет заданного форматирования (кто выше, тот главнее, изменить можно кнопками — стрелками).
Неверный диапазон условного форматирования
Важно! Условное форматирование при неправильном использовании зачастую является причиной сильных . Происходит задвоение форматирований, для примера если вы много раз копируете ячейки с выделением цветом. Тогда у вас появится множество условий с цветом. Я сам видел более 3 тысяч условий — тормозил файл безобразно. Также файл может тормозить, когда задан диапазон как на картинке выше, лучше, указывать A:A — для всего диапазона.
Подробнее о тормозах Excel и их причинах читайте . Эта статья помогла не одной сотне людей;)
Надеюсь был полезен, не прощаюсь!
Поделитесь нашей статьей в ваших соцсетях:Для того чтобы в документе, сделанном в MS Excel, правильно расставить акценты, можно увеличить размеры ячеек, выбрать другой шрифт или размер букв, а можно закрасить квадратики, на которые точно нужно обратить внимание.
Вот сейчас мы разберемся, как выделять ячейки цветом в Экселе, или изменить цвет тех, которые уже закрашены. Как сделать так, чтобы ячейка изменяла цвет по заданному условию, в зависимости от вписанного в нее значения, и как работать с созданными правилами.
Простая заливка блока
Закрасить один или несколько блоков в Экселе не сложно. Сначала выделите их и на вкладке «Главная» нажмите на стрелку возле ведерка с краской, чтобы развернуть список. Выберите оттуда подходящий цвет, а если ничего не подойдет, нажимайте «Другие цвета» .
Кстати, таким образом можно залить целую строку, просто предварительно кликните по ее номеру, чтобы выделить. Про выделение ячеек в Экселе можете прочесть отдельную статью.
Если же Вы работаете с таблицей, в которой уже что-то закрашено, тогда поменять цвет блоков, или вовсе его убрать можно так. Нажмите по нему и из цветов или выберите новый, или кликните по варианту «Нет» .
В зависимости от введенных данных
Теперь рассмотрим, как сделать так, чтобы ячейка меняла цвет исходя из заданного условия. Используется для этого условное форматирование , про которое на сайте есть отдельная статья.
Текстовых
Возьмем для примера следующую таблицу. Давайте в ней сделаем так, чтобы красный соответствовал яблоку, желтый – груше, а оранжевый – апельсину.
Выделяем данные, с которыми будем работать, в моем случае – это названия фруктов. Потом нажимаем «Условное форматирование» , которое будем постоянно использовать дальше. Из списка кликните по «Создать правило» .
Открывается вот такое окно. Вверху выбираем тип – «Форматировать только ячейки, которые содержат» , дальше тоже будем отмечать именно его. Чуть ниже указываем условия: у нас текст, который содержит определенные слова. В последнем поле или нажмите на кнопку и укажите ячейку, или впишите текст.
Отличие в том, что поставив ссылку на ячейку (=$B$4 ), условие будет меняться в зависимости от того, что в ней набрано. Например, вместо яблока в В4 укажу смородину, соответственно поменяется правило, и будут закрашены блоки с таким же текстом. А если именно в поле вписать яблоко, то искаться будет конкретно это слово, и оно ни от чего зависеть не будет.
Здесь выберите цвет заливки и нажмите «ОК» . Для просмотра всех вариантов кликните по кнопке «Другие» .
Правило создано и сохраняем его, нажатием кнопки «ОК» .
В результате, все блоки, в которых был указанный текст, закрасились в красный.
Также создаются правила и для остальных фруктов, выбирается только другой вариант заливки.
Числовых
Теперь разберемся с числовыми данными. Давайте числа в столбце D разместим на определенный фон по тому условию, которое зададим.
Выделяем столбец, создаем правило, указываем его тип. Дальше прописываем – «Значение» «больше» «15» . Последнее число можете или ввести вручную, или указать адрес ячейки, откуда будут браться данные. Определяемся с заливкой, жмем «ОК» .
Блоки, где введены числа больше выбранного, закрасились.
Давайте для выделенных ячеек укажем еще правила – выберите «Управление правилами» .
Здесь все выбирайте, как я описывала выше, только нужно изменить цвет и поставить условие «меньше или равно» .
Когда все будет готово, нажимайте «Применить» и «ОК» .
Все работает, значения равные и ниже 15 закрашены бледно голубым.
Здесь хорошо была бы видна и разница между завезенным и проданным товаром. Выделяем два столбца и нажимаем «Правила выделения ячеек» – «Повторяющиеся значения» . Выберите подходящий цвет. После этого, если две ячейки рядом будут цветные – значит, те фрукты, которые завезли, все продали.
Давайте на примере последнего столбца покажу, как сделать, чтобы ячейка меняла цвет в зависимости от указанного значения. Выделяем их и заходим в «Управление правилами» .
Создаем новое для текущего фрагмента, если нужно в данном выпадающем списке можно выбрать или для всего листа целиком, или для других листов.
Выбираем нужные пункты в открывшемся окошке. Я залью темно зеленым все значения, что больше 90. Поскольку в последнем поле я указала адрес (=$F$15 ), то при изменении в ячейке числа 90, например, на 110, правило также поменяется. Сохраните изменения, кликнув по кнопке «ОК» .
Создам еще одно правило, но в нем подсвечу светло зеленым все, что меньше или равняется 90. Не забывайте все сохранять, нажав на кнопочки, справа внизу.
Вот, что получилось. Из последнего столбца, можно визуально быстро определить прибыль от продажи какого товара была больше.
Например, если изменится цена, то будут пересмотрены и правила. Если значение станет больше или меньше заданного, то и цвет ячейки автоматически поменяется.
Думаю, Вы обратили внимание, что правило создается не только для текста и чисел, но и дат, и в зависимости от заполнения и наличия ошибок в ячейках. Указывайте условие, выбирайте цвет и сохраняйте.
Чтобы посмотреть, что Вы подобавляли, выберите диапазон и в окне «Управление правилами» будет полный список. Используя кнопки вверху их можно добавлять, изменять или удалять.
Условное форматирование в Excel позволяет выделить не только ячейку, но и всю строку сразу.
Чтобы в большой таблице выделить сразу всю строку, нужно установить в таблице наши условия форматирования таблицы.
Что такое условное форматирование, читайте в статье «
Условное форматирование в Excel ».
У нас есть таблица с данными посетителей. Нам нужно выделить посетителей, которые окончили курс обучения, лечения, работу, др. Таблица такая.
В отдельных ячейках создаем небольшую вспомогательную таблицу. Например, сделаем табличку в ячейках G2, G3 и G4.
Если мы в графе «Статус» напишем - «Завершен» или «1 этап», «2 этап», вся строка окрасится цветом.
Вместо вспомогательной таблицы, можно сделать во вспомогательной ячейке «выпадающий список».
Теперь устанавливаем условное форматирование в ячейки таблицы.
Выделяем всю таблицу с данными и дополнительно внизу таблицы ещё одну пустую строку. Пустую строку таблицы будем копировать, если нужно будет добавить строки в таблице. Копироваться будут сразу и условия форматирования.
Итак, мы выделили таблицу диапазона A2:E7.
На закладке «Главная» нажимаем кнопку «Условное форматирование» и выбираем функцию «Создать правила».
Нажимаем на строку «Сравнить столбцы таблицы для определения форматируемых ячеек».
В строке "Формат" пишем формулу. =$E2=$G$2
Обратите внимание
– ссылка на ячейку Е2 смешанная.
Нажимаем на кнопку с «0». Здесь выбираем и устанавливаем цвет заливки строки, цвет шрифта этой строки. Мы выбрали зеленый цвет заливки ячеек.
Нажимаем во всех трех диалоговых окнах «ОК». Всё.
Теперь пишем в таблице в столбце «Статус» - «Завершен» и наша строка окрасилась в зеленый цвет, который мы установили в правилах условного форматирования.
Внимание!
В ячейках столбца «Статус» писать слова так же, как написаны во вспомогательной таблице. Например, у нас написано слово «Завершен» с большой буквы. Если мы напишем в ячейке столбца слово «завершен» с маленькой буквы, то условное форматирование не сработает. Поэтому в столбце «Статус» лучше установить выпадающий список.
Как установить выпадающий список, смотрите в статье «
Выпадающий список в Excel ».
Получилось так.
