350フリーテンプレート

2013年11月30日

はじめに

作成済みのシフト表をさまざまに加工します。

多数の関数（VLOOKUP,HLOOKUP,INDEX,OFFSET,SMALL,LARGE,IF,SUM,SUMIF,COUNTIFなど）や条件付書式、入力規則、表示形式を駆使してシフト表から目的別の表を作成。
完成例と数式のシートを用意していますので、ご自由にご覧下さい。
また、Webページではセルのアドレスなどが判りにくい場合は、Excelブックをダウンロードして御使用下さい。

時間帯チャートの表示
 シフト表の勤務を月間カレンダー形式で表示
 シフト表の各種集計
 シフト別要員一覧
 シフト別月間要員一覧
 シフト別月間メンバー表
 シフト表の勤務均等化

シフト表自動作成

ソルバーによるシフト表自動作成　　　2014/10/14 up

ここでは、エクセルのシミュレーションツールの一つ「ソルバー」を用いて
シフト表の自動作成を試みてみます。

注：　本ツールはＥＸＣＥＬ２０１０以上で動作します。
　　　　（エボリューショナリー機能を使用するため、２００７以下は不可）

作成するシフト表の仕様　－　最終的に作成したいシフト表

ソルバーとは　　　「ソルバーアドイン」の組み込み　　ソルバーの使い方

　１．単一単位種別の自動割り当て
　　　　　　　　　　　－　日々の必要人数を個人単位で割り振る
　２．複数勤務種別の自動割り当て
　　　　　　　　　　　－　日勤や夜勤のように複数の勤務種別を均等割振
　３．連続勤務不可条件
　　　　　　　　　　　－　連続してはいけない勤務の組み合わせを指定
　４．不可条件　　－　各人が出来ない勤務を日単位で指定する
　５．均等化－ソルバー　－　ソルバーを使用して勤務日数を均等化
　６．均等化－均等再配置アドイン　－　別のアドインを使用して勤務日数を均等化
　７．不可勤務を複数指定　－　早出および遅出が不可のように複数の不可勤務を指定
　８．勤務指定方法の改良　－　数値ではなく、リストから勤務名として指定できるようにする　

以下、マクロ（VBA）使用

　９．自動化　－　入力から実行までをマクロ等を利用してなるべく自動化
１０．不可勤務入力改良　－　リストから複数の組み合わせで入力できるようにする
１１．人数の可変化・全自動実行　－　要員氏名を任意に追加削除可能とする
１２．予約勤務　－　前もって、指定日に希望勤務を予約しておく
１３．予約勤務入力チェック　－　入力チェック数式の説明
１４．前旬を考慮した連続不可勤務　－　前旬最終２日を連続不可勤務判定の対象とする　予定
１５．過去勤務状況を反映　－　過去の勤務日数を考慮した均等再配置　予定

「均等再配置」アドイン　－　ソースコード　

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

2013年09月30日

シフト表自動配置

シフトパターンの自動配置　－　人件費を最小に抑える従業員の勤務スケジュール

これは、エクセルに付属するソルバー応用例の一つです。

　オリジナルのサンプルファイルは下記
　　（３２ビット版Excel2010の場合－斜体部分は環境・バージョンにより異なります）
　　C:¥Program Files¥Microsoft Office¥Office14¥SAMPLES¥SOLVSAMP.XLS

ソルバーを使って問題を定義し、解決する　－　ソルバーのヘルプ

日々の必要人数がセルＦ１７～Ｌ１７のように決まっている場合、
Ａ～Ｇのシフトパターンの人を、最低何人必要（Ｄ７～Ｄ１３）かを計算します。

また、必要人数を揃えるだけでなく、人件費を最小にするようにします。

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

シフト表時間帯チャート表示

一日の勤務時間を、下図のような３０分単位の時間帯チャートで表す仕掛けを作ります。

仕様

勤務種別表
　勤務名、略称、始業時刻、終業時刻、休憩開始時刻、休憩終了時刻の表をあらかじめ用意。

シフト表
　期間中のシフトを、あらかじめ用意しておきます。
　このシフト表は、ソルバーによるシフト表自動作成の出力と連動させておくのが理想です。

１．日にちを変更すると、「勤務詳細表」に、選択した日にちに対応した、当日の勤務、および、前日の勤務が表示されるようにします。

　　日にちの選択は、
　　　　直接数値の入力
　　　　プルダウンメニューからの選択　－　入力規則
　　　　スピンボタンからの選択　－　フォーム

２．期間内シフト表に「勤務詳細表」と対応したそれぞれの人の勤務時間帯に色を付けます。このとき、

　　　　勤務中は「アクア」色、休憩中は「黄」色
　　　　前日勤務から、日をまたいで行われる勤務（夜勤）に対しても、勤務中と休憩中の色を設定します。

その他

　時間表の、時、分、および、時刻の開始列の色については、
　直接、数値を入力したり色を設定するのではなく、数式や条件付き書式ですべてのセルの設定値が同じになるのが望ましいです！

2012年10月24日

シフト表の連続勤務解析

シフト表の連続勤務状況を解析　　　2012/10/24

仕様

　シフト表から勤務の連続状況を調べます。

　１．指定勤務が指定日数連続している場合には、オレンジでセルを塗り潰します。

　　　本例では、回数を３　勤務を▲　とすると、下図のように　連続している勤務の
　　　セル色がオレンジになる

　２．回数は１～５まで指定可

　３．勤務は入力規則から選択し、ワイルドカード「*」も指定可とする。

　使用機能：条件付書式、COUNTIF、OFFSET、AND,OR　の各関数

テンプレートのダウンロード（Zip形式）

完成イメージ


数式解説シート

2008年09月29日

シフト別月間要員一覧

シフト別月間要員一覧　　2008/09/29

作成したシフト表のシフト別の月間要員一覧表を表示する

仕様
　・作成済みのシフト表で、シフト（勤務略称）を選択すると、シフト別の月間要員一覧表が自動作成される。
　　　　一覧表はNoの若い順に上詰めで表示。
　純粋にＥｘｃｅｌの標準関数と機能だけで作成。VB不使用

　使用関数：IF,ISERROR,OFFSET,SMALL,INDEX,VLOOKUP
　　　ISERROR：式がエラーであるかを調べる
　　　OFFSET：基準位置から相対位置（行数、列数）のセルの値を取り出す
　　　SMALL：指定順位の値を取り出す（昇順時）
　　　INDEX：範囲無いのデータを行・列を指定して取り出す
　使用機能：条件付書式、フォーム、名前

テンプレート詳細

テンプレートのダウンロード　（テンプレートはZip形式）

2008年09月28日

シフト別要員一覧表

シフト別要員一覧　　2008/09/28

作成したシフト表の日別のシフト要員一覧を表示する

仕様
　・作成済みのシフト表で、日にちを選択すると、その日の各シフトごとの要員一覧表が自動作成される。
　　　　一覧表はNoの若い順に上詰めで表示。
　・日にちはスクロールバーで選択。
　・日にちを選択すると、シフト表の該当部分がハイライト表示される。
　純粋にＥｘｃｅｌの標準関数と機能だけで作成。VB不使用

　使用関数：IF,ISERROR,OFFSET,SMALL,VLOOKUP
　　　ISERROR：式がエラーであるかを調べる
　　　OFFSET：基準位置から相対位置（行数、列数）のセルの値を取り出す
　　　SMALL：指定順位の値を取り出す（昇順時）
　使用機能：条件付書式、フォーム

テンプレート詳細

テンプレートのダウンロード　（テンプレートはZip形式）

2008年02月16日

シフト別月間メンバー表

シフト別月間メンバー表　　　2007/11/27

作成したシフト表のシフト別の月間メンバー表を表示する

仕様
　・作成済みのシフト表で、横軸を日、縦軸をシフト種別とし、そのメンバー一覧を作成する。

　ここでは、ＭＡＴＣＨ関数に“ｎ番目にマッチ”という機能を追加したユーザー定義関数 MyMatch 関数を作成して使用する。

　関数の仕様

　　ＭｙＭａｔｃ（検索値 , 範囲 , ｎ番目）

　使用関数：ＩＦ,,ＯＦＦＳＥＴ,SMALL,INDEX,VLOOKUP,COUNTIF,OR,AND
　　　ＭＡＸ：最大値
　　　OFFSET：基準位置から相対位置（行数、列数）のセルの値を取り出す
　　　SMALL：指定順位の値を取り出す（昇順時）
　　　INDEX：範囲無いのデータを行・列を指定して取り出す
　　　ＣＯＵＮＴＩＦ：条件にあった項目の個数
　　　ＯＲ，ＡＮＤ：論理和、論理積

　使用機能：条件付書式

テンプレートのダウンロード（Zip形式）

完成イメージ


数式解説シート

2007年12月10日

シフト表の勤務均等化

Ver 1.00　2007/12/09

適当に割り振られた月間勤務表を、なるべく各勤務日数が平等になるように均等化します。
計算式では不可能なので、ここでは、選択範囲を均等化するアドイン「均等再配置」を使用。
（ソルバーでも工夫すれば可能でしょうが、計算時間が掛かり過ぎる）

操作手順
１．日別に必要な各勤務数を決め、それに従い、適当に勤務を割り振っておく。
　　（テンプレート「sample.xls」に設定済み）右図「再配置前」参照
２．アドインの登録
　　「ツール」メニューの「アドイン」から「均等再配置.xla」を登録。右図「手順」参照
３．アドイン実行。
　　「ツール」メニューの最下段に登録された「均等再配置」をクリック
　　同、「手順」参照。
シフト表の均等再配置が開始される。

再配置された状態は右図「再配置後」を参照。

アドインの仕様

　名前：均等再配置.xlal
　動作：選択範囲において、列方向のそれぞれの要素の個数が、行方向に対して均等になるように再配置
　この時、行方向の要素数は変更しない。
　sample.xlsでは、ひと月の勤務がそれぞれ各人均等になるように再配置。右図「仕様」参照
　適用範囲：Excel2000,2002,2003にて動作確認

テンプレート（sample.zip）のダウンロード（Zip圧縮形）