[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『【関数】時間計算からの費用算出』（bunbun）
時間計算が伴う関数が苦手で分からないのでご教示願います。
会社で使用する表で、今は手計算なのですが量が多く大変です。、
マクロは全く分からないので、関数で自動計算できたらと思っております。
表のイメージは下記の通りです
A B C D E F G
2 顧客名 種類 平日/休日 作業日 開始時間 完了時間 工数
3 ■■（株） Ａ 1 2022/9/07 13:00 14:54 1:54
4 ○○会社 Ｂ 2 2022/9/10 13:30 19:24 5:54
※Ｃ列の1＝平日、2＝休日
【やりたい事】
Ｇ列の工数からＨ列に延長料金を算出したい
【条件】
・Ｂ列の種類Ａは1時間作業、Ｂは2.5時間作業
・延長料金は30分毎だが、20分を超えた時点で発生
（種類Ａは1時間20分を超えたら発生、Ｂは2時間50分超えたら発生）
・延長料金の設定は下記の通りです
平日日中帯9:00-17:00 延長料金 \3,000/0.5h 平日17:00-22:00 休日9:00-17:00 \3,900/0.5h 平日22:00-9:00 休日17:00-9:00 \4,500/0.5h
よって、回答が下記の通りなればOKです
H3 \ 6,000
H4 \30,300
計算するに辺り、必要な項目（列）を増やす事は可能です。
出来ますでしょうか・・・
宜しくお願い致します。
＜ 使用 Excel：Excel2010、使用 OS：Windows10 ＞
1.延長料金の設定の表はシート上のどこかにありますか？ ある場合、「平日日中帯9:00-17:00」などは1セルに全部入っていますか？ 2.平日と休日をまたぐような事象は発生しないと思っていいですか？ 3.G3が1:44の場合\3,000になる認識で合っていますか？ （.:*.ゆ ゅ） 2022/09/12(月) 13:13
多分こういう事ではない筈なんだよなぁ... と自分でも思いながら書いてます ^^;
_|___A____|___B____|____C____|___D____|___E____|___F____|___G____|___H____|___I____|___J____|___K____|___L____|___M____|___N____ 1|顧客名 |種類 |平日/休日|作業日 |開始時間|完了時間|工数 |延長料金|開始(分)|完了(分)|工数(分)|延長(分)|+900/30 |+600/30 2| |Ａ | 1| | 13:00| 14:54| 1:54| 6,000| 780| 894| 114| 54| 0| 0 3| |Ｂ | 2| | 13:30| 19:24| 5:54| 30,300| 810| 1164| 354| 204| 204| 144
[I2] =ROUND(E2*1440,) [J2] =ROUND(F2*1440,) [K2] =MAX(J2-I2,) [L2] =(B2="Ａ")*(K2>=80)*(K2-60)+(B2="Ｂ")*(K2>=170)*(K2-150) [M2] =(C2=1)*MAX(J2-1020,)+(C2=2)*L2 [N2] =(C2=1)*MAX(J2-1320,)+(C2=2)*MAX(J2-1020,)
[H2] =INT((L2+20)/30)*3000+INT((M2+20)/30)*900+INT((N2+20)/30)*600
（白茶） 2022/09/12(月) 15:09
＞・延長料金の設定は下記の通りです ＞ 平日日中帯9:00-17:00 延長料金 \3,000/0.5h ＞ 平日17:00-22:00 休日9:00-17:00 \3,900/0.5h ＞ 平日22:00-9:00 休日17:00-9:00 \4,500/0.5h
時間帯がまたがった場合、どう言うケースに何が適用されるのですかねぇ・・ もう少し、厳密な説明が聞きたいなぁ。。
（半平太） 2022/09/12(月) 15:57
＞・延長料金は30分毎だが、20分を超えた時点で発生 ＞ （種類Ａは1時間20分を超えたら発生、Ｂは2時間50分超えたら発生）
念の為に、ここも確認しておきたい。
一旦延長が発生したら、その後は、1分でも経過したら、次の30分としてカウントされるんですか？ 例：種類Ａが1時間31分だった場合、延長料金は「0.5ｘ2」で計算するんですか？ それとも、1時間50分になった時点で、「0.5ｘ2」なのですか？
（半平太） 2022/09/12(月) 16:25
ご連絡ありがとうございます。
お返事が遅れて申し訳ございません。
＞時間帯がまたがった場合、どう言うケースに何が適用される
跨った場合が面倒なので困ってるところでした。
例えば、本文の例題Ａ４の○○会社の場合です。
2022/9/10の休日で、開始13:30、完了19:24なので
・16:00〜17:00は、休日9:00-17:00 \3,900/0.5hが適用
・17:00〜19:24は、休日17:00-9:00 \4,500/0.5hが適用されます。
＞延長料金は30分毎だが、20分を超えた時点で発生に関しての詳細
これもまた、ややこしいのですが・・・
★基準はあくまで30分単位なのですが、20分を超えれば延長とみなしますよという事です。
例えば、種類Ａの1時間作業の場合で13:00開始だとすると
・13:00〜14:20では延長なし
・13:00〜14:21では30分延長
で、13:30がまたベースになるので、
・13:00〜14:45でも30分延長
・13:00〜14:50で初めて60分延長
・13:00〜15:10でも60分延長です。
（bunbun） 2022/09/14(水) 09:11
・13:00〜14:20では延長なし → 30分の延長でした・・・。
（bunbun） 2022/09/14(水) 09:36
跨ったサンプルが単純過ぎる気がするのですが、 開始時刻は必ず30分刻みなのですか？(それなら問題ないですけど）
（半平太） 2022/09/14(水) 09:52
24時間を超えることはないと考えていいのですか？ 1800〜1900の1900が当日である保証は現状ないので。 （.:*.ゆ ゅ） 2022/09/14(水) 10:04
＞開始時刻は必ず30分刻みなのですか？
そうですよね。
私が担当してからは30分刻みしかないのですが、
過去には45分開始などがあります。
（.:*.ゆ ゅ）様
＞24時間を超えることはないと考えていいのですか？
23:00開始26:00完了など、日を跨ぐ事はありますが、24時間を超える事はありません。
（bunbun） 2022/09/15(木) 16:32
今まで、は延長区分という列を設けて下記の設定で、1〜3の数字を入れていました。
1＝平日日中帯9:00-17:00 延長料金 \3,000/0.5h
2＝平日17:00-22:00 休日9:00-17:00 \3,900/0.5h
3＝平日22:00-9:00 休日17:00-9:00 \4,500/0.5h
それで次の様な関数で対応していましたが、延長料金の形態をまたぐ事があり、
今回の質問をさせて頂きました。
IFERROR(INT(MAX(0,工数-IF(種類="Ａ","0:50","2:20"))/"0:30")*CHOOSE(延長区分,3000,3900,4500),"")
（bunbun） 2022/09/15(木) 16:57
＞過去には45分開始などがあります。
30分刻みである保証がないとするならば、 以下の様に時間帯が跨ったケースの延長料金はどうなるんですか？ 考え方を明確にしてください(数式はこちらで考えます)
理屈と計算内訳が分かる様にご説明ください。 (○○である方の料金が優先され、3,900適用が0.5*◎個、4,500適用が0.5*△個となる)
行 _____A_____ __B__ ____C____ ____D____ ____E____ ____F____ __G__ 2 顧客名 種類 平日/休日 作業日 開始時間 完了時間 工数 3 ■■（株） Ａ 1 2022/9/7 15:45 17:05 1:20 4 ■■（株） Ａ 1 2022/9/7 19:50 22:35 2:45 5 ■■（株） Ａ 1 2022/9/7 20:25 22:45 2:20
（半平太） 2022/09/15(木) 17:31
＞23:00開始26:00完了など、日を跨ぐ事はありますが、24時間を超える事はありません。
早朝から開始(例：5:00AM）なんてあるんでしょうか？
また、23:00から翌日22:00迄なんて長期間労働もありなんですか？ ※標準が2時間半の作業で、翌日9時以降も作業しているなんて考えられないのですが・・
（半平太） 2022/09/15(木) 23:28
完成するまでお付き合い頂いてありがとうございます。
大変助かります。
作成するに辺り情報が少なくて申し訳ございません。
＜条件＞
・30分毎に延長料金を加算する
（30分の内6割（20分）に満たない場合は加算出来ない）
・初回は20分で発生
・延長時間帯が費用形態を跨ぐ場合は、金額が高い方で計算
＜例題の答え＞
テストで作成頂いてる例題回答は下記の通りとなります。
・A3の場合（15:45〜16:45は基本料金）
→16:45〜17:05は30分に満たないが、20分経っている為、
「30分」分の延長料金発生
また、完了時間が17:00を超えた為\3,900
・A4の場合（19:50〜20:50は基本料金）
→20:50〜21:20 \3,900
21:20〜21:50 \3,900
21:50〜22:20 \4,500
22:20〜22:35は15分の為加算なし
・A5の場合（20:25〜21:25は基本料金）
→21:25〜21:55 \3,900
21:55〜22:05 \4,500
22:05〜22:35 \4,500
22:35〜22:45は10分の為加算なし
これで分かりますでしょうか・・・。
不備があったら申し訳ございません。
（bunbun） 2022/09/16(金) 14:57
＞早朝から開始(例：5:00AM）なんてあるんでしょうか？
今後、お客様の都合で特例があるかもしれませんが、現在はありません
＞23:00から翌日22:00迄なんて長期間労働もありなんですか？
ありません。
過去最長は6時間です。ほとんどは延長が2時間を超えるとリスケになります。
（bunbun） 2022/09/16(金) 15:03
１．J2〜N2に 下図の様な時刻を入力する
２．I列は空白にする
３．下式を入力する (1) G3セル =F3-E3+(F3<E3)
(2) J3セル =FLOOR(MAX(0,MIN(K$2,$F3+($F3<$E3)+"0:10")-$E3-IF($B3="Ａ","1:00",IF($B3="Ｂ","2:30",NA())))+"0:00:07","0:30")-SUM($I3:I3) M3セルまでコピー ※数式中、A,Bは全角になっていますので、もし実際が半角ならその様に修正してください。
(3) H3セル =IF(COUNTA(B3:F3)<5,"",SUMPRODUCT(INDEX({45,30,39,45;45,39,45,45}*100,C3,0),J3:M3)*48)
上記数式を下にコピー <結果図> 行 _____A_____ __B__ ____C____ ____D____ ____E____ ____F____ __G__ ____H____ _____I_____ __J__ __K__ __L__ __M__ __N__ 1 I欄は必ず 2 顧客名 種類 平日/休日 作業日 開始時間 完了時間 工数 延長料金 空白にする 0:00 9:00 17:00 22:00 33:00 3 ■■（株） Ａ 1 2022/9/7 15:45 17:05 1:20 3,900 0:00 0:00 0:30 0:00 4 ■■（株） Ａ 1 2022/9/7 19:50 22:35 2:45 12,300 0:00 0:00 1:00 0:30 5 ■■（株） Ａ 1 2022/9/7 20:25 22:45 2:20 12,900 0:00 0:00 0:30 1:00
テストをロクにしてないので、そちらで入念にチェックしてください。 もし、おかしい場合は、具体的な時刻データと正解付きでご指摘ください。
（半平太） 2022/09/16(金) 19:27
＞・延長時間帯が費用形態を跨ぐ場合は、金額が高い方で計算
済みませーん
早朝開始の場合は、9時跨ぎで延長が発生すると、上の数式では対応できませんでした ※うしろの時間帯が優先する様に作ってしまった為
なので、以下に変更します。（ついでに「I列の空欄」と「N3の"33:00"」も不要としました）
１．I2〜L2に 下図の様な時刻を入力する
２．下式を入力する (1) G3セル =F3-E3+(F3<E3) (2) I3セル =FLOOR(MAX(0,MIN(F3+(F3<E3),J$2+"0:19")-(E3+IF(B3="Ａ","1:00",IF(B3="Ｂ","2:30",NA()))))+"0:10:07","0:30")
(3) J3セル =FLOOR(MAX(0,MIN(K$2,$F3+($F3<$E3)+"0:10")-$E3-IF($B3="Ａ","1:00",IF($B3="Ｂ","2:30",NA())))+"0:00:07","0:30")-SUM($I3:I3) K3セルまでコピー ※数式中、A,Bは全角になっていますので、もし実際が半角ならその様に修正してください。
(4) L3セル =FLOOR(MAX(0,G3-IF($B3="Ａ","1:00",IF($B3="Ｂ","2:30",NA())))+"0:10:07","0:30")-SUM(I3:K3)
(5) H3セル =IF(COUNTA(B3:F3)<5,"",SUMPRODUCT(INDEX({45,30,39,45;45,39,45,45}*100,C3,0),I3:L3)*48)
全体を下にコピー
<結果図> 行 _____A_____ __B__ ____C____ ____D____ ___ E ___ ____F____ __G__ ____H____ _ I _ __J__ __K__ _ L _ 1 2 顧客名 種類 平日/休日 作業日 開始時間 完了時間 工数 延長料金 0:00 9:00 17:00 22:00 3 ■■（株） Ａ 1 2022/9/7 15:45 17:05 1:20 3,900 0:00 0:00 0:30 0:00 4 ■■（株） Ａ 1 2022/9/7 19:50 22:35 2:45 12,300 0:00 0:00 1:00 0:30 5 ■■（株） Ａ 1 2022/9/7 20:25 22:45 2:20 12,900 0:00 0:00 0:30 1:00 6 ■■（株） Ａ 1 2022/9/7 13:00 14:54 1:54 6,000 0:00 1:00 0:00 0:00 7 ○○会社 Ｂ 2 2022/9/10 13:30 19:24 5:54 30,300 0:00 1:00 2:30 0:00 8 ■■（株） Ａ 1 2022/9/7 7:45 10:00 2:15 7,500 0:30 0:30 0:00 0:00 9 ■■（株） Ａ 1 2022/9/7 7:15 10:05 2:50 15,000 1:00 1:00 0:00 0:00
（半平太） 2022/09/17(土) 10:19
作業が 難航する、
事もあって、
＞9：00〜翌8：59
迄 作業する、
＞22：00〜翌21：59
迄 作業する、
なんて 事も、
ありなのですよね？
_
其の場合、
一端 上昇した、
延長料金は、
一度 上がったら、
上がったままなのですか？
（Nouble） 2022/09/21(水) 01:09
お礼のお返事が遅れて申し訳ございません。
弊社サーバの移行で色々あり、なかなか確認出来ず時間が掛かってしまいました・・・。
本当に助かります。ありがとうございました。
まだ細かいチェック（業務確認）は出来ておりませんが、何かあればご相談させて頂きます。
（bunbun） 2022/09/29(木) 15:58
随分、お返事が遅くなってしまい申し訳ございません。
色々あって着手する事が出来ませんでした・・・。
ご対応ありがとうございます。
またご質問内容ですが、作業工数が24時間以上掛かる事はないと思って構いません。
総工数はせいぜい最大5〜6時間です。
また、延長料金はあくまで最初にお伝えしている時間帯ですので、
翌日の朝9時になると料金が戻ります。
（bunbun） 2022/09/29(木) 16:07
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.