Excelに並べた顧客データ、売上データ、アンケート結果などを眺めていても、似た傾向を持つ対象がどこにあるのかを判断しにくい場面があります。
そのようなときに役立つ分析手法が、距離の近いデータ同士を自動でまとめるクラスター分析です。
専門的な統計ソフトが必要と思われがちですが、Excelでもデータの整形、距離の計算、グループ分け、散布図による確認までを順に進められます。
クラスター分析では、数値の大きさではなく、複数項目をまとめて見たときの似ている度合いを利用します。
たとえば購入金額、購入回数、来店間隔を使えば、優良顧客、継続顧客、休眠傾向の顧客などを見分ける材料になります。
この記事では、1行目にヘッダーがあるサンプルデータを使い、Excelでクラスター分析の考え方を実務に取り入れる基本手順を解説します。
最初にデータの尺度をそろえ、次に距離を求め、最後にグループの意味を読み取る流れを意識すると、分析の精度と説明の分かりやすさが高まります。
Excelでクラスター分析を進める基本手順
それではまず、Excelでクラスター分析を行う全体の流れについて解説していきます。
| 顧客ID | 購入金額 | 購入回数 | 来店間隔 |
|---|---|---|---|
| A001 | 82000 | 18 | 12 |
| A002 | 26000 | 7 | 35 |
| A003 | 97000 | 21 | 9 |
分析目的とグループ数の仮決め
クラスター分析では、先に何を知りたいのかを決めることが重要です。
顧客を販促施策別に分けたいのか、商品を売れ方で分類したいのか、拠点を業績傾向で比較したいのかによって、使用する列が変わります。
今回の例では、購入金額、購入回数、来店間隔の3項目から顧客を分類します。
グループ数は絶対的な正解ではなく、施策に使える分け方かどうかで判断するものです。
最初は3グループ程度を仮置きし、結果を確認してから2グループや4グループと比較すると理解しやすいでしょう。
分析目的の例として、高額かつ頻繁に購入する顧客には優待案内を行い、来店間隔が長い顧客には再来店を促す施策を考える、といった活用方法があります。
Excelで扱いやすいデータ形式
元データは、1行が1件、1列が1項目となる一覧形式に整えます。
1行目には顧客ID、購入金額、購入回数、来店間隔のようなヘッダーを置き、2行目以降に対象ごとの数値を入力します。
空白セル、文字列として保存された数値、単位が混在した列があると、計算結果が不安定になります。
金額の列に「円」、回数の列に「回」などを直接入力している場合は、数値だけの列を別に作成する方法が安全です。
クラスター分析に使う列は、比較したい特徴を数値で表した列に限定することが基本になります。
実施順序と結果確認の流れ
Excelでの基本的な流れは、データ確認、標準化、距離計算、グループ付与、集計、可視化の順です。
Excel単体では高度な階層クラスター分析をボタン一つで実行する機能が限られるため、まずは数式と並べ替えを用いる方法を理解すると応用が利きます。
分析ツールやアドインを利用できる環境では、その結果を使っても構いませんが、入力データの意味を確認する工程は省略できません。
【操作のポイント】最終結果だけを見るのではなく、標準化後の値とグループ別平均も確認すると、分類根拠を説明しやすくなります。
分析用データの整形と外れ値の確認
続いては、クラスター分析に使用するデータの整え方を確認していきます。
| 顧客ID | 購入金額 | 購入回数 | 来店間隔 | 確認結果 |
|---|---|---|---|---|
| A001 | 82000 | 18 | 12 | 使用可 |
| A004 | 450000 | 2 | 180 | 要確認 |
欠損値と入力形式の点検
まず、分析に使うB列からD列に空白がないかを確認します。
購入金額だけが未入力の顧客を含めたまま距離を計算すると、数式のエラーや誤った比較につながるためです。
空白を0として扱うか、その行を分析対象から外すかは、業務上の意味を考えて決めます。
たとえば購入履歴がないことを0円と判断できる場合と、単にデータ未連携である場合とでは、処理を分ける必要があります。
空白セルを安易にゼロへ置き換える前に、空白が表す意味を確認することが欠かせません。
外れ値と重複データの見分け方
極端に大きい購入金額や非常に長い来店間隔があると、少数のデータがグループ分けに大きく影響します。
外れ値は必ず削除するものではありませんが、入力ミスなのか、実際に重要な顧客なのかを確認する必要があります。
Excelではフィルターで大きい順または小さい順に並べ替えると、候補を素早く確認できます。
顧客IDが重複している場合は、同一顧客の明細が複数行にある可能性もあるため、集計してから分析用の1行にまとめましょう。
外れ値を残す場合は、外れ値を含む結果と除いた結果を比較します。
グループの意味が大きく変わるなら、外れ値専用の区分を設ける判断もできます。
分析対象列の選択基準
選ぶ項目が多すぎると、似ている理由を説明しにくくなります。
反対に項目が少なすぎると、重要な違いを見落とすかもしれません。
購入金額と購入回数のように強く関連する列を両方使うことは可能ですが、同じ特徴を過度に重ねていないか意識します。
顧客ID、郵便番号、電話番号のような識別情報は、通常は距離計算に含めません。
【操作のポイント】分析列を選んだら、元データは残したまま別シートへコピーし、分析専用の表として作業すると修正しやすくなります。
標準化による尺度の統一
続いては、各項目の単位差をなくす標準化について確認していきます。
| 顧客ID | 購入金額 | 金額標準化 | 購入回数 | 回数標準化 |
|---|---|---|---|---|
| A001 | 82000 | 0.31 | 18 | 0.42 |
| A002 | 26000 | -0.88 | 7 | -0.73 |
標準化が必要になる理由
購入金額が数万円単位で、購入回数が数回から数十回のように桁が異なる場合、そのまま距離を計算すると金額の影響が極端に大きくなります。
本来は購入回数も重要な特徴であっても、金額の差だけでグループが決まる状態になりかねません。
標準化は、異なる単位の項目を同じ物差しで比較するための前処理です。
標準化後は平均が0、標準偏差が1に近い値となり、各項目の影響を比較的公平に扱えます。
STANDARDIZE関数を使う数式
購入金額がB列のB2からB21に入力されており、E列に標準化後の値を出す場合を考えます。
=STANDARDIZE(B2,AVERAGE($B$2:$B$21),STDEV.S($B$2:$B$21))
この数式では、B2が対象となる購入金額、AVERAGE関数が購入金額列の平均、STDEV.S関数が標本標準偏差を求めます。
平均と標準偏差の参照範囲には$を付け、下方向にコピーしても範囲が動かないようにする点が大切です。
数式をE2に入力したら、フィルハンドルを下へドラッグして最終行までコピーします。
STANDARDIZE関数の結果がマイナスでも異常ではなく、平均より低いことを示す値です。
来店間隔の向きと解釈
来店間隔は数値が小さいほど来店頻度が高いという、購入金額とは逆向きの意味を持つ項目です。
標準化そのものは数値をそのまま使えますが、グループの特徴を説明する際には向きを意識しましょう。
頻度が高いほど大きい指標へそろえたい場合は、来店間隔の符号を反転させる方法もあります。
反転後の指標を作る例として、元の来店間隔がD2にある場合は、=-D2を別列に入力します。
ただし分析途中で定義を混ぜないため、列見出しを来店頻度スコアなどと分かりやすく変更します。
【操作のポイント】標準化は分析に使う全列へ同じ考え方で実施し、元の値の列は削除せずに残しておきます。
距離計算とグループ分けの実践
続いては、標準化した値から対象同士の近さを求め、Excel上でグループ分けする方法を確認していきます。
| 顧客ID | 金額標準化 | 回数標準化 | 間隔標準化 | グループ |
|---|---|---|---|---|
| A001 | 0.31 | 0.42 | -0.58 | 高頻度型 |
| A002 | -0.88 | -0.73 | 0.67 | 低稼働型 |
ユークリッド距離の計算
クラスター分析では、複数項目の差をまとめた距離としてユークリッド距離がよく使われます。
顧客Aと顧客Bの標準化済みの値を比較する場合、各項目の差を二乗し、合計した後で平方根を取ります。
距離＝SQRT((金額差)^2＋(回数差)^2＋(来店間隔差)^2)
たとえばA001の標準化値がE2からG2、A002の標準化値がE3からG3なら、別セルに次の数式を入力できます。
=SQRT((E2-E3)^2+(F2-F3)^2+(G2-G3)^2)
計算結果が小さいほど、3項目を合わせた特徴が似ていると判断できます。
距離は似ているかどうかの相対的な指標であり、単独の数値だけで良し悪しを決めるものではありません。
基準値との距離による分類
Excelで分かりやすくグループ分けする方法として、代表的な基準値との距離を比較する方法があります。
たとえば高頻度型、標準型、低稼働型を表す仮の中心値を別表に置き、各顧客から各中心値までの距離を求めます。
3つの距離のうち最も小さいものを、その顧客の仮グループとして割り当てます。
距離がH2からJ2に並んでいる場合、最小値を求める数式は次の形です。
=MIN(H2:J2)
さらにMATCH関数とINDEX関数を組み合わせれば、最小距離に対応するグループ名を返せます。
最初の中心値は仮の出発点なので、分類後の平均値を見ながら調整する考え方が実務的です。
繰り返し計算とグループの更新
グループへ割り当てた後は、各グループに含まれる顧客の平均値を計算し、新しい中心値として更新します。
AVERAGEIF関数やピボットテーブルを使うと、グループ別の購入金額、購入回数、来店間隔の平均を確認できます。
更新した中心値を使って再び距離を計算し、グループの割り当てが変わらなくなるまで繰り返します。
この考え方はk-means法に近く、データを指定した数のグループへ分ける際の基本になります。
【操作のポイント】中心値の表は分析データと別の場所に固定し、数式参照には絶対参照を使うとコピー時のずれを防げます。
グループ別集計と散布図の活用
続いては、作成したグループを集計し、可視化して意味を読み取る方法を確認していきます。
| グループ | 人数 | 平均購入金額 | 平均購入回数 | 平均来店間隔 |
|---|---|---|---|---|
| 高頻度型 | 24 | 87500 | 19.2 | 11.4 |
| 低稼働型 | 31 | 22800 | 5.8 | 39.7 |
ピボットテーブルによる特徴の比較
グループ名の列を作成したら、挿入タブからピボットテーブルを作成します。
行にグループ、値に顧客IDの件数、購入金額の平均、購入回数の平均、来店間隔の平均を配置すると、各分類の違いを一覧できます。
高頻度型の平均購入回数が高く、来店間隔が短いなら、名称と実態が整合していると判断できます。
グループ名は計算結果ではなく、集計後の特徴を読んでから付けるラベルです。
グループ名の例として、購入金額と回数が高い層は優良顧客型、回数は多いが金額が低い層は日常利用型、間隔が長い層は休眠予備群などが考えられます。
散布図による分布の確認
購入金額を横軸、購入回数を縦軸にした散布図を作成すると、分類の偏りやまとまりを視覚的に確認できます。
Excelでは、対象列を選択して挿入タブから散布図を選びます。
グループごとに系列を分け、色を変えると、近い位置の顧客が同じグループに集まっているかを把握しやすくなります。
3項目すべてを一度に平面図へ表すことは難しいため、購入金額と回数、購入回数と来店間隔など、複数の組み合わせで確認する方法が有効です。
散布図で大きく重なって見える場合は、グループ数や使用項目を見直すきっかけになります。
施策への落とし込み
分析の目的は、グループを作ること自体ではなく、次の判断へ役立てることです。
高頻度型には限定商品の先行案内、低稼働型には再来店クーポンというように、特性に応じて施策を変えられます。
ただし、個人情報の取り扱い、過度な属性推定、少人数グループへの不適切な対応には注意が必要です。
【操作のポイント】グループ別平均だけでなく人数も併記し、対象数が極端に少ないグループの判断は慎重に行います。
クラスター分析の注意点と精度の見直し
続いては、Excelでクラスター分析を行う際に押さえたい注意点を確認していきます。
| 確認項目 | 見直しの視点 | 対応例 |
|---|---|---|
| グループ数 | 分類が細かすぎないか | 2から5程度で比較 |
| 使用項目 | 目的と関係があるか | 不要列を除外 |
グループ数による結果の違い
グループ数を3にするか4にするかで、同じ顧客でも所属する区分が変わることがあります。
グループ数が少なすぎると特徴の異なる顧客が一つにまとまり、多すぎると実務で扱いにくい細かな区分になります。
分類の良さは、数学的な近さだけでなく、施策や報告で使える分かりやすさも含めて判断します。
複数のグループ数でピボットテーブルを作り、各群の人数と平均値を比較すると、採用すべき分類を検討しやすくなります。
データ量とExcelの役割
数十件から数百件程度のデータなら、Excelの数式やピボットテーブルでも分析の仕組みを確認できます。
一方で、対象件数や項目数が大きくなると、距離表の作成だけでもセル数が急増します。
多数の対象を本格的に階層クラスター分析したい場合は、統計ソフト、BIツール、PythonやRなどの利用も選択肢になります。
それでもExcelは、データの整形、結果の確認、現場への共有に強く、分析の入口として有用です。
Excelは、データを理解しながら小規模に試す場として活用し、処理規模や再現性の要件に応じて専用ツールへ広げる考え方が現実的です。
再現性を保つ管理方法
分析用シートには、使用した列、標準化の式、中心値、グループ数、実施日を記録しておきます。
翌月に同じ分析を行う場合も、同じ定義を使うことで結果の変化を比較できます。
元データを上書きせず、分析用のファイルやシートを分けて保存することも大切です。
数式の根拠とグループ名の定義を残すことが、分析結果を組織で共有する土台になります。
【操作のポイント】分析結果を配布する前に、フィルター条件、外れ値の扱い、標準化の対象範囲をメモとして残しましょう。
まとめ クラスター分析をエクセルで行う基本手順
Excelでクラスター分析を行うときは、まず目的に合う数値データを用意し、欠損値や外れ値を確認します。
購入金額、購入回数、来店間隔のように単位が異なる項目は、STANDARDIZE関数で標準化してから比較することが重要です。
その後、ユークリッド距離を使って似ている対象を把握し、基準値との距離やグループ別平均をもとに分類を進めます。
ピボットテーブルと散布図で結果を確認し、実際の施策に使える名称とグループ数へ整えると、分析が単なる集計で終わりません。
Excelは小規模なデータでクラスター分析の考え方を試すのに適したツールです。
データ量が増えた場合も、Excelで整形と確認を行いながら、必要に応じて専門ツールへつなげていきましょう。
