Excel。手早く指定行ごとの累計を算出するには、どうすればいいの。
＜SUM+OFFSET+INT+ROW関数＞
やりたいことはイメージできても、実際にそれを表現するのが難しいことがあります。
例えば、4行1組となっている表で、その4行1組ごとに累計を算出したい場合も、そのようなケースだと思います。
表のD列のように算出したいわけです。
最初に、D2の数式をご紹介します。
=SUM(OFFSET($C$2,INT((ROW()-2)/4)*4,0):C2)
この数式を、オートフィルでコピーするだけで、4行ごとに累計を算出することができます。
ただ、この数式だけでは、いったい何がどうなっているのか、わかりにくいので、説明していきます。
抑えておきたいのは、累計を算出する数式です。
D列に売上高の累計を単純に算出するならば、
=SUM($C$2:C2)
という始点を絶対参照にして、終点を相対参照のままにすれば、累計を算出することができました。
終点の相対参照は、オートフィルで自動的に参照がかわりますので、考えるのは、始点ということになります。
始点を4行ごとにするには、どうしたらいいのかを、考えてみましょう。
ポイントは4行おきに始点をずらしたいわけです。
「ずらす」というキーワードがでたら、OFFSET関数の登場だと思っていいでしょう。
今度は、OFFSET関数の引数を確認します。
OFFSET(参照,行数,列数,[高さ],[幅])
行数を4行おきに、「+1」すれば、うまくいきそうです。
列数は、ずらさないので、「0」
「高さ」と「幅」は、今回省略します。
あとは、どうやって「行数」をつくっていくかを考えます。
ここで、思い出したいのは、オートフィルをするということ。
オートフィルでずれた時に、変わるものといったら、行数です。
行数を算出するのは、ROW関数です。
行方向に何かするときには、ROW関数にお世話になります。
=SUM(OFFSET($C$2,INT((ROW()-2)/4)*4,0):C2)
この数式の、「INT((ROW()-2)/4)」を抽出したE列をつくってみました。
E2は、ROW関数で算出される行番号は「2」。
この値をデータの始点である2行目の「2」で減算します。
算出した値「0」を4行1組の組数の「4」で除算した値を、INT関数をつかって、整数化します。
最後に、「INT((ROW()-2)/4)」で算出された値に、「×4」する。
この4倍は、4組の「4」です。
そうすれば、OFFSET関数の行数として、つかうことができます。
色々な関数が登場しましたが、その中でも、OFFSET関数をつかうことで、今まで、手早く算出することができなかった帳票類も改善することができるかもしれませんね。
