Transformar a planilha do Excel em uma fórmula [fechada]

Question

Eu tenho uma planilha do Excel que tem uma computação complexa que não é trivial para se transformar em uma macro ou uma fórmula de célula única. A planilha tem cerca de 10 entradas diferentes (valores que um humano entra em diferentes células da planilha) e, em seguida, gera 5 cálculos independentes (em 5 células diferentes) com base nessa entrada. O cálculo deles é usar alguns dados pré-inseridos na planilha (cerca de 100 constantes diferentes) e fazer algumas buscas neles.

Eu gostaria de usar essa planilha inteira como uma fórmula em uma planilha diferente para calcular um conjunto de valores de entrada e produzir o conjunto correspondente de valores de saída. Imagine isso criando uma tabela diferente com 10 colunas para as variáveis de entrada e 5 colunas para as saídas, copiando cada entrada na outra planilha e copiando de volta a saída na tabela de resultados.

Por exemplo:

• A1, A2, A3, ... A10 são células onde alguém insere valores
• através de uma série de cálculos B1, B2, B3, B4 e B5 são atualizados com algumas fórmulas

Posso usar toda a série de cálculos de A1 ... A10 em B1 ... B5 sem criar uma enorme fórmula enorme ou uma macro VBA?

Eu quero ter um conjunto de valores de entrada em 100 linhas de A100, B100, C100, ... J100 em diante e fazer alguma mágica do Excel que:

1. Copie os valores de A100 ... J100 em A1 para A10
2. Aguarde até que o resultado apareça em B1 a B5
3. Copie os valores de B1 para B5 no K100 para O100
4. Repita as etapas de 1 a 3 para todas as linhas de 100 a 150

Answer 1

Você pode fazer isso com a análise de variações hipotéticas do Excel. Eu não estou familiarizado o suficiente para dizer isso. Você pode fazer isso com uma macro relativamente simples.

Na base, a macro funcionaria assim:

Open the formula workbook
Set calculation to manual
Loop thru the rows you want to apply the formula to
   Take inputs on current row of result sheet and enter them in the formula workbook
   Force Excel to recalculate
   Take results of formula workbook and enter them in the result sheet
End loop
Turn calculation back on to automatic.

Há duas maneiras de transferir os dados nas linhas da pasta de trabalho de resultados para a pasta de trabalho de fórmulas nas colunas. Uma maneira é copiar & cole usando Transpose para converter a linha em uma coluna, a outra é usar deslocamentos em que para um intervalo você está compensando a coluna e o outro intervalo que você está compensando a linha.

Um exemplo deste último é o seguinte.

    ' set ranges to top left cell
    Set rngFrom = Workbooks("Result.xls").Worksheets("Sheet1").Cells(iCurrentRow, 1)
    Set rngTo = Workbooks("Formula.xls").Worksheets("Sheet1").Range("A1")

    ' transfer values
    For i = 0 To 9
        rngTo.Offset(RowOffset:=i).Value = rngFrom.Offset(ColumnOffset:=i).Value
    Next i

Se precisar de ajuda específica para escrever uma macro VBA, avise-nos.

    

Answer 2

Eu ainda acho que sua pergunta é muito vaga. É difícil responder sem um exemplo claro. Em seu lugar, eu tentaria encontrar um especialista em excel que fizesse o trabalho para você. Alguém com muita experiência pode transformar tudo em uma macro.

Vou tentar dar uma resposta, no entanto, com base no que acho que entendi.

Na maioria dos termos gerais, acho que você só precisa ter as fórmulas na pasta de trabalho 2 acessando os valores na pasta de trabalho 1 e vice-versa. Vincular as pastas de trabalho parece ser a solução mais óbvia. Há algumas lições no site da Microsoft que mostram como fazer isso ( link , link ).

Se você acha que minha resposta está muito errada, poste um exemplo ou uma captura de tela ou um vídeo que mostre o que você está tentando realizar.

    

Answer 3

Concordo, a pergunta é um pouco vaga e tem 4 etapas no processo. Qual é o passo mais difícil? Você pode fazer um exemplo que é menor e focar apenas na parte que você não pode gerenciar?

1. Copie os valores de A100 ... J100 em A1 para A10

Os intervalos não são do mesmo tamanho, o que você quer dizer com cópia? Se você quiser um determinado valor em A1, basta colocar = A100 na célula A1. Você não precisa "copiar" de fato, apenas "referir".

2.Aguarde que o resultado apareça em B1 a B5

Você não precisa realmente esperar por isso, mas tudo bem, eu acho que está fazendo algumas coisas de fundo complexas. Eu acho que você tem essa parte planejada.

3. Copie os valores de B1 para B5 em K100 para O100 Novamente, se você quer um valor em K100, então você coloca = B1 na célula K100 e ele irá "copiá-lo" (refira-se a ele)

4.Repita os passos 1 a 3 para todas as linhas de 100 a 150 Quando você tiver configurado a linha 100, você terá um forumla em cada célula. Agora, quando você seleciona a célula, haverá um pequeno "identificador" preto no canto inferior direito. Clique e arraste para baixo. As fórmulas serão automaticamente alteradas para se referirem às células de maneira absoluta ou relativa. Por exemplo, se você tiver alguns dados na célula A1, e quiser que todas as células A100 a A150 "a vejam", você poderá colocar na célula A100: = $ A $ 1 então a célula A101 também irá olhar para A1 e assim por diante. Então, o sinal $ faz disso uma referência absoluta.

    

Answer 4

Eu sei que isso é muito tarde e espero que você tenha tido sucesso em seus esforços.

Eu criei algumas dessas planilhas. Alguns envolviam até 14 tabelas de consulta e algumas dezenas de cálculos e interpolações intermediárias; tudo para produzir uma única tabela de números. Isso foi baseado em diversas entradas do usuário, desde números reais até listas suspensas com muita validação de dados. Eu usei várias pesquisas, index () / match () consultas. E não usei nenhuma linguagem de script, pois não me importo particularmente com os avisos contínuos associados às planilhas ativadas para macro. Eu criei um número de folhas para os cálculos e pesquisas intermediários, todos protegidos com senhas e a maioria estava oculta. Havia uma folha de entrada de dados com células desprotegidas e uma folha de resultados.

Originalmente, minha esperança era entregá-lo a alguém e isso levaria o usuário até as etapas necessárias. Acontece que isso era muito complexo e requeria conhecimento específico sobre determinados tópicos.

No final, acredito ter investido mais de 120 horas e usei isso como uma ferramenta só para mim. Para fazer isso funcionar, adicionei mais uma folha com um "modelo" em branco formatado para copy & cole em um documento do MS Word. Isso vem funcionando bem há alguns anos. Quando alguns dados nas tabelas de consulta precisavam de revisão, eu simplesmente fazia essas alterações e os números refletiam essas alterações imediatamente.

Melhor, Doug