31日も曜日も祝日も自動設定のカレンダーを作る［Excel］

この記事は更新して新しいブログに引っ越しました。
↓↓↓
月末・祝日・色も自動で変わるスケジュール表作成｜Excel｜PCワークLABO

スゴ技いっぱいのスケジュール表を作ってExcelを楽しもう！
テキストとは違う独自の解説で進めていくよ。
長いので、分かる人は必要なところだけクリックして進めてね。

表示形式を設定して”年”や”月”は自動表示

【A1】に年、【B1】に月を入力するだけですぐに完成するスケジュール表を作りたい。

まずはタイトルと見出し部分だけ入力するよ。
【A3】から【D3】の見出しセルは見やすく中央揃えにしている。

【A1】に”2018”と入力すると”2018年”と表示されるようにしたい。
「表示形式」を使って設定しよう。

表示形式は、まず設定したい範囲を選択。
【A1】を選択して≪Ctrlキー+1（上の数字の1を使用 ※テンキーの1ではできない）≫
Excelはこのショートカットが大活躍！
ガンガン使っていこうー！

「セルの書式設定」ダイアログボックスを開いたら、
[表示形式]タブ[分類]の一番下にある「ユーザー定義」を使おう。
ここを使えば自由に設定できる。

右の[種類]の下に『 #”年” 』と入力し「OK」を押す。
（#はここに数字が入るという意味。その数字の右側に「年」という文字を表示させる設定）

では、【A1】に『 2018 』と入力してみよう。
”2018年”と表示されたかな？
でもセルには2018のデータしか入っていないのが確認できる。

同じ要領で【B1】の”月”をやってもいい。
今回は、横幅を小さくするので【C1】に月以降まとめて入力した。

DATE関数を使えば日付入力は不要

わかりやすくするため、あらかじめ【B1】に月を入力しておこう。
今回は10月ということで、『 10 』と入力。

では、DATE関数を使って【A4】に10月1日の日付を表示させよう。

=DATE(A1,B1,1)

”年”の場所で【A1】をクリック、”月”の場所で【B1】をクリック、日の場所で『 1 』と直接入力。

2018/10/1 と表示された。

表示形式は後で直すので今はそのまま。

次は下の【A5】に翌日の日付の2日を入力したい。
これも計算式で入れよう。
【A4】の日付に1日足すだけなので、

=【A4】+ 1

これだけ。2018/10/2になった。

次は31日までセルをコピー。

オートフィル機能で下まで一気に表示

範囲選択をすると、常に緑で囲まれている。
よく見ると、囲んだ右下に小さな四角がついている。
これを「フィルハンドル」という。

このフィルハンドルを下や右にドラッグすると連番やコピーができる。
それをオートフィル機能というよ。空いているセルを自動で埋めてくれるんだ。

やり方は簡単。
今回は【A5】を選択して、右下に出てきたフィルハンドルにカーソルを合わせる。
すると、ポインターの形が十字キーになる。
その状態になったら、ずーっとしたまでまっすぐドラッグしていく。
2018/10/31になるまで。今回は34行目まで。

数式のセルにフィルハンドルを使うと式のコピーができる。
今回の式は、”上のセル+1”というもの。
下まですべて”上のセル+1”になった。

”######” とエラーコードが出ているね。
これは、幅が狭くて表示しきれないということだから、A列の幅を広げれば直る。

列Aと列Bの表示がある真ん中にマウスポインターを合わせると、
双方向の矢印が出てくるので、そこで好きな位置まで右に引っ張る。
もしくは、その上でダブルクリック。
ダブルクリックすると左列の最長データに合わせて自動で調整してくれる。

次は、A列の日付の表示方法を変えていこう。
「2018/10/1」を「1日」という表示にしたい。

【A4】から【A34】までドラッグするのは長いので、一気に範囲選択する。
とにかく、手間を省いちゃおう。

【A4】を選択して≪Ctrlキー+Shiftキー+下向き矢印≫

Ctrlキーは、離れたところを選択することができる。
Shiftキーは、ここまでという意味がある。
下向き矢印も足すことで、”一番下まで選択”ということになるよ。

日付全てが選択された状態で≪Ctrlキー+1≫をして、セルの書式設定ダイアログボックスを表示。
今回の表示形式は、

『 d”日” 』

〇日という表示になった。

あれ？今度はなぜ「#」じゃなくて「d」なの？
そう、そこ！気付いた？
では、Excelでは知っておくとお得な「シリアル値」について解説します。
そんなのどうでもいいーって人は飛ばしちゃってOK！

【余談】シリアル値がわかれば産まれたのが何曜日かも分かる

つまり、1900年1月3日は「3」となる。
（※MACは1904年1月1日が起点となり、0から始まる）

試しに今日の日付を入れてみよう。
その後、入力したセルを選択して[ホーム]タブの[配置]グループにある、表示形式ボックスの
「 標準 」を選択してみる。

10月1日で行うと、43374　となった。
1900年1月1日から、43374日目ということになる。
Excelではこのシリアル値を使って日時計算をしている。

1900年以降は、全てExcelにはお見通しなのだ。
ってことは、自分が産まれた日が何曜日かもわかるよね？

今度は自分の誕生日を入力してみよう。
ただし、西暦を入力しないと現在の年になってしまうので、西暦から/で区切って入力する。
今度は ≪Ctrlキー+1≫しよう。

[表示形式]の「ユーザー定義」で『 aaa 』と入力する。
どうかな？曜日がわかった？
日曜の人～？
はい、私でーす。休日加算でちょっとお金かけて産まれちゃったよー。
でも安心して？
お父さんや親戚や職場の人や知らない人にも誕生を祝ってもらえたよ、きっと。

ちなみに表示形式は、
yyyyで西暦4桁表示(例:2018)。yyで西暦2桁表示(例:18)。
mは月表示、mmで月の2桁表示（3なら03となる）。
ということは･･･日はdayだからd、ddで日の2桁表示。

分かってくるとおもしろくない？
自分で表示の仕方を指定できちゃうよ。

では話を戻してー
日付のところに、『  d”日” 』としたのは、
Excelがシリアル値と認識して日付の部分を表示してくれるから。
『 #”日” 』としたらどうなる？そう、シリアル値に”日”がつく形になってしまう。
43374日　みたいな。

29日・30日・31日問題はIF関数、DAY関数とIFERROR関数で解決

月を入れたら自動で変わっても、31日がない日は？　2月はどうする？
ここで使えるのがIF関数とDAY関数。

計算式はこれ。ちょっと難しそうに見えるかもしれない。

=IF(DAY(A31+1)<>29,””,A31+1)

“” は空白という意味。
<> は不等号。=じゃないよっていう意味ね。

この式を文章にすると･･･
もし【A31】+1の結果の日付が29じゃなかったら,空白で表示してくれる？
そうでなかったら【A31】+1の数字を表示しておいて。

というもの。
2月28日の次が29日ではなく、1日となったら表示させないようにするんだね。

あれ？マークがでてきたよ。

マークをポイント（マウスポインタ―を合わせる）すると、
「このセルにある数式が、セル周辺の数式と異なっています。」
上下の計算式とこれだけ違うけど大丈夫？という確認だ。
Excelではよく出てくるので気にしなくていい。印刷もされないから。
どうしても気になるー！という人は、クリックして「エラーを無視する」を選択。

じゃあ、同じ要領で30日と31日を作れるね！

【A32】をフィルハンドルで34行目までドラッグして数式をコピーしよう。
数式をコピーしても、29の部分はそれぞれの日に変えないといけないよね？

30日のセルは、もし【A32】+1の結果の日付が30じゃなかったら･･･に。

【A33】　=IF(DAY(A32+1)<>30,””,A32+1)

【A34】　=IF(DAY(A32+1)<>31,””,A32+1)

できたできた！

【B1】の月「 10 」を「 11 」にしてみよう。
30日までしか表示されなくなったね！

今度は2月の「 2 」にしてみよう。

あ！エラーコードが出ている！

#VALUE!

計算式の中で参照しているセルがおかしいぞ！と言っているよ。
【A32】が空白になってしまっているので、【A32】+1 の計算ができずに困っている。

これを回避するのはIFERROR関数。

もしエラーになったらそのセルは空白にしちゃえばいいよね。

【A33】は、=IFERROR(IF(DAY(A32+1)<>30,””,A32+1),””)

【A34】は、=IFERROR(IF(DAY(A33+1)<>31,””,A33+1),””)

さっき作った式の前にIFERRORを入れて、最後に ,”” をつけて完成させよう。
これで大丈夫だね！
計算式をして、結果がエラーになる場合は空白にしてくれるよ。

次はB列に曜日を入れよう。
左のA列の日付（シリアル値）をコピーして、表示形式から曜日の表示にすればいい。

まずは【B4】を選択して　=A4

左と同じ、「 1日 」と表示された。

【B4】のセルを選択して、そのフィルハンドルをダブルクリック。

便利～。

【B4】から【B34】まで選択された状態のまま≪Ctrlキー + 1≫でセルの書式設定ダイアログボックスを出すよ。

曜日の漢字一文字表示は「 aaa 」なんだ。
ユーザー定義に「 aaa 」と入力しよう。

曜日に変わった。
続けてB列の幅を変えよう。

条件付き書式とWEEKDAY関数で土日に色をつける

土日には色を付けると見やすくなる。
そんな時便利なのが「条件付き書式」
指定した条件を満たしたものだけに書式を設定するよ。

今回は、日付(A列)と曜日(B列)の両方に土日の色をつけたいと思う。
【A4】から【B4】を選択し、≪Ctrlキー+Shiftキー+下向き矢印≫で一気に範囲選択。

[ホーム]タブ[スタイル]グループの[条件付き書式]から、「新しいルール」を選択。

「新しい書式ルール」ダイアログボックスが出てくる。
ルールの種類は、「数式を使用して、書式設定するセルを決定」を選ぶ。
ここを使えば、数式を使った指示ができる。

※式は、範囲選択の中でもアクティブになっている部分（白くなっている【A4】）
から見た計算式にすること。

ここで使うのは、WEEKDAY関数。
これは、指定した日付が何曜日か教えてくれる関数。
ただし、〇曜日ではなく、数字で返ってくるので注意。

「土日だけ」を抽出するという条件を数式で作ればいい。
数式はこれ。

=WEEKDAY(A4,2)>=6

【A4】の日付をWEEKDAY関数の種類”2”で出して、その結果が6以上のもの。

種類”2”を選択しているから、「6」という結果なら土曜日、「7」なら日曜日となる。
土日だけを抽出したいから”>=6”とし、6以上のものだけを抽出。
（WEEKDAY関数の結果は1～7までで、8以上はないからね。）

入力中【A4】をクリックした時、『$A$4』となるので『A4』に直しておこう。
これは、絶対参照を相対参照にするってこと。
詳しく知りたい人は、絶対参照と相対参照についてへ。

そして、その条件を満たすものに書式を設定する。
右下の「書式」ボタンを押す。

今回はフォントの色を赤にしてみる。
[フォント]タブの[色]から赤を選択。

※セルの色を変えたい場合は[塗りつぶし]タブ、他に罫線も設定できるよ。

それから[OK]を押す。

土日のフォントが赤になった。

やった！これで完成！
・・・でもいいんだけど、祝日も赤にしたい人はこれ。

条件付き書式とCOUNTIF関数で祝日にも色をつける

祝日がいつか？
それは「2018年　祝日一覧」とググってみればすぐ出てくる。
まずはそのデータをありがたく頂いて、新しいシートに貼り付けよう。

新しいシートは、Excelの一番下にある[ + ]のマークをクリックするよ。

祝日サイトの表の部分をドラッグしてコピー≪ Ctrlキー + C ≫し、
Excelの新しいシートの【A1】で貼り付け≪ Ctrlキー + V ≫

ここで注意！
貼り付けたセルの日付の部分を見てほしい。
下の場合は【B2】を選択して、名前ボックスを確認。
ここが「2018/1/1 」となっていないものはシリアル値ではない。
つまり、Excelの計算としては使えないから違うサイトからコピーし直そう。

こっちのサイトはどうかな？

これはシリアル値だね！
よし、これ使おう。
シリアル値が出るならどのサイトでもOK！
これで準備ができた。

元のシートに戻って、色を付けたい範囲、【A4】から【B34】を選択。
土日と同じ要領でいくよ。

[ホーム]タブ[スタイル]グループの、[条件付き書式]から「新しいルール」を選択。
ルールの種類は「数式を使用して、書式設定するセルを決定」。

ここで使うのはCOUNTIF関数。
条件に合ったセルの数を数えてくれる。

=COUNTIF(Sheet2!$A$1:$A$20,A4)=1

Sheet2!$A$1:$A$20･･･これはコピーした祝日の日付の入った範囲（人によって違うよ）
”=COUNTIF(” の入力の後、違うシートに貼り付けた日付範囲をドラッグすると
勝手にSheet!や＄マークがついてくるけれど、そのままで。

【A4】の＄は外してね。詳しくは参照についてへ。

この式の意味は、

シート2の【A1】から【A20】の中に【A4】と同じ日付があったらそのセルの数を数えてね。でもって、その数が”1”だったら書式の設定をしてね。

というもの。同じ日があったとしても1個しかないから、見つけた時は必ず1になる。
それを見つけて書式の設定をするんだ。

右下の[書式]ボタンを押す。
さっきと同様にフォントの色を赤にしよう。

[OK]を押すと祝日も赤になったね！
確認のため、【B1】を”10”にしてみよう。

8日の月曜日が赤になったね。

やった！

ちなみに、条件付き書式を修正したい場合は、その範囲を選択して
[ホーム]タブ[スタイル]グループの、[条件付き書式]から「ルールの管理」を選択。

修正したいルールを選択してから、「ルールの編集」を押す。
削除もできるよ。

最後は、フォントサイズや列幅、罫線をつけるなどして体裁を整えよう。
表の編集についてはまた書いていきます。