どなたかExcelの関数教授お願いいたします

質問

IFで複数の条件と値をいれる時に条件の範囲が重なる時どうすればいいしょうか…
例えばaとbの値に関してaが500未満の時は5B。
aが500以上2000未満かつbが1000未満の時は5A。
aが2000以上5000未満かつbが1000以上の時は4。
aが5000以上10000未満かつbが3000以上の時は3
aが10000以上20000未満かつbが5000以上の時は2
aが20000以上かつbが10000以上の時は1
aが20000以上かつbが30000以上の時はA
aが20000以上かつbが60000以上の時はA'
としたいのですが、bの条件がかぶってるため例えばaが10000でbが10000になっても答えが2になってくれず、0で表示されてしまいます。

どなたか解決方法が、わかる方いらっしゃいましたらお願い致します。
式は
=if(and(a1>=500),"5B",if(and(a1>=500,a1<2000,b1<1000),"5A",if(and(a1>=2000,a1<5000,b1>=1000),"4",if(and(a1>=5000,a1<1
0000,b1>=3000),"3",if(and(a1>=10000,a1<20000,b1>=5000),"2",if(and(a1<=20000,b1<=10000),"1",if(and(a1>=20000,b1>=30000),"A",if(and(a1>=20000,b1>=60000),"a'",))))))))
と式を立てましたがうまくいきません
どなたかわかる方がいらっしゃいましたらお願い致します。

回答No.10

oldmonkey

こんにちは！

解決できたのでしょうか？
少しやっかいかも知れませんが、自身で関数を組み込む手もありますよ。
添付にVBAにてコーディングした例をつけています。
（条件式を順に記述しているので分かり易い利点があります。）
・A,Bどちらかがブランクであればヌル("")を返す
・先に指摘をされている未定義の範囲は「未定義範囲」を返す

このfunctionを組み込めば、「=Eval(a5,b5）」のようにして使用可能

回答No.9

tom04

こんばんは！
一案です。

↓の画像のようにSheet2の別表を作成しておきます。
行・列項目値（薄い緑色セル）は〇以上というコトになります。

尚、質問内容では画像の表内（B2～H7セル）の入力しているセルしか条件が判断できませんので、
判らない部分は空白にしています。
実際の条件に合わせて空白セルを埋める必要があります。

Sheet1のC2セルに
=INDEX(Sheet2!$B$2:$H$7,MATCH(A2,Sheet2!$A$2:$A$7,1),MATCH(B2,Sheet2!$B$1:$H$1,1))
という数式を入れています。
これでA・B列にデータを入力すると、C列に表内データが表示されます。

※　A・B列条件が空白セルにヒットすると「0」が表示されてしまいます。

IF関数をネストしても構わないと思いますが、表の作成次第ではすっきりできると思います。

参考になれば良いのですが・・・m(_ _)m

回答No.8

MarcoRossiItaly

No.3です。もしも私なら、無理して1つのセルに長い式を書くよりも、こうします。

各セルに次のとおり入力。すると、C:Hの列範囲に値の大小によるaの階層が、I:Qにbの階層が、「0」か「1」の数字で表示されます。また、R:Yにも、「5B」に該当する場合はその列が「1」となり、その他の列が「0」と表示されます。

C3　=0+($A3<500)　　D3　=(500<=$A3)*($A3<2000)
E3　=(2000<=$A3)*($A3<5000)　　F3　=(5000<=$A3)*($A3<10000)
G3　=(10000<=$A3)*($A3<20000)　　H3　=0+(20000<=$A3)
I3　=0+($B3<1000)　　J3　=0+($B3=1000)
K3　=(1000<$B3)*($B3<=3000)　　L3　=(3000<$B3)*($B3<=5000)
M3　=(5000<$B3)*($B3<=10000)　　N3　=(10000<$B3)*($B3<=30000)
O3　=(30000<$B3)*($B3<60000)　　P3　=0+($B3=60000)
Q3　=0+(60000<$B3)
R3　=C3　　S3　=D3*I3
T3　=E3*SUM(J3:Q3)　　U3　=F3*SUM($I3:K3)
V3　=G3*SUM($I3:L3)　　W3　=$H3*N3
X3　=$H3*O3　　Y3　=$H3*SUM(P3:Q3)

回答No.7

oldmonkey

先の回答で表とすべきでしたが罫線が入らないので見難いと思いますので添付にExcelで作成した表をつけました。表中黄色の部分が未定義領域となります。

表の下に関数を入れてテストした結果をつけています。
テストした数式は添付でも明示していますが、

　=IF(A<500,"5B",IF(A<2000,IF(B<1000,"5A",""),IF(A<5000,IF(B<1000,"",IF(B>=1000,"4","SSSSS")),IF(A<10000,IF(B>=3000,"3","DDD"),IF(A<20000,IF(B<3000,"","2"),IF(B<10000,"",IF(B<30000,"1",IF(B<60000,"A","A'"))))))))

こんなものです。
　数式中未定義エリアの部分は""、であったり"DDD"、"SSSSS"となっていますがご了承ください。

回答No.6

oldmonkey

提示された条件を整理してみました。

　　　　　a　　　　　　　　　　b　　　　　　　定義
　　　　 500未満　　　　　　　　　　　　　　 5B
　500～2000未満　　　1000未満　　　 　5A
　　　　　　　　　　　　　　　　　　　　　　　　　？
2000～5000未満　　　　　　　　　　　　　　？
　　　　　　　　　　　　　　1000以上　　　　　4
5000～10000未満　　　　　　　　　　　　　 ？
　　　　　　　　　　　　　　3000以上　　　　　3
10000～20000未満　　　　　　　　　　　　　？
　　　　　　　　　　　　　　5000以上　　　　　2
20000～　　　　　　　　　　　　　　　　　　　？
　　　　　　　　　　　　　10000以上　　　　　1
　　　　　　　　　　　　　30000以上　　　　　A
　　　　　　　　　　　　　60000以上　　　　　A'

のように？を付した箇所は何を定義するのか不明になっています。
この内容によっては条件の簡素化が図れるかもしれませんよ。→ 関数も簡略化？
（極端な例ではbだけで決定することにもなりかねませんね。）

回答No.5

molly1978

ANo.4です。補足します。

>IFで複数の条件と値をいれる時に条件の範囲が重なる時どうすればいいしょうか…

何かが優先するはずですので、条件が重なるということはないはずです。aとｂの表で条件を整理すると判りやすいと思います。

IF関数は、優先するほうから決めて行きます。
"2"の条件で、(a1>=10000,b1>=5000)として、a1<20000は使ってません。上位から定義していけば、a1<20000は必要ありません。a=30000でも"1"の条件を満たさなければ"2"です。a1<20000を入れると、"2"の条件から逆に外れてしまいます。

回答No.4

molly1978

まず明らかな入力ミスが、冒頭のa1>=500の他に下から2行目の
and(a1<=20000,b1<=10000) が、and(a1>=20000,b1<=10000)

条件を表などで整理するとよいと思います。
提示された条件からは、
・a=800,b=800
・a=2400,b=400
の場合、判定が不明です。補足説明からこれは、それぞれ5Aとなるのですね。

それなら、条件式を下位（5B）からでなく、上位（A'）から定義すべきです。
=if(and(a1>=20000,b1>=60000),"A'",if(and(a1>=20000,b1>=30000),"A",if(and(a1>=20000,b1>=10000),"1",if(and(a1>=10000,b1>=5000),"2",if(and(a1>=5000,b1>=3000),"3",if(and(a1>=2000,b1>=1000),"4",if(and(a1>=500,b1<1000),"5A","5B")))))))

回答No.3

MarcoRossiItaly

IF関数の入れ子で書くと、しっちゃかめっちゃかで難しいですね。代わりに、こういうのはどうですか？

=if(a1<500,"5B",)&if((500<=a1)*(a1<2000)*(b1<1000),"5A",)&if((2000<=a1)*(a1<5000)*(1000<=b1),4,)&if((5000<=a1)*(a1<10000)*(3000<=b1),3,)&if((10000<=a1)*(a1<20000)*(5000<=b1),2,)&if((20000<=a1)*(10000<=b1)*(b1<30000),1,)&if((20000<=a1)*(30000<=b1)*(b1<60000),"A",)&if((20000<=a1)*(60000<=b1),"A'",)

bの条件が足りないところがまだあるかもしれませんが、それは質問者さん自身でないとよく分かりませんので、適当に式を修正してください。

回答No.2

oldmonkey

aとbの条件が重なっていても順次定義すれば良いと思いますが？

関数を入力する前に条件をもう一度整理してみてみください。
全ての条件が定義されているとは思いません。
全ての条件が整っていないと思い通りの結果が得られないと思います。
　例えば　a=700、b=1500 の場合は何をセットすればいいのでしょうか？

補足

a=700,b=1500の場合はaの値が4に達していないので5Aにぬります。

回答No.1

soixante

詳しく全部検証したわけではないですが、

「aが500未満の時は5B」

と言ってます。

式の冒頭のところ、

＞=if(and(a1>=500),"5B",if(and(a1>=500,a1<2000,b1<1000),"5A・・・・・

a1<500

と違いますか？

補足

すいませんそこは入力間違えです。