再質問。給与計算について

質問

「給料計算をしたいと考えている
B2に就業開始時間として数字が記載されている。
C2に休憩開始時間として数字が記載されている。
D2に休憩終了時間として数字が記載されている。
E2に就業終了時間として数字が記載されている。
記載されている数字は、
「1.2.3.4.5.6.7.8.9.10.11.12.13.14.15.16.17.18.19.20.21.22.23.24」のうちどれか。小数点にも対応してほしい。
・例2
B2に「7.5」と入力されていたら就業開始時間は「7:30」ということ。C2に「10.5」と入力されていたら休憩開始時間は「10:30」ということ。D2に「11.5」と入力されていたら休憩終了時間は「11:30」ということ。E2に「16.5」と入力されていたら就業終了時間は「16:30」ということ。

時給は、以下のようになっている。
11時から14時の間だけ1000円。その他の時間は950円。

C2の時間からD2の時間は休憩時間なのでその間の休憩は0円とする。

上記の計算をした上でF2に解を出力。

=(MIN(D2,11)-MIN(C2,11))*950+(MEDIAN(11,D2,14)-MEDIAN(11,C2,14))*1000+(MAX(14,D2)-MAX(14,C2))*950+(MIN(F2,11)-MIN(E2,11))*950+(MEDIAN(11,F2,14)-MEDIAN(11,E2,14))*1000+(MAX(14,F2)-MAX(14,E2))*950
上の関数を使って小数点があっても計算ができるようにしたいです。
給与計算は小数点があっても7750がF2に出るようにして欲しいです

回答No.11

chayamati

今晩は。
条件付きですが
休憩時間は時給1000円の時間帯を外す条件で
これは入力規則で制限しています
F列の日当のみです
F2の計算式は
=IF(B2="","",24*$H$4*(E2-B2-D2+C2-$H$2+$H$3-MIN($H$3,E2)+MIN($H$2,E2))+$H$5*24*(MIN($H$3,E2)-MIN($H$2,E2)))
入力規則のリストは１５分刻みで
Ｆ列7；00～2400　Ｇ列：7:00～10、14:00～24:00としました。

回答No.10

chayamati

今晩は
休憩時間は時給1000円の時間帯を外す条件で
これは入力規則で制限しています
F列の日当のみです
F2の計算式は
=IF(B2="","",24*$H$4*(E2-B2-D2+C2-$H$2+$H$3-MIN($H$3,E2)+MIN($H$2,E2))+$H$5*24*(MIN($H$3,E2)-MIN($H$2,E2)))
入力規則のリストは１５分刻みで
Ｆ列7；00～2400　Ｇ列：7:00～10、14:00～24:00としました。

回答No.9

chayamati

こんにちは。回答№6です
＞11時から14時の間だけ1000円。その他の時間は950円。
　時間帯の計算にてこずりました。
　この時間帯には休憩時間は入れない条件で
　添付図の金額列のセルN3の式は
=IF(A3="","",((M3-D3)-(MIN($Q$2,M3)-MAX(U1:U2,D3))-(J3-G3))*$Q$3*24+(MIN($Q$2,M3)-MAX($Q$1,D3))*$Q$4*24)
となります。
図中N4の値が空白のためで0にすると6850となります。日付列が空白ときは金額は空白です・

回答No.8

chayamati

＞7：30開始　10：30〜11：30休憩1時間　16時終了なので切り上げもしくはもし小数点ならその差額を足す関数を教えていただきたいです。
=ROUNDUP(数値)　です

回答No.7

chayamati

こんにちは
解のセルF2の式中にF2を参照していますが、これは無いですよね
(MIN(F2,11)(MEDIAN(11,F2,14)(MAX(14,F2)
F2の参照間違いですどのセルですか？
補足願います

回答No.6

chayamati

こんにちは
＞11時から14時の間だけ1000円。その他の時間は950円。
◎この時間帯は就業していることが前提ですが、
　また休憩時間もダメです

　ご希望の式も適用できませんでした

　添付図の右肩をご覧ください。式で参照します
　金額 N3=($P$2-D3+G3-$Q$2)*$R$2*24+24*($Q$2-$P$2)*$S$2-(M3-J3)*$R$2*24
　就業開始時刻　D3=B3/24+C3/24/60

回答No.5

chayamati

こんにちは。補足ありがとうございます。
＞24時間では7:00~18:00の範囲です
01.入力規則は《7,8,9,10,11,12,13,14,15,16.17,18》の12個に絞られますね
　また日をまたぐ処理は必要ないですね　
02.分は10分刻み,《0,10,20.30.40,50》
　　　　15分刻み《0.15.30,45》
　　　　のどちらかとおもうのですがどうしますか
03.入力規則設定の画面を添付します。
　添付の元の値はコンマ区切りで直接定義出来ます
　就業開始、就業終了、休憩開始、休憩終了の時は個別に設定できます
　それどれの時はさらに絞り込まれます。補足願います
　休憩時刻は任意ですか、例えば12:00～13:00の固定ではないのですか
04.計算の期間は日、週、月のどれですか

回答No.4

chayamati

こんにちは、大変難しい課題ですね
色々未定の個所が見受けられます
エクセルSheet1枚にどれだけ盛り込みますか
1.該当者一人に1枚
2.全員
3.計算期間の単位は
3-1.日単位
3-2.週単位
3-3.月単位
4.時間と時刻の使い分けましょう
　24時間は1日間です、金額＝時間×時給×24
　入力規則を適用していますね、許されない値を避けるのによい方法ですね
　ただ、プルダウンのリストは8行です、発生しない値は省きましょう
　分は0,15,30.45,55で、よろしいかと
　途中ですが添付のようなサンプルを作成中です
　図のG列、H列の翌日は、24時を過ぎたときの処理に使います。
　　0,1の入力規則を適用しています
　永いお付き合いになります。続けますか？　補足ください

補足

24時間では7:00~18:00の範囲です

回答No.3

heisukewada

前回の№２の人の解答がわかりやすいと思います。
５０円単位で計算したいのなら
=IFERROR(CEILING((G2*1000+H2*950),50),"")

回答No.2

luka3

補足ありがとうございます。
しかしながら、まだわかりません。

１. 7:30～10:30　3時間　x950 = 2,850円
２.10:30～11:30　休憩中　0円
３.11:30～14:00　2.5時間　x1000 = 2,500円
４.14:00～16:30　2.5時間　x950 = 2,375円
合計　7,725円

これが、どうして 7,750円 になりますか？