再質問 エクセルの表の列の最下行から指定数の・・

質問

お世話になっております。

3日前にここでご回答いただいて解決したと思ったのですが、実シートで作業開始早々に不都合が出たので追加のHELPのお願いです。

各列の17行目以降に行方向にデータが入った表の下から30個のデータのMaxを求める関数のVBAを教わって早々に作業を開始したのですが、なぜか最下行を含まないVBAと、計算式の入った列では結果が「#VALUE!」となり、最下行を含むVBAの場合は、計算式の入った列の結果は「0」となってしまいます。
試しに別のシートで数値の列とその数値に定数をかけた列を作って試してみましたがうまく行きます。
また、対象のシートのセルの書式は数値になっています。

具体的な数式は
=IF(F127="","",F127*5) というような単純な計算式で日付が入るような特殊な計算はやっていません。

項目 数値A 計算値A 数値B 数値B 数値C
-------------------------------------------------------------------
平均 1.1197 #VALUE! 46.6133 #VALUE! 44.6767
σ 0.0008 #VALUE! 2.5940 #VALUE! 0.2128
最小 1.117 0.000 42.100 0.000 44.300
最大 1.121 0.000 51.100 0.000 45.100

＜最下行を含む場合＞
Function sfMax(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

StartRow = LastRow - Border + 1
End If

Set tgRng = Range(Cells(StartRow, MyCol), Cells(LastRow, MyCol))
sfMax = WorksheetFunction.Max(tgRng)

End Function

＜最下行を副含まない場合＞
Function sfSTDEV(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))
sfSTDEV = WorksheetFunction.StDev(tgRng)

End Function

回答No.8

HohoPapa · ベストアンサー

>>対象とする列の最下行（1048576行目）から上方向に1行ずつチェックして
>>数値、あるいは計算結果が数値になっている最初のセルにしたいですか？　
>→これでした！

どうやら、↑が決め手になるものと思います。

ならば、以下です。

'＜最下行を含まない場合＞
Function sfSTDEV(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 Then
　　LastRow = StartRow
　End If

　'下から上方向に、数値となっているセルを探す
　Do
　　If IsNumeric(Cells(LastRow, MyCol).Value) = True Then Exit Do
　　If LastRow <= StartRow Then Exit Do
　　LastRow = LastRow - 1
　Loop

　If LastRow > StartRow + Border - 1 Then
　　LastRow = LastRow - 1
　　StartRow = LastRow - Border + 1
　End If

　Debug.Print _
　　"開始行: " & StartRow & _
　　" / 終了行: " & LastRow & _
　　" / 対象列番号: " & MyCol

　Set tgRng = Range(Cells(StartRow, MyCol), Cells(LastRow, MyCol))
　sfSTDEV = WorksheetFunction.StDev(tgRng)

End Function

お礼

本日は忙しくお礼が遅くなりました。
問題を起こしたシートで検証し一発できれいな結果が得られました。
私はきれいな爽快感と大きな達成感??が得らえれました（混乱させただけなので、ご容赦!）

尚、報告が遅くなったのはトラブっていたわけではなく、たまたま月末、月初の仕事と飛び込みが入ったので確認が遅くなってしまいました。

当方としては出来てしまえば納得ですがHohoPapaさんにとっては結構ストレス（何をやっているのか分からない）
になったことと推測します。

対象データ（列）の開始行、データ数、自身を含む、含まないが他の関数でも使えるようになったことはどの位有用かを説明したいのですが、お礼が長くなるのでこれで終わりにします。

今回も最後の最後まで面倒を見ていただき＝感謝*5です。

補足

遅くに早々のご対応ありがとうございます！！

念のため最下行を含む場合、含まない場合の両方を試してみて、何をやってもバッチリでした。

同時にデータ開始行と対象データ数の変更にも慣れました。

「最下行」から・・・はよく使っている言葉ですが、今回のように入力表に換算式を入れ始めたのは最近で、勉強になりました。（今後質問するときの）

従来は別のシートで計算した結果を入力していたので問題にならなかったためご指摘（最下行の定義の確認）があるまで全く気づきませんでした。

本当に何度もお手数をおかけしました。
また、夜分に大変ありがとうございました。

スッキリ寝られます、感謝！！！！！

明日実シートで確認してBSで締め切らせていただきます。
最初に動作確認した「ひな形シート」には数式は入っていませんでした。

回答No.7

HohoPapa

今回のようなVBAで関数を用意しているケースで
かつ、集計する対象範囲を引数で明示的に与えない場合
関数を見ただけでは、どこを集計しているのかがわかりません。

このような使い方をする場合は、
関数の特性（仕様）を正確に理解し利用する必要があります。

今回は、デバックの機能を使い、
今回の関数がどの範囲を対象にしているのかを確認する術を説明します。

まず、添付画像の個所に

Debug.Print _
"開始行: " & StartRow & _
" / 終了行: " & LastRow & _
" / 対象列番号: " & MyCol

といったコードを書き加えます。
シート上で関数の結果が表示されたら、
添付画像の要領で
イミディエイト画面を開けば
何行目から何行目まで、どの列を対象にしたかを
確認できます。

VBAの動作確認を行う上で強力な機能ですので
よかったら（ちょっとハードルが上がりますが）
マスターしてみてください。

安定稼働したら、
無駄な動作ですので、
追加した、　　Debug...　の行は
削除してください。

お礼

本当に！　本当に申し訳なく・・・・

ただすでに担当者に換算式のないシートには、すでに最初のVBAの展開を始めてしまいましたので、もし出来るなら改良版をご教授いただければ・・
（この厚かましさにもお慣れになられたのでは？　ご容赦！！）

しかし、このコードは確かに原因特定にはすごく有用な仕組みですが、HohoPapaさんはなんでも知っていますね！！！

補足

ご回答のコードを試してみました。
色々やってみたらやはり式の入った最下行のセルを対象にしています。
一番下の結果では一旦入力したC列の数値を削除してみても式の入った行の1つ上からを対象に計算されています。
そして結果が#VALUE!となりました。

開始行: 11 / 終了行: 20 / 対象列番号: 4
開始行: 11 / 終了行: 20 / 対象列番号: 3
開始行: 11 / 終了行: 20 / 対象列番号: 4
開始行: 5 / 終了行: 11 / 対象列番号: 3
開始行: 11 / 終了行: 20 / 対象列番号: 4
開始行: 5 / 終了行: 13 / 対象列番号: 3
開始行: 14 / 終了行: 23 / 対象列番号: 4

回答No.6

HohoPapa

いろいろと指摘や事象の提示があるので、
調査のポイントを絞れません。

まずは、HfAvergageの関数に絞り
>このC5の関数をD列に引っ張ると#VALUE!となりました。
この再現手順だけに絞り、詳しく丁寧に説明してみてください。
さらに、ソースコードも提示してください。
少なくとも、説明された内容だけでは、現象が起きません。
特に、計算式がどの行まで埋まっているのかを正確に説明してください。

承知とは思いますが
Average 関数は、
対象となるセル範囲に数値が1つもなければ　#DIV/0!　のエラーになりますし
HfAvergageの関数は、　#VALUE!　のエラーになります。

更にもう一つ。

繰り返しますが、
集計対象とする最終行の求め方について
今は、計算式なり値なりの埋まっているセルの中で最下行、あるいは、
閾値の行数を超えれば、最下行の１つ上の行としているわけですが
この考え方でいいんですよね？

それとも、
対象とする列の最下行（1048576行目）から上方向に1行ずつチェックして
数値、あるいは計算結果が数値になっている最初のセルにしたいですか？
つまり、計算式が埋まり、結果的に見掛け上空欄のセルは無視しますか？

お礼

ご回答での指示の通り下記を試してみました。
新規のシートに下記のコードを貼り付けました。

手順
1．C5からC14までに１～10までを埋め込みました。
2．D5からD20までに＝C5*5を埋め込みました。
3．C3にHfAverag（C:C,10）、
4．C3を横に引っ張ってコピー
結果
C3には　5.5　D3には20.0と表示されました。
20.0はDD19から上に10個のデータの平均値と合致しています。

C15からC20まで数値を埋めると正しく計算結果が表示されました。

つまり、私のやりたかったことはデータの最下行から上に30個ではなく、計算式が数値になっている行から上に30個を対象としたい、が要求事項でした。

＞対象とする列の最下行（1048576行目）から上方向に1行ずつチェックして
数値、あるいは計算結果が数値になっている最初のセルにしたいですか？　→これでした！

Function HfAverage(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 = 10

StartRow = 5 'データ開始行
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))
HfAverage = WorksheetFunction.Average(tgRng)

End Function

補足

原因が分かりました。

ご回答の通り、D列の式（＝C5*5）の入っているセルの一番下からを対象にしていることが分かりました。
だからD列の式の数（行数）によって答えが変わるようです。

1つ解消方法として見つけたのは、D列の上から4つに（＝C5*5）と式を入れておくとC５以降に数値を入れるとDの5行目以降には自動でC5*5の値が反映されて問題が発生しないことが分かりました。

ただしこの方法だ新規のシート（新製品）の場合がは17行目から20行目までにダミーのデータで埋めておく必要があります。

よって出来れば
＞対象とする列の最下行（1048576行目）から上方向に1行ずつチェックして数値、あるいは計算結果が数値になっている最初のセルに

すれば解決できるかと思うのですが・・・

回答No.5

HohoPapa

>統計計算（VBA）の対象セルが全部空白に見える（式）の場合エラーになるようです。
この『空白に見える（式）』に埋まったセルの計算式にしたがって計算した結果が空であれば、
今回提示のマクロもエクセル出来合いのMAX、MIN、SUMの関数たちも、
これらのセルは対象から除いて計算しています。

条件付き書式の設定を駆使して、エラーなら空欄を表示するような仕込みを
していませんでしょうか

今回提示のマクロは、マクロの中でゴリゴリと最大値や平均値を計算しているのではなく
マクロ内部で、（セルの埋めて使う）=Max()や=AVERAGE()の関数を呼び出しているにすぎませんから
課題マクロ関数でエラーになるのであれば、=Max()といった関数でもエラーになるはずです。
この部分は実際に試し、結果を教えてください。

課題マクロ関数が集計の対象としているセルに１つでもエラーとなっているセルがあれば
課題マクロ関数も結果がエラーになります。
まずは、課題マクロ関数が対象としているセルたちの中に計算式が埋まっているのであれば
もれなく（全数）IFERRORを組み込んでみてください。

集計対象とする最終行の求め方について
今は、計算式なり値なりの埋まっているセルの中で最下行、あるいは、
閾値の行数を超えれば、最下行の１つ上の行としているわけですが
この考え方でいいんですよね？

それとも、
対象とする列の最下行が上方向に1行ずつチェックして
0以外の数値、あるいは計算結果が0以外の数値になっているセルにしたいですか？

更に、計算対象セル範囲に有効な0以外の数値の埋まったセルが１つもない場合に
マクロ関数の結果を何にすればいいですか
・0にする
・『計算不能』と表示する
・空欄とする
などが考えられますがいかがでしょうか？

補足

HohoPapaさん
お手数をおかけしております。

下記のご指摘は私なりに理解でき、シートに不備があるはずと思うものの自分ではどうにもできないジレンマに陥っています。

今自宅で下記の簡単な表で検証をしてみましたところ再現しましたのでご報告です。

１．新しいシートのC５行目から下に、14行目まで１～1０を埋め込みました。
２．D列に＝IF(C5="","",C5*5)と入力し20行目まで下に引っ張ってコピーしました。
３．HfAverae のコードのデータ開始行の17を5に変えて、
C3に＝HfAvergage(C:C,10)と入れると10を返します。
この時の正解は5.5なのですが。。。。

このC5の関数をD列に引っ張ると#VALUE!となりました。おそらくこれが実シートの再現だと思います。

尚、その後C15行目以降に続けて数字を下に一杯入れると正解を返すようになります。
が、D列は#VALUE!のままです。
D列の式をC列の数値のセルと同じ長さにすると両方の列とも正解を返す様になります。

既成のAverage 関数を対象を17行目以降にして範囲をデフォルテで30個（指定数）にしただけだと思うのですが、何か動きが違っている気がします。
セルの数値を色々変えると再計算がうまくいかないようなケースがあります。
どのパターンか分かりませが、元に戻すボタンで戻っても計算結果が戻らない場合があるみたいなのですが。

D列に数値が出た後、D列のセルを全部削除（空白に）してもD3に数値が残ります。
何が何やら状態になってしまいました。。。。
困った！！

回答No.4

masnoske

No.3です．
条件付き書式を使って，見た目で問題のセルを判断できる方法を思い付きました．
条件付き書式で F127に以下の条件を設定し，セルを色で塗りつぶすようにしてください（たとえば，赤で塗りつぶす）．
=NOT(OR(ISNUMBER(F127),ISBLANK(F127)))

条件式が設定できたら，条件付き書式の「ルールの管理」で適用先に判定したいセル範囲を設定して下さい．
数値と空白セル以外（計算でエラーが起きるセル）に設定した書式が反映されます．

書式は必ず「塗りつぶし」で設定してください．
文字色だと空白セルと空文字列（""）やスペースとの区別がつきません．

お礼

朝一で試してみましたがどこにも着色は出ませんでした。
対象セル全てに同じ式がはいっていましたので・・・
上から1000行目辺りまで下に引っ張ってコピーしていますので。

＃No２さんのお礼枠に記載させていただきましたが原因はわまりました。

当方抜けが多く、このような不具合は頻繁に経験していますので、ご回答の検証方法は非常に参考になりました。

お騒がせしごめんなさい。

回答No.3

masnoske

No.1です。
見た目ではなく、きちんと確認されたほうがいいかと思います。
データ列（または行）ごとに COUNT関数とCOUNTA関数を比較すれば文字列や空白の有無を判定できます。
無ければ 2つの関数の結果は一致します。

お礼

なるほど！
こういう確認方法があるのですね。
月曜日に確認します。
有り難うございます。

回答No.2

HohoPapa

=IF(F127="","",F127*5) というような単純な計算式
↑の計算式が、今回のマクロで集計するデータ群の範囲に含まれている。
更に、この計算式の結果が#VALUE!になっている。
ということでいいでしょうか？

ならば、すでにコメントされていますが
F127のセルがエラー、または
F127のセルの値が文字列だろうと思います。
文字列に５をかけることはできませんから。

今回のマクロで集計する範囲に#VALUE!が含まれている場合、
マクロで計算する結果も#VALUE!になってしまいます。

このようなエラーの起きる可能性があるのであれば
一般的には（多くの場合）
エラーになったときどうするのかを計算式に組み込みます。

具体的には、
=IFERROR(IF(F127="","",F127*5),"")
といった計算式にします。

お礼

HohoPapaさん
土日にどう考えても不思議で、朝一で色々やってみて原因が分かりました。
統計計算（VBA）の対象セルが全部空白に見える（式）の場合エラーになるようです。
また、17行目以降に少なくとも2つ以上の数値が無いと同じようにエラーになります。

先ず、空白に見える数式のセルを全部削除すると正しく結果が表示されます。
次に計算結果が数値で入っているセルの式を何も入っていない空白セル（下）に引っ張って行くとある行までコピーすると急に#VALUE!になります。
1行づつ確認してみると最下行から平均は30個以上、その他の項目は31個以上の数値データが無いと#VALUE!となることが分かりました。

書いているうちにだんだん分かりにくくなってしまったようですが、いつもの推理力と忖度に期待!

何とかなりますでしょうか?
当方が思いつくのは、参照元セルに入力すると上のセルの式（F123＊5）をコピするようなコードの追加はお願いできないでしょうか?

ちなみにご提案のIFERRORも試してみましたが駄目でした。

補足

お騒がせします。
＞計算式の結果が#VALUE!になっている。
これが困ったことに見た目数値として表記されています。
17行目以降の計算の対象範囲のどこかに#VALUE!があるかと見たのですがないのです。

質問をアップした後新規Bookのシートで色々試して見ているのですが、1つ分かったのは計算式の列に引っ張る（コピーする）と「1つ以上の循環参照が発生しています・・」と出ます。

おそらくこれ（循環参照）が原因だと思うのですが、不思議な現象として、たとえばC列に数値、D列にC列を使用した計算式を入れて、C列の17行目より上の行（例えば５～８行目）に4つのHf関数を入れて、E列以降の列まで引っ張るとD列の値が表示されます。

その後でD列の計算対象の数値を変えるとD列の統計値は正しく再計算されますが、E列以降の統計値はそのまま変わりません。

E列（以降）の17行目より下には何も数値が入っていません。
E列の上のセルに改めて=Hf●●関数を入力すると正しく（０）と表示されます。
つまり関数セルを横に引っ張てコピーした時に不具合がおこる感じです。

今も色々試してみて、普通にできたり、循環参照が出たりなので、表が何らかの不具合を内包していると思い始めましたが原因不明。
いづれにしても実際のBookが手元にないので月曜に改めて不具合の詳細をご報告いたしますので宜しくお願いいたします。

いや~再現検証にも自信がなくなってきましたので会社でイチからやり直してみます。
お騒がせしました。

IFERRORは試してみますが、横の列に実測値が入っているのでその値を使用した計算結果が””では困るのですが。

回答No.1

masnoske

F127でエラーが起きているか、F127に文字列が入っていませんか？