Ｅｘｃｅｌの範囲可変グラフ

質問

必要に迫られ、数日前から関数やマクロなるモノを勉強しながら奮闘中です。
範囲可変のグラフを作りたく、以下のサイトを参考にしながら試行錯誤してるのですが、応用が効きません。。
http://www2.odn.ne.jp/excel/waza/graph.html#SEC13
http://okwave.jp/qa2138388.html

どれも、ある固定位置を起点として、増えた範囲までの全てのエリアをグラフ化してるようなのですが、
やりたいのは、任意のデータ範囲を自由に（Windowをスライドするように）グラフ化したいのです。
データの量が大量で全部表示すると見えなくなるため、
指定したい範囲をパラメータとして与えることで、その範囲だけを切り出したようなグラフにしたい。です。

例えば、
A5:B100　に以下データが入ってたとして、
（A列は上から連番でダブリなし。実際には時間=tになる）

A,B
-----
1,a ←(A5,B5)
2,b
3,c ←ここから
4,d
5,e
6,f
7,g
8,h ←ここまでをグラフ化
9,i
：

ここで、あるパラメータ用のセルに、
開始位置=3、終了位置=8という値を指定すると、
A列から3と8を見つけ出し、その範囲のグラフを作る。
3と8なので、グラフの範囲としては、A7:B12。
（実際には、列はもっと多いですが）

できれば"エクセル技道場"のような範囲指定関数＋グラフ設定のほうが良いです。
（今回マクロも少しかじったのでマクロでも何とかなるカモ・・(^^;）

すみませんが、よろしくお願い致します。

回答No.7

end-u · ベストアンサー

#6補足に対するレスです。

『Excel で名前付け範囲を使用して動的なグラフを作成する』
http://office.microsoft.com/ja-jp/excel/HA011098011041.aspx
▲こちらを参照されると良いです。

『グラフの系列を選択し、Excel の数式バーを確認すると、
その系列が SERIES 関数を使用した数式によって生成されていることがわかります。
SERIES は、このような場合にのみ使用される特殊な関数で、グラフの系列を定義します。』
とあります。

Sub test3()
　　Dim s As String

　　With Sheets.Add
　　　　s = .Name & "!"
　　　　.Range("A1:B4").Value = [{"","name";"a",1;"b",2;"c",3}]
　　　　With .ChartObjects.Add(.Range("F1").Left, 0, 500, 300)
　　　　　　.Activate
　　　　　　With .Chart
　　　　　　　　.HasLegend = True
　　　　　　　　.ChartType = xlColumnClustered
　　　　　　　　With .SeriesCollection.NewSeries
　　　　　　　　　　.Formula = "=SERIES(" & s & "B1," & s & "A2:A4," & s & "B2:B4,1)"
　　　　　　　　　　.Select
　　　　　　　　End With
　　　　　　End With
　　　　End With
　　End With
End Sub

これで系列選択状態になりますから、数式バーを見てください。
=SERIES(SheetX!$B$1,SheetX!$A$2:$A$4,SheetX!$B$2:$B$4,1)
と表示されているはずです。

手作業で適当なグラフを作成し、系列を選択した場合も同じように表示されます。
つまりグラフの系列の参照元を表しています。

前述ページに SERIES 関数の引数についても説明がありますので確認しておいてください。
=SERIES(凡例の名前,項目軸ラベル,プロット値,プロット順)
...となります。

お礼

ありがとうございます。
やっとスッキリしました。
SERIESがなにやらグラフ作成に重要な呪文なんだろうとは思ってましたが、
こういう構成だったんですね。。
あと、項目軸ラベルの設定もどうやってやるのか悩んでたことろだったので、
非常に助かりました。
（どこかのサイトで書かれてた構文を作り変えて無理やり突っ込んでましたが..）

ちなみに、「#3補足に対するレス」で書かれてた
.Formula = "=SERIES(,," & ws.Name & "!data" & i & ",)"　
は、
.HasLegend = False
だったので、凡例の名前,項目軸ラベルのところが、,, だったんですね。。

回答No.6

end-u

#3補足に対するレスですが。

提示したコードは、あくまでサンプルグラフを作成するだけのもので、
基本的には、OFFSET関数を利用した名前の定義を使えば可変範囲のグラフが作成できるという例です。
>任意のデータ範囲を自由に（Windowをスライドするように）グラフ化したい
という事だったので、スクロールバーを使った例を示しました。
>開始位置に４、終了位置に１０と指定すると、
という事であれば、MATCH関数を使って範囲を指定できるようにすると良いでしょう。

>複数ある系列を縦に累計する棒グラフにするには、
の箇所がちょっとひっかかります。
累計グラフにするには、累計データが必要です。
その辺りを加味したサンプルグラフを以下のコードで作ってみました。
コードを理解するのではなく、できあがったグラフの[元データ]などの設定を見て理解してください。

Sub test2()
　　Const x As Long = 100　　'サンプルデータ行数
　　Dim ws　As Worksheet
　　Dim i　 As Long

　　'■シートを追加、サンプルデータ作成
　　Set ws = Sheets.Add
　　With ws
　　　　'セルB1に開始値、B2に終了値
　　　　.Range("A1:B2").Formula = [{"開始", 4;"終了",10}]
　　　　.Range("C1").Formula = "=MATCH(B1,A:A,0)-5"
　　　　.Range("C2").Formula = "=MATCH(B2,A:A,0)-4-C1"
　　　　.Range("A4:E4").Value = [{"time","日計1","累計1","日計2","累計2"}]
　　　　With .Range("A5:E5").Resize(x)
　　　　　　.Columns(1).Formula = "=ROW()-4"
　　　　　　'C列、E列は累計値
　　　　　　.Offset(, 1).Resize(, 4).Formula _
　　　　　　　　　= Array("=RAND()*" & x, "= Sum(B5,C4)", "=RAND()*" & x, "= Sum(D5,E4)")
　　　　　　.Value = .Value
　　　　End With

　　　　'■グラフ作成、名前定義し系列にセット。累計値は第2軸の線グラフ
　　　　With .ChartObjects.Add(.Range("F1").Left, 0, 500, 300).Chart
　　　　　　.HasLegend = False
　　　　　　.ChartType = xlColumnClustered
　　　　　　For i = 1 To 4
　　　　　　　　ws.Names.Add "data" & i, "=OFFSET($A$5,$C$1," & i & ",$C$2,)"
　　　　　　　　With .SeriesCollection.NewSeries
　　　　　　　　　　.Formula = "=SERIES(,," & ws.Name & "!data" & i & ",)"
　　　　　　　　　　If i Mod 2 = 0 Then
　　　　　　　　　　　　.ChartType = xlLine
　　　　　　　　　　　　.AxisGroup = 2
　　　　　　　　　　End If
　　　　　　　　End With
　　　　　　Next
　　　　End With
　　End With

　　Set ws = Nothing
End Sub

補足

ネットで色々調べ、ようやく内容が理解できました。。
ただ、以下の２行だけがどうしても分からないので、
内容を解説して頂けると嬉しいのですが・・
（検索しても、SeriesやSERIESがなかなか見つからなくて）

With .SeriesCollection.NewSeries
.Formula = "=SERIES(,," & ws.Name & "!data" & i & ",)"

誠に申し訳ございませんが、よろしくお願い致します。

回答No.5

taka816_00

前ログですが
http://oshiete1.goo.ne.jp/qa4737644.html

お礼

情報ありがとうございます。
せっかく情報頂きましたが、マクロの中身以前に、質問/回答の内容が良く理解できませんでした。。
すみません。

回答No.4

xls88

過去ログですが参考になるかも知れません。

指定した期間の数値をグラフにしたいのですが
http://oshiete1.goo.ne.jp/qa4323804.html

お礼

情報ありがとうございます。
せっかく情報頂きましたが、マクロの中身以前に、質問/回答の内容が良く理解できませんでした。。
すみません。

回答No.3

end-u

>範囲指定関数＋グラフ設定
の例として、サンプルグラフを作るマクロです。
手作業で可能なので、できたグラフを参考に、応用してみてください。

Sub test()
　　Const x As Long = 100 'サンプルデータ行数
　　Const n As Long = 20　'グラフに表示するデータ数の初期値
　　Dim r　 As Range
　　Dim s　 As String

　　'【１】シートを追加し、サンプルデータを作成
　　With Sheets.Add
　　　　.Range("A1:D1").Value = Array("回", "数", "開始位置", "表示データ数")
　　　　With .Range("A2:B2").Resize(x)
　　　　　　.Formula = Array("=ROW()-1", "=RAND()*" & x)
　　　　　　.Value = .Value
　　　　End With

　　　　'【２】[フォーム]ScrollBarをシート上に置いて[コントロールの書式設定]
　　　　For Each r In .Range("C3:D3")
　　　　　　With .ScrollBars.Add(r.Left, r.Top, r.Width, r.Height)
　　　　　　　　.Value = 1　　　　　　　　　　　　 '現在値
　　　　　　　　.Min = 1　　　　　　　　　　　　　 '最小値
　　　　　　　　.Max = x　　　　　　　　　　　　　 '最大値
　　　　　　　　.SmallChange = 1　　　　　　　　　 '変化の増分
　　　　　　　　.LargeChange = 10　　　　　　　　　'スクロールバーシャフトの増分
　　　　　　　　.LinkedCell = r.Offset(-1).Address 'リンクするセル
　　　　　　End With
　　　　Next
　　　　'グラフ表示データ数の初期値をセット
　　　　.Range("D2").Value = n

　　　　'【３】シートレベルの[名前定義]で可変範囲を設定
　　　　'C2にA1からの移動数（グラフ開始位置） D2にグラフ表示データ数
　　　　.Names.Add "回", "=OFFSET($A$1,$C$2,0,$D$2,)"
　　　　.Names.Add "数", "=OFFSET($B$1,$C$2,0,$D$2,)"
　　　　s = .Name

　　　　'【４】グラフ作成し、系列追加し定義した名前をセット
　　　　With .ChartObjects.Add(.Range("E1").Left, 0, 500, 300).Chart
　　　　　　.HasLegend = False
　　　　　　.ChartType = xlLine
　　　　　　.SeriesCollection.NewSeries.Formula = "=SERIES(," & s & "!回," & s & "!数,1)"
　　　　　　.Axes(xlValue).MaximumScale = x
　　　　End With
　　End With
End Sub

補足

すごいっす。。
現時点、マクロの内容は全然理解できませんが、
期待以上の結果に感動しました。
ただ、なにぶんマクロ初心者なため、今回作りたいグラフに応用させるのに間に合うか・・
他にも色々応用できそうなので、なんとか解読してモノにしたいと思います。
ちなみに、折れ線グラフでなく、複数ある系列を縦に累計する棒グラフにするには、
どこの記述を変更すれば良いでしょうか？
Ｂ列の"数"が、更にＣ列、Ｄ列・・と並んでる場合に。

回答No.2

akiramax19

コメントアウト文が汚かったので際回答させていただきます。

すでにグラフが存在しており、グラフに出力する範囲のみを変更する
場合について説明します。

例として、セルA1に開始位置、セルA2に終了位置を入力するとします。
(開始位置に４、終了位置に１０を入力するとします。)
以下にマクロを示します。
あとはこのマクロを起動するボタンなどを御自分で設定すれば使用しやすくなります。

-------------以下マクロ-------------------

Sub Macro1()
開始位置 = Range("A1") 'A1に入力されている値を取得
終了位置 = Range("A2") 'A2に入力されている値を取得

'範囲の設定
'「A4:A10」の形に変換。trim(Str())はStr()で数字を文字列に変換し、trim()で空白部分を削除しています
範囲 = "A" + Trim(Str(開始位置)) + ":" + "A" + Trim(Str(終了位置))

'グラフ １というグラフの範囲を変更するとします。
'グラフの名前がわからない場合は、グラフを選択して、右クリック、グラフウインドウをクリックすればグラフ名が表示されます。
'例Sheet1 グラフ　1　　←この「グラフ　１」がグラフ名
ActiveSheet.ChartObjects("グラフ 1").Activate
ActiveChart.ChartArea.Select
'現在作業中のシート名と範囲を指定
'シート名Sheet1の場合
ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range(範囲), PlotBy _
:=xlColumns
End Sub

お礼

MATCH関数をマクロの中に入れるやりかたが分からなかったので、
一端、他のセルに普通のMATCH関数で該当する行番号を表示させ、
そのセルをマクロで読み込んで、範囲指定するよう書き換えることで、
期待通りのグラフを作ることが出来ました。
どうもありがとうございました。感謝です。

補足

早速の回答ありがとうございます。
初心者にも分かり易い細かい説明、非常に助かります。
難しい構文だけが羅列された回答が来たらどうしようと思ってました。
一応、思った感じで動作しました。
が・・
説明不足だったせいか、意図した内容とちょっと違います。。
頂いた内容を元に補足させて頂きます。

＞「A4:A10」の形に変換
開始位置、終了位置で指定するパラメータ（４や１０）は、
Ａ列に入ってる時間情報（文字列　1,2,3,..）のことで、
その情報から、Excelのセル座標（行座標）を求めグラフ化したかったです。
なので、
開始位置に４、終了位置に１０と指定すると、
範囲として指定したいのは、Ａ４，Ａ１０ではなく、
４という文字列（時間値）の入ってる行座標と、
１０という文字列（時間値）の入ってる行座標で構成されるグラフです。
Ａ列の時間情報（1,2,3,4,..）がＡ５セルから始まってるとすると、
１＝Ａ５、２＝Ａ６、３＝Ａ７、４＝Ａ８なので、
開始位置パラメータ＝４から求められるのは、Ａ８を期待します。
同様に、終了位置パラメータ＝１０から求められるのは、Ａ１４を期待します。

また、グラフ化したいのは、その求めたＡ８とＡ１４の間で構成されるグラフなので、
グラフの範囲は、Ａ８：Ａ１４ではなく、例えば列がＣまであった場合は、
グラフの範囲は、A８：Ｃ１４を期待しています。

＞3と8なので、グラフの範囲としては、A7:B12。
＞（実際には、列はもっと多いですが）

すみません、よろしくお願い致します。
具体的には、
横軸に時間軸、縦軸に複数の系列の累計の棒グラフとした場合、
ある時刻の間だけを抜き出して表示したい。というモノです。

回答No.1

akiramax19

すでにグラフが存在しており、グラフに出力する範囲のみを変更する
場合について説明します。

例として、セルA1に開始位置、セルA2に終了位置を入力するとします。
(開始位置に４、終了位置に１０を入力するとします。)
以下にマクロを示します。
あとはこのマクロを起動するボタンなどを御自分で設定すれば使用しやすくなります。
----------------------------------------------------------
Sub Macro1()
開始位置 = Range("A1")'A1に入力されている値を取得
終了位置 = Range("A2")'A2に入力されている値を取得

'範囲の設定
'「A4:A10」の形に変換。trim(Str())はStr()で数字を文字列に変　　'換し、trim()で空白部分を削除しています
範囲 = "A" + Trim(Str(開始位置)) + ":" + "A" + Trim(Str(終了位置))

'グラフ １というグラフの範囲を変更するとします。
'グラフの名前がわからない場合は、グラフを選択して、右クリッ　　'ク、グラフウインドウをクリックすればグラフ名が表示されま　　　'す。例Sheet1 グラフ　1　　←この「グラフ　１」がグラフ名
ActiveSheet.ChartObjects("グラフ 1").Activate
ActiveChart.ChartArea.Select
　　'現在作業中のシート名と範囲を指定
　　'シート名Sheet1の場合
ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range(範囲), PlotBy _
:=xlColumns
End Sub