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

Question

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

Answer 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

Чтобы использовать этот код, нажмите Alt+F11 в Excel, вставьте новый модуль и вставьте код.

Таким образом, если, например, вы хотите суммировать значения B2 каждого листа, отличного от активного листа, вы должны использовать следующую формулу массива (вводится нажатием Ctrl+Shift+Enter):

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

Чтобы включить значение B2 на активный лист, используйте формулу массива:

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

Чтобы суммировать только значения B2 на листах, которые включают "Ежемесячно" в имени листа (не включая активный лист), используйте формулу массива:

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

Обратите внимание на две вещи:

1. AcrossSheets возвращает массив значений. Из-за этого его следует использовать только в формулах массива.
2. Массив, возвращаемый AcrossSheets содержит значения в виде строк (текст). Если вы ожидаете, что числовые данные будут возвращены функцией (как в примерах выше), вы должны обернуть вызов функции в VALUE() . Это преобразует строковые данные в числовые данные.