Função de usuário personalizada sem usar o VBA

Question

É possível criar uma função de usuário personalizada no Excel sem usar o VBA?
Meu problema é que eu tenho uma longa seqüência de funções padrão do excel, juntas para fazer uma função muito longa. Essa função é usada em 25 planilhas diferentes na minha pasta de trabalho. Se eu precisar fazer alterações, eu gostaria de fazer isso em apenas um lugar e ter as alterações propagadas automaticamente em todas as planilhas.

Por exemplo, um caso simples e trivial seria adicionar um ao resultado de SUM (), ie. SUM (mySeries) +1, e chama-lhe uma nova função MYSUM ().

Eu hesito em traduzir a fórmula longa em VBA devido aos possíveis erros que podem surgir e à complexidade adicional.

    

Answer 1

Sim, é possível usar Fórmulas nomeadas do Excel .

Por exemplo, suponha que você precise calcular a diferença entre as somas de duas colunas consecutivas ( A5: Ax - B5: Bx ) em locais diferentes em sua pasta de trabalho ( x é a última linha de cada coluna):

Então você define em A11 um nome chamado diff (qualquer nome pode ser usado) como = Sum (A $ 5: A10) -Sum (B $ 5 : B10) , assumindo que os dados iniciam na linha 5 até a linha anterior. Pode ser qualquer célula, não apenas A11 , mas a definição muda da mesma maneira.

Infelizmente, o Excel 2010 insere prefixos absolutos ( $ ) e prefixos de planilhas, então você precisa apagar os prefixos, mas mantendo os pontos de exclamação e apagando a maioria $ caracteres.

Quando você move as fórmulas, a maioria das referências é relativa. Por isso, calcula sempre a diferença entre a coluna atual e a seguinte, a partir da linha 5 até a linha antes da linha total.

Então, se você tem dados entre C5 e D100 você coloca apenas = Diff em C101 e calcula Soma (C5: C100) - Soma (D5: D100) .

Obviamente, você pode usar nomes locais ou globais nas fórmulas nomeadas, como você mencionou na sua pergunta.

Você pode ler mais detalhes em Fórmulas nomeadas .

    

Answer 2

Eu sei que você disse que não VBA, mas fazendo o seguinte NÃO requer que você realmente reescreva suas fórmulas, saiba muito sobre o VBA, nem mantenha suas fórmulas no VBA. Você pode escrever uma vez e esquecer isso.

Crie uma função definida pelo usuário para extrair a fórmula de uma célula como uma string:

Function GetFormula(Target As Range) As String
    GetFormula = Target.Formula
End Function

Crie outro para avaliar uma string como uma fórmula:

Function Eval(Ref As String)
    Application.Volatile
    Eval = Evaluate(Ref)
End Function

Se você tivesse sua fórmula mestra na planilha1! a1, você colocaria isso em cada célula que precisar usá-la:

=eval(getformula(sheet1!a1))