エクセルで週番号から日付を計算してみる ― 2013年04月28日 18:16
エクセルで日付から週番号を計算してみましたが、
実際にWeek xx と言われてもいったい何日なの？？？
んじゃあ、
エクセルで週番号から日付を計算してみよう
と思い、下図のようなフォーマットを作って、
D3に使いたい式を入れて、1週分J列まで、セルをコピペすると、
$B$2=年と$B3＝週によって決まる1週間分の日付が算出できる、
各システムで、数式を組んでみました。
|
システム |
週の始まりの曜日 |
週の始まりの日 "D3" の算出式 |
|
|
ISO-8601システム |
月曜日 |
D3=IF($B3<1,"",IF(DATE($B$2,1,5) -WEEKDAY(DATE($B$2,1,3)) +7*($B3-1)+D$1 <DATE($B$2+1,1,5) -WEEKDAY(DATE($B$2+1,1,3)) ,DATE($B$2,1,5) -WEEKDAY(DATE($B$2,1,3)) +7*($B3-1)+D$1,"")) |
|
|
曜日基準システム |
US |
日曜日 |
D3=IF(YEAR(DATE($B$2,1,2) -WEEKDAY(DATE($B$2,1,$C$1)) +7*($B3-1)+D$1)=$B$2 ,DATE($B$2,1,2) -WEEKDAY(DATE($B$2,1,$C$1)) +7*($B3-1)+D$1,"") |
|
その他 |
月曜日 |
||
|
火曜日 |
|||
|
水曜日 |
|||
|
木曜日 |
|||
|
金曜日 |
|||
|
土曜日 |
|||
|
単純週番号システム |
D3=IF(YEAR(DATE($B$2,1,1) +7*($B3-1)+D$1) =$B$2 ,(DATE($B$2,1,1)+7*($B3-1)+D$1,"") |
||
$B3 = 調べたい週
$C$1 = 基準曜日毎のパラメータ値
| =IF(B1="日",1,IF(B1="月",0,IF(B1="火",6,IF(B1="水",5 ,IF(B1="木",4,IF(B1="金",3,IF(B1="土",2))))))) _________________________________________ B1 = 基準曜日 (日、月、火、水、木、金、土 の何れか)
|
| 週の始まりの日：D$1 = 0 翌日 ：E$1 = 1 翌々日 ：F$1 = 2 ３日後 ：G$1 = 3 ４日後 ：H$1 = 4 ５日後 ：I$1 = 5 ６日後 ：J$1 =6
|
で、各システムはどういう考え方？もあるけど、
やっぱり、
エクセルで年間カレンダーつくってみたい
わけで、以下は、
1. ISO-8601システムの考え方 と カレンダー
2. 曜日基準システムの考え方 と ちょっと凝ったカレンダー
3. 単純週番号システムの考え方 と カレンダー
です。
1 ISO-8601システムの考え方
|
Week1=その年の1月4日が含まれる週の、月曜日の日付に対し、 調べたい週番号 (Week xx) の月～日の各日付は、 Week xx の月曜日：+ 7日×(求めたい週番号-1) + 0日 Week xx の火曜日：+ 7日×(求めたい週番号-1) + 1日 Week xx の水曜日：+ 7日×(求めたい週番号-1) + 2日 Week xx の木曜日：+ 7日×(求めたい週番号-1) + 3日 Week xx の金曜日：+ 7日×(求めたい週番号-1) + 4日 Week xx の土曜日：+ 7日×(求めたい週番号-1) + 5日 Week xx の日曜日：+ 7日×(求めたい週番号-1) + 6日 と言う具合で、各日付を算出します。 |
(1) Week 1 = その年の1月4日が含まれる週の、月曜日は何日か？
◎1月4日が属する週の月曜日は、12/29～1/4の7日間のうち、何れかで、
＝1月4日の直前の日曜日の1日後
＝1月5日の直前の月曜日 という式になります。
| DATE($B$2,1,5)-WEEKDAY(DATE($B$2,1,3))
|
参照：WEEKDAY関数のポイント
(2) 調べたい週番号 (Week xx)の、月曜日は何日か？
Week1の月曜日は、Week1の月曜日 + 7× (1-1) + 0日
Week2の月曜日は、Week1の月曜日 + 7× (2-1) + 0日 ← (Week1の1週後)
Week3の月曜日は、Week1の月曜日 + 7× (3-1) + 0日 ← (Week1の2週後)
・・・
Week52の月曜日は、Week1の月曜日 + 7× (52-1) + 0日 ← (Week1の51週後)
| DATE($B$2,1,5)-WEEKDAY(DATE($B$2,1,3))+7*($B3-1)+D$1
|
※) $B3 = 調べたい週
※) D$1 = 週の始まりの日 = 0
(3) このままですと、Week0とか、Week100とかありえない週も表示されてしまうので
IF(理論式,[真の場合],[偽の場合])で、ありえない週の時は空白表示させます。
もし、Week xx が、1より小さい (0以下)なら表示しない。
| =IF($B3<1,"",[偽の場合])
|
| [偽の場合] = IF(DATE($B$2,1,5)-WEEKDAY(DATE($B$2,1,3))+7*($B3-1)+D$1 <DATE($B$2+1,1,5)-WEEKDAY(DATE($B$2+1,1,3)) ,DATE($B$2,1,5)-WEEKDAY(DATE($B$2,1,3))+7*($B3-1)+D$1,"")
|
というわけで、Week xx の月曜日の日付は下のようになります。
| ----- ISO週番号の日付式 ----- D3=IF($B3<1,"",IF(DATE($B$2,1,5)-WEEKDAY(DATE($B$2,1,3))+7*($B3-1)+D$1<DATE($B$2+1,1,5)-WEEKDAY(DATE($B$2+1,1,3)) ,DATE($B$2,1,5)-WEEKDAY(DATE($B$2,1,3))+7*($B3-1)+D$1,"")) |
※) $B3 = 調べたい週
※) D$1 = 週の始まりの日 = 0
● ISO週番号カレンダー
(1) 週の始まりは月曜日なので、エクセルのフォーマットは下図に様になります。
$B3 = 調べたい週
D$1～J$1：週の始まりの日から、1週間分の各日を算出するための追加日数
(2) さっそくフォーマットの週の始まりのセルとなるD3セルに、
上記ISO週番号の日付式 (月曜日の式) を入れてみます。
(3) D3セルを、火～日に、セルごとコピペします。
(4) シリアル値のままになっているので、書式を適当な日付フォーマットにします。
(5) 更に、Weekの$B3セルからオートフィルで一気に53まで連番をつくり、
Week 1 の数式を、Week 53 までコピペしてみると、年間カレンダーになります。
調べたい年＝B2 を変えると、D3～J55 の日付が再計算されます。
直近でWeek 53 がある年は、2009年と2015年でした。
2 曜日基準システムの考え方
|
Week1=1月1日が含まれる週の、週の始まりの曜日 (基準曜日) の日付に対し、 知りたい週番号 (Week xx) の月～日の各日付は、 Week xx の基準曜日：+ 7日×(求めたい週番号-1) + 0日 Week xx の翌日 ：+ 7日×(求めたい週番号-1) + 1日 Week xx の翌々日 ：+ 7日×(求めたい週番号-1) + 2日 Week xx の3日後 ：+ 7日×(求めたい週番号-1) + 3日 Week xx の4日後 ：+ 7日×(求めたい週番号-1) + 4日 Week xx の5日後 ：+ 7日×(求めたい週番号-1) + 5日 Week xx の6日後 ：+ 7日×(求めたい週番号-1) + 6日 と言う具合で、各日付を算出します。 |
(1) Week 1 = その年の1月1日が含まれる週の、週の始まりの曜日 (基準曜日) は何日か？
◎1月1日が属する週の週の始まりの曜日 (基準曜日) は、
1月2日の直前の週の始まりの曜日 (基準曜日) の日付となります。
| 日曜基準：DATE($B$2,1,2)-WEEKDAY(DATE($B$2,1,1)) 月曜基準：DATE($B$2,1,2)-WEEKDAY(DATE($B$2,1,0)) 火曜基準：DATE($B$2,1,2)-WEEKDAY(DATE($B$2,1,6)) 水曜基準：DATE($B$2,1,2)-WEEKDAY(DATE($B$2,1,5)) 木曜基準：DATE($B$2,1,2)-WEEKDAY(DATE($B$2,1,4)) 金曜基準：DATE($B$2,1,2)-WEEKDAY(DATE($B$2,1,3)) 土曜基準：DATE($B$2,1,2)-WEEKDAY(DATE($B$2,1,2))
|
※) 1, 0, 6, 5, 4, 3, 2 = 基準曜日毎のパラメータ値
参照：WEEKDAY関数のポイント
----- 数式の汎用化 -----
各曜日基準の、Week1の始まりの日付は、基準曜日毎に数式が違いますが、
違う点は、WEEKDAY関数の中の1箇所だけで、
上の表中の赤太字の部分である、基準曜日毎のパラメータ値です。
| 日曜基準：1 月曜基準：0 火曜基準：6 水曜基準：5 木曜基準：4 金曜基準：3 土曜基準：2
|
Week 1 = その年の1月1日が含まれる週の、
週の始まりの曜日 (基準曜日) の日付は、下のように汎用化できます。
| 汎用式：DATE($B$2,1,2)-WEEKDAY(DATE($B$2,1,$C$1))
|
※) $C$1 = 基準曜日毎のパラメータ値
| =IF(B1="日",1,IF(B1="月",0,IF(B1="火",6,IF(B1="水",5, IF(B1="木",4,IF(B1="金",3,IF(B1="土",2))))))) _________________________________________ B1 = 基準曜日 (日、月、火、水、木、金、土 の何れか)
|
(2) 調べたい週番号 (Week xx)の、週の始まりの曜日 (基準曜日) は何日か？
Week1の基準曜日は、Week1の基準曜日 + 7× (1-1) + 0日
Week2の基準曜日は、Week1の基準曜日 + 7× (2-1) + 0日 ← (Week1の1週後)
Week3の基準曜日は、Week1の基準曜日 + 7× (3-1) + 0日 ← (Week1の2週後)
・・・
Week52の月曜日は、Week1の基準曜日 + 7× (52-1) + 0日 ← (Week1の51週後)
| DATE($B$2,1,2)-WEEKDAY(DATE($B$2,1,$C$1))+7*($B3-1)+D$1
|
※) $B3 = 調べたい週
※) $C$1 = 基準曜日毎のパラメータ値
| =IF(B1="日",1,IF(B1="月",0,IF(B1="火",6,IF(B1="水",5, IF(B1="木",4,IF(B1="金",3,IF(B1="土",2))))))) _________________________________________ B1 = 基準曜日 (日、月、火、水、木、金、土 の何れか)
|
(3) このままですと、Week1と同じ週の昨年の日付や、Week0とか、Week100など、ありえない日付や週も表示されてしまうので
IF(理論式,[真の場合],[偽の場合])で、ありえない日付や週の時は空白表示させます。
IF(理論式,[真の場合],[偽の場合])
もし、その日が調べたい年ならば、[その日を表示し]、そうでないなら[表示しない]
と、記述すると、Week xx の、各基準曜日における、
週の始まりの曜日の日付は、下のようになります。
----- 曜日基準週番号の汎用日付式 ----- D3=IF(YEAR(DATE($B$2,1,2)-WEEKDAY(DATE($B$2,1,$C$1))+7*($B3-1)+D$1)=$B$2, DATE($B$2,1,2)-WEEKDAY(DATE($B$2,1,$C$1))+7*($B3-1)+D$1,"") |
※) $B3 = 調べたい週
※) $C$1 = 基準曜日毎のパラメータ値
| =IF(B1="日",1,IF(B1="月",0,IF(B1="火",6,IF(B1="水",5, IF(B1="木",4,IF(B1="金",3,IF(B1="土",2))))))) _________________________________________ B1 = 基準曜日 (日、月、火、水、木、金、土 の何れか)
|
● 曜日基準の週番号カレンダー
基準曜日をプルダウンで選ぶと、基準曜日が切り替るちょっと凝ったカレンダーを作ろうと思います。
(1) まず、下図のようなフォーマットの骨格をつくります。
$B3 = 調べたい週
D$1～J$1：週の始まりの日から、1週間分の各日を算出するための追加日数
(2) C1セルに、曜日ごとに変わるパラメータの関数と、
D2:J2セルに、曜日を表示させるための関数を入れます。
|
C1 |
基準曜日毎のパラメータ値をIF文で場合分けして記述します。 |
|
D2 |
=B1 |
|
E2 |
左隣が、月なら火、火なら水・・・と記述していきます。 =IF(D2="月","火",IF(D2="火","水",IF(D2="水","木",IF(D2="木","金" ,IF(D2="金","土",IF(D2="土","日","月")))))) |
|
F2～J2 |
E2をセルごとコピペすると、 D2が、E2, F2,・・・I2と自動で変わります。 |
(3) B1セルにプルダウンで基準曜日を選択できるようにし、
C1セルとD2:J2セルに表示された曜日の土日に色がつくように、
ちょっと細工します。
|
B1 |
基準曜日のプルダウン選択を設定 元の値(S) は、 日, 月, 火, 水, 木, 金, 土 と、曜日をコンマで区切ります。 |
|
B1 D2～J2 |
B1とD2～J2の合計8個のセルを選択して、条件付書式の設定をします。書式は好みですが、とりあえず、土曜日は青、日曜日は赤、その他の曜日は 色なし になるようにしてみました。 |
B1のプルダウンで、曜日を変えると、D2～J2までの曜日が変わり、それに伴い土日の表示が連動すればOKです。
(4) 汎用式を入れてみる
さっそくフォーマットの週の始まりのセルとなる、D3セルに、
上記の 曜日基準週番号の汎用日付式 を入れてみます。
(5) 更に、Weekの$B3セルからオートフィルで一気に54まで連番をつくり、
D3セルを、Week 54 の週末にあたる、J56までセルごとコピペしてみると、
年間カレンダーになります。
表示が切り替わり、うまくいったようです。
とは言え、使うのは、月曜か日曜始まりだけかもしれません。。。
3 単純週番号システムの考え方
1月1日が、その年のWeek1の始まりの日になります。 1月1日と同じ曜日とそれに続く6日間は、 Week xx の1月1日と同じ曜日 ：+ 7日×(求めたい週番号-1) + 0日 翌日 ：+ 7日×(求めたい週番号-1) + 1日 翌々日：+ 7日×(求めたい週番号-1) + 2日 3日後 ：+ 7日×(求めたい週番号-1) + 3日 4日後 ：+ 7日×(求めたい週番号-1) + 4日 5日後 ：+ 7日×(求めたい週番号-1) + 5日 6日後 ：+ 7日×(求めたい週番号-1) + 6日
|
(1) 調べたい週番号 (Week xx)の、週の始まりは何日か？
Week1の始まりの日は、1月1日 + 7× (1-1) + 0日
Week2の始まりの日は、1月1日 + 7× (2-1) + 0日 ← (Week1の1週後)
Week3の始まりの日は、1月1日 + 7× (3-1) + 0日 ← (Week1の2週後)
・・・
Week52の始まりの日は、1月1日 + 7× (52-1) + 0日 ← (Week1の51週後)
| DATE($B$2,1,1)+7*($B3-1)+D$1
|
※) $B3 = 調べたい週
※) D$1 = 週の始まりの日 = 0
(2) このままですと、Week53と同じ週の翌年の日付や、Week0とか、Week100など、ありえない日付や週も表示されてしまうので
IF(理論式,[真の場合],[偽の場合])で、ありえない日付や週の時は空白表示させます。
IF(理論式,[真の場合],[偽の場合])
もし、その日が調べたい年ならば、[その日を表示し]、そうでないなら[表示しない]
と、記述すると、Week xx の、週の始まりの日付は、下のようになります。
| ----- 単純週番号の日付式 ----- D3=IF(YEAR(DATE($B$2,1,1)+7*($B3-1)+D$1)=$B$2, (DATE($B$2,1,1)+7*($B3-1)+D$1,"") |
※) $B3 = 調べたい週
※) D$1 = 週の始まりの日 = 0
● 単純週番号カレンダー
(1) 1月1日の曜日 (週のはじまりの曜日) が、年によって変わってしまうので、
曜日表示 (D2:J2セル) は、その年の1月1日～7日までの日付を使って、
曜日表示させると共に、表示された曜日の土日に色がつくようにしようと思います。
そこで、以下の2つのどちらかの方法を使います。
----- 方法1 セルの書式設定を利用する -----
| D2=WEEKDAY(DATE($B$2,1,1)+D$1) とし、これをE2～J2 にコピペします。
|
※DATE関数だけでは、条件付き書式の設定ができず、セルに色が付けられません。
セルの書式設定で、表示形式を aaa とします。
WEEKDAY関数の戻り値を条件に指定します。
書式は、
土曜日は青、日曜日は赤、その他の曜日は 色なし になるようにしてみました。
----- 方法2 CHOOSE関数を利用する -----
| D2=CHOOSE(WEEKDAY(DATE($B$2,1,1)+D$1), "日","月","火","水","木","金","土") D2=CHOOSE(WEEKDAY(DATE($B$2,1,1)+D$1,2), "月","火","水","木","金","土","日") D2=CHOOSE(WEEKDAY(DATE($B$2,1,1)+D$1,3)+1, "月","火","水","木","金","土","日") の何れかとし、D2のセルごと、E2～J2 にコピペします。
|
条件付き書式の設定で、曜日基準のときと同じ設定をします。
(2) さっそくフォーマットの週の始まりのセルとなる、D3セルに、
上記の 単純週番号の日付式 を入れてみます。
(3) 更に、Weekの$B3セルからオートフィルで一気に53まで連番をつくり、
D3セルを、Week 53 の週末にあたる、J55までコピペしてみると、
年間カレンダーになります。
最近のコメント