Как в эксель выделить выходные дни

Обновлено: 06.07.2024

В этой статье описаны синтаксис формулы и использование функции РАБДЕНЬ в Microsoft Excel.

Описание

Возвращает число, которое представляет дату, отстоящую на заданное количество рабочих дней вперед или назад от начальной даты. Рабочими днями не считаются выходные дни и дни, определенные как праздничные. Функция РАБДЕНЬ используется для исключения выходных дней или праздников при вычислении дат платежей, ожидаемых дат доставки или количества фактически отработанных дней.

Совет: Чтобы вычислить число, которое представляет дату, отстоящую на заданное количество рабочих дней вперед или назад, с использованием параметров, определяющих количество выходных в неделе и то, какие это дни, используйте функцию РАБДЕНЬ.МЕЖД.

Синтаксис

Аргументы функции РАБДЕНЬ описаны ниже.

нач_дата — обязательный аргумент. Начальная дата.

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

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

Важно: Даты должны быть введены с использованием функции ДАТА или как результат вычисления других формул и функций. Например, для указания даты 23 мая 2008 г. воспользуйтесь выражением ДАТА(2008,5,23). Если ввести даты как текст, это может привести к возникновению проблем.

Замечания

В приложении Microsoft Excel даты хранятся в виде последовательных чисел, что позволяет использовать их в вычислениях. По умолчанию дате 1 января 1900 года соответствует номер 1, а 1 января 2008 года — 39448, так как интервал между этими датами составляет 39 448 дней.

Если значение аргумента "количество_дней" не является целым числом, оно усекается.

Пример

Скопируйте образец данных из следующей таблицы и вставьте их в ячейку A1 нового листа Excel. Чтобы отобразить результаты формул, выделите их и нажмите клавишу F2, а затем — клавишу ВВОД. При необходимости измените ширину столбцов, чтобы видеть все данные.

Пусть в столбце А содержится перечень дат. Выделим ячейки , содержащие дни недели Суббота и Воскресенье, красным цветом (см. файл примера ).

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

В ячейке содержится Дата

В столбце А содержатся значения в формате дата, например в ячейке А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 конструкции вида: ЕСЛИ условия такие-то, ТО цвет ячейки (а также шрифт, границы, формат данных!) такой-то.

Можно решать подобную задачу с помощью макроса, но я хочу показать, как это сделать, используя только меню EXCEL. Поехали.

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

Получить исходя из даты числовое значение дня недели можно с помощью формулы:

Двойка после значения в ячейке это тип отображаемых данных. По умолчанию или тип 1: единица – воскресенье, а семерка – суббота. Нам как-то привычнее считать, что суббота это шестой день недели, а воскресенье – седьмой. Поэтому укажите тип – 2.

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

Все прекрасно, но что насчет цветового форматирования? Становимся на соседнюю ячейку и выбираем в меню Главная – Стили – Условное форматирование – Создать правило

Нам нужен пункт Использовать формулу для определения форматируемых ячеек

В формуле кликаем на ячейку слева, пишем =“РАБ”. Аналогично для выходных дней, =”ВЫХ”. По кнопке «Формат» настраиваем внешний вид ячейки, с нашем случае это только цвет заливки.

Вернуться к редактированию уже существующих правил, удалить или создать новое правило можно через пункт меню Условное форматирование – Управление правилами

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

Теперь смело растягиваем ячейки с примерами на весь столбец и получаем итоговый результат:

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

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

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

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

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

Как выделить цветом только выходные дни по дате в Excel

Ниже на рисунке приведен пример автоматического выделения цветом дат выходных дней:

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

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

Определение выходных дней в табеле

Для определения выходных дней (суббот и воскресений) воспользуйтесь функцией ДЕНЬНЕД, которая устанавливает номер дня недели. Эту функцию введите в ячейку С28:

Функция ЕСЛИ действует по описанной ниже схеме:

Учет выходных и праздничных дней

На следующем этапе нам предстоит создать формулу, которая при соблюдении одного из условий (праздничный день, выходной день или праздничный день, выпадающий на выходной) возвращает значение ИСТИНА. Мы предполагаем разместить эту формулу в ячейке СЗО.

Для решения задачи воспользуемся логической функцией ИЛИ. Эта функция возвращает значение ИСТИНА, если хотя бы один из ее аргументов имеет значение ИСТИНА, и значение ЛОЖЬ, если все аргументы имеют значение ЛОЖЬ.

Функция ИЛИ имеет следующий синтаксис:

Читать еще: Как в excel скопировать ячейки с сохранением размера

Рис. 7.12. Панель функции ИЛИ

Формула в ячейке СЗО должна быть такой:

Произведем вложение функций:

Формулу из ячейки С28 вложим в формулу из ячейки С29:

Полученную формулу вложим в формулу из ячейки СЗО:

Перенос выходных дней

На практике нередки случаи переноса выходных дней. В нашем табеле реакция на такие переносы не обеспечена. Сейчас мы исправим этот недостаток. Даты дней, которые вследствие переноса стали рабочими, будем вводить в область G22:G24 листа Праздники. Этой области присвоим имя ДатаПеренос (рис. 7.13).

Даты дней, которые вследствие переноса стали выходными, добавим в список праздников, находящийся в диапазоне A15:G16 листа Праздники (рис. 7.5).

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

Рис. 7.13. Список с перемещенными датами

Скопируйте ее из строки формул, вставьте в ячейку С31, замените имя Да-таПраздн именем ДатаПеренос и удалите функцию НЕ.

Результирующая формула в ячейке С31 должна иметь следующий вид:

Формула возвращает значение ЛОЖЬ, если находит дату среди перенесенных, или значение ИСТИНА, если не находит. Таким образом, эта ячейка будет содержать значение ЛОЖЬ, если день вследствие переноса стал рабочим. Если же ячейка содержит значение ИСТИНА, значит, статус дня не изменился.

Теперь мы должны проанализировать значения ячеек С3О и С31. Первая содержит логическое значение, которое указывает на то, является ли день выходным либо праздничным. Логическое значение во второй ячейке отражает факт изменения статуса дня (то есть не стал ли он вследствие переноса выходных рабочим днем).

Формула для анализа должна работать так. День является выходным, если его статус не изменился (С31 содержит значение ИСТИНА), и он является праздничным, субботним или воскресным (СЗО содержит значение ИСТИНА). Во всех остальных случаях день будет рабочим.

Составить такую формулу можно с помощью функции И. Она возвращает значение ИСТИНА, если все аргументы имеют значение ИСТИНА, и значение ЛОЖЬ, если хотя бы один аргумент имеет значение ЛОЖЬ. Данная функция принадлежит к категории Логические и имеет следующий синтаксис:

Введите в ячейку С32 такую функцию:

В данную функцию надлежит вложить формулы из ячеек СЗО и С31. Ниже показано, как выглядит результирующая формула в ячейке С32:

Скопируйте формулы из ячеек С27 и С32 в ячейки С10 и С11.

РАБДЕНЬ (функция РАБДЕНЬ)

В этой статье описаны синтаксис формулы и использование функции РАБДЕНЬ в Microsoft Excel.

Аргументы функции РАБДЕНЬ описаны ниже.

Нач_дата Обязательный. Начальная дата.

В приложении Microsoft Excel даты хранятся в виде последовательных чисел, что позволяет использовать их в вычислениях. По умолчанию дате 1 января 1900 г. соответствует число 1, а 1 января 2008 г. — число 39 448, поскольку интервал между ними составляет 39 448 дней.

Microsoft Excel

трюки • приёмы • решения

Как в Excel рассчитать даты праздников

Определение даты какого-нибудь праздника может быть непростой задачей. Некоторые праздники, такие как Новый год или День независимости США, не дадут вам поломать голову, потому что всегда отмечаются в один и тот же день. Для этих видов праздников вы можете просто использовать функцию ДАТА.

Например, чтобы вычислить день празднования Нового года (который всегда выпадает на 1 января) для конкретного года, хранящегося в ячейке А1, вы можете воспользоваться такой функцией: =ДАТА(А1;1;1) . Другие праздники, в свою очередь, отмечаются в определенный день недели и месяц. Например, День труда в США приходится на первый понедельник сентября.

Во всех формулах подразумевается, что ячейка А1 содержит значение года (например, 2010). Заметьте, что Новый год, День независимости, День ветеранов и Рождество — каждый этот праздник всегда отмечается в определенный день в году, поэтому дату можно вычислить с помощью простой функции ДАТА.

Этот праздник всегда приходится на 1 января. Еще раз повторю формулу для его вычисления: =ДАТА(А1;1;1) .

День Мартина Лютера Кинга

Этот праздник приходится на третий понедельник января. Следующая формула рассчитывает дату Дня Мартина Лютера Кинга для года, указанного в ячейке А1:
=ДАТА(А1;1;1)+ЕСЛИ(2 .

День президента

День президента приходится на третий понедельник февраля. Формула для вычисления Дня президента для года из ячейки А1 такова:
=ДАТА(А1;2;1)+ЕСЛИ(2 .

Католическая Пасха

День памяти

Последним понедельником в мае является День памяти. Следующая формула вычисляет День памяти для года из ячейки А1:
=ДАТА(А1;6;1)+ЕСЛИ(2
Обратите внимание, что формула вычисляет первый понедельник в июне, а затем отнимает значение 7 от результата, возвращая таким образом последний понедельник мая.

День независимости

Праздник День независимости США всегда выпадает на 4 июля: =ДАТА(А1;7;4)

День труда

День труда отмечают в первый понедельник сентября. Следующая формула вычисляет День труда для года, указанного в ячейке А1:
=ДАТА(А1;9;1)+ЕСЛИ(2

День Колумба

Такой праздник, как День Колумба, выпадает на второй понедельник октября. Следующая формула вычисляет День Колумба для года из ячейки А1:
=ДАТА(А1;10;1)+ЕСЛИ(2

День ветеранов

Праздник День ветеранов всегда приходится на 11 ноября: =ДАТА(A1;11;11)

День благодарения

День благодарения отмечается в четвертый четверг ноября. Следующая формула вычисляет День благодарения для года, указанного в ячейке А1:
=ДАТА(А1;11;1)+ЕСЛИ(5

Католическое Рождество всегда приходится на 25 декабря: =ДАТА(А1:12;25)

Считаем будние и рабочие дни в Excel

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

Функция ДЕНЬНЕД

Функция ДЕНЬНЕД (WEEKDAY) в Excel возвращает число от 1 (воскресенье) до 7 (суббота), представляющее номер дня недели. Судя по всему, 16 декабря 2013 в формуле ниже выпадает на понедельник.

Ещё для отображения дня недели можно использовать функцию ТЕКСТ (TEXT).

Создайте пользовательский формат даты (дддд), чтобы отобразить название дня недели.

Функция ЧИСТРАБДНИ

Функция ЧИСТРАБДНИ (NETWORKDAYS) возвращает количество будних дней (исключая выходные) между двумя датами.

Если вы укажете список праздников, то функция ЧИСТРАБДНИ (NETWORKDAYS) вернёт количество рабочих дней (не считая выходных и праздничных дней) между двумя датами.

Календарь ниже поможет вам лучше понять функцию ЧИСТРАБДНИ (NETWORKDAYS).

Excel хранит даты в виде чисел и подсчитывает количество дней с 0 января 1900 года. Вместо того, чтобы подставлять диапазон ячеек в формулу, подставьте константы чисел, которые представляют эти даты. Чтобы это сделать, выделите E1:E2 в формуле ниже и нажмите F9.

Функция РАБДЕНЬ

Функция РАБДЕНЬ (WORKDAY) почти противоположна функции ЧИСТРАБДНИ (NETWORKDAYS). Она возвращает дату до или после указанного количества будних дней (выходные исключены).

Примечание: Функция РАБДЕНЬ (WORKDAY) возвращает порядковый номер даты. Примените формат даты к ячейке для её отображения.

Календарь ниже поможет вам лучше понять функцию РАБДЕНЬ (WORKDAY).

Опять же, если вы подставите список праздников, функция РАБДЕНЬ (WORKDAY) вернёт дату до или после указанного количества рабочих дней (исключая выходные и праздничные дни).

Отфильтруйте и выделите строки с выходными днями с помощью Kutools for Excel

Выделите строки с выходными в Excel

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

1. Выберите диапазон данных, в котором вы хотите выделить строки выходных дней.

2, Идти к Главная вкладку и нажмите Условное форматирование > Новое правило, см. снимок экрана:

3. В Новое правило форматирования диалоговое окно, нажмите Используйте формулу, чтобы определить, какие ячейки следует форматировать. вариант и введите эту формулу = WEEKDAY (C2,2 $)> 5 (C2 - ячейка даты начала в вашем диапазоне данных) см. снимок экрана:

4. Затем нажмите Формат , чтобы открыть Формат ячеек диалоговом окне, в открывшемся диалоговом окне нажмите Заполнять вкладка и выберите один цвет, который вы хотите отформатировать.

5, Затем нажмите OK > OK чтобы закрыть диалоговые окна, и строки с датой воскресенья или субботы были выделены. Смотрите скриншот:

Ноты:

1. Если вы хотите выделить рабочие дни с понедельника по пятницу, вы можете применить эту формулу = WEEKDAY (C2,2 $) <6 на шаге 3. (C2 это ячейка даты начала в вашем диапазоне данных)

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

Отфильтруйте и выделите строки с выходными днями с помощью Kutools for Excel

Если у вас есть Kutools for Excel установлен, вы также можете применить его Суперфильтр утилита, позволяющая быстро отфильтровать выходные дни и затем закрасить соответствующие строки желаемым цветом.

После бесплатная установка Kutools for Excel, сделайте следующее:

1. Нажмите Kutools Plus > Суперфильтр для отображения Суперфильтр панель. Смотрите скриншот:

3. Затем нажмите Фильтр, и отображаются только выходные дни. Смотрите скриншот:

5. Выберите строки с выходными днями и нажмите Главная > Цвет заливки чтобы выбрать цвет для выделения строк. Смотрите скриншот:

6. Нажмите Очистить в Суперфильтр панель для расширения диапазона. Смотрите скриншот:

Наконечник: Вы также можете фильтровать дни по конкретному дню недели.

Читайте также: