[EXCEL] 勤務時間集計表の作り方（自動記録マクロも同時マスター）

無料のタイムカード・アプリが手に入る時代ですが、諸々の事情で「給与計算」の一部、または全部をEXCELで自作されている企業・商店がまだまだあるようです。

タイムカードのように一か所から入力 → データベース化 → 自動集計をEXCEL初心者がプログラミングの知識が無くとも実現できる便利な方法をご紹介いたします。
↓ 簡単動画からご覧ください。

勤怠管理の現状

一部上場企業でも、人事課がEXCELで作った「勤怠管理簿」を各自が印刷記入後、今流行りの「人力押印」をして人事課へ提出し、人事課は手書きの管理簿を見ながら給与計算ソフトへ入力する、という現実もまだまだ目撃することがあります。

中小では店主が従業員の顔を確認した時が「出勤」というのが常識なのでスマホアプリなど信用できない、という飲食店もあります。人数 ✕ 日数 × 出退勤の情報を店主１人が集中管理することになり家族の誰かに計算作業がのしかかる、電卓を卒業して EXCEL で何とかこなしている、といった現状らしいです。

効率化の原則とは

勤務時間管理を効率化することは「システム化」そのものですが、「システムを買いなさい」と言っている訳ではなく、逆にEXCELでじゅうぶん実現可能です。ただしEXCELでも「システム化の鉄則」を守らなくては目的の「効率化」は達成できませんから、この視点から解説を進めて行きたいと思います。

入力は一か所

入口は一つ (input) があり、出口も一つ (output) でないと混乱してしまいます。

入力フォーマットは１つです、標準化された一つの入力フォームを使って様々な情報を送り込みます。給与計算の例でいえば「タイムレコーダー」が最末端の入力装置にあたります。

データベース

入力された情報は一定の形式で蓄積しますが、これを「データベース」と呼びます。データは増え続けても「入力フォーマット」「出力フォーマット」に影響を与えてはいけません。

EXCELでよく見かけるのは「月度が変わるたびにフォーマットを新調する」という準備作業です。そうではなく「入力フォーマット」は一定であり「データベース」に新月度のデータが追加されるような設計なら毎月のメンテナンスも不要になるのです。

マスタ

「基礎情報」のことで、入力時の参照に使われたり、データベース作成の際に参照されたりしますが、「常に最新情報」であること「重複がない」ことが条件です。マスタは編集権限のある人間だけが追加や変更できます。

出力

蓄積されたデータベースとマスタを使って、要求される種々のアウトプットを実行します。注意すべきは、現有のデータベース、マスタに存在しないアウトプットは不可能である所です。逆に言えば、「必要な出力を想定して、データベースやマスタを設計」することがシステムの出来映えを左右することにもなります。

EXCELの失敗パターン

EXCEL自作システム（帳票等）でよくある失敗は、いちばん最後の「出力フォーム」から作り始めることによる失敗です。

「給与計算」を例に挙げると、「給与明細」や「勤務表」から作り始めるわけです。

「月度勤務表」であれば、行が1日~31日まで、各列に「出勤時刻」「退勤時刻」「勤務時間」と続く一表だけがまっ先に出来上がってしまいます。

しかし問題はこの「月度勤務表」の「氏名」「出勤時刻」「退勤時刻」はデータベースから引っぱってきたものではないという点です。そもそもデータベースは存在しないという問題です。元データが存在しない、ということは人数分 × 月数分存在する「個人別・月度別出力フォーム」へいちいち手入力しなければならない、ということになります。

EXCEL の壁

最終成果物である「出力フォーム」からいきなり作り始めて、「出力フォーム」へ手入力するのは何故なのでしょうか？

EXCELでしくみを作る上で、入力 → データベース → マスタ → 出力各役割を果たすよう設計できれば問題は無いのですが、「入力画面」へ入力すれば「データベース」へ追加される、部分をノンプログラミングで作ることは普通は不可能でしょうし、EXCELとは無縁と考えているから発想できないのだと思います。

結果的に「見た目にはきれいなアウトプット帳票」はできあがったものの、入力の手間がかかるわりに、やっと入力完了したその表は一つの用途しか満たさないため、軸を少し変えて見たい場合は苦労して作り上げた表をバラしたり、つなぎ合わせたりしなければならず、「再集計」作業があちこちのEXCEL現場を苦しめています。

「データベース」の重要性を認識していれば、このような失敗を繰り返すことなく、EXCEL の効率も飛躍的に改善できるはずなのですが。

しかしこの壁を乗り越えるにはプログラミングを身に付ける必要がありました。プログラミングの知識を持つ人間は手を差しのべようとはしません。なぜなら「データベース」の重要性を認識しない人達にとって「壁」が見えていないからです。

自動記録マクロでできないか？

自動記録マクロとは → 解説

入力画面で入力、選択した内容を「データベース」別シートである「データベース」へ追加する動作だけでも自動記録マクロで実現できれば、「EXCELの壁」を打ち壊すことができるかもしれません。

さっそく「万能勤務表」を自動記録マクロで作成してみましょう！

入力シート

まず「入力」シートを作成します。手入力を避け、「マスタ」項目から参照した値の組み合わせで完結させることがポイントです。「データベースへ追加」ボタンの説明は後ほど。

①「日付」は常に最新日を表示する一覧を「マスタ」シートに用意してリストとします。
②「氏名」も「マスタ」シートからリスト設定します。
③「時刻」も「マスタ」シートからリスト設定します。
④式 =E4-D4
⑤式 =SUMIF(マスタ!A2:A10,入力!C4,マスタ!B2:B10)
（注）同姓同名があれば加算してしまいます
⑥式 =G4*F4*24

マスタ

①「氏名」と「時給」テーブルです。
②「日付」セルD2 の最新日は =TODAY() が入っています。
③「時刻」15分刻みですが社のルールに合わせてお作りください。

データベース

タイトルには「オートフィルター」を設定しておきます。「勤務時間計」「支給額」はフィルタリングした結果の合計を求めるため、SUBTOTAL を使用します。

①=SUBTOTAL(9,E3:E1048576)　（注）SUM ではなく SUBTOTAL を使います
②=SUBTOTAL(9,G3:G1048576)　（注）4行目ではなく3行目から足し算します

自動記録式マクロを作成

マクロの記録

「入力」シートの入力ができた状態で

①「開発」タブ「マクロの記録」（または「表示」→「マクロ」→「マクロの記録」）
②「データベース」シートの４行目を選択して「行の挿入」
③「入力」シートセル B4:H4 を選択、コピー
④「データベース」シートのセルA4 へ「値で貼り付け」
⑤「入力」シートへ戻り [ESC] キーを押した後「開発」タブ「記録終了」