Como fazer cálculos recursivos no Excel?

Question

Eu tenho um grande documento de planilha e quero ter uma planilha principal que calcule os campos de todas as planilhas. Eu sei que posso apenas selecionar uma célula de uma planilha, nomeando explicitamente a planilha e, em seguida, a linha que me interessa. No entanto, existe uma maneira de selecionar recursivamente uma célula para que, à medida que eu adiciono mais planilhas, elas sejam automaticamente incluído no campo calculado na planilha principal?

    

Answer 1

Uma maneira seria usar uma função do VBA que você possa chamar em uma fórmula. A função abaixo retornará o conteúdo de um endereço de célula (isso não funcionará para intervalos em seu formato atual) em todas as planilhas que não sejam a planilha ativa.

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

Para usar este código, pressione Alt + F11 no Excel, insira um novo módulo e cole o código.

Portanto, se, por exemplo, você quiser somar os valores de B2 de cada folha diferente da planilha ativa, use a seguinte fórmula de matriz (digitada pressionando Ctrl + < kbd> Shift + Enter ):

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

Para incluir o valor de B2 na planilha ativa, use a fórmula de matriz:

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

Para somar os valores de B2 nas planilhas que incluem "Mensalmente" no nome da planilha (sem incluir a planilha ativa), use a fórmula de matriz:

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

Observe duas coisas:

1. AcrossSheets retorna uma matriz de valores. Por causa disso, ele só deve ser usado em fórmulas de matriz.
2. A matriz retornada por AcrossSheets contém valores como strings (texto). Se você espera que os dados numéricos sejam retornados pela função (como nos exemplos acima), você deve agrupar a chamada de função em VALUE() . Isso converterá os dados da string em dados numéricos.