基于任意单元格输入的 Excel 计算

Question

我需要一列数字，这些数字都是根据任意单元格的输入生成的。假设我需要每个单元格都是前一个单元格的 50%，如下所示：

1000
500
250
125

我意识到我可以根据一个指定输入单元的输入进行乘法运算。在这种情况下，也许第一个单元是输入单元，其余单元将=输入*0.5

问题是，我希望能够使用任何单元格作为输入，并且所有其他单元格将根据相同的规则自动更改，但基于该数字。例如，以上面的例子为例，如果我输入 40 代替 250，则结果列将如下所示：

160
80
40
20

这有意义吗？我很感激任何帮助！

Answer 1

没有 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 的一定比例。

Answer 2

在此示例中，您可以将一个输入单元格 ( C6) 与一个辅助单元格 ( B6) 一起使用，其中您需要在值列表中放置一个索引：输入值将出现在列表中的该索引位置。

公式如下

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

请注意，百分比值存储在中E2。

1000从以下位置开始0：

然后更改为40at 位置2：

您也可以删除索引列，但我认为有它很方便。