Excelの関数 SUBSTITUTEとIF関数の組み合わせについて

詳しい方、ご教示いただけますと幸いです。

データベースからダウンロードしたデータを、
希望の書式に最適化するエクセルファイルを作成しています。

シートが２枚あり、
「貼り付け元」シートに貼り付けたデータが、
もうひとつの「最適化」シートにアウトプットされる形です。
「貼り付け元」のデータは何もいじらず、「最適化」シートのみ関数を入れています。

色々と組み合わせていますが、SUBSTITUTEとIFの組み合わせが上手くいきません。

【条件】

１.「貼り付け元」シートのシングルクォーテーションを取る


２.シングルクォーテーションを取り除いた値の中に
「りんご」または「みかん」が含まれており、
かつ
最適化シートのB２のセルに「対象」の値が含まれている場合は、
「ぶどう」
を返す

３．２の条件に該当しない場合は、シングルクォーテーションを取り除いた
貼り付け元のシートの内容をそのまま返す


----------------------

１については、
=SUBSTITUTE(貼り付け元!A2,"'","")

これで解消できるのはわかります。

２については、
=IF(AND(B2="対象",A2="りんご",A2="みかん"),"ぶどう")

これで解消できるのかなと思います。

ですが、１と２を満たし、３も満たす方法がわかりません。
どのように組み合わせたらよいのでしょうか？

SUBSTITUTE、IF、AND以外の関数で解決するものなのか、
１つのセル内で処理する事がそのそも無理なのかもわかりませんでした…。


ご存知の方、お知恵をお貸しいただけますと助かります。
どうぞよろしくお願いいたします。

No.3 回答者： mike_g 回答日時： 2018/09/18 10:53

》 １.「貼り付け元」シートのシングルクォーテーションを取る

》 １については、
》 =SUBSTITUTE(貼り付け元!A2,"'","")
》 これで解消できるのはわかります。
「わかります」と仰っているけど、それは冗長な作業です。
なぜなら、単に =貼り付け元!A2 で「'」が取れているからです。確認してみてください。

No.2 回答者： d-q-t-p 回答日時： 2018/09/18 09:16

デーベースクエリでやればいいのにという突っ込みはなしなん

ですかね。

「含まれている」は「一致する」とは意味が違いますので ２の
数式はそのままではまずいでしょう。

=SUBSTITUTE(IF(AND(COUNTIF(B2,"*対象*"),OR(COUNTIF(貼り付け元!A2,{"*りんご*","*みかん*"}))),"ぶどう",貼り付け元!A2),"'","")

No.1 回答者： tatsu99 回答日時： 2018/09/18 00:25

ちょっと長くなりますが

=IF(AND(IFERROR(FIND("対象",B2),FALSE),OR(IFERROR(FIND("りんご",SUBSTITUTE(貼り付け元!A2,"'","")),FALSE),IFERROR(FIND("みかん",SUBSTITUTE(貼り付け元!A2,"'","")),FALSE))),"ぶどう",SUBSTITUTE(貼り付け元!A2,"'",""))

ではどうでしょうか。
作業用セルがつかえるなら、もっと短くなるかと。