こんにちは、零二（@ReijiMinami）です。
前回は年月を入れるだけで土日祝の色付けが完全に自動化される方法を説明しました。
今回は、いよいよシフトを組んでいく作業に、出来るだけ余計な頭を使わないよう、自動で計算できる部分を増やしていきましょう。
私の休みの数もちゃんと数えてくれるの？
全員分きちんと休みの数を
ばっちり自動で数えられるよ！
スポンサードサーチ
勤務日程を簡単に入力できるようにする
まずはシフト表に勤務予定を入力する際、選択項目から簡単に選んで入力できる仕組みを作りましょう。
皆さんの職場で決まった表記の仕方があると思いますので、今回ご紹介する例をヒントに、職場にあった表記に直して活用してみて下さい。
勤務表に入力する種類を用意する
はじめに、勤務表にどんな入力をするのかを、それぞれの職場に合わせて作成しておきます。
新しいシートを、今回は「勤務表マーク一覧」という名前で作成しました。
これは私が実際に見たことのあるシフト表を参考に、例として作成したものです。
上図のように、空白が日勤という意味で示している職場もたくさんありそうなので、今回あえてこの例にしてみました。
コラム
「なぜ勤務表マーク一覧を作るのか、そのメリットは？」
- マークが変更になっても、ここを変えるだけで全てに反映される
- マークが増減しても、ここを変えるだけで他の設定を変えなくて済む
- マークの入力ミスがなくなり、結果勤務日数や休みを数える際に計算漏れがなくなる
特に3つ目は本記事を説明する上で重要となります。
手入力だと、マークによって全角・半角の間違いなど、わずかなヒューマンエラーが起きやすくなります。
用意した勤務マークから選べるように設定する
続いて、シフト表に戻り、実際に今作った勤務表マーク一覧から選べるように設定していきます。
まずは勤務予定を入力するセルを全て選択します。
「データ」タブにある「データの入力規則」を選択します。
条件の設定で、入力値の種類から「リスト」を選択します。
「元の値」と書いてある欄の右側に、上矢印のようなボタンがあるので、それを選択します。
用意した勤務表マーク一覧から、シフト表に使うマークの部分を全てドラッグして囲みます。
すると、データの入力規則にそのセル範囲が自動で入力されます。
入力されたら、Enterを押します。
最後に、OKを選択します。
これでシフト表はマウスでクリックして選択するだけで入力可能になりました。
シフト作りに必要な日数計算を自動で行えるように設定する
ここまで準備ができたら、いよいよ本題です。
今回は1つの数式だけ使って計算できる内容のものを紹介していきます。
ただし、今回紹介するやり方は「色々あるやり方のひとつである」ということだけ予め断っておきます。
なお、計算するセルの場所は、皆さんの任意の場所で構いません。
今回は上図のようなシフト表の横ですぐ確認できる場所を考えて作ってみました。
土日の数を自動計算する
土曜日の数を計算したい場合は、曜日欄に「土」と書いてある数を数えるやり方で計算します。
まず、皆さんが作ったシフト表の曜日欄を確認します。
1日の曜日がC1、31日の曜日がAG1となっています。
なので、土曜日を数える計算式は以下の通りになります。
土曜日だけを数える
=COUNTIF(1日の曜日のセル:31日の曜日のセル,”土”)
つまり、
=COUNTIF($C$1:$AG$1,”土”)
※必ず＄マークが2個ついた状態（絶対参照）にして下さい。
同様に、日曜日の数、つまり曜日欄に「日」と書いてある数を数える計算式は、以下のようになります。
日曜日だけを数える
=COUNTIF(1日の曜日のセル:31日の曜日のセル,”日”)
つまり、
=COUNTIF($C$1:$AG$1,”日”)
※必ず＄マークが2個ついた状態（絶対参照）にして下さい。
以上の計算から、土日の両方を数えたい場合は、これらの計算式を足し算すれば良いわけですね！
土日の数を数える
=COUNTIF($C$1:$AG$1,”土”)+COUNTIF($C$1:$AG$1,”日”)
営業日（平日）を自動計算する
続いて、営業日（平日、稼働日など色々な呼び方はあります）の計算です。
土日祝が関係ない職場でも、この計算によって仕事へ行く日数が計算されることが殆どではないかと思います。
この営業日を自動で計算してみましょう。
ただし、この時点で前回の記事で紹介した祝日リスト（ 祝日がいつなのかを別シートで用意する ）を作っていない方は、そちらの準備を先に終えてからやってみて下さい。
祝日リストに「祝日」と名付けてある前提で、続きを説明していきます。
はじめに、その月の1日が入力されているセルの場所を確認します。
今回の場合は、C3です。
この確認だけで、営業日が計算出来てしまいます。
営業日を数える
=NETWORKDAYS(1日の日付のセル,EOMONTH( 1日の日付のセル ,0),祝日)
つまり、
=NETWORKDAYS($C$3,EOMONTH($C$3,0),祝日)
※必ず＄マークが2個ついた状態（絶対参照）にして下さい。
この計算式は、開始日（1日）と終了日（EOMONTHはその月の最終日を計算してくれる）、そして祭日（除外して欲しい日にち）を指定するだけで、営業日が計算できちゃう便利な関数です。
今回の例では、2019年10月を元に計算しています。
営業日は21日と自動で計算されました。
他の年月を指定して、営業日の計算結果が変わるのを試してみて下さい。
土日祝の数を自動計算する
先ほど土日だけの数を数えましたが、祝日の数も入れないと意味がないケースが殆どかと思います。
そこで、今回営業日の計算結果を利用して、土日祝の数を数えてみましょう。
復習ですが、1日の日付はC3に入力されていましたね。
そして、営業日の計算は今回AH7に入力しています。
この場合、以下のような計算式で土日祝の数を算出できます。
土日祝を数える
=DAY(EOMONTH(1日の日付のセル,0))-営業日を計算したセル
つまり、
=DAY(EOMONTH($C$3,0))-$AH$7
やっていることは、
=「その月の最終日（今回は31）」ー「営業日（今回は21）」
なので、「31-21」で答えは10ですね！
このように、他の計算との合わせ技で土日祝を簡単に計算することが出来ました。
勤務日数を自動計算する
続いては勤務日数を自動で数えていきます。
今回の例では、出勤日はシフト表では空白（何も書かない）で表すということにしましたね。
もし、出勤日を何らかのマークで表している方は、次項の「休みの数を自動計算する」を参考にしていただければと思います。
つまり、ここでは「シフト表で空白を数える方法」と題した方が良さそうですね。
それでは早速やってみましょう。
シフト表で空白を数える方法
実際に今回のシフト表を例に説明していきます。
単純にスタッフ1人に対する空白の数を数えるだけなら簡単です。
しかし、月によって日数は異なり、存在しない日付は空白になります。
例えば11月ですと、31日は存在しませんよね？
この場合、31日の列は空白になりますが、勤務であると言う意味ではなくなります。
このように、同じ空白でも数えたい空白と数えたくない空白が出てきてしまいます。
そこで、先ほど計算した「土日祝の数」と「営業日の数」を使って、うまくその問題を解決することが出来ます。
勤務日数（空白）を数える
=COUNTBLANK(1日の勤務予定のセル:31日の勤務予定のセル)-(31-(営業日+土日祝の数))
つまり、今回はC4～AG4のシフトについて調べ、営業日はAH7、土日祝の数はAH9に計算してあるので、以下の式になります。
=COUNTBLANK(C4:AG4)-(31-($AH$7+$AH$9))
やっていることは、
=「指定した31日分の中の空白の数」ー「31から営業日と土日祝の数を足した数を引いた数」
そろそろ混乱してきましたか？
よくわからない方は、何も考えず真似して入力してみて下さい。
はじめは理由はわからないけどうまく計算できる状態くらいでOKです。
休みの数を自動計算する
次は休みの数を数えてみましょう。
前項で「勤務の日は空白じゃなく何か書いてある」という方も、こちらのやり方を参考にしていただけたらと思います。
今回は、分かりやすく年間休日を数えてみましょう。
例えば、とある人の「年」と書かれた個数を数えれば、年間休日の日数が計算出来るという場合、以下のような式で計算できます。
年間休日を数える
=COUNTIF(1日の勤務予定のセル:31日の勤務予定のセル,勤務表マークの書かれたセル)
つまり、今回の場合は
=COUNTIF(C4:AG4,勤務表マーク一覧!$B$7)
上の式の緑の部分に直接「”年”」と書いても計算できますが、せっかく上図の勤務表マーク一覧を作成したので、これを参照するようにしましょう。
本連載記事の3回目まで終えた時点での勤務表サンプルです。
皆さんの職場に合わせて自由に改変していただいて構いません。
おわりに
いかがでしたでしょうか？
今回は色々組み合わせて計算していったので、難しく感じる方もいらっしゃったのではないかと思います。
当ブログのコンセプトは、完全に理解しなくても真似すればとりあえず出来ることを目標にしていますので、まずはそれぞれの職場に合わせたシフト表作りに少しでも導入して頂ければ幸いです。
連載目次： 見やすいシフト表をエクセルで簡単に作る方法
本シリーズでは、見やすいシフト表を時間をかけず少しでも簡単に作れるよう記載しています。
- 【シフト表作り】月日の入力を完全自動化しよう
- 【シフト表作り】 土日祝の色付けを完全自動化しよう
- 【シフト表作り】 勤務日数や休みの数を自動で数えてみよう今ここ
- ～以下、随時加筆予定～
コメント