Excelのバブルチャートで扱える変数は，原則的には3つです。

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

これらのうち，ユーザーにとって最も評価をしづらい(＝変数の大小を判断しにくい)のは，やはり“円の大小”でしょうか。バブルチャートに関しては，そうした特性も斟酌しつつ，何を見たいか・伝えたいかをふまえた上で，3つ目の変数に1・2番目より優先順位に劣るものを割り振ることが通例かと思います。

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

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

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

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

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

Microsoft公式の解説，

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

によると，

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

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

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

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

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

• Excel VBAでデータ分析, 川上恭子(2015), p.194

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

• EXCELグラフ作成 [ビジテク] データを可視化するノウハウ 2013/2010/2007対応, 早坂清志・きたみあきこ(2015), p.98

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

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

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

との記載も別にあります。これに照らせば，「4つ以上の列」あれ? 基本的には3列で足りるんじゃないの!? なんてバブルチャートそのものに対する筆者自身の理解にも不安を覚えはじめるところです。

結局のところ，この仕様の正確なところを筆者は未だ知るところではありません。ただ最低限，Excelのバブルチャートまわりの挙動には不透明なところが多々あることは無視できないので，実務の上では「意図したものになるのかは作ってみるまで分からない」ことを前提とした方がいろんな意味で良さげです。

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

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

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

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

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

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

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

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

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

それを転写し，

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

などが目立ちます。

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

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

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

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

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

後者はともかく，前者であればExcelでも手数を加えれば実現ができそうです。

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

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

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

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

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

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

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

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

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

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

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

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

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

' バブルチャートの要素を彩色 v18.0104
' 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に掲げたマクロを実行すると，

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

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