人力検索はてな
モバイル版を表示しています。PC版はこちら
i-mobile

お世話になります。現在ワークシート上に2軸の折線グラフを10?20程(ブックにより違います)作成しています。
そのグラフの参照元をVBAにより変更したいのですが、どうすればよいのでしょうか?
参照列が固定していれば良いのですが、随時変更されますのでさらに難解になってます。

具体例ですが、初期の参照元が
“グラフ1” 系列1= B50:B70
系列2= F50:F70
“グラフ2” 系列1= D50:D70
系列2= S50:S70
とグラフが続いています。そして
セルA1=50 → 51
セルB1=70 → 71
と、セルを変化させる事により参照位置を変更させたいのです。
また、不定期ですが現在“グラフ1”では
B列、F列 を参照していますが
AA列、BD列 など変更されま。
またグラフの数も不特定です。
共通して変更されるのは 50?70などの位置のみです。

VBAで変更したいのですが、どうすれば宜しいでしょうか?

●質問者: dadan_dan
●カテゴリ:コンピュータ
✍キーワード:A1 AA BD D50 D70
○ 状態 :終了
└ 回答数 : 5/5件

▽最新の回答へ

1 ● thrillseeker
●5ポイント

VBAだと参照先が変わる度にマクロを編集し直す必要が

あると思うので、それほど便利にならないのでは?


それよりも、随時変更されるデータにグラフの参照先を

直接設定するのでは無く、グラフ参照専用の列を別途

作成し、その列が実際のデータを参照するようにしては

どうですか?


例:

“グラフ1”:系列1= AB1:AB21,系列2=AF1:AF21

列AB1:AB21:列B50:B70を参照

列AF1:AF21:列F50:F70を参照

グラフの変更→列AB,AF の参照先を変更

◎質問者からの返答

回答ありがとうございます。すでにこの方法は実施済みの方法なのですが、個人また

社内なら問題はないですが、ファイルを相手先へ送信するのですが、送信容量や

見た目を考慮すると、やはりVBAが良いと思うのですが如何でしょうか?

参照列の変更はVBAを使うと余計に複雑になりそうですね。すみません今気づきました。

数値の変更のみではどうなのでしょうか?


2 ● 黒ひよこ
●2ポイント

使う行が固定なら割とやりやすいかと。

どのデータが関連付けられてるかVBAに自動認識させたいなら、

すぐ思いつくのが下記の2つですね。

?系列1と系列2は必ず隣の列にする

例 グラフ1 系列1=B50:B70

系列2=C50:C70

グラフ2 系列1=S50:S70

系列2=T50:T70

?同じ系列に共通の見出し付ける

例 グラフ1 B49="グラフ1",系列1=B50:B70

F49="グラフ1",系列2=F50:F70

グラフ2 D49="グラフ2",系列1=D50:D70

S49="グラフ1",系列2=S50:S70

◎質問者からの返答

Dark1984Bさんありがとうございます。

最初の“系列を隣列”にする案ですが、

グラフを作成する時にも負担が少ないのでその方が助かるのですが、

他の処理の手順上並べるのは難しいです。(_ _)

次の“共通の見出しを付ける”案ですが、

“見出し”を付けるという事をしたことがないですが、

残念ながら行は固定が出来ませんが、ここからどの様に

VBAを展開させるのでしょうか?


3 ● 黒ひよこ
●3ポイント

すいません?の例が一部間違っていました。

S49="グラフ1"→正しくはS49="グラフ2"


VBAへの展開ですが、見出し行を作って、

見出しのセルの値が同じ2つの列でグラフ作るとすればいくかと。

まぁ、あくまで行が固定なら通じるやり方です。


あと複数のブックで使いたいみたいですが、

ブックごとの関連付けられてるデータ位置が同じで、

ブックによってはグラフを作らないのであれば、

グラフに使うデータのセルが空欄なら、そこのグラフは作らないとした方が楽だと思います

◎質問者からの返答

Dark1984B さんありがとうございます。

こちらの都合ですみませんが、行が変更しないと最大の効果が発揮できないのです。

ありがとうございました。


4 ● airplant
●100ポイント ベストアンサー

要望のことができるようにマクロを作ってみました。

全シートにあるグラフのカラムはそのままで、行のみを[A1]から[B1]の内容に変更した系列にします(マクロ実行時のシートのA1、B1)。

マクロを動かすと、どばっと一気にグラフが変わります。

Option Explicit

Const REfromPat As String = "\$[0-9]+\:"
Const REToPat As String = "\$[0-9]+\,"

Sub ChangeRef()
 
 Dim wS As Worksheet
 Dim chaObj As ChartObject
 Dim oSer As Series
 Dim sFormula As String
 Dim iFrom As Integer, iTo As Integer
 
 'FromとToの行を覚える
 iFrom = Range("A1").Value
 iTo = Range("B1").Value
 
 'Sheet毎、chart毎、系列毎にFromとToの記載行を文字列置換する
 For Each wS In Worksheets
 For Each chaObj In wS.ChartObjects
 For Each oSer In chaObj.Chart.SeriesCollection
 sFormula = ConvByRe(oSer.Formula, REfromPat, "$" & iFrom & ":")
 oSer.Formula = ConvByRe(sFormula, REToPat, "$" & iTo & ",")
 Next
 Next
 Next
End Sub

'正規表現で文字列を変更する
Function ConvByRe(strSrc As String, REFrom As String, RETo As String) As String
 
 Dim oRe As Variant
 Dim lnI As Long
 Dim strRep As String
 
 Set oRe = CreateObject("VBScript.RegExp")
 With oRe
 .IgnoreCase = True '大小文字無視
 .Global = True '文字列全体が対象
 .Pattern = REFrom '検索パターン
 If .test(strSrc) Then 'マッチしたら文字列変更
 strRep = RETo
 ConvByRe = .Replace(strSrc, strRep)
 Set oRe = Nothing
 Exit Function
 End If
 End With
 ConvByRe = strSrc
 Set oRe = Nothing

End Function

なお、系列には名前や項目軸ラベルは指定していないことを仮定しています。系列選択時に下記のような内容が出ることを想定しています。

=SERIES(,,Sheet1!$B$50:$B$70,1)

◎質問者からの返答

airplantさん ありがとうございました。

マクロを実行してみて!!です。

言われた通り一気に変わりました。

それよりも此処まで手を掛けて頂いてありがとうございました。

見たことのないマクロが沢山ありすぐには理解できないですが、

少しずつ勉強して行きます。時間を取らせてしまいすみませんでした。

あらがとうございました。


5 ● SALINGER
●50ポイント

ChartObjectから参照情報を取得するのは難しそうなので、グラフの系列名を参照している列のアルファベットにしてしまいます。

(系列名を使っている場合は頭につけるとか工夫すれば実現できると思います。)

系列名の変更は、グラフの右クリックから元のデータ→系列→名前で設定できます。

例えば、系列名1をB。系列名2をFにします。表示させたくない場合はクリアにします。

そして、次のコードをワークシートのchangeイベントに書き込みます。

Private Sub Worksheet_Change(ByVal Target As Range)
 If Target.Row > 1 Or Target.Column > 2 Then Exit Sub
 Dim r As String
 Dim c1, c2 As String
 Dim ch As ChartObject
 Dim minRow As Long
 Dim maxRow As Long
 
 minRow = 50 '最小行を設定
 maxRow = 100 '最大行を設定
 
 If Range("A1").Value >= minRow And Range("A1").Value <= maxRow _
 And Range("B1").Value >= minRow And Range("B1").Value <= maxRow Then
 For Each ch In ActiveSheet.ChartObjects
 ch.Activate
 With ActiveChart
 c1 = .SeriesCollection(1).Name
 c2 = .SeriesCollection(2).Name
 r = c1 & Range("A1").Value & ":" & c1 & Range("B1").Value & "," _
 & c2 & Range("A1").Value & ":" & c2 & Range("B1").Value
 .SetSourceData Source:=ActiveSheet.Range(r), PlotBy:=xlColumns
 .SeriesCollection(1).Name = c1
 .SeriesCollection(2).Name = c2
 End With
 Next
 Else
 MsgBox "正しい範囲を入力してください"
 End If
End Sub

このコードはA1セルとB1セルに50と70のように範囲を書き込むことで、ワークシート上の全てのグラフをその範囲に変更するコードです。

個々のグラフの参照列を変更するには、上記の系列名を変更し、一度A1とB1を変更することで変更できます。

◎質問者からの返答

SALINGER さんありがとうございます。

ここまで手の掛かるマクロとは思ってもみませんでした。

ありいがとうございます。

一つ問題点が発生しまして、教えて頂きたいのですが、“実行時エラー1004”が表示され

.SetSourceData Source:=ActiveSheet.Range(r), PlotBy:=xlColumns

上記↑がエラーとなってしまいました。どの様な原因が考えられるのでしょうか?

関連質問


●質問をもっと探す●



0.人力検索はてなトップ
8.このページを友達に紹介
9.このページの先頭へ
対応機種一覧
お問い合わせ
ヘルプ/お知らせ
ログイン
無料ユーザー登録
はてなトップ