今回はエクセルの神髄様の Twitter 企画 「VBA100本ノック」の 88 本目、クロスABC分析をやってみます。
お題：VBA100本ノック 88本目：クロスABC分析作成
#VBA100本ノック 88本目
— エクセルの神髄 (@yamaoka_ss) 2021年2月15日
「data」と「商品マスタ」から「クロスABC」を完成させる。
・仕入金額=仕入単価*数量
・売上金額=販売単価*数量
・粗利金額=売上金額-仕入金額
・売上ABC=売上順に並べ累計構成比が、>=50%がA、>=90%がB、以外はC
・粗利ABC=粗利順で売上ABCと同様に
※最後は売上順で pic.twitter.com/9P40cgFV1x
出題ページ
方針
VBA の課題としては実務でありそうな要所を抑えた良問です。 VBA の実力試験にちょうどいいですね。
とはいえこの手の課題は、今時の Excel なら Power Query や PowerPivot といった高度な機能を使えば簡単で便利にできてしまうことなので、VBA で頑張る時代でもなくなりました。
なので本記事ではその出題意図とは少し違った観点でこの問題に取り組みたいと思います。
それは、どうすれば VBA プログラムは解りやすくできるのか、についてです。
現実問題として、本題程度の仕様でもなぜか VBA でコーディングすると途端に複雑でややこしいものになりがちで、特に人のソースを読み解くにはかなりの集中力を要します。
どうして VBA プログラムはこんなに解りにくくなってしまうのでしょうか。
理由はたくさんあってほとんどは VBA だからしょうがないという話になるのですが、プログラムを書く側として心掛けでできることもあるはずです。
とりあえず以下の２点を何とかしたいです。
- データをセルのアドレスや位置で参照している
- 業務ロジックとセル操作がごちゃ混ぜになる
本回答ではこれらの改善する試案として、データを「名前」で参照し、業務ロジックを VBA ではなく「数式」に寄せてみたらどうかという方向で進めてみようと思います。
解答
【注意】本プログラム中で使用している累積比の計算ロジックは、そのままだとデータの分布によっては想定と微妙に異なる ABC 結果を出すことがありますので注意してください。 詳細は考察のところで解説いたします。
本プログラムは Microsoft 365 版 Excel でのみ動作します。 XLOOKUP 関数や LET 関数といった最新の関数を使用しているためです。
Option Explicit Sub VBA100_088() ' データ出力範囲に名前を付けたいのでその行数の領域を確保しておく With Worksheets("クロスABC").Range("A1").CurrentRegion.Offset(1) .ClearContents .Resize(Worksheets("data").Range("A1").CurrentRegion.Rows.Count - 1, 1).Value = " " End With ' データ列に名前の定義 Call defineFieldNames(Worksheets(Array("クロスABC", "商品マスタ", "data")), tag:="VBA100_088") Application.Calculation = xlCalculationManual With Worksheets("クロスABC") ' 入力データの転記 .Range("コード").Formula = "= data!コード" .Range("数量").Formula = "= data!数量" ' 商品マスタの検索 .Range("品名").Formula = "= XLOOKUP(コード, 商品マスタ!コード, 商品マスタ!品名)" .Range("仕入単価").Formula = "= XLOOKUP(コード, 商品マスタ!コード, 商品マスタ!仕入単価)" .Range("販売単価").Formula = "= XLOOKUP(コード, 商品マスタ!コード, 商品マスタ!販売単価)" ' 粗利の算出 .Range("仕入金額").Formula = "= 仕入単価 * 数量" .Range("売上金額").Formula = "= 販売単価 * 数量" .Range("粗利金額").Formula = "= 売上金額 - 仕入金額" ' ABC 判定 .Range("売上ABC").Formula = Join(Array( _ "= LET(", _ " 累積比, SUMIF(売上金額, "">="" & 売上金額) / SUM(売上金額), ", _ " それ以上, true, ", _ " IFS(", _ " 累積比 <= 0.5, ""A"", ", _ " 累積比 <= 0.9, ""B"", ", _ " それ以上, ""C"" ", _ " )", _ ")" _ ), vbLf) .Range("粗利ABC").Formula = Join(Array( _ "= LET(", _ " 累積比, SUMIF(粗利金額, "">="" & 粗利金額) / SUM(粗利金額), ", _ " それ以上, true, ", _ " IFS(", _ " 累積比 <= 0.5, ""A"", ", _ " 累積比 <= 0.9, ""B"", ", _ " それ以上, ""C"" ", _ " )", _ ")" _ ), vbLf) End With Application.Calculate Application.Calculation = xlCalculationAutomatic ' 数式を値にして並べ替え With Worksheets("クロスABC").Range("A1").CurrentRegion .Value = .Value ' ★ .Sort .Range("売上金額"), xlDescending, Header:=True End With ' 名前の削除 Call clearFieldNames(Worksheets(Array("クロスABC", "商品マスタ", "data")), tag:="VBA100_088") ' ★ End Sub Private Sub defineFieldNames(shts As Sheets, tag As String) Dim sht As Worksheet Dim rng As Range Dim col As Range For Each sht In shts Set rng = sht.Range("A1").CurrentRegion If 1 < rng.Rows.Count Then For Each col In rng.Columns With sht.Names.Add(col.Cells(1).Text, RefersToLocal:=Intersect(col, col.Offset(1))) .Comment = tag End With Next End If Next End Sub Private Sub clearFieldNames(shts As Sheets, tag As String) Dim sht As Worksheet Dim nm As Name For Each sht In shts For Each nm In sht.Names If nm.Comment = tag Then nm.Delete End If Next Next End Sub
考察
各データ列のセル範囲に対してその見出しを「名前」として定義し、「数式」側でデータ転記、マスタ検索、業務計算および ABC 判定を行っています。
セル範囲に名前を付けることで、アドレスやインデックスではなく、業務的に意味付けされた名前で数式内や VBA から参照でき、処理を理解しやすくなったと思います。
また、数式に業務計算をすべて掃き出したので、処理の見通しが良くなり、要求仕様との突き合わせも容易になりました。
プログラムの最後に数式を削除してしまいますが、２か所「★」でマークした行をコメントアウトしてもらえれば名前と数式を残せます。 シート上の数式で確認できるのなら、VBA を知らない担当者にも検証してもらえるのではないかと思います。
数式と名前を使うのは一案にすぎません。
名前の定義をするくらいならテーブルの構造化参照を使う方が簡単でしょう。
わざわざ数式にせず VBA 側でプログラムするにしても、例えば Enum 定義 で列番号に名前をラベルしたり、Type 定義 （ユーザー定義型）や クラスモジュールでデータを構造化するなど活用すれば、業務ロジックを切り分ける設計はいろいろ考えられそうです。
以下、今回使った技術的なトピックについてこの機会に解説します。
■ 名前参照
「名前の定義」でセルやセル範囲、数式に「名前」を付けると、数式内でその名前で参照する「名前参照」ができます。
業務的に意味のある名前でセルを参照すれば、計算式の意図が解りやすくなるだけでなく、アドレスの入力ミスも起きなくなり、シートレイアウトの変更にも強くなります。
また表のデータ列のセル範囲に見出し名などの名前をつけると、その名前参照を使った数式で各行の計算が行われます（暗黙的交差（後述））。 動的配列数式をサポートしている Excel なら数式のコピーさえも不要で、名前参照でスピルさせることができます。
また名前は、数式だけでなく、VBA からでも Range メソッド や Evaluate メソッド でのセル範囲指定に使用できます。
特に Evaluate の省略記法（糖衣構文）である角カッコ（[]）記法と名前の組み合わせは魅力的です。
' シート定義の名前の使い方 ' Range を名前指定 Range("クロスABC!数量") = Range("data!数量").Value Worksheets("クロスABC").Range("数量") = Worksheet("data").Range("数量").Value ' Evaluate で名前参照 Range("クロスABC!売上金額").Value = Evaluate("クロスABC!販売価格 * クロスABC!数量") ' 角カッコ記法で名前参照 [クロスABC!売上金額] = [クロスABC!販売価格 * クロスABC!数量] ' アクティブシートのシート名は省略できる Worksheets("クロスABC").Activate [売上金額] = [販売価格 * 数量]
実は本記事でも最初は角カッコ記法を使った解答プログラムを目論んでいたのでした。
' 注：動きません Worksheets("クロスABC").Activate [コード] = [data!コード].Value [数量] = [data!数量].Value [販売単価] = [XLOOKUP(コード, 商品マスタ!コード, 商品マスタ!販売単価)] ' ★ 配列を返してくれない😭 [売上金額] = [販売単価 * 数量] [売上累積比] = [SUMIF(売上金額, ">=" & 売上金額) / SUM(売上金額)] ' 作業列を追加 [売上ABC] = [IFS(売上累積比 <= 0.5, "A", 売上累積比 <= 0.9, "B", true, "C")]
ところがやってみると XVLOOKUP 関数がこれだとどうしても配列を返してくれないので断念し、Formula で妥協しました。 XMATCH 関数など他の関数では問題ないので XLOOKUP の不具合の可能もありますが、VLOOKUP も同様にうまくいかないのでなにか仕様的な制約があるのかもしれません。
さて、いいとこ尽くしのような名前ですが、実務ではそれほど活用されていないようです。
理由の一つとしては、列見出しで参照したければテーブル化して構造化参照を使えばいいので、古臭い機能に思われることもあるでしょう。
しかし何と言っても大きいのは、名前を使ってみるとけっこう保守管理が大変で、#NAME? や #REF? エラーの原因になったりするなど余計な苦労の経験から毛嫌いされる傾向にあることです。
ただ今後名前は、 LAMBDA 関数 の導入などによって活用する機会が増えるので、名前参照の役割と利便性も見直される可能性があります。
そこで、名前の管理の問題はマクロに任せてしまう、というソリューションも悪いアイデアではないかもしれません。
■ 名前の定義
VBA で名前の定義をする Excel のメソッドやプロパティはいくつもあります。
- Appllication.Names.Add
- Workbook.Names.Add
- Worksheet.Names.Add
- Workshhet.CreateNames
- Range.Name
名前の定義には有効範囲があって、ワークブック全体（ブック定義）と各ワークシート内（シート定義）に分けられます。 ブック定義の名前は扱いがいろいろ面倒なので、基本的にシート定義の方を使います。
上記リストの中では、Worksheet.Names だけが確実にシート定義の名前にできるのでこれを使います。
ちなみにシート定義されて名前はワークブック（Workbook.Names）からもアクセスできます。
Names.Add メソッドでは、名前と参照先を指定します。 参照先は Range のオブジェクトか絶対アドレスの数式で指定します。 すでに存在する名前を定義するとエラーにはならずに上書きされるようです。
' Range で指定 ActiveSheet.Names.Add "タマ", Range("A1:C10") ' ⇒ Sheet!$A$1:$C$10 ' アドレスで指定 ActiveSheet.Names.Add "ポチ", "=$A$1:$C$10" ' 絶対参照で記述すること ' 別シートへの参照も定義可能 Worksheets("申請書").Names.Add "氏名", "=入力フォーム!$A$1" ' 値や関数を使った数式を指定 ActiveSheet.Names.Add "今日", "=TODAY()"
また、名前には半角/全角のスペースがあるとエラーになるなどの制約があるので注意が必要です。 ほかにも使えない文字や、先頭文字に数字が使えないなどいろいろな制約があります。
本来は別の文字に置換する処理などを入れた方がいいのですが、全ての制約に対処すると複雑になるので解答プログラムでは省略しています。
詳細は以下ページを参照してください。
反対に、名前を削除するには Name オブジェクト自身の Delete メソッドを呼びます。
ただし、ユーザが定義した名前がすでにあるかもしれないのでマクロで無闇に削除するのはちょっと危険です。 解答ではそこに配慮し、 Name の Comment にタグとなるテキストをつけてマクロで定義した名前を区別するようにしています。
■ @ 演算子と暗黙的交差
VBA で設定した数式をワークシート上で見ると、名前の前に「@（アットマーク）」が挿入されている箇所があります。
図
これは Range.Formula に代入する元の数式には無かったもので、なぜか Excel が勝手に挿入してくれています。
この @ マークを削除したらどうなるかというと、スピルしてしまいます。 しかもスピルに失敗して #SPILL! エラーになります。
一体なにが起きているのでしょうか。
この @ マークは参照演算子の一つで、正式には「暗黙的なインターセクション演算子」と呼ばれるものです。 Microsoft 365 版 Excel から動的配列数式の導入に伴って追加された、新しい演算子です。
インターセクション（Intersection/交差）とは複数のセル範囲の間で重なる共有範囲のことです。 VBA プログラマには Application.Intersect のことだと言えば早いですね。 そのまま「交差」と言っても通じるでしょう。
暗黙的というからには明示的な交差もあるのかというと、あります。
あまり知られていませんが、もともと交差演算子というのがあって、それは「 」（半角スペース）を使います。
数式で複数のセル範囲の参照を半角スペースで区切ると、その交差範囲（共有範囲）を参照することができるのです。
では暗黙的な交差とは何かというと、MS 365 以前の古いバージョンの Excel では、配列数式でない（Ctrl+Shift+Enterで入力しない）通常モードの数式でセル範囲を参照すると、それだけでその数式セルの行や列と交差するセルを参照できるという仕様があったのです。 これが（交差演算子を使わない）暗黙的交差という機能です。
これで何がうれしいのかというと、数式で相対アドレスを使わずに名前参照だけで交差セルを参照できるようになるのです。
本記事の解答の数式もこの機能をあてにしていました。
ところが、動的配列数式の登場で、セル範囲の参照はスピルすることになったので、暗黙的交差は反故にされました。 当然それでは困る人もいるので互換性のために、暗黙的な交差を明示するというややこしい演算子の追加が必要になったのです。
ちなみにこの演算子に @ マークが選ばれたのは、もともとテーブル内の構造化参照で行内のフィールド値を参照するのに使われていたのを援用したもののようです。
= [@販売価格] * [@数量]
では VBA の側ではどうかというと、動的配列数式のために Range オブジェクトに Formula2 という名の付くプロパティが一式追加されています。
- Range.Formula2 プロパティ (Excel) | Microsoft Docs
- Range.Formula2Local
- Range.Formula2R1C1
- Range.Formula2R1C1Local
セル範囲参照を動的配列としてスピルさせたい場合 Range.Formula2 系のプロパティを使います。 暗黙的交差をさせたい参照には @ 演算子をプログラマが付加する必要があります。
既存の無印 Formula プロパティの方は今まで通り、代入された数式を古い Excel の形式として正しく評価しますが、暗黙的交差で参照している箇所には自動で @ 演算子が付加されます。
たしかに、セル範囲参照と暗黙的交差参照が混在している数式は @ 演算子の付加で区別しやすくなりました。 解答の VBA プログラムでも Formula2 系のプロパティを使い、数式内ではどこで暗黙的交差しているのか @ 演算子で明示するべきでしたが、どこにつけるべきかを判断するのも面倒だったので無印の Formula で Excel にお任せしました。
.Range("コード").Formula2 = "= @ data!コード" ... .Range("品名").Formula2 = "= XLOOKUP(@コード, 商品マスタ!コード, 商品マスタ!品名)" ... .Range("仕入金額").Formula2 = "= @ 仕入単価 * @ 数量" ... .Range("売上ABC").Formula2 = Join(Array( _ "= LET(", _ " 累積比, SUMIF(売上金額, "">="" & @ 売上金額) / SUM(売上金額), ", _ " それ以上, true, ", _ " IFS(", _ " 累積比 <= 0.5, ""A"", ", _ " 累積比 <= 0.9, ""B"", ", _ " それ以上, ""C"" ", _ " )", _ ")" _ ), vbLf) ...
■ 累積構成比の算出
ABC 分析を作成するにはデータの降順で累積構成比を求める必要があります。
普通ならシート上でデータの並べ替えをしたうえで値を順番に足していけばいいのですが、 今回の課題ではイジワルなことに、分析対象が２系列あるのでどちらかを並べ替えるともう片方が崩れるといった感じで、 数式では一筋縄にはいきません。
つまりデータの並び順によらず降順の累積比を算出する数式を用意する必要があるのです。
そのような算出方法はいろいろあると思いますが、一番素直で解りやすいのは解答で使用した以下の数式でしょう。
= SUMIF(売上金額, ">=" & @ 売上金額) / SUM(売上金額) ↓ Excel 2019 までの古い Excel の場合 ↓ = SUMIF(売上金額, ">=" & 売上金額) / SUM(売上金額)
【注】データ範囲を名前でなくアドレスで参照する場合は絶対アドレスを使います。
SUMIF 関数を使って、注目している行の値以上となるデータを合計して累積値としています。 考え方として悪くありません。
しかしこの計算式にはは、データの分布によっては無視できない欠点があります。 同一値が複数ある、すなわち重複データの構成比が過大評価されてしまうのです。
例えば、1% を占めるデータが 10 件あるとそれぞれが 10% として見積もられてしまい、結果としてパレート曲線が上にブレます。
今回の課題ような金額の集計データでは同一値が発生することはまれなので、この SUMIF の計算式を使ってまず問題ないでしょう。
一方、個数や回数の集計でありがちな、ロングテイルで同じ値（1 など）が長く続くようなデータの場合、ちょっと看過できないかもしれません。
それを避けたい場合、もう少しマシな計算式としては、以下のようなものが考えられます。 ただし、数式がだいぶ複雑になります。
= SUM(LARGE(売上金額, SEQUENCE(RANK.EQ(@売上金額, 売上金額)))) / SUM(売上金額)
↓ Excel 2019 までの古い Excel の場合 ↓
= SUM(LARGE(売上金額, ROW(INDIRECT("A1:A" & RANK(売上金額, 売上金額))))) / SUM(売上金額)
【注】上の数式で「この式は、一部の旧バージョンの Excel ではサポートされていません。」という警告が出たら「いいえ」ボタンを押してください。
【注】下の数式は、配列数式として入力する必要があります。（データ列範囲を選択したうえで先頭セルに数式を入力し、Control+Shift+Enterを押す） 。
この数式では逆に、累積比が過少評価されます。 つまり累積比は重複項目の最初の値と同一になります。結果的にパレート曲線は下方にブレます。
とはいえ、重複データが ABC の判定境界の位置にない限り影響はなく、そこに引っかかったとしてもどこまで気にするか、ABCの上下どちらのグループに倒したいのかは要件によるでしょう。
重複データに順序はないのだから、共に初めの累積比で通す（上位グループに倒す）のは ABC 分析として正しいような気がします。
ではなぜ解答の数式にこちらを採用しなかったかというと、数式がより難解で計算意図が読み取れないので、サンプルコードとしては解りやすい方をとったのです。
ところで、重複データがあってもブレない数式というのはないのでしょうか。
無理やりやりひねり出せばあるにはあるのですが、複雑すぎてもはや実用的ではありません。 もっと簡単な計算方法があるのかもしれませんが、筆者には以下が限界でした。
=SUM(LARGE(売上金額,SEQUENCE(INDEX(SORTBY(SEQUENCE(ROWS(売上金額)),SORTBY(SEQUENCE(ROWS(売上金額)),売上金額,-1),1),ROW(@売上金額)-ROW(INDEX(売上金額,1,1))+1,1)))) /SUM(売上金額)
もう、読む気もおきませんね。
LET 関数を使って書き直すと少し読みやすくなります。
=LET(
rng, 売上金額,
a, SEQUENCE(ROWS(rng)),
b, SORTBY(a, rng, -1),
c, SORTBY(a, b, 1),
d, INDEX(c, ROW(@rng) - ROW(INDEX(rng, 1, 1)) + 1, 1),
e, LARGE(rng, SEQUENCE(d)),
SUM(e) / SUM(rng)
)
それも気休めですが、相当重そうなのは想像できます。 こんなに複雑でトリッキーな計算式を業務用シートに混ぜ込むのはムリですね。
でももしユーザ定義関数として別途定義されていて、どこからでも呼び出せるようになっているのなら使えないこともありません。
そういう意味では、近々 Excel に LAMBDA 関数という新しい機能が追加されるというニュースは朗報です。
LAMBDA 関数を使うと数式のみでユーザ定義関数を作れるようになります。
筆者が試せる Excel 環境ではまだ LAMBDA 関数を使えるものがないためあくまで想像のレベルですが、以下のような数式にして適当な関数名で名前の定義に登録すればどこからでも累積比を算出できるようになるはずです。
=LAMBDA(
data,
IF (COLUMNS(data) > 1,
NA(),
LET (
a, SEQUENCE(ROWS(data)),
b, SORTBY(a, data, -1),
c, SORTBY(a, b, 1),
d, INDEX(c, ROW(@data) - ROW(INDEX(data, 1, 1)) + 1, 1),
e, LARGE(data, SEQUENCE(d)),
SUM(e) / SUM(data)
)
)
)
参考資料
- 名前参照について
- 暗黙的なインターセクション演算子について
- LAMBDA 関数
