こんな人に読んで欲しい
・スケジュールを管理するツールはコストがかかるので入れられない…
・Excelやスプレッドシートなら使えるけど作り方が分からない…
・スプレッドシートのテンプレートは探せばあるけど、自分で作ってみたい！ …etc
そんな方に是非読んでいただきたいです。
スプレッドシートでWBSを作るには…
早速、作り方を紹介します。
今回はExcelではなくスプレッドシートで作成しておりますので、ExcelでWBSを作成したい方はExcelに置き換えていただければと思います。
先に完成イメージをお伝えしておくと、こんな感じで作成していこうと思います。
画面左に各タスクと担当者、開始日、期限、稼働日、タスク完了率をイメージしております。
画面右はカレンダーになっていて、画面左の開始日、期限に応じて自動で色付けをしてくれるように作りたいと思います。
また、カレンダーは土日、祝日は自動で色付けさせるようにしたいと思います。
カレンダーの作り方
まずはカレンダーから作成していきます。
カレンダーはプロジェクトの開始日をどこかのセルに入力すればそれ以降の月、日、曜日の情報が全て表示できるように関数を使って作成していきたいと思います。
今回の場合は、J4のセルにプロジェクトの開始日を入力します。(自分の場合はセルにメモを追加して分かりやすくしてます。)
日付
日付の表記については [表示形式] > [数字] > [カスタム日時] で以下のように設定しております。
J4に開始日である2024/07/01の入力出来たら、日付を連番で表示させるよう作っていきます。
日付を連番で表示させるのは、下のJ5に「=J4」と入力し、表示形式をカスタム日付で日のみにしていただければ「1」と表示されます。
右のK5のセルには「=J5+1」と入力すれば「2」と表示されますので、あとは右にコピーしていけば日付の連番での表示が可能です。
曜日
曜日はJ6セルに以下のように入力すれば曜日の表示が可能です。
=TEXT(J5,"ddd")
日付同様に右にコピーしていきましょう。
ここまではExcelやスプレッドシートにあまり詳しくない方でもご理解いただけたかと思います。
ちなみに、シートのAO4に「8月」と表示されておりますが、こちらも自動で表示されるようにしております。
4行目のK列以降には以下のような関数が入ってます。
# K4の場合
=IF(EOMONTH(J5,0)<J5+1,J5+1,"")
これはEOMONTH関数という末日を計算してくれる関数を用いて翌月を表示させております。
・EOMONTH(開始日,月)
例）EOMONTH("2024/07/05",0)
= "2024/07/31"
月は0であれば当月、1であれば翌月といった形です。
今回の場合ですと、J5にある"2024/07/01"の末日である"2024/07/31"が"2024/07/01"に +1 された日付である"2024/07/02"より小さければ"2024/07/02"を表示し、そうでなければ空になるようにしています。
"2024/07/31" < "2024/07/02"
今回の場合はFALSEなので空となります
「8月」と表示されているAO4セルにつきましては以下のような計算式のため、"2024/08/01"が表示されます。(表示形式で「8月」と表示させてます。)
=IF(EOMONTH(AN5,0)<AN5+1,AN5+1,"")
"2024/07/31" < "2024/08/01"
TRUEのため、日付を表示します
休日の条件付き書式による色付けについて
WBSを作成する上で、稼働日ベースで作成したいこところです。
なので土日・祝日は色付けをし、分かりやすくしましょう。
[表示形式] > [条件付き書式] から設定します。
まず、土日祝日であれば色付けしたいセルの範囲を選択します。
例として「J7:BR33」を選択します。
セルの書式設定の条件は「カスタム数式」を選択いただき、値または数式に「=J$6="土"」と入力します。
書式設定のスタイルでは塗りつぶしたい色を選択します。例として明るいコーンフラワーブルー2を選択しました。
これで、曜日のある6行目の値を参照し、「土」があれば色を塗りつぶしてくれます。
日曜日も同様の設定を行います。
祝日について
祝日についてはいちいち調べてスプレッドシートを編集するのは嫌だったので、IMPORTDATA関数を用いて内閣府のサイトから祝日のマスタを取得したいと思います。
別のシートとして「祝日マスタ」という名前のシートを作成します。
A1セルに以下の関数を入力します。
=IMPORTDATA("https://www8.cao.go.jp/chosei/shukujitsu/syukujitsu.csv")
※アクセス許可が求められますので、許可してください
そうするとA列に日付、B列に祝日名のデータを取得してくれます。
ただ、csvデータの文字コードがSJISのため、B列の祝日名は文字化けします。
WBSを作成する上では、日付だけ分かっていれば良いので個人的には気にしません。
Excelの場合はIMPORTDATA関数が対応しておりませんので、WEBSERVICE関数を使うと良いかと思います。
参考サイト
https://studyexcel.jp/webservice/
では、祝日マスタが取得できたところで元のシートに戻り、条件付き書式の設定をしていきたいと思います。
[表示形式] > [条件付き書式] > [条件を追加] から設定します。
適用する範囲は先程土日で設定した範囲と同一のものを選択します。
今回は先程同様「J7:BR33」を選択します。
セルの書式設定の条件は「カスタム数式」を選択いただき、値または数式に以下の数式を入力します。
=COUNTIF(INDIRECT("'祝日マスタ'!A:A"),J$5)=1
書式設定のスタイルでは日曜で設定した色と同じ色を選択します。
この関数によって先程作成した祝日マスタシートを参照し、該当の日付が祝日マスタに存在するかカウントします。
もし、存在していれば書式設定のスタイルで選択した色に塗りつぶされます。
設定は以下の通りになりました。
これで休日の条件付き書式による色付けについては完了です。
稼働日の色付け
続いて、画面左のタスクの開始日と期限に応じて右のカレンダーに色付けをしていく設定をしていきたいと思います。
J8以降のセルに以下の数式が入力されております。
=IF(OR(WEEKDAY(J$5)=1,WEEKDAY(J$5)=7,COUNTIF('祝日マスタ'!$A:$A,J$5)=1),"",IF(AND(J$5>=$F8,J$5<=$G8),1,""))
IF関数とOR関数を組み合わせて、WEEKDAY関数で土曜日と日曜日、COUNTIF関数で祝日マスタに該当の日付があるかを判定しております。
もし該当の日付が土、日、祝日だった場合は空となり、それ以外であれば開始日、期限日の範囲内に該当すれば1が入力されるようにしております。
参考リンク：WEEKDAY関数について
https://xtrend.nikkei.com/atcl/contents/18/00069/00005/
これを全てのカレンダーのセルにコピーすれば以下のように各セルに「1」が入力された状態になるかと思います。
開始日、期限に合わせて数字が入ったところで条件付き書式を活用します。
[表示形式] > [条件付き書式] > [条件を追加] から新しい条件を追加します。
範囲は先程の休日の色付けを行った範囲と同じく「J7:BR30」を選択します。
セルの書式設定の条件は「次と等しい」を選択し、値のところは「1」と入力します。
あとは稼働日を色付けしたい色を選択するのですが、ここではテキストの色と背景の塗りつぶしを同一の色を選択しましょう。
そうすることによって、先ほどまで「1」と表示されていたセルがテキストと背景と同一の色となりますので文字が見えなくなり、稼働日が色付けされることなります。
テキストと背景色を同一の色で塗りつぶすことで実際には数字が入っておりますので、タスクごとの稼働日などもSUM関数で計算することも出来ます。
タスク完了率について
タスク完了率は会社などによってどのくらいの粒度で進捗率を管理したいか異なるかと思いますので、直打ちでもプルダウンでもどちらでも良いかと思います。
完了具合を一目で感覚的に把握されたい場合は条件付き書式で可能です。
[表示形式] > [条件付き書式] > [条件を追加] からタブをカラースケールを選択します。
範囲を完了率が表示されている列を選択し、書式ルールでお好きな色を選択いただければ進捗率も一目で確認することが出来ます。
最後に
最後まで読んでいただきありがとうございました。
少しでも参考になれば幸いです。