【 2016年9月15日  サイト 移転のお知らせ 】
      AddinBox サイトを [ DION ] から [ さくら インターネット ] へ移転しました。  なお、旧サイト は 2017年10月 まで残します。
      この ページ の 移転先 URL  ⇒⇒  http://addinbox.sakura.ne.jp/CalendarTopic.htm  
 
ロゴ(青) カレンダーの作り方 ロゴ(緑)
2014年5月23日、「山の日」改正祝日法が 可決/成立 しました(2016年1月1日施行)。

祝日一覧表は「祝日について」ページで作成できます。
      マクロでカレンダーを作る場合の祝日判定用の関数は「祝日マクロ」で出来ます。
      各種ツール/マクロを「山の日」対応で修正してありますので差し替えて下さい。

1.エクセルシートにカレンダーを作る

  「勤務表」などの為に『カレンダー』を作成したいという質問が良くあります。ここでは、エクセルシート上
にVBAに拠らず関数式だけでカレンダーを作成する方法を説明します。レイアウトとしては下記のように
【Box型】【縦型】【横型】(横型は縦型の応用になるので、ここでは省略します)の3種類がありますので、
順に説明していきます(Box型という名前は私が勝手にそう呼んでるだけで、『七曜タイプ』と呼んだ方が
正しいのかも‥‥‥)。
Box型 (七曜型)    縦型
 2002年1月    2002年1月
1   
      1 2 3 4 5 2   
6 7 8 9 10 11 12 3   
13 14 15 16 17 18 19 4   
20 21 22 23 24 25 26 5   
27 28 29 30 31       6   

2.【Box型(七曜)】カレンダー

  Box型カレンダーの場合、日付が入るのは、下記の37個のセルになります。
☆★印のセルは月によって、日付が入るか空白になるか変動します。
□印のセルには必ず日付が入ります。

  
  
  
  
  
  
     

  以降の説明で「開始日」とは、別に「1日固定」である必要はありません
開始日を1日にすれば『1日〜月末日』で出来上がりますが、16日にすれば『16〜翌月15日』
というカレンダーが自然と出来上がります。
(1) 開始日の入るセルは、その日の曜日セルになりますから、★印の位置の日付は
    =開始日 - WEEKDAY(開始日) + 1  ‥‥‥ (シリアル値)
になります。
   (例) 2002年1月1日  →  火曜日  →  WEEKDAY("2002/1/1")=
          →  ★日付 : ([2002年1月1日]−3+1)⇒[2001年12月30日]
 
(2) (1) が求まれば、各セルの日付は★印セルから順に
        =★の日付
        =★の日付+1
        =★の日付+2
            :
        =★の日付+36
で求まります(表示形式は全て"d")。
   (注) [直前のセル+1]という式にはしないで下さい。
基準日は★セルの日付固定です(実際の参照セルは(7)にあるように※セルにします)。
何故かと言うと、下記(3)/(5)によって★☆セルには【空白】が有り得るからです。
 
(3) 月初めの☆★印セルでの表示有無は
      =IF(そのセルの日付 < 開始日, "", そのセルの日付)
で決まります(期間外のセルは空白になります)。

 
(4) その月の表示対象の終了日は、開始日の「1ヵ月後応当日の前日」です。
      =DATE(YEAR(開始日), MONTH(開始日) + 1, DAY(開始日)) - 1
 
(5) 月末の☆印セルでの表示は
      =IF(そのセルの日付 > 終了日, "", そのセルの日付)
で決まります(期間外のセルは空白になります)。
 
(6) 祝日を[赤]にするには、祝日一覧をシート上に用意して、条件付書式を使い、下記条件式に
対して【文字色:赤】を指定します。日曜列/土曜列は元々のセル書式で文字色を【赤/青】に
しておいてください(VLOOKUP関数はMATCH関数でも代用できます)。
      =IF(xx="", FALSE, IF(ISERROR(VLOOKUP(xx, 祝日一覧, 1, FALSE)), FALSE, TRUE))
      (xx : 各々のセル)
    条件付書式には「外部ブックへの参照」となる式が記述できないので、『kt関数アドイン』の
    【kt祝日判定】は残念ながら利用できません。

    祝日一覧表は、以下の方法でシート上に作成できます。
        ・ 『kt関数アドイン』の祝日一覧表示メニュー
        ・ 『祝日について』 ページの「祝日一覧を表示」ボタンで作成してコピペ

週の各行の間に「隠し行」を挿入して、そこにkt関数アドインの『kt祝日判定』関数を書き込み
その結果を条件付書式で参照するという方法なら、祝日一覧表を用意する必要も無く祝日を
[赤]表示できます。『=kt祝日判定(ひとつ上のセル) 』という式で[True/False]が返ります。

     
     
   <----- 隠し行 -------> ひとつ上のセル日付の祝日判定式を記述する
     
   <----- 隠し行 ------->   
     
   <----- 隠し行 ------->   
     
   <----- 隠し行 ------->   
     
   <----- 隠し行 ------->   
        
   <----- 隠し行 ------->   

(7) ※印のセルが空いているので、その場所に「開始日、開始日の曜日値、★日付、終了日」を
書き込んでおきます。フォントカラーを『白』にすれば見えません。
 
(8) 1年分のカレンダーを作る場合は、上記のレイアウトをコピーして、「開始日」を【1ヶ月】ずつ
加算しておけば、他は自動的に定まります。
    =DATE(YEAR(年間基準日), MONTH(年間基準日) + n, DAY(年間基準日))
 
(9) 『月→』並びにする場合は、(1)の式
    =開始日 - WEEKDAY(開始日, 2) + 1
とします。


このBox型カレンダーのサンプルブックを下記に用意してありますので
ダウンロードしてみてください。
    サンプルブックのダウンロード(19KB)
        〃   のキャプチャ画像


    予定欄を設けたサンプルブック (16KB)


 
   (注) このサンプルブックでは1900年からの暦を作れますが、「1900年3月1日」より前の
日付
では、エクセルの仕様により、シリアル値と日付の対応が1日分ズレており、更
にはシリアル値「60」の日付が1900年2月29日として表示されるなど、正しく作成で
きません
。なお、1900年1月1日の曜日は『月曜日』が正解です。シリアル値「61」は
エクセルでもVBAでも『1900年3月1日』として表示されますが、シリアル値「1〜60」
の場合は、エクセルでは「1900年1月1日〜1900年2月29日」として、VBAでは「18
99年12月31日〜1900年2月28日」として表示されます(なお、1900年は閏年では
ありません)。
 
(10) 別 解 (2002/6/26 追記)
「第何週の何曜日の日付」というのは
    =DATE(年,月,1)-WEEKDAY(DATE(年,月,1))+7*(-1)+曜日
という式で求まります。ここで言う第何週とは7曜配置での1段目・2段目‥‥‥という意味
です。曜日は[1:日,2:月〜7:土]です。

この式を利用すると、もう少し簡単に七曜カレンダーが出来上がります。
    A1セルに年、B1セルに月
    A2〜G2には日〜土の曜日ラベル
    A3セルに下記の式を書き込みます
      =DATE($A$1,$B$1,1)-WEEKDAY(DATE($A$1,$B$1,1))
            +7*((ROW(A3)-ROW($A$3)+1)-1)+(COLUMN(A3)-COLUMN($A$3)+1)
                                []                                        [曜日]
A3セルの式をA3〜G8の範囲までフィルドラッグすると、以下のような日付で出来上がります。
 

      (A) (B) (C) (D) (E) (F) (G)
   (1) 2002 6               
   (2)
   (3) 5/26 5/27 5/28 5/29 5/30 5/31 6/1
   (4) 6/2 6/3 6/4 6/5 6/6 6/7 6/8
   (5) 6/9 6/10 6/11 6/12 6/13 6/14 6/15
   (6) 6/16 6/17 6/18 6/19 6/20 6/21 6/22
   (7) 6/23 6/24 6/25 6/26 6/27 6/28 6/29
   (8) 6/30 7/1 7/2 7/3 7/4 7/5 7/6

   この式では、1日より前/月末より後の日付も作られますので、条件付書式で
      [=MONTH(A3)<>$B$1 → フォント白]
としてから、フィルドラッグすると、「その月」以外の部分は隠れます。

[月〜]並びの場合は
      =DATE($A$1,$B$1,1)-WEEKDAY(DATE($A$1,$B$1,1),2)
              +7*((ROW(A3)-ROW($A$3)+1)-1)+(COLUMN(A3)-COLUMN($A$3)+1)
とするだけでOKです。曜日の値も[1:月,2:火〜7:日]に変わりますので
『曜日: COLUMN(A3)-COLUMN($A$3)+1 』の部分はそのままで大丈夫です。
 
(11) [火曜日]以降を先頭にして作る  (2002/7/17 追記)
[火〜月]並びを作る為には、火(1)〜月(7)という曜日値が得られる関数が必要ですが、
エクセル関数のWEEKDAY関数には、その機能がありません(VBAのWeekday関数では
第2引数[firstdayofweek]を指定する事で可能です)ので、下記の式で求めます。
      ・火(1)〜月(7)    =MOD(WEEKDAY(日付, 3) + 6, 7) + 1
      ・水(1)〜火(7)    =MOD(WEEKDAY(日付, 3) + 5, 7) + 1
      ・木(1)〜水(7)    =MOD(WEEKDAY(日付, 3) + 4, 7) + 1
      ・金(1)〜木(7)    =MOD(WEEKDAY(日付, 3) + 3, 7) + 1
      ・土(1)〜金(7)    =MOD(WEEKDAY(日付, 3) + 2, 7) + 1

この式を、上述した式内のWEEKDAY関数(下記太字)と置き換えれば出来上がりです。

(9) 『月→土・日』並びにする場合は、(1)の式を
          =開始日 - WEEKDAY(開始日, 2) + 1
              ↓
『火→月』並びなら
       =開始日 - (MOD(WEEKDAY(開始日,3)+6,7)+1) + 1

(10)別解
      [月〜土・日]並びの場合は
          =DATE($A$1,$B$1,1)-WEEKDAY(DATE($A$1,$B$1,1),2)
                +7*((ROW(A3)-ROW($A$3)+1)-1)+(COLUMN(A3)-COLUMN($A$3)+1)
                    ↓
『火→月』並びなら
          =DATE($A$1,$B$1,1)-(MOD(WEEKDAY(DATE($A$1,$B$1,1),3)+6,7)+1)
                +7*((ROW(A3)-ROW($A$3)+1)-1)+(COLUMN(A3)-COLUMN($A$3)+1)
 
   

3.【縦型】カレンダー

縦型カレンダーの作成方法はQ&A形式で説明します。
   [Q1] 年/月を指定して、その月の日付を表示したい。
   [Q2] 日付を、土曜[青]、日曜[赤]で表示したい。
   [Q3] 日付を、土曜[青]、日曜・祝日[赤]で表示したい。
   [Q4] 休日(土曜・日曜・祝日)の場合に、背景色を付けたい。
   [Q5] 第2/4土曜・日曜・祝日を休みにしたい。
   [Q6] 16日〜翌月15日というカレンダーにしたい。


[Q1]  年/月を指定して、その月の日付を表示したい。
    
月によって月末日が異なりますから、29〜31日部分の表示有無判定が必要になります。
C1に年、C2に月、[C5:C35]に日付というレイアウトの場合
        1日のセル(C5) ⇒ =DATE($C$1, $C$2, 1)
        2日のセル(C6) ⇒
                =IF(MONTH($C$5) = MONTH($C$5 + ROW() - ROW($C$5))
                                          , $C$5 + ROW() - ROW($C$5), "")
としたら、C6セルをC35セルまでオートフィルすれば出来上がります。
月末日を越えた位置のセルは空白になります。絶対参照($)は指定通りに付けて下さい。

もしくは、下記でも同じ結果が得られます。
      2日のセル(C6) ⇒ =C5 + 1
      3日のセル(C7) ⇒ =C6 + 1
              :
      28日のセル(C32)⇒ =C31 + 1
      29日のセル(C33)⇒ =IF(MONTH($C$5)=MONTH($C$32 + 1), $C$32 + 1, "")
      30日のセル(C34)⇒ =IF(MONTH($C$5)=MONTH($C$32 + 2), $C$32 + 2, "")
      31日のセル(C35)⇒ =IF(MONTH($C$5)=MONTH($C$32 + 3), $C$32 + 3, "")
 
[Q2]  日付を、土曜[青]、日曜[赤]で表示したい。
    
1日のセル(C5)の条件付書式で下記のように指定したら、31日のセル(C35)まで書式コピー
すれば出来上がりますが、
      条件1: [数式が] [=WEEKDAY(C5)=1] [フォント-色-赤]
      条件2: [数式が] [=WEEKDAY(C5)=7] [フォント-色-青]

D列を『曜日欄』とするなら、下記の方が、他のセルでも利用できるので効果的です。
(シート全体でのWEEKDAY関数の数を減らす事が出来ます。即ち、それだけ軽くなります
      1日のセル(D5) ⇒ =TEXT(C5, "aaa")
      2日のセル(D6) ⇒ =TEXT(C6, "aaa")
            :
      31日のセル(D35)⇒ =TEXT(C35, "aaa")
    (注)C列が空白セルでも、このままで大丈夫です。この式でC列に合わせて空白になります。

として、条件付書式は
      条件1: [数式が] [=D5="日"] [フォント-色-赤]
      条件2: [数式が] [=D5="土"] [フォント-色-青]
   
[Q3]  日付を、土曜[青]、日曜・祝日[赤]で表示したい。
    
[Q2]の条件にひとつ追加して
      条件1: [数式が] [祝日ならばという判定式] [フォント-色-赤]
      条件2: [数式が] [=D5="日"]                     [フォント-色-赤]
      条件3: [数式が] [=D5="土"]                     [フォント-色-青]
とすれば出来上がります(祝日を最初に判定します→祝日の土曜も[赤])。

祝日の判定方法は、
      祝日の一覧表をシート上に用意して、その範囲を日付をキーとして、[MATCH]関数
      などで検索する。
というのが一般的です。祝日一覧表は、以下の方法でシート上に作成できます。
      ・ 『kt関数アドイン』の祝日一覧表示メニュー
      ・ 『祝日について』 ページの「祝日一覧を表示」ボタンで作成してコピペ

ここでも、祝日を表す列を用意した方が効果的です。B列を祝日欄として、下記の式をセル
に書き込みます。
      1日のセル(B5) ⇒ =IF(ISERROR(MATCH(C5,祝日一覧の範囲, 0)), "", "祝")
      2日のセル(B6) ⇒ =IF(ISERROR(MATCH(C6,祝日一覧の範囲, 0)), "", "祝")
            :
そうすると、条件付書式は
      条件1: [数式が] [=B5="祝"] [フォント-色-赤]
      条件2: [数式が] [=D5="日"] [フォント-色-赤]
      条件3: [数式が] [=D5="土"] [フォント-色-青]
となります(祝日を最初に判定します→祝日の土曜も[赤])。

kt関数アドイン』の【kt祝日判定】を使うと、
                「祝日一覧」を用意しなくてもB列に祝日マークを設定できます。

      1日のセル(B5) ⇒ =IF(kt祝日判定(C5), "祝", "" )
 
[Q4]  休日(土曜・日曜・祝日)の場合に、背景色を付けたい。
    
条件付書式は下記のようになります(祝日を最初に判定します→祝日の土曜も[赤])。
      条件1: [数式が] [=B5="祝"] [フォント-色-赤] [パターン-色-強調する色]
      条件2: [数式が] [=D5="日"] [フォント-色-赤] [パターン-色-強調する色]
      条件3: [数式が] [=D5="土"] [フォント-色-青] [パターン-色-強調する色]
 
[Q5]  第2/4土曜・日曜・祝日を休みにしたい。
    
『第2週』等という日付範囲と違って、『第2土曜』等の『第2』は単純に
      日付:8〜14日
だけでOKです。同様に
      第1:1〜7日    第3:15〜21日    第4:22〜28日    第5:29日〜
となります。

A列を『第nX曜日』を算出する列("1土", "2土", "1月" 等の文字)として使用します。
      1日のセル(A5) ⇒ =IF(C5="", "", (INT((DAY(C5) - 1) / 7) + 1) & TEXT(C5, "aaa"))
      2日のセル(A6) ⇒ =IF(C6="", "", (INT((DAY(C6) - 1) / 7) + 1) & TEXT(C6, "aaa"))
B列の祝日欄を休日欄と改めて、「第2/4土曜・日曜・祝日」の時に""とします。
      1日のセル(B5) ⇒ =IF(ISERROR(MATCH(C5, 祝日一覧の範囲, 0)),
                                          IF(OR(A5="2土", A5="4土", D5="日"), "休", ""), "休")

条件付書式は下記のようになります(休日を最初に判定します→休日の土曜も[赤])。
      条件1: [数式が] [=B5="休"] [フォント-色-赤] [パターン-色-強調する色]
      条件2: [数式が] [=D5="日"] [フォント-色-赤] [パターン-色-強調する色]
      条件3: [数式が] [=D5="土"] [フォント-色-青] [パターン-色-強調する色]

kt関数アドイン』の【kt営業日判定】を使うと、
              「祝日一覧」を用意しなくてもB列に休業日マークを設定できます。

      1日のセル(B5) ⇒ =IF(kt営業日判定(休日マップ指定, C5), "", "休" )

休日マップ指定の部分には、
      「土曜・日曜・祝日」ならば『固定休日コード』で[ "10000011000@" ]
とすればOKです。
「第2/4土曜・日曜・祝日」の場合には、『休日マップの作成』で【休日マップ】をセルに
作成し、そのセルを指定します。

  (注) kt関数のように、外部ブックへの参照となる式は「条件付書式」に記述できないので、
        どうしても作業列を経由した処理になります。
 
[Q6]  16日〜翌月15日というカレンダーにしたい。
    
下記のような式で作り上げます。期間終端の判定は『月末日』を意識する事無く、単純に
『日が15まで』でOKです。C1に年、C2に月、[C5:C35]に日付というレイアウトの場合
      16日のセル(C5) ⇒ =DATE($C$1, $C$2, 16)
      17日のセル(C6) ⇒ =C5 + 1
      18日のセル(C7) ⇒ =C6 + 1
            :
      先頭から[28日目]のセル(C32)⇒ =C31 + 1
      先頭から[29日目]のセル(C33)⇒ =IF(DAY($C$32 + 1) < 16, $C$32 + 1, "")
      先頭から[30日目]のセル(C34)⇒ =IF(DAY($C$32 + 2) < 16, $C$32 + 2, "")
      先頭から[31日目]のセル(C35)⇒ =IF(DAY($C$32 + 3) < 16, $C$32 + 3, "")

【月末日〜翌月1日】の月跨り部分は、オートフィルで作った[=Cn + 1]の式で自動的に
出来上がります。
 



[ Home へ ]
ロゴ(ゴールド)   ロゴ(ゴールド)

角田 桂一 Mail:addinbox@h4.dion.ne.jp CopyRight(C) 2001 Allrights Reserved.