Cálculo do Excel com base na entrada de qualquer célula

Question

Eu preciso de uma coluna de números que são gerados com base na entrada de QUALQUER uma das células. Digamos que eu precise que cada célula seja 50% da anterior, assim:

1000
500
250
125

Eu percebo que posso multiplicar com base na entrada de uma célula de entrada designada. Nesse cenário, talvez a primeira célula seja a célula de entrada e o restante seja = entrada * 0,5

O problema é que eu gostaria de poder usar qualquer célula como entrada e TODAS as outras células serão alteradas automaticamente de acordo com as mesmas regras, mas com base nesse número. Por exemplo, tomando o exemplo acima, se eu digitar 40 no lugar de 250, a coluna resultante ficaria assim:

160
80
40
20

Isso faz sentido? Eu aprecio qualquer ajuda sobre isso!

    

Answer 1

Sem VBA

O mais próximo que você pode fazer sem o VBA é fornecer a coluna de entrada e resultado.

=INDEX($A$1:$A$10, MATCH(FALSE, ISBLANK($A$1:$A$10), 0))*2^(MATCH(FALSE, ISBLANK($A$1:$A$10), 0)-ROW())

Altere $A$1:$A$10 para o intervalo desejado e pressione Ctrl + Shift + Enter em vez de Enter ao inserir esta fórmula. Você pode fazer o autofill.

Opcional: coloque esta validação de dados no seu intervalo de entrada / coluna. Adapte o alcance à sua necessidade, mas lembre-se dos $ signs . Isso fará com que o intervalo aceite apenas uma entrada.

=COUNTA($A$1:$A$3)<=1

O resultado:

400  | 400*2^(1-1) => 400 | First non-blank value is 400
     | 400*2^(1-2) => 200 | First non-blank row number is 1, current row is 2
     | 400*2^(1-3) => 100 | Current row is 3

     | 300*2^(2-1) => 600 | First non-blank value is 300
300  | 300*2^(2-2) => 300 | First non-blank row number is 2, current row is 2
     | 300*2^(2-3) => 150 | Current row is 3

Explicação

A fórmula recebe o valor da primeira célula da coluna de entrada (A) que não está em branco (consulte aqui ). É então multiplicado com 2 powered by the row number of first non-blank cell minus current row number .

com o VBA

Adaptado de esta questão

Private Sub Worksheet_Change(ByVal Target As Range)

Dim InputRange As Range, cell As Range
Dim BaseValue As Double
Set InputRange = Range("B1:B4")

If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
If Intersect(Target, InputRange) Is Nothing Then Exit Sub

BaseValue = Target.Value*2^(Target.Row - InputRange.Row)

Application.EnableEvents = False
For Each cell In InputRange
 cell.Value = BaseValue
 BaseValue = BaseValue/2
Next cell
Application.EnableEvents = True

End Sub

Adapte seu intervalo em Set InputRange = Range("B1:B4")

Explicação

If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
If Intersect(Target, InputRange) Is Nothing Then Exit Sub

Apenas a mudança de uma célula é avaliada. Apenas a alteração de valor que não é devido à operação de exclusão é avaliada. Somente a modificação feita dentro do InputRange é avaliada.

BaseValue = Target.Value*2^(Target.Row - InputRange.Row)

Estabeleça o BaseValue, que é o valor da célula mais alta do intervalo. Isso é feito multiplicando-se o valor de entrada por 2 alimentado pela diferença entre a linha mais alta e a linha de destino (por exemplo: se você inserir em B8 e o intervalo cobrir B3: B9, então multiplique por 2 power (8-3) )

Application.EnableEvents = False
...
Application.EnableEvents = True

Impede que os eventos sejam acionados por causa da seguinte alteração.

For Each cell In InputRange
 cell.Value = BaseValue
 BaseValue = BaseValue/2
Next cell

Altera o valor de cada célula, começando do mais alto do intervalo definido, para uma proporção do valor BaseValue.

    

Answer 2

Neste exemplo, você pode usar uma célula de entrada ( C6 ) junto com uma célula auxiliar ( B6 ), onde você precisa colocar um índice em uma lista de valores: o valor de entrada aparecerá nessa posição de índice em uma lista.

A fórmula é apenas

=$C$6*$E$2^(D4-$B$6)

Observe que o valor percentual é armazenado em E2 .

Comece com 1000 em 0 position:

Doquemudarpara40naposição2:

Você também pode se livrar da coluna de índice, mas acho que é útil tê-lo.