Entrada de variável de fórmula mestre do Excel

Question

você poderia me dizer se o que estou tentando fazer aqui é possível? Tenho a sensação de que o INDIRECT poderia ser útil, mas minha mente não foi capaz de descobrir como isso ficaria. Meu objetivo é semelhante em conceito a uma análise de variações hipotéticas, mas, em vez de uma tabela, seria usada uma fórmula de célula única que referencia uma fórmula mestre e algumas entradas. What-If não funciona aqui, porque as entradas não estão bem organizadas na minha folha de análise.

Aqui está a história:

Estou criando uma planilha ("AnalysisSheet") que uso para prever e analisar resultados durante a produção de cerveja.

Tomo muitas medições ao longo do processo para controle de qualidade, e muitas vezes a mesma fórmula será aplicada aos vários pontos de dados brutos em várias etapas para análise (por exemplo, conversão de volume de líquido quente em volume de líquido frio com aproximação de expansão térmica simples ).

Essas fórmulas podem ser complexas e, portanto, difíceis de serem repetidas a cada vez, portanto, gostaria de criar uma folha separada ("FormulaSheet") que tenha as fórmulas "principais" organizadas (juntamente com entradas simuladas), para que eu pode simplesmente referenciar as fórmulas mestras da "AnalysisSheet" juntamente com os valores de entrada a serem substituídos nas entradas dummy.

Veja um exemplo de uma fórmula simples:

Em "FormulaSheet" :

A1: 20 [Valor de entrada fictícia de volume]

A2: 80 [valor de entrada fictício de temperatura]

A3: = A2 * (1-0,04 * (A3-20) / 80) [fórmula "Master"]

Agora em "AnalysisSheet" :

F7: 90 [leitura da temperatura]

F11: 40 [leitura de volume]

F13: = Cálculo como FormulaSheet! A3, mas substituindo sua referência de entrada FormulaSheet! A1 com AnalysisSheet! F11 e FormulaSheet! A2 com AnalysisSheet! F7

A minha pergunta é basicamente:

O que precisaria ser inserido no F13 para executar o que eu descrevo?

    

Answer 1

Não tenho certeza se você pode fazer o que está descrevendo, mas eu vi uma página muito boa sobre como definir suas próprias funções: link

Ele exige que você escreva a função no VBA, mas não é tão difícil quanto parece.

1. Abra as Ferramentas do desenvolvedor. Normalmente você precisa ir em Preferências do Excel > Ver > Mostrar guia do desenvolvedor.
2. Vá até a recém-exibida guia Desenvolvedor e clique em Visual Basic .
3. Na janela do Editor do VB aberta, selecione Insert > Module .
4. Insira sua (s) função (ões)

Por exemplo:

Public Function HotToColdVolume(temperature As Integer, volume As Integer)
    HotToColdVolume = volume * (1 - 0.04 * (temperature - 20) / 80)
End Function

Observação Você precisará salvar sua planilha como uma pasta de trabalho habilitada para macro (.xlsm).

Agora, na sua planilha, você pode simplesmente ligar:

=HotToColdVolume(80,20)

    

Answer 2

Ok, dessa forma, não usa o VBA, mas é bem pior. Aparentemente o Excel tinha essas funções "Excel 4.0" que você poderia usar em nomes definidos. Eles fazem o que você quer, mas eles ainda precisam ser salvos em um arquivo .xlsm :

Nível de hack 1

Existe uma função (?) chamada EVALUATE (não consigo encontrar nenhuma documentação oficial sobre isso, mas esta página explica isso) que pode avaliar o texto como uma função. Mas você não pode digitá-lo diretamente em uma célula, ele precisa "executar" de um intervalo nomeado:

ClaroqueoproblemaéquesevocêtentaravaliarafórmulaA2*(1-0.04*(A3-20)/80)vocênãoconsegueespecificarsuasprópriasentradas.

Nível2dehackers

Nasuafórmulamestra,vocêpodeespecificaruma OFFSET , mas você precisa de uma célula de referência. Você não pode simplesmente referenciar A1 pelos mesmos motivos de antes. Você precisa de uma maneira de fazer referência a "esta" célula. Digite outro hack genial que eu encontrei aqui . Selecione a célula A1 , crie um intervalo nomeado (por exemplo, THIS_CELL ), insira a fórmula =!A1 (! Significa apenas " esta planilha ").

Agora, se você quiser se referir à célula imediatamente à direita (1 col across), use:

OFFSET(THIS_CELL,0,1)

Então, na sua página de fórmulas mestras, você coloca fórmulas na coluna A e argumentos nas colunas B, C, D, etc. E você obtém uma fórmula maciçamente feia assim:

=OFFSET(THIS_CELL,0,1)*(1-0.04*(OFFSET(THIS_CELL,0,2)-20)/80)

Combineoshacks

Agora,bastacriarumintervalonomeadoqueEVALUATEso FORMULATEXT da fórmula sofisticada com off-sets embutidos:

=EVALUATE(FORMULATEXT(MasterFormulaTest!$A$2))

Insiraointervalonomeadoemumacélulaepronto!

Tudo sem VBA, as mesmas desvantagens de se você estivesse usando o VBA, mas muito mais complicado do que simplesmente usar o VBA. Completamente pior.