【VBAリファレンス】VBA練習問題VBA100本ノック 88本目：クロスABC分析作成

VBA100本ノック88本目：クロスABC分析の自動化とデータ可視化の極意

Excel VBAを用いたデータ分析業務において、ABC分析は在庫管理や売上分析の基本中の基本です。しかし、単一の切り口（売上高のみなど）だけでなく、複数の指標を組み合わせた「クロスABC分析」を実装しようとすると、途端にコードの難易度が跳ね上がります。本稿では、VBA100本ノックの88本目として名高い「クロスABC分析」を題材に、実務で通用する堅牢かつ高速なアルゴリズムの実装手法を徹底的に解説します。

クロスABC分析の概要と戦略

クロスABC分析とは、2つの異なる指標（例えば「売上金額」と「利益額」）をそれぞれABC分析し、その結果を掛け合わせることで、商品を9つのセグメント（A-A、A-B、…、C-C）に分類する手法です。

この分析の目的は、単に売上が高い商品を探すのではなく、「売上は高いが利益率が低い商品」や「売上は低いが利益率が高い商品」といった、戦略的に優先順位をつけるべきアイテムを浮き彫りにすることにあります。

VBAでこれを実装する場合、以下のステップを踏むのが定石です。
1. データセットの読み込み（配列への格納）
2. 指標ごとの累積比率計算とランク付け（A, B, Cの判定）
3. クロス集計によるセグメント決定
4. 結果の出力と書式設定

このプロセスにおいて、セルへのアクセスを最小限にし、メモリ上で演算を完結させることが、大規模データを取り扱う際のパフォーマンスを左右します。

詳細解説：配列処理とランク付けのロジック

VBAで高速化を図る最大の鍵は「Variant型の配列」の活用です。Rangeオブジェクトを直接操作すると、Excelの再計算や再描画が発生し、処理時間が指数関数的に増大します。

まず、ソースデータを二次元配列に読み込みます。次に、各指標ごとにソートを行う必要がありますが、VBAには標準で強力なソート関数がないため、クイックソートなどのアルゴリズムを自作するか、一時的にワークシートに出力してSortメソッドを利用するアプローチが一般的です。今回は、実務的なメンテナンス性を考慮し、一時シートを利用して効率的にランクを付与する手法を採用します。

累積比率の計算ロジックについては、累積値が全体合計の何％に達したかを判定します。一般的には「0〜70%をAランク、70〜90%をBランク、90〜100%をCランク」と定義しますが、この閾値は柔軟に変更できるように定数や変数として保持しておくべきです。

サンプルコード：クロスABC分析の実装

以下に、売上と利益を軸にしたクロスABC分析のサンプルコードを提示します。

Option Explicit

' メイン処理：クロスABC分析の実行
Sub ExecuteCrossABCAnalysis()
    Dim ws As Worksheet
    Dim dataRange As Range
    Dim lastRow As Long
    Dim arrData As Variant
    
    Set ws = ThisWorkbook.Sheets("Data")
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    
    ' データの取り込み
    arrData = ws.Range("A2:C" & lastRow).Value
    
    ' 1. 売上によるランク付け
    Call ApplyRank(arrData, 2) ' 2列目が売上
    
    ' 2. 利益によるランク付け
    Call ApplyRank(arrData, 3) ' 3列目が利益
    
    ' 3. セグメントの判定と出力
    Call OutputResult(arrData)
    
    MsgBox "クロスABC分析が完了しました。", vbInformation
End Sub

' ランク付与のための汎用プロシージャ
Private Sub ApplyRank(ByRef arr As Variant, colIndex As Integer)
    Dim i As Long, j As Long
    Dim total As Double, currentSum As Double
    Dim temp As Variant
    
    ' 簡易的なソート（降順）
    For i = LBound(arr, 1) To UBound(arr, 1) - 1
        For j = i + 1 To UBound(arr, 1)
            If arr(i, colIndex) < arr(j, colIndex) Then
                ' 行ごとの入れ替え
                temp = Application.Index(arr, i, 0)
                ' (※実際の実装では配列の入れ替えルーチンを記述)
            End If
        Next j
    Next i
    
    ' ランク判定ロジック
    total = Application.Sum(Application.Index(arr, 0, colIndex))
    For i = LBound(arr, 1) To UBound(arr, 1)
        currentSum = currentSum + arr(i, colIndex)
        If currentSum / total <= 0.7 Then
            arr(i, colIndex + 2) = "A"
        ElseIf currentSum / total <= 0.9 Then
            arr(i, colIndex + 2) = "B"
        Else
            arr(i, colIndex + 2) = "C"
        End If
    Next i
End Sub

' 結果出力用プロシージャ
Private Sub OutputResult(arr As Variant)
    Dim i As Long
    Dim wsOut As Worksheet
    Set wsOut = ThisWorkbook.Sheets("Result")
    
    For i = LBound(arr, 1) To UBound(arr, 1)
        wsOut.Cells(i + 1, 1).Value = arr(i, 1)
        wsOut.Cells(i + 1, 2).Value = arr(i, 4) & "-" & arr(i, 5)
    Next i
End Sub

実務アドバイス：保守性と拡張性を高めるために

実務でこのコードを運用する際、以下の3点に注意してください。

第一に「閾値の可変性」です。ビジネス環境の変化に伴い、「Aランクは80%まで」といった変更要望が必ず発生します。閾値をハードコーディングせず、別シートの「設定値」から読み込む設計にしてください。

第二に「エラーハンドリング」です。データ内に0や負の値が含まれる場合、累積比率の計算で予期せぬ挙動を示すことがあります。前処理として、データが正当な数値であることをチェックするバリデーションロジックを必ず組み込んでください。

第三に「可視化の自動化」です。分析結果をマトリクス形式（3x3のクロス集計表）で出力し、条件付き書式を適用することで、視覚的に優先度の高いアイテムが一目でわかるようにします。VBAで「どのセルにどの色を塗るか」を制御するコードを書いておくと、ユーザー体験が劇的に向上します。

まとめ：エンジニアとしての視点

クロスABC分析は、単なるデータの分類作業ではありません。経営の意思決定をサポートするための「判断材料」を生成するプロセスです。VBAでこれを自動化する最大のメリットは、分析の再現性を担保できることにあります。

今回紹介したコードは基礎的な構成ですが、ここからさらに「複数の期間を比較する」「商品属性を考慮する」といった要件を追加していくことで、強力な分析ツールへと進化します。プロフェッショナルとして、コードの書きやすさだけでなく、出力される結果が現場のビジネスパーソンにとって「どう使えるか」を常に意識して設計してください。

VBA100本ノックの学習を通じて、単に課題を解くだけでなく、その先にある「業務改善の自動化」という本質的な価値を追求し続けることを期待しています。Excel VBAは、適切な設計さえあれば、どんな高度な分析業務も最適化できる強力な武器になるはずです。