勤務表の作成について

質問

勤務表の作成について

始業時間・終業時間・休憩時間を入力すれば、実働時間・定時時間・残業時間・深夜時間・深夜残業が
自動的に出るような表を作成しようとしています。
作成はしてみたのですが、どうも上手くいきません。

始業時間・・・手入力
終業時間・・・手入力
休憩時間・・・手入力
実働時間・・・終業-始業-休憩
定時時間・・・5:00~22:00の間の業務時間
残業時間・・・実働時間が8時間を超えていた場合の5:00~22:00の間の業務時間
深夜時間・・・5:00~22:00の間以外の業務時間
深夜残業・・・実働時間が8時間を超えていた場合の5:00~22:00の間以外の業務時間

今は以下のような(間違っている)計算式を入れています。
D8=曜日(休日には休と表示)
D10=始業時間
D11=終業時間
D12=休憩時間
実働時間(D13)=IF(D10="","",D11-D10-D12)
定時時間(D14)=IF(D8="","",IF(AND(D8<>"休",D10=""),"休",IF(OR(D10="",D8="休"),"",IF(D13<TIME(8,0,0),D13,TIME(8,0,0)))))
残業時間(D15)=IF(D10="","",IF(D14="","",IF(D13-D14=0,0,IF(D11>TIME(22,0,0),IF(TIME(22,0,0)-D10-D12-D14<0,0,TIME(22,0,0)-D10-D12-D14),D11-D10-D12-D14))))
深夜時間)D17)=IF(D10="","",IF(AND(D11>TIME(22,0,0),D11<TIME(5,0,0)),D11-D18-TIME(22,0,0),""))
深夜残業==IF(D10="","",IF(D13=D14,0,D13-D14-D15))

どのような式を入れれば良いかご教授お願いします。

回答No.2

new_3710 · ベストアンサー

まず数式を考える前に、勤務入力に関する定義をしっかりしましょう。(^_^;)
おそらく下記のような定義が最も近いと思います。

【定義】（24時間表示）
●1日の勤務時刻の範囲
＝5時～翌朝5時→[ 5:00～29:00 ]とする。

●通常勤務時刻
＝5時～22時→[ 5:00～22:00 ]とする。

●深夜勤務時刻
＝22時～翌朝5時→[ 22:00～29:00 ]とする。

●通常休憩時間
＝通常勤務時刻内に取得した勤務時間に含まない休憩時間(食事・私用外出など)

●深夜休憩時間
＝深夜勤務時刻内に取得した勤務時間に含まない休憩時間(食事・私用外出など)

●定時時間
＝1日の勤務時間は8時間を定時勤務時間とする。
また、定時時間は、通常勤務時刻内の「通常定時時間」と深夜勤務時刻内の「深夜定時時間」とに分類する。

●残業時間
＝8時間を超える勤務時間は残業時間とする。
また、残業時間は、通常勤務時刻内の「通常残業時間」と深夜勤務時刻内の「深夜残業時間」とに分類する。

以上の定義から、勤務入力に必要な項目は少なくとも下記のようになると思います。

[ 始業時刻 ]※
[ 終業時刻 ]※
[ 通常定時時間 ]
[ 通常休憩時間 ]※
[ 通常残業時間 ]
[ 深夜定時時間 ]
[ 深夜休憩時間 ]※
[ 深夜残業時間 ]

以上の項目の内、※印の項目が手入力になります。

あと、時刻の区切りを表した表を同じシート内に作成し、計算式にも、その表のパラメーターを参照して計算させると便利だと思います。
将来、勤務時刻の変更や定時時間の変更などがあっても、数式を変えることなく、別表の修正で対応可能になるからです。
例えばこんな感じに。
↓
1日の始め時刻　5:00
深夜区切時刻　22:00
1日の終り時刻　29:00
1日の定時時間　8:00

他にも、一覧にしておいた方が良いパラメーターがあれば追加しても良いでしょう。

以上のことを考え直して数式を構築していくと良いのではないでしょうか？
一言アドバイスするとしたら、時間の計算が複雑になるので、1つの式の中に全部入れるのではなくて、実際に管理する表の下に、いろいろな判断をするのに必要な計算を別にさせておいて、それを参照しながら数式を構築する方がやりやすいと思います。

ご参考に！<(_ _)>

お礼

返答ありがとうございます。
確かに他に休憩時間を取るタイミングという物が必要のようでした。
また、一つのセルに全ての計算式を入れてしまうとIF分のネストが超えてしまうので
別のセルに一度別な計算をさせておいて、そこから持ってくるやり方にしようと思います。
(そうすればエラーが出ても、どちらでエラーが出ているか分かりやすくもなるので)
また数式を作り直してみます。

回答No.4

有田 賢治（@ARIKEN43）

reasさん 今日は！
まだ上手くプログラムが出来ませんか？
プログラムはまず真似をする事から始めるべきです。作成のノウハウを覚えて下さい。
補足します。
■VBAを使わずExcelで勤務表を作成する(Excel2000)
http://members.at.infoseek.co.jp/kenchan_h/index22.html
概要：そこで今回、皆様にも簡単に勤務表が作れる方法をお伝えしたいと思います。
”簡単に”と言うからには、VBAを使わずにExcelで勤務表を作成することを念頭に考えております。
どこまでご期待に沿えるか分かりませんが、このページが皆様に少しでも役立てば幸いかと存じます。
■続・VBAを使わずExcelで勤務表を作成する
http://members.at.infoseek.co.jp/kenchan_h/index23.html
■続々・VBAを使わずにExcelで勤務表を作成する
http://members.at.infoseek.co.jp/kenchan_h/index31.html
■勤務表作成フリーソフト「ローティションメーカー」
http://www.rotation.gamaguti.jp/index.htm
概要：ダウンロード・インストール↓
このソフトはインストール作業は必要ありません。
プログラム本体と、見本データを自己解凍形式のファイルで配布しますので、適当なところに解凍後、プログラム本体　RotationMaker.exe　を実行してください。
入手法：このサイトより可能です。

お礼

返答ありがとうございます。
各サイトを参考にさせて頂きます。

回答No.3

有田 賢治（@ARIKEN43）

reasさん 今晩は！
NO1です。補足です。
■Excel(エクセル)基本講座：Excel(エクセル)関数一覧表（Excel2002/2003）
http://www.eurus.dti.ne.jp/~yoneyama/Excel/kansu/itiran.html
概要：1.【種類別エクセル関数一覧表】↓作成済みエクセル関数の数：128個
☆合計・平均の関数 カウントの関数 四捨五入など 最大最小など
☆日付の関数 時刻の関数 文字列の操作1 文字列の操作2
☆検索/行列の関数 IF関数＆IS関数 三角関数・数学 データベース関数 財務関数 エンジニアリング関数
2.【アルファベット順エクセル関数一覧表】
3.【機能別エクセル関数一覧表】
☆日付/時刻の関数↓ 　
現在の日付/時刻 TODAY()　NOW()
年月日・時分秒を取り出す YEAR　MONTH　DAY　HOUR　MINUTE　SECOND
シリアル値を返す DATE　DATEVALUE　TIME　TIMEVALUE
文字列を返す DATESTRING　TEXT
曜日 WEEKDAY
稼働日数 WORKDAY　NETWORKDAYS
期間の年・月・日数 DATEDIF
月末・何ヶ月後・数日後 EOMONTH　EDATE
関数をクリックすると関数の使い方が表示されます。
■ぱんだの勤務時間計算サポート (Ver3.000) WinArrow作 (フリーウェア）
http://blog.moug.net/archives/cat_774699.html?p=5
解説：本ソフトは、勤務形態別のタイムテーブルを用いて勤務時間を求めるツールです。
《詳細》勤務時間計算は、労働基準法で定める「時給単価（基本、時間外、休出、深夜）の規定」に
対応した労働時間（除く、休憩時間）を求めることにあります。

参考にして下さい。

回答No.1

有田 賢治（@ARIKEN43）

reasさん 今日は！
＞始業時間・終業時間・休憩時間を入力すれば、実働時間・定時時間・残業時間・深夜時間・深夜残業が
＞自動的に出るような表を作成しようとしています。作成はしてみたのですが、どうも上手くいきません。
勤務表のようなプログラムは、参考例を見て作成する方が良いでしょう。
■Excel(エクセル)実用編：時給計算書の作成例（Excel2002/2003）
http://www.eurus.dti.ne.jp/~yoneyama/Excel/jituyou/jikyu.htm
概要：完成例がこのサイトからサンプルファイル(jikyu-01.zip　約20KB)のダウンロード出来ます。
■Excel(エクセル)講座 Q&A(Tips)：時刻・時間関連（Excel2002/2003）
http://www.eurus.dti.ne.jp/~yoneyama/Excel/ex-q-a/q_jikoku.html
概要：時刻の計算と時刻のシリアル値および勤務時間の計算が図解されて居ますので参考にして下さい。　
■Excel2002/2003で勤務表を作成する
http://www.h3.dion.ne.jp/~sakatsu/WorkingTimeCalc.htm
概要：詳細が細かく記述されていますが、図解はされていません。

両サイトも原則としてマクロは使用していませんので、判り易いと思います。
この様にプログラムはどの職業の、誰に適用するのかが判らないのでヒントだけしか回答出来ませんので
参考にして下さい。