この場合、エクセルのIF(?)関数を使用・・・？

質問

こんにちは、頭がパンクしそうだったので質問させて下さい。
要は生産管理です。

作業合計時間が分表示のため、分の合計から時間表示にします。
(数字の書式は標準です。)
→例：480分を時間表時に・・・
INT(480/60)&"時間"&MOD(480,60)&"分"
で時間表時(8時間0分)にしています。
この時間は休憩時間抜きの純粋作業時間です。

そこで、１日４８０分以上５４０分未満(純粋作業時間)を超えた場合は１時間残業と表示＋終業予定時刻の表示。
５４０分以上５９０分未満の場合は２時間残業と表示＋終業予定時刻の表示をしたいと思っています。
うちの会社は休憩が合計90分(８時から１７時までで)あります。
終業予定時刻は休憩込みでないと、当たり前ですが正確には出ません。

しかし、１時間残業では１７時からの休憩なしで１８時まで、２時間残業では１０分の休憩が１７時からあり、１９時までです。
ここら辺はifを使用したら・・・？と考えていますが。

これを何とか、ぱっと出したいのですが、良いアイデアありませんでしょうか？
頻繁に作業内容が入れ替わり、その都度頭で計算しながらだと非常に効率が悪く思っています。
しかし、単純に頭で計算しながら手入力していくしかないのでしょうか。

よろしくお願いします。

回答No.7

banker_U · ベストアンサー

#2,5です。
だんだん見えてきました。ただ逆に疑問も出てきました(後述)。
とりあえず、休憩は１９時以降まで働く場合しかつかない。ということで、さっきの表を作り直すと、
始業 残業 終業 休憩 作業時間 分単位
8:00 0:00 17:00 0:00 7:30 450
8:00 0:30 17:30 0:00 8:00 480
8:00 1:00 18:00 0:00 8:30 510
8:00 1:30 18:30 0:00 9:00 540
8:00 2:00 19:00 0:10 9:20 560
8:00 2:30 19:30 0:10 9:50 590
8:00 3:00 20:00 0:10 10:20 620
8:00 3:30 20:30 0:10 10:50 650
8:00 4:00 21:00 0:20 11:10 670

となります(並び順はさっきと変えてあります)。
作業時間が１分単位で出てくるのなら、これを保管するか、切り上げの仕組みをつくるかどっちかだなあと考えたのですが、

ここで疑問が生じました。
作業時間565分の時は、どうするのでしょうか？
１．仕事としては、休憩なしで一気に働けば、18:55まででおわります。
　(計算省略しますが要は115分の残業で可能と言うこと)
　２時間残業していないので、ルール上もこれはこれで正解と思います。
２．ところが、19:05まで仕事をするとしたら、休憩時間が10分入るので、
　　やはり作業時間は565分となります。
この場合は、どちらにするのでしょうか？これは決めの問題ですので、決めてください。

さらにいうと、残業は最大４時間で、ということが#1の補足に書いてありましたが、
ここで675分の作業時間を考えます。
これは450分(定時内分)＋110分(7時まで休憩控除後)＋115分と分解でき、要は20:55には作業終了できます。
ところが、最大4時間残業の場合、休憩が20分入ることから、670分しか作業時間とならず、これなら、20:50まで一気に働かせても、同じ作業時間となるので、現実には、4時間残業はありえないと言うことになります。

この辺結局決めておく必要があると言うことですが、頭で考えていると、また混乱するので、私が作った上記の表を、５分単位で作り直してみると、(私が言っている問題点の意味も含めて)頭が整理できると思います。

作成の際は、くれぐれも、終業時間から逆算していくことをお勧めします。

お礼

お返事遅れました。大変助かりました。
なんとか出来そうです。
終業時間から逆算していく事で問題回避出来そうです。
ありがとうございました！！！

回答No.6

noname#60992

No.1です。　皆様回答済みですが以下のように整理して考えてみると分かりやすいかと思います。

A2 に終業修了時刻を入れたとして
B2 に労働時間を30分単位で切り捨てた時間（分）をいれ
C2 に残業時間をいれる（昼休みの一時間は拘束時間と考えていないようなので,残業時間は労働時間-9時間と思われます。)　
D2 に休憩時間をいれる（これは、No5の方の他テーブルをVlookupで参照しても良いですし、No3のようなIF文を使ってもかまいません。　また、VBAの知識があるのならば、Select文を使用してもかまいません。）

仮定している事項ですが、一日の作業時間は８時間であるということ。　休憩時間のうち一時間は拘束時間外のであるが、残りの休憩時間は拘束時間である（作業時間に含まれる）ような感じなのですが、ここのところがはっきりすれば、簡単な問題だと思われます。

excelを少しでも使えるようになりたいのでしたら、このように作ってみたらよいと思います。

お礼

ご助言ありがとうございました。
まだまだExcelの勉強不足を実感しております。
しかし、大変助かりました。
ありがとうございました。

回答No.5

banker_U

#2です。

まだ疑問点ありますが、みなしで回答作ってみました。
私の想定している前提が正しければそう難しい問題でもありません。
要は、作業時間をスタートに作っていくから難しいのです。残業時間をスタートに考えてみましょう。

残業時間が３０分単位と言うことは、
　なし,30分,1時間30分,・・・,4時間
の９種類にしかなりませんよね。

で、終業予定時刻も、
　17:00,17:30,・・・,21:00
しか取りえない。

休憩時間は、18:30終業と20:30終業の場合が不明ですが、
それぞれ10分,20分付くとみなします(違っていたら修正してください)。

ここまで決まれば、作業時間は、自動的に決まってきます。
一覧にして、お望みの順番に並び替えると、

作業時間(分) 作業時間 残業時間 終業
450 7時間30分 0時間0分 17時00分
480 8時間0分 0時間30分 17時30分
510 8時間30分 1時間0分 18時00分
530 8時間50分 1時間30分 18時30分
560 9時間20分 2時間0分 19時00分
590 9時間50分 2時間30分 19時30分
610 10時間10分 3時間0分 20時00分
640 10時間40分 3時間30分 20時30分
670 11時間10分 4時間0分 21時00分

この表をEXCELに取り込んで(コピー－貼付でできます)、
VlOOKUPで参照するようにすればよいかと・・・

そもそも違っていたら、また聞いてください。
OKだがVLOOKUPの使い方がわからなければ、それもまた聞いてください。

補足

こんばんは、ご丁寧にありがとうございます。
30分単位の残業の場合も休憩は付きません。

Vlookupですと「作業が４７６分で終わる場合に、表のA列に羅列されている作業時間(分)の近似値を探して残業時間及び就業時間を表示する方法」でbanker_Uさんのお答えと合致するでしょうか？

回答No.4

noname#60992

ＮＯ１です。
補足要求ばかりですみません。

残業時間より作業終了時間を求めたいのでしょうか？
作業終了時間より残業時間を求めたいのでしょうか？
どちらでしょうか？

補足

どうも、こちらこそ説明不足ばかりで申し訳ありません。

どちらかと言えば作業終了時刻を優先したいと思います。
よろしくお願いします。

回答No.3

mshr1962

A2に作業合計時間があるとして
=IF(A2<=480,TEXT(A2/1440,"h時間m分"),TEXT(CEILING(((A2>=540)*10+A2)/60-7.99,1),"0時間残業")&TEXT(((A2>=540)*10+A2)/1440+3/8," hh:mm終了予定"))

お礼

どうも、ありがとうございます。
参考にさせていただきます。

回答No.2

banker_U

まあまあ。落ち着いてください。

ちょっとおっしゃりたいことが理解できません。
・８時始業で１７時が定時終業のように見えるのですが、
　それで９０分休憩があるとすると、作業時間は
　７時間３０分(＝４５０分)になりませんか？

ここから質問
・始業時間は一定(８時？)と考えていいですか？
・要は求めている完成形は
　　分単位の作業時間　時間単位の作業時間　残業時間　終業予定時刻
　ってことでいいですか？
・残業時間は時間単位で分まで表示？

ここからアドバイス
・上記の疑問が解決されれば、計算式も提示できないでもないですが、現時点では求めるストレートな回答は出せそうにありません。
・今の時点でできるアドバイス、
　一回０分から６００分ぐらいまで必要な時間分の
　表を作ってしまって、あとはそれをVlookup関数で
　参照に行く方法はどうですか？

補足

すみません、どうも頭が混乱しておりまして説明がうまくいかずに申し訳ありません。
何とか自己解決まで持っていけそうですが、色々な方法を知りたいのでよろしくお願いします。

会社には８時から１７時まで居ます。
拘束時間が４８０分(８時間)で日報を出すようになっているのでつい。

スケジュールと致しましては
８時０５分　始業
１０時００から１０時１０分　休憩
１２時００分から１３時０５分　昼休
１５時００分から１５時１０分　休憩
１７時００分から０.５(３０分)区切りで残業あり。
以後２時間に１回１０分の休憩。
正確には８時からの５分と１３時からの５分は作業時間ですが、朝礼等があるので、作業時間には含めないようにしています。

１７時から１８時までの１時間残業の場合、１７時からの１０分休憩は無し。１７時から１９時までの場合は１０分休憩が１７時から有る。
以後１９時から２１時は１９時から１０分休憩がある。

分単位の作業時間
時間単位の作業時間
残業時間(３０分単位)
終業予定時間
の４つが表示出来れば助かります。

残業は３０分単位で表示出来れば助かります。

まだ生産管理に回って２日もたっていない上に、派遣社員が何十人も入ってきて、作業組み替え等で頭がパンク寸前です。
周りにエクセルが出来る人が居ないので、よろしくお願いします。

今の自己解決策としては
分単位の合計をMODとINTで時間表示し、
IF(B1>479,"30分残業",IF(B1>509,"１時間残業"))...続く
と、今確認なしで考えています。
終業予定時間に関しては、今だ頭をひねっております・・・。

回答No.1

noname#60992

＞２時間残業では１０分の休憩が１７時からあり、１９時までです。

休憩時間は作業時間に含まれると考えてよろしいでしょうか？

２時間以上の残業の可能性はありますか？　ある場合は休憩時間はどうなりますか？

補足

回答頂きありがとうございます。
上記にも記載致しましたが、スケジュールと致しましては
８時０５分　始業
１０時００から１０時１０分　休憩
１２時００分から１３時０５分　昼休
１５時００分から１５時１０分　休憩
１７時００分から０.５(３０分)区切りで残業あり。
以後２時間に１回１０分の休憩。
となっております。

一応拘束時間が４８０分(８時間)で昼の休憩が１時間ある事を考えると、休憩時間は作業時間に含まれていると考えていいと思います。

２時間以上の残業はあります。
最長４時間までで、２時間につき１０分の休憩が与えられます。