Excel。年度別で4～9月を上半期それ以外は下半期として楽に合計値を算出したい
＜IF+MONTH関数・SUMIF関数＞
日付関係の計算というのは、簡単に算出出来るイメージはするのですが、実際につくってみると厄介だったりします。
次のデータは、ある商品の販売金額を降順にしたものです。
今回は、4-9月を上半期、10-3月を下半期とします。
A1:C11までのデータから年度別上半期合計をF1に下半期合計をF2に算出しております。
今回は、単年の表なので、2020/10/1以前という条件で合算させてしまえば、上半期合計を算出することができますが、複数年のデータの場合、そう単純な数式では対応できません。
算出するにあたり、基本的な考え方としては、一度上半期か下半期かを判断させる必要があります。その後上半期と下半期ごとに合計値を算出していきます。
上半期と下半期を判断させる方法としては、
D2に次のような数式を作ることが多いかと思います。
=IF(AND(MONTH(B2)>=4,MONTH(B2)<=9),"上半期","下半期")
AND関数をつかった数式ですね。
AND関数をつかうと、期間のように、「ここ～ここまで」という条件をつくることができます。
この方法でも上半期と下半期を判断することができますが、AND関数が苦手な人向けというか、Excelならではの数式というものあります。
D2に次のような数式を作成します。
=IF((MONTH(B2)>=4)*(MONTH(B2)<=9),"上半期","下半期")
この数式でも、上半期と下半期を先程のAND関数同様に算出してくれます。
確かにAND関数は使っていませんが…
(MONTH(B2)>=4)*(MONTH(B2)<=9) は何をしているのでしょうか？
Excelのスキルアップの一環だと思って確認していきましょう。
それぞれをバラして説明してきます。
H2には、(MONTH(B2)>=4)
I2には、(MONTH(B2)<=9)
という数式を設定しております。
条件を満たせば「TRUE」。
満たさないと「FALSE」を返します。
このTRUEとFALSEだとなんだかわかりませんので、H列I列の結果に「×1」してみます。
ご覧のように、TRUEだと「1」。FALSEだと「0」と算出されましたね。
そう、Excelでは、TRUEを1。FALSEを0としているアレです。
これを「×（乗算）」して「1」ならばTRUEとなり条件は成立する、つまり「上半期」と判断できるわけです。
あとは、上半期と下半期ごとに合計値を算出したいので、SUMIF関数を使えば、それぞれを算出することができます。
F1の数式は、
=SUMIF($D$2:$D$11,"上半期",$C$2:$C$11)
F2の数式は、
=SUMIF($D$2:$D$11,"下半期",$C$2:$C$11)
これで4-9月を上半期、10-3月を下半期として、それぞれの合計値を算出することができました。
ところで、算出はできたのですが、D列につくった判断するための途中計算が見えていて資料としては、カッコ悪いですね。
最後に、D列の途中計算結果を表示形式で非表示にしましょう。
D2:D11を範囲選択して、セルの書式設定ダイアログボックスを表示します。
表示形式のユーザー定義で、「;;;」（セミコロン×3）と設定してOKボタンをクリックします。
これで完成しましたね。
日付関係や期間計算は、なかなか数式一発で算出とはいかないケースが多いようですが、自分にあった方法を見つけられるといいですね。
