☰

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

tag-icon • tag-icon microsoft-excel worksheet-function
将单元格内容重复划分为‘n’个相等的值并重新分配到每个‘m’个单元格中

这是一个后续问题将单元格内容重复划分为‘n’个相等的值并重新分配到连续的单元格中，但在这种情况下，我希望通过跳过由输入值确定的单元格将答案重新分布在更大的范围内。

最初的问题是解决了经过罗宾。

回顾

在原始问题中卡梅伦12最初有：

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

他们最终希望得到：

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

就我而言，我试图弄清楚如何做同样的事情，但根据输入的数字重新分配这些值。例如，对于米= 2，即每 2 个月或 2 个单元格，输出为：

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

为了米= 3（每 3 个月或 3 个单元格）它看起来像这样：

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

如果需要进一步说明，请告诉我。

注意：我一直尝试使用它来解决它MOD()，但它让我很为难。

更新：以下屏幕截图有助于澄清多个输入值的情况：

电子表格的屏幕截图

对于最上面的数据行，7 月 1 日的 41.8 万美元应该被拆分并分摊到每个米=3 个月，这应该与 9 月 1 日拆分的 250 万美元的月份不同。输出序列应如下所示：

83000, 0, 500000, 83000, 0, 500000, 83000, 0, 500000…

（注：为了更容易识别，我对数值进行了四舍五入。）

对于第二个日期行，7 月 1 日的 25 万美元和 10 月 1 日的 35 万美元应输出：

50000, 0, 0, 120000, 0, 0, 120000, 0, 0, 120000, 0, 0, 120000, 0, 0, 70000…

其中120000是七月和十月分割的总和（50000+70000）。

答案1

编辑：我认为我之前的公式（见本次修订) 是最简单的，因为它是在给定多个输入的情况下解决最简单的输出场景。

事实证明，多个输入实际上需要的是它们彼此独立地分布。令人惊讶的是，这个问题的解决方案实际上更简单，和没有任何警告！因此，我用新公式更新了这个答案。

您使用的方法是正确的MOD()。

这是一个使用的解决方案同一张桌子从上一个问题，值为 5n值为 3米（存储在A6）：

工作表截图

数组中输入（Ctrl++ Shift）Enter以下公式B3，然后复制粘贴/填充到表格的其余部分（不要忘记删除and {）}：

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

注意事项：没有任何。

解释：

该公式的美化版本如下：

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

公式有两处变化上一个答案。

首先，第一个INDEX()中的第二个参数IFERROR()已被修改为

N(IF(1,COLUMN()-(COLUMN(INDEX(3:3,1):INDEX(3:3,$A$5))-1)))

到

N(IF(1,COLUMN()-(COLUMN(INDEX(3:3,1):INDEX(3:3,$A$5*$A$6))-1)))

这个公式更容易理解，如果你考虑一下，如下所示n= 5 和米= 3，这实际上相当于：

COLUMN()-{0,1,2,3,4,5,6,7,8,9,10,11,12,13,14}`

由于我们有效地将分割值分散到更多输出单元，因此我们需要生成一个偏移量数组来访问前一个n×米-1，加上当前的输入值，而不是前一个数组的n值。

请注意，我们仍然将输入值除以n，因为他们仍然只分开那么多次。

SUM()第二个变化是，原来位于,内的数组（现已修改）«15-most-recent-input-splits»与另一个数组相乘：

SUM(
  (0=MOD(COLUMN(INDEX(3:3,1):INDEX(3:3,$A$5*$A$6))-1,$A$6))
  *
  «15-most-recent-input-splits»
)

与上面类似，对于n= 5 和米= 3，该数组实际上等同于：

  (0=MOD({0,1,2,3,4,5,6,7,8,9,10,11,12,13,14},$A$6))

结果MOD()生成一个新数组，其中当前输入单元格为零，每个单元格的倍数为米左侧单元格，其他所有单元格均为非零值（最近的n×米输入单元格）。

将此数组与零进行比较，结果将得出最终数组，其中TRUE值为零，FALSE否则为。

由于在数组相乘时TRUE会转换为 1 和0，因此该数组本质上是一个“筛子”，只允许来自输入单元格的分割输入值通过，这些输入单元格是FALSESUM()米远离当前单元格。

希望通过逐步完成公式，I3可以使上述所有内容更加清晰：

  • COLUMN(INDEX(3:3,1):INDEX(3:3,$A$5*$A$6))-1
    → COLUMN(INDEX(3:3,1):INDEX(3:3,15))-1
    → COLUMN($A$3:$O$3)-1
    → {1,2,3,4,5,6,7,8,9,10,11,12,13,14,15}-1
    → {0,1,2,3,4,5,6,7,8,9,10,11,12,13,14}
    =«offsets-to-15-most-recent-inputs»
  • COLUMN()-«offsets-to-15-most-recent-inputs»
    → COLUMN()-{0,1,2,3,4,5,6,7,8,9,10,11,12,13,14}
    → {9}-{0,1,2,3,4,5,6,7,8,9,10,11,12,13,14}
    →{9,8,7,6,5,4,3,2,1,0,-1,-2,-3,-4,-5}
  • N(IF(1,{9,8,7,6,5,4,3,2,1,0,-1,-2,-3,-4,-5}))
    → N({9,8,7,6,5,4,3,2,1,0,-1,-2,-3,-4,-5})
    → {9,8,7,6,5,4,3,2,1,0,-1,-2,-3,-4,-5}
    =«column-#s-of-15-most-recent-inputs»
  • INDEX($2:$2,«column-#s-of-15-most-recent-inputs»)/$A$5
    → INDEX($2:$2,{9,8,7,6,5,4,3,2,1,0,-1,-2,-3,-4,-5})/5
    → {100000,0,0,0,7,0,10000,0,"INPUT","INPUT",#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!}/5
    →{20000,0,0,0,1.4,0,2000,0,#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!}
  • IFERROR({20000,0,0,0,1.4,0,2000,0,#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!},0)
    → {20000,0,0,0,1.4,0,2000,0,0,0,0,0,0,0,0}
    =«15-most-recent-input-splits»
  • (0=MOD(COLUMN(INDEX(3:3,1):INDEX(3:3,$A$5*$A$6))-1,$A$6))
    → → → → =(0=MOD(«offsets-to-15-most-recent-inputs»,3))
    (0=MOD({0,1,2,3,4,5,6,7,8,9,10,11,12,13,14},3))
    (0={0,1,2,0,1,2,0,1,2,0,1,2,0,1,2})
    {TRUE,FALSE,FALSE,TRUE,FALSE,FALSE,TRUE,FALSE,FALSE,TRUE,FALSE,FALSE,TRUE,FALSE,FALSE}
    «sieve»
  • SUM(«sieve» * «15-most-recent-input-splits»)
    → SUM({TRUE,FALSE,FALSE,TRUE,FALSE,FALSE,TRUE,FALSE,FALSE,TRUE,FALSE,FALSE,TRUE,FALSE,FALSE}*{20000,0,0,0,1.4,0,2000,0,0,0,0,0,0,0,0})
    → SUM({1,0,0,1,0,0,1,0,0,1,0,0,1,0,0}*{20000,0,0,0,1.4,0,2000,0,0,0,0,0,0,0,0}) → SUM({20000,0,0,0,0,0,2000,0,0,0,0,0,0,0,0}) →22000

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

  • INDEX($2:$2,COLUMN()-(COLUMN(INDEX(3:3,1):INDEX(3:3,$A$5*$A$6))-1))
    → INDEX($2:$2,{9}-(COLUMN($A$3:$O$3)-1))
    → → →INDEX($2:$2,{9}-(1-1))
    INDEX($2:$2,9)100000

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

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

笔记：

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

*关于黑客攻击为何有效的确切解释将不得不等到我（最终？）自己首先弄清楚 ;-)

相关内容