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

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

image

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

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

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

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

 

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

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

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

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

 

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

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

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

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

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

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

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

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

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

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

image

 

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

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

Почнемо:

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

image

image

image  

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

image 

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

    image

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

    image

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

image

image

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

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

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

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

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

    image

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

    image

     

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

image

 

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

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

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

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

image

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

 

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

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

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

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

image

 

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

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

    image

 

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

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

image

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

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

image

image

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

image

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

 

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

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

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

image

  • У комірку G22 вводимо формулу =SUM(G8:G21)
  • Копіюємо іі у комірки H22, І22, J22 і вводимо відповідну назву полів у комірці А22:

image  

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

image

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

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

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

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

image

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

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

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

Ви можете відслідковувати коментарі до цього запису за допомогою RSS 2.0. Ви можете лишити коментар, або використати трекбек з Вашого сайту.

1 коментар »

 
 

Лишити коментар

XHTML: Ви можете використати наступні HTML-теги <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

 Допомогти блогу та заохотити автора:

BTC: 13kN2tJubi9J1UHUxVJBu9NgjxsmGBccAa

XMR: 44z7DhWqm2eAny1EaC5ny5U3yspQA2wgPGnatvsto9WHeWas6wTpfBZK4kuxnhbxToKouJTYXoMtcVT5Td2hxiQVFEWgMez

KRB: KbbpHAZ41RFBf5ErK7VzpuNQJfZaMcfYoNhLQCHW95cfWWUZedaLFkb92yejTCS9JvJb1PckeZuWfHapya4qL3bZ7kPSEVL