☰

黑客等级 1

tag-icon • tag-icon microsoft-excel worksheet-function
黑客等级 1

您能告诉我我在这里尝试做的事情是否可以以某种方式实现吗？我觉得 INDIRECT 可能会派上用场，但我的脑子里还没有想出它会是什么样子。我的目标在概念上类似于假设分析，但它不是使用表格，而是使用引用主公式和一些输入的单个单元格公式。假设分析在这里不起作用，因为输入在我的分析表中没有整齐地组织起来。

以下是故事内容：

我正在创建一个电子表格（“AnalysisSheet”），用于预测和分析啤酒酿造过程中的结果。

我在整个过程中进行了许多测量以进行质量控制，并且通常会将相同的公式应用于各个步骤的多个原始数据点进行分析（例如，通过简单的热膨胀近似将热液体体积转换为冷液体体积）。

这些公式可能很复杂，每次重复都会很麻烦，因此我想创建一个单独的工作表（“FormulaSheet”），其中整齐地布局了“主”公式（以及虚拟输入），这样我就可以简单地从“AnalysisSheet”中引用这些主公式以及要替换到虚拟输入中的输入值。

以下是一个简单公式的示例：

在“公式表”：

A1：20 [音量虚拟输入值]

A2：80 [温度虚拟输入值]

A3：=A2*(1-0.04*(A3-20)/80) [“主”公式]

现在“分析表”：

F7：90 [温度读数]

F11：40 [音量读数]

F13：=计算与 FormulaSheet!A3 相同，但将其输入引用 FormulaSheet!A1 替换为 AnalysisSheet!F11，将其输入引用 FormulaSheet!A2 替换为 AnalysisSheet!F7

我的问题基本上是：

需要在 F13 中插入​​什么才能执行我所描述的操作？

答案1

我不确定你是否能做到你所描述的，但我确实看到了一个关于定义你自己的功能的非常好的页面：
https://www.dummies.com/software/microsoft-office/excel/how-to-create-custom-excel-functions/

它确实要求您用 VBA 编写函数，但这并不像听起来那么难。

  1. 打开开发人员工具。通常您需要进入 Excel 首选项 > 视图 > 显示开发人员选项卡。
  2. 进入新显示的“开发人员”选项卡并单击Visual Basic。
  3. 在打开的 VB 编辑器窗口中，选择Insert > Module。
  4. 输入您的功能

例如：

Public Function HotToColdVolume(temperature As Integer, volume As Integer)
    HotToColdVolume = volume * (1 - 0.04 * (temperature - 20) / 80)
End Function

注意您需要将电子表格另存为宏启用工作簿（.xlsm）。

现在，您可以在电子表格中简单地调用：

=HotToColdVolume(80,20)

答案2

好吧，这种方式不使用 VBA，但可以说更糟糕。显然，Excel 有这些“Excel 4.0”函数，您可以在定义的名称中使用它们。它们可以做您想做的事情，但仍需要将它们保存在文件中.xlsm：
Excel 4.0 定义名称

黑客等级 1

有一个名为EVALUATE（我找不到关于此的任何官方文档，但是这一页解释一下）它可以将文本作为函数进行求值。但您不能直接将其输入到单元格中，它需要从命名范围“运行”：
命名范围评估

当然，问题是如果您尝试评估公式，A2*(1-0.04*(A3-20)/80)您就无法指定自己的输入。

黑客等级 2

在您的主公式中您可以指定OFFSET，但你需要一个引用单元格。你不能仅仅A1出于与之前相同的原因引用。你需要一种方法来引用“这个”单元格。输入我发现的另一个天才黑客这里. 选择单元格A1，创建命名区域（例如THIS_CELL），输入公式=!A1（！只是意味着“这张表”）。

现在，如果您想引用紧邻右侧的单元格（1 列），您可以使用：

OFFSET(THIS_CELL,0,1)

因此，在主公式页面上，您将公式放在 A 列，将参数放在 B、C、D 等列。您会得到非常丑陋的公式，如下所示：

=OFFSET(THIS_CELL,0,1)*(1-0.04*(OFFSET(THIS_CELL,0,2)-20)/80)

Excel 偏移公式

结合技巧

现在你只需要创建一个命名范围，它EVALUATE是FORMULATEXT带有内置偏移的奇特公式：
Excel 最终命名范围

=EVALUATE(FORMULATEXT(MasterFormulaTest!$A$2))

将命名范围输入到单元格中，就完成了！

Excel 最终公式

所有这些都无需 VBA，缺点与使用 VBA 相同，但比单纯使用 VBA 要复杂得多。彻底糟糕。

相关内容