Excel 値を返す数式についてです

Question

大変お世話になっております。
Sheet1のB列に、リスト（Sheet2）に基づきある値を返し、#N/Aは空白セルにした関数を入れております。（Sheet1のJ列が検索値です。）
=IFERROR(INDEX(Sheet2!$B$1:$B$6,MATCH(J2,Sheet2!$A$1:$A$6,FALSE)),"")
更に条件が追加され、
1）Sheet1のW列に或る文字が選択されている場合（”L”、”国”、”ラング”、等）上記リスト（Sheet2）に基づいたある値を返さず、空白にする数式をお教え頂けると有難いです。
2）もしくは、W列に上記の文字が入っている場合（”L”、”国”、”ラング”、等）上記リスト（Sheet2）に基づいたある値を返さず、何かしらの文字（”除外”、等）を返す数式をお教え頂けると有難いです。
（※注意です※：W列は『データの入力規則』のリストに基づき、プルダウンが設定してあります。）
（Win11 Office365です）
ご回答を楽しみにお待ちしております。
どうぞ宜しくお願い申し上げます。

goomania · Accepted Answer

fujillinさんも指摘されておられますが、ご質問文に曖昧な部分があるので、推測を交えた回答になってしまいます。

ご質問者が掲示された数式が2行目を検索値にしておられるので、この数式もB列2行目(つまりB2セル)に記述されているものと推測します。

また、同様にW列のどこに「或る文字(”L”、”国”、”ラング”、等)」があるのか不明ですが、「W列中いずれかに」ではなく、「Match関数の検索値と同じ行のW列に」と推測しました。

前置きはさておき、結論から申し上げるとと、ご質問者はIFERROR(INDEX～(MATCH(～)),"")という数式を使いこなしておられるので、難しく考えずこれをネスト(入れ子)にすればよいのではないでしょうか。

添付画像をご覧まください。

ご質問者が掲示した数式は、
=IFERROR(INDEX(Sheet2!$B$1:$B$6,MATCH(J2,Sheet2!$A$1:$A$6,FALSE)),"")･･････(a)
ですが、画像①は数式(a)のとおり、「sheet1のJ2と同じ値をsheet2にある表のA列から探して、見つかったらその表の同じ行のB列の値を表示し、見つからなかったら「空白」を表示する」という状態を示しており、W列は入力規則により選択入力するようになっています。

とりあえず数式(a)に使うsheet2の表は画像➁のように「ひらがな(あいうえお･･･)」を「アルファベット(ABCDE･･･)」に置換するという単純なものにしてあります。従ってJ2に「あ」がある場合、B2に「A」が表示されます。

また、
>W列は『データの入力規則』のリストに基づき、プルダウンが設定してあります。
ということなので、sheet3のA列に画像④のように”L”、”国”、”ラング”･･･を入れて入力規則のリストとして使用するものとします(リストはとりあえず6行分としています)。

ここで、sheet3のB列には入力規則のリストの分だけ"除外"と入力しておくものとします。

画像③のように、B2セルに

=IFERROR(INDEX(Sheet3!$B$1:B6,MATCH(W2,Sheet3!$A$1:$A$6,FALSE)),IFERROR(INDEX(Sheet2!$B$1:$B$6,MATCH(J2,Sheet2!$A$1:$A$6,FALSE)),""))

という数式を記述しています。
これで、添付画像③のようにB2に「除外」が表示されます。
つまり、ご質問者のいう、2)の状態が完成します。

ご質問者のいう、1)の状態を実現したいのなら、sheet3のB列に入力されている"除外"を削除して空白にすればよいだけです。

fujillin · Answer

No1です。

＞◆W列は『”Sheet1”』のW列です
あらそうでしたか。（すみません。勘違いしてしまいました。）

W列というのが列全体の意味なのか、ご提示の式で検索している値のある2行目のW2セルのことなのか不明ですけれど・・・
考え方としては、No1に示した通りです。

W2セルをチェックしたいのなら、検索する必要はなくなるので、No1の①の式はそのままW2を参照すれば良くなりますので、単にW2と置き換えれば済みます。

どの行で検索するときにでも、「W列全体のどこかにリストと一致する値があるか否かをチェックしたい」となるとちょいと複雑です。
COUNTIFで「一致する値があるか」をチェックできる事はNo1に示した通りですが、同じことをW列全体に対して行う必要があるので・・
　SUMPRODUCT(COUNTIF(W列の範囲,リストのセル範囲))
とすることで、まったく一致するものが無い場合には0が返り、一致するものがある場合はその数が返されます。
この結果を利用して判断すれば宜しいでしょう。
（作業列を利用して、一行ずつ「一致するものがあるかどうか」をチェックする方がわかりやすくなると思います。）
上の式は、それを一つにまとめたものですが、計算負荷は作業列を用いる場合に比して相当に増大します。

fujillin · Answer

こんばんは

ご質問文に曖昧な部分があるので、はっきりとはしませんが、以下勝手に解釈しました。

検索した同じ行のW列の値は、
INDEX(Sheet2!$W1:$W$6,MATCH(J2,Sheet2!$A$1:$A$6,FALSE)) 
で取得できます。（検索値が存在しない場合はエラー値が返る）
仮に、上式を①とします。

①の結果が、リストの中に存在するか（完全一致で良いものと解釈）は、
　COUNTIF(リストの範囲,①)　　--②
あるいは
　MATCH(①,リスト範囲,0)　　--③
などで調べられます。
（前者は無い場合に0、後者は無い場合はエラー値になります）

②の式を利用するなら、結果によって
　・エラー値の場合 -- 最初の検索行が見つからない場合
　・0の場合 -- 検索行はあるがリストにはない場合
　・1以上の場合 -- 検索行もあり、結果がリストにある場合
と考えることができます。

③を利用するなら、エラーの返る場合は、
　・最初の検索で該当行が見つからない場合
　・該当行があって、その値がリストにない場合
値（数値）が返る場合は、該当行がありかつリストにその値がある場合
と考えられます。

ご質問の、1）、2）の内容によって、上記のうち利用しやすいものを使えば宜しいのではないでしょうか。

Excel 値を返す数式についてです

fujillinさんも指摘されておられますが、ご質問文に曖昧な部分があるので、推測を交えた回答になってしまいます。

No1です。

こんばんは

似たような質問が見つかりました

関連するカテゴリからQ&Aを探す

デイリーランキングこのカテゴリの人気デイリーQ&Aランキング

マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング