【Excel】反復計算を使う前の確認|循環参照と収束の違い

反復計算は、意図した循環を含む計算を繰り返すための機能です。循環参照の警告を消す目的だけで有効にしないでください。入力ミスによる循環なら、参照先を直すのが先です。

1. 意図しない循環参照を先に直す

「数式」→「エラーチェック」→「循環参照」で対象セルを確認します。合計セル自身をSUMの範囲に入れていないか、別セルを経由して自分へ戻っていないかを調べます。

反復計算をオンにすると、意図しない循環も許される場合があります。値が表示されたことを、正しい計算になった証拠としないでください。元の式と変更前の設定を残します。

2. 反復計算を設定する

1. 作業用のコピーを開きます。
2. Windows版では「ファイル」→「オプション」→「数式」を開きます。
3. 「反復計算を行う」を有効にします。
4. 最大反復回数と変化の最大値を指定します。
5. 意図した結果へ近づくかを検算します。

標準では100回、または変化が0.001未満になった時点が終了条件です。回数上限へ達しただけの場合もあるため、終了と収束は区別します。

3. 別の行に計算過程を並べて確かめる

例えば「前の値に10を足して2で割る」を、初期値0から繰り返すと、5、7.5、8.75、9.375と10へ近づきます。A2に0、A3に=(A2+10)/2を入れて下へコピーすれば、循環を使わず過程を確認できます。

このように計算がどちらへ進むかを見てから、必要なら反復設定を検討します。反復計算をオンにするだけで、任意の式がニュートン法へ変わるわけではありません。

4. 発散や振動を見分ける

前の値に1を足す式は増え続けます。前の値を1から引く式を0から始めると、1と0を行き来します。回数を増やしても、期待した1つの値へ近づかない計算です。

小さな変化で止まった場合も、必要な単位に対して十分な精度かを確認します。0.001が意味する大きさは、円、千円、比率などの単位で異なります。初期値や回数を変えた時の結果も比べます。

5. 固定時刻の記録として過信しない

自己参照とNOWを組み合わせる時刻保持式は、再計算、コピー、セルの消去、別環境での再読込などを含めた検証が必要です。業務の確定記録や変更履歴を残す仕組みとして、一律に推奨できません。

固定した日付や時刻が必要なら、値として入力する方法や、管理された入力・記録機能を検討します。必要な記録を数式の保持状態だけへ依存させないようにします。

6. 設定値と結果を記録する

反復計算を使うモデルには、初期値、計算式、最大回数、変化のしきい値、期待する結果を残します。他の開いているブックの計算にも影響がないか確認してください。

共有先で開き直し、同じ入力から同じ結果が得られるかを試します。計算が重い時に上限回数だけを増やすのではなく、式や依存関係を見直します。

7. 不要なら元の設定へ戻す

検証が終わったら設定を戻し、意図しない循環が残っていないか再確認します。反復を前提にしたブックは、その旨を利用者に分かる場所へ記載します。

確認手順と終了条件はMicrosoftの循環参照の説明を参照できます。

📊

Excelトラブル完全解決データベースこの記事以外にも、様々なエラー解決策をまとめています。困った時の逆引きに活用してください。

🏆

超解決 Excel検定 あなたのExcel実務能力を3分で測定！【1級・2級・3級】