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

Question

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

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

4

microsoft-excel recursion

Источник

Walter Cecil Worsley IV 27 янв '13 в 20:21

1 ответ

Другие вопросы по тегам microsoft-excel recursion

Excellll 27 янв '13 в 22:17 2013-01-27 22:17 · Answer 1 · 2013-01-27 22:17

Одним из способов будет использование функции 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")))

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

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