EXCELL VBA のAVERAGEIFについて

質問

VIAT　office2007　を使用しています。
EXCELL　VBA　で、　AVERAGEIFA関数を利用して、A、B、Cクラスごとに平均点を算出したいのです。

G6:G80 に出席番号が次のように入っています。
C11、A02・・・B21、A08、・・・A21、C12、・・・B28

Ｋ列に点数が入っています。

Ｋ100　にA組の平均点を表示させたいのです。
CELLS(100,11).Value = "=AVERAGEIF($G$6:$G$8０,A*,$K$6:$K$80)"
とすると、正しく計算されますが、$K$6:$K$80 のところを現在セルがある列を自動的に取得して、
平均点を算出させたいのです。

どうしたらよろしいのでしょうか?
お教え願います。

回答No.4

tom04 · ベストアンサー

No.2・3です！

表の配置が具体的に判れば適格なアドバイスができると思うのですが・・・

↓のお礼欄を読んでみると氏名列はG列で
他の列方向には各科目の点数が表示してある訳ですかね？

＞activecell.column　を利用しても列番号が数値で得られ、平均点を算出する範囲を指定できません。何か良い方法はありませんでしょうか?

もしかして↓のような感じをお望みなのでしょうか？

Sub test2()
Dim i, j, k As Long
Dim vl As Variant
Dim str As String
str = Application.InputBox("クラスを入力") '←A～Cの好みの組名を入力
j = ActiveCell.Column
If Selection.Count = 1 Then
For i = 6 To 80
If Cells(i, 7) Like str & "*" Then
vl = vl + Cells(i, j)
k = k + 1
End If
Next i
Cells(100, j) = vl / k
Else
MsgBox "1列のみ選択してください。"
End If
End Sub

※　項目行（科目）があれば、Match関数等で列番号は取得できると思います。

今回も外していたらごめんなさいね。

お礼

理解しづらい質問にお答えいただき有り難うございました。やはり平均点を算出する列を、Inputboxで手入力するしかないようですね。プログラムを参考に作成したいと思います。有り難うございました。

回答No.5

KURUMITO

K列から右の列に点数が入っているのでしたら次のようなマクロにすればよいでしょう。

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
If Target.Column >= 11 And Target.Row >= 6 And Target.Row <= 80 Then
Cells(100, Target.Column) = WorksheetFunction.AverageIf(Range(Range("G6"), Range("G80")), "A*", Range(Cells(6, Target.Column), Cells(80, Target.Column)))
End If
End Sub

お礼

ご回答有り難うございます。取り敢えず　Inputbox関数を使って作成したいと思います。有り難うございました。

回答No.3

tom04

No.2です！
前回のコードは列を間違っていました。

＞Ｋ列に点数が入っています・・・

を見逃して、勝手にH列に点数があるものと鵜呑みをしていました。

No.2のコード内の
＞vl = vl + Cells(i, 8)
を
＞vl = vl + Cells(i, 11)
に変更してください。

どうも失礼しました。m(_ _)m

お礼

ご返答、有り難うございます。この例ですとｋ列を指定していますが、そうではなくて現在セルがある列番号をマクロで取得して、平均点を取得したいのです。activecell.column　を利用しても列番号が数値で得られ、平均点を算出する範囲を指定できません。何か良い方法はありませんでしょうか?

回答No.2

tom04

こんばんは！
ご希望の方法のVBAとは違うと思いますが・・・
一例です。

Sub test()
Dim i, k As Long
Dim vl As Variant
Dim str As String
str = Application.InputBox("クラスを入力") '←A～Cの好みの組名を入力
For i = 6 To 80
If Cells(i, 7) Like str & "*" Then
vl = vl + Cells(i, 8)
k = k + 1
End If
Next i
Cells(100, 11) = vl / k
End Sub

※　AVERAGE関数ではなく単純に、検索組の人の点数の合計を人数で割っているだけです。

他に良い方法があればごめんなさいね。m(_ _)m

回答No.1

imogasi

＞AVERAGEIFA関数
エクセルに、こんな関数ありますか。AVERAGEIF関数、AVERAGEIFS関数のどちらかな。質問なのだから、正確に書くこと。
ーー
又データ実例を質問にあげて質問するものだ。少人数の例にして。
ーー
セルに式をVBAで埋め込むようなのは余り意味がない。
関数で余分目にセル範囲を取っておけば、空白セルはAVERAGE関数では旨くやってくれる。
ーー
平均の範囲は上行は固定されていて、下の行がデータの有無で場合によって動く場合は、
Sub test01()
d = Range("A65536").End(xlUp).Row
MsgBox d
Worksheets("Sheet2").Cells(12, "A") = Application.WorksheetFunction.Average(Worksheets("Sheet2").Range("A2:A" & d))
End Sub
のようなのを参考に勉強のこと。
Rangeが要る
Worksheets("Sheet2").が要る
Application.WorksheetFunction　　が要る
など経験が要る。
ーー
この例
1　＜－A2
2
3
4
5

3　＜－A12

お礼

ご回答、有り難うございます。質問の仕方が適切ではなく、失礼しました。関数はAVERAGEIF関数の間違いでした。

ご回答の例ではA列の平均を出していますが、そうではなくて
現在カーソルがある列をVBAで取得して、計算式をセルに代入したいのです。
何か良い解決方法はありませんでしょうか?