SUM関数、行挿入時に自動更新する？しない？

質問

excel2007を使用しています。

今、例えばA1からA10に数値が入っているとして、11行目に合計をとりたいときは、A11にsum関数でsum(A1:A10)と入力しますよね。
このとき、10行目と11行目の間に行挿入して明細が1個増えたとします。合計を入れた数式は1行下の12行目に移動し11行目に任意の数値を入力します。すると、sum関数はsum(A1:A11)に自動更新されますか？それとも、sum(A1:A10)のままですか？

実はこれについて、自動更新するときとしないときとがあるようなのですが、どういうときに自動更新されて、どういうときに自動更新されないのかがわからず困っております。
色々試しているのですが、データがA1からA10まで連続的に入っているときは更新されます。ところが、A1,A4,A10などととびとびで入っているときは更新されません。しかし、とびとびでも、A1からA6とA9,A10に数値を入力したときは更新されました。また、A2,A4,A6,A8と規則的に１ぎょうおきにに入っているときも更新されました。

他にもいろいろと試してみましたが、どんなときに自動更新され、どんなときに自動更新されないのか、規則性が見えてきません。。。

どなたかおわかりになる方がいらしたら、教えていただければと思います。
よろしくお願い致します。

回答No.4

MarcoRossiItaly · ベストアンサー

おっしゃっている現象は、「形式と数式の自動拡張」のオプションによるものです。

設定する場所は、Office ボタン「Excel のオプション＞詳細設定＞編集設定＞データ範囲の形式および数式を拡張する」です。

これは、書式が勝手に設定されたり、空白セルに数式が勝手に入力されたりする機能です。邪魔くさいと思われる場合は、チェックを外してしまいましょう。

これが機能するために必要な条件については、参考 URL をご覧ください。質問者さんが No.1 の補足欄でおっしゃっている(1)、(2)の現象が確かにそのとおりに起こることも、読んでいただくと分かると思います。

＞他の方の回答にもあるとおり、「自動更新されることはない」のが正しい動きのようですが、

いちばん下に行を追加する場合、(1)の「60」を入力していない時点での SUM 関数による参照範囲については、そのとおりです。

＞こうなってしまうので、A1～A5の値によって自動更新するしないがあるのかな、、と思ってしまうわけです。

上のオプションが設定されている場合は、そのとおりです。

ただ、(1)で「60」という数値を入力する際に、Ctrl+Enter のキー操作により入力すると、自動拡張が働かないかと思います。よかったらお試しください。

参考URL：

http://office.microsoft.com/ja-jp/excel-help/HP001216424.aspx

お礼

オプション設定を変えてみたら、期待通りの自動更新されない状態になりました。
こういうものがあったんですねぇ～。

また、「Ctrl+Enter のキー操作により入力すると、自動拡張が働かないかと思います。」もやってみました。確かに自動更新されませんでした。すごいですねぇ～こういう技があるとは・・・。

参考URLでご指示いただいたページも閲覧しました。
頭の中がもやもやしていた数日間でしたが、お陰様でたいへんスッキリ♪いたしました。
勉強になりました。

このたびはありがとうございました。

回答No.6

chayamati

確認しました。
仰る通りです。失礼しました。

１０１行目にsum(A1:A10０)として検証してみました。
　合計行の上３行が有効数字(０を含む)であれば自動更新されることを確認しました。
　※この動作は1度に複数行を挿入しても有効であるようです。

　その他詳しい検証はしていませんが
　合計行の上２行が有効数字で上３行目が空白でも
　ここまでのセルにある程度有効数字があると自動更新されることがあります。

回答No.5

keithin

＞入力＋Enterのタイミングで、…関数の中を見てみるとsum(A1:A6)と自動更新されています

あぁ、元のよくご相談を見てみると、数式を移動した後に「新たにデータを記入すると」数式が更新されると書いてありましたね。
誰もかれもが「数式を移動した」だけでは自動更新しないと回答しています。

エクセルのオプションの詳細設定で、「データ範囲の形式および数式を拡張する」の機能が作用すると、そういう動作が起こります。
その場合の自動更新の条件は「４行以上連続して記入してある」なので、Ａ１：Ａ４にデータが連続で記入してあってＡ５に=SUM(A1:A4)と数式が記入してある状況から、ご相談の操作を行うと数式が書き換わります。

まばらにデータが記入されているときにはこの機能が反応しない（エクセルが連続データの領域だと拾ってくれない）ので、Ａ１とＡ５のようなときには作動しません。

よくご相談を読まずに回答してしまい、大変失礼しました。

お礼

いいえ、とんでもないです。
さらに詳しいご回答ありがとうございました。
オプションの設定を変えてみたら、確かに自動更新されなくなりました！
これで頭がスッキリいたしました。

このたびはありがとうございました（^^)

回答No.3

ogawa_sora

今晩は。
最初に指定した範囲内に行を挿入した場合は、自動で範囲が増えます。
質問者さんの場合：１～１０セルの間に行を挿入した時、自動で１～１１セルが指定範囲になります
最初に指定した範囲外に行を挿入した場合は、自動では範囲が増えませんので範囲を指定し直す必要があります
質問者さんの場合：１～１０セルが最初に範囲指定したセルなので、１０セルと１１セルに行を挿入しても指定範囲は１～１０セルのままです。

お礼

お礼が遅くなりましたが、、、ご回答ありがとうございました。

回答No.2

chayamati

集計の途中のセルに空白セルのあるなしは関係ありません

集計対象の最後の行までにすると自動更新されます。

１０行目で挿入すると１０行目の手前に挿入され元の１０行目は１１行目に移動して自動更新されますが
１１行目（SUM関数の行）で挿入すると１０行目は元の位置で自動更新しません

お礼

お礼が遅くなりましたが、ご回答ありがとうございました。

回答No.1

keithin

＞10行目と11行目の間に行挿入して
　：
＞sum関数はsum(A1:A11)に自動更新されますか？

いいえ。エクセルでは、その操作で関数が自動更新される事はありません。

＞自動更新するときとしないときとがあるようなのですが

行挿入する場所がご質問で書かれているように１０行目と１１行目の間ではなく、１行目から１０行目の間だった場合には、数式が更新されます。たとえば１１行目ではなく、１０行目を選んで行挿入の操作をしているような場合です。（参考：１行目を選んで行挿入した時は、違う風に自動更新されます）

ちなみに「行挿入」するには、行番号の数字の上で右クリックして「挿入」を選びます。

＃どうしても納得いかないときは
「例えば」じゃなく、「実際には」どういう数式をどこ番地のセルに記入していて、その時に「どこを選んでどういう操作をした」のか、実際にあなたのエクセルを目の前に開いて事実をよく確認しながら、目に見えるその通りにご相談を書きなおしてみて下さい。

補足

ご回答ありがとうございます。
他の方の回答にもあるとおり、「自動更新されることはない」のが正しい動きのようですが、私のパソコンで操作するとそうならず、何かexcelの設定上の問題なのでしょうか・・・。

実際の操作手順を書かせていただきますね。

(1)自動更新される場合
A1～A5セルに順に10，20，30，40，50と入力します。
A6に=sum(A1:A5)と入力すると、合計値150が表示されます。
関数を入力した6行目の行番号の上で右クリックで1行挿入します。
挿入した行が6行目となり、6行目に60と入力します。
すると、入力＋Enterのタイミングで、下の行へ移動した関数の入ったセルA7の値は210となり関数の中を見てみるとsum(A1:A6)と自動更新されています。

(2)自動更新されない場合
A1に10、A5に50と入力します。
A6にsum(A1:A5)と入力すると、合計値60が表示されます。
関数を入力した6行目の行番号の上で右クリックで1行挿入します。
挿入した行が6行目となり、6行目に60と入力します。
すると、入力＋Enterのタイミングで、下の行へ移動した関数の入ったセルA7の値は60のままで関数の中もsum(A1:A5)と自動更新されてはいません。

こうなってしまうので、A1～A5の値によって自動更新するしないがあるのかな、、と思ってしまうわけです。すみませんが、お手数おかけしますが、よろしくお願い致します。