Excelでデータ群をクラスタリングするK-means風手法

1章：K-meansの基本を“Excel脳”で理解する（できること／できないこと）

「クラスタリング＝なんとなく似ているもの同士をグループ分けする」――このざっくりした理解でOKです。K-means（ケイミーンズ）は、その中でも“平均（mean）”を使ってグループを更新していく代表的な手法。難しく聞こえますが、やっていることはExcelでよく見る発想に近いです。

Excel脳で言い換えると、K-meansは次の繰り返しです。

1. 代表点（重心）をK個用意する（仮の「中心セル」みたいなもの）
2. 各データがどの代表点に一番近いかを計算して、所属先を決める
3. 所属が決まったら、各グループのAVERAGEで新しい代表点を作り直す
4. 2〜3を、所属が変わらなくなるまで繰り返す

たとえば「売上」と「購入頻度」の2項目で顧客を分けたいとします。K-meansは、顧客それぞれを2次元の点として見て、距離が近い顧客同士を同じクラスタに寄せる動きをします。Excel的には、各行（顧客）に対して距離の計算列を作り、MINで最小のクラスタ番号を割り当て、最後にピボットやAVERAGEIFSで重心を更新……というイメージです。

ここで重要なのが、K-meansが得意なこと／苦手なこと。Excel実装に入る前に、期待値を合わせておくと「思ったより微妙…」を避けられます。

できること（得意）

• 数値データをK個のグループに分ける（売上・頻度・単価・滞在時間など）
• 結果が解釈しやすい（各クラスタの平均値＝“こういう層”が見える）
• Excelで再現しやすい（距離計算、最小値判定、平均の更新＝関数で組める）

できないこと（苦手）

• 文字データ中心の分類（職種名や商品名だけ、みたいなデータはそのままだと不可）
• 形がいびつなクラスタ（細長い・輪っか状などは苦手。丸っぽい集まりが得意）
• 外れ値に弱い（1件の超高額購入者が“中心”を引っ張ることがある）
• K（クラスタ数）を自動で決めてくれない（ここは人間が仮説を持つ必要あり）

つまりK-meansは、「数値で測れる特徴があり」「だいたい似た者同士が固まっていそう」なデータに強い。一方で、前処理（欠損・外れ値・尺度合わせ）を雑にすると途端に崩れるタイプです。次章では、まさにこの“精度が決まる下準備”をExcelでどう整えるかを整理していきます。

2章：クラスタリング前の下準備（データ整形・欠損・標準化で精度が決まる）

K-meansは「距離が近い＝同じグループ」と判断します。つまり、距離計算に入れる前のデータが汚いと、そのまま“汚い距離”で分類されます。ここを丁寧にやるだけで、同じ手順でも結果の納得感が一気に上がります。

① データ整形：1行＝1対象、1列＝1特徴にする

まずはExcelの基本形に寄せます。クラスタリング対象（例：顧客）を各行に、特徴量（例：売上、購入頻度、平均単価）を各列に置きます。

• 見出し行は1行目に固定（後で参照しやすい）
• 「合計」「平均」などの集計行はデータ範囲から除外
• 文字列の「-」「N/A」混入は数値扱いできず事故るので、空白か数値に統一

また、日付やカテゴリ（支店名など）が混ざる場合、K-meansに入れるのは基本数値列だけ。カテゴリを使いたいなら、支店ごとに0/1の列（ダミー化）が必要ですが、Excelだけでやると列が増えがちなので、まずは数値特徴に絞るのが現実的です。

② 欠損値：放置しない（削除・補完・別扱い）

欠損は「距離が計算できない」or「勝手に0として扱って歪む」原因になります。対処はざっくり3択です。

• 削除：欠損が少ないなら、対象行を除外（母数は減るが確実）
• 補完：平均との差し替えが簡単（例：=IF(ISBLANK(B2),AVERAGE($B:$B),B2)）
• 別扱い：欠損が意味を持つなら「欠損フラグ列（0/1）」を作る

20代会社員の実務だと、まずは「欠損率が数％なら削除、数十％なら列ごと見直し」が無難です。補完は“それっぽい数字”を入れる行為なので、後で施策に使うなら、どのルールで埋めたかをメモしておきましょう。

③ 外れ値：中心（平均）を引っ張るので要注意

K-meansは平均で中心を作るので、外れ値が1件あるだけで重心がズレます。まずはサクッと見える化。

• 並べ替えで上位・下位を確認
• 箱ひげ図（可能なら）や散布図で極端な点を把握

対策は「除外」「上限下限でクリップ（例：上位1%を上限に丸める）」「対数変換（LOG）」など。特に金額系（売上・単価）は桁が暴れやすいので、LOG変換は効きやすいです（例：=LOG10(売上+1)）。

④ 標準化：尺度（単位）を揃えないと“売上だけで分類”になる

ここが一番大事です。例えば「売上（最大1000万）」と「購入頻度（最大30回）」をそのまま距離に入れると、距離はほぼ売上で決まります。つまり、頻度を入れたつもりでも無視されがち。

対策は標準化（zスコア）が定番です。各列を「平均との差」「ばらつき」で割って、平均0・標準偏差1に揃えます。

• 平均：=AVERAGE(B:B)
• 標準偏差：=STDEV.S(B:B)
• 標準化：=(B2-$B$平均セル)/$B$標準偏差セル

列ごとに「標準化後」列を作っておくと、3章の距離計算がスムーズです。なお、0〜1に揃える正規化（min-max）でもOKですが、外れ値に弱いので、迷ったらzスコアを推します。

まとめると、2章のゴールは「距離を計算しても妥当な数値テーブル」を作ること。整形→欠損→外れ値→標準化まで整ったら、いよいよExcelで“K-means風”の反復手順（初期点→距離→割り当て→重心更新）を組んでいきます。

3章：Excelで作る「K-means風」手順（初期点→距離→割り当て→重心更新）

下準備（欠損・外れ値・標準化）ができたら、ここからはExcelでK-meansっぽい反復を組みます。マクロなしでもいけます。やることは1章で触れた通り「距離→割り当て→平均で重心更新」を回すだけ。ポイントは“どのセルが入力でどのセルが計算か”を固定して、コピペで反復できる形にすることです。

Step0：シート構成を決める（事故防止）

• Data：標準化後の特徴量（例：Z_売上、Z_頻度…）
• Centroid：各クラスタの重心（K行×特徴量列）
• Calc：距離、所属クラスタ、各クラスタの集計

1シートに全部詰めてもOKですが、見失いやすいので分けると楽です。

Step1：初期点（重心）をK個置く

初期値は「それっぽい中心」を仮置きするだけ。おすすめは次のどれかです。

• データからK行を抜いてコピペ（標準化後の値の行をそのまま重心として採用）
• 乱数で行番号を選ぶ（=RANDBETWEEN(2,最終行)→INDEXで引く）

Centroidシートに、例えばK=3なら「C1〜C3」みたいにクラスタIDを振り、列に特徴量（Z_売上、Z_頻度…）を並べます。

Step2：距離を計算する（各データ×各重心）

Calcシートで、各データ行に「クラスタ1までの距離」「クラスタ2までの距離」…を横持ちで作ります。距離はユークリッド距離（平方根付き）が定番ですが、比較だけなら平方根は不要なので「二乗距離」でOKです（速い＆見やすい）。

特徴量がData!B:D（3特徴）にあり、Centroidのクラスタ1の重心がCentroid!B2:D2にある例：

=SUMXMY2(Data!B2:D2, Centroid!B2:D2)

SUMXMY2は「差の二乗和」を一発で返してくれます。クラスタ2、3も重心参照行を変えて横にコピーします。

Step3：最小距離のクラスタに割り当てる

距離が出たら所属決定です。距離列がE:G（クラスタ1〜3）なら、所属クラスタは次のようにできます。

=XMATCH(MIN(E2:G2), E2:G2)

これで「最小の距離が何列目か」＝クラスタ番号になります（Microsoft 365想定）。古いExcelなら、MATCHでもOKです。

Step4：割り当て結果から重心を更新する（平均を取り直す）

ここがK-meansの“肝”。各クラスタに属したデータだけを集めて、特徴量ごとに平均を取ります。例えばクラスタID列がCalc!H、標準化特徴がData!B:Dにあるなら、クラスタ1のZ_売上の新重心は：

=AVERAGEIF(Calc!$H:$H, 1, Data!B:B)

特徴量列ごとに横へコピー、クラスタ2なら条件を2に、という形でCentroid表を埋めます。これで「重心が新しい位置」に更新されます。

Step5：収束するまで繰り返す（Excelでは“手で回す”が現実的）

更新された重心をもとに距離→割り当てが変わり、また平均で重心が変わる…を繰り返します。Excelだと、基本は重心表だけを“値貼り付け”して次の反復へが安定します（参照が循環すると沼ります）。

• Centroid表をコピー →「値として貼り付け」
• 割り当て（クラスタID）が変わるかを見る
• 変わらなくなったら終了

ここまでできれば、Excelでも十分「K-means風」にクラスタが作れます。次章では、Kの決め方や初期値で結果がブレる問題（初期値ガチャ）、そしていつ止めるか（収束判定）を、実務で使えるレベルに整えるコツをまとめます。

4章：精度を上げるコツ（Kの決め方・初期値ガチャ対策・収束判定）

3章の手順で「K-means風」は回せます。ただ、実務で使うならもう一段大事なのが精度（＝納得感）を上げる工夫です。具体的には、①Kをどう決めるか／②初期値で結果がブレる問題をどう潰すか／③いつ止めるか（収束判定）を押さえるだけで、同じExcelでも一気に“使える分析”になります。

① Kの決め方：まずは「目的→候補K→指標」で絞る

Kは自動で正解が出ません。なので順番は目的から逆算が安定です。例えば顧客施策なら「営業が使えるのは3〜6分類」など、運用制約が先にあります。

その上で、候補K（例：2〜8）を回して、各Kのクラスタ内のバラつきを見ます。Excelでも「SSE（各点と重心の二乗距離の合計）」を作れます。

• Calcにある「最小二乗距離」（各行のMIN距離）を別列にして
• SSEセルで =SUM(最小二乗距離列)

Kを増やすほどSSEは必ず下がります。なので、グラフにして下がり方が鈍る“肘（エルボー）”のKを候補にします（完全に綺麗な肘は出ないので、実務では「鈍ったところ＋運用できる数」で決めるのが現実的）。

② 初期値ガチャ対策：複数回回して“当たり”を採用する

K-means最大の罠が初期重心で結果が変わること。Excel実装だと特に「たまたま偏った初期点」だと、変なクラスタで収束しがちです。対策はシンプルで、初期値を変えて複数回やるだけ。

• Kを固定して、初期点を変えて5〜10回回す
• 各回のSSE（上で作ったSUM）を記録する
• SSEが最小の回を採用（＝クラスタ内が最も締まっている）

初期点の作り方も工夫できます。完全ランダムよりおすすめは、「一度離した点を選ぶ」やり方です。たとえば、1つ目はランダムに選び、2つ目以降は「既に選んだ重心から距離が大きいデータ行」を候補にすると、初期重心が散ってスタートしやすくなります（Excelなら距離列を作って並べ替え→上位から採用、で十分“k-means++っぽさ”が出ます）。

③ 収束判定：属先が変わらない or 重心の移動が小さい

「いつ止めるか」が曖昧だと、無限に回して疲れます。Excelでは次のどちらかでOKです。

判定A：所属クラスタがもう変わらない

前回のクラスタID列（例：Prev）をコピーして保持し、今回のID列（Now）と比較します。例えば行ごとに：

=IF(Prev2=Now2,0,1)

を作って、最後に =SUM(差分列) が0なら終了。直感的でおすすめです。

判定B：重心の移動量が一定以下

Centroidの「前回重心」と「今回重心」の差の二乗和をクラスタごとに計算し、合計が小さければ止めます。

=SUMXMY2(前回重心の行範囲, 今回重心の行範囲)

これが全クラスタで十分小さい（例：0.001未満など）なら収束扱い。特徴量が標準化済み（2章）なら、閾値を決めやすいのもメリットです。

最後に現場目線の注意点を1つ。SSEが小さい＝正解、ではありません。クラスタが「解釈できる」「施策に落ちる」ことが重要です。K・初期値・収束をコントロールすると、Excelでも“説明できるクラスタ”が作れます。次章では、そのクラスタをどう業務に使うか（顧客セグメント／商品カテゴリ／異常値検知）を具体例でつなげます。

5章：現場で使える活用例（顧客セグメント・商品カテゴリ・異常値の見つけ方）

Excelで「K-means風」クラスタが作れたら、次は“どう使うか”です。コツは、クラスタ番号（1/2/3…）のままだと誰も動けないので、クラスタの特徴を日本語ラベルに翻訳して、施策・運用に接続すること。ここでは現場で刺さりやすい3パターンに絞って紹介します。

① 顧客セグメント：営業・CRMに落とす

例として特徴量を「標準化済みの売上（Z）」「購入頻度（Z）」「平均単価（Z）」にすると、クラスタはだいたい次のように解釈できます。

• 高頻度×高単価：優良顧客（維持施策・アップセル）
• 高頻度×低単価：リピート層（まとめ買い提案・セット）
• 低頻度×高単価：スポット高額層（離脱防止・次回導線）
• 低頻度×低単価：ライト層（コストをかけないナーチャリング）

実務では、クラスタごとに“人数（件数）”と“元の尺度の平均値”を並べると一気に伝わります。標準化したままだと現場がイメージできないので、ピボットやAVERAGEIFで「売上（円）平均」「頻度（回）平均」を出して、最後にラベル付けしましょう。

② 商品カテゴリ：似た売れ方の商品を束ねる

顧客だけでなく「商品」を行にしても使えます。特徴量の例は「月次売上」「粗利率」「返品率」「購入者数」「季節指数」など。クラスタリングすると、“同じ売れ方・同じ課題のある商品群”が束になります。

使いどころはシンプルで、クラスタ単位で意思決定が速くなります。

• 高売上×低粗利クラスタ：値付け・仕入れ交渉の優先対象
• 低売上×高粗利クラスタ：露出強化（売り方改善で伸びしろ）
• 高返品率クラスタ：品質・説明文・サイズ表記の見直し

ポイントは、商品ごとに施策を考えると破綻するので、「クラスタ＝施策メニュー」にして運用コストを下げることです。

③ 異常値の見つけ方：クラスタから“浮いた行”を拾う

K-means自体は外れ値に弱い（2章）ものの、運用的には異常検知の“あたり”を付けるのに役立ちます。やり方は簡単で、Calcにある「所属クラスタへの最小二乗距離（MIN距離）」を使います。

• 各行のMIN距離が大きい＝「どの中心からも遠い」＝要確認
• MIN距離を降順に並べ、上位1〜5%をチェック対象にする

例えば顧客なら「入力ミス（桁違い）」「一時的な爆買い」「不正の疑い」、商品なら「急な返品増」「想定外の値引き」など、原因調査の優先順位付けに効きます。ここでも大事なのは、異常を自動断定しないこと。クラスタリングはあくまで“見に行くべき行を絞る仕組み”として使うと、日次・週次でも回せます。

まとめると、ExcelのK-means風手法は「分析して終わり」ではなく、ラベル化→施策化→監視指標（距離）までつなげると一気に現場の武器になります。クラスタは作った瞬間より、運用に組み込んだ瞬間に価値が出ます。