[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『稼働時間による終了時間の計算』（oichan）
お教え願います。 稼働時間による終了時間の計算と題しましたが、やりたいことは稼動する時間は決まっており、 開始時間を入力したら自動的に終了時間を表示させたいのです。 ただ、月曜から金曜日までは8:30-26:00(休憩12:00-13:00)で16.5時間稼動、 土曜日は8:30-17:00(休憩12:00-13:00)で7.5時間稼動します。 日曜、祝日は動きません。
例えば、稼働時間が30時間で2007/9/28 13:00(金)から稼動し始めたとします。 すると、金曜日は13:00-27:00の13時間稼動、土曜日は7.5時間稼動、日曜日をはさむので 残りの9.5時間を月曜日にやることになりますので8:30に開始すると19:00(休憩12:00-13:00)終了となります。
A B 1 開始時間 終了時間 2 2007/9/28 8:30 2007/10/1 19:00
このような計算ができるのでしょうか？ 説明が下手で申し訳ありませんがお教え願います。
同じ式を何度も再利用しますので、名前定義を使う方法です。
<名前定義> ※「B2セル」を選択してから、Ctrl + F3キー でダイアログを出し、下記の名前定義をして下さい。
名前 ｜ 参照範囲への式 (※ 入力は、Ctrl+V で貼り付けて下さい)
ーーーーーー｜ーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーー
１ 祝日 ｜ =$J$2:$J$100 ← そちらで実際に作成している範囲にして下さい。
２ 候補日 ｜ =INT($A2-"8:30")+ROW($Z$1:$Z$15)
３ 補正後要稼動｜ =$C2+(MOD($A2-"8:30",1)-MIN("1:00",MAX(0,MOD($A2-"8:30",1)-"3:30")))*24
４ 正味稼動累計｜ =MMULT(N(ROW($Z$1:$Z$15)>COLUMN($A$1:$O$1)),(COUNTIF(祝日,候補日-1)=0)*
((WEEKDAY(候補日)>2)*16.5+(WEEKDAY(候補日-1)=7)*7.5))+0.008
５ 日後 ｜ =MATCH(補正後要稼動,正味稼動累計)-1
６ 該当時刻 ｜ =ROUND(補正後要稼動-(LOOKUP(補正後要稼動,正味稼動累計)-0.008),3)
<式の入力> B2セル =IF(OR(A2="",C2=""),"",INT(A2-"8:30")+日後+"8:30"+(該当時刻+(該当時刻>3.5))/24)
<結果図> 行 _________A_________ _________B_________ __C___ E F _G_ _H_ _I_ ____J____ 1 開始時間 終了時間 要稼動 祝祭日 2 2007/9/28 13:00(金) 2007/10/1 19:00(月) 30 2007/7/16 3 2007/9/17 4 2007/9/23 5 2007/9/24 6 2007/10/8
(半平太)
小数誤差対策のため、以下変更いたします。(半平太)
1. 名前定義 該当時刻 =補正後要稼動-(LOOKUP(補正後要稼動,正味稼動累計)-0.008)
2. B2セルの数式
=IF(OR(A2="",C2=""),"",INT(A2-"8:30")+日後+TEXT("8:30"+(該当時刻+(該当時刻>3.50001))/24,"h:mm"))
過去に見たことの無いような関数が含まれていますが調べて理解してみます。
大変助かりました。ありがとうございました。
(oichan)
行 _________A_________ _________B_________ __C___ E F _G_ _H_ _I_ ____J____
1 開始時間 終了時間 要稼動 祝祭日 2 2007/9/28 13:00(金) 2007/10/1 20:30(月) 30 2007/7/16 3 2007/9/17 4 就業開始時間 就業終了時間 休憩時間 2007/9/23 5 平日 8:30 25:00 1 時間 2007/9/24 6 土曜日 9:00 17:00 1 時間 2007/10/8
(oichan)
テストは十分行っておりません(テストデータ作りが面倒なので、、）。 ご参考程度に(半平太)
1.名前定義
名前 参照範囲
------------------------------------------------------------------------------------------
祝日 =$J$2:$J$100
St就開時 =IF(WEEKDAY($A$2-MIN($B$5:$B$6)+"0:00:30")=7,$B$6,$B$5)
St休開時 =IF(WEEKDAY($A$2-MIN($B$5:$B$6)+"0:00:30")=7,$E$6,$E$5)
St休終時 =IF(WEEKDAY($A$2-MIN($B$5:$B$6)+"0:00:30")=7,$F$6,$F$5)
候補日 =INT($A$2-ST就開時+"0:00:30")+ROW($AA$1:$AA$16)-1
実就時 =MOD($A$2,1)+(MOD($A$2,1)+"0:00:30"<ST就開時)
補正後要稼動 =$C$2+(実就時-ST就開時-MIN(ST休終時-ST休開時,MAX(0,実就時-ST休開時)))*24
日後 =MATCH(補正後要稼動,正味稼動累計)-1
Ed就開時 =IF(WEEKDAY(INT($A$2-St就開時+"0:00:30")+日後)=7,$B$6,$B$5)
Ed休開時 =IF(WEEKDAY(INT($A$2-St就開時+"0:00:30")+日後)=7,$E$6,$E$5)
Ed休終時 =IF(WEEKDAY(INT($A$2-St就開時+"0:00:30")+日後)=7,$F$6,$F$5)
正味稼動累計 =MMULT(N(ROW($AA$1:$AA$16)>COLUMN($A$1:$P$1)),(COUNTIF(祝日,候補日)=0)*
((WEEKDAY(候補日+1)>2)*$G$5+(WEEKDAY(候補日)=7)*$G$6))+0.00
該当時刻 =補正後要稼動-(LOOKUP(補正後要稼動,正味稼動累計)-0.008)
2.数式 (1) F5セル =E5+D5/24 フィルダウン (2) G5セル =(C5-B5)*24-D5 フィルダウン (3) B2セル =IF(OR(A2="",C2="",COUNT($B$5:$E$6)<8),"",INT(A2-St就開時+"0:00:30")+日後+TEXT(Ed就開時 +(Ed休終時-Ed休開時)*((Ed就開時+該当時刻/24)>(Ed休開時+"0:00:30"))+該当時刻/24,"[h]:mm"))
<結果図> 行 _________A_________ _________B_________ ___C____ _D__ __ E ___ __ F ___ ___G____ 1 開始時間 終了時間 要稼動 2 2007/9/28 13:00(金) 2007/10/1 20:30(月) 30 3 4 就業開始 就業終了 休憩 休憩開始 休憩終了 正味稼動 5 平日 8:30 25:00 1 12:00 13:00 15.5 6 土曜日 9:00 17:00 1 12:00 13:00 7
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.