Office 2019 Excel 2019

年と月を選択するだけで繰り返し使用できる予定表を作成(関数使用)

投稿日:2021年7月11日 更新日:

Excelで予定表(スケジュール表)を作成することは多いと思いますが、毎月、作成するのは面倒ですね。

ここでは、[年]と[月]を選択するだけで、予定表が作成できるように関数を使って作成します。

一度作成すると、繰り返し使用できます。便利なオリジナルの予定表を作成してみませんか?

また、土日と祝日のセルには条件付き書式で塗りつぶしの色を設定します。

もし、作成済みの予定表などがあれば、活用できそうな要素だけでも取り込んでみてください。

繰り返し使用できる予定表

表の作成

予定表の項目や、罫線などは、以下のようにしています。

セル[A2]からセルC[33]までを予定表の範囲として罫線(格子)を設定して、セル[E2]からセル[F3]は、年と月を表示する表としています。

できれば、罫線は作成後に設定されるほうがいいと思いますが、ここではわかりやすくするために先に設定しています。

罫線は、任意で設定してください。

予定表の罫線と項目

[年]と[月]をリストから選択できる入力規則を作成

年と月をドロップダウンリストから選択できるようにします。

ここでは、セル[E3]に[年]を、セル[F3]に[月]を選択できるようにします。

セル[E3]を選択して、[データ]タブの[データツール]グループにある[データの入力規則]をクリックします。

[データ]タブの[データの入力規則]

[データの入力規則]ダイアログボックスが表示されます。

[設定]タブを選択して、[入力値の種類]で[リスト]を選択します。

[元の値]のテキストボックスに、リストに表示したい数値(年)を入力して、[OK]ボタンをクリックします。

ここでは、以下のように入力します。カンマで区切ります。

2021,2022,2023

[データの入力規則]ダイアログボックスでリストの[年]を入力

セル[E3]も同じように、[月]を表示できるように以下のように設定します。

1,2,3,4,5,6,7,8,9,10,11,12

[データの入力規則]ダイアログボックスでリストの[月]を入力

セル[E3]とセル[F3]は、ドロップダウンリストから選択できるようになります。

年と月は、表示しておきます。

ドロップダウンリストの確認

DATE関数で日付を表示

セル[A3]にDATE関数を使って、日付を表示します。

セル[A3]にDATE関数を入力

年と月は、セル参照にします。セル[A3]には、[2021/7/1]と表示されます。

=DATE(E3,F3,1)

セル[A3]の数式バー

[関数の引数]ダイアログボックスです。

[関数の引数]ダイアログボックス-DATE

DATE 関数 - Office サポート(Microsoft)

参考[関数の挿入]ダイアログボックスや[関数の引数]ダイアログボックスの表示については、以下の記事で解説しています。

関数を使って作業効率化(関数の探し方とダイアログボックスの表示)

Excelの関数がわかると、作業を効率化できます。 [関数の挿入]ダイアログボックスや[関数の引数]ダイアログボックスの表示方法を解説します。 また、 ...

続きを見る

一か月分の日付を表示

セル[A4]に[=A3+1]と入力します。[2021/7/2]と表示されます。

=A3+1

セル[A4]に数式を入力

オートフィル機能を使って、セル[A33]までドラッグして数式をコピーします。

数式をコピー

セルの書式設定で曜日を表示

日付のセルには、曜日を表示するように設定します。

セル[A3]からセル[A33]まで範囲選択して、[セルの書式設定]ダイアログボックスを表示します。

ショートカットキーは、[Ctrl]+[1]です。

キーボード[Ctrl]+[1]

[表示形式]タブの[ユーザー定義]を選択します。

[種類]のテキストボックスに[yyyy/m/d(aaa)]と入力して[OK]ボタンをクリックします。

[yyyy/m/d]は表示されていると思いますので、[(aaa)]のみ入力します。

yyyy/m/d(aaa)

[セルの書式設定]-[表示形式]タブの[ユーザー定義]

選択したセル範囲に、曜日が表示されるようになります。

曜日が表示されるようになった日付

参考もし、曜日だけ隣のセルに表示させたい場合は、以下のようにセル[B3]に[=TEXT(A3,"aaa")]と入力してください。

=TEXT(A3,"aaa")

TEXT 関数 - Office サポート(Microsoft)

TEXT関数

土日のセルに塗りつぶしを設定

土日と祝日のセルに条件付き書式を使って、塗りつぶしを設定します。

セル[A3]からセル[A33]まで範囲選択して、[ホーム]タブの[スタイル]グループにある[条件付き書式]から[新しいルール]をクリックします。

[ホーム]タブの[条件付き書式]-[新しいルール]

[新しい書式ルール]ダイアログボックスが表示されます。

日曜日のセルに塗りつぶしを設定

[数式を使用して、書式設定するセルを決定]を選択して、[次の数式を満たす場合に値を書式設定]の数式ボックスに、以下のように入力します。

WEEKDAY関数は、曜日を割り出す関数です。戻り値は、日曜=1~土曜=7となります。

1を指定することで[日曜]を判別できるわけです。

=WEEKDAY(A3)=1

WEEKDAY 関数 - Office サポート(Microsoft)

[新しい書式ルール]ダイアログボックス

そして、[書式]をクリックして、塗りつぶしの色を設定します。

[書式]をクリックすると、[セルの書式設定]が表示されますので、[塗りつぶし]タブで任意の色を指定します。

ここでは、[その他の色]をクリックして、[色の設定」の[標準]タブで指定しています。

参考もし、塗りつぶしではなく、フォントの色を指定したい場合は[フォント]タブで色を指定します。

[セルの書式設定]の[塗りつぶし]タブ

以下のように設定できたら、[OK]ボタンをクリックします。

[新しい書式ルール]の日曜日の設定

土曜日のセルに塗りつぶしを設定

同じように土曜日のセルにも塗りつぶしを設定します。

数式は、以下のように入力します。

=WEEKDAY(A3)=7

[新しい書式ルール]の土曜日の設定

以下のように、土日のセルに塗りつぶしが設定されます。

土日のセルに塗りつぶしが設定された予定表

参考土日のセルではなく、行に塗りつぶしを設定したい場合は、塗りつぶしを設定したい範囲を選択して、条件付き書式で、以下のように入力します。

=WEEKDAY($A3)=7

以下の記事が参考になると思います。

条件付き書式で土日の行に塗りつぶしを設定

ドロップダウンリストから選択して自動的にセルに塗りつぶしを設定

Excelで表を作成してセルに入力する文字列が決まっている場合は、データの入力規則を使ってドロップダウンリストから選択して入力できるようにすることがで ...

続きを見る

祝日のセルに塗りつぶしを設定

祝日のセルに塗りつぶしを設定するには、先に祝日リストを作成します。

祝日リストの作成

別シートに以下のサイトを参考に祝日リストを作成します。

国民の祝日について - 内閣府

2021年の祝日リストです。会社の記念日などでお休みになる日があれば追加してください。

日付は、文字列ではなく、日付形式にします。

祝日リスト

参考祝日リストは、NETWORDAYS関数や、WORKDAY関数などでも使用します。

営業日数を求めるNETWORKDAYS関数とNETWORKDAYS.INTL関数

Excelで土日と祝日を除いて、開始日から終了日までの日数を求める方法です。 NETWORKDAYS関数を使います。 サンプル表の確認 ここでは、下の ...

続きを見る

開始日から指定した営業日後の日付を求めるにはWORKDAY関数

土日と祝日などを除いて、開始日から指定した日数の経過した日付を求める方法です。 WORKDAY関数を使います。 土日以外の曜日を除きたい場合は、WOR ...

続きを見る

範囲に名前付け

セル[A2]からセル[A18]までを範囲選択して、名前を付けます。

名前は、[名前ボックス]に入力して[Enter]キーを押します。任意の名前で構いません。

ここでは[祝日]とします。

[名前ボックス]に範囲の名前を入力

参考範囲に名前を付けたり修正する方法は、以下で解説しています。

セル範囲に名前を定義して数式で使用する

Excelでは、セル範囲に名前を定義しておくと、関数の引数に名前を利用することができます。 定義された名前は、[数式]タブの[数式で使用]ボタンから簡 ...

続きを見る

条件付き書式で設定

条件付き書式で以下のように設定します。塗りつぶしの色も任意で指定してください。

数式には、COUNTIF関数を使用します。

[=COUNTIF(範囲,検索条件)]でセル範囲の中で検索条件にあう数を求めます。以下は、日付が祝日リストの中にある場合、書式設定をするという意味になります。

=COUNTIF(祝日,A3)=1

COUNTIF 関数 - Office サポート(Microsoft)

新しい書式ルールで祝日の設定

以下のように祝日に塗りつぶしが設定されます。

祝日に塗りつぶしが設定された予定表

タイトルも自動表示

セル[B1]にタイトルを入力しますが、このタイトルも自動で切り替えられるようにします。

セル[B1]に[=A3]と入力します。

セル[B2]の数式

セル[B1]を選択して、[セルの書式設定]ダイアログボックスを表示します。

[表示形式]タブの[ユーザー定義]を選択して、[種類]のテキストボックスに以下のように入力して[OK]ボタンをクリックします。

yyyy年m月の予定表

ダブルクォーテーションは、自動で付きますので、一度、閉じて開くと以下のようになっています。

yyyy"年"m"月""の""予""定""表"

以下のように入力した方が分かりやすかもしれません。

以下のように入力すると、表示が変わることはありません。文字の部分はダブルクォーテーションで囲むということです。

yyyy"年"m"月の予定表"

[セルの書式設定]-[表示形式]タブの[ユーザー定義]

以下のように表示されます。

セル[B2]の表示

参考[セルの書式設定]の[表示形式]には便利なものがあります。

郵便番号と電話番号の簡単入力(東京都内の電話番号は超便利)

セルに郵便番号を入力する場合、7桁の番号を連続して入力しても、自動で3桁目の後ろに[-](ハイフン)を表示させることができます。 また、電話番号は先頭 ...

続きを見る

タイトルを中央配置に

タイトルを表の中央に配置します。

セル[A1]からセル[C1]を範囲選択して、[ホーム]タブの[配置]グループにある[セルを結合して中央揃え]をクリックします。

[ホーム]タブの[セルを結合して中央揃え]

タイトルが中央に配置されます。フォントサイズなども任意で調整してください。

中央に配置されたタイトル

セル[E3]とセル[F3]の値を変更して、自動で日付とタイトルが変更するかを確認します。

月を変更した予定表

翌月の日付が表示されないようにする

月の日数は、28日から31日までなので、31日より少ない場合は、翌月の日付が表示されてしまいます。

翌月の日付が表示された予定表

翌月の行を非表示にしてもいいですが、ここでは、翌月の日付が表示されないように関数で設定します。

セル[A31]に以下の数式を入力してみます。

セル[A30]の月[MONTH]が同じならば、[+1]して、そうでなければ[""](空白)にする、という意味です。

=IF(MONTH(A30)=MONTH(A30+1),A30+1,"")

MONTH 関数 - Office サポート(Microsoft)

関数の引数ダイアログボックス-IF

セル[A30]は空白になりますが、数式をセル[A33]までコピーすると、以下のようにエラー[#NALUE!]が表示されてしまいます。

上のセルが空白だからですね。

数式をコピーしてエラーが表示された予定表

数式を修正します。

IFEEROR関数を使って修正

以下は、[IFERROR]関数を使って、エラーであれば空白にするという意味です。

=IFERROR(IF(MONTH(A30)=MONTH(A30+1),A30+1,""),"")

[関数の引数]ダイアログボックス-IFERROR

IFERROR関数は、Excel2007以降で使用できる関数です。

VLOOKUP関数のエラー回避はIFERROR関数で

Excelでデータを参照する場合、VLOOKUP関数を使います。 下の左の表、セル[C2]からセル[C7]には、VLOOKUP関数が入力されていて、右 ...

続きを見る

IF関数をネストして修正

または、以下のようにします。

セル[A30]が空白ならば空白にして、そうでなければ数式を実行するという意味です。

=IF(A30="","",IF(MONTH(A30)=MONTH(A30+1),A30+1,""))

[関数の引数]ダイアログボックス-IF

セル[A31]の数式を修正して、セル[A33]までコピーします。

翌月の日付は非表示になりました。

数式のエラーが修正された予定表

印刷範囲の設定

プリントアウトするときのことを考えて、印刷範囲の設定をしておきます。

印刷したい範囲を選択します。

ここでは、セル[A1]からセル[C33]までを範囲選択して、[ページレイアウト]タブの[ページ設定]グループにある[印刷範囲]から[印刷範囲の設定]をクリックします。

[ページレイアウト]タブの[印刷範囲]-[印刷範囲の設定]

印刷プレビューで確認します。クイックアクセスツールバーの[印刷プレビューと印刷]をクリックします。

クイックアクセスツールバーの[印刷プレビューと印刷]

または、ショートカットキー[Ctrl]+[P]を使用します。

キーボード[Ctrl]+[P]

Backstageビューの[印刷]が表示されます。

もし、用紙の中央に印刷したければ、[設定]の下にある[ページ設定]をクリックして、

Backstageビューの[印刷]

[ページ設定]ダイアログボックスの[余白]タブで[ページ中央]の[水平]と[垂直]のチェックボックスをオンにして[OK]ボタンをクリックします。

[ページ設定]ダイアログボックスの[余白]タブ

ページの中央に配置されます。

後は、任意で諸々の変更や修正をしてください。

中央に配置された予定表

行の高さを大きくしたり列を追加したりすると、1ページに収まらず、はみ出してしまうこともあると思います。以下の記事を参考に調整してみてください。

1ページに収めて印刷するには[縮小印刷]機能で[1ページ]を指定

ExcelでA4用紙の縦一枚に印刷したいけど、少しだけページからはみ出してしまう場合の解決方法です。 余白の調整で1ページに収めることもできますが、[ ...

続きを見る

印刷時の余白の調整はBackstageビューの[印刷]から

Excel2010での余白の調整方法です。 A4用紙の縦一枚に収めるつもりだったけど、少しだけページからはみ出してしまう。どうにか1ページに収めたい、 ...

続きを見る

参考以下の記事も合わせて読んでいただくと、さらに便利な予定表を作成できるかもしれません。

ドロップダウンリストから選択して自動的にセルに塗りつぶしを設定

Excelで表を作成してセルに入力する文字列が決まっている場合は、データの入力規則を使ってドロップダウンリストから選択して入力できるようにすることがで ...

続きを見る

チェックボックスを使って条件付き書式のオンとオフを切り替える

フォームコントロールのチェックボックスを使って、チェックがオンであれば条件付き書式の書式を設定して、オフにすると解除するような仕組みを作ることができま ...

続きを見る

リストボックス(コンボボックス)の選択項目でセルの行に色を付ける

フォームコントロールのリストボックス、またはコンボボックスを設置して、リストで選択した項目を含むセルの行に塗りつぶしを設定します。 リストボックスは、 ...

続きを見る

また、Excelの日付セルから会議の設定やOutlookの予定表を起動することもできます。

日付を右クリックしてOutlookの会議の設定やカレンダー(予定表)を表示

Excelの日付のセルで右クリックしてショートカットメニューから、Outlookの会議や予定表を表示することができます。 Microsoft Outl ...

続きを見る

検索

ほかの記事も検索してみませんか?

Access / Excel / PowerPoint / Word

created by Rinker
★★★★★ 分かりやすい!Excelの基本はこれでOK♪
Amazonのレビューを見る

Profile

執筆者/はま

-Office 2019 Excel 2019

Copyright © 2001-2024 初心者のためのOffice講座 All Rights Reserved.

Copyright© 初心者のためのOffice講座 , 2024 AllRights Reserved Powered by AFFINGER4.