LibreOffice CalcとExcelの計算結果誤差をみなさんで検証していただいた結果まとめ

LibreOffice CalcとExcelでフィボナッチ数列を計算すると結果に違いが生じたそうです。 丸め誤差の処理の差では?という予想はつきましたが、調べられなかったので検証をお願いをしたところ、いろんな事がわかったのでまとめました。 最後、奥村先生の話の中で、以前のExcelの誤差の丸め方が少し違うようなので注意が必要そうです
90
LibreOffice日本語チーム @LibreOffice_Ja

これ、誰か検証していただけませんか? この方が書かれている「結果の信頼性はExcelに分があるように思う」も、なぜそう思われたのか理由も含めて調べるには時間がかかりそうなので(中の人は数学苦手…) twitter.com/haou13fri/stat…

2019-06-18 23:06:42
検証の発端となったツイート
寿限無 @haou13fri

一般項によりフィボナッチ数列を計算すると、LibreOffice Calc では、ちょうど25番目で本来の結果とのずれが生じた。しかしExcelだとExcel2000と2019で試したが、いずれも結果にずれは生じなかった

2019-06-17 13:15:12
寿限無 @haou13fri

方法。セルA1に1、B1に数式 =(((1+SQRT(5))/2)^A1-((1-SQRT(5)/2)^A1)/SQRT(5) を入れる。A2に2、B2はB1のcopyを入れ、A3とB3以下は上からドラッグする。

2019-06-17 13:15:12
寿限無 @haou13fri

結論。やはりそれぞれのOfficeソフトウェアに一長一短があるので、使い分けるしかない。一方は他方の代わりにはならない。結果の信頼性はExcelに分があるように思う

2019-06-17 13:15:12
検証していただいたツイート
bug @hiddenbugs

@LibreOffice_Ja LibreOffice 6.4 nightly(x64), Excel 2016 で =1/SQRT(5)*(((1+SQRT(5))/2)^A1-(((1-SQRT(5))/2)^A1)) は共にA1が25のとき75025になりました。 なお、式はWikipediaを見ながら修正しています。 1/SQRT(5)が乗算されていなかったのと、 (1-SQRT(5))/2ではなく(1-(SQRT(5)/2))となる式になっていた

2019-06-18 23:36:22
K.Shirakata @argrath

@hiddenbugs @LibreOffice_Ja 75025になっているセルの書式設定で小数点以下15桁とかにするとLibOは10桁目に1が出てきますね。Excelは0です。

2019-06-19 00:16:31
bug @hiddenbugs

@argrath @LibreOffice_Ja こちらでも再現しました。>1が出てきますね。

2019-06-19 00:28:03
K.Shirakata @argrath

LibOの実数演算で誤差が出るやつ、出ること自体は仕方ないにしても小数点以下15桁とか表示させたときに10桁目に誤差が出てしまうのはちょっといけてないな…。long doubleで扱うべきところがどこかでdoubleになってるとかなんだろうけど。

2019-06-19 00:48:15
K.Shirakata @argrath

まあ実数演算の誤差のやつはp5pでもよく話題になってるし難しいよなあ…。

2019-06-19 00:56:21
いさな @jo3emc

@hiddenbugs @argrath @LibreOffice_Ja Excelは手元になくて比較できませんが、LibOの状況は再現しました(Win7 32bit)。(haou13friさん例示の数式は転記ミスの模様。) 余談ながらTclとPythonでも試してみたところLibOと概ね同じ挙動でした。Cは今環境がなくて試せてません。要は2進数の丸めの扱いですね…。 pic.twitter.com/UwgNZ8kxVC

2019-06-19 02:50:22
拡大
いさな @jo3emc

@hiddenbugs @argrath @LibreOffice_Ja 逆に言うとExcelで整数値が帰ってくる方がおかしいとも…? あるいはDecimal計算がデフォルトになってるのか…。

2019-06-19 02:52:39
Chuui @es_chuui

@jo3emc @hiddenbugs @argrath @LibreOffice_Ja Excel Onlineで試してみました。確かにn=25でも誤差が生じないです。n=74で漸化式計算側も精度の情報落ちが生じますが、逆にいうとn=1~73まで正しく整数で答えが出ていることが確認できました。(確認のため小数点以下を30桁表示しました。実際の内部の演算精度はそこまでないと思いますが。) pic.twitter.com/5oU7uHDZuy

2019-06-19 07:03:42
拡大
拡大
Chuui @es_chuui

@jo3emc @hiddenbugs @argrath @LibreOffice_Ja (考察1) 一般に浮動小数点の演算はIEEE754の規格を使う。 多くのプログラム言語では倍精度(64bit)を使うが、他にも倍々精度・拡張精度・拡張倍精度が定義されており、IntelCPUのFPUは対応している。Excelはそれを使っているのか? (考察2) 数式処理で、なるべく誤差がでない計算をしているのか? 続く

2019-06-19 07:29:36
Chuui @es_chuui

@jo3emc @hiddenbugs @argrath @LibreOffice_Ja (考察3) 独自の浮動小数点のフォーマットをプログラム用意する方法とか? だが、FPUが対応していない浮動小数点書式を扱うのは、さすがに演算速度が厳しいのでは?(整数ならともかく) (考察4) IEEE754(浮動小数)の丸め指定で、なるべく端数がでないのを選んでいるとか? う~む、詳しい方教えて!

2019-06-19 07:31:01
Excelの丸め誤差について

神戸大の中澤先生と三重大の奥村先生の話の中で、Excelの数字の丸め方が特殊?であるという話が出ました

中澤 港%人類生態学者@神戸大学 @MinatoNakazawa

浮動小数点演算の丸め誤差が出ない方が特殊な処理だと思う。Rで A <- 1:25; B <- (((1+sqrt(5))/2)^A-(((1-sqrt(5))/2)^A))/sqrt(5); C <- numeric(25); C[1] <- 1; C[2] <- 1; for (i in 3:25) { C[i] <- C[i-2] + C[i-1] }; B==C とやると4項目からFALSE twitter.com/LibreOffice_Ja…

2019-06-19 08:19:00
中澤 港%人類生態学者@神戸大学 @MinatoNakazawa

確かにoptions(digits=20); Bとやると,4項目が3.0000000000000004となっている。25項目は誤差が5e-11を初めて超える項。ちなみにLibreOffice Calc 6.2.4.2 (x64)では,小数点以下16桁表示で10桁目に1が出るがB=CはTRUEを返す。 pic.twitter.com/YJUKURp4IL

2019-06-19 08:26:57
拡大
Haruhiko Okumura @h_okumura

昔調べたことですが,Excelの不動小数点演算はおかしな(まずい)ことをしているようです oku.edu.mie-u.ac.jp/~okumura/softw… twitter.com/MinatoNakazawa…

2019-06-19 09:22:07
中澤 港%人類生態学者@神戸大学 @MinatoNakazawa

@h_okumura ありがとうございます。やはりExcelの処理が特殊なのですね

2019-06-19 09:40:53
中澤 港%人類生態学者@神戸大学 @MinatoNakazawa

@LibreOffice_Ja 引用RTでRでの計算結果から,Excelの方が特殊な処理をしているのではないかと書いたら,三重大学の奥村先生がコメントくださり twitter.com/h_okumura/stat… かつて検証されたページを教えてくださいました。

2019-06-19 09:43:30
元ツイートの方の検証
残りを読む(34)

コメント

エリ・エリ・レマ・サンバディトゥナイ @mtoaki 2019年6月19日
「特殊な計算」は1を掛けたり括弧で囲むだけでキャンセルされるようなので、この場合関係ないような気がする。
0
シナモン @cinnamonP 2019年6月19日
丸め誤差と言っても一般事務で問題になるレベルではないので、倍精度が必要な場面だけ丸め誤差が出ることを考慮する程度で十分でしょう
4
SAKURA87@多摩丁督 @Sakura87_net 2019年6月19日
エクセルのマニュアルには「色々計算して誤差がなるべくないようにしてる。まぁそれでも誤差が出るときは許してちょんまげ(意訳)」という記述があるのでエクセルの仕様通りの動きではある。この辺がやはり「事実上標準な表計算ソフトとそれっぽい何か」の違いなんだろうな。
12
lonngfa @lonngfa 2019年6月19日
こういうのがあるから、「Excelの計算結果は絶対! 検算なんて意味ない!」という主張を見ると、生暖かい目になってしまう。
3
@D9hV1M7YOI3PZCj 2019年6月19日
その辺の計算したいならMathematicaとか使うのがいいんかね
4
kartis56 @kartis56 2019年6月19日
表計算ソフトに小数点以下があるような計算やらせるのが間違ってる
17
Es-Dur @das_Es_Dur 2019年6月19日
sqrtなど、一定以上細かくなる結果が重要になる除算を含む式をExcelで扱ってる時点で敗北感があるし、丸め誤差が生まれるような処理と、ほとんど整数の世界で閉じてる世界の区別も付けられない文系はどうしようもないな
6
Es-Dur @das_Es_Dur 2019年6月19日
永遠に関数電卓で検算やってりゃいいんじゃないですかね。はっきり言うが、馬鹿が「検算が必要だ」と言ってるシチュエーションで "こういうの" ってまず起こりえないです。
32
01_02_05 @01_02_05 2019年6月19日
エクセルの関数はそもそもが銀行丸めだから、知らずに使うと普通の決算書レベルの計算でも値が変わるシチュエーションはあるよね。
2
rice_of_sato @gohan_of_sato 2019年6月19日
ネジにトンカチ使ってるようなもんでしょ
1
ゆーき @yuki073 2019年6月19日
lonngfa このレベルだと普通の電卓のほうが精度が悪いですよ。
13
SAKURA87@多摩丁督 @Sakura87_net 2019年6月19日
das_Es_Dur 検算が必要というのはどちらかと言えばエクセルではなく作業した人が信用されてないんや。エクセルが何重にもチェックをして安易にエラーが起きないような仕組みにしているシートで全てを躱して計算ミスをやらかす奴がいるから検算させるんだ。
1
飛鷹隼 @junhiyoh 2019年6月19日
LOTUS 1-2-3はどうなんじゃろ……(軽いしコンパクトだし昔のシート資産が山ほどあるので何のかんので未だExcelと併用で使ってる人
1
takatakattata @takatakattata1 2019年6月19日
たまーに陥るんだよね。 なんか端数が合わないとか。
0
ばびっと2号 @_Babbit2 2019年6月19日
+DEC2HEX(29/100*100) が1Cになるのは何とかしてほしい。ちなみにカッコ内が誤差で28.9999...になっている等ではなく、+DEC2HEX(FLOOR(29/100*100,1))は1Dになる。
0
寿限無 @haou13fri 2019年6月19日
あ?なるほど $ ruby -v ruby 2.5.5p157 (2019-03-15 revision 67260) [x86_64-linux-gnu] sqrt5 = 2.23606797749979 for a in 1..25 b = (((1 sqrt5)/2)**a-((1-sqrt5)/2)**a)/sqrt5 puts (b) end
0
寿限無 @haou13fri 2019年6月19日
これの結果がこうよ 1.0 1.0 2.0 3.0000000000000004 5.000000000000001 (中略) 46368.00000000004 75025.00000000006 LibreOfficeの結果は75025.0000000001だったので、最小の桁を四捨五入してると思われる。 検算してわかったが、実はこれで合ってた。
0
RGB000 @19666_61 2019年6月20日
浮動小数点演算は沼だなぁ
1
山吹色のかすてーら @sir_manmos 2019年6月20日
pc98のMicrosoft Basicでsqrt(36)が6にならなかったことを思い出す。これは内部でsqrt(x)を4byte のfloat でexp(log(x)/2)やってるのが原因だったよう。Newton法かなんかの別関数作ればよかったんだろうけど、8bit時代にメモリが足らなかったからかな?16bitになったんだから(収束速度も速いし)それくらい作ればいいのにと思った。
0
はやし・しのぶ @Hirarinmac 2019年6月23日
junhiyoh 小数点2桁程度の計算を伴う計算式(1枚単位を出す必要があるので)をExcelとLotus Approachの併用で使ってたけど、計算結果が数円単位で微妙に違うのはなんでなんやろ、とは思ってた。ちなみに知ってる会社のWindows7→Windows10移行の関門が「Win10でLotus1-2-3が使えるかどうか」というのはうん、まぁ。
0