戻る        Q&A         印刷用(PDF)

エクセルで作る縦型カレンダー

前回は、月曜日から始まるボックス型のカレンダーを作りました。
今回は、1日から始まる縦型の万年カレンダーです。日付・曜日のほか、祝日や振替休日なども表示します。
日付を行方向に並べることで横型のカレンダーにすることもできます。

1.祝日・休日テーブルの作成  (ファイルはこちら)

カレンダー本体を作成する前に祝日・休日を表示するためのテーブルを作成します。
このテーブルでは、移動祝日(ハッピーマンデー)や振替休日・国民の休日にも対応します。
また、春分の日と秋分の日は、翌年の日にちを国立天文台作成の暦象年表に基づき、閣議決定後2月に広報され正式に決まります。
このため、ここでは天文学的な春分日・秋分日により算出することにします。

祝日・休日は次のようになっています。
名 称 日  付
元日 1月1日
成人の日 1月第2月曜日
建国記念の日 2月11日
春分の日 2月21日頃
みどりの日 4月29日
憲法記念日 5月3日
こどもの日 5月5日
海の日 7月第3月曜日
敬老の日 9月第3月曜日
秋分の日 9月23日頃
体育の日 10月第2月曜日
文化の日 11月3日
勤労感謝の日 11月23日
天皇誕生日 12月23日
※振替休日 日曜日と祝日が重なったとき
※国民の休日 祝日と祝日に挟まれた平日

※振替休日と国民の休日が重なったときは、振替休日とする。

下図のようなテーブルを作成します。
カレンダー本体は別のシートに作ります。

B2セルには西暦で年を入力しておきます。
最終的にはカレンダー本体の開始年をYEAR関数で取り出しこのセルに表示させます。
C列に祝日や休日の名称を入力し、B列にはそれぞれに合わせて該当する日付を表示させるように計算式を入れていきます。

C列には、振替休日や国民の休日となる可能性があれば、それぞれの祝日などの後にその名称を入力しておきます。
振替休日や国民の休日に該当したときは、B列にその日付を表示するよう設定します。

(1)日付の変わらない祝日
年の変化に対応できるように、B2セルの西暦年とリテラル(定数)からDATE関数で求めます。

(例) 元日の場合
    =DATE($B$2,1,1)

※行列番号に「$」を付加することで、計算式を他のセルにコピーしたとき、計算式中のセル番地が自動的に変化するのを防ぐことができます。
※行列番号で行番号・列番号ともに「$」の付いたものを絶対参照、ともに「$」のないものを相対参照、どちらか片方だけに付いたものを複合参照といいます。
Excelのヘルプでキーワードを「セルとセル範囲の参照」として検索すると解説が表示されます。

他の日付が固定されている祝日のセルにもこの計算式をコピーし、DATE関数の月と日を修正します。

(2)曜日の変わらない祝日
次の計算式で算出することができます。

(例1) 成人の日(第2月曜日)
     =DATE(B$2,1,14-WEEKDAY(DATE(B$2,1,0),3))
(例2) 海の日(第3月曜日)
     =DATE(B$2,7,21-WEEKDAY(DATE(B$2,7,0),3))
以下、該当する祝日のセルにコピーし、DATE関数にある「月」2ヶ所(数式の斜体部分)を修正します。

(3)春分の日と秋分の日
(春分の日)
  =DATE(B$2,3,INT(20.8431+0.242194*(B$2-1980)-INT((B$2-1980)/4)))
(秋分の日)
  =DATE(B$2,9,INT(23.2488+0.242194*(B$2-1980)-INT((B$2-1980)/4)))

※1.この数式は、1980年から2099年まで有効です。
※2.(出典)Addin Box http://www.h3.dion.ne.jp/~sakatsu/holiday_topic.htm

(4)振替休日
当該祝日が日曜日(WEEKDAY関数を使用)のときに振替休日と判定します。

(例) 元日(セルB4)が振替休日か判定 =IF(WEEKDAY(B4)=1,B4+1,"")

※1.IF関数の詳細は、Excelのヘルプでキーワード「IF」として検索してください。
※2.計算式の中で文字列を表示するときには「”」で前後を囲みます。
上記の例では「”」と「”」の間に何もないので、この場合そのセルには”何も”入りません。
これは「スペース(空白)」でも「0(ゼロ)」でもありません。

(5)国民の休日
前後が祝日の場合の平日は国民の休日となります。
ただし、振替休日と重なったときは振替休日が優先します。

(憲法記念日とこどもの日)
5月4日は必ず休日となります。ただし、5月3日の曜日によって次のようになります。

・5月3日が土曜日のとき: 休日(国民の休日でも振替休日でもない。例:2008年)
・5月3日が日曜日のとき: 振替休日(国民の休日ではない。例:2009年)
・5月3日が上記以外のとき: 国民の休日

(計算式)
・振替休日
    =IF(WEEKDAY(B13)=1,B13+1,"")
・国民の休日
    =IF(WEEKDAY(B13)=7,"",IF(WEEKDAY(B13)=1,"",B13+1))

(敬老の日と秋分の日)
敬老の日が月曜日に固定されているので、秋分の日の曜日により国民の休日となる可能性があります。

(計算式)
・両祝日間が2日間であることから判定
    =IF(B21-B19=2,B19+1,"")
・秋分の日が必ず水曜日になることから判定
    =IF(WEEKDAY(B21)=4,B19+1,"")

2.日付(年月日)と曜日を表示する (ファイルはこちら)

(1)カレンダー開始日セル用に行を挿入
「祝日」シート2行目の上に行を追加します。

@行番号の2と3をドラッグして選択(行全体が選択されます)
Aメニューから「挿入→行」とします。
B2行目と3行目の間に2行追加されます。

※1.行の挿入は、選択した範囲の上の行との間に、選択した行数分挿入されます。
※2.行の削除は、削除する行を選択し、メニューから「編集→削除」とします。
※3.列の挿入は、列を選択後「挿入→列」とすると、選択した列の左側に挿入されます。
※4.列の削除は、削除する列を選択後、メニューから「編集→削除」とします。

(2)カレンダー開始日セルの編集
祝日シートのカレンダー開始年月日の入力セルはB2セルとします。

@B2セルに外枠罫線を引きます。(ツールバー罫線ボタン、またはセルの書式設定)
AB2セルを薄めの色で塗りつぶします。(塗りつぶしの色ボタン、または書式設定)
BB3セルに注記を入力します。(例:「←年月日を入力してください。」)
CB2セルに仮の開始日として「2006/1/1」を半角で入力します。

※1.文字列は、右側のセルにデータがなければ、セル幅を超えて表示されます。
※2.数値のときは、セル幅が自動拡張されるか指数表示となります。
※3.演算結果がセル幅を超えると「###」のようにエラー表示されます。
※4.現在表示されている年と違う年を入力することで、次項YEAR関数が正しく入力されたか確認できます。

(3)祝日テーブル用の西暦年に数式を入力
仮入力した祝日テーブルの年のセルに、開始日から年を取り出す数式を入力します。
  セルB4に =YEAR(B2)  と入力します。

※YEAR関数が正しく入力されると表示は、「2006」となります。

(4)カレンダー用シートの挿入
カレンダー本体を作成するため別のシートを開きます。シートがないときは追加します。
シート名を「カレンダー」に変更します。

・シートの追加方法: メニューから「挿入→ワークシート」
・シート名の変更方法: ショートカットメニューから「名前の変更」
・シートの移動方法: シートタブをドラッグして移動

(5)カレンダーの日付欄の設定
カレンダーの日付を表示する欄を作ります。(セルB4〜B34までの31個のセル)
日付欄は入力した開始日から始まる日付のシリアル値になるように設定します。
「日」として表示させるのは、セルの書式設定により行います。

@1日のセル(セルB4)の数式: =祝日!B2 (開始日と同じ)

[数式入力の操作方法]
・カレンダーシートのセルB4をアクティブにする。
・「=」を入力し、シート名「祝日」、開始年月日(セルB2)を順にクリックする。
・数式バーに「=祝日!B2」と表示されるので、「Enter」キーを押して確定する。

A2日のセル(セルB5)の数式: =B4+1 (セルB4の翌日)

[数式中のセル番地の入力方法]
a.キーボードから直接セル番地を入力する。
b.マウスで該当するセルをクリックする。
c.カーソル移動キー(←、↑、↓、→)で当該セルまで移動する。

B3日以降のセル(セルB6〜B34): 2日の数式をコピーします。
Cセルの書式設定をします。(分類:ユーザー定義、種類:d

(6)曜日表示欄の設定
日付欄の右のセル(セルC4〜C34)を曜日欄とします。日付と同様シリアル値をセルの書式設定で曜日表示にします。

@1日の右のセル(セルC4)の数式: =B4 (セルB4と同内容)
AセルC4の数式を31日の右のセルまでコピーします。

[フィルハンドルのダブルクリックによるコピーの方法]
・セルC4をアクティブにします。
・セル右下にマウスポインタを合わせ字のフィルハンドルを表示させます。
・この状態でダブルクリックをするとセルC4の内容がセルC34までコピーされます。

Bセルの書式設定をします。(分類:ユーザー定義、種類:aaa

※曜日の書式の種類(例:土曜のときの表示例)
  aaa(土)、aaaa(土曜日)、ddd(Sat)、dddd(Saturday)

(7)年・月表示欄の設定
年はセルB2に月はセルB3に表示し、それぞれ「年」「月」をつけて表示します。

@年のセル(セルB2)に、数式「=B4」を入力します。
Aセルの書式設定をします。(分類:ユーザー定義、種類:yyyy”年”
B月のセル(セルB3)にも、数式「=B4」を入力します。
Cセルの書式設定をします。(分類:ユーザー定義、種類:m”月”

※数式の場合と同様に、書式設定でも文字列を扱うときは、「”」で文字を括ります。

3.祝日・休日の名称を表示する (ファイルはこちら)

D〜E列を使って祝日・休日の名称を表示します。

(1)祝日・休日テーブルの検索
日付をキーとして祝日・休日テーブルを検索し、結果をD列に表示します。このD列は名称表示のための作業領域として使い、カレンダー上では非表示とします。
関数の入力に、今回は関数貼り付けボタンを使ってみます。

@セルD4(1日の曜日表示の右隣)をアクティブにします。
Aツールバーの「関数貼り付け」ボタン(fx)をクリックします。
B関数の分類「検索/行列」、関数名「VLOOKUP」をクリックし「OK」します。
CVLOOKUP関数のダイアログボックスが表示されます。

D検索値欄右のダイアログ縮小ボタン(赤い矢印)をクリックします。
EセルB4(日付欄)をクリックし、検索値に「B4」を表示させます。
Fダイアログを元に戻すボタン(Bと同じ位置)をクリックし、表示を元に戻します。

G範囲欄右のダイアログ縮小ボタンをクリックします。
H祝日シートのB6〜C40(祝日・休日テーブル)をドラッグして選択します。
IF4キーを押し絶対参照にします。範囲欄の表示「祝日!$B$6:$C$40」を確認します。
Jダイアログ元に戻すボタンをクリックし、ダイアログ表示を元に戻します。

K列番号に「2」、検索の型に「FALSE」をそれぞれ半角で入力します。
L「OK」ボタンをクリックし、VLOOKUP関数のダイアログボックスを終了します。
MセルD4にVLOOKUP関数が入力され、検索結果「元日」が表示されます。
NセルD4の数式は、=VLOOKUP(B4,祝日!$B$6:$C$40,2,FALSE)となります。
OセルD4をセルD34までコピーします。

※1.このように、関数は関数貼り付けボタンを使って入力することもできます。
※2. 数式のセル番地の参照方法は、F4キーで変えられます。
※3. 検索の範囲は固定なので絶対参照とし、コピーにより変化しないようにします。
※4. 列番号の「2」は、祝日テーブル2列目の名称を表示することを表します。
※5. 検索の型が「FALSE」のときは、検索値が一致したものが表示対象となります。
   一致しないときは、エラー値「#N/A」 (使用する値がない)が返されます。
※6. VLOOKUP関数の詳細は、ヘルプで調べてみましょう。
※7. このような操作を行わず、Nの数式を直接セルD4に入力しても結果は同じです。

(2)祝日・休日の名称表示(エラー値の排除)
D列の検索結果からエラー値を除き、祝日・休日の名称のみをE列に表示します。
@セルE4をアクティブにします。
A数式 =IF(ISNA(D4),"",D4) を入力します。
BセルE4をセルE34までコピーします。
Cエラー値「#N/A」がなくなり、祝日・休日の名称だけ表示されます。

4.書式設定をして体裁を整える

(1)祝日・日曜日・土曜日の色分け
条件付き書式で、文字色を設定します。祝日と日曜日は「赤」、土曜日を「青」にします。
条件付き書式を設定すると、指定した条件のときだけ、設定した書式で表示されます。

@条件付き書式を設定する範囲B4〜E34(日付・曜日・祝日名)を選択します。 
Aメニューから、「書式→条件付き書式」を選択します。
⇒条件付き書式ダイアログボックスが表示されます。

B「条件1」に、祝日の条件と書式を設定します。
・左側のボックス右の▼をクリックして、「セルの値が」を「数式が」に変えます。
・右側のボックスに「=$E4<>””」と入力します。(セルE4に文字があるか)
・書式ボタンをクリック、フォントの色から「赤」を選択して「OK」します。
⇒セルの書式設定が閉じて、条件付き書式設定ダイアログボックスに戻ります。

C追加ボタンをクリックして「条件2」を表示させ、日曜日の条件と書式を設定します。
・左側のボックス右の▼をクリックして、「セルの値が」を「数式が」に変えます。
・右側のボックスに「=WEEKDAY($B4)=1」と入力します。(セルB4は日曜日か判定)
・書式ボタンをクリック、フォントの色から「赤」を選択して「OK」します。
⇒セルの書式設定が閉じて、条件付き書式設定ダイアログボックスに戻ります。

D追加ボタンをクリックして「条件3」を表示させ、土曜日の条件と書式を設定します。
・左側のボックス右の▼をクリックして、「セルの値が」を「数式が」に変えます。
・右側のボックスに「=WEEKDAY($B4)=7」と入力します。(セルB4は土曜日か判定)
・書式ボタンをクリック、フォントの色から「青」を選択して「OK」します。

E条件付き書式設定ダイアログボックスに戻ったら、「OK」で閉じます。
F祝日、日曜日、土曜日がそれぞれの色に変わったことを確認します。

※1.曜日の判定は、日付の入っているB列またはC列で行います。(列のみ絶対参照)
※2.数式中のセル番地は、アクティブセルを対象とします。
※3.祝日は、E列を使い名称の有無(文字の有無)で判定します。(列のみ絶対参照)
  この他、D列とISNA関数で判定することもできます。「=ISNA($D4)=FALSE
※4.条件付き書式では、3つの条件まで設定することができます。
※5.条件の優先順位は、条件1>条件2>条件3 となります。
  このため、土曜日より祝日の条件を先に記述する必要があります。

(2)作業用に使った列を非表示にする
祝日テーブルの検索結果表示に使った作業用の列Dを非表示にします。

@列番号のDをクリックし、列全体を選択します。
Aメニューから「書式→列→表示しない」をクリックします。

※「表示しない」とした列を再表示させるときは、非表示列を含む範囲(ここでは、列C〜列E)を列選択し、「書式→列→再表示」とします。ショートカットメニューから選択することもできます。

(3)その他の書式を設定して完成させる (ファイルはこちら)
カレンダー部分の仕上げとして体裁を整えて完成させます。
a. 用紙サイズ、印刷の向きを決めます。(ファイル→ページ設定)
b. 列幅や行高の変更(範囲選択して境界をドラッグ、または書式→列・行→幅・高さ)
c. フォントのサイズ・色、塗りつぶし色の設定(書式→セル→フォント・パターン)
d. セルを結合し表示幅を広げる。(「セルを結合して中央揃え」または書式→セル→配置)
e. 罫線を引く。(ツールバーの罫線、または書式→セル→罫線)

※プレビュー画面で全体を確認しながら調整します。

5.画像を挿入して完成です (ファイルはこちら)

カレンダーの右側に好みの画像を挿入します。

@事前に表示する画像を用意しておきます。(今回はサンプル画像を使用します。)
A「ファイル→ページ設定」で印刷の向きを確認します。(今回は「横」とします。)
B画像を挿入するセルをアクティブにします。(今回はセルF2とします。)
Cメニューから「挿入→図→ファイルから」として画像を挿入します。
Dプレビューを表示させ、余白の調整をして閉じます。
Eシートに、ページ範囲が破線で表示されます。
F画面の表示をズームで75%程度にして、全体を見ながら画像の大きさを調整します。
Gブックを保存してから印刷してみましょう。

(参考) 子青水墨画廊

6.オプション

(1)年を和暦表示にする
「平成17年」 ← 表示形式を「ggge”年”」にします。
「平17年」 ← 表示形式を「gge”年”」にします。
「H 17年」 ← 表示形式を「ge”年”」にします。
「2006年 (平成17年)」 ← 表示形式を「yyyy”年 (“ggge”年)”」にします。

(2)月を英語表記にする
「January」 ← 表示形式を「mmmm」にします。
「Jan」 ← 表示形式を「mmm」にします。

(3)個人予定を表示する
祝日の表示と同様の機能を追加することで、個人の予定を表示することができます。

@予定テーブルを作成する
祝日テーブルとは別の領域に、個人用の予定テーブルを作成します。テーブルの行数を多めにして、領域を確保します。
A検索結果表示用の列を作成する
検索結果を表示する列(作業用)を挿入し、VLOOKUP関数でテーブルを検索します。
B予定表示用の列を作成する
予定を表示する列を挿入します。IF関数とISNA関数でエラー表示を排除します。また、作業用の列を「表示しない」設定にします。
C条件付き書式を設定する(表示の色などを変えるとき)
予定の設定された日の色などの変更は、条件付き書式で行います。ただし、すでに「条件3」まで使用済みのため、祝日(条件1)と日曜日(条件2)を次のように統合し、「条件1」とします。
  =OR($E4<>””,WEEKDAY($B4)=1)
個人予定を土曜日表示より優先するときは、「条件2」として設定します。

(4)月末日以降を非表示にする
翌月となる可能性のある日付の月が当月と同じでないときは、「日」を表示しないように変更します。

29日用以降のセルの数式を次のように変更します。
・29日用(セルB32): =IF(MONTH($B$31+1)=MONTH($B$4),$B$31+1," ")
・30日用(セルB33): =IF(MONTH($B$31+2)=MONTH($B$4),$B$31+2," ")
・31日用(セルB34): =IF(MONTH($B$31+3)=MONTH($B$4),$B$31+3," ")

※数式の最後の部分「," ")」は「"」と「"」の間に空白を1つ入れます。

戻る