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

大変お世話になっております。
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です）
ご回答を楽しみにお待ちしております。
どうぞ宜しくお願い申し上げます。

No.3 ベストアンサー 回答者： goomania 回答日時： 2022/11/22 23:40

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列に入力されている"除外"を削除して空白にすればよいだけです。

この回答へのお礼

goomania 様

大変お世話になっております。
ご連絡とご回答を下さいまして、本当に有難うございました!

今、goomania 様の数式を使わせて頂きました。
驚く程簡単に実現出来ました…!
画像も添付下さいまして、とても解り易かったです…!

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

有難うございます…! その通りです!!

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

その通りです…! goomania 様の推測が的確でしたため、とても嬉しいです…! 本当に有難うございます!

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

考え方もご教授下さいまして、感謝申し上げます…! 入れ子にすれば、色々と条件の追加が出来るのですね…。入れ子の数式の型が解り大変勉強になりました…!

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

有難うございます! sheet3のB列に除外だけでなく、他の文言を記載すると、Sheet1のB列に、値を返すことが出来ました。空白も返せました! 様々な応用が出来ました…! ご親切を下さいまして有難うございます…!

どうしても同列に条件を追加しなければならず、一昨日の夜遅くまで色々と試しており、通勤中、電車に乗りながらスマートフォンで調べておりましたが、全く出来ず良い数式も見つからず諦めていました…。
お陰様で、本当に助かりました…!
心より感謝申し上げます…!

また別の件で質問をさせて頂くかと思います…。
その折にも是非宜しくお願い致します…!
この度はご親切と貴重なお時間を下さいまして本当に有難うございました!
深く感謝申し上げます!
今後共、どうぞ宜しくお願い申し上げます…!

お礼日時：2022/11/23 05:09

No.2 回答者： fujillin 回答日時： 2022/11/21 22:12

No1です。

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

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

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


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

この回答へのお礼

fujillin 様
お世話になっております。
ご連絡を下さいまして、本当に有難うございました。
質問内容が曖昧にも関わらず、色々とご指南を下さいまして感謝申し上げます!
仕事をしており、お礼が遅くなりまして大変申し訳ございませんでした…。
また質問をさせて頂くと思います。
その折にも、どうぞ宜しくお願い致します!
この度は本当に有難うございました。
今後共、どうぞ宜しくお願い申し上げます…!

お礼日時：2022/11/23 05:16

No.1 回答者： fujillin 回答日時： 2022/11/21 20:35

こんばんは

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

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

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


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

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

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

この回答へのお礼

大変お世話になっております。
ご回答を下さいまして本当に有難うございます。

当方の記載が分かりづらく申し訳ございませんでした。

◆W列は『”Sheet1”』のW列です。

Sheet1のB列に、リスト（Sheet2）に基づきある値を返す、関数を入れております。（Sheet1のJ列が検索値です。）
=INDEX(Sheet2!$B$1:$B$6,MATCH(J2,Sheet2!$A$1:$A$6,FALSE))
として（=IFERRORは使わず…）

それに加えて、

1）『”Sheet1”』のW列に或る文字が選択されている場合（”L”、”国”、”ラング”、等）上記リスト（Sheet2）に基づいたある値があったとしてもその値を返さず、Sheet1のB列を空白にする数式をお教え頂けると有難いです。

2）もしくは、『”Sheet1”』のW列に上記の文字が入っている場合（”L”、”国”、”ラング”、等）上記リスト（Sheet2）に基づいたある値があったとしてもその値を返さず、Sheet1のB列に代わりに何かしらの文字（”除外”、等）を返す数式をお教え頂けると有難いです。

◆『”Sheet1”』のW列は『データの入力規則』のリストに基づき、プルダウンが設定してあります。
◆『”Sheet1”』のW列に（”L”、”国”、”ラング”、等）がなく、上記リスト（Sheet2）に基づいたある値があれば、リスト（Sheet2）に基づいた値を返したいです。
◆『”Sheet1”』のB列に上記、1）か　2）を満たす数式を使うとすると、どのような数式になるかをお教え下さいますと有難いです（=IFERRORは使わなくて大丈夫です）。

もし宜しければ、ご回答を下さいますととても有難い限りです!
ご多忙中 大変申し訳ございません…。
ご連絡を楽しみにお待ちしております。
どうぞ宜しくお願い申し上げます。

お礼日時：2022/11/21 21:53