ここから本文です

ひとり事務です。タイムカードのエクセル集計、早出・残業・深夜などの細かい時間...

アバター

ID非公開さん

2013/9/1715:37:10

ひとり事務です。タイムカードのエクセル集計、早出・残業・深夜などの細かい時間指定を条件付きで入力する関数をお教えいただけませんでしょうか?

エクセルで勤怠管理表を作成しようとしております。

初めてのひとり事務、前任の方との引継時に、
タイムカード集計をを手計算した後に、PC入力をしていると言われました。
時間がもったいないのでエクセルで計算できないものかと思い
自分で作成しているのですが
関数のサイトや知恵袋などで調べていくうちに、
数式がどんどんぐちゃぐちゃになっていくため、お力添え頂けないかと思いこちらで質問させて頂くことにしました。


出勤 退勤 早出 残業 深夜残業 合計

↑のようにしたいのですが、

基本9:00~17:00(休憩1H)
残業は17:00~22:00(21:59でしょうか)
深夜残業は22:00~
全て15分刻み(以下切り捨て)

早出は5:00~9:00
8:00までは切り捨てで、7:59から1:00+15分刻みで計算

15分切り捨て等は違反だとは思いますが、
それらに見込み残業手当がついているので、時間が集計できればいいようなので。。。

しかも今までの計算法は0:15ではなく0.25などの*24の計算です。

エクセルに触る機会が少なかった自分には到底調べようがありません。

このようなわがままな内容で数式にすることは可能でしょうか?

(出・退・早・深・合 に基本時間などの計算も必要であれば追加して作成します)

補足kkyy5223様
まさにそちらです!excelの神が…
早速作成致しました。

もしまだお付き合い頂けるようでしたら2点だけ宜しいでしょうか…
早出で、7:51出勤ですと「1.0」6:30出勤は「2.5」のように計算する事は難しいですか?
(入力すると「0」「1.5」と計算されます)

また、退社が19:00だと残業1.75と表示されるのですが、2.0にするには19:01と入力した方が良いでしょうか。

無理なお願いですので、本当に気が向かれたらで構いません!

閲覧数:
35,486
回答数:
2
お礼:
250枚

違反報告

ベストアンサーに選ばれた回答

kky********さん

編集あり2013/9/1812:57:41

補足より:

早出に関しては8時までを計算するのかと思いました。
D4を一ケ所変更するだけです。
=IF(A4="","",IF(A4<$C$1,FLOOR($D$1-MAX(A4,$B$1),"0:15")*24,0))

残業に関して
計算誤差があるようです。下記サイトをご覧ください。
http://www.eurus.dti.ne.jp/~yoneyama/Excel/ex-q-a/q_jikoku.html
E4の式を
=IF(A4="","",IF(B4>$E$1,FLOOR(VALUE(TEXT(MIN(B4,$F$1)-$E$1,"h:mm")),"0:15")*24,0))
にしたら、19時の時2と表示されました。20時の時は前の式で大丈夫だったのですが・・。
どの時間の時だめなのかまでは把握できていません。

------------------------------------------------

条件通りに作成してみました。

一行目の時刻は計算のポイントとなる時刻で、このように指定したおいた方が計算式が短くなるので。

4行名以降のA列とB列の書式は、ユーザー定義で [h]:mm とします。
日をまたいで退勤した時は午前1時であれば25:00と入力します。

C4の式
=IF(A4="","",(MIN(B4,$E$1)-MAX(A4,$D$1)-$G$1)*24)
D4の式
=IF(A4="","",IF(A4<$C$1,FLOOR($C$1-MAX(A4,$B$1),"0:15")*24,0))
E4の式
=IF(A4="","",IF(B4>$E$1,FLOOR((MIN(B4,$F$1)-$E$1),"0:15")*24,0))
F4の式
=IF(A4="","",IF(B4>$F$1,FLOOR(B4-$F$1,"0:15")*24,0))
G4の式 (残業は単価が違うので合計する必要があるかわかりませんが)
=SUM(C4:F4)

下へオートフィルします。

補足より:

早出に関しては8時までを計算するのかと思いました。
D4を一ケ所変更するだけです。...

アバター

質問した人からのコメント

2013/9/18 17:00:32

降参 完成致しました!!
本当に本当にありがとうございます!

こんなに早く返答頂けるとは思わず…感激です!

勇気を出してこちらで質問して良かったです。
自分でうなっていたのではいつまで経っても完成できなかったと思います。
もっと勉強しなくては…
教えて頂いたおかげで得られた時間でまた新しい業務に取り組めます。

重ね重ねありがとうございました。

ベストアンサー以外の回答

1〜1件/1件中

mic********さん

2013/9/1716:56:59

とりあえず、今まで手計算で、これから、というならば、
まずは時刻形式で入力するものを作ってはいかがでしょうか。

出勤・退勤・休憩・残業・早出・深夜・夜間休憩・などなどの項目をすべて並べて、足し引きだけで出来る程度のものを。

まずは計算させるよりも、希望通りにあなたが入力できるフォーマットを作る方が先だと思います。
計算はそれからでも出来ます。
Excel初心者に出退勤管理は結構高い壁です。
ウチはExcelではなく、プログラム組みましたから、全然別のシステムで。
まぁ、人数もいるからなんですけどね。

ただ、勤務形態が一定ではない(というよりはどこも大体複雑)なので、計算は後回しでまず手入力で出来る表を作成してみてはいかがでしょう。

以下にテンプレートとかちょっと紹介します。
http://www.bizocean.jp/doc/category/14/
http://www.relief.jp/itnote/archives/003583.php

この辺を参考に御社のルールに則ったものをまずは作ってみてください。
で、ある程度手計算して、決まったものをとらえることが出来た時、意外とご自身で計算式出来ちゃうかもしれませんし。

あわせて知りたい

この質問につけられたタグ

みんなで作る知恵袋 悩みや疑問、なんでも気軽にきいちゃおう!

Q&Aをキーワードで検索:

Yahoo! JAPANは、回答に記載された内容の信ぴょう性、正確性を保証しておりません。
お客様自身の責任と判断で、ご利用ください。
本文はここまでです このページの先頭へ

「追加する」ボタンを押してください。

閉じる

※知恵コレクションに追加された質問は選択されたID/ニックネームのMy知恵袋で確認できます。

不適切な投稿でないことを報告しました。

閉じる