グラフの更新

Visual Basicコードを使用してグラフを更新することができます。このセクションでは、データの最後にポイントを追加することによって、グラフを更新して作成する方法を説明します。また、トップに説明のための余白を用意し、最初の列がx軸の記述子で、2番目の列がy軸データとなるものを使用します。

グラフでデータを更新するために、名前付きの範囲を使用します。範囲に名前を付けるには、ワークシートのNameプロパティを使用します。たとえば、セルE6にMyCellと名前を付けるには以下のようにします。

Define Name dialog box

    Names.Add Name:="MyCell", RefersTo:=Cells(6, 5)

ワークシートでこれを確認できます。セルE6でクリックをすると、MyCellという名前が数式ツールバーの名前ボックスの左に表示されます。また、メニューで Insert > Name > Define... を選択すると、名前と対応するセルの位置がダイアログに表示されます。

Excelのスプレッドシート上の名前や定義をすべてプリントできます。メニューでInsert > Name > Paste...を選択し、Paste Listをクリックします。ワークシート内のアクティブなセルで始まる名前がリスト表示されます。

セルに名前を付けると、セル内に範囲内のデータ・ポイント数を計算するための数式などを入力できます。 また、セルは変数として機能します。この数式を含むセルは、データ・ポイントの数を表示します。グラフのデータの範囲が複雑になる場合があるので、この範囲にも名前を指定します。下記に、データ・ポイントの数を計算するコードを示します。strTempは= CountA($A$2:$A$1000)のように表示される文字列です。rngがセルA1を示す場合は、A2からA1000までの最初の列のポイントの数が計算されます。

    Dim strTemp As String

 strTemp = "= CountA(" & Range(rng.Cells(2, 1), rng.Cells(1000, 1)).Address & ")" rng.Cells(1, 5) = strTemp rng.Parent.Names.Add Name:="NumberPoints", RefersTo:=rng.Cells(1, 5)

これで、グラフのx軸の数式を、NumberPointsという名前を使用して作成できます。NumberPointsは数式の変数として機能するので、x軸に対応する範囲を変更することができます。StrFirstDataCellは、OFFSET関数で機能する左上のセル用の明示的なアドレスを提供する文字列です。左上のセルがA!の場合、strFirstDataCellに対応する数式は[graphExample.xls]Sheet1!R1C1のようになります。

次に、x軸を作成します。XaxisFormulaはグラフのx軸に対応する範囲として使用される文字列です。文字列であるXaxisという名前が割り当てられます。A1が左上のセルの場合は、XaxisFormulaの数式はOFFSET([graphExample.xls]Sheet1!R1C1,1,0,NumberPoints,1)のようになります。

 strFirstDataCell = rng.Cells(1, 1).Address(ReferenceStyle:=xlR1C1, external:=True) XaxisFormula = "OFFSET(" & strFirstDataCell & ",1,0," & "NumberPoints" & ",1)" Names.Add Name:= "Xaxis", RefersToR1C1:="=" & XaxisFormula

x軸をy軸の基準として参照します。y軸はx軸ですが、右側の列です。

YaxisFormula = "OFFSET(" & "Xaxis" & ",0,1,,)" Names.Add Name:= "Yaxis", RefersToR1C1:="=" & YaxisFormula

サンプル・コードでは、XaxisFormulaの文字列は、名前が付けられた数式として使用される文字列変数です。最終的な名前と数式をワークシートにコピーして、コードを実行した後に表示できます。これらの文字列は複雑になる場合があるので、これらをデバッグするには最適な方法です。メニューでInsert > Name > Paste...を選択すると、Excelワークシートに数式を表示させることができます。

作成した最初のセルNumberPointsではx軸の行のポイント数を計算します。このセルを使用して、変化するデータにアクセスします。

グラフを作成する前に、ワークシートにダミー・データを作ります。ダミー・データは、一番上の行のそれぞれのセルに、2つの文字列または2つの軸タイトルで構成する必要があります。これらの2つのセルにタイトル"X-axis"と"Y-axis"を書き込みます。これらのタイトルの下で、グラフでは最初の列の2番目のセルに文字列が期待されるので、そこに"xxx"と書き込みます。これはx軸のデータによって上書きされます。グラフでは2番目の列の2番目のセルに数値が期待されるので、そこに"1"を書き込みます。

 With rng .Cells(1, 1) = "X-axis" .Cells(1, 2) = "Y-axis" .Cells(2, 1) = "xxx" .Cells(2, 2) = 1 End With

これで、名前の付いた範囲が有効となり、グラフを作成することができます。グラフのSourceを、1番上の2つのセルとその下のデータの行のダミー・データに設定し、2 x 2の領域を囲みます。これはグラフの作成に十分なデータ数です。下記のコードでグラフを作成します。

 Dim SheetName As String Dim chrt As Chart Dim sourceAddress As String SheetName = rng.Parent.Name Set chrt = Charts.Add sourceAddress = Range(rng.Cells(1, 1), rng.Cells(2, 2)).Address() chrt.SetSourceData Source:=Sheets(SheetName).Range(sourceAddress), PlotBy _ :=xlColumns

この時点で、最初の列がx軸で2番目の列がy軸であること、タイトル（y軸のトップは文字列）が付くこと、x軸データはx軸に対応する注釈データであること（2番目のセルは文字列のため）であることが、グラフで認識されます。1つのみのデータ・ポイントがあることをグラフが認識します。このため、修正が必要です。X軸/y軸データが名前の付いた範囲であることをグラフに認識させる必要があります。このため、以下のようにグラフを変更します。

 With chrt.SeriesCollection(1) ' This does not work when sheetname has a space character .XValues = "=" & SheetName & "!" & nameXaxis .Values = "=" & SheetName & "!" & nameYaxis End With

名前付けが終了したら、データ・サイズを変更し、グラフを更新することができます。マクロのサンプル"MakeChart"をExcelで試します。ワークシート内にグラフとデータがあれば、データの下半分が消去され、そのデータがグラフ上で更新されます。グラフのデータ・ポイントでクリックしてみます。データの周囲に枠が表示され、グラフ上のデータを示します。グラフの空領域で右クリックします。Source Data... を、次にSeriesタブを選択します。xとy軸に対する範囲を確認できます。すべてが機能している場合、名前が付けられた範囲となります。

Related Links