散布図は、2つの事象がどのくらい一致しているかを見るためのものですが、もうひとつ、複数アイテムを2軸分析して、それぞれの属性がどの位置なのかを表すという使い方もします。
PPM分析
例えば、成長度合いと市場占有率で散布図を書いたPPM分析では、アイテムごとに属性「花形」「金のなる木」「問題児」「負け犬」に分別するという分析手法です。
でもそれは本来の2つの値の比較、相関を見るという散布図の使い方ではないので、Excelではうまく描くことができません。
Excelの散布図でPPM分析する時にできないこと
散布図で縦軸、横軸にプロットすることはできます。
しかし、その点がどのアイテムを表しているかをデータラベルに表示できません。値だけは表示できるのですが・・・。
PPM分析のグラフでアイテム名がグラフ上に表示されないのはグラフとして致命的です。
なんで散布図で系列名が表示されないのか
系列名が表示されない理由は、実は簡単です。
この表で散布図を描く場合、地区名が系列と思ってしまいますが、散布図はB列とC列だけで描くことができ、A列は関係ないのです。
実際の系列名は、散布図は縦軸のタイトルを系列名とするのでセルC1を系列名とします。
Excelグラフの系列の設定の独立性
でも諦めることはありません。
これは散布図を描いた後にデータを追加したとき表示されるダイアログボックスです。
系列名とX（横軸）、Y（縦軸）のセルを任意に設定できるのです。
これを繰り返し、データを追加していけば思い通りの系列名の付いた系列をどんどん作成できます。
系列名ラベル入りの散布図を自動的に描くVBA
しかし、個数が多くなると大変ですね。
そこで、簡単にではありますが、VBAを考えてみました。
A列に系列名、B列に横軸値、C列に縦軸値の一覧表を作成しておいてください。
一気にグラフを描き上げます。
2軸分析の場合、横軸は左が大きい値になることが多いので、横軸は反転しています。
Dim i As Integer
Dim imax As Integer
Dim shname As String’セルA7に空の散布図を描く
Range(“A7”).Select
ActiveSheet.Shapes.AddChart2(240, xlXYScatter).Select
Application.CutCopyMode = False
‘A列のデータ数を数えてグラフに描くデータ数を決める
imax = Application.WorksheetFunction.CountA(Range(“A:A”)) – 1
‘データ取り込み用にシート名を取得
shname = ActiveSheet.Name
‘データ個数分をループ
For i = 1 To imax
‘A列を系列名、B列をX、C列をYとして、データラベルを追加して系列を追加
‘データ系列は系列名だけ
ActiveChart.SeriesCollection.NewSeries
With ActiveChart.FullSeriesCollection(i)
.Name = “=” & shname & “!$A$” & i + 1
.XValues = “=” & shname & “!$B$” & i + 1
.Values = “=” & shname & “!$C$” & i + 1
.ApplyDataLabels
.DataLabels.Select
.HasLeaderLines = False
.HasDataLabels = True
.DataLabels.ShowValue = False
.DataLabels.ShowSeriesName = True
End With
‘データ系列の色
ActiveChart.FullSeriesCollection(i).Select
Selection.Format.Line.Visible = msoTrue
With Selection.Format.Fill
.Visible = msoFalse
.ForeColor.RGB = RGB(255, 0, 0)
.Transparency = 0
.Solid
End With
Next
‘横軸を反転して高い値→低い値へ
ActiveChart.Axes(xlCategory).Select
ActiveChart.Axes(xlCategory).ReversePlotOrder = True
ActiveChart.Axes(xlCategory).Crosses = xlMaximum
End Sub
こんな感じに出来上がります。
まとめ
散布図が実は２つの使い方がされていること、２軸分析に手軽に散布図を使いますけど実はハードルが高かったこと、その原因と対策のVBAを紹介しました。
Excelは結構本来の機能とは違う目的で使うことがよくあります。簡単にできればいいのですが、そうではない時はマクロやVBAを作って対処します。その時に、なぜ、VBAでやらなければならなくなっているのかも考えて作るとその先の何かが見えるかもしれませんね。
コメント