皆様、御力をお貸し下さい。。。 スプレッドシートで関数を組みたいのですが、上手く組めません。 ▼抽出

皆様、御力をお貸し下さい。。。

スプレッドシートで関数を組みたいのですが、上手く組めません。

▼抽出したい数値
添付の表をもとに0を省いた直近3日間の平均値を算出したいです。。。
※1セルで完結する数式でおねがいしたいです。

宜しくお願い致します。

No.3 ベストアンサー 回答者： goomania 回答日時： 2023/02/05 19:42

添付画像をご覧ください。

EXCELだと使えないRANK.EQ関数の引数に配列が使えるので、結果をD2セルに表示するとすると、

=SUMPRODUCT((RANK.EQ((A2:A8)*(B2:B8>0),($A$2:$A$8)*(B2:B8>0),0)<=3)*(B2:B8))/3

という数式を記述しています。

これで、ご希望の結果が得られると思います。

数式の解説
【1】(A2:A8)*(B2:B8>0)の部分は数値が0出ない日付の一覧を作成しています
【2】RANK.EQ(･･･････,0)<=3)という関数を使って数値が0出ない日付一覧を大きい順に並べて3位まで求めます
【3】これで、「直近3日」が求められるので、SUMPRODUCT関数を使って、その日付に該当する数値の合計を求めます
【4】その直近3日の「数値」合計を3で割れば求める結果が得られます

No.2 回答者： fujillin 回答日時： 2023/02/04 20:15

こんばんは

スプレッドシートはほとんど存じませんので、もっと簡単な方法がありそうにも思いますが・・

セル位置が不明なので、以下と仮定しました。
・「日付」がA列、「数値」がB列とする。
・1行目はタイトル行で、データは2行目から始まっている。

結果を求めたいセルに
=average(index(sortn(filter(A2:B99,B2:B99<>0),3,0,OFFSET(A2,0,0,countif(B2:B99,"<>0")),false),,2))
ではいかがでしょうか？

※ 未来の日付データは存在しないものと仮定し、日付の値の大きいものから順に抽出します。
※ 条件を満たすデータが3つより少ない場合は、その平均値になります。
※ 条件を満たすデータが存在しない場合は、エラー値になります。

No.1 回答者： oniwabangairu 回答日時： 2023/02/04 18:14

下記の場合

=SUM(E3:E196)/(COUNT(E3:E196)-COUNTIF(E3:E196,0))