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

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

【Excel】月間予定表の作り方、日付や曜日を自動で変更して色も変更する

Excelで月間予定表や年間スケジュールを作成するときは、日付と曜日を自動で表示するように設定します。そして、月が替わったら自動的に日付と曜日が変わるようにしたほうが良いです。曜日によってをつける場合も、Excelの条件付き書式を使えば自動的に色を変えることができます。

このような処理を自動で行うのに最初に理解すべきことは、Excelで「1」と直接入力するのと、セルに「1」と表示するのは異なるということです。

そこで、今回は、月間予定表の正しい作成方法と色を付けるときの注意点について出題します。なお、カレンダーの作り方については別の記事で解説します。

目次

1.曜日の変換と判定の基本

TEXT関数、WEEKDAY関数を使って、曜日の変換と判定をする方法の基本についてはこちらの記事をご覧ください。

 

2.数値の入力と日にちの表示の違い

問題

(1)「1」「2022/1/1」「2022/11/1」と入力しなさい。
(2)表示形式を「d」にしなさい。
(3)表示形式を「aaa」にしなさい。
(4)セルに「1日」と入力して曜日に変換することは可能か。 

解説

Excelに「1」「2022/1/1」「2022/11/1」と入力します。

f:id:waenavi:20191115100634j:plain

 

左隣のセルを参照します。

f:id:waenavi:20191115100855j:plain

 

表示形式を「d」にします。セルの表示はすべて「1」になります。表示形式を「d」にすると年と月が非表示になります。非表示になっているだけで、セルには年と月の情報があり、セルに入力されている値は1ではなく、日付のシリアル値です。

f:id:waenavi:20191115100954j:plain

 

表示形式を「aaa」にします。曜日が表示されます。数値としての「1」を入力すると、7で割ったときの余りに対応する曜日が表示されます(参考:【Excel】日付から曜日を自動で表示するのにWEEKDAY関数を使ってはいけない)。しかし、年と月の情報が無いので、特定の日の曜日を示しているわけではありません。

f:id:waenavi:20191115101120j:plain

 

日付(シリアル値)が入力されているセルの場合は、その日付(シリアル値)に対応する曜日が表示されます。土曜日は2022/1/1の曜日であり、火曜日は2022/11/1の曜日です。

f:id:waenavi:20191115103914j:plain

 

このように、曜日を判定する場合は数値を直接入力するのではなく、日付を年月日で入力して、表示形式を「d」にしなければなりません。

f:id:waenavi:20191115104558j:plain

 

「1日」と入力しても年と月の情報がないので曜日の判定のしようがありません。

f:id:waenavi:20191115104731j:plain

 

年月日を入力します。

f:id:waenavi:20191115104940j:plain

 

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

f:id:waenavi:20191115105200j:plain

 

左隣のセルを参照します。

f:id:waenavi:20191115105353j:plain

 

表示形式を「aaa」に変えます。これは正しい曜日です。繰り返しになりますが、「1日」と入力するのと「1日」と表示するのは違います。

f:id:waenavi:20191115105500j:plain

 

3.縦型の月間スケジュール

問題

次の図のように2か月間の予定表を作成しなさい。

f:id:waenavi:20191115142510j:plain

 

解説

(1)初日の日付から1か月間の日付と曜日を求める

セルA1に「2020/1/1」と入力します。

f:id:waenavi:20191115105901j:plain

 

A1を参照します。

f:id:waenavi:20191115110149j:plain

 

幅が狭いので#####となります。

f:id:waenavi:20191115112006j:plain

 

A列の表示形式を「d」にします。

f:id:waenavi:20191115112106j:plain

 

上のセルに1を足します。

f:id:waenavi:20191115112140j:plain

 

31日まで表示します。数値の1~31が表示されていますが、セルに入力されている値はシリアル値です。

f:id:waenavi:20191115112208j:plain

 

左隣のセルを参照します。

f:id:waenavi:20191115112321j:plain

 

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

f:id:waenavi:20191115112412j:plain

 

この曜日は、2020年1月1日~31日のシリアル値に基づいて算出された正しい曜日です。

f:id:waenavi:20191115112937j:plain

 

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

f:id:waenavi:20191115112556j:plain

 

セルA1を「2020/2/1」にすると2月のカレンダーになります。この曜日は2020年2月の正しい曜日です。

f:id:waenavi:20191115112647j:plain

 

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

2020年2月29日(うるう年)の次の日は3月1日なので、翌月の日付が表示されます。

f:id:waenavi:20191115112858j:plain

 

これを表示しないようにするには、2つの処理が必要です。

  • 日にちが「1」になったら空白にする
  • 日にちが「2」以降も空白にする

まず、翌月月初ならDAY=1です。

  • =IF(DAY(A4+1)=1,"",A4+1)

f:id:waenavi:20191115113156j:plain

 

月初を空白にしたので、2日以降はエラーになります(DAY関数のエラー)。

f:id:waenavi:20191115113251j:plain

 

IFERRORで空白にすればよいです。

  • =IFERROR(IF(DAY(A4+1)=1,"",A4+1),"")

f:id:waenavi:20191115113416j:plain

 

これで30日、31日の部分が空白になります。

f:id:waenavi:20191115113454j:plain

 

別解

月が異なっていたら空白にするという方法もあります。

  • =IFERROR(IF(MONTH(A4)=MONTH(A4+1),A4+1,""),"")

f:id:waenavi:20191115113531j:plain

 

(3)コピーして翌月のカレンダーを作る

2月のカレンダーをコピーします。

f:id:waenavi:20191115113618j:plain

 

先頭の日付を2020/3/1に変更します。

f:id:waenavi:20191115113645j:plain

 

コピーした表はすべて相対参照なので、自動的に3月の日付と曜日になります。

f:id:waenavi:20191115142510j:plain

 

別解

翌月の初日を関数で求めるにはEDATE関数を使います。セルA1の1か月後の日付を求めます。

  • =EDATE(A1,1)

f:id:waenavi:20191115142604j:plain

 

セルA1を12/1にすると、12月と翌年1月のカレンダーになります。

f:id:waenavi:20191115142752j:plain

 

4.特定の年と月から月間予定表を作る場合

問題

セルA1に西暦年、セルB1に月を入力して3か月間の予定表を作りなさい。

f:id:waenavi:20191115142849j:plain

 

解説

さきほどの予定表では一番上のセルに月初の日付を直接入力しました。特定の年月を入力した場合は、DATE関数で1日の日付を求めます(表示形式「d」)。

  • =DATE(A1,A2,1)

f:id:waenavi:20191115143041j:plain

 

2日目以降の日付を求めます。

  • =IFERROR(IF(DAY(A5+1)=1,"",A5+1),"")

f:id:waenavi:20191115143238j:plain

 

左隣のセルを参照します(表示形式「aaa」)。

f:id:waenavi:20191115143351j:plain

 

日付と曜日をコピーするとエラーになります。初日がエラーなので日付も曜日も表示できません。

f:id:waenavi:20191115144116j:plain

 

翌月の1日はEDATE関数で求めます。

  • =EDATE(A5,1)

f:id:waenavi:20191115143609j:plain

 

これで翌月の日付と曜日が正しく表示されます。

f:id:waenavi:20191115143650j:plain

 

来月の年月は、初日の日付からYEARとMONTHで求めます。

  • =YEAR(E5)
  • =MONTH(E5)

f:id:waenavi:20191115143759j:plain

 

3月の予定表の年、月、EDATE、日付、曜日をまるごとコピーすると、相対参照によって自動的に4月のカレンダーになります。

f:id:waenavi:20191115143840j:plain

 

そして、月末も処理されていることを確認します。

f:id:waenavi:20191115144222j:plain

 

5.月の途中から始まる予定表

問題

2020年7月24日金曜日から同年8月9日日曜日まで開催されるイベントの開催日数を求め、この期間の予定表を作りなさい。

f:id:waenavi:20191115145112j:plain

 

解説

1日から始まる表ではなく、月の途中から始まる場合も作り方は同じです。まず、開催日数を計算します。開始日と終了日が分かっている場合の日数は「終了日-開始日+1」です(参考:【Excel】完全マスター!日付の足し算と引き算を使った日数の計算問題)。17日間であることが分かります。

  • =B2-A2+1
  • "2020/8/9"-"2020/7/24"+1

f:id:waenavi:20191115144755j:plain

 

17行x2列の表を用意します。

f:id:waenavi:20191115144819j:plain

 

開始日を参照します。

f:id:waenavi:20191115144858j:plain

 

1を足します。

f:id:waenavi:20191115144938j:plain 

 

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

f:id:waenavi:20191115145047j:plain

 

6.いろいろな色の変え方

(1)曜日だけ色を変える

問題

さきほど作成した2か月間の予定表で、土曜日を青色、日曜日を白色の文字にして、日曜日については塗りつぶしの色を赤色にしなさい。

f:id:waenavi:20191115145620j:plain

 

解説

セルに表示されているのは日本語の曜日ですが、実際にセルに入力されている値はシリアル値(整数値)です。そして、曜日を判定する関数はWEEKDAY関数です(参考:【Excel】日付から曜日を自動で表示するのにWEEKDAY関数を使ってはいけない)。

  • 日曜・・・WEEKDAY=1
  • 月曜・・・WEEKDAY=2
  • 火曜・・・WEEKDAY=3
  • 水曜・・・WEEKDAY=4
  • 木曜・・・WEEKDAY=5
  • 金曜・・・WEEKDAY=6
  • 土曜・・・WEEKDAY=7

曜日を選択します。このとき、白くなっているセルがB4であることを確認します。

f:id:waenavi:20191115145729j:plain

 

このとき、30日と31日の部分は空白になっていますが、数式で強制的に空白にしているだけなので選択範囲に含めます。

f:id:waenavi:20191115145758j:plain

 

条件付き書式、新しいルールで「数式を使用して、書式設定するセルを決定」にします。

f:id:waenavi:20191115145901j:plain

f:id:waenavi:20191115145933j:plain

 

「=weekday(b4)=7」と入力して、書式のボタンをクリックします。セルB4は相対参照です。

f:id:waenavi:20191115150110j:plain

 

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

f:id:waenavi:20191115150148j:plain

 

土曜日が青色になります。

f:id:waenavi:20191115150223j:plain

 

さらに、新規のルールで「数式を使用して、書式設定するセルを決定」にします。「=weekday(b4)=1」と入力して、書式のボタンをクリックします。

f:id:waenavi:20191115150309j:plain

 

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

f:id:waenavi:20191115150353j:plain

 

塗りつぶしを赤色にします。

f:id:waenavi:20191115150427j:plain

 

これで、土曜と日曜の色が変わります。

f:id:waenavi:20191115150451j:plain

 

2月の曜日を3月にコピーします。

f:id:waenavi:20191115150515j:plain

 

先頭の日付を4月にすると2か月分の曜日が変わって、色も変わります。

f:id:waenavi:20191115150551j:plain

 

(2)条件付き書式のクリア

問題

曜日の部分に設定されている条件付き書式をすべてクリアしなさい。 

解説

曜日の部分を選択します。

f:id:waenavi:20191115150638j:plain

 

条件付き書式、ルールのクリアのなかから、選択したセルからルールをクリアを選びます。

f:id:waenavi:20191115150716j:plain

 

これで色が消えます。

f:id:waenavi:20191115150810j:plain

 

(3)土日の行を網掛けにする

問題

土日の日付と曜日をグレー、25%灰色のパターン(網掛けの塗りつぶし)にしなさい。

f:id:waenavi:20191115151121j:plain

 

解説

2列選択します。

f:id:waenavi:20191115151304j:plain

 

条件付き書式、新しいルールで「数式を使用して、書式設定するセルを決定」にします。

f:id:waenavi:20191115145933j:plain

 

WEEKDAY関数は第2引数を2にすると、土曜日=6、日曜日=7を返します。

  • 第2引数=2・・・1 (月曜)~ 7 (日曜)

「=weekday(a4)>=6」と入力して、書式のボタンをクリックします。セルA4は相対参照です。

f:id:waenavi:20191115151441j:plain

 

塗りつぶしでパターンの色をグレーにします。

f:id:waenavi:20191115151527j:plain

 

パターンの種類を25%灰色にします。

f:id:waenavi:20191115151556j:plain

 

これで日にちと曜日の塗りつぶしができます。

f:id:waenavi:20191115151645j:plain

 

4月の日にちと曜日を5月にコピーします。これで完成です。

f:id:waenavi:20191115151815j:plain

 

 

(4)行全体に条件付き書式を設定してはいけない

問題

次の図のように、予定を記入する欄に条件付き書式を設定しなさい。また、この問題点を指摘しなさい。

f:id:waenavi:20191115151934j:plain

 

解説

日付と曜日は数式が入力されていて、予定は自由記入欄です。

f:id:waenavi:20191115152011j:plain

 

予定にはシリアル値が入っていないので、条件付き書式で相対参照をすることができません。特定の列を基準として、行全体に条件付き書式を設定するには、数式の列番号を固定します。A列を固定します。

  • =WEEKDAY($A4,2)>=6

f:id:waenavi:20191115152112j:plain

f:id:waenavi:20191115152316j:plain

 

ところが、この条件付き書式をコピーすると、同じ行がグレーになってしまいます(土日がグレーにならない)。

f:id:waenavi:20191115152349j:plain

 

この部分の条件付き書式を修正します。A列を固定したのが原因です。条件付き書式の管理でこれをE列に変更します。

  • =WEEKDAY($E4,2)>=6

f:id:waenavi:20191115152613j:plain

f:id:waenavi:20191115152655j:plain

 

これで修正できました。

f:id:waenavi:20191115151934j:plain

 

ところで、土日でグレーになっているセルをコピーします。

f:id:waenavi:20191115153134j:plain

 

これを翌月の土日にコピーするとグレーが消えてしまいます。これはA列の日付を基準として色を決定している条件付き書式がコピーされたからです。

f:id:waenavi:20191115153320j:plain

 

また、切り取り・貼り付けをするとグレーが消えることもあります。

f:id:waenavi:20191115153506j:plain

 

このように、条件付き書式も書式の一種なので、編集をしている最中に、無意識のうちに壊れることがあります。書式をコピーせず値や数式だけをコピーすれば問題ないのですが、誤って書式も含めてコピーしても気がつかないことがあります

f:id:waenavi:20191115154329j:plain

 

数式が入力されていない、コピー・切り取り・貼り付けを行う可能性のある列(自由に記入できる欄)に対して、条件付き書式を設定するのはできるだけ避けたほうが良いです。

f:id:waenavi:20191115153937j:plain

 

7.横型のスケジュール表

問題

縦に氏名、横に日付を入力して月間予定表を作ろうとしている。横方向に日付と曜日を表示し、土日の列についてはグレーで色を塗りつぶしなさい。

f:id:waenavi:20191115154724j:plain

 

解説

(1)日付と曜日を求める

縦型も横型も基本的な考え方は同じです。セルA1に1日の日付を入力します。

f:id:waenavi:20191115154916j:plain

 

参照します。列幅が狭いので#####になります。

f:id:waenavi:20191115154952j:plain

f:id:waenavi:20191115155028j:plain

 

3行目の表示形式を「d」にします。

f:id:waenavi:20191115155114j:plain

 

その翌日は1を加算します。このときに月末の処理をしておきます。

  • =IFERROR(IF(DAY(B3+1)=1,"",B3+1),"")

f:id:waenavi:20191115155155j:plain

 

上のセルを参照します。

f:id:waenavi:20191115155228j:plain

 

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

f:id:waenavi:20191115155304j:plain

 

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

f:id:waenavi:20191115155343j:plain

 

最後に年月を変えて、曜日と月末の処理があっているか確認します。

f:id:waenavi:20191115161158j:plain

f:id:waenavi:20191115161201j:plain

 

(2)土日を塗りつぶす

氏名欄を除く表全体を選択します。

f:id:waenavi:20191115161257j:plain

 

条件付き書式、新しいルールで「数式を使用して、書式設定するセルを決定」にします。

f:id:waenavi:20191115145933j:plain

 

3行目を固定して「=WEEKDAY(b$3,2)>=6」と入力して、書式のボタンをクリックします。

f:id:waenavi:20191115161430j:plain

 

塗りつぶしをグレーにします。

f:id:waenavi:20191115161648j:plain

 

これで土日の列がグレーになります。

f:id:waenavi:20191115154724j:plain

 

(3)条件付き書式の注意点

このように、やむを得ず列全体に対して条件付き書式を設定することがあります。この場合は、条件付き書式が壊れないように注意します(値や数式の貼り付けなど)。特に、複数の人で共有するファイルの場合は、使用するすべての人が条件付き書式の正しい知識を身につけ、いつでも条件付き書式が適切に修正できるようにしなければなりません

f:id:waenavi:20191115161843j:plain

 

8.年間予定表

問題

セルA1に4桁の西暦年を入力した。

f:id:waenavi:20191115162503j:plain

 

4月始まりの年間予定表を作成しなさい。

f:id:waenavi:20191115162715j:plain

 

解説

上記のことを踏まえて、12か月分の年間予定表を作ってみましょう。年度の初日は、DATE関数で「=DATE(A1,4,1)」です。これをセルA3に入力します。列幅が狭い場合は#####になります。

f:id:waenavi:20191115162850j:plain

 

3行目の表示形式を「m月」にします。4月と表示されますが実際には4/1の日付が入っています。

f:id:waenavi:20191115163044j:plain

 

翌月の初日はEDATE関数で求めます。5月と表示されますが実際には5/1の日付が入っています。

  • =EDATE(A3,1)

f:id:waenavi:20191115163131j:plain

 

 

2つのセルを選択します。右向きにオートフィルをすると、翌年の3月まで求めることができます。

f:id:waenavi:20191115163214j:plain

f:id:waenavi:20191115163217j:plain

 

セルA3を参照します。

f:id:waenavi:20191115163246j:plain

 

表示形式を「d aaa」にします。

f:id:waenavi:20191115163324j:plain

 

2日目以降は上のセルに1を加算します。また、月末の処理をします。

  • =IFERROR(IF(DAY(A5+1)=1,"",A5+1),"")

f:id:waenavi:20191115163355j:plain

 

条件付き書式を設定します。条件付き書式、新しいルールで「数式を使用して、書式設定するセルを決定」にします。「=weekday(a5,2)>=6」と入力して、書式のボタンをクリックします。セルA5は相対参照です。

f:id:waenavi:20191115163550j:plain

 

グレーの塗りつぶしをします。

f:id:waenavi:20191115161648j:plain

 

これで土日がグレーになります。

f:id:waenavi:20191115163635j:plain

 

最後に、1日~31日の日にち、曜日、予定を選択します。

f:id:waenavi:20191115163701j:plain

 

右向きにオートフィルをします。翌年3月までの日付と曜日が求められます。これで完成です。

f:id:waenavi:20191115163753j:plain

 

月末の日付を確認します。

f:id:waenavi:20191115163934j:plain

 

セルA1を2021にすると、2021年度のカレンダーになります。

f:id:waenavi:20191115163826j:plain

 

9.定期的なスケジュールの自動入力

(1)週に1回の場合

問題

2か月間の予定表を作成した。

f:id:waenavi:20191115164054j:plain

 

列を増やして、火曜と金曜なら「可燃」、木曜なら「プラスチック」と表示しなさい。

f:id:waenavi:20191115164255j:plain

 

解説

まず、数式で決定する予定と自由に記入する予定を混同してはいけません。かならず、数式を入力する列と自由に予定を記入する列を分けます。

f:id:waenavi:20191115170056j:plain

 

定休日やゴミ出しの日など、週に1回一場ある予定で、曜日によって決まっているものをCHOOSE関数で入力することができます。ただし、変更の無いものに限られます

  • =CHOOSE(WEEKDAY,日,月,火,水,木,金,土)
  • =CHOOSE(WEEKDAY(A4),"","","可燃","","プラスチック","可燃","")

f:id:waenavi:20191115170203j:plain

 

日付が空白の場合にエラーになります。

f:id:waenavi:20191115170906j:plain

 

IFERRORで空白にします。

  • =IFERROR(CHOOSE(WEEKDAY(A4),"","","可燃","","プラスチック","可燃",""),"")

f:id:waenavi:20191115170932j:plain

 

(2)月に1回の場合

問題

さらに、第2水曜だけ「資源ごみ」と表示しなさい。 

f:id:waenavi:20191115171122j:plain

 

解説

第2木曜日になる可能性のある日付は8日~14日です。

  • 第1・・・1日~7日
  • 第2・・・8日~14日
  • 第3・・・15日~21日
  • 第4・・・22日~28日
  • 第5・・・29日~31日

つまり、DAYが8日以上14日以下であれば「第2」であると言えます。

  • AND(DAY(A4)>=8,DAY(A4)<=14)

これをCHOOSE関数の水曜日の中に入れます。

  • =IFERROR(CHOOSE(WEEKDAY(A4),"","","可燃",IF(AND(DAY(A1)>=8,DAY(A1)<=14),"資源ごみ",""),"プラスチック","可燃",""),"")

f:id:waenavi:20191115171157j:plain

 

10.特定の曜日だけの予定表

問題

土日を除いた予定表を作りなさい。また、月曜日、木曜日、土曜日だけの予定表を作りなさい。

f:id:waenavi:20191115171557j:plain

 

解説

日曜日から土曜日までのすべての曜日ではなく、平日だけの予定表を作るにはWORKDAY関数を使います。WORKDAY関数の第2引数(日数)を1にすると土日を休日としたときの「翌営業日」を求めることができます。

  • WORKDAY(開始日,日数)

セルA1に月の初めの日を入れます。ただし、この日付が平日とは限りません。

f:id:waenavi:20191115171819j:plain

 

予定表の初日は、前月末の日付を基準として考えます。

  • WORKDAY(前月末,1)・・・前月末から見た翌営業日
  • WORKDAY(A1-1,1)

f:id:waenavi:20191115171904j:plain

 

次回の日付を求めるには、その上のセルを基準とします。

  • =WORKDAY(A3,1)

f:id:waenavi:20191115171959j:plain

 

曜日を求めたり、月末を処理したりする方法は上記とほぼ同じです。

  • =IFERROR(IF(DAY(WORKDAY(A3,1))<DAY(A3),"",WORKDAY(A3,1)),"")

f:id:waenavi:20191115172137j:plain

 

曜日を求めます。

f:id:waenavi:20191115172455j:plain

 

土日以外の曜日を定休日とする場合は、WORKDAY.INTL関数を使います。WORKDAY.INTL関数は第3引数で休業日を指定することができます。

  • WORKDAY.INTL(開始日,日数,[休業日])

営業日を「0」、休業日を「1」として月曜始まりで指定します。例えば、月曜日、木曜日、土曜日だけを営業日とする場合、「0110101」となります。月曜始まりであることに注意します。

f:id:waenavi:20191115172350j:plain

 

予定表の初日は、前月末の日付を基準として考えます。

  • WORKDAY.INTL(前月末,1,"0110101")
  • WORKDAY.INTL(A1-1,1,"0110101")

f:id:waenavi:20191115172551j:plain

 

次回の日付を求めるには、その上のセルを基準とします。

  • =WORKDAY.INTL(D3,1,"0110101")

f:id:waenavi:20191115172720j:plain

 

11.月間カレンダーの作り方と休日祝日の処理について

1か月のカレンダーの作り方については別の記事で解説します。また、休日や祝日、年末年始休業等の特別な予定に関することについても別の記事で解説します。

 


解説は以上です。カレンダーの作り方へつづく・・・


 


Copyright(C)2018-2019 waenavi, All rights reserved. [www.waenavi.com 定礎 平成三十年八月]