Excel 2003使用者です。

質問

Excel 2003使用者です。

図のようにB列に不規則な行間隔(セル間隔)で数値が入っています。
その数値の右側C列にB列の直近５つの数値の平均値を算出するには、
C列にどんな関数式を入れたらできるのでしょうか？

おわかりの方、よろしくお願いします。

回答No.1

tom04 · ベストアンサー

こんばんは！
一例です。

データは2行目からあるとします。

C2セルに
=IF(OR(B2="",COUNT($B$2:B2)<5),"",(SUM(INDEX($B$2:B2,MAX(IF($B$2:B2<>"",ROW($A$1:A1)))):INDEX($B$2:B2,LARGE(IF($B$2:B2<>"",ROW($A$1:A1)),5))))/5)
これは配列数式になってしまいますので、この画面からC2セルにコピー＆ペーストしただけではエラーになると思います。
C2セルに貼り付け後、F2キーを押す、またはC2セルをダブルクリック、または数式バー内で一度クリックします。
編集可能になりますので、Shift＋Ctrlキーを押しながらEnterキーで確定！

数式の前後に｛　｝マークが入り配列数式になります。
このC2セルをオートフィルでずぃ～～～！っと下へコピーしてみてください。

希望に近い形にならないでしょうか？

尚、かなり下までコピーするのも大変ですので、
一つの方法として、C列に1列挿入　→　元のC列がD列に移動します。
挿入したC2セルに「1」と入力OK
当方使用のExcel2003の場合ですが
C2セルをアクティブにし　→　メニュー　→　編集　→　フィル　→　連続データの作成　→
「列」を選択「停止値」は適当にこれ以上データはないという数値を入力
（仮に300と入力したとします。）　→　OK
これでC301セルまで連番が表示されます。
先ほどの数式をD2セルにコピー＆ペースト（配列数式です）し、D2セルのフィルハンドルでダブルクリック
これで先ほどのC列の番号が表示されている行すべてに数式がコピーされます。
最後にC列すべてを削除して完了です。

以上、長々と書きましたが
他に良い方法があれば読み流してくださいね。m(__)m

お礼

アドバイスいただいた数式を実際に使ってみました。
結果、Very Good でした。

ありがとうございました。

回答No.4

KURUMITO

Ｃ列に作業列を作って、お求めの答えをＤ列に表示させる方法です。
Ｃ1セルには次の式を入力して下方にオートフィルドラッグします。

=IF(B1="","",COUNT(B$1:B1))

Ｄ1セルには次の式を入力して下方にオートフィルドラッグします。

=IF(OR(C1="",C1<5),"",AVERAGE(INDEX(B:B,MATCH(C1-4,C:C,0)):B1))

お礼

作業列を用いた算出手法としては、スッキリ表示できました。
どれも希望通りの値を表示できました。
ありがとうございました。

回答No.3

noname#204879

セル D1 に式 =COUNT(B$1:B1) を入力
セル C1 に式 =IF(B1="","",IF(D1>4,AVERAGE(OFFSET(B1,,,MATCH(D1-4,D$1:D1,0)-ROW(A1)-1,)),AVERAGE(OFFSET(B1,,,-ROW(),)))) を入力
範囲 C1:D1 を下方にズズーッとドラッグ＆ペースト

お礼

できました！
こちらも希望の値をピタリと算出してくれます。
ありがとうございました。

回答No.2

某HN クロメート(Chromate)（@CoalTar）

C列を作業列として
C1セル 0
C2セル =COUNT($B$2:B2)
下へオートフィル

D2セル =IF(B2="","",IF(C2<5,"",SUM(INDEX(B:B,MATCH(C2-5,C$1:C2)+1):B2)/5))
フィルハンドルダブルクリック

わかりやすさ。。。。微妙
だけど、計算速度は速いと思います。
作業列を使うことによってわかりやすくなるようにアレンジも可能と思われます。

お礼

試してみました。できました。

ありがとうございました。