【EXCEL】表とグラフの連動、グラフのラベル設定

質問

EXCEL初心者です。関数やマクロ等全く分かりません…。
使用しているバージョンは２０１０です。

・EXCELのSheet１に表( 添付したもの )、Sheet２に表の内容を元に散布図を作成したいです。

・具体的には、
　表の「情報１」＝グラフのｙ軸、　
　表の「情報２」＝グラフのｘ軸、　
　表の「番号」＝ｘとｙの交点
　としたいです。

　例えば、4行目のデータは以下のようにさせたいです。
　ｘ＝３００、 ｙ＝１００、 ３００と１００の交わった所に１と入れたいです。
　これを5行目、6行目…と行いたいです。

・また、表の内容を変えると、自動的にグラフにも変更内容が反映されるようにしたいです。

一応、自分で調べて行ったところ、グラフのラベル設定を↓こちらのサイトで
http://www4.synapse.ne.jp/yone/excel2010/excel2010_graph_sanpu.html
行ってみたのですが、VBAにそのままサンプルコードを貼り付けたところ、
ラベルが、今回設定したい「番号」ではなく「名前」になってしまいます・・・。
ちなみに、貼り付けたサンプルコードは↓こちらです。
Sub AttachLabelsToPoints()

'Dimension variables.
Dim Counter As Integer, ChartName As String, xVals As String

' Disable screen updating while the subroutine is run.
Application.ScreenUpdating = False

'Store the formula for the first series in "xVals".
xVals = ActiveChart.SeriesCollection(1).Formula

'Extract the range for the data from xVals.
xVals = Mid(xVals, InStr(InStr(xVals, ","), xVals, _
Mid(Left(xVals, InStr(xVals, "!") - 1), 9)))
xVals = Left(xVals, InStr(InStr(xVals, "!"), xVals, ",") - 1)
Do While Left(xVals, 1) = ","
xVals = Mid(xVals, 2)
Loop

'Attach a label to each data point in the chart.
For Counter = 1 To Range(xVals).Cells.Count
ActiveChart.SeriesCollection(1).Points(Counter).HasDataLabel = _
True
ActiveChart.SeriesCollection(1).Points(Counter).DataLabel.Text = _
Range(xVals).Cells(Counter, 1).Offset(0, -1).Value
Next Counter

End Sub

これを貼り付けると、ラベルが「番号」ではなく「名前」になってしまいました。
上記サイトに
「 　また、以下のようなコードでもラベルを付けることができました。 グラフをアクティブにして
　VBA(マクロ）を実行します。　」
とあったのですが、当方マクロやEXCELはど素人で、
「　グラフをアクティブ？？　」状態で全くわかりませんでした…。

また、表の内容を書き換えたときに、グラフに自動的に反映されません…。

どうすれば求めているグラフのラベル設定ができ、また表の内容の書き換えたときグラフが連動
するようになってくれるのでしょうか・・・？？

回答No.5

xls88 · ベストアンサー

＞No.3 この回答への補足
＞表をSheet１に、グラフをSheet２に書くと、グラフにラベルが表示されません・・・
＞表もグラフも同じSheet内だと、表示されます。
グラフは正しく表示プロットされていると理解してOKですか？

グラフは正しく表示されているとして
多分Sheet２がアクティブ（画面表示されている）状態だと思います。
No.3で書きましたが複数のシートを跨ぐ処理の場合注意が必要です。
シート名を付与して住所を明らかにしなければいけません。
シート名がない場合はActivesheetということになります。

No.2のコードは同一シートが前提になっています。
リンク式で
ActiveSheet.Name
と書いている処を確実にデータ表のシート名が入るようにしなければいけません。
（例2）
ActiveChart.SeriesCollection(1).Points(Counter).DataLabel.Formula = _
"=Sheet1!" & Range(xVals).Cells(Counter, 1).Offset(0, -2).Address
というようにデータ表のシート名を決め打ちしてみてください。

ところで本文で提示されたコードでは
Range(xVals)でシート名を含めデータ表のセル番地が解っています。
ということは
Range(xVals).Parent.Name
でデータ表のシート名が採れるはずです。
これを使えばあらかじめシート名が解っていなくても対処できます。

＞また、ワークシートのイベントプロシージャについてですが、
＞表の書かれたSheet1、グラフの書かれたSheet２共に「change」で試してみましたが、
＞表の行を追加したときに追随されませんでした。
＞どこか間違っているのでしょうか？？
確かなことはコードを拝見しないことには云えませんが、、、
多分
SeriesCollection(1)のFormula
あるいは
XValuesプロパティで「Xの値」の範囲
Valuesプロパティで「Yの値」の範囲
を書き換えなければいけません。

いずれにしましてもイベントプロシージャの件は次のステップとして
まずは本題をクリアすることに集中した方がよいと思います。

因みにVBAを使わなくても方法はあります。
＜参考＞
グラフの参照範囲を自動的に変更する
http://officetanaka.net/excel/function/tips/tips71.htm

＞いただいたURLをじっくり勉強してみます。。
殆どがコード作成依頼が多い中頼もしいお言葉です。
応援します。

デバッグ方法を知ってた方が良いです。
＜参考＞
デバッグ方法
http://www.239-programing.com/excel-vba/basic/basic021.html

お礼

お礼が遅くなり申し訳ございません。

あれから、いただいたURLなどでお勉強しました。

下地がなかったので、お答えいただくのも大変だったかと思います。

本当に質問以外の役立つことまで教えてくださってありがとうございました！

まだまだですがご回答の内容が少し理解できるようになりました！

お答えいただいたお二方をBAとしたいのですが…質問以外のことも教えてくださったのでBAとさせていただきました。

MackyNO1さまも実際に検証してくださったりと本当に感謝しております。ありがとうございました！

またおかしな質問をしていたらどうかご容赦くださいませ^^;

回答No.4

MackyNo1

>の、「'ラベルに使うセル範囲を指定します。」のところは消してよいのでしょうか？

アポストロフィー以降の部分はコメントなのでプログラムの対象外のデータですので消しても消さないでも関係ありません（コードを説明しているだけです）。

＞それとも、今回の表の「番号」の１から１０までの範囲のセルを入力すればよいですか？？

実際にラベルにしたい数字が書かれているセル範囲（シート名＝Sheet2?の部分を含めて）を指定すればOKです（こちらでも検証して問題ないことを確認しています）。

お礼

あっ！できました！
ありがとうございます、検証までしていただき本当に感謝です。

このやりかたのほうが私のような初心者には分かりやすいですね！
ありがとうございました！

回答No.3

xls88

＞No.2 この回答への補足
＞XとYが入れ替わっていた場合はどう変わるのでしょうか？？
見た目軸目盛が変わるだけです。
どちらをXにするかYにするかはご自分に決定権があります。

因みにSeriesCollection(1)の
XValuesプロパティで「Xの値」
Valuesプロパティで「Yの値」
を個別に設定することができます。

＞表の行を追加したときには追随されなかったのですが、それは可能ですか？？
＞表の「番号」の１１、１２…と増やして言ったときグラフも追随して・・・・・
可能です。
ワークシートのイベントプロシージャを使うことになります。
＜参考＞
ワークシートのイベントプロシージャ
http://excelvba.pc-users.net/fol3/3_6.html

＞また、グラフと表が別のSheetにあったとき、コードの書き方が変わったりはしますか？？
基本変わりはありません。
シート名を付けて書くようにすれば大丈夫です。

補足

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

またつまづいてしまいました。
NO.２でご回答いただいた↓でやってみたところ、
ActiveChart.SeriesCollection(1).Points(Counter).DataLabel.Formula = _
"=" & ActiveSheet.Name & "!" & Range(xVals).Cells(Counter, 1).Offset(0, -2).Address

表をSheet１に、グラフをSheet２に書くと、グラフにラベルが表示されません・・・
表もグラフも同じSheet内だと、表示されます。

また、ワークシートのイベントプロシージャについてですが、
表の書かれたSheet1、グラフの書かれたSheet２共に「change」で試してみましたが、
表の行を追加したときに追随されませんでした。
どこか間違っているのでしょうか？？
いただいたURLをじっくり勉強してみます。。

回答No.2

xls88

＞・具体的には、
＞ 　表の「情報１」＝グラフのｙ軸、　
＞ 　表の「情報２」＝グラフのｘ軸、　
＞ 　表の「番号」＝ｘとｙの交点
＞ 　としたいです。
XとYが入れ替わっているのでは？
現状のままでOKなら
ActiveChart.SeriesCollection(1).Points(Counter).DataLabel.Text = _
Range(xVals).Cells(Counter, 1).Offset(0, -1).Value
の列オフセット量を-2にして試してください。
ActiveChart.SeriesCollection(1).Points(Counter).DataLabel.Text = _
Range(xVals).Cells(Counter, 1).Offset(0, -2).Value

ただし値を書き込むだけですから元データの変更には追随しません。
追随させるには
Range(xVals)がアクティブシートにあるとするなら
ActiveChart.SeriesCollection(1).Points(Counter).DataLabel.Formula = _
"=" & ActiveSheet.Name & "!" & Range(xVals).Cells(Counter, 1).Offset(0, -2).Address
というようにFormulaでセルとリンクさせます。

補足

出来ました！表の「情報１」「情報２」の数字を変えても追随されていてすごいです！
本当にありがとうございます！！

＞XとYが入れ替わっているのでは？
実は、実際のEXCELのデータは別の場所にあって見られないので、
確かこうだったという記憶で書かせていただきました、申し訳ありません。
XとYが入れ替わっていた場合はどう変わるのでしょうか？？

ご迷惑ついでに、補足質問なのですが…。

表の行を追加したときには追随されなかったのですが、それは可能ですか？？
表の「番号」の１１、１２…と増やして言ったときグラフも追随してくれるようになればベストなのですが…。

また、グラフと表が別のSheetにあったとき、コードの書き方が変わったりはしますか？？
いま表と別のシートにグラフを作成しようとしたところ、やり方が悪いのかグラフが作れなかったので、
まず表と同じシートにグラフを書いて、コードを打ち込んでグラフのラベルなどを作って…
そのあとに別のシートにグラフをコピーするという
何だか頭の悪いことをしています…。

回答No.1

MackyNo1

＞また、以下のようなコードでもラベルを付けることができました。 グラフをアクティブにしてVBA(マクロ）を実行します。
とあったのですが、当方マクロやEXCELはど素人で、「　グラフをアクティブ？？　」状態で全くわかりませんでした…。

こちらのコードの方が初心者にはわかりよいですね。
グラフをアクティブとは、グラフエリアをクリックしてグラフを選択状態にすることです（リボンにグラフツールのタブが出ます）。

実際に適用する場合は、提示されているコードの以下の部分を、数字が書いてあるシート名とその範囲に変更することになります。

D_Label = Sheets("Sheet1").Range("B2:B9").Value

補足

早速ありがとうございます！
やはりアクティブはそういう意味だったのですね…。お恥ずかしいです。

>実際に適用する場合は、提示されているコードの以下の部分を、数字が書いてあるシート名とその範囲に変更>することになります。

>D_Label = Sheets("Sheet1").Range("B2:B9").Value

これなのですが、
おっしゃっていただいたように、
D_Label = Sheets("Sheet1").Range("B2:B9").Value
を、「番号」の列の数字のセル範囲に変えて、
D_Label = Sheets("Sheet1").Range("A4:A13").Value
としましたが、何度か試したのですが思うとおりになりません…。

Sub test()
　　Dim i As Integer
　　Dim D_Label As Variant
　　　　'ラベルに使うセル範囲を指定します。
　　　　D_Label = Sheets("Sheet1").Range("B2:B9").Value
　　　　With ActiveChart.SeriesCollection(1)
　　　　　　.ApplyDataLabels
　　　　　　For i = 1 To .Points.Count
　　　　　　　　.Points(i).DataLabel.Text = D_Label(i, 1)
　　　　　　Next i
　　　　End With
End Sub
の、「'ラベルに使うセル範囲を指定します。」のところは消してよいのでしょうか？
それとも、今回の表の「番号」の１から１０までの範囲のセルを入力すればよいですか？？
(↑試してみたとき、添付の表ではわからないようになっていますが「番号」の１から１０に対応するセル
「 A4:A13 」と入力しました　)
両方試してみたのですが、毎回、ラベルの一箇所だけがなぜか別のデータになります…。
具体的には、「 番号 」の１０のところが７０となります…。

あと、これだと表の書き換え時のグラフとの連動はできないですよね？？

うーん、わからないことだらけですみません。