LAMBDA関数で「Excelに存在しない統計関数」を実装する

ジニ係数は、所得分配の不平等さを測る指標として知られていますが、ビジネスデータ分析では「売上が一部の商品・顧客・店舗にどれくらい偏っているか」を見る指標としても使えます。ABC分析やローレンツ曲線と非常に相性がよく、ロングテールの度合いを1つの数値に圧縮できます。

ところがExcelには GINI 関数がありません。毎回データを昇順に並べ、順位を付け、累積比率を作り、ローレンツ曲線の面積を計算するのは、単発分析ならまだしも、繰り返し使うには重すぎます。

A列に商品別売上が並んでいるとします。まずはセル上でLAMBDAを即時実行し、ロジックが正しく動作するかを確認します。

動作確認ができたら、このLAMBDA式を「名前の管理」に登録します。名前は GINI としておきます。

数式の内部では、まず TOCOL(data, 1) で入力範囲を1列の数値配列に整形し、空白を無視します。続いて SORT で昇順に並べ、SEQUENCE(n) で順位ベクトルを作成します。

ジニ係数の簡便式は、昇順に並べた値 x と順位 i の積和を利用して求めます。LET によって x、n、i を名前付きの中間変数として切り出すことで、長い数式でありながら「何をしているか」が追跡しやすくなります。

GINI(B2:B6) と呼び出せるようになれば、以後はローレンツ曲線を毎回作らなくても、データの偏り具合だけを即座に数値化できます。

標準偏差は便利ですが、外れ値の影響を強く受けます。売上、購買金額、滞在時間、処理時間のように裾が長くなりやすいデータでは、少数の極端な値に標準偏差が引っ張られ、「典型的なばらつき」を表しにくくなることがあります。

そこで使えるのが MAD（Median Absolute Deviation） です。各データから中央値を引き、その絶対偏差の中央値を取ることで、外れ値に頑健なばらつき指標を得ます。しかしこれもExcel標準には専用関数がありません。

B列に処理時間データが並んでいるとします。最後の1件だけ極端に大きい値が混ざっています。

名前の管理で MAD として登録すれば、以後は以下のように呼び出せます。

MADのキモは、平均ではなく中央値を中心に据える点にあります。中央値は極端な値の影響を受けにくいため、外れ値が混ざっても「大多数のデータがどれくらい散らばっているか」を比較的安定して表現できます。

ABS(x - med) は、各データが中央値からどれくらい離れているかを配列として返します。その絶対偏差の中央値を再び MEDIAN で取れば、外れ値に引きずられにくいばらつき指標が完成します。

実務データには、入力ミスや極端な取引など、分析の邪魔をする外れ値がつきものです。外れ値の検出方法はいくつかありますが、分布の形状を仮定しない頑健な手法として広く使われるのが IQR法 です。

IQR（四分位範囲）とは、第3四分位数（Q3）から第1四分位数（Q1）を引いた値です。一般に、Q1 − 1.5×IQR を下回るか、Q3 + 1.5×IQR を上回る値を外れ値の候補とみなします。

しかしExcelには外れ値フラグを一括で返す専用関数がありません。QUARTILE.INC でQ1・Q3を計算し、IQRを求め、各データと比較する——この処理を毎回セルに展開するのは、繰り返し分析には向きません。LAMBDAで関数化しておく典型的な題材です。

実践例2と同じ処理時間データを使います。MADは「ばらつきの大きさ」を1つの数値で返しましたが、OUTLIER_IQR は「個々のデータが外れ値かどうか」をフラグで返します。

まずセル上で即時実行して動作確認します。C2 に入力して C6 までオートフィルすると、案件Eだけ TRUE が返ります。

動作確認ができたら「名前の管理」に登録します。

※ data 引数は判定対象の全データ範囲を絶対参照で固定してください。val 引数は判定したい個々のセルを相対参照にすることでオートフィルが使えます。

IQR法の強みは、データの分布形状を一切仮定しない点にあります。標準偏差を使った外れ値検出（平均±3σなど）は正規分布を前提とするため、裾の長いデータでは過検出・見逃しが起きやすくなります。IQR法はその点で頑健です。

数式内の乗数 1.5 はTukeyが提唱した標準的な値ですが、より厳しく検出したい場合は 3.0 を使うこともあります。この乗数を引数として外から渡せるよう拡張するのも、LAMBDAならではの使い方です。

t検定を行うと、平均差が統計的に有意かどうかは確認できます。しかし、サンプルサイズが大きいと小さな差でも有意になり、逆にサンプルサイズが小さいと実務的に大きな差でも有意になりにくいことがあります。

そこで、2群の平均との差を標準偏差で割り、差の大きさを標準化して見るのが Cohen's d です。Excelにはt検定の関数はあっても、効果量を直接返す関数はありません。

A列にAチーム、B列にBチームのテストスコアが入っているとします。ここでは互いに独立した2つの群（対応のない2群）を想定し、サンプルサイズが異なる場合にも対応できる「プールした標準偏差」を使う基本形を実装します。

※本数式は「対応のない独立した2群」のための計算式です。同一人物の「施策前・施策後」の比較など、データ同士にペアの関係がある（対応のある）場合は、差分の標準偏差を用いた別の数式が必要になりますので、データの構造に注意してください。

sp は2群の分散をサンプルサイズで重み付けして合成したプール標準偏差です。2つの群の平均差をこの sp で割ることで、元の単位に依存しない「標準偏差何個分の差か」を表します。

t検定が「偶然とは言いにくいか」を問うのに対し、Cohen's dは「どれくらい大きな差なのか」を問います。LAMBDA化しておくと、検定結果の横に効果量を添える分析表を簡単に組み立てられます。

アンケート分析では、「満足度」を1つの質問だけで測るのではなく、複数の設問で構成された尺度として扱うことがあります。そのとき、複数項目が同じ概念を安定して測れているかを確認する指標として Cronbach's α が使われます。

しかしExcelには CRONBACH.ALPHA のような標準関数はありません。項目ごとの分散を求め、合計得点の分散を求め、項目数を数え、式に代入する必要があります。この処理は典型的な「毎回同じなのに、毎回作り直す」分析です。

B列からE列に4つのアンケート項目、各行に回答者が並んでいるとします。各項目は1〜5点のリッカート尺度を想定します。

Cronbach's αは、項目数 k、各項目の分散の合計、合計得点の分散から求められます。項目同士が同じ方向を向いていれば、回答者ごとの合計得点の分散は大きくなり、項目分散 of 単純な寄せ集めとの差が生まれます。

数式内では、BYCOL によって項目列ごとの分散を計算し、BYROW によって回答者ごとの合計得点を作っています。ここで LAMBDA は、名前付き関数を作るためだけでなく、BYCOL や BYROW に渡す小さな処理単位としても登場します。

この例は、LAMBDAが単独で便利なのではなく、動的配列関数群と組み合わせたときに「統計処理の部品化」を一気に進められることを示しています。

1. Excel標準に存在する関数をわざわざ包まない： STANDARDIZE のように標準関数で十分な処理は、LAMBDA化しても記事としての旨味は薄い。狙うべきは、標準関数にないが実務では繰り返し使う統計処理です。
2. まずセル上で即時実行して検証する： =LAMBDA(...)(...) の形で動作確認してから名前の管理へ登録すると、名前付き関数のデバッグがかなり楽になります。
3. LET で中間変数に名前を付ける： LAMBDAで作る関数は長くなりがちです。意味のある名前を置くことで、数式を「読めるコード」に近づけます。
4. LET の変数名はセルアドレスと競合しない名前にする： n1・q1 のような短い名前はセルアドレスと衝突し、予期しない挙動を引き起こすことがあります。アンダースコアを先頭に付けた _n1・_q1 のような名前が安全です。
5. 動的配列関数と組み合わせて配列対応させる： TOCOL、BYROW、BYCOL、MAP と組み合わせることで、1セルの処理を範囲全体へ展開できます。

LAMBDA関数の本質は、単に「長い数式に名前を付ける」ことではありません。分析のたびに作り直していた統計ロジックを、名前付きの小さな関数として切り出し、ワークブックの中に再利用可能な部品として置くことです。

GINI、MAD、OUTLIER_IQR、COHEN_D、CRONBACH_ALPHA のような関数を登録しておけば、Excelは単なる表計算ソフトから、少しだけ「自分専用の統計パッケージ」に近づきます。