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 为需要填序号的单元格。程序先定义一个变量用来作为页码，然后不断用它替换指定单元格中的值，每替换一次就打印一张。这一方法硬要说有什么缺点的话，大概也就是打印任务数比较多，可能在几千份时会占用不少内存。
更完整的模板还需要在界面上做个按钮和两个输入框，以便让完全没有宏能力的财务也能定义起始和结束页码并打印。但今天这需求仅仅是帮助打印而不是做个新模版，所以这任务也就完成了不再搞复杂了。
样例下载 模板-日常费用报销表