１行おきに集計したい

久々にネタが出来た。

状況：

出退勤表にありがちなシチュだと思うが、こんな感じの表を考えてみる。

１日の労働時間の集計をするには単純に

(退勤時間 - 出勤時間)の合計

な分けだが、それを数式で表すにはどうすればいい？

　１．集計用の列を追加して、その列で集計する。
　こんな感じ。

　２．なんとか１個のセル内で計算する。

１の方法でも集計処理自体は問題なく実現できる。
しかし、見た目を整えるのが面倒だ。
集計用の別シートを作成して、そこで処理すれば、見た目は何とかなるだろう。
しかし、この方法ではネタにならない。

というわけで、２を採用。

考え方：

(退勤時間 - 出勤時間)の合計

この式を

退勤時間の合計 - 出勤時間の合計

と展開する。

つまり、バックが白色の行の合計から、薄青の行の合計を引くことを考える。
合計を求めるならSUM関数だが、範囲指定をどうするか？

セルを１行おきに直接指定する。

まあ、人数追加（行追加）とかなければ、問題はない。
行数が多いと数式を記述するのが大変そうだが・・・

しかし、人数追加にも対応できて、記述も楽なやり方はないだろうか？

行追加に対応するためには、エクセル自身の補正機能？
（あの、行や列を追加した時に、数式の範囲を自動的に変えてくれる機能）
を使えるようにしたい。
そのためには、

　”B4:B11”

というような連続した範囲をSUM関数に指定する必要がある。

そして、その連続した範囲を２つの別々の範囲に分けて合計を求める。
ということを考えてみる。

実際の数式：

範囲を分けるやり方だが、
　配列数式で範囲の一部を０に置き換えて合計する方法
　 {=(SUM(IF(条件１,B4:B11,0))-SUM(IF(条件２,B4:B11,0)))*24}

が考えられる。（24を乗じているのは単位を時間にするため）
これには、出勤時間と退勤時間の行を判定する条件が必要になる。

さてどうするか？
これは行番号で判定できるだろう。
今回の例では、１行おきなので、行番号が偶数か奇数で範囲を分けられるので、

条件１：
　ISODD(ROW(B4:B11)-ROW(B$4))

条件２：
　ISEVEN(ROW(B4:B11)-ROW(B$4))

とすれば良いだろう。
（ここで、ROW(B$4))を引いているのは、表の上に行が追加された場合を想定しての処理。）
これでいいんだが、飛ばす行数が変わる場合も想定して、

条件１：
　MOD(ROW(B4:B11)-ROW(B$4),2)=1

条件２：
　MOD(ROW(B4:B11)-ROW(B$4),2)=0

こっちのほうがいいだろう。

結果：

これでＯＫ。

参考：

こんな変更があってもわずかな手間で修正できる。

B5:B16とB$5への変更は行を追加したときにエクセルが自動的にやってくれる。
手作業で変更したのは除数を２から３にしただけ。

以上、今回はおしまい。