各タスクの開始日、作業日数をいれると、設定した休日を考慮した工程表を作ります。
サンプル
とりあえず、完成形は下記をダウンロードしてください。
1.カレンダーの作り方
サンプルファイルのD1に日付を入力すると、自動で月、日、曜日が表示されます。
これは、関数は使ってません。日付の表示形式で月、日、曜日を表示しています。
E3にはD1を参照する数式
=D1
が入っていて、E2,E4はE3を参照しています。
後ろの列は前の列に1を加算する数式が入っています。
エクセルの日付は日数のシリアル値なので、1を足すと1日後の日付が得られます。
日付の表示形式
セルの書式設定手順
- セルを右クリック
- セルの書式設定をクリック
- ダイアログが表示されるので、表示形式タブをクリック
- ユーザー定義をクリック
- 種類の直下のテキストボックスに以下の文字列を入力する
| 表示する値 | 入力する文字列 |
| 月 | m |
| 日 | d |
| 曜日 | aaa |
曜日のaaaが肝ですね。関数を駆使しなくても表示できるので便利です。ちなみに、英語表記にしたいならdddです。
2.休日の設定
別のシート（休日シート）に休日を設定します。
曜日と個別の休日（祭日等）を入力します。
曜日の下に休日には1を入力します。
I2セルにはB2:H2までを連結した文字列を作成する数式
=CONCATENATE(B2,C2,D2,E2,F2,G2,H2)
が入っています。
あとでWORKDAY.INTL関数でこの値を使用します。
3.終了日の計算
開始日と日数から休日を考慮した終了日を計算します。
WORKDAY.INTL関数を使用します。
書式
WORKDAY.INTL(開始日, 日数, [週末], [休日])
週末の指定の仕方がみそです。指定しないと土日を休日として計算します。
休日シートに設定した2進数形式の文字列で指定します。
ちょっと複雑ですが、D6には以下の数式を入力します。
=IF(AND(B6<>"",C6<>""),WORKDAY.INTL(B6,C6-1,休日!$I$2,休日!$A$5:$A$504),"")
何も入力していなくてもエラーにならないようにIF関数と組み合わせています。
わざと、日数を1日減らしています。これは、関数が作業終了日より1日多い値を返すためです。
4.工程表の作成
いよいよメインの工程表の作成です。条件付き書式を使って、作業日を塗りつぶします。
稼働日はオレンジ、休日は赤で表示します。
数式を指定して、TRUEのときに指定した色で塗りつぶす設定をします。
条件付き書式は3つルールを設定します。
稼働日、休日（曜日）、休日（指定日）です。
条件付き書式の設定手順
- E6をクリック
- ホームタブの条件付き書式をクリック
- 新しいルールをクリック
- 数式を使用して、書式設定するセルを決定をクリック
- 次の数式を満たす場合に値を書式設定の下のテキストボックスに数式を入力する
- 右下の書式ボタンをクリック
- 塗りつぶしタブをクリックし、セルの色を設定する
- 3つのルール設定が終わったら、E6を他の列にコピーする。（条件付き書式もコピーされる）
稼働日に設定する数式
=IF($D6,IF(AND(E$3>=$B6,E$3<=$D6),TRUE,FALSE),IF(E$3=$B6,TRUE,FALSE))
開始日と終了日を参照して、列の日付がその範囲に入っていればTRUE、入っていなければFALSEを返す数式です。
終了日が入っていない場合は開始日と一致すればTRUEにしています。
サンプルでは書式はオレンジの塗りつぶしを指定しています。
休日に設定する数式
曜日の数式
=IF(INDEX(休日!$B$2:$H$2,,WEEKDAY(E$3,2))=1,TRUE,FALSE)
休日シートの曜日設定を参照して、1だったらTRUEを返す数式を指定しています。
WEEKDAYは曜日を返す関数です。INDEX関数でその曜日に指定された値を参照しています。
指定日の数式
=IFERROR(MATCH(E$3,休日!$A$5:$A$504,0),FALSE)
MATCH関数を使用して、休日リストに一致する日付があるか検索しています。
見つからない場合はエラーを返すのでIFERROR関数で、エラーの場合はFALSEにしています。
サンプルでは両方とも書式は赤の塗りつぶしを指定しています。
マクロを使わなくても関数と条件付き書式でなんとかできました。
5.googleスプレッドシート
googleスプレッドシートでも使えます。
曜日の表示形式だけ互換性がないので、設定しなおします。
- 表示形式の詳細設定をクリック
- その他の日付や時刻の形式をクリック
- 年月日曜日が表示されている形式をクリック
- 年、月、日を削除
One thought on “エクセルで工程表”