労務のためのExcelTIPS～[h]:mm:ssを一発で設定せよ～
本記事は労務アドベントカレンダー2021の17日目の記事です。
毎月の給与計算、そしてその前提として必要となる勤怠集計。
みなさん、勤怠システムからぽちっと給与計算システムへ連携できている方はどのくらいいらっしゃるでしょうか？
私は転職回数が多いので何社かの勤怠集計・給与計算を見てきましたが、フレックスタイム制や固定残業代が増えてきたこともあり、ワンポチで給与連携！というのはまだまだ少数派という印象です。給与計算は外部委託というのも多いので、それ用にデータ加工しているというのも多い。また、インセンティブの集計もあったりして、まずは月次の勤怠をダウンロードして集計しなおして給与計算システムへインポートする形式が多くを占めているのではないでしょうか。
24時間を超える時刻表示のトラップ
Excelは優秀でcsvで開いたデータは自動的にデータ型を判断してくれてそれに合わせた書式を設定してくれますが、ごくたまにデータ形式の判断が間違っていたり、一部手動で集計が必要な時に列を追加すると前の列の書式を持ってきてしまったままで…なんてことでミスが起こります。
これマジで泣きたい。。。そしてミスの部類としてはダメージがでかい。
例えば所定労働時間8時間の方が有給の特別休暇を4日間取得して、
所定8時間ｘ4日＝32時間
を労働時間に参入するという扱いをするときがあります。うまいことシステム内で拾ってくれないときは手動で労働時間に追加します。
列を追加して入力していくのですが、D列の後ろにE列を追加して入力をしていくとこのようなことが起こります。
そしてこれをcsvにすると入力した32:00ではなく表示されている8:00がデータとして残ってしまいます。
これがミスの種類としては最上級に怖いので、集計する前にデータの書式を確認・設定し直したいと思います。
書式設定のメニューからあれこれと選択するのは大変なので一発でできるショートカットを作ります。
書式設定は[h]:mm:ssでいいんだけど
やることは、
セルの書式設定＞その他の表示形式＞ユーザー設定＞[h]:mm:ss
です。
と言っても毎回このように設定するのは一苦労です。しかも日付・時刻の初期設定にないので、ユーザー定義で自分で作らないといけません。
ということでマクロの登場
毎回対象を選んで、設定というのは骨が折れるのでマクロを作成してポチっとな、で完了するようにします。
実際に動かすマクロのコードは1行でOKです。
時：分でよければ
Selection.NumberFormatLocal = "[h]:mm"
時：分：秒まで必要な時は
Selection.NumberFormatLocal = "[h]:mm:ss"
個人用マクロブックに登録したい
マクロは今開いているシートに紐づくもの、ブックに紐づくものなど、様々な場所に作成することができます。ここでは、Excelを開いたときに常に利用したいマクロなのでちょっと工夫した場所に置きます。
それは個人用マクロブックと呼ばれるもので、実はExcelを開くと同時にPersonal.xlsbというファイルがバックグラウンドで開いています。通常は全く見えないため気になりませんが、こういう時に威力を発揮するものになります。
では上記のコードを入力するためマクロ（VBAエディタ）を開きましょう。
表示＞マクロ＞マクロの表示か、Alt+F11で以下の画面が開きます。
ここで左上のプロジェクトで見えるのはThisWorkbook、今開いているファイルです。ここではなく、Excelを開いたときにいつでも使いたいものなので個人用マクロブック「Personal.xlsb」を使います。
しかし、一度も個人用マクロブックを利用したことがないときは表示されません。
ということで、いったんこの画面を閉じて、個人用マクロブックを作成します。
個人用マクロブックを作成する
マクロの記録を行うと個人用マクロブックが作成されます。
今回の書式設定を行いながらマクロを作成してみましょう。
表示＞マクロ＞マクロの記録を表示します。
まずはマクロの名前を決めます。
そして、ショートカットキーを登録します。
私の場合は時間の「j」にしました。
cとかvはすでに割り当てられているので、まだ割り当てられていないキーにしましょう。
マクロの保存先は個人用マクロブックを選択してください。
これでOKを押して、書式設定を行います。
で、最後に「記録終了」します。
作成されたマクロを確認する
ここで、開発＞VisualBasicか、Alt+F11でVBAEditorを開きます。
最初に見た画面からプロジェクトに「PERSONAL.XLSB」が増えました。これが個人用マクロブックです。
ここで記録したマクロは、標準モジュール＞Module○に記録されます。
初めての方はModule1にできてます。
今回はModule6に作成されたマクロを見てみます。
Sub 時間表示24時間以上の設定をするマクロ()
'
' 時間表示24時間以上の設定をするマクロ
'
' Keyboard Shortcut: Ctrl+j
'
Selection.NumberFormatLocal = "[h]:mm:ss"
End Sub最初に書いたこの1行が出来上がりました！
Selection.NumberFormatLocal = "[h]:mm:ss"これで完了です。
まずは忘れずに左上の保存ボタンを押してから、右上の×ボタンをクリックしてウィンドウを閉じてExcel画面に戻ります。
では実行してみましょう
先ほどの時刻をもとに戻してみて、ショートカットキーで実行してみましょう。
「Ctrl+j」をポチっとな。あっという間にできましたー。
マクロを実行した時の注意点
間違えた操作をしたとき、Ctrl+Zで取り消しをすることができますが、マクロで実行したものは元に戻すことができません。
間違えたときは手動で戻しましょう。
マクロの記録とたった1行でできるショートカットキー。
小さなことから一歩ずつ業務改善していきながらサンタさんを待ちましょう。
この記事が気に入ったらサポートをしてみませんか？