vba 如何以可读的方式重写excel公式？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12713913/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to rewrite excel formulas in a readable manner?
提问by 0x90
I have an Excel file with formulas in this manner:
我有一个带有这种方式的公式的 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)))))))
=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)))))))
I would like to simplify them so it will be written in a more readable manner.
我想简化它们，以便以更易读的方式编写。
- Can I edit/write Excel formulas in indented way?
- What kind of simplifications can I do?
- Should I use an VBA script instead of Excel's formulas?
- 我可以以缩进的方式编辑/编写 Excel 公式吗？
- 我可以做什么样的简化？
- 我应该使用 VBA 脚本而不是 Excel 的公式吗？
采纳答案by user3357963
As an example using helper columns, you could shorten the formula with the following
作为使用辅助列的示例，您可以使用以下内容缩短公式
[A1] =VLOOKUP(F16,$M$36:$N$41,2,FALSE)
[A1] =VLOOKUP(F16,$M$36:$N$41,2,FALSE)
[B1] =HEX2DEC(W$10)
[B1] =HEX2DEC(W$10)
[C1] =HEX2DEC(W16)
[C1] =HEX2DEC(W16)
[D1] =HEX2DEC(W17)
[D1] =HEX2DEC(W17)
then the large formula is shortened to
然后将大公式缩短为
=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))))))
=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))))))
This is particularly effective when using volatile functions such as DATE or NOW which you don't want to recalc for every cell when it's the same result.
这在使用诸如 DATE 或 NOW 之类的易失性函数时特别有效，当结果相同时，您不想为每个单元格重新计算。
Whether it's more readable, perhaps not but you can label column headings with appropriate comments
它是否更具可读性，也许不是，但您可以使用适当的注释标记列标题
回答by Dick Kusleika
You can use Alt+Enter in the formula bar to make your formula multiline. Sadly, no tabs only spaces so it becomes tedious to create and edit. See also
您可以在公式栏中使用 Alt+Enter 使公式多行。可悲的是，没有制表符只有空格，因此创建和编辑变得乏味。也可以看看
http://www.dailydoseofexcel.com/archives/2005/04/01/excel-formula-formatter/
http://www.dailydoseofexcel.com/archives/2005/04/01/excel-formula-formatter/
回答by Jamie Bull
Naming some of the cells you refer to might make the whole thing more readable
命名您引用的某些单元格可能会使整个内容更具可读性
回答by Gareth Hayter
FormulaDeskis a free Excel add-in that makes complex formulas more readable and easier to understand without having to rewrite them. It makes creating, editing, debugging and understanding formulas much easier. It has two modes: 'Edit View' and 'Explore View', which can be toggled.
FormulaDesk是一个免费的 Excel 插件，它使复杂的公式更具可读性和更容易理解，而无需重写它们。它使创建、编辑、调试和理解公式变得更加容易。它有两种模式：“编辑视图”和“探索视图”，可以切换。
The ‘Edit' view is an enhanced formula editor, with Intellisense etc. It formats as you type, vertically offsetting nested elements for clarity and understandability.
The ‘Explore' view presents the formula in a simple nested/rolled-up way, with the simplest top-level view of your formula first, but allowing you to drill-down to nested expressions. This enables you to quickly understand how/why it is returning the current result. Click the green bars (rolled-up results) to drill-down. Alternatively, click the ‘Expand all', ‘Collapse all' buttons.
In both views you can hover over various elements (eg: functions, parameters etc) to see a popup with more information, such as definition/description, current value etc. Hovering over a range will display its current value for example.
There are quite a few other capabilities.
“编辑”视图是一个增强的公式编辑器，具有 Intellisense 等功能。它在您键入时进行格式化，垂直偏移嵌套元素以提高清晰度和可理解性。
“探索”视图以简单的嵌套/滚动方式呈现公式，首先是最简单的公式顶级视图，但允许您深入查看嵌套表达式。这使您能够快速了解它如何/为什么返回当前结果。单击绿色条（汇总结果）进行深入分析。或者，单击“全部展开”、“全部折叠”按钮。
在这两个视图中，您可以将鼠标悬停在各种元素（例如：函数、参数等）上以查看包含更多信息的弹出窗口，例如定义/描述、当前值等。悬停在某个范围上将显示其当前值。
还有很多其他功能。
[Disclosure: I am the author of FormulaDesk]
[披露：我是FormulaDesk的作者]
回答by barry houdini
You can simplify your formula substantially while still keeping a single formula. You are repeating almost the same expression 4 times with the HEX2DEC/VLOOKUPpart, that can be reduced to a single instance if you recognise that this
您可以在保留单个公式的同时大幅简化您的公式。您在HEX2DEC/VLOOKUP零件上重复了几乎相同的表达式 4 次，如果您认识到这一点，则可以将其简化为单个实例
=IF(formula<0,0,formula)
=IF(公式<0,0,公式)
.....is equivalent to
..... 相当于
=MAX(0,formula)
=MAX(0,公式)
[for numeric results of formula]
[公式的数值结果]
and if you nest your IF(D17="".....expression within the main formula, i.e. this version
如果您将 IF(D17="".....expression 嵌套在主公式中，即此版本
=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))))))
=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))))))
回答by Tulains Córdova
A combination of helper columns and named ranges would make that formula quite simple.
辅助列和命名范围的组合将使该公式非常简单。
In the following image you can see how named ranges can unclutter a formula:
在下图中，您可以看到命名范围如何整理公式：
Notice that "prices" is name for range A2:A7 and "inflated_prices" is name for B2:B7.
请注意，“prices”是范围 A2:A7 的名称，“inflated_prices”是 B2:B7 的名称。
Notice also that names are intelligent: sum(prices)will sum the whole range, whereas =+prices*2in B2 resolves to =+A2*2, =+prices*2in B3 resolves to =+A3*2and so on.
另请注意，名称是智能的：sum(prices)将对整个范围求和，而=+prices*2在 B2 中解析为=+A2*2，=+prices*2在 B3 中解析为=+A3*2等等。
回答by Rob Gale
Since you asked about VBA code, I thought I'd give it a try. It's certainly more understandable and therefore maintainable, however the function has 11 arguments so it's a little unwieldy.
既然您询问了 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
To make it easier to follow your formula logic (and I didn't know what the cells represent), I named the variables for the cell references. You'll want to rename them to something meaningful. The code belongs in a module.
为了更容易遵循您的公式逻辑（我不知道单元格代表什么），我为单元格引用命名了变量。您需要将它们重命名为有意义的名称。代码属于一个模块。
回答by jeffreyweir
If you have a formula that looks like that, then to get a meaningful response you will have to post a sample workbook on a forum with clear instructions on what you are trying to achieve.
如果您有一个类似的公式，那么要获得有意义的回复，您必须在论坛上发布示例工作簿，其中包含有关您要实现的目标的明确说明。
Yes, some of the answers above point out how you can view the formula better, or get rid of some superfluous stuff, or hide the complexity within some VBA (which in my opinion only addresses cosmetics, probably with significant expense in terms of greatly increased recalculation time).
是的，上面的一些答案指出了如何更好地查看公式，或摆脱一些多余的东西，或将复杂性隐藏在某些 VBA 中（在我看来，这仅针对化妆品，可能会在大大增加重新计算时间）。
But without knowing the intentof the formula - and of the workbook in which it sits - one can only offer so much advice.
但是如果不知道公式的意图——以及它所在的工作簿——人们只能提供这么多建议。
If you have tens of thousandsof formulas like this in your workbook, then you have a data structure problem, and not a formula problem. The most efficient formula is the one that is avoided. If you were to redesign this workbook from scratch so that it leveraged off of Excel Tables, PivotTables, and perhaps the Advanced Filter, then you would avoid tens of thousands of formulas like this one. Maybe hundreds of thousands of formulas.
如果您的工作簿中有数以万计这样的公式，那么您有数据结构问题，而不是公式问题。最有效的公式是避免使用的公式。如果您要从头开始重新设计此工作簿，以便它利用 Excel 表格、数据透视表和高级筛选器，那么您将避免使用数以万计的此类公式。也许有成百上千的公式。
回答by Sherwood Botsford
FormulaDesk only works on windows.
FormulaDesk 仅适用于 Windows。
In addition to FormulaDesk above:
除了上面的FormulaDesk：
Here's a link to FastExcel's current Beta 3. But they seem to have been around for over a decade. $29 bucks. I think windows only. The site is unclear.
这是 FastExcel 当前 Beta 3 的链接。但它们似乎已经存在了十多年。29 美元。我认为只有窗户。网站不清楚。
Similar functionality from Precision Calc. $12 Has an Nagware free version if you only need this now and then. Windows only.
Precision Calc 的类似功能。12 美元有一个 Nagware 免费版本，如果你只是偶尔需要的话。仅限 Windows。
