セルの範囲内から部分一致する文字を表示する関数

質問

最初に、「検索するセルの範囲内から文字を表示する方法」の質問で回答をいただいた方々には大変ご迷惑をおかけしまして申し訳ありませんでした。
それでは本題ですが、A行とD行には次のような文字が入力されています。

A15：USDJPY
A16：EURUSD
A17：GBPUSD
A18：AUDUSD
A19：USDCHF
A20：USDCAD
A21：NZDUSD

D5：USDJPY
D6：EURUSD
D7：GBPUSD
D8：AUDUSD
D9：USDCHF
D10：USDCAD
D11：NZDUSD
D12：EURJPY
D13：GBPJPY
D14：AUDJPY
D15：CHFJPY
D16：CADJPY
D17：NZDJPY
D18：EURGBP
D19：EURAUD
D20：EURCHF
D21：EURCAD
D22：EURNZD
D23：GBPAUD
D24：GBPCHF
D25：GBPCAD
D26：GBPNZD
D27：AUDCHF
D28：AUDCAD
D29：AUDNZD
D30：CADCHF
D31：NZDCHF
D32：NZDCAD

次に、関数式の条件は以下の通りです。

1.　D行で「JPY」の文字が含まれている場合は、全て「USDJPY」と表示します。
2.　D行で「USD」の文字が4文字目以降の場合は、全て「不要」と表示します。
3.　D行で「USD」の文字が1文字目からの場合は、D行を表示します。ただし、「USDJPY」の場合は除きます。
4.　D行で「JPY」も「USD」も含まれない場合は、D行の右側の3文字に当てはまる文字列をA15からA21の範囲から検索し、部分一致する文字を表示します。
「4」の意味は、
例えば、D18の「EURGBP」であれば、右側の3文字が「GBP」なのでA15からA21の範囲で「GBP」が含まれるA17の「GBPUSD」を表示します。
また、D21の「EURCAD」であれば、右側の3文字が「CAD」なのでA15からA21の範囲で「CAD」が含まれるA20の「USDCAD」を表示します。

関数はI5からI行に表示するようにしており、I5の式は次の通りです。
=IF(OR(NOT(ISNUMBER($B$7)),NOT(ISNUMBER($J5))),"",IF(NOT(ISERROR(SEARCH("JPY",$D5,1))),"USDJPY",IF(SEARCH("USD",$D5,1)=4,"不要",IF(SEARCH("USD",$D5,1)=1,$D5,VLOOKUP("*" & RIGHT($D5,3) & "*",A$15:A$21,1,FALSE)))))

しかし「4」のD行で「JPY」も「USD」も含まれない場合、「#VALUE!」のエラーが表示されてしまいます。
それを「4」のように表示したいのですが、どのような関数式にすればよいのでしょうか？
Excel2016です。

回答よろしくお願いします。

回答No.2

dogs_cats · ベストアンサー

serch関数を使用するとエラー処理が必要になるので、今回ではcountif関数をワイルドカードでJPYを含んでいるか、right、left関数でUSDであるかを判断する事で対応可能だと思います。
＄B7とJ列が数値であるかの判定はcount関数で可能です。
（画像はB7、I列にダミーデータとして１を入力しています）

添付画像の結果で良ければ下記数式
I5式
=IF(COUNT($B$7,J5)<>2,"",IF(COUNTIF(D5,"*JPY*"),"USDJPY",IF(RIGHT(D5,3)="USD","不要",IF(LEFT(D5,3)="USD",D5,VLOOKUP("*" & RIGHT($D5,3) & "*",A$15:A$21,1,FALSE)))))

下方へオートフィル

お礼

回答ありがとうございます。

単に数値であるかの判定はCOUNT関数で済むんですね。
勉強になります。
また、全体の式が非常にコンパクトになりました。

ありがとうございました。

回答No.6

Chiquilin

何故 もらった数式の理解しないまま１つの数式でやろうとしている
のでしょう。

=IF(COUNT(B$7,J5)<2,"",SUBSTITUTE(SUBSTITUTE(LOOKUP(1,-FIND({"","USD","USD","JPY"},D5,{1,1,4,1}),{2,1,"不要","USDJPY"}),1,D5),2,IFERROR(VLOOKUP("*"&RIGHT(D5,3)&"*",A$15:A$21,1,FALSE),"")))

正直 計算を分ければ何てことない話だと思います。

お礼

回答ありがとうございます。

>何故 もらった数式の理解しないまま１つの数式でやろうとしている
のでしょう。

申し訳ありません。
理解していない部分もあるのですが、こうしていろいろな方々に回答をいただけると新たに発見する部分や勉強になることもあり、感謝しています。
回答者の方々には迷惑な話であり、時間を割いていただきご苦労なことと思いますが。

ありがとうございました。

回答No.5

bunjii

回答No.3の追加です。
貼付画像はIF関数の入れ子構造をフローチャートにしたものです。
IF関数の論理式で「#VALUE!」を防ぐためにSEARCH関数を次のように修正しています。
SEARCH("JPY",$D5,1)　→　SEARCH("JPY",D5&"JPY")<LEN(D5)
尚、D列の空欄セルに対する処理は含まれていませんので必要であれば初段へIF関数を追加してください。

お礼

再び、回答をいただきましてありがとうございます。

わざわざフローチャートを作っていただきありがとうございます。
式が長くなるほど何をしているのか分からなくなるため、フローチャートを作るのは分かりやすく整理できますね。

ありがとうございました。

回答No.4

msMike

条件1と3は矛盾する等の不備があるようだけど、前回の
http://okwave.jp/qa/q9146635/a25472948.html
における「完全にこちらのミス」を充分には修復されていないようですね。
貴方には難し過ぎて修復困難と推察します。
此処はゴチャゴチャ長文にせずに、最終目的(具体的な用途、一体全体何に使いたいのか？)だけを簡単に述べるに留めた方が得策かと。
何回もキャッチボールするのはセンバツ野球に任せておきませう。

お礼

回答ありがとうございます。

質問は具体的かつ簡潔にというのはおっしゃる通りですね。

ありがとうございました。

回答No.3

bunjii

>それを「4」のように表示したいのですが、どのような関数式にすればよいのでしょうか？
提示の数式について添削することが良いのか、別の数式を提案すれば良いのかを明確にしてください。（質問者の思考に合わせて回答したいと思います）
前者の場合は「#VALUE!」のエラーの発生原因がSERCH関数にあるので検索値の"JPY"または"USD"を対象文字列に連結して検出された位置でIF関数の分岐を行えば解決できます。
後者の場合はフローチャートを書いて論理を整理しながら数式全体を組み立てる必要がありますので、回答者があなたの目的を理解できるように説明しなければなりません。
尚、「A行とD行」のような表現は誤りで「A列とD列」に変えてください。（行と列を取り違えると思考が混乱します）

お礼

回答ありがとうございます。

回答をいただいて感謝しておりますが、既に回答者様の方の中で分かりやすく短い式で回答をしていただいた方がおりましたので解決できました。

いつもそうなのですが、説明するのが非常に苦手で痛感します。
行ではなく列なんですね。ご指摘ありがとうございます。

ありがとうございました。

回答No.1

kagakusuki

条件に不明な点が幾つもあります。
　1～4のどれにも該当しない場合や、逆に複数の条件を同時に満たしている場合にはどの様な表示にすれば宜しいのでしょうか？

・例えばD列の値が「JPYUSD」となっていた場合には、1の条件と2の条件の両方を同時に満たしている事になりますが、1と2という２つの条件のどちらを優先させれば宜しいのでしょうか？

・D列の値の中に「USD」の文字が2文字目に含まれている場合で、尚且つ「JPY」の文字が含まれていない場合(例えば「AUSD」)にはどうすれば宜しいのでしょうか？

・D列の値の中に「USD」の文字が3文字目に含まれている場合で、尚且つ「JPY」の文字が含まれていない場合(例えば「ABUSD」)にはどうすれば宜しいのでしょうか？

・D列の値の中に「USD」の文字が1文字目と４文字目の両方に含まれている場合で、尚且つ「JPY」の文字が含まれていない場合(例えば「USDUSD」)には、2の条件と3の条件の両方を同時に満たしている事になりますが、2と3という２つの条件のどちらを優先させれば宜しいのでしょうか？

・D列の値の中に「USD」の文字が2文字目と4文字目以降の両方に含まれている場合で、尚且つ「JPY」の文字が含まれていない場合(例えば「AUSDBUSD」)にはどうすれば宜しいのでしょうか？

・D列の値の中に「USD」の文字が3文字目と4文字目以降の両方に含まれている場合で、尚且つ「JPY」の文字が含まれていない場合(例えば「ABUSDUSD」)にはどうすれば宜しいのでしょうか？

・D列の値の中に「USD」の文字が2文字目に含まれている場合で、尚且つ「JPY」の文字が含まれている場合(例えば「AUSDJPY」)にはどうすれば宜しいのでしょうか？

・D列の値の中に「USD」の文字が3文字目に含まれている場合で、尚且つ「JPY」の文字が含まれている場合(例えば「ABUSDJPY」)にはどうすれば宜しいのでしょうか？

・D列の値の中に「USD」の文字が1文字目と４文字目の両方に含まれている場合で、尚且つ「JPY」の文字が含まれている場合(例えば「USDUSDJPY」)には、1の条件と2の条件と3の条件の3つを同時に満たしている事になりますが、1と2と3という3つの条件の内のどれを優先させれば宜しいのでしょうか？

・D列の値の中に「USD」の文字が2文字目と4文字目以降の両方に含まれている場合で、尚且つ「JPY」の文字が含まれている場合(例えば「AUSDJPYUSD」)にはどうすれば宜しいのでしょうか？

・D列の値の中に「USD」の文字が3文字目と4文字目以降の両方に含まれている場合で、尚且つ「JPY」の文字が含まれている場合(例えば「ABUSDJPYUSD」)にはどうすれば宜しいのでしょうか？

　後、御質問文にある質問者様が考えた関数は必要も無いのにNOT関数を使っているといった点を始めとして無駄が多過ぎます。
　もし、1の条件が他の全ての条件に優先していて、尚且つ「USD」の文字が2文字目～3文字目の範囲内に現れる事は無く、「USD」の文字が複数含まれている事も無いという条件が常に満たされている事が保障されている場合には、I5セルに次の様な関数を入力してから、I5セルをコピーしてI6～I32のセル範囲に貼り付けると良いと思います。

=IF(OR(COUNT($B$7,$J5)<2,$D5=""),"",IF(ISNUMBER(FIND("JPY",$D5)),"USDJPY",CHOOSE(MATCH(IFERROR(FIND("USD",$D5),0),{0,1,4}),IF(LEN($D5)<3,"(該当無し)",IFERROR(VLOOKUP("*"&RIGHT($D5,3)&"*",$A$15:$A$21,1,FALSE),"(該当無し)")),$D5,"不要")))

お礼

回答ありがとうございます。

>条件に不明な点が幾つもあります。
　1～4のどれにも該当しない場合や、逆に複数の条件を同時に満たしている場合にはどの様な表示にすれば宜しいのでしょうか？

そこまで考えていただき、ありがとうございます。
1～4以外や複数の条件を満たすということはありません。

>後、御質問文にある質問者様が考えた関数は必要も無いのにNOT関数を使っているといった点を始めとして無駄が多過ぎます。

関数をコンパクトにしていただき、ありがとうございます。

ありがとうございました。