エクセル 列の削除・挿入と関数、計算式の範囲について

質問

A　　B　　C　　D　　E
1　 2 2 3 4 2
2 1 1 2 3 5
3 2 5 6 7 2
4 2 2 3 4 4
5 2 1 5 8 6
6 9 11 19 26 19 ←各列合計

sheet1に上記のような表があったとします。
6行目以外は誰でも入力可能です。しかし6行目の各列合計の行は計算式が入っているため、改変できないようにセルを保護します。
本来ならそれで解決なんですが…
列の削除や挿入をしなければならないんです。
セルの保護をしている箇所があると、当然削除は出来ません。
これはどうしようもないものでしょうか?

もし上記が無理なら、sheet2に合計のみの表を作って計算式を入れたいのですが、今度は削除や挿入をするため、参照先がおかしくなりエラーが出ます。

何か良い解決方法があれば教えてください。

回答No.5

mshr1962 · ベストアンサー

#2です。
＞もしよろしければマクロで行う手順を教えていただけないでしょうか？

Sub GOUKEIRESET()
'合計式を再セットするマクロ
Dim AN As Integer
Range("A1").Select
Selection.End(xlDown).Select
AN = ActiveCell.Row
Range(Selection, Selection.End(xlToRight)).Select
Selection.FormulaR1C1 = "=SUM(R[" & (2 - AN) & "]C:R[-1]C)"
End Sub

お礼

ありがとうございます。
試してみます。

回答No.4

zap35

＃０３です。実際に試してみました

質問のデータが入力されて、６行目に保護をかけた状態で行を挿入、削除してもSUM関数のセル参照範囲は自動的に変更され正しい結果が得られます。

＃０１さんの補足でもふれていますが、シートの保護をするときに、「行の挿入」「行の削除」を可能とするよう設定しても#REF!になりますか？

お礼

細かくお答えいただきありがとうございました。
多分、私がしたいのは行ではなく列だったのでうまくいかなかったのだと思います。

補足

なぜでしょう…私のファイルではエラーが出ます。
自動的に修正はされないみたいです。

回答No.3

zap35

求めるものが良く理解できません

６行目に数式が入っているとして、３行目が削除された場合は、６行目にあった数式はどのセル範囲を参照するようになるのが希望ですか

同様に３行目と４行目の間に行を挿入した場合はどうなれば良いのですか？

もしかしたら式は６行目に固定して１行目：５行目を合計するのですか？

その条件が明示されなければマクロも組めません。

補足

sheet2に合計だけを表示する方法をとりたいのです。
普通なら、
=SUM(sheet1!A1:A5）
と入力すれば事足りますが、列の削除をすると、
='1'!#REF!
とエラーが出ます。
これをどうにかしたいのです。

よろしくお願い致します。

回答No.2

mshr1962

マクロを使うなら、最終行に数式を再設定するとか可能です。
使わないならSheet2で
=SUM(Sheet1!A:A)
のように列全体を集計するとかすればいいのでは？

補足

列の削除、挿入をすると参照先が変わってエラーになります。

もしよろしければマクロで行う手順を教えていただけないでしょうか？

回答No.1

akio_myau

ＥＸＣＥＬのバージョンが書いていないのですが
この質問からするとＥＸＣＥＬ２０００あたりを使っているのでしょうか。
ＥＸＣＥＬ２００２以上のバージョンにアップグレードすれば
シートの保護の際に
列の挿入や列の削除が出来るようにするチェックボックスがあります。
アップグレードを検討してはどうでしょうか。

お礼

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

補足

すみません、バージョンはXPです。
列の削除等のチェックボックスの存在は知っていますが、6行目はロックをかけています。
最終的にシートの保護にすると、削除は出来なくなります。