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.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()の機能として公開されている仕様に変更がなければ普通に使っている分には問題ないかもしれませんが、内部的な動作が変わってしまうと互換性がなくなって動かなくなるかもしれない、という可能性がありますね。
どうもありがとうございました。
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」より小さくしています
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Excel(エクセル) EXCELでの複雑な複数条件について 4 2022/05/09 16:19
- Visual Basic(VBA) VBA初心者です 検索した数字の行に色をつける 5 2023/02/13 14:22
- Visual Basic(VBA) 【VBA】特定の文字で改行(次の行)に行きたい。 3 2022/04/11 17:20
- 政治 参議院の一票の格差を解消し、なおかつ問題の多い隣の県との合区を解消する方法を考えました 2 2022/11/15 11:40
- Excel(エクセル) ExcelのIF関数について 4 2023/05/24 12:54
- Excel(エクセル) 至急です><Excelの関数を教えてください。 2 2022/03/22 17:56
- Excel(エクセル) 【Excel】住所に郵便番号を付記する方法 3 2022/05/07 17:15
- Excel(エクセル) Excelマクロの表示のExcel内をfindで検索 3 2022/06/15 20:07
- Excel(エクセル) VLOOKUP が機能しない、その原因は何 ? 8 2022/10/19 12:06
- Excel(エクセル) 範囲選択すると最後の一行で急に出てくる#が邪魔で困っています。 ExcelでVLOOKUPで引っ張っ 2 2022/08/31 10:03
このQ&Aを見た人はこんなQ&Aも見ています
-
プロが教える店舗&オフィスのセキュリティ対策術
中・小規模の店舗やオフィスのセキュリティセキュリティ対策について、プロにどう対策すべきか 何を注意すべきかを教えていただきました!
-
EXCELで0/範囲
Excel(エクセル)
-
【Excel】部分一致の逆引きの方法について
Excel(エクセル)
-
VLOOKUP関数 、住所、 部分一致 (困っています。至急即応頂けると助かります)
その他(Microsoft Office)
-
-
4
エクセルのFIND関数の複数セル参照について
その他(Microsoft Office)
-
5
エクセルで空白セルを含む列の最終行の値を取得する式を教えてください
Excel(エクセル)
-
6
もしセルが#N/A"なら~をする・・・には?"
Excel(エクセル)
-
7
エクセル:マクロ「Application.CutCopyMode = False」って?
Excel(エクセル)
-
8
IF関数で空欄(")の時、Nullにしたい
その他(Microsoft Office)
-
9
エクセル関数について(フォントサイズ変更)
Excel(エクセル)
-
10
複数の文字列のいずれかが含まれていたらTRUEを返す関数について
Excel(エクセル)
-
11
部分一致の対象を表から抽出する方法
Excel(エクセル)
-
12
Excelで行ごとコピー、同じ行を3行にしたい
Excel(エクセル)
-
13
Vlookup関数とcountif関数の組み合わせについて
Excel(エクセル)
-
14
【Excel関数】UNIQUE関数で0"を返さない方法?"
Excel(エクセル)
関連するカテゴリからQ&Aを探す
おすすめ情報
このQ&Aを見た人がよく見るQ&A
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
エクセル初心者です 関数の入れ...
-
【関数】先頭だけにある、半角...
-
エクセル 白黒印刷で白線を印刷...
-
Excelのチェックボックスの使い...
-
【関数】適切な文字数の数字を...
-
Excelのpivotについて質問です
-
Excel ピボットテーブルで日付...
-
LOOKUP関数を使えばいいのでし...
-
エクセル関数を教えてください
-
エクセルのセルに同じ大きさの...
-
UNIQUE関数が使えないバージョ...
-
excelの不要な行の削除ができな...
-
エクセルで「-0.0」と表示さ...
-
時間によってファイル名が変わ...
-
WPS OFFICEでの縦書きについて
-
エクセルの関数について教えて...
-
Aというブックの1というシート...
-
【マクロ】シート名を取得する...
-
VBA Private Sub Worksheet_Cha...
-
VBA、Excelのworkbook.open に...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
Excel 2019 のピボットテーブル...
-
[関数得意な方]教えて下さい・...
-
Excelにてある膨大なデータを管...
-
[関数について]わかる方教えて...
-
Excel初心者です。 詳しい方、...
-
excelの不要な行の削除ができな...
-
エクセル関数に詳しい方教えて...
-
INDIRECTを使わず excelで複数...
-
[オートフィルタ]で抽出された...
-
エクセルの神よ、ご回答を! エ...
-
エクセル関数に詳しい方、教え...
-
各ページの1番上の表示について
-
Excelで写真のような表を作った...
-
エクセルで不等号記号(≠)が上に...
-
数学 Tan(θ)-1/Cos(θ)について...
-
Excel 2019 は、SPILL機能があ...
-
Excelで全角を半角にしたいので...
-
条件付き書式を教えてください
-
Excel フィルターを掛けた状態...
-
[オートフィルタ]の適用範囲の...
おすすめ情報