Как изменить фон выделенной области ячеек excel

2 способа изменить фон в Excel

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

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

Динамическое изменение цвета фона клетки

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

Решение : Для этой задачи предусмотрена функция Excel «условное форматирование», чтобы окрашивать клетки с числами, больше чем X, меньше чем Y или в диапазоне между X и Y.

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

Примечание: Скриншот был сделан в программе 2010 версии. Но это не влияет ни на что, поскольку последовательность действий одинаковая, независимо от того, какую версию — последнюю, или нет — использует человек.

Итак, что нужно сделать (пошагово):

1. Выбрать клетки, в которых следует отредактировать оттенок. Например, диапазон $B$2:$H$10 (наименования колонок и первая колонка, в которой приводятся названия штатов, исключены из выборки).

2. Нажать на «Главная» в группе «Стили» . Там будет находиться пункт «Условное форматирование» . Там же нужно выбрать пункт «Новое правило» . В английской версии Excel последовательность шагов следующая: “Home”, “Styles group”, “Conditional Formatting > New Rule» .

3. В открывшемся окне следует поставить галочку «Форматировать только ячейки, которые содержат» (“Format only cells that contain” в английской версии).

4. Внизу этого окна под надписью «Форматировать только ячейки, для которых выполняется следующее условие» (Format only cells with) можно назначить правила, по которым и будет выполняться форматирование. Мы выбрали формат для указанного значения в клетках, которое должно превышать 3.7, как видно со скриншота:

5. Далее следует кликнуть по кнопке «Формат» . Появится окно, где слева находится область выбора фонового цвета. Но перед этим следует открыть вкладку “Заполнить” ( «Fill») . В данном случае это красный оттенок. После этого следует кликнуть по кнопке «ОК».

6. Затем вы возвратитесь в окно «Новое правило форматирования» , но уже внизу этого окна можно предварительно посмотреть, как будет выглядеть эта клетка. Если все отлично, нужно нажать на кнопку «ОК».

В результате, получится что-то вроде этого:

Далее нам необходимо добавить еще одно условие, то есть, поменять фон клеток со значениями меньше 3.45, на зеленый цвет. Для выполнения этой задачи необходимо снова кликнуть на «Новое правило форматирования» и повторить описанные выше шаги, только условие нужно поставить как «меньше чем, или эквивалентно» (в английской версии «less than or equal to», а далее прописать значение. В конце нужно нажать кнопку «ОК».

Теперь таблица отформатирована таким способом.

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

Рекомендация : Если будет необходимость, можно воспользоваться похожим методом форматирования, редактируя не фон, а шрифт. Для этого в окне форматирования, который появился на пятом этапе, нужно выбрать вкладку «Font» и руководствоваться подсказками, приводимыми в окне. Там все интуитивно понятно, и разобраться может даже новичок.

В результате, получится такая табличка:

Как оставить цвет ячейки таким же, даже если меняется значение?

Задача : Вам необходимо окрасить фон так, чтобы он не менялся никогда, даже если в будущем фон изменится.

Решение : найдите все клетки с определенным числом, используя функцию Excel “Найти все” «Find All» или дополнением “Выбрать особые ячейки” («Select Special Cells»), а после этого отредактировать формат ячейки, пользуясь функцией “Форматировать ячейки” («Format Cells»).

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

Выбрать все клетки, которые содержат определенное условие

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

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

Введите нужные значения и кликните на «Найти все» .

Подсказка : Можно кликнуть на кнопку «Параметры» справа, чтобы воспользоваться некоторыми дополнительными настройками: где искать, как просматривать, учитывать ли большие и маленькие буквы, и так далее. Также можно прописывать дополнительные символы, например, звездочку (*), чтобы найти все строки, содержащие эти значения. Если использовать знак вопроса, можно отыскать какой угодно единичный символ.

В нашем прошлом примере, если необходимо найти все котировки на топливо между 3,7 и 3,799 долларов, мы можем уточнить наш поисковой запрос.

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

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

“Бочка меда” здесь обнаруживается благодаря тому, что есть другой инструмент, который поможет в выполнении таких сложных задач. Называется он «Select Special Cells». Это дополнение (которое нужно устанавливать к Excel отдельн), которое поможет:

• найти все значения в определенном диапазоне, например, между -1 и 45,
• получить максимальное или минимальное значение в колонке,
• найти строку или диапазон,
• отыскать клетки по окраске фона и многое другое.

После установки дополнения просто нажмите кнопку “Выбрать по значению” («Select by Value») и потом уточните поисковый запрос в окне аддона. В нашем примере мы ищем числа больше 3,7. Нажмите на “Выбрать” («Select»), и через секунду получите результат наподобие такого:

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

Изменение фона выбранных клеток через окно «Форматировать ячейки»

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

Чтобы сделать это, необходимо открыть окно «Формат ячеек» , нажав на клавишу Ctrl + 1 (также можно нажать правой кнопкой мышки по выделенным клеткам и кликнуть левой кнопки мыши по пункту «форматирование ячеек») и настроить такое форматирование, которое необходимо.

Мы выберем оранжевый оттенок, но можно выбрать любой другой.

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

В результате, получится такая таблица:

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

Редактирование цвета фона для особенных ячеек (пустых или с ошибками при написании формулы)

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

Применение формулы для редактирования фона

Здесь окрас ячейки будет редактироваться автоматически, исходя из ее значения. Этот метод во многом помогает пользователям и востребован в 99% ситуаций.

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

1. На вкладке «Главная» необходимо кликнуть на «Условное форматирование» –> «Новое правило» (так, как в шаге 2 первого раздела «Динамическое изменение цвета фона».

2. Далее необходимо выбрать пункт «Использовать формулу для определения…» .

3. Ввести формулу =IsBlank() (ЕПУСТО в русскоязычной версии), если требуется отредактировать фон пустой клетки, или =IsError() (ЕОШИБКА в русскоязычной версии), если надо найти клетку, где есть ошибочно написанная формула. Поскольку в данном случае нам необходимо отредактировать пустые ячейки, вводим формулу =IsBlank() , а потом размещаем курсор между круглыми скобками и нажимаем на кнопку рядом с полем ввода формулы. После этих манипуляций следует выбрать вручную диапазон клеток. Кроме этого, можно указать диапазон самостоятельно, например, =IsBlank(B2:H12) .

4. Кликнуть по кнопке «Форматировать» и выбрать подходящий фоновый цвет и сделать все так, как описано в пункте 5 раздела «Динамическое изменение цвета фона клетки», а потом нажать «ОК». Там же можно посмотреть, какой будет цвет клетки. Окно будет выглядеть приблизительно так.

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

Статическое изменение фонового цвета специальных ячеек

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

Если необходимо перманентное изменение специальных клеток (пустых или содержащих ошибки), следуйте этой инструкции:

1. Выберите ваш документ или несколько ячеек и нажмите на F5 для открытия окна «Переход», а потом нажмите на кнопку «Выделить» .
2. В открывшемся диалоговом окне выберите кнопку «Blanks» или «Пустые ячейки» (исходя из версии программы — рус. или англ.) для выделения пустых клеток.
3. Если вам следует подсветить клетки, имеющие формулы с ошибками, следует выбрать пункт «Формулы» и оставить единственный флажок возле слова «Ошибки». Как следует из скриншота выше, выделять можно клетки по любым параметрам, и каждая из описанных настроек доступна при необходимости.
4. Напоследок необходимо изменить цвет фона выделенных ячеек или кастомизировать их любым другим способом. Для этого нужно воспользоваться методом, описанным выше.

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

Как выжать максимум из Excel?

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

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

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

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

Как в Excel закрасить ячейки, строку или столбец по условию?

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

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

Выделение строки.

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

Прежде всего выделяем при помощи мыши весь диапазон интересующих нас данных – A2:D21. Шапку таблицы выделять не нужно. Затем действуем по уже отработанной схеме: вызываем меню функции и выбираем последний пункт – «Использовать формулу для определения форматируемых ячеек» (1). Далее записываем выражение (2):

Мы должны закрасить вторую строку таблицы в зависимости от значения в С2. Здесь есть маленькая хитрость.

Обратите внимание, что абсолютная ссылка (знак $) установлена здесь только на столбец С. То есть, мы проверяем на условие «Бразилия» в выделенном нами диапазоне все позиции в этом столбце, то есть С2, С3, C4 и так далее. А вот закрашивать будем всю строку, так как ранее выделена была вся таблица. Для этого выбираем вариант оформления (3): цвет фона или шрифта, либо оба.

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

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

Выделение столбца.

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

Тем не менее, давайте рассмотрим пример с выделением по условию столбцов таблицы.

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

Как и в предыдущем примере, определим для начала диапазон, который мы будем форматировать: =$B$3:$S$7. И вновь будем использовать формулу (2) для определения условия.

Функция ДЕНЬНЕД позволяет определить номер дня недели по указанной дате. Цифра 2 означает, что используется привычный нам порядок, когда первый день недели – это понедельник.

Таким образом, если номер окажется больше 5 (то есть, это будет суббота или воскресенье), то необходимо применить указанный нами формат (3) и закрасить выходной день.

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

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

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

Выделение через строку.

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

Итак, возьмем для примера небольшую таблицу.

Выделим диапазон A1:D18.

Затем создаем новое правило при помощи формулы

В чем ее смысл? Если номер, полученный при помощи функции СТРОКА(), делится без остатка на 2, то значит у нее чётный номер, и к ней следует применить правило форматирования. Если же номер не делится на 2 без остатка, то это нечетная. Ее мы оставляем без изменений.

В результате получилась «полосатая» таблица по принципу «четный-нечетный».

Рекомендации

При вводе статуса работ важно не допустить опечатку. Если вместо слово Завершена, например, пользователь введет Завершено, то Условное форматирование не сработает.

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

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

Закрасить группу строк.

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

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

1. Счетчик в начале нужно установить в ноль. Для этого служит выражение (СТРОКА()-2). Поскольку начинаем со второй, то обнуляем счетчик, вычитая 2.
2. Далее нужно определить, к какой по счету группе относится текущее местоположение курсора. Результат п.1 делим на 3.
3. Отсекаем дробную часть при помощи функции ЦЕЛОЕ и получаем порядковый номер группы: ЦЕЛОЕ((СТРОКА()-2)/3).
4. Добавляем 1, поскольку результатом для первой группы будет число меньше 1. А нужно, чтобы отсчет групп начинался с 1.
5. Затем действуем по методике, отработанной в предыдущем примере: производим действия только с нечетными группами. Для этого используем функцию ОСТАТ с аргументом 2. То есть, находим остаток от деления на 2. Если число четное, то остаток будет равен нулю. Ноль равносилен результату ЛОЖЬ, поэтому с такими группами ничего не делаем. Если число нечетное, остаток от деления на 2 будет равен 1, что равноценно ИСТИНА. И вот тут-то мы и закрасим эту группу.

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

Аналогично можно разбить на группы по 4 строки. Тогда в формуле выше просто замените цифру 3 на 4. И все будет работать.

А если в шапке вашей таблицы больше, чем одна строка, просто замените -2 на большую цифру, соответственно высоте шапки таблицы.

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

Автоматическое заполнение ячеек датами

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

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

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

Автоматическое заполнение ячеек актуальными датами

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

Как работает формула для автоматической генерации уходящих месяцев?

На рисунке формула возвращает период уходящего времени начиная даты написания статьи: 17.09.2017. В первом аргументе в функции DATA – вложена формула, которая всегда возвращает текущий год на сегодняшнюю дату благодаря функциям: ГОД и СЕГОНЯ. Во втором аргументе указан номер месяца (-1). Отрицательное число значит, что нас интересует какой был месяц в прошлом времени. Пример условий для второго аргумента со значением:

• 1 – значит первый месяц (январь) в году указанном в первом аргументе;
• 0 – это 1 месяца назад;
• -1 – это 2 мес. назад от начала текущего года (то есть: 01.10.2016).

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

Далее перейдите в ячейку C1 и введите следующую формулу:

Как видно теперь функция ДАТА использует значение из ячейки B1 и увеличивает номер месяца на 1 по отношению к предыдущей ячейки. В результате получаем 1 – число следующего месяца.

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

Выделите диапазон ячеек B1:L1 и выберите инструмент: «ГЛАВНАЯ»-«Ячейки»-«Формат ячеек» или просто нажмите комбинацию клавиш CTRL+1. В появившемся диалоговом окне, на вкладке «Число», в разделе «Числовые форматы:» выберите опцию «(все форматы)». В поле «Тип:» введите значение: МММ.ГГ (обязательно буквы в верхнем регистре). Благодаря этому мы получим укороченное отображение значения дат в заголовках регистра, что упростит визуальный анализ и сделает его более комфортным за счет лучшей читабельности.

Обратите внимание! При наступлении января месяца (D1), формула автоматически меняет в дате год на следующий.

Вставляем отделяющие линии между группами строк.

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

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

Для этого сначала выделим весь наш диапазон с данными.

Внимание! Первую шапку таблицы не выделяем, начинаем с данных!

В нашем случае, выделяем A3:G33.

Затем далее действуем по уже отработанной схеме. В меню условного форматирования выбираем использование формулы (1). Далее записываем само правило:

Иначе говоря, мы проверяем, равна ли наша текущая дата предыдущей. Если не равна, значит, мы перешли к новому дню. Соответственно наше текущее положение нужно выделить. Выбираем формат (3). Тип границы – линия (4). Она будет использоваться по верхней границе (5).

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

Условное форматирование для сравнения двух столбцов.

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

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

Можно использовать специальный пункт вкладки «Условное форматирование» — «Повторяющиеся значения».

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

Выделение совпадений двух столбцов построчно.

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

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

Выберите, в каких ячейках вы будете отмечать совпадения – в первой или во второй таблице. Я выделил B3:B25. То есть, в первой таблице мы закрасим ячейки, которые дублируются во второй таблице.

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

Как найти и закрасить совпадения в нескольких столбцах.

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

Давайте закрасим цветом те ячейки в столбце B, которые хотя бы однажды встречаются в G,H и I.

Диапазон форматирования – B3:B25. Выделяем его и в меню – «Создать правило» выбираем «Использовать формулу…»

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

Последовательно двигаемся сверху вниз и сравниваем каждую ячейку колонки B с находящимися в той же горизонтали значениями в G,H и I.

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

Но если столбцов будет не 3, а, предположим, 10? Формула станет слишком громоздкой. Ведь придется указать 10 критериев совпадения.

Есть более простой способ. Изменим правило форматирования и используем функцию СЧЁТЕСЛИ:

СЧЁТЕСЛИ определяет, как часто определенное значение встречается в диапазоне. Считаем, сколько раз значение из B3 встречается в G,H и I таблицы, то есть в $G3:$I3. Если будет более одного совпадения, то срабатывает правило.Функция возвращает 1. А 1 в логическом выражении соответствует ИСТИНА, 0 — ЛОЖЬ. То есть, если счет равен нулю, то в текущей позиции нашего столбца содержится уникальное значение, которое больше нигде в диапазоне поиска не встречается. Согласитесь, так гораздо удобнее, чем писать множество однотипных критериев.

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

Вот это новое правило:

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

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

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

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

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

Комментарии:

1. Svetlana — 27.06.2015 21:28

наконец-то узнала, как это можно сделать!

Виктор — 14.04.2016 17:23

Здравствуйте, а можно сделать условное форматирование столбца А с фразами по условию «Текст —- содержит» по нескольким словам, а лучше по столбцу В, состоящего из слов?

salam — 19.05.2016 16:24

Подскажите как подсвечивать ячеку В2 при условии если ячейка А2 не пустая?

Федя — 16.11.2016 14:39

Как задать цвет определенному значению в одной ячейки, например — вожу 5 — она будет красным цветом, вожу 4 — она станет зелёным цветом

Оля — 03.05.2017 12:12

подскажите, как заливать в гамме одного цвета с разными оттенками в столбике, если напр., если 100% — зеленый, 95- зеленый но светлее, 75 — еще светлее и т.д. заранее спасибо

Используем цвет ячеек в Excel

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

1 Выделение ячейки цветом вручную

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

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

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

Выделите ячейки, которые надо подсвечивать. На вкладке «Главная» выберите «Условное форматирование->Правила выделения ячеек->Больше» (или другой пункт):

В появившемся окне можно ввести значение и стиль окрашивания. Например, для получения такого результата:

были выделены ячейки последнего столбца и к ним применено два условия (по очереди) — если значение более 300 — окрась зеленым, если меньше 240 — красным.

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

Также, интересный эффект можно получить с помощью цветовых шкал. Достаточно просто выделить диапазон и выбрать пункт «Условное форматирование->Цветовые шкалы». Если в ячейках записаны числа — они сами будут подсвечены в выбранных тонах — от минимального значения к максимальному. Результат может быть например таким:

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

Выделение ячеек цветом (цветная заливка).

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

Рассмотрим несколько способов заливки цветом нужных ячеек в программе Excel.

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

Инструмент заливки ячеек расположен на панели быстрого доступа во вкладке «Главная» в разделе «Шрифт» . Чтобы закрасить ячейку, следует ее выделить левой кнопкой мыши и выбрать нужный цвет на панели в разделе «Шрифт» > «Цвет заливки».

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

Чтобы окрасить ячейки в нужный цвет следует выполнить следующие действия:

1. Выделить ячейки левой кнопкой мыши;
2. Кликнуть по выделению правой кнопкой мыши;
3. В появившемся контекстном меню кликнуть по строчке «Формат ячеек…»;
4. В появившемся окне формы настроек следует выбрать вкладку «Заливка»;
5. На вкладке заливка Вы можете выбрать понравившийся Вам оттенок из предложенных или выбрать индивидуальный нажав на кнопку «Другие цвета…» ;
6. После нажатия кнопки «ОК» ячейки приобретут нужную заливку.

Третий способ – заливка, зависящая от наполнения ячеек (условная).

Заливка по условиям наполнения ячейки выполняется при помощи опции «Условное форматирование». Кнопка условного форматирования находится на вкладке «Главная».

Рассмотрим условное форматирование по следующим критериям:

Ячейки, имеющие значение меньше «5» залить зеленым цветом, выше «5» – красным.

Чтобы настроить условное форматирование ячеек следует выполнить следующие действия:

1. Выделить нужные ячейки левой кнопкой мыши;
2. Выбрать и нажать кнопку «Условное форматирование» во вкладке «Главная» панели быстрого доступа;
3. Выбрать правило форматирования — указать условие и цвет заливки. (для нашего примера следует создать два правила: 1) меньше пяти – зеленая заливка; 2) больше пяти — красная)
4. Нажать «Enter».

Добавить комментарий Отменить ответ

Этот сайт использует Akismet для борьбы со спамом. Узнайте, как обрабатываются ваши данные комментариев.