将单元格作为公式的输入参数传递到其他电子表格中-Java 学习之路

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

Sheet1:
      A       B            C            D
1     VarName Multiplier1  Multiplier2  Multiplier3
2     Var1    0.3          0.8          0.4
3     Var2    0.4          0.9          0.1
...  
100   Var100  0.2          0.2          0.7
101           Parameter1   Parameter2   Index
102           Sheet2!A2    Sheet2!B2    Complicated formulas like =SUMPRODUCT(B2:B100, C2:C100, D2:D100)+C102/B102

Sheet2:
         A            B            C
1        Parameter1   Parameter2   FinalResult
2        100          25           Complicated formulas like =IF(Sheet1!D102<1, 1, Sheet1!D102)

Sheet3:
         A            B            C
1        Parameter1   Parameter2   FinalResult
2        110          30           ?
3        140          40           ?
...(about 200,000 rows)

More informaton: 在Sheet1和Sheet2中，实际公式比这里显示的公式复杂得多 . 在Sheet1中，我们有来自Sheet2的7个基本参数计算出的154个中间变量，sumproduct只是一个中间函数 . 在Sheet2中，根据从Sheet1计算的参数和索引，有15种不同的情况 . 所以基本上我不能将它们硬编码在一起，唯一的入口是在Sheet2中 . 我不会改变Sheet1和Sheet2的另一个原因是因为这是从其他公司购买的模型，我们被告知使用它的方式是在Sheet2中输入参数 .

如何在Sheet3的C列中编写公式，以便我可以计算每对Parameter1和Parameter2的结果？我尝试录制宏来复制和粘贴如下：

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)

我可以用内循环自动填充因此更快？或者任何其他更好的方式来完成我的任务将是受欢迎的 .

1 回答

1
更新答案：

我需要一个函数f看起来像f（x）{把x放在Sheet2的正确位置;等到Sheet2完成计算;从Sheet2的另一个地方获取结果并返回} = PassingParameterAndGetReturnValue（A2：B2，Sheet2！$ A $ 2：$ B $ 2，Sheet2！$ C $ 2）

你想要的 custom function (UDF) requires changing the values of other cells which is not possible because of the Excel environmental . 你正在做的事情（"cells copy & paste"）是通过VBA Sub 程序运行的 . 但您可能也注意到这不是一个好主意，因为单元格复制和粘贴是在前端执行的，屏幕更新速度很慢 . Find more details in this post: Set a cell value from a function.

正如我之前所说，无论表1中涉及多少变量/公式，您的表2都是多余的 . 它将您的计算分开并增加您的工作流程 . 表2输入参数显然是从表3中虚拟 . 您可以将表2公式移动到表1以保持一致 .

如果您确实需要自定义函数，那么您可以做的唯一方法是按照我之前发布的以下步骤修改您的工作表结构 . 或者您也可以通过查看每个计算工作流程详细信息来编写需要高度定制编码的Sub . 同样，代码/公式可以根据您的方案的实际情况而有所不同 . 除非得到整本工作簿，否则我不能在这里为你编写代码 .

我不建议您使用VBA方法来完成您的任务（编写Sub或UDF） . 据我所知，您的任务可以通过普通的工作表函数单独完成 . 我希望你能从下面的工作表图片中得到一些提示 .

早期答案：

实际上，您不需要VBA和Sheet 2.您可以通过一个公式获得结果：

基本工作表公式函数方法

我可以看到sheet2的目的仅用于单个计算（公式 Sheet2!D102 ） . 另一个公式存储在Sheet1（公式 Sheet1!D102 ）中 .

实际上，您可以将 Sheet1!D102 和 Sheet2!D102 中的两个公式合并为一个公式 .

例如，在Sheet3单元格C2中，您可以使用以下公式：
```
=IF((SUMPRODUCT(Sheet1!$B$2:$B$11,Sheet1!$C$2:$C$11,Sheet1!$D$2:$D$11)+(B2/A2))<1,1,(SUMPRODUCT(Sheet1!$B$2:$B$11,Sheet1!$C$2:$C$11,Sheet1!$D$2:$D$11)+(B2/A2)))
```
以上公式逻辑可以简化为：
```
=IF( (SumProductResult+(Parameter2/Parameter1))<1 , 1 , (SumProductResult+(Parameter2/Parameter1)) )
```
Logic & algorithm (not formula / code):
```
If (SumProductResult+(Parameter2/Parameter1)) < 1  
Then show/Return 1  
Else show/Return (SumProductResult+(Parameter2/Parameter1))  
End If
```
VBA方法

如果您真的希望VBA这样做，那么您可以使用VBA创建自定义函数（仍使用上述算法） .
```
Function FinalResultReturn(SumProductResultCell As Double, Parameter1 As Double, Parameter2 As Double) As Double
    ComputedValued = SumProductResultCell + (Parameter2 / Parameter1)

    If ComputedValued < 1 Then
        FinalResultReturn = 1
    Else
        FinalResultReturn = ComputedValued
    End If
End Function
```
如何使用此VBA：
- 创建VBA模块并粘贴上面的代码以创建新的自定义功能 .
- 现在，您可以在工作表中使用自定义函数 .
Sytnax:
```
=FinalResultReturn( SumProductResultCell , Parameter1 , Parameter2 )
```
Example:
在Sheet3单元格C2中输入：
```
=FinalResultReturn(Sheet1!$D$999,A3,B3)
```
其中 Sheet1!$D$999 是SumProductResultCell，即 =SUMPRODUCT(B2:B100, C2:C100, D2:D100 ） .
（PS：我创建了这个SumProductResultCell，用于最小化自定义函数的输入参数 . ）

我希望我没有误解你的工作表计算工作流程 . 如果是，请告诉我并进行修改 .
回复于 2024-04-26T21:52:29+08:00

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

1 回答

更新答案：

早期答案：

基本工作表公式函数方法

VBA方法

如何使用此VBA：

相关问题