|
如果你的表格总是会卡，不妨使用今天分享的六个方法去检查一下。 一、减少跨文件的引用 理由：根据经验，Excel在计算时，使用同样的数据源，同样的函数，数据源和结果在同一个sheet的速度最快，在同一个文件不同sheet的速度较快，在不同文件引用就慢了。当然，对于数据量不是很大的情况下，三种方式的速度无明显差异。 当数据计算量较大时，就必须考虑数据的整体布局：数据是分工作簿保存、分工作表保存，还是保存在同一工作表。 建议：基于整体性原则，如果是清单型数据表格，可能的话，尽量将数据整合同一工作表（同一个sheet），至少尽量在同一工作簿（同一个文件）。 如果不能整合在同一sheet，那么宁愿使用少量的大型工作簿，也不要使用数量较多的小型工作簿。 大型工作薄的意思是：将不在一个文件的数据源整个sheet复制到一起，也就是把多个文件合并，即使合并后的文件比较大，计算起来也比在很多个小文件直接来回链接数据要快得多 尽可能地避免工作簿间的链接，对外部工作簿进行链接，既影响表格的打开速度，并且当工作簿移动或删除时，还容易出现断链，不易于查找和修复。 二、如果确确实实不能合并的，必须使用分开的文件进行计算，也需要注意以下几点： 1、对关闭的工作簿尽量使用简单的直接单元格引用。这样做可以避免在重新计算任何工作簿时重新计算所有链接的工作簿。 这句话的意思是：链接数据只需要使用一次，不用每次重新计算，也就避免了实时更新链接值。 2、如果不能避免使用链接的工作簿，最好将它们全部打开而不是关闭 并且表格打开顺序也有讲究，要首先打开要链接到的工作簿，然后再打开包含链接的工作簿。 也就是先打开数据源的表格，再打开引用数据源的表格，一般来说，从打开的工作簿比从关闭的工作簿中读取链接的速度要快。 3、如果使用了链接数据，并且在编辑表格的时候有不顺畅的感觉，可以尝试以下方法： 方法1：保留少量的公式，删除多余的，等到数据录入完成，再将公式下拉完成全部的计算，最后备份好现有的公式后，将表格粘贴为数值。 方法2：暂时关闭自动计算功能，数据录入后再打开自动计算，最后也是备份公式，粘贴数值。 两个方法根据实际情况选择就行了，原理是一样的，都是减少编辑过程中的计算次数。 前两个其实是一个问题，都是链接其他文件的不管卡不卡，我都不建议大家使用跨文件的引用。 三、规范数据源 原始数据一定要规范，否则，还要使用函数公式处理不规范的数据，增加计算环节，并且这种处理用到数组公式的时候很多，会严重影响计算速度。 例如数据的来源不是手工输入，而是从其他系统导出，导出的数据可能并不规范，比如数字是文本格式、数字后有空格、不可见字符，这些数据就没法直接参与运算，如果不手工整理成规范数据，还得用函数公式进行规避，这就大大影响计算速度。 而规范数据源这样的工作，用分列或是查找替换都可以迅速的完成，然后再去用公式，效率可以成倍的提高。 如果非要用公式去规范，有以下经验： 数字变文本，可以在后面连接一个空值，例如：a1&""； 文本变数字，可以在后面进行一个计算，例如：a1+0、a1*1、--a1都行； 清除多余空格，不可见字符，可以使用clean函数和trim函数： 不可见使用clean，trim是清除多余空格，请注意是多余空格。完全清除空格用SUBSTITUTE函数。 四、减少易失函数的使用 这个必须要解释一下，什么是易失函数。 不知道你有没有发现这样一个情况，有时候只是打开表格，什么都没做，直接关闭就会提示是否保存。 如果你没遇到过，可以马上来试试，新建一个文件，任意单元格里输入公式：=now（） 保存后关闭，然后再打开这个文件，直接关闭就会有提示。 now这个函数就是一个易失函数。这种函数的特点就是，每次打开表格都会计算，任何一个操作也会计算。 RAND、NOW、TODAY、OFFSET、CELL、INDIRECT和 INFO。都是这样特性的函数。 当然，并不是说这样的函数用了就一定卡，只是这样的函数大量存在可能就会卡。 多大的量才叫大，根据你的电脑配置和你的自己感觉来确定吧。 常见案例：使用offset和counta做动态区域，对于数据量少的文件，这样的做法体会不到卡的感觉，如果上万行数据，再去用动态区域，反正我是不愿尝试的。 状态栏经常会看到多少个cpu在计算，计算完成的进度这样的提示，一点鼠标动不动就白屏，无响应等等状况齐至。 对于数量量大的文件，推荐使用Excel的表格功能来代替动态区域。 表格功能的具体用法参阅：传说Excel中有个“超级表”功能，今天带你揭开神秘的面纱？ 五、公式范围尽量准确 对于某些函数，例如：vlookup、SUMPRODUCT等等，在使用时尽量避免整列引用范围，因为这些函数会对范围内的每一个值都进行计算，并不会考虑范围内是否有数据，所以精确使用范围会提高速度。 当然，vlookup的模糊匹配方式不会卡，因为精确匹配使用的是遍历法查找，模糊匹配是二分法查找，原理不同，具体是什么原理，可以参阅：Excel中的精确匹配和大致匹配究竟是什么意思？ 对于另一些函数，例如，countif、sumif、lookup等等，使用整列则对速度没有明显影响，这是由函数的计算方式决定的。 当然，如果你分不清哪些函数可以整列使用范围，哪些不能整列使用范围，也没关系，感觉到卡的时候去修改范围就好了，只是在修改范围的时候，要修改完整，也就是对于的范围同时修改，否则会出错，比如数据有5000行，你为了预留一些空间，可以将范围指定到6000 ，哪怕指定到10000，也比整列要快很多。 六、善用辅助列 某些问题，使用辅助列会大大提高计算效率。 常见的有2种情况： 1、例如多条件引用，如果使用辅助列将条件合并，就可以避免使用数组公式，否则，数据量一大，没有辅助列而去用数组合并条件，会成倍的增加计算量，想不卡都难。 2、对于多次使用的结果使用辅助列完成。 例如这样的一个数据源 需要统计每个学生的平均分超过60的有多少人、总分超过300分的有多少人、最高的平均分是多少，最高的总分是多少等等这些数据。 那么建议增加两个辅助列，平均分和总分，然后再去统计，这样可以减少很多计算量。 不然只能用数组公式去做了。 最后的建议： 以上6点只是从公式计算的角度去分析的造成表格卡的一些原因，并不是全部。 有些时候，表格卡可能是由于有大量误操作产生的文本框，可以用定位，对象，删除；或者是无用的格式，例如整行整列大面积的填充背景 复杂的条件格式等等；需要针对具体问题去找到原因。 总之，养成良好的操作习惯，从数据源就开始规范表格，再合理的利用辅助列和选择适当的函数，使用表格肯定会非常顺畅。 |
|
|
