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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Пусть в диапазоне А6:С16 имеется таблица с перечнем работ, сроками выполнения и статусом их завершения (см. файл примера ).

Задача1 - текстовые значения

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

Решение1

Создадим небольшую табличку со статусами работ в диапазоне Е6:Е9 .

Выделим диапазон ячеек А7:С17 , содержащий перечень работ, и установим через меню Главная/ Цвет заливки фон заливки красный (предполагаем, что все работы изначально находятся в статусе Не начата ).

Убедимся, что выделен диапазон ячеек А7:С17 (А7 должна быть ). Вызовем команду меню Условное форматирование/ Создать правило / Использовать формулу для определения форматируемых ячеек .

• в поле «Форматировать значения, для которых следующая формула является истинной » нужно ввести =$C7=$E$8 (в ячейке Е8 находится значение В работе ). Обратите внимание на использоване ;
• нажать кнопку Формат ;
• выбрать вкладку Заливка ;
• выбрать серый цвет;
• Нажать ОК.

ВНИМАНИЕ : Еще раз обращаю внимание на формулу =$C7=$E$8 . Обычно пользователи вводят =$C$7=$E$8 , т.е. вводят лишний символ доллара.

Нужно проделать аналогичные действия для выделения работ в статусе Завершена . Формула в этом случае будет выглядеть как =$C7=$E$9 , а цвет заливки установите зеленый.

В итоге наша таблица примет следующий вид.

Чтобы быстро расширить правила Условного форматирования на новую строку в таблице, выделите ячейки новой строки (А17:С17 ) и нажмите . Правила будут скопированы в строку 17 таблицы.

Задача2 - Даты

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

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

Сначала создадим формулу для условного форматирования в столбцах В и E. Если формула вернет значение ИСТИНА, то соответствующая строка будет выделена, если ЛОЖЬ, то нет.

В столбце D создана =МАКС(($A7=$A$7:$A$16)*$B$7:$B$16)=$B7 , которая определяет максимальную дату для определенного сотрудника.

Примечание: Если нужно определить максимальную дату вне зависимости от сотрудника, то формула значительно упростится =$B7=МАКС($B$7:$B$16) и формула массива не понадобится.

Теперь выделим все ячейки таблицы без заголовка и создадим правило . Скопируем формулу в правило (ее не нужно вводить как формулу массива!).

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

Для этого используйте формулу =И($B23>$E$22;$B23<$E$23)

Для ячеек Е22 и Е23 с граничными датами (выделены желтым) использована $E$22 и $E$23. Т.к. ссылка на них не должна меняться в правилах УФ для всех ячеек таблицы.

Для ячейки В22 использована смешанная адресация $B23, т.е. ссылка на столбец В не должна меняться (для этого стоит перед В знак $), а вот ссылка на строку должна меняться в зависимости от строки таблицы (иначе все значения дат будут сравниваться с датой из В23 ).

Таким образом, правило УФ например для ячейки А27 будет выглядеть =И($B27>$E$22;$B27<$E$23) , т.е. А27 будет выделена, т.к. в этой строке дата из В27 попадает в указанный диапазон (для ячеек из столбца А выделение все равно будет производиться в зависимости от содержимого столбца В из той же строки - в этом и состоит "магия" смешанной адресации $B23).

А для ячейки В31 правило УФ будет выглядеть =И($B31>$E$22;$B31<$E$23) , т.е. В31 не будет выделена, т.к. в этой строке дата из В31 не попадает в указанный диапазон.

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

Как выделить одну строку?

Существует несколько способов для того чтобы выделить строку:

1. Можно в окне открытого листа кликнуть левой кнопкой мыши курсором-стрелкой один раз по номеру нужной строки;

2. также, в окне открытого листа, можно установить маркер выделения в любую ячейку нужной строки и использовать сочетание клавиш Shift+Space (пробел);

3. и наконец, можно установить маркер выделения в первую ячейку нужной строки и использовать сочетание клавиш Ctrl+Shift+Стрелка вправо. При этом, если в строке есть данные, то первое нажатие этого сочетания клавиш выделит часть строки с даными, а второе - уже всю строку.

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

1. Выделить первую строку нужного диапазона и не отпуская левую кнопку мыши протащить курсор-стрелку по номерам нужных строк;

2. выделить первую строку нужного диапазона и при нажатой клавише Shift кликнуть по номеру последней строки нужного диапазона;

3. кликнуть курсором ввода текста в адресном окне строки формул, вписать диапазон строк (например 10:20) и нажать клавишу Enter.

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

Для выборочного выделения нескольких строк необходимо кликать по номерам нужных строк курсором-стрелкой при нажатой клавише Ctrl.

Как выделить строки по условию?

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

Надстройка позволяет:

1. Одним кликом мыши вызывать диалоговое окно макроса прямо из панели инструментов Excel;

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

возможен ввод сразу нескольких текстовых значений разделенных знаком ";" (точка с запятой);

3. Выбирать одно из восьми условий для строк с искомым значением:

- совпадает с искомым значением;

- не совпадает с искомым значением;

- содержит мскомое значение;

- не содержит искомое значение;

- начинается с искомого значения;

- не начинается с искомого значения;

- заканчивается искомым значением;

- не заканчивается искомым значением.

4. При поиске текста учитывать либо не учитывать регистр (различать заглавные и строчные буквы);

5. При нахождении строки предусмотрена возможность выделять:

а) всю строку по ширине листа;

б) часть строки, ограниченную используемым диапазоном (от первой заполненной до последней заполненной ячейки) ;

в) часть строки, ограниченную выделенным (выбранным) диапазоном поиска.

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

Одним из основных вопросов при работе с таблицами считается, как выделить цветом ячейку в Е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 для выделения цвета ячейки позволяет решать широкий круг задач, например выделять ячейки определенным цветом:

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