
職員のシフトを組むとき「休みの希望を出したのに出勤の予定が組まれていた」「勤務日数を均等にしたいのに変更の度に数えるのが大変」など、頭の中や手計算でバランスよく作成するのは大変。しかし、エクセルの関数を活用すると、職員ごとの月間勤務日数の自動集計や重複チェックが可能です。
この記事のポイント
- Excelで効率よくシフトのシミュレーションをする方法がわかる
- 職員ごとの月間勤務日数の自動集計や重複チェックに便利な関数を紹介
- カレンダーの日付や曜日を自動で入力する小ワザも
使い方
以下、テンプレート「シフトシミュレーション」をもとに解説します。
ステップ① 日付と曜日を自動で表示するカレンダーをつくる
まず、シフトを入力するカレンダーを作成します。このとき「DATE関数(=DATE(年,月,日))」を活用すると、一カ月分の日付と曜日を自動で表示するしくみをつくれます。
- カレンダーの基準となる1日目(F6)には「=DATE(A4,C4,1)」と入力
- 2日目には「=1日目+1」を入力。オートフィル機能で最終日まで数式をコピーします
- 日付に応じた曜日を表示するには、「TEXT関数(=TEXT(値,表示形式“aaa”))」を使います
ステップ② 職員名簿を作成する
続いて、職員名を直接入力ではなく候補一覧から選択できるように職員名簿を作成。カレンダー上の職員名を入力するセルを、[データ]-[データの入力規則]から入力値の種類をリスト、元の値を職員名簿のセル範囲に設定することで名簿に記載した職員名を選択肢として表示できます(参照したい列に任意の名前を付けて元の値に設定することも可能)。
さらに、職員ごとのシフト別勤務日数の集計や重複チェックも関数を使うことで自動化。
- 「COUNTIF関数(=COUNTIF(範囲, 検索条件))」を使って、検索条件に一致したデータの数を数えることができます。図では、K列のシフト(早番1)に組まれたS7の職員(わかば)の数を「=COUNTIF($K$6:$K$36,S7)」でカウントしています。
- 同じ日に同じ職員が入力されると、「チェック」に「重複」と表示されます。COUNTIF関数で範囲内の各値の出現回数を数え、SUMPRODUCT関数で合計。その合計を空白でないセルの数(COUNTA関数)で引いたとき、「0」以外の場合は「重複」となります。
行事予定を記入して配布すれば月間カレンダーとしても活用できます。