Excel (一般機能)

Excelの一般機能に関するフォーラムです。
  • 解決済みのトピックにはコメントできません。
このトピックは解決済みです。
質問

 
(Windows XP全般 : Excel 2003)
セル挿入による数式の変更をさせない方法
投稿日時: 18/06/22 19:11:36
投稿者: 富山の初心者

お願いします。
 

シートが下記状態であったとします。

    A   B 
1  10  10  B1の数式:=SUM($A$1:A1)
2  20  30  B2の数式;=SUM($A$1:A2)
3  40  70  B3の数式:=SUM($A$1:A3)
4  80 150  B4の数式:=SUM($A$1:A4)
5 160 310  B5の数式:=SUM($A$1:A5)

この状態でA2に挿入(下方向へシフト)
すると下記状態(B2:B5が変わる)となりますが、

1  10  10 B1の数式:=SUM($A$1:A1)
2      30 B2の数式;=SUM($A$1:A3)
3  20  70 Bの数式3:=SUM($A$1:A4)
4  40 150 Bの数式4:=SUM($A$1:A5)
5  80 310 Bの数式5:=SUM($A$1:A6)
6 160

下記のようにする、
すなわち、
「挿入で影響したセルを参照している数式を変えない」
方法を教えていただけませんか

1  10  10 B1の数式:=SUM($A$1:A1)
2      30 B2の数式;=SUM($A$1:A2)
3  20  70 B3の数式:=SUM($A$1:A3)
4  40 150 B4の数式:=SUM($A$1:A4)
5  80 310 B5の数式:=SUM($A$1:A5)
6 160	
    

回答
投稿日時: 18/06/22 20:00:14
投稿者: 半平太

1  10  10 B1の数式:=SUM($A$1:A1)
2      30 B2の数式;=SUM($A$1:A2) ← (1)この数式なら、30じゃなくて10じゃないですか?
3  20  70 B3の数式:=SUM($A$1:A3)

(2) A1セルに新セルが挿入された場合は、どうなればいいんですか?
    B2セルについてだけ教えて頂ければ結構です。

回答
投稿日時: 18/06/22 20:03:34
投稿者: んなっと

いろいろありますよ。
 
=SUM(A$1:INDEX(A:A,ROW()))
とか
=SUM(INDEX(A:A,1):INDEX(A:A,ROW()))

投稿日時: 18/06/22 23:42:15
投稿者: 富山の初心者

半平太様、ご指摘の通り、要望結果を間違えていました。
 

1  10  10 B1の数式:=SUM($A$1:A1)
2      10 B2の数式;=SUM($A$1:A2)
3  20  30 B3の数式:=SUM($A$1:A3)
4  40  70 B4の数式:=SUM($A$1:A4)
5  80 150 B5の数式:=SUM($A$1:A5)
6 160
       ↑B列の結果をまちがえていました

A1セルに新セルが挿入された場合は,
以下のようにしたい。
1     0 B1の数式:=SUM($A$1:A1)
2  10  10 B2の数式;=SUM($A$1:A2)
3  20  30 B3の数式:=SUM($A$1:A3)
4  40  70 B4の数式:=SUM($A$1:A4)
5  80 150 B5の数式:=SUM($A$1:A5)
6 160

再度、記述しますが、
「挿入で影響したセルを参照している数式を変えたくない」のです。

 
んなっと様、ありがとうございます。
「いろいろありますよ」の確認をしておりませんが、
「元々の式を変えたくない」というのが、本題であります。
なので、「=SUM($A$1:A1)」はあくまで例として、記述しました。
 
再度、よろしくおねがいします。

回答
投稿日時: 18/06/22 23:57:30
投稿者: 半平太

>「挿入で影響したセルを参照している数式を変えたくない」のです。
 
何の工夫もない数式全てについて、そう希望するのでしたら無理です。仕様なので。

回答
投稿日時: 18/06/23 06:13:01
投稿者: んなっと

セル参照部分をINDIRECT(" ")で囲む
=SUM(INDIRECT("A1:A2"))
に変更するのも希望する形とは言えないようですね。
 
「挿入」や「切り取り→貼り付け」だと無理なので、
少し面倒ですが「コピーしてから空白セルを作る」のはどうでしょうか。
 
●A2:A5を一つ下の範囲にコピー
 
   A  B
1  10  10
2  20  30
3  20  50
4  40  90
5  80 170
6 160   
 
●A2を空白セルに
 
   A  B
1  10  10
2     30
3  20  50
4  40  90
5  80 170
6 160   

投稿日時: 18/06/25 12:56:52
投稿者: 富山の初心者

半平太さん、ありがとうございます。
 
「仕様なので。」・・・計算式を変えないオプションなどがないものか。
と思いましたが、やはりだめですか。
 
んなっと、ありがとうございます。
現在、私も御指南の方法で対処しておりますが、
回数が多くなると、たいへんなもので、質問にいたりました。
 
皆さま、ありがとうございました。
 
解決とさせていただきます。