Excel 関数で多数の複数条件・OR条件を指定

質問

こんにちは。Excelのことでわからない事を教えてください。
該当セル内に複数条件を指定して、いずれかが当てはまれば指定した文字列が表示されるようにしたいのですが、その条件が100個くらいになってしまいます。

例えば、
A1セルに【AAまたはBBまたはCC】が入力されたらC1セルに【ああ】と表示する。
A1セルに【DDまたはEEまたはFF】が入力されたらC1セルに【いい】と表示する。
A1セルに【GGまたはHHまたはII】が入力されたらC1セルに【うう】と表示する。
というようなことをしたいので、私は、C1セルに以下のような関数を作りました。

=IF(OR(COUNTIF(A1,"*AA*"),COUNTIF(A1,"*BB*"),COUNTIF(A1,"*CC*")),"ああ",IF(OR(COUNTIF(A1,"*DD*"),COUNTIF(A1,"*EE*"),COUNTIF(A1,"*FF*")),"いい",IF(OR(COUNTIF(A1,"*GG*"),COUNTIF(A1,"*HH*"),COUNTIF(A1,"*II*")),"うう",""))

それで、その指定したい条件が100件くらいになってしまうのですが（指定するワードが100個という意味です）、地道に作っていくしかないのでしょうか。もっとスマートなやり方はありますか？ワードは今後追加もしくは削除していく可能性があるので、こんな作り方でいいのか悩んでいます。
そもそもこのようなものをExcelでやろうとするのは間違いですか？
皆様のお知恵を拝借したいと思います。アドバイス宜しくお願いします。

回答No.7

bunjii · ベストアンサー

>現在このアルファベットは2文字と3文字が主流なのです。（例：BB88／EEX4321）なので分けるとするなら、アルファベット／数字　ということですかね？
A列の文字列から先頭の英文字のみ切り分けるにはLEFT関数とFIND関数を使います。
但し、数式内でA列の文字列にある数字（0～9）の位置で1番左に有る桁数を調べるには配列演算を使うためINDEX関数で前処理を行います。
配列の中で一番小さい値を調べるにはMIN関数を使います。
A1セルの"BB88"からBBを切り出すには下記の数式となります。
LEFT(A1,MIN(INDEX((FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),0))-1)
上記のA列の特定のセル（A1）から目的の英文字を切り出す手法を使ってVLOOKUP関数でSheet2のE:F列から目的の文字列を抽出する数式を組み立てます。
=IFERROR(VLOOKUP(LEFT(A1,MIN(INDEX((FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),0))-1),Sheet2!E:F,2,FALSE),"")
添付画像はExcel 2013で検証した結果です。
Excel 2007以降のバージョンで再現できるはずです。

お礼

ありがとうございます！素晴らしいです！！
早速自身のファイルで実行して、やりたかった事が実現できました！
関数の説明もしていただいたので、内容を把握することもできました。
拙い説明で何度もお手間かけましたが根気強く最後までお付き合いいただき本当に感謝致します。ありがとうございました！

回答No.6

bunjii

>A列の文字列は現状、先頭の2文字と3文字になります。
先頭の2文字または3文字を区分けするための条件は何ですか？

>今後1文字というのも出てくる可能性がありますが、
VLOOKUP関数の[検索値]がA列の文字列の一部であれば区分けをするための条件を指定して頂けないと数式を組めません。

>データシートとして別に（Sheet2）を作成し、E列に「条件の文字列」、F列に「表示文字列」を作りました。
E列には完全一致の文字列を羅列しているということですよね？
例えばSheet1のA1セルに"AB1234"とあるときSheet2のE列の"AB"がE2セルにあり、Sheet2のF2セルに"あい"があればSheet1のC1セルへ"あい"と表示（代入）される数式を組み立てれば良いのですよね？
Sheet1のC1セルへ次の数式を設定します。
=IFERROR(VLOOKUP(LEFT(A1,2),Sheet2!E:F,2,FALSE),"")
このときLEFT(A1,2)は予めA1セルの左から2文字を[検査値]とすることが分かっているときに使えます。
A1セルに"ABC3456"と入力されたとき自動的に左から3文字を[検査値]にするためにはLEFT関数の文字数を算出するための論理が必要になります。
条件を再確認して補足してください。

補足

再度ありがとうございます！
今回は商品の発注シートを作りたいのです。Sheet1のA列に商品番号を入力したらC列に自動的にブランド名が表示されれば入力の手間が省けるし、また、別のシートでC列を基にブランド別に集計を行いたいので入力ミスを防ぐ目的もあります。

＞先頭の2文字または3文字を区分けするための条件は何ですか？
「AA1234」のような文字列は商品番号です。「アルファベット」その後に「ランダムな数字」の組み合わせです。この番号は私が決めるのではなく、企業（今までブランドと表記）が独自に作った実際のものとなります。1ブランドにつき複数のアルファベット品番が使われています。
現在このアルファベットは2文字と3文字が主流なのです。（例：BB88／EEX4321）
なので分けるとするなら、アルファベット／数字　ということですかね？
答えになってますでしょうか。

＞E列には完全一致の文字列を羅列しているということですよね？
アルファベット部分は一致しています。

＜Sheet2＞は下記のように作ってます。
　　　　（E列）　　（F列）
　　　－検索値－　－戻す値－
２行目　　AA　　　ああ（社）
３行目　　BB　　　ああ（社）
４行目　　CC　　　ああ（社）
５行目　　DD　　　いい（社）
６行目　　EEX　　うう（社）
７行目　　EER　　うう（社）
８行目　　FF　　　ええ（社）

アルファベットは見やすく作ってますが実際は昇順でもないしランダムな組み合わせです。GH／JEFなど
このアルファベット品番は50個～くらいになり、廃盤になれば削除したいし新しくできれば追加したいので、別シートに作るのがいいなと思いました。

＞例えばSheet1のA1セルに"AB1234"とあるときSheet2のE列の"AB"がE2セルにあり、Sheet2のF2セルに"あい"があればSheet1のC1セルへ"あい"と表示（代入）される数式を組み立てれば良いのですよね？

そのとおりです！

＜Sheet1＞求めている結果。
　　　　（A列）　　（C列）
　　　　商品番号　ブランド名
２行目　CC88　　　ああ（社）
３行目　EEX444　　うう（社）
４行目　EEX123　　うう（社）
５行目　FF7777　　ええ（社）

IF関数みたいに単純に、文字列に含まれている一部の値を参照するっていうのは難しいんですね～・・
何度もすみません！

回答No.5

bunjii

>A列に、条件に指定した文字列（AA、BB等）だけ入力した場合は成功しましたが、AA1234など数字が含まれると反映されません。
検査値の考え方が逆でした。
A列の文字列は先頭の2文字が検索値として固定して良いとすれば下記のように訂正してください。
=IFERROR(VLOOKUP("*"&A1&"*",E:F,2,FALSE),"")
　　　　　↓
=IFERROR(VLOOKUP(LEFT(A1,2),E:F,2,FALSE),"")

補足

再びありがとうございます！あれから試行錯誤してました＾＾；
A列の文字列は現状、先頭の2文字と3文字になります。今後1文字というのも出てくる可能性がありますが、2文字と3文字だけでもできれば有り難いです。
私がやりたいことは、
https://qanda.rakuten.ne.jp/qa5889713.html
こちらの方と同じなのですが、私は「条件の文字列」「表示文字列」は別シートに作りたいのでちょっと状況が違い、応用できずにいます。

データシートとして別に（Sheet2）を作成し、E列に「条件の文字列」、F列に「表示文字列」を作りました。
事前情報に不備が多くお手数お掛けしてすみません。どうぞ宜しくお願いします。

回答No.4

bunjii

>それで、その指定したい条件が100件くらいになってしまうのですが（指定するワードが100個という意味です）、地道に作っていくしかないのでしょうか。もっとスマートなやり方はありますか？
IF関数の入れ子構造では無理があります。
基本的には回答No.1の考え方で良いと思います。
Excel 2007以降のバージョンではIFERROR関数が組み込まれていますので次の数式で良いと思います。
=IFERROR(VLOOKUP("*"&A1&"*",E:F,2,FALSE),"")
但し、E列には条件の文字列、F列には対応する表示文字列を組み合わせで入力します。
Excel 2003以前のバージョンのときは次のように変更すると良いでしょう。
=IF(COUNTIF(E:E,"*"&A1&"*"),VLOOKUP("*"&A1&"*",E:F,2,FALSE),"")
VLOOKUP関数の代わりにINDEX関数とMATCH関数の組み合わせでも同じ結果を得られます。
=IFERROR(INDEX(F:F,MATCH("*"&A1&"*",E:E,0)),"")

お礼

Excelのバージョンは2013です。
こちらにまた補足させていただきます。
アドバイスを参考に
=IFERROR(VLOOKUP("*"&A1&"*",E:F,2,FALSE),"")
でやってみました。
A列に、条件に指定した文字列（AA、BB等）だけ入力した場合は成功しましたが、AA1234など数字が含まれると反映されません。”*”を付ければ部分一致でいけるのかなと思ったのですが。。

補足

ご回答ありがとうございます！
「IF関数の入れ子構造では無理があります」とのことでアドバイスありがとうございます。
また、先の方への補足も追加でさせていただいたのですが、
A列には1行ずつ、1セルに対して1つの品番（例：AA1234）を入力したらC列にブランド名（例：ああ）が表示されるようにしたいです。なおかつ「条件の文字列」「表示文字列」は別シートに設けたいのです。
頂いたアドバイスを元に検証してみたいと思います。

回答No.3

mt2015

ごめんなさい、画像が前の回答時のものでした(^^;

補足

再度のご回答ありがとうございます！画像まで付けて頂いてわかりやすいです。
また私の説明が悪かったようで申し訳ないのですが、A列には1行ずつ、1セルに対して1つのワードを入力したいのです。A列に品番を打ち込んだらC列にブランド名が表示されるということをしたいのです。（1ブランドが数種類の品番を持っています（ああ社の品番はAA数字、BB数字、CC数字　※数字は不規則）。
例/
　A1：AA123　C1：ああ
　A2：GG888　C2：うう
　A3：BB666　C3：ああ
　A4：EE4321　C4：いい
関数に詳しくないので、アドバイス頂いた「C2セル」になぜ関数が入るのかなー？と疑問を持ちました。
また希望としては、「指定値」「表示値」等は別のシートに設けたいのですが可能でしょうか。
何度も申し訳ありません。自分でも検証してみます。

回答No.2

mt2015

じゃあ、こんなかんじで。
E:F列に一覧を作るのは同じ。G列は一致チェック用に使います。
G2セルに　=ISNUMBER(FIND(E2,A$1))　と入れて下にコピー
C2セルには　=IFERROR(INDEX(F:F,MATCH(TRUE,G:G,0)),"")　と入れます。

一致するワードが複数あった場合は上にある方を優先します。
また、一致するワードが無い場合は空白としています。

補足

すみません、「C2セル」と書かれているのは、画像でいうところのC1ですよね？！下の補足質問のところで勘違いしていました！
G列のFALSEとTRUEの使い分けがわかりませんーー;

回答No.1

mt2015

A1セルに入るのはAAやBBなど「指定するワード」と同一ですか？
で、あれば検索ワードと表示したい値の一覧を作り、Vlookup関数で表示させればすみます。

お礼

No.7の回答で解決致しました。
今回説明不足ですみませんでした。
ご協力いただきありがとうございました。

補足

説明が不十分で申し訳ありません。
A1セルには、指定するワード+ランダムな数字になります。（FF1234、AA88など）