logo标签列表

如何以易读的方式重写Excel公式？

vbaexcelexcel-formula
19

我有一个Excel文件，其中的公式如下：

=IF(OR(ISERROR(G16),ISERROR(G17)),X16,IF(OR(G16="xxx",G16="yyy",G16="zzz"),Y16,IF(G16="333","N\A",IF(G17="333",Z16,IF(D17="",IF((HEX2DEC(W$10)-HEX2DEC(W16))/VLOOKUP(F16,$M$36:$N$41,2,FALSE)<0,0,(HEX2DEC(W$10)-HEX2DEC(W16))/VLOOKUP(F16,$M$36:$N$41,2,FALSE)), IF((HEX2DEC(W17)-HEX2DEC(W16))/VLOOKUP(F16,$M$36:$N$41,2,FALSE)<0,0,(HEX2DEC(W17)-HEX2DEC(W16))/VLOOKUP(F16,$M$36:$N$41,2,FALSE)))))))

我想简化它们，使其更易读。

  • 我可以以缩进的方式编辑/编写Excel公式吗？
  • 我可以进行哪些简化？
  • 我应该使用VBA脚本代替Excel的公式吗？
  • 是的，您可以使用缩进来编写/编辑Excel公式。这样做可以提高可读性和理解性。
  • 您可以使用以下方法简化公式：将长公式分解成几个小公式，使用命名范围代替单元格引用，使用函数和数组公式等。
  • 这取决于您的具体需求。如果Excel公式无法满足您的要求，则可以考虑使用VBA脚本来实现更高级的功能。
2
对于这样令人讨厌复杂的函数，我可能会在VBA中编写一个自定义函数，以便我可以使用缩进并使其更易读。 - enderland
1
编写一个 VBA 函数，将您的原始代码整合进去，并在电子表格中使用该 VBA 函数，而不是代码。 - Robert Harvey
2
如果是为了自己使用，使用辅助列来构建公式组件可能会很有用。对于一些复杂的计算，使用这种方法甚至更快。 - user857521
1
@ooo 如果我理解有误，请纠正我，但是助手列的意思是将公式的一部分放在不同的单元格中，然后引用这些单元格。例如：您可以将公式OR(ISERROR(G16),ISERROR(G17))改为某个其他单元格的公式=OR(ISERROR(G16),ISERROR(G17))，然后在您的复杂公式中，它只需读取IF([助手单元格的引用]...，而不是使用OR(ISERROR(G16),ISERROR(G17))。 - Daniel
1
回答您的问题：我可以以缩进的方式编辑/编写Excel公式吗？ 是的。我可以做哪些简化？ 分解公式/缩进/编写简短版本/像@ooo建议的使用辅助列。我应该使用VBA脚本而不是Excel的公式吗？ 这取决于情况。如果我可以通过公式完成某些操作，我倾向于避免使用VBA。 - Siddharth Rout
显示剩余3条评论
9个回答
13
1
为什么这不是被选中的答案？似乎最好地回答了“我可以以缩进的方式编辑/编写Excel公式吗？”这个问题。 - flindeberg
5
给一些你所指的单元格命名，这样整个内容更容易阅读。
4

使用辅助列作为示例，您可以使用以下方法缩短公式

[A1] =VLOOKUP(F16,$M$36:$N$41,2,FALSE)

[B1] =HEX2DEC(W$10)

[C1] =HEX2DEC(W16)

[D1] =HEX2DEC(W17)

那么，大型公式被缩短为

=IF(OR(ISERROR(G16),ISERROR(G17)),X16,IF(OR(G16="xxx",G16="yyy",G16="zzz"),Y16,IF(G16="333","N\A",IF(G17="333",Z16,IF(D17="",IF((B1-C1)/A1<0,0,(B1-C1)/A1), IF((D1-C1)/A1<0,0,(D1-C1)/A1))))))

当使用DATE或NOW等易变函数时，这非常有效，因为您不希望在每个单元格上重复计算相同的结果。

它是否更易读，可能不是，但您可以使用适当的注释标记列标题。

如果你愿意，你可以更进一步地提高代码的可读性，并命名你的辅助区间。因此，你可以像使用“VLookUpOfWhatever”这样的名称来引用相同的区间，而不是使用“A1”。;-) - Daniel
我倾向于只使用命名区域或公式来构建多条件查找。这只是我的个人偏好，因为它们往往会减慢我的速度。 - user857521
@ooo，我该如何以最简单和安全的方式将此应用于10K个单元格？谢谢 :) - 0x90
4

FormulaDesk 是一个免费的 Excel 插件，它可以使复杂公式更易于阅读和理解，无需重写。它使得创建、编辑、调试和理解公式变得更加容易。它有两种模式：'编辑视图'和'探索视图'，可以进行切换。

  • '编辑视图'是一个增强的公式编辑器，具有 Intellisense 等功能。它会在您输入时进行格式化，为了清晰易懂会对嵌套元素进行垂直偏移。

  • '探索视图'以简单的嵌套/汇总方式展现公式，最简单的顶层视图最先出现，但您可以深入到嵌套表达式中去了解当前结果返回的原因。点击绿色条形图（汇总结果）即可深入了解。或者，点击'展开全部'、'折叠全部'按钮。

  • 在两个视图中，您可以将鼠标悬停在各种元素（例如函数、参数等）上，以查看弹出窗口中的更多信息，例如定义/描述、当前值等。悬停在范围上将显示其当前值。

  • 还有许多其他功能。

[披露：我是 FormulaDesk 的作者]

enter image description here

3
结合辅助列和命名区域，可以使该公式变得非常简单。
在下面的图片中，您可以看到命名区域如何使公式更清晰：
请注意，“prices”是A2：A7范围的名称，“inflated_prices”是B2：B7的名称。
请注意，名称是智能的：sum(prices)将总和整个区域，而B2中的=+prices*2解析为=+A2*2，B3中的=+prices*2 解析为=+A3*2等。 enter image description here
这是我一直想问的一个问题 - 在Excel公式中，=+实际上有什么作用？为什么不只是=A2*2？似乎在金融领域最为普遍 :) - user857521
1
在我的情况下，当我要输入一个简单的公式，比如A22时，我按+进入“单元格选择模式”，通过移动箭头键来选择第一个单元格，而按=需要你按住shift键。然后，一旦你完成输入公式，Excel会自动补全为=，使公式变成=+A2。我认为大多数情况下，公式中出现前导+是因为这个原因。 - Tulains Córdova
1
@ooo，我相信Lotus曾经在公式的开头使用“+”，所以我认为这是从那里遗留下来的——在Excel中是多余的。 - barry houdini
3
您可以通过保留单个公式来大大简化公式。您使用HEX2DEC/VLOOKUP部分重复了几乎相同的表达式4次，如果您认识到这一点，可以将其缩减为单个实例。

=IF(formula<0,0,formula)

.....等价于

=MAX(0,formula)

[对于公式的数字结果]

如果您将IF(D17="".....表达式嵌套在主公式中，即以下版本，则可以：

=IF(ISERROR(G16&G17),X16,IF(OR(G16={"xxx","yyy","zzz"}), Y16,IF(G16="333","N\A",IF(G17="333",Z16,MAX(0,(HEX2DEC(IF(D17="",W$10,W17))-HEX2DEC(W16))/VLOOKUP(F16,$M$36:$N$41,2,0))))))

2

既然你问到了VBA代码，我就试着来翻译一下。这样做显然更易于理解，因此也更易于维护，但是函数有11个参数，有点不太方便。

Function Magic(d17 As Range _
                , f16 As Range _
                , g16 As Range _
                , g17 As Range _
                , w10 As Range _
                , w16 As Range _
                , w17 As Range _
                , x16 As Range _
                , y16 As Range _
                , z16 As Range _
                , m36 As Range) As Variant


    Dim a As Variant
    Dim b As Variant

    If IsError(g16.Value) Or IsError(g17.Value) Then
        Magic = x16.Value
        Exit Function
    End If

    If g16.Value = "xxx" Or g16.Value = "yyy" Or g16.Value = "zzz" Then
        Magic = y16.Value
        Exit Function
    End If

    If g16.Value = "333" Then
        Magic = "N\A"
        Exit Function
    End If

    If g17.Value = "333" Then
        Magic = z16.Value
        Exit Function
    End If

    If d17.Value = "" Then
        a = Application.WorksheetFunction.Hex2Dec(w10.Value) _
                - Application.WorksheetFunction.Hex2Dec(w16.Value)
        a = a / Application.WorksheetFunction.VLookup(f16.Value, m36, 2, False)
        If a < 0 Then
            Magic = 0
            Exit Function
        Else
            Magic = a
            Exit Function
        End If
    Else
        b = Application.WorksheetFunction.Hex2Dec(w17.Value) _
                - Application.WorksheetFunction.Hex2Dec(w16.Value)
        b = b / Application.WorksheetFunction.VLookup(f16.Value, m36, 2, False)
        If b < 0 Then
            Magic = 0
            Exit Function
        Else
            Magic = b
            Exit Function
        End If
    End If
End Function

为了更容易地跟踪您的公式逻辑（我不知道单元格代表什么），我为单元格引用命名了变量。您需要将它们重命名为有意义的名称。该代码应放在模块中。
1
如果您有类似于此的公式，要获得有意义的响应，您将需要在论坛上发布一个样本工作簿，并清楚地说明您想要实现什么。

是的，以上一些答案指出了如何更好地查看公式，或者摆脱一些多余的东西，或者将复杂性隐藏在一些VBA中（我认为这只涉及美学方面，可能会显著增加重新计算时间的成本）。

但是，如果不知道公式的意图和所在的工作簿，则只能提供有限的建议。

如果您的工作簿中有数万个这样的公式，则存在数据结构问题而不是公式问题。最有效的公式是可以避免的。如果您重新设计此工作簿，使其利用Excel表、数据透视表和可能的高级筛选器，则可以避免数万个此类公式。也许是数十万个公式。

0

FormulaDesk 只能在 Windows 上运行。

除了上面提到的 FormulaDesk：

这里是 FastExcel 当前的 Beta 3 链接。但他们似乎已经存在十多年了。29 美元。我认为只支持 Windows。该网站不太清楚。

http://fastexcel.wordpress.com/2014/04/28/making-sense-of-complex-formulas-an-indenting-viewer-editer/

类似于Precision Calc的功能。$12，如果您只是偶尔需要此功能，则有一个无广告版本。仅适用于Windows。

http://precisioncalc.com/tf/what_is_the_formulator.html

网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接