Excel。4月-6月を第1四半期として、四半期別集計を算出したい。
＜INT+MOD+MONTH関数,SUMIF関数＞
ピボットテーブルを使えば、”秒殺”で四半期別集計は算出することができるのですが、表が出来上がっていて、該当のセルに結果を表示したい場合など、ピボットテーブルを使用しないで、算出させたいというケースもあります。そこで、今回は、4月-6月を第1四半期として、四半期別集計を算出していきます。
次の表があります。
E列の四半期ですが、表示形式をつかって、第○四半期と表示させています。
E2:E5までは、1～４の数値のみがセルに入力されています。
さて、どのようにしたら、4月から6月を第1四半期と判断することができるのでしょうか？
SWITCH関数など4ならば1というような設定をしてもいいのですが、これだと多くの引数が必要になってしまい、わかりやすい反面、効率がわるいです。
そこで、次の手順を踏むことで4月から6月を第1四半期と判断する計算手順があります。
H列には、1月から12月までの数値が入力されています。
H2の1は1月を意味しています。
最初に行うのは、I列のマイナス4を行います。
マイナス3では？と思うかもしれませんが、マイナス3にすると、ズレが生じてしまい、上手くいきません。
I2の数式は、
=H2-4
2番目は、
1年は12カ月なので、12で除算しますが、必要なのは、除算した結果の”余り”なので、MOD関数を使います。
J2の数式は、
=MOD(I2,12)
3番目は、四半期が3カ月分で構成されているので、3で除算します。
K2の数式は、
=J2/3
4番目は、誤差を調整しますので、+1をします。
L2の数式は、
=K2+1
最後は、整数部分だけが必要なので、INT関数で整数部分を取り出します。
M2の数式は、
=INT(L2)
このような手順によって、4月から6月を第1四半期と判断することができます。
これをまとめて数式化しています。
C2をクリックして、次の数式を作ります。
=INT(MOD(MONTH(A2)-4,12)/3+1)
手順とは逆で、INT関数から作成しますので、注意しましょう。
算出出来たら、オートフィルを使って数式をコピーします。
あとは、この結果を使って、集計します。
F2をクリックして、SUMIF関数ダイアログボックスを表示します。
範囲には、$C$2:$C$20
検索条件には、E2
合計範囲には、$B$2:$B$20
F2の数式は、
=SUMIF($C$2:$C$20,E2,$B$2:$B$20)
当然、手入力で数式を作成してもOKです。
それでは、オートフィルを使って数式をコピーします。
これで、4月から6月を第1四半期として集計することができました。
簡単なようですが、これもクセがありますので、パズルのような手順を踏んで数式を作っていくといいですね。
面倒だと感じたら、ピボットテーブルを使って算出してみると、いいかもしれません。
