将单元格内容重复划分为‘n’个相等的值并重新分配到连续的单元格中

2024-6-19 • tag-icon

microsoft-excel worksheet-function

将单元格内容重复划分为‘n’个相等的值并重新分配到连续的单元格中

我拥有的：

n = 5
0, 0, 0, 0, 1000, 0, 0, 0, 0

我想要的是：

0, 0, 0, 0, 200, 200, 200, 200, 200, 0, 0...

因此，当您看到顶行中的值（1000）时，底行将均匀地分割该值n次，直到所有分割的总和等于原始值。

如果可能的话，我建议避免使用 VB。

编辑1：

你能保证遇到的任何值都能被 n 整除吗？

不。但是，一旦总和与原始值之间的差值 < 1，则继续。

你能保证有足够的细胞进行 n 次分裂吗？

不。

你能保证只有一个非零值吗？

不会，会有多个值。如果顶行中看到另一个值，而底行尚未完成拆分，则底行会将旧的和新的拆分加在一起。

非零值会一直处于同一位置吗？

不可以，它可以出现在除了第一个之外的任意位置。

编辑2：

为了透明起见，我试图将大笔费用平均分摊到n年。

也许这将是一个更好的例子：预期输入和预期输出的时间表

注意：

必须进行分割输入值的过程，直到各个分割的总和等于输入值。
如果我们尚未完成对前一个输入值的分割时看到另一个输入值，则输出行将继续对相应的分割求和，直到每个输入值都满足第一个点中的条件。

答案1

编辑：虽然之前的公式不必要地复杂（见本次修订) 仍然有用，例如，如果需要对输入应用滑动缩放因子，我已经在这个答案中用新的、更简单、更容易理解的版本替换了它。

这是一个非 VBA 解决方案，它不需要任何辅助行/列或额外的表：

数组——输入以下公式B3，然后将其填充到右侧：

{=SUM(IFERROR(INDEX($2:$2,N(IF(1,COLUMN()-(COLUMN(INDEX(3:3,1):INDEX(3:3,$A$5))-1))))/$A$5,0))}

解释：

该公式的美化版本如下：

{=
SUM(
  IFERROR(
    INDEX(
      ($2:$2),
      N(IF(1,COLUMN()-(COLUMN(INDEX(3:3,1):INDEX(3:3,$A$5))-1)))
    )/$A$5,
    0
  )
)}

如果考虑到 n = 5，第一个公式的第二个参数INDEX()实际上等同于：

COLUMN()-{0,1,2,3,4}

它基本上通过生成一个可变长度（基于 n，存储在A5我的示例中）的偏移量数组来访问前 n-1 个输入值加上当前的输入值。

逐步执行公式I3应该会使上述内容更加清晰：

(COLUMN(INDEX(3:3,1):INDEX(3:3,$A$5))-1)→ {1,2,3,4,5}-1→{0,1,2,3,4}
COLUMN()-{0,1,2,3,4}→ {9}-{0,1,2,3,4}→{9,8,7,6,5}
N(IF(1,{9,8,7,6,5}))→ N({9,8,7,6,5})→{9,8,7,6,5}
(INDEX($2:$2,{9,8,7,6,5})/$A$5→ {100000,0,0,0,7}/5→{20000,0,0,0,1.4}
SUM(IFERROR({20000,0,0,0,1.4},0))→20001.40

INDEX($2:$2,N(IF(1,expression)))是强制 Excel 返回数组的必需 hack ^*expression ，因为默认情况下，第二个参数的INDEX()计算结果为单个值。仅使用in会导致INDEX($2:$2,expression)I3

INDEX($2:$2,COLUMN()-(COLUMN(INDEX(3:3,1):INDEX(3:3,$A$5))-1))→ INDEX($2:$2,9-(COLUMN($A$3:$E$3)-1))→ → →INDEX($2:$2,9-(1-1))INDEX($2:$2,9)100000

因为在返回单个值的表达式中，返回范围第一个单元格的列。COLUMN(multi-cell-range)

IFERROR()如果公式存在于靠近工作表左侧的单元格中，则需要此函数，从而访问行标题，或尝试访问列左侧的单元格A。

笔记：

美化的公式确实可以起作用。
美化版本中的括号($2:$2)用于强制$2:$2将其保持在自己的行上。

_{^*我必须先自己弄清楚，才能确切解释这种黑客攻击为何有效 ;-)}

答案2

代码：

Sub Divide(n As Integer)
Dim temp(), i As Integer, j As Integer, cnt As Integer, tmp

' Copy values from row to array
temp = Rows(Selection.Row).Value

' Search for the last cell. Use SpecialCells instead is the better variant.
For i = UBound(temp, 2) To LBound(temp, 2) Step -1
    If Not IsEmpty(temp(1, i)) Then
        cnt = i
        Exit For
    End If
Next i

' Perform main operation
For i = cnt To 1 Step -1
    If Rows(Selection.Row).Cells(1, i).Value <> 0 Then
        tmp = Rows(Selection.Row).Cells(1, i).Value
        Rows(Selection.Row).Cells(1, i).Delete Shift:=xlShiftToLeft
        For j = 1 To n
            Rows(Selection.Row).Cells(1, i).Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
            Rows(Selection.Row).Cells(1, i).Value = tmp / n
        Next
    End If
Next

End Sub

Sub 接受n值作为参数。
Sub 将选定行（左上角）处理进去。
Sub 不检查单元格中的值是否可以转换为数字以进行比较0和除以n。
其他错误也未被检查。
当移动超过列限制时，移动的值将会丢失。
并且根本没有任何优化。

使用此代码作为一个想法。

答案3

这是一个无需使用 VBA 的可行解决方案。

唯一的区别是您需要在某处添加一个表格来单独输入每个费用。

我强烈建议您这样做，因为它可以大大简化计算，并且从财务角度来看也更加清晰：您可以轻松添加诸如成本参考、描述、服务器类型等列。如果您有多个项目，甚至可以添加数量和基本价格列。您可以扩展它并生成有趣的报告，在我看来，这比编写 VBA 要容易得多。

黄色单元格代表输入。

在 C2 中，命名单元格，以便于在其他地方_N轻松访问变量5 years
对于您的成本（范围A5:D10），插入一个您可以命名的表格Costs以便于参考

以下是要使用的公式，如有必要，请将其拖动/复制到右侧（屏幕截图中为蓝色）：

=IFERROR([@[Real Cost]]/_N;0)

=IF([@From]>0,[@From]+_N-1,"")

=SUMPRODUCT((B$13=Costs[From])*(Costs[Real Cost]))

=SUMPRODUCT((B$13>=Costs[From])*(B$13<=Costs[Until])*(Costs[Yearly Cost]))

相关内容