こんな感じのフォーマットを作成してみます
流れとしてはこんな感じ
- 「連続データの作成」を使用して時間軸を作成
- 時間軸の時間をリストに設定
- IFとAND関数を使用し、出勤・退勤時間が時間軸に該当する場合は「〇」を出力する
- 「〇」を出力する範囲の文字色にガントチャートの色を設定する
- 「〇」の数を数えるためにCOUNTIF関数を使用
- 「条件付き書式設定」を使って「〇」が出力されているセルに書式設定を行う
- STEP3で設定した文字色と同じ色で塗りつぶしをするとガントチャートになる
Excelでガントチャート形式のシフト表を作成する方法
Excelで時間軸を作成
今回はサムネと同じように1時間ごとのガントチャート形式のシフト表を作成します。
①セル[D1]に時間軸の開始時刻を入力
②「ホーム」タブをクリック
③「フィル」を選択(Excelウインドウが小さい場合は「編集」→「フィル」の順に選択)
④「連続データの作成」をクリック
①「増分値」には時間軸のメモリを入力
②「停止値」には時間軸の退勤時刻を入力
③「OK」をクリック
シフト表の時間軸が完成しました。
プルダウンメニューで時間を選択できるようにする
セル[B3:C8]を選択
①「データ」タブをクリック
②「データの入力規則」をクリック
①入力値の種類のプルダウンメニューから「リスト」を選択
②元の値の上矢印をクリックして作成した時間軸の範囲を選択
③セル範囲を選択したら下矢印をクリック、最後に「OK」をクリック
プルダウンメニューで勤務時間を選択することができるようになりました。
ガントチャートを作成する準備
あらかじめ出勤、退勤列に適当に時間を入力しておく。
セル[D3]に以下の数式を入力してEnter
1 |
=IF(AND($3-TIME(0,60,0)>=D$1,$B3<=D$1),"〇","") |
数式の内容を理解したい方はこちらをクリック!
出勤が8:00・退勤が12:00の場合、8:00の時間軸の条件式は一致するので「〇」が出力されます
「〇」が出力されたセルを選択し、右下の■をセル[D8]までドラッグ。
さらにセル[D8]の右下■をセル[S8]までドラッグ
出勤・退勤時間に該当するセルに「〇」が出力されます
出勤人数をカウントする方法
セル[D10]に以下の数式を入力してEnter
1 |
=COUNTIF(D3:D8,"〇") |
「〇」の数がカウントされます。
さらに右下の■をセル[S10]までドラッグ
すべての時間軸の出勤人数がカウントされました
セル[D3:S8]を選択
「〇」の文字色を「オレンジ」に変更
ここで設定する色が、ガントチャートのバーの色になります
ガントチャートを作成する
セル[D3:S8]を選択
「ホーム」タブから「条件付き書式設定」→「新しいルール」を選択
①「指定の値を含むセルだけを書式設定」をクリック
②「特定の文字列」→「次の値を含む」→「〇」を入力
③「書式」をクリック
④「塗りつぶし」を選択
⑤さきほど設定した「〇」の文字色と同じ色(今回はオレンジ)を選択
⑥「羅線」タブをクリック
⑦「実線」を選択
⑧「白、背景1」を選択
⑨上線と下線を選択
➉「OK」をクリック
⑪「OK」をクリック
「〇」が出力されているセルが塗りつぶされ、ガントチャートが完成しました。
あとはデザインを整えて完成です。
コメント