- Excelで作ったカレンダー日付や曜日だけでなく、祝日や下位者の休日も付け加えたい。
- より一般的なカレンダーの形に変えたい。
- 1日ごとに記入できる項目欄を増やしたい。
こんな悩みを解決できるカレンダーを作っていきます。
今回はDATE関数やWEEKDAY関数、文字列に関する関数と条件付き書式設定を使っていきますので、必要に応じてこちらもご確認ください。
DATE関数とWEEKDAY関数で形を作る
祝日一覧表を作る
月外日数と祝日を条件付き書式設定で色掛けする
いつもみたいに練習用ファイルも載せておくのでご活用ください。
DATE関数とWEEKDAY関数で形を作る
決まった形ではありませんが、冒頭で挙げたように次の部分で加工しやすいようにしておきます。
- 日付と祝日が記載される欄(セル)を用意しておく
- 1日毎に記入できる項目を増やしておく(今回は3行分)
手順1、祝日記入欄を作っておく
B、D、F、H、J、L、N列のセル幅を「3」に設定します。
Ctrlキーを押しながらB、D、F、H、J、L、N列をクリックすると複数列同時に選択できます。あとは右クリック→「列の幅」から列の幅を3に設定します。
またA3～A6セルまでを選択して[ホーム]タブの中の「セルを結合して中央揃え」でセルを結合しておきます。結合したセルには『第1週目』と入れておくのがわかりやすいかと思います。
あとは『第1週目』が入っているセルをオートフィルで『第6週目』まで表示させてください。
続いて、B2セルに『月』と入れてC2セルと「セルを結合して中央揃え」をして、『日』が表示されるまでオートフィルします。これで下準備は終わりです。
手順2、日付を入れる数式を入れていく
B3セルがカレンダーの一番上に表示されます。しかし、カレンダーの左上≠月初とは限らず、前の月の最後の何日かであるパターンが多いです。なのでB3セルに
このように数式を入れておきます。なお今回は2018年8月のカレンダーを作ると仮定してC1は『2018』、E1『8』としておきましょう。
=DATE(2018/8/1)-WEEKDAY(2)
WEEKDAY関数の種類ですが今回3です。よって以下のようになります。0…日
1…月
2…火
3…水
4…木
5…金
6…土
このようになるので『2018/8/1』の2日前である『2018/7/30』がB3セルに入ります。
DATE関数とWEEKDAY関数に関してわからなくなったらこちらをご確認ください。
ただ、デフォルトのセルの書式設定が「短い日付形式(yyyy/m/d)」となっているので『##』と表示されてしまいます。なのでセルの書式設定をしていきます。
これでB3セルは『30』と表示されていればOKです。
祝日一覧表を作る
手順3、新しく祝日一覧表を作る
さきほど作った「祝日一覧表」のシートにそのまま貼り付けます
しかしこのままでは文字化しているので文字列として年、月、日としてそれぞれ抽出をしていきます。
手順4、文字列を取り出してDATE関数を作る
『○○年』の○○を取り出す。
=IFERROR(LEFT(B1,4),””)
左から4番目の文字を取り出します。
『○○月』の○○を取り出す。
=IFERROR(IF(MID(B1,6,2)=MID(B1,6,1)&”月”,MID(B1,6,1),MID(B1,6,2)),””)
もし、6文字目から2つを取り出した時に『○月』となるなら6文字目から1文字だけ取り出し、そうでないなら6文字目から2文字取り出します。
『○○日』の○○を取り出す。
=IFERROR(MID(B1,FIND(“月”,B1)+1,2),””)
『月』という文字の1文字となりの文字から2文字分取り出します。
あとは取り出した○○年○月○○日の○をDATE関数で日付にして
G1セルに
=IFERROR(DATE(D1,E1,F1),””)
H1セルには
＝IF(C1=””,””,C1)
としておきます。
なお、すべてIFERROR関数から始まっていますがこれは予備の欄を作っておく為です。これは年によって日曜と祝日が被って振替休日が増減したり、新しい祝日が追加されることや人によって会社特有の休日を付け加えることができるようにするためです。
※H1セル＝IFERROR(C1,””)とするとC列が空欄の場合は『0』が帰ってきてしまうのでIFERROR関数ではなくIF関数にしてください。
あとは、上でも説明しましたが予備分まで含めて40行目までD1:H1の範囲をオートフィルで引っ張りましょう。
手順5、セル自体に名前を付ける
手順7で使いますがすごく簡単に説明すると、
「祝日一覧表に記載されている日付(◆と仮定)＝カレンダーの日付(▼と仮定)」
この条件に当てはまったセルにだけ色掛けをする条件付き書式設定を使います。しかし実は問題があり、対象となる◆は１つのセルでなければなりません。なので複数のセルを1つのセルとして認識させるのが「手順5、セル事態に名前を付ける」という事です。
前置きが長くなりましたが簡単です。
①G1:G30セルを選択します。
②この部分を祝日と入力します。これで完了です。
もし該当範囲の変更や名前を変更したいときには、「数式」タブ→「名前の管理」を選択すれば以下のようなダイアログボックスが開かれるのでこちらで変更してみてください。
月外日数と祝日を条件付き書式設定で色掛けする
手順6、カレンダーの形を完成させる
「祝日一覧表」シートから離れてカレンダーを作成していた元々のシートに戻ります。
C3セルに次のような数式を入れてください。
=IFERROR(VLOOKUP(B3,祝日!$G$1:$H$20,2,FALSE),””)
今回の検索範囲は別シートになっていますが、検索範囲まで数式を入れたら祝日一覧表シートを選べば自動的に検索範囲を別シートから選ぶことができます。[シート名]!が別シートから検索範囲を引っ張っている部分です。
あとはIFERROR関数で該当する日付がない場合は空欄にするとしておけば完了です。
続いて、B4:C4セルを選択して右クリック→セルの書式設定を選びます。ダイアログボックスの「配置」タブを選び以下のように設定してOKを押してください。
設定したらB6:C6までオートフィルをします。そしてB3:C3からB6:C6まで選択したら再度セルの書式設定を選び今度は罫線を設定します。罫線はお好みで設定してください。
手順7、月外日数と祝日を条件付き書式設定で色掛けする
一番の難所なので頑張りましょう。
B3セルに以下の条件付き書式設定を設定していきます。具体的な流れはこんな感じです。
- 月外の書式設定に =B3<DATE($C$1,$E$1,1)
- 月外の書式設定に =B3>DATE($C$1,$E$1+1,0)
- 祝日の書式設定に =COUNTIF(祝日,B3)=1
数式の相対参照・絶対参照をミスするとオートフィルが使えなくなります。必ず間違えないようにしましょう。
なお書式設定例をチャートにしてみました。
カレンダーの月を2018/1と2018/8で比較してみます。
しっかりと反映されています。
しかし注意していかなければならない点があります。それは「ルールの優先度」です。↓で図解しています
続いて祝日の内容が表示されているC3セルにも同じような設定をします。
- 月外の書式設定に =B3<DATE($C$1,$E$1,1)
- 月外の書式設定に =B3>DATE($C$1,$E$1+1,0)
- 祝日の書式設定に =COUNTIF(祝日,B3)=1
こちらをC3セルで条件付き書式設定しましょう。
手順8、オートフィルで形を整えていく
あと少しで終わりますので、頑張っていきましょう。
B3:C3からB6:C6まで選択したらD・E列にオートフィルをします。その際に注意してほしいのがオートフィルをしたときに、右下に「オートフィルオプション」が出ます。
オートフィルオプションのリストから「書式のみコピー」を選んでください。
同様に、B7;C10セルまで下にオートフィルを掛けていきましょう。これは手順6で設定したセルの罫線や、手順7で設定した条件付き書式設定だけを他の日付にコピーしています。
そして、
E3セルにはC3セルの数式をコピーして貼り付けてください。
C7セルにはC3セルの数式をコピーして貼り付けてください。
あとは順にオートフィルすれば完成です。
最後に「ページレイアウト」タブから印刷向きを縦から横に設定して、「表示」タブで改ページプレビューを使って範囲内に収まっているか確認しましょう。
Excelのバージョンによっては余裕があったり、逆に幅を調整する必要があるので列や行の幅を調整してみてください。
終わりに
これでカレンダーは完了しました。大事なのはカレンダーを作る過程で使った関数やExcelの機能を使いこなして組み合わせていくことです。
VLOOKUPとCOUNTIFで複数条件を取り出す方法など、あらゆる使い方を自分で組み合わせていければ中級者になったといえると思います。
皆さんもいろいろやってみましょう。
