Расчет Excel на основе ввода из любой ячейки

Question

Мне нужен столбец чисел, которые все генерируются на основе ввода ЛЮБОЙ из клеток. Допустим, мне нужно, чтобы каждая ячейка составляла 50% от предыдущей, вот так:

1000
500
250
125

Я понимаю, что могу умножить на основе ввода одной назначенной входной ячейки. В этом сценарии, возможно, первая ячейка является входной ячейкой, а остальные будут = input * 0.5

Проблема в том, что я хотел бы иметь возможность использовать любую ячейку в качестве входных данных, и ВСЕ другие ячейки будут автоматически меняться в соответствии с теми же правилами, но на основе этого числа. Например, если взять приведенный выше пример, если я наберу 40 вместо 250, результирующий столбец будет выглядеть так:

160
80
40
20

Имеет ли это смысл? Я ценю любую помощь в этом!

Answer 1

В этом примере вы можете использовать одну входную ячейку (C6) вместе с вспомогательной ячейкой (B6), где вам нужно поместить индекс в список значений: входное значение появится в той позиции индекса в списке.

Формула просто

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

Обратите внимание, что процентное значение хранится в E2 .

Начните с 1000 в 0 позиции:

Чем изменить на 40 в позиции 2:

Вы также можете избавиться от столбца индекса, но я думаю, что это удобно.

Answer 2

Без VBA

Самое близкое, что вы можете обойтись без VBA, предоставив столбцы ввода и результата.

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

Измените $A$1:$A$10 на желаемый диапазон и нажмите Ctrl+Shift+Enter вместо Enter при вставке этой формулы. Вы можете заполнить его автоматически.

Необязательно: поместите эту проверку данных в свой диапазон ввода / столбец. Приспособьте диапазон к своей потребности, но помните знаки $. Это позволит диапазону принимать только один вход.

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

Результат:

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

объяснение

Формула берет значение первой ячейки из входного столбца (A), которое не является пустым (см. Здесь). Затем он умножается на 2 powered by the row number of first non-blank cell minus current row number .

С VBA

Адаптировано из этого вопроса

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

Адаптируйте свой диапазон на 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

Оценивается только изменение одной ячейки. Только изменение значения, которое не из-за операции удаления, оценивается. Оценивается только изменение, внесенное в InputRange.

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

Установите BaseValue, то есть значение самой верхней ячейки диапазона. Это делается путем умножения входного значения на 2 с разницей между самой верхней строкой и целевой строкой (пример: если вы вводите в B8 и диапазон охватывает B3: B9, то оно умножается на 2 power (8-3))

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

Предотвращает запуск событий из-за этого следующего изменения.

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

Изменяет значение каждой ячейки, начиная с самой верхней части определенного диапазона, до пропорции BaseValue.