エクセルの表の列の最下行から指定数のデータで

質問

データが縦方向に入っている表があります。
新しいデータは最下行に追加していきます。
それぞれの列の最下行から1つ上のセルから任意の数で平均や最大、最小値を求めたいのです。
（つまり統計値に最新値を含まない値で最新値を評価したい）

但し、下記の条件が付きます。
1.データは17行目から始まって、それより上の行は列によってデータ数が違っています。
2.出来ればデータ数が指定（例えば30個）に名足りない場合は最新値を含むデータにしたい。
最初はデータ数が少ないのでそれも含めて統計処理したい。
（この条件でハードルが上がるようなら無視でもOK）
3.データ列には空白セルや「ー」が入っている場合が混在しているので扱うのは数値のみ。

昔ここで教えてもらいながら、試行錯誤してかなり長い式を作って使っていたのですが、最新データを含むと非常にまずいケースがあることに気付き、色々試行しているうちに全く混乱してしまったのでHELPしました。

回答No.13

HohoPapa · ベストアンサー

>標準モジュールに記載して検証したのですが他に良い場所あれば教えてください
深追いしたことがありませんので確固たる返答ではありませんが、
一般的にも常識的にも標準モジュールに配置するのが正しい対応です。

複数のブックから共通で使えるようにするのであれば
アドインにする必要があり、ちょっとハードルが上がります。
よかったら、
https://excel-ubara.com/excelvba4/EXCEL297.html
に説明がありますので、挑戦してみてください。

>hpMinにしたら何度やっても動かず
あたかも、hpが接頭辞でMinが出来合いの関数のように感じるかもしれませんが
接頭辞と思っているのは使う側の考え方の話だけであって、
エクセルやＶＢＡは、hpMinを１つの単語としてとらえていますので
hpだから使えないということは起こりません。

過日触れた3か所を書き換えること、かつ、その足並みがそろっていれば
問題ないはずです。

NGなら、書き込んだコード全数とエラーの症状を教えてください。

お礼

毎度、毎度手取り足取り、負んぶに抱っこで感謝というより申し訳なく思います。

今回も当方にとって一番好ましい解決法が得られました。
これは左のセルに4個のHf関数を選択するだけで後は項目の数だけ横に引っ張れば完成するのが新鮮です。
自身を含む、含まない場合も確認し期待通りに動きました。

書き込み場所は間違っていなければ、すでに標準モジュールを4つ挿入してそれぞれわかるようにモジュールの名前を（Hf●●）に変えて（変える方法を調べました）ひな形に設定完了しました。

また関数名に関しては「Special関数」と分かればfsと迷うことは無いのですが、最初のHf●●で統一しました。

補足

既に 補足して改善していただく事は無いのですがお礼の補足です。

この統計処理を数年前に初めて、当時VBAの存在を知らずに「関数」で調べまくって、聞きまくって期待通りに動いた時には非常に達成感がありましたが、すぐに異常値を検出するのに自身（入力した直近の値）を入れないと最大と最小値にはアラーム（書式設定で赤字等）が出ないことに気付き全項目を下から30個にしていたのですが、同時に平均と±3シグマ値に自身の異常値を入れると意味が曖昧になることを知りながら、データ数が多い汎用品種では（不本意ながら）問題は少ないとしていました。
（極端な異常値は入力前に前回の結果との比較で気付きますので）
また、重要項目2つは30個（任意）のデータ数で自動でグラフ化するVBAもHohoPapaさんに教わっていますのでグラフで見ると最大、最小は一目瞭然ですので。

長年の懸案事項がスッキリ解決できました。

改めて御礼申し上げますとともに、今後とも（あと数年？）よろしく御願い申し上げます。

回答No.2

masnoske

No.1です．
OFFSETの第1引数がおかしくないですか？
第1引数は参照なので，ここにはセル番地が入ります．
第1引数は INDIRECT("D"&COUNTA(D$17:D$10000)+16)
で求めていますが，返されるのはセル番地ではなく数値になっていませんか？
というのも，質問者は以下のように書かれています．

>下記の式はＤ列にあるデータの最下行から上に30個を対象にした時の最小値の式です。

D列は最小値を求めることができる，つまり数値ですよね．
だから，OFFSETの第1引数を INDIRECTで求めるのであれば，怪しいのは INDIRECT("D" の "D" の部分かと思います．
どこか別の列に参照元の番地が入力された列があるはずなので，その列を指定すれば良いと思います．

オリジナルの表がわからないので，提示された数式から推測できるのはこれぐらいです．

補足

何度もすみません。

複数の列に入っている各列の下から30個のデータの最小値等を求めたいのですが、下記の数式の入っているセルはD列の10行目で、D列の最小値を求めたいのです。

A列に項目、B列以降に各列の特性値が入っています。
データは各列の同じ列の17行目より下に入っています。
17行目から対象のデータが入っています。

A B C D E ・・・・
１
２ 平均
・ 最大値
10 最小値 10行目に先ほどの式が入っています。
・
・
17 ここからデータが入っています。

回答No.1

masnoske

> 昔ここで教えてもらいながら、試行錯誤してかなり長い式を作って使っていたのですが、

VBAではなく，ワークシート関数で処理しているということですね．
であれば，もう少し全体像が掴めるような情報が必要です．
試行錯誤して作った長い式を使った結果、最新データを含むんだ場合にどのような問題があるのかを具体的に示して下さい．

あなたは過去にここで質問されて適切な回答を得られたかも知れませんが，初めて見る回答者には全く理解できません．

お礼

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

後日、関数での対応に限界を感じてＶＢＡもかじり始めたので、ＶＢＡの方が簡単ならその回答を期待したのであえて今の関数は記載しませんでした。

ＶＢＡなら分かる、とも言えませんがどちらが使いやすいかが分らないレベルですので宜しくお願いします。

補足

早々のご回答ありがとうございます。
そうですねよね。
下記の式はＤ列にあるデータの最下行から上に30個を対象にした時の最小値の式です。
これだと最下行のデータも入れての最小値になるのでこれを最下行の値を抜いて30個（31行上から）の値にしたいのです。
対象データは17行目からです。

=MIN(OFFSET(INDIRECT("D"&COUNTA(D$17:D$10000)+16),,,MIN(30,-COUNTA(D$17:D$10000)),1))