エクセルで再計算のエラーが出る

質問

エクセルで、INDEX関数と循環参照を使って表を作っています。

＜INDEX＞
Sheet1には、A列に名前（2000ケースくらい）、B列以降に毎日のある数字（100日分）が入力されています。
ただし、A列に名前はあるが、B列以降に数字が入力されていない場合も混じってあります。

Sheet2にはINDEX関数を使って、あるセルC1に「1」と入力すればSheet1のA1の毎日の数字がSheet2のA1からA100に参照されるように作ってあります。
C1に「2」と入力すればSheet1のA2の毎日の数字が参照されます。

＜循環参照＞
上記のSheet2にでは、明らかに少なすぎるデータを欠損データとするように循環参照をしています。
具体的には、とある1日のデータが、100日分の平均値の10分の1未満の場合には欠損とするようにしてあります。
Sheet2のセルA1からA100に100日分の数値がINDEXで参照されており、

セルB1に「=if(A1="","",if(A1=0,"",if(A1<A102,"",A1)))」
セルB2～B100までB1と同様の計算式
セルB102に「=B102/10」
セルB101に「=average(B1:B100)」

と、循環参照させてあります。
エクセルのオプションの「数式」の「ブックの計算」は自動、「反復計算を行う」にチェックし反復回数は100（100日分あるため)、変化の最大値は1（小数点以下の数値は必要ないので）にしてあります。

＜計算エラー＞
上記のINDEXと循環参照を利用して通常は問題はありませんが、
Sheet1に数値が入力されていないケースが出た後にエラーが出て来ます。
Sheet1のA列で、例えばA4のケースに100日分のデータが入力されていなかったとします。
Sheet2のC1に1～3の数値を入力した時は問題ありませんが、4を入力したらB列は全て「#NUM」と表示されます。
これは、循環参照をするにもできないからなので、理解できます。
その後は、C1に何を入力しても（さっきは問題なかった1～3を入力しても）、「#NUM!」が表示されたままになり、循環参照の再計算がうまくいきません。
主動でF9を押して再計算も「#NUM!」のままになります。

どのようにすれば、これを回避できるでしょうか?

最終的には、グラフ等を作成して、マクロを組んで一括で数百ケース程を印刷しようと思っています。

アドバイスよろしくお願いいたします。

※
Office Home and Business 2010
Windows7 professional 32bit
を使用しています。

回答No.3

keithin · ベストアンサー

自信たっぷりに間違ってませんと言い切りましたね。
ご自分の文章と実際のエクセルを照合したり、あるいは指摘を受けてどこか間違えてないかを探したり、あるいは回答の数式はそこをキチンと説明している事などに気が付かなかったようです。

循環参照の使い方も、循環参照を使って何を計算したいのかも、完全に把握しています。一応そのうえで間違いを指摘しておくと

１）平均の10分の1以下もデータ無しとするので（if(A1<A102,""の部分）
→Ａ１０２はご説明のどこにも出てきません。回答では修正済みです。

２）平均の10分の1は29.4です。
→Ｂ１０２の数式は確かに循環参照していますが、循環参照させたいのはそうじゃありません。回答では必要な１／１０だけ説明しています。

３）B1からB10に#NUM!#NUM!#NUM!…
→Ｂ１からＢ１０に１０個のエラーが発生する、というご説明をされています。
でもエラーが直接の原因は、Ｂ１からＢ１０の他に「もう一個」、平均値を求めているセル（Ｂ１０１）でもエラーを発生しているからです。（余談ですがもう一個、平均値の１／１０も当然エラーになります）
そしてAVERAGE関数は、対象セル範囲に「一つも数字が無い場合に」#NUMではなく#DIV/0エラーを発生します。結果してＢ１からＢ１０の全てのセルが、AVERAGE関数のエラーに引っ張られて#DIV/0になります。

ついでに
４）IFERROR関数を使うと、「#NUM!」の時には任意の何かを表示させるだけで、循環参照が動いているわけではなくなる
→この認識も間違いです。
「AVERAGE関数がエラーになる」場合、循環させるべき値がそもそも存在しないため、IFERRORで循環を切って暫定的な値を表示します（今回は""を表示します）。
その後Ａ列に数字が現れるとエラーは解消され、回答の数式により意図した通りの計算結果が現れます。

さて。以上から推測されるのは、あなたのご相談と補足は「実際にご自分のエクセルを確認したり、回答を試してみる」といった事実確認をしていない、ただの「説明のための説明」だということです。ヒトの話を聞いて、まずはその通り実際にご自分の手を動かしてやってみる所から始めてみてはいかがですか。

お礼

A102のところが間違っていました。
ご指摘ありがとうございました。

そもそもの、INDEX関数で作っているところにエラーがあったのがわかりました。
そこにIFERROR関数を入れたら、循環も全て解決しました。

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

回答No.2

bunjii

>エクセルで、INDEX関数と循環参照を使って表を作っています。
循環参照を使うと正しい結果が得られません。
従って、最大反復回数と変化の最大値を指定します。
反復回数が少ないと目的の値とかけ離れた結果になります。

>Sheet2のC1に1～3の数値を入力した時は問題ありませんが、4を入力したらB列は全て「#NUM」と表示されます。
A列とB列の値はどのようになっているかによって対処方法が異なります。

>セルB1に「=if(A1="","",if(A1=0,"",if(A1<A102,"",A1)))」
>セルB2～B100までB1と同様の計算式
>セルB102に「=B102/10」
>セルB101に「=average(B1:B100)」
論理が合いません。

>どのようにすれば、これを回避できるでしょうか?
エラーの表示を回避するだけであればIFEROOR関数でエラーのとき""にすれば良いでしょう。
信憑性のある値に近づけたいのであれば反復回数を順次増やしてみることになるでしょう。

お礼

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

回答No.1

keithin

B1，B102としてご相談に提示された数式は，恐らくあなたの実際のエクセルの内容とは違う間違った数式です。あるいはもしかすると言葉のご説明に誤りがあります。
また「#NUM!が出る」というご説明も誤りですね。

簡易にはＢ１０２に１０分の１を入れておき
Ｂ１：
=IF(A1="","",IF(A1=0,"",IF(A1<IFERROR($B$102,0),"",A1)))
以下コピー
とでもしてみます。
IFERRORの時ゼロでいいのかは，あなたのエクセルの実際のデータに応じて適切に調整して下さい。

補足

ご回答ありがとうございます。

説明不足だったかもしれませんが、私が記入した式に間違いはありません（絶対参照を抜かしていることを除いて）。

私の数式は循環参照をすることを前提にして作っています。
100日分のデータは多すぎるので、ここでは10日分のサンプルでご説明します。

18 0 576 515 ＿ 37 422 415 367 299
という10日間のデータがあったとします。
ここでは「＿」はデータが無いことを意味することとします。
この場合は9日分のデータしかなく、平均は294になり、平均の10分の1は29.4です。

「=if(A1="","",if(A1=0,"",if(A1<A102,"",A1)))」
私が質問時に記入したこの式では、0もデータ無しと同じ扱いをするので、

18 ＿ 576 515 ＿ 37 422 415 367 299
と変化し、平均は331になります。

平均の10分の1以下もデータ無しとするので（if(A1<A102,""の部分）、
＿ ＿ 576 515 ＿ 37 422 415 367 299
と変化し、平均は375で、平均の10分の1以下は37.5です。

37.5以下がもう一か所あるのでさらに処理され、
＿ ＿ 576 515 ＿ ＿ 422 415 367 299
というデータになったところで処理を終える

という循環参照を入れています。

つまりA1からA10に
18 0 576 515 ＿ 37 422 415 367 299
と入っていれば、B1からB10に
＿ ＿ 576 515 ＿ ＿ 422 415 367 299
と表示されるようになります。

これらを、INDEX関数を使って順番に表示することになるのですが、
データが全くないケースを一度表示させると、その後INDEX関数で元のケースに戻っても、
A1からA10に
18 0 576 515 ＿ 37 422 415 367 299
が表示されても、B1からB10に
#NUM! #NUM! #NUM! #NUM! #NUM! #NUM! #NUM! #NUM! #NUM! #NUM!
と表示されてしまいます。

回答してくださったkeithinさんのIFERROR関数を使うと、「#NUM!」の時には任意の何かを表示させるだけで、循環参照が動いているわけではなくなるので、結局処理が止まったままとなります。

手を付けていただきまして恐縮でしたが、私のしたい方向と少し違っていたかと思います。