エクセルの計算

質問

エクセルで計算するときに、
Ｍｏｄ（84，３）　で計算すると正しく０を返してくれるのですが、
Ｍｏｄ（Ｃｏｍｂｉｎ（9，3），３）で計算すると３と間違った値を返してきます。

何故でしょうか。

回答No.2

中村 拓男（@tknakamuri） · ベストアンサー

本当ですね。ちょっと驚き。

動きとしては 84 よりほんのわずか小さな値を
ComBinが返しているみたいですね。

83.99999999999999999999999

というかんじ。

これの 3 の剰余は Excel の Mod の仕様では

2.999999999999999999999999999

なので、 3 と表示するのだと思われます。

Mod(Round(Combin(9,3),0),3)

なら大丈夫でした。

ただ、Combin(9,3)　= 84 は True を返します。
ひょっとすると表示と同程度に丸めてから比較しているのかも。

バグっぽいですが、整数と浮動小数の区別のないExcelで
整数を扱うのは要注意みたいですね。

回答No.5

中村 拓男（@tknakamuri）

せっかく数学カテなので、誤差が入りにくい計算法を調べてみました。
私の知っていたのは、2項展開してゆく方法ですが、よく調べてみたら
こんなシンプルな方法がありました。

例えば 7C3 = (5x6x7)/(1x2x3) ですが、分母と分子を別々に計算するのは
これぐらいなら簡単ですが、mCn の m, n が大きくなってくると、分母と分子が
巨大化して困難です。

でも

5÷1×6÷2×7÷3

という順番で計算すれば、計算の途中結果があまり大きくならず、誤差が入りません。
つまり分子と分母の各数を小さい順に交互に掛けて割ればよいだけです。このとき
割り算は常に割り切れます。

なぜ、割り算が常に割り切れるかの証明は、数学のよい練習問題だと思います(^^;

回答No.4

中村 拓男（@tknakamuri）

>コンピュータが2進法であるかぎり避けられません。

いえ、この件に関してはさけられます。IEEE754形式では不動小数点でも誤差なしで非常に大きな整数が保持可能ですし（2^53まで)、入力に整数と仮定し、結果が整数を正確に表せる上限を越えない範囲で、combinationを誤差が入らないように実装することは可能です。
MSさんは実装をちょっとさぼったようですね。

回答No.3

sporespore

EXCELがデータをメモリに入れるときは2進法で入れます、これを2進法で計算して元に戻すときにごくわずかなエラーが出てこれが今回の不思議な現象を起こします。知られている問題ですが、コンピュータが2進法である限り避けられません。
答えが3みたいですが、小数点以下を表示すると2.9999999999999となっていることが分かりますでしょうか。つまり、Modの中のCombin(9,3)が正しく整数を内部で認識していないということです。従ってCombin(9,3)が整数と指定すればいいのでＭｏｄ（Int(Ｃｏｍｂｉｎ（9，3）），３）とすれば正しい答えになります。
この、Excelのエラーについては簡単に見ることができます。
A1に5000.1と入力、A2に-4999.82と入力、A3に-0.28と入力し、A4に=Sum(A1:A3)として、小数点以下の桁数を３０桁位見える様にしてみてください。すべてゼロではなくてはならないのに数字が見えますよね。これが今回のエラーの原因です。

回答No.1

hashioogi

私のエクセル（2010）では"=MOD(COMBIN(9,3),3)"は3になりますけど…。