VBAのxlDownと同じ昨日の関数

質問

VBAでRange("A5").End(xlDown).Rowとしたら、A5から下方向にデータが連続して入力されている
最終行を返しますが、これと同じ機能を持ったエクセル関数はありますか？

今、A5からA10までデータが入っていたら、
初めて空白セルが存在する一つ上のセルの行の「10」という値か
A5からA10まで6行データが入力されているので、「6」という値を取得したいです。
出来れば、後者がありがたいです。

Counta関数を使って、
COUNTA($A:$A)-COUNT($A$1:$A$4)と書きましたが、A12以降に値が入っていたら、対応できないので上手くないかなと思います。

回答No.7

kagakusuki

済みません、.End(xlDown).Rowと同じ機能ではなく、.End(xlup).Rowと同じ機能の関数を回答しておりました。
　もしも、対象範囲内における「データが入力されている最終行」よりも上の行に、空欄のセルが存在していた場合には、「初めて空白セルが存在する一つ上のセルの行」は、「データが入力されている最終行」よりも上の方にある行という事になりますから、MATCH関数を使っただけでは、.End(xlDown).Rowと同じ機能にはなりません。
　尤も、

>A5から下方向にデータが連続して入力されている

という条件がありますから、どちらでも大差はない事になるとは思います。
　因みに、データが存在する行の途中に、空欄のセルが挟まっている場合において、A5を1番目として数えた場合に、「初めて空白セルが存在する一つ上のセルの行」が何番目に当たるのかという値を返す関数は、次の様なものとなります。

=SUMPRODUCT((COUNTBLANK(OFFSET(A5,,,ROW(A5:A10)-ROW(A5)+1))=0)*1)

　尚、SUMPRODUCT関数は、行ごと（或いは列ごと）に繰り返し処理を行う関数であるため、設定するセル範囲が数千行にもなる場合には、計算処理に要するコンピューターの負荷が大きくなり過ぎて、結果が現れるまでに時間が掛かる様になってしまいますので、あまりにも広い行範囲を対象とする場合にはお勧めしません。（配列計算式で求める方法の場合も同じ理由により、処理に時間を要しますので注意して下さい）

回答No.6

kagakusuki

WindFaller様、御気分を害してしまい申し訳御座いません。

　化学や一部の工学分野などを始めとする分子や原子を扱う分野などでは、10の23乗を超える数を扱う事も珍しくはありませんし、その様な場合でも有効桁数さえ15桁以下であればExcelで扱う事が十分に可能なのですから、必ずしも10^15にすれば良いとは限りませんが、仰る様に経理や品数を扱う場合には10^10でも間に合う場合が殆どですね。

　それと、私は「最終行を求める」という事に囚われ過ぎてしまい、本質問の内容を.End(xlDown).Rowと同じ機能の話ではなく、.End(xlup).Rowと同じ機能の話であると勘違いをしておりました。
　私は、VBAを使う様になり始めてから間もなくの頃、自作したVBAマクロの中で.End(xlup).Rowで最終行を求めた処、何故かそのマクロを起動させる前に削除済みになっている筈のセルに、（UsedRangeではなく）.End(xlup).Rowが反応してしまい、正しい最終行を求める事が出来ないという現象が発生した事があるのです。
　その際、確認のため新規のExcelbookを開いて、.End(xlup).Rowを使って最終行を求める簡単なマクロを試した時にも、同様の現象が現れたため、.End(xlup).Rowでは必ずしも最終行を求める事が出来る訳ではないと思い込んでおりました。
　只、今回改めて、新規のExcelbookを開いて、.End(xlup).Rowを使って最終行を求める簡単なマクロを試した際には、同様の現象を再現する事が出来ませんでしたので、もしかしますと、一時的に表れたバグの様なものだったのかも知れません。（前述の昔作成したマクロは、最終行を求める事が出来ない失敗作として削除してしまったため、動作を再確認する事は出来ませんでした）

回答No.5

WindFaller

#4さんへ
私は、この質問者さんの意図を十分に理解してるわけでも、関数に関しても、思い出しながらのことですから、十分とは言えませんが、あまり、突飛な回答やあやふやな内容で、こちらの回答を指摘されるのは困ります。単独で、質問者さんに回答をするのが、ここの掲示板のマナーです。

最大の数値は「2^1024-1」

Excelの数値は、倍精度浮動小数点数型(Double型)と思うでしょうけれども、事実上のExcelの表計算上では、16桁以上は意味がありません。もし、問題なら、10^15にすればよいと思います。

これは、実務上のことで、国家予算でもあるまいし、10億の数字が悪いかどうかは、質問者さん自身に依存します。

>VBAで.End(xlDown).Rowとした場合、必ずしもデータが存在する最終行の行番号が求められるとは限りません。

　End(xlDown).Rowでは、「過去において一度でもデータが入力された事があるセルの行番号」ということはありません。
="" これは、数式が入ってますし、"" はデータとして存在します。しかし、データが入力した痕跡を探す方法というよりも、書式を入れている場合は、UsedRangeに反応しますが、Endプロパティでは掛かりません。

以下は、あくまでも、A列のみを対象としていますが、書式のあるなしで、違いがでます。

Set r = ActiveSheet.UsedRange
　i = r.Cells(r.Rows.Count, 1).Row
　j = Cells(Rows.Count, 1).End(xlUp).Row

回答No.4

kagakusuki

文字の場合、通常は

=MATCH("*",A:A,-1)

で最終行を求める事が出来るのですが、例えば「　ABC」等の様に頭文字が空白スペースとなっている文字列データが、最終行にあった場合には、正しい最終行を求める事が出来ませんし、頭文字が空白となっている文字列データが、最終行よりも上の行に存在していた場合には#N/Aエラーとなってしまうという難点があります。
　又、空白の場合だけではなく、頭文字が半角の「♂」や「♀」、「!」、「#」、「%」、「(」、等々となっている文字列の場合も同様です。
　ですから、完璧を期するなら、

=MATCH(CHAR(1),A:A,-1)

とした方が良いかも知れません。

　一方、数値の場合には10000000000よりも大きな数値が入力される可能性も無いとは言えませんから、MATCH関数の検索値を10^10としたのでは不十分だと思います。
　Excelで扱う事の出来る最大の数値は「2^1024」よりも1小さい値までなのですから、MATCH関数の検索値として「2^1024-1」が設定出来れば良いのですが、Excelでは「2^1024」を数値として扱う事が出来ませんので、「2^1024-1」の前半部分の「2^1024」が数値として認識されないために、「2^1024-1」という計算が行われず、MATCH関数の検索値として「2^1024-1」を指定した場合には#NUM!エラーとなってしまいます。
　ですから、数値の場合には、

=MATCH(1.99999999999999*2^1023,A:A)

或いは

=MATCH(9.99999999999999E+307,A:A)

或いは

=MATCH(MAX(A:A)+1,A:A)

等の様にされた方が良いと思います。
　実用的には、

=MATCH(9E+307,A:A)

でも十分です。

　そして、数値データと文字列データが混在している場合には、

=MAX(IF(COUNT(A:A),MATCH(9E+307,A:A),0),IF(COUNTIF(A:A,"*?"),MATCH(CHAR(1),A:A,-1),0))

になります。（IF関数を使ってエラー回避を行いませんと、範囲内に数値データか文字列データのいずれか一方でも存在していない場合には、#N/Aエラーとなってしまいます）

　尚、回答No.1様の方法や、この回答の上記の方法は「A列の中でデータが存在する最終行」を求める方法になりますから、A10よりも下にあるセル、例えばA11セルにデータが存在していた場合には、「A5からA10までの範囲内で初めて空欄セルが存在する一つ上のセルの行番号」ではなく、「11」という値が返されてしまいます。
　ですから、「A5からA10までの範囲内で初めて空欄セルが存在する一つ上のセルの行番号」を求める場合には、次の様な関数となります。

=MAX(IF(COUNT(A5:A10),MATCH(9E+307,A5:A10),0),IF(COUNTIF(A5:A10,"*?"),MATCH(CHAR(1),A5:A10,-1),0))+ROW(A5)-1

　尚、A5からA10までの範囲内にデータが全く存在せず、全て空欄のセルばかりとなっている場合には、「A5からA10までの範囲内で初めて現れる空欄セル」はA5セルなのですから、上記の関数では「A5セルの1つ上のセルの行番号」である「4」が返されます。

　ついでに言いますと、VBAで.End(xlDown).Rowとした場合、必ずしもデータが存在する最終行の行番号が求められるとは限りません。
　完全に確認出来ている訳では御座いませんが、どうやら.End(xlDown).Rowで最終行を求めようとした場合には、もしも、データが存在する最終行よりも下の行に、ワークシート関数の計算処理の結果として「""」（空欄）となっているセルが存在している場合や、「過去において一度でもデータが入力された事があるセル」が存在していた場合などには、「データが存在する最終行の行番号」ではなく、「過去において一度でもデータが入力された事があるセルの行番号」が返される様です。

回答No.3

web2525

=SMALL(IF((A:A=""),ROW(A:A),99999),1)
↑
これでA列の最初の空白セル行を求められます
配列計算なので【Shift】+【CTRL】+【Enter】で確定
配列計算なので列指定してしまうと、再計算がメタクチャ時間がかかります

範囲を指定することである程度改善されます
=SMALL(IF((A1:A20=""),ROW(A1:A20),99999),1)

回答No.2

WindFaller

>検査する列がA列でも、検査開始するセルがA5だったりA10だったりで変わってしまいます。

それは、不具合という意味ですか？

MATCH関数で、検査開始位置を探せというのなら、#1の数式を利用すれば、例えば、こうなるのでは？

以下は、文字列の場合ですが、

=MATCH(FALSE,INDEX(INDIRECT("A1:A"&MATCH("*",A:A,-1))<>"",,),1)

#1の数式は、

Range("A5").End(xlDown).Row
ではなく、
Range("A"& Rows.Count).End(xlUp).Row
ですね。

>"*"この部分は何を意味しているのでしょうか？
何かの文字(any character)という意味ですが、何か問題が発生しているのですか？

回答No.1

WindFaller

昔々、やったことがあります。間違っているかもしれません。
値の入っている最大行を出す数式です。

>初めて空白セルが存在する一つ上のセルの行の「10」という値か

文字の場合
=MATCH("*",A:A,-1)

数字の場合
=MATCH(10^10,A:A,1)

どちらかの場合は、
=MAX(MATCH("*",A:A,-1),MATCH(10^10,A:A,1))

ただし、何もない場合のエラー処理はされていません。

お礼

検査する列がA列でも、検査開始するセルがA5だったりA10だったりで変わってしまいます。
"*"この部分は何を意味しているのでしょうか？