物を分単位で借りた時のレンタル料が正しく出せない

質問

皆様には大変お世話になっております。仕事でまた躓いてしまっています。
何方か、対処方法をご存じでしたらご教示頂けると大変助かります。

物を分単位で数分間~24時間借りた時のレンタル料を求めたいのですが、
下記のような症状となってしまって、非常に困っています。

利用日＝2022/4/28、　レンタル開始時刻＝10:00　レンタル終了時刻＝11:00を
Aセル、Bセル、Cセルに入力し、Dセルに、上記の利用時間を数値として求める為、
=IFERROR((C1-B1)*24,"")の式を入れた所、
例えば、1000円の10%の消費税は所々99円となってしまいました。

調べて見ると、小数点第14位までは、1.00000000000000と表示されますが、
小数点第15位まで表示させると、0.999999999999999と表示が変わりました。
この場合、利用時間は1.0としたいのですが、この式ではうまくいかない事が分かりました。

そこで、=IFERROR((C1-B1)*24,"")の式を、=IFERROR(TEXT(C1-B1,"hh:mm")*24,"")へ変更した所、
上記のレンタル利用時間では、うまく消費税の値を出す事が出来ましたが、
利用日＝2022/4/28、　開始時刻＝0:00　終了時刻＝24:00と入力した場合は、
終了時刻＝00:00、利用時刻＝00:00、利用時刻のセルは空白となってしまいました。

利用日＝2022/4/28、　開始時刻＝10:00　終了時刻＝11:00と時も利用時間は1が出るようになり
利用日＝2022/4/28、　開始時刻＝00:00　終了時刻＝24:00と入力した時も、終了時刻は24:00となり、
利用時間も24が出るようにしたいのですが、対処方法が全く掴めないでいます。

回答No.4

SI299792 · ベストアンサー

あなたの数式でも、
B1: 0:00
C1: 0:00
D1: 0.000000000000000
になりますが。

これは、時間計算の誤差です。
B1: 0:00
C1: 1:00
D1: 1.000000000000000

B1: 0:00
C1: 24:00
D1: 24.000000000000000

を満たすには
=TEXT(C1-B1,"[h]:m")*24
が１番簡単か思います。しかし、切りのいい時間は正しく計算されますが、分単位の場合誤差が出る可能性があります。

その後にどのように慮金を出しているのか解りませんが、
=ROUND((C1-B1)*1440,0)
にして分単位で計算した方がいいと思います。

お礼

具体的なご教示ありがとうございました。大変参考になりました。
取り合えず、私の力量では、当面は以下のように式を設定する事を考えています。
対策として：
終了時刻のセルは、24:00を入力した時、00:00とならないよう、書式設定で『[h]:mm』を設定する。
利用時間のセルへ=ROUND(((C1-B1)*1440)/60,2)を設定する。

回答No.3

imogasi

質問の式は「分単位に」なっていないでしょう。そこが問題でしょう。
ーーー
エクセルの時間（正確には「時刻」かも）は「日付・時刻シリアル値」という考えの数値で、この理解ができてないのでしょう。
基本は１日２４時間が1.0　単位で表します。１分は1/(24*60)と考えたX分になります。１日＝２４時間＝24*60分だから。
ーー
セルにエクセルの標準的な時刻の入力方式で入力（２３分は０：２３（半角で）のように）したとき、セルの値は、そういう計算で計算された数値がセットされています（と考える）。
ややこしいのは
（１）小数点以下のまるめの問題
（２）X時０分をまたがる時刻の分計算、ですが、
（２）は２つの日付時刻せりある値を単純に算術減算すれば、値が出て、分計算したいなら、1/(24*60)＝１分のエクセル方式での値で割れば、分が出る。
（１）の問題は複雑そうなので略。
ーー
実例をやって見ると
ａ1セルに10:35　、 B1セルに11:02　と入力、C1に関数　=B1-A1と入れると、見た目は
0:27とでます。０時間２７分という意味に違いない。C1セルの表示形式は日付・時刻に、気を利かして設定してしまうのだと思います。
ーー
（理解のためのテスト）
これをセルの値で表示すると、小数点以下５桁で、打ち切って表示（数値で表示）で、
A1,B1,C1は
0.44097 0.45972 0.01875
の数になってます。
C1の、0.01875 は、そのセルの値を使って　=C1*24*60と計算すると、「27」です。
この27という「数字＝数量」に当たる、分（「ふん」の）数を計算し、さらに27(分）という数に対応した、レンタル料を計算（エクセル関数ででも）すればよい。割り算とかVLOOKUP関数とかで、
色々な商売の料金体系があるでしょうから。
＝＝＝
最近この手の質問が多い。
正式なエクセルの教習場所、とか教師の指導の元で、学ばないからだと思う。
周りに聞く先輩もいないのに、商売のことに、エクセルを使うとは大胆と思うが。長年やってれば、どこかで行き詰まり、本やWEBなどで調べて、仕組みがわかってくるでしょうが、技巧的な仕組みなので、自然に気づくというのは難しいかも。
かくいう小生も、上記の説明で、（マイクロソフトのエクセル設計者などからすれば）多少？実態と違う点、説明があるかもしれないですが。確かめるすべがない。
＝＝
エクセルのセルの値を対象にして、計算をする必要が出たとき、見た目＝表示形式適用後のもの・数など）で考えてはいけないです。そこでは、分や時の単位の文字は余分という点は気づくでしょうが、２０分の場合で、２０という、見てくれを使っては、いけないということです。セルの値にまで遡って、単位に換算し直して、考え直す必要があるということです。
この文章を書くのに（文章が長いことと、毎回同じことを書いている（自分の勉強にはならないので）ああ疲れた、です。

お礼

大変ご丁寧に説明をして下さり、ありがとうございました。
大変勉強になりました。
これからもEXCEL精進して、勉強して行きます。

補足

ご丁寧なご教示ありがとうございます。
データのフォーマット変更は不可ですので、やはり、開始時刻、終了時刻、利用時間のセルはこのままで使わなければなりません。　取り合えず、終了時刻は『[h]:mm』を設定します。利用時間が分単位の式ではなかった事理解しました。しかし、利用時間のセルに小数点2桁の数字で、0.99999999の対策をした式については、未熟者で浮かびませんでした。具体的に教えていただけないでしょうか。

回答No.2

kon555

No1です、補足というか、余談的な内容になります。当座の対策というより根本的なやり方の話になります。ちょっと長くなりますが、お時間のある時にでもご一読下さい。

　質問文を読む限り、貴方の今のシートは分単位計算には不向きな作りになっているように思います。
　分単位で時間計算する場合、時間差を算出する時は『◯時間』単位ではなく、『◯分』単位で計算する形にした方が良いです。なぜならこちらの方が、小数点にまつわる誤差を回避できるからです。
　具体的には質問文の中の『この場合、利用時間は1.0としたい』という部分を、1.0（時間）ではなく60（分）で計算した方が良いという事です。

　そのような計算にする場合、1セルに「1:00」のように入力するのではなくて、『時』と『分』を別セルに分けてしまえば『時』×60＋『分』でシンプルに分単位が算出できます。
　また先の回答で述べたように、日付を跨ぐケースが存在するなら『開始日』『終了日』を織り込まないと計算が狂います。これもそれぞれセルを分け、日数差を計算した後に1440を掛けて分単位の差に直しましょう。

　今お使いのシートで実務的にOKなら当面はいいのですが、作り替える際などには意識してみて下さい。

お礼

かみ砕いて説明をして下さり、ありがとうございました。
EXCELの理解力がまだまだ足りていませんが、めげずに頑張って精進していきます。

回答No.1

kon555

>>利用日＝2022/4/28、　開始時刻＝0:00　終了時刻＝24:00と入力した場合

　時刻の表記方で言うと『0:00』と『24:00』は同じですから、そのせいでおかしくなっているのでしょう。
　利用日を1セルではなく利用開始日と終了日の2セルに分けるのが本筋でしょうが、書かれている内容からすると、利用時間は最大で24時間、また利用時間0はありえないですよね？
　もしそうであれば、if関数を使って利用時間0なら利用時間24、とするのが1番シンプルだと思います。