こんにちはゲストさん。会員登録(無料)して質問・回答してみよう!

解決済みの質問

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でやろうとするのは間違いですか?
皆様のお知恵を拝借したいと思います。アドバイス宜しくお願いします。

投稿日時 - 2017-03-09 18:50:32

QNo.9303104

すぐに回答ほしいです

質問者が選んだベストアンサー

>現在このアルファベットは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以降のバージョンで再現できるはずです。

投稿日時 - 2017-03-10 22:19:37

お礼

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

投稿日時 - 2017-03-11 14:43:40

ANo.7

このQ&Aは役に立ちましたか?

0人が「このQ&Aが役に立った」と投票しています

回答(7)

ANo.6

>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関数の文字数を算出するための論理が必要になります。
条件を再確認して補足してください。

投稿日時 - 2017-03-10 17:23:23

補足

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

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


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

<Sheet2>は下記のように作ってます。
    (E列)  (F列)
   -検索値- -戻す値-
2行目  AA   ああ(社)
3行目  BB   ああ(社)
4行目  CC   ああ(社)
5行目  DD   いい(社)
6行目  EEX  うう(社)
7行目  EER  うう(社)
8行目  FF   ええ(社)

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


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

そのとおりです!


<Sheet1>求めている結果。
    (A列)  (C列)
    商品番号 ブランド名
2行目 CC88   ああ(社)
3行目 EEX444  うう(社)
4行目 EEX123  うう(社)
5行目 FF7777  ええ(社)


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

投稿日時 - 2017-03-10 19:57:05

ANo.5

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

投稿日時 - 2017-03-10 12:04:07

補足

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

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

投稿日時 - 2017-03-10 13:16:27

ANo.4

>それで、その指定したい条件が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)),"")

投稿日時 - 2017-03-09 22:27:36

補足

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

投稿日時 - 2017-03-10 07:15:52

お礼

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

投稿日時 - 2017-03-10 10:13:57

ANo.3

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

投稿日時 - 2017-03-09 20:54:25

補足

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

投稿日時 - 2017-03-10 06:59:37

ANo.2

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

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

投稿日時 - 2017-03-09 20:53:06

補足

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

投稿日時 - 2017-03-10 07:04:58

ANo.1

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

投稿日時 - 2017-03-09 19:28:18

補足

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

投稿日時 - 2017-03-09 20:40:36

お礼

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

投稿日時 - 2017-03-11 14:48:51

あなたにオススメの質問