☰

用公式文本的 vlookup 替换 Excel 嵌套 IF

tag-icon • tag-icon microsoft-excel
用公式文本的 vlookup 替换 Excel 嵌套 IF

我正在制作一个用于物料需求计划的电子表格（只是为了好玩）。一个问题是批量规则。我必须在很多地方使用 OFFSET 和 INDIRECT 函数，而且公式非常难读（而且很长！）。

有没有办法将不同规则的公式文本放入数组并根据 VLOOKUP 选择它们？我希望找到一个执行以下操作的函数：

=FORMULAFROMTEXT(VLOOKUP(rule,$A1:$B5,formula, FALSE))

其中规则可能是“L4L”、“FOQ”等之一，表格如下所示

L4L     L4L-formula
FOQ     FOQ-formula
...

对我来说，这看起来比

IF(rule="L4L",L4L-formula,IF(rule="FOQ",FOQ-formula,IF(...))).

答案1

您将需要使用 VBA 创建一个函数。

打开 Excel 工作表后，执行以下操作启动 Visual Basic 编辑器：

  • 在 Windows PC 上
    按Alt+ F11。
  • 在 Mac 上
    按FN+ ALT+F11

Insert然后点击Module。

Visual Basic 编辑器的右侧出现一个新的模块窗口。

将以下内容复制到新模块窗口：

Function Eval(Ref As String)
Application.Volatile
Eval = Evaluate(Ref)
End Function

现在您可以关闭 Visual Basic 编辑器，因为您已经创建了一个EVAL(cell reference)公式函数。

VLOOKUP()现在在使用公式时使用这个新函数。

如果要根据数据所在的行来构建公式，例如，

      ¦  A  ¦  B  ¦    C    ¦
-----------------------------
   1  ¦  2  ¦  2  ¦  =A1+B1 ¦
-----------------------------
   2  ¦  4  ¦  2  ¦  =A2+B2 ¦
-----------------------------

和细胞C1并且C2是L4L-formula

然后你需要记住公式的波动性。例如，你不能只输入 A1+B1 L4L-formula。你必须输入没有等号 (=) "B"&ROW()&"+C"&ROW()为了L4L formula。

然后您需要为结果公式创建一个辅助列。

如果你的公式列表如下：

      ¦  A  ¦       B       ¦
-----------------------------
   1  ¦ L4L ¦  L4L-formula  ¦  Let's say this is F + G
-----------------------------
   2  ¦ FOQ ¦  FOQ-formula  ¦  Let's say this is F x G
-----------------------------

如果L4L-formula是F + G，那么如前所述，您需要输入"F"&ROW()&"+G"&ROW()到单元格中B1

如果FOQ-formula是，F x G那么你需要输入"F"&ROW()&"*G"&ROW()到单元格中B2

现在，如果您的工作表在同一张纸上，并且如下所示：

      ¦  F  ¦  G  ¦  H  ¦
-------------------------
   1  ¦  2  ¦  2  ¦ L4L ¦
-------------------------
   2  ¦  4  ¦  2  ¦ FOQ ¦
-------------------------

然后将以下内容放入单元格I1并向下拖动：

=EVAL(VLOOKUP(H1,A:B,2,FALSE))

列I现在是一个辅助列，用于为该行构建所需的公式。当然，您可以在打印时隐藏该列（如果需要）。

对于该公式的结果，您可以将其放入=EVAL(I1)单元格中J1，也可以将其拖下来。

相关内容