マクロで名前をつけたセル範囲の平均値を求める

質問

Excel2000のマクロを使用しています。
まだまだ初心者なので、少し複雑になるとさっぱりです。
ぜひお力をお貸しください。

例）
A1のセルに200、A2のセルに300、A3のセルに500のデータを持ち、
Range("A1":"A3")に "data"と名前を付けたとします。
ここでAVERAGE関数を使いたいのですが、
ActiveCell.FormulaR1C1 = "=AVERAGE("A1":"A3")"という方法ではなく
"data"で参照して平均値を求めたいのですが、
ActiveCell.FormulaR1C1 = "=AVERAGE(data)としても参照してくれません。

この場合のいい解決法があったら教えてください。
よろしくお願いいたします。

回答No.4

nishi6 · ベストアンサー

マクロ内で行いたいことが、
　セルに『AVERAGE』を使った算式をセットしたいのか、
　計算した値をセットしたいのか判断つきかねるので両方書いてみます。

　　　Ａ　　　Ｂ　　　Ｃ
１　１０
２　２０
３　３０　　２０　　２０
４　４０
５　５０
６　６０　　５０　　５０
７　７０
８　８０
９　９０　　８０　　８０

Ａ列に合計したい範囲(A1:A3)、(A4:A6)、(A7:A9)があって、
マクロではこれに、Data1、Data2、Data3 の範囲名をつけています。
そして、
　=AVERAGE(****)　の算式を　B3、B6、B9
　平均の計算結果値を　　　　C3、B6、C9
に出力しています。

一旦、変数に代入した範囲名を使うわけですから、"=AVERAGE(" & 範囲名 & ")"　のように書く必要があります。

Sub Macro1()
　　Dim i As Integer　　　　　　'カウンタ
　　Dim 範囲名 As String　　　　'平均を計算するセル範囲に付ける範囲名
　　Dim 出力セル As String　　　'出力セル

　　For i = 1 To 3
　　　　範囲名 = "Data" & i　　　'カウンタを使って範囲名(文字列を決める)
　　　　Select Case i
　　　　　　'カウンタにしたがって、範囲名を付けるセル範囲を変える。
　　　　　　'計算結果の出力セルを指定する
　　　　　　Case 1: Range("A1:A3").Name = 範囲名: 出力セル = "B3"
　　　　　　Case 2: Range("A4:A6").Name = 範囲名: 出力セル = "B6"
　　　　　　Case 3: Range("A7:A9").Name = 範囲名: 出力セル = "B9"
　　　　End Select

　　　　'指定した出力セルに算式を書き込む例
　　　　Range(出力セル).Formula = "=AVERAGE(" & 範囲名 & ")"

　　　　'指定した出力セルの右にマクロでの計算結果（値）を書き込む例
　　　　Range(出力セル).Offset(0, 1) = Application.Average(Range(範囲名))
　　Next
End Sub

お礼

どうもありがとうございます。
ばっちり解決しました。
変数に代入して、＆でくくればよかったのですね。
勉強になりました。

本当に感謝！感謝です。

回答No.3

imogasi

＃２です。＃１のご回答のように
Sub test06()
Cells(4, 1).Formula = "= Average(aa)"
End Sub
としてでも、＃２の状況で、答え２がセットできましたので、報告します。

回答No.2

imogasi

A1:A3をaaと名前をつけやって見ました。
a1に１、a2に２、a3に３を入れました。
Sub test04()
MsgBox Application.WorksheetFunction.Average(Range("a1:a3"))
End Sub
Sub test05()
MsgBox Application.WorksheetFunction.Average(Range("aa"))
End Sub
どちらも２になりました。Range("aa")の””などを注意してください。
・VBAではWorksheetFunction.・・になること。
・関数式の=average()などの（）内の表現が変ること。
などの点から、質問のやり方では上手く行かないのでは
ないでしょうか。

補足

早速の回答ありがとうございます。でも、ちょっと質問が間違っていたようです。
すみませんが、以下のことについても教えてください。

範囲をいくつか指定するため、"data" & i(カウンタ)と設定した場合
平均の計算式内でAverageの後の名前指定はどうしたらよいのでしょうか？
Average(data & i)ではできないのですが。

例）
for文のi = 1の時
Range("A1：A3")に対し、マクロ内で名前をdata & iとつけました。
ここで、data1範囲のAverageを計算したいのですが、うまくできません。
どうか教えてください。お願いします。

回答No.1

ryuu001

おかしいですね？

EXCEL2000ですが、問題無くできました。

名前は合っていますか？