エクセルで四半期を求める数式と集計する方法

エクセルで日付データから 四半期 を求めたい。

つまり、年度が4月始まりの場合、「5月10日」なら第一 四半期なので「1」を、「11月8日」なら第三 四半期なので「3」を表示させたい。
さらには 四半期ごとの集計 もしたい。

このようなことでお困りの方のために今回は『四半期を求める数式集計する方法』をピックアップします。



四半期を求める数式

下のサンプルを使用します。

いきなり B列に 四半期を表示させることは可能ですが、ここでは みなさんが理解しやすいようにステップを踏むことにします。

まず 四半期を求める準備として「会計月」を表示させます。
つまり、4月なら「1」、5月なら「2」、1月が「10」、最後の3月が「12」という数字です。
これってつまり「その日付の3か月前の月」ってこと。

セルB2にはこんな 数式が入ります;

MONTH ( EDATE (A2, -3) )

EDATE関数とは、指定した月だけ前あるいは後の日付のシリアル値を返す関数です。
上の数式の青字の部分は、セルA2の日付の3か月前(マイナス3)という意味です。

そのシリアル値から「月」だけ抽出するために、MONTH関数 を使いました。
セルA2には「1」が表示され、さらに数式をした方向にコピーすると下図のようになります。


▲1から12まで会計月が並んだ

つぎに、B列の会計月の数字をつかってC列に「四半期」を表示させます。
B列の会計月を3で割ってみます。
すると、、、

うーん、おしい!
小数点以下を 切り上げれば四半期の数字になる ことがわかります。

そこで、CEILING関数 をつかって整えます。
セルC2に入る 数式は;

CEILING ( B2/3, 1 )

となります。
セルC2の数式を編集して、さらに下の行までコピーすると下のようになります。


▲四半期が出た!

以上のことから、B列にいきなり四半期を表示させる場合、数式は以下のようになります;

CEILING ( MONTH ( EDATE (A2, -3) ) /3, 1 )

実際の画面がこちら;


▲一発で四半期を表示させる数式


四半期ごとに集計する

四半期ごとの集計には、サンプルのような表の場合は フィルター機能と SUBTOTAL関数を使います。


▲月別の売上表

まず、表の「売上」(C列)の最下行に SUBTOTAL関数 をセットします。
SUBTOTAL関数は、リストまたはデータベースの集計値を返す関数、つまりサンプルの場合は フィルターをかけた結果に対して集計してくれる便利な関数 です。

表のセルC14には以下の 数式が入ります;

SUBTOTAL ( 9, C2:C13 )

2番目の引数(赤字)の部分は、入れる数字によって集計方法が変わり、合計(SUM)の場合は「9」を設定します。


▲SUBTOTAL関数がセットされた

つぎに、フィルターを有効にします。
フィルターを有効にするには、表のいずれかのセルを選択した状態で、メニューの「ホーム」タブの中にある「並べ替えとフィルター」をクリックし、さらに「フィルター」をクリックします。

すると表のヘッダー部分に「▼」ボタンが表示されてフィルターをかけられるようになります。

試しにB列を「2」(=第2四半期)でフィルターをかけてみると、、、


▲第2四半期で集計できた!

SUBTOTAL関数は第2四半期の売上集計(合計)を表示しました。

以上で『エクセル四半期を求める数式集計する方法』はおしまい。
その他の関連記事と合わせて仕事や趣味に役立ててください。

関連記事






-関数, EXCEL&VBA
-, , , ,

© 2021 h1r0-style.net