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

カレンダー_アイキャッチ Excel

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

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

Excel_スケジュール手順

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

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

Excel_セルの中央揃え

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

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

Ctrlキー+1 ≫でセルの書式設定ダイアログボックスが出てくる。
(上の数字の1を使用 ※テンキーの1ではできない)

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

Excel_セルの書式設定ダイアログボックス

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

表示形式入力のポイント!
・日本語など、文字列を入れたい部分は””(半角)で囲む。
・記号と英数は半角で入力。

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

Excel_数式バーとの違い

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

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

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

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

=DATE(年,月,日)

=DATE(A1,B1,1)

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

Excel_DATE関数

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

Excel_DATE関数2

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

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

=【A4】+ 1

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

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

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

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

Excel_フィルハンドル

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

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

Excel_オートフィル

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

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

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

Excel_列幅ダブルクリック

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

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

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

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

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

d”日”

Excel_表示形式

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

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

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

シリアル値とは、Excelで日時を計算するために使われている数値のこと。
1900年1月1日を「1」とし、毎日1ずつ増えていく。

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

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

Excel_日付を標準にする

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(論理式,値が真の場合,値が偽の場合)
難っ!私の言葉に変えると、
=IF(条件の計算式,その条件を満たしていたらこの表示,満たしていなかったらこの表示)
=DAY(シリアル値)
( )の中の数字をシリアル値とし、”日”の部分を表示してくれる関数

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

Excel_DAY関数

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

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

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

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

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

Excel_エラーインジケーター

マークをポイント(マウスポインタ―を合わせる)すると、
「このセルにある数式が、セル周辺の数式と異なっています。」
上下の計算式とこれだけ違うけど大丈夫?という確認だ。
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 」にしてみよう。

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

Excel_エラーコード#VALUE!

#VALUE!

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

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

=IFERROR(値,エラーの場合の値)
”値”部分の計算式をして、もしエラーコードが表示される結果になったら、
”エラーの場合の値”に入っているように表示して!と指示する関数。

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

Excel_IFERROR関数

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

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

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

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

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

Excel_セル参照

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

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

フィルハンドルをダブルクリックすると、
隣接する列が続いているところまで一気にコピーしてくれる。

便利~。

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

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

Excel_表示形式aaa曜日

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

Excel_列幅変更

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

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

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

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

Excel_条件付き書式新しいルール

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

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

Excel_WEEKDAY関数

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

=WEEKDAY(シリアル値,種類)
シリアル値:日付を表す数値を入力するか、日付のセルを選択
種   類:1or省略にすると、1=日曜,2=月曜,3=火曜・・・ ~ 7=土曜
種類を2にすると、1=月曜 ~ 7=日曜として結果が数字で返ってくる。

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

=WEEKDAY(A4,2)>=6

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

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

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

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

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

Excel_条件付き書式

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

それから[OK]を押す。

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

Excel_条件付き書式フォント

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

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

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

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

Excel_シートの追加

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

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

Excel_シリアル値確認

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

Excel_祝日

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

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

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

Excel_条件付き書式COUNTIF関数

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

=COUNTIF(範囲,検索条件)
範  囲:指定した条件に合うものがあるかどうか確認したい範囲
検索条件:範囲の中で見つけたい条件となる数式や文字など

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

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

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

この式の意味は、

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

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

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

Excel_条件付き書式

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

Excel_条件付き書式確認

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

やった!

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

Excel_条件付き書式ルールの管理

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

Excel_条件付き書式ルールの編集

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

Excel_スケジュール表

コメント

  1. […] この表の作り方を詳しく知りたい人は、31日も曜日も祝日も自動設定のカレンダーを作る[Excel]を見てね。 […]

タイトルとURLをコピーしました