環境
- Excel 2010
売上予測のようなものを集計するのに、シートごとの共通のセルを合計する必要が出てきた。それも、場合によっては、10月～1月の合計を式だけで計算したいというような要望が出た。
ここで、Excelのセルに配列があることを知り、それを利用することで柔軟に開始月から終了月までの合計が出せるような式を考えた。ちなみに単純に月シートだけあるなら3D集計で事足りる。私の場合シートが連続していなかったり何かそれ用のシートで挟むというのが好みでなかった。はるかに挟み方法が楽である。
='第1Q:第2Q'!E14'
これだと、第1Qから第2Qの間にあるシートのE14セルの合計を計算してくれる。その代り間にある全てのシートの合計を計算してしまうから、いちいちシートの移動をしたりする必要は出てくる。
コード - code
$A$13 = 開始月
$A$14 = 終了月
もちろん、開始月や終了月はリストから選択できるようにしている。
=IF($A$14>3,SUMPRODUCT(SUMIF(INDIRECT("'"&ROW(INDIRECT($A$13&":"&$A$14))&"'!E14"),"<>0")),SUMPRODUCT(SUMIF(INDIRECT("'"&ROW(INDIRECT($A$13&":"&"12"))&"'!E14"),"<>0")) + SUMPRODUCT(SUMIF(INDIRECT("'"&ROW(INDIRECT("1"&":"&$A$14))&"'!E14"),"<>0")))
長い。
いつも思うのだが、Excelの式である程度複雑なことをやろうとすると可読性が落ち、理解が困難になる。ちょっとずつ分解して考えてみる。
会社の年間という概念が、4月始まりなのが厄介だ。このせいで、上記のような10月～1月といった処理を行わなければいけなくなる。これは簡単で、10～12月、1月～3月と分けて足し算してあげればいい。仕方なくIF文で条件判断をしている。
ROW(INDIRECT($A$13&":"&$A$14))
数式配列を作成する。
ROW(10:1)
というのは1～10の配列になってしまう。都合が悪い。
INDIRECT("'"&ROW(INDIRECT($A$13&":"&$A$14))&"'!E14")
何故だか"'"でくくってやる必要がある。このページ最後のMicrosoftのリンクの後ろの方に書いてある。
そして、配列を処理できる関数のSUMPRODUCTを使うが、その前にSUMIFをかます。正直こうやらないと動かないのでよくいまだに理解しきれていない。
SUMPRODUCT(SUMIF(～,"<>0"))
その他
もっといいやり方があれば是非教えてほしいです。
特に、配列数式で
{10,11,12} + {1} = {10,11,12,1}
とできたらもっとスマートに記述できるのに、、、