Excel。出勤時間を自動で塗りつぶす日勤シフト表を作ってみる。
【シフト表】
以前に勤務日や午前・午後・夜勤の出勤によってセルが、自動的に塗りつぶされるシフト表の作り方をご紹介したことがあったのですが、
出勤している時間が塗りつぶされている日勤のシフト表を作りたいんだけど、
どうしたらいいかなぁ～と、
ご質問がありましたので、今回はその作り方をご紹介してみたいと思います。
作りたい表はこんなのです。
出勤する時間帯。それも30分刻みですが、セルを塗りつぶしているというシフト表です。
今回はサンプルなので3名ですが、
多くの方がシフトや時間によって、
様々な勤務シフトで稼働している会社さん部署さんも多いと思います。
いちいち塗りつぶしているのは面倒ですよね。
そこで、例えばC3に出勤時間をD3に退勤時間を入力すると、
その勤務時間を自動的に塗りつぶし、
さらに、勤務時間も計算しちゃうシフト表を紹介していきたいと思います。
以前紹介したものは、プロジェクトでしたので、何日から何日までということだったのですが、
今回の時間は少し癖がありますので、注意するところもありますが、
ある癖も回避できるように作っていきます。
では、まずは、フレームを作りましょう。
まずポイントなのですが、E2に8:00と入力してF2に8:30と入力します。
この二つのセルを範囲選択をしてオートフィルハンドルを使ってコピーしていきましょう。
実は入力を気を付けないと、何日の何時ということで、あとで支障をきたしてしますので、
入力するよりもオートフィルハンドルを使うことをお勧めします。
うちの会社は、20:00までなんですが…という場合は列を増やしていただければ大丈夫です。
次に、C列とD列を作っていきますが、
ここの入力を今日以外で11:00と入力してもうまく反応してくれません、
ここもポイントで、日付が絡んでくるからです。
つまり、手で入力すると、その日の11:00になってしまい、
2行目の行タイトルの時間と異なってしまい、
この後に設定する条件付き書式がうまく稼働してくれません。
そこで、このC列とD列には、データの入力規則を使って、入力するようにした方が、
楽ですし、きちんと条件付き書式が反応してくれます。
では、C3:D5を範囲選択してデータタブのデータの入力規則をクリックして、
データの入力規則のダイアログボックスを表示しましょう。
入力値の種類を【リスト】にして、元の値には、=$E$2:$W$2 と入力します。
見出し行の時間のところを使っています。
あとは、OKボタンをクリックしましょう。
これで、出勤と退勤を入力しやすくなりました。
では、サンプルで、出勤を8:00 退勤を11:00としておきます。
今度は、勤務している時間に1を算出するように数式を作っていきます。
E3をクリックして、IF関数のダイアログボックスを表示します。
まず、出勤時間と見出し行の時間を比べて、出勤時間が大きいかを確認しますので、
論理式には、$C3>E$2
オートフィルハンドルを使って数式をコピーしますので、複合参照を設定しておきます。
真の場合には、”” 空白
偽の場合には、今度は退勤と見出し行の時間を比べますので、IF関数を入力しますので、
再度IF関数のダイアログボックスを表示しましょう。
論理式には、$D3>E$2
真の場合には、1
偽の場合には、”” 空白
あとは、OKボタンをクリックしましょう。
数式は、
=IF($C3>E$2,"",IF($D3>E$2,1,""))
となっています。数式をオートフィルハンドルを使ってコピーします。
続いて、勤務時間の数式を作っていきます。
30分ごとに1が入っておりますので、合計数を2で割る必要があります。
x3の数式は、
=SUM(E3:W3)/2
となります。この数式もオートフィルハンドルを使ってコピーします。
今のところ、このようになっています。
この1が入力されているセルに塗りつぶしをしますので、
【条件付き書式】を使って数値の1が入力されていたらセルを塗りつぶすように、
設定していきましょう。
E3:W5までを範囲選択をして、ホームタブの【条件付き書式】をクリックします。
セルの強調表示ルールの指定の値に等しいをクリックします。
そうすると、指定の値に等しいダイアログボックスが表示されますので、
1と入力して、書式には、明るい赤の背景を選択してOKボタンをクリックします。
書式の塗りつぶしは何でもOKです。
これで、塗るつぶしはできましたが、数値の1が邪魔ですね。
これを削除しては何の意味もありませんし、塗りつぶしの色と同じにしても印刷した時に、
薄く見えてしまうこともありますので、表示形式を使って見えなくさせます。
E3:W5までを範囲選択をして、セルの書式設定ダイアログボックスを表示します。
表示形式のユーザー定義を選択して、
;;;（セミコロン×3）
と入力して、OKボタンをクリックしましょう。
これで完成しましたね。
様々なテクニックを盛り込みましたが、一部でも知っていると他にも流用できますので、
少しずつでもいいので、様々なテクニックを覚えていくと、
きっと仕事で使っている資料や表も使いやすくなると思いますので、
改善していってはいかがでしょうか？
