エクセルで請求額の式を作成したい

質問

こんにちは。とある、社員さん（他社へ派遣している）の派遣先へ出す請求書の請求金額を出す、計算シートを作成しています。
毎月、20日には【見込み時間】として勤務表がＦＡＸで送られ（20日～月末までの分）25日には見込み請求として請求しています。
そして翌月の頭に、【実績時間】として本当に稼動した勤務表がＦＡＸできます。その差額を同じく25日に請求しています。

この社員の規定は以下の通りです。

基本140ｈ～180ｈまでは基本時間内として定額支給とする。
　　180ｈ以上の時間は0.5ｈ単位で超過精算とする。
例：【一月見込み時間】　188.0ｈ （式：見込み時間－180ｈ）
　　※結果、180ｈ越してる分8ｈを定額にプラスして精算する。
　　【一月実績時間】　182.0ｈ
　　（式：実績時間-見込み時間、ただし180ｈに満たないときは”0”　　　と表示したい）
　　※この場合、見込み時点で8ｈ超過分を支払済みなので、
　　　実績が180ｈ越したとしても過払いしていることになる。
　　　だから、差額の6ｈを控除することとなる。

以上を踏まえた、エクセルにて計算式を作成することは可能でしょうか？
説明がわかりにくかったら申し訳ありません。どうぞ、よろしくお願い致します。

回答No.3

CMLT · ベストアンサー

140以下のことが記載されてないので140以下にはならないものとします。
一月見込み時間を X 、
一月実績時間を Y とすると、以下のパターンが考えられる。
(1)X、Y共に180以下　(2)Xが180超　(3)Yが180超　
(4)共に超でX>Y　(5)共に超でX=Y　(6)共に超でX<Y
この場合、MAX(X,180)とMAX(Y,180）の差を求めるとIF関数よりわかりやすい。
まず、見込みの超過時間は、=MAX(X,180)-180
これで180以下の場合は0になる。
また、実績後の清算は、=MAX(Y,180）-MAX(X,180)とした場合、
(1)(5)では当然0となる。
(3)(6)では結果がプラスで出るのでその分清算
(2)(4)では結果がマイナスで出るのでその分控除
あとは表示のさせ方を工夫すればよいでしょう。

もし、140時間以下も同額で0.5ｈ単位で清算するのであれば、
見込みの不足時間は、=140-MIN(X,140)
=MIN(X,140)-140とすれば負ででるので、上の場合と合わせると、=MAX(X,180)-180+MIN(X,140))-140
簡略化して、=MAX(X,180)+MIN(X,140))-320
（プラスで出たら超過時間、マイナスは不足時間）
実績後の清算は、=MIN(Y,140)-MIN(X,140)
上の場合と合わせると、=MAX(Y,180）-MAX(X,180)+MIN(Y,140)-MIN(X,140)
（プラスで出たら清算、マイナスは控除）

以上、IF関数では場合分けがややこしくなるのが、これなら足し算引き算だけになります。
ちなみに見込120ｈ、実績200ｈは（200-180）+（140-120）で40と表示されます。

お礼

ご回答下さいましてありがとうございます。
大変、解りやすく指示通りに作成しましたら、超過分（180ｈ以上）、控除分（140ｈ以下）ともに、正しく計算されました。min maxを使うと便利なのですね。回答者様のお陰で月々に計算が楽になりました。
min maxを詳しく調べ勉強してみます！
本当にありがとうございました。

回答No.4

koko88okok

No.1,2です。

> 完璧マスターを読んでわかりました。
(見込み時間>=140,0,"基本時間未満")
　　　　　　　　　　　　↑
　　ここには、見込み時間-140）となるんですね！

いえ、見込み時間が140hより大きい場合は、「0」(前半の式で、180hより小さいことが分かっているので）
そうでなければ(140hより小さい場合は)「基本時間未満」と表示しなさい
という式です。（140h以下の場合についての記述がなかったので、勝手にそのように表示させることにしました。）

別の表現をすれば、
前半は、180hを超えた場合の計算で、後半は、見込み時間が規定の180h－140hかどうかを判定し、180h－140hであれば、「0」そうでなければ、「基本時間未満」と表示して、別の処置を促そうとするものです。

回答No.2

koko88okok

No.1です。

すみません。140時間以下の場合を考慮していませんでした。

「見込み時間による清算時間」 =IF(見込み時間>180,見込み時間-180,0)
を次のように変更してください。

「見込み時間による清算時間」 =IF(見込み時間>180,見込み時間-180,IF(見込み時間>=140,0,"基本時間未満")

「IF関数 完璧マスター！」
http://kokoro.kir.jp/excel/if.html

お礼

完璧マスターを読んでわかりました。
(見込み時間>=140,0,"基本時間未満")
　　　　　　　　　　　　↑
　　ここには、見込み時間-140）となるんですね！

助かりました。本当にありがとうございました。

補足

早速のご回答、ありがとうございます！
ところで、この→140,0,"基本時間未満")の”基本時間未満”には140ｈと180ｈのどちらが入るのでしょう？
今から、添付していただいた完璧マスター、を読んでみます。

回答No.1

koko88okok

ご質問が何か分かりませんが、文章から次のような計算をしたいのかな？と思います。

「見込み時間による清算時間」 =IF(見込み時間>180,見込み時間-180,0)

「実績時間による清算時間」 =実績時間-見込み時間