下の記事で、リフィル作成に必要な関数を紹介しました
この記事では、年間カレンダーの作成を例として
- どのようなExcel機能（特に関数）を使うのか
- どのように日付関連の機能を作っていくのか
について、実際に関数をどう使っているのか紹介したいと思います
関数を使ったカレンダーの作成
私の場合、年間カレンダーは当月分のみの表示にした方が見やすく感じるため
↓のような形式で作成しました
ここでのポイントは「前月と次月の日付は表示しない」こと
そのために、全体の6行を以下のように分けて関数を組む事にしました
- 1行目
- 2～4行目
- 5～6行目
1行目の条件式
1行目は「１日以前の日付は表示しない」ことが条件となります
これをExcelに落とし込むと
- 「１日の曜日」と「縦列の曜日」が一致した場所に「１」を表示する
- 「１日の曜日」よりも「縦列の曜日」が古い場合は「空白」を表示する
- 「１日の曜日」よりも「縦列の曜日」が新しい場合は「２以降」を表示する
という事になります
早速ですが、具体的な数式は以下になります
月曜列
=IF(WEEKDAY(DATE(“年”,”月”,1),2)=1,1,””)
火曜列（火曜列以降は※の数字が3、4、5、、と増えます）
=IFERROR(IF(WEEKDAY(DATE(“年”,”月”,1),2)=2※,1,C7+1),IF(WEEKDAY(DATE(“年”,”月”,1),2)=2※,1,””)“”)
以下、使った関数の説明です
- DATE関数
指定した年月日をシリアルナンバー（1990年1月1日を”1″とした続き番号）に変換する関数
後述の関数はすべてシリアルナンバーが必要なので使用します
使い方は「DATE(“年の値“,”月の値“,”日の値“)」
例：DATE(1990,1,3) → 「3」が返ってくる - WEEKDAY関数
シリアルナンバーから曜日を1～7の数値で返す関数
曜日始まりを示す数値で何曜始まりかを指定できます(日曜始まり：1、月曜始まり：2といった具合)
使い方は「WEEKDAY(“シリアルナンバー“,”曜日始まりを示す数値“)」
WEEKDAY(44197,2) → 「5」が返ってくる
※「44197」は2021/1/1のシリアルナンバー、「2」は月曜始まりのオプション
月曜を1として火曜が2・・・となるので、金曜である5が返ってくる - IF関数
条件を指定して、その条件を満足た場合と満足しなかった場合の二つの値を返す関数
使い方は「IF(“条件式“,”条件を満足した場合“,”条件を満足しなかった場合“)」 - IFERROR関数
指定した値がエラーだった時に別の値を返す関数
「＃VALUE」や「＃REF」が表示される時に値を表示させたい場合に使います
使い方は「IFERROR(“値や計算式“,”エラー時に返したい値や計算式“)」
例えば、月曜列に使用した関数
=IF(WEEKDAY(DATE(“年”,”月”,1),2)=1,1,””)
は
DATE関数で「カレンダーに記載する年の月初(1日)のシリアルナンバーを取得」
→WEEKDAY関数で「シリアルナンバーを月曜始まり形式で曜日を表す数字(1～7)に変換」
→IF関数で「変換した数字が月曜の値(1)と合っていれば「１日」を、合っていなければ「空白」を表示」
という事をやっている訳ですね
2～4行目の条件式
中盤の3行(2行目～4行目)に関しては特に条件はいりません
前の日付からそのまま+1すれば良いだけなので、以下の式を使います
=”前の日付セル”+1
5～6行目の条件式
後半の2行(5～6行)については、次月の日付を表示させないために
- 月末までの日付はそのまま表示
- 月末+1日以降は「空白」を表示
という考えで数式を組み立てています
具体的な数式としては
=IF(DATE(“年”,”月”,”前日”)+1>=DATE(“年”,”月”+1,1),””,”前日”+1)
となります
また、中身の説明を大雑把にすると
【表示させたい日の前日】と【次月初日】を【シリアル値】で比較
→【表示させたい日の前日 + 1】が【次月初日】以上になれば「空白」を表示
※表示させたい日の前日が「2/28」の場合、シリアル値で+1すると「3/1」になるため空白を表示する
→そうでなければ「表示させたい日の前日+1(つまり当日)」を表示
当日を表示させるための判定に、前日の値を参照している訳です
条件付き書式を使った祝日表示
お次は祝日の表示について
祝日については
Wikipediaの「国民の祝日」を参考にしました
→ https://ja.wikipedia.org/wiki/国民の祝日
こちらを参考に条件式書式を作っていきます
条件付き書式は
- 条件付き書式を使いたい範囲を選択する
- 「ホーム」タブの「条件付き書式」→「新しいルール」を選択
で使えます（図は2月の条件付き書式を設定する場合）
選択すると以下の画面が出てくるので
「指定の値を含むセルだけを書式設定」を選択します
ルールの内容については、今回は「次の値に等しい」を選択してください
「次の値に等しい」を選択した画面
あとは「ここに条件式を代入」と書かれた場所に任意の値や数式を代入し
「書式」ボタンから自分の好きなように書式を設定すれば完了
代入する際ですが、関数が使えます
例えば、2020年から2月23日が「天皇誕生日」として祝日になりましたが
2月23日が日曜の場合は24日に振り替えられます
これを関数として条件式にすると
=IF(WEEKDAY(DATE(2020,2,23),2)=7,24,23)
となります
※WEEKDAYを月曜始まりのオプションで使用、日曜である7が帰ってきたら24を返し、それ以外は23を返す
この関数をそのまま条件式の欄に代入できるので、後は書式を設定すれば完了
全ての祝日の関数表現をここで紹介するとめちゃくちゃ長くなるので、残りは割愛させてください(-_-;)
Excelサンプルファイル（バイブル横表示 下綴じ）
最後に、私が作成した年間カレンダーをサンプルとして上げておきます
個人利用の範囲内でしたらご自由にお使いください
※個人利用の範囲内でのみ使用してください
・改変したものを配布する際は、必ず当ブログまでご連絡下さい
おわりに
今回は年間カレンダーを例にとった、Excel機能の使い方を紹介しました
Excel機能（関数や条件付き書式）に関してはハードルが高く感じるかもしれませんが
調べてみると他サイト様でも丁寧な説明がされていたりして、案外と簡単に出来たりします
ぜひ、Excelを使ったリフィル作りに挑戦してみて下さい
それでは次の記事でお会いしましょう
読んでいただき、ありがとうございました～
コメント