Содержание
- Синтаксис функции
- Пример
- Примеры использования функций ЧИСТРАБДНИ и ЧИСТРАБДНИ.МЕЖД в Excel
- Расчет отпускных для увольняющегося сотрудника в Excel
- Начисление зарплаты сотрудникам по отработанным дням в Excel
- Особенности использования функций ЧИСТРАБДНИ и ЧИСТРАБДНИ.МЕЖД в Excel
- Примеры использования функции СЕГОДНЯ в Excel
- Автоматизированный график дней рождений сотрудников в Excel
- Анализ дебиторской задолженности до текущей даты в Excel
- Особенности использования функции СЕГОДНЯ в Excel
Возвращает количество рабочих дней между нач_дата и кон_дата. Праздники и выходные в это число не включаются. Функцию ЧИСТРАБДНИ можно использовать для вычисления оплаты работника на основе количества дней, отработанных в указанный период. Если данная функция недоступна или возвращает ошибку #ИМЯ?, установите и загрузите надстройку «Пакет анализа».
Синтаксис
ЧИСТРАБДНИ(нач_дата;кон_дата;праздники)
Важно! Даты должны вводиться с использованием функции ДАТА или как результат других формул и функций. Например, следует использовать ДАТА(2008;5;23) для 23-го мая 2008 года. Проблемы могут возникнуть, если даты вводятся как текст.
Нач_дата — дата, которая представляет начальную дату.
Кон_дата — дата, которая представляет конечную дату.
Праздники — необязательный список из одной или нескольких дат, которые требуется исключить из рабочего календаря, например государственные праздники. Список может представлять собой диапазон ячеек, содержащих даты, или массив констант или числовые форматы, представляющие даты.
Внимание!
Microsoft Excel хранит даты как ряд последовательных номеров, что позволяет выполнять над ними вычисления. По умолчанию день 1 января 1900 года имеет номер 1, а 1 января 2008 — номер 39448, так как интервал в днях между этими датами составляет 39448. Microsoft Excel для «Макинтоша» по умолчанию использует другую систему дат.
Если любой из аргументов не является допустимой датой, то функция ЧИСТРАБДНИ возвращает значение ошибки #ЗНАЧ!.
Примечание. Чтобы в показанном на рисунке примере преобразовать диапазон ячеек, содержащий праздничные дни кроме суббот и воскресений, в массив констант, выделите ссылку A5:A10 в формуле, а затем нажмите клавишу F9. В этом случае в аргумент функции A5:A10 будет заменен на массив фиксированных значений, представляющий даты в цифровом формате.
Еще про Excel.
Выбор другого помощника по Office
Некоторые сведения в этом разделе могут быть неприменимы к отдельным языкам.
1. Щелкните изображение помощника.
Если помощник не отображается, выберите команду Показать помощника в меню Справка.
2. В выноске помощника нажмите кнопку Параметры.
Если выноска не отображается, щелкните изображение помощника.
3. На вкладке Коллекция выберите нужного помощника с помощью кнопок Назад и Далее.
Примечания
При наличии доступа к Интернету можно загрузить дополнительных помощников с веб-узла Microsoft Office. Выберите в меню Справка команду Office в Интернете.
Гиперссылки в данном разделе указывают на ресурсы Интернета. Вернуться в окно справки можно в любое время.
Отображение и скрытие помощника по Office в мастерах
Некоторые сведения в этом разделе могут быть неприменимы к отдельным языкам.
Чтобы включить или отключить использование помощника по Office для предоставления справки в мастерах, выполните одну из следующих процедур.
Отображение и скрытие помощника только для одного мастера
Запустите мастер, а затем нажмите кнопку
в окне мастера.
Примечание. Если помощник по Office не отображается, по нажатию кнопки
помощник будет включен.
Отображение и скрытие помощника для всех мастеров
1. В выноске помощника по Office нажмите кнопку Параметры.
Если выноска помощника по Office не отображается, щелкните изображение помощника.
2. На вкладке Параметры установите или снимите флажок отображать справку для мастеров.
Примечание. Справка, отображаемая помощником, имеется не во всех мастерах.
Отображение и скрытие поля «Задать вопрос»
1. В меню Сервис выберите команду Настройка.
2. Откройте вкладку Параметры.
3. Щелкните правой кнопкой мыши поле Задать вопрос
в строке меню.
4. Установите или снимите флажок Отображать окно вопроса.
Примечание. В случае скрытия поля Задать вопрос оно продолжает отображаться до закрытия диалогового окна Настройка.
Включение и отключение звукового сопровождения помощника по Office
Некоторые сведения в этом разделе могут быть неприменимы к отдельным языкам.
Для прослушивания звукового сопровождения помощника по Office на компьютере должна быть установлена звуковая плата.
1. Щелкните изображение помощника.
Если помощник по Office не отображается, выберите команду Показать помощника в меню Справка.
2. В выноске помощника по Office нажмите кнопку Параметры.
Если выноска помощника по Office не отображается, щелкните изображение помощника.
3. На вкладке Параметры установите или снимите флажок Звуковое сопровождение.
Функция ЧИСТРАБДНИ( ) , английская версия NETWORKDAYS() , возвращает количество рабочих дней между двумя датами, т.е. при подсчете праздники и выходные не учитываются.
Синтаксис функции
ЧИСТРАБДНИ(нач_дата;кон_дата;)
Праздники — необязательный параметр, представляет собой ссылку на диапазон ячеек, содержащий одну или нескольких дат, которые требуется исключить из рабочего календаря, например государственные праздники.
Пример
В файле примера приведен пример использования функции с учетом праздников:
=ЧИСТРАБДНИ(A6;B6;G6:G26)
G6:G26 — ссылка на диапазон ячеек, содержащий праздничные даты. Если диапазон пуст или содержит пустые ячейки, то ошибки не возникает.
и без учета праздников:
=ЧИСТРАБДНИ(A6;B6)
В файле примера для удобства создан Динамический диапазон Праздники , который представляет собой ссылку на диапазон праздничных дней. При добавлении праздничных дат, они автоматически добавляются в диапазон Праздники .
В этом случае пример использования функции выглядит так:
=ЧИСТРАБДНИ(A6;B6;Праздники)
Если диапазон Праздники пуст, то функция ЧИСТРАБДНИ() возвращает ошибку.
Функция ЧИСТРАБДНИ() предполагает, что на неделе 2 выходных: Суббота и Воскресенье. Если необходимо подсчитать рабочие дни, например, в случае четырехдневной недели или когда выходные дни — воскресенье и среда, то читайте статью Подсчет рабочих дней при нестандарной рабочей неделе .
Функция ЧИСТРАБДНИ в Excel используется для расчета числа дней между датами начала и окончания какого-либо события за исключением выходных и праздничных, и возвращает соответственное числовое значение.
Функция ЧИСТРАБДНИ.МЕЖД в Excel выполняет расчет количества дней между указанными датами начала и окончания некоторого события за вычетом выходных и праздничных и возвращает соответствующее числовое значение. В отличие от функции ЧИСТРАБДНИ, имеет синтаксис, позволяющий указать число рабочих дней в неделе и их позиции.
Примеры использования функций ЧИСТРАБДНИ и ЧИСТРАБДНИ.МЕЖД в Excel
Пример 1. Определить количество выходных между датами 20.09.2018 и 30.10.2018 включая праздничные дни, которые приходятся на следующие даты: 28.09.2018 и 05.10.2018.
Вид исходной таблицы данных:
Для расчета используем следующую формулу:
Описание аргументов:
- (B3-B2) – общее число дней между указанными датами;
- B2 – дата начала некоторого события;
- B3 – дата окончания некоторого события;
- B4:B5 – диапазон ячеек, содержащих праздничные дни.
Примечания:
- Данные в ячейках B2:B5 указаны в виде результатов выполнения функций ДАТА.
- Ячейка B6 должна иметь числовой формат данных для отображения корректных результатов.
В результате расчетов получим:
Между указанными в условии датами всего 13 выходных с учетом 2-х праздников.
Расчет отпускных для увольняющегося сотрудника в Excel
Пример 2. Рассчитать отпускные для увольняющегося работника, который поступил на работу 10.05.2015 и был уволен 15.06.2018, за время работы ни разу не брал отпуск. Всего за весь период работы было выплачено 457240 рублей зарплаты. Праздничными днями являются следующие даты: 1-8 января, 23 февраля, 8 марта, 1 мая, 9 мая, 12 июня, 4 ноября.
Вид исходной таблицы:
Для расчета общего количества отпускных дней за период работы используем формулу:
В результате получим:
Определим сумму выпускных по формуле:
Выражение B21/ЧИСТРАБДНИ(B2;B3;B6:C6:C6:D19:E18) определяет среднюю з/п за рабочий день. Умножив это значение на количество дней отпуска за весь период получим общую сумму отпускных:
Начисление зарплаты сотрудникам по отработанным дням в Excel
Пример 3. Определить зарплату работника за июль 2018 года, если расчет оплаты производится по отработанным дням, при этом по уставу фирмы до 15 числа каждого месяца число выходных в неделе — 2 (суббота и воскресенье), а после 15 – один (воскресенье). Если 15-е число любого месяца является субботой, данный день выходным не является.
Вид исходной таблицы данных:
Для расчета используем следующую формулу:
Функция ЕСЛИ выполняет проверку, является ли 15-е число июля субботой. Число рабочих дней вычисляется как сумма числа рабочих дней в неделях, для которых количество выходных равно двум, и неделях с одним выходным днем. Если 15-е является субботой, то полное количество дней с двумя выходными рассчитывается как разница дат ДАТА(2018;7;15)-6 и начало месяца.
Результат расчетов:
Количество рабочих дней до 15-го числа составило 10 дней (15.07.2018 – воскресенье), после 15-го – 14. Произведение 24 рабочих дней и оклада по дням (1320 рублей) равно 31680 рублей зарплаты за месяц.
Особенности использования функций ЧИСТРАБДНИ и ЧИСТРАБДНИ.МЕЖД в Excel
Функция ЧИСТРАБДНИ имеет следующий синтаксис:
=ЧИСТРАБДНИ(нач_дата;кон_дата;)
Описание аргументов:
- нач_дата – обязательный аргумент, принимающий значение формата даты, которое характеризуют дату начала какого-либо события (точку отсчета числа рабочих дней);
- кон_дата – обязательный аргумент, принимающий значение формата даты, которые характеризуют дату окончания какого-либо события;
- – необязательный для заполнения аргумент, принимающий ссылку на ячейку или диапазон ячеек, содержащие данные в формате даты, которые указывают даты праздничных дней. Может быть указан в виде массива, содержащего представления дат в виде чисел в коде времени Excel.
Функция ЧИСТРАБДНИ.МЕЖД имеет следующий синтаксис:
Описание аргументов:
=ЧИСТРАБДНИ.МЕЖД(нач_дата;кон_дата;;)
Описание аргументов:
- нач_дата и кон_дата – обязательные аргументы, смысл которых тождественен одноименным аргументам функции ЧИСТРАБДНИ;
- – необязательный аргумент, который может быть указан в виде текстовой строки или числового значения из диапазона от 1 до 7 и от 11 до 17 соответственно, характеризующих число выходных и их позиции в неделе. По умолчанию принимается значение 1, соответствующее выходным в субботу и воскресенье. Если указано число 2 – выходными считаются воскресенье и понедельник. Далее по аналогии вплоть до числа 7, соответствующего выходным дням в пятницу и субботу. Число 11 характеризует единственный выходной день в воскресенье, 12 – в понедельник и далее по аналогии вплоть до 17, характеризующего субботу в качестве единственного выходного дня. Текстовый вариант представления аргумента имеет вид «0000011», где 1 – выходной день, 0 – рабочий день. Если строка состоит из 7 единиц («1111111»), функция ЧИСТАРАБНИ.МЕЖД вернет значение 0;
- – необязательный аргумент в виде ссылки на ячейку или диапазон ячеек, содержащих данные в формате даты, указывающие даты праздничных дней, либо массив числовых значений, являющихся представлениями дат в коде времени Excel.
Примечания:
- Аргументы нач_дата и кон_дата обеих рассматриваемых функций должны быть указаны в виде результатов выполнения функции ДАТА или прочих функций, возвращающих данные в формате даты. Представление дат в виде текстовых строк может привести к непредсказуемым результатам.
- Если в качестве аргумента нач_дата была передана более поздняя дата чем та, которая передана в качестве аргумента кон_дата, результатом выполнения функций ЧИСТРАБДНИ и ЧИСТАРБДНИ.МЕЖДУ будет отрицательное число, модуль которого равен количеству рабочих дней между указанными датами.
- Любые аргументы рассматриваемых функций, принимающие даты вне предела допустимых значений в Excel, будут приводить к возникновению ошибок #ЧИСЛО!
- Если в качестве аргумента функции ЧИСТРАБДНИ.МЕЖД передано число, которое не принадлежит диапазонам допустимых значений, или строка, содержащая недопустимые символы (любые символы, кроме «0» и «1»), большее или меньшее количество символов, чем число дней в неделе, результатом выполнения будут коды ошибок #ЧИСЛО! или #ЗНАЧ!.
Функция СЕГОДНЯ автоматически определяет и возвращает числовое значение текущей даты в коде времени Excel при каждом открытии файла.
Примеры использования функции СЕГОДНЯ в Excel
Пример 1. Компания ведет учет данных о своих сотрудниках и хранит соответствующие данные в таблицах Excel. В одной из таблиц хранятся даты рождения сотрудников. Необходимо добавить столбец, в котором будут отображаться динамически обновляемые данные о текущем возрасте сотрудников.
Исходная таблица данных:
Определим возраст первого сотрудника с помощью следующей формулы:
Функция ГОД возвращает числовое значение года из указанной даты. Например, запись ГОД(СЕГОДНЯ()) вернет значение 2018, а ГОД(B3) – 1982. Для корректного отображения результатов необходимо отформатировать ячейки С3:C12 в Числовой формат. Результаты вычислений для всех сотрудников:
В отличие от ручного способа ввода текущего возраста сотрудника, представленные выше данные будут автоматически обновляться.
Автоматизированный график дней рождений сотрудников в Excel
Пример 2. Используя таблицу из первого примера добавить столбец, в котором будет отображаться количество дней до дня рождения каждого сотрудника. Если день рождения сотрудника сегодня – отображать соответствующую запись.
Исходная таблица:
Для вычислений используем следующую формулу:
Функция РАЗНДАТ вычисляет разницу двух указанных дат и представляет полученное значение в зависимости от вида третьего аргумента, используемого для форматирования результатов. Например, для получения количества лет используется символ «y”, соответствующий первой букве слова «year» (год), «d” – для расчета количества дней (days – дни).
Результаты вычислений для остальных сотрудников:
Для отображения записи о том, что день рождения сотрудника сегодня, используем следующую формулу:
Данная формула является немного модифицированным вариантом предыдущей формулы. В частности, выполняется проверка двух условий: И(МЕСЯЦ(B13)=МЕСЯЦ(СЕГОДНЯ());ДЕНЬ(СЕГОДНЯ())=ДЕНЬ(B13)), то есть, соответствуют ли номера месяцев и дней в дате рождения в сравнении с текущей датой. Если результат вычислений – ИСТИНА, будет возвращена текстовая строка «ДР», если ЛОЖЬ – производится расчет количества дней до следующего дня рождения.
Например, произведем расчет для сотрудника, у которого сегодня день рождения:
Анализ дебиторской задолженности до текущей даты в Excel
Пример 3. Предприятию был выставлен долг со следующими условиями:
- За каждый день просрочки начисляются штрафы по ставке 5% от текущей суммы долга плюс начисленные проценты в день.
- По истечению 20-дневного срока с момента первого начисления штрафа будет проведено слушание в суде, проценты начисляться не будут.
Необходимо ввести формулу, которая будет рассчитывать ежедневно актуальную сумму долга.
Исходная таблица данных:
Для расчета текущей задолженности используем формулу:
Данная формула является видоизмененным вариантом формулы для расчета доходности при известных начальной сумме и числа периодов капитализации:
В качестве показателя степени используется выражение ДНИ(СЕГОДНЯ();B4), которое определяет количество прошедших дней от даты выставления долга до сегодняшней даты.
Поскольку сумма задолженности будет расти на протяжении 20 дней, используем следующий вариант записи:
Таким образом, была добавлена проверка условия, прошло ли 20 и более дней с момента выставления долга. Если да, сумма долга будет фиксированной и рассчитана по формуле B2*СТЕПЕНЬ((1+B3);20).
Результат вычислений:
Особенности использования функции СЕГОДНЯ в Excel
Функция имеет синтаксис без аргументов:
=СЕГОДНЯ()
Данная функция не принимает аргументов.
Примечания:
- В Excel используется специальный формат представления дат для упрощения операций по расчету промежутков времени и дат. Excel работает с датами от 00.01.1900 (нулевой день был введен специально, поскольку отсчет начинается с нуля) до 31.12.9999, при этом каждая дата из данного промежутка представлена в виде количества дней, прошедших от начала отсчета – 00.01.1900.
- Если в результате выполнения функции СЕГОДНЯ требуется получить значение в форме записи дат, необходимо настроить соответствующий формат данных, отображаемых в ячейке. Если необходимо получить число в коде времени Excel, следует выбрать Числовой формат данных.
- Функция СЕГОДНЯ предоставляет динамически обновляемый результат в зависимости от даты открытия книги или обновления ее данных.
- Автоматическое обновление возвращаемого данной функцией результат может не происходить, если в пункте меню Параметры категории Формулы разделе Параметры вычислений не выбран вариант автоматического вычисления введенных формул.
- Данная функция сама по себе используется достаточно редко, однако часто применяется для вычисления разницы дат совместно с другими функциями для работы со временем и датами.