Звітуємо про свою роботу

Кілька місяців тому я вже писав про створення звітів про витрати та про відпрацьований час. Однак, того разу мова йшла про використання стандартних шаблонів, які входять до складу Microsoft Office, або можуть бути завантажені з Office Online.

Сьогодні ж мені хотілося б розглянути ситуацію, коли жоден з запропонованих шаблонів з тих чи інших причин нас не задовольняє.  Це цілком природня ситуація (запитання на цю тему я отримую майже регулярно), оскільки витрачений час відслідковується по-різному, в залежності від компанії та посади, тож іноді доводиться засукати рукава, і позробити потрібний шаблон самостійно. А ще хотілося б, щоб усі розрахунки, і навіть введення частини вихідних даних робились автоматично.

Створюючи шаблон для обліку робочого часу, слід не забувати про такі важливі речі, як:

• користувач – ви створюєте шаблон для власного використання, чи його також будуть використовувати інші люди?
• звітний період – це буде звіт за тиждень, два тижні, місяць?
• спосіб оплати – чи буде розмір погодинної оплати залежати від проекту, над яким ви працюєте? Чи буде ваш документ рахувати і суму оплати, чи лише витрачений на роботу час?

Добре поміркуйте над цими питаннями, і ми почнемо.

Визначаємо цілі

Припустимо, що шаблон, який ви створюєте, будуть використовувати інші працівники компаніє. В цьому випадку треба врахувати одразу кілька важливих моментів:

• валідація – яким чином ви забезпечите перевірку правильності даних, введених користувачем (наприклад, формат дат, грошових сум і т.ін.)?
• доступність – яким чином користувачі отримуватимуть доступ до шаблону (локальний файл, спільна тека, файл, опублікований на інтранет-порталі, і т.д.)?
• захист – як ви забезпечите захист шаблону від змін внесених користувачем (формули, макроси, і т.п.)?
• розмежування доступу – яким чином ви будете захищати конфіденційні дані, введені користувачем від інших працівників?

Непрості запитання, чи не так? Однак, якщо ви створюєте шаблон документу для власного використання, ваша задача значно спрощується.:-)

Будуємо бланк

Перший крок у справі створення нашого шаблону – це побудова “шапки” – введення назв полів для всіх даних, які будуть у ньому міститися.

Це можуть бути:

• Інформація про співробітника
  • ім’я
  • посада
  • табельний номер
  • назва відділу
  • ім’я керівника
  • …

Не слід захоплюватись цією справою – включайте в документ лише ті дані, які там дійсно повинні бути.

• Початок звітного періоду
  • перший день тижня
  • перший день звітного місяця
  • початковий і кінцевий дні двотижневого періоду
  • початковий і кінцевий дні довільного періоду часу

Якщо ваш шаблон буде розраховано на фіксований проміжок часу, ви можене навіть заздалегідь згенерувати дати, назви днів тижня та ін.

• Записи про витрачений час
  • час початку і завершення роботи
  • перерви
  • час хвороби
  • вихідні дні
  • перепрацювання (оплачується за подвійним тарифом!:-))
  • …
• Підсумки
  • проміджні підсумки
  • загальний підсумок
  • місце для підпису співробітника і його керівника

Що саме з переліченого вище ви будете використовувати, залежить від ваших конкретних потреб і стандартів, прийнятих у вашій компанії.

Результат цього першого кроку – “шапка” документу – може бути, наприклад, таким:

Автоматизуємо введення дат

Звичайно, користувачі можуть вводити дати самостійно. Однак, найбільше помилок виникає саме при введенні дат – люди використовують неправильний роздільник між числом, місяцем та роком, плутають місцями дату і місяць, і т. ін. Тож, правильна карма – генерувати дати автоматично. Звичайно, це вдасться лише в тому випадку, коли ви точно знаєте на який проміжок часу буде розрахований даний шаблон (ви можете створити їх декілька – для різних звітних періодів).

Почнемо:

• припустимо, що людина ввела початкову дату в полі “Звітний період” (комірка B2). Так, користувач може помилитись і тут, однак, це допустимий ризик.:-). Якщо ж ви все ж таки хочете перестрахуватись, застосуйте до цієї комірки правила валідації даних (вкладинка Data, команда Data Validation в групі Data Tools):

Тепер, якщо користувач введе неправильну дату, він побачить ось таке повідомлення:

• В першій комірці поля “Дата” (А8) вводимо формулу: =IF(B2<>"";B2;"")
  Результат повинен бути таким:

  

• У комірку А9 вводимо формулу: =IF(A8<>"";A8+1;"")
  Отримуємо:

  

• Копіюємо формулу, введену у комірку А8 стільки разів, скільки це нам потрібно. Наприклад, для звіту за два тиждні, нам слід “протягти” формулу до комірки А21 включно:

• Вводимо до комірок стовпчика В формулу, яка відображає день тижня для дати, записаної у відповідній комірці стовпчика А. Наприклад, у комірці В8 ми пишемо: =А8, після чого протягуємо формулу до комірки В21 включно:

  
  Не дивуйтеся, зараз ми відформатуємо дані належним чином, і все буде гаразд.

Форматуємо дати і дні тижня

Для цього ми виконуємо такі дії:

• Виділяємо діапазон А8:А21
• Правим кліком мишею викликаємо контекстне меню, і обираємо пункт Format Cells… 
• На вкладинці Numbers вибираємо категорію Date і натискаємо Ok:

  

• Виділяємо діапазон B8:B21
• Правим кліком мишею викликаємо контекстне меню, і обираємо пункт Format Cells… 
• На вкладинці Numbers вибираємо категорію Custom
• У полі Type пишемо dddd:

  

   

Результат повинен бути таким:

Рахуємо відпрацьовані години

У комірку G8 пишемо формулу =IF(((D8-C8)+(F8-E8))*24>8;8;((D8-C8)+(F8-E8))*24).

Копііюємо її у всі комірки стовпчика до G21 включно. Думаю, тут все зрозуміло – формула просто додає різницю між часом початку роботи і часом її закінчення до обідньої перерви і після, а потім множить на 24, визначаючи таким чином кількість відпрацьованих годин. Якщо виходить більше 8, в комірку все одно записується 8.

Отримуємо таку картину:

Поки що скрізь нулі, оскільки комірки стовпчиків C,D,E,F не містять даних.

Рахуємо надлишкові години

У комірку H8 пишемо формулу =IF(((D8-C8)+(F8-E8))*24>8;((D8-C8)+(F8-E8))*24-8;0)).

Копііюємо її у всі комірки стовпчика до H21 включно. Вважаю, що формула виглядає цілком логічною, і не вимагає пояснень.

Отримуємо таку картину:

Форматуємо час початку і закінчення роботи

• Виділяємо діапазон C8:F21
• Правим кліком мишею викликаємо контекстне меню, і обираємо пункт Format Cells… 
• На вкладинці Numbers вибираємо категорію Time і формат 13:30, натискаємо Ok:

  

Вводимо стандартні години початку і закінчення роботи

Для звичайного 40-годинного робочого тижня все буде дуже просто:

Зверніть увагу на те, що відпрацьований та надлишковий час вже пораховано автоматично. Зрозуміло, що це лише зразок. Користувачі можуть правити ці години у відповідності до їх власного розкладу.

Залишилось лише задати для стовпчиків G і H формат Number (на випадок, якщо будуть вказані години з хвилинами):

У якості ж “стандартної” тривалості хвороби і перерв вписуємо нулі, застосувавши той же формат (Number), що і для стовпчиків G і H:

Це також лише зразок – у разі потреби працівник впише відповідну кількість втрачених годин.

Підводимо підсумки

Що ж, залишилось підрахувати підсумки за кожен день і за весь звітний період:

• У комірку K8 вписуємо формулу: =G8+H8-I8-J8
• Копіюємо її у всі комірки стовпчика аж до K21 включно:

Останній штрих – на всяк випадок – перевірка отримних сум. Задля цього, у комірку К22 ми пишемо таку формулу:
=IF(SUM(K8:K21)=(G22+H22-I22-J22);SUM(K8:K21);"Помилка!"). Зміст формули, вважаю, зрозумілий: якщо суми за рядками і стовпчиками “не сходяться”, у комірці з’явиться напис “Помилка!”.

На даному етапі ви можете також спробувати повводити різні значення у поля “Звітний період”, “Початок”, “Кінець”, “Хвороба” і “Перерви”, аби впевнитись, що все рахується так, як слід.

Останні штрихи

Все! Залишилось лише відформатувати шаблон за власним смаком (кольори, шрифти, назва і логотип компаніі та ін.)

Бажаючі зробити шаблон ще кращим можуть спробувати застосувати валідацію даних до діапазону C8:F21, аби не дати можливості користувачам ввести неправильний час:

Ще одне вдосконалення – захист формул. Однак, я б волів надати право зробити це вам. Нехай це буде невеличким домашнім завданням.:-) І маленька підказка – не забудьте, що захист комірок не працію, доки ви не захистили лист.

На сьогодні все. Ми своїми руками створили шаблон звіту про відпрацьований час, який сподіваюсь, дозволить вам і вашим колегам цей самий час економити, не винаходячи кожного разу велосипед.:-)

Цей пост я написав на основі статті Build a simple timesheet in Excel. Цікаво, а чи знайдете ви в оригіналі статті помилку, яка робить створюваний документ нефункціональним?