はじめに

今回タイトルにしたスケジュール作成のためのスプレッドシートを作るにあたり、SiTEST さんのブログを大変参考にさせていただきました。ブログの通りにガントチャートの日付や休日の設定を行ない、お手本とそっくりに仕上げることができました。誠にありがとうございます。ガントチャートについてご興味のある方は SiTEST さんのブログを読んでみてください。

トリニティは製品の企画・開発・営業などは日本国内で行ない、生産は中国の工場に委託していて中国にもスタッフがいます。企画開発から生産、発売までのスケジュールは日本のスタッフが管理していますが、中国と日本では営業日も祝日も異なるため、スケジュールを立てるときにそれぞれの営業日と休みをカレンダーを見ながら微調整しているそうで、もう少しシンプルかつ短時間でスケジュールを作れるようにならないかという課題が出てきました。

そこで、スケジュール作成の時間を短縮させることをゴールに据えて、日本と中国のスケジュールを簡単に作成できて、一元管理できるよう試行錯誤しています。試作段階ですが、今回はスプレッドシートでのスケジュール作成フォーマットの備忘録として書きたいと思います。

つくりたいもの

• タスク実施にかかる日数を入れておけば、日本チームのタスクは日本の営業日とお休み、中国チームのタスクは中国の営業日とお休みを計算に入れて、スケジュールが自動的に入力されるようにしたい
• 開始日が決まっている場合は終了日が自動で入力されるようにしたい
• 終了日が決まっている場合は開始日が自動で入力されるようにしたい（赤く囲った日付がそれぞれ自動入力されるようにしたい）

現状

• 日本の営業日はカレンダー通り土日祝日休み。加えて、冬期休業や社員研修などカレンダーとは異なる休みがある
• 中国の工場は土曜日も稼働しているため、日曜休みでスケジュールを組んでいる。工場の休業日もそれぞれ異なるので、スケジュールの調整・管理がさらに煩雑になっているように見える
• 始日からスケジュールを立てることもあれば、終了日からスケジュールを立てることがある

スケジュールの設定

ベースで使う関数は下記の2つにしました。

• 日本の営業日：
  WORKDAY（https://support.google.com/docs/answer/3093059?hl=ja）
• 中国の営業日：WORKDAY.INTL（https://support.google.com/docs/answer/3294972?hl=ja&ref_topic=3105385）

私たちの場合、日本と中国の営業日の最大の違いは、土曜日がお休みかどうかというところです。ネットで調べると、日本の営業日やお休みを元にスケジュールを求めるときは WORKDAY 関数が使えるということが分かりました。この関数を使うと、特別な設定をしなくても、土日を休みにしてくれます。

WORKDAY.INTL 関数は、毎週決まった曜日を休みとして設定することができますので、土日休みではない中国のスケジュールを求めることができます。開始日と作業期間が決まっているときに終了日を求めること、終了日と作業期間が決まっているときに開始日が求めることをスケジュールを求めると表現しています。

【準備 – 休日リストの作成】

毎週のお休みのほかに、祝日や臨時休業は設定が必要です。そのためにまずは、休日リストを作っておきます。日本の祝日は、内閣府が公開している祝日リストをコピペしました。

【準備 – タスク・期間・割り当て先を入力しておく】

見出し通り、タスク・期間・割り当て先を入力しておきます。

【日本の営業日とお休みを加味して終了日を自動計算する】

構文

=WORKDAY(開始日, 日数, [休日])

まず、開始日を入力します。次に、上記構文を終了日の列に入力します。

添付画像の黄色のセルに、「=WORKDAY（D7,C7,’休日設定’!$C:$C）」を入れています。構文の［休日］にあたるところには、一番始めに作成した「休日設定」シートの該当箇所を指定します。

【中国の営業日とお休みを加味して終了日を自動入力させる】

構文

=WORKDAY.INTL(開始日, 日数, [週末], [祝日])

まず、開始日を入力します。次に、上記構文を終了日の列に入力します。

添付画像の黄色のセルに、「=WORKDAY.INTL(D8,C8,11,’休日設定’!$F:$F)」を入れました。［週末］と書かれているところには、ルールに則って数字（もしくは文字列）を入れます。ルールは本文最後にメモしておきたいと思います。

今回は「中国は日曜日を週末に設定」したいので「11」を入れました（ちなみに、［週末］に［1］を入れると、土日休みという意味になります。日本の営業日はすでに WORKDAY 関数で作ってしまったので。。。ひとまずこのまま進めることにしました）。構文の［祝日］にあたるところには、一番始めに作成した「休日設定」シートの該当箇所を指定します。

【終了日から開始日を求めるには？】

WORKDAY 関数も WORKDAY.INTL 関数も、［期間］にマイナス（-）をつけてあげればOKです。［開始日］を入れていた部分には、［終了日］を入れてあげます。

添付画像の黄色セルに「=WORKDAY(E21,-C21,’休日設定’!$C:$C)」と入れました。

添付画像の黄色セルに「=WORKDAY.INTL(E20,-C20,’休日設定’!$C:$C)」と入れました。

【割り当て先によって使用される関数が自動的に選択されるようにする】

割り当て先が「PM」の場合は中国の営業日と祝日が計算に反映され、割り当て先が PM 以外の場合は日本の営業日と祝日が反映されるようにします。IF 関数を使って WORKDAY 関数と WORKDAY.INTL 関数をドッキングさせてあげればOKです。

終了日から開始日を求める場合は［期間］をマイナスにするだけで、構文の構造は一緒です。作った関数をコピペしていけば、割り当て先によって開始日もしくは終了日が入力されていきます。添付の画像の黄色いセルに、それぞれ関数が入っています。

=IF(F9=”PM”,WORKDAY.INTL(D9,C9,11,’休日設定’!$F:$F),WORKDAY(D9,C9,’休日設定’!$C:$C))

=IF(F22=”PM”,WORKDAY.INTL(E22,-C22,11,’休日設定’!$F:$F),WORKDAY(E22,-C22,’休日設定’!$C:$C))

以上で、スケジュール作成フォーマットは完成です。

開始日から終了日を計算する場合は終了日の列に、

=IF(F●=”PM”,WORKDAY.INTL(D●,C●,11,’休日設定’!$G:$G),WORKDAY(D●,C●,’休日設定’!$C:$C))

終了日から開始日を計算する場合は開始日の列に

=IF(F●=”PM”,WORKDAY.INTL(E●,-C●,11,’休日設定’!$G:$G),WORKDAY(E●,-C●,’休日設定’!$C:$C))

をそれぞれ入れれば、日付を自動計算してくれるようになりました。

▼まとめ

• WORKDAY 関数、WORKDAY.INTL 関数を使うと、開始日と作業期間が決まっているときに終了日を求める、終了日と作業期間が決まっているときに開始日が求めることができます
• 土日休みの場合は、WORKDAY 関数が便利です
• 土日休みでない場合は、WOKRDAY.INTL 関数を使って求めることができます
• 設定した毎週のお休み以外の祝日や社員研修、臨時休業などは休日設定リストを作っておきます
• 開始日から終了日を計算したいときは、構文の［期間］をプラスの数値にします
• 終了日から開始日を計算したいときは、構文の［期間］をマイナスの数値にします
• IF 関数を使って、割り当て先が中国の場合は WORKDAY.INTL 関数の結果を反映し、日本の場合は WORKDAY 関数を反映させることができます

試作段階なので、まだまだ整備が必要です。同じようなことで困っている方がいらっしゃったら参考になれば幸いです。

（メモ：WORKDAY.INTL 関数の［週末］設定ルール）

WORKDAY.INTL 関数の［週末］に数字を入れると、下記のとおりお休みを設定することができます。

1…土曜日と日曜日
2…日曜日と月曜日
3…月曜日と火曜日
4…火曜日と水曜日
5…水曜日と木曜日
6…木曜日と金曜日
7…金曜日と土曜日
11…日曜日
12…月曜日
13…火曜日
14…水曜日
15…木曜日
16…金曜日
17…土曜日

★

「あさすけ先生の、超役に立つGoogleスプレッドシート解説」関連記事

 
▶ 【スプレッドシート時短術】19,767個の空白セルを3秒で「0」にする小技
▶ 計算式のコピペ漏れを防ぐ！　気難しそうに見えて案外シンプルないいヤツ 〜ARRAYFORMULA関数〜
▶ VLOOKUP関数のエラー原因を探すのに2時間もかかっていた私が、いろいろな関数を使いこなせるようになったコツを考えてみた。
▶ Googleスプレッドシートに週ごとにタスクをまとめて、その週の未完了タスク・完了タスク・タスクの達成率がひと目でわかるようにする
▶ IMPORTRANGE関数で休日設定シートを転記すればすべてが丸く収まった
▶ 値ペーストと書式ペーストを使いこなすと作業が格段に速くなる – エクセルやGoogleスプレッドシート
▶ Googleスプレッドシートでデータの結合・分割の幅を広げたい。改行するなら CHAR(10) を使おう！
▶ 「$」マークを使いこなして表をパパッと作る 〜相対参照と絶対参照の復習〜
▶ わざわざ使いたい、転置して貼り付けができるGoogleスプレッドシートの「TRANSPOSE関数」
▶ 地味にはまっている、GoogleスプレッドシートでUNIQUE関数とSORT関数で入力規則（プルダウン）用のデータリストをつくる方法
▶ Googleスプレッドシートのフィルタ表示機能が便利
▶ Googleスプレッドシートでチェックリストを5分で作ってみる
▶ 営業日の異なる日本と中国のスケジュール作成ツールをGoogleスプレッドシートで作ってみた（本記事）