Skip to main content

电子表格优化器

简介

我们不必忍受缓慢的电子表格。非常常见的情况是,低效的电子表格有很大的改进空间,我们可以找出并进行优化。一种优化方法是优化电子表格公式。例如,我们可以使用更合适的函数,使公式执行得更快,更简短,更易于理解。

电子表格优化器可以帮助你在效率和可读性方面优化公式。

  • 电子表格优化器会自动识别可以在效率和可读性方面改进的公式。
  • 它会提出更好的公式重写方法,并告诉你为什么这些方法更好。
  • 任何时候,你都可以接受其推荐或撤销更改。

入门指南

除了按照安装的步骤安装10 Studio插件外,尝试电子表格优化器的最快方式是点击下面并下载工作簿示例。

下载 10Studio-Sample-Auto.xlsx

一旦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)