Excel関数で複数条件を指定する方法

質問

Excelで条件を指定して、決まった値を返したいのですが、複数条件の方法がわかりません。

セルA１に入力された文字列に、
【りんご】という文字が含まれている場合はE列に「100」と表示させ、
【バナナ】【みかん】【キウイ】のいずれかが含まれている場合は「0」と表示させ、
上記以外の場合「500」と表示させたいです。

A列が検索したい文字列が入力される列で、E列に結果を表示。
検索する文字列は部分一致で、完全一致ではありません。A1に「青森りんご」と入力されたらE1に「100」と表示させたいです。
条件で書いた２段目の候補は10個程度あり、「上記以外」の候補は1000以上あります。

IF(COUNTIF(A1,"*りんご*"),"100",IF(COUNTIF(A1,"*バナナ*"),"0","500"))

上記のように自分で考えてみて、りんご・バナナ・それ以外　であればできたのですが、バナナのとことを複数指定する方法がわかりません。
他のやり方でも結構ですのでどうすればいいかわかる方宜しくお願いします。

回答No.4

bunjii · ベストアンサー

>上記のように自分で考えてみて、りんご・バナナ・それ以外　であればできたのですが、バナナのとことを複数指定する方法がわかりません。
内側のIF関数を工夫すれば目的に合う数式になります。
IF(COUNTIF(A1,"*バナナ*"),"0","500")
　　　　　　　↓
IF(OR(COUNTIF(A1,"*バナナ*"),COUNTIF(A1,"*みかん*"),COUNTIF(A1,"*キウイ*")),"0","500")
OR関数は複数の条件の何れかがTRUEのときTRUEを返しますので必要な条件を列記すれば目的を達成できます。
但し、提示の数式内では結果の値を文字列（"100"、"0"、"500"）にしているので数値にするときは下記のように数値を""で括らないようにしてください。
=IF(COUNTIF(A1,"*りんご*"),100,IF(OR(COUNTIF(A1,"*バナナ*"),COUNTIF(A1,"*みかん*"),COUNTIF(A1,"*キウイ*")),0,500))

お礼

ご回答いただきありがとうございます。
教えて頂いた方法でやりたい事ができました！！
しかも返す値は数値にしたかったので、そのアドバイスまで頂けて感激しました。とても参考になりました。ありがとうございました。

回答No.5

Chiquilin

=500-SUM(COUNTIF(A1,"*"&{"りんご","バナナ","みかん","キウイ"}&"*")*{400,500,500,500})

でどうですか？

お礼

ご回答いただきありがとうございます。
今回500に指定したいワードが無数にあって全てを指定することはできないので、今後指定ワードが限られた場合に活用させて頂こうと思います！

回答No.3

mt2015

図を添付し忘れました(^^;

回答No.2

mt2015

候補名と点数の表を別に作っておきます。
添付の図の例では、G2：H8にその表があります。

E2セルには↓の式を入れ、Ctrl+Shit+Enter　で、配列数式として確定します。
=OFFSET(H$1,-1+10^4-MAX((10^4-ROW($G$2:$G$8))*ISNUMBER(FIND($G$2:$G$8,$A1))),0)

確定したE2セルを下方向にコピーしてください。

式の説明をすると面倒なのですが、式中の↓の部分で、A列の文字列がリスト中のどの候補が含まれているかを出しています。
10^4-MAX((10^4-ROW($G$2:$G$8))*ISNUMBER(FIND($G$2:$G$8,$A1)))

お礼

再度のご回答ありがとうございました。
画像まで添付していただいてわかりやすかったです。

試してみたのですが、教えて頂いた方法だと、確定している条件以外の全てをリスト化しなければいけませんよね？（500に当たるワードが無数にありますので）今回はそれが無理でしたので期待した結果は得られませんでした。
ですがまた別の機会で利用させて頂こうと思います！
ありがとうございました。

回答No.1

mt2015

条件が良くわかりません。
・「りんごバナナ」の様に複数の候補が含まれている場合はどうします？
・候補に、「ライチ」「イチゴ」があるとして、A列の文字列が「ライチゴ」だった場合どちらが含まれていることになる？
・A1の文字列だけが対象？それともA1、A2と対象文字列が複数あって、E1以下に検索結果を表示したい？

お礼

すみません間違いがありました。

検索対象は同一行です！、

補足

ありがとうございます。補足致します。

今日仕入れた果物のリストが別のbookにあり、果物名のデータをA列に貼り付けた時に、果物別に加点をしたいのです。

・「りんごバナナ」の様に複数の候補が含まれている場合はどうします？

１つのセルに複数の候補（果物名）が混ざることはありません。

・A1の文字列だけが対象？それともA1、A2と対象文字列が複数あって、E1以下に検索結果を表示したい？

検索対象は同一列だけです。

A E
1 青森りんご 100
2 完熟バナナ 0
3 青森みかん 0
4 りんご一級 100
5 あまおうイチゴ 500

こんなイメージです。
説明がわかりづらくてすみません。