Excel：指定したデータ範囲を可変的に取得する方法。

質問

Excel：指定したデータ範囲を可変的に取得する方法。
現在、個人の労働時間の昨年度と今年度を月ごとに比較するグラフを作っています。
一人ひとりのグラフを新規シートに追加していくのですが、人数が多いのでこれをマクロに
したいのです。
【sheet1の表（元データ）】
(A) (B) (C) (D) (E) (F) (G) (P)
(1)　　　　　 １月　 ２月　 ３月　 ４月・・・１２月
(2) 1 社員A 20期　20 15.5 22.75 12 27.2
(3)　　　 21期 12 12 26 10 13
(4) 2 社員B 20期 : : : : :
(5)　　　 21期
(6) 3 社員C 20期
(7)　　　 21期
(8) 4 社員D 20期
　　　 　21期
：　:

【作りたいマクロ】
例）A2のセル[１]を選択して実行すると、社員Aのデータ範囲（B2～P3までと一行目の月単位行）を
グラフ化し、新規シートに追加する。
・上記例を他社員のデータ範囲にも使えるようにデータ範囲を可変的にしたい。

・できればそのマクロをボタン化して、A列にそれぞれボタンを挿入したい。
※A4[2]セルのボタンを押すと、社員Bのグラフが作成される。

下記にわかるところまでのコードを記します。（vba初心者で拙いコードですがお許しください）
Sub Macro1()

Range(Cells(1, 2), Cells(1, 18)).Select '１月～月平均の列
Range(Cells(2, 2), Cells(3, 18)).Select 'グラフ化する範囲

Cells(2, 2).Activate

ActiveSheet.Shapes.AddChart.Select 'グラフ追加
'グラフデータの範囲設定
ActiveChart.SetSourceData Source:=Worksheets("Sheet1").Range(Worksheets("Sheet1").Cells(1, 2), Worksheets("Sheet1").Cells(1, 18))
ActiveChart.SetSourceData Source:=Worksheets("Sheet1").Range(Worksheets("Sheet1").Cells(2, 2), Worksheets("Sheet1").Cells(3, 18))

ActiveChart.ChartType = xlColumnClustered
ActiveChart.ApplyLayout (5)
ActiveChart.Location Where:=xlLocationAsNewSheet, name:="グラフ"
ActiveSheet.Move after:=Worksheets("Sheet1")
Sheets("Sheet1").Select
End Sub

上記のコードだと範囲指定しているため社員Aのグラフしか作成できません。
範囲を可変的にするために変数を使おうと考えているのですが、
どのように書いてよいのか混乱してしまって・・・

皆様にご教授願いたいと思いましてこちらに質問させていただきました。
長文および読み辛くなってしまい申し訳ありません。

どうかよろしくお願いいたします。

回答No.2

end-u · ベストアンサー

test2はマクロを使って可変範囲のグラフを作ってますが、
最初に作っておけば良いので手作業でも可能なのです。
例えば

【名前定義】【参照範囲】
syain2　　　　=OFFSET($B$2,($A$1-1)*2+2,0)
_20期2　　　 =OFFSET($D$2,($A$1-1)*2+2,0,1,13)
_21期2　　　 =OFFSET($D$3,($A$1-1)*2+2,0,1,13)

という感じで名前定義を追加します。
参照範囲はtest2で作った名前参照範囲から２行下へOFFSETさせた位置関係です。（+2）
あとはtest2グラフをコピーしてタイトルを[テキストの編集]で、
系列を[データの選択]で編集します。
=Sheet1!syain2
系列"20期"　の系列値　=Sheet1!_20期2
系列"21期"　の系列値　=Sheet1!_21期2

一応、マクロで表現すると。

Sub test3()
　　Dim s As String

　　With ActiveSheet
　　　　.Range("A1").Value = 1
　　　　'[名前定義]
　　　　.Names.Add "社員", "=OFFSET($B$2,($A$1-1)*2,0)"
　　　　.Names.Add "_20期", "=OFFSET($D$2,($A$1-1)*2,0,1,13)"
　　　　.Names.Add "_21期", "=OFFSET($D$3,($A$1-1)*2,0,1,13)"
　　　　.Names.Add "社員2", "=OFFSET($B$2,($A$1-1)*2+2,0)"
　　　　.Names.Add "_20期2", "=OFFSET($D$2,($A$1-1)*2+2,0,1,13)"
　　　　.Names.Add "_21期2", "=OFFSET($D$3,($A$1-1)*2+2,0,1,13)"
　　　　s = .Name
　　　　'グラフ作成
　　　　With .ChartObjects.Add(.Range("Q1").Left, 0, 500, 300).Chart
　　　　　　.ChartType = xlColumnClustered
　　　　　　.SeriesCollection.NewSeries.Formula = _
　　　　　　"=SERIES(""21期"",'" & s & "'!R1C4:R1C15,'" & s & "'!_21期,)"
　　　　　　.SeriesCollection.NewSeries.Formula = _
　　　　　　"=SERIES(""20期"",'" & s & "'!R1C4:R1C15,'" & s & "'!_20期,)"
　　　　　　.ApplyLayout (5)
　　　　　　.ChartTitle.Text = "='" & s & "'!社員"
　　　　　　'y軸タイトル
　　　　　　.Axes(xlValue).HasTitle = False
　　　　End With
　　　　With .ChartObjects.Add(.Range("Q1").Left, 300, 500, 300).Chart
　　　　　　.ChartType = xlColumnClustered
　　　　　　.SeriesCollection.NewSeries.Formula = _
　　　　　　"=SERIES(""21期"",'" & s & "'!R1C4:R1C16,'" & s & "'!_21期2,)"
　　　　　　.SeriesCollection.NewSeries.Formula = _
　　　　　　"=SERIES(""20期"",'" & s & "'!R1C4:R1C16,'" & s & "'!_20期2,)"
　　　　　　.ApplyLayout (5)
　　　　　　.ChartTitle.Text = "='" & s & "'!社員2"
　　　　　　.Axes(xlValue).HasTitle = False
　　　　End With
　　End With
End Sub

お礼

お礼が遅くなってしまい申し訳ありません。
end-u様に教えて頂いたコードを元に希望通りのマクロができました！
複数の比較グラフについてなのですが、名前定義の際に社員１と社員２を、
A１セルとB１セルに分けることで任意の社員のグラフを表示させることができました。
名前定義やOFFSETの使い方を知らなかったのでとても勉強になりました。
急な質問にも関わらず、丁寧にご指南くださり本当にありがとうございます。

回答No.1

end-u

Sub test1()
　　Dim r As Range 'SourceData範囲
　　Dim x As Range 'x軸ラベル範囲
　　Dim n As Long　'Data先頭行

　　With ActiveSheet
　　　　'ActiveCellがデータ範囲になければExit
　　　　If Intersect(ActiveCell, .UsedRange) Is Nothing Then MsgBox "exit": Exit Sub
　　　　'Data先頭行取得
　　　　n = WorksheetFunction.Floor(ActiveCell.Row, 2)
　　　　'1行目の場合Exit
　　　　If n = 0 Then MsgBox "exit": Exit Sub
　　　　'SourceData範囲セット
　　　　Set r = .Cells(n, 2).Resize(2, 14)
　　　　'x軸ラベル範囲セット
　　　　Set x = .Range("D1:O1")
　　End With
　　With Charts.Add
　　　　.ChartType = xlColumnClustered
　　　　.SetSourceData Source:=r, PlotBy:=xlRows
　　　　.SeriesCollection(1).XValues = x
　　　　.ApplyLayout (5)
　　　　'グラフタイトル
　　　　'.HasTitle = False
　　　　'y軸タイトル
　　　　'.Axes(xlValue).HasTitle = False
　　End With

　　Set r = Nothing
　　Set x = Nothing
End Sub
...こんな感じでいけるハズ。
ActiveCellの位置に応じてData範囲を判断しグラフ作成します。
つまりボタンは１コで良いです。
Worksheet_BeforeDoubleClickイベントなどを使っても良いかもしれません。（VBAに慣れてきたら）

ただ、人数が多いなら『一人ひとりのグラフを新規シートに追加していく』
のは大変なような気がします。
データがあるシート上にグラフを作って、
1 , 2 ... などと A列の（社員を識別する？）番号を A1セルに 入力する事で
グラフを切り替えたりする事なども考えたほうが良いかもしれません。
下記は名前定義を使う例。
（単純に、作業セルに関数をセットし、該当データのみ引っ張ってくる方式でも良いと思います）
１コ作れば、A1セルの値を変更する事でデータ変更できます。

Sub test2()
　　Dim s As String

　　With ActiveSheet
　　　　.Range("A1").Value = 1
　　　　'[名前定義]
　　　　.Names.Add "社員", "=OFFSET($B$2,($A$1-1)*2,0)"
　　　　.Names.Add "_20期", "=OFFSET($D$2,($A$1-1)*2,0,1,12)"
　　　　.Names.Add "_21期", "=OFFSET($D$3,($A$1-1)*2,0,1,12)"
　　　　s = .Name
　　　　'グラフ作成
　　　　With .ChartObjects.Add(.Range("P1").Left, 0, 500, 300).Chart
　　　　　　.ChartType = xlColumnClustered
　　　　　　.SeriesCollection.NewSeries.Formula = _
　　　　　　"=SERIES(""21期"",'" & s & "'!R1C4:R1C15,'" & s & "'!_21期,)"
　　　　　　.SeriesCollection.NewSeries.Formula = _
　　　　　　"=SERIES(""20期"",'" & s & "'!R1C4:R1C15,'" & s & "'!_20期,)"
　　　　　　.ApplyLayout (5)
　　　　　　.ChartTitle.Text = "='" & s & "'!社員"
　　　　　　'y軸タイトル
　　　　　　.Axes(xlValue).HasTitle = False
　　　　End With
　　End With
End Sub

補足

ご回答ありがとうございます！
どちらの方法も望んでいた動作ができて本当に感動しました！！

有難くtest2のコードを使わせて頂きたいのですが、
こちらのコードを応用してグラフを複数表示させることは可能でしょうか？
通常はtest2のコードで全く問題ないのですが、社員Aのグラフと社員Bの
グラフを比較したいときなどに、複数表示することができたらと思いまして…

よろしければ教えていただけると幸いです。
重ねての質問失礼いたします。