
大変お世話になっております。
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ベストアンサー
- 回答日時:
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列に、値を返すことが出来ました。空白も返せました! 様々な応用が出来ました…! ご親切を下さいまして有難うございます…!
どうしても同列に条件を追加しなければならず、一昨日の夜遅くまで色々と試しており、通勤中、電車に乗りながらスマートフォンで調べておりましたが、全く出来ず良い数式も見つからず諦めていました…。
お陰様で、本当に助かりました…!
心より感謝申し上げます…!
また別の件で質問をさせて頂くかと思います…。
その折にも是非宜しくお願い致します…!
この度はご親切と貴重なお時間を下さいまして本当に有難うございました!
深く感謝申し上げます!
今後共、どうぞ宜しくお願い申し上げます…!
No.2
- 回答日時:
No1です。
>◆W列は『”Sheet1”』のW列です
あらそうでしたか。(すみません。勘違いしてしまいました。)
W列というのが列全体の意味なのか、ご提示の式で検索している値のある2行目のW2セルのことなのか不明ですけれど・・・
考え方としては、No1に示した通りです。
W2セルをチェックしたいのなら、検索する必要はなくなるので、No1の①の式はそのままW2を参照すれば良くなりますので、単にW2と置き換えれば済みます。
どの行で検索するときにでも、「W列全体のどこかにリストと一致する値があるか否かをチェックしたい」となるとちょいと複雑です。
COUNTIFで「一致する値があるか」をチェックできる事はNo1に示した通りですが、同じことをW列全体に対して行う必要があるので・・
SUMPRODUCT(COUNTIF(W列の範囲,リストのセル範囲))
とすることで、まったく一致するものが無い場合には0が返り、一致するものがある場合はその数が返されます。
この結果を利用して判断すれば宜しいでしょう。
(作業列を利用して、一行ずつ「一致するものがあるかどうか」をチェックする方がわかりやすくなると思います。)
上の式は、それを一つにまとめたものですが、計算負荷は作業列を用いる場合に比して相当に増大します。
fujillin 様
お世話になっております。
ご連絡を下さいまして、本当に有難うございました。
質問内容が曖昧にも関わらず、色々とご指南を下さいまして感謝申し上げます!
仕事をしており、お礼が遅くなりまして大変申し訳ございませんでした…。
また質問をさせて頂くと思います。
その折にも、どうぞ宜しくお願い致します!
この度は本当に有難うございました。
今後共、どうぞ宜しくお願い申し上げます…!
No.1
- 回答日時:
こんばんは
ご質問文に曖昧な部分があるので、はっきりとはしませんが、以下勝手に解釈しました。
検索した同じ行の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は使わなくて大丈夫です)。
もし宜しければ、ご回答を下さいますととても有難い限りです!
ご多忙中 大変申し訳ございません…。
ご連絡を楽しみにお待ちしております。
どうぞ宜しくお願い申し上げます。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
このQ&Aを見た人はこんなQ&Aも見ています
-
プロが教えるわが家の防犯対策術!
ホームセキュリティのプロが、家庭の防犯対策を真剣に考える 2組のご夫婦へ実際の防犯対策術をご紹介!どうすれば家と家族を守れるのかを教えます!
-
Excel 計算式を教えて下さい
Excel(エクセル)
-
何故割り算なのでしょうか?
Excel(エクセル)
-
Excelで日数の関数がうまく出せません。
Excel(エクセル)
-
4
Excel教えてください。 下記のことが出来るのは、マクロですか?条件付き書式でしょうか、、?知恵を
Excel(エクセル)
-
5
VLOOKUP が機能しない、その原因は何 ?
Excel(エクセル)
-
6
Excelでこの式の意味をおしえていただけますでしょうか、またどのように理解したらいいのでしょうか
Excel(エクセル)
-
7
10円の誤差が分からない
Excel(エクセル)
-
8
該当日が期間内に当てはまる場合、開始日と終了日をExcel関数を用いて入力させたい
Excel(エクセル)
-
9
excelの数式の書き方について。 以下のような数式をSheet1に書いています。 Sheet1のB
Excel(エクセル)
-
10
エクセルで期間に入っているかどうかの判定
Excel(エクセル)
-
11
エクセル、日々の集計整理方法。(再送です。)
Excel(エクセル)
-
12
特定の条件で計算式を組みたいのですが、教えて頂けますでしょうか?
Excel(エクセル)
-
13
処理年月が連続(指定年月~何ケ月間)している人のみ抽出をExcelのみで可能でしょうか
Excel(エクセル)
-
14
合計額がゼロになってしまう
Excel(エクセル)
-
15
EXCELで次の数式を教えてください。
Excel(エクセル)
-
16
COUNTIFS関数で結果が1多い
Excel(エクセル)
-
17
年間仕事用シフトカレンダーに、日勤 夜勤 休み に色分けをした。
Excel(エクセル)
-
18
エクセルの関数式を教えてください。
Excel(エクセル)
-
19
エクセルで 例えばA1のセルに1+1 と入力すると B1のセルに2 と答えが出る関数があったと思いま
Excel(エクセル)
-
20
Excelにて、下記のようなデータを月だけ変えて下にドラックしていきたいです。どうすれば良いですか?
Excel(エクセル)
関連するカテゴリからQ&Aを探す
このQ&Aを見た人がよく見るQ&A
このカテゴリの人気Q&Aランキング
-
4
EXCELで2つの数値のうち大きい...
-
5
あるセルに特定の文字列を打つ...
-
6
エクセルの2ページ目の作り方
-
7
グラフの横・縦項目が全部表示...
-
8
(Excel)あるセルに文字を入力...
-
9
エクセルのプロパティーでセキ...
-
10
EXCELで2列を参照し、重複する...
-
11
Excel に貼り付けた図形が、保...
-
12
Excel関数で、範囲内の最後のセ...
-
13
エクセルで作った新しいウイン...
-
14
EXCEL VBAで全選択範囲の解除
-
15
エクセルのセル内の余白の設定...
-
16
VBAで保存しないで閉じると空の...
-
17
Excel 書式を関数で判断。
-
18
エクセルのセル内に全角数字を...
-
19
エクセル:シート名を手入力で...
-
20
セル入力文字が、「右のセルに...
おすすめ情報
公式facebook
公式twitter