EXCEL 複数単語を一つの単語に置き換える関数を教えて下さい

EXCELで複数の単語を一つの単語に置け変えたいと思っています。置換なのでSUBSTITUTE関数なのかな、と思うのですが、一対一の置換ではなく、たとえばA列に「キュウリ」「ニンジン」とあればB列に「野菜」と返し、同じくA列に「カツオ」「マグロ」とあればB列に「魚」と返すような方法です。よろしくお願いいたします。

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

• 

  すみません。知識不足、低レベルで・・・。たぶんとても丁寧に説明頂いていることと思いますが。。ご説明の意味がわかりません。
  実際のエクセル画像を付けましたので、入力した数式のどこが間違っているかお教え頂くと有難いです。
  ①のE列に関数を入れました。E列にD列記載の役職名を、データから見つけ、表示しなおして欲しいと思っています。現在①のE列に返された結果は全て間違っています。また空欄も出てしまいました。
  ②にE列にいれた数式を表示しましたが、因みにE2セルに入力した関数は
  =IFERROR(INDEX(G$1:K$1,SUMPRODUCT((G$2:K$17=D2)*COLUMN(D$1:H$1))),"")　です。
  この関数で私がセルの指定が間違っているのでしょうね。
  教えて頂ければと思います。お手数おかけします。

  
  No.3の回答に寄せられた補足コメントです。 補足日時：2018/04/24 19:52

• ②のエクセルです

  
    補足日時：2018/04/24 19:53

No.4 ベストアンサー 回答者： tom04 回答日時： 2018/04/24 21:24

No.2・3です。

G2～K17セルには重複データはありませんよね。

前回の回答の最後の3行をもう一度読み返してみてください。
＞必ずA列（列番号＝1）からINDEX関数の範囲の列数分！
と記載していますよね。

①の配置だとE2セルに
=IFERROR(INDEX(G$1:K$1,SUMPRODUCT((G$2:K$17=D2)*COLUMN(A$1:E$1))),"")

ではどうなりますか？m(_ _)m

この回答へのお礼

tom04さん、お礼が遅くなりすみませんでした。教えて頂いた新しい数式で解決しました！　大変に助かりました。ありがとうございました。

お礼日時：2018/05/01 16:22

No.3 回答者： tom04 回答日時： 2018/04/24 18:05

No.2です。

数式の説明を少しだけ・・・
当然ですが、対応表には重複がないという前提なので、

まず、INDEX関数で
D1～G1の4列の範囲を指定しました。
その中の何列目のデータか？を表示したいので
＞SUMPRODUCT((D$2:G$10=A2)*COLUMN(A$1:D$1))
という配列数式を使っています。

仮に前回の配置だとA列に「マグロ」と入力した場合、対応表ではINDEX関数の範囲の2番目になりますね。
D列　→　「FALSE」×　A列の番号（0*1）＝0
E列　→　「TRUE」×　B列の番号（1*2）＝2
F列　→　「FALSE」×　C列の番号（0*3）＝0
G列　→　「FALSE」×　D列の列番号（0*4）＝０

となり、配列数式の結果は「2」となります。
結局　INDEX関数の範囲の2番目　→　「魚」が返ります。

※　判りやすくするために4列だけの表にしましたが
何列あっても構いませんので、
＞COLUMN(A$1:D$1)
の「A$1:D$1」部分はINDEX関数で範囲指定した列数分を
必ずA列からの範囲にすればお望みの結果になるはずです。m(_ _)m

No.2 回答者： tom04 回答日時： 2018/04/23 23:05

こんばんは！

一案です。
↓の画像のように対応表を作ってみてはどうでしょうか？
画像ではB2セルに
=IFERROR(INDEX(D$1:G$1,SUMPRODUCT((D$2:G$10=A2)*COLUMN(A$1:D$1))),"")

という数式を入れフィルハンドルで下へコピーしています。m(_ _)m

この回答へのお礼

tom04さん、具体的に教えて頂きありがとうございます。私が知りたかったイメージはこのとおりです。対応表をつくるべきですね。
ただ頂いた関数を同様のエクセルシートの中で使用してみたのですが、（該当セルやセル範囲も変更して）結果は空欄表示となってしまいました。なぜでしょうか？　まだ頂いたSAMPLEの配置では試していないので、この後やってみようと思います。*COLUMN(A$1:D$1)の「A$1:D$1」の範囲が何をさすかが、ちょっとわかりませんでした。教えて頂けますでしょうか？

お礼日時：2018/04/24 17:09

No.1 回答者： あらっそ 回答日時： 2018/04/23 22:46

セル位置を含めた具体例と　こうなってほしいという結果を書いたほうが、やりたいことが伝わりますよ。

キュウリ、ニンジンが同じセル内にあるのか、A列の別のセルなのかわかりません。
キュウリ、ニンジンを野菜と返し、カツオ、マグロを魚と返すと判断する元ねたはどこにあるのですか？

この回答へのお礼

ご指摘ありがとうございます。確かにそうですね。質問が中途半端ですみません。

お礼日時：2018/04/24 17:00