日付をさかのぼる形で、データがあるセルのうち直近の３個のセルの平均を求める

添付画像に示したように、指定した日付から遡るようにしてデータがあるセルのうち、直近の３つ（Ｎ個）のセルの平均値を求めたいのですが、どんな関数が使えそうなのか？、変数を使うことになるのか？皆目見当がつきません。厚かましいお願いですが、どなたかお教え頂けませんでしょうか？

No.4 回答者： goomania 回答日時： 2021/05/20 20:08

すでに3名の回答者が回答しておられて、No.1さんにより、考え方も丁寧に解説されていますので、さまざまな解決方法があると思います。

No.2さんが配列数式を使用しない数式をお示しになっていますが、配列数式の回答、配列数式でない回答の両方をお示しします。
ご質問者の説明に基づき、添付画像のようなデータがあるとします。
※データがない日は「---」と入力されているものとします。

まず、配列数式を使用するとすれば、SUM関数とIF関数を組合わせた数式として、B10に、

{=SUM(IF(($A$2:$A$9>=LARGE(($A$2:$A$9)*ISNUMBER(B2:B9),3)),B2:B9,0))/3}

<{}を除いた数式を入力し、Ctrl+Shift+Enterで確定させる>という数式が考えられます。これを、右方向へオートフィル等でコピーします。この数式が最も短いかも知れません。
数式中に「3」が2回登場しますが、これは直近3回の「3」ですので、直近〇回の回数を記述するものとします。

さらに、IF関数を使用せず、SUM関数のみを使用した配列数式として、B10に以下の数式も考えられます。

{=SUM(($A$2:$A$9>=LARGE(($A$2:$A$9)*ISNUMBER(B$2:B$9),3))*SUBSTITUTE(B$2:B$9,"-","0"))/3}

一方、配列数式を使用しない方式としてSUMPRODUCT関数を用いた数式として、B10に

=SUMPRODUCT(($A$2:$A$9>=LARGE(INDEX(($A$2:$A$9)*ISNUMBER(B2:B9),0),3))*SUBSTITUTE(B2:B9,"-","0"))/3

という数式も考えられます。

No.1さんも「エラー対策は何もしていません。 」とおっしゃていますし、No.2さんの回答、No.3さんの回答も「日付が昇順でなかったら」という対策はとられていないようです。
上記の3つの数式は一応「日付が昇順でなくても」直近3回の平均を計算するようにしたつもりです。

この回答へのお礼

銀鱗さん、fujillinさん、mike_gさん、goomaniaさん
丁寧なご回答有難うございました。私は農作業でバタバタしていて、質問をしたまま、ゆっくりＰＣに向かう時間が取れず、皆様へのお礼が遅くなり大変申し訳ありませんでした。これから皆様から頂いたご回答をじっくり読ませて頂き勉強したいと思っています。お陰さまで、解決方法がありそうだと分かり大変うれしく思っています。遅まきながら、ひとまずお礼を申し上げます。

お礼日時：2021/06/03 02:43

No.3 回答者： mike_g 回答日時： 2021/05/20 16:34

添付図参照(Excel ２０１９)

次式を入力したセル B12 を右方＆下方にオートフィル
=INDEX(B$2:B$9,LARGE(IF(ISNUMBER(B$2:B$9),ROW(B$2:B$9)),$A12)-1)
【お断わり】上式は必ず配列(CSE)数式として入力のこと
式 =AVERAGE(B12:B14) を入力したセル B10 を右方にオートフィル
オ・シ・マ・イ

No.2 回答者： fujillin 回答日時： 2021/05/20 13:43

こんにちは

既に模範解答が出ていますけれど・・・

＞直近の３つ（Ｎ個）のセルの平均値～
個数を可変にしたいのかもと想像しました。
こんな感じではいかがでしょうか？
※ 以下は3つの場合。（「3」の部分を変えれば対象個数が変わります）
=AVERAGE(OFFSET(B1,LARGE(INDEX(ISNUMBER(B2:B9)*ROW(B2:B9),),3)-1,,10-LARGE(INDEX(ISNUMBER(B2:B9)*ROW(B2:B9),),3)))

※ 見ればわかるように、作業セルを1つ使えば、より簡単な式になります。
※ 数値が指定数（＝３つ（Ｎ個））存在しない場合は、エラーになりますのでご注意。
（その場合には存在する値の平均でよいのなら、式の追加で可能です。）

No.1 回答者： 銀鱗 回答日時： 2021/05/20 04:06

=AVERAGE(INDEX(B2:B9,MATCH(LARGE(IF(B2:B9="",0,ROW(B2:B9)),1),IF(B2:B9="",0,ROW(B2:B9)),0)),INDEX(B2:B9,MATCH(LARGE(IF(B2:B9="",0,ROW(B2:B9)),2),IF(B2:B9="",0,ROW(B2:B9)),0)),INDEX(B2:B9,MATCH(LARGE(IF(B2:B9="",0,ROW(B2:B9)),3),IF(B2:B9="",0,ROW(B2:B9)),0)))

こんなかな。
もっとスマートなやり方があるかもしれませんが、
説明をするなら基本を押さえる必要があるので、
この方法を採用しました。

Ctrl+Shift+Enterで数式を確定させ【配列数式】にします。
確定後に数式全体が「{=～}」のように{}で囲われたらOK。
Enterだけではエラーになります。

・・・
とても面倒なことをやってます。
ですので、このくらい長い数式になる。

・値が入力されているセルの行番号を取得。
・その番号の上位３つに対応するセルを取得。
・平均を取る。

この手順を行っていますが、読み取れますか？
読み取れないのであれば、諦めてください。

AVERAGE関数に3つの値を与えて平均を出しています。
それぞれの値はINDEX関数で範囲の中の下から1番目2番目3番目のデータを拾い出しています。
INDEX関数はMATCH関数で得られた値を使って配列から値を取得します。
MATCH関数はLARGE関数で1番目、2番目、3番目に大きい値が配列の何番目にあるかを返します。
LARGE関数は配列の中の指定された順位の値を返します。
IF関数はデータが無ければ0を、データがあればROW関数の値を配列の値として返します。
※ このROW関数が【配列数式】で、配列数式を使わない場合は作業列になります。

=AVERAGE(
　　　INDEX(
　　　　　B2:B9
　　　　　,MATCH(
　　　　　　　LARGE(IF(B2:B9="",0,ROW(B2:B9)),1)
　　　　　　　,IF(B2:B9="",0,ROW(B2:B9))
　　　　　,0))
　　　,INDEX(
　　　　　B2:B9
　　　　　,MATCH(
　　　　　　　LARGE(IF(B2:B9="",0,ROW(B2:B9)),2)
　　　　　　　,IF(B2:B9="",0,ROW(B2:B9))
　　　　　,0))
　　　,INDEX(
　　　　　B2:B9
　　　　　,MATCH(
　　　　　　　LARGE(IF(B2:B9="",0,ROW(B2:B9)),3)
　　　　　　　,IF(B2:B9="",0,ROW(B2:B9))
　　　　　,0)))

質問ではデータが無い箇所を「---」としているようですので
　IF(B2:B9="",0,ROW(B2:B9))
を
　IF(B2:B9="---",0,ROW(B2:B9))
としても良いでしょう。

なお、エラー対策は何もしていません。