セルアドレスを$で固定指定しても、行や列の削除などを行うと更新されてしまう

こんにちは。

> 固定指定してあるのに、なぜ更新されるのでしょうか？

それは戸島さんの理解が間違っています。

$F$5 という式での $ の役割は、

 "F" や "5" という 文字表記を固定 するものではありません。

式を記入した時のF5 セルを参照****する ことを固定するものです。

式が記入されているセルがコピー/移動されても、

F5 セルよりも内側（シートのトップセルに近い側)で挿入/削除をしても、

『式を記入した際の F5 セル を参照し続けなさい』 というものです。

４行目を削除すれば、削除前に 『 C1000 』 であったセルは 『C999 』になります。

その結果、式が、書かれているように変更されるのは 、

エクセルの正しい動作であり、絶対参照という機能の正しい結果です。

同様に、３行目を削除すれば、その３行目の中にある『C3 』セルも消えます。

削除後に繰り上がって C3 セルになったセルは

式記入時の C3 セルではありません。

『式記入時の C3 セルを参照し続けなさい』と言う指定をしておいて、

その参照先を消し去ったのですから、エクセルから

【それはエラーです】 といわれるのは正しい動作であり、期待通りの結果です。

>やりたいことは、行の挿入や削除をしても、参照範囲（セルそのものではなく、そのときのC3からC1000までの範囲）を変えたくないので、なにかよい方法ないでしょうか？

このような場合にこそ、セル範囲を文字列で指定できるINDIRECT関数を使うことになります。

=SUM(INDIRECT("C3:C"&LOOKUP(1,0/(INDIRECT("C1:C1000")<>""),ROW(INDIRECT("1:1000")))))

なお上記の関数ならC3セルを削除しても新たなC3セルからの集計ができますのでエラーが表示されません。

＞あとできれば1000ではなく最終行までとしたいのですが。（個人で使うので1000行で十分ですが。）

一般に、検索系の関数や配列数式では数式範囲を必要以上に大きくすると（たとえば列全体を指定する）、再計算に時間がかかりシートの動きが重くなるので、お勧めできません。

今回の場合そもそも INDIRECTを使う必要自体がないように思います。

 =SUM(C:C)-SUM(C$1:C$2)

でいいはずです。この式を C2に入れてますという場合でも D2に「★」などを入れて

 =SUMIF(D:D,"<>★",C:C)

とすれば済みます。

基本的に配列計算と揮発性の関数を組み合わせるのは避けた方がいいと思います。

＞結局数式はこうなりました。

=IF(ISERROR(LOOKUP(1,0/(INDIRECT("$C$3:$C1000")<>""),ROW(INDIRECT("$3:$1000")))),,SUM(INDIRECT("$C$3"):INDIRECT("C"&LOOKUP(1,0/(INDIRECT("$C$3:$C$1000")<>""),ROW(INDIRECT("$3:$1000"))))))

まずINDIRECT関数のセル参照は文字列なので絶対参照にする必要はありません。

またIF関数とISERROR関数を使っていますが、エラーが出ることはないのではないでしょうか？

最終的な数式は以下のような関数でよいと思います。

=SUM(INDIRECT("C3:C"&LOOKUP(1,0/(INDIRECT("C3:C1000")<>""),ROW(INDIRECT("3:1000")))))

＞これひとつきを上旬、中旬、下旬にシートを分けて、同じ数式を３シートに記述しています。

同じレイアウトのシートなら、シートを作業グループにして、1つのシートに入力すればすべてのシートに同じ数式を入力することができます。

具体的には、Ctrlキーを押しながら上旬、中旬、下旬シートのシート名をクリックして選択し（これで作業グループになります）、どれか1つのシートの数式入力セルに数式を入力してEnterすればすべてのシートの同じセル番地に同じ数式が入力できます。

最後に別のシートを選択して（シート名を右クリックして「作業グループ解除」）、作業グループを解除してください。。

Tsunodaさん、ありがとうございます。

理解できました。

セルアドレスの文字列で参照しているのではなく、セルそのものを参照しているということですね。

やりたいことは、行の挿入や削除をしても、参照範囲（セルそのものではなく、そのときのC3からC1000までの範囲）を変えたくないので、なにかよい方法ないでしょうか？

あとできれば1000ではなく最終行までとしたいのですが。（個人で使うので1000行で十分ですが。）

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