Заливка ячеек в зависимости от значения в Microsoft Excel

Заливка ячеек в зависимости от значения в Microsoft Excel

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

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

Давайте узнаем, как это можно сделать в программе Excel.

Скачать последнюю версию Excel

Процедура изменения цвета ячеек в зависимости от содержимого

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

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

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

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

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

Но выход существует. Для ячеек, которые содержат динамические (изменяющиеся) значения применяется условное форматирование, а для статистических данных можно использовать инструмент «Найти и заменить».

Способ 1: условное форматирование

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

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

1. Выделяем столбец, в котором находится информация по доходам предприятия. Затем перемещаемся во вкладку «». Щелкаем по кнопке «Условное форматирование», которая располагается на ленте в блоке инструментов «Стили». В открывшемся списке выбираем пункт «Управления правилами…».
2. Запускается окошко управления правилами условного форматирования. В поле «Показать правила форматирования для» должно быть установлено значение «Текущий фрагмент». По умолчанию именно оно и должно быть там указано, но на всякий случай проверьте и в случае несоответствия измените настройки согласно вышеуказанным рекомендациям. После этого следует нажать на кнопку «Создать правило…».
3. Открывается окно создания правила форматирования. В списке типов правил выбираем позицию «Форматировать только ячейки, которые содержат». В блоке описания правила в первом поле переключатель должен стоять в позиции «Значения». Во втором поле устанавливаем переключатель в позицию «Меньше». В третьем поле указываем значение, элементы листа, содержащие величину меньше которого, будут окрашены определенным цветом. В нашем случае это значение будет 400000. После этого жмем на кнопку «Формат…».
4. Открывается окно формата ячеек. Перемещаемся во вкладку «Заливка». Выбираем тот цвет заливки, которым желаем, чтобы выделялись ячейки, содержащие величину менее 400000. После этого жмем на кнопку «OK» в нижней части окна.
5. Возвращаемся в окно создания правила форматирования и там тоже жмем на кнопку «OK».
6. После этого действия мы снова будем перенаправлены в Диспетчер правил условного форматирования. Как видим, одно правило уже добавлено, но нам предстоит добавить ещё два. Поэтому снова жмем на кнопку «Создать правило…».
7. И опять мы попадаем в окно создания правила. Перемещаемся в раздел «Форматировать только ячейки, которые содержат». В первом поле данного раздела оставляем параметр «Значение ячейки», а во втором выставляем переключатель в позицию «Между». В третьем поле нужно указать начальное значение диапазона, в котором будут форматироваться элементы листа. В нашем случае это число 400000. В четвертом указываем конечное значение данного диапазона. Оно составит 500000. После этого щелкаем по кнопке «Формат…».
8. В окне форматирования снова перемещаемся во вкладку «Заливка», но на этот раз уже выбираем другой цвет, после чего жмем на кнопку «OK».
9. После возврата в окно создания правила тоже жмем на кнопку «OK».
10. Как видим, в Диспетчере правил у нас создано уже два правила. Таким образом, осталось создать третье. Щелкаем по кнопке «Создать правило».
11. В окне создания правила опять перемещаемся в раздел «Форматировать только ячейки, которые содержат». В первом поле оставляем вариант «Значение ячейки». Во втором поле устанавливаем переключатель в полицию «Больше». В третьем поле вбиваем число 500000. Затем, как и в предыдущих случаях, жмем на кнопку «Формат…».
12. В окне «Формат ячеек» опять перемещаемся во вкладку «Заливка». На этот раз выбираем цвет, который отличается от двух предыдущих случаев. Выполняем щелчок по кнопке «OK».
13. В окне создания правил повторяем нажатие на кнопку «OK».
14. Открывается Диспетчер правил. Как видим, все три правила созданы, поэтому жмем на кнопку «OK».
15. Теперь элементы таблицы окрашены согласно заданным условиям и границам в настройках условного форматирования.
16. Если мы изменим содержимое в одной из ячеек, выходя при этом за границы одного из заданных правил, то при этом данный элемент листа автоматически сменит цвет.

Кроме того, можно использовать условное форматирование несколько по-другому для окраски элементов листа цветом.

1. Для этого после того, как из Диспетчера правил мы переходим в окно создания форматирования, то остаемся в разделе «Форматировать все ячейки на основании их значений». В поле «Цвет» можно выбрать тот цвет, оттенками которого будут заливаться элементы листа. Затем следует нажать на кнопку «OK».
2. В Диспетчере правил тоже жмем на кнопку «OK».
3. Как видим, после этого ячейки в колонке окрашиваются различными оттенками одного цвета. Чем значение, которое содержит элемент листа больше, тем оттенок светлее, чем меньше – тем темнее.

Урок: Условное форматирование в Экселе

Способ 2: использование инструмента «Найти и выделить»

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

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

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

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

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

   Но существует возможность поступить несколько по-другому, что нам даст тот же результат. Можно в строке поиска задать следующий шаблон «3?????». Знак вопроса означает любой символ.

   Таким образом, программа будет искать все шестизначные числа, которые начинаются с цифры «3». То есть, в выдачу поиска попадут значения в диапазоне 300000 – 400000, что нам и требуется.

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

   Вводим выражение «3?????» в поле «Найти» и жмем на кнопку «Найти все».

3. После этого в нижней части окошка открываются результаты поисковой выдачи. Кликаем левой кнопкой мыши по любому из них. Затем набираем комбинацию клавиш Ctrl+A. После этого выделяются все результаты поисковой выдачи и одновременно выделяются элементы в столбце, на которые данные результаты ссылаются.
4. После того, как элементы в столбце выделены, не спешим закрывать окно «Найти и заменить». Находясь во вкладке «» в которую мы переместились ранее, переходим на ленту к блоку инструментов «Шрифт». Кликаем по треугольнику справа от кнопки «Цвет заливки». Открывается выбор различных цветов заливки. Выбираем тот цвет, который мы желаем применить к элементам листа, содержащим величины менее 400000 рублей.
5. Как видим, все ячейки столбца, в которых находятся значения менее 400000 рублей, выделены выбранным цветом.
6. Теперь нам нужно окрасить элементы, в которых располагаются величины в диапазоне от 400000 до 500000 рублей. В этот диапазон входят числа, которые соответствуют шаблону «4??????». Вбиваем его в поле поиска и щелкаем по кнопке «Найти все», предварительно выделив нужный нам столбец.
7. Аналогично с предыдущим разом в поисковой выдаче производим выделение всего полученного результата нажатием комбинации горячих клавиш CTRL+A. После этого перемещаемся к значку выбора цвета заливки. Кликаем по нему и жмем на пиктограмму нужного нам оттенка, который будет окрашивать элементы листа, где находятся величины в диапазоне от 400000 до 500000.
8. Как видим, после этого действия все элементы таблицы с данными в интервале с 400000 по 500000 выделены выбранным цветом.
9. Теперь нам осталось выделить последний интервал величин – более 500000. Тут нам тоже повезло, так как все числа более 500000 находятся в интервале от 500000 до 600000. Поэтому в поле поиска вводим выражение «5?????» и жмем на кнопку «Найти все». Если бы были величины, превышающие 600000, то нам бы пришлось дополнительно производить поиск для выражения «6?????» и т.д.
10. Опять выделяем результаты поиска при помощи комбинации Ctrl+A. Далее, воспользовавшись кнопкой на ленте, выбираем новый цвет для заливки интервала, превышающего 500000 по той же аналогии, как мы это делали ранее.
11. Как видим, после этого действия все элементы столбца будут закрашены, согласно тому числовому значению, которое в них размещено. Теперь можно закрывать окно поиска, нажав стандартную кнопку закрытия в верхнем правом углу окна, так как нашу задачу можно считать решенной.
12. Но если мы заменим число на другое, выходящее за границы, которые установлены для конкретного цвета, то цвет не поменяется, как это было в предыдущем способе. Это свидетельствует о том, что данный вариант будет надежно работать только в тех таблицах, в которых данные не изменяются.

Урок: Как сделать поиск в Экселе

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

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

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

Еще статьи по данной теме:

Источник: //lumpics.ru/color-cell-depending-value-excel/

Обучение условному форматированию в Excel с примерами

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

Инструмент «Условное форматирование» находится на главной странице в разделе «Стили».

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

Сравним числовые значения в диапазоне Excel с числовой константой. Чаще всего используются правила «больше / меньше / равно / между». Поэтому они вынесены в меню «Правила выделения ячеек».

Введем в диапазон А1:А11 ряд чисел:

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

Введем в левое поле число 15. В правое – способ выделения значений, соответствующих заданному условию: «больше 15». Сразу виден результат:

Выходим из меню нажатием кнопки ОК.



Сравним значения диапазона А1:А11 с числом в ячейке В2. Введем в нее цифру 20.

Выделяем исходный диапазон и открываем окно инструмента «Условное форматирование» (ниже сокращенно упоминается «УФ»). Для данного примера применим условие «меньше» («Правила выделения ячеек» – «Меньше»).

В левое поле вводим ссылку на ячейку В2 (щелкаем мышью по этой ячейке – ее имя появится автоматически). По умолчанию – абсолютную.

Результат форматирования сразу виден на листе Excel.

Значения диапазона А1:А11, которые меньше значения ячейки В2, залиты выбранным фоном.

Зададим условие форматирования: сравнить значения ячеек в разных диапазонах и показать одинаковые. Сравнивать будем столбец А1:А11 со столбцом В1:В11.

Выделим исходный диапазон (А1:А11). Нажмем «УФ» – «Правила выделения ячеек» – «Равно». В левом поле – ссылка на ячейку В1. Ссылка должна быть СМЕШАННАЯ или ОТНОСИТЕЛЬНАЯ!, а не абсолютная.

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

В нашем примере в момент вызова инструмента была активна ячейка А1. Ссылка $B1. Следовательно, Excel сравнивает значение ячейки А1 со значением В1. Если бы мы выделяли столбец не сверху вниз, а снизу вверх, то активной была бы ячейка А11. И программа сравнивала бы В1 с А11.

Сравните:

Чтобы инструмент «Условное форматирование» правильно выполнил задачу, следите за этим моментом.

Проверить правильность заданного условия можно следующим образом:

1. Выделите первую ячейку диапазона с условным форматированим.
2. Откройте меню инструмента, нажмите «Управление правилами».

В открывшемся окне видно, какое правило и к какому диапазону применяется.

Условное форматирование – несколько условий

Исходный диапазон – А1:А11. Необходимо выделить красным числа, которые больше 6. Зеленым – больше 10. Желтым – больше 20.

• 1 способ. Выделяем диапазон А1:А11. Применяем к нему «Условное форматирование». «Правила выделения ячеек» – «Больше». В левое поле вводим число 6. В правом – «красная заливка». ОК. Снова выделяем диапазон А1:А11. Задаем условие форматирования «больше 10», способ – «заливка зеленым». По такому же принципу «заливаем» желтым числа больше 20.
• 2 способ. В меню инструмента «Условное форматирование выбираем «Создать правило».

Заполняем параметры форматирования по первому условию:

Нажимаем ОК. Аналогично задаем второе и третье условие форматирования.

Обратите внимание: значения некоторых ячеек соответствуют одновременно двум и более условиям. Приоритет обработки зависит от порядка перечисления правил в «Диспетчере»-«Управление правилами».

То есть к числу 24, которое одновременно больше 6, 10 и 20, применяется условие «=$А1>20» (первое в списке).

Условное форматирование даты в Excel

Выделяем диапазон с датами.

Применим к нему «УФ» – «Дата».

В открывшемся окне появляется перечень доступных условий (правил):

Выбираем нужное (например, за последние 7 дней) и жмем ОК.

Красным цветом выделены ячейки с датами последней недели (дата написания статьи – 02.02.2016).

Условное форматирование в Excel с использованием формул

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

Есть столбец с числами. Необходимо выделить цветом ячейки с четными. Используем формулу: =ОСТАТ($А1;2)=0.

Выделяем диапазон с числами – открываем меню «Условного форматирования». Выбираем «Создать правило». Нажимаем «Использовать формулу для определения форматируемых ячеек». Заполняем следующим образом:

Для закрытия окна и отображения результата – ОК.

Условное форматирование строки по значению ячейки

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

Таблица для примера:

Необходимо выделить красным цветом информацию по проекту, который находится еще в работе («Р»). Зеленым – завершен («З»).

Выделяем диапазон со значениями таблицы. Нажимаем «УФ» – «Создать правило». Тип правила – формула. Применим функцию ЕСЛИ.

Порядок заполнения условий для форматирования «завершенных проектов»:

Обратите внимание: ссылки на строку – абсолютные, на ячейку – смешанная («закрепили» только столбец).

Аналогично задаем правила форматирования для незавершенных проектов.

В «Диспетчере» условия выглядят так:

Получаем результат:

Когда заданы параметры форматирования для всего диапазона, условие будет выполняться одновременно с заполнением ячеек. К примеру, «завершим» проект Димитровой за 28.01 – поставим вместо «Р» «З».

«Раскраска» автоматически поменялась. Стандартными средствами Excel к таким результатам пришлось бы долго идти.

Источник: //exceltable.com/formatirovanie/uslovnoe-formatirovanie-v-excel

Excel выделение цветом ячеек по условиям, Эксель условное форматирование

Нужно выделить повторяющиеся значения в столбце? Надо выделить первые 5 максимальных ячеек? Необходимо сделать термальную шкалу для наглядности (цвет меняется в зависимости от увеличения/уменьшения значения ячеек)? В Excel выделение цветом ячеек по условиям можно сделать очень быстро. В Excel за выделение цветом ячеек отвечает специальная  функция «Условное форматирование». Настоятельно рекомендую! Подробнее читаем ниже:

Условное форматирование, где найти?

Для начала, на ленте задач в главном меню найдите раздел Стили и нажмите на кнопку Условное форматирование,

При нажатии откроется меню, с разными вариантами этого редактирования. Как вы видите возможностей здесь действительно много.

Теперь подробнее о самых полезных:

Excel выделение цветом ячеек по условиям. Простые условия

Для этого зайдите в пункт Правила выделения ячеек. Если к примеру, вам нужно выделить все ячейки больше 100, нажмите кнопку Больше. В окне:

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

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

Чтобы выделить все повторяющиеся значения выберите соответствующее меню (см. картинку в начале статьи). Далее снова появиться окошко с форматированием. Настройте как вам удобно.

Что делать если необходимо найти повторения по двум и более столбцам, например когда ФИО в разных столбцах? Сделайте еще один столбец и объедините значения формулой =СЦЕПИТЬ(), т.е.

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

Важно понимать, что если порядок слов будет различаться Excel сочтет такие строки неповторяющимися.

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

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

Построение термальной диаграммы и гистограммы

Классная функция для визуализации данных — термальная/температурная диаграмма. Суть в том что, в зависимости от величины значения в столбце или строке, ячейка подсвечивается определенным оттенком цвета. Таблицы воспринимаются гораздо лучше на глаз, а принимать решение становится проще. Ведь один из лучших анализаторов — это наш глаз и соответственно мозг, а не машина!

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

Рекомендую для презентаций и аналитики — гистрограммы в ячейках и термальные диаграммы основа простой визуализации при помощи Excel.

Выделение цветом ячеек содержащих определенный текст

Очень часто нужно найти ячейки, которые содержат определенный набор символов, можно конечно воспользоваться функцией = ПОИСК(), но проще и быстрее применить в условное форматирование, пройдите — Правила отбора ячеек — Текст содержит (см. картинку 2).

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

Excel выделение цветом. Фильтр по цвету

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

Проверка условий форматирования

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

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

Тогда у вас может быть множество условий с цветом. Я сам видело более 3 тысяч условий — тормозил файл безбожно. Подробнее о тормозах Excel и их причинах читайте здесь.

Эта статья помогла не одной сотне людей 😉

Я рад, что узнали о том, что такое выделение цветом ячеек по условиям в Excel.

Поделитесь нашей статьей в ваших соцсетях:

Источник: //excelworks.ru/2015/10/05/vyidelenie-tsvetom/

VBA Excel. Цвет ячейки (заливка, фон)

Заливка ячейки цветом в VBA Excel. Фон ячейки. Свойства .Interior.Color и .Interior.ColorIndex. Цветовая модель RGB. Стандартная палитра из 56 цветов. Предопределенные константы.

Свойство .Interior.Color объекта Range

Начиная с Excel 2007 основным способом заливки диапазона или отдельной ячейки цветом (зарисовки, добавления, изменения фона) является использование свойства .Interior.Color объекта Range путем присваивания ему значения цвета в виде десятичного числа от 0 до 16777215 (всего 16777216 цветов).

Заливка ячейки цветом в VBA Excel

Пример кода 1:

Sub ColorTest1() Range(“A1”).Interior.Color = 31569 Range(“A4:D8”).Interior.Color = 4569325 Range(“C12:D17”).Cells(4).Interior.Color = 568569 Cells(3, 6).Interior.Color = 12659 End Sub

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

Есть один интересный нюанс: если присвоить свойству .Interior.Color отрицательное значение от -16777215 до -1, то цвет будет соответствовать значению, равному сумме максимального значения палитры (16777215) и присвоенного отрицательного значения. Например, заливка всех трех ячеек после выполнения следующего кода будет одинакова:

Sub ColorTest11() Cells(1, 1).Interior.Color = -12207890 Cells(2, 1).Interior.Color = 16777215 + (-12207890) Cells(3, 1).Interior.Color = 4569325 End Sub

Проверено в Excel 2016.

Вывод сообщений о числовых значениях цветов

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

Пример кода 2:

Sub ColorTest2() MsgBox Range(“A1”).Interior.Color MsgBox Range(“A4:D8”).Interior.Color MsgBox Range(“C12:D17”).Cells(4).Interior.Color MsgBox Cells(3, 6).Interior.Color End Sub

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

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

В VBA Excel есть предопределенные константы часто используемых цветов для заливки ячеек:

Присваивается цвет ячейке предопределенной константой в VBA Excel точно так же, как и числовым значением:

Пример кода 3:

Range(“A1”).Interior.Color = vbGreen

Цветовая модель RGB

Цветовая система RGB представляет собой комбинацию различных по интенсивности основных трех цветов: красного, зеленого и синего. Они могут принимать значения от 0 до 255. Если все значения равны 0 – это черный цвет, если все значения равны 255 – это белый цвет.

Выбрать цвет и узнать его значения RGB можно с помощью палитры Excel:

Палитра Excel

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

Пример кода 4:

Range(“A1”).Interior.Color = RGB(100, 150, 200)

Свойство .Interior.ColorIndex объекта Range

До появления Excel 2007 существовала только ограниченная палитра для заливки ячеек фоном, состоявшая из 56 цветов, которая сохранилась и в настоящее время. Каждому цвету в этой палитре присвоен индекс от 1 до 56. Присвоить цвет ячейке по индексу или вывести сообщение о нем можно с помощью свойства .Interior.ColorIndex:

Пример кода 5:

Range(“A1”).Interior.ColorIndex = 8 MsgBox Range(“A1”).Interior.ColorIndex

Просмотреть ограниченную палитру для заливки ячеек фоном можно, запустив в VBA Excel простейший макрос:

Пример кода 6:

Sub ColorIndex() Dim i As Byte For i = 1 To 56 Cells(i, 1).Interior.ColorIndex = i Next End Sub

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

Готовую стандартную палитру из 56 цветов можете посмотреть здесь.

Источник: //vremya-ne-zhdet.ru/vba-excel/tsvet-yacheyki-zalivka-fon/

Условное форматирование (2 урок)

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

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

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

Для этого мы рассмотрим пример УФ (условного форматирования) по значению другой ячейки. Для этого используем имеющийся у нас набор случайных чисел в диапазоне A1: A15.

А в ячейке B3 ставим к примеру число «25», которое и послужит основанием для создания условий форматирования.

Затем один раз кликаем левой кнопкой мыши на исходное число «25», на основании которого делаем анализ. То есть ячейку B3 и ссылка автоматически вставляется в левую часть открытого окна «больше»

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

Вот и все

Сравнение двух баз данных

Усложняем задачу. У нас имеется две разных базы с данными. Нам необходимо найти в них одинаковые значения. Для примера используем данные диапазона A1: A15. А в диапазон B1: B15 забиваем ряд случайных чисел

Выделяем исходный диапазон чисел A1: A15. Причем выделяем сверху – вниз (это имеет значение, об этом я скажу чуть ниже). В открывшемся окне инструмента «условное форматирование» на панели инструментов, выбираем «правило выделения ячеек» — «равно»

и в левой части выпавшего окна вставляем относительную ссылку в диапазоне B1: B15. А точнее, в нашем случае ячейка В1.

Для тех, кто не знает, что такое «относительная ссылка» я сделаю пояснение чуть позже. А пока делаем так же как в предыдущем примере. То есть кликаем один раз в ячейке B1. По умолчанию в окне ставится значение абсолютной ссылки. Удаляем во вставленной ссылке в левой части открытого окна «равно» знак «$» перед номером ячейки и номером столбца.. Было так «$B$1»

стало так «B1».

Ссылка наша стала относительной. Можно правда оставить ссылку смешанной. Но не буду Вас запутывать, делайте пока как показано в примере.

Ссылки в Excel

А теперь немного пояснения. Ссылка в Excel это указание адреса на ячейку (диапазон) зашифрованное в формулу. Ссылки в Excel могут быть относительными, абсолютными и смешанными. Относительная ссылка – это значение формулы с изменяемым адресом относительно нового места. То есть к примеру, у нас в ячейке A16 стоит вот такая формула для подсчета суммы в диапазоне A1:A15

А нам хотелось бы применить такие же параметры для диапазона B1:B15. В этом случае мы просто копируем формулу из A16 и вставляем в B16.

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

Абсолютная ссылка

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

Теперь мы поняли A1 – это относительная ссылка, а $A$1 – это абсолютная ссылка. Я думаю вы уже догадались, что такое смешанная ссылка. Это когда что-то одно, или адрес строки или адрес столбца являются относительными, а что-то абсолютными. Применение этих ссылок это, так же отдельная тема.

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

Ссылку в этом случае мы должны указать на В1. Только в этом случае Excel выполнит правильный сравнительный анализ

Сравнительный анализ

Если мы выделили диапазон снизу-вверх, а ссылку привязали к ячейке В1, никакого анализа мы не получим. Проверить правильность заданного нами правила очень просто. Выделяем любую ячейку диапазона с условным форматированием. Далее заходим в меню инструмента, находим «Управление правилами» и кликаем.

//www.youtube.com/watch?v=CoWFGjJEmCg

Если правило задано нами правильно, то в окне «диспетчера правил условного форматирования» мы видим какая ссылка привязана к нашему диапазону

Это проверочное действие. Ну а то что функция у нас работает правильно, мы видим по выделенной другим цветом информации в диапазоне  A1: A15. Следовательно, запомнили ссылку всегда ставим напротив активной ячейки, поэкспериментируйте и Вы поймете. Следите пожалуйста за этим правилом при использовании данной функции условного форматирования. И так подведем итог.

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

Ну а на следующем уроке мы будем изучать «условное форматирование нескольких условий»
С уважением                                      

Спасибо Вам, что поделились статьей в социальных сетях!

Источник: //vseochenprosto.ru/uslovnoe-formatirovanie-2-urok/

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

Не все фирмы покупают специальные программы для ведения дел. Многие пользуются MS Excel, ведь эта хорошо приспособлена для больших информационных баз. Практика показала, что дальше заполнения таблиц доходит редко.

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

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

Где находится условное форматирование

Как в экселе менять цвет ячейки в зависимости от значения – да очень просто и быстро. Для выделения ячеек цветом предусмотрена специальная функция «Условное форматирование», находящаяся на вкладке «»:

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

Правила выделения ячеек

С помощью этого набора инструментов делают следующие выборки:

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

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

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

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

Правила отбора первых и последних значений

Рассмотрим вторую группу функций «Правила отбора первых и последних значений». В ней вы сможете:

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

Гистограммы

Если заливка ячейки цветом вас не устраивает – применяйте инструмент «Гистограмма». Предлагаемая окраска легче воспринимается на глаз в большом объеме информации, функциональные правила подстраиваются под требования пользователя.

Цветовые шкалы

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

Наборы значков

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

Создание, удаление и управление правилами

Функция «Создать правило» полностью дублирует «Другие правила» из перечисленных выше, создает выборку изначально по требованию пользователя.

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

Вызывает интерес инструмент «Управление правилами» – своеобразная история создания и изменения проведенных форматирований. Меняйте подборки, делайте правила неактивными, возвращайте обратно, чередуйте порядок применения. Для работы с большим объемом информации это очень удобно.

Отбор ячеек по датам

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

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

Установите числовой формат «Дата» и выберите его тип по своему усмотрению.

Для отбора нужных дат применяем такую последовательность действий:

• выделяем столбцы с датами (в нашем случае за январь);
• находим инструмент «Условное форматирование»;
• в «Правилах выделения ячеек» выбираем пункт «Дата»;
• в правой части форматирования открываем выпадающее окно с правилами;
• выбираем подходящее правило (на примере выбраны даты за предыдущий месяц);
• в левом поле устанавливаем готовый цветовой подбор «Желтая заливка и темно-желтый текст»
• выборка окрасилась, жмем «ОК».

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

Выделение цветом столбца по условию

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

Нам необходимо пометить синим цветом тех поставщиков, у которых мы купили товара на сумму большую, чем 100 000 рублей. Чтобы сделать такую выборку воспользуемся следующим алгоритмом действий:

• выделяем столбец с январскими закупками;
• кликаем инструмент «Условное форматирование»;
• переходим в «Правила выделения ячеек»;
• пункт «Больше…»;
• в правой части форматирования устанавливаем сумму 100 000 рублей;
• в левом поле переходим на вкладку «Пользовательский формат» и выбираем синий цвет;
• необходимая выборка окрасилась в синий цвет, жмем «ОК».

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

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

Источник: //FreeSoft.ru/blog/kak-v-excel-vydelit-yacheyku-tsvetom-pri-opredelennom-uslovii-primery-i-metody