Excel 计算缺陷与大数计算

Excel 很多时候可以当作一个简易的数学计算程序，代替 Mathematica 或者 Matlab 之类的专业软件进行一些不算太复杂的数值运算。但 Excel 的数据处理存在很多弱项，在遇到时需要相应作一些处理。

问题一：有效位数大约只有 15-16 位，更多的位数只会用 0 填充了。

精确计算的 2ⁿ 的尾数不会是 0，始终是 2→4→8→6→2…… 的循环，但从截图上可以看到，Excel 在计算 2⁵⁰ 时，就遇到了有效位数问题，使得末尾出现了数字0。

关于问题一的应对：

从例子中可以看到，Excel 提供了 15 位的精度，这意味在在『千万亿』这个级别上 Excel 依然可以进行精确的计算。相当于以小数点后 4 位精度，即 0.0001 元 = 0.01 分 的精度下，处理九千亿人民币以下的财务数据。处理全国 GDP 的数据也可以精确到分，以米为精度可以让光跑一个月，以毫秒为精度覆盖三万多年。

但是如果你真觉得不够，就需要自己用公式实现进位，使用多个单元格作为『数字段』，来确保每个单元格内的数字长度不超过 15 位。

以 2ⁿ 为例，其计算由两部分组成：

最右一列公式为：

Z1=2
Z2=TEXT(RIGHT(Z1*2,12),"000000000000")
Z3=TEXT(RIGHT(Z2*2,12),"000000000000")
Z4=TEXT(RIGHT(Z3*2,12),"000000000000")
……

其中，Right 函数保证每个单元格只取结果的最右 12 位，让精度始终符合 15 位上限的要求。而 Text() 函数则保证当截取 12 位数字时，不会将原来在中间位置的 0 因为截取而成为首位 0 消失掉。例如，2⁶³ = 461,1686,0092,1369,3952，当截取 12 位时，会获得0092,1369,3952，如果不通过 Text() 函数保存首位的 0，则最后合并回去时就会产生错误。

左边每一列的公式均为：

X2=TEXT(RIGHT(Y1*2+IFERROR(VALUE(LEFT(Y1*2,LEN(Y1*2)-12)),0),12),"000000000000"); Y2=TEXT(RIGHT(Y1*2+IFERROR(VALUE(LEFT(Z1*2,LEN(Z1*2)-12)),0),12),"000000000000")
X3=TEXT(RIGHT(Y1*2+IFERROR(VALUE(LEFT(Y2*2,LEN(Y2*2)-12)),0),12),"000000000000"); Y3=TEXT(RIGHT(Y1*2+IFERROR(VALUE(LEFT(Z2*2,LEN(Z2*2)-12)),0),12),"000000000000")
X4=TEXT(RIGHT(Y1*2+IFERROR(VALUE(LEFT(Y3*2,LEN(Y3*2)-12)),0),12),"000000000000"); Y4=TEXT(RIGHT(Y1*2+IFERROR(VALUE(LEFT(Z3*2,LEN(Z3*2)-12)),0),12),"000000000000")
                                                                              ……;                                                                               ……

这个公式同时适用于左边任意多列，使得只要电脑性能过关，尽可用尽 Excel 的所有列（一共 16384 列）。

公式略复杂，以 Y2 为例：

Y2=TEXT(RIGHT(Y1*2+IFERROR(VALUE(LEFT(Z1*2,LEN(Z1*2)-12)),0),12),"000000000000")

最外层 Text() 依然是为了保留首位 0。Value(Left()) 用于提取右边列的进位数字，即当前列的右侧列如果出现超过 12 位的数字时，则截取头部进到本列。Iferror() 用于检测是否进位。将进位数字和本列上一行数据乘二的结果相加后，再检测是否本列也多于 12 位，如果多则截取。公式引用关系如下：

使用类似思想，可以精确进行一次数值变化不超过 10¹⁴ 的大部分大数计算。需要注意的是，假如一次数值变化较大，则每单元格所能保留的位数就相应变小，不一定是 12 位了。

应对问题一的要点有二， 一是自行实行截取与进位，二是利用 Text() 公式特性，保留截断后的首位 0 不丢失。我通常把这种处理办法称为『大数多列化处理』。

问题二：数值上限大约在 2¹⁰²⁴-1，由于有效位数限制，实际上限更小一点，大约在 2¹⁰²³+2¹⁰²²+……+2⁹⁷¹ ≈ 1.7977e308 左右。

这与问题一不同点在于，这个问题不关注精确展开，而更关注公式计算过程中的上限值。当然，使用问题一中的办法也能解决本问题中的部分情况，但对于更大的数字，例如用尽 Excel 所有列（16384列）也写不下的数字，大约 10^16384*14 = 10²²⁹³⁷⁶，问题一中精确展开的解法就无能为力了。况且在实际展开中，在装满 Excel 前就早早会遇到内存和 CPU 瓶颈了。问题二的解法注重于在有限的计算资源下计算尽可能大的数字。

我们以计算 361 的阶乘为例，如果使用 Excel 公式直接输入 =Fact(361) 则只会得到一个 #NUM! 的结果。意即该计算的值或者计算过程中已经出现了超过 Excel 单元格所能容纳的最大值。

关于问题二的应对：

我们在 Excel 中准备三列数字，A 列为从 1-361 的展开。C1 公式为 =FLOOR.MATH(LOG10(A1))，B1 公式为 =A1/10^C1。

从 C2 起公式为：

C2=FLOOR.MATH(LOG10(B1*A2))
C3=FLOOR.MATH(LOG10(B2*A3))
C4=FLOOR.MATH(LOG10(B3*A4))
……

从 B2 起公式为：

B2=B1*A2/10^C2
B3=B2*A3/10^C3
B4=B3*A4/10^C4

于是形成如下形式：


即 B C 两列形成了类似科学计数法的 b × 10 ^c 的数列。但不同之处在于，C 列的所有值全部相加，才是整个计算过程的最终解，如图：

即：361! = B361 × 10 ^SUM(C1:C361) = 1.43792325888489 × 10⁷⁶⁸，和上一篇博客对照一下，结果还是很精确的。

仔细观察 B、C 两列数值，其实原理就是每当一个新的 A 乘进来，都对结果作一次科学计数法处理，形成 b × 10 ^c 结构，确保每一次都有 1<b<10，然后把乘方 c 扔在一边最后再相加。

这一解法的关键在于在计算的每一步都即时处理，避免单元格数字过大而『爆掉』。通过这种方法，Excel 的可计算数域范围从大约 10³⁰⁸ 变大到了大约 10^{1,0000,0000,0000,0000}，更大的数字则会产生 10 ~1000 倍甚至更大的误差。但如果对 C 列的数字再作问题一解法中的多列化处理，则可计算数上限大约会变成 10^10229348 ，这里被迫用了幂的幂。这个数字相当大，并且实际不可能用到。早在这个极限之前，你的电脑内存估计就会挂掉。

当然，因为有 VBA，Excel 理论上也可以做复杂大数字计算，但考虑到学习成本和应用场景，不如学习 Mathematica 来得方便了。一般使用 Excel 做计算，都仅限于操作单元格和自带公式可以解决的问题。