提高EXCEL的运算效率.doc

提高Excel的运算效率【编译自/】【注：本文不是原文翻译，可能夹杂了译者的错误理解，请以原文为准】l 计算次序excel并不是按行列或固定的方式来计算的，相反，它是根据关系链来动态地决定其计算顺序。计算次序分为两步：1.创建关系树（链），并标记“未计算”单元格；2.确定关系链的计算顺序，计算公式，并记住计算顺序，以便下次重算。关于关系树（链）：如果一个单元格的计算结果要依赖于另一单元格，则称二者有从属关系。举例来说，A1=A2+5, A2=A3+1, A3=2 显然计算次序应是：A3A2A1，我们称A3向前引用A2，A2向前引用A1。（反之，也可说A1向后引用A2，也称父关系、子关系，A3的父关系是A2，A2的子关系是A3) 单元格或名称改变后，即使重算方式设定为手动，excel也将重新创建关系链（或称关系树）。如果关系树都是在本工作表内的，其速度将大大快于跨工作表关系树的创建。不过在这方面excel2002比2000和97版有重大改进。EXCEL在自动重算时，并不是对全部单元格进行计算，而是进行“聪明”的计算，即只选择对那些需要进行重算的单元格、公式、名称进行计算（包括刚刚输入或更改的单元格及其关系树上的所有单元格）。但以下有例外：1. 2000以上版本打开时默认为自动重算，但当打开低版本的工作表时，会进行全部重算。2. 易失函数全部重算3. 按下“全部重算”键，强制重算（ctrlAltF9）4. 如果关系链上的单元格大于65536如果大量使用OFFSET, INDEX, LOOKUP之类的函数，且其引用范围较大，可能会导致关系链增大。5. 如果在代码中使用了worksheet.enablecalculation 6. 没有使用的名称不会计算7. 如果名称所引用的单元格重算了，名称也会重算l 如何控制计算点击菜单工具选项重新计算，我们看到了几种重算的方式。需要注意的是：这里设置的选项，有些会使得以后打开的工作簿都具有相同的选项，见下面的说明（“关于设置的级别”）。自动重算：就是当单元格有变化时，自动进行所有工作表的计算（包括打开工作簿时）。如果是用不同版本EXCEL保存的工作簿，打开时会重算，因为它们的计算引擎是不同的。 手工重算（F9或CtrlAltF9）：只有按下键时才进行计算，或保存时重算（如果勾选）。当excel认为需要重算时，会在状态栏出现“计算”的字样。 除模拟运算表外自动重算：全部计算是指对全部公式进行计算（按CtrlAltF9），重算是指对所有的单元格、名称、易失函数公式及其从属单元格的新建、改变后的计算（按F9）。通常情况下，重算要比全部计算快。在excel2002版本中，按Ctrl-Alt-Shift-F9可以进行带重建关系树的全部计算。关于设置的级别 应用级设置：包括重算方式的设置、迭代方式等。他们都将影响后续打开的工作簿。这些设置保存在每一个工作簿中。尽管如此，还是要注意：所有后续打开的工作簿的此类设置均服从最先打开的工作簿的设置，而忽略本身的原设置。 工作簿级设置：这些设置为工作簿级设置，只对本工作簿起作用，而对同时打开的其他工作簿不起作用。包括：远程更新引用；以显示值为准；1904日期系统；保存外部链接；接受公式标志l 易失函数见 ： /dispbbs.asp?BoardID=3&ID=108514在该帖中，我们已经知道，OFFSET, INDIRECT, TODAY, NOW, RAND,CELL, INFO均属于易失函数；而根据测试结果，微软公布的易失函数ROWS, COLUMNS, AREAS, WEEKNUM却并不具有易失性；至于INDEX，在97版之后，就不再是易失函数。除此之外，还有一些操作属于易失性操作，这些操作也都会触发自动重算。包括： 自动筛选：进行自动筛选时，会把筛选区域的所有公式标志为“未计算”；单击或双击改变行高、列宽： 在自动计算的模式下，单击或双击行列分割线改变行高、列宽的，将引发重新计算。但手动改变行高列宽不会引发重算。在“手工重算”模式下，也不会标记工作表为“未计算”插入或删除行、列、单元格。同时，那些引用了本工作表或其他工作表的名称以及引用其他工作表或被其他工作表引用的公式，也会被标志为“未计算”。 增加名称定义，或改变、删除已定义的名称 工作表改名或改变工作表的位置【注：这一点可能在xp以上版本不是问题？】 此外，在Excel2003版中，隐藏行，或取消隐藏行。这是因为excel2003中的subtotal函数的新特性所致。但对列的隐藏或取消隐藏不属于易失操作。l 查找函数查询函数在工作表中经常被大量使用。所以探讨他们的效率问题具有重大意义。我们已经知道，关于查找函数帮助中说到：MATCH(lookup_value, lookup_array, match_type)Match_type=1 默认选项，数组升序排列。返回等于或小于查找值的最大数值Match_type=0 返回精确匹配Match_type=-1 数组降序排列，返回等于或大于查找值的最小数值VLOOKUP(lookup_value, table_array, colnum, range_lookup)Range_lookup=TRUE 默认选项，数组升序排列。返回等于或小于查找值的最大数值Range_lookup=FALSE 返回精确匹配 与MATCH+INDEX或OFFSET相比，VLOOKUP的速度略快(约5%），使用内存略少，公式更简单。但如果MATCH+INDEX用得好，可以比VLOOKUP节省大量的时间。 以下两公式是等价的：VLOOKUP(A1, Data!$A$2:$F$1000,3,False)INDEX(Data!$A$2:$F$1000,MATCH(A1,$A$1:$A$1000,0),3) 要提高查询速度，建议：1. 只要有可能，先对数据区排序2. 尽量减少查找区域的大小。或者可采用动态区域名称3. 尽量使用排过序的数据加近似匹配，而不要使用未排序加精确匹配，前者的速度比后者要快得多，而且与查找区域的大小关系不大。4. 如果已经排序，但不知道被查数值是否在表中，因而无法使用近似匹配，则可使用以下公式：IF(lookup_val=Index(lookup_array,MATCH(lookup_val,lookup_list),1) ,Index(lookup_array,MATCH(lookup_val,lookup_array), colnum),“notexist”)在大范围的数据查找中，两个近似匹配也比一个精确匹配要快！如果数据未排序，且当被查找数据不在范围内时，会出现#/N/A的提示，为避免此问题，通常采用ISNA()函数进行处理，如：IF(ISNA(VLOOKUP(lookupval,table,2,FALSE),0,VLOOKUP (lookupval,table,2, FALSE)但这是一个影响速度的方法。因为它要进行两次查找。可以采用辅助单元格的办法来解决：如，在A1中输入：=MATCH(lookupvalue,lookuparray,0)，而在B1中输入：=IF(ISNA(A1),0,INDEX(table,A1,colnum)如果不想使用辅助单元格，也可用以下公式，速度也不差：IF (COUNTIF(lookuparray,lookupvalue)=0, 0, VLOOKUP(lookupval, table, 2 FALSE)从多列中返回精确匹配：使用一个辅助列来储存MATCH的结果(stored_row)，对每一列使用公式INDEX(Lookup_Range,stored_row,column_number)也可使用数组公式：VLOOKUP(lookupvalue,Lookup_Range,4,2,FALSE)行列双向查询： 使用一个INDEX加两个MATCH来解决，如下例：INDEX($B$2:$Z$1000,MATCH(RowLookup_Value,$A$2:$A$1000),MATCH(colLookup_value,$B$1:$Z$1)三维查询： 一个办法是使用函数CHOOSE()，不过只适用于表格数量不多的情况：INDEX(CHOOSE(TableLookup_Value,TableName1,TableName2,TableName3,TableName4),MATCH(RowLookup_Value,$A$2:$A$1000),MATCH(colLookup_value,$B$1:$Z$1) 另一个办法是：INDEX(INDIRECT(Sheet & TableLookup_Value & !$B$2:$Z$1000), MATCH(RowLookup_ Value, $A$2:$A$1000),MATCH(colLookup_value,$B$1:$Z$1)也可以：INDEX(INDIRECT(VLOOKUP(TableLookup_Value,TableOfTAbles,1),MATCH(RowLookup_Value,$A$2:$A$1000),MATCH(colLookup_value,$B$1:$Z$1)还有一个办法是，不使用那么多的工作表，而是把他们都集中到一个大表中。在MATCH,VLOOKUP和HLOOKUP中都允许使用通配符?和*l 关于动态查找范围把下列公式定义到名称中，在公式中引用，就会动态地决定查找的范围，从而避免无谓的大范围查找：=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1)，也可以使用INDIRECT 函数但这个公式也有点问题。主要是OFFSET是个易失函数，而COUNTA又要计算大量的行。为此可以使用辅助单元格：Counts!z1=COUNTA(Sheet1!$A:$A)而动态范围=OFFSET(Sheet1!$A$1,0,0,Counts!$Z$1,1)l 求和与条件求和SUM和SUMIF ：运行时间正比于其数据范围。所以如果数据区是排过序的，就可以适当地选择计算区域来减少计算时间。SUMIF 和COUNTIF可以使用通配符?和*DSUM：速度很快，但使用不大方便l 多条件求和数组公式的一个经常使用的地方就是用SUM数组进行多条件求和。但它通常很慢。 优化的办法是：用SUMPRODUCT代替。它比SUM数组快约5%10%，而且还能处理文本和空值。=SUM($D$2:$D$10301*$E$2:$E$10301)=SUMPRODUCT($D$2:$D$10301*$E$2:$E$10301)=SUMPRODUCT($D$2:$D$10301,$E$2:$E$10301)这三个公式结果相同，但第三个公式比前两个约快77%用SUMPRODUCT条件求和，其形式如下：SUMPRODUCT(-(条件1)，-（条件2)，（求和区域）其中两个减号(-)的作用是把条件的逻辑值转变为数值以便计算，也可用+0和*1代替，但(-)比+0,*1都略快些。但注意其中的各部分区域范围必须相同，且不能是同一列。也可使用下式：SUMPRODUCT(条件1)*（条件2)*（求和区域） 但这个公式略慢于用逗号分割的公式，而且其求和区域内不能有文本值。另一个办法是用DSUM代替SUM数组公式 如果多条件求和的数组公式不多，也可用DSUM来代替，它也比数组公式略快。但其缺点是必须有单独的区域来存放条件，所以使用不大方便。l 数组公式数组公式的优缺点： 优点：简洁。一个公式就可以代替多行或多列的普通公式 功能强大。可减小文件体积（但不一定减小内存占用）可以有一定的保护作用。多单元格数组公式必须选定全部公式所在单元格才能修改，一定程度上防止了新手误操作。 缺点：可读性差。新手不易理解。许多情况下速度略慢参与操作的几个参数必须具有相同的尺寸，这可能导致一些并不需要计算的单元格也参与计算。特别是在多条件求和的数组公式中。要想提高数组运行的速度，应： 避免引用整行，尽量减小引用范围 尽量使用动