【Excel】カレンダーに合わせて日付の色を自動で変える|講座#3

Word・Excel

前回記事で、カレンダーの日付が自動表示される方法をお伝えしました。

今回は「日付の自動表示に伴い、自動で色を変更する方法」をご紹介します。

 

難易度 中級

 

FURIO
FURIO

知っておけば色々と役に立つので頑張ろう!

各画像はクリックで拡大できるよ。

 

▼ 前回記事

【Excel】西暦の入力で、日付を自動表示する方法|講座#2
カレンダー作成で学ぶエクセル講座#2。西暦を入力すると、それに対応したすべての日付が自動で表示される方法です。

 

スポンサーリンク
スポンサーリンク

前回のおさらいと、やりたいこと

前回は、ここまで作成しました。

 

今回は下記のように「ただ日付が表示されるだけ」の状態から

「➊ 休日:赤」、「➋ 当月以外の日付:薄い灰色」、で表示されるようにします。

 

この2つの違いは

➊ 該当すれば色を変える

➋ 該当しなければ色を変える

と考えると分かりやすいかと思います。

 

FURIO
FURIO

もちろん日付に伴って色も自動で変わるよ!

以下、順番にやってみてね

 

自動で休日(土日)を赤にする

上のリボンから、「ホーム」タブ →「条件付き書式」→「ルールの管理」を選択

 

「新規ルール」を選択

 

「数式を使用して、書式設定するセルを決定」を選択

 

「次の数式を満たす場合~」の欄に

=SUBTOTAL(102, $B$4:$AC$33)

と入力(コピペ)する。

 

SUBTOTAL関数について

SUBTOTAL関数は、集計値を求める関数です。

この関数を条件付き書式に使用することで、指定のセルや文字に色をつけることができます。式の中に出てくる「102」は引数で、数値の個数を求めます。また「$B$4:$AC$33」でカレンダーの範囲を設定しています。

 

▼「$B$4:$AC$33」は、下記の様に全指定です。

 

自分で作成したカレンダーに使用したい方は、「$B$4:$AC$33」の部分だけ変更してください。102という引数は、そのまま使えます!

 

色の指定は「書式」から行います。

 

「色」から、赤を選びます。

 

OK → OKと押して下記の画面まで戻ったら、「適用先」欄を一度クリック。

 

 赤く表示させたいセルをカレンダーから選択します。

※点で囲まれているのが選択セルです。Ctrlを押しながら行うと、簡単に複数セルが選べます。

ここまでカレンダー講座どおりに進めている方は、以下の式をコピペでもOKです。
=$B$6:$B$11,$H$6:$H$11,$I$6:$I$11,$O$6:$O$11,$P$6:$P$11,$V$6:$V$11,$W$6:$W$11,$AC$6:$AC$11,$B$17:$B$22,$H$17:$H$22,$I$17:$I$22,$O$17:$O$22,$P$17:$P$22,$V$17:$V$22,$W$17:$W$22,$AC$17:$AC$22,$B$28:$B$33,$H$28:$H$33,$I$28:$I$33,$O$28:$O$33,$P$28:$P$33,$V$28:$V$33,$W$28:$W$33,$AC$28:$AC$33

 

選択したらOKで完了。

以下のように表示されれば作業完了です!

 

FURIO
FURIO

もちろん「土曜日は青にしたい」という場合も同じやり方でできるよ!

ぜひチャレンジしてみてね!

 



前月・次月の日付を薄灰色にする

日付を自動表示させたとき、下記のように前月・次月の日付も出てきてしまいます。

▲ これでは見づらいので、

 

▼ このように当月以外の日付は薄灰色になるよう設定します。

 

FURIO
FURIO

休日を赤にする、とは別の関数を使うよ!

 

条件付き書式を、各月ごとに設定する

  まず「1月」からです。B6のセルを選択します。

 

上のリボンから、「ホーム」タブ →「条件付き書式」→「ルールの管理」を選択

 

「新規ルール」を選択

 

「数式を使用して、書式設定するセルを決定」を選択

 

「次の数式を満たす場合~」の欄に

=MONTH(B6)<>MONTH($B$4)

と入力(コピペ)する。

※このとき「B6」を「$B$6」のように固定しないで下さい。「$B$6」にしてしまうと、色が変更されません!
※「$B$4」は1月の「1」が入力されているセルです。こちらは固定してください。

 

色の指定は「書式」から行います。

 

「色」から、薄い灰色を選びます。

 

OK → OKと押して下記の画面まで戻ったら、「適用先」欄を一度クリック。

 

 該当月の “日付が表示されるセル” をすべて選択します。

1月なら、下記のように選択します

※点線内が選択した箇所

 

OKを押して、以下のように変更されれば成功です。

※「31以降が薄い灰色になりました

 

【色が変わらない!という時は…】
条件付き書式は、上にあるものほど優先度が高くなります
もし「文字を赤くする条件」が、「薄灰色にする条件」よりも上に来ている場合、薄灰色にはなりません。
表示順の入れ替えは、下記画像の▲▼ボタンで行うことができます。

 

ここからがちょっと面倒ですが、薄灰色にする数式は各月の日数に準拠する必要があるので、それぞれの月で設定をする必要があります。

 

たとえば2月なら、以下のようになります。

このように1月を基本として、各月の数式が若干変化します。

数式は以下の表を参考にして下さい。

 

数式
1月(基本) =MONTH(B6)<>MONTH($B$4)
2月 =MONTH(I6)<>MONTH($B$4)+1
3月 =MONTH(P6)<>MONTH($B$4)+2
4月 =MONTH(W6)<>MONTH($B$4)+3
5月 =MONTH(B17)<>MONTH($B$4)+4
6月 =MONTH(I17)<>MONTH($B$4)+5
7月 =MONTH(P17)<>MONTH($B$4)+6
8月 =MONTH(W17)<>MONTH($B$4)+7
9月 =MONTH(B28)<>MONTH($B$4)+8
10月 =MONTH(I28)<>MONTH($B$4)+9
11月 =MONTH(P28)<>MONTH($B$4)+10
12月 =MONTH(W28)<>MONTH($B$4)+11

 

すべての設定が終わって、以下のようなカレンダーになればOKです!

 



祝日など、特定の休日を自動で変更する

祝日や年末年始休暇など、特定の休日を自動で変更することもできます。

 

FURIO
FURIO

もちろん祝日に限らず、自分で決めた日を好きな色に変更することができるよ!

 

 

任意の日付の色を自動で変更する方法

シートを追加します。今回は、分かりやすく「祝日」とします。

 

祝日シートを開き、C列に入力していきます。

※端に入力するよりも分かりやすいからCを選んでいますが、どこでもいいです!

試しに、1/1~1/3まで入力してみましょう。表示方法は「年/月/日」でも、「○月○日」でも大丈夫です。休みの理由を隣のセルに入力してもいいですし、しなくてもいいです。

 

「カレンダー」シートに戻り、B6セルを選択します。

 

上のリボンから、「ホーム」タブ →「条件付き書式」→「ルールの管理」を選択

 

「新規ルール」を選択

 

「数式を使用して、書式設定するセルを決定」を選択

 

「次の数式を満たす場合~」の欄に

=COUNTIF(祝日!$C:$C,B6)=1

と入力(コピペ)する。

 

色の指定は「書式」から行います。

 

 色の指定は「書式」から行います。

祝日は通常休みと重複することもあるので、今回はセルの塗りつぶしも設定します。

 

OK → OKと押して下記の画面まで戻ったら、「▼」ボタンを押して、祝日設定のルールを一番下に移動させる。

 

「適用先」欄を一度クリック。

 

祝日を反映させたいセルをカレンダーから選択。

 

 こんな感じで、祝日が反映されました。

 

あとは「祝日」シートの「C列」に反映させたい日を入力すれば、自動反映されるようになります。

 

年が変わっても自動反映させる方法

C列に日付を入力すると自動的に当年の西暦が判定されます。

(たとえば12/24と入力したとき、2023/12/24となる)

この方法だと、対象はあくまで2023年であり、それ以外の年は自動反映されません。

 

そこで、西暦を変更しても反映される方法の紹介です。
「祝日」シートC列に、通常の日付ではなく、DATA関数を使って入力します。

 

「祝日」シート、C列のセルを選択(C列なら、どのセルでもOK)

 

例として、毎年1/18を休みにしたい場合は、

=DATE(カレンダー!B1,カレンダー!B4,18)

と入力(コピペ)します。

 

考え方は以下の通りです。

 

年 → カレンダーの西暦を入力しているセル

月 → カレンダーの月を入力しているセル

日 → 手入力

 

「 fx」を押して、関数ダイアログボックスからも入力できます。

 

ダイアログボックスを開いたら「DATE」で検索。

 

その▼ こんな感じで、「年月」は該当するセルを選択し、「日」は直接入力します。

西暦のセルを選択したとき、「B1:D1」と表示されたら注意!
B1だけを選ばないと反映されません。

 

これで西暦を変更しても、自動反映されます!

※1日、2日、3日は関数ではないので消えてしまいます

 

 

作業は以上です。

これで西暦を変えるだけで、アッという間にすべてが更新されるようになりました!
超ラクです!

 

FURIO
FURIO

お疲れさまでした!苦労した甲斐があったかな?

次回は「自動で表示される日数の集計」を紹介するよ!

 



コメント