☰

不使用 VBA 的自定义用户函数

tag-icon • tag-icon microsoft-excel worksheet-function vba
不使用 VBA 的自定义用户函数

是否可以在不使用 VBA 的情况下在 Excel 中创建自定义用户函数？
我的问题是，我有一长串标准 Excel 函数串在一起形成一个非常长的函数。此函数用于我的工作簿中的 25 个不同工作表。如果我需要对其进行更改，我希望只在一个地方进行更改，并将更改自动传播到所有工作表。

例如，一个简单而普通的情况是将 SUM() 的结果加一，即 SUM(mySeries)+1，并将其命名为新函数 MYSUM()。

我犹豫是否将长公式转换成 VBA，因为可能会出现错误并且会增加复杂性。

答案1

是的，如果你使用Excel 命名公式。

例如，假设您需要计算两个连续列的总和之间的差值（A5：Ax — B5：Bx）在工作簿中的不同位置（X是每列的最后一行）：

因此你定义A11一个名字叫差异（可以使用任何名称）作为=总和（A$5:A10）-总和（B$5:B10），假设数据从行开始5直到上一行。它可以是任何单元格，而不仅仅是A11，但定义也会发生相同的变化。

很遗憾，Excel 2010插入绝对前缀 ($）和工作表前缀，因此您需要删除前缀，但保留感叹号并删除大多数$人物。

移动公式时，大多数引用都是相对的。因此，它总是从行开始计算当前列与下一列之间的差值5直到总行之前那一行。

因此，如果你有介于C5和D100你把=差异在C101并计算总和 (C5:C100) - 总和 (D5:D100)。

显然，您可以在命名公式中使用本地或全局名称，就像您在问题中提到的那样。

您可以在阅读更多详细信息命名公式。

答案2

我知道您说不要 VBA，但是执行以下操作不是要求您真正重写公式，了解 VBA 的很多知识，也不需要维护 VBA 中的公式。您可以编写一次，然后忘掉它。

创建一个用户定义函数，从单元格中提取公式作为字符串：

Function GetFormula(Target As Range) As String
    GetFormula = Target.Formula
End Function

创建另一个来像公式一样评估字符串：

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

如果您的主公式在 sheet1!a1 中，则应将其放入每个需要使用它的单元格中：

=eval(getformula(sheet1!a1))

答案3

这个线程有点旧了，但是我在寻找其他东西时偶然发现了它，并想分享一下我之前创建的一本工作簿，试图完成这个确切的事情：https://www.dropbox.com/s/gf5qrjj5q81tpke/Title_Case_Named_Range_Function.xlsx?dl=1

简而言之，您可以通过劫持 Excel 的一个内置公式（带有 0 长度字符串输出）并想出一种方法通过命名范围字符串操作来解释插入的数据，从而创建不使用 VBA 的 UDF（某种程度上），但这样做效率极低。您已收到警告！:)

方法：

  1. 在示例中，我使用了=REPT([单元格引用或此处引号之间的某些文本],0)和=TEXT([单元格引用或此处引号之间的某些文本],";;;")作为起点，能够将某种形式的用户输入放入内置公式中，而该公式不会在单元格本身中产生任何可见的文本结果。

  2. 然后我创建了一个命名范围这相当于你的 UDF 名称（酶联免疫吸附试验是本表中的主要示例，但其中也有几个其他变体）。这命名范围实际上引用了工作簿中一大堆其他隐藏的名称来提取公式文本从其所用的单元格中获取结果，然后对其应用某种逻辑以生成字符串形式的最终输出。值得注意的是，此方法只能返回文本结果，而不能返回数字（尽管它可以将数字作为文本返回，以便在单独的单元格中进行转换）。

  3. 在其自己的，=TCase会产生错误，因为没有单元格/字符串引用可供其解释（在示例工作表中，错误消息只是提醒需要遵循的格式）。但是，结合酶联免疫吸附试验使用预期的 0 长度字符串 Excel 公式允许它将公式中的输入读取为字符串并将您的逻辑应用于它。最后，公式/输出将如下所示。请注意，我在下面公式中直接插入了文本字符串，但您会在示例表中看到它也可以解释单个单元格引用。

    公式：
    =TCase&REPT("i want to convert this mIxEd sTRing into A TITLE cAsE string!!!",0)

    输出：

    I Want to Convert This Mixed String Into a Title Case String!!!

最后，我会不是建议大家在遇到任何情况时都遵循这种方法，除非出于病态的好奇心，看看 Excel 能达到什么极限。VBA 是一个更简单、更优雅的 UDF 解决方案，但我至少很乐意接受这个挑战。

笔记：如果你想更轻松地了解示例文件中的工作原理，请使用评估公式从公式功能区中，在插入示例的单元格中进入/退出公式。我隐藏了所有底层命名区域，以便在完成后清理它们，这样它们就不会出现在姓名经理除非你将其全部取消隐藏。

答案4

以下内容基于@Kirk 的回答，但我想提供他所使用的技术的 MRE/MCVE。

在此处输入图片描述

在名称管理器中添加以下名称：

Name            Refers to
myfn            =INDIRECT(myfn.reference)+1
myfn.debug      =myfn.reference
myfn.formula    =FORMULATEXT(myfn.self)
myfn.reference  =MID(myfn.formula;myfn.textcall+1;LEN(myfn.formula)-(myfn.textcall+1)-2)
myfn.self       =INDIRECT("RC";FALSE)
myfn.textcall   =FIND("(";myfn.formula)

然后使用相对引用调用“函数”，如下所示：

=myfn&REPT(B2;0)

或使用绝对引用（或任何其他类型的引用）：

=myfn&REPT($B$2;0)

注意：要翻译成 fr-fr，只需将“RC”替换为“LC”

相关内容