集計対象セルの切り取り移動で集計計算がずれる

問題：

表にデータを入力する途中、気がつくと入力行がずれていた。とか、いくつかデータを飛ばして入力していた。
なんてことは、よくある事。

例えば、下の赤の網掛け部分は本当は１行下に入力しなければならなかったような場合。

そんなとき、また入力し直すか？
面倒だな。
やはり、すでに入力されているセルを正しい位置に切り取り移動(Ctrl+X,Ctrl+V)したいところだろう。

ところが、これをやると

上のように集計計算がずれてしまう。

これは、計算対象セルの移動に対応して、エクセルが数式に使われているセル範囲を追従して自動的に変更するために起こることだ。

表の作成中は、非常に便利な機能なのだが、表の完成後には、こんな風に、やっかいなことも。

コピー貼り付けではこんなことは起こらないので、そっちで対応してもいいんだが、元の空欄にしたいセルの内容を削除するという、１ステップ余計に必要だし、間違いの元になることも。

できれば切り取り移動で済ませたい。

という訳で考えてみた。

対策：

問題は計算対象範囲のセルが移動した時に数式に使われているセル範囲が自動的に追従して変わるということ。

ということで、数式に計算対象のセルを直接使わないようにすることを考える。

上の例でいうと、SUM関数の中にC8:E8というような移動する可能性のあるセルを使わないようにする。

では、どうするのかというと、移動する可能性のない、基準セルからの位置で計算範囲をしてやれば良い。

実際の数式：

具体的にはOFFSET関数を使って、次のように範囲指定する。

F7の数式：

=SUM(OFFSET($B$2,ROW($B7)-ROW($B$2),1,1,COLUMN(F$2)-COLUMN($B$2)-1))

OFFSET関数のパラメータ

　基準セル：$B$2

　行オフセット：ROW($B7)-ROW($B$2)・・・基準行との差

　列オフセット：1・・・基準セルの隣

　範囲の高さ：1

　範囲の幅：COLUMN(F$2)-COLUMN($B$2)-1・・・基準列と”計”列との間に含まれる列数

＊）基準との差で行オフセットや幅を求めているのは、行、列の挿入・削除を考慮しての処理。

この状態で切り取り移動すると、

という具合に計算結果はずれなくなった。

これはこれでいいんだが、行の色はずれたままだな。

このように書式のずれは、これだけでは対応できない。

やはり書式には条件付書式。

　条件式　=ISEVEN(ROW(B3))・・・行番号が偶数ならば

　書式：　薄い青で塗りつぶし

　適用範囲　=$B$3:$F$12

こうすれば、

と、行の色も問題ない。

上の方法だと切り取られた７行目の条件付書式もなくなるのでダメでした。
たまたま７行目が塗りつぶしなしだったので見逃してしまっていた。
書式の問題は数式だけでは無理っぽいな。

ちなみに、左右の罫線はそれぞれ計算範囲外の隣の列のセルで書式指定してあるので、条件付書式で指定しなくても問題ない。

これで、入力ミス時の修正作業が、少し楽になるかも。

以上、今回はこれで解決。