![如何在 Excel 中进行递归计算?](https://linux22.com/image/1348732/%E5%A6%82%E4%BD%95%E5%9C%A8%20Excel%20%E4%B8%AD%E8%BF%9B%E8%A1%8C%E9%80%92%E5%BD%92%E8%AE%A1%E7%AE%97%EF%BC%9F.png)
我有一个大型电子表格文档,我想拥有一个可以计算所有工作表中的字段的主工作表。我知道我可以通过明确命名工作表然后选择我感兴趣的行来从工作表中选择一个单元格。但是,有没有办法递归选择一个单元格,这样当我添加更多工作表时,它们会自动包含在主工作表中的计算字段中?
答案1
一种方法是使用可以在公式中调用的 VBA 函数。下面的函数将返回除活动工作表之外的所有工作表中单元格地址的内容(这不适用于当前形式的范围)。
Public Function AcrossSheets(rngAddress As String, Optional includeThisSheet As Boolean = False, Optional wText As String) As Variant
Application.Volatile
Dim tmpResults As String, ws As Worksheet
If includeThisSheet Then
For Each ws In Worksheets
If wText <> "" Then
If InStr(ws.Name, wText) <> 0 Then
tmpResults = tmpResults & ws.Range(rngAddress).Value & ", "
End If
Else
tmpResults = tmpResults & ws.Range(rngAddress).Value & ", "
End If
Next ws
Else
For Each ws In Worksheets
If ws.Name <> ActiveSheet.Name Then
If wText <> "" Then
If InStr(ws.Name, wText) <> 0 Then
tmpResults = tmpResults & ws.Range(rngAddress).Value & ", "
End If
Else
tmpResults = tmpResults & ws.Range(rngAddress).Value & ", "
End If
End If
Next ws
End If
tmpResults = Left(tmpResults, Len(tmpResults) - 2)
AcrossSheets = Split(tmpResults, ", ")
End Function
要使用此代码,请在 Excel 中按Alt+ F11,插入新模块,然后粘贴代码。
因此,例如,如果您想要计算B2
除活动工作表之外的每个工作表的值的总和,则可以使用以下数组公式(按Ctrl+ Shift+输入Enter):
=SUM(VALUE(AcrossSheets("B2")))
若要在活动工作表中包含值B2
,请使用数组公式:
=SUM(VALUE(AcrossSheets("B2",TRUE)))
B2
要仅对工作表名称中包含“Monthly”(不包括活动工作表)的工作表的值求和,请使用数组公式:
=SUM(VALUE(AcrossSheets("B2",FALSE,"Monthly")))
请注意两件事:
AcrossSheets
返回值数组。因此,它只应在数组公式中使用。- 返回的数组
AcrossSheets
包含字符串(文本)形式的值。如果您希望函数返回数值数据(如上例所示),则必须将函数调用包装在 中VALUE()
。这会将字符串数据转换为数值数据。