エクセルでシフト表を作成している方より７連続勤務を禁止、またはすぐに気づけるようにする方法はないかと相談があり作ってみました。
検索しても、なかなか思った通りのものが見つからず作るのに手間がかかったので紹介します。
今回使用するエクセルは下よりダウンロードできます。
目次
１、エクセルの形式と課題
２、数値に置き換える
３、連続する最高の値を表示
４、連続する最高の値の最高値を表示する
１、エクセルの形式と課題
まずは今回使用するエクセルの形式と課題についてです。
簡単なシフト表もどきですが・・・
列に日付、１０日まで
行に人名、３名です
勤務は早番、平常、遅番と休みがあることにします。（複数種類の勤務がある場合にも対応します）
わかりやすいように休みを赤にしています。
初めは、７日の中で休みがあるかをカウントすればできると思いました。
しかし、Aさんのように1日と10日に休みがあると7日区切りで休みをカウントしても意味がないことがわかります。しかも、シフト表は一月分なので31日です。
そのため、今回は別シートで早、平、遅、休みを数値（1と0）に置き換えます。置き換えた数値で1日から10日までで連続する1の最高値を求めます。求めた最高値が7以上かで条件付き書式または入力規則で判定を出すという形式にしたいと思います。
２、数値に置き換える
まずは、新しいシート（置き換え）を作成して同じような勤務表をコピーアンドペーストします。勤務については削除します。
その後は、休みの日を0としてそれ以外を1とするIFとCOUNTIFを使った式を入れます。シフト表シートを基準に入れます。
=IF(COUNTIF(シフト表!C3,”休み”),0,1)
こうすることで、休みの日は0それ以外の出勤日は1となります。ここでのポイントは休みを0にすることです。出勤日はいろいろな種類がありますが、休みの種類は少ないのでカウントがしやすいです。
休み以外にも必要な場合は例えば有給などの場合は
=IF(COUNTIF(シフト表!C3,”休み”)+COUNTIF(シフト表!C3,”有給”),0,1)
と+ で入れてください。
数値に置き換えオートフィルで全体に反映するとこのようになります。
３、連続する最高値を求める
1が連続する最高値を求めます。今回は11日に当たる列で求めます。
Aさんの場合は3列目なので
=MAX(FREQUENCY(COLUMN(C3:L3),(C3:L3<>1)*COLUMN(C3:L3))-1)
と打ち込みます。そうすると8と表示されるはずです。
B、Cさんはオートフィルで連続する最高の値が出ます。
４、連続する最高値の最高値をシフト表に表示する
このままでも使えますが、別シート（置き換え）をいちいちみないといけないのでシフト表のシートで連続する最高値の最高値を出しましょう。
シフト表のC1に一番大きい値をMAX関数で求めます。
=MAX(置き換え!M3:M5)
こうすることで勤務表を作成しながら連続勤務数を一目で確認することができます。
もう少し、凝りたい場合は条件付き書式で7以上なら赤くする。入力規則で7以上になったら警告を出すなどの方法が考えられます。
最後にもう一度、今回使ったエクセルファイルを貼っておきます。
見て触れて学ぶネット詐欺体験サービス「にせサギ」
にせサギはネットサギを安全に体験できるサービスです。
子供・高齢者・障害者、誰もが安全にインターネットを使える社会を目指しています。
解説の小冊子や教え方や授業案を記載した資料も無料で配布しています！
