在Excel中创建自定义函数

Question

这似乎是Excel必须具备的功能，但我找不到它在哪里。

如何创建一个自定义函数而不使用VBA？（VBA太大了，会导致安全警告等问题）。

例如，我有一个包含几个非常复杂公式的电子表格。每个公式都在多个列中复制。每个列都有数百个条目，因此每个条目都会被复制数百次。如果我进行微调，那么我必须手动填充或从一列复制我的更改到另一列。

一个简单的公式看起来像这样：
=(Payment1 - F$12)*12 + ($D21-H21)
但我想做的是：
=MyFunction(f$12,$D21,H21)
并且实际的"MyFunction"公式只需编写一次即可。

我发现了一些接近我想要的东西。例如，在表格中，Excel将自动复制公式中的更改到该列的其余部分，节省了手动选择范围并执行“填充下去”的步骤。

它还允许对命名单元格进行相对引用，这似乎相当于用户定义的无参数函数。

- joeking

---

http://www.cpearson.com/excel/writingfunctionsinvba.aspx 是一个不错的教程。然而，无法逃避VBA。 - Jüri Ruut

我同意这个说法：“创建用户自定义函数需要使用VBA，没有其他方法。”（用户自定义函数）[http://www.ozgrid.com/Excel/free-training/ExcelVBA1/excelvba1lesson21.htm] - Daniel

您可以在定义名称中集成函数，但它们不会使用参数。然而，您可以将大多数运算符、函数以及相对地址和绝对地址都集成到命名区域中。 - nutsch

Answer 1

如果您可以使用文本创建公式，那么您可以定义一个名称来评估该函数。

在单元格A2中，创建名称EvalAbove，在Refers To中输入=evaluate(A1)

这样，您就可以构造一个公式，例如B1包含SUM，B2包含=(" "&B1&"(A2:A5)")，然后在B3中，您可以放置=EvalAbove 这意味着如果您更改B1中的公式名称，则B2将更改以显示更改后的公式，而B3将更改以显示结果。

请注意，这仍然算作启用宏的工作簿，但没有VBA代码，只有命名范围。

- SeanC

---

这个代码可以运行，但我发现只有当我编辑B3时，“B3”才会刷新。如果我更改A2:A5范围内的数据，则B3中的总和不会更新，即使我在“数据”中单击“全部刷新”。但是，编辑B3（而不是更改它）可以使其刷新。 - joeking

Answer 2

如果我理解正确，您可以对所展示的示例进行此操作。否则，您可能需要稍微重新排列一下以符合要求。

您的函数有三个参数：

第一个来自当前列的第12行， 第二个来自当前行的D列， 第三个来自当前行右侧第二列。 我假设Payment1已经是一个命名变量了？

将光标设置在F21中，然后定义此名称：

MyFunction =(Payment1 - F$12)*12 + ($D21-H21)

这将设置参数来自所示位置。

为了更好地理解，请切换到RC模式并将公式键入为：

 =(Payment1 - R12C)*12 + (RC4-RC[+2])

你现在可以通过F列传递公式。

=MyFunction

它将始终使用相应的F12列Dxx和Hxx列中的值。如果你将公式拖到下一列，它将使用G12、Dxx和Ixx。如果你想要更改公式，请在定义名称空间中进行编辑。

这是规则的一般例外，即你不能在Excel中有非VBA UDF。通常在Excel中，你希望作为函数“参数”的东西实际上是固定的位置（行或列），可以相对地进行寻址。

例如，你经常希望在左侧单元格上执行udf。

因此，给出左侧单元格的立方根的udf将是一个命名公式，如下所示：

Cuberoot = (RC[-1])^(1/3)

或者在A1格式中，在B1中设置光标并输入=(A1)^(1/3)。Excel会在内部将其转换为RC形式。

对于三个参数-使用三列。

它有效，并且不会遭受关于evaluate()的易变性问题。

是的，我知道这是一个旧帖子，但它可能会帮助遇到相同问题的人。

Bob J.

- user2092957