エクセル計算式

質問

午前10時から午後６時まで、５分単価税込３０００円という設定で、合計の金額を出す計算式をエクセルで作りたいのですが教えて頂けませんでしょうか？
開始時間、終了時間は随時変更することがあります。
大変、お世話になりますが何卒よろしくお願い致します。

回答No.4

MarcoRossiItaly

No.3 です。すみません、一部、訂正させてください。

No.3 で、切捨てだと計算を誤ってしまうが、切上げであればで誤差対策をしていなくても大丈夫であるかのように書いてしまいましたが、実際は、切上げであっても誤るケースが存在しますね。

失敗例

A1　10:00
B1　10:35
C1　=3000*roundup((b1-a1)/"0:05",)

C1 セルには「21,000」と表示してほしいのに、「24,000」になってしまいます。これは、「(b1-a1)/"0:05"」という部分の計算結果が「7.00000000000001」という値になるためです。小数点第 1 位は「0」になっていますが、切り上げると「7」ではなく「8」になります。

やはり切上げでも No.3 のように、誤差対策をした数式を使ってください。

回答No.3

MarcoRossiItaly

オートフィルで作製した時刻の連続データは、もともと誤差を含んでいます。しかしその後、切り上げを行う場合は、だいたい誤らずに計算できそうです。また、オートフィルではなく手入力で時刻を記入するとより誤差の小さな時刻データとなり、Excel が微小な誤差を除去する機能も働くので、だいたい正確な金額が計算できると思います。

しかし大体ということでは気持ち悪いとか、何らかの都合で、切上げではなく切捨てを行う必要がある場合もあるかもしれません。

完全に計算誤差を防止するためには、(1) HOUR、MINUTE 関数によりいったん整数化し（整数は確実に誤差を含まない）、(2)それを 5 分という時間で割った後、小数点第 2 位を ROUND で四捨五入します。1/5 分 = 0.2 分であるため、小数点以下に 1 桁を残せば十分です。(3)最後にそれを ROUNDUP します。

あるいは簡易法として、ROUND、ROUNDUP により 2 回丸めるだけで済ますという方法もあるかと思います。

いずれも、元々 0.1 分単位の時刻データは存在しないことが前提です。

C1　=3000*roundup(round((60*hour(b1-a1)+minute(b1-a1))/5,1),)
C3　=3000*roundup(round((b1-a1)/"0:05",1),)

回答No.2

bunjii

>午前10時から午後６時まで、５分単価税込３０００円という設定で、合計の金額を出す計算式をエクセルで作りたいのですが教えて頂けませんでしょうか？
通常は下記の式で良いと思いますが微妙な誤差を生じる場合があります。

=ROUNDUP((終了時刻-開始時刻)*288,0)*3000

または

=ROUNDUP((終了時刻*288-終了時刻*288),0)*3000

ROUNDUP関数の端数の扱いで前者と後者に差異が生じることもあります。
何れが正しいかは実績で判断すると良いでしょう。
尚、定数の288は時刻のシリアル値を5分単位に変換するための値（24*60/5=288）です。

回答No.1

keithin

はて？？

＞午前10時から午後６時まで、５分単価税込３０００円という設定で
　：
＞開始時間、終了時間は随時変更する

午前１０時より前の時間，また午後６時を越える時間は「勘定に入れない」とおっしゃりたかったのでしょうか。
また，とりあえず業務時間数は５分単位に「切り捨て」にします。

Ａ１に開始時刻を時：分形式で記入
Ｂ１に終了時刻を時：分形式で記入
Ｃ１に
=IF(COUNT(A1:B1)=2,ROUNDDOWN((MIN(B1,"18:0")-MAX(A1,"10:0"))/"0:5",0)*3000,"")
と記入，セルの書式設定を標準若しくはカンマ区切りとしておく。