Excel 入力セル終端のデータを取り出す

エクセルの列末のデータだけ表示が出来ないでしょうか - Office系ソフト - 教えて！goo
http://oshiete.goo.ne.jp/qa/4260158.html

上記の質問ページに、以下の回答がありますが、
どうして実現できるのかわかりません。

------------

◆途中に空白があってもいいですよ
★数値の場合（Ａ列最後の行のデータ）
=LOOKUP(10^10,A:A)

★文字列の場合（Ａ列最後の行のデータ）
=LOOKUP(1,0/(ISTEXT(A:A)),A:A)

★数値・文字列混在の場合（Ａ列最後の行のデータ）
=LOOKUP(1,0/(A:A<>""),A:A)

------------

「LOOKUP」関数はもともと、
「条件にあったセルの値を表示」ですよね？

それがなぜ、入力セル終端のセル内容を取り出せるのでしょうか？
どなたか詳しい解説をお願いいたします。

・「10^10」← 10の10乗？
・0/(ISTEXT(A:A)) ← 0 をTRUEかFALSEで割るってどういうこと？
・0/(ISTEXT(A:A)) ← そもそも列に対して「ISTEXT」できるの？
・0/(A:A<>"") ← 0 をTRUEかFALSEで割るってどういうこと？

No.5 回答者： tom04 回答日時： 2013/09/16 19:26

こんばんは！

横からお邪魔します。
質問に対する直接の回答ではないのでごめんなさい。

別案として・・・
配列数式になってしまいますので、極端にデータ量が多い場合はおススメしませんが、

=INDEX(A1:A3000,MAX(IF(A1:A3000<>"",ROW(A1:A3000))))

としてShift＋Ctrl＋Enterで確定！m(_ _)m

No.4 回答者： MarcoRossiItaly 回答日時： 2013/08/25 00:31

＞私の環境では、下の2つは動作しません。

環境：Windows XP／Excel2000
★2） [=LOOKUP(1,0/(ISTEXT(A:A)),A:A)] は、 [#DIV/0!]
★3） [=LOOKUP(1,0/(A:A<>""),A:A)] は、 [#NUM!]


なるほど。こちらの手元では 2000 がなくて、検証できないので申し訳ないのですが、例えば★2）という数式を次式に差し替えたら、どうなりますか？

=lookup(1,index(0/istext(a:a),),a:a)
あるいは
=index(a:b,match(1,index(0/istext(a:a),)),2)

なお ISTEXT を ( ) で囲う必要は、特にありません。囲ってもいいです。


＞私の、質問中の★マークの３つの数式例よりも、
　よりよい数式というのはあるのでしょうか？
　（シンプルな方が好みです）


LOOKUP あるいは INDEX(MATCH) がうまく行かないようなら、全然シンプルではないのですが仕方がないので、次式などで最下行の右隣を求めることは可能です。たぶん 2000 でも。

=offset(a1,max(index(countif(indirect("a"&row(a1:a3000)),"<>"&"")*row(a1:a3000),))-1,1)

※この数式では、数値か文字列かは問いません。

「a:a」としてもいいのですが、行数が多すぎると計算にかなり時間がかかったりするので、「a1:a3000」くらいの感じがお勧めです。この質問ページに載っている他の全ての配列数式においても、同様です。

途中の行に空白セルがないと分かっている場合は、配列あるいは作業列に頼る必要がないので、次式で十分です。

=offset(a1,count(a:a)-1,1)　　 ……数値の場合
=offset(a1,counta(a:a)-1,1)　　……文字列の場合

この数式における「a:a」は配列ではなく、A 列全体という「セル範囲」を意味しています。


＞基本的な質問ですが、C1セルに [=LOOKUP(1,A:A,B:B)] と入力します
　……
　使い方が間違っているのでしょうか。


実はそうなんです。No.3 さんも答えてくださっていますが、A 列を昇順に並べておかないと、正しい答えが出ない仕様です。

数式「=LOOKUP(1,A:A,B:B)」を、「=LOOKUP(2,A:A,B:B)」とか「=LOOKUP(4,A:A,B:B)」、「=LOOKUP(6,A:A,B:B)」に書き替えたらどうなるか、試してみてください。A 列が 3 種類の並びをしているときに、それぞれの数式中の数字を何種類かに書き換えてみてください。

昇順になっていないときの関数の動きがどうなっているのかというと、実は「よく分かりません」。「その値を初めて下回った行の直前の行」になっていそうな気もしますが、何とも言えません。動きが保証されていないということは、確実に言えます。

質問者さんが参考にされたページの数式が割り算を行っている理由も、この辺りのことが関係しています。つまり割り算をすれば、必ずゼロかエラーのどちらかになることが約束されており、エラー値は読み飛ばしてくれるので、昇順に並べ替えることを考える必要がないというわけです。

この回答へのお礼

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

=lookup(1,index(0/istext(a:a),),a:a)
=index(a:b,match(1,index(0/istext(a:a),)),2)
上記の2つの数式は、Excel2000ではダメです。
どちらも [#DIV/0!] が返ってきます。


=offset(a1,max(index(countif(indirect("a"&row(a1:a3000)),"<>"&"")*row(a1:a3000),))-1,1)
こちらの式はOKみたいです。
数値・文字列どちらも動作しますね。

・offset関数
・max関数
・index関数
・countif関数
・indirect関数
・row関数

解説いただければありがたいですが、
時間を見つけて、ヘルプ参照しながら謎解きしてみたいと思います。


[途中に空白セルがない場合] の数式も合わせ、
ありがとうございます。

お礼日時：2013/08/29 21:03

No.3 回答者： tsubuyuki 回答日時： 2013/08/24 21:51

あらま・・確かに２０００デフォルトでは上手くいかないですねぇ。

じゃぁ、次善の策で「作業列」を使って簡単に。

Ａ列に「データ」があるとして、
作業列を設け（例えばＢ列を作業列として）、
　　式：=IF(A1="","",ROW())
で、必要行数フィルしておきます。
抽出してきたいセル（任意）に
　　式：=INDIRECT("A"&MAX(B:B))
としてやると、最終行の値を返してくれます。

以上、本件への回答です。


追加で【質問】いただいた内容について。

私が＃１で貼ったリンク先を参照いただきたいのですが、
「ベクトル形式の構文」の項の「重要」に記載がある通り、
基本的には検査範囲は昇順で並べてやる必要があります。

> 使い方が間違っているのでしょうか。
上記の考え方からみると、間違えていると言えます。

この回答へのお礼

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

[INDIRECT関数] は使ったことがありません。
間接的に参照できるのですね。
「A1 形式の参照、R1C1 形式の参照、セル範囲を参照する名前が入力されているセルの参照、または名前によるセル参照」
が可能ということで、今後利用してみます。

[LOOKUP関数]
「検査範囲 に入力されている値は、…コード順の昇順に配置されている必要があります。」
確かに記載がありますね。

お礼日時：2013/08/28 00:44

No.2 回答者： MarcoRossiItaly 回答日時： 2013/08/22 23:08

この数式を理解するには、その前に知っておかなければならない概念がたくさんあります。

書き切れないので、ここで全ては書きません。研究していってください。


＞「10^10」← 10の10乗？

そのとおりです。「非常に大きな数」という意味で使われていると思いますが、10^10 を超える数値も Excel でフツーに扱うことが可能なので、もっと大きな数でなくていいのかな？という感じで、ちょっと微妙な値を持ってきたなという感想です。


＞0/(ISTEXT(A:A)) ← 0 をTRUEかFALSEで割るってどういうこと？
＞0/(A:A<>"") ← 0 をTRUEかFALSEで割るってどういうこと？

TRUE、FALSE という単語の語義そのものには、あまり意味がありません。これら「論理値」は、四則演算などの数式の中に混ぜると、1 や 0 に変化するという性質があるので、それを利用しようとしています。試しにどこかのセルに、次式のような「論理式」を記入してみてください。

=0>1
=0+(0>1)
=a1=b1
=(a1=b1)*(c1=d1)

1 や 0 に変化するということは、0 ÷ 1 あるいは 0 ÷ 0 という計算をさせていることになります。もちろん、前者の答えは 0 だし、後者はエラー値「#DIV/0!」を返すことになりますね？


＞0/(ISTEXT(A:A)) ← そもそも列に対して「ISTEXT」できるの？

ISTEXT が検査する対象はセルなので、通常そのような書き方はしませんが、ここではムリヤリそういう書き方をすることによって、配列を作っているからです。配列の意味は一言で説明できないので、インターネットでいろいろ検索するなどしていただきたいですが、大雑把に言うと、ベクトルとか行列のような形のデータの集まりといった感じです。

配列を扱える仕様になっている関数は限られますが、LOOKUP では可能ということです。

ご質問の数式の途中計算がどうなっているか知りたかったら、数式タブの「数式の検証」を実行してみることをお勧めします。その中の「検証」ボタンを何回も押すごとに、計算が進んでいきます。ただし、「A:A」というふうにセル数が多すぎると Excel がハングしてしまうので、例えば「=LOOKUP(1,0/ISTEXT(A1:A10),A1:A10)」くらいにしてから「数式の検証」を始めてください。

今回 ISTEXT の部分で生成させている配列は、割り算の結果、{0;0;……;0;0;#DIV/0!;#DIV/0!;……} という具合の内容になります。この配列から LOOKUP で 1 を検索しようとすると、最後の 0 の位置でひっかかるのです。つまり必ずしも 1 でなくても、0 より大きな数なら幾つでも同じ結果が得られます。

この回答へのお礼

【お礼】
回答ありがとうございます。
何となく概要はわかりました。

ただ、質問中の★マークの３つの数式例のうち、
私の環境では、下の2つは動作しません。
環境：Windows XP／Excel2000

★2） [=LOOKUP(1,0/(ISTEXT(A:A)),A:A)] は、 [#DIV/0!]
★3） [=LOOKUP(1,0/(A:A<>""),A:A)] は、 [#NUM!]
が返ってきます。

【質問】
[No.1] さんの回答に対して「お礼」とともに「質問」を投稿させていただいています。
よろしければ、コメントをお願いいたします。

お礼日時：2013/08/24 17:31

No.1 回答者： tsubuyuki 回答日時： 2013/08/22 22:25

ＬＯＯＫＵＰ関数の「クセ」と言うか「習性」と言うか、

悪く言ってしまうと「影響が小さい不具合を利用した裏ワザ」的な使い方ですね。
　　参照：http://support.microsoft.com/kb/324986/ja



理解しやすいだろう順に行きますが・・長くなるのでご覚悟くださいませ（笑）。
良かったら、読みながら手も動かして、実際に操作してみてくださいませ。


ＬＯＯＫＵＰ関数は通常、合致したセルを返す関数ではありますが、
「合致するデータが無かったら、配列の末端を返す」クセがあります。
なので
　　=LOOKUP(10^10,A:A)
これだと、「１０,０００,０００,０００」に合致するセル・・
つまり、「あり得ないほど大きい数字」を指定して、
「配列の最後尾を持ってくるように」わざと仕向けている、と言う感じです。


続いて
> ・0/(ISTEXT(A:A)) ← そもそも列に対して「ISTEXT」できるの？
この疑問。
これ、実は出来ちゃうんです。
試しにやってみましょう。
新規シートのＢ１セルに、「=ISTEXT(A:A)」としてやり（おそらく「ＦＡＬＳＥ」が返ります）。
コレを１０行ほどフィルしてやりましょう（全部「ＦＡＬＳＥ」と表示されるはずです）。
で、Ａ列に「何か適当な文字列」を入力してやると、あら不思議。
「適当な文字列」を入力した「行」のＢ列が「ＴＲＵＥ」に変わりましたね。
簡単に「範囲で指定すると、対応したセルに関して見てくれる」と覚えて支障無さそうです。

コレを利用することにより、
> ・0/(ISTEXT(A:A)) ← 0 をTRUEかFALSEで割るってどういうこと？
これが活きてきます。
まず、先ほどの実験をそのまま使って、Ｃ１に「=B1*1」、下にフィルします。
そうすると、「ＴＲＵＥ」の行には「１」、「ＦＡＬＳＥ」の行には「０」が返ります。
「ＦＡＬＳＥ」はエクセルの内部的には「０」だということがわかります。
そのままＤ１セルに「=0/ISTEXT(A1)」として、下にフィルします。
数学的に「０（ＦＡＬＳＥ）で割ることはできない」ので、
Ａ列が文字列以外（Ｂ列が「ＦＡＬＳＥ」）の行には「#DIV/0」エラーが返ります。
一方、「ＴＲＵＥ」の行には「１」が返っているはずです。
「ＴＲＵＥ」はエクセル的には「１」と言うことです。
なので式は、「０÷１」の意味になり、「０」を返してきます。
つまり、「式：=0/(ISTEXT(A:A))」は「０かエラー」しか返さないのですね。

これらを利用して。
　　=LOOKUP(1,0/(ISTEXT(A:A)),A:A)
としてやることにより、
「Ａ列の配列（０かエラーの集合）から、１を探し、対応するＡ列のデータを返しなさい」
で、「０かエラー」しかありえない配列から探してくるわけですから、
最上段の「合致するデータが無かったら、配列の末端を返す」クセが発動して、
最終行のデータを引っ張ってくる、というカラクリです。


ラスト。
> ・0/(A:A<>"") ← 0 をTRUEかFALSEで割るってどういうこと？
これは「空白以外」には「ＴＲＵＥ」を返し、「空白」には「ＦＡＬＳＥ」を返す式です。
これも上同様に「ＴＲＵＥ＝１、ＦＡＬＳＥ＝０」を使ってやり、
「０とエラーの配列」を作って「合致するデータが無かったら、配列の末端を返す」クセを誘発し
　　=LOOKUP(1,0/(A:A<>""),A:A)
この式を実現させているわけです。



以上、長々と失礼いたしました。

この回答へのお礼

【お礼】
回答ありがとうございます。
何となく概要はわかりました。

ただ、質問中の★マークの３つの数式例のうち、
私の環境では、下の2つは動作しません。
環境：Windows XP／Excel2000

★2） [=LOOKUP(1,0/(ISTEXT(A:A)),A:A)] は、 [#DIV/0!]
★3） [=LOOKUP(1,0/(A:A<>""),A:A)] は、 [#NUM!]
が返ってきます。

【質問】
基本的な質問ですが、C1セルに [=LOOKUP(1,A:A,B:B)] と入力します
「う」と返ってきそうですが、 [#N/A]
　　A　　B　　C
1｜3　　あ　　#N/A
2｜2　　い
3｜1　　う
4｜2　　え
5｜3　　お

「お」と返ってきそうですが、 [#N/A]
　　A　　B　　C
1｜5　　あ　　#N/A
2｜4　　い
3｜3　　う
4｜2　　え
5｜1　　お

「え」と返ってきます
　　A　　B　　C
1｜1　　あ　　え
2｜0　　い
3｜1　　う
4｜1　　え
5｜0　　お


使い方が間違っているのでしょうか。

もし、上の「え」と返ってきたのが利用できるならば、
以下の数式でも使えそうです。
[=LOOKUP(1,(A:A<>"")*1,A:A)]
結果は、 [#NUM!] が返ってきてしまいますね…。

[A:A<>""] に、 [1] を掛ければ、空白セルは [FALSE*1=0] 、入力セルは [TRUE*1=1] となり
[検査範囲] は、 [0] か [1] の配列になります。

[検査値] として、 [1] を検索すれば、終端セルの値を返してくれそうなんですが…。


私の、質問中の★マークの３つの数式例よりも、
よりよい数式というのはあるのでしょうか？
（シンプルな方が好みです）

◆途中に空白があってもいいですよ
★1）数値の場合（Ａ列最後の行のデータ）
=LOOKUP(10^10,A:A)

★2）文字列の場合（Ａ列最後の行のデータ）
=LOOKUP(1,0/(ISTEXT(A:A)),A:A)

★3）数値・文字列混在の場合（Ａ列最後の行のデータ）
=LOOKUP(1,0/(A:A<>""),A:A)

お礼日時：2013/08/24 17:26