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

問題：

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

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

そんなとき、また入力し直すか？
面倒だな。
やはり、すでに入力されているセルを正しい位置に切り取り移動(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

こうすれば、

と、行の色も問題ない。

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

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

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

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

範囲内の文字列を結合するには？

問題？

範囲内の文字列を結合と言っているのは、

みたいな表があって、

　　地名リスト：福岡県&熊本県&熊本市&佐賀県&佐賀市&長崎県&大分市

とセル内の文字列を結合して表示するような場合のことだ。

文字列の結合には、ご存知のように、&演算子、CONCATENATE関数が使える。
しかし、上のようにセパレータで区切ったり、数が多いと結構大変だ。

数値の場合、SUM関数のように範囲指定して演算をするような関数は用意されているので、範囲指定して、SUM関数等を使えばいいだけ。
しかし、文字列の場合、こんな風に範囲指定できる関数は用意されていないようだ。

文字列の場合、１個づつ&演算子やCONCATENATE関数で結合するしかないのか？
まあこれでも目的を果たせるならいいが、結合する範囲が変わったり、途中に行や列が挿入されたら？
という訳で、今回は範囲内の文字列の結合。

考え方：

文字列の結合には結局のところ&かCONCATENATEを使う必要がある。
途中にセルが挿入されても、数式のコピペで対応できるようにする。

ということで、採用したのが単純な方法。
隣のセルと結合した結果をそのまた隣と結合していくチェーン方式。

実際の数式：

計算用セルを横に追加する。（通常は非表示）

D4が最終結果となる。

D4の式：

=IF(OR(B4="",OFFSET(C4,-1,0,1,1)=""),"",$E$3)  &IF(OFFSET(C4,1,0,1,1)="",T(B4),B4&OFFSET(D4,1,0,1,1))

C4の式（結合対象範囲を判断するために使用）：
=ROW(C4)

D4式の解説
　基本処理：
　　(セパレータ$E$3)と(現在行のB列)と(下の行のD列)を結合。
　　文字列結合には&演算子を使用（CONCATENATE関数でもＯＫ）。

　前方にセパレータを追加しない条件：
　　B列が空欄または、一番上の行。

　最下行の処理：
　　下の行と結合しない。

*)上下のセルをC3やC5と直接指定せずに、OFFSET関数を使っているのは、途中に行を追加した場合に正しく直下や直上のセルを指定できるようにするため。
*)T関数は最下行のB列が空欄だった場合に0ではなく空欄("")とするために追加。
*)C列の上下は空欄にする。

これで完成。途中に行を挿入しても行コピーでＯＫだ。

さらに応用として、IFを追加して、条件にあう場合だけ結合というのもできるだろう。

今回のねた、範囲内の文字列を結合するなんていう場合は稀だとは思うが、たまたま別件で必要になったので考えてみた。

以上、終了。

数式分解ツール

複雑に入れ子になった数式を分解表示するツールを数式で作成したので公開してみる。
オレンジの箇所に数式を入力すると下に分解表示されます。
作成途中から、コレを使いながらコレを作りました。
使えるかも？