Excel使いこなしの基礎と応用


Excelで万年カレンダー仕様の予定表を作る(6)

土曜日・日曜日の行全体に色を付けて平日と差別化する設定は、条件付き書式にWEEKDAY関数を組み入れて行います。祝日の塗り分けにはMATCH関数を応用。ここでは、軽めのグラデーションを適用して塗り分けることにします。また、土・日・祝日の日にちと曜日のフォントの色も条件付き書式で変えてみましょう。

土・日・祝日の行の塗分け

スケジュール表作成のプロセス


Step6. 条件付き書式と関数で土・日・祝日を塗り分け

step-arrow

土・日の背景色の塗り分けにはWEEKDAY関数

まず、WEEKDAY関数の設定に馴染んでおきましょう。

日付「1」の行のセルを選択して、「数式」タブ「関数ライブラリ」の「日付/時刻▼」のリストから「WEEKDAY」をクリック。
WEEKDAY関数をクリック
WEEKDAY関数の引数」ダイアログボックスが開きます。

シリアル値」に曜日の入ったB3セルを指定、F4キーを3回押して絶対列参照にしておきます。 ▶絶対参照・複合参照について詳しく
種類」は空欄のままか「1」と入力。▶WEEKDAY関数の設定や引数について詳しく
WEEKDAY関数の引数でシリアル値を指定
これで指定は完了。「OK」すると、
金曜日を示唆する6が表示
」という数字が表示されました。
WEEKDAY関数は曜日を対応する1~7の整数で返してくるのです。1は日曜、7は土曜なので、6は金曜ということです。

数式バーには「=WEEKDAY($B3)」と入っています。
この式は次の条件付き書式の設定で使うのでコピーしておきましょう。コピーし終わったらこの入力は削除します。

スポンサーリンク

表題と列項目を除いたスケジュール表の全セルを選択します。

最初に、日にちの「1」が入ったA3セルをクリック、続いて、Contrlキーを押しながら最後列・最後尾のP33セルをクリックすれば、その範囲が選択できます。
土・日・祝日を塗り分ける条件付き書式の適用範囲
ホーム」タブ「スタイル」グループにある「条件付き書式▼」から「ルールの管理」をクリックします。
条件付き書式のルールの管理をクリック
条件付き書式ルールの管理」が開いたら「新規ルール」をクリック。
新規ルールをクリック
新しい書式のルール」ダイアログが開いたら、ルールの種類の「数式を使用して、書式を設定するセルを決定」をクリック。

ルールの内容を編集して下さい」下の「次の数式を満たす場合は…」のテキストボックスに先ほどコピーしておいた式「=WEEKDAY($B3)」をペーストし、続けて「=7」と入力します。

=7」の7はWEEKDAY関数が返す曜日に対応する数字で、7は土曜を示唆します。
WEEKDAY関数の後に=7と入力
入力が終わったら、下の方にある「書式」をクリック。
セルの書式設定」⇒「塗りつぶし」で「塗りつぶし効果」をクリック。グラデーションの設定画面が現れるので、「色2」▼のカラーパレットから淡いブルーを指定、「グラデーションの種類」は「」、「バリエーション」を上から下へブルー~白が流れるよう設定します。
土曜の行にブルーのグラデーション適用
「OK」を3回押して「条件付き書式ルールの管理」に戻り、「適用」をクリックするとスケジュール表の土曜の行がブルー系のグラデーションで塗りつぶされたのが確認できると思います。

スポンサーリンク

続けて、日曜の書式も設定してしまいましょう。

もう一度「新規ルール」をクリック。

「新しい書式のルール」ダイアログ ⇒「数式を使用して、書式を設定するセルを決定」⇒ 「次の数式を満たす場合は…」のテキストボックスに先ほどコピーしておいた式「=WEEKDAY($B3)」をペーストし、続けて「=1」と入力します。この1は日曜を示唆します。

「書式」⇒「セルの書式設定」⇒「塗りつぶし」で「塗りつぶし効果」をクリック。グラデーションの設定画面「色2」▼のカラーパレットから淡いピンクを指定、「グラデーションの種類」は「横」、「バリエーション」を上から下へピンク~白が流れるよう設定。
日曜の行にピンクのグラデーション適用
「新しい書式のルール」は↓こうなります。
日曜の行の新しい書式のルール
条件付き書式ルールの管理」に戻ると、土日の書式が並んで設定されてますね。
土日の塗りつぶしを設定した条件付き書式ルールの管理
とりあえず、ここで「OK」。きれいに塗り分けられてますね。
土日の塗り替えが完了したワークシート

祝日の背景色の塗り分けにはMATCH関数

こちらも最初にMATCH関数の組み立てから始めましょう。

Excelで万年カレンダー仕様の予定表を作る|Step.5で使った「祝日表」とは別に、祝日名が1列に並んだ表を作り、全ての祝日名(下図の赤枠の中)を範囲選択して「祝日名」と名前を定義します。
▶名前の定義と管理についてくわしく
祝日名のリスト
日付「1」の行のセルを選択して、「数式」タブ「関数ライブラリ」の「検索/行列▼」のリストから「MATCH」をクリック。
MATCH関数をクリック
MATCH関数の引数」ダイアログボックスが開きます。

検索値」に祝日の入ったC3セルを指定し、F4キーを3回押して絶対列参照にしておきます。 ▶絶対参照・複合参照について詳しく
検索値に祝日セル番地を入れて絶対列参照にする
検索範囲」には先程定義した名前「祝日名」と入力。
照合の種類」は完全一致を示す「0」を入力。
検索範囲に祝日名、照合の種類に完全一致の0を入力
(「照合の種類」には「0=完全一致」の他、「1=一致するものがなければ検索値より小さい値の近似値を探す」「-1=一致するものがなければ検索値より大きい値の近似値を探す」があります)

「OK」を押すと、セルには「」という数値が表示されます。
これは「検索値=元旦」を「検索範囲=祝日名」の中から「完全一致」で探し、「1行目」に見つかりました、という結果です。

数式バーには「=MATCH($C3,祝日名,0)」と表示。WEEKDAY関数の時と同様、この式もコピーしておきます。コピーしたら、この入力値は削除。


条件付き書式の設定に進みましょう。

表題と列項目を除いたスケジュール表の全セル(A3:P33)を選択し、再び、「条件付き書式」⇒「ルールの管理」を開きます。「新規ルール」をクリック。

「新しい書式のルール」ダイアログ ⇒「数式を使用して、書式を設定するセルを決定」⇒ 「次の数式を満たす場合は…」のテキストボックスに先ほどコピーしておいた式「=MATCH($C3,祝日名,0)」をペーストします。

「書式」⇒「セルの書式設定」⇒「塗りつぶし」で「塗りつぶし効果」をクリック。

グラデーションの設定画面で、「色2」▼のカラーパレットから淡いオレンジを指定、「グラデーションの種類」は「横」、「バリエーション」を上から下へオレンジ~白が流れるよう設定。
祝日の行にオレンジのグラデーション適用
「新しい書式のルール」は↓こうなります。
祝日行の新しい書式のルール
条件付き書式ルールの管理」に戻ると、土日の上に祝日の書式が並んでますね。
祝日の書式が加わった条件付き書式ルールの管理
ここで「OK」。ちゃんと塗り分けられてますね。
土・日・祝日が塗り分けられたワークシート

土・日・祝日の行の「日にち」と「曜日」のフォントカラーを変える

上記の塗りつぶしと一緒にフォントカラーも変えられますが、それだと行全体に適用されてしまうので、ここでは、「日にち」の数字と「曜日」の文字、「祝日名」のフォントカラーだけを変えて、予定を書き込むエリアの文字色は黒のままにしておこうと思います。
土・日・祝日行の日にちと曜日だけに条件付き書式を適用

「日にち」「曜日」「祝日名」のセル範囲(A3:C33)を選択し、もう一度「条件付き書式」⇒「ルールの管理」を開き、「新規ルール」をクリック。

土・日・祝日ともに、塗り分けの時と作業は同じです。
「新しい書式のルール」ダイアログ ⇒「数式を使用して、書式を設定するセルを決定」⇒ 「次の数式を満たす場合は…」のテキストボックスに塗り分けと同じ式を入力。

「書式」の「フォント」で「」を設定します。
土・日・祝日のフォントカラーを設定
土曜の「新しい書式のルール」
土曜の行の条件付き書式
日曜の「新しい書式のルール」
日曜の行の条件付き書式
祝日の「新しい書式のルール」
祝日の行の条件付き書式

▶次は罫線を引いて、スケジュール表の体裁を仕上げましょう

Officeで作れるもの