在其他电子表格中将单元格作为公式的输入参数传递

32 浏览
0 Comments

在其他电子表格中将单元格作为公式的输入参数传递

我的工作簿看起来像这样：

Sheet1:

A B C D

1 变量名 乘数1 乘数2 乘数3

2 变量1 0.3 0.8 0.4

3 变量2 0.4 0.9 0.1

...

100 变量100 0.2 0.2 0.7

101 参数1 参数2 索引

102 Sheet2!A2 Sheet2!B2 像 =SUMPRODUCT(B2:B100, C2:C100, D2:D100)+C102/B102 这样复杂的公式

Sheet2:

A B C

1 参数1 参数2 最终结果

2 100 25 像 =IF(Sheet1!D102<1, 1, Sheet1!D102) 这样复杂的公式

Sheet3:

A B C

1 参数1 参数2 最终结果

2 110 30 ?

3 140 40 ?

...(大约有200,000行)

更多信息：

在Sheet1和Sheet2中，实际的公式比这里显示的要复杂得多。在Sheet1中，我们从Sheet2的7个基本参数计算出154个中间变量，求和乘积只是一个中间函数。在Sheet2中，根据来自Sheet1的参数和索引计算出15种不同的情况。因此，基本上我无法硬编码它们在一起，唯一的入口是在Sheet2中。我不改变Sheet1和Sheet2的另一个原因是，这是从其他公司购买的模型，我们被告知使用它的方式是在Sheet2中输入参数。

我该如何在Sheet3的C列中编写公式，以便可以计算出每对参数1和参数2的结果？我尝试录制宏来复制和粘贴，如下所示：

Sub ToEnd()
Dim i As Long
Application.ScreenUpdating = False
i = 1
While i < 200000
    Worksheets("Sheet3").Range("A1:B1").Offset(i, 0).Select
    Selection.Copy
    Sheets("Sheet2").Select
    Range("A2").Select
    ActiveSheet.Paste
    Range("C2").Select
    Selection.Copy
    Sheets("Sheet3").Select
    Range("C1").Offset(i, 0).Select
    Selection.PasteSpecial Paste:=xlValues
    i = i + 1
    Wend
End Sub

但是在6秒钟内我只能得到200个结果，我估计一个案例的时间复杂度最多为10^4，所以比我预期的要慢得多。我想知道是否可以在Sheet3的C2中编写一些类似于

=PassingParameterAndGetReturnValue(A2:B2, Sheet2!$A$2:$B$2, Sheet2!$C$2)

这样我就可以使用内部循环自动填充，从而更快地完成任务了？或者还有其他更好的方法来完成我的任务？

0

1 答案

0 Comments

问题的原因是 Excel 环境不允许更改其他单元格的值，因此无法通过自定义函数来实现。解决方法是将 Sheet2 的公式移动到 Sheet1 中，并将它们合并为一个公式。此外，还可以使用 VBA 来创建自定义函数，但这需要根据实际情况进行高度定制的编码。如果需要更多帮助，可以提出问题。另外，也可以将所有公式合并到 Sheet1 中并进行一些繁琐的硬编码，这样可以提高运行速度。

0