[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『時間合計』（くろ）
いつもお世話になってます。
時間を合計したいのですが、とてつもない長い式になりもっと簡潔に出来ないでしょうか？
ご教授お願いします。
H4=IF(COUNT(C4:D4)<2,"",IF(AND(WEEKDAY(A4)<>7,WEEKDAY(A4)<>1,COUNTIF(祝日,A4)<>1),IF(AND(C4>$AJ$3,OR(E4<>"",F4<>"")),IF(D4+(C4>D4)-C4<="6:00"*1,0,IF(D4+(C4>D4)-C4<="8:45"*1,MIN(D4+(C4>D4)-C4-"6:00"*1,"0:45"*1),IF(D4+(C4>D4)-C4<="13:15"*1,MIN(D4+(C4>D4)-C4-"8:00"*1,"1:15"*1),IF(D4+(C4>D4)-C4<="18:15"*1,MIN(D4+(C4>D4)-C4-"12:00"*1,"2:15"*1),IF(D4+(C4>D4)-C4>="23:00"*1,MIN(D4+(C4>D4)-C4-"19:45"*1,"4:15"*1),MIN(D4+(C4>D4)-C4-"16:00"*1,"3:15"*1)))))),TEXT(MAX(0,MIN(D4+(C4>D4),$AK$4)-MAX(C4,$AK$3))+MAX(0,MIN(D4+(C4>D4),$AM$4)-MAX(C4,$AM$3))+MAX(0,MIN(D4+(C4>D4),$AO$4)-MAX(C4,$AO$3))+MAX(0,MIN(D4+(C4>D4),$AQ$4)-MAX(C4,$AQ$3))+MAX(0,MIN(D4+(C4>D4),$AT$4)-MAX(C4,$AT$3))+MAX(0,MIN(D4+(C4>D4),$AV$4)-MAX(C4,$AV$3))+MAX(0,MIN(D4+(C4>D4),$AY$4)-MAX(C4,$AY$3)),"h:mm")*1),IF(D4+(C4>D4)-C4<="6:00"*1,0,IF(D4+(C4>D4)-C4<="8:45"*1,MIN(D4+(C4>D4)-C4-"6:00"*1,"0:45"*1),IF(D4+(C4>D4)-C4<="13:15"*1,MIN(D4+(C4>D4)-C4-"8:00"*1,"1:15"*1),IF(D4+(C4>D4)-C4<="18:15"*1,MIN(D4+(C4>D4)-C4-"12:00"*1,"2:15"*1),IF(D4+(C4>D4)-C4>="23:00"*1,MIN(D4+(C4>D4)-C4-"19:45"*1,"4:15"*1),MIN(D4+(C4>D4)-C4-"16:00"*1,"3:15"*1))))))))
C4,AK3,AM3,AO3,AQ3,AT3,AV3,AY3=開始時間
D4,AK4,AM4,AO4,AQ4,AT4,AV4,AY4=終了時間
H4=1.平日でAJ3<C4でE4又はF4が空白でない場合⇒6：00<=0:45,8:45<=0:30,13:15<=1:00,18:15<=1:00,23:00<=1:00
2.平日で上記以外 ⇒AK3:AK4＋AM3:AM4＋AO3:AO4＋AQ3:AQ4＋AT3:AT4＋AV3:AV4＋AY3:AY4とC4:D4の差の合計
3.土日祝の場合 ⇒6：00<=0:45,8:45<=0:30,13:15<=1:00,18:15<=1:00,23:00<=1:00
＜ 使用 Excel：Excel2010、使用 OS：Windows8 ＞
このままでは回答がつきにくいでしょう。
表のレイアウトを示し、どの範囲をどのような条件で集計したいのか、 具体的に示して頂けませんでしょうか。
（通りすがりの者） 2015/12/16(水) 15:37
1.条件がまとまります 条件1と条件3の結果は同じになるのであれば、 A4の日付が平日で、AJ3>=C4で、E4:F4に空白がない場合はAK3:AK4＋AM3:AM4＋AO3:AO4＋AQ3:AQ4＋AT3:AT4＋AV3:AV4＋AY3:AY4とC4:D4の差の合計 それ以外は6：00<=0:45,8:45<=0:30,13:15<=1:00,18:15<=1:00,23:00<=1:00 になるのでしょう 果たして今もなっているのかもしれませんが
2.違うセルに勤務時間を出す D4+(C4>D4)-C4は夜勤の時も考慮して勤務時間を算出しているのですよね 式の中に何回も出てくるのだし、この際どこかのセルに放り出して、そこを参照すればいかがでしょう
3.WEEKDAY関数について WEEKDAY関数の第2引数に2を設定すると、土曜は6、日曜は7を返します なので、WEEKDAY(A4,2)<6で平日となります
……とかいろいろ書きましたが、シートレイアウトと条件による欲しい結果を書けば、エキスパート様から回答がつくかと思います 式を解読するのに労力がかかりすぎます
（＿＿＿＿） 2015/12/16(水) 16:51
レイアウトを追加します。 休憩時間(H列)を求めたいです。AJ:AYは複数パターンがあるのでその都度変更する予定です。
|[A] |[B] |[C] |[D] |[E] |[F] |[G] |[H] [1]|日付 |曜日|出勤|退勤|休暇|代休|振休|休憩 [2]| | | | | | | | [3]|平成27年4月| | | | | | | [4]| 1|水 | | | | | |
|[AJ] |[AK] |[AL] |[AM] |[AN] |[AO] |[AP] |[AQ] |[AR] |[AS] |[AT] |[AU]|[AV]|[AW]|[AX] |[AY]
[1]|通常勤務（１）|休憩 |通常勤務（２）|休憩 |時間外（１）|休憩 |深夜（１）|休憩 |深夜（２）|時間外（２）|休憩 |深夜|休憩|深夜|時間外|休憩
[2]| | | | | | | | | | | | | | | |
[3]|9:15 |12:15|13:00 |18:00|18:30 |22:00|23:00 |27:00|28:00 |29:00 |32:15|0:00|3:00|4:00|5:00 |8:15
[4]|12:15 |13:00|18:00 |18:30|22:00 |23:00|27:00 |28:00|29:00 |32:15 |33:15|3:00|4:00|5:00|8:15 |9:15
（くろ） 2015/12/16(水) 17:15
回答ありがとうございます。
上にレイアウトを貼付けました。
休憩時間(H列)を求めたいです。
宜しくお願いします。
（くろ） 2015/12/16(水) 17:16
回答ありがとうございます。
＞＞1.条件がまとまります
この様になってるつもりなのですが、この通りかなり長くなってしまうので
もっと簡潔に出来ないかと質問してます。
＞＞2.違うセルに勤務時間を出す
分かり難くくて申し訳ないですが、これは休憩だけの式です（レイアウトを追加したので参考にして下さい）
＞＞3.WEEKDAY関数について
ずっと土曜は6、日曜は1だと思ってました！！
WEEKDAY(A4,2)<6で済むのはいいですね
この場合祝日はどういう式になるのですか？？
（くろ） 2015/12/16(水) 17:23
>AND(WEEKDAY(A4)<>7,WEEKDAY(A4)<>1,COUNTIF(祝日,A4)<>1) この部分について こんな感じでもいいかな
=WORKDAY(A4-1,1,祝日)=A4
時間の計算の様ですが、小数演算誤差が気になりますね 出勤、退勤時刻に決まり事はありますか （AAA） 2015/12/17(木) 07:31
回答ありがとうございます。
＞＞ =WORKDAY(A4-1,1,祝日)=A4
だけでもいいんですね！！！
＞＞時間の計算の様ですが、小数演算誤差が気になりますね
出勤、退勤時刻に決まり事はありますか
時間の計算苦手でこれだと誤差がでるんですね
どのようにしたら回避できるのでしょうか？
出勤、退勤時刻の決まり事とは？
何時から出勤しても平日の場合は休憩時間の箇所は同じです。
（くろ） 2015/12/17(木) 10:44
詳しくは読んでいませんが
下記の場合どのようになりますか
出勤 退勤 9:30 17:30 11:30 19:30
全て8時間で IF(D4+(C4>D4)-C4<="8:45"*1,MIN(D4+(C4>D4)-C4-"6:00"*1,"0:45"*1)・・・ に該当しそうですが、表を見ると 11:30 〜19:30の場合1:15になりそうな気もしますね
提示された式が正しいという前提で回答すればいいのですか
（AAA） 2015/12/17(木) 19:32
1.平日でAJ3<C4でE4又はF4が空白でない場合と土日祝の場合
出勤 退勤 9:30 17:30 0：45 11:30 19:30 0：45
2.平日で上記以外
出勤 退勤 9:30 17:30 0：45 11:30 19:30 1：15
になります。
宜しくお願いします。
（くろ） 2015/12/18(金) 10:31
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.