エクセルで最新の30個の合計を出したい

質問

いつもお世話になっております。
例えばA列に下方向に数値データが入っている表があって日々データが追加されていくのですが、最新データ＝一番下のデータから30個のデータの平均を表示させたいのですが。

A列のデータの数を数えてその行から上に30個さかのぼった行から一番下の行までを範囲指定すればよいと思うのですが具体的な式が分かりません。
出来れば関数での方法をお願いします。

回答No.6

watabe007 · ベストアンサー

>当方が先ず思いつくのは＝IF（countA:A＜30,・・・）
一行目から最終行まで空白が無いのであれば
=COUNTA(A:A) で最終行が求められますね
その場合は
=SUM(OFFSET(INDIRECT("A"&COUNTA(A:A)),-29,,30,1))
↑最終行から上に29行目を求め、その位置から30行分の演算をしましたが
=SUM(OFFSET(INDIRECT("A"&COUNTA(A:A)),,,-30,1))
最終行から-30行でも得られそうですね、
と、まぁ~ いろいろ試して下さい。

お礼

今少し手が空いたので、実際の大きな表で試してみて、問題なく動く事確認できました。
横に引っ張れば各列のデータがコピーできるので非常に助かります。

INDRECTの使い方が分かりました。
交点のセルを求めるのに使ってはいるのですが、応用と他の関数との組み合わせが何故か（というか実力不足で）動かないのが現状です。

本件は以前より試行錯誤して、あきらめて（A１－A500)とかを指定して統計値を使用しているのですが、シート（品種）ごとにデータ数が大きく違うので比較するのに無理があると思いながら使っていました。

ご丁寧な回答ありがとうございました。

補足

色々なご回答非常に参考になります。
一応すべてのご回答を試してみて、やはり最後の式が当方には一番わかりやすいのでこれを使うことにします。

うっかりしていましたが最初はデータ数が1個から始まり、シートによってはB列以降にもかなり横に大きな表もあり、各列の上部数行に平均、最大、最小、±3シグマ等々の統計値や説明行が入るので、ご回答の式では当方には扱いにくい。

そこで得意の（やはり）IFを使って
=IF(COUNT(A:A)<30,SUM(A8:A37),SUM(OFFSET(INDIRECT("A"&COUNTA(A:A)+7),,,-30,1)))

と不細工かもしれませんがこれでデータが30個以下でもデータにマイナスがあっても目的の統計値が得られるようです。
データのないセルは、入力漏れではないことの確認のために－を入れるルールになっていますので空白セルはありませんのでこれでいけると思うのですが。

今回のINDIRCTがよく分かりませんがOFFSETなのでCOUNTの後に上部の行数を入れてみたらうまくいきました。

今回も絶大なサポートのおかげで解決しました。
いつもこんな感じでお手数をおかけし申し訳ありません、大感謝！！

回答No.8

msMike

[No.7お礼]へのコメント、

》 B列・・にもデータがあり…
それならそうと最初から言えばストトンと済んだものを！
B列が使えないならC列で、
C、D、…、Y列も使えないならZ列で、
という次第です。
同式を入力したセル Z30 のフィルハンドルを「エイヤッ!」と…

まだ目的とどう違いますか？

お礼

ご回答の方法も使っているのですが今回は種子が違っていました。
質問の仕方が悪く申し訳ありませんでした。

補足

質問に抜けあり申し訳ありませんでした。

例えばA列からR列まで日々下にデータが増えていくのですが、A100にデータを入れたら、A5に最大値や最小値や±3シグマが常に表示されていて、入力した値の統計的な位置が分かる表なので、これらの値がS列以降の場所に表示されても見えないので。
つまり、上から10行程度が枠固定になった表なので入力時に見えていることが必要なので、各列の上部に統計値が出ている必要があるのです。

回答No.7

msMike

式 =AVERAGE(OFFSET(A30,,,-30)) を入力したセル B30 のフィルハンドルを「エイヤッ!」とダブクリするってのは如何かと？

お礼

ご回答ありがとうございます。
B列・・にもデータがあり少し目的と違っています。

回答No.5

watabe007

最終行を求めるのにMATCH(,A:A,-1)を使いましたが
マイナスの数値には対応しないですね
なのでMATCH(MAX(A:A)+1,A:A,1)に変更してください。
=SUM(OFFSET(INDIRECT("A"&MATCH(,A:A,-1)),-29,,30,1))
　　↓
=SUM(OFFSET(INDIRECT("A"&MATCH(MAX(A:A)+1,A:A,1)),-29,,30,1))
最終行を求めるのに前回提示したようにいろいろな方法が有るので
試してみてください。

補足

何度もご丁寧にありがとうございます。
ご回答色々試してみて、当方が一番分かりやすい式で、結果報告させていただきます。

当方が先ず思いつくのは＝IF（countA:A＜30,・・・）で行き詰ります。
ケース分けする場合必ず先頭にIF来るので複雑になってHELPです。

回答No.4

ubku

泥臭いですが、

=AVERAGE(INDIRECT("A"&ROW(INDIRECT("A"&COUNTA(A:A)))-29&":"&"A"&ROW(INDIRECT("A"&COUNTA(A:A)))))

これを好きなセルに入れてもおなじ結果が出ます。
どのやり方にも共通しますが、データ数が30個より少ないとエラーになります。
こちらはMATCH関数を使っていないので、データに0やマイナスの数値があってもちゃんと計算されるのが強みです。

お礼

ありがとうございました。
おかげ様で何とか解決できました。

回答No.3

watabe007

おまけ
Ａ列に文字が入力されている最終行
　=MATCH("",A:A,-1)
　=MATCH("*",A:A,-1)
Ａ列に数字が入力されている最終行
　=MATCH(MAX(A:A)+1,A:A,1)
　=MATCH(10^308,A:A,1)
　=MATCH(,A:A,-1)
Ａ列に文字・数字が入力されている最終行
　=MAX(MATCH(MAX(A:A)+1,A:A,1),MATCH("",A:A,-1))
　=MAX(MATCH(,A:A,-1),MATCH("*",A:A,-1))
などが有ります。

お礼

ご丁寧な回答に感謝。
最終行を求めるのが苦手ですので非常に参考になりました。
と言うか、この関数を使う時には必ず他の関数との組み合わせになるのでいつも四苦八苦の試行錯誤を繰り返しています。

最近は安易にここで聞いてしまって少し反省しながらも時間節約のため、ご容赦！！

この回答はワードに張り付けて、「最終行の求め方」とファイル名にして他のテクニックと一緒に保存しておきます。

回答No.2

watabe007

合計なら
=SUM(OFFSET(INDIRECT("A"&MATCH(,A:A,-1)),-29,,30,1))

お礼

ご回答ありがとうございます。
ここまで丁寧でなくてもさすがにOKですヨ。

実は合計のほかに、平均、偏差、最大値、最小値も同一の表で使用して統計的な管理に使っていますのでどれでも良かったのですが。

回答No.1

watabe007

>一番下のデータから30個のデータの平均を表示させたいのですが。
表示させたいセルに
=AVERAGE(OFFSET(INDIRECT("A"&MATCH(,A:A,-1)),-29,,30,1))

お礼

いつもお世話になっております。
早々のご回答に感謝です。

質問に記載しようと思って忘れたのですが、後のご回答の通り最初はデータ1つから始まるので、指定データ数（30個)以下の場合もあることを記載するのを忘れました。
気付いた時には対応回答が出ていました。
2-3日手が無いので、結果は後日させていただきます。