わえなび ワード&エクセル問題集

当サイトはWordとExcelの実力を鍛える最高水準の特訓問題集です。リンクを張るのは自由です。みんなで勉強しましょう!

【Excel関数】先週、今週、翌週の曜日の日付、週番号、今週の日付の判定

日本のカレンダーは週の始まりを日曜日にするものと月曜日にするものが混在してます。もともと曜日の起源は西洋の神々の名前であり、太陽=日曜日から順に、月、火星、水星、木星、金星、土星と命名されたため、アメリカをはじめ多くの国では日曜始まりとなっています。

しかし、最近では土日のことを「週末」と言うように週の始まりを月曜日とすることが多く、国際規格ISO 8601や日本工業規格JIS X 0301では月曜が週の最初の日として扱われています。1週間を日曜始まりとするか、月曜始まりとするかによって、「先週」や「来週」の意味が変わります。

そこで、今回は、日曜始まりと月曜始まりの先週、今週、翌週の日付の計算方法について解説します。

目次

1.週の計算の基本

(1)エクセルの週の始まりは日曜日

Excelはアメリカのカレンダーが元となっており、1週間の始まりは日曜日です。

WEEKDAY関数のデフォルトは日曜=1、月曜=2、・・・土曜=7となっています。1900年1月1日を起点とするシリアル値は、土曜日が7の倍数になるため、土曜日=7または0としたほうが計算上都合が良いのです。

f:id:waenavi:20191106173345j:plain

なお、特定の曜日の日付を求める基本的な計算方法については、こちらの記事をご覧ください。

 

(2)次回と翌週は違う

上記の記事では、次回の日曜日~土曜日の計算方法を解説しました。しかし、「次回」と「翌週」では意味が違います。例えば、次回の水曜日は、7日に1回ある水曜日のうち直近の日を指しますが、翌週水曜日は週単位で考えるので、月曜日や火曜日から見ると次々回の水曜になります。

f:id:waenavi:20191106173444j:plain

 

また、前回の水曜日は直前の水曜日を指しますが、先週の水曜日が前々回を指すことがあります。

f:id:waenavi:20191106173512j:plain

 

2.TODAYを基準として先週、今週、来週の*曜日を求める

(1)日曜始まりの場合

問題

1週間を日曜日から始まり土曜日で終わるものとするとき、今日の日付(TODAY関数)を用いて、先週、今週、来週の日曜日~土曜日の日付を求めなさい。

f:id:waenavi:20191106180213j:plain

 

解説

日付(シリアル値)から1を引いて、その数を7の倍数で切り捨てると、日曜~金曜については直前の土曜日を返し、土曜については7日前の土曜日を求めることができます(上記記事参照)。これは日曜始まりの場合の「先週土曜日」にあたります。

f:id:waenavi:20191106180359j:plain

 

したがって、日曜始まりの場合、先週の土曜日を求める数式は「=FLOOR(TODAY(),7)」となります。

f:id:waenavi:20191106180557j:plain

 

-6~+7をすることで先週から今週までの日にちが求められます。

f:id:waenavi:20191106180709j:plain

  • 先週日曜日:=FLOOR(TODAY()-1,7)-6
  • 先週月曜日:=FLOOR(TODAY()-1,7)-5
  • 先週火曜日:=FLOOR(TODAY()-1,7)-4
  • 先週水曜日:=FLOOR(TODAY()-1,7)-3
  • 先週木曜日:=FLOOR(TODAY()-1,7)-2
  • 先週金曜日:=FLOOR(TODAY()-1,7)-1
  • 先週土曜日:=FLOOR(TODAY()-1,7)
  • 今週日曜日:=FLOOR(TODAY()-1,7)+1
  • 今週月曜日:=FLOOR(TODAY()-1,7)+2
  • 今週火曜日:=FLOOR(TODAY()-1,7)+3
  • 今週水曜日:=FLOOR(TODAY()-1,7)+4
  • 今週木曜日:=FLOOR(TODAY()-1,7)+5
  • 今週金曜日:=FLOOR(TODAY()-1,7)+6
  • 今週土曜日:=FLOOR(TODAY()-1,7)+7

FLOOR関数で先週から今週の日付を一気に求めることができます。

f:id:waenavi:20191106180916j:plain

 

日付(シリアル値)を7の倍数で切り上げると、今週の土曜日を求めることができます。

f:id:waenavi:20191106185634j:plain

 

したがって、CEILINGを用いて、今週の日曜~土曜を次のように表すこともできます。

  • 今週日曜日:=CEILING(TODAY(),7)-6
  • 今週月曜日:=CEILING(TODAY(),7)-5
  • 今週火曜日:=CEILING(TODAY(),7)-4
  • 今週水曜日:=CEILING(TODAY(),7)-3
  • 今週木曜日:=CEILING(TODAY(),7)-2
  • 今週金曜日:=CEILING(TODAY(),7)-1
  • 今週土曜日:=CEILING(TODAY(),7)

f:id:waenavi:20191106185720j:plain

 

来週の日付はその1~7日後です。

  • 来週日曜日:=CEILING(TODAY(),7)+1
  • 来週月曜日:=CEILING(TODAY(),7)+2
  • 来週火曜日:=CEILING(TODAY(),7)+3
  • 来週水曜日:=CEILING(TODAY(),7)+4
  • 来週木曜日:=CEILING(TODAY(),7)+5
  • 来週金曜日:=CEILING(TODAY(),7)+6
  • 来週土曜日:=CEILING(TODAY(),7)+7

CEILING関数で今週から翌週の日付を一気に求めることができます。

f:id:waenavi:20191106185923j:plain

 

(2)月曜始まりの場合

問題

1週間を月曜日から始まり日曜日で終わるものとするとき、今日の日付(TODAY関数)を用いて、先週、今週、来週の月曜日~日曜日の日付を求めなさい。

f:id:waenavi:20191106190047j:plain

 

解説

日付(シリアル値)から5を足して、その数を7の倍数で切り捨てて6を引くと、月曜~土曜については直前の日曜日を返し、日曜については7日前の日曜日を求めることができます(上記記事参照)。これは月曜始まりの場合の「先週日曜日」にあたります。

f:id:waenavi:20191106190537j:plain

 

したがって、月曜始まりの場合、先週の日曜日を求める数式は「=FLOOR(TODAY()+5,7)-6」となります。

f:id:waenavi:20191106190620j:plain

 

-6~+14をすることで先週から来週までの日にちが求められます。

  • 先週月曜日:FLOOR(TODAY()+5,7)-12
  • 先週火曜日:FLOOR(TODAY()+5,7)-11
  • 先週水曜日:FLOOR(TODAY()+5,7)-10
  • 先週木曜日:FLOOR(TODAY()+5,7)-9
  • 先週金曜日:FLOOR(TODAY()+5,7)-8
  • 先週土曜日:FLOOR(TODAY()+5,7)-7
  • 先週日曜日:FLOOR(TODAY()+5,7)-6
  • 今週月曜日:FLOOR(TODAY()+5,7)-5
  • 今週火曜日:FLOOR(TODAY()+5,7)-4
  • 今週水曜日:FLOOR(TODAY()+5,7)-3
  • 今週木曜日:FLOOR(TODAY()+5,7)-2
  • 今週金曜日:FLOOR(TODAY()+5,7)-1
  • 今週土曜日:FLOOR(TODAY()+5,7)
  • 今週日曜日:FLOOR(TODAY()+5,7)+1
  • 来週月曜日:FLOOR(TODAY()+5,7)+2
  • 来週火曜日:FLOOR(TODAY()+5,7)+3
  • 来週水曜日:FLOOR(TODAY()+5,7)+4
  • 来週木曜日:FLOOR(TODAY()+5,7)+5
  • 来週金曜日:FLOOR(TODAY()+5,7)+6
  • 来週土曜日:FLOOR(TODAY()+5,7)+7
  • 来週日曜日:FLOOR(TODAY()+5,7)+8

FLOOR関数で先週から来週の日付を一気に求めることができます。

f:id:waenavi:20191106190843j:plain

 

3.先週の1週間を表す

問題

先週の月曜と日曜の日付を求め、1週間を表しなさい。

f:id:waenavi:20191106191158j:plain

 

解説

「先週の1週間」「来週の1週間」という場合、日曜始まりか月曜始まりかに注意しなければなりません。月曜~日曜を1週間とする場合、月曜始まりで計算します。

  • 先週月曜日:FLOOR(TODAY()+5,7)-12
  • 先週日曜日:FLOOR(TODAY()+5,7)-6

f:id:waenavi:20191106191253j:plain

 

別解

日曜日の日付が分かっている場合、同じ週の月曜日はその6日前です。

  • 先週月曜日:先週日曜-6

f:id:waenavi:20191106191322j:plain

 

4.カレンダーの先頭の日付

(1)年と月からカレンダーの先頭を求める

問題

セルA1に西暦4桁、セルA2に月を入力した。セルA5以降に日曜始まりのカレンダーを作成し、先月の日付の場合はグレーで表示する場合、セルA5の日付を求めなさい。

f:id:waenavi:20191106191430j:plain

 

解説

カレンダーの1行目には必ずその月の初日(1日)があります。

f:id:waenavi:20191106192301j:plain

 

日曜始まりで、初日(1日)の「今週の日曜日」にあたる日がカレンダーの先頭の日です。日曜始まりの今週の日曜日は上記の計算から、「=FLOOR(日付-1,7)+1」なので、「=FLOOR(1日-1,7)+1」となります。「1日-1」とは前月の末日のことなので、「=FLOOR(前月末,7)+1」とすることができます。

f:id:waenavi:20191106192749j:plain

 

前月末はDATE関数の第3引数を0にします。

  • =FLOOR(DATE(A1,A2,0),7)+1

f:id:waenavi:20191106192527j:plain

 

ちなみに、翌日を求めるのは+1、翌週の日付を求めるのは+7です。

f:id:waenavi:20191106193055j:plain

 

また、月が異なる場合にグレーにするときの条件付き書式の条件は「=MONTH(A5)<>$A$2」です。

f:id:waenavi:20191106193243j:plain

 

(2)初日からカレンダーの先頭を求める

問題

セルA1に任意の日付を入力した。セルA5以降に日曜始まりのカレンダーを作成し、先月の日付の場合はグレーで表示する場合、セルA5の日付を求めなさい。

f:id:waenavi:20191106193737j:plain

 

解説

任意の日付から前月末の日付を求めるにはEOMONTH関数で「EOMONTH(日付,-1)」です。

  • =FLOOR(EOMONTH(A1,-1),7)+1

f:id:waenavi:20191106200137j:plain

 

セルA1の表示形式を「yyyy年m月」にします。

f:id:waenavi:20191106200226j:plain

 

5.WEEKNUM関数(週番号)の使い方

問題

セルA1に入力した日付の週番号を求めなさい。

f:id:waenavi:20191106202117j:plain

 

解説

1月1日(元日)を含む週を「1」として週の数を数えたものを週番号といいます。

f:id:waenavi:20191106202300j:plain

 

WEEKNUM関数は、同じ年の1月1日から数えたときの週番号を求める関数です。何週目にあたるかを求めることができます。

  • WEEKNUM(日付,週の始まり)

WEEKNUM関数の第2引数を省略すると日曜始まりとなります(Excelは日曜始まりがデフォルト)。「=WEEKNUM(A1)」と入力します。元日から数えて23週目であることが分かります。

f:id:waenavi:20191106202111j:plain

 

日曜始まりと月曜始まりでは週番号が1日ずれます。

f:id:waenavi:20191106202222j:plain

 

月曜始まりの週番号を求める場合は、WEEKNUM関数の第2引数を2にします。「=WEEKNUM(A1,2)」と入力します。

f:id:waenavi:20191106202114j:plain

 

6.週の判定

(1)今週の場合にフラグを立てる

問題

A列に入力した日付が今週の日付であれば1、そうでなければ0と表示しなさい。

f:id:waenavi:20191106202458j:plain

 

解説

日曜始まりの場合、今週の日曜日以上、来週の日曜日未満の日付であれば、今週の日付となるので、IF関数を用いて判定をしようと思えばできます。

  • =IF(AND(A1>=FLOOR(TODAY()-1,7)-6),A1<FLOOR(TODAY()-1,7)+1),1,0)

しかし、週が同一かどうかを判定するにはWEEKNUM関数を用いたほうがはるかに簡単です。週番号がTODAYと同じであれば今週の日付と判定することができます。

  • =WEEKNUM(A2)=WEEKNUM(TODAY())

f:id:waenavi:20191106202549j:plain

 

また、月曜始まりの場合、WEEKNUM関数の第2引数を2にします。1日ずれます。

  • =WEEKNUM(A2,2)=WEEKNUM(TODAY(),2)

f:id:waenavi:20191106202630j:plain

 

(2)今週なら0、先週なら-1、来週なら1

問題

A列に入力した日付が今週の日付であれば0、先週以前なら-1、-2、・・・とし、来週以降なら1、2、・・・と表示しなさい。

f:id:waenavi:20191106202458j:plain

 

解説

この場合もWEEKNUM関数を使えばよいです。日曜始まりの場合はWEEKNUMからWEEKNUMを引くだけです。

  • =WEEKNUM(A2)-WEEKNUM(TODAY())

f:id:waenavi:20191106203049j:plain

 

月曜始まりなら第2引数を2にします。

  • =WEEKNUM(A2,2)-WEEKNUM(TODAY(),2)

f:id:waenavi:20191106203137j:plain

 

7.週番号から週の始まりと終わりを求める

問題

セルA1に西暦4桁を入力し、セルA2に週番号を入力した。週の始まりの日と終わりの日を求めなさい。

f:id:waenavi:20191106203325j:plain

 

解説

WEEKNUM関数は日付から週番号を求める関数ですが、その逆で週番号から日付を求めることもできます。元日(1月1日)に7を足すと次の週、14を足すとさらに次の週になります。いっぱんに週番号をNとすると、(N-1)*7を足すことによって、その週の日付を求めることができます。このことは日曜始まりと月曜始まりで同じです。

f:id:waenavi:20191106211815j:plain

 

元日=DATE(A1,1,1)に(N-1)*7を加算します。

  • DATE(A1,1,1)+(A2-1)*7

簡単にすると「DATE(A1,1,A2*7-6)」となります。

f:id:waenavi:20191106212022j:plain

 

日曜始まりの場合、「今週の日曜日」と「今週の土曜日」を求めます。

  • 今週の日曜日:=FLOOR(日付-1,7)+1
  • 今週の土曜日:=FLOOR(日付-1,7)+7

したがって、次のような式になります。

  • 日曜(はじめ):=FLOOR(DATE(A1,1,A2*7-7),7)+1
  • 土曜(おわり):=FLOOR(DATE(A1,1,A2*7-7),7)+7

これらの式は、さらに次のように簡単にすることができます。

  • 日曜(はじめ):=FLOOR(DATE(A1,1,A2*7),7)-6
  • 土曜(おわり):=FLOOR(DATE(A1,1,A2*7),7)

f:id:waenavi:20191106212343j:plain

 

月曜始まりの場合、「今週の月曜日」と「今週の日曜日」を求めます。

  • 今週の月曜日:FLOOR(日付+5,7)-5
  • 今週の日曜日:FLOOR(日付+5,7)+1

したがって、次のような式になります。

  • 月曜(はじめ):=FLOOR(DATE(A1,1,A2*7-1),7)-5
  • 日曜(おわり):=FLOOR(DATE(A1,1,A2*7-1),7)+1

f:id:waenavi:20191114152648j:plain

 

西暦年と週番号が分かっている場合、各曜日の日付を求める計算式は次の通りです。

(日曜始まり)

  • 日曜日:=FLOOR(DATE(年,1,週番号*7),7)-6
  • 月曜日:=FLOOR(DATE(年,1,週番号*7),7)-5
  • 火曜日:=FLOOR(DATE(年,1,週番号*7),7)-4
  • 水曜日:=FLOOR(DATE(年,1,週番号*7),7)-3
  • 木曜日:=FLOOR(DATE(年,1,週番号*7),7)-2
  • 金曜日:=FLOOR(DATE(年,1,週番号*7),7)-1
  • 土曜日:=FLOOR(DATE(年,1,週番号*7),7)

(月曜始まり)

  • 月曜日:=FLOOR(DATE(年,1,週番号*7-1),7)-5
  • 火曜日:=FLOOR(DATE(年,1,週番号*7-1),7)-4
  • 水曜日:=FLOOR(DATE(年,1,週番号*7-1),7)-3
  • 木曜日:=FLOOR(DATE(年,1,週番号*7-1),7)-2
  • 金曜日:=FLOOR(DATE(年,1,週番号*7-1),7)-1
  • 土曜日:=FLOOR(DATE(年,1,週番号*7-1),7)
  • 日曜日:=FLOOR(DATE(年,1,週番号*7-1),7)+1

 

8.カレンダーの第何週にあたるか

問題

A列の日付が、同じ月で第何週にあたるかを求めなさい。

f:id:waenavi:20191106213016j:plain

 

解説

日曜始まりの場合、WEEKNUM(A1)で週番号が求められます。これを1、2、3・・・にすればよいです。下の図の場合は17を引けばよいのですが、この17は月初の週番号から1を引いた数です。

f:id:waenavi:20191106213208j:plain

 

同じ月の1日(月の初日)は先月末+1、つまり、EOMONTH(A1,-1)+1なので、「WEEKNUM(EOMONTH(A1,-1)+1)-1」を引きます。

  • ="第"&WEEKNUM(A1)-WEEKNUM(EOMONTH(A1,-1)+1)+1&"週"

f:id:waenavi:20191106213607j:plain

 

月曜始まりならWEEKNUMの第2引数を2にします。

  • ="第"&WEEKNUM(A1,2)-WEEKNUM(EOMONTH(A1,-1)+1,2)+1&"週"

 


解説は以上です。


 


Copyright(C)2018-2019 waenavi, All rights reserved. [www.waenavi.com 定礎 平成三十年八月]