Excelの時刻の不思議

A1とB1に 「0:30」が入っています。それぞれ別の集計方法で出た数字です。

しかし、A1=B1　で　Falseという結果が出ました。

シリアル値を確認すると以下のように相違するのが確認できました。

A1：0.0208333333333333
B1：0.0208333333333334

末尾の3と4の違いは何でしょうか？
また時刻形式などシリアル値を理解してない人にも伝わる形で、同一でないことを示せる表示形式などないでしょうか？
例えば、秒の桁数を増やすとか。

同一時刻であることをTRUEにてチェックしたいのにFALSEが出ると困るので、TRUEになるための関数などでの加工はできないでしょうか？
Excelでなく手動で計算するといずれも「0時間30分」なので結論は同一ですが、
Excelの都合上、相違してしまっているのは分かりますが、実際の時間計算上狂わさせずに同一にまるめたいです。

No.12 回答者： grumpy_the_dwarf 回答日時： 2024/09/26 10:35

> 日付にも時間にも桁区切り数値にも表示させられるし、

> 計算にも使用できてるのに文字列にしている、、、分かりづらいですね。

それはtext関数とは別に、Excelが数字の並んだ文字列を勝手に数値に変換する場合としない場合があるわけです。sum関数やsumproduct関数やaverage関数は変換せず無視するけど、max関数やmround関数は勝手に数値に変換します。text関数の結果が数値じゃないのはisnumber関数で確認すればちゃんとFALSEになるので明確です。

No.11 回答者： mike_g 回答日時： 2024/09/19 13:47

》 A1とB1に 「0:30」が入っています

》 末尾の3と4の違いは何でしょうか？

A1とB1は誰が入力しましたか？
貴方がそうなら、A1とB1の入力の違いは何でしょうか？

No.10 回答者： xs200 回答日時： 2024/09/12 13:36

microsoftのページから引用します。

The TEXT function lets you change the way a number appears by applying formatting to it with format codes.
書式を設定して表示を変更する。

文字列の定義が#9とは違うようです。
文字列どうしの足し算はできますか?
='1+'1
これはエラーですが
=TEXT(1,"#")+TEXT(1,"#")
は2です。これに1を足せば(=TEXT(1,"#")+TEXT(1,"#")+1)、3になります。text関数の結果が文字列だというならこれらはエラーになるべきですがなりません。text関数の結果は数値です。

ただtext関数で変換してしまうとわけがわからなくなることがあるので、text関数の結果で計算するのは避けた方がいい(避けるべき)です。

たとえば1.236をtext(1.236,"#.##")とすると表示は1.24になります。これは文字列ではなく数値ですからこれに0.1を足すと1.34になります。表示されなかった桁は消えてしまいます。roundと同じです。

ということでtext関数の結果は文字列ではありません。

この回答へのお礼

ありがとうございます。
計算のできる値にはできるけど、
厳密には「丸められてはいない」(見た目の表示に騙されてはいけない)
という事ですね。

お礼日時：2024/09/12 16:51

No.9 回答者： grumpy_the_dwarf 回答日時： 2024/09/11 16:55

xs200さんのNo.8の回答は間違ってるので読まなくていいです。

text関数の結果は「文字列」です。表示形式を適用して文字列を生成してますから、計算上の微小な誤差は表現されず消えてしまいます。ちゃんと桁の揃った文字列になってれば比較は可能ですよ。

mround関数との違いは、数値として比較しているか文字列で比較するかですから、mroundの方がマジメっぽいですね。

この回答へのお礼

text関数を調べると、確かに「テキストに変換する関数」とあります。
しかし、日付にも時間にも桁区切り数値にも表示させられるし、計算にも使用できてるのに文字列にしている、、、分かりづらいですね。
mroundじゃないといけない説明があると区別できる気がするのですが。。。

お礼日時：2024/09/11 17:01

No.8 回答者： xs200 回答日時： 2024/09/11 15:09

まだ締まってなかったので回答続き。

text関数は文字列を返す関数ではありません。表示形式を指定する関数ですから数値をtext関数に入れて時間表示にしても中身は数値のままです。12345をtext関数で"#,#"にすれば12,345という表示になった数値です。文字列ではありません。

No.6の繰り返しですが
a1に0:0:6, a2に0:0:5, a3に0:0:1
をいれてb1から順にtext(a1,"hh:mm:ss")とします。
=b1-b2-b3=0
とすればFALSEになります。text関数を使っても結果は同じことです。丸めるしかなく
=round(b1-b2-b3,5)=0
にすればTRUEです。

日付であれば整数ですから
2024/9/1をtext関数でyy/mm/ddにして計算しても正しい結果になります。
2024/9/1 =text(a1,"yy/mm/dd")
2024/9/2 =text(a2,"yy/mm/dd")
=b2-b1=1
はTRUEです。

等しいか判断するには数値を丸めて比較するしかありません。

No.7 回答者： grumpy_the_dwarf 回答日時： 2024/09/10 16:54

なにしろExcelの数値は15桁しか有効桁数がないので、10分が0.00694444444444444444……と無限に続くはずが15桁に丸められてしまって0.00694444444444444しかありません。

で、20分ならどうなるかというと、0.013888888888888888……と無限に続くはずがやっぱり丸められてしまって0.0138888888888889になるわけです。こういう誤差が微妙に蓄積したり打ち消しあったりした結果が質問の誤差ですね。

仕方がないので、たとえば1秒単位で丸めるなどしましょう。
=mround(元々の集計の数式, "0:00:01")
これで同じになるはずです。

この回答へのお礼

ありがとうございます。
セルの見た目が両方とも「0:30」であったとしても、
その結果になる経緯が、どういう足し引きが行われてたかによって、
・・・3333000
・・・3334000
になり得るって事ですね。

皆さんから教えて頂いた、丸める関数がmroundですが、
text関数でも良いんじゃないかと思い、違いがはっきり分かりません。
text関数は文字列だと仰りますが、"hh:mm:ss"の結果は、計算に使えるのでmroundじゃないといけない理由が未だ不明です。。。

お礼日時：2024/09/10 17:00

No.6 回答者： xs200 回答日時： 2024/09/09 12:46

>1.2-1.1-0.1=-1.38778E-16

が理解できません。

Exceで=1.2-1.1-0.1と入れればわかることです。0にはなりません。
ほかにも=4.8-4.7-0.1は-3.60822E-16になります。0にはなりません。

シリアル値の方がわけわからないと思いますが、たとえば
a1に0:0:6, a2に0:0:5, a3に0:0:1
をいれてa4に
=a1-a2-a3=0
とすればFALSEになります。6秒-5秒-1秒=0秒ではありません。

・余分な桁は捨てる
・誤差として影響のない数の差は無視する

に
・すべて整数で計算する
を加えておきます。
6-5-1なら必ず0です。秒未満がなければすべて整数で計算できます。

やればわかることなので理解できないではなくやってみてください。

Excelのバージョンは
Microsoft® Excel® 2021 MSO (バージョン 2408 ビルド 16.0.17928.20114) 64 ビット
でやってます。バージョンにより差があると思います。

この回答へのお礼

ありがとうございます。
実際にExcelで、
=1.2-1.1-0.1 を試してみて分かりました。
不思議ですね。
「1.2-1.1」の結果は「1.0」ですが所数のけたを増やしていくと
「0.999999999999」になりました。
Excelの問題でしょうか？

1.2-1.1-0.1＝0 にならない理由って説明可能なのでしょうか？

シリアル値は1900/01/01を基準としているって事で何となく分かった気ではいます。

お礼日時：2024/09/10 11:19

No.5 回答者： xs200 回答日時： 2024/09/06 11:03

桁数は有限なので誤差が出る前提で計算しないといけません。

丸めてない数のイコール計算なんてこわくてできません。

・余分な桁は捨てる
・誤差として影響のない数の差は無視する

たとえば
=1.2-1.1=0.1
これはFALSEです。1.2-1.1=0.1ではありません。
1.2-1.1-0.1=-1.38778E-16
これを説明すればシリアル値を理解していない人にも誤差がでるのは理解してもらえるのでは?
この例ではroundを使うか、1.2と比べて計算に影響のない0.01と比較するabs(1.2-1.1-0.1)<0.01とでもすればTRUEになります。

この回答へのお礼

すみません

1.2-1.1-0.1=-1.38778E-16
が理解できません。
シリアル値の方が分かりやすいと思ってしまうのですが、
何か気づかせてくれると嬉しいです。

お礼日時：2024/09/09 11:35

No.4 回答者： kigoshi 回答日時： 2024/09/05 12:43

> MROUND(A1,"0:0:1") → TEXT(A1,"hh:mm:ss") でも良いかなと思いました

これだと、数値ではなく文字列になってしまいます。
No.1の私の回答でVALUE関数でくくっているのは、数値化して、そのあとの計算にも使えるようにするためです。

この回答へのお礼

ありがとうございます。
私の方が理解が浅いのですが、

TEXT(A1,"hh:mm:ss") のセルが”0:30:00”となっている時
「＝そのセル*2」で"1:00:00"になりますし、
書式設定でシリアル値にもなりましたので、
文字列ではなく数値に思えるんですよね。

MROUNDやTEXTの結果はいずれも同様の計算が出来たのでイマイチ違いが分かりません。
また、文字列になっているセルを、日付や時刻や数値に変換するのがTEXT関数ではないでしょうか？

私の理解が不足していたら教えて頂けると幸いです。

お礼日時：2024/09/05 16:43

No.3 回答者： artoo 回答日時： 2024/09/05 01:25

計算で得た結果とのことなので、計算誤差の累積でしょう。

方法としては、
案1：秒単位に丸めるなどして同一にする
案2：細かい桁では異なることを時刻形式のままで、なんとしても相手に伝えたい場合は、ミリ秒とかマイクロ秒まで含んだ時分秒形式で10進数文字列化する。ミリ秒までなら、セルの表示書式設定で「h:mm:ss.000」にすればよいですが、マイクロ秒だとExcelの標準機能では無いはずなので独自に計算や文字列処理で作り出すしかないです。ミリ秒までだと、上記A1 B1の差は出ないですね。マイクロ秒でも無理かも。