分享

让Excel运行更快的技巧

 一兵个人图书馆 2020-03-03

有时候即使文件只有1、2M大小的时候,在有较多公式运算时右下角还是会跳出来运算进度,一等就是一两分钟,运气不好就死掉,这种情况很让人捉急。

很多大型企业做财务、资产管理、数据管理岗位的,操作数十万行左右规模记录,同时还挂着VLOOKUP运算的Excel更是常见。动辄数分钟的等待,甚至程序卡死,电脑卡死的情况屡见不鲜。

针对这些恼人的场景,除去提升计算机性能外,还有什么其他的习惯能让EXCEL运算速度加快?跟数据格式、公式用法之类的有直接关系么?

Excel数据计算的物理基础依赖CPU和内存,大数据量和复杂公式的处理方式,直接影响着内存的占用,不合理的设计,很容易导致内存占满。

Excel

文件优化方法

更多Excel与统计分析知识和资料,请关注本公众号!

01

善待Excel,合理设计数据的布局

评估你的工作需求是否应该使用EXCEL,文字处理用WORD,多媒体演示用PPT,批量复杂重查询多关联数据表用Access。超过3w行的数据尽管理论上EXCEL还是可以处理(最多1048576行),但要审慎选择。

相同性质或加在一起解决某类特定问题的sheet页组成一个工作簿文件(就是EXCEL文件),既不要一个工作表一个文件(太散,没有结构)也不要所有工作表堆在一个文件里(后续很难归类检索)。

不要跨工作簿引用数据。

02

善待数据,原始数据要规范

公式结果适当的时候转化为数字,可使用选择性粘贴功能(避免因引用或数据源改变影响计算结果)。

不要在同一列大量混合存放文本和数字,除非有明确理由,应拆开存放。

除非表格本身已经是最终输出状态,否则确保数据有列字段名,字段名不要使用多行(回车换行或者合并单元格等)不要有表名(会破坏列字段名称默认位置)。

全称、简称、别称不要混用,稳妥起见建议设置数据验证只支持下拉选择。

如果有必要,使用数据库思维检查一下你的表格是否规范(这章也提到了一部分)。

04

要关注公式函数的计算效率,尽量使用效率高的函数,或使用其他功能代替。

1. 非必要的情况下,不使用可变函数(易失性函数)。

2. 使用其他行和列计算并存储中间结果一次,以便在其他公式中重复使用它们。并且,如前所述,尽可能引用其他单元格已有的计算结果,这样可提高运算效率。

3. 减少每个公式中的引用数,最大程度地减少函数中的引用单元格范围。

4.尽可能使用最有效的函数(一般情况下自定义函数慢于 Excel 中的内置函数),编制适当的公式,尽可能减少公式的计算次数

=IF(ISERROR(VLOOKUP('张三',$A$2:$C$1000,3,0)),'查无此人',VLOOKUP('张三',$A$2:$C$1000,3,0))

如果表格A2:A1000中有“张三”, 使用上面这个公式,则 Excel 要运算VLOOKUP函数两次。Excel 2007以后的版本中,可以使用 IFERROR 来减少运算的次数:

05

非必要的情况下不使用会触发重新计算的操作

06

可能的情况下先对数据进行排序,再使用查找引用。尽可能避免对未排序数据执行查找,因为速度很慢

07

关闭自动计算

在手动计算模式下,可以通过按 【F9】触发智能重新计算。使用【Shift+F9】 仅重新计算所选工作表,按【Ctrl+Alt+F9】强制对所有公式执行完整计算,也可以通过按【Ctrl+Shift+ Alt+F9】 强制彻底重新构建依赖项和执行完整计算。

F9计算所有打开的工作簿中的所有工作表。

按 Shift+F9 可计算活动工作表。

按 Ctrl+Alt+F9 可计算所有打开的工作簿中的所有工作表,不管它们自上次计算以来是否已更改。

如果按Ctrl+Alt+Shift+F9,则会重新检查相关公式,然后计算所有打开的工作簿中的所有单元格,其中包括未标记为需要计算的单元格。

Excel优化

小技巧

01

公式只保留第一行

这个模仿数据库的更新方法。如果你使用的是一个数十万行的大数据表,而里面涉及计算,为什么要把公式储存几十万遍呢?储存一个结果比如42,比储存计算过程要简便的多。

操作时,一列30万行公式计算,平时只留第一行为公式,剩下粘贴为值。数据有更新时重新填充公式,再F9,然后再粘贴为值只留第一行公式。能剩下小一半的文件体积。

02

谨慎使用数组公式

数组公式非常绚丽,但是也非常吃CPU。没有什么问题是一个“好的数据结构”+“简单公式”解决不了的。

03

复杂公式使用VBA代替

3行原则:公式超过3行,请考虑VBA!

04

谨慎使用照相机功能

05

不要把Excel当数据库用

大量的原始数据和中间计算结果直接存在表格里,一开始用的方便,数据量一大就是SB了。我们公司一个老交易员的Excel表格有250MB. 打开表格要1分钟。这个问题并没有什么简的解决办法。最好的作法当然是把数据储存交给专业语言如sql,但一般初学者要搞这个还是太麻烦。我的建议是当你的数据达到100MB的时候,恭喜你,你处理的工作已经开始有点复杂了,去学门专业的编程语言吧。建议的顺序是:VBA, MATLAB, SQL。

05

关闭自动计算

    本站是提供个人知识管理的网络存储空间,所有内容均由用户发布,不代表本站观点。请注意甄别内容中的联系方式、诱导购买等信息,谨防诈骗。如发现有害或侵权内容,请点击一键举报。
    转藏 分享 献花(0

    0条评论

    发表

    请遵守用户 评论公约

    类似文章 更多