Excel

20日・25日・翌月10日を締め日にして月ごとに集計する(EDATE・EOMONTH関数)

この記事では、月の区切りを月末ではなく、「20日締め」、「25日締め」、「10日締め」のような区切りにして月ごとのデータを集計する方法を紹介します。

指定した月数後または月数前の日付を求めるEDATE(イーデイト)関数を使って求めるパターンと、指定した月数後または月数前の月末の日付を求めるEOMONTH(エンドオブマンス)関数を使って求めるパターンを紹介します。

 

20日までの売上を今月分として集計する

EDATE関数で求める場合の考え方

まずは、指定した月数後または月数前の日付を求めるEDATE(イーデイト)関数で集計してみます。

EDATE関数で、20日までを今月分、21日以降を来月分として集計するには、いったん日付から20日を引きます

例えば、「4月21日~5月20日」であれば、20日を引くことで「4月1日~4月30日」、「5月21日~6月20日」であれば、20日を引くことで「5月1日~5月31日」と締め日単位の1カ月を前月の日付にします

 

この「4月1日~4月30日」や「5月1日~5月31日」などの日付をもとにして、EDATE関数で1カ月後の日付を求め、1カ月後の日付の「月」をMONTH(マンス)関数で取り出します。取り出した「月」を基準にして、SUMIF関数で集計するという手順になります。

 

EDATE関数の具体例

具体例で説明します。今、A列が受注日、B列が売上金額の表があります。

20日締めの場合、C2セルには、[開始日]を受注日A2セルから20を引いた式、[]を1カ月後を示す「1」としたEDATE関数を入力します。このEDATE関数の式をMONTH関数で囲み、1カ月後の日付の「月」を求めます。C2セルのEDATE関数の式は、=MONTH(EDATE(A2-20,1)) です。

 

また、F2セルには、C列の1カ月後の日付の「月」を[検索範囲]、E2セルを[検索条件]、B列を[合計範囲]にしたSUMIF関数を入力します。F2セルのSUMIF関数の式は、=SUMIF(C$2:C$8,E2,B$2:B$8) です(=SUMIF($C$2:$C$8,E2,$B$2:$B$8) でもOK)。

EDATE関数(20日締めの場合)

 

EOMONTH関数で求める場合の考え方

次は、指定した月数後または月数前の月末の日付を求めるEOMONTH(エンドオブマンス)関数で集計してみます。

EOMONTH関数で集計する場合も、EDATE関数と同様に、いったん日付から20日を引きます

 

この20日を引いた日付をもとにして、EOMONTH関数で1カ月後の月末の日付を求め、1カ月後の月末の日付の「月」をMONTH関数で取り出します。取り出した「月」を基準にして、SUMIF関数で集計するという手順になります。

 

EOMONTH関数の具体例

今回も具体例で説明します。さきほどと同様に、A列が受注日、B列が売上金額の表があります。

20日締めの場合、C2セルには、[開始日]を受注日A2セルから20を引いた式、[]を1カ月後を示す「1」としたEOMONTH関数を入力します。このEOMONTH関数の式をMONTH関数で囲み、1カ月後の月末の日付の「月」を求めます。C2セルのEOMONTH関数の式は、=MONTH(EOMONTH(A2-20,1)) です。

 

また、F2セルには、C列の1カ月後の日付の「月」を[検索範囲]、E2セルを[検索条件]、B列を[合計範囲]にしたSUMIF関数を入力します。F2セルのSUMIF関数の式は、=SUMIF(C$2:C$8,E2,B$2:B$8) です(=SUMIF($C$2:$C$8,E2,$B$2:$B$8) でもOK)。

EOMONTH関数(20日締めの場合)

 

25日までの売上を今月分として計算する

EDATE関数で求める場合の考え方

EDATE関数で、25日までを今月分、26日以降を来月分として集計するには、いったん日付から25日を引きます

例えば、「4月26日~5月25日」であれば、25日を引くことで「4月1日~4月30日」、「5月25日~6月26日」であれば、25日を引くことで「5月1日~5月31日」と締め日単位の1カ月を前月の日付にします

 

この「4月1日~4月30日」や「5月1日~5月31日」などの日付をもとにして、EDATE関数で1カ月後の日付を求め、1カ月後の日付の「月」をMONTH関数で取り出します。取り出した「月」を基準にして、SUMIF関数で集計します。

 

EDATE関数の具体例

今回も、A列が受注日、B列が売上金額の表があるとします。

25日締めの場合、C2セルには、[開始日]を受注日A2セルから25を引いた式、[]を1カ月後を示す「1」としたEDATE関数を入力します。このEDATE関数の式をMONTH関数で囲み、1カ月後の日付の「月」を求めます。C2セルのEDATE関数の式は、=MONTH(EDATE(A2-25,1)) です。

 

また、F2セルには、C列の1カ月後の日付の「月」を[検索範囲]、E2セルを[検索条件]、B列を[合計範囲]にしたSUMIF関数を入力します。F2セルのSUMIF関数の式は、=SUMIF(C$2:C$8,E2,B$2:B$8) です(=SUMIF($C$2:$C$8,E2,$B$2:$B$8) でもOK)。

EDATE関数(25日締めの場合)

 

EOMONTH関数で求める場合の考え方

EOMONTH関数で集計する場合も、EDATE関数と同様に、いったん日付から25日を引きます

この25日を引いた日付をもとにして、EOMONTH関数で1カ月後の月末の日付を求め、1カ月後の月末の日付の「月」をMONTH関数で取り出します。取り出した「月」を基準にして、SUMIF関数で集計するという手順になります。

 

EOMONTH関数の具体例

さきほどと同様に、A列が受注日、B列が売上金額の表があるとします。

25日締めの場合、C2セルには、[開始日]を受注日A2セルから25を引いた式、[]を1カ月後を示す「1」としたEOMONTH関数を入力します。このEOMONTH関数の式をMONTH関数で囲み、1カ月後の月末の日付の「月」を求めます。C2セルのEOMONTH関数の式は、=MONTH(EOMONTH(A2-25,1)) です。

EOMONTH関数(25日締めの場合)

 

翌10日までの売上を今月分として計算する

翌10日までを今月分、翌11日以降を来月分として集計するには、いったん日付から10日を引きます

例えば、「4月11日~5月10日」であれば、10日を引くことで「4月1日~4月30日」、「5月11日~6月10日」であれば、10日を引くことで「5月1日~5月31日」と締め日単位の1カ月を当月の日付にします

 

20日締め、25日締めとは違い、当月の日付が得られているので、得られた「4月1日~4月30日」や「5月1日~5月31日」などの日付の「月」をMONTH関数で取り出します。取り出した「月」を基準にして、SUMIF関数で集計します。

C2セルの式は、=MONTH(A2-10) となります。

翌10日締め

 

関連記事

参考文献

-Excel
-, ,

© 2022 まなびっと Powered by AFFINGER5