大変お世話になっております。
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で質問しましょう!
似たような質問が見つかりました
- その他(Microsoft Office) Excel 2列の値を返す数式についてです 1 2022/11/23 22:59
- Excel(エクセル) SUMIFSと日付変換 10 2023/04/16 15:38
- Excel(エクセル) VLOOKUP が機能しない、その原因は何 ? 8 2022/10/19 12:06
- Excel(エクセル) ある数値に対して、値を返す数式についてです 2 2022/09/13 22:06
- その他(Microsoft Office) 従業員増減対応で当番種類の増減対応な当番表 21 2022/07/19 07:30
- Excel(エクセル) Excelのリストにある文字を含むセルを、複数の色で色付けしたいです 2 2022/08/11 17:39
- Excel(エクセル) 【Excel質問】別シートにある複数の同型の表から、同じ行項目にある数字を集計する 4 2023/02/16 00:14
- Visual Basic(VBA) 3つのプロシージャをまとめたら実行時エラー発生で対応不能 6 2022/05/17 01:47
- その他(プログラミング・Web制作) pythonでクラスで複数のメソッドを利用する方法 2 2022/04/15 04:17
- Excel(エクセル) データ入力規則リスト 空白を無視 3 2022/07/13 15:11
関連するカテゴリからQ&Aを探す
おすすめ情報
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
エクセルで日付の入ったセルの...
-
Excelのif関数で文字が見えなく...
-
INDEX関数やMATCH関数で値を取...
-
Excel 2019 は、SPILL機能があ...
-
各ページの1番上の表示について
-
写真のコピー
-
Excelファイルの「数式」タブ→...
-
Excel 改ページの文字色の変更...
-
Excel 2019 のピボットテーブル...
-
Excelで行をコピー、同じ行内に...
-
エクセルでセルに「氏名を入力...
-
Excel フィルターを掛けた状態...
-
エクセル グラフ軸について
-
excelの不要な行の削除ができな...
-
Excelで全角を半角にしたいので...
-
式の説明をお願いします。
-
条件付き書式を教えてください
-
INDIRECTを使わず excelで複数...
-
リンク先が空白若しくはゼロの...
-
Excelにて数式を使用して 工数...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
ファイル内にある数字の出現回...
-
Excel関数の先頭に「@」が入っ...
-
エクセルの気味悪い不思議
-
Excel VBAで、実行時にsheet上...
-
表示されている人数だけを数え...
-
他人が作ったマクロの理解
-
Excelの関数について質問です。
-
Excel 集計表
-
エクセル 日時の計算式について
-
Excelの関数に関して質問です。...
-
エクセル:セル内の文字列の下...
-
絞り込み検索
-
エクセルの関数で
-
エクセルの書式設定について教...
-
余分なEXCELファイルに印刷され...
-
VBA 同一シート内での転記の仕方
-
長期休みの関数はありますか
-
Excelの空のセル
-
エクセルで入力してある文を別...
-
Excelのマクロで、セルを結合し...
おすすめ情報