業務改善と標準化を同時に実現:MS 365業務システム開発

階層クラスター分析をエクセルでやる方法【初心者向け】

    
階層クラスター分析をエクセルでやる方法【初心者向け】
\ この記事を共有 /
階層クラスター分析をエクセルでやる方法【初心者向け】

クラスター分析で統計的に客観的に対象データを似た者同士でグループ分けができます。階層クラスター分析と非階層クラスター分析の違いを知り、エクセル上で実際に階層クラスター分析をやる事により、クラスター分析の概念をよく理解できるようになります。

(動画時間:6:23)

ダウンロード  ←これをクリックして「階層クラスター分析学習用ファイル」をダウンロード出来ます。

クラスター分析とは?

こんにちは、リーンシグマ、ブラックベルトのマイク根上です。

今日はこの動画リクエストからです。

クラスター分析動画リクエスト


「顧客データを分類するため、エクセルを利用した階層クラスター分析の方法を紹介したビデオを作成していただけませんでしょうか。」

Robさん、動画リクエストありがとうございました。

このクラスター分析ができるようになると、今まで情報を似た者同士で分類する時に主観的にやっていた事をそれに付随するデータを使って科学的に分類できるようになるのです。

まずは「クラスター分析」とは何かを理解する必要がありますね。「クラスター(Cluster)」とは「房」とか「かたまり」という意味の英語です。顧客データなど対象が沢山あると対策を考え難いです。

沢山あるデータをこのクラスター分析で似たもの同士に分類することで対象数を減らせますし、そのグループ毎の特性から対策を練って実行し易くできるのです。

過去に新QC7つ道具の親和図について動画を作りました。その考え方に似ていますが、親和図では自分の主観でグループ分けをしますが、このクラスター分析ではデータを基に統計的に客観的にグループ分けができるのです。⇒「親和図法で問題/アイデアを整理しよう。【エクセルテンプレート】」

階層クラスター分析と非階層クラスター分析

この便利なクラスター分析ですが、階層クラスター分析と非階層クラスター分析の二種類があります。どちらも分析の目的と結果は同じなのですが、それを出す計算方法が違います。

今回のリクエストは階層クラスター分析なので後で詳しくエクセル上で説明をしますが、これはデータ数が多すぎると計算が大変になるし、結果を視覚的に見えるのですがそれが不明瞭になってしまいます。

それに対して非階層クラスター分析はデータが多くなっても使えますので実務ではこっちをよく使いますが、それには専用のソフトウェアが必要です。今日は階層クラスター分析を手作業でお見せしますので、クラスター分析の概念をしっかりご理解下さい。

顧客データで階層クラスター分析の考え方

階層クラスター分析サンプルデータ

今回サンプルデータとして顧客の売上データを使います。 各顧客データには購入頻度と平均購入金額の二つの属性データがあります。勿論もっと多くの属性でもこの分析はやれますが、今回は分かり易い様に単純化しています。

まず、この手のデータは散布図を見たいですよね。購入頻度が横軸で、平均購入金額を縦軸に下図の様な分布になっています。似た者同士を分類するとはこの各点の距離の近いもの同士を分類する事に他ならないのです。それを具体的に計算で出すのが階層クラスター分析なのです。

クラスター分析前に散布図作成

階層クラスター分析をエクセル上でやってみる

その作業を実際にやる前に、平面上の二点の距離の計算方法だけは知っておきましょう。それは各軸方向の差の平方和で出せます。例えば二点が(6, 3)と(2, 9)にあったとして、各軸方向の差の平方和は(6-2)の二乗プラス(3-9)の二乗で52です。単位を合わせるために平方根にして距離は7.21になります。

二点の距離の計算方法

全ての点のお互いの距離を計算したのが下図の行列表になるのです。ここで今回のサンプルデータの場合一つ問題があります。平均購入金額の数字が購入頻度の数字に比べて著しく大きく、それが各距離の差にそのまま影響してしまっているのです。

     セルL5 二点の距離を計算する数式

散布図上のクラスター分析行列表

その影響度を同じにしたり逆に前もって重要度が分かっていて意図的に違う影響度にしたい場合はそのデータを補正する必要があるのです。今回の場合は同じにしたいので、各属性データの最大値で各データを割って全てのデータを割合にし100%内に収められます。

      セルH4  データを補正する関数

階層クラスター分析行列表(補正済)

この上図の行列表はこのパーセンテージで計算し直しています。この中で小さいパーセンテージの顧客同士が距離が近い事になるので同じ分類になるのです。

次にこの中から小さい順の顧客の組み合わせを探していきます。それには分かり易く下に同じ書式の表を作りRANK関数を使って小さい順の順位を出しました(下図)。ここからクラスター分析の作業を実際にやってみます。

       セルL18 データをランキングするRANK関数

階層クラスター分析行列表ランキング

まず一番距離が小さいのが顧客GとHです。それをトーナメント戦の表の対戦相手みたいに書きます。次がEとFで同じ様に書きます。

階層クラスター分析2

次がHとIですが、HはGとすでにクラスターになっていますので、Iはそのクラスターに入る事になります。

階層クラスター分析1

次はGとIで、すでにクラスター同士ですね。次がIとJでJをG、H、Iのクラスターに入れます。これを繰返して全ての顧客でやったのが下図になります。

階層クラスター分析完成

これによると各クラスターがはっきり分かりますね。ちなみにこの樹形図をデンドログラムと言います。このクラスターの区分を最初の散布図に当てはめたのが下図です。

散布図上のクラスター分析結果

これは統計的に区分したのですが、視覚的にも納得できますね。今回僕が使ったエクセルファイルを学習用として、また使ったエクセル関数をご確認されたい方は下の動画説明欄からダウンロードできますので、確認してみて下さい。⇒「階層クラスター分析学習用ファイル」をダウンロード」

「こちらの記事も読まれてます。」