[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『早出出勤労働時間の合計をもとめたい時』(かしわもち)
いろいろ考え、調べたりしても答えがでませんでしたのでお教えください。
現在下記のように出退勤表を作成しています。
A C E F G H I J K
17 日 曜日 出退勤時刻 実働時間
18 出勤 退勤 出勤 退勤 勤務時間 休憩 実働時間
19 21 火 6:46 9:00 7:00 9:00 2:00 0 2:00
20 22 水 6:47 9:00 7:00 9:00 2:00 0 2:00
21 23 木 7:00 9:00 7:00 9:00 2:00 0 2:00
22 24 金 6:50 9:00 7:00 9:00 2:00 0 2:00
23 25 土 6:55 12:30 7:00 12:30 5:30 0 5:30
24 26 日
早出出勤は
朝7時〜8時30分に出勤した場合に7:00〜9:00までの2時間が早出時間給の対象にになります。
そのため、7:00〜9:00までに出勤した1ヶ月の合計をもとめたいのです。
それと7:00〜8:30までに出勤した1ヶ月の出勤日数をもとめたいのです。
この表の欄外か横に、早出勤務合計時間と回数を出したい場合の関数がわかりません。
教えて頂ければ助かります。
よろしくお願いします
< 使用 Excel:Excel2010、使用 OS:Windows7 >
(1) J19セル =SUMPRODUCT((E19:E27-"8:30"<0)*(F19:F27-"8:59">0)*("9:00"-E19:E27)) (2) K19セル =SUMPRODUCT((E19:E27-"8:30"<0)*(F19:F27-"8:59">0))
<結果図> 行 _A_ __B__ _____C_____ __D__ ____E____ __F__ ____G____ __H__ ____I____ __J__ _K_ 17 日 曜日 出退勤時刻 実働時間 18 出勤 退勤 出勤 退勤 勤務時間 休憩 実働時間 19 21 火 6:46 9:00 7:00 9:00 2:00 0 2:00 12:19 7 20 22 水 6:47 9:00 7:00 9:00 2:00 0 2:00 21 23 木 7:00 9:00 7:00 9:00 2:00 0 2:00 22 24 金 6:50 9:00 7:00 9:00 2:00 0 2:00 23 25 土 6:55 12:30 7:00 12:30 5:30 0 5:30 24 26 日 25 27 月 7:12 13:00 26 28 火 8:30 11:00 27 29 水 8:29 12:00
(半平太) 2015/05/17(日) 21:10
#VALUE! となりできませんでした。
もうすこし確認してみます。
(かしわもち) 2015/05/18(月) 14:21
何が問題かわかりませんでした。
もう一度教えて頂いてもよろしいでしょうか?
申し訳ありません
(かしわもち) 2015/05/18(月) 14:30
空白セルは未入力ではなく、数式で空白文字→("")を出しているってことなんでしょうか?
(1) J19セル ="9:00"*K19-SUMPRODUCT((E19:E27<"8:30"*1)*(F19:F27>"8:59"*1),E19:E27) (2) K19セル =SUMPRODUCT((E19:E27<"8:30"*1)*(F19:F27>"8:59"*1))
E列とF列にどんな数式が入っているのか示して頂いた方がいいかも知れません。
(半平太) 2015/05/18(月) 14:50
その後もう一度、文字認識するものが無いか確認しながら入力し直しました。
半平太さまの通りに値ができました。
ありがとうございました。
実は遅出出勤もありまして、応用して行ってみました
遅出出勤は
夕15時30〜19時に出勤した場合に15:30〜19:00までの2時間30分が遅出時間給の対象にになります。
やはり同じく1ヶ月分の計を求めたいのです。
回数は半平太さまの教えて頂いたものを応用し、正しい答えがもとめられました。
しかし、時間の計が???です。
=SUMPRODUCT((G19:G29-"15:30">0)*(H19:H29-"18:59"<0)*("19:00"-G19:G29))
これでは、だめなようなでの、教えてください。
お手数かけてもうしわけありません
(かしわもち) 2015/05/18(月) 15:05
1.結局、空白文字列が入っているセルはなかった、と云うことなんですか?
>夕15時30〜19時に出勤した場合に15:30〜19:00までの2時間30分が遅出時間給の対象にになります。 2.この説明はよく分からないです。
早出の時は、「朝7時〜8時30分に出勤した場合に7:00〜9:00までの2時間」なので 「1時間半の間で→2時間」でした。
なら遅出は、「2時間の間で→2時間半」または「2時間半の間で→3時間」だったらまだ分かるんですけどねぇ。 第一、15:30〜19:00って3時間半ありますよ? 何か変じゃないですか?
あと、実働時間の出勤時刻はどうやって(どんな数式で)求めているのですか? 手入力なんですか? 数式だったら、どんなものか分からないと、正確な数式は作れないです。
(半平太) 2015/05/18(月) 16:30
いろいろな説明不足失礼しました。
遅出出勤の説明です。
15時30分から19時までの勤務
ですが、15時30分から勤務開始ですが、仕事が無ければ18時に退社や18時30分に退社など、退社の時間はまちまちです。しかし事業所が19時までなので19時までは勤務している場合もあります。
2時間30分というのは私の間違いです。申し訳ありません
なので半平太さまのいうとおり3時間30分です。
(かしわもち) 2015/05/18(月) 18:28
申し訳ありません。私には難解すぎます。
他の回答者のレスをお待ちください。 m(__)m
(半平太) 2015/05/18(月) 19:33
なので半平太さまの質問にしっかり答えられなくて申し訳ないです
E列とF列の実労働時間の出勤退勤の数式です。
実働時間の出勤時間セル=IF(出勤セル="","",CEILING(出勤セル, $I$15))
I15のセルには丸める分を入力してあります。ちなみに15分
実働時間の退勤時間セル=IF(退勤セル="","",FLOOR(退勤セル, $K$15))
K15のセルには丸める分を入力してあります。ちなみに5分
です。
(かしわもち) 2015/05/18(月) 19:37
ありがとうございました。
(かしわもち) 2015/05/18(月) 19:38
半平太さまからの数式
J19セル ="9:00"*K19-SUMPRODUCT((E19:E27<"8:30"*1)*(F19:F27>"8:59"*1),E19:E27)
を夕方の勤務の数式に応用してみましたが正しい値が返ってこなく、ほとほと参りました。
15:30〜19:00の勤務の1ヵ月計を求めたかったのです。
(15:30には必ず出勤しますが、退社時間は18:30や18:45などまちまちです)
半平太さまの
=”9:00”
の意味がわからず、いろいろ試してみましたが、正しい答えは返ってきません。
下記の式ではダメだったのです
="3:30"*H9-SUMPRODUCT((G19:G49>"16:30"*1)*(H19:H49<"19:01"*1),G19:G49)
質問1 この最初の=の後に続く”時間”はなでしょう?
質問2 数式のカッコ内の”時間”の後にアスタリスク1となっている意味はなんでしょうか?
申し訳ありません教えてください。
(かしわもち) 2015/05/18(月) 22:58
>実働時間の出勤時間セル=IF(出勤セル="","",CEILING(出勤セル, $I$15)) >実働時間の退勤時間セル=IF(退勤セル="","",FLOOR(退勤セル, $K$15))
この数式は、小数演算誤差が入りこむ余地がありますので、後述の数式に変更してください。
>(15:30には必ず出勤しますが、退社時間は18:30や18:45などまちまちです)
・・と言うことなら、出勤時刻が15:30のものだけ計算すればいいことになりますよね? あと、退勤時刻は「現実問題」として、19:00は超えっこないと云う決め打ちでいいですよね?
それでよければ、
(1) G19セル =IF(E19="","",TEXT(CEILING(E19, $I$15),"h:m")*1) (2) H19セル =IF(F19="","",TEXT(FLOOR(F19, $K$15),"[h]:m")*1) (3) L19セル ="9:00"*M19-SUMPRODUCT((G19:G27<"8:30"*1)*(H19:H27>"8:59"*1),G19:G27) (4) M19セル =SUMPRODUCT((G19:G27<"8:30"*1)*(H19:H27>"8:59"*1)) (5) L20セル =SUMPRODUCT(N((G19:G27="15:30"*1)),H19:H27)-"15:30"*M20 (6) M20セル =COUNTIF(G19:G27,"15:30"*1)
< 結果図> 行 _A_ _B_ __C__ _D_ _____E_____ __F__ ____G____ __H__ ____I____ __J__ ____K____ __L__ _M_ 17 日 曜日 出退勤時刻 実働時間 18 出勤 退勤 出勤 退勤 勤務時間 休憩 実働時間 19 21 火 6:46 9:00 7:00 9:00 2:00 0 2:00 10:00 5 20 22 水 6:47 9:00 7:00 9:00 2:00 0 2:00 6:15 2 21 23 木 7:00 9:00 7:00 9:00 2:00 0 2:00 22 24 金 6:50 9:00 7:00 9:00 2:00 0 2:00 23 25 土 6:55 12:30 7:00 12:30 5:30 0 5:30 24 26 日 25 27 月 15:30 18:30 26 28 火 8:30 11:00 27 29 水 15:30 18:45
(半平太) 2015/05/18(月) 23:57
たびたびありがとうございます。
19:00は超えないということで決めて大丈夫です。
結果図の通りにもとめられました!
大変大変ありがとうございます!
いろいろ諸事情があり、すぐに返信お答えできなく申し訳ありません!
大変助かりました!
(かしわもち) 2015/05/19(火) 11:33
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.