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

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

【Excel関数】カレンダー、週間スケジュールの日付を自動で表示する方法


Excelでカレンダーを作る場合、年月を変えたときに自動的に日にちが変わるようにすることができます。12か月分の年間カレンダーを作る場合は、1か月分だけ作って残りの11か月分はコピーをするだけで完成させることができます。

ところで、カレンダーを作る方法は2通りあります。カレンダーの左上の日付を基準として作る方法と、右上の日付を基準として作る方法です。また、カレンダーには日曜始まりと月曜始まりがあります。

そこで、今回はカレンダーの日付を自動的に表示する方法について出題します。なお、休日・祝祭日や連休の扱いについては別の記事で解説します。

f:id:waenavi:20191116010139j:plain

目次

1.月間予定表

縦長または横長の月間予定表の作り方については、こちらの記事をご覧ください。

 

2.カレンダーの初日を求める

(1)日曜始まりの場合

問題

セルA1に月の初めの日付を入力した。日曜始まりのカレンダーを作成する場合、左上の日付を求めなさい。

f:id:waenavi:20191115201151j:plain

 

また、セルA1とA2に年月を入力した場合はどうか。

f:id:waenavi:20191115200940j:plain

 

解説

7列のカレンダーを作成するときは、まず左上の日付を求めます。日曜始まりの場合、左上の日付は前月の最終日曜日ですが、当月の1日が日曜日の場合は、その1日が左上の日付となります。

f:id:waenavi:20191115230659j:plain

 

この日付の求め方は別の記事で解説していますが、簡単に言えば、前月末の日付を7の倍数で切り捨てると前月の最終土曜日になるので、それに1を足せばよいです(参考:【Excel関数】第1、第2、最終などの特定の曜日の日付を求める計算方法まとめ)。

  • 左上=FLOOR(前月末,7)+1

f:id:waenavi:20191115230813j:plain

 

月初の日付を入力しているので1を引くと前月末になります。そして、7の倍数で切り捨てて1を足します。

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

f:id:waenavi:20191115224331j:plain

 

2022年5月にすると5月1日が左上の日付となります。

f:id:waenavi:20191115224450j:plain

 

年と月を入力した場合、前月末の日付はDATE関数で日にちを0にすればよいです。つまり「DATE(年,月,0)」です。

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

f:id:waenavi:20191115224624j:plain

 

(2)月曜始まりの場合

問題

セルA1に月の初めの日付を入力した。月曜始まりのカレンダーを作成する場合、左上の日付を求めなさい。

f:id:waenavi:20191115230331j:plain

 

また、セルA1とA2に年月を入力した場合はどうか。

f:id:waenavi:20191115225536j:plain

 

解説

月曜始まりの場合、左上の日付は前月の最終月曜日ですが、当月の1日が月曜日の場合は、その1日が左上の日付となります。日曜始まりの図とほとんど同じです。

f:id:waenavi:20191115230924j:plain

 

そして、前月末の1日前を基準として考えたら、日曜始まりと同じように考えられます。つまり、前月末の日付から1を引き、7の倍数で切り捨ててから2を足せばよいです。

  • 左上=FLOOR(前月末-1,7)+2

f:id:waenavi:20191115231309j:plain

 

月初の日付から2を引き、7の倍数で切り捨てて2を足します。

  • =FLOOR(A1-2,7)+2

f:id:waenavi:20191115230408j:plain

 

2021年11月にすると11月1日が左上の日付となります。

f:id:waenavi:20191115230519j:plain

 

年と月を入力した場合、前月末の1日前の日付はDATE関数で日にちを-1にすればよいです。つまり「DATE(年,月,-1)」です。

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

f:id:waenavi:20191115230432j:plain

 

3.左上を基準とするカレンダーの作成

問題

セルA1に月の初めの日付を入力し、左上の日付を求めた。日曜始まりのカレンダーを作成しなさい。

f:id:waenavi:20191115231816j:plain

 

また、前月と翌月の日付については条件付き書式によりフォントの色を白色にしなさい。

f:id:waenavi:20191115232845j:plain

 

解説

(1)翌日と1週間後

翌日は1を加算します。

f:id:waenavi:20191115232057j:plain

 

次の週以降は上のセルに7を加算すればよいです。これは月曜始まりでも同じです。第6週まで求めます。

f:id:waenavi:20191115232152j:plain

 

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

f:id:waenavi:20191115232315j:plain

 

日付の表示形式を「d」にします。

f:id:waenavi:20191115232449j:plain

 

(2)前月と翌月を白色にする

条件付き書式でセルA1に入力した日付と異なる場合にフォントを白色にします。条件付き書式、新しいルールで「数式を使用して、書式設定するセルを決定」にします。

f:id:waenavi:20191115232605j:plain

 

「=month($a$1)<>month(a4)」と入力して、書式のボタンをクリックします。セルA1は絶対参照、セルA4は相対参照です。

f:id:waenavi:20191115232636j:plain

 

フォントの色を白色にします。

f:id:waenavi:20191115232721j:plain

 

これで完成です。

f:id:waenavi:20191115232756j:plain

 

他の月にして日にちが変わることを確認します。

f:id:waenavi:20191115232845j:plain

 

4.週間予定表の作り方

問題

2022/2/7(月)~2022/2/13(日)の日付と曜日を表示しなさい。また、2022年の第35週(月曜始まり)の日付と曜日を表示しなさい。

f:id:waenavi:20191115234315j:plain

 

解説

2022/2/7と入力します。

f:id:waenavi:20191115233403j:plain

 

翌日以降は+1です。

f:id:waenavi:20191115233539j:plain

 

表示形式を「m/d(aaa)」にします。

f:id:waenavi:20191115233621j:plain

 

月曜始まりの場合、週番号から月曜日を求める計算式は次の通りです(参考:【Excel関数】先週、今週、翌週の曜日の日付、週番号、今週の日付の判定)。

  • =FLOOR(DATE(年,1,週番号*7-1),7)-5
  • =FLOOR(DATE(A21,1,A22*7-1),7)-5

f:id:waenavi:20191115233920j:plain

 

翌日以降は+1です。

f:id:waenavi:20191115234039j:plain

 

週番号を1つ増やすと、翌週の日付になります。

f:id:waenavi:20191115234236j:plain

 

5.カレンダーの右上の日付を求める

(1)日曜始まりの場合

問題

セルA1に月の初めの日付を入力した。日曜始まりのカレンダーを作成する場合、右上の日付を求めなさい。

f:id:waenavi:20191115234741j:plain

 

また、セルA1とA2に年月を入力した場合はどうか。

f:id:waenavi:20191115234804j:plain

 

解説

今度は、カレンダーの右上の日付を最初に求めてみましょう。日曜始まりの場合、左上の日付は当月の第1土曜日です。第1土曜日は、月初の日付(1日)を7の倍数で切り上げるだけで求められます(参考:【Excel関数】第1、第2、最終などの特定の曜日の日付を求める計算方法まとめ)。

  • 右上=CEILING(月初,7)
  • 右上=CEILING(A1,7)

f:id:waenavi:20191115235038j:plain

 

2021年5月にすると5月1日が右上の日付となります。

f:id:waenavi:20191115235106j:plain

 

年と月を入力した場合、月初は「DATE(年,月,1)」です。

  • =CEILING(DATE(A1,A2,1),7)

f:id:waenavi:20191115235147j:plain

 

(2)月曜始まりの場合

問題

セルA1に月の初めの日付を入力した。月曜始まりのカレンダーを作成する場合、左上の日付を求めなさい。

f:id:waenavi:20191115235628j:plain

 

また、セルA1とA2に年月を入力した場合はどうか。

f:id:waenavi:20191115235645j:plain

 

解説

月曜始まりの場合、右上の日付は第1日曜日ですが、前月末の日付を切り上げて1を足します。

  • 左上=CEILING(前月末,7)+1
  • =CEILING(A1-1,7)+1

f:id:waenavi:20191115235556j:plain

 

2021年8月にすると8月1日が左上の日付となります。

f:id:waenavi:20191115235726j:plain

 

年と月を入力した場合、前月末の日付はDATE関数で日にちを0にすればよいです。

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

f:id:waenavi:20191115235746j:plain

 

6.右上を基準とするカレンダーの作成

問題

セルA1に月の初めの日付を入力し、右上の日付を求めた。日曜始まりのカレンダーを作成しなさい。ただし、前月と翌月の日付については空白にしなさい。

f:id:waenavi:20191116001825j:plain

 

解説

(1)第1週はさかのぼる

前日は1を引きます。

f:id:waenavi:20191116000309j:plain

 

左へオートフィルをします。先月の日付が表示されます。

f:id:waenavi:20191116000418j:plain

 

(2)月末を超えたら空白にする

これを空白にするには2つの処理が必要です。

  • 「1日」だったら空白にする
  • それより前も空白にする

月初ならDAY=1です。

  • =IF(DAY(G4)=1,"",G4-1)

f:id:waenavi:20191116000643j:plain

 

月末を空白にしたのでその左側はエラーになります(DAY関数のエラー)。IFERRORで空白にすればよいです。これで先月の部分が空白になります。

  • =IFERROR(IF(DAY(G4)=1,"",G4-1),"")

f:id:waenavi:20191116000709j:plain

 

次の週の日曜日の日付を求めようと思っても、上のセルが空白なので、土曜日の日付に1を足します。

f:id:waenavi:20191116000856j:plain

 

月曜日以降は+1です。

f:id:waenavi:20191116000937j:plain

 

(3)第3週以降について

第3週以降は上のセルに7を加算します。第6週まで求めると、翌月の日付が表示されます。

  • =A5+7

f:id:waenavi:20191116001242j:plain

 

翌月の日付を空白にするには2つの処理が必要です。

  • 前週に7を加算して月末を超えたら空白にする
  • それ以降も空白にする

月末はEOMONTH(A5,0)です。

  • =IF(A5+7>EOMONTH(A5,0),"",A5+7)

f:id:waenavi:20191116001406j:plain

 

月を変えても正しいカレンダーが表示されることを確認します。

f:id:waenavi:20191116001442j:plain

 

空白にするとその下のセルはエラーになります(EOMONTH関数のエラー)。IFERRORで空白にすればよいです。

  • =IFERROR(IF(A5+7>EOMONTH(A5,0),"",A5+7),"")

f:id:waenavi:20191116001538j:plain

 

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

f:id:waenavi:20191116001624j:plain

 

日にちの表示形式を「d」にします。

f:id:waenavi:20191116001751j:plain

 

7.総合問題演習:年間カレンダー

問題

セルA1に年度を表す4桁の数値を入力した。4月から6月までのカレンダーを作成しなさい。

f:id:waenavi:20191116005452j:plain

 

解説

年度から4月1日の日付を求めます。

  • =DATE(A1,4,1)

f:id:waenavi:20191116005554j:plain

 

表示形式を「m月」にします。

f:id:waenavi:20191116005637j:plain

 

右上の日付を求めます。

  • =CEILING(B3,7)

f:id:waenavi:20191116005713j:plain

 

列幅が狭いと#####になります。表示形式を「d」にします。

f:id:waenavi:20191116005744j:plain

f:id:waenavi:20191116005813j:plain

 

前日の日付を求めます。

  • =IFERROR(IF(DAY(H5)=1,"",H5-1),"")

f:id:waenavi:20191116005844j:plain

 

左へオートフィルをします。

f:id:waenavi:20191116005901j:plain

 

日曜日の日付を求めます。

f:id:waenavi:20191116005940j:plain

 

第2週の日付を求めます。

f:id:waenavi:20191116010011j:plain

 

第3週以降の日付を求めます。

  • =IFERROR(IF(B6+7>EOMONTH(B6,0),"",B6+7),"")

f:id:waenavi:20191116010109j:plain

 

次に5月のカレンダーを作ります。5月1日の日付は4月1日の1か月後なのでEDATE関数で求めることができます。

  • =EDATE(B3,1)

f:id:waenavi:20191116010139j:plain

 

4月の日にちはすべて相対参照で求めているので、そのまま5月にコピーして使うことができます。

f:id:waenavi:20191116010322j:plain

 

6月についてはEDATE関数の部分も含めてすべてコピーして使うことができます。

f:id:waenavi:20191116010354j:plain

 

これで完成です。

f:id:waenavi:20191116010715j:plain

 

3か月後の場合は「=EDATE(B3,3)」です。

f:id:waenavi:20191116010525j:plain

 

これを8回コピーすれば12か月分のカレンダーになります。

f:id:waenavi:20191116010629j:plain

 

年度を変えるだけで翌年度のカレンダーになります。

f:id:waenavi:20191116010833j:plain

 

8.休日祝日の処理

休日や祝日、年末年始休業等の特別な予定の処理については別の記事で解説します。 

 


解説は以上です。祝日の処理編へつづく・・・


 


Copyright(C)2018-2021 waenavi, All rights reserved. [www.waenavi.com 定礎 平成三十年八月]
Youtube | 公式サイト | twitter | 著作権 | 運営者情報 | お問い合わせ