Fórmulas na Matriz Multi-Planilhas (MATRIZ.MULT)

Faça uma pergunta

Resumo:


Introdução


Só as fórmulas "Soma, Média, Nb, Nbval" permitem fazer cálculos em campos descontínuos ou Multi-Planilhas (Cálculo em 3 Dimensões).

Exemplo :
=Soma(Plan1:Plan3!A1:A10) : efetua a soma do trecho A1:A10 das 3 planilhas.


Soma.si, Nb.si, Somaprod, Index, Equiv e as fórmulas matriciais não o fazem, mesmo com trechos nomeados

O objetivo desta função personalizada não é o de reescrever as diversas fórmulas mas, de transformar um trecho descontínuo ou um trecho em certo número de planilhas, em uma única matriz.

Sintaxe


=Trf_plage(Meu trecho; "Plan1:Plan3")
  • Meu trecho: O trecho a ser processado, ex: A1:A10
  • " Plan1:Plan3" : Os trechos a serem processados, um bloco de planilhas (1a Folha: Última Folha) este parâmetro é opcional, para os trechos descontínuos que não precisam de Multi-Planilhas.

Exemplos


Quero precisar que as fórmulas ou nb.si ou soma.si ainda não funcionam, mas podem ser facilmente transformadas em fórmulas matriciais.

Índice associado ao Equiv funciona muito bem. (Agora temos uma busca Multi-Planilhas).
As fórmulas com fundo cor-de-rosa são fórmulas matriciais.

Elas devem ser validadas, pressionando simultaneamente as teclas Ctrl + Shift + Enter. Se a validação for feita corretamente, chaves ({}) enquadrarão a fórmula, automaticamente.


Saiba mais clicando aqui

Código


Veja o código da função personalizada, a ser colocado em um módulo padrão.

Function M_Charge(plage As Range, Optional planilhas As String = "") As Variant   
    Dim cel As Range, i As Long, j As Integer, tablo() As Variant, tablof() As Variant   
    Dim f As Integer, plan1 As String, plan2 As String   
    Application.Volatile 'Permite o cálculo automático   
' Definição da planilha padrão, se nenhuma planilha for mencionada   
    If planilhas = "" Then planilhas = ActiveSheet.Name & ":" & ActiveSheet.Name   
    i = -1   
    If InStr(planilhas, ",") > 0 Then ' processamento das planilhas descontínuas (separadas por vírgulas)   
        While InStr(planilhas, ",") > 0   
            i = i + 1   
            ReDim Preserve tablof(i)   
            tablof(i) = Left(planilhas, InStr(planilhas, ",") - 1)   
            feuilles = Mid(planilhas, InStr(planilhas, ",") + 1, Len(planilhas) - InStr(planilhas, ","))   
        Wend   
    End If   
    i = i + 1   
    ReDim Preserve tablof(i)   
    tablof(i) = planilhas  
    i = -1   
    For f = LBound(tablof) To UBound(tablof) ' processa os diversos blocos de planilhas  
        planilhas = tablof(f)   
        If InStr(planilhas, ":") = 0 Then planilhas = planilhas & ":" & planilhas 'eu crioi o bloco a planilha está sozinha   
' Recuperação da planilha do começo e do fim   
        plan1 = Left(planilhas, InStr(planilhas, ":") - 1)   
        plan2 = Right(planilhas, Len(planilhas) - InStr(planilhas, ":"))   
' Revisão de todas as planilhas entre Plan1 e Plan2   
        For j = Sheets(plan1).Index To Sheets(plan2).Index  ' Each ws In Sheets(planilhas)   
            For Each cel In plage ' Para cada página do trecho   
                i = i + 1   
                ReDim Preserve tablo(i) 'eu incremento a tabela que está sendo criada   
                tablo(i) = Sheets(j).Cells(cel.Row, cel.Column).Value 'Recuperação do valor   
            Next   
        Next j   
    Next f   
        M_Charge = tablo 'Atribuição da tabela à função (a matriz foi criada)   
End Function   


Jean-François Pillou

Nosso conteúdo é produzido em colaboração com especialistas em tecnologia da informação sob o comando de Jean-François Pillou, fundador do CCM.net e diretor digital do Grupo Figaro. CCM é um site sobre tecnologia líder em nível internacional e está disponível em 11 idiomas.

Mais informações sobre a equipe do CCM

Veja também