[[20091015110658]]　『グラフの値をマクロで一括変更』（SSS）　ページの最後に飛ぶ

『グラフの値をマクロで一括変更』（SSS）

 Sheet2の中にグラフが12個あります。
 それぞれ系列が3つあって、シート名データの同列行別より項目軸ラベルと値を下記のように参照しています。

 系列　　　値
 A　　　　=データ!$Y$16:$AJ$16
 B　　　　=データ!$Y$2:$AJ$2
 C　　　　=データ!$Y$30:$AJ$30

 項目軸ラベルに使用　　=データ!$Y$1:$AJ$1

 1ヶ月毎に更新していくのですが、マクロのボタンを押すごとに
 Sheet2のグラフ全ての項目軸ラベルと参照先を一括で右へ1列ずらしていくマクロはどんなコードでしょうか？
 また、左へずらす場合はどんなコードでしょうか？

 右へ1つずらすのを1つのグラフで自動記録しました。

 Option Explicit

 Sub Macro1()
    ActiveSheet.ChartObjects("グラフ 22").Activate
    ActiveChart.SeriesCollection(3).Select
    ActiveChart.SeriesCollection(1).XValues = "=データ!R1C26:R1C37"
    ActiveChart.SeriesCollection(1).Values = "=データ!R16C26:R16C37"
    ActiveChart.SeriesCollection(2).XValues = "=データ!R1C26:R1C37"
    ActiveChart.SeriesCollection(2).Values = "=データ!R2C26:R2C37"
    ActiveChart.SeriesCollection(3).Values = "=データ!R30C26:R30C37"
 End Sub

 これをどのように加工するのでしょう…？
 教えてください。
 宜しくお願いします。

 質問です
 ① "=データ!R1C26:R1C37" → "=データ!R1C27:R1C38" に変更するってことですよね？
 ② 先月と今月でデータの違いはあるのでしょうか？
    先月はY16:AJ16だが、今月はY16:AK16にデータが増えるとか

 (1or8)

 1or8さん

 すみません、自動記録で勝手にR1C1のような形式になっているのですが、
 私、これが良く分からなくて…（なんで勝手になっちゃったんでしょう。いつもはA1とかなのに）
 最初に載せたもので説明しますと

 系列　　　値
 A　　　　=データ!$Y$16:$AJ$16　→=データ!$Z$16:$AK$16
 B　　　　=データ!$Y$2:$AJ$2　　→=データ!$Z$2:$AK$2
 C　　　　=データ!$Y$30:$AJ$30　→=データ!$Z$30:$AK$30

 項目軸ラベルに使用　　=データ!$Y$1:$AJ$1　→=データ!$Z$1:$AK$1

 右へ1列だと上記となります。
 12列をそのままごそっと1列分移動です。
 説明不足で申し訳ありません…

 あ！　一応…
 行参照はグラフごとに違いますが、変更対象ではありません。
 変更対象は、あくまで列だけです。

 (SSS)　ちなみにXP・XLS2003です

 R1C1 = A1
 R2C1 = A2
 R1C2 = B1
 R → Row
 C → Column
 です。

 ②に関してはどうなのでしょうか？　(1or8)

 おじゃまします。
 こういうことでしょうか？

 対象グラフを選択して、下記マクロを実行してみてください。
 （とりあえず、サンプルは 右にシフトする場合だけです。
   左Shiftのときは Direction = -1 としてください）

 Sub Try1_Shift()
    Dim Ser As Series
    Dim rangeY As Range
    Dim v, u As String
    Dim Direction As Long: Direction = 1

    With ActiveChart
        For Each Ser In .SeriesCollection
            v = Split(Ser.Formula, ",")
            Set rangeY = Excel.Range(v(2)).Offset(, Direction)
            v(2) = rangeY.Address(, , xlA1, True)
            If Len(u) = 0 Then u = rangeY.Offset(1 - rangeY.Row).Address(, , xlA1, True)
            v(1) = u
            Ser.Formula = Join(v, ",")
        Next
    End With
 End Sub

  (kanabun)

 ↑について、若干のコメントをば。。。

 > 自動記録で勝手にR1C1のような形式になっているのですが
 マクロ記録はR1C1形式で記録される、これは Excelの仕様です。
  （マクロの記録はR1C1形式で範囲が記録されますが、）
   実は A1形式の参照範囲も 系列オブジェクトのあるプロパティのなかに
   記憶されています。

 > グラフ全ての項目軸ラベルと参照先を一括で右へ1列ずらしていくマクロ
  たとえば、元の参照範囲が Range("データ!$Y$2:$AJ$2") だったら、
 「右へ1列ずらした」範囲は Range("データ!$Y$2:$AJ$2").Offset(,1) ですよね？
  元の参照範囲を「右へ1列ずらした」範囲に直して、元のデータ範囲を保持している
  プロパティに再セットしてやればいいことになります。
  「元のデータ範囲を保持しているプロパティ」は 系列オブジェクトの
   Formulaプロパティ
   です。

 グラフの系列をどれかひとつポイントして数式バー(Formula Bar)を見てください。
 たとえば、系列「B」を選択したとき、数式バーにはつぎのように表示されます。
    =SERIES("B",データ!$Y$1:$AJ$1,データ!$Y$2:$AJ$2,2)

 これが、Seriesオブジェクトの Formula プロパティです。

 Formulaプロパティですから、数式のセル参照形式は セルのFormula同様、A1形式です。
 当然、R1C1形式で参照・設定するために FormulaR1C1プロパティもあります。

 プログラムのなかでやっていることを解説しますと、

     アクティブグラフのすべての系列について順に
      For Each Ser In .SeriesCollection

           その系列の Formula式（文字列）をカンマで区切って４つの要素に分割します
            v = Split(Ser.Formula, ",")
            たとえばそのFormula が 
              「=SERIES("B",データ!$Y$1:$AJ$1,データ!$Y$2:$AJ$2,2)」
            だったとしますと、カンマで分割後、変数v には
              v(0) = 「=SERIES("B"」
              v(1) = "データ!$Y$1:$AJ$1"
              v(2) = "データ!$Y$2:$AJ$2"
              v(3) = "2)"
            という４つの要素の配列が格納されます。

            このうち３番目の要素 v(2) がY軸用元データ範囲ですから、
            このデータ範囲を右へ１列シフトした範囲を得るには
              Set rangeY = Excel.Range(v(2)).Offset(, 1)  
            とすればいいことになります。
            Formulaの３番目の要素を Offset後の範囲アドレスに置き換えます。
              v(2) = rangeY.Address(, , xlA1, True)

            X／項目軸元データ範囲は１行目と決まっているので、X／項目軸元データ範囲アドレス用変数uに
            まだアドレスが入ってないときだけ
               If Len(u) = 0 Then u = rangeY.Offset(1 - rangeY.Row).Address(, , xlA1, True)
            を実行します。
               v(1) = u

            以上で、シフト後のx軸データ範囲とy軸データ範囲とが v(1) v(2)にセットされましたので、
            以下で、カンマ区切りで要素を再結合して Formulaプロパティにセットしてやります。
            Ser.Formula = Join(v, ",")

      Next
     以上を全ての系列について 繰り返します。
 です。
   はなはだ簡単ですが... (kanabun)

 1or8さん
 ②ですが、今月から過去一年のデータを見ているので増える事は今後ないです。
 12列をそのままごそっと1列分移動です。

 kanabunさん
 ばっちり動きます、ありがとうございます。
 丁寧な説明で本当に助かります。

 ただ私はおっちょこちょいなので、グラフの選択し忘れがありそうで…
 また、グラフはとっかえひっかえや増える可能性もあります。
 出来たら手動選択ではなく、「そのシートにある全てのグラフ」というくくりでしたいのです。

 全てのグラフを選択したマクロの記録をとってみました。
 ActiveSheet.Shapes.Range(Array("Chart 24", "Chart 23", "Chart 22", "Chart 7" _
    )).Select
 うーん…これをどのように書いてくださったコードと合わせたらよいでしょう？
 と言うか合わせられなかったりしますか？

 ちなみにオートシェイプで図形は他に数個あります。（ActiveSheet.Shapesと出てきたので言っておいた方がいいのかと思って書きます。）
 グラフ以外のオートシェイプを選択した自動記録
     ActiveSheet.Shapes.Range(Array("AutoShape 33", "AutoShape 34", "Rectangle 1", _
        "Rectangle 2" _
    , "Text Box 3", "Text Box 4", "AutoShape 18", "Text Box 20" _
    , "AutoShape 21")).Select

 （SSS）

 dim i as long
 for i = 1 to ActiveSheet.ChartObjects.Count
     ActiveSheet.ChartObjects(i).Activate
     With ActiveChart '(kanabunさんのコード、With～End With間を)
        ・
        ・
     End With
 Next

 上記をkanabunさんのコードに加えてください。
 (1or8)

 1or8さん
 ありがとうございました、ばっちりです。

 ちなみに…なのですが、行の場合は
 基準セル.Offset(行番号,列番号)なので
 Set rangeY = Excel.Range(v(2)).Offset(1, 0)　←1行下へ  
 Set rangeY = Excel.Range(v(2)).Offset(-1, 0)　←1行上へ
 でOKですよね？

 なんだか後々参照元の表を縦横入替を命じられる予感がして。
 一応聞いておきます。

 (SSS）

 OKです　(1or8)

 お世話になりました、有難うございました。
 (SSS)

 その節はお世話になりました。
 すみません、やはり縦横入替えを命じられまして四苦八苦しております。
 お助け下さい。

 グラフの参照は教えて頂いたように修正したのですが、
 項目軸なのですが、$A$17:$A$28を$A$18:$A$29としたいのに$AK$1:$AK$12となってしまいます。
 参照元は縦横入替えでA4から下へとずっと続いている状態です。
 これはコードのどこを変更すればよいでしょうか？

 kanabunさんの解説を見て
 v(1)がuで…If Len(u) = 0 Then u = rangeY.Offset(1 - rangeY.Row).Address(, , xlA1, True)
 かとは思っているのですが…

 (SSS)

 イメージがちょっとよく分からないので
 どんなデータがあって
 どの範囲でグラフが出来ている時
 どの範囲のグラフに変更したいのか
	[A]	[B]	[C]	[D]
[1]				
[2]				
[3]				
[4]				
[5]				
[6]				
 こんな感じの小さなサンプルで良いので
 書いてみてもらえませんか？

 (HANA)

 こんにちは、お願いします。

 シート名 入力用
  	[A]	[B]	[C]	[D]
 [1]				
 [2]				
 [3]	1～3行まで項目等････			
 [4]    07/10　　　B列からグラフのデータ
 [5]    07/11
 [6]    07/12
 [7]    08/01
  :
  :
 延々と下へと増えていきます。

 現在のコードは
  Sub UPShift()
    Dim Ser As Series
    Dim rangeY As Range
    Dim v, u As String
    Dim Direction As Long: Direction = -1
    Dim i As Long

     For i = 1 To ActiveSheet.ChartObjects.Count
     ActiveSheet.ChartObjects(i).Activate
     With ActiveChart
        For Each Ser In .SeriesCollection
            v = Split(Ser.Formula, ",")
            Set rangeY = Excel.Range(v(2)).Offset(Direction, 0)
            v(2) = rangeY.Address(, , xlA1, True)
            If Len(u) = 0 Then u = rangeY.Offset(1 - rangeY.Row).Address(, , xlA1, True)
            v(1) = u
            Ser.Formula = Join(v, ",")
        Next
    End With
 Next

 End Sub
 Sub DOWNShift()
    Dim Ser As Series
    Dim rangeY As Range
    Dim v, u As String
    Dim Direction As Long: Direction = 1
    Dim i As Long

     For i = 1 To ActiveSheet.ChartObjects.Count
     ActiveSheet.ChartObjects(i).Activate
     With ActiveChart
        For Each Ser In .SeriesCollection
            v = Split(Ser.Formula, ",")
            Set rangeY = Excel.Range(v(2)).Offset(Direction, 0)
            v(2) = rangeY.Address(, , xlA1, True)
            If Len(u) = 0 Then u = rangeY.Offset(1 - rangeY.Row).Address(, , xlA1, True)
            v(1) = u
            Ser.Formula = Join(v, ",")
        Next
    End With
 Next

 End Sub

 グラフの系列は
 =SERIES("在庫月数",入力用!$A$17:$A$28,入力用!$AL$17:$AL$28,3) 
 =SERIES("在庫数",入力用!$A$17:$A$28,入力用!$Z$17:$Z$28,1)
 =SERIES("出庫数",入力用!$A$17:$A$28,入力用!$N$17:$N$28,2)
 =入力用!$A$17:$A$28 (これが項目軸ラベル)
 で、現在のコード名？DOWNShiftを動かすと
 =SERIES("在庫月数",入力用!$A$18:$A$29,入力用!$AL$18:$AL$29,3) 
 =SERIES("在庫数",入力用!$A$18:$A$29,入力用!$Z$18:$Z$29,1)
 =SERIES("出庫数",入力用!$A$18:$A$29,入力用!$N$18:$N$29,2)
 =入力用!$AK$1:$AK$12 (これが項目軸ラベル)
 となります。
 項目軸ラベルが=入力用!$A$18:$A$29となって欲しいのです。

 (SSS)

 ＞v(1)がuで…If Len(u) = 0 Then u = rangeY.Offset(1 - rangeY.Row).Address(, , xlA1, True)
 ＞かとは思っているのですが…
 ですね。。。

 項目軸は、Row 方向（rangeYの上）ではなく
 Column方向（rangeYの左側）に在ると思います。

 こんな変更をしてみるとどうでしょう？
 　If Len(u) = 0 Then u = rangeY.Offset(, 1 - rangeY.Column).Address(, , xlA1, True)

 (HANA)

 HANAさん、できました。
 ありがとうございました。
 RowとColumnですか…
 (SSS)

コメント返信：

[ 一覧(最新更新順) ]

[[20091015110658]] 『グラフの値をマクロで一括変更』（SSS） ページの最後に飛ぶ

[[20091015110658]]　『グラフの値をマクロで一括変更』（SSS）　ページの最後に飛ぶ