excel 行を追加した場合のSUM関数の変化について

質問

excel2000を使用しています。

とあるセル、例えばC1～C5には、そのセルと同列のA、Bの和が入っているとします（例、=A1+B1）。
C6には、さらにC1からC5の合計値が入っていたとします。(=SUM(C1:C5)
そこで質問ですが、
今回新たにC5とC6の間に行を一つ追加しC6がC7へシフト、=A6+B6とう計算式が入っているC6が新たに出来たとします。
その場合、C7の計算式が=SUMC1:C6と書き換わってくれません。
現状いちいち手で修正している状況です。
勝手に書き換わってくれる方法、もしくは何か良い方法はありませんでしょうか？
よろしくお願いします。

回答No.1

Nouble · ベストアンサー

Ａ１からＣ５を演算対象として使っている時に
Ｃ５の下に行挿入するのですよね？

確かにこれでは範囲は拡張されないと思います

総計を取る場所を変えてよければ
対処法があります

Ｃ７で総計を取らず
Ｃ８より下で総計を取り
SUM関数の対象に予め、空のセルを含めておきます

まあ例えば
C1:C5が総計対象であるのに対し
=Sum(C1:C6)
といった具合に
です

この状態でＣ５の下に行挿入やセル挿入をすると
対象範囲が自動で拡張されていきます

ですがこれもそれと解ってないと無意味
Sumの対象範囲だからと
挿入せずにデータを入れてしまうと
元も子もありません

ですのでお勧めは
レポートシートを別途設けて
例えは会計ならそこに集計やら買い掛や売り掛け

違う用途だとしても
そのシートさえ見れば概要から何からが
ワンストップで解るようにすれば
データを一々目に触れさせなくて済み
欲しい情報に素早くアクセスできるようになるので
かなり見易くなると思うのですが

こうすると副産物として
=Sum(C1:C5000)
とか
=Sum(C:C)
と、いった指定ができるようになります

まあ２個目のは２０００ではできるか怪しいのですが

こうしておけば
そうそう度々には
修正する必要に刈られることはなくなると思いますので
結構こっちの方がお勧めです

まあ先程の
「シートを分ける」
までしないとしても
上の方に集計があると解っていれば
探そうと思うこと自体がなくなりますし

上にあれば表示域の固定でしたっけ？
あれで常に表示させておくこともできるようになりますから
楽になると思いますよ

〔※　但し注意が必要です
Sum関数を始めとする幾つかの関数には
データレンジに所謂指向性があり
演算対象として範囲指定しても
レンジ外の部分のみ演算対象に含めない
と言う癖があります

ですので別シートに置く場合でもＣ列の集計結果は
Ｃ列に
行の集計は別シートにおいても同じ行に置く必要があるはずです

で、
sumprduct関数はそれを気にせずに済みます
本来の用途からは外れるようですが
特に配列数式では指向レンジを気にしてられないこともあり
多用されています

お礼

ご回答ありがとうございます！
細かいポイントまで、丁寧にご説明頂き色々参考になりました！

後半の注意書きの部分が少々分からなかったのですが…
（なにぶんexcelは初心者レベルなもので…）

なんとか質問内容においても、「空のセルを入れておく」方法で解決出来そうです。レポートシートの部分もあまり理解が出来なかったのですが…、今後の参考にさせて頂きます！何かデータ量が膨大になったりした場合、そちらの方がやりやすそう、というかデータが見づらくなったりして、そうせざるを得なくなりそうですが…
今やっているものはそこまでデータ量が多くはないので、空のセルを入れる方法でなんとかなりそうです！
詳しい＆細かいご回答大変ありがとうございました！

ちなみにsum(C:C)という表記はexcel2000では出来ませんでした…
一列合計出来てしまうみたいな、そんな優れた表記なんですかねぇ…（すばらしい）

回答No.6

koko88okok

１行目に行を挿入して、C1に　=SUM(C2:C200)　などと十分余裕を持った数式を入力し、本来の合計欄には、「図のリンク貼り付け」を使って、C1セルのデータを貼り付ける方法は如何でしょう？

１行目の合計欄が邪魔なら、行を非表示にします。

「コピーした内容を図として貼り付ける」
http://www.moug.net/tech/exopr/0010041.htm

「Excelのシートやセルを利用しやすいように表示する」
http://www.atmarkit.co.jp/fwin2k/win2ktips/377exlayout/exlayout.html

お礼

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

今回の問題には、SUM(C2:C200)の様に余裕を持たせることが出来きなく、一行一行追加しないとならない状況だった為、今回質問をさせて頂きました。
そちらの余裕を持たすことが出来ない状況を解決をし、ご回答者様の方法を採るのもまた一つあったのですが、NO1様のご解答により、一行づつ追加し→数式が変更されそうなので、今回はそちらやってみようかなと思います。
ご回答大変参考になりました、ありがとうございます！

回答No.5

onlyrom

そのような場合はエクセルの数式拡張のオプションを設定しておきます。

（１）「ツール」メニュー　＞　「オプション」
（２）表示されるオプションダイアログの「編集タブ」をクリック
（３）編集画面の「設定」項目の
　　　「リスト形式および数式を拡張する」にチェックを入れる
（４）ダイアログを閉じる

これで質問の件はできるようになります。
但し追加した行の前の５行のうち少なくとも３行には
式が入っていなければいけませんが今回の場合は問題ないですね。

また、式が書き換えられるタイミングは
行が追加された時点ではなく（この時点では空白行のままです）
式の対象となるセル（今回は、Ａ６，Ｂ６）に値が入力された時です。

要するに、Ａ６，Ｂ６に値が入力された時点で
Ｃ６　に　＝Ａ６＋Ｂ６　の式が自動セットされ
Ｃ７　が　＝ＳＵＭ（Ｃ１：Ｃ６）に自動で書き換わるということです

で、上記のオプションが設定されてるにも拘わらず式が拡張されてないなら、
それは追加した行の該当セル（Ａ６，Ｂ６）がまだ未入力の状態だと考えられます。

以上。

お礼

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

ご回答内容のダイアログ、チェックボックスを確認しましたが、既にチェックがされている状態でした。

今一度確認の為、行を追加後、しっかりとA6,B6に価する場所に数値を入れましたが書き換わりませんでした。

何か他に設定項目等あるのでしょうか…

回答No.4

Wendy02

こんにちは。

上位バージョンですと、リストという方法がありますが、Excel2000 ですと、それがありません。SUM関数は、トップの場所と、SUM関数の手前の行までの計算ですから、このようにすればよいです。

R1C3　絶対参照　3列目の１行目
R[-1]C　相対参照　現在の列１行手前

=SUM(INDIRECT("R1C3:R[-1]C",FALSE))

回答者の一部には、上記の数式に、変な文句をつける人もいますから、

=SUM(OFFSET($C$1,,,ROW()-1))
こういうのもありですね。ただし、これは、1行目からということですから、それが違うなら、ROW()の現在行から、行の差を引いてくれればよいです。

お礼

ご回答ありがとうございます！
仕組みはNO2様の仕組みに非常に似ている様な気がします。

INDIRECT関数、R1C1形式など非常に勉強になります…（勉強をする場ではないのですが…）
今まで知らなかった知識ばかりで、様々のご回答者様のご回答を拝見する度に勉強させられる次第です…

今回の解決方法としてはNO2様のお礼の欄に書かせて頂いたのですが、単純なデータ表の為、今回は一番分かり易い（自分にとって）NO1様の方法で行こうかなと思っています。（現状は）

今後是非ご回答者様のご回答も参考にさせて頂こうと思っています。
ご回答ありがとうございました！

回答No.3

mu2011

一例です。
５行目をコピー→その位置で右クリック→コピーしたセル行を挿入を選択で数式が拡張されると思いますが如何でしょうか。

お礼

ご返答遅くなりました。
おそらくその場合だと拡張される事はされるのですが、今回一番下段に空白のセルを挿入したいと思っていましたので…
質問内容を詳しく記載せずにすみません。

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

回答No.2

higgledypiggledy

色々と試したところいくつかの問題点がありますが、それを解決するためにワークシートに入力のルールを設定すれば何とかなるかと思います。

【考え方はこうです。】
・Ｃ１は固定なので要はＣ列の最終データがどこにあるか検索できればよい
・空白以外のセルを数えるcounta関数があるのでこれを使う（注意１）
・しかしＣ列においてそれを数えると循環参照となってエラーが発生
・それではＣ列以外（Ａ列かＢ列）で行えばよい（注意２）
　もともとＣ列は連続の数式なのでそうではないデータを数えるほうが結果として都合がよい

＊注意項目
（注意１）　空白は無効として数えないので空白のデータを作らずに、"0" や "-" を必ず入力する
（注意２）　全行 "C1=A1+B1" のように対応させるセルを揃える

上記の注意が実行可能なら
=SUM(OFFSET(C1,0,0,COUNTA(A:A)))
で大丈夫だと思います。

蛇足ですがエクセルの新しいバージョンでは自動で修正がきくみたいな感じです。

お礼

ご回答ありがとうございます！
NO.1の方から順にご回答を拝見させて頂いておりますが、この問題もNO.1様の回答で無事解決出来そうだなと考えていましたが、
ご回答者様の回答を拝見し、excelって色々なやり方があるんだな～としみじみ実感致しました。
OFFSET関数初めて知りました…これからかなりいい仕事しそうな関数です…

現在やっている事は、ただ単純に行の挿入→データの集計のみなので、現状はNO.1様のご回答で何とかなるかな？と思っている状況です。
また合計対象範囲を臨機応変に変えたり、精密なデータ表を作る場合、このやり方が重宝するかと思いますので、その際参考にさせて頂きます！
ご回答ありがとうございました！