VBAでグラフをつくろうと思っています

質問

VBA初心者です。今回VBAでグラフを作ろうと思っています。皆様力をお貸しください。

今回作ろうと思っているのは自分の点数がクラスの中でどの位置（偏差値はどの程度か）にいるのか、視覚的に把握するようなものです。イメージ図を添付しておきます。
○の部分が自分の所属しているところで、σが標準偏差になります。

仮に40名の点数と偏差値が計算してあるとして、図のような点数や偏差値に対応したグラフを作成することは可能でしょうか？
また、点数を入力する画面を設けた場合（これはできました）、自分の点数を入力すればその点数に応じた場所に○をプロットすることはできますでしょうか？

回答よろしくお願いいたします。

回答No.5

mitarashi · ベストアンサー

#4です。たびたび済みません。
正規化標準偏差という表現は正しくありませんでした。
正規化した値というべきでしょう。計算方法は#1さんが回答されています。
下記で復習させていただきました。
http://detail.chiebukuro.yahoo.co.jp/qa/question_detail/q1422230404

コードの方ですが、最後にワークシートに移すとサイズ調整が面倒なので、最初にワークシートに移してからサイズを設定するには下記の様に変更すれば良いです。これに伴い、データラベルの移動値などを微調整する必要が出てきます。（変数を増やしているので宣言も必要になります）
Set sh = ThisWorkbook.Worksheets(2)
'グラフの作成（データ系列は後で設定）
chartObjCount = sh.ChartObjects.Count
Set myChart = Charts.Add
myChart.Location Where:=xlLocationAsObject, Name:=sh.Name
With sh.ChartObjects(chartObjCount + 1)
Set myChart = .Chart
.Width = 400
.Height = 250
End With
myChart.ChartType = xlXYScatter

お礼

連投での回答ありがとうございます。
説明とプログラムつきでとてもわかりやすかったです！

#１さんとあわせて、ＶＢＡへ取り組まさせていただきます！

また、質問することがあたらよろしくお願いします。

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

回答No.4

mitarashi

#2,#3です。乗りかかった船でVBA化してしまいました。正統的なグラフ作成コードではないと思いますが、ご参考まで。xl2010で試しています。マーカーの色を変える部分は2003等では動かないと思いますが、長くなってしまいますので（言い訳）、#2のリンク先をご参考に修正願います。

'グラフのサイズをデフォルトでやっているので、データラベル位置の再現性のためには指定すべきですが手抜きです。
Sub test()
Dim sh As Worksheet
Dim myChart As Chart
Dim xValueRng1 As Range, xValueRng2 As Range, yValueRng As Range
Dim i As Long

Set sh = ThisWorkbook.Worksheets(2)
Set myChart = Charts.Add
myChart.ChartType = xlXYScatter
With sh
Set xValueRng1 = Range(.Range("D4"), .Range("D" & .Rows.Count).End(xlUp))
Set xValueRng2 = Range(.Range("E4"), .Range("E" & .Rows.Count).End(xlUp))
Set yValueRng = Range(.Range("C4"), .Range("C" & .Rows.Count).End(xlUp))
End With
With myChart
.SeriesCollection.NewSeries
.SeriesCollection(1).XValues = xValueRng1
.SeriesCollection(1).Values = yValueRng
.SeriesCollection(1).Name = xValueRng1.Cells(0)
.SeriesCollection.NewSeries
.SeriesCollection(2).XValues = xValueRng2
.SeriesCollection(2).Values = yValueRng
.SeriesCollection(2).Name = xValueRng2.Cells(0)
.SeriesCollection(2).MarkerStyle = -4142
.HasLegend = False
'データラベル移動の余地を準備
.PlotArea.Width = .PlotArea.Width - 40
.PlotArea.Left = .PlotArea.Left + 40
End With
With myChart.Axes(xlCategory)
.MinimumScale = -3
.MaximumScale = 3
.MajorUnit = 1
.HasMinorGridlines = True
.MinorUnit = 1
.TickLabelPosition = xlHigh
.TickLabels.NumberFormatLocal = "0""σ"""
End With
With myChart.Axes(xlValue)
.MinimumScale = 1
.MaximumScale = xValueRng2.Cells.Count
.MajorUnit = 1
.HasMinorGridlines = True
.MinorUnit = 1
.TickLabelPosition = xlLow
.TickLabels.NumberFormatLocal = """"""
End With
'データラベルの位置の調整は微調整が必要かも
For i = 1 To yValueRng.Rows.Count
With myChart.SeriesCollection(2).Points(i)
myChart.SeriesCollection(1).Points(i).Format.Fill.ForeColor.RGB = yValueRng.Cells(i).Offset(0, -1).Interior.Color
.HasDataLabel = True
.DataLabel.Left = .DataLabel.Left - 60
.DataLabel.Text = yValueRng.Cells(i).Offset(0, -1).Value
End With
Next i
myChart.Location Where:=xlLocationAsObject, Name:=sh.Name
End Sub

回答No.3

mitarashi

#2です。
B列の科目名とグラフのプロットが食い違っていたので修正しました。失礼いたしました。
目盛り数字を消す書式設定は、セル側でなく、グラフ側で行う方が良いので変更しました。
今度は完成形の図を添付いたしました。

回答No.2

mitarashi

暇に飽かせてやってみました。
VBAは一切使用せず、グラフの機能と根気だけでやっております。
理解の助けになる様に、添付図はあえて途中段階で止めてあります。
要するに、データラベル表示用の系列を１個加えて、点毎にデータラベルを変更し、位置をY軸の左に移動しています。ラベル表示用の系列のマーカーは当然非表示にします。
本来のY軸の数字は、書式を""にする事で非表示にしています。
散布図の点の色も人力でコツコツと変更しています。
VBAで楽にやりたければ、下記#3、#4がご参考になるかもしれません。
http://okwave.jp/qa/q7524408.html

以上ご参考まで。

回答No.1

noname#192382

図の向きがどうしても横になりますが、手動で出来ました。
もとのdataから平均値を差し引き、それを標準偏差で割った値を散布図にプロットすればよいわけです。
VBAでもできると思います。

お礼

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

散布図にプロットすればうまい具合に出来上がるのですね。
ＶＢＡでも試してみたいと思います。

回答ありがとうございました！