エクセルにて「週」から日付を逆引きしたい

質問

WEEKNUM関数などで、ある日付からその日が第何週にあたるか算出する方法はわかったのですが、
その逆に対象の「週」からその週に当てはまる日付を算出したいと思っています。

A1に４０（週）と入れるとB1～H1に9/26～10/1の日付が表示されるような形を考えています。

今は別シートにテーブルをあらかじめ作っておいてVLOOKUPで週をキーにして見に行くようにしていますが、
テーブルを作るのも大変ですし、もうすこしスマートな方法はありませんでしょうか？

回答No.3

zenjee · ベストアンサー

カレンダーでは通常一番左の列が日曜となっているので、それを前提にお答えします。
まず、表の配列ですがA列を第何週かを入力する列とします。
見出し行としてB1～H1に曜日（日・月・火・水・木・金・土）を入力します。
第何週になるかは年によって異なるので、まず2005年の式から始めます。
B2に次の式を入力します。
=IF($A2="","",DATE(2005,1,1)+7*($A2-1)-6)
C2に
=IF($A2="","",B2+1)
と入力し、右方向にH2までコピーします。これで2005年は完了です。

次に2006年です。まずB3に
=IF($A3="","",DATE(2006,1,1)+7*($A3-1))
と入力します。
次にC3に
=IF($A3="","",B3+1)　と入力し、その式を右にH3までコピーします。
以上で完了です。年別に2行目は2005年分、3行目は2006年分ですからA列に数値を入力して試して見てください。

2007年になるとB列の式が次のように変わります。
=IF($A63="","",DATE(2007,1,1)+7*($A63-1)-1)
C列からH列までの式は毎年不変です。

このように今年の第○週は何月何日になるかというのは、その年の元日を起点とする以上、毎年少しずつ変わらざるを得ないでしょう。

お礼

ご回答ありがとうございました。
わかりやすい手順まで書いていただき助かります。
日曜日から始まるスタイルを普段使用していますのでとても見やすいです。

やはり起点の元旦の曜日を考慮した式を使ったほうがずれないですし確実ですね。
こちらを応用させていただきたいと思います。

回答No.2

imogasi

考え方
シリアル値ということを常に念頭におきます。
２００５年の例とします。
第４０週を例にします。
年末日のシリアル値　　=DATEVALUE("2005/1/1")-1
年初日の曜日
↑
第１週　普通は７日に対し、端数日数
↓｜
初めの月曜日（または日曜日）月曜日を週の開始日にとる方式にします。
↑
第２週
｜
以下　７日（月曜日から日曜日まで）ごとの繰り返し
｜
｜
・・・７＊（４０－１－１）日　ー１は年初の半端、－１は４０の前週
　　　　　　　　　　　　　　　のためにー１　。３８週分　
｜
｜
↓
第３９週の終わり日
第４０週の始まり日
↑
７日（または６日分＝質問の例）
式にすると
=DATEVALUE("2005/1/1")-1+((40-2)*7)+8-WEEKDAY("2005/1/1",2)+ROW()
をA1に入れてA2以下に６（ないし７）行複写。
（結果）
2005/9/26
2005/9/27
2005/9/28
2005/9/29
2005/9/30
2005/10/1
４０のところをセルの値にすれば、変えると任意の第X週が計算できる。

お礼

ご回答ありがとうございました。
No1の方と同じようにコチラの式も
（このまま使用すると）2006年の第一週がずれてしまうようです。

回答No.1

mshr1962

年の開始日(1月1日)の曜日で計算できます。
B1=DATE(YEAR(TODAY()),1,(A1-1)*7+1-WEEKDAY(DATE(YEAR(TODAY()),1,1),3))
C1=B1+1
D1=C1+1
..
H1=G1+1

お礼

早い回答ありがとうございました。
YEAR関数をセル指定にすれば他の年にも使用できますね。
ただ、２００６年の第1週がずれてしまうようです。
シンプルでとてもわかりやすいのでいろいろ応用できるとおもいます。