Описание работы функции
Function = FILL COLORS (CELL) возвращает код цвета заливки выбранной ячейки. Обязательный аргумент:
- ЯЧЕЙКА — ссылка на ячейку, к которой вы хотите применить функцию.
Ниже приведен пример, демонстрирующий, как работает функция.
Обратите внимание, что функция не пересчитывается автоматически. Это связано с тем, что при изменении цвета заливки ячейки Excel формулы не пересчитываются. Для пересчета формулы нужно использовать сочетание клавиш Ctrl + Alt + F9
Пример использования
Поскольку заполнение ячеек значительно упрощает восприятие данных, почти все пользователи любят их использовать. Однако есть и большой недостаток: в стандартном функционале Excel нельзя выполнять операции на основе цвета заливки. Нельзя складывать ячейки определенного цвета, посчитать их количество, найти максимум и так далее.
С функцией COLORFILL все становится возможным. Например, «перетащите» определенную формулу с цветом заливки в соседний столбец и рассчитайте на основе числового кода ячейки.
При работе с таблицами первостепенное значение имеют отображаемые в них значения. Но его дизайн также является важной составляющей. Некоторые пользователи считают это второстепенным фактором и не обращают на него особого внимания. Но зря, ведь грамотно оформленная таблица — важное условие ее лучшего восприятия и понимания пользователями. Визуализация данных играет в этом особенно важную роль. Например, вы можете использовать инструменты визуализации для раскрашивания ячеек таблицы в зависимости от их содержимого. Давайте узнаем, как это можно сделать в Excel.
Процедура изменения цвета ячеек в зависимости от содержимого
Конечно, всегда приятно иметь хорошо оформленную таблицу, в которой ячейки окрашены по-разному в зависимости от содержимого. Но эта функция особенно актуальна для больших таблиц, содержащих значительный объем данных. В этом случае заполнение ячеек цветом значительно упростит пользователям навигацию по этому огромному объему информации, поскольку он, можно сказать, уже структурирован.
Можно попробовать раскрасить элементы листа вручную, но опять же, если стол большой, на это уйдет много времени. Кроме того, в таком наборе данных может сыграть роль человеческий фактор и будут допускаться ошибки. Не говоря уже о том, что таблица может быть динамической, и данные, которые она содержит, периодически и в больших количествах меняются. В этом случае ручное изменение цвета обычно становится нереальным.
Но выход есть. Условное форматирование применяется к ячейкам, которые содержат динамические (редактируемые) значения, и вы можете использовать инструмент «Найти и заменить» для статистических данных».
Способ 1: условное форматирование
Используя условное форматирование, вы можете установить определенные пределы значений, при которых ячейки будут окрашены в определенный цвет. Окрашивание будет происходить автоматически. Если значение ячейки из-за модификации превышает границу, этот элемент листа будет автоматически перекрашен.
Посмотрим, как работает этот метод, на конкретном примере. У нас есть таблица доходов компании, где данные разбиты по месяцам. Нам нужно выделить разными цветами те элементы, в которых сумма дохода меньше 400 000 рублей, от 400 000 до 500 000 рублей и превышает 500 000 рублей.
- Выберите столбец, содержащий информацию о доходах компании. Итак, перейдем к вкладке «Главная». Нажмите кнопку «Условное форматирование», расположенную на ленте в панели инструментов «Стили». В открывшемся списке выберите пункт «Управление правилами…».
Открывается окно управления правилами условного форматирования. В поле «Показать правила форматирования для» должно быть установлено значение «Текущий фрагмент». По умолчанию это именно то, что там должно быть указано, но на всякий случай проверьте и, если есть неточности, измените настройки согласно рекомендациям выше. Далее следует нажать кнопку «Создать правило…».
Откроется окно для создания правила форматирования. В списке типов правил выберите «Форматировать только те ячейки, которые содержат». В блоке описания правила в первом поле переключатель должен находиться в положении «Значения». Во втором поле установите переключатель в положение «Минус». В третьем поле укажите значение; элементы листа, содержащие значение ниже которого будут окрашены в определенный цвет. В нашем случае это значение будет 400000. Затем нажмите кнопку «Форматировать…».
Откроется окно формата ячейки. Перейдите на вкладку «Заливка». Выберите цвет заливки, которым мы хотим выделить ячейки, содержащие значение меньше 400000. Затем нажмите кнопку «ОК» внизу окна.
После этого действия мы снова будем перенаправлены в Диспетчер правил условного форматирования. Как видите, правило уже добавлено, но нам нужно добавить еще два. Затем снова нажмите кнопку «Создать правило…».
И снова мы в окне создания правила. Перейдите в раздел «Форматировать только содержащиеся ячейки». В первом поле этого раздела оставьте параметр «Значение ячейки», а во втором установите переключатель в положение «Между». В третьем поле нужно указать начальное значение диапазона, в котором будут форматироваться элементы листа. В нашем случае это число 400000. В четвертом мы указываем конечное значение этого интервала. Он составит 500 тысяч. Затем нажмите кнопку «Форматировать…».
В окне форматирования вернитесь на вкладку «Заливка», но на этот раз выберите другой цвет, затем нажмите кнопку «ОК».
Вернувшись в окно создания правила, также нажмите кнопку «ОК».
Как видите, мы уже создали два правила в диспетчере правил. Итак, осталось создать третью. Нажмите кнопку «Создать правило».
В окне создания правила вернитесь в раздел «Форматировать только содержащиеся ячейки». В первом поле оставляем опцию «Значение ячейки». Во втором поле установите переключатель в положение «Другая» полиция. В третье поле вбиваем число 500000. Затем, как и в предыдущих случаях, нажимаем на кнопку «Форматировать…».
В окне «Формат ячеек» вернитесь на вкладку «Заливка». На этот раз мы выбираем другой цвет из двух предыдущих случаев. Нажимаем на кнопку «ОК».
В окне создания правил повторите, нажав на кнопку «ОК».
Откроется диспетчер правил. Как видите, все три правила созданы, поэтому нажмите кнопку «ОК».
Элементы таблицы теперь окрашены в соответствии с условиями и границами, указанными в настройках условного форматирования.
- Если мы изменим содержимое в одной из ячеек, выйдя за границы одного из указанных правил, этот элемент листа автоматически изменит свой цвет.
В качестве альтернативы вы можете использовать условное форматирование немного по-другому, чтобы окрашивать элементы листа в цвет.
- Для этого после перехода из диспетчера правил в окно создания форматирования мы остаемся в разделе «Форматировать все ячейки по их значениям». В поле «Цвет» можно выбрать цвет, оттенки которого будут заливать элементы листа. Затем следует нажать кнопку «ОК».
Также в диспетчере правил нажмите кнопку «ОК».
- Как видите, ячейки в столбце затем окрашиваются в разные оттенки одного цвета. Чем выше значение, содержащее элемент листа, тем светлее тень, чем ниже — темнее.
Способ 2: использование инструмента «Найти и выделить»
Если ваша таблица содержит статические данные, которые вы не планируете изменять с течением времени, вы можете использовать инструмент для изменения цвета ячеек в зависимости от их содержимого под названием «Найти и выбрать». Указанный инструмент позволит вам найти указанные значения и изменить цвет в этих ячейках для нужного пользователя. Но имейте в виду, что при изменении содержимого элементов листа цвет не изменится автоматически, но останется прежним. Чтобы изменить цвет на текущий, придется повторить процедуру еще раз. Поэтому этот метод не оптимален для таблиц с динамическим содержимым.
Давайте посмотрим, как это работает на конкретном примере, для которого мы все возьмем одну и ту же таблицу доходов компании.
- Выберите столбец с данными, которые нужно отформатировать с помощью цвета. Затем перейдите на вкладку «Главная» и нажмите кнопку «Найти и выбрать», которая находится на ленте на панели инструментов «Редактировать». В открывшемся списке нажмите на пункт «Найти».
На вкладке «Найти» открывается окно «Найти и заменить». В первую очередь находим значения до 400 000 руб. Так как у нас нет ни одной ячейки, содержащей значение менее 300000 рублей, поэтому, по сути, нам нужно выбрать все элементы, содержащие числа в диапазоне от 300000 до 400000 К сожалению, вы можете указать этот диапазон напрямую, как в случай применения условного форматирования в этом методе невозможен.
Но есть возможность сделать это немного по-другому, что даст нам такой же результат. Вы можете установить следующий шаблон в строке поиска «3. «. Вопросительный знак обозначает любой символ. Таким образом, программа будет искать все шестизначные числа, начинающиеся с цифры» 3 «. То есть результаты поиска будут содержать значения в диапазоне 300 000 — 400 000, что и есть имеем Если в таблице были числа меньше 300 000 или меньше 200 000, то для каждого интервала в сто тысяч поиск нужно производить отдельно.
Вводим выражение «3. «В поле« Найти »и нажмите кнопку« Найти все».
После этого внизу окна откроются результаты поиска. Щелкните левой кнопкой мыши по одному из них. Затем мы набираем комбинацию клавиш Ctrl + A. Далее выбираются все результаты поиска и одновременно выбираются элементы в столбце, к которому эти результаты относятся.
После выбора элементов в столбце не спешите закрывать окно «Найти и заменить». Находясь на вкладке «Главная», на которую мы переместились ранее, перейдите на ленту в панели инструментов «Шрифт». Щелкните треугольник справа от кнопки «Цвет заливки». Откроется набор различных цветов заливки. Выберите цвет, который мы хотим применить к элементам листа, содержащим значения менее 400 000 руб.
Как видите, все ячейки в столбце, содержащие значения менее 400 000 рублей, выделены выбранным цветом.
Теперь нам нужно раскрасить элементы, в которых лежат значения в диапазоне от 400 000 до 500 000 рублей. Этот диапазон включает числа, соответствующие «4. Вбиваем его в поле поиска и нажимаем на кнопку «Найти все», предварительно выделив нужный нам столбец.
Как и в предыдущий раз в результатах поиска, мы выделяем весь результат, полученный нажатием комбинации горячих клавиш CTRL + A. Далее переходим к значку выбора цвета заливки. Щелкните по нему и щелкните значок желаемого оттенка, который будет окрашивать элементы листа, значения которых находятся в диапазоне от 400 000 до 500 000.
Как видите, после этого действия все элементы таблицы с данными в диапазоне от 400000 до 500000 выделяются выбранным цветом.
Теперь осталось выбрать последний диапазон значений — более 500000. Здесь нам тоже повезло, так как все числа выше 500000 находятся в диапазоне от 500000 до 600000. Поэтому в поле поиска введите выражение «5. «И нажмите кнопку» Найти все «. Если бы были значения больше 600 000, нам также пришлось бы искать выражение «6» и т.д.
Снова выберите результаты поиска, используя комбинацию Ctrl + A. Затем, используя кнопку на ленте, выберите новый цвет, чтобы заполнить диапазон выше 500 000 по той же аналогии, которую мы делали ранее.
Как видите, после этого действия все элементы столбца будут раскрашены в соответствии с введенным в них числовым значением. Теперь вы можете закрыть окно поиска, нажав на стандартную кнопку закрытия в правом верхнем углу окна, так как нашу задачу можно считать решенной.
- Но если мы заменим число другим, выходящим за пределы, установленные для определенного цвета, цвет не изменится, как в предыдущем методе. Это указывает на то, что этот параметр будет надежно работать только в тех таблицах, где данные не изменяются.
Как видите, есть два способа раскрасить ячейки на основе содержащихся в них числовых значений: с помощью условного форматирования и с помощью инструмента «Найти и заменить». Первый метод более прогрессивен, так как позволяет более четко определить условия, при которых элементы листа будут выделены. Кроме того, при условном форматировании цвет элемента изменяется автоматически при изменении содержимого в нем, чего не может сделать второй метод. Однако вы также можете использовать заливку ячеек по значению с помощью инструмента «Найти и заменить», но только в статических таблицах.
Инструкция для Excel 2010
АКТИВИРУЙТЕ СУБТИТРЫ!
Как это сделать в Excel 2007
АКТИВИРУЙТЕ СУБТИТРЫ!
Выделите ячейки с ценами ордеров и, нажав на стрелку рядом с кнопкой «Условное форматирование», выберите «Создать правило».
Выбираем четвертый элемент, который позволяет сравнивать текущие значения со средним. Нас интересуют значения выше среднего. Нажав кнопку «Форматировать», установите цвет ячеек.
Мы подтверждаем свой выбор, и ячейки с ценой выше средней становятся синими, привлекая внимание к дорогостоящим заказам.
Выделите ячейки со статусами заказов и создайте новое правило. На этот раз мы воспользуемся вторым вариантом, который позволяет проверить содержимое ячейки. Выберите «Текст», «содержит» и введите слово «Готово». Устанавливаем зеленый цвет, подтверждаем и проделанная здесь работа становится зеленой.
Что ж, давайте сделаем еще одно правило, раскрашивая просроченные заказы в красный цвет. Выделяем даты заказов. При создании правила снова выберите второй элемент, но на этот раз установите «Значение ячейки», «минус» и в следующем поле введите функцию, которая возвращает сегодняшнюю дату.
Хорошо, у нас есть забавная красочная электронная таблица, которая позволяет нам визуально отслеживать ход выполнения заказов.
Вы заметили, что состояния устанавливаются путем выбора значений из раскрывающегося списка? Мы описали, как создавать такие списки в инструкции «Как создать выпадающий список в Excel».
Как это сделать в Excel 2003
АКТИВИРУЙТЕ СУБТИТРЫ!
«Условное форматирование» в меню «Формат». Это потребует немного больше ручной работы. Вот как будут выглядеть настройки для нашей первой задачи: закрасьте ячейки со значениями выше среднего.
Придется вручную ввести функцию «= СРЕДНЕЕ ()», поместить курсор между скобками, нажать на кнопку рядом с ней и указать мышью желаемый диапазон.
Но принцип тот же.
Покорите Excel и до скорой встречи!