関数を使って「万年カレンダー」を作る（その１）

Excelの関数を使えば、簡単に「カレンダー」を作ることができます

 　関数を使えば、「年」と「月」を指定するだけで自動的に「日付」や「曜日」が入力される「カレンダー」を作ることができます。

「年」と「月」を入力するだけで、正しい位置に日付が表示される

　　　　

条件付き書式を使って「祝日に色を付ける」、「対象月以外の色を変える」

　さらに条件付き書式を組み合わせれば「祝日の色を変える」ことや、「対象月以外の色を変える」ことができます。　

　　　　

Excel上で「日付」は「シリアル値」で扱われます。

　入力する際に「スラッシュ（1/1）」や「1月1日」の形式で入力することで、そのDATAは「日付」と認識され自動的にシリアル値として扱われます。

カレンダーを作成する

　DATE関数とWEEKDAY関数を使って、「日付」が自動的に入力されるカレンダーを作成します。

1. 入力された「年」と「月」からDATE関数を使って「開始日」を作成します。
2. WEEKDAY関数を使って「第１月曜日」の日付を計算します。
3. 「第１月曜日」に「１」を加えていくことで日付を入力していきます。
4. 表示形式を「日付」だけが表示されるように変更します。
5. 「祝日の一覧表」を用意し、条件付き書式で「祝日」に色を付けます。
6. MONTH関数で条件付き書式の「条件」を作成し、対象月以外の日付の色を変えます。

１．入力された「年」と「月」をもとに数式を使って「開始日」を作成します

　「開始日」は直接入力せず、DATE関数を使って入力します。

1. 「開始日」の欄に、DATE関数で「年」と「月」と「１」を合体させます

　　　　数式＝DATE（ ”年" , ”月” , 1 )

　　　　

２．カレンダーの左上「第1日曜日の日付」を算出します

　作成するカレンダーは「日曜始まり」なので、数式を使ってカレンダーの左上（第1日曜日）の日付を計算します。

　　　　

指定した「月（年月）」の曜日をWEEKDAY関数を使って計算します

1. WEEKDAY関数で「月初」が何曜日かを計算し「指数」を求める
2. 第１日曜日が「開始日」から「何日前か」を計算する。
3. 「開始日」から「何日前か」を減じて「第1日曜日の日付」を求める

　　　　数式「2019/1/1－３＋１＝2018/12/30」

　　　　

　　　　

※ カレンダーが「日曜始まり」なので、WEEKDAY関数の指数は「１」になる　　

　「月曜始まり」のカレンダーの場合は、指数を「２」にする。

　　　　

第1日曜日が計算されました

　指定した「年」、「月」の第1日曜日が計算されました。

　　　　

３．第1日曜日を基準にして、第１週目の日付の計算式を入力

　１列目の「月曜日」のセルに、左隣のセルの日付に「１を加える」数式を入力し右方向にコピーします。

1. 第1日曜日の右隣りのセルに数式「 ＝ B7 + 1 」と入力
2. 「フィルハンドル」をドラッグして、１週目の最後の「土曜日」までコピーします

　　　※ フィルハンドルの説明は最下行にあります

　　　　

２週目を設定します

　２列目の左端のセルに、「１列目の右端」のセルに「１を加える」数式を入力し、「月曜日」のセルに左隣のセルに「１を加える」数式を入力し右方向にコピーします。

1. 第２日曜日に、１列目の右端に１を加えた数式「＝H7＋１」を入力
2. 第２月曜日に左端のセルに「１を加える」数式を入力し「フィルハンドル」をドラッグして、２週目の最後の「土曜日」までコピーします

　　　　

３週目以降も同様に設定します

　　　　

４．セルの「書式」を変更して、「日」だけを表示します

　セルの表示形式を変更して、表示を「日」だけが表示されるように変更します。

1. 「日付」の部分を範囲選択して、右クリックします
2. ポップアップリストから「セルの書式設定」を選択します
3. セルの書式設定で、「表示形式」のタブを選択します
4. 分類で「ユーザー定義」をクリックします
5. 種類に「ｄ」を入力（※ リストにある場合はそれを選択）

　　　　

様式を整えて完成

　表全体の「配置」を変更して、セルの中央に文字が表示されるようにします。

1. 表全体を範囲選択
2. 「ホーム」タブを選択
3. 配置で「上下中央揃え」を選択
4. 配置で「中央揃え」を選択

　　　　

対象月を変更します

　月の指定を「２」にすると、開始日の曜日等が正しく表示されます。

　　　　

５．条件付き書式を使って「祝日」の色を変える 

　条件付き書式を使えば、用意した「祝日一覧」の表を使って祝日の色を変えることができます。

　　　　

祝日一覧の表を用意します

　別シートに祝日一覧の「表」を作成し、その「表」をテーブル化し「祝日」と名前を付けます。

　※「祝日の表」をテーブル化することで、「祝日のデータ」を編集（挿入・削除）しても、自動で表の範囲が変更されるので範囲設定をやり直す必要がありません。

1. 祝日一覧のどこか１カ所を選択
2. 「挿入」タブを選択
3. 祝日一覧の範囲が選択されているのを確認
4. 「先頭行をテーブルの見出し･･･」にチェックを入れる

　　　　

テーブルに「名前」を付けます

　テーブル名を「祝日」に変更します。

1. テーブル化した範囲のどこか１カ所を選択
2. 「テーブルデザイン」のタブが表示されるので選択（※ テーブルの範囲を選択していないと表示されません)
3. 「テーブル名」に「祝日」と入力します

　　　　

条件付き書式を入力します

1. 「数式を入力して、書式設定･･･」を選択
2. 数式「 =COUNTIF( 祝日,B7)=1 」を入力　　

　　　　

COUNTIF関数の「範囲」には、範囲に付けた「名前」を入力します

　　　　

「祝日」が赤く塗りつぶされました

　　　　

  

６．条件付き書式を使って、「対象月」以外の部分を灰色にします

　条件付き書式の条件にMONTH関数を使って条件を設定することで、「対象月以外」の日付部分の色を変えることができます。

　　　　

対象月以外の部分の色を「薄い灰色」に変更します

1. 日付部分を範囲選択
2. 「ホーム」タブを選択
3. 「条件付き書式」を選択
4. 「新しいルール」をクリック

　　　　

数式を入力して、「条件」を設定します

1. 「数式を入力して、書式設定･･･」を選択
2. 数式「 =MONTH(B7)<>$C$3 」を入力　　

　　　　

※ 条件付き書式を「範囲」に設定する場合、「範囲の左上」のセルを設定すれば自動的に「相対参照」になります。

※ 数式が自動で「B7 ➡ B8 ➡ B9･･･」、「C7 ➡ C8 ➡ C9･･･」となります。

「該当月」以外が灰色になりました

　「1月27日～1月31日」と「3月1日～3月2日」の部分が灰色になっています。

　　　　 

フィルハンドルを使えば、簡単に「コピー」や「選択」ができます

　　　　

　その他にもVLOOKUP関数を使えば、リスト形式で入力した「スケジュール表」 から「予定表」を作成することができます。

　今回は「年」と「月」を選択するだけで「カレンダー」が作成される方法を説明しました。

　その２では既に「１日から入力されているカレンダー」に「土・日」と「祝日」の部分に条件付き書式を使って「色を付ける」方法を説明します。

　　　　

k-ohmori9616.hatenablog.com