【Excel】簡単!便利!年月を指定するだけでカレンダーが作れます!
こんにちは!ハロー!パソコン教室イオンタウン新船橋校 鈴木(@hello_shinfuna)です。
パソコン教室では好きな日時で予約を入れてもらうために毎月カレンダーを印刷したものを生徒さんにお渡ししています。
↓こんなの
毎月お渡しする帳票なのでカレンダーの日付や曜日を簡単に変更できる方が断然楽ですよね。
というわけで上部にある年月を変更するだけでカレンダーの内容が一瞬でかわるように関数を使ってフォーマット化してあります。
よく一覧タイプのフォーマットを見かけますが、カレンダータイプとは作りが全く異なります。
以前、スタッフの吉沢が月が変わっても、自動で日付や曜日を表示するカレンダーの作り方という記事を書いてましたね。
入力した開始日を一覧の上部に表示させ、下のセルに行く毎に『+1』した日付を表示させる仕組みです。
至ってシンプルですね。
カレンダータイプの場合はフォーマットの曜日欄にあわせて日付を表示する必要があるので少し複雑になります。
ではどのような仕組みでフォーマット化すればいいのか見ていきましょう!
スポンサーリンク
日付と曜日欄のマッチングにはWEEKDAY関数を使って一工夫!
2017年12月1日は金曜日なので日曜始まりのカレンダーの場合、1行目の日~木までに日付は表示されません(当たり前w)。
1日が何曜日かは毎月変わってくるのでいつでも正確な曜日欄に日付を表示できるようフォーマット化しておく必要があります。
まず、2017年12月1日が金曜日であることはWEEKDAY関数を用いれば容易に分かります。
=WEEKDAY(シリアル値、種類)
シリアル値・・・日付が入力されているセルを指定
種類・・・戻り値の種類を指定
戻り値の種類というのが3パターンあるので、任意の形を指定します。
※だいたいのカレンダーは日曜始まりなので1を指定した方が分かりやすいかもしれません。
1(日曜=1〜土曜日=7)
2(月曜=1〜日曜=7)
3(月曜=0〜日曜=6)
というわけでWEEKDAY関数によって戻ってくる値は種類を『1』にした場合、
日曜=1 月曜=2 火曜=3 水曜=4 木曜=5 金曜=6 土曜=7 となります。
2017/12/1は金曜日なので戻り値は『6』になります。
このWEEKDAYDAY関数を使って求めた1日の戻り値を使ってどの欄から表示させればよいかを設定します。
実はカレンダーの下部に仕掛けがありまして(通常は非表示にしてます)、ここでマッチングをしているのです。
A18に表示されている『6』という値が =WEEKDAY(DATE(F1,G1,1),1)で返ってきた値です。
その上の行に表示されているA17~G17の1~7の数値は数式で求めたものではなく単に入力した値になります。
この値はWEEKDAY関数の戻り値である1~7の数値を意識したものになります。
※ちゃんとカレンダーのフォーマットに合わせて日曜のA列に1,月曜のB列に2,という流れになっています。
この17行目の値がミソで、A18で求めた値と17行目の値が合致した列が1日という仕組みになっています!
2017年12月でいえば戻り値が『6』、17行目に『6』が表示されている列はF列、というわけでF3から日付がスタートしているのです。
大まかな流れは上記の通りですが、今度はカレンダー内に設定してある関数を見ていきましょう。
1日の表示欄が決まればあとは日付+1をするだけの簡単な仕組み!
A3に入力されている数式
=IF($A$18=A17,DATE(F1,G1,1),"")
$A$18=A17 が一致していれば DATE(F1,G1,1) を表示、一致していなければ空欄を表示という数式です。
もちろんこの場合はA18が6、A17が1なので何も表示されません。
このままA3に入力されている数式をオートフィルかければいいのかな?と思いがちですが、この数式はA3だけの指定です。
B3に入力されている数式
=IF($A$18=B17,DATE($F$1,$G$1,1),IF(A3="","",A3+1))
前半の組み立てはA3に入力されている数式とほぼ同じです。
A18=B17であれば1を表示させますが、A18=B17でない場合は無条件に空欄を表示というわけにはいきません。
A3は1が表示されるか何も表示されないかの2択でしたが、B3~G3は数値される値が1とは限りませんよね。
そのため一つ前のセルの状態を確認しています。
B3の一つ前のセルはA3になるので、この場合はA3=空欄であればB3も空欄、A3=空欄でない場合(何かしたら値が入力されている状態)、その値に1を追加した値を返しています。
簡潔に言うと、WEEKDAY関数の戻り値と曜日列に割り振った値が一致すれば『1』を表示、一致しなければ一つ前のセルが空欄ではなければ『1』を追加した値を表示するといった数式です。
これでA3が『1』だった場合、B3には『2』と表示されるわけですね。
G3まではこの繰り返しです。オートフィルでB3の数式をコピーでOKです。
※オートフィルをかける前には必ず絶対参照の状態を確認しておきましょうね!
で、ここまでで3行目の設定は完了です。
ではカレンダーの2週目以降の数式は?というと、至ってシンプル。
1週目は『1』をどこで表示させるかという問題があったので、数式も少しややこしかったですが、2週目に『1』が表示されることはあり得ないので、一つ前のセルに表示されている値に1を追加していけばOKですね!
A5の場合 =G3+1
A6の場合 =A5+1
このままG13まで続きます。
これでカレンダーフォーマットは完成です!
行17~18は非表示にした状態でF1とG1の年月を変更してあげれば一瞬でカレンダーが完成します。
ちなみに日曜が表示されるA列はフォントの色『赤』、土曜が表示されるG列は『青』が書式設定されています。
完璧♪と思いたいのですが、このフォーマットは祝日を検出してフォントの色を『赤』に変えるという内容は組み込んでいません。
これをやると一気にハードルが上がるので実装せず^^;
祝日なんて年に数回しかないので、そこは手動でフォントの色を変えればいいかな~と妥協しちゃいました。
やる気のある方は是非祝日の色変え表示のフォーマット化にチャレンジしてみて下さい!
次回はこの予定表の続き
予定表をお渡しする生徒さんは数百名いらっしゃいます。
予定表には会員番号や氏名を印字しているのですが、生徒さんの人数分だけ毎回変更して印刷していると日が暮れてしまいます。
というわけでWordではおなじみですが、差し込み印刷的な機能を実装しています。
このおかげで放っておけば生徒さんの情報が差し込まれた予定表が数百枚印刷されている!という状態に。
かなり便利なのですが、やはり難易度は上がります。
乞うご期待!
いいね or シェアして友達と記事を共有しよう
このブログを定期購読するなら下のボタンをクリック!
パソコンの基礎からMOS対策講座まで、船橋市のわかりやすいパソコン教室です!
まずは、無料体験からお気軽にご連絡ください。
お問合せ(お電話)047-406-5786
〒273-0864 船橋市北本町1丁目830-1 イオンタウン新船橋店 マックスバリューさん2F
スポンサーリンク
- 2017.11.30 Thursday
- Excel
- 18:08
- comments(0)
- -
- -
- by ハロー!パソコン教室イオンタウン新船橋校