便利なツールの話パソコンの話リファレンスInkscapeの使い方FreePlaneの使い方このブログについてブログ特集お問い合わせサイトマップダウンロードinkscape・GIMPを使ったデザインお勉強ブログ。WordPressなどのウェブ関連のお勉強もしています。menuホームブログシフトの作成を簡単にするExcelテンプレートの作り方ブログ2016.04.14シフトの作成を簡単にするExcelテンプレートの作り方Excelなどの表計算ソフトでシフトを作るためのテンプレートを探していたのですが、なかなか希望通りのものに巡り会えなかったので自作してみました。作りたかったものは、4桁で時間を入力すると、勤務時間を自動計算してくれるシフト表です。普通の時間計算だと、時間として認識させるためにはコロン「:」を入れる必要があります。ExcelではVBAなどのマクロを使うと簡単にできるようなのですが、VBAの知識は無かったので、自分のわかる機能で実装する方法を探した結果がこれでした。年月の入力のみで自動で日付や曜日まで計算する方法テンプレートのダウンロードまずは出来上がったテンプレートを公開していますので、こちらからどうぞ。【XLS】シフト作成Excelテンプレート時間を4桁の数字で入力しても、何時間のシフトなのか計算できるようにしました。自動で計算できるようにしました。年月の太枠内に作りたいシフトの年月を入力するシフトの開始日が自動計算される。シフト期間の月、日、曜日が自動計算される。土曜日・日曜日は赤背景が挿入される。コロン(:)を抜いた時間表記で入力する。9時に出勤なら、「出」の欄に「900」と入力。休憩時間が1時間なら「休憩」の欄に「100」と入力。休憩時間が分単位なら、時間に直して入力する。欄を入力するだけでOK。時間の入力を4桁の数字で行う方法Excelの入力では、4桁の数字をそのまま時間として表示したり、計算したりすることはできません。今回のテンプレートで実装した方法は、入力された値を表現を変更して計算するという方法です。このためにTEXT()を使います。"表示フォーマット")の形で使います。実際のデータではこんな感じになります。8:00勤務時間を自動で計算させるためにはD2・D3のセルにはTEXT()を使った計算式を入力します。D2の場合はこのようにします。"00:00")すると、書式がコロンありの時間に書き換えられて、時間としての計算がされるようになります。実はこれだけでは不十分で、時間を表示させる書式設定をする必要が有ります。セルの書式としてExcel上のセルの書式設定で設定すれば、時間の表示をすることができます。合計した時間を表示するときは、「[h]:mm」と記載すると期間の意味で時間を表示することができます。この場合は時刻のところにはフォーマットがないので、ユーザー設定として定義します。年月の入力のみで自動で日付や曜日まで計算する方法このテンプレートで次に力を入れたのが、日付の自動補完機能です。何年の何月のシフトを作るのか?を入力すると、自動で日付と曜日まで入力してくれる機能です。この機能の実装の流れはこんな感じです。年月を手入力する年月からシフト開始日を設定するシフト開始日の通し番号(シリアル値)を取得する。1日ごとにシリアル値が1増えるように計算式を組む。シリアル値から月日・曜日を入力する。はじめに入力値からシフトの開始日を作成します。シフトの開始日は会社ごとに異なるでしょうから、それぞれの状況に合わせて再設定した方がいいと思います。開始日を作成するにはCONCATENATE()で文字列として作成します。・・・)この関数ではセルのデータや文字列をつなげて、文字列として出力します。上のスクリーンショットのA3では、年と月の入力値からシフト開始日を文字列として一旦作成しています。次に、文字列になった年月日をシリアル値という通し番号に変換します。上のスクリーンショットのA5がこれに該当します。シリアル値は整数が日付、小数点以下が時刻を表します。シリアル値に1を足すと基準の日付の1日後ということになります。これを利用して、すべての日付を自動計算しています。シリアル値を求めるにはDATEVALUE()を使います。DATEVALUE()の定義DATEVALUE("日付の文字列")1DATEVALUE("日付の文字列")A5はシフト開始日のシリアル値を求めているので、こう書いています。A5の求め方=DATEVALUE(A5)1=DATEVALUE(A5)これで「2016年5月1日」のシリアル値が「42491」とわかるので、1日ずらすたびに1を足していけばいいことになります。これをその下側(A6以下)に適応しています。ここからシフト表の日付・曜日を自動取得していきます。日付を取得するにはDAY()を使います。DAY(シリアル値)1DAY(シリアル値)そのシリアル値が示している年月日の日だけを表示します。曜日を取得するにはWEEKDAY()を使用します。WEEKDAY(シリアル値)1WEEKDAY(シリアル値)シリアル値が示している日付の曜日を数字で表示します。デフォルトでは「1~7」が「日~土」にそれぞれ対応します。その上で、曜日を表示させるセルの書式設定を変更します。ユーザー設定で「aaa」とすると、曜日を表す1~7の数値が日~土に変換されて表示されます。解説が長くなりましたが、今回のシフト表で使った機能はこんなところです。"いいね!"Facebookで更新情報をお届け。)この記事へのコメントはありません。この記事へのトラックバックはありません。URL返信をキャンセルする。-URL上に表示された文字を入力してください。スポンサードリンクスポンサードリンク記事を検索するプロフィールtipLogInkscapeやGIMP、WordPressなどの記事、ブログ運営についての記事をメインに書いています。2019年6月17日にInkscapeの本を出しました。報酬の受取に苦労してません?初心者でもAmazon・楽天のアフィリエイトで効率的...ブログやサイトの作り方TCDテーマ比較「CUBEY」と「BlogPress」どっちがいい?tipLogのワードプレステーマこういう記事はいかがですか?自作?外注?その前にコレ!ブログのロゴデザインをす...ブログやサイトのロゴを作るときって時間的・金銭的に結構なコストがかかりますよね。PluginClose。