エクセルで表の一番下から30個の平均を求めたい

質問

いつもお世話になっております。
先日ここで下方向に伸びていく表の最新データから上方向に30個の統計値を出す関数の組み合わせを教えてもらって各列に採用して一応目的は達成できたのですが、出来ると更に欲が出てきて非常に気になるので追加で教えていただきたく。

最新データを含む30個のデータの平均や±3σの値と比較して設定値を外れた場合はアラーム（赤太字）を出すようにしているのですが、この統計値に最新データは含まれないようにしたいのです。
最新データが異常値かどうかを判定するのに、現行だと異常値を含めた統計値と比較してアラームが出ることになるので出来れば最新入力値を含まない統計値を出したいのです。（特に今回データ数を30個にしたので統計値への影響が大きくなるので誤報の可能性が高くなってしまう事に気付きました。）

一番下まで数えて、そこから1つ上のセルから30個遡るのはこの式の変形では無理だと思うので、自分では出来ないので宜しくお願いします。

=IF(COUNT(C$6:C$1000)<30,AVERAGE(C$17:C$46),AVERAGE(OFFSET(INDIRECT("C"&COUNTA(C$17:C$1000)+16),,,-30,1)))

回答No.14

mt2015 · ベストアンサー

ANo.1です。

> しかし、実際のシートで色々試してみたのですが、データ数が30個以上の場合は正しく動いたのですが、データ数を30個以下にしてみたらデータ数が30個ではない値になってしまいます

元の式がデータが30個未満の時は存在する全データの平均を出すようになっていたので、それをベースに30個未満の時は一番下のデータを除いた平均を出しています。
＃データが25個あったら、最後の1個を除いた24個のデータの平均

実際に30個未満の時はどうしたいのでしょう？

> 再度30個以上にしても正しく計算されないのですが。
>
> 再度新しく式をコピペしたら正常に動きます。
> 最初から30個以下だと空白になります。

これはExcelの「ブックの計算」が手動になっている様に思えます。
Excelのオプションで自動にしてみてください。
自動への変更の仕方が解らなければ、ご使用のExcelのバージョンを提示してください。

お礼

本当にありがとうございました。
また、申し訳ありませんでした。

最初のご回答で解決できていました。

補足

＞実際に・・・どうしたいのでしょう？

本当～に申し訳ありません！
当方の検証ミスが重なり混乱して追加の質問（お願い）をしてしまいました。
複数のご回答で同じ結果だったので当方のミスは明らかなので色々試行錯誤して解決しましたのでこれ以上のお手数はかけないで下さい。

従来は何も考えずにただエクセルにデータを入力して少数点の打ち間違い（桁違い）等の防止が目的だったので統計値の意味も定義もなくただエクセルに入力していました。（その前はワードに直接入力で指摘されて訂正）
データが蓄積してきたので一歩改善で3σ外れ、過去最高値、最低値等の傾向異常も検出してみようと思った次第です。

実績が増えてくるとデータが数個しかない製品と数百個のデータの製品の傾向異常値では意味に大きな違いがあるのでデータ数を同じ（とりあえず30個）にしたい、と思った次第です。

やりたいことは、実績のない製品（データ数が足りない）場合は全部、30個を超えたら自身は除く、という「感じ」で質問させていただきました。
この辺の数字はご回答の数字を変えれば対応できるかと思ったので全体がいい加減な質問になってしまいました。

回答No.3

山田 太郎（@f_a_007）

【補足」少し、計算に誤りがありました。

10-5=5・・・・5,6,7,8,9,10
10-4=6・・・・6,7,8,9,10

このミスは、まあ、見逃してください。

回答No.2

山田 太郎（@f_a_007）

Q、A列の総平均値、トップ5、ラスト5の平均値を取得するのは？
A、以下のSQL文でも求めることが出来ます。

C1の式：=DLookup("SELECT Count(*) FROM [Sheet3$A1:A1000]",,FALSE)
C3の式：=DLookup("SELECT AVG(F1) FROM [Sheet3$A1:A1000]",,FALSE)
C4の式：=DLookup("SELECT AVG(F1) FROM [Sheet3$A" & D1-5 & ":A1000]",,FALSE)
C5の式：=DLookup("SELECT AVG(F1) FROM [Sheet3$A1:A" & D1-5 & "]",,FALSE)

＞エクセルで表の一番下から30個の平均値
　↓
＞エクセルで表の一番下から05個の平均値←C4の式

と、SQL文ですと割と簡単に求められるかと思います。こういうやり方でよければ、DLooolup()をご紹介します。

お礼

ご回答ありがとうございます。

当方には全くなじみのない発想で式が全く理解できないので、申し訳ありませんが検証はPending とさせていただきます。

回答No.1

mt2015

データはC6から下、C17から下　どちらですか？
恐らくC17から下だと思いますが。

元の式を活かしつつ修正するとしたらこんな感じでしょうか
=IFERROR(AVERAGE(OFFSET(INDIRECT("C"&COUNTA(C$17:C$1000)+15),,,-1*MIN(COUNTA(C$17:C$1000)-1,30),1)),"")

もし、C6以下の数値をカウントする事に意味が有るようでしたらこの回答は無視してください。

補足

早々のご回答ありがとうございます。

早速コピペで試してみたところ1発で動きました。
データはご推測の通り17行目からです。
しかし、実際のシートで色々試してみたのですが、データ数が30個以上の場合は正しく動いたのですが、データ数を30個以下にしてみたらデータ数が30個ではない値になってしまいます。

再度30個以上にしても正しく計算されないのですが。

再度新しく式をコピペしたら正常に動きます。
最初から30個以下だと空白になります。

この式を適用したい表、列は一杯あり、列の上の方（例えば16行まで）に色んな情報があり、最初は1個のデータから始まるので、もし必要なら式の最初にIF（データ数が30個以下の場合は17行目から30個を計算する）でもよいのですが。

当方の無知ゆえの不具合だと思いますが、表により色んなケースが想定されるので今少しご指導をお願いします。