調べたいExcel-VBAの使用例などキーワードを入力してください

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

タイムシフト表を作る方法作成事例
スポンサーリンク

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

とりっぷぼうる
とりっぷぼうる

私のところもそうですが、従来ながらのエクセルでシフト表を作っていて、こんなことを思ったのではないでしょうか?

  • 各個人のシフト希望時間帯の入力作業を簡単にしたい
  • 勤務状況をひと目でわかる時間帯別グラフで見たい
  • お休みのスタッフは表から消して出勤者のみに見やすくしたい

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

スポンサーリンク

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

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

シフト作成1
シートだらけのシフト表

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

シフト希望表2
シンプルな記入箇所(クリックで拡大します)※25日までを抜粋

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

シフト一覧表
10日までを抜粋

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

既存のエクセルで作っているシフト表に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名となるのはご理解いただけるでしょう。

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

とりっぷぼうる
とりっぷぼうる

それでは画像のように瞬間移動するVBAを追加しましょう

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

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

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

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

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

瞬間移動するVBAコード

Private Sub CommandButton1_Click()
Unload UserForm1
Sheets(“A1”).Select
Range(“C2”).Select
End Sub

VBA解説
  1. コマンドボタン1が押されたら
  2. ユーザーフォーム1を閉じる
  3. シートA1を選択する
  4. セルC2を選択する
  5. マクロ記録終了

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

とりっぷぼうる
とりっぷぼうる

出来上がったら、VBA画面でプログラムの実行ボタンを押して【▶】みれば、瞬間移動すれば完成です。

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

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

スポンサーリンク

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

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

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

タイムシフトスタッフ表示ボタン
せっかくなのでフォントも変えてみました
ユーザーフォームを立ち上げるVBA

Sub ユーザーフォーム立ち上げ()
UserForm1.Show
End Sub

VBAコード
  1. 「ユーザーフォーム立ち上げ」というマクロを実行する
  2. ユーザーフォーム1を表示する
  3. マクロ記録終了

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

タイムシフト作成表示未設定
このままだと誰か分からない(クリックで拡大します)

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

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

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

名前を読み込んでからフォーム立ち上げ

Sub ユーザーフォーム立ち上げ()
UserForm1.CommandButton1.Caption = Worksheets(“A1”).Range(“A4”).Text
UserForm1.CommandButton2.Caption = Worksheets(“A2”).Range(“A4”).Text
UserForm1.CommandButton3.Caption = Worksheets(“A3”).Range(“A4”).Text
UserForm1.CommandButton4.Caption = Worksheets(“A4”).Range(“A4”).Text
UserForm1.CommandButton5.Caption = Worksheets(“A5”).Range(“A4”).Text
UserForm1.CommandButton6.Caption = Worksheets(“A6”).Range(“A4”).Text
UserForm1.CommandButton7.Caption = Worksheets(“A7”).Range(“A4”).Text
UserForm1.CommandButton8.Caption = Worksheets(“A8”).Range(“A4”).Text
UserForm1.CommandButton9.Caption = Worksheets(“A9”).Range(“A4”).Text
UserForm1.CommandButton10.Caption = Worksheets(“A10”).Range(“A4”).Text
UserForm1.CommandButton11.Caption = Worksheets(“A11”).Range(“A4”).Text
UserForm1.CommandButton12.Caption = Worksheets(“A12”).Range(“A4”).Text
UserForm1.CommandButton13.Caption = Worksheets(“A13”).Range(“A4”).Text
UserForm1.CommandButton14.Caption = Worksheets(“A14”).Range(“A4”).Text
UserForm1.CommandButton15.Caption = Worksheets(“A15”).Range(“A4”).Text
UserForm1.CommandButton16.Caption = Worksheets(“A16”).Range(“A4”).Text
UserForm1.CommandButton17.Caption = Worksheets(“A17”).Range(“A4”).Text
UserForm1.CommandButton18.Caption = Worksheets(“A18”).Range(“A4”).Text
UserForm1.CommandButton19.Caption = Worksheets(“A19”).Range(“A4”).Text
UserForm1.CommandButton20.Caption = Worksheets(“A20”).Range(“A4”).Text
UserForm1.Show
End Sub

VBA解説
  1. 「ユーザーフォーム立ち上げ」というマクロを実行する
  2. コマンドボタン1の名前をシートA1のセルA4の値に書き換える
  3. コマンドボタン2の名前をシートA2のセルA4の値に書き換える
  4. コマンドボタン3の名前をシートA3のセルA4の値に書き換える
  5. —省略—
  6. コマンドボタン20の名前をシートA20のセルA4の値に書き換える
  7. ユーザーフォーム1を表示する
  8. マクロ記録終了
タイムシフト作成名前設定
コマンドボタンの名前が変わりました(クリックで拡大します)

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

スポンサーリンク

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

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

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

シフト希望表2
シンプルな記入箇所(クリックで拡大します)

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

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

タイムシフト表コピー作成
クリックで拡大します

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

シフトコピーボタン

Sub シフトコピー()
For i = 2 To 32
Cells(i, 3).Value = Cells(i, 10).Value
Cells(i, 4).Value = Cells(i, 11).Value
Next i
End Sub

VBA解説
  1. 「シフトコピー」というマクロを実行する
  2. 回数指定した繰り返し処理の変数「i」を宣言し、2~32まで代用する
  3. セルi行目の3列目の値はセルI行目の10列目の値とする
  4. セルi行目の4列目の値はセルI行目の11列目の値とする
  5. 繰り返す
  6. マクロ記録終了

ここで覚えておきたい「指定回数分繰り返し処理をする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」ですね。

従業員番号リンク

=’A1′!A2

名前リンク

=’A1′!A4

全く問題ないですよね。

出勤時間と退勤時間

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

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

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

DGET関数で出勤時間を取得

=DGET(A1!B1:D32,A1!C1,B1:B2)

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

DGET関数で退勤時間を取得

=DGET(A1!:D32,A1!D1,B1:B2)

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

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

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

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

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

とりっぷぼうる
とりっぷぼうる

もし出勤時間が6:30よりも早いか、退勤時間が6:30よりも遅い場合の両方を満たせば、「*」を付け、そうではなく両方を満たさない時は空欄にする

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

IF関数とAND関数

=IF(AND(C4<=6.5/24,D4>=6.5/24,”*”,””)

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

スポンサーリンク

VBA設定

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

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

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

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

前日に日付を変える

Sub 前日()
Range(“B2”) = Range(“B2”) – 1
End Sub

VBA解説
  1. 「前日」というマクロを実行する
  2. セルB2の値はセルB2の値から1を引いた値にする
  3. マクロ記録終了

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

タイムシフト全員
クリックで拡大します
とりっぷぼうる
とりっぷぼうる

ただこれだけでは、お休みのスタッフは空欄になっている状態を含めて従業員全てが表示されるので、見にくいですよね

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

行の非表示を加えた前日ボタン

Sub 前日()
Range(“B2”) = Range(“B2”) – 1
Range(“A3:AQ23”).AdvancedFilter Action:=xlFilterInPlace, CriteriaRange _
:=Range(“C1:C2”), Unique:=False
End Sub

VBA解説
  1. 「全日」というマクロを実行する
  2. セルB2の値はセルB2の値から1を引いた値にする
  3. セルA3~AQ23の範囲でフィルター機能を使ってデータを抽出し、検索条件はセルC1~C2(翌日ボタンの後ろに隠して条件を書いている)とする
  4. 検索条件に一致する行を全て表示する
  5. マクロ記録終了

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

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

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

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

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

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

さて、今日はここまで。

続きは後日追記します。

ボタン1つで作業完了へ
ちょっとの工夫でエクセル作業が飛躍的に早く終わったらいいなあ~と思いませんか? 面倒くさい作業よ!さようなら!

スポンサーリンク


作成事例
参考になりましたらシェア宜しくお願い致します

山頂テラスデッキからの三段紅葉(2019年11月)

フリーパスって助成金つぎ込んでいいの?

コロナ禍のGo To トラベル以外にも助成金だらけでうらやましいね。

安くなったフリーパスで遊びに行きたいけど、給与も減って余裕ないよね。

Excel VBA