Excelでシフト表を作成する方法

Excelでシフト表を作成する方法をご紹介します。

条件

  • Excel 2016

完成イメージ

まずは完成イメージを確認します。

  • 各人ごとに開始時間と終了時間を入力します。
  • 開始~終了までを棒グラフのような表記にします。
  • 開始~終了を3通り入力できるようにします。
  • 開始/終了時刻をリストから選択するようにします。

シフト表の作成

表の作成

まずは、セルを罫線で囲ったり、セルを塗りつぶしたりして表を作成します。
今回は以下のように作成しました。

セルの書式設定

開始~終了のセル全体を選択し、セルの書式設定を開きます。
表示形式で「文字列」を選択し、OKボタンを押します。

同様に、時刻のセルも、表示形式で「文字列」に設定します。

条件付き書式にルール追加

セルの色設定

時刻(セルI5)~右下(セルAB25)までを選択し、メニューの「条件付き書式 > 新しいルール」を選択します。

新しい書式ルール画面が開くので、「数式を使用して、書式設定するセルを決定」を選択します。
「次の数式を満たす場合に値を書式設定」の入力欄に以下を記入します。

=MOD(COLUMN(),2)=0

書式ボタンを押して、セルの書式設定画面を開きます。
塗りつぶしタブにおいて、背景色に薄いグレーを選択してOKボタンを押します。

新しい書式ルール画面もOKボタンを押して閉じます。
すると、以下のように交互にセルの色がグレーになります。

グラフの表示

時刻の下(セルI6)~右下(セルAB25)までを選択し、メニューの「条件付き書式 > 新しいルール」を選択します。

新しい書式ルール画面が開くので、「数式を使用して、書式設定するセルを決定」を選択します。
「次の数式を満たす場合に値を書式設定」の入力欄に以下を記入します。
(当該数式は、文字列を数値に変換後、開始1~終了1日時の範囲かどうかを確認するというものです。)

=AND(VALUE(I$5)>=VALUE($C6),VALUE(I$5)<VALUE($D6))=TRUE

塗りつぶしタブにおいて、塗りつぶし効果ボタンを押します。

塗りつぶし効果画面で、適当に色を選択し、グラデーションの種類は「横」、バリデーションで真ん中が濃くなるものを選択します。

OKボタンを押して各画面を閉じます。

同様の手順で「開始2~終了2」および「開始3~終了3」の書式ルールを追加します。

  • 開始2~終了2の数式
    • =AND(VALUE(I$5)>=VALUE($E6),VALUE(I$5)<VALUE($F6))=TRUE

       

  • 開始3~終了3の数式
    • =AND(VALUE(I$5)>=VALUE($G6),VALUE(I$5)<VALUE($H6))=TRUE

以上で書式ルールの設定は終わりです。
条件付き書式ルールの管理画面(対象セルの範囲を選択して、条件付き書式 > ルールの管理)を確認すると、以下のように4つのルールが設定されている状態です。

現段階でも、開始および終了に時刻を入力すれば、以下のような表示となります。

セルのリスト設定

より入力を楽にするため、セルで値をリスト選択できるようにします。

開始1~終了3の入力セル全体を選択した状態で、データタブの「データの入力規則 > データの入力規則」を選択します。

データの入力規則画面が開くので、入力値の種類で「リスト」を選択し、”空白を無視する”と”ドロップダウンリストから選択する”をチェックした状態で、元の値に以下を入力します。

10:00,10:30,11:00,11:30,12:00,12:30,13:00,13:30,14:00,14:30,15:00,15:30,16:00,16:30,17:00,17:30,18:00,18:30,19:00,19:30,20:00

OKボタンを押して画面を閉じます。

以上でリスト選択により、時刻の入力が出来るようになりました。

以上でシフト表の作成は完了です。

サンプル

今回作成したシフト表サンプルを公開しています。
以下のリンクをクリックしてダウンロードしてください。

シフト表サンプル

参考

GetNaviweb:エクセルでシフト表を作るには? 「ガントチャート」作成手順をイチから解説

エクセルでシフト表を作るには? 「ガントチャート」作成手順をイチから解説

数値文字列を数値に戻すVALUE関数

https://kokodane.com/2013_kan_047.htm

Office Hack:Excelでプルダウンメニュー(ドロップダウンリスト)を作成する方法

https://office-hack.com/excel/pulldown-menu/

コメントを残す

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です