- ベストアンサー
エクセルで勤怠管理(残業管理)をするための関数と式の作成方法
- エクセルで勤怠管理(残業管理)をするために、関数や式を使用する方法について教えてください。
- 具体的には、出社時刻、退社時刻、普通残業、深夜残業の時間を自動的に計算する方法を知りたいです。
- 質問者は、複雑な計算が必要なため、適切な関数や式がわからず困っています。
- みんなの回答 （3）
- 専門家の回答
質問者が選んだベストアンサー
その他の回答 (2)
- mshr1962
- ベストアンサー率39% (7418/18948)
No1です。検証しました。下記の式に書き換えてください。 C2=ROUND((MAX(MIN(B2,22/24),73/96)-MIN(MAX(A2,73/96),22/24)+MAX(B2-29/24,0)-1/99999)*48,0)/48 D2=ROUND((MAX(MIN(B2,29/24),91/96)-MIN(MAX(A2,91/96),29/24)-1/99999)*48,0)/48 ※1. 時間にする時の微小誤差補正のため1/99999(0.9秒)減らしています。 ※2. 29時以降の普通残業を30分単位にするためROUNDの中に移動しました。
お礼
mshr1962様 確認、お礼が遅くなってしまい申し訳ありませんでした！ す、す、素晴らしすぎます！！ ありがたく使わせてもらい、 あとは自分なりにも勉強してみます。 関数は奥が深い。。。 本当にありがとうございました（＾◇＾）
補足
お礼をしたのに再び質問してしまい申し訳ありません。 C2/D2の式をコピーさせていただきました。 そして下方向へもコピーさせていただいたのですが 出社時間、退社時間が空白でも 残業2ヶ所はコピーした時間が出てしまいます。 A列B列が空白の時は、C列D列も空白とかできますか？ ユーザー定義でh:mm;; 等を入れてみてもだめでした((+_+)) 他にも思い当たるものはいろいろやってみましたが分かりませんでした。 何度も質問申し訳ありません。 宜しくお願いいたします。
- mshr1962
- ベストアンサー率39% (7418/18948)
普通残業 C2=ROUND((MAX(MIN(B2,22/24),73/96)-MIN(MAX(A2,73/96),22/24))*48,0)/48+MAX(B2-29/24,0) 深夜残業 D2=ROUND((MAX(MIN(B2,29/24),91/96)-MIN(MAX(A2,91/96),29/24))*48,0)/48 ※上記は15分単位で四捨五入しています。 00:00～00:14は00:00 00:30～00:44は00:30 00:45～00:59は01:00
補足
mshr1962様 早速の回答ありがとうございました！！ ただし22:00以降入力すると どうしても普通残業に4:00となってしまいます。 私の説明が下手で大変申し訳ありませんm（_ _）m ※画像で早見リストを添付しました。 見ずらいと思いますがこの枠内があてはまる残業時間になります。 やってるうちにわけわかんなくなってしましました。 このリストを使った方が早いのか、 はたまた他にやり方（式）があればご教授願いますm（_ _）m
関連するQ&A
- 関数で（Part2）
エクセルで勤怠管理(残業管理)をしたいと思ってます。 前回質問し、素晴らしい回答をいただいたのですが、 再度分からないことが出てきてしまい、教えていただきたいのです。 ▼前回質問▼ ※定時 09:00～18:00 <休憩18:00～18:15無給> ※普通残業 18:15～22:00(30分単位0:30加算) 休憩22:15～22:45(無給3:30のまま) ※21:45～29:00までは普通残業の+3:30を表示したい ※29:30～32:30(30分単位0:30加算) ※深夜残業 22:45～29:00(30分単位0:30加算) ※29:00～32:30は6:30のまま表示をしたい 例) A B C D E 出社 退社 <普通残業> <深夜残業> <残業合計> ------------------------------------------------------------------------- 2 9:00 18:44 0:30 0:00 0:30 3 9:00 22:45 3:30 0:30 4:00 4 9:00 24:45 3:30 2:30 6:00 5 9:00 30:30 5:00 6:30 11:30 6 9:00 18:12 0:00 0:00 0:00 上記の用な感じのフォームを考えてます。 C3・D3を自動的に出すのにどのような式を入れたらよいのかわかりません(泣) (1) C3の普通残業分はどのような式を入れたら良いのか ※18:00～18:15までは0:00で、18:15～22:15までは30分単位 ※休憩時間含む22:15～22:45まで3:30表示 ※29:30～32:30は30分単位で普通残業へ (2) 同じくD3の深夜残業分の式をどのようにしたら良いのか ※～22:45までは0:00か空白、22:45～29:00までは30分単位、29:00～32:30は6:30表示にしたい。 >C2=IF(OR(A2="",B2=""),"",ROUND((MAX(MIN(B2,22/24),73/96)-MIN(MAX(A2,73/96),22/24)+MAX(B2-29/24,0)-1/99999)*48,0)/48) D2=IF(OR(A2="",B2=""),"",ROUND((MAX(MIN(B2,29/24),91/96)-MIN(MAX(A2,91/96),29/24)-1/99999)*48,0)/48) と素晴らしい回答をいただいたのですが。。。 深夜残業で 23:15は1:00 23:45は1:30・・・と本来なら続くのですが、 23:15では0:30 23:45では1:00・・・と30分足りなくなってしまいます。 どう直したら良いのでしょうか。。。m（_ _）m 何卒宜しくお願いいたします。
- ベストアンサー
- オフィス系ソフト
- エクセルでタイムカード計算
初心者です。エクセルで次のような計算をしたいのですが 基本の 始業時間19：00 終業時間1：10 で 実働 5：30 です 21：00～21：20 まで20分休憩を引きます 23：10～23：30 まで20分休憩を引きます 19：00より前は早出になります 1：20から残業ですが2Hに10分ずつ休憩が入ります 残業は30分単位です それで第2の設定として A 19：00～21：59 普通時間（早出含む） B 22：00～1：10 普通残業 C 1：20～ 深夜残業 作成したい表は 始業時間と 終業時間をいれると A（定時だと2：40） B（定時だと2：50） C と3段階で表示されるものです 何とかできないでしょうか？ フリーソフトも色々みたのですが？ 困っています 宜しくお願いいたします
- ベストアンサー
- その他MS Office製品
- Ｅｘｃｅｌの時間計算について
Ｅｘｃｅｌでの時間計算式についてお尋ねします。 Ｃ３に出勤時間、Ｃ４に退社時間があり、Ｄ３に早出時間、Ｄ４に残業時間を表示させたいのですが、残業時間は、色々と調べ完成したのですが、早出時間の表示の仕方が分かりません。 基本勤務時間を９：００～１８：００とし、Ｃ３が８：００となった場合は、Ｄ３に１：００、Ｃ３が６：００となった場合は、Ｄ３に３：００と表示させたいのです。 但し３０分単位とし、６：１０は６：００として早出時間３：００、６：４０は６：３０とし早出時間２：３０とします。 お分かりになる方がいらっしゃいましたら、お知恵を拝借させてください。 よろしくお願い致します。
- ベストアンサー
- オフィス系ソフト
- タイムカードで勤務時間を計算する関数
タイムカードを作成しているのですが、初心者のため関数がわかりません・・・ どなたかご教授いただけないでしょうか？ 以下の「時間内勤務時間」「普通残業時間」「深夜残業時間」に入れるべき関数がわかれば お願いします。 出社時刻 退社時刻 休憩時間 時間内勤務時間 普通残業時間 深夜残業時間 9:00 22:20 0:45 7:45 4:15 0:20 時間内勤務時間・・・9:00～17:30までの勤務時間。休憩時間45分引いて最高で7:45です。 普通残業時間・・・17:30～22:00までの時間。休憩時間15分を引いて最高で4:15です。 深夜残業時間・・・22:00以降の時間です。
- ベストアンサー
- その他MS Office製品
- エクセル日報 休日出勤 定時内も残業扱いにしたい
度々申し訳ありません。 完璧に出来たと思っていましたらまた問題が出てしまいました。 昨日http://okwave.jp/qa/q8167846.htmlで質問した者です。 早出 5：00～8：50 定時 8：50～17：20 普通残業 17：20～22：00 深夜 22：00～5：00 C D E F G H I ９ 出社 退社 昼休憩 夕方休憩 早出残業 普通残業 普通残業内休憩 J K L ９ 深夜残業 深夜残業内休憩 実労時間 G９セル =IF(ISBLANK(C9),"",IF(COUNT(C9:D9)<>2,"",MIN(D9,"8：50")-MIN(C9,"8：50"))*24) Ｈ９セル =IF(ISBLANK(C9),"",IF(O9="休日出勤",MIN(D9,"21:59")-C9,MAX("17:20",MIN(D9,"21:59"))-"17:20")*24) Ｊ９セル =IF(D9<=TIME(21,59,0),0,(D9-"21:59")*24) Ｌ９セル =(TIMEVALUE(TEXT((1+D9)-C9,"h:mm"))-TIME(0,E9,0)-TIME(0,F9,0)-TIME(0,I9,0)-TIME(0,K9,0))*24 という数式が入ってます。 Ｏ９セルが休日出勤入力欄です。 休憩時間は分単位(例えば45／分)で入力してもらうようにしました。 上記で作成したところ、通常出勤に於いては問題ないのですが、休日出勤として8：50より前に早出出勤した場合、早出残業欄にも早出分が表示されてしまい、普通残業分にも含まれる為重複してしまいます。 例えば8：20出社 17：05退社 昼休憩60分 と入力すると、早出残0.50 普通残8.75 実労7.75 と出てしまいました。 Ｏ９セルに休日出勤と入力した場合、早出残0.50を出ないようにし、普通残は昼休憩を差し引いた7.75にすることは出来るでしょうか？ 夕方休憩、普通残業内休憩は表の下部で残業合計から差し引くようにしてますので考慮しなくて大丈夫です。 ただ、休日出勤でない通常勤務の際は普通残業から昼休憩を引かれてはおかしくなってしまいますので、その辺りも検討頂けると有り難いです。 色々とややこしくて申し訳ありませんが、お力を貸して頂けると有り難いです。 説明不足ありましたらご指摘ください。
- ベストアンサー
- オフィス系ソフト
- エクセルで時間計算（勤怠）の仕方を教えて！！
エクセルを使って、勤怠時間表を作りたいんです。 ＜決まりごと＞ 始業時間…後の時間で30分単位（例 8：47→9：00、9：12→9：30） 終業時間…前の時間で30分単位（例18：20→18：00、19：59→19：30）休憩時間…1時間 残業時間…18：00以降、30分単位 深夜残業…22：00以降、30分単位 ※9：15に始業18：15に退社だと、実働時間は7.5時間で残業は0です。 例えば Ａ始業時間 Ｂ終業時間 Ｃ実働時間 Ｄ通常残業 Ｅ深夜残業 9：17 22：40 12.0Ｈ 4.0Ｈ 0.5Ｈ ＡとＢを入力すれば、Ｃ～Ｅがでてくる、というエクセルの計算式を教えて下さい。
- 締切済み
- その他（ビジネス・キャリア）
- エクセルで給料計算
私は現在、賃金体系が時給制です。 勤務時間が疎らなので、手入力で休憩時間を排除したり、残業時間を集計したりすると非常に面倒に感じてしまいます。 そこでエクセルで給料計算を作成しております。 普通に足し算引き算で計算は出来るのですが、下記載の就業時間内で出勤時間と退社時間だけを入力する事で、普通就業時間、休憩時間、残業時間、深夜割増時間を自動算出する方法を、どなたかご教授して頂けないでしょうか。 エクセルのスキルは初心者です。よろしくお願い致します。 ※時間単位は15分 8:30～11:45(普通就業時間） 11::45～12:30(食事休憩45分） 12:30～15:00(普通就業時間） 15:00～15:15(休憩15分） 15:15～17:00(普通就業時間） 17:00～17：15(休憩15分） 17：15～20:15(残業） 20:15～20:30(休憩15分） 20:30～22：00(残業） 22：00～22：15(休憩15分） 22：15～5：00(残業、深夜割増）
- 締切済み
- オフィス系ソフト
- Excelで残業時間集計
過去の質問も確認しましたが、分からなかったので質問させて下さい。 Excel（2000）で残業時間の集計をしたいのですが、うまく集計出来る関数が分かりませんでした。 【条件】 ◆就業時間が9:00～17:30 ◆休憩時間が12:00～13:00、20:30～21:30 ◆就業時間より30分以上時間が経過している場合は残業時間に該当 ◆22:00以降は深夜残業 ◆土曜日は休憩時間を除く実労働時間が残業時間となる 【例１】 B2に平日の残業開始時間を入力し、C2に残業終了時間を入力すると、D2に残業時間、E2に深夜残業時間が表示される。 【例２】 B3に土曜日の残業開始時間を入力し、C3に残業終了時間を入力するとD3に残業時間、E3に深夜残業時間が表示される。 上記の例で関数を作るとすると、どのような関数が適当なのでしょうか？教えて下さい。宜しくお願い致します。
- 締切済み
- オフィス系ソフト
- エクセルの出勤簿の計算式を教えて下さい。
はじめまして。今月から出勤簿をエクセルで管理したいと思っています。 始業時間9.00終業時間18：00休憩が1時間です。 残業は18.:30からの30分単位になります（18：00～18：30は休憩時間です）。また、深夜残業と早朝出勤はほとんどありませんので無視して下さい。 始業時間と終業時間を入力したら実質労働時間が計算されるような関数を教えて下さい。 出来れば終業が20：30でしたら20.5で入れたいです。（0.5単位） 上手く説明できていないかもしれませんが、宜しくお願い致します。
- ベストアンサー
- その他（ビジネス・キャリア）
- エクセル出退勤表で時間外と深夜時間外の関数
エクセルで出退勤表を作りたいと思っています。 所定労働時間は10:00～18:30で休憩1時間入り、7:30です。 18:30～22:00までは時間外労働時間、22:00～翌5:00までは深夜時間外です。例えば出勤時間10:00、退勤時間23:00とすると、時間外は3:30で深夜時間外は1:00です。 セルの縦列には A：日付（例3/11で表示） B:曜日 C：出勤時間（例10:00で表示） D：退勤時間（例23:30で表示） E：所定労働時間（7:30で固定で休憩の1時間を含んでいない） F：時間外労働時間（例3:00で表示） G：深夜時間外（例2:30で表示） Q1:カレンダーの暦通り、曜日をBセルに自動表示させる方法はありますか？ Q2:Fセルの18:30～22:00までの時間外労働時間を計算する関数を教えて下さい。 Q3:Gセルの22:00～翌5:00までの深夜残業時間を計算する関数を教えて下さい。 Q4：出勤時間と退勤時間を入力すれば時間外と深夜時間外が表示されるようにしたいのですが可能ですか？ よろしくお願いします。 下記の回答を見つけてｅｘｃｅｌで作成しましたが、２２：００以降の深夜残業は計算で出るのですが 朝５：００前の出勤が計算されません。 朝５：００前の出勤が深夜残業に計算で加味される関数式が判りましたらご教示お願い致します。 例として１．朝の３：４０出勤で２５：２０分退勤 ２．朝の３：３０出勤で１４：３０分退勤 の場合でお願いします。 >Q3:Gセルの22:00～翌5:00までの深夜残業時間を計算する関数を教えて下さい。 G2=IF(OR(C2="",D2=""),"",MAX(D2+(D2<C2),22/24)-22/24) ※D2+(D2<C2) C2=10:00で、D2=5:00の場合にD2=27:00とします。 ※22/24="22:00"=22時のこと ※17/48=8.5/24="08:30"=８時間半（休憩時間を含む所定労働時間）のこと
- ベストアンサー
- その他（ビジネス・キャリア）
お礼
さっそくお答えしていただき本当にありがとうございます！ ユーザー定義ではなくIF関数を使うのですね。 大変勉強になりました。 これを機に、まだまだ関数初心者ですが 応用ができるよう学習していきたいと思います。 本当にありがとうございました！
補足
すみません。またまた問題が出てしまいました(>_<) 深夜残業で 23:15は1:00 23:45は1:30・・・と続くのですが 23:15では0:30 23:45では1:00・・・と30分足りなくなってしまいます。 どう直したら良いのでしょうか。。。m（_ _）m 何度も申し訳ありません。