这似乎很明显，excel 必须具有此功能，但我找不到它.
This seems like such an obvious thing that excel must have this feature, I just can't find it.
如何在不使用VBA的情况下创建自定义函数? (VBA太大了，会引发安全警告等.
How can I create a custom function without using VBA? (VBA is too big of a hammer and causes security warnings, etc).
例如，我有一个包含几个非常复杂的公式的电子表格.这些公式中的每一个都复制到几列中.每列都有数百个条目，因此每列都重复了数百次.如果我要进行调整，则必须手动填写或将更改从一列复制到另一列.
For example, I have a spreadsheet with several very complex formulas. Each of these formulas are replicated in several columns. Each column has hundreds of entries, so each one is replicated hundreds of times. If I tweak something then I have to manually fill-down or copy my change from one column to another.
一个简单的看起来像这样: =(付款1-F $ 12)* 12 +($ D21-H21) 但是我想做的是: = MyFunction(f $ 12，$ D21，H21) 并仅在某个地方编写一次"MyFunction"的实际公式.
A simple one looks like this: =(Payment1 - F$12)*12 + ($D21-H21) But what I'd like to do is: =MyFunction(f$12,$D21,H21) And have the actual formula for "MyFunction" written just once someplace.
我发现一些东西几乎可以满足我的需求.例如，在表格中，Excel会自动在公式的其余部分向下复制公式中的更改，从而省去了手动选择范围并执行填充"的步骤.
I've found a few things that come close to giving me what I want. For example, in tables Excel will automatically replicate changes in a formula down the rest of the column saving you the step of manually selecting the range and doing a "Fill Down".
它还将允许命名单元格的相对引用，这似乎等效于用户定义的无参数函数.
It will also allow relative references off of named cells, which seems equivalent of a user-defined parameter-less functions.
推荐答案如果可以使用文本创建公式，则可以定义 name 来评估函数.
if you can use text to create the formula, then you can define a name to evaluate the function.
在单元格A2中创建一个名称 EvalAbove ，然后在引用"中输入=evaluate(A1)
In cell A2, create a name EvalAbove, and in Refers To, enter =evaluate(A1)
这样，您可以构造一个公式 例如B1包含SUM，B2包含=("="&B1&"(A2:A5)") 然后在B3中将=EvalAbove
This way, you can construct a formula e.g. B1 contains SUM, B2 contains =("="&B1&"(A2:A5)") and in B3, you can then put =EvalAbove
这意味着如果您在B1中更改公式名称，则B2将更改以显示更改后的公式，而B3将更改以显示结果.
This means that if you change the formula name in B1, then B2 will change to show the changed formula, and B3 will change to show the result.
- 请注意，这仍然算作启用了宏的工作簿，但是没有VBA代码，只是命名了范围
更多推荐
在Excel中创建自定义函数
发布评论