[[20150517164716]]　『早出出勤労働時間の合計をもとめたい時』（かしわもち）　ページの最後に飛ぶ

『早出出勤労働時間の合計をもとめたい時』（かしわもち）

いろいろ考え、調べたりしても答えがでませんでしたのでお教えください。

現在下記のように出退勤表を作成しています。

　　　Ａ　　Ｃ　　　Ｅ　　　　Ｆ　　　Ｇ　　　Ｈ　　　　Ｉ　　　　Ｊ　　　Ｋ

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　　日

早出出勤は
朝７時～８時３０分に出勤した場合に７：００～９：００までの２時間が早出時間給の対象にになります。
そのため、７：００～９：００までに出勤した１ヶ月の合計をもとめたいのです。

それと７：００～８：３０までに出勤した１ヶ月の出勤日数をもとめたいのです。

この表の欄外か横に、早出勤務合計時間と回数を出したい場合の関数がわかりません。

教えて頂ければ助かります。

よろしくお願いします

＜使用 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

半平太様
ありがとうございます。
J１９のセルについて
半平太様の通り式を入力しましたが

 #VALUE!
となりできませんでした。

もうすこし確認してみます。
（かしわもち） 2015/05/18(月) 14:21

半平太様
J２０のセルについても
#VALUE!
となりました。

何が問題かわかりませんでした。

もう一度教えて頂いてもよろしいでしょうか？
申し訳ありません
（かしわもち） 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までの２時間３０分が遅出時間給の対象にになります。

やはり同じく1ヶ月分の計を求めたいのです。

回数は半平太さまの教えて頂いたものを応用し、正しい答えがもとめられました。

しかし、時間の計が？？？です。

=SUMPRODUCT((G19:G29-"15:30">0)*(H19:H29-"18:59"<0)*("19:00"-G19:G29))

これでは、だめなようなでの、教えてください。

お手数かけてもうしわけありません
（かしわもち） 2015/05/18(月) 15:05

 １．結局、空白文字列が入っているセルはなかった、と云うことなんですか？

 ＞夕15時30～19時に出勤した場合に15：30～19：00までの２時間３０分が遅出時間給の対象にになります。 
 ２．この説明はよく分からないです。

 早出の時は、「朝７時～８時３０分に出勤した場合に７：００～９：００までの２時間」なので
 「1時間半の間で→2時間」でした。

 なら遅出は、「2時間の間で→2時間半」または「2時間半の間で→3時間」だったらまだ分かるんですけどねぇ。
  第一、15：30～19：00って3時間半ありますよ？　何か変じゃないですか？

 あと、実働時間の出勤時刻はどうやって(どんな数式で）求めているのですか？　手入力なんですか？
 数式だったら、どんなものか分からないと、正確な数式は作れないです。

（半平太） 2015/05/18(月) 16:30

半平太さま

いろいろな説明不足失礼しました。

遅出出勤の説明です。
１５時３０分から１９時までの勤務
ですが、１５時３０分から勤務開始ですが、仕事が無ければ１８時に退社や１８時３０分に退社など、退社の時間はまちまちです。しかし事業所が１９時までなので１９時までは勤務している場合もあります。

２時間３０分というのは私の間違いです。申し訳ありません
なので半平太さまのいうとおり３時間３０分です。

（かしわもち） 2015/05/18(月) 18:28

 申し訳ありません。私には難解すぎます。

 他の回答者のレスをお待ちください。　m(__)m

（半平太） 2015/05/18(月) 19:33

空白文字列の設定のことですが、それが、どこにあるとエラーなのかわからず。
私自身も、理解できてないです。

なので半平太さまの質問にしっかり答えられなくて申し訳ないです

Ｅ列とＦ列の実労働時間の出勤退勤の数式です。

実働時間の出勤時間セル=IF(出勤セル="","",CEILING(出勤セル, $I$15))

I15のセルには丸める分を入力してあります。ちなみに１５分

実働時間の退勤時間セル=IF(退勤セル="","",FLOOR(退勤セル, $K$15))

K15のセルには丸める分を入力してあります。ちなみに５分

です。
（かしわもち） 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の勤務の１ヵ月計を求めたかったのです。
（15：30には必ず出勤しますが、退社時間は18：30や18：45などまちまちです）

半平太さまの

＝”９：００”

の意味がわからず、いろいろ試してみましたが、正しい答えは返ってきません。

下記の式ではダメだったのです

="3:30"*H9-SUMPRODUCT((G19:G49>"16:30"*1)*(H19:H49<"19:01"*1),G19:G49)

質問１　この最初の＝の後に続く”時間”はなでしょう？
質問２　数式のカッコ内の”時間”の後にアスタリスク１となっている意味はなんでしょうか？

申し訳ありません教えてください。

（かしわもち） 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

コメント返信：

[ 一覧(最新更新順) ]

[[20150517164716]] 『早出出勤労働時間の合計をもとめたい時』（かしわもち） ページの最後に飛ぶ

[[20150517164716]]　『早出出勤労働時間の合計をもとめたい時』（かしわもち）　ページの最後に飛ぶ