エクセルの関数について（ＩＦ，ＡＮＤ関数）

質問

エクセルでIF,ANDを用いた関数計算の数式を作っているのですが、うまく出来ません。
どなたか詳しい方、教えて下さい。

たとえばこんな感じです。
※条件は２個
●第１条件～東京、大阪、北海道（３パターン）
●第２条件～数値の大小（３パターン）

この２個の条件を満たす値、全部で９種類の計算パターンを実行できる数式を作ろうと思っています。
あともう１条件（ＩＦ文）入れたいのですが、これ以上入れると計算しません。

=IF(AND(B22>5000,B5="東京"),111,IF(AND(B22>=2000,B5="東京"),ROUND(300*B22^2/1000,4),IF(AND(B22<2000,B5="東京"),222,IF(AND(B22>5000,B5="大阪"),333,IF(AND(B22>=2000,B5="大阪"),ROUND(300*B22^2/1000,4),IF(AND(B22<2000,B5="大阪"),444,IF(AND(B22>5000,B5="北海道"),555,666)))))))

回答No.1

papayuka · ベストアンサー

IFのネストは７つまでです。
これを回避するには最初に一定の条件で分けてから、次の条件で分けるようにします。
まず「東京」かどうか、又は「>5000」で分けるなど。

=IF(B5="東京",IF(B22>5000,111,IF(B22>=2000,ROUND(300*B22^2/1000,4),222)),IF(B5="大阪",IF(B22>5000,333,IF(B22>=2000,ROUND(300*B22^2/1000,4),444)),IF(B5="北海道",IF(B22>5000,555,IF(B22>=2000,ROUND(300*B22^2/1000,4),666)),"")))

条件２が2000以上5000以下の場合は、条件１に影響しないなら、そこで分岐させた方がまだ解りやすいかも。

それよりも別途対応表を作り、VLOOKUP等を使用した方が更に解り易いけど。

お礼

早速の回答、ありがとうございました。
IFのネストは７つまでなんですね。
勉強になりました。
エクセルの関数については、最近ちょっとづつ取り入れたりしていますが、まだまだ分からない所だらけです。VLOOKUPについてはまだよく分からないので勉強してみます。

回答No.10

sige1701

質問の主旨を誤解していたようです。

>全部で９種類の計算パターンを実行できる数式を作
>ろうと思っています。

=IF(B5="東京",IF(B22>5000,111,IF(B22>=2000,ROUND(300*B22^2/1000,4),222)),IF(B5="大阪",IF(B22>5000,333,IF(B22>=2000,ROUND(300*B22^2/1000,4),444)),IF(B5="北海道 ",IF(B22>5000,555,IF(B22>=2000,ROUND(300*B22^2/1000,4),666)),"その他")))

または

=IF(AND(B22>5000,B5="東京"),111,0)
+IF(AND(B22>=2000,B5="東京"),ROUND(300*B22^2/1000,4),0)
+IF(AND(B22<2000,B5="東京"),222,0)
+IF(AND(B22>5000,B5="大阪"),333,0)
+IF(AND(B22>=2000,B5="大阪"),ROUND(300*B22^2/1000,4),0)
+IF(AND(B22<2000,B5="大阪"),444,0)
+IF(AND(B22>5000,B5="北海道"),555,0)
+IF(AND(B22>=2000,B5="北海道"),ROUND(300*B22^2/1000,4),0)
+IF(AND(B22<2000,B5="北海道"),666,0)

お礼

早速の回答、ありがとうございました。
参考になりました。

回答No.9

imogasi

難しい問題ですね。というか、エクセル関数で処理するに不適当なパターンです。VBAのように記述の行数に制約の無いプログラムで処理するのが、ふさわしい。
ーー
もともと関数はセルの値から、セルの値を求めるものです。この問題も大筋そうなのだが、５０００－２０００だけ式の「型」で指定されている。エクセルは、関数が文字列でわかっても、その式の計算はできない、と思うので、難しいのです。
ーー
だから一般的な回答はできず、この質問のたまたまの、都合のいい、条件に縋って、式を簡略化するよりほか無い。
もうひとつ、２０００の等号の問題がある。
ーーー
参考までに上げると、下記の表で表（の値）を引くには
A1:E5のデータ（第１行目は参考までに上げたもの）
-5000 5000-2000 2000 1999-0 （第１行目）
10000 5000 2000 2000 （第２行目、区分け
東京 222 111 111 333
大阪 555 444 444 666
北海道 888 777 777 999

大阪 2000 444 （第９行A8:C8）

の、大阪と２０００を指定して、４４４を引くには、
C9に
=INDEX($B$3:$E$5,MATCH($A$9,$A$3:$A$5,0),MATCH($B$9,$B$2:$E$2,-1))
とする。
---
それで
（１）式を指定するところの、北海道以外の２０００から５０００をIF文での中で分離、して（＝　北海道でなく２０００から５０００）
該当は式ROUND(300*B22^2/1000,4)を指定。
（２）それ以外を上記説明のINDEX-MATCHで値を引く。
ーーーー
少しは見通しが良くなり、これ以上の多様性にも対処できるでしょう。
上記の、表引きは、複雑なので、式に凡ミスがあればお許しください。

お礼

早速の回答、ありがとうございました。
ＶＢＡとはマクロのことでしょうか？
INDEX-MATCHについても理解しきれていないのでもう少し勉強します。

回答No.8

poohron

No.7です。
表示結果の表をちょっと書き間違えました(;^_^A

　　　　　＞5000　　>=2000　　<2000
東　京　　　333　　　222　　　　111
大　阪　　　666　　　555　　　　444
北海道　　 999　　　888　　　　777

でした…。

お礼

早速の回答ありがとうございました。

回答No.7

poohron

ANDは用いていない回答です。

=(IF(B5="東京",0,IF(B5="大阪",1,IF(B5="北海道",2,-1)))*3
+IF(B22>5000,3,IF(B22>=2000,2,IF(B22>=0,1,0))))*111

※ただし、ご質問に記述された関数とは表示が以下のとおり若干変わります。
また、東京・大阪・北海道以外の地名の場合は0または負の数字が表示されます。
　　　　　＞5000　　>=2000　　<2000
東　京　　　111　　　222　　　　333
大　阪　　　444　　　555　　　　666
北海道　　 777　　　888　　　　999

AND,IFどころか他の関数も使わずに記述することも可能です。

=(((B5="東京")+(B5="大阪")*2+(B5="北海道")*3-1)*3
+(B22>5000)+(B22>=2000)+(B22>=0))*111

お礼

早速の回答ありがとうございました。
参考になりました。

回答No.6

redowl

北海道の　残り部分が　？？？ですが

IF関数　使わずで．．．．．

=(B5="東京")*(B22>5000)*111+(B5="東京")*(B22<2000)*222
+(B5="大阪")*(B22>5000)*333+(B5="大阪")*(B22<2000)*444
+(B5="北海道")*(B22>5000)*555+(B5="北海道")*(B22<2000)*666
+(B22<=5000)*(B22>=2000)*ROUND(300*B22^2/1000,4)

お礼

早速の回答、ありがとうございました。
このような作成方法もあるのですね。
ちなみにこの中の＊って掛け算の意味なのでしょうか？？
もうちょっと勉強してみます。

回答No.5

sige1701

数値を求めていますので

=IF(AND(B22>5000,B5="東京"),111,0)
+IF(AND(B22>=2000,B5="東京"),ROUND(300*B22^2/1000,4),0)
+IF(AND(B22<2000,B5="東京"),222,0)
+IF(AND(B22>5000,B5="大阪"),333,0)
+IF(AND(B22>=2000,B5="大阪"),ROUND(300*B22^2/1000,4),0)
+IF(AND(B22<2000,B5="大阪"),444,0)
+IF(AND(B22>5000,B5="北海道"),555,0)
+IF(AND(B5<>"東京",B5<>"大阪",B5<>"北海道"),666,0)

でもいいかな

お礼

早速の回答、ありがとうございました。
勉強になりました。

計算を試してみた所、Ｂ２２が５０００以上になると２種類の計算値を足してしまう所がありました。（B22>5000、B22>=2000の両方の計算値の合計を足してしまう）

数式の考え方自体は学ばせて頂きました。

回答No.4

sige1701

=IF(B5="東京",IF(B22>5000,111,IF(B22>=2000,ROUND(300*B22^2/1000,4),222)),IF(B5="大阪",IF(B22>5000,333,IF(B22>=2000,ROUND(300*B22^2/1000,4),444)),IF(AND(B22>5000,B5="北海道"),555,666)))

お礼

早速の回答ありがとうございました。
助かりました。

回答No.3

chukenkenkou

#2回答者です。

間違いを回答していまいました。

こちらでテストした時に転記ミスをしたようで、#1回答者さんの説明どおり、IF文のネストで引っ掛かってますね。

条件式の見直しをするしかないようです。

お礼

早速の回答、ありがとうございました。
まだまだ分からない所だらけですが、こんな便利なページがあるんですね。勉強になりました。

回答No.2

chukenkenkou

関数のネストの条件に、引っ掛かってしまったようですね。

関数のいくつかを別の作業用のセルに入れて、計算するようにすれば大丈夫のようです。

例えば、C列を作業用に使えるとすれば、、、

C1セルに、「=AND(B22>5000,B5="東京")」
C2セルに、「=AND(B22>=2000,B5="東京")」
C3セルに、「=AND(B22<2000,B5="東京")」
C4セルに、「=AND(B22>5000,B5="大阪")」
C5セルに、「=AND(B22>=2000,B5="大阪")」
C6セルに、「=AND(B22<2000,B5="大阪")」
C7セルに、「=AND(B22>5000,B5="北海道")」

のように入力します。

質問で示されている式を、上記セルを参照するように変更します。

=IF(B5="×",-1,IF(C1,111,IF(C2,ROUND(300*B22^2/1000,4),IF(C3,222,IF(C4,333,IF(C5,ROUND(300*B22^2/1000,4),IF(C6,444,IF(C7,555,666))))))))

作業用の列は、非表示にすれば、見た目は変わりませんね。

お礼

早速の回答ありがとうございました。