新しいサービス【AwesomeQR】

【Excel】休日と祝日の任意の範囲に自動で色を付けてオリジナルカレンダーを作成しよう!【無料サンプルダウンロード】

3 min 29,627 views

はじめに

今回は、Excelで休日と祝日の任意の範囲に自動で色付けする方法をシェアしたいと思います。

完成系

まずは、完成系を下図に示します。

Excel色付けカレンダーイメージ

今回は、簡単に考えるため、条件として以下2つ設定します。

  • 土日の場合に、B列からD列までをグレーにする
  • 祝日の場合に、B列からD列までを緑色にする

方法

動画でご覧になりたい方は、以下を参照ください。

  1. STEP

    日付を入力

    まずは、日付を入力します。

    下図のように、A1セルに「1」を入力して、編集>フィル>連続データの作成をクリックします。

    Excelのフィルの連続データ作成

    続いて、下図の通りに設定して、OKをクリックします。

    連続データの設定

    すると、下図の通り、自動で365までの数値が入力されました。

    連続データ結果

    続いて、B1セルに「2022/1/1」を入力して、下図のようにB1セルの右下にマウスのカーソルを持っていって、ダブルクリックをする。

    オートフィル

    すると、下図のように1年分(365日分)の日付データが入力されます。

    オートフィル結果

    続いて、分かりやすいように曜日を設定します。

    C1セルに以下の関数を入力します。

    =TEXT(B1,"aaa")
    曜日の関数

    すると、曜日が入力されるので、日付同様に、C1セルの右下をダブルクリックして、曜日も追従させます。

    曜日のオートフィル

    これで、日付の入力は完成です。

  2. STEP

    祝日の準備

    下図のように、祝日シートを作成して、日付と祝日の名称を入力します。

    祝日リスト

    ここは、祝日だけでなく、会社の創立記念日とか夏休みとかを入れてもいいと思います。

    カレンダーのシートに戻って、D1セルに以下の関数を入力して、日付同様に、D1セルの右下をダブルクリックして、祝日の名称を入力します。

    =IFERROR(VLOOKUP(B1,祝日!$A$1:$B$15,2,FALSE),"")

    簡単に説明しますと、祝日シートにある範囲で、日付が合致したら祝日の名称を入れるという式です。

    祝日表示の関数

    これで、祝日の名称も入力されました。

  3. STEP

    色付け

    それでは、最後に祝日に緑、土日にグレーを設定します。

    条件付き書式

    上図の通り、B列からD列までを選択した状態で、条件付き書式>新しいルールをクリックします。

    ルールの種類は「数式を使用して、書式設定するセルを決定」として、以下の関数を入力します。

    =NETWORKDAYS.INTL($B1,$B1,1,祝日!$A$1:$A$15)=0
    数式を使用して、書式設定するセルを決定

    この関数は、第一引数が「開始日」、第二引数が「終了日」、第三引数は「1」にすると土日となり、第四引数が「祝日の範囲」を設定することで、結果が「0」となりますので、その条件に合致した時に書式を変更するという意味になります。詳しくは、以下のURLを参照ください。

    上記の設定をしたら、上図の書式をクリックします。

    祝日色設定
    祝日フォント設定

    上図のように、塗りつぶしタブで好みの色(今回は緑)を選択して、フォントタブで好みのフォントの色(今回は白)を選択して、OKをクリックします。

    祝日書式設定

    上図のように、プレビューで好みの設定であることを確認して、OKをクリックします。

    すると、下図のように、祝日だけでなく、土日も緑色に塗りつぶされてしまいました。

    祝日設定結果

    なので、これから土日をグレーに上塗りする設定を追加します。

    上記同様に、 B列からD列までを選択した状態で、条件付き書式>新しいルールをクリックします。

    新しい書式設定

    ルールの種類は「数式を使用して、書式設定するセルを決定」として、以下の関数を入力します。

    
    =NETWORKDAYS.INTL($B1,$B1,1)=0
    休日設定

    祝日のと同様に、土日の書式を設定していきます。

    休日色設定

    塗りつぶしタブで、グレーを選択してOKをクリックします。

    休日フォント設定

    上図のように、プレビューで好みの設定であることを確認して、OKをクリックします。

    すると、下図のようになりますが、土日の部分のフォントが白色になってしまっています。

    休日書式設定結果

    それでは、土日のフォントの白色を解消していきます。

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

    上図のように、条件付き書式>ルールの管理をクリックします。

    条件を満たす場合は停止という条件

    上図のように、土日の設定の条件を満たす場合は停止に☑を入れて、OKをクリックします。

    すると、下図のように、完成します。

    休日・祝日書式設定の結果

さいごに

いかがだったでしょうか。

今回のテーマとしては、セル内に特定の文字列がなくても、指定した任意の範囲でセルの書式を設定できるという紹介になります。

こちらを応用すれば、かなり凝ったオリジナルのカレンダーが作成できるのではないでしょうか。

それでは、最後にサンプルファイルを添付いたしますので、ご自由にダウンロードしてご参照ください。

関連記事