営業日だけを見たい! Google スプレッドシートで色々便利な日程表を作ってみた | SiTest (サイテスト) ブログ

メニューボタン閉じるボタン

営業日だけを見たい! Google スプレッドシートで色々便利な日程表を作ってみた

Excel や Google スプレッドシートで、日程表を作成するケースはないでしょうか?
そんな時に面倒なのが「営業日」の計算です。
土日だけではなく、祝日もあるので、地道にやろうとすると大変です。
ですが、 Google スプレッドシートでは、便利な関数が用意されています。
(もちろん Excel でも!)
それらを駆使すれば、苦労せずに素敵な日程表が作成できます。
この記事では、テンプレとして使えて、シンプルかつ便利な日程表を作ります!

スポンサーリンク

まず、最初にどんな日程表を作成するかを見ておきましょう!
完成版
この日程表には以下の様な機能があります。
  ・表示開始日付に連動して、月・日・曜日が自動で更新される
  ・営業日かどうかを祝日を含めて判定する
  ・営業日でない場合は、背景色をグレーにする

作ったシートはこちらにあります。

これで、わざわざ月・日・曜日を更新し直す手間がグッ!と減り、見栄えも良いです!
以下では、D列について解説しますが、それより右の列はD列をコピペすればOKです。

表示開始日付と月・日・曜日を連動させるには

このシートの1行目には、日付計算用の行が隠れています。
そこで、列ごとに表示する日付を計算しています。
隠れたセルを表示

列の日付を計算

=$A$4 + COLUMN(D1) - COLUMN($D1)

D1セルに書いている関数がこちら。
この関数では「 表示開始日付に、日付を足し算して、表示したい日付を算出 」しています。
$A$4が表示開始日付です。
COLUMN関数は、列番号を計算する関数です。
ここで、「$」マークに注目してください。
「絶対参照」と「相対参照」を上手く使って、列数によって表示開始日付との差分を計算するようにしています。

月を表示

=IF(TEXT(C1,"M") = TEXT(D1,"M"), "", TEXT(D1,"M"))

D3セルに書いている関数がこちら。
「 列の日付の月が、一つ左隣の列の月と異なっている場合は、月を表示 」しています。
全ての列に月を表示してもいいですが、見にくいので、月が変わるときだけ表示するようにしています。
TEXT関数は、指定した書式で文字列に変換して表示してくれる関数です。
ここでは、「M」と指定することで、日付の月だけを表示するようにしています。

日と曜日の表示では、全ての列に表示しているだけで、月の表示方法と基本は同じです。

=TEXT(D1,"DD")

D4セルに書いている関数がこちら。
これで、日だけを表示できます。

=TEXT(D1,"ddd")

D5セルに書いている関数がこちら。
これで、曜日を表示できます。

土日祝日など、営業日でない日を判定する

シートの2行目に設定しているのが、営業日かどうかの判定です。
判定にはNETWORKDAYS関数を使用しています。
NETWORKDAYS関数とは、指定した期間に営業日が何日あるかを計算する関数です。

=NETWORKDAYS(開始日, 終了日, [祝日])

書き方はこちら。

=NETWORKDAYS(D$1,D$1,'休日設定'!$A$2:$A$25)

実際D2セルに書いている関数はこちら。
開始日と終了日を同じ日にすることで、その日が営業日かどうかを判定しています。
営業日であれば1が、営業日でなければ0が返ってきます。
ここでは「休日設定」というシートを作成して、祝日や有給などに柔軟に対応できるようにしています。

休日設定

営業日でない日は、背景色をグレーをにする

営業日の判定ができたところで、セルの背景色をグレーにする設定をしていきましょう!
条件付き書式を使います。
  条件付き書式 > 新しい条件を追加  を選択します。

条件付き書式
こちらで以下を設定しています。
  ・範囲に適用 : 書式設定を反映したい範囲を選択
  ・セルの書式設定の条件 : 営業日でない列がTRUEになるように条件設定
  ・書式設定のスタイル : 背景色・文字色など、スタイルを設定
できたら「完了」を押します。

これで、シートが完成です。

最後に

いかがでしたでしょうか?
実は、私自身は、このシートをベースにカスタマイズして、もっと便利にして使っています。
例えば以下のような機能です。
  ・開始日と日数を入力すると、営業日だけで終了日を自動計算
  ・当日日付の文字色を変更する
  ・開始日・終了日を元に、ガントチャートにする
  ・進捗率を入力し、ガントチャート上に表示できるようにする

カスマイズ次第で、色んな場面に使えるので、ぜひご活用下さい。

また、以下のような Google スプレッドシート関連の記事も記載していますので、ぜひ御覧ください。
  ・Google スプレッドシートで使えるアドオンを作ってみよう
  ・Google 純正! Google Analytics のスプレッドシートアドオンを使おう
  ・Googleスプレッドシート初心者脱出!配列数式でスマートに計算しよう
  ・Googleスプレッドシート初心者脱出!SUMPRODUCT関数を使ってみよう
  ・アンケート?それならGoogleフォームでサクッと解決!