【VBA】関数(SubTotal)とAutoFilterを使った楽な集計法

何かしらの集計を行う際に、MAX関数やSUM関数等を使用した集計を行う事がよくあります。
しかし、フィルター機能を使った場合は上記関数だと集計出来ない事があります。
フィルターを使う場合は、SubTotal関数を使用します。

例として、下記のような処理Start~Stopの動作している時間帯の計算と最大値の集計を行う事を想定した処理を書きます。

まず、処理名(C列)でフィルターしながらSubTotal関数で集計を行う為に配列に処理名の格納から行っていきます。
ワークシートの適当な部分に、処理名を記載しておきます。(G列に記載しました。画面に収まってないですが、処理_1_Start ~ 処理_20_Stop で40個分の処理名を作成しました。)

下記コードを実行して、ウォッチウィンドウ等で格納されたか確認して下さい。

配列に格納

    Dim Job_Collection(39) As Variant
    
    For i = 2 To 41
        
        Job_Collection(i - 2) = Cells(i, 7)
        
    Next i

処理時間の計算を行います。
シンプルに、終了時間 - 開始時間 = 動作時間 で計算します。
書式も、hhmmssに変更します。

動作時間の計算

Dim i,j,k As Long

For i = 2 To Cells(Rows.Count, 4).End(xlUp).Row Step 2
    
    Cells(i + 1, 5) = Cells(i + 1, 4) - Cells(i, 4)
    
Next i

Columns(5).NumberFormat = "hh:mm:ss"

今回一番大事なところです。
処理名を格納した配列でフィルターをかけ、SubTotal関数で最大値の取得を行います。
下記のような処理を書きました。

メイン

For j = 2 To 41 Step 2
    
        Range("C1").AutoFilter 1, Job_Collection(j - 2), xlOr, Job_Collection(j - 2 + 1)
        Cells(k, 11) = WorksheetFunction.Subtotal(4, Columns(5))
        
        k = k + 1
        
Next j
    
Range("C1").AutoFilter
Columns(14).NumberFormat = "hh:mm:ss"

最終的には、下記のようなコードになりました。
集計がすぐ終わるので、楽です。

VBA_Sample

Sub Main_Process()

    Dim i, j, k As Long
    Dim Job_Collection(39) As Variant
    
    #G列から処理名を取得し、Job_Collectionに格納
    For i = 2 To 41
        
        Job_Collection(i - 2) = Cells(i, 7)
        
    Next i
   
    #動作時間の計算処理
    Call Calculation
    
    k = 2
    
    #フィルター後に、最大処理時間をK列に転記
    For j = 2 To 41 Step 2
    
        Range("C1").AutoFilter 1, Job_Collection(j - 2), xlOr, Job_Collection(j - 2 + 1)
        Cells(k, 11) = WorksheetFunction.Subtotal(4, Columns(5))
        
        k = k + 1
        
    Next j
    
    Range("C1").AutoFilter
    Columns(14).NumberFormat = "hh:mm:ss"

End Sub



Sub Calculation()

Dim i As Long

For i = 2 To Cells(Rows.Count, 4).End(xlUp).Row Step 2
    
    Cells(i + 1, 5) = Cells(i + 1, 4) - Cells(i, 4)
    
Next i

Columns(5).NumberFormat = "hh:mm:ss"

End Sub

実行すると、E列に動作時間/K列に最大値が記載されます。

↓実行後

集計作業によっては、一日かかる事もありますが
記載した内容を応用すれば、すぐ集計を終わらせる事が出来そうです。