Как сделать рекурсивные вычисления в Excel?

20866
Walter Cecil Worsley IV

У меня большой документ электронной таблицы, и я хочу иметь основной рабочий лист, который рассчитывает поля из всех рабочих листов. Я знаю, что могу просто выбрать ячейку на рабочем листе, явно назвав рабочий лист, а затем строку, которая меня интересует. Однако есть ли способ рекурсивного выбора ячейки, чтобы при добавлении большего количества листов они автоматически включены в расчетное поле в мастер лист?

4
Вы в порядке с использованием VBA? Excellll 11 лет назад 0
Можете ли вы указать немного дальше, что вам нужно? Что вы имеете в виду под "вычисляет поля из всех листов"? Хотите ли вы суммировать ячейки по всем листам? Какой расчет вы имеете в виду? Peter Albert 11 лет назад 0
Вы, вероятно, имеете в виду цикл, а не рекурсию. Совершенно разные подходы. Lee Taylor 11 лет назад 2

1 ответ на вопрос

2
Excellll

One way would be to use a VBA function that you can call in a formula. The function below will return the contents of a cell address (this will not work for ranges in its current form) across all worksheets other than the active sheet.

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

To use this code, press Alt+F11 in Excel, insert a new module, and paste in the code.

So, if, for example, you want to sum the values of B2 from each sheet other than the active sheet, you would use the following array formula (entered by pressing Ctrl+Shift+Enter):

=SUM(VALUE(AcrossSheets("B2")))

To include the value of B2 on the active sheet, use the array formula:

=SUM(VALUE(AcrossSheets("B2",TRUE)))

To only sum the values of B2 on sheets that include "Monthly" in the sheet name (not including the active sheet), use the array formula:

=SUM(VALUE(AcrossSheets("B2",FALSE,"Monthly")))

Note two things:

  1. AcrossSheets returns an array of values. Because of this, it should only be used in array formulas.
  2. The array returned by AcrossSheets contains values as strings (text). If you expect numerical data to be returned by the function (as in the examples above), you must wrap the function call in VALUE(). This will convert the string data to numerical data.

Похожие вопросы