Для выполнения этой задачи будем использовать возможности условного форматирования.
Возьмем таблицу, содержащую список заказов, сроки их исполнения, текущий статус и стоимость. Попробуем сделать так, чтобы ее ячейки раскрашивались сами, в зависимости от их содержимого.
ВКЛЮЧИТЕ СУБТИТРЫ!
ВКЛЮЧИТЕ СУБТИТРЫ!
Выделим ячейки с ценами заказов и, нажав на стрелочку рядом с кнопкой «Условное форматирование», выберем «Создать правило».
Выберем четвертый пункт, позволяющий сравнивать текущие значения со средним. Нас интересуют значения выше среднего. Нажав кнопку «Формат», зададим цвет ячеек.
Подтверждаем наш выбор, и ячейки с ценой выше средней окрасились в голубой цвет, привлекая наше внимание к дорогим заказам.
Выделим ячейки со статусами заказов и создадим новое правило. На этот раз используем второй вариант, позволяющий проверять содержимое ячейки. Выберем «Текст», «содержит» и введем слово «Выполнен». Зададим зеленый цвет, подтверждаем, и выполненные работы у нас позеленели.
Ну и сделаем еще одно правило, окрашивающее просроченные заказы в красный цвет. Выделяем даты выполнения заказов. При создании правила снова выбираем второй пункт, но на этот раз задаем «Значение ячейки», «меньше», а в следующем поле вводим функцию, возвращающую сегодняшнюю дату.
«ОК», и мы получили весело разукрашенную таблицу, позволяющую наглядно отслеживать ход выполнения заказов.
Обратили внимание, что статусы задаются выбором из выпадающего списка значений? Как делать такие списки, мы рассказывали в инструкции .
ВКЛЮЧИТЕ СУБТИТРЫ!
«Условное форматирование» в меню «Формат». Тут понадобится немного больше ручной работы. Вот так будут выглядеть настройки для нашей первой задачи – закрасить ячейки со значениями больше средних.
Придется вручную ввести функцию «=СРЗНАЧ()», поставить курсор между скобками, нажать на кнопочку рядом и мышкой указать нужный диапазон.
Но принцип действий тот же самый.
Покоряйте Excel и до новых встреч!
При работе с таблицами первоочередное значение имеют выводимые в ней значения. Но немаловажной составляющей является также и её оформление. Некоторые пользователи считают это второстепенным фактором и не обращают на него особого внимания. А зря, ведь красиво оформленная таблица является важным условием для лучшего её восприятия и понимания пользователями. Особенно большую роль в этом играет визуализация данных. Например, с помощью инструментов визуализации можно окрасить ячейки таблицы в зависимости от их содержимого. Давайте узнаем, как это можно сделать в программе Excel.
Конечно, всегда приятно иметь хорошо оформленную таблицу, в которой ячейки в зависимости от содержимого, окрашиваются в разные цвета. Но особенно актуальна данная возможность для больших таблиц, содержащих значительный массив данных. В этом случае заливка цветом ячеек значительно облегчит пользователям ориентирование в этом огромном количестве информации, так как она, можно сказать, будет уже структурированной.
Элементы листа можно попытаться раскрасить вручную, но опять же, если таблица большая, то это займет значительное количество времени. К тому же, в таком массиве данных человеческий фактор может сыграть свою роль и будут допущены ошибки. Не говоря уже о том, что таблица может быть динамической и данные в ней периодически изменяются, причем массово. В этом случае вручную менять цвет вообще становится нереально.
Но выход существует. Для ячеек, которые содержат динамические (изменяющиеся) значения применяется условное форматирование, а для статистических данных можно использовать инструмент «Найти и заменить» .
С помощью условного форматирования можно задать определенные границы значений, при которых ячейки будут окрашиваться в тот или иной цвет. Окрашивание будет проводиться автоматически. В случае, если значение ячейки, вследствие изменения выйдет за пределы границы, то автоматически произойдет перекрашивание данного элемента листа.
Посмотрим, как этот способ работает на конкретном примере. Имеем таблицу доходов предприятия, в которой данные разбиты помесячно. Нам нужно выделить разными цветами те элементы, в которых величина доходов менее 400000 рублей, от 400000 до 500000 рублей и превышает 500000 рублей.
Кроме того, можно использовать условное форматирование несколько по-другому для окраски элементов листа цветом.
Если в таблице находятся статические данные, которые не планируется со временем изменять, то можно воспользоваться инструментом для изменения цвета ячеек по их содержимому под названием «Найти и выделить» . Указанный инструмент позволит отыскать заданные значения и изменить цвет в этих ячейках на нужный пользователю. Но следует учесть, что при изменении содержимого в элементах листа, цвет автоматически изменяться не будет, а останется прежним. Для того, чтобы сменить цвет на актуальный, придется повторять процедуру заново. Поэтому данный способ не является оптимальным для таблиц с динамическим содержимым.
Посмотрим, как это работает на конкретном примере, для которого возьмем все ту же таблицу дохода предприятия.
Но существует возможность поступить несколько по-другому, что нам даст тот же результат. Можно в строке поиска задать следующий шаблон «3?????» . Знак вопроса означает любой символ. Таким образом, программа будет искать все шестизначные числа, которые начинаются с цифры «3» . То есть, в выдачу поиска попадут значения в диапазоне 300000 – 400000 , что нам и требуется. Если бы в таблице были числа меньше 300000 или меньше 200000 , то для каждого диапазона в сотню тысяч поиск пришлось бы производить отдельно.
Вводим выражение «3?????» в поле «Найти» и жмем на кнопку «Найти все ».
Как видим, существует два способа окрасить ячейки в зависимости от числовых значений, которые в них находятся: с помощью условного форматирования и с использованием инструмента «Найти и заменить» . Первый способ более прогрессивный, так как позволяет более четко задать условия, по которым будут выделяться элементы листа. К тому же, при условном форматировании цвет элемента автоматически меняется, в случае изменения содержимого в ней, чего второй способ делать не может. Впрочем, заливку ячеек в зависимости от значения путем применения инструмента «Найти и заменить» тоже вполне можно использовать, но только в статических таблицах.
Допустим, что одним из наших заданий является ввод информации о том, делал ли заказ клиент в текущем месяце. После чего на основе полученной информации необходимо выделить цветом ячейки по условию: какой из клиентов не совершил ни одного заказа на протяжении последних 3-х месяцев. Для таких клиентов нужно будет повторно выслать предложение.
Естественно это задание для Экселя. Программа должна автоматически найти таких контрагентов и соответственно выделить их цветом. Для этих условий будем использовать условное форматирование.
Сначала подготовим структуру для заполнения регистра. В первую очередь условно рассмотрим готовый пример автоматизированного регистра, который изображен ниже на рисунке:
Пользователю только необходимо указать если клиент совершал заказ в текущем месяце, то в соответствующую ячейку следует вводить текстовое значение «заказ». Главное условие для выделения: если на протяжении 3-х месяцев контрагент не сделал ни одного заказа, его номер автоматически выделяется красным цветом.
Представленное данное решение должно автоматизировать некоторые рабочие процессы и упростить визуальный анализ данных.
В первую очередь для регистра с номерами клиентов создадим заголовки столбцов с зеленым цветом и актуальными месяцами, которые будут автоматически отображать периоды времени. Для этого в ячейку B1 введите следующую формулу:
Как работает формула для автоматической генерации уходящих месяцев?
На рисунке формула возвращает период уходящего времени начиная даты написания статьи: 17.09.2017. В первом аргументе в функции DATA – вложена формула, которая всегда возвращает текущий год на сегодняшнюю дату благодаря функциям: ГОД и СЕГОНЯ. Во втором аргументе указан номер месяца (-1). Отрицательное число значит, что нас интересует какой был месяц в прошлом времени. Пример условий для второго аргумента со значением:
Последний аргумент – это номер дня месяца указано во втором аргументе. В результате функция ДАТА собирает все параметры в одно значение и формула возвращает соответственную дату.
Как видно теперь функция ДАТА использует значение из ячейки B1 и увеличивает номер месяца на 1 по отношению к предыдущей ячейки. В результате получаем 1 – число следующего месяца.
Теперь скопируйте эту формулу из ячейки C1 в остальные заголовки столбцов диапазона D1:L1.
Выделите диапазон ячеек B1:L1 и выберите инструмент: «ГЛАВНАЯ»-«Ячейки»-«Формат ячеек» или просто нажмите комбинацию клавиш CTRL+1. В появившемся диалоговом окне, на вкладке «Число», в разделе «Числовые форматы:» выберите опцию «(все форматы)». В поле «Тип:» введите значение: МММ.ГГ (обязательно буквы в верхнем регистре). Благодаря этому мы получим укороченное отображение значения дат в заголовках регистра, что упростит визуальный анализ и сделает его более комфортным за счет лучшей читабельности.
Обратите внимание! При наступлении января месяца (D1), формула автоматически меняет в дате год на следующий.
Теперь необходимо выделить ячейки цветом, касающиеся текущего месяца. Благодаря этому мы с легкостью найдем столбец, в который нужно вводить актуальные данные в этом месяце. Для этого:
Столбец под соответствующим заголовком регистра автоматически подсвечивается зеленым цветом соответственно с нашими условиями:
Как работает формула выделения столбца цветом по условию?
Благодаря тому, что перед созданием правила условного форматирования мы охватили всю табличную часть для введения данных регистра, форматирование будет активно для каждой ячейки в этом диапазоне B2:L15. Смешанная ссылка в формуле B$1 (абсолютный адрес только для строк, а для столбцов – относительный) обусловливает, что формула будет всегда относиться к первой строке каждого столбца.
Главное условие для заполнения цветом ячеек: если в диапазоне B1:L1 находиться та же дата, что и первого дня текущего месяца, тут же ячейки в целом столбце изменяют свой цвет на указанный в условном форматировании.
Обратите внимание! В условиях этой формулы, для последнего аргумента функции ДАТА указано значение 1, так же, как и для формул в определении дат для заголовков столбцов регистра.
В нашем случаи - это зеленая заливка ячеек. Если мы откроем наш регистр в следующем месяце, то уже ему соответствующий столбец будет выделен зеленым цветом в независимости от текущего дня.
Табличная часть сформатирована, теперь заполним ее текстовым значением «заказ» в смешанном порядке клиентов для текущего и прошлых месяцев.
Теперь нам необходимо выделить красным цветом ячейки с номерами клиентов, которые на протяжении 3-х месяцев не совершили ни одного заказа. Для этого:
Номера клиентов подсвечиваются красным цветом, если в их строке нет значения «заказ» в последних трех ячейках к текущему месяцу (включительно).
Анализ формулы для выделения цветом ячеек по условию:
Сначала займемся средней частью нашей формулы. Функция СМЕЩ возвращает ссылку на диапазон смещенного по отношении к области базового диапазона определенной числом строк и столбцов. Возвращаемая ссылка может быть одной ячейкой или целым диапазоном ячеек. Дополнительно можно определить количество возвращаемых строк и столбцов. В нашем примере функция возвращает ссылку на диапазон ячеек для последних 3-х месяцев.
Важная часть для нашего условия выделения цветом находиться в первом аргументе функции СМЕЩ. Он определяет, с какого месяца начать смещение. В данном примере – это ячейка D2, то есть начало года – январь. Естественно для остальных ячеек в столбце номер строки для базовой ячейки будет соответствовать номеру строки в котором она находиться. Следующие 2 аргумента функции СМЕЩ определяют на сколько строк и столбцов должно быть выполнено смещение. Так как вычисления для каждого клиента будем выполнять в той же строке, значение смещения для строк указываем –¬ 0.
В тоже время для вычисления значения третьего аргумента (смещение по столбцам) используем вложенную формулу МЕСЯЦ(СЕГОДНЯ()), Которая в соответствии с условиями возвращает номер текущего месяца в текущем году. От вычисленного формулой номера месяца отнимаем число 4, то есть в случаи Ноября получаем смещение на 8 столбцов. А, например, для Июня – только на 2 столбца.
Последнее два аргумента для функции СМЕЩ определяют высоту (в количестве строк) и ширину (в количестве столбцов) возвращаемого диапазона. В нашем примере – это область ячеек с высотой на 1-ну строку и шириной на 4 столбца. Этот диапазон охватывает столбцы 3-х предыдущих месяцев и текущий.
Первая функция в формуле СЧЕТЕСЛИ проверяет условия: сколько раз в возвращаемом диапазоне с помощью функции СМЕЩ встречается текстовое значение «заказ». Если функция возвращает значение 0 – значит от клиента с таким номером на протяжении 3-х месяцев не было ни одного заказа. А в соответствии с нашими условиями, ячейка с номером данного клиента выделяется красным цветом заливки.
Если мы хотим регистрировать данные по клиентам, Excel идеально приспособлен для этой цели. С легкостью можно записывать в соответствующие категории число заказанных товаров, а также даты реализации транзакций. Проблема постепенно начинает возникать с ростом объема данных.
Если их так много, что тратим несколько минут на поиск конкретной позиции регистра и анализ введенной информации. В таком случае стоит добавить в таблицу регистра механизмы, для автоматизации некоторых рабочих процессов пользователя. Что мы и сделали.
Когда приходится работать с большим объемом данных в Excel, где строки сильно растянуты и не умещаются в один экран, сложно сравнить значения в одной и той же строке. Постоянно приходится передвигать горизонтальную полосу прокрутки. Работа была бы более удобной если бы целая активная строка была бы выделена цветом – как курсор на целую строку. Чтобы выделить активную строку или столбец цветом, создадим свою функцию в Excel с помощью макроса и с комбинируем ее с условным форматированием.
Чтобы создать свой широкий курсов на целую строку в Excel, необходимо создать макрос и использовать его вместе с условным форматированием. Для примера создадим таблицу с широкими строками и большим количеством столбцов. Нам нужно выделить строку цветом по условию Excel. Например, если активная ячейка находится в строке 2, тогда выделить цветом всю строку 2 (в пределах таблицы или на весь лист). Для примера создадим таблицу с широкими строками и большим количеством столбцов:
Подсветка текущей строки в Excel цветом:
В результате происходит выделение строки условным форматированием только там, где находится активная ячейка. Эффект широкого курсора таблицы для комфортного просмотра данных с большим объемом информации. Сделав активной любую ячейку таблицы и сразу же выделяется цветом строка в которой находится данная ячейка.
Полезный вопрос! А как же выделить активный столбец цветом? Аналогичным методом реализовывается с помощью макроса не только подсветка цветом активной строки, но и столбца. Для этого:
Применяемая в условном форматировании созданная нами функция VBA =АктивнаяСтрока() позволяет нам получить номер текущей строки в которой стала активной любая из ее ячеек. Результат вычисления пользовательской функции =АктивнаяСтрока() сравнивается с результатом функции =СТРОКА(), которая также возвращает номер текущей строки в данном моменте вычисления условного форматирования.
Так как обе функции выполняются при активной ячейке в одной и той же строке их значения равны между собой и возвращают общее логическое значение ИСТИНА. В результате чего происходит выделение строки цветом по условию. Если значение ИСТИНА, тогда присваивается формат цвета для целой активной строки таблицы.
Чтобы быстро найти и выделить все ячейки, содержащие определенных типов данных, таких как формулы с помощью команды Перейти . Кроме того, с помощью Перейти к найти только ячейки, которые соответствуют определенным критериям, - например последнюю ячейку на лист, содержащую данные или форматирование.
Сделайте следующее:
Щелкните |
Чтобы выделить |
---|---|
Примечания |
Ячейки с примечаниями. |
Константы |
|
формулы |
Примечание: Флажки под параметром формулы определяют тип формул. |
Пустые |
Пустые ячейки. |
Текущую область |
текущая область, например весь список. |
Текущий массив |
Весь массив, если активная ячейка содержится в массиве. |
Объекты |
Графические объекты (в том числе диаграммы и кнопки) на листе и в текстовых полях. |
Отличия по строкам |
Все ячейки, которые отличаются от активной ячейки в выбранной строке. В режиме выбора всегда найдется одной активной ячейки, является ли диапазон, строки или столбца. С помощью клавиши ВВОД или Tab, вы можете изменить расположение активной ячейки, которые по умолчанию - первую ячейку в строке. Если выбран более одной строки, сравнение выполняется для каждого отдельного ряда выделенного фрагмента и ячейку, которая используется для сравнения для каждой дополнительной строки находится в том же столбце как активную ячейку. |
Отличия по столбцам |
Все ячейки, которые отличаются от активной ячейки в выделенном столбце. Всегда найдется одной активной ячейки в выделенном фрагменте, будет ли это диапазон, строки или столбца. С помощью клавиши ВВОД или Tab, можно изменить расположение на активную ячейку - по умолчанию - первую ячейку в столбце. Если выбрать более одного столбца, сравнение выполняется для каждого отдельного столбца выделенного фрагмента. Ячейки, которая используется для сравнения для каждого дополнительного столбца находится в той же строке в активной ячейке. |
Влияющие ячейки |
Ячейки, на которые ссылается формула в активной ячейке. В разделе зависимые ячейки только непосредственно , чтобы найти только те ячейки, на которые формулы ссылаются непосредственно; на всех уровнях , чтобы найти все ячейки, на которые ячейки в выделенном диапазоне ссылаются непосредственно или косвенно. |
Зависимые ячейки |
Ячейки с формулами, которые ссылаются на активную ячейку. Выполните одно из указанных ниже действий. Выберите вариант только непосредственно , чтобы найти только ячейки с формулами, ссылающимися непосредственно на активную ячейку. Выберите вариант на всех уровнях , чтобы найти все ячейки, ссылающиеся на активную ячейку непосредственно или косвенно. |
Последнюю ячейку |
Последнюю ячейку на листе, содержащую данные или форматирование. |
Только видимые ячейки |
Только ячейки, видимые в диапазоне, который пересекается со скрытыми строками и столбцами. |
Условные форматы |
Только ячейки, которые применено условное форматирование. В разделе "Проверка данных" выполните одно из указанных ниже действий. все , чтобы найти все ячейки, к которым применено условное форматирование; этих же , чтобы найти ячейки с тем же условным форматированием, что и в выделенной ячейке. |
Проверка данных |
Только ячейки, которые применены правила проверки данных. Выполните одно из указанных ниже действий. Выберите вариант все , чтобы найти все ячейки, для которых включена проверка данных. Выберите вариант этих же , чтобы найти ячейки, к которым применены те же правила проверки данных, что и к выделенной ячейке. |
Вы всегда можете задать вопрос специалисту Excel Tech Community , попросить помощи в сообществе Answers community , а также предложить новую функцию или улучшение на веб-сайте