Excel 関数

質問

文字と数字（例えば111aaa）から、数字だけを抽出する関数を作ろうとしたら上手くいかず、ネット上で調べたら次のようなものが出てきました。

=LOOKUP(10^17,LEFT(A1,COLUMN($1:$1))*1)

となっていました。うまく作動はするのですがどうも意味がわからないところがあります。
Q,　Column($1:$1)を単独で用いると１を返します。それをLEFTの文字数に表したところで１しか表さないと思うのですが、lookupまで用いると、lookupで探している値に近くなる数字を探し、Column($1:$1)が任意に動いているような挙動をしています。これはなぜなのでしょうか？

回答No.8

bunjii · ベストアンサー

>これは配列として{"1","11","111",111a","111aa",・・・・}があるけど、その先頭にくる１しか返さないということですか？
違います。
数式としては配列の結果を返しますが入れ物が1つの場合は先頭の1つを入れて残りの値は入らないので捨てられると考えると分かり易いでしょう。
つまり、B1セルへ次の数式を設定すると1つのセルへは1つの値（文字列も数値も扱いは同じ）しか入りません。
=LEFT(A1,ROW($A:$A)) → "1" （文字列の1）
単独でLEFT関数を使うと切り出す位置を複数（配列値）にしても先頭の1つしか返せないのですが配列値の受け皿がある関数で用いると複数（配列値）の結果を返します。
=INDEX(LEFT(A1,ROW($A:$A)),[行番号]) → {"1";"11";"111";"111a";"111aa";・・・・} のような結果になり、行番号を順次指定すれば指定の[行番号]の値を取り出せます。
同様のことをLOOKUP関数で応用しています。
尚、COLUMN($1:$1)は列番号の全てを返し、ROW($A:$A)は行番号の全てを返します。
また、配列値の表現でカンマ（,）は列の区切りでセミコロン（;）は行の区切りになります。
添付画像はLOOKUP関数の引数を示すものです。

お礼

忙しくお返事が遅れてしまい申し訳ございません。
お聞きしたかったことを細かく説明してくださり、整理ができとてもスッキリしました。これで少しずつ応用を考えて行けそうです。
たくさんの回答本当にありがとうございました。

回答No.9

msMike

[No.6お礼]へのコメント、
ありがとうございました。

さて、式 =LOOKUP(10^17,LEFT(A1,COLUMN($1:$1))*1)
の解説の件、
既に得られた回答で“解決”されたとは思いますが、お約束していたので私なりの“怪説”をしておきます。
１．範囲 D5:M5 を選択して、マウスカーソルを[数式バー]上に移
￣￣して式 =COLUMN($1:$1) を入力して、Ctrlキーと Shiftキーを
￣￣抑えたままで Enterキーを「エイヤッ！」と叩きつけ(配列数
￣￣式作成の操作)。(→Fig-1)
２．同じ要領で、範囲 B3:H3 を選択⇒
￣￣式 =LEFT(A1,COLUMN($1:$1)) を入力⇒
￣￣Ctrl+Shift+Enter (→Fig-2 の３行目)
３．式 =B3*1 を入力したセル B2 を右方にオートフィ
￣￣ル (→Fig-2 の２行目)
４．式 =LOOKUP(10^17,B2:H2) をセル B1 に入
￣￣力 (→Fig-2 の１行目)

お礼

お返事が遅れてしまい誠に申し訳ございません。
他の方の回答も大変わかりやすかったのですが、msMikeさんの回答は自分で配列一つ一つをexcelに打ち込むことで視覚的に、見ただけでわかりやすくすんなりこんなことだったのかと、理解することができました。
本当に有難うございました。

回答No.7

craftsman_t

No3の続きです。

>配列の初めの数字が返される仕組みになっているのですか？
そうです。
代入される配列の、最初の数値から、新しい配列の最初の数値が計算されます。
2番目以降も、代入される配列と、新しい配列が対応します。

>あと文字に数を書けたら、全て０を返すということですか？
失礼しました。私の書き方が不適切でした。
おっしゃられるように、エラーとなり、#VALUEという値になります。
なので正しくは、

LEFT(A1,　COLUMN($1:$1))*1
　={1,11,111,#VALUE,#VALUE,...}
となります。

お礼

回答ありがとうございました。
とてもわかり易い回答で理解が深まりました。
お時間をいただき本当にありがとうございました。

回答No.6

msMike

[No.1補足]へのコメント、
》 こちらで拝見しました。
そのページの何処に例の式 =LOOKUP(10^17,LEFT(A1,COLUMN($1:$1))*1) が載ってますか？

お礼

すみません。以下の２つのサイトに載っていました。
http://www.geocities.jp/chiquilin_site/data/050223_numerical_extraction.html
https://qiita.com/mhara/items/82421d1b34e88a3efba1

回答No.5

bunjii

>配列が答えの場合、たくさん答えがあるのでエラーになるような気がしていたのですが、この場合初、めの一桁の数字だけを返すという認識で大丈夫でしょうか。
LOOKUP関数の第2引数（配列値）は次のようになります。
LEFT(A1,COLUMN($1:$1))*1
　　　　　↓
{"1","11","111",111a","111aa",・・・・}*1
　　　　　↓
{1,11,111, #VALUE!,#VALUE!,・・・・}
LOOKUP関数の検査範囲の配列に含まれる#VALUE!（数値でない値を演算したときのエラー値）をExcelで扱える数値より大きい値と見做していると考えてください。
従って、検査値の10^17（1E+17）より#VALUE!の方が大きいため検査値以下の最大値が111になりますので、LOOKUP関数の演算結果は111になります。
LOOKUP関数の検査範囲は複数の値として評価されますので、初めの1桁の数字が返されるとの認識は誤りです。

お礼

回答ありがとうございます。すみません、少し質問意図がずれてしまったかもしれません。lookupを使うときではなく、次のようなときは「１」だけしか返しませんよね？
A1=111aaa
=LEFT(A1,ROW($A:$A))
これは配列として
{"1","11","111",111a","111aa",・・・・}
があるけど、その先頭にくる１しか返さないということですか？
もっと簡単には
ROW($A:$A)
は
={1,2,3,4,5...}
という配列を表しているけど、1しか返さないのは先頭のものだけを返すからということですか？

回答No.4

imogasi

直接の回答でないのですみません。私の長年のＯＫＷＡＶＥの回答経験から参考に述べてみます。
関数の質問での回答で
・配列数式
・ベクトル形式と配列形式の別のある関数
・式の中に組み合わせて３つ以上の式を組み合わせた（ネストした）もの
が出てきたら、
関数を使った課題解決は、初心者には、難しい部類です。
ーー
例えば、条件を付けて、他のセル範囲に抜き出す関数（それもワーク列を使わない方法）
https://oshiete.goo.ne.jp/qa/8349027.htmlのＮＯ・５の回答のようなもの。
ーー
それにＬＯＯＫＵＰ形式の１０＾１７なども、エクセル関数の解説書のＬＯＯＫＵＰ関数の説明文章の終りの方に、ちょこっと説明されている機能を、熟達者が課題に応用し、それがエクセル中級以上の人たちに広まったものです。
http://www.kenzo30.com/ex_kisotyu/ex_ks_tyukyu9_9_6.htm
の「検索/行列関数（LOOKUP関数）」を読むのが、良いと思います。
ーー
意外にこれらの回答をコピペして、結果が出たらＯＫ、有難う、という質問者が多いのようですが、理解できる質問者もいるのかなと、人間の可能性に驚いています。
ーー
関数は
・中間的変数（術語です）を一時的に保持したてのちに使ったり、
・繰り返し処理をして結果を出す方式ができません（式の複写などで済ませます）
ーー
　こういう関数どう付き合っていくべきか、小生の場合は、自分の非力を思い知らされ、悩ましい。それで小生は、ＶＢＡでの回答に傾く場合が多いです。
しかしこちらの方がむつかしいと考える人も多いわけで、悩ましい。
ＶＢＡで使う思考ロジックはＶＢＡなら簡単という場合も多いから。
例えばある列のデータで、Ｆｉｎｄでデータのある行を知り、他の列のデータを採るというのは、人間が表引き（縦列内を目で探し、列をづらして探す）を手作業でやる場合の思考の、アナロジーで、それをプログラムを組めば、出来るのです。

お礼

回答ありがとうございます。
VBA（マクロ）は自分で記録するものしかやったことがなかったので、選択肢にありませんでした。時間ができたときにでも、どんなものか見てみてそのほうが直感的にできそうでしたら、VBAも勉強してみたいと思います。

それにしてもExcelって、こんなことまでできるのかと感心するばかりです。
ちなみに別の質問のNo,5さんの式の長さには頭が痛くなりました。こんなのをさっと考えつけるようにいつかなるでしょうか......（無理ですね。）

回答No.3

craftsman_t

横から失礼いたします。

COLUMN($1:$1)により、{1,2,3,4,5...}という数字の集まりが作られます。
これを、配列と呼びます。

たとえば配列を手入力で指定した、下図のシートと次の関数でも、同じ結果になります。
=LOOKUP(10^17,LEFT(A1,A3:IV3))*1)

なお関数の働き方は

COLUMN($1:$1)
　={1,2,3,4,5...}

LEFT(A1,　COLUMN($1:$1))
　={1,11,111,111a,111aa...}

LEFT(A1,　COLUMN($1:$1))*1
　={1,11,111,0,0...}

LOOKUP関数は入力された配列から、10^17以下の最大値を探すため、111が返されます。

補足

回答ありがとうございます。配列の考え方が詳しくそれに関しては納得しました。
しかし答えが配列の場合、例えば
LEFT(A1,　COLUMN($1:$1))
　={1,11,111,111a,111aa...}
において、この場合「１」が返されるのはなぜでしょう。配列の初めの数字が返される仕組みになっているのですか？

あと文字に数を書けたら、全て０を返すということですか？
本来であれば、エラーがででくるところ、わかりやすく「０」にしていただいているのですか？初歩的な質問ばかりで申し訳ありません。

回答No.2

bunjii

>Q,　Column($1:$1)を単独で用いると１を返します。
Column($1:$1)は1行目のカラム全体を1～16384の値の配列値として返しますので=Column($1:$1)を単独で実行すれば先頭の1のみを返します。

>lookupまで用いると、lookupで探している値に近くなる数字を探し、Column($1:$1)が任意に動いているような挙動をしています。
LOOKUP関数のヘルプでどのような使い方ができるかを学習してください。
LOOKUP関数の第1引数は検索値です。第2引数は検索範囲の配列値です。第3引数は省略可能です。
提示の数式で第1引数（検索値）が10^17（100,000,000,000,000,000）で、第2引数が（検索範囲）1,11,111の3個と163841個の#VALUE!エラーの配列値です。
第3引数（対応範囲）を省略すると第2引数が対応範囲として動作します。
LOOKUP関数の返り値は対応範囲の中で検索値に1番近い値を返しますので111が今回の目的に合うと判断されます。
従って、111が目的の値になります。

お礼

何度もいろいろな質問に答えていただき本当に有難うございます。
なるほど、わかってきました。1,11,111という事だったのですね。そのあたりに関しては理解できてきました。あとは配列の考え方が少し納得出来ないところです。
配列が答えの場合、たくさん答えがあるのでエラーになるような気がしていたのですが、この場合初、めの一桁の数字だけを返すという認識で大丈夫でしょうか。

回答No.1

msMike

その式、誠にお見事、感心いたしました。
何処でそれを目にされました？
それが記載されているページの URL を教えてください。
そうしたら、私が優しく解説して差し上げます。

補足

こちらで拝見しました。
http://www.nec-nexs.com/bizsupli/useful/excel/21.html