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

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

Argument ‘Topic id’ is null or empty

Сейчас на форуме

© Николай Павлов, Planetaexcel, 2006-2023
info@planetaexcel.ru

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

ООО «Планета Эксел» ИНН 7735603520 ОГРН 1147746834949

ИП Павлов Николай Владимирович ИНН 633015842586 ОГРНИП 310633031600071

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

Cоздаём графики работы, автоматически заполненяемые табеля учёта рабочего времени и т.п…. (продолжение)

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

Создадим в нашей книге новый лист, под названием ПРАЗДНИКИ, на нем организуем «умную таблицу», я назову её ВЫХОДНЫЕ (сменить имя таблицы можно на вкладке «Конструктор», когда выделена любая ячейка таблицы).

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

Присвоим имена нашим столбцам (без заголовков), чтобы их можно было использовать в правилах Условного форматирования.

Для этого нужно выделить ячейки, к примеру A1:A2, на вкладке Формулы выбрать команду Задать Имя, и в выпадающем окошке ввести имя для диапазона (я назвал его «Праздник», второму диапазону B1:B2 я присвоил имя «Перенос»).

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

У нас диапазоны формируются на основании «умной таблицы», поэтому при добавлении строк они будут расширяться автоматически.

Теперь необходимо дополнить формулу для условного форматирования, чтобы эти даты учитывались при определении выходных дней.

1. Выделяем диапазон ячеек, для которых нужно применять форматирование.
2. На вкладке Главная, выбираем Условное форматирование и в выпадающем списке пункт Управление правилами
3. В появившемся окне выбираем правило и нажимаем на кнопку Изменить правило.4, Меняем нашу формулу.

=ИЛИ(И(ДЕНЬНЕД(G1;2)>5;СЧЁТЕСЛИ(Перенос;G1)=0);СЧЁТЕСЛИ(Праздник;G1)>0)

Т.е. у нас должно выполняться несколько условий:

— чтобы день недели был субботой или воскресеньем и при этом даты не было в списке перенесенных дней (условия должны выполняться одновременно, за этим в формуле следит функция И (AND).

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

Так как эти условия не могут выполняться одновременно – нужно использовать функцию ИЛИ (OR).

Нажав на «ОК» (2 раза) мы увидим, что всё работает так, как мы задумывали и теперь эти даты тоже участвуют в определении выходных и рабочих дней.

Для тех кто хочет комплексно изучить Excel, понять его суть, логику и принципы работы курс «Excel бизнес-анализ и прогнозирование»
Кроме 27 часов материала, 12 домашних заданий и курсового проекта в чате онлайн поддержки вы сможете задавать свои вопросы и получать подобные подсказки в решение Ваших трудностей по работе с Excel.
Детально ознакомиться с программой курса, наполнением пакетов и ценами можно тут.

Автор статьи тренер DATAbi Михаил Беленчук

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

Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel Web App Excel 2010 Excel для Mac 2011 Excel Starter 2010 Еще. Меньше

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

Синтаксис

Аргументы функции ЧИСТРАБДНИ.МЕЖД описаны ниже.

• Нач_дата и кон_дата Обязательный. Даты, разницу между которыми требуется вычислить. Начальная дата может предшествовать конечной дате, совпадать с ней или быть позже нее.
• Выходной Необязательный. Указывает, какие дни недели являются выходными и не включаются в число рабочих дней между начальной и конечной датой. Значение может задаваться номером выходного дня или строкой, определяющей, какие дни являются выходными. Номера обозначают следующие выходные дни:

Номер выходного дня

Выходные дни

Строковые значения дней недели включают семь знаков, каждый из которых обозначает день недели (начиная с понедельника). Значение 1 представляет нерабочие дни, а 0 — рабочие дни. В строке допустимо использовать только знаки 1 и 0. При значении 1111111 всегда возвращается 0.

Например, 0000011 означает, что выходными днями являются суббота и воскресенье.

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

Замечания

• Если начальная дата позже конечной даты, возвращаемое значение будет отрицательным, а его модуль будет равен числу полных рабочих дней.
• Если start_date вне диапазона для текущего базового значения даты, NETWORKDAYS. InTL возвращает #NUM! значение ошибки #ЗНАЧ!.
• Если end_date вне диапазона для текущего базового значения даты, NETWORKDAYS. InTL возвращает #NUM! значение ошибки #ЗНАЧ!.
• Если строка выходных дней имеет недействительные длину или содержит недопустимые символы, NETWORKDAYS. InTL возвращает #VALUE! значение ошибки #ЗНАЧ!.

Пример

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

Получаем 22 предстоящих рабочих дня. Вычитает 9 нерабочитающих выходных дней (5 суббот и 4 воскресенья) из общего числа дней между двумя датами. По умолчанию суббота и воскресенье считаются нерабочитами.

Получаем -21, т. е. 21 прошедший рабочий день.

Из 32 дней между 1 января 2006 и 1 февраля 2006 года вычитаем 10 нерабочих дней (4 пятницы, 4 субботы и 2 праздника) и получаем 22 предстоящих рабочих дня. Используем аргумент 7 в качестве выходных дней, которыми являются субботы и воскресенья. Также на этой период времени приходится два праздника.

Получаем 22 предстоящих рабочих дня. Такой же период времени, как и в приведенном выше примере, но с выходными днями по субботам и средам.

ВЫДЕЛЕНИЕ ЦВЕТОМ ТОЛЬКО ВЫХОДНЫЕ ДНИ ПО ДАТЕ В EXCEL

КАК ВЫДЕЛИТЬ ЦВЕТОМ ТОЛЬКО ВЫХОДНЫЕ ДНИ ПО ДАТЕ В EXCEL

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

2. В появившемся окне выберите опцию: «Использовать формулу для определения форматируемых ячеек».

3. В поле ввода введите формулу: =ИЛИ(ДЕНЬНЕД(A2)=1;ДЕНЬНЕД(A2)=7)

4. После внесения всех настроек подтвердите их нажатием на кнопку ОК на всех открытых окнах.