电子表格优化器
简介
我们不必忍受缓慢的电子表格。非常常见的情况是,低效的电子表格有很大的改进空间,我们可以找出并进行优化。一种优化方法是优化电子表格公式。例如,我们可以使用更合适的函数,使公式执行得更快,更简短,更易于理解。
电子表格优化器可以帮助你在效率和可读性方面优化公式。
- 电子表格优化器会自动识别可以在效率和可读性方面改进的公式。
- 它会提出更好的公式重写方法,并告诉你为什么这些方法更好。
- 任何时候,你都可以接受其推荐或撤销更改。
入门指南
除了按照安装的步骤安装10 Studio插件外,尝试电子表格优化器的最快方式是点击下面并下载工作簿示例。
一旦10 Studio插件已加载,点击10 Studio
选项卡下的电子表格优化器
按钮以启动应用程序。
操作指南
- 如果有受密码保护的电子表格,手动取消保护它们
- 加载10 Studio插件,点击
10 Studio
选项卡中的电子表格优化器
按钮。 - 点击任务窗格中的
优化
按钮。 - 如果工具没有找到可以优化的公式,将出现
工作簿没有不良公式需要优化
。否则,工具会列出需要优化的公式。你可以点击项目来实际替换公式(并撤销更改)。
优化规则
在这个阶段,我们应用以下规则来优化公式。我们的工具试图在公式内部检测出糟糕的模式并建议一个好的模式。目前,我们不会在一条公式中同时应用两个规则。
VLOOKUP => INDEX+MATCH
VLOOKUP公式在工作表结构变化时可能会很脆弱。当用户在表中插入一列时,VLOOKUP的第三个参数指向的列号不会自动更改;因此,由于列的插入,VLOOKUP将不会从原来的列返回值。而INDEX+MATCH公式总是会引用初始列,无论工作表的结构如何变化。
例子:
=VLOOKUP(A1,C2:G9,2,false)
=> =INDEX(D2:D9,MATCH(A1,C2:G9,0))
SUMIFs => SUMIFS
在大表上添加多个SUMIFS表达式可能会很耗资源,因为它会多次遍历整个表。如果SUMIFS表达式有类似的结构,可以用SUM+SUMIFS的方式重写公式。这样效率更高,因为它只遍历一次表格。
例子:
=SUMIFS(N1:N9,L1:L9,C1,M1:M9,"H1")+SUMIFS(N1:N9,L1:L9,C1,M1:M9,"H2")
=> =SUM(SUMIFS(N1:N9,L1:L9,C1,M1:M9,{"H1","H2"}))
IF+ISERROR => IFERROR
IFERROR是一个相对较新的函数,可以替代IF和ISERROR的组合。
例子:
=IF(ISERROR(a),b,a)
=> =IFERROR(a,b)
nested-IF => IFS
IFS是一个新引入的函数,可以用来替代某种类型的nested-if公式。
例子:
=IF(a,b,IF(c,d,IF(e,f,g)))
=> =IFS(a,b,c,d,e,f,g)
nested-IF => IF
在nest-if公式中可能有多余的部分。例如,我们可以减少IF使公式更简洁。
例子:
=IF(a,b,IF(a,b,c))
=> =IF(a,b,c)
nested-IF => IF+AND/OR
嵌套-if中的一些逻辑可以用如AND或OR等逻辑运算符表示,这使公式更加简洁。
例子:
=IF(B6=a,x,IF(C6=b,x,y))
=> =IF(OR(B6=a,C6=b),x,y)
=IF(B6=a,IF(C6=b,x,y),y)
=> =IF(AND(B6=a,C6=b),x,y)
nested-AND/OR => AND/OR
嵌套的逻辑表达式,如nested-AND/OR,可以被重写为更简洁的形式。
例子:
=AND(a,AND(b,c,d),e)
=> =AND(a,b,c,d,e)
=OR(OR(a,b,c),d,e)
=> =OR(a,b,c,d,e)