提高excel的运算效率

《提高excel的运算效率》由会员分享，可在线阅读，更多相关《提高excel的运算效率（5页珍藏版）》请在金锄头文库上搜索。

1、 提高提高 ExcelExcel 的运算效率的运算效率【编译自编译自 http:/ excel 并不是按行列或固定的方式来计算的，相反，它是根据关系链来动态地决定其 计算顺序。 计算次序分为两步：1.创建关系树（链） ，并标记“未计算”单元格；2.确定关系链的 计算顺序，计算公式，并记住计算顺序，以便下次重算。 关于关系树（链）：关于关系树（链）： 如果一个单元格的计算结果要依赖于另一单元格，则称二者有从属关系。 举例来说，A1=A2+5, A2=A3+1, A3=2 显然计算次序应是：A3A2A1， 我们称 A3 向前引用 A2，A2 向前引用 A1。 （反之，也可说 A1 向后引用 A2，

2、也 称父关系、子关系，A3 的父关系是 A2，A2 的子关系是 A3)单元格或名称改变后，即使重算方式设定为手动，excel 也将重新创建关系链（或 称关系树） 。如果关系树都是在本工作表内的，其速度将大大快于跨工作表关系树的创建。 不过在这方面 excel2002 比 2000 和 97 版有重大改进。EXCEL 在自动重算时，并不是对全部单元格进行计算，而是进行“聪明”的计算，即只 选择对那些需要进行重算的单元格、公式、名称进行计算（包括刚刚输入或更改的单元格及其 关系树上的所有单元格） 。 但以下有例外： 1.2000 以上版本打开时默认为自动重算，但当打开低版本的工作表时，会进行全部

3、重算。 2.易失函数全部重算 3.按下“全部重算”键，强制重算（ctrlAltF9） 4.如果关系链上的单元格大于 65536 如果大量使用 OFFSET, INDEX, LOOKUP 之类的函数，且其引用范围较大， 可能会导致关系链增大。 5.如果在代码中使用了 worksheet.enablecalculation 6.没有使用的名称不会计算 7.如果名称所引用的单元格重算了，名称也会重算如何控制计算如何控制计算 点击菜单工具选项重新计算，我们看到了几种重算的方式。 需要注意的是：这里设置的选项，有些会使得以后打开的工作簿都具有相同的选项，见下 面的说明（“关于设置的级别” ） 。 自动重

4、算：自动重算：就是当单元格有变化时，自动进行所有工作表的计算（包括打开工作簿时） 。 如果是用不同版本 EXCEL 保存的工作簿，打开时会重算，因为它们的计算引擎是不同的。手工重算（手工重算（F9 或或 CtrlAltF9）：）：只有按下键时才进行计算，或保存时重算（如果勾 选） 。当 excel 认为需要重算时，会在状态栏出现“计算”的字样。除模拟运算表外自动重算除模拟运算表外自动重算：全部计算是指对全部公式进行计算（按 CtrlAltF9） ，重算是指对所有的单元格、名称、 易失函数公式及其从属单元格的新建、改变后的计算（按 F9） 。通常情况下，重算要比全部计 算快。在 excel200

5、2 版本中，按 Ctrl-Alt-Shift-F9 可以进行带重建关系树的全部计算。 关于设置的级别关于设置的级别应用级设置应用级设置：包括重算方式的设置、迭代方式等。他们都将影响后续打开的工作簿。这 些设置保存在每一个工作簿中。尽管如此，还是要注意：所有后续打开的工作簿的此类设置均 服从最先打开的工作簿的设置，而忽略本身的原设置。工作簿级设置：工作簿级设置：这些设置为工作簿级设置，只对本工作簿起作用，而对同时打开的其他 工作簿不起作用。包括：远程更新引用；以显示值为准；1904 日期系统；保存外部链接；接 受公式标志易失函数易失函数 见见 ： http:/ 在该帖中，我们已经知道，OFFSE

6、T, INDIRECT, TODAY, NOW, RAND,CELL, INFO 均属于易失函数；而根据测试结果，微软公布的易失函数 ROWS, COLUMNS, AREAS, WEEKNUM 却并不具有易失性；至于 INDEX，在 97 版之后，就不再是易失函数。除此之外，还有一些操作属于易失性操作，这些操作也都会触发自动重算。包括：除此之外，还有一些操作属于易失性操作，这些操作也都会触发自动重算。包括：自动筛选：自动筛选：进行自动筛选时，会把筛选区域的所有公式标志为“未计算” ； 单击或双击改变行高、列宽：单击或双击改变行高、列宽： 在自动计算的模式下，单击或双击行列分割线改变行高、 列宽

7、的，将引发重新计算。但手动改变行高列宽不会引发重算。在“手工重算”模式下，也不 会标记工作表为“未计算” 插入或删除行、列、单元格。插入或删除行、列、单元格。同时，那些引用了本工作表或其他工作表的名称以及引用 其他工作表或被其他工作表引用的公式，也会被标志为“未计算” 。增加名称定义，或改变、删除已定义的名称增加名称定义，或改变、删除已定义的名称工作表改名或改变工作表的位置工作表改名或改变工作表的位置【注：这一点可能在 xp 以上版本不是问题？】此外，在此外，在 Excel2003 版中，隐藏行，或取消隐藏行。版中，隐藏行，或取消隐藏行。这是因为 excel2003 中的 subtotal 函

8、 数的新特性所致。但对列的隐藏或取消隐藏不属于易失操作。查找函数查找函数 查询函数在工作表中经常被大量使用。所以探讨他们的效率问题具有重大意义。 我们已经知道，关于查找函数帮助中说到： 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_

9、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.只要有可能，先对数据区排序

10、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”) 在大范围的数据查找中，两个近似匹配也比一个精确匹配要快！在

11、大范围的数据查找中，两个近似匹配也比一个精确匹配要快！如果数据未排序，且当被查找数据不在范围内时，会出现#/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,coln

12、um) 如果不想使用辅助单元格，也可用以下公式，速度也不差： 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 加两个 M

13、ATCH 来解决，如下例： 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),M ATCH(RowLookup_Value,$A$2:$A$1000),MATCH(colLookup_value,$B$1:$Z$1)另一个办法是：

14、 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) 还有一个办法是，不使用那么多的工作表，而是把他们都集中到一个大表

15、中。在 MATCH,VLOOKUP 和 HLOOKUP 中都允许使用通配符?和*关于动态查找范围关于动态查找范围 把下列公式定义到名称中，在公式中引用，就会动态地决定查找的范围，从而避免无 谓的大范围查找： =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)求和与条件求和求和与条件求和 SUM 和 SUMIF ：运行时间正比于其数据范围。所以如果数据区是排过序的，就可以适当 地选择计算区域来减少计算时间。 SUMIF 和 COUNTIF 可以使用通配符?和* DSUM：速度很快，但使用不大方便多条件求和多条件求和 数组公式的一个经常使用的地方就是用 SUM 数组进行多条件求和。但它通常很慢。优化的办法是：用用 SUMPRODUCTSUMPRODUCT 代替代替。它比 SUM 数组快约 5%10%，而且还能处理文本和空值。 =SUM($D$2:$D$10301*$E$2:$E$10301)