Excel関数で、こんなことできますか?

質問

Excel関数で、こんなことできますか?

『沢山のパターンの中から、自動判定したい。』

(1)～(4)の4パターンで自動判定するよう設定したいのですが、
Excel関数を使って、できるのでしょうか?
もし無理であれば、(1)、(2)のみとかだけでもできればありたがたいです。

ちなみに(1)のみの場合は、IF関数で次のように設定しました。
=IF(B6>49,"S",IF(B6>34,"A",IF(B6>24,"B",IF(B6>14,"C","D"))))

この他に合計値を出す範囲内のセル( 例えばB3やB4 )に
「N」という表示があった場合には、(2)～(4)の判定をさせたいです。
(エラー値となる場合にはISERRORで”N”と表示するようにしています。)

(1)エラーがない場合
判定 　　合計
　S 　45以上50以下
　A 　35以上45未満
　B 　25以上35未満
　C 　15以上25未満
　D 　10以上15未満

(2)B3=Nの場合
判定　　合計
　S 　35以上40以下
A 　25以上35未満
　B 　15以上25未満
　C 　10以上15未満
　D 　 5以上10未満

(3)B4=Nの場合
判定　 合計
　S 　25以上30以下
　A 　20以上25未満
　B 　15以上20未満
　C 　10以上15未満
　D 　5以上10未満

(4)B3,B4ともNの場合
判定 合計
　S 　20以上25以下
　A 　15以上20未満
　B 　10以上15未満
　C 　 5以上10未満
　D 　 2以上 5未満

Excel関数でどの関数を使えるのかも判りませんし、マクロも判りません。
普段は似たものを検索してコピペしていますが、複雑なものは判りません。
どなたか、お判りでしたら、お教え下さい。
よろしくお願いいたします。

回答No.2

kagakusuki · ベストアンサー

今仮に、合計値を出す範囲内のセルが存在するシートがSheet1であるとします。
　まず、別のシート（ここでは仮にSheet2とします）に次の様なリストを作成して下さい。

A3セルに　D
A4セルに　C
A5セルに　B
A6セルに　A
A7セルに　S
A8セルに　S
B1セルに　N無し
B2セルに　0
B3セルに　10
B4セルに　15
B5セルに　25
B6セルに　35
B7セルに　45
B8セルに　50
C1セルに　B3=N
C2セルに　1
C3セルに　5
C4セルに　10
C5セルに　15
C6セルに　25
C7セルに　35
C8セルに　40
D1セルに　B4=N
D2セルに　2
D3セルに　5
D4セルに　10
D5セルに　15
D6セルに　20
D7セルに　25
D8セルに　30
E1セルに　B3=N,B4=N
E2セルに　3
E3セルに　2
E4セルに　5
E5セルに　10
E6セルに　15
E7セルに　20
E8セルに　25

　尚、上記のリストにおいて、Sheet2の1行目と2行目に入力する内容は、数式等の入力時に解り易くするためのもので、計算に必要があるものではありませんから、面倒であれば省略されても構いません。
　それから、Sheet1の判定結果を表示させるセルに次の数式を入力して下さい。

=IF(OR($B$6<OFFSET(Sheet2!$B$3,,($B$3="N")+($B$4="N")*2),$B$6>OFFSET(Sheet2!$B$8,,($B$3="N")+($B$4="N")*2)),"N",INDEX(Sheet2!$A$3:$A$8,MATCH($B$6,OFFSET(Sheet2!$B$3:$B$8,,($B$3="N")+($B$4="N")*2))))

　以上で完成です。

　因みに、合計する範囲がB3～B4の様にセルの個数が２個ではなく、ｎ個に増えた場合には、
Sheet2のリストの列数を

1＋2^ｎ

本にまで増設して、

2列目には「N無し」（10進数の「2-2」=2進数の「0」）のデーター
3列目には「1個目のセルがN」（10進数の「3-2」=2進数の「1」）のデーター
4列目には「2個目のセルがN」（10進数の「4-2」=2進数の「10」）のデーター
5列目には「2個目と1個目のセルがN」（10進数の「5-2」=2進数の「11」）のデーター
6列目には「3個目のセルがN」（10進数の「6-2」=2進数の「100」）のデーター
7列目には「3個目と1個目のセルがN」（10進数の「7-2」=2進数の「101」）のデーター
8列目には「3個目と2個目のセルがN」（10進数の「8-2」=2進数の「110」）のデーター
9列目には「3個目と2個目と1個目のセルがN」（10進数の「9-2」=2進数の「111」）のデーター
10列目には「4個目のセルがN」（10進数の「10-2」=2進数の「1000」）のデーター
11列目には「4個目と1個目のセルがN」（10進数の「11-2」=2進数の「1001」）のデーター
12列目には「4個目と2個目のセルがN」（10進数の「12-2」=2進数の「1010」）のデーター
13列目には「4個目と2個目と1個目のセルがN」（10進数の「13-2」=2進数の「1011」）のデーター
14列目には「4個目と3個目のセルがN」（10進数の「14-2」=2進数の「1100」）のデーター
15列目には「4個目と3個目と1個目のセルがN」（10進数の「15-2」=2進数の「1101」）のデーター
16列目には「4個目と3個目と2個目のセルがN」（10進数の「16-2」=2進数の「1110」）のデーター
17列目には「4個目と3個目と2個目と1個目のセルがN」（10進数の「17-1」=2進数の「1111」）のデーター
　　・
　　・
　　・
　　・
（2^n+1）列目には「n個目と(n-1)個目と(n-2)個目と・・・・2個目と1個目のセルがN」（10進数の「（2^n+1）-1」=2進数の「1111・・・1{←1がn回繰り返される}」）のデーター

という具合に入力して行き

判定結果を表示させるセルに入力する数式は、数式中の

($B$3="N")+($B$4="N")*2

の部分を

($B$3="N")+($B$4="N")*2+($B$5="N")*4+($B$5="N")*8・・・・・・+($B$(n+2)="N")*2^(n-1)

というパターンで変更して下さい。

お礼

ご回答ありがとうございます。
セルの位置と、内容をすごく丁寧に書いて下さったので、
判りやすかったです。
早速試してみたいと思います。
お忙しいところ、貴重なお時間を頂きましてありがとうございました!!

回答No.1

m_and_dmp

添付画像のように(1)～(4)の4つのケースをエクセルシート上に展開しておきます。
(4)のケースの右にケースによって異なる評価値を抽出するエリア（黄で塗りつぶしたエリア）を設けます。
計算をシンプルにする、あるエラーの時(E1)はたとえばB3に"N"ではなく、数字の2, 別ののエラーの時(E2)はたとえばB4に"N"ではなく数字の4と表示するように変更します。エラーではないときはどちらも数字の0とします。
”N"のままでも処理できますが、数字の方が1手順少なくなります。
そうすると、ケース(1)は、E1+E2=0、ケース(2)は、E1+E2=2、
ケース(3)は、E1+E2=4、ケース(4)は、E1+E2=6　となります。
OFFSET関数の中でこの数字を使って、4つのケースの中から数値を抽出します。
添付図では、ケース(1)の左の行の45という数値のセルは、E14です。ここがOFFSET関数の基準になります。
抽出エリアの数式は左側、タイトルの下から、
=OFFSET(E$14,0,M$12,1,1)
=OFFSET(E$14,1,M$12,1,1)
=OFFSET(E$14,2,M$12,1,1)
=OFFSET(E$14,3,M$12,1,1)
左側は、
=OFFSET(F$14,0,N$12,1,1)
=OFFSET(F$14,1,N$12,1,1)
=OFFSET(F$14,2,N$12,1,1)
=OFFSET(F$14,3,N$12,1,1)
となります。
式の意味を最初の式で説明しますと、基準となるセルF$14から0行下へ、セルM$12の数値分だけ右へずれた行の数値を拾ってきます。詳しくはOFFSET関数のヘルプで調べて下さい。
水色に塗ったエリアは、評価結果を計算するエリアです。
得点は、手入力、
E1+E2は、エラーコードの合計ですからどこかのセルに0,2または、0,4と出てくるとして、
その和の計算式を入れておきます。（現在は手入力）
評価結果の式はつぎのとおりです。（自動改行されてしまいますが、1本の式です。）
=IF(L21>N14,"TOO HIGH",IF(L21>M14-1,"S",IF(L21>M15-1,"A",IF(L21>M16-1,"B",IF(L21>M17-1,"C",IF(L21>M18-1,"D",IF(L21<N18,"TOO LOW")))))))

LOW ENDより小さい場合、TOO LOWと、HIGH ENDより大きい場合は、TOO HIGH、と表示する部分を追加してあります。
あらかじめケース分けしておけば、数式は、02momoさんが、作った式ひとつで、すむのです。

このように抽出用のエリアを設けると、IF文の中に数値を持ち込まないですむので、評価値を変更するのが簡単です。（テーブルの数値を変更するだけ）
ただし、注意しなければならないのは、ケーステーブルの構成が変わるとエラーコードを変えてやる必要があります。サンプルは一ケースが2行構成で、隙間なしに並べられています。間に空白行を入れると計算結果が違ってきますので、とりあえずはプロトタイプで試してください。

お礼

ありがとうございます!!
添付画像まで付けて頂き、判りやすいです。
早速試してみようと思います。
貴重なお時間を頂き、丁寧にご説明頂きましてありがとうございました。