Excelで、15分単位での時給計算が正しく表示できない

お世話になります。
15分単位での時給計算を行いたいのですが、15分きっかりの時だけ
下記計算式で出てくる数値が0.25にならず、0.00になってしまいます。

例）
時給千円で、17:00から18:15まで業務したとして、
A1に開始時間、B1に終了時間、C1に勤務時間、
D1に15分単位の値を表示させます。
C1はB1-A1（セルの書式は時刻に設定）、
D1の計算式は
=FLOOR(C1,"00:15:00")/"1:00"
にしています。

上記勤務時間の場合、D1に1.25と出てほしいのですが
1.00になってしまいます。
15分きっかりの場合でも、0.25と表示させる計算式あるいは
設定をお教えいただけたらと思います。

No.4 ベストアンサー 回答者： nattocurry 回答日時： 2010/01/08 15:54

15分というのは、EXCEL内部では、1/24/60*15の計算値として扱われます。

割り切れない値なので、丸め誤差が生じます。
C1の計算結果は、表示的には、1:15でも、内部的には、1:14:59.99999みたいな値になっているとか、"0:15"の値が、内部的には、0:15.0000000001みたいな値になっている可能性があります。

A1もB1も、入力値は1分単位でしょうから、C1の計算結果も1分単位となりますよね。
D1の計算式の中で、C1の値に1秒足すと、望み通りの結果が得られますよ。

=FLOOR(C1+"0:00:01","0:15")/"1:00"

この回答へのお礼

ありがとうございました！
完璧に希望する結果を得られました。

お礼日時：2010/01/12 10:25

No.12 回答者： MackyNo1 回答日時： 2010/01/09 10:44

>手入力と同じ入力にするには別な方法として

B2に0:15と入力、
B3に=B2+"0:15" と式を入力し
B3を下へオートフィル

参考までに。

「B3に=B2+"0:15" と式を入力しB3を下へオートフィル」した場合は、正確には手入力した値と異なりますので注意が必要です。

たとえば、「=手入力のセル=オートフィルのセル」の数式では、すべての値で「TRUE」を返しますが、「=手入力のセル-オートフィルのセル=0」でより正確に大小関係を判定すると、「1：15」や「2：00」のところでは「FALSE」となり、2つの値は同じ値でないことを示しています。

簡単な数式の場合には、上記のようなオートフィルでも大小関係で誤った判定をすることがないのですが、数式内で時間の引き算をするようなケースでは、上記の問題が発生するので注意する必要があります。

この回答へのお礼

ご回答、ありがとうございました。
参考にさせていただきました。

お礼日時：2010/01/12 10:33

No.11 回答者： wisemac21 回答日時： 2010/01/09 08:32

式の誤記？

>=FLOOR(TEXT(C1,"h：mm")*1,"00:15:00")/"1:00"
B2に　=FLOOR(TEXT(A2,"h:mm")*1,"00:15:00")

手入力と同じ入力にするには別な方法として
B2に0:15と入力、
B3に=B2+"0:15" と式を入力し
B3を下へオートフィル

この回答へのお礼

ご回答、ありがとうございました。
参考にさせていただきました。

お礼日時：2010/01/12 10:33

No.10 回答者： MackyNo1 回答日時： 2010/01/08 23:52

補足情報です。

時刻などのデータを連続データとして入力するときにオートフィルすることがよくありますが、この場合にも今回と同じ丸め誤差の問題が発生します。

たとえば、Ａ列に「0:15」「0:30」と入力し、この2つのセルをオートフィルコピーすると15分ごとのデータを作成できますが、実際には15分ごとのデータにはなっていません（30分と15分の差を基準に計算しているため）。

実際、Ｂ列に「0:15」「0:30」「0:45」・・・と入力して、C2セルに「=A2=B2」と入力して下方向にオートフィルすると「1:15」「2:00」などでFALSEとなるので同じ値でないことを示しています。

一方、D列に「=A2-B2=0」と同じ意味の数式を入力すると、「1：00」のところではA列とB列は等しいはずなのに、引き算した結果は本当は０ではないことを示しています（正確にはD列の引き算で比較するほうが正しいのですが、数式によってはC列の値結果を基準にしてもOKの場合もある）。

しかし、実際に引き算した結果をE列に計算すると「０」となり、エクセルが誤差を自動的に修正していることがわかります（2進数で表示できない「１時間」を24倍にしたときに「１」となるのと同じようなことが起こっています）。

しかし、「3：15」のようにC列が「TRUE」D列が「FALSE」の組み合わせでも実際に引き算した値が０になっていない場合もあります（誤差が修正できない大きさ？）。

一方、「1：15」のようにどちらの演算結果もFALSEになっているものは実際に引き算した値も０になっていません。

また、2進数で表示できる値、すなわち「1:30」「3:00」「4;30」「6:00」になる部分では、エクセルが自動的に誤差を修正し、オートフィルした場合でも手入力の値と同じ結果になります（整数化した時と同じ）。

以上のように、エクセルが内部的に複雑な修正処理を行っていますので、数式を作成する場合、特に引き算が関与する数式で大小関係を判定するような場合は、この誤差を考慮する必要があります。

これらの誤差に簡便に対応するには、先に回答した微小値での補正か、以下のTEXT関数を使って計算結果の誤差を補正する（手入力と同じ結果にする）方法があります（もちろん整数化で対応することもできますが、数式を変更する必要があるので、簡便ではありません）。

=FLOOR(TEXT(C1,"h：mm")*1,"00:15:00")/"1:00"

この回答へのお礼

ご回答、ありがとうございました。
参考にさせていただきました。

お礼日時：2010/01/12 10:34

No.9 回答者： chie65535 回答日時： 2010/01/08 16:49

追記。

数学的に言えば
(B1-A1)*96
と
B1*96-A1*96
は等しいです。

しかし、残念ながら「(B1-A1)*96とB1*96-A1*96では、誤差の大きさが違う」ので「等しくはない」のです。

例えば
A1⇒17:00
B1⇒18:15
C1⇒=(B1-A1)*96（書式は標準）
D1⇒=B1*96-A1*96（書式は標準）
E1⇒=C1=D1
としてみましょう。

C1とD1が等しいなら、E1には「TRUE」と表示される筈です。しかし結果は「FALSE」です。

なんと「数学的に等しい筈の式が、Excel的（と言うか、コンピュータ的）には等しくない」のです。

どのくらいの誤差があるかは、F1に
=C1-D1
と入力してみると判ります。

F1に
-7.1054273E-15
と表示され「10のマイナス15乗くらいの誤差がある」のが判ります。

この回答へのお礼

ご回答、ありがとうございました。
参考にさせていただきました。

お礼日時：2010/01/12 10:34

No.8 回答者： chie65535 回答日時： 2010/01/08 16:31

Excelで時刻・時間を扱う場合「そのまま足し算や引き算するのは禁物」です。

今回の場合、15分単位ですから「15分が整数になるように、96倍してから計算」しましょう。

C1は「=B1-A1」とします（このC1は誤差を含みます）

D1は「=INT((B1*96)-(A1*96))/4」とします。

D1では「誤差を含んだC1を使用せず、96倍して誤差が出ないようにした数値同士を引き算」します。

また、"1:00"で割ると、それだけで誤差が大きくなるので、4で割ります。

"1:00"で割るのは「1/24」で割る、つまり、24倍するのと同じですから、これは「*24」でも同じです。

元の計算式が「96倍してある」ので「24倍」にする為には「96/24=4」で、結局「/4」になっています。

また「FLOOR(??,"00:15:00")」は「FLOOR(??,1/96)」と同等ですが「1/96で切り捨てすると、誤差で切り捨てし過ぎる」ので「FLOOR(??*96,1)/96」の方が誤差が減ります。

さらに「FLOOR(??,1)」は「INT(??)」と同等です。

元の式から変形を重ねると
FLOOR(??,"00:15:00")/"1:00"
↓
FLOOR(??,"00:15:00")*24
↓
FLOOR(??,1/96)*24
↓
FLOOR(??*96,1)/96*24
↓
INT(??*96)/96*24
↓
INT(??*96)/4
となります。

その結果、D1は
=INT((B1*96)-(A1*96))/4
と言う式になる訳です。

この回答へのお礼

ご回答、ありがとうございました。
参考にさせていただきました。

お礼日時：2010/01/12 10:35

No.7 回答者： minosennin 回答日時： 2010/01/08 16:27

誤差の原因はすでにご回答にあるとおりです。

某テキストに紹介されている解決法です。

=TIME(HOUR(C1),FLOOR(MINUTE(C1),"15"),0)

この回答へのお礼

ご回答、ありがとうございました。
参考にさせていただきました。

お礼日時：2010/01/12 10:32

No.6 回答者： MackyNo1 回答日時： 2010/01/08 16:04

＞C1はB1-A1（セルの書式は時刻に設定）、

＞D1の計算式は
＞=FLOOR(C1,"00:15:00")/"1:00"
＞にしています。

小数点以下の数値の計算におけるエクセルの丸め誤差が発生するパターンです。
すなわち、Ｂ１とＡ１の値が2進数で表せない数値の場合、これらを引き算したとき、有効数字以下の桁数部分でちょうど「0：15：00」に相当する数値よりもわずかに小さい値になったとき（わずかに大きい値になる場合は問題ありませんが）、正確には15分未満の値となりますので、今回のように切り捨てられてしまうという問題が発生します。

解決策は、以下のようにＣ1の部分に微小値を加えて、切り捨て処理するのが簡単です。

=FLOOR(C1+10^-10,"00:15:00")/"1:00"

この回答へのお礼

ありがとうございました！
微小値を加えるという方法があるのですね。
たいへん参考になりました。

お礼日時：2010/01/12 10:28

No.5 回答者： passes 回答日時： 2010/01/08 15:57

こんな感じでも。

=INT(C1*24*4)/4

この回答へのお礼

ありがとうございました！
参考にさせていただきました。

お礼日時：2010/01/12 10:32

No.3 回答者： gyouda1114 回答日時： 2010/01/08 15:54

お試しを

D1に　=CEILING(C1,"0:15")*24
表示形式を標準にする。

この回答へのお礼

ありがとうございました！
参考にさせていただきました。

お礼日時：2010/01/12 10:31