Excelで計算式がずれるパターンとずれないパターンを教えて下さい(絶対参照の話ではない)

長年困っていることがあります。
Excelで行や列を挿入する際に、既に入っている計算式についてうまくセルの位置や集計範囲を自動で判別して反映してくれる場合と、そうでない場合があると思います。
そのため、仕事で他人から依頼があり行や列を挿入するのにかなり躊躇してしまいます。
「こういうパターンの場合は前後の計算式も自動修正される」「こういうパターンの場合は手動で計算式の修正が必要」というパターンはあるのでしょうか・・？
「この場合は、ずれないから大丈夫」と分かっていれば怖くないのですが、「来月からこの商品を表に追加して」などと言われた際には、こわごわ行や列を挿入しては、計算式がずれていないか１つ１つセルをクリックして確認しいつも膨大な時間がかかります。
実際仕事で使用しているのは損益計算書などの複雑なフォームですが、一番シンプルな例を添付します。
この場合、CとDの間の５行目に一行追加するとなると、「計」のSUMの計算式は修正しなければなりません。ですが、表によっては「あれ？挿入した行も勝手にSUMの集計範囲に加わってくれている！」
ということもあります。なにかパターンがあるのでしょうか。

No.5 ベストアンサー 回答者： tom04 回答日時： 2020/10/09 08:50

こんにちは！

すでに回答は出ていますが・・・
＞=SUM(B2,B3,B4,B5,B6)

のような数式にせずに、一般的には連続している場合は
=SUM(B2:B6)

だけでいけます。
（対象範囲が飛び飛びの場合はお示しのような数式にすることもあります）

さて、数式が入っているシートに行挿入（行削除）という操作はあまりやらない方が良いと思いますが、
どうしても後から行挿入・行削除をしたい場合は
お示しの画像の場合はB7セルを
=SUM(INDIRECT("B2:B"&ROW()-1))

としておけばとりあえずは対応できると思います。m(_ _)m

この回答へのお礼

1年以上たってしまって申し訳ありません・・。
ご回答ありがとうございました。
当時いただいた回答を見て難しそうな関数だな・・と諦めてしまったのですが、改めて今ネットでINDIRECT関数について検索して勉強しています。ただ、　SUM(INDIRECT("B2:B"&ROW()-1))をぜひ活用したいのですが、数式の意味が理解できません。B2:Bというのはどういう意味でしょうか・・。
といっても締め切られてしまっているので、回答できませんよね・・( ..)

お礼日時：2021/11/10 19:49

No.4 回答者： d-q-t-p 回答日時： 2020/10/09 02:52

そもそも数式入れてから編集なんてしませんけど……

入力表に数式を入れるとしたらテーブル設定にして構造化参照を仕込んで
いる時くらいです。どうしてもという場合でも列参照にします。

画像の事例なら
=SUMIF(A:A,"<>計",B:B)

と入れておけば行挿入や行削除を気にする必要はなくなります。
循環参照にもならないし。

> なにかパターンがあるのでしょうか。
行挿入する位置が範囲で参照している参照元と被るかどうかだけです。

No.3 回答者： 銀鱗 回答日時： 2020/10/09 02:17

数式とその数式に含まれる範囲の間に行を挿入する場合は、範囲は拡張されません。

これ、今も古いバージョンも同様です。

質問のケースでは、6行目と7行目の間に行挿入するとSUM関数の範囲は拡張されません。

No.2 回答者： mike_g 回答日時： 2020/10/09 01:20

添附圖參照(Excel 2019)

ジックリ御鑑賞下されたく(^_^)

No.1 回答者： maho-maho 回答日時： 2020/10/08 23:59

上記のカッコ内が、全部一つずつセルを指定していますね。

これだと行が増えても反映してくれません。

通常はオートSUMを使って
=SUM(B2:B6)
などとすると行が増えても修正されると思います。