空白のセルを除いた指定個数の平均

質問

お世話になります。
初めての質問なので失礼がありましたらご容赦ください。

エクセルで空白のセルを除いた指定個数の平均を取りたいと考えております。例えば

　　　Ａ　　 Ｂ　　Ｃ
１　8月1日　 1
２　8月2日　 2
３　8月3日　 2
４　8月4日　
５　8月5日　 2
６　8月6日　 2
７　8月7日　 4
８　8月8日　
９　8月9日　 2

とあるとします。
Ｃ列の位置にＢ列の過去4日分の空白を除いた平均値を
表示したいのです。
ですので計算結果の例としましては
Ｃ９が2.5　Ｃ７が2.5　Ｃ６が2　となります。
空白のセルに規則性はありません。

どなたか教えて頂けませんか？

回答No.3

Wendy02 · ベストアンサー

こんにちは。

株価グラフには、そういう考え方があったような気がしますね。

ということは、C5 が始まりでよろしいですね。

=SUM(INDEX($B$1:B5,LARGE(INDEX(($B$1:B5<>"")*ROW($B$1:B5),,),4),):B5)/4
これで、下にドラッグ・コピーすれば出来ると思います。

お礼

出来ました！
確かに株価の整理に使おうと思っていたので
大変助かりました。

どうもありがとうございます！

回答No.2

imogasi

勘違いかも知れないが、質問を読んで一瞬、関数では難しいと思った。
それでユーザー関数を作った。
標準モジュールに
Function av(a)
d = a.Row
c = a.Column
k = 0
For i = d To 1 Step -1
If Cells(i, c) = "" Then
Else
k = k + 1
s = s + Cells(i, c)
If k = 4 Then
av = s / 4
Exit Function
End If
End If
Next
av = ""
End Function
と入れる。
ーー
例データ
8月1日 1
8月2日 2
8月3日 3
8月4日
8月5日 2 2
8月6日 2 2.25
8月7日 4 2.75
8月8日 2.75
8月9日 2 2.5
シートのC1に
=av(B1)
と入れて式を下方向に複写する。
結果
上記データ例のC列。
ーー
８月８日は空白の方が良いのかな？
Function avb(a)
If a = "" Then
avb = ""
Exit Function
Else
d = a.Row
c = a.Column
k = 0
For i = d To 1 Step -1
If Cells(i, c) = "" Then
Else
k = k + 1
s = s + Cells(i, c)
If k = 4 Then
avb = s / 4
Exit Function
End If
End If
Next i
avb = ""
Exit Function
End If
End Function
式は
=AVb(B1)

お礼

出来ました！ありがとうございます！

ちなみに　例は4日平均ですが
もしも5日平均にするならば
If k = 4 Then
av = s / 4
の4をそれぞれ5に変えれば良いわけですよね？

本当にありがとうございました！！

回答No.1

kirara77

初めまして。
Ｃ列の各行に設定する日付判断は、Ａ列の日付で過去４日間のＢ列セルを選択するか、単純に相対行数で行うかは
お好きにされれば良いと思いますが、
空白セルに関して言えば単純に４日分のＢ列セルをAVERAGE関数を用いて算出すれば良いですよ。
空白セルは計算では無視されますから。

お礼

質問の内容が悪くて申し訳ございません。

単純に過去4日分ではなく、「Ｂ列に数値の入力されている日付の4日分の平均」です。

つまりＣ9には8月5,6,7,9日の平均値が入るということです。