提高EXCEL的运算效率.doc

提高Excel的运算效率 【编译自/】 【注：本文不是原文翻译，可能夹杂了译者的错误理解，请以原文为准】 计算次序 excel并不是按行列或固定的方式来计算的，相反，它是根据关系链来动态地决定其计算顺序。 计算次序分为两步：1.创建关系树（链），并标记“未计算”单元格；2.确定关系链的计算顺序，计算公式，并记住计算顺序，以便下次重算。 关于关系树（链）： 如果一个单元格的计算结果要依赖于另一单元格，则称二者有从属关系。 举例来说，A1=A2+5, A2=A3+1, A3=2 显然计算次序应是：A3—A2—A1，我们称A3向前引用A2，A2向前引用A1。（反之，也可说A1向后引用A2……，也称父关系、子关系，A3的父关系是A2，A2的子关系是A3) 单元格或名称改变后，即使重算方式设定为手动，excel也将重新创建关系链（或称关系树）。如果关系树都是在本工作表内的，其速度将大大快于跨工作表关系树的创建。不过在这方面excel2002比2000和97版有重大改进。 EXCEL在自动重算时，并不是对全部单元格进行计算，而是进行“聪明”的计算，即只选择对那些需要进行重算的单元格、公式、名称进行计算（包括刚刚输入或更改的单元格及其关系树上的所有单元格）。 但以下有例外： 2000以上版本打开时默认为自动重算，但当打开低版本的工作表时，会进行全部重算。 易失函数全部重算 按下“全部重算”键，强制重算（ctrl＋Alt＋F9） 如果关系链上的单元格大于65536 如果大量使用OFFSET, INDEX, LOOKUP之类的函数，且其引用范围较大，可能会导致关系链增大。 如果在代码中使用了worksheet.enablecalculation 没有使用的名称不会计算 如果名称所引用的单元格重算了，名称也会重算 如何控制计算 点击菜单－工具－选项－重新计算，我们看到了几种重算的方式。 需要注意的是：这里设置的选项，有些会使得以后打开的工作簿都具有相同的选项，见下面的说明（“关于设置的级别”）。 －自动重算：就是当单元格有变化时，自动进行所有工作表的计算（包括打开工作簿时）。如果是用不同版本EXCEL保存的工作簿，打开时会重算，因为它们的计算引擎是不同的。 －手工重算（F9或Ctrl＋Alt＋F9）：只有按下键时才进行计算，或保存时重算（如果勾选）。当excel认为需要重算时，会在状态栏出现“计算”的字样。 －除模拟运算表外自动重算： 全部计算是指对全部公式进行计算（按Ctrl＋Alt＋F9），重算是指对所有的单元格、名称、易失函数公式及其从属单元格的新建、改变后的计算（按F9）。通常情况下，重算要比全部计算快。在excel2002版本中，按Ctrl-Alt-Shift-F9可以进行带重建关系树的全部计算。 ――关于设置的级别 －应用级设置：包括重算方式的设置、迭代方式等。他们都将影响后续打开的工作簿。这些设置保存在每一个工作簿中。尽管如此，还是要注意：所有后续打开的工作簿的此类设置均服从最先打开的工作簿的设置，而忽略本身的原设置。 －工作簿级设置：这些设置为工作簿级设置，只对本工作簿起作用，而对同时打开的其他工作簿不起作用。包括：远程更新引用；以显示值为准；1904日期系统；保存外部链接；接受公式标志 易失函数 见 ： /dispbbs.asp?BoardID=3ID=108514 在该帖中，我们已经知道，OFFSET, INDIRECT, TODAY, NOW, RAND,CELL, INFO均属于易失函数；而根据测试结果，微软公布的易失函数ROWS, COLUMNS, AREAS, WEEKNUM却并不具有易失性；至于INDEX，在97版之后，就不再是易失函数。 除此之外，还有一些操作属于易失性操作，这些操作也都会触发自动重算。包括： －自动筛选：进行自动筛选时，会把筛选区域的所有公式标志为“未计算”； －单击或双击改变行高、列宽： 在自动计算的模式下，单击或双击行列分割线改变行高、列宽的，将引发重新计算。但手动改变行高列宽不会引发重算。在“手工重算”模式下，也不会标记工作表为“未计算” －插入或删除行、列、单元格。同时，那些引用了本工作表或其他工作表的名称以及引用其他工作表或被其他工作表引用的公式，也会被标志为“未计算”。 －增加名称定义，或改变、删除已定义的名称 －工作表改名或改变工作表的位置【注：这一点可能在xp以上版本不是问题？】 －此外，在Excel2003版中，隐藏行，或取消隐藏行。这是因为excel2003中的subtotal函数的新特性所致。但对列的隐藏或取消隐藏不属于易失操作。 查找函数 查询函数在工作表