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

関数設定例

参考画像1

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

週次期間の算出方法

▼週初め(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を使い始めで関数の仕様が把握しきれない場合は、セル指定部分だけ調整し、取りあえず使用してみることをおすすめ。

この記事を書いた人

小濱 直人

小濱 直人

主にソーシャルメディア関連の運用業務を担当。 圧力IH炊飯器で炊いて食べる、お米が好き。

おすすめ記事

タグ

2020新卒バトンAdobe IllustratorBIツールBOTCCDLab.CSSCSV事例DockerDXECExcelExcel関数GitGoogleAnalyticsGoogleスプレッドシートGoogleデータポータルLT会MembersDinerOJTPhotoshopPythonRubySDGsSEOSimilarWebSlackSNSSocial Art JapanプロジェクトSQLUIUXUXリサーチVSCodeWebディレクションWebディレクターWebマーケティングWeb解析Well-beingWordPressアクセシビリティアナリティクスウェビナーウェビナー運用エシカルエシカルファッションエンジニアオウンドメディアオンラインイベントお悩み相談室キャリアクライアントワークコーディングコミュニケーションコンテンツマーケティングコンペサービスサイト構造サステイナブルサンプルスウェーデンスキルアップセミナーソーシャルアーティストソーシャルクリエイターチームビルディングツールデータディレクションディレクターデザイナーデザインデンマークトンマナナレッジブームの裏側フレームワークプログラミングプログラミング教育フロントエンドマーケティングマネジメントスキルミーティングメタバースメンバーズメディカルマーケティングカンパニーメンバーズルーツカンパニーユーザーテストライティングラボ活動リモートワークショップワークスタイル事例事例紹介仕事術仙台仙台オフィス分析効率化勉強会動画北欧医療業界品質管理地方金融企業基本情報技術者試験広告運用提案数学新卒研修新規構築機械学習気候変動海洋プラスチック問題生産性向上産学連携研修社会課題社会課題調査競技プログラミング脱炭素自動化ツール色彩検定製薬業界試験対策資格開発環境障がい者雇用食の問題