Posso usar uma planilha como uma função no Excel?

Question

Eu tenho uma planilha configurada de tal forma que eu posso inserir um valor, fazer algumas contas para me dar um monte de valores, repetir matemática algumas vezes e somar todos os valores e retornar um valor de saída. É tão complexo que seria impraticável copiar e colar todas as células toda vez que eu testar uma nova entrada. Foi bom quando tive que testar 2 ou 3 parâmetros diferentes, mas agora que preciso testar 50 entradas diferentes e criar um gráfico, preciso de ajuda.

Existe alguma maneira de criar uma tabela com os 50 valores de entrada, de alguma forma inseri-los na planilha e obter os valores de saída para que eu possa fazer um gráfico? Opções usando vb-script podem funcionar.

EDIT: Eu incluí a maioria dos cálculos, onde o valor em A2 é o valor de entrada, e o valor em D10 é o valor de saída. Veja abaixo da imagem

    

Answer 1

A maneira como fiz isso no passado é usar o vba para inserir todos os valores em células adequadas, deixar o excel fazer os cálculos e copiar os resultados usando algo como a macro abaixo. Eu coloquei uma imagem da folha de teste abaixo. Obviamente, o seu seria mais profundo e você provavelmente colocaria a tabela em uma folha diferente dos seus cálculos. Fórmula em G5 foi soma (G2: G4). Nenhuma fórmula na coluna D é onde os resultados são armazenados.

Sub get_data()
Dim dataA As Double, dataB As Double, dataC As Double, dataOutput As Double
Dim lastRow As Long, currentRow As Long

With ActiveSheet
    lastRow = .Range("A" & .Rows.Count).End(xlUp).Row

    For currentRow = 2 To lastRow
        'Read in data from table
        dataA = .Range("A" & currentRow).Value
        dataB = .Range("B" & currentRow).Value
        dataC = .Range("C" & currentRow).Value

        'Put data into worksheet
        .Range("G2").Value = dataA
        .Range("G3").Value = dataB
        .Range("G4").Value = dataC

        'You could also avoid the variables by putting it directly into the cell without variable
        '.Range("G2").Value = .Range("A" & currentRow).Value
        'etc

        Calculate

        'store results in variable
        dataOutput = .Range("G5").Value

        'Put results back in table
        .Range("D" & currentRow).Value = dataOutput
    Next
End With

End Sub

    

Answer 2

Você pode usar tabelas de dados no Excel para isso:

1. Você lista suas entradas em uma linha ou coluna (para que elas representem um eixo da tabela).
2. Depois, vá para "Análise de hipóteses" > "Tabela de dados".
3. O assistente solicita os campos de entrada e saída. Em seguida, cola o valor de saída de cada entrada ao lado de cada entrada.