Excel и Google Таблицы — мощнейшие инструменты с сотнями функций, из которых мы обычно используем от силы десяток. Но чем больше о них узнаешь — тем проще работать. Сегодня расскажем о том, как заставить Excel и Google Таблицы считать рабочие дни и «натаскать» их на производственный календарь.
Функции работы с датами
Итак, в Excel и Google Таблицах есть две функции для работы с датами:
- ЧИСТРАБДНИ / NETWORKDAYS — количество дней между двумя датами.
- РАБДЕНЬ / WORKDAY — дата, которая наступит по прошествии заданного числа рабочих дней.
Например, нам нужно знать, какая дата наступит через 30 рабочих (а не календарных) дней после определенной даты. Для этого нужна функция РАБДЕНЬ:
=РАБДЕНЬ(дата; число рабочих дней; [Праздники])
У обеих функций есть необязательный аргумент «Праздники» — он позволяет ссылаться на отдельный диапазон с датами, которые не нужно считать рабочими. По умолчанию все дни с понедельника по пятницу при расчетах будут считаться рабочими, а государственные и корпоративные праздники нужно исключать через последний аргумент этих функций.
Поэтому если нам нужно исключить какие-то дни как праздничные, их нужно ввести в отдельные ячейки и затем ссылаться на эти ячейки:
=РАБДЕНЬ(B1;30;G1:G2)
Функция ЧИСТРАБДНИ возвращает не дату, как РАБДЕНЬ, а число — это число рабочих дней в промежутке от одной даты до другой:
=ЧИСТРАБДНИ(начальная дата; конечная дата;[Праздники])
Даты, как и в других функциях, могут быть статичными (их можно указать внутри функции в кавычках или ссылаться на ячейки с ними), а могут быть и расчетными.
Допустим, нам нужно вычислить число рабочих дней в текущем году на текущую дату:
=ЧИСТРАБДНИ(первый день года; текущая дата; [Праздники])
Текущую дату вычислить легко — это функция СЕГОДНЯ / TODAY. А вот первый день года можно вычислить с помощью функции ДАТА / DATE, которая позволяет сформировать дату из трех составляющих — года, месяца и дня:
=ДАТА(год; месяц; день)
Например, следующая функция будет возвращать 01.01.2023:
=ДАТА(2023;1;1)
Если нам нужна первая дата текущего года, то второй и третий аргументы останутся единицами, а год можно вычислять как год от сегодняшней даты:
=ДАТА(ГОД(СЕГОДНЯ();1;1)
Итак, если подытожить, то функция для расчета рабочих дней в текущем году будет выглядеть так:
=ЧИСТРАБДНИ(ДАТА(ГОД(СЕГОДНЯ());1;1); СЕГОДНЯ() )
У обеих функций есть «международная» версия (.МЕЖД или .INTL на конце), в которой задается специальный тип рабочей недели. Вы можете выбрать один из вариантов в списке или ввести соответствующее ему число вручную. Например, 7 — это выходные в пятницу и субботу.
Также вы можете задать задать ваш собственный вариант недели (в том же аргументе) в формате 0011001, где 1 = выходной, а 0 = рабочий день.
Даты праздников (производственный календарь)
Хорошо. А откуда взять праздничные дни, ведь они не учитываются в этих функциях автоматически?
Есть прекрасный сайт с производственным календарем для каждого года начиная с 2014 — он работает для России и нескольких стран постсоветского пространства. Форматы там разные. Например, в формате XML у дат есть атрибут t с типом и можно извлечь только праздничные дни (тип 1), только сокращенные рабочие дни (тип 2) или рабочие субботу и воскресенье (тип 3). В формате TXT есть просто список всех выходных и праздничных дней без типов.
Как получать данные? Можно разово вручную — скачать CSV и открыть в Excel / Google Таблицах или открыть ссылку с TXT-форматом, выделить все (Ctrl + A), скопировать (Ctrl + C) и вставить (Ctrl + V) в Excel.
Но так придется вставлять данные вручную для каждого года. Если вам нужна формула, которая будет возвращать данные для текущего года, можно воспользоваться функциями ГОД / YEAR и СЕГОДНЯ / TODAY, как мы делали выше. Следующее сочетание будет возвращать номер текущего года:
ГОД(СЕГОДНЯ())
Далее его можно будет подставить в ссылку вместо значения конкретного года, так как ссылки меняются только на номер года, например:
http://xmlcalendar.ru/data/ru/2023/calendar.txt
http://xmlcalendar.ru/data/ru/2024/calendar.txt
http://xmlcalendar.ru/data/ru/2025/calendar.txt
Соответственно, ссылка на календарь текущего года в TXT-формате в формуле будет выглядеть так:
“http://xmlcalendar.ru/data/ru/» & ГОД(СЕГОДНЯ()) & «/calendar.txt”
Далее можно ее загрузить одной из функций. В Google Таблицах это IMPORTDATA:
=IMPORTDATA(“http://xmlcalendar.ru/data/ru/” & YEAR(TODAY()) & «/calendar.txt»)
В Excel (только на WIndows) это функция ВЕБСЛУЖБА / WEBSERVICE. Чтобы она не возвращала все даты «слипшимся» списком в одной ячейке, добавим функцию ТЕКСТРАЗД / TEXTSPLIT, чтобы разделить по переносу строку (символ с номером 10 нельзя напечатать, поэтому используем функцию СИМВОЛ / CHAR):
=ТЕКСТРАЗД(ВЕБСЛУЖБА(“http://xmlcalendar.ru/data/ru/”&ГОД(СЕГОДНЯ()) &”/calendar.txt”);;СИМВОЛ(10))
Но, как вы можете видеть, Excel не воспринимает эти значения как даты. Придется поколдовать: убрать непечатаемые символы через функцию ПЕЧСИМВ / CLEAN и сделать текст настоящей датой с помощью ДАТАЗНАЧ / DATEVALUE:
=ДАТАЗНАЧ(ПЕЧСИМВ(ТЕКСТРАЗД(ВЕБСЛУЖБА(“http://xmlcalendar.ru/data/ru/”&ГОД(СЕГОДНЯ()) &”/calendar.txt”);;СИМВОЛ(10))))
Про то, как извлекать отдельный тип дат из формата XML, можно прочитать здесь. Пользуйтесь!
Обложка отсюда.
