Olá Excelmaníacos!
Estou escrevendo mais uma vez um post para responder a uma dúvida curiosa que me deparei em um fórum. O usuário relatou que no Excel existe a função SUBTOTAL que possibilita somar somente células em linhas que estão visíveis, e sua dúvida é se existe alguma forma de somar células que estão apenas nas planilhas visíveis da pasta de trabalho, ou seja, se ocultarmos uma planilha, as células referentes àquela planilha não devem ser somadas na fórmula.
Eu desconheço uma alternativa nativa de resolver esta questão, porém o legal do Excel é que podemos desenvolver nossas próprias soluções. E neste caso, eu vou propor uma solução através do desenvolvimento de uma função personalizada pelo usuário (UDF) que testará se o intervalo está numa planilha visível e, caso verdadeiro, retorne o intervalo para a fórmula processar o cálculo, e caso falso, devolva zero.
O código é bastante simples, veja abaixo com comentário nas linhas:
Public Function ÉVÍSIVEL(rng As Excel.Range) As Variant 'Comando que a função seja volátil, ou seja, efetue o recálculo a cada alteração no arquivo Application.Volatile 'Declaração da variável da planilha a ser testada Dim ws As Worksheet 'Atribuir a planilha à variável ws usando a propriedade Parent.Name do range que retornar o nome da planilha Set ws = Sheets(rng.Parent.Name) 'Teste lógico para saber se á planilha é visível (-1 é visível, 0 é oculta e 2 é "muito oculta" (very hidden) If ws.Visible = -1 Then 'Se visível a função retorna o próprio intervalo Set ÉVÍSIVEL = rng Else 'Caso esteja oculta retorna zero ÉVÍSIVEL = 0 'Fim do teste lógico End If 'Atribuição de Nothing a variável para limpar a memória Set ws = Nothing End Function
Este código deve ser inserido num módulo da sua pasta de trabalho no VBE (ALT+F11), conforme imagem exemplo abaixo:
Após inserir o código, pode fechar o VBE, e realizar os cálculos na planilha.
Por exemplo, vamos somar os intervalos A1:A3 da plan2 e da plan3 numa célula da plan1. A fórmula ficaria:
=SOMA(ÉVÍSIVEL(Plan2!A1:A3);ÉVÍSIVEL(Plan3!A1:A3))
Note que para cada intervalo é necessário aplicar a função para fazer a validação de visibilidade.
As observações que deixo é:
1º) Você terá que salvar o arquivo como .xlsm (pasta de trabalho habilitada para macros do excel).
2º) A ação de ocultar uma planilha não dispara o recálculo da planilha, portanto após ocultar uma planilha, é necessário pressionar F9 para forçar o recálculo ou fazer qualquer atualização numa célula para a planilha recalcular e atualizar a fórmula.
Baixe um modelo com esta UDF aqui: Download do Modelo
É muito bom ver artigos seus novamente Rafael! Ótima solução!
CurtirCurtir
Que honra te ver passando aqui no meu blog!
Pois é, vida está corrida, mas quero retomar este bom hábito de escrever e quem sabe gravar, pois compartilhar conhecimento é vida!
CurtirCurtir