[EXCEL]AVERAGEでブランクを飛ばした計算式

質問

みなさん、こんばんは。
[EXCEL]AVERAGEでブランクを飛ばした計算式を教えてください。
よろしくお願いします。

まず
A列1～13に
10
20
30
40
50
60
(空白/ブランク)
10
20
30
40
50
60

と数字が入っています。

B列6行に
=IF(A6="","",AVERAGE(A1:A6))と式を入れ、この式をB列13まで延ばすと、
B列6～13は

35

38
36
34
32
30
35

となります。

私の希望は7行目のブランクを無視して数字があるところだけを指定範囲(今回は範囲は6行)計算したいのです。
結果としてB列6～13は

35

35
35
35
35
35
35

となるのが希望です。
B列6、13は、連続していますので、そのままの平均なので35ですが、
他はブランクがありますので、そのブランク1つ分を計算範囲に入れず、
範囲先頭部分をひとつ増やして計算させたいのです。

B列8ならA7を除く、A2～A6,A8の平均(AVERAGE(A2:A6,A8))
B列10ならA7を除く、A4～A6,A8～A10の平均(AVERAGE(A4:A6,A8:A10))

という感じにです。
よろしくお願いします。

回答No.1

imogasi · ベストアンサー

結構面倒な課題ですね。
B6に
=IF(A6="","",AVERAGE(OFFSET(A1,0-COUNTBLANK(A1:A6),0,6+COUNTBLANK(A1:A6),1)))
と入れて、B13まで式を複写する。
結果　A1:B13
10
20
30
40
50
60 35

10 35
20 35
30 35
40 35
50 35
60 35
となりましたが。
何しろ小数例のテストですので、この式や結果を良く吟味をお願いします。

お礼

私が使っているパターンすべてを試しきれてませんが、
数パターンやってみたところ私が希望していた通りの式となっておりました。
無事問題が解決できました！ありがとうございました。

回答No.4

koko88okok

AVERAGE関数は空白を計算しないので、A1に項目を設けて、A2以下に同じデータを入力します。（A8が空白セルになります。）その上で、

A7セルに「=IF(A7="","",AVERAGE(A1:A7))」を入力して、下にオートフィルすれば、ご希望のように表示されます。

ただし、A1セルは文字列であることが条件です。

お礼

7行の間にブランク１つという条件では、スリムな計算式でいいですね！
ご回答ありがとうございました。

回答No.3

ka_na_de

VBAでユーザー定義関数を作ってみました。
以下のコードを標準モジュールに貼り付けたうえで、
B6に
=IF(A6="","",MyAve(A1:A6))
と入れて、B13まで式を複写して下さい。

ブランクが二つ以上あっても、上方向にデータを探して
平均をだします。

'===========標準モジュールに記述================================
Function MyAve(OrgRng As Range)
　On Error GoTo Err
　Dim OrgRngRowsCnt As Long, NewRngRowsCnt As Long
　Dim NewRng As Range

　OrgRngRowsCnt = OrgRng.Rows.Count
　Set NewRng = OrgRng
　Do Until WorksheetFunction.Count(NewRng) = OrgRngRowsCnt
　　　If NewRng.Row = 1 Then GoTo Err
　　　Set NewRng = NewRng.Resize(NewRng.Rows.Count + 1).Offset(-1)
　Loop
　MyAve = WorksheetFunction.Average(NewRng)
　Set NewRng = Nothing
　Exit Function
Err:
　MyAve = "error"
　Set NewRng = Nothing
End Function

お礼

VBA、まだ私は使ったことありませんが、
「ブランクが二つ以上あっても、上方向にデータを探して平均をだします。」
すばらしいですね！時間があるときに挑戦みようという気になりました。
式まで新規に作っていただきありがとうございました。

回答No.2

ASIMOV

ちょっと、違うような気もしますが、一応結果は35になるので

＞B列6行に
＞=IF(A6="","",AVERAGE(A1:A6))と式を入れ、
ここで、７行を行選択して、一端「行削除」します
そして
＞この式をB列13まで延ばすと、
としてから、7行を選択して、「行挿入」で、結果は35に．．．

どうでしょう？

お礼

ご回答ありがとうございます。
ブランクも式によって導き出していますので、削除はできません。
私の説明不足でした、失礼しました。