意外とネットに載ってない、VBAを用いた時間の合計の算出方法
簡単そうで色々厄介なExcelの時間計算。ネットで調べると、TimeValue関数、TimeSerial関数、Hour関数などを使った方法が出てくるが、いずれも23:59:59を超えた場合には意図しない結果となる場合があり、考慮が必要になってくる。また、セルの書式設定からユーザー定義「[h]:mm」を選択するというセル依存の方法ではなく、VBAで完結させたい。Excel VBAに関する記事は数多く存在するものの、時間の合計を求める方法については意外と出てこなかった。そこで今回は、Excel VBAを用いて24時間を超える時間の合計を求める方法を詳しく紹介していきたい。
最終的には以下のような作業管理表の勤務時間をプロジェクトごとに算出するVBAを作成する。前編では、Excelにおける時間の扱いについて学んでいく。「Excelで24時間以上の時間を合計したいけどなんか結果がおかしい」、「入力した時刻が何故か勝手に変わってしまう」など、時間の計算が上手く出来ないという方は是非一度見て頂けたらと思う。
実際に24時間以上の合計を求めるVBAのコードについては、後編で紹介しています。
Excelにおける時間の扱い
まず、Excelにおける時間の扱いについて理解しておきたい。Excelでは、時間データは「シリアル値」として格納される。シリアル値は「１９００年１月１日」から連番で管理されている。
ためしに、Excelの任意のセルに半角数字の１を入力し、
セルの書式設定から「日付」を選択する。
すると、「1900/1/1 0:00」と表示された。このように、「1900/1/1 0:00」を１として、「1900/1/2 0:00」は２、「1900/1/3 0:00」は３とシリアル値で格納される。
Excelのセルは、デフォルトでは書式設定が「標準」になっている。この状態で時刻を入力するとどうなるだろうか？
23:59:59までは「h:mm:ss」表記
Excelでは0:00:00～23:59:59は「h:mm」または「h:mm:ss」表記で表示される。
「23:59:59」と入力すると、「23:59」と表示された。※環境によっては「23:59:59」と表示される場合もあります
実際の値を見ても「23:59:59」となっている。
Excelにおける時間データは日付形式（表示上の形式）・シリアル値両方で管理されており、23:59:59までは日付形式で表示するようになっている。
24:00:00以降はシリアル値として格納される
次に、「24:00」と入力してみる。
実際の値を見ると、「1900/1/1 0:00:00」となっている。
- 25:00 → 1900/1/1 1:00:00
- 26:00 → 1900/1/1 2:00:00
- 27:00 → 1900/1/1 3:00:00
という風に、24:00:00を起点に、そこから時間が加算されていく。
24時間を超える場合はどうなるか
では、実際のケースを見てみよう。
勤怠時間の合計を特定のセルに入力する場合を考える。
勤怠時間の合計として「150:25（150時間25分）」をセルに入力すると、自動的に「150:25:00」と表示される。
実際の値としては、「1900/1/6 6:25:00」となっている。
さきほど説明した通り、24:00:00以降はシリアル値として格納されるので、150:25:00であれば、150÷24=6.25、0.25＝6時間（1日が24時間、0.5日が12時間・・・と考える）なので、シリアル値での日付は1900/01/06 6:25:00 ということになる。
| h:mm:ssでの値 | シリアル値 |
|---|---|
| 24:00:00 | 24(時間) / 24(時間) = 1 |
| 12:00:00 | 12(時間) / 24(時間) = 0.5 |
| 01:00:00 | 1(時間) / 24(時間) = 0.041666… |
つまり、hh:mm:ss形式の値からシリアル値を求めたい場合には、hh部分÷24をして、1900/01/01に商の整数部分の値を足してmm:ssをくっつければ求めることが出来る。
こんなことが出来ても使う場面は少ないかと思うが、VBAで時刻の計算を行う際にはシリアル値について十分に理解しておく必要がある。例えば、給与計算をする場合であれば、勤務時間×時給で給与を計算する際に、そのまま計算しようとするとうまくいかないことがある。
原因は時間が内部でシリアル値として扱われたことであり、100÷24＝4.166666666666667が時給の1200円に掛けられて5000円という値が算出されていた。
これを正しく計算するためには、いくつか方法がある。
時間の合計が24時間を超える場合の計算方法
では、ここから実際に24時間を超える時間の合計を算出する方法を見ていく。ただし、前編では「VBAを使わずにセルの操作で算出する方法のみ扱う。
勤務時間の計算方法１：時間に24を掛ける
シリアル値を求めるには時間を24で割ったが、逆にシリアル値を時間にするには24を掛ければよい。
勤務時間の計算方法２：HOUR関数とMINUTE関数を使用する
２つ目の方法として、関数を使用して「時」と「分」をそれぞれ取り出してから合計を求める方法がある。
HOUR関数：引数に指定したシリアル値から時間の値を返す（戻り値は0(時)～23(時)の整数）
MINUTE関数：引数に指定したシリアル値から時刻の分を返す（戻り値は0(分)～59(分)の整数）
出勤・退勤時刻が表に存在するときは、まず勤務時間のセルに日々の勤務時間計を「8.0」「9.5」などのように小数で算出する。
そして、その合計と時給を掛けることで給与額を算出することができる。
注意点
VBAを用いずにExcelの関数や書式設定で対応する方法は手っ取り早く、一度Excelの体裁を整えてしまえばそれ以降は問題なく使えるだろう。しかし、表の内容を変更したり、項目を追加したりする際にうまく計算が出来なくなる可能性がある。
先に例で挙げた方法も、実際には関数を設定しただけでなく、セルの書式設定にてそれぞれ表示形式を設定している。
それでも、そんなに頻繁に変更することはないという場合には寧ろExcelの関数で済ませたほうがいいかもしれない。（VBAであっても項目の追加があれば対応が必要になるのは同じなので・・・）その時々に応じてどのように管理するかは考えるのが良い。扱うデータが多い場合や、項目の変更が頻繁に起こる場合などにはVBAを使用して算出する方法がおすすめだ。
後半では、実際にExcel VBAを用いて、24時間を超える場合の合計時間の算出を行っていく。
コメント