HOME > Q&A > Excel （一般機能）

Excel （一般機能）
Excelの一般機能に関するフォーラムです。
  • 解決済みのトピックにはコメントできません。
このトピックは解決済みです。
質問

 
(指定なし : 指定なし)
この関数で何故やりたいことが実現できるのか教えてください③
投稿日時: 22/05/14 05:32:21
投稿者: のらねこ

同じ質問の繰り返し投稿で恐縮ですが、こちらの関数の理屈についてご教授ください。
 
曖昧検索
https://detail.chiebukuro.yahoo.co.jp/qa/question_detail/q11259994876
 
①のパターン
=IFERROR(LOOKUP(1,0/FIND(値が含まれる範囲,参照セル),返す文字列の範囲),"")
 
URL先の通り、A2が参照セル、D2:D4が値が含まれる範囲、E2:E4が返す文字列の範囲
A2はD4の文字列を1文字目に含むとします
 
式
　=IFERROR(LOOKUP(1,0/FIND(D$2:D$4,A2),E$2:E$4),"")
 
IFERRORは文字列を含まなかったときのエラー回避だから一旦スルー
　=LOOKUP(1,0/FIND(D$2:D$4,A2),E$2:E$4)
 
FINDで値が含まれる範囲の文字列が参照セルの何文字目かを返す（例ではD4がA2の1文字目に含む）
　=LOOKUP(1,0/{FIND(D2,A2),FIND(D3,A2),FIND(D3,A2)},E$2:E$4)
 
FINDの結果、文字列が含まれれば開始位置を、含まれなければ#VALUE!が配列に入る
　=LOOKUP(1,0/{#VALUE!,#VALUE!,1},E$2:E$4)
 
ここまでは自分なりに理解してみたのですが、
　0/{#VALUE!,#VALUE!,1}
この部分で、0/#VALUE!も0/1もエラーでは？？とつまづいてしまいました。
また上記の理解ができれば分かるのかもしれませんが、
　LOOKUP(1
この部分も、LOOKUPで1を検索する意味が理解できませんでした。
 
 
②のパターン
=INDEX(返す文字列の範囲,MATCH(0,0/FIND(値が含まれる範囲,A2),0))&""
 
こちらはコピペでテストしてみましたがうまく挙動しませんでした。
またやはりこの部分が理解できませんでした。
　0/{#VALUE!,#VALUE!,1}
 
 
私はLOOKUP関数がうまく使いこなせないのと、
IFERROR関数でのエラー回避はできる限り避けたいため、できれば②を使いたいと考えています。
考えの至らない点についてアドバイスをいただけますと幸いです。
投稿日時: 22/05/14 07:54:38
投稿者: のらねこ

すみません、思い込みで「0/1（=0）」を「1/0（=エラー）」と勘違いしていました！
 
①のパターン
 
=IFERROR(LOOKUP(1,0/FIND(D$2:D$4,A2),E$2:E$4),"")
=IFERROR(LOOKUP(1,0/{FIND(D2,A2),FIND(D3,A2),FIND(D3,A2)},E$2:E$4),"")
=IFERROR(LOOKUP(1,0/{#VALUE!,#VALUE!,1},E$2:E$4),"")
=IFERROR(LOOKUP(1,{#VALUE!,#VALUE!,0},E$2:E$4),"")
LOOKUPの検索値「1」の近似値は「#VALUE!,#VALUE!,0」の内「0」のみなので、3番目の配列に相当するE4を返す
いずれの値も含まない場合は「#VALUE!,#VALUE!,,#VALUE!,,#VALUE!・・・・」とエラーばかりの配列になるので、IFERRORでブランクに置き換える
 
この考えでいかがでしょうか。
上記の場合1つわからないのが、LOOKUPの検査値を「0」ではなく「1」にしないのはなぜかということです。
0/FIND(・・・　で含まれる場合は必ず「0」が配列に入るので、近似値の「1」ではなく「0」を入れないメリットが何かあるのでしょうか？
 
②のパターンについて
=INDEX(E:E,MATCH(0,0/FIND(D:D,A2),0))&""
=INDEX(E:E,MATCH(0,0/{FIND(D2,A2),FIND(D3,A2),FIND(D3,A2)},0))&""
=INDEX(E:E,MATCH(0,0/{#VALUE!,#VALUE!,1},0))&""
=INDEX(E:E,MATCH(0,{#VALUE!,#VALUE!,0},0))&""
=INDEX(E:E,3)&""
 
①の勘違いを踏まえて、ここまで自分で考えてみたのですが、返す文字列の範囲を列選択するとE1の項目も含まれてしまってずれてしまうのでは？となったあたりで訳がわからなくなってしまいました。
考え方の不足・誤りについてご指摘をお願いします。
回答
投稿日時: 22/05/14 23:00:14
投稿者: 半平太

LOOKUPで「1」を検索値にする理由は、一番最後のものをヒットさせたい為です。
 
サンプルは単純すぎて、頭からヒットさせても、同じものになってしまいます。
最低二つヒットするケースで違いを考察する必要があります。
 

(1) C1セル =LOOKUP(1,A1:B6)                最後尾
(2) D1セル =LOOKUP(0,A1:B6)　　　　　　　　予測不能
(3) E1セル =INDEX(B1:B6,MATCH(0,A1:A6,0))　最初

<ケース1>
行  ___A___  _B_  _C_  _D_   E 
 1        0   1    6    3    1
 2  #DIV/0!   2               
 3        0   3               
 4  #DIV/0!   4               
 5  #DIV/0!   5               
 6        0   6               

※D1セルの数式は3行目がヒットしましたが、たまたまに過ぎません。
　凡人には予測つかない位置でヒットします。危ない数式です。

 
<ケース2>
行  ___A___  _B_  _C_  _D_   E 
 1        0   1    6    6    1
 2  #DIV/0!   2               
 3  #DIV/0!   3               
 4  #DIV/0!   4               
 5  　　　0   5               
 6        0   6

回答
投稿日時: 22/05/15 06:39:07
投稿者: WinArrow
投稿者のウェブサイトに移動

最初の投稿文に戻って

引用:

ここまでは自分なりに理解してみたのですが、
　0/{#VALUE!,#VALUE!,1}
この部分で、0/#VALUE!も0/1もエラーでは？？とつまづいてしまいました。
また上記の理解ができれば分かるのかもしれませんが、
　LOOKUP(1
この部分も、LOOKUPで1を検索する意味が理解できませんでした。

>この部分で、0/#VALUE!も0/1もエラーでは？？とつまづいてしまいました。
「0/#VALUE!」はエラー
「0/1」はエラーではありません。
答えは「0」です。
 
>LOOKUPで1を検索する意味が理解できません
 
【LOOKUP関数の仕様】
検索値が検索範囲に存在しない場合、
検索値以下の最大値を返す
ということで
{#VALUE!,#VAUE!,0}
となります。
「0」の位置は、３番目ですから、
対応する範囲（E2:E4）の3番目の値が返ります。

投稿日時: 22/05/15 12:23:01
投稿者: のらねこ

半平太さん、WinArrowさん
 
ご丁寧な解説ありがとうございます。
引っかかっていた部分が理解できました。
次は自分で一から組み立てられるように取り組んでみます。
ご教授ありがとうございました！