Excel(エクセル)で予定表を作成する方法

Introduction

Excel(エクセル)で予定表を作成する方法です。

祝日の設定などは関数で、土日の色変更は条件付き書式で対応します。

【使用する関数】

  • VLOOKUP関数
  • IFERROR関数
  • OR関数

【使用する機能】

  • 名前の定義
  • 条件付き書式
  • など

 長い説明になるので、のんびりやるにゃ

スポンサーリンク

年ごとの祝日(休みの日)を用意する

年間の予定表やカレンダーを作成するには、祝日などの日曜日を除いた休日の設定が必要です。

祝日は年によって日にちが変わるものが多いので毎年用意する必要があります。

別のワークシートに用意しましょう。

今回は隣の「Sheet2」に用意します。

インターネットで「祝日 内閣府」のように検索すると簡単に見つけることができますよ。振替休日に注意が必要です

ここでは2018年と2019年のものを用意しておきます。

2018年の祝日一覧

2018/1/1元日
2018/1/8成人の日
2018/2/11建国記念の日
2018/2/12振替休日
2018/3/21春分の日
2018/4/29昭和の日
2018/4/30振替休日
2018/5/3憲法記念日
2018/5/4みどりの日
2018/5/5こどもの日
2018/7/16海の日
2018/8/11山の日
2018/9/17敬老の日
2018/9/23秋分の日
2018/9/24振替休日
2018/10/8体育の日
2018/11/3文化の日
2018/11/23勤労感謝の日
2018/12/23天皇誕生日
2018/12/24振替休日

2019年の祝日一覧

2019/1/1元日
2019/1/14成人の日
2019/2/11建国記念の日
2019/3/21春分の日
2019/4/29昭和の日
2019/5/3憲法記念日
2019/5/4みどりの日
2019/5/5こどもの日
2019/5/6振替休日
2019/7/15海の日
2019/8/11山の日
2019/8/12振替休日
2019/9/16敬老の日
2019/9/23秋分の日
2019/10/14体育の日
2019/11/3文化の日
2019/11/4振替休日
2019/11/23勤労感謝の日
2019/12/23天皇誕生日

今回は上にある2018年の表全体を引っ張って選択して右クリック → コピー

予定表を作成する隣のシートをクリックして、

分かり易く左上の「A1」を選択してから、[貼り付け] → [貼り付け先の書式に合わせる]のボタンをクリックします。

祝日のデータが貼り付くのですが、「A」列の幅が狭く、日付全体が表示することができないため「#」マークが表示されてしまいます。

列幅を広げましょう。「A」の列番号と「B」の列番号の間にマウスを移動してダブルクリックすると、

列幅が自動調整されて、日付がちゃんと表示されます。

祝日の準備ができました。

このデータは、後で予定表に関数を使用して挿入します。そのために範囲に名前を付ける作業をしましょう

では、この祝日の範囲に名前を定義します。

日付全体が選択されている状態で、左上の[名前ボックス]をクリックします。

文字が入力できるようになるので、

好きな名前を入力しましょう。

今回は、「祝日」という名前で定義しました。

名前がちゃんと付いたか確認したい場合は、右側の下三角▼をクリックしましょう。定義した名前の一覧が表示されます。

今回は祝日のみを用意しますが、場合によってはお正月休みやお盆休みも間に追加しておくのもお勧めです。

学生の方は開校記念日や試験休みを定義するのも良いでしょう。

予定表の項目を作成する

まず、予定表の項目を設定します。

左から「日付」「曜日」「祝日」までは下の例と同じにして、その次は「予定1」など好きな項目を複数作成すると良いでしょう。

日付と曜日を設定する

次に日付と曜日を設定します。

日付は西暦で年月日の間に「/」を入れて設定します。

後で表示を月日や日付だけにすることもできますが、入力時は年号から入れる必要があります。また日付に対応した正しい曜日を隣に入力

あとは2つのセルを選択して、

下にオートフィル機能でコピーします。

列幅が狭くて「#」が表示されるので、「A列」を広げましょう

日付と曜日の設定ができました。

最初の日付と曜日が正確であれば、どれだけ下にコピーしても日付と曜日がずれることはありません。

祝日をVLOOKUP関数で設定する

では、祝日の設定です。ここは少し面倒。関数を使用します。

「祝日」の場所「C2」をクリックしたら、[関数の挿入]ボタンをクリック

[関数の分類]を[検索/行列]か[すべて表示]に変更して、[VLOOKUP]関数を選んで[OK]。


VLOOKUP関数・・・他の表から対応データを探し出し、一致した行中のデータを求める関数

VLOOKUP(検索値,範囲,列番号,検索の型[省略可])

  • 検索値・・・検索する値(一致させたい場所のセル参照や値、文字列)を指定
  • 範囲・・・検索する(一致させる)相手のデータ全体を指定
  • 列番号・・・「範囲」で指定したデータの、左から何列目と「検索値」を一致させるかを指定
  • 検索の型・・・完全一致させたい場合は「FALSE」、近似値で一致させたい場合は「TRUE」を入力。省略した場合は「TRUE」になる。(FALSEは「0」、TRUEは「1」でも可)

今回の引数は下のようになります。

  • [検索値]・・・A2
  • [範囲]・・・祝日
  • [列番号]・・・2
  • [検索方法]・・・FALSE

=VLOOKUP(A2,祝日,2,FALSE)

言葉にすると、「A2」番地の日付と、定義付けをした「祝日」データ範囲の日付の場所が一致した場合に、「祝日」データ範囲の2列目のデータを、完全一致(FALSE)で引っ張ってくるということ。

VLOOKUP関数に関して詳しく知りたい方は、Excel(エクセル)で行中で一致したセルを抽出する|VLOOKUP関数の使い方をご覧ください。

[OK]をクリックすると、下のように名前を付けて定義しておいた祝日が表示されます。

しかし、その関数を下にオートフィル機能を使用してコピーすると・・・エラーが表示されてしまいます。

今作成した関数に一致するデータが無い場合(祝日が存在しない日付)に、エラーが表示されてしまうのです。

それならば、エラー表示の場合は空欄になるようにしてしまいましょう。

使用するのはIFERROR関数。


IFERROR関数・・・数式の結果がエラーだった場合は指定した値を返し、それ以外の場合は数式の結果を返す関数

IFERROR(値,エラーの場合の値)

  • 値・・・エラーをチェックする数式を指定(エラーで無い場合はこの数式の結果が表示される)
  • エラーの場合の値・・・数式がエラーの場合に返す値を指定

IFERROR関数の[値]の場所に、先ほどのVLOOKUP関数を入れてあげます。

=IFERROR(VLOOKUP(A2,祝日,2,FALSE),””)

赤くした箇所が追加する場所です。今回はセルをダブルクリックするか、数式バーを使用して直接入力して修正するのが楽かもしれません。

最後の方に入力する「””」はダブルクォーテーションを2回入力です。「””」で空欄を意味します

[関数の挿入]から作成する場合は、IFERROR関数を最初に選び、下のように作成します。

修正が終わったら、オートフィル機能を使用して、下にコピーします。

エラーが消えました。

土曜日と日曜日を条件付き書式で設定する

では、土曜日を青色、日曜日と祝日を赤色に変更しましょう。

条件付き書式機能を使用します。

設定する範囲を行選択します。行番号を下に引っ張って選択

※今回は右側に予定の列が増えても対応できるように「行選択」しますが、使用しない右の列まで色が付くのが嫌な方は、「使用するセル範囲だけ」を選択しましょう。

[ホーム]タブ → [条件付き書式]をクリックして、

一覧から[新しいルール]を選択

表示された[新しい書式ルール]のダイアログボックスでは、[数式を使用して、書式設定するセルを決定]を選びます

そのまま下の[次の数式を満たす場合に値を書式設定]の場所に

=$B2=”土”

と入力します。

「B列」のセルが「土」だったらという条件です。

ポイントは「B」の前だけに「$」記号を付けること。「2」の前に付けてはいけません。これによって、「B2」より下の場所にも同じ設定が適用されます。

数式が入力できたら、右下の[書式]をクリック

条件を満たした時に塗りつぶす色を指定します。

好きな色を指定しましょう。今回は薄い青を選んでみました。

[OK]をクリックすると、前の画面に戻るので続けて[OK]

土曜日が薄い青で塗り潰されました。

続けて、日曜日と祝日の設定です。

先ほどと同じ[ホーム]タブ → [条件付き書式] → [新しいルール]で表示された画面に、

=OR($B2=”日”,$C2<>””)

と入力します。

OR関数を使用して、「B列」の場所が「日」か、「C列」の場所が空欄で無かったらという条件を作成しています。

やはり「B」と「C」の前にだけ「$」記号を付けるのを忘れずに。

設定ができたら、右下の[書式]をクリックして色の設定です。今回は薄いピンクを選んでみました。

設定完了です。

条件付き書式機能は、後で設定したものが優先されるので、土曜日で祝日だった場合はピンクが優先されます。

予定表の外観を整える

ここまできたら、後は自由に項目を強調したり線を引いたりするだけです。

項目を強調するようにして、線を引いてみました。

今回は1月だけ用意しましたが、2月以降も下に必要であれば、1番下のセル範囲だけ選択して、

右下の■を下に引っ張ってオートフィル機能を使用してコピーすれば、

設定した関数や条件付き書式もコピーされます。

違う月を別のワークシートに用意する場合は、ワークシートごとコピーして、最初の日付と曜日だけ修正して下にコピーするのが早いでしょう。

まとめ

手順は多くなりましたが、1度作成しておけば日付と曜日を変更するだけなので便利です。

年や月が替わる場合は、ワークシートをコピーして対応しましょう。

自分の予定表であれば、そのまま下にコピーして1年分を同じワークシートで作成してしまうのも良いですね。

 自分で予定表を作れば、レイアウトも自由自在にゃ
スポンサーリンク
関連コンテンツ