[[20221013155507]]　『マクロじゃなくエクセル関数で最終行を検索する方』（マサカリカボチャ）　ページの最後に飛ぶ

『マクロじゃなくエクセル関数で最終行を検索する方法ってあるんすか』（マサカリカボチャ）

題名のごとくですが・・・
マクロじゃなくエクセル関数で最終行を検索する方法ってあるんすか？

=Cells(Rows.Count,"E").End(xlUp).Row
と同じ効果のエクセル関数があれば知りたいです。

よろしくお願いします。

＜使用 Excel：Office365、使用 OS：Windows10 ＞

HYPERLINK関数はどうでしょう。
A列に１行目からデータが入力されているとします。

あるセルに:　=HYPERLINK("#A"&COUNTA(A:A))

１行目からデータが入力されていないときには「COUNTA(A:A)」に「+n」で
調整します。
（メジロ） 2022/10/13(木) 16:18:54

=MAX(FILTER(ROW(A:A),A:A<>""))

とか。
（d-q-t-p） 2022/10/13(木) 16:29:45

https://www.excelspeedup.com/saisyuugyou/
（?） 2022/10/13(木) 16:33:31

できるできないでいえば
=XMATCH("*?",A:A&"",2,-1)

でもできますが列参照に配列計算を入れると処理が重くなるのでお勧めしません。
上のサイトで紹介している配列使った方法も軒並み激重なので止めときましょう。

昔ながらの方法でいえば

=AGGREGATE(14,6,MATCH({1E15,"ーー"},A:A),1)

これが比較的ましです。
（d-q-t-p） 2022/10/13(木) 17:02:25

 最終行の行番号を取得して何につかうのでしょうね？
 365ならもっといい方法がありそうな気がします　たぶん
（´・ω・`） 2022/10/13(木) 17:14:44

 =LOOKUP(1,0/(A:A<>""),ROW(A:A)) でも

（jjj） 2022/10/13(木) 17:17:37

 >上のサイトで紹介している配列使った方法も軒並み激重なので止めときましょう。
 サイト拝見しました。
 そう言い切れるのなら確実な検証結果を示してください。
（サイト拝見） 2022/10/13(木) 17:28:47

 d-q-t-pさんの　AGGREGATE案が一番軽そう。（一つひとつのセルを相手にしていないので）

（半平太） 2022/10/13(木) 17:33:11

ある可変行の列の3行目以下をCOUNTIFして結果を2行目に出したいのです。
列全てを範囲にすると循環参照になっちゃうので。

まあ別の列に表示しろよ、と言われればそうなんですが
表にしたときに見た目が悪かったんで方法があるなら知りたいな、と思いまして。
（マサカリカボチャ） 2022/10/13(木) 18:40:33

つまり「自身も含む」列の範囲のCOUNTIFで循環参照にならない方法、
が知りたかったわけです。

最終行が分かれば
=COUNTIF(INDIRECT("E3:E" & 最終行),検索値)
で循環参照にならずにすむかな、と。
（マサカリカボチャ） 2022/10/13(木) 18:53:41

 やってみると、循環参照を避けるのって結構難しいなぁ。。

 E2セル =LET(r,E3:INDEX(E:E,ROWS(E:E)),COUNTIF(E3:INDEX(E:E,MAX(FILTER(ROW(r),r<>""))),検索値))

（半平太） 2022/10/13(木) 22:24:01

 なんで最終行を特定しないとダメなんですかね？

 範囲を広めにとるか、表をテーブルにして構造化参照にすればいいのでは？

 以上
（笑） 2022/10/13(木) 22:47:41

 ＞まあ別の列に表示しろよ、と言われればそうなんですが

 あれ？　なら、範囲限定にする必要ないじゃないですか？　広めにとっておけばいいんですから。
 私は、範囲内の空白セルを数えたりしたいのかなと思っちゃたんですが。

 E2セル =COUNTIF(E3:E1048576,検索値)　とか、マジックナンバーが嫌であれば
 E2セル =COUNTIF(E3:INDEX(E:E,ROWS(E:E)),検索値)

（半平太） 2022/10/13(木) 22:53:12

＞ある可変行の列の3行目以下をCOUNTIFして結果を2行目に出したいのです。
それなら簡単。 F1・F2に「1」と入力して

E2
=COUNTIFS(F:F,"<>1",E:E,検索値)

これで循環参照を回避できます。

昨日は書きませんでしたがワークシート関数はそもそもデータ最終行を取得
してから計算する（or するようになった）ものが多いのでわざわざ範囲を
可変で指定しようとするくらいなら多めに範囲を取るか構造化参照に切り替
えるか FILTERで絞ってから計算するかをお勧めします。
（d-q-t-p） 2022/10/14(金) 07:04:42

コメント返信：

[ 一覧(最新更新順) ]

[[20221013155507]] 『マクロじゃなくエクセル関数で最終行を検索する方』（マサカリカボチャ） ページの最後に飛ぶ

[[20221013155507]]　『マクロじゃなくエクセル関数で最終行を検索する方』（マサカリカボチャ）　ページの最後に飛ぶ