Excelのピボットテーブルなどでは、標準では1月を開始月とする年・四半期で集計されます。
しかし、企業によっては4月を開始月とした年度・四半期で売上などを集計することも多いと思います。本記事では、開始月を任意に設定し、日付から年度・四半期を表示させる方法を紹介します。作成した年度・四半期のデータはピボットテーブルの集計にも使用することができます。
日付から任意の年度・四半期を設定する方法
たとえば、以下のような注文データの日付に基づき、年度と四半期を算出します。ここでは、4月を開始月として計算します。
年度を表示させる方法
年度を表示させるには年度のセルに以下のように数式を入力します。YEAR関数を使って年度を表示させます。ポイントは「MONTH(B2)-4+1」日付の月から「開始月（4月）」を引いて1を加えることで、3か月前の日付の年を表示させるようにしています。
=YEAR(DATE(YEAR(B2),MONTH(B2)-4+1,DAY(B2)))
これで、該当の年度が表示されます。2022年4月から2022年度なので、2022/1/1は2021年度となります。
開始月を変えたい場合は、MONTH(B2)から引く数字を変えます。たとえば、あまり多くはないと思いますが、2月を開始月とする場合、「MONTH(B2)-2+1」とします。
=YEAR(DATE(YEAR(B2),MONTH(B2)-2+1,DAY(B2)))
そうすると、2021年度と表示されます。
数字だけではわかりづらいので、セルの書式設定のユーザー定義で「0″年度”」と表示形式を設定しておきます。
これで、「~年度」という表示になります。
四半期を表示させる方法
次に4月を開始月として四半期を計算します。
四半期を表示させるセルには以下のように数式を入力します。まず、MONTH(B2)でセルB2の日付から月を抽出し、「-4」で月から開始月を引きます。1月の場合は、「1-4=-3」と負の数になり四半期を正しく計算できません。そこで、「+(12*(MONTH(A2)<B1)))」で 月が開始月より前の場合は12を加え、そうでない場合は0を加えます。これにより、年度をまたぐ四半期も正しく計算できます。上記で計算された月を3で割り、1を加えて四半期を1~4の範囲にします。
=INT((MONTH(B2)-4+(12*(MONTH(B2)<4)))/3)+1
数式を確定すると整数値をシリアル値として認識し、日付の表示になる場合があります。
セルの書式設定からユーザー定義で「第○四半期」と表示されるように設定します。
これで第4四半期と表示されます。
開始月を変えたい場合は、MONTH(B2)から引く数字と日付が開始月より小さいかを判断する数字を変えます。たとえば、2月を開始月とする場合、以下のように数式を変更します。
=INT((MONTH(B2)-2+(12*(MONTH(B2)<2)))/3)+1
年度・四半期の表示
この数式を下にコピーすれば、各年度・四半期を一気に表示させることができます。以下は4月を開始月とする例です。
ピボットテーブルでの活用
上記で設定した年度・四半期はピボットテーブルの集計にも活用できます。基本的なピボットテーブルの使い方は下記記事をご覧ください。
先ほど作成した「年度」「四半期」の項目を行ボックスに追加します。集計する値には購入金額の合計とします。
これで4月を開始月とする四半期ごとのデータを集計することができます。
コメント