Excelで日数単位のプロジェクト計画を作るときに祝日を考慮した計画を作成しなければならない。
今年のように10連休やお盆休みが長いとプロジェクト計画時点で考慮していないと破綻する。
この記事では以下のことについて解説しています。
- Excelでスケジュール表を作るときに土日祝日の書式を自動的に設定
- 祝日を考慮した稼働日数、稼働時間を算出する
システムエンジニアとしてExcel歴22年、情報処理プロジェクトマネージャ試験合格、PMP合格、PMS合格の私が
スケジュール表のテンプレートを作ります。
目次【本記事の内容】
- 1.Excelでスケジュール表を作るときに土日祝日の書式を自動的に設定
- 1-1.スケージュール期間中の祝日のマスタを作る
- 1-2.土日祝日を条件付き書式を設定する
- 1-3.条件付き書式を設定する時のコツ
- 2.祝日を考慮した稼働日数、稼働時間を算出する
- 2-1.SUMIF関数で人別に集計する
- 2-2.SUMIF関数ので山積みをチェックする
- 2-3.Excelとは関係ないけどバッファ期間を設ける
１．Excelでスケジュール表を作るときに土日祝日の書式を自動的に設定
１－１ スケージュール期間中の祝日のマスタを作る
祝日を登録するためのマスタを別シートに作成する。
たまにシートにマスタを設定してる人がいるが、
そういう人は名前を付けたら別シートのレンジを参照できるという事を知らないのだろう。
設定する休みは土日以外で会社が休みになる日を登録しておく。
そして、登録した範囲に名前を付ける。
名前の付けた方は祝日の範囲を選択しておき、名前ボックスに「祝日」と入力すれば完了だ。
最初に完成形のイメージを共有すると以下のような表が出来ることを想定している。
土日の列はグレーで塗られて、会社がお盆休みの日もグレーアウトされて要員の割当を間違えてすることはないだろう。
二重下線の下は集計欄になっていて、人別に集計している。黄色く塗られているところが計画を見直す必要があるところだ。
さらに詳しく
Excelでよくユーザーの人から質問されるのは曜日を表示するにはどうすれば良いでしょうか？という質問だ。これは簡単でTEXT関数を使用する。
=TEXT( 日付が入力されているアドレス（例：A1）,"aaaa"）で月曜日とか水曜日の漢字3文字
=TEXT( 日付が入力されているアドレス（例：A1）,"aaa"）で月とか水の漢字1文字
１－２ 土日祝日を条件付き書式を設定する
ホームタブの条件付き書式から新規ルールを作成する。
まずは土曜日の条件付き書式を設定しよう。
ココがポイント
D$1としたのは1行目にある曜日を他の行や列にコピーしても参照がずれないようにするためだ。
同じように日曜日の条件付き書式を設定する。
祝日の条件付き書式は少し異なる。最初に名前を付けた祝日レンジの中にその列の日付があればカウントされる。
カウント結果が0（ゼロ）なら祝日ではなく、1の場合は祝日になるという条件式だ。
条件付き書式の管理で適用するレンジを設定する。メモ帳で編集してCtrl＋Vで貼り付ければ簡単だ。
１－３ 条件付き書式を設定する時のコツ
条件付き書式を設定する時の問題ってキーボードで移動しようとするとセルが動いてしまい途中まで入力した計算式がずれてしまう。
修正したいところにマウスでカーソルを持っていって編集するか、テキストエディタで編集するのが良いだろう。
この問題は20年前から変わっていないのだがなんとかならないだろうか。
２．祝日を考慮した稼働日数、稼働時間を算出する
２－１ SUMIF関数で人別に集計する
休みのところにはグレーで塗られるようになった。これで間違えて休日に人を割り当てるということはないだろう。
SUMIF関数は以下のような式だ。特に難しい所はないかと思う。
２－２ SUMIF関数ので山積みをチェックする
一日7時間を超えて山積みしているような箇所は別の人を割り当てるかずらす。
集計欄にセルの値が>7 だったら黄色くするような条件付き書式を設定しておくとわかりやすいだろう。
２－３ Excelとは関係ないけどバッファ期間を設ける
山積みを設けるときにはマイルストーンに対してバッファ期間を設けるようにしている。
バッファ期間が設けられない場合は人を追加するか、スラッシングするか、スコープを狭めるか、段階リリースできないか。
そんな観点で要員計画を作成するようにしている。