BDAstyle

ビジネスデータ分析ツールの作成 with Excel

3つの変数をもとにした “通常の” バブルチャートの作り方と,それに4つ目の変数を組み込みたいときの試行[RFM指標の可視化を例として] with Excel

イントロダクション

Step 0シチュエーションの設定

雑貨店Xがあります。ある日,Xは既存会員の1日の購買行動を概見したいと考えました。

そこでXは開店から閉店までのべ1000件にわたる売上(M)をタイムスタンプ(TS)とともに記録します。その後,会員IDと紐づいたその他の情報―――フリクエンシー(ある期間内での来店回数:F)およびリーセンシー(直近の来店日からの経過日数:R)を各個のレコードに加えたものが下表です。

DL

バブルチャート 元データ

ここでは,この表の可視化の手段としてバブルチャートを選択してみたいと思います。

バブルチャートは3つの変数を扱えるので,上表のとりわけ1つの変数(リーセンシー)を犠牲にしてまずは次のような形式のものを前段で作成します。

3変数のバブルチャート

そして後段ではいずれの変数も犠牲にせずに,4つすべての変数を表現することを試みたいと思います。

4変数のバブルチャート

工程

どの変数をグラフのどの部品に割り当てるか

Step 1

エクセルのバブルチャートで扱える変数は,原則的には3つです。

特徴として,シート上のリスト形式の表をソースにした場合,最も左にある変数(列)がX軸に,左より2つ目の変数がY軸に,そして,同じく3つ目の変数が円の大きさ(デフォルトでは面積)に割り当てられます。

X軸:1列目, Y軸:2列目, バブル:3列目

これらのうち,ユーザーにとって最も評価を加えづらく感じさせる軸と言えば,やはり「円の大きさ」が該当するでしょうか。バブルチャートに関しては,そうした特性も斟酌しつつ,何を見たいか・伝えたいかをふまえた上で,3つ目の変数に1・2番目より優先順位に劣るものを割り振ることが通例かと思います。

この事例でも同様のことを定めます。このグラフでは,

「何時何分に,(会員による)どの程度の金額の購買が発生したか」

を最優先としたいので,3つの変数をグラフ上の各要素へ割り当てるにあたっては,フリクエンシーを(現時点での)優先順位の最下位として下の図のように定めたいと考えます。

X軸:TimeStamp, Y軸:Monetary, バブル:Frequency

ということで,シートの上には,左より「TS」「M」「F」列の順で位置を保つ必要があります。

「見出し」問題

Step 2

エクセルでバブルチャートをつくるにあたって,ある意味もっともややこしいのが,この,“作図の前の範囲の指定”の操作じゃないかと,筆者自身はそんな感を持っています。

Microsoft公式の解説,

データをバブル チャートで表示する―"Office.com"

によると,

注: 選択時に,行または列の見出しは含めない方がいいでしょう。 データと一緒に見出しを選択すると,チャートが正しく出力されないことがあります。

との記述があります。公式で「見出しを含めない」方法を推奨しているので,それに倣って以下範囲を選択して,

「見出しを含まない」選択

バブルチャートを作成するも,

挿入→散布図またはバブルチャートの挿入→バブル

残念なことに当初意図したようなグラフをつくることができません。

意図しない形状のグラフ

この,いわば「行または列の見出しを含めるか」問題については,Excel BI関連の解説書からも2冊を手繰ってみると,たとえば

では含めない方法にて例示されており,

では含める方法で例示されるなど,ケースバイケースで相違がみられ実務においても見解の定まるところではないようです。

また,異なる話題でOffice.comよりの重ねての引用ですが,

注: ワークシートに,4 つ以上の行または列が入力されていることを確認します。 3 つ以下の行または列しか入力されていないデータでバブル チャートを作成すると,チャートにはバブルが正しくプロットされません。

との記載も別にあります。これに照らせば,そもそもバブルチャートに必要な3変数の入力,およびそれらの範囲指定だけじゃ実はマズかったりするんじゃないか!? ……なんてバブルチャートそのものに対する筆者自身の理解にも不安を覚えはじめるところです。

―――結局のところ,この仕様の正確なところは筆者は未だ知るところではありません。ただ最低限,エクセルのバブルチャートまわりの挙動には不透明なところが多々あるし―――であるならば「意図したものになるのかは作ってみるまで分からない」ことを前提とした方がよさげ―――という発想のもとに作図に望めば心の平穏的にもbetterだろうな,といった考えを作図の際には持つようにしています。

バブルチャートの挿入

Step 3

とまれ,公式の解説どおり「見出しを含めない」やり方でダメなら「含める」やり方を試すしかありません。

この事例でも,「含める」やり方でバブルチャートを挿入すると,

「見出しを含む」選択

下図のように意図したグラフをきちんとつくることができました。くどいようですが,(先の引用にも最適解が存在しないことが示唆されているとおり)このケースに関しては「含める」やり方のほうがたまたまハマったというだけのことです。

意図した形状のグラフ=選択がハマった!

ラベルをより直感的な時系列の表現に

Step 4

さて,目的のグラフのひな型を手に入れた時点で,グラフのサイズそのものを任意の大きさに整えます。

そのうえで,今度はX軸を

  1. 8:00 ~ 21:00の範囲で,
  2. 1時間ごとに区切る

形式にかえていこうと思います。

なお頁頭のサンプルデータで処理を追う場合には,以下日付部分は入力せず(時刻のみ入力)すすめてください。

―――ということで,別のシートにでも条件1の下限と上限を書き入れて,

開店時間と閉店時間

それを転写し,

シリアル値に変換

転写した方をシリアル値に直します(「表示形式:標準」)。

シリアル値に変換

条件2に照らし,下限+1時間の同様のデータをさらに追加して,

開店時間より1時間後

これのシリアル値も用意します。

シリアル値に変換

下限およびそれから1時間後の差をとって,

1時間のスパンのシリアル値は?

「軸の書式設定」ウインドウの最小値最大値項目(バージョンごとに名称の違いあり)に,シート上の値を下図のような対応でコピペしていきます。

軸のオプションシへシリアル値をコピペ

そして最後に表示形式を調整します(この場合,秒表示は過剰なので)。

秒単位をカット

クラウド or バブル?

Step 5

X軸の調整を終えところで,あらためて図を確認してみると,グラフについて“バブル”がけっこうな大きさを占めていることがわかります。

これじゃむしろ“クラウド”と呼んだ方が相応しいかも……と思わせないでもありませんので,個々の要素をより強調していきたいと考えます。

バブルサイズが大きい!

ということで,バブルサイズの調整値をデフォルトの設定より適宜下げていきます。

サイズを20に変更

これによって,グラフは下図のようになりました。

この調整に関しても,グラフのサイズと相談しながら過大でもなく・過小でもなくを目指して試行を重ねる必要があります。

よりハッキリした!

要素の色と透明度

Step 6

バブルの色については,「データ系列の書式設定」ウインドウから任意のものに変更が可能です。

ただし,これによって透明度の設定が外れます。この状態では,判別が容易でないので,

カラーの変更→透明度が飛ぶ

任意の透明度を与えてやって修正を加えます。

透明度を与える

完成,そして検証

Step 7

目的としたバブルチャートの完成です。

図を参照しての特徴をあげれば,

  • 既存会員の来店ピークは 11:00,および16:00ごろ
  • 過半の既存会員の購買が10,000円を下回る
  • 10,000円を上回る購買をした既存会員のフリクエンシーランクはほぼ中位以下
  • 閉店近くになると,利用慣れしていない(来店頻度のごく浅い)会員は利用を避ける

などが目立ちます。

完成したバブルチャート

とりわけ頭からの2点については,各個のヒストグラムを以下に用意してみると,その傾向をあらためて補強できるかと思います。

'TimeStamp','Monetary','Frequency'のヒストグラム(比較資料)

第4の変数

Step 8

これ以降は,上の手続きまでに無視してきた第4の変数「リーセンシー」について組み込んでいきたいと思います(分布は下図)。

'Recency'のヒストグラム(比較資料)

Xでは,このリーセンシーは直近の来店時より40日を超えない会員を“active”なクラスタに,40日を超える会員を“passive”なクラスタに分類するといった機械的なルールを運用しています。

つまり,先につくったグラフに第4の変数としてactive/passiveの程度を加えていけたら便利なのに!と思う機会もあるわけです。しかしながら,エクセルでは標準機能としては第4の変数を表現する手立てを持ちません。

エクセルの他では,ヒートマップのようにバブルの色の微妙な階調の変化で表現したものか,あるいはバブルそのものを円グラフやドーナツグラフに置き換えたアウトプットを見かけることがあります。

後者はともかく,前者であればエクセルでも手数を加えれば実現ができそうです。

具体的には,直近の来店日から間もない会員を強い赤,管理上限の120に最も近い会員を強い青としてリーセンシーの表現を試みたいと思います。

「最近」を最も濃い赤,「ずっと以前」を濃い青,それらの中間点が白

すなわち,active/passiveの境界(40)を挟んで,それより左が赤の範囲,右が青の範囲とし,

濃い赤=0, 濃い青=120

色の作成は各個に用意するrgbでおこなうものとします。

濃い赤:rgb(255,0,0)―白:rgb(255,255,255)―濃い青:rgb(0,0,255)

RGB値の作成

Step 9

先の決まり事を受け,rgbを計算するための別表の見出し部分をつくります。

[セルF1より,右方に]class, weight, rgb_red, rgb_green, rgb_blue

ここでは色をつくるにあたり,リーセンシーのとる0~40~120の均等でない区間を1~0~-1の範囲の中で表現したいと思います(以下この1~0~-1の範囲の数字を「ウエイト」と呼びます)。

ウエイト

すなわち,たとえばの数字「10」の場合は 0.75,同じく「100」の場合は-0.75と置き換えることができます。

表の最初の2列において,まずはこのウエイトの計算をおこないます。

ウエイトの計算方法

具体的には,リーセンシーの値を参照して赤か青(または白)のいずれのクラスタかを判別し,

F2 =IF(D2<40, "RED", IF(D2=40, "WHITE", "BLUE"))

[セルF2]

リーセンシーをウエイトに変換します。

G2 =IF(F2="RED", 1-D2/40, IF(F2="WHITE", 0, -((D2-40)/(120-40)) ))

[セルG2]

そしてウエイトを参照して,rgbの各値を調整します。

H2 =IF(G2<=0, 255*(1+G2), 255)
I2 =IF(G2<=0, 255*(1+G2), 255*(1-G2))
J2 =IF(G2<=0, 255, 255*(1-G2))

[セルH2:J2]

マクロ

Step 10

あとは,11つのバブルの色を,透明度を維持したままで先の結果と置き換えていくだけです(下図は最初の要素のみ設定を行ったもの)。

バブルの個別の要素をシート上に計算した値をもとにして着色

もっとも,実際には「置き換えていくだけ」なんて簡単なもの言いで済ませてしまうのは乱暴かも知れません。この例のように,要素の数が多くなればそれだけ手数も膨らんでしまいます。

ということで,より現実的な対応としてはマクロの力を借りるのがbetterかと思います(以下参考例)。

' バブルチャートの要素を彩色 b1801
' bdastyle.net/tools/rfm-analysis/3or4v-bubblechart.html
' by hawcas 2018

Sub Coloring_Elements()

Dim tgt As Range ' 右表
Dim rSize As Long ' R 要素の数
Dim colR() As Long ' rgb値パラメータ
Dim i As Long

Application.ScreenUpdating = False

Set tgt = Range(Range("h1"), Range("h1").End(xlDown))
rSize = Application.WorksheetFunction.Count(tgt)
ReDim colR(rSize, 2)

For i = 0 To rSize - 1
    With tgt.Range("a1")
        colR(i, 0) = .Offset(i + 1, 0).Value
        colR(i, 1) = .Offset(i + 1, 1).Value
        colR(i, 2) = .Offset(i + 1, 2).Value
    End With
Next i

    For i = 0 To rSize - 1
        With ActiveSheet.ChartObjects(1).Chart.SeriesCollection(1).Points(i + 1).Format.Fill
            .ForeColor.RGB = RGB(colR(i, 0), colR(i, 1), colR(i, 2))
            .Transparency = 0.25
        End With
    Next i

Application.ScreenUpdating = True

End Sub

完成

Step 11

たとえば,先のStepに掲げたマクロを実行すると,

「マクロ」ダイアログ

アクティブなシートの1つ目のグラフに対して,シートからrgb値を参照したうえで彩色がなされます(環境により処理に時間を要します)。

バブルチャートに第4の変数の組み込みを完成!

先のヒストグラムでも見たとおり,上図からもactive/passiveに明確な差を見ることができません。ただし,第4の変数を導入したことで,フリクエンシーとの相関の傾向がはじめて浮かぶようになりました。

その他の参照