Excel 计算缺陷与大数计算
Excel 很多时候可以当作一个简易的数学计算程序,代替 Mathematica 或者 Matlab 之类的专业软件进行一些不算太复杂的数值运算。但 Excel 的数据处理存在很多弱项,在遇到时需要相应作一些处理。
问题一:有效位数大约只有 15-16 位,更多的位数只会用 0 填充了。
精确计算的 2n 的尾数不会是 0,始终是 2→4→8→6→2…… 的循环,但从截图上可以看到,Excel 在计算 250 时,就遇到了有效位数问题,使得末尾出现了数字0。
关于问题一的应对:
从例子中可以看到,Excel 提供了 15 位的精度,这意味在在『千万亿』这个级别上 Excel 依然可以进行精确的计算。相当于以小数点后 4 位精度,即 0.0001 元 = 0.01 分 的精度下,处理九千亿人民币以下的财务数据。处理全国 GDP 的数据也可以精确到分,以米为精度可以让光跑一个月,以毫秒为精度覆盖三万多年。
但是如果你真觉得不够,就需要自己用公式实现进位,使用多个单元格作为『数字段』,来确保每个单元格内的数字长度不超过 15 位。
以 2n 为例,其计算由两部分组成:
最右一列公式为:
1 2 3 4 5 |
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 消失掉。例如,263 = 461,1686,0092,1369,3952,当截取 12 位时,会获得0092,1369,3952,如果不通过 Text() 函数保存首位的 0,则最后合并回去时就会产生错误。
左边每一列的公式均为:
1 2 3 4 |
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 为例:
1 |
Y2=TEXT(RIGHT(Y1*2+IFERROR(VALUE(LEFT(Z1*2,LEN(Z1*2)-12)),0),12),"000000000000") |
最外层 Text() 依然是为了保留首位 0。Value(Left()) 用于提取右边列的进位数字,即当前列的右侧列如果出现超过 12 位的数字时,则截取头部进到本列。Iferror() 用于检测是否进位。将进位数字和本列上一行数据乘二的结果相加后,再检测是否本列也多于 12 位,如果多则截取。公式引用关系如下:
使用类似思想,可以精确进行一次数值变化不超过 1014 的大部分大数计算。需要注意的是,假如一次数值变化较大,则每单元格所能保留的位数就相应变小,不一定是 12 位了。
应对问题一的要点有二, 一是自行实行截取与进位,二是利用 Text() 公式特性,保留截断后的首位 0 不丢失。我通常把这种处理办法称为『大数多列化处理』。
问题二:数值上限大约在 21024-1,由于有效位数限制,实际上限更小一点,大约在 21023+21022+……+2971 ≈ 1.7977e308 左右。
这与问题一不同点在于,这个问题不关注精确展开,而更关注公式计算过程中的上限值。当然,使用问题一中的办法也能解决本问题中的部分情况,但对于更大的数字,例如用尽 Excel 所有列(16384列)也写不下的数字,大约 1016384*14 = 10229376,问题一中精确展开的解法就无能为力了。况且在实际展开中,在装满 Excel 前就早早会遇到内存和 CPU 瓶颈了。问题二的解法注重于在有限的计算资源下计算尽可能大的数字。
我们以计算 361 的阶乘为例,如果使用 Excel 公式直接输入 =Fact(361) 则只会得到一个 #NUM! 的结果。意即该计算的值或者计算过程中已经出现了超过 Excel 单元格所能容纳的最大值。
关于问题二的应对:
我们在 Excel 中准备三列数字,A 列为从 1-361 的展开。C1 公式为 =FLOOR.MATH(LOG10(A1)),B1 公式为 =A1/10^C1。
从 C2 起公式为:
1 2 3 4 |
C2=FLOOR.MATH(LOG10(B1*A2)) C3=FLOOR.MATH(LOG10(B2*A3)) C4=FLOOR.MATH(LOG10(B3*A4)) …… |
从 B2 起公式为:
1 2 3 |
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 × 10768,和上一篇博客对照一下,结果还是很精确的。
仔细观察 B、C 两列数值,其实原理就是每当一个新的 A 乘进来,都对结果作一次科学计数法处理,形成 b × 10 c 结构,确保每一次都有 1<b<10,然后把乘方 c 扔在一边最后再相加。
这一解法的关键在于在计算的每一步都即时处理,避免单元格数字过大而『爆掉』。通过这种方法,Excel 的可计算数域范围从大约 10308 变大到了大约 101,0000,0000,0000,0000,更大的数字则会产生 10 ~1000 倍甚至更大的误差。但如果对 C 列的数字再作问题一解法中的多列化处理,则可计算数上限大约会变成 1010229348 ,这里被迫用了幂的幂。这个数字相当大,并且实际不可能用到。早在这个极限之前,你的电脑内存估计就会挂掉。
当然,因为有 VBA,Excel 理论上也可以做复杂大数字计算,但考虑到学习成本和应用场景,不如学习 Mathematica 来得方便了。一般使用 Excel 做计算,都仅限于操作单元格和自带公式可以解决的问题。
Excel 数组公式的概念解释
Excel 公式,本质就是输入原始数据,处理后再输出结果数据,放在公式的单元格里。
有些公式,输入是一个数据,输出也是一个数据,例如取整 int()、10 底对数 log()。若 A1=5.5,=Int(A1) 显示为 5。
有些公式,输入是一组数据,输出一个数据,例如 Sum。这一组数据整个是一个参数。若 A1:A5={1,2,3,4,5},输入公式 =Sum(A1:A5),显示为 15。A1:A5 数组是 Sum() 的一个参数。
有些公式,输入是两个参数,输出一个数据,例如 Match(A2,A1:A5)。两个参数,A1 是一个待查数据,参数二是被搜索的数组。
而数组公式,则是输入一组数据,输出一组数据。
以 Match() 为例,Match 公式的形式为 =Match(lookup_value, lookup_array, [match_type]),其中 第三参数 match_type (查询模式)在本文讨论中忽略。则本文讨论的简化为 =Match(lookup_value 待查询数值, lookup_array 被搜索数组)
可以看到,一个 Match 公式一次只能在 lookup_array 里查找一个数值。而把 Match 公式改写为数组公式,并用 Ctrl+Shift+Enter 确认以后,实际公式则变成了 {=Match( lookup_value_array, lookup_array, [match_type] )}。
在公式里,本来应该是单一数值的地方,被替换成了一个数组,待查询数值 变成了 待查询数组。则 Excel 会自动响应 Ctrl+Shift+Enter 命令,把该公式拆分成多次分别执行,每次取待查询数组里的一项,单独给出一个结果,然后循环到该数组里的每个元素都被查询一遍。
例如,选择 C1:C5 单元格并在公式栏中输入 =Match( B1:B5, A1:A10, 0 ) ,按 Ctrl+Shift+Enter 回车。Excel 会自动内部展开五次 =Match() 查询,每次查询在第一个参数位分别填入 B1 – B5。即在 A1:A10 中分别查找 B1 – B5 的值,查 5 遍,并把 5 个结果分别放在对应的 C1:C5 单元格里。
所以:
1. 因为往往有多个输出结果,使用数组公式需要先选择好输出位置,再在公式栏写公式,写完用 Ctrl+Shift+Enter 确认。注意,这多个单元格包含的是『一个公式』。
2. 数组公式需要你在写公式时,把『一个数据』的参数改写为『一组数据』。(例子中 Match() 函数本来的 lookup_value 即『需要查找的值』改成了『需要查找的数组』。)Excel 会自动循环这个改写数组里的每一个数据,然后把公式计算结果填到对应的单元格里。
3. 数组公式修改起来较为费劲,经常会出现『不能更改数组的某一部分』,正确的方法是先按 Ctrl+/ 全选该数组公式的整体占用位置,然后再在公式栏进行修改。
4. 某些公式,例如 Sum()、Len() 使用数组公式和直接使用该公式往往没有区别。所以如果你见到某个教程在以 Sum 举例讲数组函数时就不用往下看了。百度搜出来有不少是这样的。
5. 一般来说,常用的数组计算 Excel 都已经提供了特定的函数,比如 Logest()、Frequency 等。如果返回的值有两个以上的,也通常都拆成了多个公式,比如线性回归的 Slope()、Linest()、Steyx() 等。当需要多个计算结果时,也无需使用数组公式,使用 Excel 的公式复制粘贴就可以完成绝大部分工作。上文的例子即是如此,选择 C1:C5 然后输入 Match() 数组公式,和先在 C1 输入普通的 Match 公式 =Match( B1, $A$1:$A:$10,0 ),然后把公式复制到 C2:C5 上,效果是一样的,后续处理起来还方便一些。
那么数组公式有什么用呢?
大部分情况其实没什么用,确实没什么用,所以很多人用了好久也没用过数组公式。哲学点地说,等到你需要用数组公式时,数组公式就有用了。
数组公式的最大特点是『输出的是一个数组』,所以它需要用多个单元格才能放下,同时,它可以作为数组参数供其它函数使用。所以数组函数最大的使用场景是通过复杂嵌套函数,实现更大程度的 Excel 自动化。
例如,去除重复单元格,可以使用 Alt+A+M 的『删除重复项』实现,但这样意味着每次数据更新,都需要重新进行人工操作,当处理步骤较多时,往往意味着后续步骤也需要重新操作。而使用数组公式,则可以一劳永逸地解决这个问题。
1 |
{=INDEX(A:A,SMALL(IF(MATCH(A$2:A$20,A$2:A$20,)=ROW($1:$19),ROW($2:$20),4^10),ROW(A1)))&""} |
因为『删除重复项』本质上就是一个『输入一个数组,输出一个数组』的操作。在这个例子里,Match() 函数的第一个参数和两个 Row() 参数进行了相同的对应循环,并把每个计算结果填入相应的单元格里。
而另一个例子
1 |
{=Mid(A1,Row(1:100),1)} |
则相当于把 A1 单元格中的每个字符都单拆出来。辅以其它公式嵌套,可以比较方便地计算诸如『若干个单元格一共包含多少个特定字符』之类的问题。
一句话总结:当你在使用 Excel 时,需要处理『处理若干个数据,过程中包含若干数据,结果也是若干个数据』时,在宏程序之外,还可以考虑使用数组公式。
Excel 自动打印表格份数/序号
#Update:
改进了。现在的样式如下:
第一步,先做一张报销单:
从自动化的角度来说,尽量采用公式帮助计算、限定单元格可输入的内容、以及设置单元格格式。在这张单据里,编号单元格为 K1。
公式:
对于报销单而言,使用 =SUM() 和 =VLOOKUP() 通常就足够了,至多再加个 =SUMIF()、=COUNTIF() 就足以做出挺复杂的报销单了。
单元格格式:
选定单元格后 『右键→设置单元格格式』。标准的可以选择第三类货币格式,复杂的可以自行定义格式,使用#代表一个数位,使用0代表『当这个数位是最高位但为0时依然显示』。
限定填充序列:
选定单元格后菜单栏选择『 数据→数据验证→数据验证』。允许序列,来源直接写入需要的内容,用英文逗号分隔。
第二步,添加 Excel 的开发工具菜单:
依次点击菜单上的 『文件→选项→(跳出 Excel 选项窗口)→自定义功能区→☑开发工具』打钩。
第三步,设置打印宏:
点击菜单上的 『开发工具→Visual Basic』,打开 VBA 面板,左侧双击 『ThisWorkbook』,把以下代码粘贴到出现的窗口内,然后关闭窗口
注意 K1 为这篇文章中的编号位置,其它表单需要相应调整。
1 2 3 4 5 6 7 8 9 |
Sub PrintWithNumber() '定义宏名 Dim startnum, loopnum, N As Long '定义变量:起始编码,打印份数和循环值 loopnum = Application.InputBox(Prompt:="需要打印多少份?") '定义打印份数 startnum = Sheet1.Range("K1") '读入表格上的份数的起始值 For N = startnum To startnum + loopnum - 1 '开始循环 ActiveSheet.PrintOut '打印当前表格 Range("K1") = N + 1 '份数加一 Next N '下一个循环直到结束 End Sub '结束 |
第四步,添加宏按钮:
点击『开发工具→插入→按钮(窗体按钮)』。
然后用鼠标在 Excel 空白处拖出一个方块,当松开鼠标时,会自动跳出『指定宏』窗口,选择窗口内的『ThisWorkbook.PrintWithNumber』,然后确定。
右键点击该按钮,选择『编辑文字』,修改为『打印报销单』。
第五步,设置打印区域:
这一步是为了防止把按钮本身也打印出来。
全选需要打印的区域,选择菜单 『页面布局→打印区域→设置打印区域』。
第六步,另存为 xlsm:
由于使用了 vba,必须把文件存为 xlsm 格式。(启用宏的 Excel 格式)
完工。
今天接到一个小任务,寥寥一记。
在 Excel 中,常见的一种需求是打印带份数号码的各种表单,例如报销单、外出单等。通常财务都会提供模板给员工自行填写,再由财务按入帐顺序统一写上序号。但当某人一次性要写几十份,或者说类似的需求,需要一次性打印 N 份时,有没有办法让打印的表格上自动带上序号?
解决办法需要依赖 Excel 中的宏程序,在 Excel 中按 Alt+F11 可以直接打开宏编辑器。或需要先通过『视图』→『宏』→『录制宏』,再经由编辑宏的办法来打开。
核心宏程序如下,一目了然:
1 2 3 4 5 6 7 |
Sub test() Dim N As Integer For N = 1 To 10 Range("E1") = N ActiveSheet.PrintOut Next N End Sub |
E1 为需要填序号的单元格。程序先定义一个变量用来作为页码,然后不断用它替换指定单元格中的值,每替换一次就打印一张。这一方法硬要说有什么缺点的话,大概也就是打印任务数比较多,可能在几千份时会占用不少内存。
更完整的模板还需要在界面上做个按钮和两个输入框,以便让完全没有宏能力的财务也能定义起始和结束页码并打印。但今天这需求仅仅是帮助打印而不是做个新模版,所以这任务也就完成了不再搞复杂了。
样例下载 模板-日常费用报销表