2020/08/30

複数系列のデータを連続的にグラフ化

• １．背景
• ２．概要
• ３．プログラムの流れ
• ４．標準モジュール
• 　４－１．宣言部
• 　４－２．メインプログラム
• 　　４－２－１．変数宣言
• 　　４－２－２．Ｘ軸の先頭セルの位置取得
• 　　４－２－３．データ範囲の位置をモジュール変数に代入
• 　　４－２－４．Ｙ軸の初期値に、どの列を割り当てるか計算
• 　　４－２－５．グラフシート作成
• 　　４－２－６．ダイアログの呼出し
• 　４－３．Ｘ軸Ｙ軸範囲の計算
• 　４－４．グラフ描画
• 　　４－４－１．初期グラフ作成時の問題点
• 　　４－４－２．グラフ移動後のグラフ種類変更時の問題点
• 　　４－４－３．グラフのデータ指定とグラフ種を設定の順序の解決策
• 　　４－４－４．グラフのデータ指定の方法
• ５．フォーム外観
• ６．フォームモジュール
• 　６－１．変数宣言部
• 　６－２．フォーム起動部
• 　　６－２－１．スクロールバーの初期設定の順序
• 　　６－２－２．ChartType値をモジュール変数CTypeに代入するタイミング
• 　　６－２－３．ダイアログの表示
• 　　６－２－４．その他
• 　６－３．スクロールバー変更時
• 　６－４．グラフ種類変更ボタン
• 　６－５．グラフ削除・終了ボタン
• 　６－６．Labelへのデータ書込み
• ７．最後に

１．背景

ある挙動の要因を調べるために、その周辺の様々な挙動を同時に計測し比較する手法があります。
たとえば自動車の運転席の振動が大きい時、その原因を調べるために運転席の振動と一緒に「エンジンや車軸の振動値、またエンジン回転数や車速など」も同時に測り比較することによって、原因を突き止めるようなことです。

今回は、その様な複数のデータ系列があった場合、「グラフのＸ軸を固定しＹ軸を連続的に変更することで相関性を比較確認できる」ものを紹介します。
実際には固有振動数などが複雑にからむので「そんなに単純では無い！」と怒られそうですが、傾向を掴むくらいのレベルでしたら役に立つかもしれません。

２．概要

測定したデータは、通常は時系列的な形をしていると思います。今回は、例えば図２－１のようにＢ列に時刻データがあり、Ｃ～Ｇ列に５系列のデータが並んでいるものを想定しています。
なお、時間軸が無いデータでも問題ありませんが「データ列は全て繋がっている」ことが必要です。

図２－１

データを比較するために、まず基準となるデータ列（Ｘ軸）を選びます。データシート上の「グラフ化」ボタンをクリックすると、図２－２のようにInputBoxが現れますので「基準列（グラフのＸ軸）の先頭セル」をクリックします。

図２－２

すると新たにグラフシートが作成され「基準列（Ｘ軸）」と「他の列（Ｙ軸）」の間の散布図が表示されます。（図２－３）
「他の列」は複数あります（図２－１では５列）ので、ダイアログのスクロールバーを移動することで比較対象列が移動します。
（スクロールバーの位置によっては「同じデータ列同士の比較」になり、グラフ上は45度の直線上に並びます。）
尚ダイアログ上には、グラフのＸ軸Ｙ軸のデータ範囲がアドレス形式で表示されます。また両データの相関係数も表示されます。


図２－３

なお、グラフ種類を変更する際にはダイアログの「Ｇ種類変更」ボタンをクリックすると、図２－４のようにExcel組み込みダイアログが現れ、グラフ種類の変更が可能です。

図２－４

終了する場合は、ダイアログの「Ｇ削除 終了」ボタンをクリックすると、グラフシートが削除されマクロ終了します。
もしグラフシートをそのままにしたいのであれば、ダイアログ右上の×印で終了して下さい。

３．プログラムの流れ

全体の流れは図３－１のようになっています。
プログラムは暫定的にシート上の「グラフ化」ボタンで起動させていますが、実際にはプログラムをアドイン等にして「データとは異なる場所から起動」させる必要があります。
アドインへの登録方法については、下記を参照願います。ファイル名が異なるだけで手順は全く同じです。
・「セルの罫線を矢印キーで引く」
・「西暦・和暦対照表」


図３－１

初めに、ユーザー側にＸ軸（グラフの固定軸）の先頭セルを指示してもらい、そのセルを手掛かりにデータ範囲を把握します。
次に初期のＹ軸列を仮決めし、グラフシートを作成します。この時はグラフ内には表示するデータは入っていません。
その後、グラフ化する際のＸ軸Ｙ軸のセル範囲を計算し、新規作成したグラフシートに表示するデータを指定します。この段階で初期のグラフは完成します。

グラフが完成した後でダイアログを表示させ、ユーザーがスクロールバーを移動すると、グラフの軸データを「変更後のＸ軸Ｙ軸」に入れ替えることで、別の組み合わせのグラフを表示することが出来ます。
尚、図３－１には記載していませんが、グラフ種類も変更可能です。グラフ種類の値は共通変数として保持し、グラフ軸変更の際にはグラフ種類も共に変更指示を出しています。

４．標準モジュール

４－１．宣言部

標準モジュールの宣言部では図４－１のように、プロジェクト（標準モジュール＋フォームモジュール）内で共通して使用するPublic変数１つと標準モジュール内で使用する変数６つを宣言しています。

1. '==========　⇩①　宣言部　====================
2. Public myChart As Object　　　 '作成したグラフオブジェクト
3. Dim Xstart As Range　　　　　 'X軸列の先頭セル
4. Dim StartCol As Long　　　　　 'データ域の先頭列位置
5. Dim Xcol As Long　　　　　　　 'X軸の列位置
6. Dim EndCol As Long　　　　　 'データ域の最終列位置
7. Dim StartRow As Long　　　　 'データ域の先頭行位置
8. Dim EndRow As Long　　　　　 'データ域の最終行位置

図４－１

それぞれの変数は、図４－２の部位を指しています。

図４－２

４－２．メインプログラム

今回のサンプルファイルのワークシート上の「グラフ化」ボタンをクリックすると呼び出されるプロシージャが図４－３です。

9. '==========　⇩②　メインプログラム　====================
10. Sub MakeChart()
11. 　Dim AxisX As Range　　　　　 'グラフのX軸のデータ範囲
12. 　Dim AxisY As Range　　　　　 'グラフのY軸のデータ範囲
13. 　Dim CurrentCol As Long　　　 'グラフのY軸のデータ列位置
15. 　On Error Resume Next
16. 　　Set Xstart = Application.InputBox("X軸データの先頭セルを指定して下さい", Type:=8)
17. 　　If Not Err.Number = 0 Then Exit Sub
18. 　On Error GoTo 0
20. 　With Xstart
21. 　　StartCol = .CurrentRegion.Column
22. 　　EndCol = StartCol + .CurrentRegion.Columns.Count - 1
23. 　　Xcol = .Column
24. 　　StartRow = .Row
25. 　　EndRow = .End(xlDown).Row
26. 　End With
28. '　//Y軸の初期値に、どの列を割り当てるかを決める
29. 　If StartCol = EndCol Then　　　　　　　 'データが１列しか無い場合
30. 　　CurrentCol = Xcol
31. 　Else　　　　　　　　　　　　　　　　　 'データが複数列ある場合
32. 　　If StartCol = Xcol Then　　　　　　　 'データの先頭列がX軸の場合
33. 　　　CurrentCol = StartCol + 1
34. 　　Else　　　　　　　　　　　　　　　　　 'X軸がデータ先頭列以外の場合
35. 　　　CurrentCol = StartCol
36. 　　End If
37. 　End If
39. 　Set myChart = Charts.Add
40. 　Call myAxis(AxisX, AxisY, CurrentCol)
41. 　Call DrawChart(AxisX, AxisY, xlXYScatter)
43. 　Call UserForm1.Start(StartCol, EndCol, CurrentCol, xlXYScatter)
44. End Sub

図４－３

４－２－１．変数宣言

１１～１３行目は、MakeChartプロシージャ内で使用する変数です。
「AxisX」はＸ軸のデータ範囲です。StartRow行からでは無く、データの１つ上のタイトル（と思われるセル）を含んでいます。
「AxisY」はＹ軸のデータ範囲で、Ｘ軸と同様にタイトルを含んでいます。
「CurrentCol」は「AxisYを作るためのデータ」です。グラフを新規作成した段階では、Ｘ軸の場所により最適な位置に計算で求めています（２９～３７行目）が、それ以降はダイアログのスクロールバーの値に同期します。

４－２－２．Ｘ軸の先頭セルの位置取得

１６行目は、InputBoxメソッドを使っています。
InputBox関数と異なりInputBoxメソッドは、「引数Typeに8を指定」することで「セル範囲」を取得できます。この機能を利用して「Ｘ軸の先頭データの位置」を取得します。

InputBoxにセル範囲を入れれば（＝セルをクリックすれば）、InputBoxメソッドはRange型の値を戻します。その戻り値を変数Xstart（３行目でRange型として宣言）で受け取りますが、もしキャンセルボタンをクリックしたり、ダイアログ右上の×印をクリックしたりすると「False」を戻します。
その際はRange型の変数では受けられずにエラーが発生するため、１５行目で「On Error Resume Next」を設けて「エラーが発生しても次に進む」ようにし、１７行目でエラー番号を調べてゼロで無い（＝エラーが出ている）時は、マクロ終了しています。
１８行目の「On Error GoTo 0」では「Resume Next」機能をキャンセルさせ、１６行目以外でエラーが発生した場合にはキチンとエラー終了させるようにしています。

尚、このInputBoxメソッド及びエラー処理については、「サンプリング周期が異なるデータの補間法」も参照下さい。
また、ユーザーが「データで無いセルを選択」すると、１７行目では引っ掛からずに別な場所でエラーが発生します。ユーザーのミスをどこまでフォローするかは、ユーザーの種類（仲間なのか、違う部署なのか、お客様なのか　等）で異なると思いますので、それに合わせて緩くするのかガチガチにするのかを決めてください。但し取扱説明書に記載するくらいは必須と思います。

４－２－３．データ範囲の位置をモジュール変数に代入

２０～２６行目では、図４－１で宣言したモジュール内変数に値を代入しています。
ユーザーが指定したＸ軸データの先頭セル（＝Xstart）を元にして、図４－２の各値を計算・代入しています。

４－２－４．Ｙ軸の初期値に、どの列を割り当てるか計算

２９～３７行目は、グラフを新規作成した初期段階でのＹ軸をどの列にするかを決め、その列位置をCurrentColに代入します。
Ｙ軸は「Ｘ軸と重ならない」ように選択しますが、「データ列が１列しか無い」場合には同じ列同士にしています。

まず２９行目では、データ列が１列しか無い（＝StartColとEndColが一緒）場合には、Ｙ軸（＝CurrentCol）にＸ軸の列位置（＝Xcol）を代入します。
フルイに掛けられた３１行目以降は、データ列が複数存在することになります。
そこで３２行目ではＸ軸（＝Xcol）としてデータ範囲の先頭列（＝StartCol)を選択したかを調べます。その場合には、先頭列の隣の列（＝StartCol + 1 ）をＹ軸にします。
Ｘ軸が先頭列では無い場合は、先頭列をＹ軸にします。

４－２－５．グラフシート作成

３９～４１行目では、新たなグラフシートを作り、散布図を作成しています。
３９行目の「Charts.Add」でグラフシートを作ります。
さて、グラフシートを作成するには「Charts.Add」と共に「Charts.Add2」というのがあります。Add2はExcel2016で追加されたメソッドの様で、「株式型や地理型のサブフィールドを並べ替えの対象に指定できる」ようです。
ちなみに「Charts.Add」と「Charts.Add2」とで作成したグラフの違いを図４－４に示します。比較してみると枠線の有無や目盛線の太さ等が異なる様ですが、「どうしてもAdd2」という人以外は旧バージョンでも使用できるAddを使用した方が良いと思います。


図４－４

４０行目では、Ｘ軸・Ｙ軸のセル範囲（Range型）を計算しています。
詳細は図４－６で説明しますが、myAxisプロシージャにはＹ軸の列位置「CurrentCol」を渡し、戻り値相当としてＸ軸セル範囲「AxisX」, Ｙ軸セル範囲「AxisY」を得ます。

４１行目では、４０行目で得た「AxisX」「AxisY」、および「xlXYScatter（散布図を表す定数）」をDrawChartプロシージャ（図４－７）に与え、散布図を完成させています。
尚グラフには図４－５のように多くの種類がありますが、とりあえず初期の種類としては散布図を選びました。

グラフの種類一覧（１）

定数	値	意味
xl3DArea	-4098	3 D エリア
xl3DAreaStacked	78	3D 積み上げ面
xl3DAreaStacked100	79	100% 積み上げ面
xl3DBarClustered	60	3D 集合横棒
xl3DBarStacked	61	3D 積み上げ横棒
xl3DBarStacked100	62	3D 100% 積み上げ横棒
xl3DColumn	-4100	3D 縦棒
xl3DColumnClustered	54	3D 集合縦棒
xl3DColumnStacked	55	3D 積み上げ縦棒
xl3DColumnStacked100	56	3D 100% 積み上げ縦棒
xl3DLine	-4101	3D の線
xl3DPie	-4102	3D 円グラフ
xl3DPieExploded	70	分割 3-d 円
xlArea	1	分野
xlAreaStacked	76	積み上げ面グラフ
xlAreaStacked100	77	100% 積み上げ面
xlBarClustered	57	集合横棒
xlBarOfPie	71	バーの円
xlBarStacked	58	積み上げ横棒
xlBarStacked100	59	100% 積み上げ横棒
xlBubble	15	バブル
xlBubble3DEffect	87	3-d 効果付きバブル
xlColumnClustered	51	集合縦棒
xlColumnStacked	52	積み上げ縦棒グラフ
xlColumnStacked100	53	100% 積み上げ縦棒
xlConeBarClustered	102	集合円錐型横棒
xlConeBarStacked	103	積み上げ円錐型横棒
xlConeBarStacked100	104	100% 積み上げ円錐型横棒を横
xlConeCol	105	3-d 円錐型縦棒
xlConeColClustered	99	集合円錐型縦棒
xlConeColStacked	100	積み上げ円錐型縦棒
xlConeColStacked100	101	100% 積み上げ円錐型縦棒
xlCylinderBarClustered	95	集合円柱横棒
xlCylinderBarStacked	96	積み上げ円柱型横棒
xlCylinderBarStacked100	97	100% 積み上げ円柱型横棒を横
xlCylinderCol	98	3D 円柱型縦棒
xlCylinderColClustered	92	集合円錐型縦棒

グラフの種類一覧（２）

定数	値	意味
xlCylinderColStacked	93	積み上げ円錐型縦棒
xlCylinderColStacked100	94	100% 積み上げ円柱縦棒
xlDoughnut	-4120	ドーナツ
xlDoughnutExploded	80	分割ドーナツ グラフ
xlLine	4	Line
xlLineMarkers	65	マーカー付き折れ線
xlLineMarkersStacked	66	マーカー付き積み上げ折れ線
xlLineMarkersStacked100	67	100% 積み上げ折れ線マーカー付き
xlLineStacked	63	積み上げ折れ線
xlLineStacked100	64	100% 積み上げ折れ線
xlPie	5	円
xlPieExploded	69	分割円グラフ
xlPieOfPie	68	補助円グラフ付き
xlPyramidBarClustered	109	集合ピラミッド型横棒
xlPyramidBarStacked	110	積み上げピラミッド型横棒
xlPyramidBarStacked100	111	100% 積み上げピラミッド型横棒を横
xlPyramidCol	112	3-d ピラミッド型縦棒
xlPyramidColClustered	106	集合ピラミッド型縦棒
xlPyramidColStacked	107	積み上げピラミッド型縦棒
xlPyramidColStacked100	108	100% 積み上げピラミッド型縦棒
xlRadar	-4151	レーダー
xlRadarFilled	82	塗りつぶしレーダー
xlRadarMarkers	81	データ マーカー付きレーダー
xlStockHLC	88	高値-安値-終値
xlStockOHLC	89	開く-高値-安値-終値
xlStockVHLC	90	ボリューム-高-安値-終値
xlStockVOHLC	91	ボリュームに開く-高-安値-終値
xlSurface	83	3D 表面
xlSurfaceTopView	85	表面 (トップ ビュー)
xlSurfaceTopViewWireframe	86	表面 (トップ ビューがワイヤ フレーム)
xlSurfaceWireframe	84	3 D サーフェス (ワイヤー フレーム)
xlXYScatter	-4169	散布図
xlXYScatterLines	74	折れ線付き散布図
xlXYScatterLinesNoMarkers	75	行とデータ マーカーなしの散布図
xlXYScatterSmooth	72	平滑線付き散布図
xlXYScatterSmoothNoMarkers	73	データ マーカーなし平滑線付き散布図

図４－５

４－２－６．ダイアログの呼出し

４３行目では、UserForm１のStartプロシージャ（図６－２）を呼び出し、ダイアログを起動させています。
引数として「StartCol」「EndCol」「CurrentCol」「xlXYScatter（初期のグラフ種）」の４つを渡していますが、初期グラフ種を除いた３つは標準モジュール側で使用しているモジュール変数です。これと同じ変数値をフォーム側のモジュール変数として使用するために引数で渡しています。

このような標準モジュール側とフォームモジュール側とを結ぶ引数渡しは、図４－１の変数宣言時に「Dimでは無くPublicとして変数宣言」をすれば必要無くなるのですが、変数の可視範囲を極力狭くするために、このような手法を取りました。
今回の機能だけであればPublic変数を使用しても悪い影響は無さそうですが、もし別機能を追加するような場合には注意しなければならない項目が増える事になってしまいます。

４－３．Ｘ軸Ｙ軸範囲の計算

図４－３の４０行目、図６－５の７５行目から呼び出されるmyAxisプロシージャが図４－６です。
引数として「AxisX」「AxisY」「CurrentCol」の３つが渡されている様に見えますが、実際に受け取っているのは「CurrentCol」のみで、プロシージャ内での計算結果として「AxisX」「AxisY」を戻しています。

45. '==========　⇩③　Ｘ軸Ｙ軸範囲の計算　====================
46. Sub myAxis(AxisX As Range, AxisY As Range, CurrentCol As Long)
47. 　With Xstart.Parent
48. 　　Set AxisX = Range(.Cells(StartRow - 1, Xcol), .Cells(EndRow, Xcol))
49. 　　Set AxisY = Range(.Cells(StartRow - 1, CurrentCol), .Cells(EndRow, CurrentCol))
50. 　End With
51. End Sub

図４－６

４７行目の「Xstart.Parent」は、ユーザーが指定したＸ軸データの先頭セル（＝Xstart）の「親（＝Parent）」という意味ですので、「データが存在するワークシート（サンプルファイルではSheet1）」という意味になります。
このワークシート上のセル範囲を４８～４９行目で計算します。

４８行目は、ユーザーが指定したＸ軸のセル範囲です。この式の中で使われている変数（StartRow、EndRow、Xcol）は、マクロが動いている間は不変の値ですので、毎回計算するのも少し無駄な気がします。しかしＸ軸とＹ軸を異なる場所から引いてくると分かり難くなりますので、このような形にしました。

４９行目は、引数「CurrentCol」で指定された列位置のＹ軸セル範囲です。このAxisY値は、毎回列位置が変更になります。

Ｘ軸・Ｙ軸ともに先頭行を「StartRow - 1」としているのは、データの１つ上の行に「タイトル行があると仮定」して１つ上の行までをセル範囲としています。タイトル行までをセル範囲に含めているために、グラフ上部にタイトルが表示されることになります。
もしタイトル行が無い場合でもエラーは発生しませんが、グラフ上部のタイトル部は空欄になります。そのため、ダイアログ内にＸ軸Ｙ軸のセル範囲のアドレスを表示することにしました。

計算した「AxisX」「AxisY」の値は、引数の形で呼び出し元に戻されますので、呼び出し元では「AxisX」「AxisY」を戻り値と同じように、その後の計算に使っていきます。

４－４．グラフ描画

図４－３の４１行目、図６－５の７６行目から呼び出されるDrawChartプロシージャが図４－７です。
引数として「AxisX」「AxisY」「CType」の３つを受け取り、グラフ表示に必要なデータを書き込みます。

52. '==========　⇩④　グラフ描画　====================
53. Sub DrawChart(AxisX As Range, AxisY As Range, CType As XlChartType)
54. 　With myChart
55. 　　.ChartType = CType
56. 　　.SetSourceData　Range(AxisX.Address(External:=True) & ", " & AxisY.Address(External:=True))
57. 　　.ChartType = CType
58. 　End With
59. End Sub

図４－７

５４行目は、既に作成済みのグラフシート（＝Public変数のmyChart）に対して処理を行うため「With myChart」とします。
５５・５７行目は「myChart.ChartType = CType」と同一コードです。これはミスでは無く必要なコードです。
実は今まで「グラフのデータ範囲とグラフ種類の設定は、どの順番でも同じ」と考えていたのですが、以下の様な問題点が分かり小細工が必要となりました。

４－４－１．初期グラフ作成時の問題点

まず初期グラフを作成する場合ですが、ユーザーが時刻データを選択した場合に、どのようにグラフが生成されていくのかを調べてみました。（図４－８）
まず、図４－３の３９行目で「Chrts.Add」をした段階では、図４－８の一番左側の様に「全データを使った棒グラフ」になります。

図４－８

次にグラフのデータ指定とグラフ種を設定する必要がありますが、その順番の組合せとしては「SetSouceData → ChartType」と「ChartType → SetSouceData」の２通りがあります。
実行した結果ですが、「SetSouceData → ChartType」の場合は図４－８の上側のように、「時刻データとY軸データの2系列のグラフ」になる現象が発生しました。ちなみに「ChartType → SetSouceData」の場合は図４－８の下側のように「正常な散布図」になりました。
つまり「時間軸をＸ軸に設定」した場合には「ChartType → SetSouceData」の順で設定する必要があることが分かります。

なお、この「意図しないグラフ」になるのは「初回表示時」だけで、表示後にダイアログ上のスクロールバーを移動させて再表示させると正常表示になりますし、またＸ軸として時刻データ以外を選択した時も初回から正常なグラフになります。

４－４－２．グラフ移動後のグラフ種類変更時の問題点

一方で次のような場面では逆の現象が発生しました。
初期の散布図が表示された後、「ダイアログのスクロールバーでＹ軸を移動した先でグラフ種類を変更し、スクロールバーでＹ軸を元に戻す」という場合です。
図４－９のように、「ChartType → SetSouceData」の順では、グラフの「点も線も表示されない」状態になるのです。逆の「SetSouceData → ChartType」の順だと正常な表示となります。

図４－９

尚この現象も、スクロールバーを行ったり来たりさせて複数回表示させると、やはり正常なグラフになります。

４－４－３．グラフのデータ指定とグラフ種を設定の順序の解決策

データ範囲とグラフ種の設定順序によりグラフ表示に問題が出る原因については、よく分かりません。但し、グラフ種変更後に、一度も表示させていないＹ軸グラフに移動する際には正常に表示することから、「グラフのデータ範囲とグラフ種」をExcelがどこかにキャッシュとして記憶しているのでは、と想像しています。

不具合の現象は１回限りの現象です。しかし、１回でも想定外のグラフになるのは「エラー」ですので、グラフ種を設定するコード「myChart.ChartType = CType」を、「myChart.SetSourceData」の前後である５５行目と５７行目の両方に置いて、想定外グラフにならないようにしています。
つまり、５５行目は「新規に時系列グラフを作成した時」用、５７行目は「グラフ種類変更時」用です。

なお、ChrtTypeを２つ置くことによるデメリット（不具合）は今のところ見つかっていませんが、複雑なグラフ設定をする場合には注意しておいた方が良いかもしれません。

また、第三引数を「CType As XlChartType」と宣言してありますが、「CType As Long」と宣言しても同じです。今回のマクロ内では３箇所で「As XlChartType」を宣言していますが、「As Long とゴッチャ」にしても問題なく動くようです。しかし、どちらかに統一しておいた方が良いとは思います。

４－４－４．グラフのデータ指定の方法

グラフ種設定に挟まれている５６行目の「myChart.SetSourceData」の引数について説明します。
.SetSourceDataの引数にはグラフの元データのセル範囲を指定します。例えば今回で言えばＸ軸のセル範囲は「AxisX」、Ｙ軸のセル範囲は「AxisY」ですので、「Union(AxisX,AxisY)」を引数にすれば良いはずです。
たしかに、Ｘ軸とＹ軸が異なる列の場合にはそれで問題ありません。しかしＸ軸とＹ軸が同じ場合にはどうでしょうか。

Ｘ軸とＹ軸が同じ場合には、「Union(AxisX,AxisY)」の計算結果は「AxisX」になります（同じセル範囲ですので当然です）。
ではこの場合にグラフの表示としてはどうなるでしょうか。やってみるとX軸（横軸）にはデータの個数が並び、Y軸に値が並ぶはずです。しかし「Ｘ軸とＹ軸が同じ散布図」としてユーザーが期待するのは、図４－１０の左右どちらでしょうか。

図４－１０

私であれば、迷わず「右の45度の直線に並んだグラフ」を選びます。今回ダイアログ中には相関係数を表示していますが、その値が「1.000」になっていますので、尚更です。

そこで、図４－１０の右の様なグラフを作るにはどうするか　です。通常２つの列をセル範囲（Area）として引数にする場合は、例えば「Range("A1:A10,C1:C10")」のように記載します。
なお間違えて「Range("A1:A10","C1:C10")」とするとＡ列からＣ列までの連続範囲になってしまいますので注意が必要です。

Ｘ軸Ｙ軸のセル範囲は「AxisX」「AxisY」ですので、各々のセル範囲を文字列にして「,（カンマ）」でつなげば良いのですが、
「Range(AxisX.Address & ", " & AxisY.Address)」としてしまうと、今回の場合はエラーが発生します。
なぜかと言うと「.Address」で得られる文字列「（例）"A1:A10"」は「アクティブなワークシートでの "A1:A10" セル範囲」を指すことになりますが、今回実際にアクティブになっているのは「グラフシート」だからです。

そのため「シート名までを含めたアドレス」にする必要があるため、「.Address(External:=True)」とAddress にExternalパラメータを指定することで、「ワークブック、ワークシート名を含む外部参照として」セル範囲文字列を得る必要があります。
今回はブック名までは不要なのですが、残念ながらシート名だけというパラメータはありません。

この方法を「AxisX」「AxisY」の両方に使い、得られた文字列を「,（カンマ）」でつないで、以下の様な形にします。
　Range(AxisX.Address(External:=True) & ", " & AxisY.Address(External:=True))　
これを５６行目の「.SetSourceData」の引数としています。

５．フォーム外観

Ｙ軸の切り替え等を行うためのフォームを図５－１のように作成します。

図５－１

ボタン表面、およびLabelの「Ｘ軸」「Ｙ軸」「相関」は、あらかじめ文字を入れておきます。
データの行数・列数が非常に多い場合はデータのアドレスが長くなりますので、Label１～２は長めにとってあります。

６．フォームモジュール

６－１．変数宣言部

フォームモジュールの先頭に、モジュール変数（UserForm１内での共通変数）を図６－１のように宣言します。

60. '==========　⇩⑤　フォーム内の変数宣言部　====================
61. Dim AxisX As Range　　　　　　 'グラフのX軸列範囲
62. Dim AxisY As Range　　　　　　 'グラフのY軸範囲
63. Dim CType As XlChartType　　　 'グラフの種類

図６－１

「AxisX」「AxisY」は、それぞれＸ軸・Ｙ軸のデータ範囲で、グラフのセル範囲として設定することを想定して「タイトル行を含む」範囲としています。また「CType」はグラフのグラフの種類を表します。

６－２．フォーム起動部

図４－３の４３行目から呼び出されるプロシージャが、図６－２のStartプロシージャです。引数として「StartCol」「EndCol」「CurrentCol」「CT（グラフ種類の値）」を受け取ります。

64. '==========　⇩⑥　フォーム起動部　====================
65. Public Sub Start(StartCol As Long, EndCol As Long, CurrentCol As Long, CT As XlChartType)
66. 　CType = CT
67. 　Me.ScrollBar1.Max = EndCol
68. 　Me.ScrollBar1.Value = CurrentCol
69. 　Me.ScrollBar1.Min = StartCol
71. 　Me.Show 0
72. End Sub

図６－２

６－２－１．スクロールバーの初期設定の順序

６７～６９行目を先に説明します。
Startプロシージャが受け取った引数の内、「StartCol」「EndCol」「CurrentCol」は、グラフのＹ軸の移動のための「スクロールバーのMax・Min・Value値」の設定に使用します。
スクロールバーのMax値にはデータ最終列であるEndCol値を、Value値には初期散布図のＹ軸であるCurrentCol値を、Min値には、データ先頭列であるStartCol値を順に代入します

このスクロールバーの設定順序「Max → Value → Min」には意味があります。図６－３は、Max,Min,Valueの設定順序を図にしたものです。

図６－３

スクロールバーのValue値が変化すると、Changeイベントが発生します。スクロールバーを配置するのであれば、当然ながらその操作をプログラムに結び付けるべくChangeイベントプロシージャを使用しているはずです。
そのChangeイベントは、フォームの起動中（今回で言えば、Startプロシージャを動かしている時も）にも発生しますので、スクロールバー設定中にValue値が動く時にも発生し、イベントプロシージャを呼び出してしまいます。
そして図６－３で分かる通り「設定する順序により、Value値が変わる回数（＝イベントプロシージャを呼び出す回数）が異なる」のです。

Changeイベントプロシージャがどんな役割を持っているかはそれぞれですが、今回の場合は「グラフのデータ範囲変更とグラフ種設定」です。何回も「グラフのデータ範囲変更と・・・」を実行するとそれだけ処理に時間が掛かりますし、設定値が毎回異なった場合にはグラフがチラチラする事にもつながります。
ですので、Value値が変わる回数がもっとも少ない「Max → Value → Min」の順番で設定しています。

６－２－２．ChartType値をモジュール変数CTypeに代入するタイミング

Value値が変わる回数が少ないと言っても、最低１回は発生（今回の場合、Value値＝Ｙ軸範囲の列位置 のため）します。そのValue値変更が発生（＝Changeイベント発生）するのは６８行目を実行した時です。

Changeイベントが発生すると、グラフの「データ範囲変更」及び「グラフ種設定」が行われます。「データ範囲変更」の方はスクロールバーのValue値を元に計算されますので問題無いのですが、「グラフ種設定」の方はモジュール変数CTypeの値が必要になります。
つまり、６８行目より前に「モジュール変数CTypeへの値代入」が必要なので、６６行目で「CType = CT」の設定をしています。（６８行目以降に「CType = CT」を置くと、グラフ設定時にCType＝0となるのでエラーが発生します。）

６－２－３．ダイアログの表示

起動前の準備が出来たら、７１行目で UserForm１（Me でフォームを表す事が可能です）を表示します。引数にゼロ（vbModeless）を指定していますので、Excelのシート部分の操作も可能です。引数に１（既定値なので、引数を指定しないと１を指定した事になる）を指定すると、フォーム以外の操作は出来なくなります。

６－２－４．その他

スクロールバーの設定を行う際 Value値が変化しなければ、不要なChangeイベントが発生しないと説明しました。Value値をゼロにした分 Min値とMax値を調整し、実際にスクロールバーを動かした際もValue値を±すれば可能です。
しかし、Changeイベントが発生しないままフォームが起動するとデメリットもあります。

今回フォーム上のLabel１～３にデータ（Ｘ軸Ｙ軸のアドレス、相関係数）を書き込んでいます（LabelMakeプロシージャ（図６－１１））が、この操作はChangeイベントプロシージャから呼び出しています。
起動時にChangeイベントが発生しないとなると、起動直後のフォームのLabelにはデータが書き込まれていない事になるので、別途LabelMakeプロシージャを呼び出す必要が出てきます。
その場合には例えば６９行目の後ろに、以下の２行を追加する等の対応が必要となります。

• Call myAxis(AxisX, AxisY, ScrollBar1.Value) 　　 'スクロールバーのValue値を渡し、AxisX, AxisY値を得る
• Call LabelMake(AxisX, AxisY) 　　　　　　　　　　 'AxisX, AxisY値からLabelデータを計算し表示する

図６－４

６－３．スクロールバー変更時

グラフのＹ軸を変更しようとして、スクロールバーを動かした時に発生するChangeイベントが図６－５になります。

73. '==========　⇩⑦　スクロールバー変更時　====================
74. Private Sub ScrollBar1_Change()
75. 　Call myAxis(AxisX, AxisY, ScrollBar1.Value)
76. 　Call DrawChart(AxisX, AxisY, CType)
77. 　Call LabelMake(AxisX, AxisY)
78. End Sub

図６－５

７５行目で、変更されたスクロールバーのValue値をmyAxis（図４－６）に渡し、Ｘ軸Ｙ軸のセル範囲である AxisX, AxisY を得ます。
７６行目で、その AxisX, AxisY と、グラフ種類値のCTypeをDrawChart（図４－７）に渡し、グラフのＸ軸Ｙ軸とグラフ種を変更します。
７７行目で、LabelMake（図６－１１）に AxisX, AxisY を渡し、ダイアログ上のLabel１～３に表示グラフのアドレスと相関係数を表示させます。

以上の動作はChangeイベントプロシージャに書かれているので、スクロールバーの「スクロールボックス（図６－６参照）」をクリックして動かしただけでは反応しません。

図６－６

それは図６－７ のように、スクロールバーをどのように操作させるかで、発生するイベントが異なるからです。
もし、スクロールボックスを移動させただけでもグラフのＸ軸Ｙ軸が変わるようにしたいのであれば、Scrollイベントプロシージャを作成し、Changeイベントプロシージャと同様のコードを記入する必要があります。

スクロールバーの操作と発生するイベントの関係

クリックする場所	操作	動く量・示す値	発生イベント
スクロール矢印	クリック	SmallChangeプロパティの量	Changeイベント
スクロールボックスと スクロール矢印の間の領域（レール）	クリック	LargeChangeプロパティの量	Changeイベント
スクロールボックス	クリックし移動中	スクロールボックスの位置のValue値 (動く量はValue=1ずつ)	Scrollイベント
	移動後クリックを離す	クリックを離した場所のValue値	Changeイベント

図６－７

６－４．グラフ種類変更ボタン

ダイアログ内の「Ｇ種類変更」ボタンをクリックすることで動作するプロシージャが図６－８です。

79. '==========　⇩⑧　グラフ種類変更ボタン　====================
80. Private Sub CommandButton1_Click()
81. 　myChart.Activate
82. 　Application.Dialogs(xlDialogChartType).Show
83. 　CType = myChart.ChartType
84. End Sub

図６－８

８１行目は、グラフ種を変更する対象の「グラフシート上のグラフ」をアクティブにしています。
マクロ操作のみの場合にはグラフはアクティブな状態を続けているのですが、今回UserForm１はモードレスで表示させている（図６－２の７１行目）ので、ユーザーがシート側を操作することが可能です。
もしグラフがアクティブになっていない状態で８２行目のコードを実行すると、グラフ種変更ダイアログが表示されません。特にエラーも出ないようですので、ユーザーにしてみれば「グラフ種変更ボタンをクリックしたのに、反応しない」ことになります。

８２行目は、Excelの組み込みダイアログを表示させています。非常に多くのダイアログがあり、「グラフ種変更」だけでも、「xlDialogMainChart」「xlDialogMainChartType」「xlDialogFormatCharttype」「xlDialogChartType」の４種の定数が該当します。（違いは良く分かりません。見た目は、どれも同じダイアログの様です）
今回は一番無難そうな「xlDialogChartType」を使用しています。

８３行目は、変更したグラフ種類の値をモジュール変数CTypeに代入しています。
なお、グラフ種を変更せずにキャンセルボタンをクリックしても８３行目は実行され、CType値は更新されます。

６－５．グラフ削除・終了ボタン

ダイアログ内の「Ｇ削除・終了」ボタンをクリックすることで動作するプロシージャが図６－９です。

85. '==========　⇩⑨　グラフ削除・終了ボタン　====================
86. Private Sub CommandButton2_Click()
87. 　Application.DisplayAlerts = False
88. 　　myChart.Delete
89. 　Application.DisplayAlerts = True
90. 　Me.Hide
91. End Sub

図６－９

８８行目はグラフシートを削除してます。削除の対象は「図４－３の３９行目で作成したグラフシート」ですので、ダイアログ右上の×印で終了した時に残されたグラフシートはそのままです。
普通に削除しようとすると、図６－１０の様なアラートが発生し、OKボタンをクリックしないと削除できません。

図６－１０

ですので、そのアラートを出さない為に、８７行目で「Application.DisplayAlerts = False」とアラートを非表示にしてからDeleteを実行しています。
削除後は「Application.DisplayAlerts = True」でアラート表示を元の状態に戻しています。

グラフシートを削除後、９０行目でダイアログを消しています。「Me.Hide」の代わりに「Unload Me」でも問題ありません。

６－６．Labelへのデータ書込み

図６－５の７７行目から呼び出される LabelMakeプロシージャが図６－１１です。引数として、Ｘ軸Ｙ軸のセル範囲を受け取ります。

92. '==========　⇩⑩　Label１～３へのデータ書込み　====================
93. Sub LabelMake(AxisX As Range, AxisY As Range)
94. 　Me.Label1.Caption = AxisX.Address
95. 　Me.Label2.Caption = AxisY.Address
96. 　Me.Label3.Caption = Format(Application.WorksheetFunction.Correl (AxisX, AxisY), "0.000")
97. End Sub

図６－１１

９６行目は、ワークシート関数の「Correl」を使って相関係数を求め、桁数を揃えてLabel３に書き込んでいます。
Excelの相関係数を求める関数にはCorrelの他に「Pearson」がありますが、Microsoftのページを調べてみると内部式は図６－１２の如く同じですので、得られる係数ももちろん一緒（戻り値はDouble型）です。

図６－１２

また、桁数揃えにはFormat関数を使い、今回は小数点３桁で表示をしています。この桁数に理由はありませんが、本来はデータの個数やデータが持っている桁数などで有効桁数を決めるべきだと考えます。

７．最後に

データを分析する際に「各系列毎にグラフ化しシート一面にグラフを並べて見比べる」ことをした方も多いと思います。私もその一人で、小さなグラフを並べるマクロを作ったこともあります。
色々な方法があって当然ですが、今回のようにＹ軸を送っていく方法も系列が多い場合には有効な気がしますし、発展させてＸ軸Ｙ軸両方を動かせるようなものとか、全系列の相関係数を計算して高い方から表示する等、グラフには工夫のしどころが多いと思います。

複数系列のデータを連続的にグラフ化（it-038.xlsm）

セキュリティ向上を目的として「インターネット経由でダウンロードしたOfficeファイル（Excel等）のマクロは、既定でブロック」されるようにOfficeアプリケーションの既定動作が変更になりました。（2022年4月より切替開始） 解除の方法については「ダウンロードファイルのブロック解除方法」を参照下さい。