チュートリアル

シフトの作成を簡単にするExcelテンプレートの作り方

Excelなどの表計算ソフトでシフトを作るためのテンプレートを探していたのですが、なかなか希望通りのものに巡り会えなかったので自作してみました。

作りたかったものは、4桁で時間を入力すると、勤務時間を自動計算してくれるシフト表です。

普通の時間計算だと、時間として認識させるためにはコロン「:」を入れる必要があります。

ExcelではVBAなどのマクロを使うと簡単にできるようなのですが、VBAの知識は無かったので、自分のわかる機能で実装する方法を探した結果がこれでした。

テンプレートのダウンロード

まずは出来上がったテンプレートを公開していますので、こちらからどうぞ。

Icon
【XLS】シフト作成Excelテンプレート

時間を4桁の数字で入力しても、何時間のシフトなのか計算できるようにしました。

年月日、曜日は1箇所に入力すれば自動計算されるように設定しました。

休日・総労働時間を自動で計算できるようにしました。

使い方

  1. 年月の太枠内に作りたいシフトの年月を入力する
    1. シフトの開始日が自動計算される。
    2. シフト期間の月、日、曜日が自動計算される。
    3. 土曜日・日曜日は赤背景が挿入される。
  2. 個人のシフトの入力は、出勤時間・退勤時間・休憩時間をコロン(:)を抜いた時間表記で入力する。
    1. 9時に出勤なら、「出」の欄に「900」と入力。
    2. 休憩時間が1時間なら「休憩」の欄に「100」と入力。
    3. 休憩時間が分単位なら、時間に直して入力する。
      例)90分なら「130」 45分なら「45」
  3. 1日の労働時間、週間の労働時間、月間の労働時間、休日は自動で計算されるので、出勤・退勤・休憩の欄を入力するだけでOK。

時間の入力を4桁の数字で行う方法

Excelの入力では、4桁の数字をそのまま時間として表示したり、計算したりすることはできません。

今回のテンプレートで実装した方法は、入力された値を表現を変更して計算するという方法です。

このためにTEXT()を使います。

TEXT(データ値, "表示フォーマット")

の形で使います。

実際のデータではこんな感じになります。

  A B C D
1 出勤 退勤 休憩 勤務時間
2 900 1800 100 8:00
3 1100 2000 100 8:00

勤務時間を自動で計算させるためにはD2・D3のセルにはTEXT()を使った計算式を入力します。

D2の場合はこのようにします。

= TEXT(B2,"00:00") - TEXT(A2,"00:00") - TEXT(C2, "00:00")

すると、書式がコロンありの時間に書き換えられて、時間としての計算がされるようになります。

実はこれだけでは不十分で、時間を表示させる書式設定をする必要が有ります。

セルの書式としてExcel上のセルの書式設定で設定すれば、時間の表示をすることができます。

 

シフト作成Excelテンプレート 2016-04-14 09-35-03-944

シフト作成Excelテンプレート 2016-04-14 09-35-16-823

合計した時間を表示するときは、「[h]:mm」と記載すると期間の意味で時間を表示することができます。

シフト作成Excelテンプレート 2016-04-14 09-39-12-045

この場合は時刻のところにはフォーマットがないので、ユーザー設定として定義します。

年月の入力のみで自動で日付や曜日まで計算する方法

このテンプレートで次に力を入れたのが、日付の自動補完機能です。

何年の何月のシフトを作るのか?を入力すると、自動で日付と曜日まで入力してくれる機能です。

この機能の実装の流れはこんな感じです。

  1. 年月を手入力する
  2. 年月からシフト開始日を設定する
  3. シフト開始日の通し番号(シリアル値)を取得する。
  4. 1日ごとにシリアル値が1増えるように計算式を組む。
  5. シリアル値から月日・曜日を入力する。

シフト作成Excelテンプレートedit 2016-04-14 10-06-34-946

はじめに入力値からシフトの開始日を作成します。

シフトの開始日は会社ごとに異なるでしょうから、それぞれの状況に合わせて再設定した方がいいと思います。

開始日を作成するにはCONCATENATE()で文字列として作成します。

CONCATENATE("文字列1", データ値, "文字列2", ・・・)

この関数ではセルのデータや文字列をつなげて、文字列として出力します。

上のスクリーンショットのA3では、年と月の入力値からシフト開始日を文字列として一旦作成しています。

次に、文字列になった年月日をシリアル値という通し番号に変換します。

上のスクリーンショットのA5がこれに該当します。

シリアル値は整数が日付、小数点以下が時刻を表します。

つまりシリアル値に1を足すと基準の日付の1日後ということになります。

これを利用して、すべての日付を自動計算しています。

シリアル値を求めるにはDATEVALUE()を使います。

DATEVALUE("日付の文字列")

A5はシフト開始日のシリアル値を求めているので、こう書いています。

=DATEVALUE(A5)

これで「2016年5月1日」のシリアル値が「42491」とわかるので、1日ずらすたびに1を足していけばいいことになります。

これをその下側(A6以下)に適応しています。

ここからシフト表の日付・曜日を自動取得していきます。

まず、日付を取得するにはDAY()を使います。

DAY(シリアル値)

そのシリアル値が示している年月日の日だけを表示します。

曜日を取得するにはWEEKDAY()を使用します。

WEEKDAY(シリアル値)

シリアル値が示している日付の曜日を数字で表示します。

デフォルトでは「1~7」が「日~土」にそれぞれ対応します。

その上で、曜日を表示させるセルの書式設定を変更します。

シフト作成Excelテンプレート 2016-04-14 23-23-11-188

ユーザー設定で「aaa」とすると、曜日を表す1~7の数値が日~土に変換されて表示されます。

 

解説が長くなりましたが、今回のシフト表で使った機能はこんなところです。

日付・時刻の計算はなれるまでは少し大変ですが、なれてしまえばそれほど難しい物ではないので、この機会にオリジナルのシフト表を作成してみてはどうでしょうか?

記事が気に入ったら
tipLog を "いいね!"
Facebookで更新情報をお届け。

tipLog

WordPressテーマ

WordPressテーマ「MAG(TCD036)」

スポンサードリンク

関連記事

  1. 素材

    inkscapeの基本機能だけで矢印を描く方法

    今回はよく検索されている、inkscapeで矢印を作るための基本チュー…

  2. チュートリアル

    無料のURL短縮ツール「ClickScope」をサーバーに設置する方法

    コスト削減も兼ねて、無料のURL短縮ツール「ClickScope」をサ…

  3. 素材

    inkscapeのタイルクローンでドットパターンを作る手順

    inkscapeを勉強し始めたとき、ドットパターンをinkscapeで…

  4. 素材

    inkscapeで取り込んだJPGやPNG画像をトリミングをする方法

    inkscapeでフライヤーや店舗のメニューを作りたいと思ったらトリミ…

  5. チュートリアル

    GIMPで秋の行楽・旅行バナーを作りました。

    秋の旅行をイメージしてGIMPでバナーを作成してみました。以下…

  6. チュートリアル

    GIMPのカラーハーフトーンでロゴ作成

    カラーハーフトーンとは、画像をドットで表現することです。GIM…

コメント

  1. この記事へのコメントはありません。

  1. この記事へのトラックバックはありません。

CAPTCHA


スポンサードリンク

ピックアップ記事

おすすめの本

使用している素材

カテゴリー

 
PAGE TOP