今回は時間帯ごとの勤務人数をカウント、休憩時間も反映したガントチャート型シフト表の作成方法をご紹介いたします。
シフト表について
「ガントチャート型シフト表」では
- 名前を入力して時給を自動反映する方法
- データ入力規則の設定方法
- 勤務時間と日給の計算方法
- 休憩時間も反映したガントチャート型シフト表の作成方法←今回の記事
4つの記事で構成されています。
目次
ガントチャート型のシフト表の作成方法
勤務中の時間と休憩時間の判定
- 【I3】に下記の数式を入力します。
|
1 |
=IF(AND($E3>I$1,$D3<=I$1),"0",IF(AND($C3>I$1,$B3<=I$1),"1","")) |
#が表示されているI行には前回の記事で作成した時間軸が入力されているはずやね。
数式の解説
数式が長いので色分けして解説します。
=IF(AND($E3>I$1,$D3<=I$1),”0″,IF(AND($C3>I$1,$B3<=I$1),”1″,””))
IF((A,B),””)：AとBの両方とも一致しなければ空白を表示
A：AND($E3>I$1,$D3<=I$1),”0″
①休憩開始時刻>8:00
②休憩終了時刻<8:00
⇒①と②両方の条件の一致すれば0表示
B：IF(AND($C3>I$1,$B3<=I$1),”1″)
①Aの条件式が一致しなかった
②勤務開始時刻>8:00
③勤務終了時刻<8:00
⇒①②③すべての条件の一致すれば1を表示
- 【I10】までドラッグします。
- ドラッグしたあとに表示されるボタンをクリックし「書式なしコピー」を選択します。
- さらにそのまま右端【BL10】までドラッグします。
- ドラッグした後に表示されるボタンをクリックし「書式なしコピー」を選択します。
これで自動的に勤務している時間帯には「1」、休憩している時間帯には「0」が入力されるようになりました。
COUNTIFで時間帯ごとの勤務人数を自動で数える
- 【I11】に下記の数式を入力します。
|
1 |
=COUNTIF(I3:I10,"1") |
数式の解説
COUNTIF関数は特定の文字の個数を数えます。
今回の場合、セル【I3:I10】の中に「1」は何個あるか数えてその時間帯の勤務人数をカウントしています。
- 右端までドラッグします。
- ドラッグしたあとに表示されるボタンをクリックし「書式なしコピー」を選択します。
これで各時間帯の勤務人数をカウントできるようになりました。
なお、休憩中のスタッフは人数にはカウントされていません。
条件付き書式設定でガントチャートに色をつける
- ガントチャートを設定したい【I3:BL10】を範囲選択し、ホームタブ内の「条件付き書式」から「新しいルール」をクリックします。
- 「指定の値を含むセルだけを書式設定」を選択します。
- 「特定の文字列」「次の値を含む」を選択し、右側の枠内に「1」（休憩時間は「0」）を入力します。
- 「書式」をクリックします。
- 「塗りつぶし」タブをクリックします。
- 勤務中の時間帯（または休憩時間）に塗りつぶしたい色を選択します。
- 「フォント」タブをクリックします。
- 先ほど選択した塗りつぶしの色と同じ色を選択します。
- 「罫線」タブをクリックします。罫線の左端・右端をそれぞれ2回クリックし、右図のような状態にします。
- 罫線の色「白」を選択します。
- 上線と下線をクリックします。それぞれボタンが青くなっていれば大丈夫です。
- 「OK」をクリックします。
- 「OK」をクリックします。
同様に休憩時間も条件付き書式設定をすればガントチャート型のシフト表が完成します。
仕上げに…
- 「#」が表示されているセルを範囲選択します。
- 「#」のセルの塗りつぶしと同じ色を選択します。
セル内の数式が消えたわけではなく、視覚的に見えなくなっているだけやで。
これでガントチャート型シフト表の完成です。
コメント