Как в эксель выделить выходные дни
Как выделить дату текущего дня / недели / месяца в Excel?
Можно ли в Excel автоматически выделить текущий день, неделю или месяц в диапазоне данных? По умолчанию Excel предоставляет функцию условного форматирования, чтобы помочь вам решить эту задачу, как вы могли бы применить это условное форматирование?
Вкладка Office позволяет редактировать и просматривать в Office с вкладками и значительно упрощает работу .
- Повторное использование чего угодно: Добавляйте наиболее часто используемые или сложные формулы, диаграммы и все остальное в избранное и быстро используйте их в будущем.
- Более 20 текстовых функций: Извлечь число из текстовой строки; Извлечь или удалить часть текстов; Преобразование чисел и валют в английские слова.
- Инструменты слияния : Несколько книг и листов в одну; Объединить несколько ячеек / строк / столбцов без потери данных; Объедините повторяющиеся строки и сумму.
- Разделить инструменты : Разделение данных на несколько листов в зависимости от ценности; Из одной книги в несколько файлов Excel, PDF или CSV; От одного столбца к нескольким столбцам.
- Вставить пропуск Скрытые / отфильтрованные строки; Подсчет и сумма по цвету фона ; Отправляйте персонализированные электронные письма нескольким получателям массово.
- Суперфильтр: Создавайте расширенные схемы фильтров и применяйте их к любым листам; Сортировать по неделям, дням, периодичности и др .; Фильтр жирным шрифтом, формулы, комментарий .
- Более 300 мощных функций; Работает с Office 2007-2019 и 365; Поддерживает все языки; Простое развертывание на вашем предприятии или в организации.
Выделите дату текущего дня / недели / месяца в ячейке с условным форматированием
Удивительный! Использование эффективных вкладок в Excel, таких как Chrome, Firefox и Safari!
Экономьте 50% своего времени и сокращайте тысячи щелчков мышью каждый день!
Если вам просто нужно выделить ячейку текущего дня, недели или месяца, вы можете сделать это, выполнив следующие простые шаги:
1. Выберите диапазон данных, в котором вы хотите выделить конкретную дату.
2. Нажмите Главная > Выделите правила ячеек > Дата наступления, см. снимок экрана:
3. И в Дата наступления В диалоговом окне в первом раскрывающемся списке выберите дату, которую вы хотите выделить, а во втором раскрывающемся списке выберите один цвет, который вы хотите отформатировать для определенной даты, см. снимок экрана:
4. Затем нажмите OK, ячейка с сегодняшней датой была выделена, как показано на следующем снимке экрана:
Выделите дату текущего дня / недели / месяца в строке с условным форматированием
1. Выберите диапазон данных или целые строки данных, за исключением заголовков, если они есть.
2. Нажмите Главная > Условное форматирование > Новое правило, см. снимок экрана:
3. А потом в Новое правило форматирования диалоговое окно, нажмите Используйте формулу, чтобы определить, какие ячейки следует форматировать. вариант, затем введите эту формулу = сегодня () - день недели (сегодня (), 3) = $ A2-будний день ($ A2, 3) в Значения формата, в которых эта формула верна поле, чтобы выделить дату текущей недели. (A2 указывает на первую ячейку даты, которую вы использовали, вы можете заменить ее по своему усмотрению) См. снимок экрана:
Функции: Чтобы выделить сегодняшнюю дату, примените эту формулу: = $ A2 = СЕГОДНЯ () ;
Чтобы выделить дату текущего месяца, введите следующую формулу: = ТЕКСТ ($ A2, "ммгг") = ТЕКСТ (СЕГОДНЯ (), "ммгг") .
4. После ввода формулы нажмите Формат кнопку в Формат ячеек диалоговом окне выберите один цвет, который вам нравится Заполнять меню.
5. Затем нажмите OK > OK чтобы закрыть диалоги. И строки, содержащие дату текущей недели, отформатированы. Смотрите скриншот:
Внимание: В этом диапазоне данных форматирование будет изменяться автоматически при изменении или добавлении записей дат.
Иногда во время анализа данных необходимо выделить цветом даты за определенный период времени. Еще лучше если выделение цветом дат будет выполнятся автоматически по условию при каждом изменении параметров начала и конца периода времени.
Как выделить даты цветом только за определенный промежуток времени в Excel
Ниже на рисунке показано, как можно применить условное форматирование для автоматического выделения цветом диапазона значений с днями между начальной и конечной датой. Если искомая дата начала и/или конца периода будет изменятся, тогда условное форматирование будет приспособлено к новым условиям и автоматически подсветит цветом все соответственные ячейки с новым условием.
Чтобы создать такое правило автоматического форматирования цветом по условию, выполните следующие действия:
- Выделите целевой диапазон ячеек, в котором нужно подсветить даты цветом (в данном примере это D2:D17). После чего выберите инструмент: «ГЛАВНАЯ»-«Условное форматирование»-«Создать правило». Возникнет окошко «Создание правила форматирования ячеек» точно так, как изображено внизу на рисунке:
- В появившемся окне укажите на опцию «Использовать формулу для определения форматируемых ячеек». Таким образом у нас появилась возможность применять формулу с логическим выражением для определения условий форматирования. Если в своем итоговом результате вычисления формула будет возвращать логическое значение ИСТИНА для значения текущей ячейки, тогда сразу же будет для нее применен формат, заданный пользователем в настройках данного инструмента.
- Заполните поле ввода следующей формулой:
В результате из списка разных дат за разные года выделились цветом только даты за период 2010-2012 год.
Функция ДЕНЬ возвращает день, который соответствует определенной дате. Результатом работы функции является число из диапазона от 1 до31.
Функция ДЕНЬНЕД возвращает день недели, который соответствует определенной дате. Результат работы данной функции – это число из диапазона от 1 до 7 (единица соответствует воскресенью, а семь – субботе).
Функция ДНИ возвращает числовое значение, характеризующее разницу дней между двумя заданными датами.
Особенности синтаксической записи функции ДЕНЬ, ДЕНЬНЕД и ДНИ в Excel
Функция ДЕНЬ имеет следующий синтаксис:
Принимает на вход единственный параметр дата, который соответствует дате, день которой требуется определить.
Функция ДЕНЬНЕД имеет следующую синтаксическую запись:
Описание аргументов функции:
- Дата – обязательный параметр, соответствующей дате, день недели которой требуется определить. Данный параметр имеет особенности, описанные в примечаниях к предыдущей функции.
- Тип – число в диапазоне от 1 до 3, указывающее на то, какой день недели считается первым (в некоторых странах первый день недели – воскресенье, в нашей – понедельник). Если тип=1, первым днем недели будет считаться воскресенье, тип=2 – понедельник. Если тип=3, результатом работы функции ДЕНЬНЕД будет число из диапазона от 0 до 6, где 0 соответствует понедельнику.
Примечение: в некоторых случаях вместо чисел 1,2…7 требуется вывод названия дня недели (понедельник, вторник… воскресенье). Для этого можно составить такую таблицу:
И затем выводить текстовое значение, соответствующее номеру дня недели:
Однако для данной задачи можно использовать следующую функцию: =ТЕКСТ(A2;"dddd").
Результатом выполнения данной функции является текст «воскресенье». Если требуется краткая запись дня недели (Сб, Пн, Ср), в качестве второго параметра следует указать «dddd».
Функция ДНИ используется для расчета количества дней между двумя указанными датами и имеет следующий синтаксис: =ДНИ(кон_дата;нач_дата).
Описание аргументов данной функции:
- Кон_дата – обязательный параметр, характеризующий дату окончания какого-либо события.
- Нач_дата – обязательный параметр, характеризующий дату начала определенного события для выполнения расчета.
Примеры расчетов в Excel с использованием функций ДЕНЬ, ДЕНЬНЕД и ДНИ
Для решения используем следующую формулу: =B3-ДЕНЬ(B3)+1.
Аналогично заполним остальные колонки. В результате получим новый столбец с правильными датами выдачи зарплаты:
Определение рабочих и выходных дней по формуле ДЕНЬНЕД
Пример 2. Программный продукт считывает данные из таблицы Excel и использует их в своих расчетах. Одна из ячеек содержит данные в формате Дата, соответствующие дате получения посылки. Необходимо определить, соответствует ли указанная дата рабочему дню недели (с Пн по Пт) и вернуть логическое значение “true” или “false” (если выходной день) для дальнейшей обработки программой.
Исходная таблица имеет следующий вид:
Для определения дня недели будем использовать ДЕНЬНЕД, первым аргументом которой является данные из ячейки «Дата прибытия», а вторым – 2 (отсчет дней недели начинается с единицы, которая соответствует понедельнику). Для решения используем следующую функцию:
Функция ЕСЛИ проверяет условие: находится ли полученное в результате работы функции ДЕНЬНЕД значение в интервале от 1 до 5 включительно и возвращает соответствующий результат (true или false).
Вычислим значения для остальных полей данных:
Как видно, все даты являются рабочими днями недели.
Зачем нужна функция ДНИ в Excel?
Пример 3. Общеизвестно, что ВОВ была начата 22 июня 1941 года и закончилась 9 мая 1945 года. Необходимо определить, сколько дней проходили военные действия.
Внесем исходные данные в таблицу:
Для расчета используем следующую формулу: =ДНИ(B3;A3).
Получаем следующий результат:
То есть, военные действия длились на протяжении 1417 дней.
Обратите внимание! Любая дата в Excel – это число дней, прошедших начиная от даты 01.01.1900г. Подробнее описано в статье: Как вычислить дату в Excel? Поэтому можно свободно использовать такую простую формулу без функций: =B3-A3.
Как видно на рисунке результат аналогичен. Фактически функция ДНИ в Excel не нужна вообще.
Пусть в столбце А содержится перечень дат. Выделим ячейки , содержащие дни недели Суббота и Воскресенье, красным цветом (см. файл примера ).
Выделять ячейки будем с помощью правил Условного форматирования . В зависимости от содержимого ячейки (дата, текст или число) правила будут разными.
В ячейке содержится Дата
В столбце А содержатся значения в формате дата, например в ячейке А6 - 13.08.2012.
Чтобы настроить правило Условного форматирования нужно:
- выделить диапазон А6:А14
- выбрать пункт меню Главная/ Стили/ Условное форматирование/ Создать правило.
- выбрать тип правила Использовать формулу для определения форматируемых ячеек
- в поле Форматировать значения, для которых следующая формула является истинной ввести формулу =ДЕНЬНЕД(A6;2)>5
- задать формат, например красную заливку фона ячейки.
Даты в столбце А , у которых номер дня недели >5, будут выделены красным.
В ячейке с пользовательским форматом ДДД или ДДДД содержится Дата
Пусть в столбцах B и С также содержатся значения в формате дата, например в ячейке B6 введем формулу =A6 .
Для ячеек в диапазоне B6:B14 настроим пользовательский формат ДДД (для диапазона С6:С14 ДДДД).
Не смотря на то, что теперь в ячейках отображаются Пн и Понедельник, значения в ячейках продолжают оставаться в формате Дата. В этом можно убедиться скопировав значение ( CTRL+V ), например, из ячейки С6 , и вставив его Как значение ( Главная/ Буфер обмена/ Вставить / Вставить значение ). В Строке формул будет отображаться значение 13.08.2012, а не слово понедельник . Поэтому настраивать правило Условного форматирования нужно так как показано в предыдущем разделе.
В ячейке содержится Число
Пусть в столбце D содержится номер дня недели, например в ячейке D6 введем формулу =ДЕНЬНЕД(A6;2) .
В этом случае правило Условного форматирования настраивается аналогично случаю когда в ячейке содержится Дата, только несколько упрощается формула =$D6>5
В ячейке содержится Текстовое значение
Пусть в столбце E содержится день недели в текстовом формате, например слово Понедельник.
В этом случае правило Условного форматирования настраивается с помощью формулы =ИЛИ($E6="суббота";$E6="воскресенье")
Сегодня я расскажу о том, как выделить строки выходного дня в Excel с помощью этого условного форматирования и удобного инструмента.
Отфильтруйте и выделите строки с выходными днями с помощью Kutools for Excel
Выделите строки с выходными в Excel
Вы можете выделить строки по выходным дням с помощью условного форматирования, выполнив следующие действия:
1. Выберите диапазон данных, в котором вы хотите выделить строки выходных дней.
2, Идти к Главная вкладку и нажмите Условное форматирование > Новое правило, см. снимок экрана:
3. В Новое правило форматирования диалоговое окно, нажмите Используйте формулу, чтобы определить, какие ячейки следует форматировать. вариант и введите эту формулу = WEEKDAY (C2,2 $)> 5 (C2 - ячейка даты начала в вашем диапазоне данных) см. снимок экрана:
4. Затем нажмите Формат , чтобы открыть Формат ячеек диалоговом окне, в открывшемся диалоговом окне нажмите Заполнять вкладка и выберите один цвет, который вы хотите отформатировать.
5, Затем нажмите OK > OK чтобы закрыть диалоговые окна, и строки с датой воскресенья или субботы были выделены. Смотрите скриншот:
Ноты:
1. Если вы хотите выделить рабочие дни с понедельника по пятницу, вы можете применить эту формулу = WEEKDAY (C2,2 $) на шаге 3. (C2 это ячейка даты начала в вашем диапазоне данных)
2. В этом диапазоне данных форматирование будет изменяться автоматически при изменении или добавлении записей дат.
Отфильтруйте и выделите строки с выходными днями с помощью Kutools for Excel
Если у вас есть Kutools for Excel установлен, вы также можете применить его Суперфильтр утилита, позволяющая быстро отфильтровать выходные дни и затем закрасить соответствующие строки желаемым цветом.
После бесплатная установка Kutools for Excel, сделайте следующее:
1. Нажмите Кутулс Плюс > Суперфильтр для отображения Суперфильтр панель. Смотрите скриншот:
3. Затем нажмите Фильтр, и отображаются только выходные дни. Смотрите скриншот:
5. Выберите строки с выходными днями и нажмите Главная > Цвет заливки чтобы выбрать цвет для выделения строк. Смотрите скриншот:
6. Нажмите Очистить в Суперфильтр панель для расширения диапазона. Смотрите скриншот:
Наконечник: Вы также можете фильтровать дни по конкретному дню недели.
Читайте также: