チュートリアル

シフトの作成を簡単にする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()を使います。

の形で使います。

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

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

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

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

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

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

セルの書式として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()で文字列として作成します。

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

 

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

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

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

tipLog

WordPressテーマ

WordPressテーマ「MAG(TCD036)」

スポンサードリンク

関連記事

  1. グラフィックデザイン

    「とあるのロゴジェネレーター」を参考にinkscapeでロゴをリメイク

    「ロゴ作成の勉強ってどうやってすればいのー?」と、よく自分でも考え…

  2. グラフィックデザイン

    inkscapeで「冷えてます。」POPをつくってみたのでデータ配布します

    ちょっと仕事で使うことになったので試しにPOP作成をinkscapeで…

  3. チュートリアル

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

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

  4. ウェブコーディング

    PHPで配列と文字列の変換をデータベースに応用する方法

    データベース検索などを利用するときに、$_POSTや$_GETで受け取…

  5. グラフィックデザイン

    GIMPでトリミング画像に影をつけてサクッと立体感を生み出すチュートリアル

    写真から切抜きした画像単体では、立体感がなく物足りないような印象を受け…

  6. ウェブコーディング

    WordPressの記事内でPHPを実行した結果を表示する方法

    WordPressの記事内でPHPを実行できると、記事に表示できるコン…

コメント

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

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

CAPTCHA


スポンサードリンク

ピックアップ記事

スポンサードリンク

カテゴリー

  ロゴマルシェ - LogoMarche   ボタンマルシェ - ButtonMarche
  1. ブログ収益化

    月額100円から使える「ロリポップレンタルサーバー」
  2. グラフィックデザイン

    自作?外注?その前にコレ!ブログのロゴデザインをするなら「Logaster」を使…
  3. グラフィックデザイン

    レイアウトデザインに最適な無料ソフト「inkscape」(≒Illustrato…
  4. グラフィックデザイン

    フォトレタッチ・グラフィックデザインの無料ソフト「GIMP」(≒Photosho…
  5. ブログ収益化

    脱初心者しませんか?Amazon・楽天のアフィリエイトを上手に活用するための予備…
PAGE TOP