こんにちはゲストさん。会員登録(無料)して質問・回答してみよう!

解決済みの質問

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

Excel:指定したデータ範囲を可変的に取得する方法。
現在、個人の労働時間の昨年度と今年度を月ごとに比較するグラフを作っています。
一人ひとりのグラフを新規シートに追加していくのですが、人数が多いのでこれをマクロに
したいのです。
【sheet1の表(元データ)】
(A) (B) (C) (D) (E) (F) (G) (P)
(1)      1月  2月  3月  4月・・・12月
(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のセル[1]を選択して実行すると、社員Aのデータ範囲(B2~P3までと一行目の月単位行)を
グラフ化し、新規シートに追加する。
・上記例を他社員のデータ範囲にも使えるようにデータ範囲を可変的にしたい。

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

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

Range(Cells(1, 2), Cells(1, 18)).Select '1月~月平均の列
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のグラフしか作成できません。
範囲を可変的にするために変数を使おうと考えているのですが、
どのように書いてよいのか混乱してしまって・・・

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

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

投稿日時 - 2010-05-17 15:22:39

QNo.5901466

すぐに回答ほしいです

質問者が選んだベストアンサー

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で作った名前参照範囲から2行下へ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

投稿日時 - 2010-05-20 21:38:14

お礼

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

投稿日時 - 2010-05-24 15:16:10

ANo.2

このQ&Aは役に立ちましたか?

11人が「このQ&Aが役に立った」と投票しています

回答(2)

ANo.1

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範囲を判断しグラフ作成します。
つまりボタンは1コで良いです。
Worksheet_BeforeDoubleClickイベントなどを使っても良いかもしれません。(VBAに慣れてきたら)



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

投稿日時 - 2010-05-17 23:43:17

補足

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

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

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

投稿日時 - 2010-05-20 13:29:30

あなたにオススメの質問