エクセルで日付から週番号を計算してみる ― 2013年04月07日 22:07
エクセルで年月日から週番号を表示させたい
と思い計算してみることにしましたが、、、
エクセルのバージョンによって使える関数が異なるようです。
●Excel 2010/2013
●Excel 97/2000/2002/2003/2007
●Excel 2010/2013
1 ISO-8601システム
(1) WEEKNUM(日付シリアル値,Type) 関数
|
例：変換したい年月日がA1セルにある場合 |
◎ 日付シリアル値
|
方法 |
2012年1月2日の場合 |
|
セルに日付を入れて参照する |
WEEKNUM(A1,21) |
|
DATE(年,月,日) 関数を使う |
WEEKNUM(DATE(2010,1,2),21) |
|
日付シリアル値を使う |
WEEKNUM(40180,21) |
|
Type |
週の始まりの曜日 |
システム |
主な使用エリア |
第1週 (Week 1) |
|
21 |
月曜日 |
EU圏 |
最初の木曜日を含む週 ＝1月4日を含む週 |
(2) ISOWEEKNUM(日付シリアル値)関数
Excel 2013では、ISO-8601システム専用の関数があります。
例：変換したい年月日がA1セルにある場合 |
2 曜日基準システム
WEEKNUM(日付シリアル値,Type) 関数|
例：変換したい年月日がA1セルにあり、USシステムの場合 |
◎ 日付シリアル値
|
方法 |
2012年1月2日の場合 |
|
セルに日付を入れて参照する |
WEEKNUM(A1,Type) |
|
DATE(年,月,日) 関数を使う |
WEEKNUM(DATE(2010,1,2),Type) |
|
日付シリアル値を使う |
WEEKNUM(40180,Type) |
下表から目的に合ったTypeを選びます。Typeの1は省略できます。
|
Type |
週の始まりの曜日 |
システム |
主な使用エリア |
第1週 (Week 1) |
|
1 |
日曜日 |
US |
US圏 |
1月1日を含む週
同じ週の12/31以前は含まない |
|
2 |
月曜日 |
その他 |
|
|
|
11 |
月曜日 |
|
||
|
12 |
火曜日 |
|
||
|
13 |
水曜日 |
|
||
|
14 |
木曜日 |
|
||
|
15 |
金曜日 |
|
||
|
16 |
土曜日 |
中東諸国 |
||
|
17 |
日曜日 |
US |
US圏 |
●Excel 97/2000/2002/2003/2007
WEEKNUM関数が準備されていますが、Excel 2007 での週番号システムと日付/時刻表示の実装に、Excel の WEEKNUM 関数を使用した週番号システムについて
、と言う記事があって、WEEKNUM 関数で簡単に計算することができないようです。そこで、Excel 2007 での週番号システムと日付/時刻表示の実装に掲載されているWEEKNUM関数を使わない方法を参考にすると、
考え方は同じですが、計算式の記述方法は、それぞれ複数あります。
下表の数式は、WEEKDAY関数のTypeを省略し、最後に1を足す方法の例になります。
|
例：変換したい年月日がA1セルにある場合 |
|
システム |
週の始まりの曜日 |
算出式 |
|
|
月曜日 |
=1+INT((A1-DATE (YEAR(A1-WEEKDAY(A1-1)+4),1,5) +WEEKDAY(DATE (YEAR(A1-WEEKDAY(A1-1)+4),1,3)))/7) |
||
|
US |
日曜日 |
=1+INT((A1-(DATE(YEAR(A1),1,2) -WEEKDAY(DATE(YEAR(A1),1,1))))/7) |
|
|
その他 |
月曜日 |
=1+INT((A1-(DATE(YEAR(A1),1,2) -WEEKDAY(DATE(YEAR(A1),1,0))))/7) |
|
|
火曜日 |
=1+INT((A1-(DATE(YEAR(A1),1,2) -WEEKDAY(DATE(YEAR(A1),1,6))))/7) |
||
|
水曜日 |
=1+INT((A1-(DATE(YEAR(A1),1,2) -WEEKDAY(DATE(YEAR(A1),1,5))))/7) |
||
|
木曜日 |
=1+INT((A1-(DATE(YEAR(A1),1,2) -WEEKDAY(DATE(YEAR(A1),1,4))))/7) |
||
|
金曜日 |
=1+INT((A1-(DATE(YEAR(A1),1,2) -WEEKDAY(DATE(YEAR(A1),1,3))))/7) |
||
|
土曜日 |
=1+INT((A1-(DATE(YEAR(A1),1,2) -WEEKDAY(DATE(YEAR(A1),1,2))))/7) |
||
|
=1+INT((A1-DATE(YEAR(A1),1,1))/7) |
|||
--- 使っている関数 ---
|
INT(数値) 小数点以下を切り捨てる |
|
DATE(年,月,日) 年月日から日付シリアル値を返す |
|
YEAR(日付シリアル値) 日付シリアル値から年を返す |
|
WEEKDAY(日付シリアル値,Type) 日付シリアル値の曜日を数値で返す |
|
Type |
月 |
火 |
水 |
木 |
金 |
土 |
日 |
|
1 or 省略 |
2 |
3 |
4 |
5 |
6 |
7 |
1 |
|
2 |
1 |
2 |
3 |
4 |
5 |
6 |
7 |
|
3 |
0 |
1 |
2 |
3 |
4 |
5 |
6 |
----- ポイント -----
A1-WEEKDAY(B1,Type) で、A1の直前の特定曜日の日付を算出する
|
B1 |
Type |
|
|
1 or 省略 |
2 |
|
|
..., A1 , A1+7, ... |
直前の土曜日 |
直前の日曜日 |
|
..., A1-1, A1+6, ... |
直前の日曜日 |
直前の月曜日 |
|
..., A1-2, A1+5, ... |
直前の月曜日 |
直前の火曜日 |
|
..., A1-3, A1+4, ... |
直前の火曜日 |
直前の水曜日 |
|
..., A1-4, A1+3, ... |
直前の水曜日 |
直前の木曜日 |
|
..., A1-5, A1+2, ... |
直前の木曜日 |
直前の金曜日 |
|
..., A1-6, A1+1, ... |
直前の金曜日 |
直前の土曜日 |
=A1-WEEKDAY(A1-1,1)=A1-WEEKDAY(A1-1)=A1-WEEKDAY(A1,2)
＜考え方＞
1月10日が日曜日のとき、WEEKDAY関数 (Type=1or省略)は、1を返します。
1月10日から、戻り値の１を引くと、１日前なので、1月9日(土) になります。
1月10日は年によって曜日が変わるので、
もし、1月10日が、
月曜日なら２日前で、1月8日(土) になります。
火曜日なら３日前で、1月7日(土) になります。
・・・
土曜日なら７日前で、1月3日(土) になります。
つまり、
ある日付(A1)から、同じ日の WEEKDAY(A1) 戻り値を引くと、
A1の直前の土曜日の日付が算出できます。
では、
ある日付(A1)から、前日の WEEKDAY(A1-1) 戻り値を引くと、
引く値が同日より１小さくなるので、曜日が後ろにシフトし、
A1の直前の日曜日の日付が算出できます。
逆に、
ある日付(A1)から、翌日の WEEKDAY(A1+1) 戻り値を引くと、
引く値が同日より１大きくなるので、曜日が前にシフトし、
A1の直前の金曜日の日付が算出できます。
よって、
ある日付(A1)から、ある日付(B1)のWEEKDAY関数戻り値を引くと、
B1がA1の何日前か？後か？によって、A1の直前の特定曜日の日付となります。
●Type=2 を使っても、戻り値が1～7なので、同じことができます。
●Type=3 を使うと、戻り値が0～6なので、
A1と同じ曜日は直前 (7日前) ではなく、A1と同じ日付になってしまいます。
--------------------------
1 ISO-8601システムの考え方
|
Week 1の月曜日から変換したい日までの日数を7で割り、 |
＜WEEKDAY関数で、Type=1 or 省略 を使った場合＞
=1+INT((A1-DATE(YEAR(A1-WEEKDAY(A1-1)+4),1,5) +WEEKDAY(DATE(YEAR(A1-WEEKDAY(A1-1)+4),1,3)))/7) |
＜WEEKDAY関数で、Type=2 を使った場合＞
|
=1+INT((A1-DATE(YEAR(A1-WEEKDAY(A1,2)+4),1,5) +WEEKDAY(DATE(YEAR(A1-WEEKDAY(A1,2)+4),1,4),2))/7) |
(1) A1：変換したい年月日のあるセル (例として 2010年1月2日(土)=40180 を使います)
(2) A1にとってのWeek 1の月曜日が何日か？を計算します。
ISO-8601のWeek 1は、下表のように、7種類のパターンがあります。
12/29～1/4 (=1/5の前) の7日間のうち、何れかが月曜日になることになります。
|
月 |
火 |
水 |
木 |
金 |
土 |
日 |
|
12/29 |
12/30 |
12/31 |
1/1 |
1/2 |
1/3 |
1/4 |
|
12/30 |
12/31 |
1/1 |
1/2 |
1/3 |
1/4 |
1/5 |
|
12/31 |
1/1 |
1/2 |
1/3 |
1/4 |
1/5 |
1/6 |
|
1/1 |
1/2 |
1/3 |
1/4 |
1/5 |
1/6 |
1/7 |
|
1/2 |
1/3 |
1/4 |
1/5 |
1/6 |
1/7 |
1/8 |
|
1/3 |
1/4 |
1/5 |
1/6 |
1/7 |
1/8 |
1/9 |
|
1/4 |
1/5 |
1/6 |
1/7 |
1/8 |
1/9 |
1/10 |
●Week 1は1月4日を含む週と言い換えられます。
●また、A1が12/29～1/3のとき、
A1にとってのWeek 1の1月4日は、A1と同じ年とは限りません。
|
A1の曜日 |
月 |
火 |
水 |
木 |
金 |
土 |
日 |
|
同じ週の木曜日が 前年になる場合 |
|
|
|
前年 |
|
|
1/1 |
|
|
|
|
前年 |
|
1/1 |
1/2 |
|
|
|
|
|
前年 |
1/1 |
1/2 |
1/3 |
|
|
同じ週の木曜日が 翌年になる場合 |
12/29 |
12/30 |
12/31 |
翌年 |
|
|
|
|
12/30 |
12/31 |
|
翌年 |
|
|
|
|
|
12/31 |
|
|
翌年 |
|
|
|
例ではA1=2010年1月2日(土)なので、表の3行目に当たり木曜日は前年です。
そこで、
| A1が属する週の木曜日の年の1月4日は何日か？を計算し、
|
|
その年の1月4日が含まれる週=Week 1の、月曜日は何日か？を計算します。 |
●A1が属する週の木曜日の年の1月4日は何日か？
|
=DATE(YEAR(A1-WEEKDAY(A1-1)+4),1,4)=39817 (2009年1月4日) |
◎A1が属する週の木曜日＝A1の直前の日曜日の4日後
(直前の日曜日：上記WEEKDAY関数のポイントにある表参照)
|
=A1-WEEKDAY(A1-1)+4 (例 2009年12月31日=40178) =A1-WEEKDAY(A1,2)+4 (例 2009年12月31日=40178)
|
◎A1が属する週の木曜日の年
|
=YEAR(A1-WEEKDAY(A1-1)+4) (例 YEAR(40178)=2009) =YEAR(A1-WEEKDAY(A1,2)+4) (例 YEAR(40178)=2009)
|
●その年の1月4日が含まれる週=Week 1の、月曜日は何日か？
◎1月4日が属する週の月曜日は、12/29～1/4の7日間のうち、何れかで、
＝1月4日の直前の日曜日の1日後
＝1月5日の直前の月曜日 という式になります。
|
=DATE(YEAR(A1-WEEKDAY(A1-1)+4),1,4) -WEEKDAY(DATE(YEAR(A1-WEEKDAY(A1-1)+4),1,4)-1)+1 =DATE(YEAR(A1-WEEKDAY(A1-1)+4),1,5) -WEEKDAY(DATE(YEAR(A1-WEEKDAY(A1-1)+4),1,3)) (例 =39818-WEEKDAY(39816)=39818-7=39811 2009年12月29日) =DATE(YEAR(A1-WEEKDAY(A1,2)+4),1,4) -WEEKDAY(DATE(YEAR(A1-WEEKDAY(A1,2)+4),1,4),2)+1 =DATE(YEAR(A1-WEEKDAY(A1,2)+4),1,5) -WEEKDAY(DATE(YEAR(A1-WEEKDAY(A1,2)+4),1,4),2) (例 =39818-WEEKDAY(39817,2)=39818-7=39811 2009年12月29日) |
(3) Week 1の月曜日から変換したい日までの日数を7で割り、
小数点以下を切り捨てるとWeek 1のとき0になるので、1を足します。
|
=1+INT((A1-(DATE(YEAR(A1-WEEKDAY(A1-1)+4),1,5) -WEEKDAY(DATE(YEAR(A1-WEEKDAY(A1-1)+4),1,3))))/7) =1+INT((A1-DATE(YEAR(A1-WEEKDAY(A1-1)+4),1,5) +WEEKDAY(DATE(YEAR(A1-WEEKDAY(A1-1)+4),1,3)))/7) (例 =1+INT((40180-39818+7)/7)=1+INT(369/7) =1+INT(52.7...)=1+52=53 : Week 53) =1+INT((A1-(DATE(YEAR(A1-WEEKDAY(A1,2)+4),1,5) -WEEKDAY(DATE(YEAR(A1-WEEKDAY(A1,2)+4),1,4),2)))/7) =1+INT((A1-DATE(YEAR(A1-WEEKDAY(A1,2)+4),1,5) +WEEKDAY(DATE(YEAR(A1-WEEKDAY(A1,2)+4),1,4),2))/7) (例 =1+INT((40180-39818+7)/7)=1+INT(369/7) =1+INT(52.7...)=1+52=53 : Week 53) |
例の 2010年1月2日(土) は、Week 53 でした。
2 曜日基準システムの考え方
|
変換したい日と同じ年の1月1日が属する週における、 |
(1) A1：変換したい年月日のあるセル (例として 2010年1月6日(水)=40184 を使います)
(2) A1と同じ年の1月1日が属する週における、
週の始まりの日 (日曜始まりなら日曜日の日付) が、12月26～1月1日の何日か？
を計算します。
週の始まりが日曜日の場合は、
＝1月1日の直前の土曜日の1日後
＝1月2日の直前の日曜日
となるので、他の曜日を基準している場合も同様に考えると、
＝1月2日の直前の基準曜日
となり、
上記WEEKDAY関数のポイントにある表より
(A1=1月2日)-WEEKDAY(B1,Type)
|
B1 |
Type |
|
|
1 or 省略 |
2 |
|
|
A1 : 1月2日 |
直前の土曜日 |
直前の日曜日 |
|
A1-1 : 1月1日 |
直前の日曜日 |
直前の月曜日 |
|
A1-2 : 1月0日 (12/31) |
直前の月曜日 |
直前の火曜日 |
|
A1+4 : 1月6日 |
直前の火曜日 |
直前の水曜日 |
|
A1+3 : 1月5日 |
直前の水曜日 |
直前の木曜日 |
|
A1+2 : 1月4日 |
直前の木曜日 |
直前の金曜日 |
|
A1+1 : 1月3日 |
直前の金曜日 |
直前の土曜日 |
各基準となる曜日における週の始まりの日は、以下の表のようになります。
|
週の始まりの曜日 |
Type |
週の始まりの日の算出式 |
|
|
日曜日 |
1 or 省略 |
=DATE(YEAR(A1),1,2)-WEEKDAY(DATE(YEAR(A1),1,1)) (例 =40180-WEEKDAY(40179)=40180-6=40173 |
|
|
2 |
=DATE(YEAR(A1),1,2)-WEEKDAY(DATE(YEAR(A1),1,2),2) (例 =40180-WEEKDAY(40180,2)=40180-6=40173 |
||
|
月曜日 |
1 or 省略 |
=DATE(YEAR(A1),1,2)-WEEKDAY(DATE(YEAR(A1),1,0)) (例 =40180-WEEKDAY(40178)=40180-5=40174 |
|
|
2 |
=DATE(YEAR(A1),1,2)-WEEKDAY(DATE(YEAR(A1),1,1),2) (例 =40180-WEEKDAY(40179,2)=40180-5=40174 |
||
|
火曜日 |
1 or 省略 |
=DATE(YEAR(A1),1,2)-WEEKDAY(DATE(YEAR(A1),1,6)) (例 =40180-WEEKDAY(40177)=40180-4=40175 |
|
|
2 |
=DATE(YEAR(A1),1,2)-WEEKDAY(DATE(YEAR(A1),1,0),2) (例 =40180-WEEKDAY(40178,2)=40180-4=40175 |
||
|
水曜日 |
1 or 省略 |
=DATE(YEAR(A1),1,2)-WEEKDAY(DATE(YEAR(A1),1,5)) (例 =40180-WEEKDAY(40176)=40180-3=40176 |
|
|
2 |
=DATE(YEAR(A1),1,2)-WEEKDAY(DATE(YEAR(A1),1,6),2) (例 =40180-WEEKDAY(40177,2)=40180-3=40176 |
||
|
木曜日 |
1 or 省略 |
=DATE(YEAR(A1),1,2)-WEEKDAY(DATE(YEAR(A1),1,4)) (例 =40180-WEEKDAY(40175)=40180-2=40177 |
|
|
2 |
=DATE(YEAR(A1),1,2)-WEEKDAY(DATE(YEAR(A1),1,5),2) (例 =40180-WEEKDAY(40176,2)=40180-2=40177 |
||
|
金曜日 |
1 or 省略 |
=DATE(YEAR(A1),1,2)-WEEKDAY(DATE(YEAR(A1),1,3)) (例 =40180-WEEKDAY(40174)=40180-1=40178 |
|
|
2 |
=DATE(YEAR(A1),1,2)-WEEKDAY(DATE(YEAR(A1),1,4),2) (例 =40180-WEEKDAY(40175,2)=40180-1=40178 |
||
|
土曜日 |
1 or 省略 |
=DATE(YEAR(A1),1,2)-WEEKDAY(DATE(YEAR(A1),1,2)) (例 =40180-WEEKDAY(40180)=40180-7=40173 |
|
|
2 |
=DATE(YEAR(A1),1,2)-WEEKDAY(DATE(YEAR(A1),1,3),2) (例 =40180-WEEKDAY(40174,2)=40180-7=40173 |
(3) 各基準となる曜日における週の始まりの日から変換したい日までの日数を7で割り、
小数点以下を切り捨てるとWeek 1のとき0になるので、1を足します。
＜WEEKDAY関数で、Type=1 or 省略 を使った場合＞
|
日=1+INT((A1-DATE(YEAR(A1),1,2) +WEEKDAY(DATE(YEAR(A1),1,1)))/7) |
＜WEEKDAY関数で、Type=2 を使った場合＞
|
日=1+INT((A1-DATE(YEAR(A1),1,2) +WEEKDAY(DATE(YEAR(A1),1,2),2))/7) |
3 単純週番号システムの考え方
|
変換したい日と同じ年の1月1日から 変換したい日までの日数を7で割り、 |
|
|
週の始まりの曜日 |
算出式 |
|
単純週番号 |
1月1日の曜日 |
=1+INT((A1-DATE(YEAR(A1),1,1))/7) |
通常日本で使う日曜始まりや月曜始まりの月間カレンダーでは
週の途中で、週番号が変わることになります。
関連記事 -------------------------
√ 週番号 Week xx ってどんな風に決まっている？√ エクセルで週番号から日付を計算してみる
√ iPhoneカレンダーへ祝日やwebcal(.ics)の追加と削除
最近のコメント