こんにちは！今回はExcel VBAを使い、時間と分が別々の列に分かれているデータから所要時間を自動計算する方法をご紹介します。VBAを使うことで、大量のデータ処理や他の操作と組み合わせて自動化が可能です。今回のコードでは、指定したセル範囲の開始時刻と終了時刻から所要時間を計算し、結果をセルに出力します。手動入力ミスを防ぎ、作業効率が大幅に向上するため、シフト管理や会議の所要時間集計など、多くの場面で役立つ方法です。
使用するデータリスト
まず使用するデータリストについて解説します。データリストは「データ」のワークシート上に、「№」「開始時」「開始分」「終了時」「終了分」という構成となっており、今回はデータを12行分作成しました。
「所要時間」の列は計算結果を返す列となっています。
ＶＢＡの実行結果
ＶＢＡを実行した結果下図のような結果を得る事が出来ます。
コード解説★★★（コピーはここ）★★★
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 |
Sub Sample08() ' 設定セクション（セル参照で設定） Dim DataStartCell As Range: Set DataStartCell = Range("B2") ' データの開始セル Dim StartHourCell As Range: Set StartHourCell = Range("B2") ' 開始時のセル Dim StartMinuteCell As Range: Set StartMinuteCell = Range("C2") ' 開始分のセル Dim EndHourCell As Range: Set EndHourCell = Range("D2") ' 終了時のセル Dim EndMinuteCell As Range: Set EndMinuteCell = Range("E2") ' 終了分のセル Dim ResultCell As Range: Set ResultCell = Range("F2") ' 結果の出力セル ' 変数の宣言 Dim NN As Long, LastRow As Long Dim HourDiff As Double, MinuteDiff As Double Dim Results() As Variant ' データの最終行を取得 LastRow = StartMinuteCell.End(xlDown).Row ' 結果を格納する配列を初期化 ReDim Results(DataStartCell.Row To LastRow) For NN = DataStartCell.Row To LastRow ' 時間差の計算（終了時 - 開始時） HourDiff = Cells(NN, EndHourCell.Column).Value - Cells(NN, StartHourCell.Column).Value MinuteDiff = Cells(NN, EndMinuteCell.Column).Value - Cells(NN, StartMinuteCell.Column).Value ' 時間差が負の場合は24時間を加算 If HourDiff < 0 Or (HourDiff = 0 And MinuteDiff < 0) Then HourDiff = HourDiff + 24 ' 配列に合計時間を格納 Results(NN) = TimeSerial(HourDiff, MinuteDiff, 0) Next NN ' 一括でセルに書き込む ResultCell.Resize(LastRow - DataStartCell.Row + 1).Value = Application.Transpose(Results) End Sub |
解説
- 変数の宣言：使用する変数や配列、コレクションを宣言しています。また、このコードでは設定値の入力もここで行っています。
- 情報の取得と配列サイズ調整 ：データリストの行数調査及び配列のサイズ変更を行っています。
- 所要時間の調査：開始、終了時間から所要時間を算出します。
- データの格納：指定したセルへ結果を出力します。
変数の宣言
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
Sub Sample08() ' 設定セクション（セル参照で設定） Dim DataStartCell As Range: Set DataStartCell = Range("B2") ' データの開始セル Dim StartHourCell As Range: Set StartHourCell = Range("B2") ' 開始時のセル Dim StartMinuteCell As Range: Set StartMinuteCell = Range("C2") ' 開始分のセル Dim EndHourCell As Range: Set EndHourCell = Range("D2") ' 終了時のセル Dim EndMinuteCell As Range: Set EndMinuteCell = Range("E2") ' 終了分のセル Dim ResultCell As Range: Set ResultCell = Range("F2") ' 結果の出力セル ' 変数の宣言 Dim NN As Long, LastRow As Long Dim HourDiff As Double, MinuteDiff As Double Dim Results() As Variant |
１行目：マクロ名を「Sample08」と定義。
３～８行目：データの参照元や入力先のセルを指定する。
１１～１３行目：ＶＢＡ内で使用する変数を宣言している。
情報の取得と配列サイズ調整
|
1 2 3 4 5 |
' データの最終行を取得 LastRow = StartMinuteCell.End(xlDown).Row ' 結果を格納する配列を初期化 ReDim Results(DataStartCell.Row To LastRow) |
２行目：データの数を取得する。
５行目：データ数を利用して結果を格納する配列のサイズを調整する。
所要時間の調査
|
1 2 3 4 5 6 7 8 9 10 11 |
For NN = DataStartCell.Row To LastRow ' 時間差の計算（終了時 - 開始時） HourDiff = Cells(NN, EndHourCell.Column).Value - Cells(NN, StartHourCell.Column).Value MinuteDiff = Cells(NN, EndMinuteCell.Column).Value - Cells(NN, StartMinuteCell.Column).Value ' 時間差が負の場合は24時間を加算 If HourDiff < 0 Or (HourDiff = 0 And MinuteDiff < 0) Then HourDiff = HourDiff + 24 ' 配列に合計時間を格納 Results(NN) = TimeSerial(HourDiff, MinuteDiff, 0) Next NN |
１行目：変数ＮＮをデータ開始行の値から最終行の値になるまで処理を繰り返す。
３、４行目：開始と終了の時間と分の差をそれぞれの変数に格納する。
７行目：終了時分が翌日になっている場合、開始時間に２４を加算する。
１０行目：TimeSerialを使用して所要時間を算出し、配列に格納する。
１１行目：変数ＮＮを１加算して１行目から繰り返す。
データの格納
|
1 2 3 |
' 一括でセルに書き込む ResultCell.Resize(LastRow - DataStartCell.Row + 1).Value = Application.Transpose(Results) End Sub |
２行目：配列に格納した所要時間を事前に指定したセルへ入力する。
３行目：ここでこのコードは終了。
使用している技術
基本：FOR-NEXT、TimeSerial
まとめ
この記事では、Excel VBAを活用して、時間と分が別々の列に分かれているデータから所要時間を計算する方法を解説しました。VBAによる自動化は、手動作業のミスを減らし、効率を大幅に向上させるため、シフト管理や会議時間の集計など、多くの業務で役立ちます。今回紹介した方法は、指定したセル範囲の開始時刻と終了時刻から所要時間を算出し、その結果を自動的に出力するものでした。VBAの基本的な技術を使うことで、データ処理の自動化を手軽に実現できるので、ぜひ活用してみてください。
コメント