在Excel中创建自定义函数 [英] Create a custom function in Excel

问题描述

这似乎很明显，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中创建自定义函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！