excel グラフのデータ範囲に名前定義を使いたい

質問

可変の範囲を持つ折れ線グラフを作成したいのです。
グラフウィザードでデータ範囲を
=エクセルファイル1.xls!valueとして（範囲をvalueとして名前定義しています。データがある行までを範囲指定するものです）
実行すると、一見正しく表示されているのですが
表のデータが増えたりしても範囲を変更してくれません。
グラフを右クリックして元のデータを見てみると、先程
=エクセルファイル1.xls!valueと指定したところが具体的な範囲（A10:G20)等に変わってしまっていました。
最初にグラフを作成したときの範囲がずっと反映されるようなのです。
私は常に=エクセルファイル1.xls!valueの範囲を反映させてほしいのですが……
具体的にどういう方法があるのでしょうか……

回答No.1

end-u · ベストアンサー

qa4939552.htmlではうまく伝わらなかったようですね。
[名前定義]を使う可変範囲グラフは、『系列ごとに』名前を定義する必要があります。
それにSetSourceDataでセットするのではありません。
http://office.microsoft.com/ja-jp/excel/HA011098011041.aspx
のようにSERIES関数を使います。

系列データだけの簡易なサンプル。
Sub try_1() 'サンプルシート作成
　　Const y As Long = 10 'サンプルデータ初期行数
　　Const x As Long = 5　'サンプル系列数
　　Dim cht As Chart
　　Dim i　 As Long
　　Dim s　 As String

　　With Sheets.Add
　　　　'サンプルデータ作成
　　　　With .Range("A1").Resize(y, x)
　　　　　　.Formula = "=INT(RAND()*100)"
　　　　　　.Value = .Value
　　　　End With

　　　　'グラフ作成
　　　　s = .Name & "!"
　　　　Set cht = .ChartObjects.Add(.Cells(1, x + 1).Left, 0, 500, 300).Chart
　　　　cht.ChartType = xlLine
　　　　For i = 1 To x
　　　　　　'[名前定義]
　　　　　　.Names.Add "系" & i, "=OFFSET($A$1,0," & i - 1 & ",COUNTA($A:$A))"
　　　　　　cht.SeriesCollection.NewSeries.FormulaR1C1 = "=SERIES(,," & s & "系" & i & ",)"
　　　　Next
　　End With

　　Set cht = Nothing
End Sub

系列の名前やx軸も設定するサンプル。
Sub try_2() 'サンプルシート作成
　　Const y As Long = 10 'サンプルデータ初期行数
　　Const x As Long = 5　'サンプル系列数
　　Dim cht As Chart
　　Dim i　 As Long
　　Dim s　 As String
　　Dim v

　　With Sheets.Add
　　　　'サンプルデータ作成
　　　　.Range("A1").Value = "回"
　　　　.Range("B1").Resize(, x).Formula = "=ADDRESS(ROW(),COLUMN(),4)"
　　　　.Range("A2").Resize(y).Formula = "=ROW(A1)"
　　　　.Range("B2").Resize(y, x).Formula = "=INT(RAND()*100)"
　　　　With .Range("A1").CurrentRegion
　　　　　　.Value = .Value
　　　　End With

　　　　'グラフ作成
　　　　s = .Name & "!"
　　　　Set cht = .ChartObjects.Add(.Cells(1, x + 2).Left, 0, 500, 300).Chart
　　　　cht.ChartType = xlLine
　　　　.Names.Add "回", "=OFFSET($A$1,1,0,COUNTA($A:$A))"
　　　　For i = 1 To x
　　　　　　'[名前定義]
　　　　　　.Names.Add "ラベル" & i, "=OFFSET($A$1,0," & i & ")"
　　　　　　.Names.Add "系" & i, "=OFFSET($A$1,1," & i & ",COUNTA($A:$A))"
　　　　　　v = Array(s & "ラベル" & i, s & "回", s & "系" & i, i)
　　　　　　cht.SeriesCollection.NewSeries.FormulaR1C1 = "=SERIES(" & Join(v, ",") & ")"
　　　　Next
　　End With

　　Set cht = Nothing
End Sub

#VBAコードのサンプルではありません。
#[名前定義]を使った可変範囲グラフのサンプルです。
#できあがったグラフの[元のデータ]-[系列]タブ内容と、
#系列を選択して数式バーに表示されるSERIES関数と、
#[名前定義]を参照して理解してください。

お礼

ありがとうございます!!
VBAを実行して正しく動くことを確認しましたが
自分の知識ではプログラムの内容を理解するのにまだ
時間がかかりそうです
しかし時間がかかっても少しずつ読み取っていこうと思います！
長文本当に感謝しています、ありがとうございました＾＾