Книги Проза Остросюжетная проза Молодёжная литература Современная зарубежная литература Классическая литература Интеллектуальная проза Романы взросления Детство Художественная литература для детей Научно-познавательные книги для детей KUMON Чевостик Развитие и обучение детей Досуг и творчество детей Книги для подростков Для родителей Комиксы для детей Детское творчество Умные книжки Подготовка к школе Необычный формат Подарочные Психология Популярная психология Стресс и эмоции Любовь и отношения Осознанность и медитация Книги для родителей Быть подростком Защита от токсичности Бизнес Аудиокниги Менеджмент Продажи Истории успеха Развитие сотрудников Предпринимателю Управление компанией Стратегия Управление проектами Переговоры Публичные выступления HR Российский бизнес IT Культура Автофикшн и биографии Серия «Таро МИФ» Серия «Мифы от и до» Подарочные книги Культурные истории, страноведение Искусство и архитектура Театр и кино, музыка, литература Серия «Главное в истории» Саморазвитие Спокойствие и душевное равновесие Аудиокниги Мечты и цели Мотивация Мозг и интеллект Продуктивность Психология Общение Сила воли Тайм-менеджмент Деньги Обучение Выбор профессии Принятие решений Осознанность Лайфстайл Современная магия Дом и сад Кулинария Велнес, красота, мода Творчество Вдохновение и мотивация Handmade и творческий бизнес Рисование для начинающих Рисование для продолжающих Леттеринг и каллиграфия Писательство Фотомастерская Активити для взрослых Легендарная серия Барбары Шер Психология творчества Дизайн Развитие творчества Творческий бизнес Визуальное мышление Творческое мышление МАК МИФ Комиксы Детские комиксы Взрослые комиксы Молодежные комиксы Серии Познавательные комиксы Здоровье и медицина Правильное питание Спорт Долголетие Бег Фитнес Медитация Здоровый сон Диеты Научпоп Физика Математика Экономика Здоровье и медицина Мышление и психология Технологии Подарочные книги Искусство, культура и путешествия Для детей Работа и бизнес Для души и уюта Захватывающие истории Время для себя Маркетинг Маркетинг и брендинг Генерация идей Копирайтинг, блогинг, СМИ Серия «Думай иначе» Настольные игры Курсы и мероприятия «Книжные» профессии Душа, ум и тело Карьера и бизнес Лектории Практикумы: hard skills Бесплатно Курсы месяца Получить профессию Все курсы Для бизнеса Электронная библиотека Офисная библиотека Детские подарки Подарки партнерам Продвижение бренда Курсы для компаний Издать книгу Издательство Работа у нас Логотип Предложить книгу Об издательстве Авторам Вопросы и ответы Контактная информация Блоги Блог МИФа Психология и саморазвитие Творчество Проза Кругозор Книжный клуб МИФа Комиксы Бизнес-блог Бизнесхак и маркетинг Формула менеджмента Саморазвитие Корпоративная культура Опыт МИФа Обзоры книг Папамамам Развитие ребенка Психология Вот так книга! Искусство учиться
Теги
Big Data
Бизнесхак и маркетинг
Как научить таблицы считать рабочие дни
2 октября 354 просмотра

Ренат Шагабутдинов
Ренат Шагабутдинов

Excel и Google Таблицы — мощнейшие инструменты с сотнями функций, из которых мы обычно используем от силы десяток. Но чем больше о них узнаешь — тем проще работать. Сегодня расскажем о том, как заставить Excel и Google Таблицы считать рабочие дни и «натаскать» их на производственный календарь.

Если хотите узнать о магии таблиц больше — вам помогут курсы и практикумы нашего незаменимого «табличного волшебника» Рената Шагабутдинова. Их несколько: «Магия Excel»«Драйв. Гугл драйв» и «P7-Офис. Магия таблиц». А скоро стартует практикум «Google Таблицы для работы и жизни». На каждый действует скидка 30% по промокоду BLOG.

Функции работы с датами

Итак, в Excel и Google Таблицах есть две функции для работы с датами:

  1. ЧИСТРАБДНИ / NETWORKDAYS — количество дней между двумя датами.
  2. РАБДЕНЬ / 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, можно прочитать здесь. Пользуйтесь!

Обложка отсюда.

Теги
Big Data
Похожие статьи