[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『関数で最終行の取得』（ぽむ）
お世話になります。
A列に文字列、B列に数値が入っているデータがあります。 このデータの、「最終行の値」ではなく「最終行」を関数で求めたいと思います。
しかし、このデータはところどころ空白が存在しています。
A B
1 佐藤 5000
2 田中 4000
3
4 鈴木 20000
5
6
7 遠藤 9000
例えば上記の表だと =MATCH(OFFSET(A1,COUNTA(A:A)+2,),A:A,0) という式を組んだら答えが返ってきましたが、空白の数が変わった時に対応できません。 また、データに重複があった時に機能しなくなってしまいます。
次に =MATCH(LOOKUP(9^9,B:B),B:B,0) としてみたら、こちらは空白の数に関係なく対応できました。 しかし、やはり重複の問題が解決できません。
どなたか良い案がありましたら教えて下さい。 ちなみにA列とB列は必ず同時に空白になります。（どちらだけ入力されている事はありません）
よろしくお願いします。
=LOOKUP(1,0/(A1:A1000<>""),ROW(1:1000))
※1000行目まで
(GobGob)
こんなのでも。 =MATCH(1,INDEX(0/(A1:A1000<>""),0),1)
追記 GobGobさんのとぽむさんのを組み合わせた =LOOKUP(9^9,B:B,ROW(B:B)) なんてのでもいいかな？
ただ、2003以前の場合は =LOOKUP(9^9,B1:B1000,ROW(B1:B1000)) と行の制限付けてね。
追記の2 JPNさんのご指摘のように2003以前でも行の制限いらなかった。 (あれ？なにを勘違いしたんだろう？) (春日野馨)
これでもいいかも
=MAX(INDEX((A1:A1000<>"")*ROW(A1:A1000),0))
=MAX(IF(A1:A1000<>"",ROW(A1:A1000))) ← はCtrl＋Shift＋Enterで確定 2003でも↓でいけると思いますけど？ > =LOOKUP(9^9,B:B,ROW(B:B))
（JPN）
必ずAB列の最終行が同じなのかな
=MAX(INDEX((A:B<>"")*ROW(A:B),))
By しげちゃん
みなさんの式を全て試し、全てうまくいきました。ありがとうございます！ しかし、LOOKUP関数についてどうしても分かりません。
=LOOKUP(9^9,B1:B10,ROW(B1:B10))
と上の表へ入力した時、途中の計算結果で
=LOOKUP(9^9,{5000;4000;0;20000;0;0;9000;0;0;0},{1;2;3;4;5;6;7;8;9;10})
と表示されます。
これはベクトル形式の使い方だと思うのでそちらのヘルプを見ると
・検査値が見つからないと、検査範囲に含まれている検査値以下の最大の値が使用されます。
とあります。ならば
=LOOKUP(9^9,{5000;4000;0;20000;0;0;9000;0;0;0},{1;2;3;4;5;6;7;8;9;10})
は、検索値以下の最大値である20000に対応した、4が返ってくるのでは？？ここでなぜ7が返ってくるのでしょうか？
昇順に並んでいない事が関係しているような気がするのですが、理解できません。
お手数おかけしますが、解説を頂ければ幸いです。 (ぽむ)
Excelのヘルプの前に「二分探索」でweb検索してみて。
LOOKUP(VLOOKUP、HLOOKUPのTRUE型、MATCHの1,-1型）は逐次サーチでなく二分探索で検索するんで。
んで、二分探索で見つからない場合はLOOKUP関数は配列の最後の位置を返すよ。
今回は配列データよりでかい数値を検索値にするから結局関数結果は配列の 最後のデータを返す。となるんですな。
(GobGob)
＞入力されている値は、（中略）昇順に並べておく必要があります。そうでない場合、LOOKUP 関数では正しい値を見つけることができません。
本来は検査値か検査値以下の最大の値を検索する関数ですが、
昇り順に並んでいないためにそうならない。
この場合、「9^9」か、「9^9に満たない、範囲内で最後に表れる数値」が検索されています。
ＬＯＯＫＵＰ関数を設計した人が想定していない使い方ともいえます。
ちなみに、途中に9^9があれば、その行番号が返ります。
数式のデバッグでは空白セルを０で返していますが、
実際には未入力で返される０と、０が入力されて返っている０は区別されているようです。
（みやほりん）
>ちなみに、途中に9^9があれば、その行番号が返ります。 真ん中より後ろに9^9があればその位置が返る可能性があります、じゃないかな？ (春日野馨)
＞・検査値が見つからないと、検査範囲に含まれている検査値以下の最大の値が使用されます。
検査値が見つからないのは配列データの範囲内 （例えば0～100を10区切りの配列に対して25を検索など）と言う場合と 配列データの範囲外（0～100より大きい数値）と言う場合があるよ。
LOOKUP（二分探索）はデータが昇順（MATCHの-1型は降順）に並んでいる必要がある。 んで真ん中のデータから大小を見極めながら結果を返す。
例えば LOOKUP(180,{0,25,50,75,100,125,150,175,200},{1,2,3,4,5,6,7,8,9}) なら
① 180を全体で比較 → 真ん中（100）より大きい →次の比較は{125,150,175,200}
② 180を{125,150,175,200} で比較 →真ん中(150、175）より大きい → 次の比較は 200
③ 180を200と比較、 → 200より小さい → 200の前のデータを返す →結果 175
④ 175に対応した{1,2,3,4,5,6,7,8,9}の位置を返す → 結果 8
となるよ。(あくまで二分探索としてのイメージね）
けど、今回の場合は配列の順列がどうであれ配列の数値より大きい検索値なんで 最後のデータを返す。
これはLOOKUPというか二分探索処理する関数の特性を使った 裏ワザ（？）的な使い方ですな。
まぁ、こんなロジックですわ。
(GobGob)
ありがとうございます。二分探索を検索したところ、偶数前取りで常に真ん中から比較するという記事を見ました。 GobGobさんの詳しい追記も頂き、その挙動は理解できたと思います。 ヘルプの記述についても昇順が前提という事で納得できました。
しかし、
>二分探索で見つからない場合はLOOKUP関数は配列の最後の位置を返すよ。
という事は、
=LOOKUP(9^9,{5000;4000;0;20000;0;0;9000;0;0;0},{1;2;3;4;5;6;7;8;9;10})
は10を返すという事ですよね？実際に上記数式をドラッグしてF9を押すと10になるのですが、
元々同じ式である
=LOOKUP(9^9,B1:B10,ROW(B1:B10))
を入力すると7が返ります。欲しい答えは7なので合っているのですが、これは何故でしょうか？？
とここまで書いて皆さんの書き込みを読み返したところ、みやほりんさんの記述でヒントを頂きました。
>数式のデバッグでは空白セルを０で返していますが、 >実際には未入力で返される０と、０が入力されて返っている０は区別されているようです。
=LOOKUP(9^9,{5000;4000;0;20000;0;0;9000;0;0;0},{1;2;3;4;5;6;7;8;9;10})
という数式そのままだと答えは確かに10ですが、
実際の数式
=LOOKUP(9^9,B1:B10,ROW(B1:B10))
を入力した際には、「最後に入力されているデータより後ろの未入力セル」は省かれる？
そのため内部的に
=LOOKUP(9^9,{5000;4000;0;20000;0;0;9000},{1;2;3;4;5;6;7})
という計算がされ、7が返るという認識です。
もし間違っていたらご指摘下さい。難しいですね…。 （ぽむ）
＞「最後に入力されているデータより後ろの未入力セル」は省かれる？
というか、未入力セルは無視かもしれないね。
A B C 1 1 1 1 2 5 1 3 10 3 10 4 500 8 5 5000 11 6 10000 19 7 120000 19 8 100 9 10 11 1000 12 13 14 15 16 17 18 19 10000
A列検索値、C列配列
B1 =LOOKUP(A1,C:C,ROW(A:A))
(GobGob)
ありがとうございました。大変勉強になりました！ （ぽむ）
※2012/4/18 当方の回答、最後から2番目を一部修正しました。
(GobGob)
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.