Табель учета рабочего времени в excel. Учет времени работы сотрудников. Скачать пример
Важно! Если у Вашей организации большой штат, воспользуйтесь табелем учета версии 3.3. Он обладает абсолютно идентичной механикой учета рабочего времени и расчета заработной платы, но с более продвинутым и функциональным интерфейсом по заведению сотрудников. Для этого перейдите на соответствующую статью по данной ссылке: Табель учета 3.3 для большого штата сотрудников
Также на нашем сайте представлены различные вариации исполнения табеля учета рабочего времени, ознакомиться с которыми Вы можете перейдя по соответствующим ссылкам статей:
— Табель почасового учета TimeSheet (Фото дня)
— Табель учета рабочего времени в днях по форме Т-13
— Табель расчет и планирование вахты
— Табель учета рабочего времени с учетом ночных смен
=РАБДЕНЬ(«11.06.2013»;1) – возвращает результат 12.06.2013.
=РАБДЕНЬ(«11.06.2013»;4) – результат 17.06.2013.
=РАБДЕНЬ(«11.06.2013»;1; «12.06.2013») – функция вернет результат 13.06.2013.
=РАБДЕНЬ(«11.06.2013»;1; ) – результатом будет 14.06.2013.
Производственный календарь в MS Excel
- Блок «Учет дней», в котором подсчитываются все рабочие активности сотрудника в течении месяца. Отображение тех или иных столбцов можно задать на листе «Настройки»;
- Блок «Расчет Переработок». Здесь макрос считает плановые переработки/недоработки (от количества часов по производственному календарю), переработки сверх нормы: первые 2 часа и последующие часы, работу в выходные и праздничные дни. Над столбцами присутствуют коэффициенты увеличения оплаты труда за соответствующие активности, которые могут быть изменены вручную. Отображение столбцов также задается на листе «Настройки».
Пример использования:
Обратите внимание на пример таблицы. Последние 2 строки возвращают одинаковый результат. Т.к. минимальная дата, которая может быть представлена в Excel, имеет 1900 год, то ко всем числам, которые переданы аргументу «Год» и меньше минимального года, то к ним автоматически прибавляется 1900.
Рассчитать количество рабочих дней
Задача: подсчитать, сколько рабочих дней попадает между двумя датами. Функция Excel ЧИСТРАБДНИ прекрасно справляется с задачей, если у вас 5-дневная рабочая неделя с субботой и воскресеньем в качестве выходных. Эта заметка покажет вам, как выполнить расчет рабочей недели любой продолжительности и любым набором выходных.
Прим. Багузина. Следует отметить, что, начиная с версии 2010 в Excel появилась более гибкая функция ЧИСТРАБДНИ.МЕЖД, которая дает возможность расчета для любой продолжительности рабочей недели и любого (но постоянного) набора выходных дней. Эта функция будет описана во второй части заметки. Тем не менее, подход Билла Джелена остается интересным и сам по себе.
Рис. 1. ЧИСТРАБДНИ предполагает, что рабочая неделя длится с понедельника по пятницу
Для начала определите диапазон, и назовите его Праздники. Для этого выделите диапазон вместе с заголовком и пройдите по меню ФОРМУЛЫ –> Создать из выделенного (рис. 2). Убедитесь, что выбрана опция в строке выше. Кликните Ok в окне Создание имени из выделенного диапазона.
Определив диапазон, снова введите формулу в С3. Теперь она примет вид: =ЧИСТРАБДНИ(A3;B3;Праздники)
Рис. 3. Число рабочих дней при 6-дневной рабочей неделе
Рассмотрим работу формулы подробнее. В отношении всех дат, входящих в диапазон от даты старта до даты окончания (включительно), формула проверяет две вещи. Во-первых, не является ли какая-либо дата праздником. Во-вторых, какие даты в диапазоне дат воскресенья.
Вам нужен быстрый способ, чтобы сравнить каждую дату, начиная с А3, и заканчивая В3 со списком праздников. В данном примере (в ячейке С3), у вас только 8 дней, но в строке 5, у вас более 300 дней. Формула использует тот факт, что в Excel даты хранятся в виде порядковых номеров. Хотя в ячейке A3 отображается 20 января 2009, Excel на самом деле хранит дату как числовое значение 39854.
Первая функция ДВССЫЛ формирует массив всех дат, входящих в диапазон. Аргументы внутри ДВССЫЛ сцепляют дату (хранящуюся как число) из ячейки А3 с датой из B3, разделяя их двоеточием. Промежуточный результат вычисления можно увидеть в окне Вычисление формулы (рис. 4). Фрагмент ДВССЫЛ(A3& » : » &B3) превратился в $39854:$39861.
Обычно аргумент ДВССЫЛ это диапазон типа » А2:IU2 » . Однако, если вы когда-либо использовали ссылку на диапазон от первого столбца (A) до последнего столбца, вы видели, что =СУММ(2:2) эквивалентна =СУММ(А2:IV2) в Excel 2003 и =СУММ(А2:XFD2) в Excel 2007. Таким образом, можно ссылаться на целые строки. Фрагмент СТРОКА($39854:$39861) возвращает массив из восьми чисел (рис. 5).
Теперь вы можете сравнить этот массив из восьми чисел с диапазоном праздничных дат. Фрагмент СЧЁТЕСЛИ(Праздники;СТРОКА(ДВССЫЛ(A3& » : » &B3))) превратился в СЧЁТЕСЛИ(;). Формула подсчитывает, сколько раз каждый праздник (первый аргумент функции СЧЁТЕСЛИ) совпадет с одним из значений из диапазона дат (второй аргумент; см. рис. 6).
Функция СЧЁТЕСЛИ возвращает 1, если праздник находится в диапазоне дат и 0, если – нет (рис. 7). Так как вас интересуют не праздничные даты, а рабочие, вы сравниваете результат функции СЧЁТЕСЛИ с нулем. =0 возвращает . Операция минус минус преобразует массив логических значений в числовые: .
Рис. 8. Дополнительные столбцы, иллюстрирующие работу формулы
В столбце G, проверяется, равен ли результат СЧЁТЕСЛИ единице. Если да, возвращается ИСТИНА, иначе – ЛОЖЬ. В столбце H введена формула —G, которая преобразует каждое значение ИСТИНА в 1, а ЛОЖЬ в 0. На рис. 3, ячейки Н2:Н9 представляют собой виртуальные результаты первой половины формулы, которая находит даты – не праздники.
Вторая половина формулы использует функцию ДЕНЬНЕД, чтобы найти даты, не являющиеся воскресеньями. Функция ДЕНЬНЕД возвращает порядковый номер дня недели и имеет два аргумента, второй из которых определяет, какой день недели считать первым (рис. 9).
Если у вас 7-дневная рабочая неделя, вам остается только исключить праздничные дни. Формула упрощается: =СУММПРОИЗВ( — — (СЧЁТЕСЛИ(Праздники;СТРОКА(ДВССЫЛ(A3& » : » &B3)))=0)).
Рис. 11. Функция ВЫБОР для произвольного числа рабочих дней
Поскольку функция ВЫБОР обычно не возвращает массив, вы должны ввести следующую формулу, используя сочетание клавиш Ctrl+Shift+ввод:
Резюме: вы познакомились с концепцией создания огромного массива всего из двух значений. Например, =СТРОКА(ДВССЫЛ(«1:10000»)) создает массив 10 000 значений от 1 до 10 000. Вы можете использовать эту концепцию, чтобы проверить большой массив дат, задав лишь начальную и конечную точку.
Начиная с версии 2010 в Excel появилась более гибкая функция ЧИСТРАБДНИ.МЕЖД, которая дает возможность расчета для любой продолжительности рабочей недели и любого (но постоянного) набора выходных дней (рис. 12). В ячейке С2 формула имеет вид: =ЧИСТРАБДНИ.МЕЖД(A2;B2; » 0101011 » ;Праздники).
Синтаксис функции: ЧИСТРАБДНИ.МЕЖД(нач_дата;кон_дата;[выходной];[праздники])
Аргумент Выходной является необязательным. Указывает, какие дни недели являются выходными и не включаются в число рабочих дней между начальной и конечной датой. Значение может задаваться номером выходного дня (всего 17 значений, подробнее см. справку Excel) или строкой, определяющей, какие дни являются выходными.
Как Убрать Праздники и Выходные в Excel. | 📝Справочник по Excel
Пример использования:
=МИНУТЫ(«22:45:00 «) – функция вернет значение 45.
=МИНУТЫ(0,428472222222222) – возвращает значение 17, так как 0,428472222222222 является числовым представлением времени 10:17:00.
