エクセルの数式について教えてください。

セルA26に数式を設定し、下記の場合分けによってリストの文字が、A26に表示出来る方法を教えてください。
リスト
AA43「申請書」AB43「委任状」AC43「付近見取図」AD3「意思図」AE43「採光検討」AF43「換気検討」AG43「概要書」AH43「工事届」のリストがあるます。
場合分け
１．セルF26に文中に「【〇〇】」が有った場合にリスト「申請書」がA26に表示。
　　（【〇〇】の〇〇は都合によって変わります）
２．セルF26に文中に「委任」が有った場合にリスト「委任状」がA26に表示。
３．セルF26に文中に「付近」が有った場合にリスト「付近見取図」がA26に表示。
４．セルF26に文中に「図」が有った場合にリスト「意匠図」がA26に表示。
５．セルF26に文中に「採光」が有った場合にリスト「採光検討」がA26に表示。
６．セルF26に文中に「換気」が有った場合にリスト「換気検討」がA26に表示。
７．セルF26に文中に「概要」が有った場合にリスト「概要書」がA26に表示。
８．セルF26に文中に「換気」が有った場合にリスト「換気検討」がA26に表示。
以上です。もし、F26の文中に上記の文字が無かった場合は、A26は空白でお願いします。
ExcelのVersionは2010ですがこれは自宅のExcelで実際は仕事場のOffice３６５を使用することになります。
よろしくお願いします。

質問者からの補足コメント

• 

  回答ありがとうございます。
  私の質問に間違えがありました。
  ８．セルF26に文中に「工事」が有った場合にリスト「工事届」がA26に表示。
  でした。
  又、貴社に教えて頂いた数式を「A26」に設定し、その数式をA27～A39にコピペをし
  F27～F39にそれぞれランダムに「１～８」までの文中の文字によってA27～A39にそれぞれのリストを表示させたいのですが、出来ますでしょうか。
  貴社の数式でF26に「立面図」と入力した時にA26が空白
  F26に「採光」A26が「意匠図」
  F26に「換気」A26が「採光」
  F26に「概要」A26が「換気」
  F26に「工事」A26が「概要書」
  となってしまします。
  私の間違えや、説明が悪くご迷惑をお掛けいたしますが、解決方法を教えてください。

  No.2の回答に寄せられた補足コメントです。 補足日時：2023/06/18 11:40

• 

  何度もありがとうございました。
  リスト側の私の設定が悪くて採光→意匠図等々なってしまっておりました。
  今後、教えて頂きました数式を変更して使用したいと考えております、
  F26の文字を複数サーチしてリストの文字をA26に表示する方法を、もしよろしければ、
  下記についても教えて頂けますでしょうか。
  例えば
  リストの７番を変更して
  F26に「地積」又は「謄本」又は「公図」又は「地盤」の文字が入力された場合に
  A26にリストAG43の「土地資料」と表示出来る方法があれば教えてください。
  よろしくお願いします。

  No.3の回答に寄せられた補足コメントです。 補足日時：2023/06/18 13:13

No.6 ベストアンサー 回答者： goomania 回答日時： 2023/06/19 13:02

No.5です。

ご質問者からの補足で
>リストの７番を変更してF26に「地積」又は「謄本」又は「公図」又は「地盤」
>の文字が入力された場合にA26にリストAG43の「土地資料」と表示出来る方法
とのご希望がありました。

これを実現するには、AG43セル～AJ43セルを全て「土地資料」、AK43セルを「工事届」としたうえで、
前回回答の数式を、

=IFERROR(LOOKUP(1,0/SEARCH({"【*】","委任","付近","図","採光","換気","地籍","謄本","公図","地番","工事"},F26),$AA$43:$AK$43),"")

という数式に修正すればよいでしょう。

この回答へのお礼

回答ありがとうございます。
私の補足に対応していただきましてありがとうございます。

お礼日時：2023/06/19 15:44

No.7 回答者： goomania 回答日時： 2023/06/19 14:08

ご質問者からの補足で

>F26の文字を複数サーチしてリストの文字をA26に表示する方法
とのご希望がありました。A26セルに

=TEXTJOIN("、",TRUE,LEFT($AA$43:$AH$43,ISNUMBER(SEARCH({"【*】","委任","付近","図","採光","換気","概要","工事"},F26))*LEN($AA$43:$AH$43)))

という数式を記述することで実現できると思います。
但し、TEXTJOIN関数はバージョン2010では使えませんので、上記数式は365で活用願います。

※余談ですが、添付画像をご覧いただくと、「公図」は「土地資料」にヒットするための文字列ですが、「土地資料」が対象範囲になく、「図」が含まれるため「意思図」にヒットしています。
このような想定外の結果を生むこともあるので、
>「一文字だけ一致」のような検索方法･････
はできるだけ避けるべきだと思います。

この回答へのお礼

回答ありがとうございます。
色々と教えて頂きまして感謝いたします。

お礼日時：2023/06/19 15:44

No.5 回答者： goomania 回答日時： 2023/06/19 12:25

No.4です。

前回回答で、

=IFERROR(LOOKUP(1,0/SEARCH({"【*】","委任","付近","図","採光","換気","概要","工事"},F26),$AA$43:$AG$43),"")･･･①

という数式をお示ししました。

しかし、結果は変わらないのですが、上記①のうち「$AA$43:$AG$43」の部分は「$AA$43:$AH$43」が正しいと思います。つまり、「【間違い】とは言い切れないが【標準回答】ではない」ため訂正いたします。
「$AH$43」の部分はAA列以降の同じ行のセルがであれば、結果に影響を与えないためリストの範囲との不一致に気づきませんでした。
しかし、LOOKUP関数のサポートを見ても「【検索範囲】と【対応範囲】は同じサイズにする」と書かれているため、同じサイズにするには訂正が必要だと判断したものです。

No.4 回答者： goomania 回答日時： 2023/06/18 13:34

ご質問者が提示された2～8の具体的な条件のうち、8は6と同じものになっており、間違いだとの補足がありました。

このため、8は以下の条件であるものとします。

>８．セルF26に文中に「工事」が有った場合にリスト「工事届」がA26に表示

前回のこのカテゴリーでのご質問者の同様のご質問
https://oshiete.goo.ne.jp/qa/13501010.html
において、fujillinさんから、
>「一文字だけ一致」のような検索方法では、すぐに破綻しそうなルールと考えられます。
とのご指摘があったのに、何故改善しようとしないのでしょうか?

例えばF26の文中に「付近見取図」があったら、
>３．セルF26に文中に「付近」が有った場合にリスト「付近見取図」がA26
>に表示。
>４．セルF26に文中に「図」が有った場合にリスト「意匠図」がA26に表
>示。
というルールがバッティングしてしまいます。
つまり3、4の条件がバッティングしてしまい、どちらを表示してよいのかわからない状態になるということです。

文中から特定する以上、複数の単語が両方含まれるケースがあるのはやむを得ないとは思いますが、その場合のルールも決めておく必要があります。
従ってA26はF26の値により2～8までの条件に基づいて決定するが、万一複数の条件が合致する場合は番号の大きい方を採用する
という複数条件合致の場合の対策も考えておくことにします。

すると、このご質問はご質問者が以前ご質問された
https://oshiete.goo.ne.jp/qa/13491052.html
と同様のご質問であると判ります。従って、A26セルに

=IFERROR(LOOKUP(1,0/SEARCH({"【*】","委任","付近","図","採光","換気","概要","工事"},F26),$AA$43:$AG$43),"")

を記述すればよいということになります。
前回のご質問ではFIND関数を用いましたが、今回はワイルドカードを使う必要があるため、SEARCH関数を使用しています。

なお、配列定数として{"【*】","委任","付近","図","採光","換気","概要","工事"}を数式内に記述していますが、メンテナンスを考えるとこちらも邪魔にならない範囲のセルに記述しておく方法が好ましいのではないかと思います。また、この数式は2010でも365でも使用可能です。

この回答へのお礼

回答ありがとうございます。
私の説明不足を考慮して頂きまして感謝いたします。
早速試して見ます。

お礼日時：2023/06/18 16:13

No.3 回答者： zongai 回答日時： 2023/06/18 12:14

先の回答がベースでいいのかな？

とりあえず、式コピーへの対応したもの。

=IF(ISNUMBER(SEARCH("【", F26)), INDEX($AA$43:$AH$43, 1), IF(ISNUMBER(SEARCH("委任", F26)), INDEX($AA$43:$AH$43, 2), IF(ISNUMBER(SEARCH("付近", F26)), INDEX($AA$43:$AH$43, 3), IF(ISNUMBER(SEARCH("図", F26)), INDEX($AA$43:$AH$43, 4), IF(ISNUMBER(SEARCH("採光", F26)), INDEX($AA$43:$AH$43, 5), IF(ISNUMBER(SEARCH("換気", F26)), INDEX($AA$43:$AH$43, 6), IF(ISNUMBER(SEARCH("概要", F26)), INDEX($AA$43:$AH$43, 7), IF(ISNUMBER(SEARCH("工事", F26)), INDEX($AA$43:$AH$43, 8), ""))))))))

表示がずれるのは、お手持ちのデータで
AA43「申請書」AB43「委任状」AC43「付近見取図」・・・
のところが
AB43「申請書」AC43「委任状」AD43「付近見取図」・・・
になってませんか？

No.2 回答者： zongai 回答日時： 2023/06/18 11:18

> ８．セルF26に文中に「換気」が有った場合にリスト「換気検討」がA26に表示。

換気が2個目。
工事、工事届　の組み合わせかな？

> AA43「申請書」AB43「委任状」AC43「付近見取図」AD3「意思図」AE43「採光検討」AF43「換気検討」AG43「概要書」AH43「工事届」のリストがあるます。

つまり、この9個がリストの中身なの？
リスト「申請書」を表示って、単に「申請書」って表示されればいいってこと？

とりあえず、AIに作らせて動作確認したもの。
=IF(ISNUMBER(SEARCH("【", F26)), INDEX(AA43:AH43, 1), IF(ISNUMBER(SEARCH("委任", F26)), INDEX(AA43:AH43, 2), IF(ISNUMBER(SEARCH("付近", F26)), INDEX(AA43:AH43, 3), IF(ISNUMBER(SEARCH("図", F26)), INDEX(AA43:AH43, 4), IF(ISNUMBER(SEARCH("採光", F26)), INDEX(AA43:AH43, 5), IF(ISNUMBER(SEARCH("換気", F26)), INDEX(AA43:AH43, 6), IF(ISNUMBER(SEARCH("概要", F26)), INDEX(AA43:AH43, 7), IF(ISNUMBER(SEARCH("工事", F26)), INDEX(AA43:AH43, 8), ""))))))))

No.1 回答者： fujillin 回答日時： 2023/06/18 11:16

こんにちは

365環境前提でよいのなら・・

どこかに対照用のリストを作成しておけば良いでしょう。

添付図では、E1:F6 を仮のリストにしてあります。（実際はどこでも良いです）
Ａ列に判定対象の文字列があるとして、図ではＢ1セルに
=INDEX(FILTER(F$1:F$6,LEN(SUBSTITUTE(A1,E$1:E$6,""))<LEN(A1),""),1)
の式を入力して、下方にフィルコピーしてあります。

※ リストの項目に包含関係があったり、文字列が複数のキーワードを含む場合などには、順序を工夫しないと思うのとは違う方の結果になる可能性があります。
優先順位の高い項目を先に記述するようにしておけば良いでしょう。

この回答へのお礼

回答ありがとうございます。
早速参考にさせて頂きます。

お礼日時：2023/06/18 16:09