☰

在 Excel 中构建和管理复杂函数的建议

tag-icon • tag-icon microsoft-excel worksheet-function
在 Excel 中构建和管理复杂函数的建议

很多时候，我会得到一个公式，该公式将函数包装在函数内的函数内，而当某些新条件要求我将另一个函数包装在其周围时，我常常发现自己完全忘记了哪个括号放在哪里，哪个函数适用于哪组参数，并且通常发现自己在修改产生意外结果后花了半个小时调试语句。

我一直在做的是将函数分成一列中的各个部分...每一行代表一个函数，当我对最终结果感到满意时，我将每一部分复制回到前一个单元格中的相应位置，直到我得到一个完整的、希望可以正常工作的函数。

考虑：

         =IF((ISERROR((VLOOKUP(D2,$A$2:$A9,1,0))),(IF((D2=(VLOOKUP(D2,$A$2:$A$9,1,0))),0,D2)),E2)
0        =IF((ISERROR((VLOOKUP(D2,$A$2:$A9,1,0))),L17,E2)
TRUE     =ISERROR((VLOOKUP(D2,$A$2:$A9,1,0))
0        =IF((D2=L18),0,D2)
the      =VLOOKUP(D2,$A$2:$A$9,1,0)

如果能够以内联方式记录，或者使用类似于 VBA 弹出窗口来编辑公式，那就太好了，但由于没有存在，我很想知道您发现哪些其他技术对有效构建复杂函数很有帮助。您有什么看法？

答案1

以下是我自己使用并建议员工在培训期间使用的一系列主要规则：

  1. 建立复杂的公式（尤其IF是 s）逐部分（或逐个参数），即如果您有一列数据想要根据特定条件进行解析，请从简单开始=IF(LEFT(A1,2)="AB",1,0)- 这样您就可以确保条件按预期工作并为每种类型的值返回正确的结果。此外，您用自己的计算替换每个参数（见下文），但这样您就可以确保任何错误/错误结果不是由错误条件本身引起的，否则错误可能会干扰，并且整体公式结果完全出乎意料，并且极难纠正。
  2. 每个参数都准备在单独的列中，如下所示独立公式，只有当它起作用时，它才会合并到主公式中（或超级公式正如一些 Excel 圣经中所说）。
  3. 使用硬编码值作为中间解决方案。例如，如果您正在构建动态图表，范围通常使用OFFSET和函数计算。因此，首先使用纯数字COUNTA构建，例如，确保它返回您真正想要的内容，然后才开始构建计算部分，例如。OFFSET=OFFSET(A1,0,0,1,10)=OFFSET(A1,0,0,1,COUNTA(A:A)-1)
  4. 不要忽视公式求值器（功能区Formulas > Evaluate Formula）：对于第一次看到它的人来说，它就像一缕光）事实上，拥有正确的语法并不意味着通常拥有预期/正确的结果，这就像 Excel 调试器。

  5. 为了提高复杂公式的可读性，您可以使用新行分隔符ALT（可以通过公式栏中的+添加ENTER）以及额外的空格使它们看起来像带有缩进的代码（在评估公式时所有这些都会被忽略）：

    =IF(
    LEFT(A1,2)="AB",
       MONTH(TODAY()),
       MONTH(B1)
   )

我希望这些建议能让生活变得更轻松）当然其他回答者会添加更多有用的观点。

答案2

一般来说，我认为将较大的公式拆分到多列是最佳做法！

但是，如果您需要构建更大的公式，我建议您做以下几点：

  1. 使用命名范围作为常量 - 使你的公式更容易阅读 - 最好=IF(A1>SalesThreshold,A1,0)有=IF(D2>Assumptions!$B$239,D2,0)
  2. 使用 Excel 表格并用列名替换常规引用。这样，=IF(AND(YEAR(B2)=2013,C2="MyProduct",D2>SalesThreshold),D2,0)就变成了=IF(AND(YEAR([Date])=2013,[Product]="MyProduct",[Sales]>SalesThreshold),[Sales],0)
  3. 如果公式仍然很复杂，您可以使用N()注释函数。该函数会将任何文本转换为 0，因此您可以拥有如下函数：
    =如果(
N4>N20+N("快速检查")，
N4^2+N("需要平方，因为我想不出更好的例子"),
N4/2+N（“不知道在这里评论什么！”））

希望有帮助！

答案3

方程式办公桌是一款全新的免费工具，专为满足您的需求而设计。它以嵌套、滚动的方式格式化和显示公式，并显示每个子部分的结果，让您能够非常快速地获得更好的理解。

[披露：我是 FormulaDesk 的作者]

在此处输入图片描述

相关内容