Excel VBAを活用したサプライヤー別納期遅延率の集計方法

Excel VBAを用いて、サプライヤー別の納期遅延率を集計する方法について解説します。この記事では基本的な集計手法から、より実践的な応用例までをカバーしています。また、VBAのコード内で使用される関数や構文についての詳細な解説も行っていますので、VBA初心者から中級者まで幅広く参考にしていただけます。

VBAを使用した納期遅延率集計の基本手法

まず、基本的な納期遅延率の集計方法から始めます。

Sub 遅延率集計()
    Dim LastRow As Long
    Dim i As Long
    Dim TotalOrder As Long
    Dim DelayedOrder As Long

    'データの最後の行を取得
    LastRow = ThisWorkbook.Sheets("Sheet1").Cells(ThisWorkbook.Sheets("Sheet1").Rows.Count, "A").End(xlUp).Row

    '集計開始
    For i = 2 To LastRow
        '納品が遅れた場合
        If ThisWorkbook.Sheets("Sheet1").Cells(i, 3).Value > ThisWorkbook.Sheets("Sheet1").Cells(i, 2).Value Then
            DelayedOrder = DelayedOrder + 1
        End If
        TotalOrder = TotalOrder + 1
    Next i

    '遅延率を計算し、表示
    ThisWorkbook.Sheets("Sheet1").Cells(2, 5).Value = DelayedOrder / TotalOrder

End Sub

このコードは、Sheet1において、A列にサプライヤー名、B列に納期、C列に実際の納品日が入力されている前提となっています。

コードの解説

1. `LastRow`はデータの最後の行を示す変数。
2. `For i = 2 To LastRow`により、2行目から最後の行までのデータを順番に処理します。
3. `If`の条件文で、納品日が納期を超えていれば`DelayedOrder`を1増やします。それを全体の注文数`TotalOrder`で割ることで遅延率を計算しています。

応用例1: サプライヤーごとの遅延率集計

納期遅延率をサプライヤーごとに分けて集計する方法について解説します。

Sub サプライヤー別遅延率集計()

... [中略: 上記の基本的なコードと同じ部分は省略] ...

    'サプライヤー名のリストを作成
    Dim SupplierList As Collection
    Set SupplierList = New Collection

    'エラーを無視してサプライヤー名のリストを作成
    On Error Resume Next
    For i = 2 To LastRow
        SupplierList.Add ThisWorkbook.Sheets("Sheet1").Cells(i, 1).Value, ThisWorkbook.Sheets("Sheet1").Cells(i, 1).Value
    Next i
    On Error GoTo 0

    'サプライヤーごとの遅延率集計
    Dim SupplierName As String
    For Each SupplierName In SupplierList
        'ここで各サプライヤーごとの遅延率を計算するコードを書く
        ... 
    Next SupplierName

End Sub

コードの解説

サプライヤー名のリストをCollectionオブジェクトを使用して作成します。それを基にして、各サプライヤーごとの遅延率を計算します。

応用例2: ピボットテーブルを用いた集計

ピボットテーブルを使用して、サプライヤーごとの納期遅延率を自動で集計する方法を解説します。

Sub ピボットテーブルによる集計()

    Dim pt As PivotTable
    Dim pc As PivotCache
    Dim pf As PivotField

    Set pc = ThisWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=ThisWorkbook.Sheets("Sheet1").Range("A1:C" & LastRow))
    Set pt = pc.CreatePivotTable(TableDestination:=ThisWorkbook.Sheets("Sheet2").Range("A1"), TableName:="納期遅延率集計")

    'フィールドの設定
    Set pf = pt.PivotFields("サプライヤー名")
    pf.Orientation = xlRowField
    Set pf = pt.PivotFields("納期")
    pf.Orientation = xlDataField
    Set pf = pt.PivotFields("実際の納品日")
    pf.Orientation = xlDataField

    '集計方法の設定など、詳細な設定をここで行う
    ...

End Sub

コードの解説

1. PivotCacheとPivotTableオブジェクトを作成。
2. サプライヤー名を行フィールドとして、納期と実際の納品日をデータフィールドとして追加。
3. 必要に応じて集計方法や表示形式を設定します。

応用例3: サプライヤーごとの遅延理由の分析

遅延した場合の理由を記録している場合、

そのデータを用いてサプライヤーごとの遅延理由の分析を行う方法を解説します。

Sub 遅延理由の分析()

    'サプライヤー別の遅延理由の集計を行うコード
    ...

End Sub

コードの解説

こちらのコードは、サプライヤーごとの遅延理由を分析するものとなります。詳細なコードと実装方法は、具体的なデータ構造や要件に応じて変わるため、ここでは概要のみの記述となります。

まとめ

Excel VBAを使用することで、サプライヤー別の納期遅延率を効率よく集計することができます。基本的な手法から、より詳細な応用例までを学ぶことで、日常業務の自動化やデータ分析の幅が広がります。ぜひ、今回の内容を参考にして、VBAを活用したデータ集計を行ってみてください。