Окрашивание ячейки по дате

Пример формулы для выделения цветом просроченных дат в Excel

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

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

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

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

Выделите целевой диапазон ячеек (в данном примере A3:A8) и выберите инструмент: «ГЛАВНАЯ»-«Условное форматирование»-«Создать правило». В результате чего появится окно для внесения всех необходимых настроек инструмента:

В появившемся окне из верхней части где находится список опций выберите пункт: «Использовать формулу для определения форматируемых ячеек». Данная опция позволяет нам использовать собственные формулы для составления сложны правил условного форматирования. Формула должна содержать логическое выражение и соответственно возвращать логическое значение для каждой ячейки из выделенного диапазона. Если будет возвращено – ИНСТИНА, тогда к этой ячейке будет применятся правило и присваивается новый формат, который предварительно настроен этим же инструментом.
В полю ввода формул введите логическое выражение представленное на этом шаге. Данная формула проверяет значение ячеек: будет ли их дата выпадать после 90 дней, пройденных от сегодняшнего дня. Отсчитывается от даты, указанной в целевой ячейке A3 выделенного просматриваемого диапазона. Если да (ИСТИНА) – сразу же применяется условное форматирование.
=$B$1-A3>90
Нажмите на кнопку «Формат» для вызова окна, в которому будут доступные все опции оформления формата: цвет фона и границы, размер шрифта и т.п. После указания желаемых настроек для оформления стиля форматирования нажмите кнопку ОК на всех открытых окнах, чтобы подтвердить все настройки и получить готовый результат.

А в результате выделились все даты актуальность которых превышает 90 дней.

javascript - изменить цвет всей ячейки в выбранную дату fullCalendar

Переполнение стека

Около
Товары

Для команд

Переполнение стека Общественные вопросы и ответы
Переполнение стека для команд Где разработчики и технологи делятся частными знаниями с коллегами
Вакансии Программирование и связанные с ним технические возможности карьерного роста
Талант Нанимайте технических специалистов и создавайте свой бренд работодателя

Как посчитать ЦВЕТНЫЕ ячейки в Excel [Пошаговое руководство + ВИДЕО]

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

Разве не было бы замечательно, если бы в Excel была функция подсчета цветных ячеек?

К сожалению, для этого нет встроенной функции.

НО ..

Это легко сделать.

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

В этом руководстве я покажу вам три способа подсчета цветных ячеек в Excel (с VBA и без него):

Использование фильтра и функции ПРОМЕЖУТОЧНЫЙ ИТОГ
Использование GET.ЯЧЕЙКА функция
Использование пользовательской функции, созданной с помощью VBA

# 1 Подсчет цветных клеток с использованием фильтра и ПРОМЕЖУТОЧНЫЙ ИТОГ

Для подсчета цветных ячеек в Excel необходимо выполнить следующие два шага:

Фильтр цветные ячейки
Используйте функцию ПРОМЕЖУТОЧНЫЙ ИТОГ для подсчета видимых цветных ячеек (после фильтрации).

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

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

Вот количество шагов цветных ячеек в Excel:

В любой ячейке под набором данных используйте следующую формулу: = ПРОМЕЖУТОЧНЫЙ ИТОГ (102, E1: E20)
Выберите заголовки.

Перейдите в Данные -> Сортировка и фильтр -> Фильтр. Это применит фильтр ко всем заголовкам.
Щелкните любой из раскрывающихся списков фильтров.
Перейдите в «Фильтр по цвету» и выберите цвет. В приведенном выше наборе данных, поскольку для выделения ячеек используются два цвета, фильтр показывает два цвета для фильтрации этих ячеек.

Как только вы отфильтруете ячейки, вы заметите, что значение в функции ПРОМЕЖУТОЧНЫЙ ИТОГ изменяется и возвращает только количество ячеек, видимых после фильтрации.

Как это работает?

Функция ПРОМЕЖУТОЧНЫЙ ИТОГ использует 102 в качестве первого аргумента, который используется для подсчета видимых ячеек (скрытые строки не учитываются) в указанном диапазоне.

Если данные не отфильтрованы, они возвращают 19, но если они отфильтрованы, то они возвращают только количество видимых ячеек.

Попробуйте сами .. Загрузите файл примера

# 2 Подсчет цветных ячеек с помощью функции GET.CELL

GET.CELL - это функция Macro4, которая была сохранена по причинам совместимости.

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

Однако он работает в именованных диапазонах Excel.

См. Также : Узнайте больше о функции GET.CELL.

Вот три шага для использования GET.ЯЧЕЙКА для подсчета цветных ячеек в Excel:

Создайте именованный диапазон с помощью функции GET.CELL
Используйте именованный диапазон, чтобы получить цветовой код в столбце

Использование числа цвета для подсчета количества окрашенных ячеек (по цвету)

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

Создание именованного диапазона

Перейдите в Формулы -> Определить имя.
В диалоговом окне «Новое имя» введите:
- Имя: GetColor
- Объем: Рабочая тетрадь
- Относится к: = GET.CELL (38, Sheet1! $ A2)
  В приведенной выше формуле я использовал Sheet1! $ A2 в качестве второго аргумента. Вам нужно использовать ссылку на столбец, в котором есть ячейки с цветом фона.

Получение цветового кода для каждой ячейки

В ячейке рядом с данными используйте формулу = GetColor

Эта формула вернет 0, если в ячейке НЕТ цвета фона, и определенное число, если есть цвет фона.

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

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

Если вы последуете описанному выше процессу, у вас будет столбец с числами, соответствующими цвету фона в нем.

Для подсчета определенного цвета:

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

= СЧЁТЕСЛИ ($ F $ 2: $ F $ 20, GetColor)

Эта формула даст вам количество всех ячеек с указанным цветом фона.

Как это работает?

Функция СЧЁТЕСЛИ использует названный диапазон (GetColor) в качестве критерия. Именованный диапазон в формуле относится к соседней ячейке слева (в столбце A) и возвращает цветовой код для этой ячейки.Следовательно, этот номер цветового кода является критерием.

Функция СЧЁТЕСЛИ использует диапазон ($ F $ 2: $ F $ 18), который содержит номера цветовых кодов всех ячеек и возвращает количество на основе номера критерия.

Попробуйте сами .. Загрузите файл примера

# 3 Счетчик, раскрашенный с использованием VBA (путем создания пользовательской функции)

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

Но, если вас устраивает использование VBA, это самый простой из трех методов.

Используя VBA, мы могли бы создать пользовательскую функцию, которая будет работать как функция СЧЁТЕСЛИ и возвращать количество ячеек с определенным цветом фона.

Вот код:

 'Код создан Sumit Bansal с https://trumpexcel.com Функция GetColorCount (CountRange как диапазон, CountColor как диапазон) Dim CountColorValue As Integer Dim TotalCount как целое число CountColorValue = CountColor.Interior.ColorIndex Установите rCell = CountRange Для каждой rCell в CountRange Если rCell.Interior.ColorIndex = CountColorValue Тогда TotalCount = TotalCount + 1 Конец, если Следующий rCell GetColorCount = TotalCount Конечная функция

Для создания этой пользовательской функции:

При активной книге нажмите Alt + F11 (или щелкните правой кнопкой мыши вкладку рабочего листа и выберите Просмотреть код). Это откроет редактор VB.
На левой панели под книгой, с которой вы работаете, щелкните правой кнопкой мыши любой из листов и выберите Вставить -> Модуль.Это вставит новый модуль. Скопируйте и вставьте код в окно кода модуля.
Дважды щелкните имя модуля (по умолчанию имя модуля в Module1) и вставьте код в окно кода.
Закройте редактор VB.
Вот и все! Теперь у вас есть настраиваемая функция на листе под названием GetColorCount.

Чтобы использовать эту функцию, просто используйте ее как любую обычную функцию Excel.

Синтаксис: = GetColorCount (CountRange, CountColor)

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

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

Примечание: Поскольку в книге есть код, сохраните его с расширением .xls или .xlsm.

Попробуйте сами.. Загрузите файл примера

Знаете ли вы другой способ подсчета цветных ячеек в Excel?

Если да, поделитесь со мной, оставив комментарий.

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

excel - Сумма ячеек на основе цвета и даты

Переполнение стека

Около
Товары
Для команд

Переполнение стека Общественные вопросы и ответы
Переполнение стека для команд Где разработчики и технологи делятся частными знаниями с коллегами
Вакансии Программирование и связанные с ним технические возможности карьерного роста

изменить цвет ячейки Excel, если дата в указанной ячейке меньше 20 дней с сегодняшней даты

Переполнение стека

Около
Товары
Для команд

Переполнение стека Общественные вопросы и ответы
Переполнение стека для команд Где разработчики и технологи делятся частными знаниями с коллегами
Вакансии Программирование и связанные с ним технические возможности карьерного роста
Талант Нанимайте технических специалистов и создавайте свой бренд работодателя