時間帯別表示できるタイムシフト表をエクセルでつくろう

スポンサーリンク

スタッフのスマホに専用アプリを入れて会社のパソコンでシフト管理できる、有料クラウドサービスが導入できる規模のある企業などで、働いている方はこのページにたどり着くことはないと思います。

そんな要望が届いたので、エクセル関数とエクセルVBAを組み合わせて作ってみましたので、参考になれば幸いです。

既存のエクセルシフト表にVBAを加える？作り直す？

今回作成前の従来のエクセルでのシフト表もそうなのですが、「1シート1人」で管理していませんか？

図1は人数分のシートを用意して、それぞれの従業員の勤務希望時間を記入して、「シフト表一覧」にそれぞれのセルを「＝」で結んで全員の勤務シフトを表示していました。

本当にシンプルなので、超アナログ感たっぷりのシフト表を使っていました、しかもつい最近まで・・・

シフトをずっと作っていたスタッフにしてみれば、長年使っていたエクセルでのシフト表なので、ごくごく自然だったようですが、担当が変われば見方も変わります。

既存のエクセルで作っているシフト表にVBAを加えて、シフト表自体をバージョンアップさせるか、作り直すか？

ヒアリングして分かった問題点

既存のシフト表にVBAを付け加えて作業効率化を図るか、全く新しく作り直すかの判断は、新しい担当者に問題点を聞いてからでも遅くはありません。

そして、何が面倒か？これができたら早くシフト作ることができるのか？などをヒアリングしてみると、なるほどと思う事が次から次へと出てきました。

1. 入力時にスタッフを探す（シート）事が面倒
2. 希望時間を記入後に人員調整で消すと後から見直せない
3. 時間帯に誰が勤務しているのか見つけづらい
4. 休日はカレンダーのように赤文字がいい
5. 概算の給与計算ができると人件費の把握ができる

言われる通り既存のエクセルのシフト表を見ると、探すのが面倒だし、スタッフの勤務状況が分かりずらいという問題点は、うなずけます。

自分の決定したシフトだけを見る分には問題ないですが、シフトを作成している途中では、時間帯別になっていないので頭の中でイメージが必要でした。

取り急ぎならばVBAを加えれば解決できる

これらすべての要望を取り入れると作成に時間がかかってしまうので、とりあえず短期間で出来そうな、1・2・3のみ取り入れてエクセルでのシフト表をイメージしてみました。

そうなると、1番の従業員の人数の分だけシートが増えて探しずらいという点を修正するだけならば、ユーザーフォームを使って名前を押せばジャンプする設定をすれば解決なので、すぐできそうですね。

次に2番の希望時間帯の入力と修正後の勤務時間帯が、同じセル1か所で作業をするのが問題なので2つに分ければ解決です。

最後に3番はタイムシフト形式で表示できるシートを加えれば完成しそうなので、既存のシフト表にVBAを加える修正版を作ってみました。

時間があれば作り直すが一番

実際のところ、既存のシフト表は使っている関数もほぼ無く、VBAも使っていないので最初から作り直しても実際のところさほど変わりません。

ただ、日々通常業務をしながらのついでの作業で、エクセルのシフト表を修正するので時間をかけてはいられません。

ただ、シートの数が多すぎるので無駄にVBAコードが長くなるので、VBAコードを書くのも面倒だから、とりあえずの修正ができたらちゃんと最初から作った方が4番・5番も取り入れて作るので、後回しにしましょう。

既存エクセルシフトにVBAを追加する

自分のところながら、ここまでアナログなエクセルシフト表だったとは知らなかった！のですが、1番目の改善要望でもある「シートがスタッフ分あるので探すのが面倒」という点を改善しましょう。

各スタッフそれぞれのシートに瞬間移動する

上の図にはA1～A16が見切れていますが、実際にはA20までシートが続いています。

スタッフ1名につき1シートなので20名分まで用意されていますので最大20名となるのはご理解いただけるでしょう。

このままじゃ誰がどこのシートに名前があるかすらわからない状態ですが、実際にはシート名をスタッフ名に変更してシフト作成時にはスタッフ個人のシートを探して入力しています。

まず画像のようにオリジナルフォームを作ることができる「ユーザーフォーム」を作成し、コマンドボタンを人数分の20個と、元に戻る用のコマンドボタン1個の合計20個を配置します。

ユーザーフォームの作り方やコマンドボタンの配置の仕方などは、ここでは省略しますが、気にかる方は下記に記載しているのでご覧くださいませ。

このユーザーフォームを使う事により、ボタンにスタッフ名を取り込んで表示させて、押すことによりそのスタッフのシートへ瞬間移動させることができるので、その設定をするVBAコードを書いてみましょう。

ここではコマンドボタン1にはシートA1のスタッフ、コマンドボタン2にはシートA2のスタッフと・・・20人分それぞれ書き込みます。

では、コマンドボタン1に書いたVBAコードで、セルC2は1日の出勤時間を記入するセルになっています。

とても簡単なので、コマンドボタン2にはシートA2に変更するだけで、同じようにコマンドボタン20まで繰り返せば完成です。

こうすることで、シートをわざわざ探すことも無くなるし、そもそもシート名を変更することも必要なくなります。

※絶対にシート名を変更させてはいけません、シート名が変わるとエラーの原因になります

スポンサーリンク

瞬間移動後にスタッフリスト再表示

そして、各シートの名前の下の辺りのセルA5にコマンドボタンを配置して、ユーザーフォーム1を立ち上げるVBAコードを書いておきます。

これにより、瞬間移動したら希望シフトなど入力して、セルA5辺りに配置したコマンドボタンを押すことにより、再びユーザーフォーム1を立ち上げることができるので、次のスタッフへの瞬間移動が可能になります。

これで、スタッフ表示というコマンドボタンを押すとユーザーフォームが立ち上がるのですが、名前がおかしなことになっています？？

これは、ユーザーフォームを表示するVBAコードしか書いていないので、コマンドボタンの名前がそのまま表示されているのです。

このままでは誰なのか分からないので、表示と同時にコマンドボタンの名前をスタッフの名前に書き換えましょう。

手順としては、シートのスタッフの名前をコマンドボタンに表示してからユーザーフォームを立ち上げれば完成です。

MENUボタンには、シート「Menu」へ戻る設定をしますが、まだ戻る先の詳細を決めていないので、後で設定します。

スポンサーリンク

希望時間帯と決定時間帯を分けて管理する

これは言い換えれば、提出してもらった勤務希望シフト（元データ）と、人員調整をして削除したり短縮したシフト（新データ）を比較できるようにすれば完成です。

ものすごく単純なのですが、元々の個人シートにあるシフト希望表を2つにしてしまえばいいのです。

最初にも見ていただいた元々の個人シフト希望表を下記のように、2つにして希望シフトを右側に入力して、修正後のシフトを従来の左側を使うようにすれば完成です。

ただ、希望シフトを2回入力するのは面倒なので、シフトコピーボタンを（コマンドボタン）を配置しておけば解決ですね。

それでは、シフトコピーボタンにVBAコードを書き込みましょう。

ここで覚えておきたい「指定回数分繰り返し処理をするVBAコード」がありますが、VBAコードを書く回数が増えれば、とっても便利なVBAコードだと分かるのでぜひマスターしてくださいね。

このFor～Nextステートメントを使う事により、1日～31日までの出勤・退勤の合計62個のセルをボタン1回押すだけで順番にコピーして書き込んでくれる優れものです。

詳細についてはここでは省略しますので、詳しく知りたい方は下記をご覧くださいませ。

このVBAコードを各シートのシフトコピーボタンを配置すれば完成です。

タイムシフト表を作成する

現在使用しているスタッフ全員のシフト一覧表で、8名分まで切り取って画像にしました。

縦にスタッフ名で横に日付、スタッフの上段が出勤時間が表示されて下段が退勤時間が表示されるようになっています。

人員調整などをして確定したシフト希望時間は、従来から使っているものそのまま「＝」で結んでシートの一覧表にリンクさせています。

例えば、A1さんの1日の出勤時間はA1さんのシートのセルC2の値が入るようになっています。

これを廃止するわけではなく、事務所などに張り出したり、スタッフへPDFファイルにして送付してあげたりするのにそのまま使いますが、シフトを作成する時に時間帯の勤務状況を把握するために見やすくなるのがタイムシフト表です。

タイムシフト表とは？

タイムシフト表とは、当日の営業時間帯に勤務しているスタッフを視覚的に分かるように出勤時刻・退勤時刻を数字で表すだけではなく、「＊」などの記号や背景色を変えて号ブラフ風に表した1日ごとのシフト表です。

本当は、「＊」で勤務時間帯を表すのではなく、背景色を変更して棒グラフ風にしたいところなのですが、VBAコードを書くのに時間がかかるので、単純な数式を入れて「＊」で表しています。

そしてタイムシフトの欠点でもある、1日ごとに31日分必要なのですが、同じ作業を31回も設定するのが面倒なので、前日・翌日ボタンを配置して、押したらそれぞれ日付が変更になり、データを再取得して表示するようにします。

そしてよく見ていただくと分かるのですが、8行目から19行目が非表示になっています。

これは、勤務予定のないスタッフまで表示されると空欄だらけで見にくくなるので、お休みのスタッフは自動的に非表示になり、出勤予定者のみの行表示としています。

それではエクセル関数、VBAコードそれぞれ順番にお話ししましょう。

エクセル関数の設定

今回はエクセル関数を使ってタイムシフト表を作りますが、たった3つの種類のエクセル関数で出来ています。

従業員番号と名前

タイムシフト表の従業員番号と名前は20名分すべて、各個人シートの社員番号と名前とリンクさせているだけです。

例えば、A1さんならば、シート名「A1」にある社員番号は「セルA2」、名前は「セルA4」ですね。

全く問題ないですよね。

出勤時間と退勤時間

データベース関数のDGETを使います。

表示したい日付が指定されたら、個人シートの勤務希望表の範囲から該当する日付を見つけて出勤時間を取得して表示し、同じことを退勤時間でも行います。

まずは上のタイムシフト表の画像の、セルC4（A1さんの出勤時間）に下記のDGET関数を書いてみました。

このエクセルDGET関数では、A1さんの出勤時間を取得したので今度は退勤時間を打ち込みましょう。

違いは出勤時間のC1が退勤時間のD1に変更になっただけです。

DGET関数の詳細についてはここでは省略するので、詳しく知りたい方は下記をご覧くださいませ。

出勤を表す「＊」マークをつける

タイムラインシフト表の一番肝心なところになり、最初にお話しした通り時間があればVBAを使って背景色を変更することにより棒グラフ風に見た目をよくしたいのですが、ここでは時間の関係で出勤しているならば「＊」印にして、勤務外ならば空白で表します。

例えば6:30のセル（E1）を日本語でこの表現をエクセル関数風に表すと下記のようになりませんか？

このようになり、もし～だったらなのでIF関数を使用し、両方を満たす時という条件が加わっているのでAND関数を組み合わせます。

このエクセル関数を30分単位で6:30～25:30までを繰り返して入力すれば、1人分が完成し、20名分繰り返せばタイムラインシフト表が完成しますね。

スポンサーリンク

VBA設定

前日・翌日ボタンにマクロを記録

この前日ボタンと翌日ボタンを配置してシフトを状況を見たい日が見られるようにしています。

もちろん、直接セルB2に日付を打ち込むこともできます。

まずは、前日ボタンを押すと、日付表示がされているセルB2の値（日付）が表示の前日になるようにVBAコードを書き込みます。

とてもシンプルで、当たり前なのですが前日なので「‐1」をしただけで翌日ならば「+1」となります。

当たり前ですが、勤務しているスタッフが時間帯別に見た目で分かるようにしたのが、タイムシフト表なので、お休みのスタッフの行は自動的に非表示に設定するVBAコードを付け足しましょう。

実は、翌日ボタンで隠しているのですが、セルC1には「出勤」と記載、セルC2には「>7:00」と記載しています。

これにより、7時以降に出勤予定が記載されている人を抽出するって事が出来るようになっていて、記載がない人はお休みなのでフィルター機能により抽出されなくなって非表示にできているんです。

当然ながら翌日ボタンにも同じように記載しています。

フィルター機能に関しての詳細はここでは省略しますので、詳しくは下記をご覧くださいませ。

これでおおよそのタイムシフト表が出来上がりましたが、上手くできましたでしょうか？

あとは、体裁を整えるためにメニュー画面を付け加えたり、各シートのカレンダーの元になる日付の設定などを加えればひとまず完成ですね。

さて、今日はここまで。

続きは後日追記します。