Excel VBAで自動化：シート分類から基本統計量計算まで

こんにちは！Excel VBAを使ってデータ整理の自動化に挑戦してみませんか？

今回は過去に紹介したコードを実際に利用したうえでさらに追加機能を付加したコードを紹介します。これにより実際に私の紹介したコードの利用イメージを持っていただけるようになります。

実現する機能は以下のようなものです。

• 開始終了データから各工程の所要時間を自動計算　【過去紹介記事へ】
• 大量のデータを品種別にワークシートに自動分類　【過去紹介記事へ】
• 各品種毎の工程所要時間に対して基本統計量を自動計算する　【本記事での追加機能】

このコードを使うことで、手作業の煩わしさから解放され、データ整理の効率が大幅にアップします。さらに、手動操作によるミスも防ぐことができるので、データ処理がよりスマートに行えます。

本記事は、VBAコードを直接コピーして使用したい方や、VBAの基本を理解している方、または他者のコードを参考にしたい方に向けて記載しています。コードの詳細な解説は割愛していますが、実際に試してみることでその効果を実感してください！

使用するデータリスト

まず使用するデータリストについて解説します。データリストは「データシート」のワークシート上に下図の構成でＡ１セルより作成しています。今回はデータを1000行分作成しました。データの種類として品種を10種類、工程を4種類用意しました。

ＶＢＡの実行結果

VBAを実行すると、下図のように所要時間を計算したうえで品種毎に分類されたデータシートと、見出しに基本統計量が表示されます。本コードでは見出しの並び替えや罫線は実行していませんが、必要に応じて実施しても良いと思います。

コード★★★（コピーはここ）★★★

コードの解説

このVBAコードは、大きく分けて３つの機能で構成されています。

1. Sample10：以前に紹介した「大量のデータを品種別にワークシートに自動分類」を実行します。
2. Sample08：以前に紹介した「開始終了データから各工程の所要時間を自動計算」を実行します。
3. 基本統計量計算：今回新たに作成した、計算した所要時間を用いて基本統計量を「見出し」シートに算出します。

Sample10の変更箇所

• １２行目：所要時間列が空の為、１行目のヘッダー行参照に変更
  LAST_C = wsData.Cells(1, Columns.Count).End(xlToLeft).Column」
• １６行目：所要時間計算ロジック（Sample08）の呼び出しを追加
  Call Sample08(LAST_R)
• ３８行目：基本統計量計算ロジックの呼び出しを追加
  Call CreateSummarySheet(wsData, LAST_C, LAST_R, Data_C, Category_List)

Sample08の変更箇所

• １行目：LastRowの変数をSample10からの引数に変更
  Sub Sample08(ByVal LastRow As Long)
• ３～８行目：それぞれのセルを変更
  Dim StartHourCell As Range: Set StartHourCell = Range(“D2“)  ‘ 開始時のセル
  Dim StartMinuteCell As Range: Set StartMinuteCell = Range(“E2“) ‘ 開始分のセル
  Dim EndHourCell As Range: Set EndHourCell = Range(“F2“)      ‘ 終了時のセル
  Dim EndMinuteCell As Range: Set EndMinuteCell = Range(“G2“)  ‘ 終了分のセル
  Dim ResultCell As Range: Set ResultCell = Range(“H2“)        ‘ 結果の出力セル
• １１、１６行目：引数に変更によるコメントアウト
  Dim NN As Long ‘, LastRow As Long
  ‘LastRow = StartMinuteCell.End(xlDown).Row

基本統計量計算の特徴

データを格納する際に「Scripting.Dictionary」を使用しているが、今回は「品種」と「工程」の２つを見て統計量の計算をする必要があります。そこで、データ収集の際には「品種＆工程」という形でマージしてデータを取得し、出力する際に「Split」を使用して分離という作業を行っています。

Sub CreateSummarySheet(ByVal wsData As Worksheet, _
                       ByVal LAST_C As Long, _
                       ByVal LAST_R As Long, _
                       ByVal Data_C As Variant, _
                       ByVal Category_List As Object)
    Dim wsSummary As Worksheet
    Dim Category As Variant, Process As Variant
    Dim i As Long, j As Long, SummaryRow As Long
    Dim Values() As Double
    Dim Results As Collection
    Dim ProcessCategory As String
    Dim ProcessCategory_List As Object
    Dim CategorySplit As Variant
    
    ' 見出しシートの作成または取得
    On Error Resume Next
    Set wsSummary = Worksheets("見出し")
    If wsSummary Is Nothing Then
        Set wsSummary = Worksheets.Add(After:=Worksheets(Worksheets.Count))
        wsSummary.Name = "見出し"
    Else
        wsSummary.Cells.Clear ' 既存のデータをクリア
    End If
    On Error GoTo 0
    
    ' 見出しの設定
    wsSummary.Cells(1, 1).Resize(1, 8).Value = Array("品種", "工程", "最小値", "最大値", "平均値", "N数", "分散", "標準偏差")
    
    SummaryRow = 2
    
    ' 品種＆工程ごとに統計を計算
    Set ProcessCategory_List = CreateObject("Scripting.Dictionary")
    
    For i = 2 To LAST_R
        ProcessCategory = wsData.Cells(i, 2).Value & "＆" & wsData.Cells(i, 3).Value ' 品種＆工程（B列＆C列）
        If Not ProcessCategory_List.exists(ProcessCategory) Then
            ProcessCategory_List.Add ProcessCategory, Nothing
        End If
    Next i
    
    For Each Category In ProcessCategory_List.Keys
        Set Results = New Collection
        
        ' 対象データの収集
        For i = 1 To UBound(Data_C, 1)
            ProcessCategory = Data_C(i, 1) & "＆" & Data_C(i, 2) ' 品種＆工程
            If ProcessCategory = Category Then
                Results.Add Data_C(i, LAST_C - 1) ' データ列の数値を収集
            End If
        Next i
        
        ' 統計計算の実行
        If Results.Count > 0 Then
            ReDim Values(1 To Results.Count)
            For j = 1 To Results.Count
                Values(j) = Results(j)
            Next j
            
            ' 品種と工程を分離
            CategorySplit = Split(Category, "＆")
            
            ' 統計値の計算と出力
            wsSummary.Cells(SummaryRow, 1).Value = CategorySplit(0) ' 品種
            wsSummary.Cells(SummaryRow, 2).Value = CategorySplit(1) ' 工程
            wsSummary.Cells(SummaryRow, 3).Value = WorksheetFunction.Min(Values)
            wsSummary.Cells(SummaryRow, 4).Value = WorksheetFunction.Max(Values)
            wsSummary.Cells(SummaryRow, 5).Value = WorksheetFunction.Average(Values)
            wsSummary.Cells(SummaryRow, 6).Value = Results.Count
            wsSummary.Cells(SummaryRow, 7).Value = WorksheetFunction.Var(Values)
            wsSummary.Cells(SummaryRow, 8).Value = WorksheetFunction.StDev(Values)
            
            SummaryRow = SummaryRow + 1
        End If
    Next Category
End Sub 

使用技術

基本：Split、WorksheetFunction、
参照：開始終了データから各工程の所要時間を自動計算　【過去紹介記事へ】
　　　大量のデータを品種別にワークシートに自動分類　【過去紹介記事へ】

まとめ

本記事では、Excel VBAを使用してデータ整理の自動化を行う方法を紹介しました。シート分類、所要時間の計算、基本統計量の算出を自動化することで、手作業の負担を軽減し、データ処理の精度と効率を大幅に向上させることができます。これにより、日々の業務でのExcel操作がさらにスムーズになり、時間の節約にもつながります。

VBAのコードは直接コピーして使用することもでき、VBAの基本を理解している方にとってはカスタマイズの参考にもなります。この記事をきっかけに、Excelでのデータ処理を自動化し、業務効率化を目指してみてください。

AIで効率化してライバルに差をつける！【ビットランドAI】