PAGE TOP

公開日:2020.10.08

更新日:

【前篇】繰り返す作業は自動化を!スケジュールテンプレートを自動化したら便利だった話

こんにちは!OTです。

ディレクターを始め、プロジェクトのガントチャート・スケジュールは皆さん何で作っていますか?便利なツールは色々ありますが、何だかんだEXCELで作っている人は多いのではないでしょうか。

今回本当は執筆当番ではないのですが、とっても便利なスケジュールテンプレートを作ったので自慢したく、急遽筆を執っております。(ハードルを上げていくスタイル)

毎回同じ作業を繰り返すのって非効率

弊社はGoogleスプレッドシートで作っているのですが、私は新しいプロジェクトが始まるたびに、
・日付を入れる
・曜日を入れる
・土日、祝日のセルの色を変える
・各工程の開始日、終了日に合わせてセルの色を塗る
を毎回やった結果、3案件目くらいで「めんどくさい」って気持ちが生まれたため、上記の作業の自動化を試みました。

今回は、上記の4工程を自動化できる数式と書式設定をご紹介します。
(同じ方法でEXCELでもできるはず…です!)

基本のフォーマット

私がガントチャートをつくる時の基本的なフォーマットはこちら


A列:番号(No)
B列:作業内容
C列:担当者
D列:開始日
E列:終了日
F列以降:カレンダー(3行目に月、4行目に日にち、5行目に曜日)

なので、数式は上記を基準に説明していきます。
人によっては項目に差があると思いますので、適宜使いやすいようにカスタムを。

日付を入れる

それでは日付を入れていきましょう。
私は4行目は日にちだけを表示しています。(10/1のようにすると文字が多くて見づらいので、見やすくするため)
まず、プロジェクト開始日をF3セルに入力します。日にちのみ表示する場合は、
ツールバー > 表示形式 > 数字 > 表示形式の詳細設定 > その他の日付や時刻の形式
を編集します。

年と月は不要なので、それぞれクリックして「削除」を押すと日付が残ります。
※年・月の文字が別途残っちゃう場合は、その文字も消してください

日付も同様にクリックすると、書式が出てくるので「先行ゼロなしの日」を選択。

こうすることで、スッキリ日にちだけが表示できました。
※「月」はそこまで手間ではないので、各1日の上に、手動で10月、11月……と入れています

曜日を自動で入れる

日付の下には曜日を入れます。こちらは上記の日付に合わせて自動反映されるようにしたいので、簡単な数式を使います。

「=F4」と入力するだけ!


こうして真上の日付をコピーしてそのままセルに反映できます。そうしたら先程の「その他の日付や時刻の形式」を編集して、年・月・日をすべて消したら「曜日の省略形」を選びましょう。
そうすることで、上の日にちを参照してスプレッドシートが曜日を自動入力してくれます。あとは、右にドラッグして一気にコピーするだけ。それぞれのセルが真上のセルを参照するので、自動で曜日が変わります。

土日のセルの色をグレーにする


弊社はカレンダー通りの営業日なので、土日祝はおやすみです。そのため、分かりやすいようにセルを「条件付き書式」を使ってグレーで塗り分けます。

土日をスプレッドシートが自動で判断するためには「WEEKDAY関数」を使います。
スプレッドシート(EXCEL)は、曜日を数字で判断して自動で取り出すんですね。その数字は以下。

日曜=1、月曜=2、火曜=3、水曜=4、木曜=5、金曜=6、土曜=7

つまり2020/10/8と書かれたセルをWEEKDAY関数で参照すると、木曜日なので「5」となります。

土日のセルをグレーにしたい時は、スプレッドシートに「WEEKDAY関数で1か7の数字が返ってきたらグレーで塗ってね」と条件付き書式を入れればよいのです。
スケジュールの起点であるF4を選択して、ドラッグでスケジュールのエリアを全選択したらカスタム数式で「=WEEKDAY(F$4)=1」と記入します。
※カスタム数式の意味は「F4のセルが日曜日」です
※日付のいる行4行目は参照を固定するので$マークをつけます。Fは常に参照列がずれていくのでそのまま。

書式を設定すると、日曜日の列が自動でグレーアウトされましたね!
同じ要領で、土曜日も設定しましょう。「条件を追加」をクリックして、カスタム数式を「=WEEKDAY(F$4)=7」に。これで土曜日のセルもグレーアウトです。

祝日のセルの色をグレーにする

土日も自動化したら祝日も自動化したい(ってマネージャーに言われた)ので、祝日も自動でグレーにするようにします。

まずスプレッドシートに祝日を認識してもらう必要があるので、参照できる祝日シートをつくりましょう。 シートを追加>新しいシート をして名前をつけます。私は「祝日リスト」と名付けました。そしたら、その祝日リストに祝日を入力していきます。
祝日は「EXCEL 祝日 一覧」で調べるとまとめられているので、コピペするだけ!私は3年分登録しました。

せっかくなので会社の休業日もグレーにしようと思い立ち、合わせてリストにしました。
青字は弊社の年末年始休暇(記事公開時の予定)です。期間中の連絡はお休み明けになりますので、ご承知くださいませ。

さて、祝日リストができたら祝日もグレーになるよう書式設定をしましょう。先程の土日の書式変更にさらに「条件を追加」して、カスタム数式を変更します。
数式は「=COUNTIF(INDIRECT(“祝日リスト!A1:A”),F$4)=1」です。

COUNTIF…特定の条件に一致しているセルを数える
INDIRECT…指定される文字列への参照を返す
“祝日リスト!A1:A”…別シートを参照する時は「シート名!セル番号」と記載します(シート名が日本語の場合は””でくくる)

…つまり数式は「祝日リストシートのA列に、F4行と一致する日付が1個あった場合は(グレーに)」となっているわけです。

こうして、祝日リストにある日にちも自動でグレーになりました。

そして後篇へ

日付を1つ入力するだけで、自動で曜日が入力され、さらに土日祝の列も塗りつぶされる。これだけでもスケジュール制作の作業がかなり楽になったのではないでしょうか!

次はいよいよ、「開始日と終了日(D,E列)を入力すると自動で該当期間のセルが塗りつぶされる」説明をしたいのですが、気がついたら文章がかなり長くなっていました…。こちらの説明は後編に分けようと思います。

おまけ

後編の説明まで待てないよ!一刻も早くスケジュール制作を効率化したいよ!
という方のために、今回のスケジュールテンプレートを無料公開することにしました。
クリックすると、Googleスプレッドシートが開きます(閲覧権限のみ)

とっても便利!色々自動なスケジュールテンプレート

ご利用になりたい方は、ツールバー>ファイル > コピーを作成 より、ご自身のアカウントでコピーしたファイルを編集してくださいね。

それでは後編、お楽しみに!(テンプレ公開したけど読んでほしい!)

スケジュールのフォーマットを作ったはよいけど、作業を洗い出すのが苦手…という方は、こちらの記事もご覧ください。

プロジェクト成功の鍵は段取りにあり!WBSでタスクを細分化しよう

もっとEXCEL・スプレッドシートの便利数式を知りたい!という方はこちらの記事もご覧ください。

【営業・ディレクター必見】Excel効率化!便利ショートカットと数式をご紹介

monomodeリクルートサイト
AD JOURNAL