今回は「VBAを使わないけど地味にハイテクな予定表の作り方」についてです。
目次
１．初めに
皆さんは会社での業務予定をどのように管理していますか？
予定管理表・スケジュール表のような、何かしらを用意しているのではないでしょうか？
そもそも管理しない・カレンダーを使用する・手帳に記入するなど人それぞれ多様なやり方があると思いますが、業務上で使用するならやはりExcelを用いた管理が多いでしょう。
今回は、VBAを使用しないけど地味にハイテクな予定管理表を作成する手順を書いていこうと思います。
Excel関数は多様しますが、VBAは一切使用しません。
VBAって聞くだけで頭が痛くなる方もいるでしょうからね。
誰でも作れるをモットーにして本記事を書いているので、手順通りに作ってもらえば内容を理解していなくてもそれなりのものができるようになっています。
※ Excel関数の説明など、何をやっているかの詳細は別のページで行うような形式にしています。
最終的には以下のような機能などがついた予定管理表になります。
- 開始日と終了日を入力するとその期間が塗りつぶされる
- 土日祝日などの休日の列が自動で塗りつぶされる
- 日付の経過に対応している
個人利用するならこの程度の機能が付いていれば充分だと思います。
２．予定管理表作成手順
予定表を作成するにあたりExcel関数を使用したりExcel特有の機能をいくつか使用することがあります。
先程述べた通り、関数や機能の詳しい説明は省いていくので、何をやっているのか気になったら個別に用意したリンク先を参照してください。
また、予定表を表示するシートの他にもう1つシートが必要なので、予定表を作るシートは「予定表シート」、別のシートは「その他シート」という名前としています。
以後の説明で出てくるので覚えておいてください。
それでは、早速予定管理表を作っていきましょう！
２－１．年・月・日・曜日を表示する
予定を立てるには日付を入力しないことには始まらないので、まずはそこからです。
直接入力するのは面倒なので、関数をどんどん使っていきます。
まず、「その他シート」を開き、C2セルに「=TODAY()」と入力して今日の日付を表示させます。
この状態で隣のD2セルに「=C2+1」と入力して翌日の日付を表示させます。
後は、D2セルを選択した時に右下に現れる■をクリックした状態で右方向にドラッグしましょう。
こうすることで、“今日”を基準に日付を1日ごとに横並びに表示することができます。
この日付の並びを予定表に使用する為、長めに表示させておきましょう。
次に、TEXT関数を用いて月・日・曜日を別個に表示していきます。
「予定表シート」に戻り、G4セルに「=TEXT(その他!C2,”m”)」、G5セルに「=TEXT(その他!C2,”d”)」、G6セルに「=TEXT(その他!C2,”aaa”)」と入力します。
※ 以降も数式を入力する指示が何回も出てきますが、全部コピペで問題ないです。
TEXT関数を使うと月や曜日を表示できるようになるので、この数式の場合はG4セルに月、G5セルに日付、G6セルに曜日が表示されます。
この例を作成しているのは2021年1月22日(金)なので、以下のように表示されます。
数式を入力したG4・G5・G6セルを選択して右にドラッグして月・日・曜日の表示は完了です。
どこまで右に伸ばすかはお任せしますが、ここでは説明の為にBZセルまで複製したとします。
TODAY関数を参照させているので、G4・G5・G6セルには”今日”のステータスが表示されます。
その為、勝手に日付が更新されていくようになるので、納期設定をすると少しずつ納期が差し迫ってくる様を見ることができます(笑)
年の表示もどこかに1ヶ所欲しいので、西暦で表示したければ「=TEXT(TODAY(),”yyyy”)」、和暦で表示したければ「=TEXT(TODAY(),”ggg”)」と「=TEXT(TODAY(),”e”)」という数式を使用します。
しれっと”令和”も表示されるようになっています。(2022年現在)
２－２．レイアウトをざっくり作る
ここに関しては『こうした方が良い』という答えは無いです。
自分が必要だと思う項目を作って大体のレイアウトを作りましょう。
私は予定日と実施日、その開始日と終了日の比較ができれば充分なので、以下のようなレイアウトにしています。
特にこだわりがないのなら、まずはこれ通りに作ってみればいいんじゃないかな？
この調子で行は100まで拡張しています。
つまり、右下端はBZ100セルになっています。
区分という欄は後で使います。
２－３．枠を固定する
開始日や日付の表示されたセルはスクロール時にその場から動かないで欲しいので、枠を固定します。
G7セルを選択した状態で「表示」⇒「ウィンドウ枠の固定」⇒「ウィンドウ枠の固定(F)」と選択することで枠が固定できます。
『枠の固定って何？』と思った方はとりあえずやってみてください。
そうすれば理解できます。
２－４．開始日と終了日の間のセルが塗りつぶされるようにする
Excelは、ルールを設定することでセルを自動で塗りつぶすことができます。
この機能を使って、開始日と終了日を入力するとその間のセルが自動で塗りつぶされるようにしていきます。
詳しいやり方は以下の記事からどうぞ。
塗りつぶしの色を個人的に区別したかったので、区分に”予定”と入力してあれば薄緑色、”実際”と入力してあれば薄青色になるようにしてみます。
別の色を使用したい場合は、以下の説明で薄緑色・薄青色にすると言っている部分を好きな色に変更してください。
まずは薄緑色の設定です。
表の右下端がBZ100セルなので、G7:BZ100の範囲を選択します。
G7セルを押して、Shiftキーを押しながらBZ100セルをクリックすると楽です。
ここで選択した範囲は勝手に絶対参照になりますが、そのままで良いです。
この状態で「ホーム」⇒「条件付き書式」⇒「新しいルール」を選択します。
すると、ルールの種類を選択する画面が表示されます。
ここで「数式を使用して、書式設定するセルを決定」を選択し、数式を以下のように入力してセルの色を薄緑色に設定します。
=AND(その他!C$2>=$E7,その他!C$2<=$F7,$D7=”予定”)
これで、区分に”予定”と入力した状態で開始日と終了日を入力すれば対応する期間が薄緑色に塗りつぶされるようになりました。
次に薄青色の設定です。
基本は薄緑色の時とやることは同じです。
数式と選択する色が変化するだけです。
G7:BZ100の範囲を選択します。
この状態で「ホーム」⇒「条件付き書式」⇒「新しいルール」を選択します。
ここで「数式を使用して、書式設定するセルを決定」を選択し、数式を以下のように入力してセルの色を薄青色に設定します。
=AND(その他!C$2>=$E7,その他!C$2<=$F7,$D7=”実際”)
これで、区分に”実際”と入力した状態で開始日と終了日を入力すれば対応する期間が薄青色に塗りつぶされるようになりました。
この時点でそれっぽい形になってますね。
“予定”と”実際”の部分を好きな文字に変えればその文字に対応した色に塗りつぶす設定にできるので、その辺りは自分でカスタマイズしてみるといいかもです。
２－５．区分の色分けをする
区分の”予定”と”実際”の部分も開始日～終了日を塗りつぶした色と同じように変化させておこうと思います。
特に不要だと思ったら２－６に進みましょう。
まずは薄緑色の設定です。
D7:D100の範囲を選択します。
この状態で「ホーム」⇒「条件付き書式」⇒「新しいルール」を選択します。
ここで「指定の値を含むセルだけを書式設定」を選択し、「セルの値」、「次の値に等しい」と選択した状態でルールの内容を「=”予定”」にしてセルの色を薄青色に設定します。
これで、区分に”予定”と入力したセルの色が薄緑色に塗りつぶされます。
後は薄青色の設定ですが、ルールの内容を”予定”から”実際”、色を薄緑色から薄青色に変更して複製するだけなので説明は割愛します。
２－６．休みの日を塗りつぶす
土日祝日などの休日を識別できるようにする為に色分けの設定します。
人によって休みの形態は異なるので、ここでの例では完全週休二日制で土日休みを想定します。
なので、土日に当たる日付の列を薄橙色に塗りつぶしていきたいと思います。
使用する関数はWEEKDAY関数です。
G4:BZ100の範囲を選択します。
この状態で「ホーム」⇒「条件付き書式」⇒「新しいルール」を選択します。
ここで「数式を使用して、書式設定するセルを決定」を選択し、数式を以下のように入力してセルの色を薄橙色に設定します。
=WEEKDAY(その他!C$2,2)>=6
これで、土日の列が薄橙色に塗りつぶされるようになりました。
次は、特定の日付(祝日など)を塗りつぶす設定です。
この設定をする為には色を変更したい日付リストを作成する必要があるので、「その他シート」の空きにリストを作りましょう。
「その他シート」のC4セルに休日と入力し、C5:C100の範囲に休みの日を追加していく形にします。
ここで使用する関数はCOUNTIF関数です。
G4:BZ100の範囲を選択します。
この状態で「ホーム」⇒「条件付き書式」⇒「新しいルール」を選択します。
ここで「数式を使用して、書式設定するセルを決定」を選択し、数式を以下のように入力してセルの色を薄橙色に設定します。
=COUNTIF(その他!$C$5:$C$100,その他!C$2)=1
これで、リストに入力した日付の列も薄橙色に塗りつぶされるようになりました。
２－７．イレギュラーな出勤日の色を設定する
会社によっては一般的な祝日がある週は代わりに土曜日が出勤日になったりとイレギュラーな出勤日がありますよね？
その場合は出勤日が薄橙色だと不都合なので、別のルールを設定して白く塗りつぶしてしまいましょう。
設定方法は先程休日リストを参照させたやり方と同じで、こちらは出勤日リストを参照させるだけです。
「その他シート」の休日リストの右側に出勤日リストを作りましょう。
後は、ルールを以下のように設定すれば完了です。
=COUNTIF(その他!$D$5:$D$100,その他!C$2)=1
これで予定表としての形は整いました。
２－８．ルールの優先順位の設定
最後に注意しておく点が1つあります。
それは、ルールの適用される順番です。
ここまで説明してきたように、ルールは複数設定が可能です。
では、1つのセルに2つのルールを適用している場合、どちらのルールが優先されるようになっているのでしょうか？
答えは、自分で指定しない限りルールを設定した順番が古いほど優先順位が低くなっていきます。
ということで、優先順位を変えましょう。
「ホーム」⇒「条件付き書式」⇒「ルールの管理」を開きます。
すると、これまで設定してきたルールの一覧が表示されます。
ここで下に表示されているルールほどデフォルトで古いルールになっています。
ルールを選択した状態で図9の赤枠部の矢印をクリックすると順番が入れ替わるので、自分の好きな順番で並び替えましょう。
また、ルールを追加・削除・編集したい場合はこの画面からまとめて変更可能です。
少なくとも、休日のルール(２－６)の優先順位を出勤日のルール(２－７)より下にしておいてください。
そうしないと、出勤日として表示したいのにそのまま休日を表す薄橙色になってしまいます。
以上、「VBAを使わないけど地味にハイテクな予定表の作り方」についてでした。
