エクセルで週番号から日付を計算してみる2013年04月28日 18:16

週番号 Week xx ってどんな風に決まっている?で、週番号をいろいろ調べてみて、
エクセルで日付から週番号を計算してみましたが、
実際に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,"")

 $B$2 = 調べたい年
 $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:週の始まりの日から、1週間分の各日を算出するための追加日数
週の始まりの日:D$1 = 0
翌日     :E$1 = 1
翌々日    :F$1 = 2
3日後    :G$1 = 3
4日後    :H$1 = 4
5日後    :I$1 = 5
6日後    :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))

 ※) $B$2 = 調べたい年
 参照: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

 ※) $B$2 = 調べたい年
 ※) $B3 = 調べたい週
 ※) D$1 = 週の始まりの日 = 0


(3) このままですと、Week0とか、Week100とかありえない週も表示されてしまうので
IF(理論式,[真の場合],[偽の場合])で、ありえない週の時は空白表示させます。

もし、Week xx が、1より小さい (0以下)なら表示しない
=IF($B3<1,"",[偽の場合])

もし、翌年のWeek1の月曜日より前の日だったら表示し、そうでなければ表示しない
[偽の場合]
= 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,""))
 ※) $B$2 = 調べたい年
 ※) $B3 = 調べたい週
 ※) D$1 = 週の始まりの日 = 0



● ISO週番号カレンダー


(1) 週の始まりは月曜日なので、エクセルのフォーマットは下図に様になります。
1ISOフォーマット
 $B$2 = 調べたい年
 $B3 = 調べたい週
 D$1~J$1:週の始まりの日から、1週間分の各日を算出するための追加日数

(2) さっそくフォーマットの週の始まりのセルとなるD3セルに、
上記ISO週番号の日付式 (月曜日の式) を入れてみます。
ISOフォーマット+1数式

(3)
D3セルを、火~日に、セルごとコピペします。
火~日にセルごとコピペ

(4) シリアル値のままになっているので、書式を適当な日付フォーマットにします。
ISOフォーマット+2書式

(5) 更に、Weekの$B3セルからオートフィルで一気に53まで連番をつくり、
Week 1 の数式を、Week 53 までコピペしてみると、年間カレンダーになります。
1ISOフォーマットカレンダー
数式はWeek 53まで入っていますが、表示されないので、2012年は52週までしかないようです。

調べたい年=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))

 ※) $B$2 = 調べたい年
 ※) 1, 0, 6, 5, 4, 3, 2 = 基準曜日毎のパラメータ値
 参照:WEEKDAY関数のポイント

 ----- 数式の汎用化 -----
 各曜日基準の、Week1の始まりの日付は、基準曜日毎に数式が違いますが、
 違う点は、WEEKDAY関数の中の1箇所だけで、
 上の表中の赤太字の部分である、基準曜日毎のパラメータ値です。
日曜基準:1
月曜基準:0
火曜基準:6
水曜基準:5
木曜基準:4
金曜基準:3
土曜基準:2

 このパラメータ値をC1セルに入れることにすると、
 Week 1 = その年の1月1日が含まれる週の、
 週の始まりの曜日 (基準曜日) 日付は、下のように汎用化できます。
汎用式:DATE($B$2,1,2)-WEEKDAY(DATE($B$2,1,$C$1))

 ※) $B$2 = 調べたい年
 ※) $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

 ※) $B$2 = 調べたい年
 ※) $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


(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,"")

 ※) $B$2 = 調べたい年
 ※) $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



● 曜日基準の週番号カレンダー

基準曜日をプルダウンで選ぶと、基準曜日が切り替る
ちょっと凝ったカレンダーを作ろうと思います。
曜日基準カレンダー切り替え

(1) まず、下図のようなフォーマットの骨格をつくります。
フォーマット骨格
 $B$2 = 調べたい年
 $B3 = 調べたい週
 D$1~J$1:週の始まりの日から、1週間分の各日を算出するための追加日数


(2) C1セルに、曜日ごとに変わるパラメータの関数と、
 D2:J2セルに、曜日を表示させるための関数を入れます。

C1

基準曜日毎のパラメータ値をIF文で場合分けして記述します。
=IF(B1="",1,IF(B1="",0,IF(B1="",6,IF(B1="",5, IF(B1="",4,IF(B1="",3,IF(B1="",2)))))))

D2

=B1

E2

左隣が、月なら火、火なら水・・・と記述していきます。

=IF(D2="","",IF(D2="","",IF(D2="","",IF(D2="","" ,IF(D2="","",IF(D2="","",""))))))

F2J2

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

 ※) $B$2 = 調べたい年
 ※) $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,"")
 ※) $B$2 = 調べたい年
 ※) $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 にコピペします。

 ※ここのWEEKDAY関数では、TYPEに、2, 3 は使えません
 ※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
 ------------------------------


(2) さっそくフォーマットの週の始まりのセルとなる、D3セルに、
 上記の 単純週番号の日付式 を入れてみます。


(3) 更に、Weekの$B3セルからオートフィルで一気に53まで連番をつくり、
 D3セルを、Week 53 の週末にあたる、J55までコピペしてみると、
 年間カレンダーになります。

コメント

コメントをどうぞ

※メールアドレスとURLの入力は必須ではありません。 入力されたメールアドレスは記事に反映されず、ブログの管理者のみが参照できます。

※なお、送られたコメントはブログの管理者が確認するまで公開されません。

名前:
メールアドレス:
URL:
コメント:

トラックバック