【Excel】週次／月次／半期／全期間　関数での自動算出方法

関数設定例

集計日を基準に、週次／月次／半期／全期間の年月日範囲を自動算出させる手順。
『集計をする際、表内に半期／全期間の記載が必要』『自動化したものの、集計期間を全て手作業で行っている』などの場合に利用可能。
※ピボットテーブルの算出で問題ない場合は、タイムライン機能を使用する方が良い

週次期間の算出方法

▼週初め（B3セル）

      =B1-WEEKDAY(B1,3)
    

〇解説

B1は集計日のセルを指定。
WEEKDAYは、指定した日付に対し、曜日を数字で算出する関数。
集計日のB1から曜日の数値分引くことにより、週初（月曜日）の年月日を取得する。
また、WEEKDAY関数内にある「3」は0 ～ 6 を月曜日から日曜日とする整数が返ってくるように指定。

▼週末（D3セル）

      =B3+6
    

〇解説

B3は週初めのセルを指定。B3に対し、+6することで、週末の年月日を取得する。

月次範囲の算出方法

▼月初（B4セル）

      =EOMONTH(B1,-1)+1
    

〇解説

B1は集計日のセルを指定。
EOMONTHは、その月の最終日を求める関数。
B1の集計日から、-1することにより、前月の最終日を求める。
前月最終日を取得後、+1することにより、今月の月初を取得する。

▼月末（D4セル）

      =EOMONTH(B1,0)
    

〇解説

B1は集計日のセルを指定。
EOMONTHは、その月の最終日を求める関数。
B1の集計日の月末を求めるため、0を指定することで今月の月末を取得する。

半期範囲の算出方法

▼上期／下期の初日（B5セル）

      =IF(3>=MONTH(B1),DATE(YEAR(B1)-1,10,1),IF(AND(4<=MONTH(B1),MONTH(B1)<10),DATE(YEAR(B1),4,1),DATE(YEAR(B1),10,1)))
    

〇解説

B1は集計日のセルを指定。
IFは、指定した条件によって別々の処理をさせる関数。
DATEは、年月日を指定することでシリアル値を求める関数。
YEARは、指定した値に対し、年に当たる数値を求める関数。
MONTHは、指定した値に対し、月に当たる数値を求める関数。

＜集計日の月が1～3の場合【3>=MONTH(B1)】＞

      DATE(YEAR(B1)-1,10,1)
    

集計日が1～3月の場合、前年の10月1日を取得する必要がある。
そのため、YEAR関数で取得した年に対して、-1引くことにより前年の値を求め、月に10、日に1を入れる。

＜集計日の月が4～9の場合【4<=MONTH(B1),MONTH(B1)<10】＞

      DATE(YEAR(B1),4,1)
    

集計日が4月～9月の場合、今年の4月1日を取得する必要があるため、YEAR関数で今年の値を求め、月に4、日に1を入れる。

＜集計日の月が10～12の場合【上記のIF関数でどの条件にも値しないとき】＞

      DATE(YEAR(B1),10,1)
    

集計日が10月～12月の場合、今年の10月1日を取得する必要があるため、YEAR関数で今年の値を求め、月に10、日に1を入れる。

▼上期／下期の末日（D5セル）

      =EOMONTH(EDATE(B5,5),0)
    

〇解説

B5は上期／下期の初日のセルを指定。
EOMONTHは、その月の最終日を求める関数。
EDATEは、指定した日付に対し、数か月後の値を求める関数。
EOMONTH 関数によりB5の上期／下期の月末を取得する際、EDATEで5ヶ月後を計算する関数を挟むことで、上期／下期の末日を求める。

全期間範囲の算出方法

▼期初（B6セル）

      =IF(3>=MONTH(B1),DATE(YEAR(B1)-1,4,1),DATE(YEAR(B1),4,1))
    

〇解説

B1は集計日のセルを指定。
IFは、指定した条件によって別々の処理をさせる関数。
DATEは、年月日を指定することでシリアル値を求める関数。
YEARは、指定した値に対し、年に当たる数値を求める関数。
MONTHは、指定した値に対し、月に当たる数値を求める関数。

＜集計日の月が1～3の場合【3>=MONTH(B1)】＞

      DATE(YEAR(B1)-1,4,1)
    

集計日が1～3月の場合、前年の4月1日を取得する必要がある。
そのため、YEAR関数で取得した年に対して、-1することにより前年の値を求め、月に4、日に1を入れる。

＜集計日の月が4～12の場合【上記のIF関数で条件に値しないとき】＞

      DATE(YEAR(B1),4,1)
    

集計日が4～12月の場合、今年の4月1日を取得する必要があるため、YEAR関数で今年の値を求め、月に4、日に1を入れる。

▼期末（D6セル）

      =EOMONTH(EDATE(B6,11),0)
    

〇解説

B6は期初のセルを指定。
EOMONTHは、その月の最終日を求める関数。
EDATEは、指定した日付に対し、数か月後の値を求める関数。
EOMONTH 関数によりB6から期末を取得する際、EDATEで11ヶ月後を計算する関数を挟むことで、期末を求める。

最後に

上記で各期間を自動算出させることにより、条件付き書式に利用したり日付範囲内のデータを取得するための関数に利用したりするなど、更に自動化の手段として組み込むことが可能。
Excelを使い始めで関数の仕様が把握しきれない場合は、セル指定部分だけ調整し、取りあえず使用してみることをおすすめ。