エクセルの計算式エラーの理由

質問

エクセルで以下の計算式を作成しました。

=IF(LOOKUP(D22,D6:D20,C6:C20)=-2,"A",IF(LOOKUP(D22,D6:D20,C6:C20)=2,"B",IF(C2<=-2,"A",IF(C2>=2,"B",IF(AND(C2=-1,B1>1/3),"A",IF(AND(C2=1,B1<1/3),"A", IF(AND(C2=0,OR(C4<=-2,AND(C4=-1,B1>1/3),AND(C4=0,B1>1/2),AND(C4=1,B1<1/3))),"A","B")))))))

それぞれのIFの計算式は間違っていないのですが・・・
それを組み合わせると「エラー」の表示がでてしまいます。
この原因が分かる方アドバイス願いします。

また、エラーの出ないようにするにはどうすればいいのでしょうか？

よろしくお願いします。

回答No.2

ham_kamo · ベストアンサー

Excel2003までは関数のネスト（入れ子）は、７段階までです。（Excel2007では64になりましたが）
まずは以下のサイトを見てください。

http://www.relief.jp/itnote/archives/001147.php

そして、上のサイトで解説されているように、質問文の式を展開すると、

=IF(LOOKUP(D22,D6:D20,C6:C20)=-2,"A",
　IF(
　　LOOKUP(D22,D6:D20,C6:C20)=2,"B",
　　IF(C2<=-2,"A",
　　　IF(C2>=2,"B",
　　　　IF(
　　　　　AND(C2=-1,B1>1/3),"A",
　　　　　IF(
　　　　　　AND(C2=1,B1<1/3),"A",
　　　　　　IF(
　　　　　　　AND(C2=0,
　　　　　　　　OR(C4<=-2,
　　　　　　　　　AND(C4=-1,B1>1/3),
　　　　　　　　　AND(C4=0,B1>1/2),
　　　　　　　　　AND(C4=1,B1<1/3)
　　　　　　　　)
　　　　　　　),"A","B"
　　　　　　)
　　　　　)
　　　　)
　　　)
　　)
　)
)

と最高９段までネストしています。それでエラーが出るのです。
対処法ですが、このままの式ではどうしようもないので、補助セルを使って式を分割します。

上記IFの中のネストが深い部分を、いったん補助セルに抜き出します。仮にX1とすると、

X1=IF(AND(C2=0,OR(C4<=-2,AND(C4=-1,B1>1/3),AND(C4=0,B1>1/2),AND(C4=1,B1<1/3))),"A","B")

そして、元のIFの中でX1に抜き出した箇所を、以下のようにX1に変えます。

=IF(LOOKUP(D22,D6:D20,C6:C20)=-2,"A",IF(LOOKUP(D22,D6:D20,C6:C20)=2,"B",IF(C2<=-2,"A",IF(C2>=2,"B",IF(AND(C2=-1,B1>1/3),"A",IF(AND(C2=1,B1<1/3),"A",X1)))))

これらを同じように分解すると、
X1=IF(
　AND(C2=0,
　　OR(C4<=-2,
　　　AND(C4=-1,B1>1/3),
　　　AND(C4=0,B1>1/2),
　　　AND(C4=1,B1<1/3)
　　)
　),"A","B"
)

求めたい式
=IF(LOOKUP(D22,D6:D20,C6:C20)=-2,"A",
　IF(
　　LOOKUP(D22,D6:D20,C6:C20)=2,"B",
　　IF(C2<=-2,"A",
　　　IF(C2>=2,"B",
　　　　IF(
　　　　　AND(C2=-1,B1>1/3),"A",
　　　　　IF(
　　　　　　AND(C2=1,B1<1/3),"A",X1
　　　　　)
　　　　)
　　　)
　　)
　)
)
となり、ネストが６段までになるので、エラーにならないかと思います。

なお、式に出てくるセルに値を入れて検証したりとかはしていません。間違っているときはご容赦願います。

お礼

ham_kamoさま

早速のアドバイスありがとうございます。
アドバイスに従い補助セを活用して入力してみたら上手く作動しました。
本当にありがとうございました。来週月曜日締切りの資料が間に合いました（笑）

今後もアドバイスよろしくお願いします。

回答No.5

mshr1962

#3です。順番にこだわるなら、IF文のネストを少なくする苦肉の策です。
各結果に"A"になるものは奇数、Bになるものは偶数を割り当てました。
条件が真なら乗算した数値、偽なら0に置き換わります。
その最大値を2で割ったあまりが1なら"A"、0なら"B"を表示します。
=IF(MOD(MAX((LOOKUP(D22,D6:D20,C6:C20)=-2)*9,(LOOKUP(D22,D6:D20,C6:C20)=2)*8,(C2<=-2)*7,(C2>=2)*6,AND(C2=-1,B1>1/3)*5,AND(C2=1,B1<1/3)*3,AND(C2=0,OR(C4<=-2,AND(C4=-1,B1>1/3),AND(C4=0,B1>1/2),AND(C4=1,B1<1/3)))*1),2)=1,"A","B")

お礼

mshr1962さま

私の不十分な質問コメントにいろいろな角度からのアドバイス本当にありがとうございます。
「奇数」「偶数」という発想はまったくありませんんでした。
確かにアドバイスをいただいた式を組み込んでみるとすんなりシステムは作動しました（笑）
今後もアドバイスよろしくお願いします。

回答No.4

imogasi

この質問に対し、じっくり考えて、回答されている回答者がおられるので、イヤならパスすればよいので、私の言うことも説得性がないとお考えかもしれません。しかし
こんな複雑な式を回答者に解読させるのでなく、今後は、文章や例での説明も添えて質問してくださるようお願いします。
（１）関数でも、別の方法があるのではないか。
（２）VBA（やユーザー定義関数）でやれば邯鄲になるのではないか
という漠然とした、予想をしつつ。

お礼

imogasiさま

アドバイスありがとうございます。
確かに多くの方からのアドバイスをいただく上で、私の質問の記載方法はあまりに不十分であったと反省しております。
ご指摘の通り、文章や例を添えることで違った角度からのアドバイス（VBA使用など・・）がいただけたかもしれません。
今後同様に質問事項を記載するときにはアドバイスどおり対応していきたいと思います。
今後ともアドバイスよろしくお願いします。

回答No.3

mshr1962

ネストの階層数に関しては#1,2のかたの言われるとおりです。
＞また、エラーの出ないようにするにはどうすればいいのでしょうか？
この式は一見、複雑に見えますが結果は常に"A"か"B"ですよね。
"A"になる条件だけを集めて、それ以外は"B"にすればいいのではと思いますが．．．
=IF(OR(LOOKUP(D22,D6:D20,C6:C20)=-2,C2<=-2,AND(C2=-1,B1>1/3),AND(C2=1,B1<1/3),AND(C2=0,OR(C4<=-2,AND(C4=-1,B1>1/3),AND(C4=0,B1>1/2),AND(C4=1,B1<1/3)))),"A","B")

お礼

mshr1962さま

早速のアドバイスありがとうございます。
式を簡略化することは私も考えました。
ただmshr1962さんのご提案の式では例えば
LOOKUP(D22,D6:D20,C6:C20)=-2,"A",
という条件と
C2<=-2,"A",が並列関係になりますよね！？

ここには書きませんでしたが、一応AとBに振り分ける条件として強い条件と弱い条件があるので・・・IFを多用して順位をつけてみたのですす・・。

IF(LOOKUP(D22,D6:D20,C6:C20)=2,"B",　の場合には
C2<=-2,"A",　を満たしていてもBに振り分けたいのです・・・。

分かりにくくてすいません　^^;

でも私が考えた以上に数式はすっきりできることは勉強になりました。
ありがとうございました。

回答No.1

yaburegasa

こんにちは
関数のネスティングのレベルが、大きすぎるのではないですか？
７レベルまでだったかに思いますが・・・

お礼

yaburegasaさま

早速のアドバイスありがとうございます。
関数に「ネスティング」というものがありレベルも制限されているのですね・・・。大変勉強になりました。

ありがとうございました。

今後ともアドバイスよろしくお願いします。