Автор: Если необходимо оперативно сделать финансовую электронную таблицу или предстоит совместная с коллегами работа над электронными таблицами, отличной бесплатной альтернативой Excel является Google Sheets.
Достоинством программ для работы с электронными таблицами можно считать их гибкость. Электронная таблица может представлять собой базу данных, вычислительную систему, платформу для статистического моделирования, текстовый редактор, библиотеку мультимедиа, список дел и т.д. Возможности приложений подобного рода почти неограниченны. Одним из популярных вариантов применения электронных таблиц, в том числе Google Sheets, является отслеживание почасового графика работы сотрудников и оплачиваемых часов.
Если применять Google Sheets для этих целей, следует вычислять разницу между двумя временными метками. Например, если кто-то пришёл на работу в 9:15 и ушёл в 16:30, продолжительность его рабочего времени составила 7 часов 15 минут. Если вы собираетесь применять Sheets для подобного рода вычислений, то быстро поймёте, что приложение изначально создавалось не для подобных задач.
Хотя Google Sheets не предназначается специально для регистрации рабочего времени, при небольшой подготовке можно выполнять и такую задачу. В данной статье показано, как автоматически вычислять разницу между двумя временными метками в Google Sheets.
Мы будем рассматривать формат расписания, где указывается время начала работы, время завершения и расчётная продолжительность.
Вычисление продолжительности времени в Google Sheets
Для измерения разницы во времени между значениями в двух ячейках сначала нужно указать Google Sheets, что в ячейках находится время. Если этого не сделать, разница между 9:00 и 10:00 выдаст результат 100, а не 60 минут или 1 час.
Чтобы правильно считать разницу во времени, столбцы времени нужно установить в формате «Время», а столбец продолжительности как «Продолжительность». Для настройки электронной таблицы:
- Откройте таблицу Google Sheets.
- Выберите первый столбец («Время начала») и нажмите на раскрывающийся список формата 123 в меню. В нём выберите значение «Время».
- Повторите описанные выше действия для столбца «Время завершения».
- В столбец «Отработанные часы» поставьте формат «Продолжительность».
- Теперь у столбцов выставлен нужный формат для вычисления продолжительности времени между двумя указанными временными метками.
В этом примере «Время начала» находится в столбце A, начиная с ячейки A2, а «Время завершения» в столбце C, начиная с ячейки C2. Отработанное время в часах располагается в столбце E. При правильно указанных форматах вычисление происходит очень просто. Нужно применять формулу ‘=(C2-A2)’. Она вычисляет разницу во времени в двух ячейках и выдаёт результат в часах.
Можно усложнить расчёт, указывая кроме продолжительности ещё и дату. Этот может пригодиться, если бывают рабочие смены продолжительностью свыше 24 часов или если два дня включены в одну смену. Для этого задайте для столбцов времени начала и завершения смены формат «Дата и время».
Выполняя описанные выше действия, при помощи указанной формулы в Google Sheets можно быстро вычислить продолжительность.
Добавление временных промежутков и перерывов при расчёте времени
При вычислении рабочих часов желательно учитывать перерывы. Есть несколько способов добавить их в расчёты.
- Создайте столбец «Начало перерыва» и укажите все перерывы в ячейках.
Можно оставить автоматически заданный формат столбца и программа выполнит нужные вычисления самостоятельно.
- Создайте столбец «Конец перерыва» и тоже оставьте автоматически выбранный формат.
- Подсчитайте количество часов в столбце «Отработанные часы».Формула выглядит как E2 = (B2-A2) + (D2-C2). Это значит «Начало перерыва» — «Время начала» + «Время завершения» — «Конец перерыва» = количество отработанных часов за день.
Используйте эту формулу в каждой строке, тогда столбец отработанных часов будет выглядеть так:
Для следующей строки формула будет
E3 = (B3-A3) + (D3-C3)
и т.д.
Преобразование минут в дроби
При работе с временем вместо минут может быть удобнее пользоваться дробями. Это значит, что полчаса (30 минут) будут показаны как 1/2. Есть несколько простых способов превратить минуты в дроби.
- Создайте новую ячейку. В нашем примере это ячейка K2. Поставьте ей числовой формат.
- Введите в ячейку формулу ‘= (E2) * 24‘.
- С используемыми в примере числами вы получите значение 5.50.
Можно быстро применить формулу к группе ячеек или столбцу и преобразовать их.
Поиск минимального количества отработанного времени
Если необходимо найти наименьшее количество отработанного времени, на помощь придёт встроенная функция MIN(). Она находит минимальное значение в списке чисел.
- Создайте новую ячейку и дайте ей название «Длительность». В данном случае это ячейка I2. Скопируйте туда функцию ‘=MIN(E2:E12)‘.
Со значениями из этого примера в столбце «Минимальное количество отработанных часов» будет 5:15:00.
Можно быстро применять функции MIN() или MAX() к столбцу или группе ячеек.
Вычисление общего количества отработанных часов
Если вы не разбираетесь в программировании или в Excel, определённые встроенные функции Google Sheets могут показаться странными. Это не помешает легко найти общее количество отработанных часов. В нашем примере будет рассчитано общее количество отработанных всеми сотрудниками за день часов.
- Создайте новую ячейку и назовите её «Продолжительность». В данном случае это будет ячейка G13.
- В панели формул (fx) введите ‘=SUM(E2:E12)’. Она выдаёт общее количество отработанных часов в ячейках с E2 по E12. Эта формула использует стандартный синтаксис Excel и ряда языков программирования.
Должно получиться значение 67:20:00.
Заключение
Google Sheets создавали не для вычисления промежутков времени, но можно пользоваться программой и для этого. В результате выполнения описанных выше простых действий можно отслеживать отработанное время. Когда промежутки времени превышают 24 часа, вычисления усложняются, но Sheets может справиться и с этим, если поменять формат «Время» на «Дата».
Об авторе
