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

質問

データが縦方向に入っている表があります。
新しいデータは最下行に追加していきます。
それぞれの列の最下行から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.12

HohoPapa

>最大値と最小値は「最下行の数値を含む」コードを教えて下さいませ。
ご推察のとおり、

　MyCol = Rng.Column
　LastRow = Cells(Rows.Count, MyCol).End(xlUp).Row
　If LastRow > StartRow + Border - 1 Then
　　LastRow = LastRow - 1　　　　　　　　　'<<===注
　　StartRow = LastRow - Border + 1
　End If

注の行を削除、またはコメントアウトすれば期待の動作になります。

他の関数（最大値や平均値）も指摘の通り、

Function sfMin(Rng As Range, Optional bd) As Double
と
　sfMin = WorksheetFunction.Min(tgRng)
の2行、3か所を修正します。

余談ですが、接頭語sfはなんでもいいです。
ちなみに、SpecialFunctionの略にしたつもりです。

お礼

先程この補足をアップした後、いよいよ実際のひな形Bookで使用し始めるか、とhpMinにしたら何度やっても動かず???
確かに自宅で早々に関数名を変えて検証したはず!!
と今まであれこれ試行を続けていました。

HP、Hp、ｈｐ等々を試した結果からの結論はｈｐの組み合わせは使えない、でした。

当方が使用したのは名付けて「HohoPapa関数」略してhf関数でした。
HfはOKでもHFは駄目でした。

どうしてもhpの並びはダメでした。
使える文字の組み合わせの規則があるのでしょうか?

また、関数の候補はHF、Hfでも両方出てくるので関数の頭は大文字の感じがしますのでHfで使い始めようかと。

頭が大文字だと将来なにか不都合があるようならご教示お願いします。

補足

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

今更ですがこのコードは標準モジュールに記載して検証したのですが他に良い場所あれば教えてください（最初に聞くべき補足質問でした）

試行錯誤派を見くびってはいけません。
最初の試行錯誤時にsfかfsか何度か間違ってしまったので今後も間違う可能性あると思い最初にコードをhpで試して問題ない事を確認してその後の検証はhpAvetrage等で検証しています。
名付けて「HohoPapa関数」の略です。
時間が経ったら頭が出てこなくてこのページの回答を検索する羽目になる可能性を潰しておきました。

回答No.11

HohoPapa

私の案は、
オリジナルな関数をVBAで作成し、
シート上で使う一般的な関数
( =Sum(D3:D9) といった関数）と同じように
関数式の埋まったセルに、求めた結果を表示さする方法です。

この要領が理解でき、結果が期待通りであれば、
引き続き、
>平均、最小、最大、標準偏差（STDEV)
これの残りである、平均、最大、標準偏差（STDEV)を紹介させてもらいます。
（すでに提示したコードを見れば自力で解決できるかもしれません）

以下、再確認です。

(1)再確認１
閾値として提示されている30という数値は
データの行数でいいんですね？
そうではなく、
単なる文字など数値以外と空欄なセルとを除いた数ですか？

(2)再確認２
データ開始行が17とのことなので、
　Dim StartRow As Long
　StartRow = 17 'データ開始行
といった記述でVBAのコードになかに埋め込んでいるわけですが
この方法でいいですね？
それとも、関数式のパラメータで渡すようにする
例えば　
=sfMin(F:F,F14,17)
といった使い方のほうがいいですか？

補足

（長文失礼します）
いや～ビックリ!
普通の関数のように=sfと入れると候補として出てきました。

(1)データは行数で規定するのが正解です。LOT単位の統計データなので母集団が変わると意味が違う気がしますので。
(2) 例外（古いシート）の場合は17行目から始まる（マクロを含む）新書式に改定していますのでこの方が好都合。
またコード中に分かり易く解説があるので必要なら手で修正します。
(3)他の項目への応用はコード中の3か所（のみ）を変えてみたら動きましたので追加のコードは上記修正で間違っていなければ不要です。

!!1つ大事なお願いがあります!!
最大値と最小値は「最下行の数値を含む」コードを教えて下さいませ。（当然ですがどちらか1つのケースでOKです）
最大、最小値は自身を含んでの該当、非該当を判定しないと意味が無く、3σ外れは逆に自身を入れることは間違い＝これに気付いて適当に答えから関数をいじったのが間違いでした。

おそらくコード中の1を削除or追加すれば出来ると思うのですが、関数の時のように意味が分からずに試行錯誤でいじったら同じ間違いを犯す可能性があるので、お手数をお掛けしますが何卒お願い致します。

回答No.10

HohoPapa

#8では閾値をVBA内に固定値で保持するコードとしましたが
別なセルで指定したいようですので
更に修正してみました。

関数式を
=sfMin(F:F,F14)
すれば、閾値をF14セルから取得します。
もし、
=sfMin(F:F)
とした場合は、Const DefBorder = 30
この記述に従う動作になります。

Function sfMin(Rng As Range, Optional bd) As Double

　Dim LastRow As Long
　Dim MyCol As Long
　Dim tgRng As Range
　Dim Border As Long
　Dim StartRow As Long
　Const DefBorder = 30

　StartRow = 17 'データ開始行
　If IsMissing(bd) Then
　　Border = DefBorder　　'省略された場合の閾値
　Else
　　If ((bd = 0) Or (bd = "")) Then
　　　Border = DefBorder　　'省略された場合の閾値
　　Else
　　　Border = bd
　　End If
　End If

　MyCol = Rng.Column
　LastRow = Cells(Rows.Count, MyCol).End(xlUp).Row
　If LastRow > StartRow + Border - 1 Then
　　LastRow = LastRow - 1
　　StartRow = LastRow - Border + 1
　End If

　Set tgRng = Range(Cells(StartRow, MyCol), Cells(LastRow, MyCol))
　sfMin = WorksheetFunction.Min(tgRng)

End Function

補足

当方の補足と入れ違いになったようです。

シート（品種）データ数は決まっているので、使い方としては通常の関数と同じように固定のセル
sfMIN(F:F,$B$2）というように使えるのでしょうね！
しかも、デフォルトで30個。

メッチャ便利～～～！
月曜日に動作確認して結果報告します。

回答No.9

masnoske

No.5です．
>実際のシートは列によって16行目までのデータ数が違っていたりしますのでここまで来たら完成まで何卒お付き合いお願いします。

そういう場合は，下手に列ごとに関数を調整するのではなく，データの開始行が17行目になるようにセルを挿入し，開始行を17行目に揃えるほうが後のトラブルを防ぎやすいです．

補足

何度も済みません。

余計なことを書いてしまいました。
ご指摘の通り、データ開始行を17行にするために16行目までのデータ数が違っていることを言いたかったのです。

現在使用している式の”D"の呪いのせいです。
D列のデータ数をCOUNTするのでここを細工する必要がある場合を想定しての蛇足でした。

回答No.8

HohoPapa

#7は、求めている仕様を読み誤っているかもしれないので
再ポストします。

・データを調べる範囲は、関数式の引数が指定する列の
　　17行目から下方向にデータの埋まった最終行まで
・ただし、データが30行以下の時には全データ
・データが30行を超える場合は、
　最終行の値を対象から除き、かつ、
　最終行の1行上から30行

都合、
30行埋まっているときと31行埋まっているときは同じ結果になります。

Function sfMin(Rng As Range) As Double

　Dim LastRow As Long
　Dim MyCol As Long
　Dim tgRng As Range

　Dim StartRow As Long
　StartRow = 17 'データ開始行
　Const Border = 30　　'閾値

　MyCol = Rng.Column
　LastRow = Cells(Rows.Count, MyCol).End(xlUp).Row
　If LastRow > StartRow + Border - 1 Then
　　LastRow = LastRow - 1
　　StartRow = LastRow - Border + 1
　End If

　Set tgRng = Range(Cells(StartRow, MyCol), Cells(LastRow, MyCol))
　sfMin = WorksheetFunction.Min(tgRng)

End Function

それとも、30行というのは、
行数ではなく、数値以外、空欄なセルを除いて数えますか？

お礼

あっ、勘違いしていました。
なんと！　VBAで関数を作る、ということですね？
よって各列の16行目までのセルにVBA関数を入れ使うのですか！

想定外の回答で色々難しそうだと思ってしまいました。

補足

おそらくご推察の通りのことがやりたいのです。
対象とするデータ数は下から31行目までです。
データ数30個と31個の場合が同値になること承知しています。

欲を言えばシート上の指定のセルでデータ数を30とか50に変えられれば品種ごとにコードをいじらずにVBAのコピペだけで・・・
またいつもの欲が出てきたので止めます。
30を50に変えるだけなのでどちらでもOKです。

今使っている統計値は、平均、最小、最大、標準偏差（STDEV)の4つです。
これに±3シグマ値等の計算式が各列の16行目までに表示されるようになっているのですが、結果の表示場所（セル）はどこで指定することになるのでしょう？

また、ご回答のコード中で使うにはそれぞれの関数のAverage、MIN、MAX、STDEVで良いのでしょうか？
VBAで出来れば他のシートへの横展開が非常に楽なのですが、途方のレベルではハードルが高いかとましたが、関数も同じくらいハードルが高いことを痛感している次第です。

＜補足＞
A列に日付、B列にLOT No、それ以降の列から特性値が入っているので、空白や-をデータ数から除くと違うLOTの母集団になってしまうので。
何故、空白が入るかというと、顧客要求で途中で特性項目（列）が増減するためです。

いや～長文失礼しました！！

回答No.7

HohoPapa

akira0723さんはそれなりにVBAを扱えるわけですから
セル上で使える出来合いの関数の組み合わせで実現するよりも、
VBAでオリジナルな関数を作成して使うほうが
シンプルで扱い易いように思います。

求めている仕様を私が漏れなく把握できているか疑問ですが
以下の仕様でコードを書いてみました。
よかったら試してみてください。

セルに埋める計算式は、=sfMin(F:F)　です。
カッコ内は列番号です。

・データを調べる範囲は、関数式の引数が指定する列の
　　17行目から下方向にデータの埋まった最終行まで
・ただし、データが30行以下の時には全データ
・データが30行を超える場合は、最終行の値を対象から除く

Function sfMin(Rng As Range) As Double

　Dim LastRow As Long
　Dim MyCol As Long
　Dim tgRng As Range

　Const StartRow = 17 'データ開始行
　Const Border = 30　　'閾値

　MyCol = Rng.Column
　LastRow = Cells(Rows.Count, MyCol).End(xlUp).Row
　If LastRow > StartRow + Border - 1 Then
　　LastRow = LastRow - 1
　End If

　Set tgRng = Range(Cells(StartRow, MyCol), Cells(LastRow, MyCol))
　sfMin = WorksheetFunction.Min(tgRng)

End Function

これでよければ、最大値、平均値のコードを紹介します。

回答No.6

masnoske

No.5です．
No.4を以下のように訂正します．

ようやく状況が理解できました．
データに空白や "－"が入るようになった結果，これまでの式では対応できなくなったという事ですね．
それでも OFFSETの第1引数のナゾは残りますが（笑）

No.4では上記の場合でも対応するため複雑な数式となりましたが，データに空白や "－"が入らないのであれば，以下の数式で対応できます（B列の例）．
その場合は，10017:20000行に入力した数式も不要です．

=IF(COUNT(B$17:B$10000)<=30,AVERAGE(B$17:B$10000),AVERAGE(OFFSET(INDIRECT(SUBSTITUTE(ADDRESS(1,COLUMN(),4,1),"1","") & COUNT(B$17:B$10000)+16),-1,0,-30)))

IF条件の <=30と最後の -30は，どこか適当なセルに抽出データ数を入力して参照するほうが良いと思います．
たとえば B1に 30と入力し，以下のようにする感じです．

=IF(COUNT(B$17:B$10000)<=B1,AVERAGE(B$17:B$10000),AVERAGE(OFFSET(INDIRECT(SUBSTITUTE(ADDRESS(1,COLUMN(),4,1),"1","") & COUNT(B$17:B$10000)+16),-1,0,-B1)))

これぐらいなら 1つの数式でも理解できると思いますが，列番号をアルファベットで取り出す SUBSTITUTE(ADDRESS(1,COLUMN(),4,1),"1","") は，わかりにくいので別セルに入力して参照するか，直接 "B" としても良いでしょう．直接 "B" とした場合は，他の列にコピペした時には気を付けて下さい．

補足

自宅で新規シートのB列～E列に１～５０位を適当に引っ張って、色々試してみました。
見事に正しい値が表示されました！　
また、式の部分ぶぶんはなんとなくわ分かるものの、全体はほとんど理解できませんが、やりたいように変形できることはしっかり確認しました。
特にありがたいのは、データ数を別のセルに決めてしまえば、他の列にコピーすればそのまま複数の値が求められるのは感謝感激です。

今までは”D"があったので、Ave、Min、Max、STDEV・・・をまとめて横に引っ張た後、”D"をE、F、G・・・・と縦横に修正しなければならず、ウッカリすると隣の列の統計値が出ていたり。。。

現在300枚以上？のシートに使用している表なのでこの改善は今後非常にありがたいです。

最初に具体的な表の図を添付しなかったために推理力と忖度をしていただき誠に申し訳なく。
本当に助かりました。

申し訳ありませんが念のために月曜日に実際のシートで確認してみてから締め切らせていただきます。
実際のシートは列によって16行目までのデータ数が違っていたりしますのでここまで来たら完成まで何卒お付き合いお願いします。

お礼枠は結果報告（or 追加質問）に取っておきますのであしからず。

回答No.5

masnoske

No.4です．
ようやく状況が理解できました．
データに空白や "－"が入るようになった結果，これまでの式では対応できなくなったという事ですね．
それでも OFFSETの第1引数のナゾは残りますが（笑）

No.4では上記の場合でも対応するため複雑な数式となりましたが，データに空白や "－"が入らないのであれば，以下の数式で対応できます（B列の例）．
その場合は，10017:20000行に入力した数式も不要です．

=IF(B$7<=B$6,AVERAGE(B$17:B$10000),AVERAGE(OFFSET(INDIRECT(SUBSTITUTE(ADDRESS(1,COLUMN(),4,1),"1","") & COUNT(B$17:B$10000)+16),-1,0,-30)))

最後の -30は，どこか適当なセルに抽出データ数を入力して参照するほうが良いと思います．
たとえば B1に 30と入力し，以下のようにする感じです．

=IF(B$7<=B$6,AVERAGE(B$17:B$10000),AVERAGE(OFFSET(INDIRECT(SUBSTITUTE(ADDRESS(1,COLUMN(),4,1),"1","") & COUNT(B$17:B$10000)+16),-1,0,-B1)))

これぐらいなら 1つの数式でも理解できると思いますが，列番号をアルファベットで取り出す SUBSTITUTE(ADDRESS(1,COLUMN(),4,1),"1","") は，わかりにくいので別セルに入力して参照するか，直接 "B" としても良いでしょう．直接 "B" とした場合は，他の列にコピペした時には気を付けて下さい．

回答No.4

masnoske

No.3です．

私が考えた方法は以下のとおりです．
(A) データは 17:10000の行範囲に入力する．
(B) 10017:20000の行範囲に 17:10000の有効データ（数値データ）の番号を付与する．
(C) 6:13の行範囲に途中の計算結果を表示する．

特に長い数式は後でワケがわからなくので (C)のように小分けにしたほうが保守しやすいです．
以下に手順を示します．
(1) B10017に数式 =COUNT(B$17:B17) を入力する（$の使い方に注意）．
(2) B10017の数式を B20000までコピーする．
(3) B6にデータの抽出数を入力する（質問者の例では 30）．
(4) B7に以下の数式を入力する．
　=COUNT(B$17:B$10016)
(5) B8に以下の数式を入力する．
　=B9-B6+1 を入力する．
(6) B9に以下の数式を入力する．
　=B7-1 を入力する．
(7) B10に以下の数式を入力する．
　=SUBSTITUTE(ADDRESS(1,COLUMN(),4,1),"1","")
(8) B11に以下の数式を入力する．
　=B$10 & MATCH(B8,B$10017:B$20000,0)+16
(9) B12に以下の数式を入力する．
　=B$10 & MATCH(B9,B$10017:B$20000,0)+16
(10) B13に以下の数式を入力する．
　=B11&":"&B12
(11) B2に以下の数式を入力する．
　=IF(B$7<=B$6,AVERAGE(B$17:B$10000),AVERAGE(INDIRECT(B$13)))
(12) B3に以下の数式を入力する．
　=IF(B$7<=B$6,MAX(B$17:B$10000),MAX(INDIRECT(B$13)))
(13) B4に以下の数式を入力する．
　=IF(B$7<=B$6,MIN(B$17:B$10000),MIN(INDIRECT(B$13)))
(14) B列をC列以降必要なだけコピーする．

では，各手順の説明です．
(1)～(2) COUNTで数値データを数えています．これが有効データ番号になります．
(3) 何件抽出するかを設定できるようにしました．数式に入れ込むと使い勝手と保守性が悪くなります．
(4) COUNTで有効データ数を数えています．これは最下行の有効データ番号になります．
(6) 番号が前後しますが， 最下行の 1つ上の有効データ番号（＝抽出終了No.）を求めています．
(5) (6)の抽出終了No.と (3)の抽出数から抽出開始No.を求めています．
(7) 列番号（アルファベット）を求めています．
　提示された数式では "D" のようにダイレクトに入力されていますが，入力ミスがないように数式にしました．
(8) MACHTで抽出開始No.が何行目に記入されているかを求め，(7)と組み合わせて抽出開始セルを求めています．
(9) (8)と同様にして抽出終了セルを求めています．
(10) 抽出開始セルと抽出終了セルを ":" で繋いで計算範囲セルを求めています．
(11)～(13) 統計計算の部分です．計算範囲を INDIRECTで指定しています．
　IFを使って，式の前半部分でデータ数が抽出数以下の場合を計算し，後半部分でデータ数が抽出数を超えた場合を計算しています．
　これまでのほとんどの手順は，後半部分のための準備になります．

複雑な数式もこのように小分けすれば理解しやすくなります．
ふぅ～

画面の部分コピーは，WIN10なら標準で付属されている Snipping Tool を使えば簡単です．
画面左下の検索窓に snip と入力すれば見つかると思います．

回答No.3

masnoske

No.2です．
イメージ的には図のような感じでしょうか．
この場合，1つの数式で計算するのは無理だと思います．
というのも，データに空白や "ー"が入っているので「下から 30個」を選択できません．
おそらく提示された数式以外の部分に「下から 30個」のセル範囲を数式で計算し，提示された INDIRECT関数でこの範囲を参照して統計処理していると思います．
INDIRECT関数に "D" が入っているので，5～16行目の間に何かしらの数式が入力されていて，それを参照するようになっていませんか？
あるいは，提示された数式はオリジナルではなく，質問者が色々試行しているうちに全く混乱してしまった数式だとか（笑）

補足

本当に何度もすみません。
また、図の添付方法が分らず図するお手間をおかけし申し訳ありません。

う～～ん
17行目から下は数値のみですし、16行目より上を参照していることも無いのですが・・・・

ただ、－や空白は後から出てきた現象で、最初はうまくできたはずなのですが、最下行を含むか含まない、を操作しているうちにおかしくなってしまったと思います。
一応最初はダミーデータの数を変えてみて正常に計算されることを確認したはずなので。

今は空白セルを禁止として、計算時にはこれもデータの数としてカウントしての統計値で良いとしています。
30個ではなくーの分が少ないデータ数での計算。

また、ーや空白は、過去の表に式を入れた場合の不具合なので、今後の時間経過とともにーも空白も無くなるので当面は少し精度が悪くてもOKなのですが。

全部が数値データになった時点で、正確な値が求まるように改善しておくのが目的です。

そもそもデータが30個以下の場合は、下から１７行目までの数値で最下行の値を含み、超えた場合は最下行を含まず187行目まで対象に、という条件が身の程知らずでした。
少なくとも30個以上の場合は正確に、以下の場合はエラーでも良しとしても良いとも思っています。
ただ、その場合は対象のデータ数は20個にしたいのでが（欲は出るもので）・・・・

いづれにしても、最下行を含む、含まないの設定は、この式では私には対応ができないと分かったので他の方法をご教示頂ければ幸いです。
（よって最初に現行の式を機挿しませんでした）