完成型

前回まで使った技術でできる内容ですので、興味がある人はチャレンジしてみてください。

機能としては、
1） 最初に表示させる年月を入れると、自動的にその先5ヶ月分のカレンダーを表示させます。
2） 祝日を予め設定しておけば、該当する日付に祝日名が表示されます。
これらの機能を関数を使って実装していきます。

項目としては以下の通りです。
台紙をダウンロード

① 下準備としてfunction(機能)欄を作る

①-①祝日表を作ります

台紙からには予め入っています

①-② スタート月の5ヶ月先の「月」を自動で表示させる

「2018年10月」を最初の月に設定しました。 ですので、5ヶ月先は「2018年11月、2018年12月、2019年1月、2019年2月、2019年3月」となります。

青い文字の部分に数字を入れたら、自動で5ヶ月分先まで出したいと思います。

表示ロジックを考える

では、自動で5ヶ月先を表示するロジックを考えましょう。 「ある数字を1つづつ足し、12月までいけば次は1月に戻る」このような日本語を作ります。コレが最初の一歩です。 慣れないうちはまず、やりたいことを日本語の文章を何処かに書いておくと良いと思います。

次に、もう少しプログラミング的な文章に直してみます。
「もし、ある数字"n"に1を足した数字が12より小さい場合、"n"に1を足す。そうでなければ1にする」
nが10の場合、10+1=11となり12より小さいので10+1を実行、nが12になったとき12+1は12より大きくなってしまうので1にする。 この文章を導き出せれば、答えは出たようなものです。

もしIF、　ある数字nに1を足したn+1数字が　 12より小さい 12場合、nに1を足すn+1。　 そうでなければ1にする1

=IF(n+1  12, 真の場合(n+1), 偽の場合(1))

↓セルW5に入れる式
=IF(
  W4+112,  // セルW4に1足した数字が12より小さければ
  W4+1,     // セルW4に1を足す
  1         // そうでなければ1にする
)

↓セルW6に入れる式
=IF(
  W4+212,  // 2を足したら
  W4+2,
  1
)

このような式が成り立ちますが、このままではまだ不十分です。実際の結果はこうなります。

このままだと12月になったときに1月になってしまいますし、1になった後に、また1ヶ月づつ足していく計算が必要になります。 もう少し条件を絞り出して、式を作ってみましょう
もし、(IF)
　ある数字nに1を足した数字が12より小さい場合、 (n+1 12)条件式
　nに1を足す。(n+1)真
　そうでない場合、もし、(IF)偽 + もし
　　nが12なら、(n=12)条件式
　　12にする(12)真
　　そうでなければ(n+1) - 12(n+1) - 12偽を実行する
1つ目の偽の式は「12より小さくない場合」という条件にマッチした式になりますが、 その数字nに1を足した数字が「12」になった場合と「13」になった場合は計算式が異なります。 ですので、偽の場合の式に再度、IFで始まる条件分岐の式を入れることになります。

=IF(
  W4+112,     // "n"であるセルW4に1足した数字が12より小さければ
  W4+1,        // W4に1を足した数字を入れる
  IF(          // そうでなければ、もし、
    W4+1=12,   // その数が12だったら
    12,        // 12にする
    (W4+1)-12  // そうでなければ、n+1した数字から12を引く
  )
)

↓セルW5に入れる式
=IF(
   $W$4+112,
   $W$4+1,
   IF(
      $W$4+1=12,
      12,
      $W$4+1-12
   )
)

↓セルW6に入れる式
=IF(
   $W$4+212,
   $W$4+2,
   IF(
      $W$4+2=12,
      12,
      $W$4+2-12
   )
)

こうすれば、12より大きい13月や14月になってもその数から12を引けば、13は1月に、14は2月と表示されることになります。 また翌々月以降のセルにはそれぞれ「足す数字」を変えれば良いですね。 また、最初の月は絶対参照にしたいので$で固定します。

または、全く別の考え方でも式を作ることが出来ます。
ある数字"n"に1を足した数字を12で割った“余り”を表示する
という考え方です。 こちらの方がよりプログラミング的な発想と言えるでしょう。 もし、
　ある数字"n"に1を足した数字が12より小さい場合、
　"n"に1を足す。
　そうでない場合、"n"に1を足した数字を12で割って余りを出す
だいぶ短くなりましたね。 割り算した余りを出すにではMOD関数を使います。

MOD(割られる数, 割る数) = 割り算の余りを求める

=IF(
   $W$4 + 1 = 12,        // n+1が12より小さいか、同じなら
   $W$4 + 1,            // n+1を表示
   MOD( ($W$4 + 1), 12)   // そうでない場合(n+1が13以上)、n+1を12で割った余りを表示
)

↓その次の月の式
=IF(
   $W$4 + 2 = 12,
   $W$4 + 2,
   MOD( ($W$4 + 2), 12)
)

条件式の「n+1=12」は「12“以下”(12含む)」と言う意味です。 これは、12月の時に「真の式」が実行されるようにするためです。試しに「=」を取ると「0」と表示されます

①-③ スタート月から12月を超えた場合に年を1つ繰り上げる。

	W	X
4	10	2018	←初期値
5	11	2018
6	12	2018
7	1	2019	←ここが転換！どうやって2019にする？
8	2	2019
9	3	2019

最終的にはこのような表になれば良いですね。多少力技になりますが、計算式を作っていきましょう。
同じように日本語文章を作りましょう。

まず考えられるのが、「前月より当月の数字が大きい場合」ですね。
6行目(当月)は5行目(前月)より大きい。7行目(当月)は6行目(前月)より小さい。
といった違いが出てくるはずなので、そこを探し出します。
もし、
　前の月より当月の数字が大きい場合、
　初期値を入れる。
　そうでない場合、初期値に1を足す

=IF(
  W4  W5,     前の月より当月が大きいなら
  X4,         そのまま
  X4 + 1        1足す
)

まずはこのような式が考えられると思います。実際入れてみましょう。

確かに1月は2017年と表示されましたが、2月はどうでしょう？ 2月は1月より大きいので“真”となり初期値"2018"が表示されました。
そこで考えられるのが、「前の月の"年"が、初期値より大きいなら」という条件式を更に足すことです。 もし、
　前の月より当月の数字が大きい場合、かつ、前の月の「年」が初期値より大きいなら、
　初期値を入れる。(true)
　そうでない場合、初期値に1を足す(false)

X5に入る式
=IF(
   AND(W4  W5, X4 = $X$4),
   $X$4,
   $X$4 + 1
)

X6に入る式
=IF(
   AND(W5  W6, X5 = $X$4),
   $X$4,
   $X$4 + 1
)

X7に入る式
=IF(
   AND(W6  W7, X6 = $X$4),
   $X$4,
   $X$4 + 1
)

X7にはいる式では、条件式「W6 W7」が「12月 1月」となり「偽判定」となるので、X7のセルの値は「2018+1」となる。
X8にはいる式では、前の月「1月」の年「2019」は初期値の年「2018」より大きく「偽判定」となるので、X8のセルの値は「2018+1」となります。
初期値は絶対参照($)にしました。 一旦これでfunction(機能)エリアは完成です。

② カレンダー部分を作る

②-① functionで作った"月"を代入

functionエリアで作った数字を、カレンダー部分に代入していきます。 直接カレンダー部に書いても良いのですが、まとめて書いたほうが編集しやすいのでfunctionエリアに書いています。 "="でセル番号を指定するだけで代入完了です。

②-② 28日以降の日付

月によっては30日で終る月もあれば31日で終わる月もあります。2月に至っては28日だったり29日だったりします。 このあたりを条件式を使って実装していきましょう。

まずは28日まではどの月もあるので、28までは入れておきます。 29日以降の式としては「もし X 年 Z 月に"30日"という日が存在すれば、表示する、そうでなければ表示しない」 という日本語での文章ができます。2018年10月に31日があれば表示する。という感じです。
日にちを計算したいときには日付用の関数、DAY関数とDATE関数を利用します。
"DAY"関数 ：シリアル値からその日の"日"を取得　DAY(シリアル値) = 日付が出ます
　 "DATE"関数：日付をシリアル値に変換する　DATE(年,月,日) = シリアル値が出ます

シリアル値とは「1900 年 1 月 1 日」を「1」とし、その日からの通算日数を表した数値です。 =DAY(42756)という式を書くと結果は"21"が出力されますが、これは2017年1月21日の"21"が出力されたことになります。
※セルに42756と書いて、表示形式を日付にすれば2017/1/21と表示されます。

xxxx年zz月ww日の「日」の部分を出すには、

=DAY(DATE(2016, 10, 31))  //こうすると「31」という数字が取得できます。

例えば2016年にはありますが、2017年の2月に29日はありません。

=DAY(DATE(2016, 2, 29))
↓
29

=DAY(DATE(2017, 2, 29))
↓
1

この"1"という値は、3/1の"1"になります。仮に(2017, 2, 31)だと"3"（3/3の3）と出力されます。

というわけで、29日のセルに、下記のような式を書くと

=IF(
  DAY(DATE(X8, W8, 29))=29,    //もし[X8]年[W8]月29日がほんとにあれば、29を出して
  29,
  ""
)

2017年2月の場合、=DAY(DATE(X8, W8, 29))の結果は1となり「1=29」と式が成り立たないので偽、つまり空白になり、 2017年3月の場合、=DAY(DATE(X8, W8, 29))の結果は29となり「29=29」と式が成り立ったので真、つまり29が表示される。
※X8とW8はfunctionに記入している月と日になります
※29の部分を30、31と適宜変えて入力しましょう。

※googleスプレッドシートの場合は、色付けの際に必要なので1〜28の日付も関数で出しておきましょう。

=DATE($X$4,$B$4,1)

②-③ 曜日を自動で表示させる

曜日は"DATE"関数で出力されたシリアル値から自動で表示できます。

=DATE(年, 月, 日)  //年と月と日のセルを入力します

セルを選択して右クリックし、セルの書式設定を表示させ、図のように表示形式の種類でaaaと記述します。

そうすると、下のように日付から曜日に表示が替わります。

=DATE($X$4,$W$4,C4)

※googleスプレッドシートの場合はTEXT関数で指示します。

=TEXT(DATE($X$4,$W$4,C4), "dddd")

ただし、29日以降は日付がない場合もあります。日付がない場合は曜日も表示させないように変更します。

=IF(
  C32="",              // もし、29日以降の日付が何もなければ
  "",
  DATE($X$4,$W$4,C32)  // 日付があればの式
)

あとは、オートフィルでコピーしてください。絶対参照すべき値に注意してください。
曜日は以上です。

②-④ 祝日を自動で表示させる

祝日を表示させるには、functionエリアの祝日の文字列を検索して該当すれば表示。という感じにします。 文字列を検索する関数はCOUNTIFです。

まずは日本語の文章で式を書きます。 もし、この日が祝日の範囲の"祝日日"と同じ日なら、"祝日名"を表示する。同じ日がなければ空白に。 つまり、この式を配置する日が、例えば2016年10月1日だった場合、 その日と同じ日が祝日表の中にあれば、真(true)、なければ偽(false)を実行するという式です。

COUNTIF(範囲, "検索条件") となります
COUNTIF(Z5:Z39, DATE(年, 月, 日))  //範囲内に検索条件と同様のものがあれば、その数を返す

=IF(
  COUNTIF(Z5:Z39, DATE(X4, W4, C4)),
  真,
  偽
)

「Z5:Z39」この範囲が祝日の日付が入っている範囲です。COUNTIFで検索します。 合致すれば数字"1"が返り、true判定になります。
偽のところ（合致する祝日がなかった）は空白で良いですが、 真になった場合は"祝日名"を自動で挿入したいです。

=IF(
  COUNTIF(Z5:Z39, DATE(X4, W4, C4)),
  真,    // 合致した日付の「◯◯の日」という祝日名を入れたい
  ""
)

検索値をキーにして、同じ行、別の列にある値を取得する関数はVLOOKUPです。

VLOOKUP(検索値, 範囲, 列番号, [検索方法])　ですので
今回の場合は、

VLOOKUP(2018年10月8日が, 祝日の範囲での場所, 祝日の範囲の何列目を表示させるか, [完全に一致した場合のみ])
↓
VLOOKUP(DATE(X4,W4,C14), Z5:AB39, 3, 0)  //という式を作ります

2018/10/8は"体育の日"です。祝日表にも2018/10/8がありますので、 完全一致した日付の行の、3列目である"体育の日"という文字列を、trueの場合表示させることになります。

ですので、祝日を表示させたいセルに記述する内容は、

=IF(
  COUNTIF(Z5:Z39, DATE(X4, W4, C4)),
  VLOOKUP(DATE(X4, W4, C4), Z5:AB39, 3, 0),
  ""
)

となります。絶対参照値を気をつけながらオートフィルでコピーすれば完成です。

③ 土日に色を付ける

まず、色分けしたい範囲を選択します

条件付き書式から、一番下のルールの管理を選びます

+ボタンをクリックします

スタイルからクラシックを選択

数式を使用して、書式設定するセルを決定を選択

=WEEKDAY($D4)=1     // 引数が1もしくは書かなければ、1が日曜日になります（1:日、2:月、3:火、4:水、5:木、6:金、7:土）
=WEEKDAY($D4, 2)=1  // 引数に2を書くと、1は月曜日になります（1:月、2:火、3:水、4:木、5:金、6:土、7:日）
=WEEKDAY($D4, 3)=1  // 引数に3を書くと、1は火曜日になります（1:火、2:水、3:木、4:金、5:土、6:日、7:月）

と入力してOKボタンをクリック。[注 意] $K$4ではなく、$K4です。
D4,5,6,7...と続くセルで=1つまり日になるところという意味になります。

googleスプレッドシートの場合は「曜日」でなく「日付」を出している関数で曜日を判別しますので、

1. ツールバーの[123]アイコンをクリック
2. プルダウンの最下部にある「表示形式の詳細設定」の「カスタム数値形式」をクリック
3. 入力フィールドに "d日" または "dd日" と入力し「適用」をクリック

文字色や背景色を設定します

書式: からユーザー設定書式を選択

文字色や背景色を設定します

祝日に文字が入っていれば、色をつける設定

これで土日に色がつき、祝日が入るところは自動で色がつきます。 また、この表示順にルールが適応されます。一番上が一番強いです。

例題が出来たら答え合わせをしてみましょう。

ダウンロード