直近の５個の平均を求めたい

３０の列でいくつかのデータが入力されていて、最終データは行ごとに違う場合、行ごとに最終データを基準として前5個の平均を求めたい

質問者からの補足コメント

• mike_gさん回答ありがとうございます。
  補足させていただきますと、全セルにデータがあるのではなく空白セルも混在しています。
  最終データは右端列です。
  　データの入力されている右端列の位置を自動的に求めてそこからデータの入力されている5個分の平均を求めたいです。

  No.1の回答に寄せられた補足コメントです。 補足日時：2016/11/06 19:54

No.4 回答者： CoalTar 回答日時： 2016/11/07 17:03

捕捉より、

B1セルから右へデータが入力されているとして
A1セルに
=IF(COUNT(B1:Z1)<5,"",SUM(INDEX(B1:Z1,LARGE(IF(ISNUMBER(B1:Z1),COLUMN(B1:Z1)-1),5)):INDEX(1:1,MATCH(9^9,1:1)))/5)
[Ctrl]+[Shift]＋[Enter]配列数式、{}で囲まれる。

No.3 ベストアンサー 回答者： tom04 回答日時： 2016/11/06 20:41

こんばんは！

手っ取り早くユーザー定義関数（この質問のためだけの関数）を作ってみてはどうでしょうか？

Alt＋F11キー　→　メニュー　→　挿入　→　標準モジュール　→　VBE画面のカーソルが点滅しているところに
↓のコードをコピー＆ペースト　→　Excel画面の戻り（VBE画面を閉じて）通常のワークシート関数と同じ使い方をします。

Function heikin(myRng As Range) 'この行から//
Dim k As Long, cnt As Long, mySum As Variant
For k = myRng.Count To 1 Step -1
If myRng(k) <> "" And IsNumeric(myRng(k)) Then
cnt = cnt + 1
mySum = mySum + myRng(k)
If cnt = 5 Then Exit For
End If
Next k
heikin = mySum / cnt
End Function 'この行まで//

仮にA1～AD1セルの入力済みセルの最後5セルの平均を表示したい場合は
=heikin(A1:AD1)
という数式を入れます。

※　通常のワークシート関数と同じ使い方ができるといいましたので、関数の引数ダイアログボックスも表示可能です。
数式バーの左側の「fx」のアイコンをクリック　→　「関数の分類」で「ユーザー定義」を選択　→　「MyRng」の窓でセルの範囲選択！
これで大丈夫です。

※　エラー処理はしていませんので
IF関数との併用でエラー処理を行ってください。

※　データ数が5より少ない場合は
その平均が表示されます。m(_ _)m

この回答へのお礼

tom04さん
ご回答ありがとうございました。
なかなかパソコンを開く時間が持てなくてお礼の返信が遅れましたことを
お許しください。
　まだ、すべて順調に動くと云うところまで至っておりませんが、一筋の光明が見えてきたような気がします。
　本当にありがとうございました。

お礼日時：2016/11/10 20:06

No.2 回答者： ママチャリ 回答日時： 2016/11/06 09:34

まずは、ざっくり回答です。

B1~AE1の30セルに数値が入力されていて、A1セルに平均を表示することを前提とします。

【A1セル】=AVERAGE(OFFSET(B1:AE1,0,COUNTA(B1:AE1)-5,1,5))

ただし、次のような考慮が必要かもしれません。michirukunさんの要件で必要なものはありますか？
①入力が無い行がある。または、入力が5セル未満である（入力が無い場合はエラー？5個未満はあるだけの平均？）。
➁所々に空白があり、空白セルは平均の対象外としたい（空白を除く直近5個の平均を求めたい）。
③数値でないセルは、上記③と同様に対象外としたい。
上記のような考慮が必要な場合、数式では難しいかもしれません（要件にもよりますが…）。

No.1 回答者： mike_g 回答日時： 2016/11/06 07:46

》 ３０の列で…

例えば、Ａ列から、
連続して、ＡＤ列までとか？
１行おきにＢＨ列までとか？

》 …いくつかのデータが入力されていて…
各列の全セルにデータがあるのでなく、空白セルも混在していると？

》 …最終データは行ごとに違う…
「最終」って右端列のこと？それとも下端行のこと？

イメージが沸かないので、とにかく、サンプルデータを示してくださいナ！