[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 回答日時： 2007/09/16 21:10

結構面倒な課題ですね。

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

1035
2035
3035
4035
5035
6035
となりましたが。
何しろ小数例のテストですので、この式や結果を良く吟味をお願いします。

この回答へのお礼

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

お礼日時：2007/09/17 08:57

No.4 回答者： koko88okok 回答日時： 2007/09/16 23:35

AVERAGE関数は空白を計算しないので、A1に項目を設けて、A2以下に同じデータを入力します。

（A8が空白セルになります。）その上で、

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

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

この回答へのお礼

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

お礼日時：2007/09/17 08:59

No.3 回答者： ka_na_de 回答日時： 2007/09/16 22:04

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

お礼日時：2007/09/17 08:58

No.2 回答者： ASIMOV 回答日時： 2007/09/16 21:13

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

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

どうでしょう？

この回答へのお礼

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

お礼日時：2007/09/17 08:57