Excelの関数やVBAなどをTips集の本で勉強中です。
その中の「検索値の一部の値を含むリストから抽出したい」という項目があります。例としては、
A15:B20の範囲に、
足立区100
荒川区200
板橋区300
江戸川区400
大田区500
葛飾区600
という区コード表が入力されており、
A1に「東京都板橋区大原町1-1-1」と入力されているとき、B1に上のテーブルから区コードを求めて表示する、とあります。
このときB1に入る式が、
=LOOKUP(1,0/FIND($A$15:$A$20,A1,$B$15:$B$20)
となっているのですが、説明を読んでもなぜこの式で抽出できるのかがわかりません。(というか、この式の説明がないのです。こう入力すると求められます、としか書いてなくて…)
特に、
・LOOKUP関数の最初の1とはなんなのか?(普通ここには検索値が入りますよね)
・0/FIND(...) のところの意味は?
がわかりません。
ネットで検索しても、0/というのは日付などで大量にヒットするため、解説を見つけられません。誰か教えてください。
No.1ベストアンサー
- 回答日時:
>=LOOKUP(1,0/FIND($A$15:$A$20,A1,$B$15:$B$20)
◆式が違っていませんか?
=LOOKUP(1,0/FIND($A$15:$A$20,A1),$B$15:$B$20)
◆こんなことでは、ないでしょうか?
1)式=LOOKUP(1,0/FIND($A$15:$A$20,A1),$B$15:$B$20) が入力されているセルを指定します
2)数式バーで、「0/FIND($A$15:$A$20,A1)」を選択して、「F9」を押します
3)表示が、「{#VALUE!;#VALUE!;0;#VALUE!;#VALUE!;#VALUE!}」になります
4)LOOKUP関数は、LOOKUP(検査値,検査範囲,対応範囲) です
5)検査範囲に、検査値が無い場合は「検査値に一致する値がないと、検査値以下の値の最大値を検査結果とします。」
6)ということで、3番目の0を検索し、対応範囲の3番目の「300」を表示します
◆ 0/FIND(...) にすることで、検索値の「1」より小さくしています
No.2
- 回答日時:
こんばんは。
余計な話なのかもしれませんが、私は、その数式の成立について考えてみました。だから、直接の回答ではありません。
#1さんが書いていらっしゃるから、同じことは書きませんが、その数式を導き出すための、確実な論理や方法が見出せません。この数式を作った人は、偶然の産物か、もしくは、内部的な作成コードを知っている人だと思います。Excelの関数の一般的な仕様に反するように思います。LOOKUPは、他社との共有化を図る関数だそうですから、もしかしたら、他社製の仕様ではないでしょうか?
FIND($A$15:$A$20,A1)
=
{#VALUE!;#VALUE!;4;#VALUE!;#VALUE!;#VALUE!}
分子が0だから、その数字の部分は、0 になるけれど、本来は、FINDの戻り値ですね。
調べてみると、LOOKUPは、VALUEエラー値だけで、他のエラー値は受けないようです。
この場合、私なら、Excelでは、エラー値を、TRUE/FALSE に変換しないといけないと思ってしまいます。エラー値の連続の中で、値のみを見つけ出すような関数は、一般関数にはなかったように思います。エラー値が入れば、再び、同じか別のエラーを出すはずです。その仕様自体は、なぜ、そのようになっているのかは、製作側が提示していないとするれば、後は、偶然に一人の人が見つけたとしか思えません。
例えば、以下のような、私が配列で使うINDEX(ISERRORのINDEXは、過去の人たちが、使っていたワザなのですが、
=INDEX($B$15:$B$20,MATCH(FALSE,INDEX(ISERROR(FIND($A$15:$A$20,A1)),,),0),)
誰かが、見つけたワザです。私は、それを「定石」と呼んではいます。もともと、関数は、本当にMSが一環して作っているとは思えませんから、いろんな仕様があるのだとは思います。ある種、バグのようなもので、それは、自分が見つけようとしても、無理だと思います。こういうものは、ある日突然、神様のいたずらで見つかることはあっても、求めて求められるものではないような気がします。関数は、しょせん、結果論だから、VBAよりも遥かに難しい部分があると思います。
詳しい考察をありがとうございます。
なるほど、確かにこのTipsに関しては、いくら考えてでもわからなかった理由がわかりました。
他のTipsは、どんなに複雑でも、それぞれの関数の外部仕様を理解してよく考えれば、「なるほど、これがこうだからこうなるのか」と仕組みが理解できました。しかしこのやり方に関してはいくら考えてもわからない。その理由が、仰るようにExcelの一般的な仕様に反した内部的な仕様を利用していたものだから、とすると説明がつきます。
Excelの関数の仕様について、まだ何が一般的かもわからない状態ですが、このように一般的でない内部的な仕様(あるいはバグ?)を利用したやり方は、将来的にExcelがバージョンアップしたときに、FIND()の機能として公開されている仕様に変更がなければ普通に使っている分には問題ないかもしれませんが、内部的な動作が変わってしまうと互換性がなくなって動かなくなるかもしれない、という可能性がありますね。
どうもありがとうございました。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
このQ&Aを見た人はこんなQ&Aも見ています
関連するカテゴリからQ&Aを探す
おすすめ情報
- ・漫画をレンタルでお得に読める!
- ・ゆるやかでぃべーと タイムマシンを破壊すべきか。
- ・「I love you」 をかっこよく翻訳してみてください
- ・歩いた自慢大会
- ・許せない心理テスト
- ・字面がカッコいい英単語
- ・昔のあなたへのアドバイス
- ・かっこよく答えてください!!
- ・あなたが好きな本屋さんを教えてください
- ・スタッフと宿泊客が全員斜め上を行くホテルのレビュー
- ・【大喜利】【投稿~8/27】 こんなガソリンスタンド二度と来るか!なぜそう思った?
- ・これ何て呼びますか Part2
- ・人生で一番思い出に残ってる靴
- ・【お題】動物のキャッチフレーズ
- ・【お題】甲子園での思い出の残し方
- ・ゆるやかでぃべーと すべての高校生はアルバイトをするべきだ。
- ・「それ、メッセージ花火でわざわざ伝えること?」
- ・自分用のお土産
- ・人生で一番お金がなかったとき
- ・一番好きなみそ汁の具材は?
- ・泣きながら食べたご飯の思い出
- ・ちょっと先の未来クイズ第1問
- ・ゴリラ向け動画サイト「ウホウホ動画」にありがちなこと
- ・初めて自分の家と他人の家が違う、と意識した時
- ・単二電池
- ・チョコミントアイス
このQ&Aを見た人がよく見るQ&A
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
【マクロ】functionプロシージ...
-
Excelの警告について
-
Excelシートに画像を貼る時
-
googleのスプレッドシート
-
エクセル IF計算式?でしょうか?
-
【マクロ】for nest について ...
-
エクセル折れ線グラフについて...
-
EXCELで数値が異なった数字を足...
-
Excelで数値を時間数に変換する...
-
Excelについて
-
エクセルでファイルの最終更新...
-
エクセルを使用して、円周率を...
-
エクセルの数式バーのフォント...
-
【マクロ】VLOOKUPにて参照元に...
-
Excel 対象のセルに入力が無い...
-
エクセルでセルに標準で入力さ...
-
EXCELの散布図で日付が1900年に...
-
Excelで表を作ったところに文字...
-
【マクロ】名前を保存する際に...
-
Excelについて。Excelに縦1列に...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
エクセルの数式バーのフォント...
-
エクセルでファイルの最終更新...
-
2列に入っているデータを1列...
-
データチェックを行うエクセル...
-
再質問です。マクロの修正箇所...
-
エクセルで 自動的に◯や数字を...
-
数字入力後他の文字等が表示さ...
-
Excel 小さくなったスクロール...
-
F9キーについて。
-
【ExcelVBA】ダブルクォーテー...
-
Excelに詳しい方! B列が「日...
-
セルの数を求めたい
-
Excelを無料で使うには? パソ...
-
IFとIFS関数
-
【Excel】別シートから条件に合...
-
EXCELの散布図で日付が1900年に...
-
ある表にフィルターをかけて出...
-
【ExcelVBA】名前を付けて保存→...
-
Excelセルを跨いで合計を出す方法
-
エクセルのツールバーから数値...
おすすめ情報