☰

根据每个列表每次允许的最大金额，将每月随机数字列表分配到 3 个新的轮换列表中

tag-icon • tag-icon microsoft-excel worksheet-function microsoft-excel-2016
根据每个列表每次允许的最大金额，将每月随机数字列表分配到 3 个新的轮换列表中

我有一份每月发生次数的列表（例如，第 1 个月 = 4，第 2 个月 = 3.7，第 3 个月 = 4.1，第 4 个月 = 4.0，等等）。我想按月将这些数字按其值和顺序分配到 3 列。第 1 列将是列表中的前 4 个可用数字，第 2 列将是接下来的 4 个，第 3 列将是接下来的 3 个，然后第 1 列将是接下来的 4 个，并且序列将循环。有人能帮我使用满足这一要求的 Excel 函数来处理这三列吗？

工作表如下所示：

答案1

编辑 -

  • 将原始公式简化为最简形式 （或者是？）。
  • 添加命名范围和零预间隔 （更短）。

很酷的问题！以下是解决方案：

=IF( MOD( SUM($A$2:$A2,-$A2) - SUM($B$1:B$1,-B$1), SUM($B$1:$D$1)) < B$1, IF( MOD( SUM($B$1:B$1) - SUM($A$2:$A2,-$A2), SUM($B$1:$D$1)) < $A2, MOD( SUM($B$1:B$1) - SUM($A$2:$A2,-$A2), SUM($B$1:$D$1)), $A2)) + IF( MOD( SUM($A$2:$A2,-$A2) - SUM($B$1:B$1,-B$1), SUM($B$1:$D$1)) <> 0, IF( MOD( SUM($B$1:B$1,-B$1) - SUM($A$2:$A2,-$A2), SUM($B$1:$D$1)) < $A2, IF( MOD( SUM($A$2:$A2) - SUM($B$1:B$1,-B$1), SUM($B$1:$D$1)) < B$1, MOD( SUM($A$2:$A2) - SUM($B$1:B$1,-B$1), SUM($B$1:$D$1)), B$1))) +INT($A2/SUM($B$1:$D$1))*B$1

选择后复制并粘贴到公式栏中B2这是一个多行格式的公式，既便于阅读又便于维护。通过从栏底部边缘向下拖动来调整公式栏的大小。直接复制到工作表上的单元格中很可能会将公式放入多行中，这是不好的。

按回车键，然后选择公式单元格。复制并拖动公式，直到它位于最后一个列表列标题下。当所有单元格仍处于选中状态时，一次复制并拖动整行（这只是一个快捷方式），无论要计算多少输入。

几点。我只知道它需要是模数，由间隔组成，并且圆圈是可视化问题的最佳方式。列本身已经是模数（OP 的重点）。在对每行数据的开始和结束范围值应用 MOD 之后（即MOD(SUM(<blue column to current row>))），什么也没激发出来。
快速访问谷歌“模块化数学旋转”和Fabian “ryg” Giesen 的论文模运算中的区间出现了。非常感谢“ryg”。

论文解释了很多内容，虽然没有涉及计算范围重叠量，但它包含了两条非常有启发性的信息。

  • 此 Java 代码片段用于测试区域是否重叠：
    modN(c - a) <= modN(b - a) || modN(a - c) <= modN(d - c)
    OR ( ) 的两边||直接编码到公式中。一边位于第一个主元中IF，在公式的中间，另一边位于另一个主元中IF。
  • 关于模数运算，这一点同样重要：
    大多数编程语言都使用截断除法，这意味着模数的绝对值小于 N 但可能是负数；在将这里的任何方程式转换为代码时都需要考虑到这一点！

快速 Excel 测试确认=MOD(-1,10)结果为 9，正如预期的那样。这简化了一切，确保距离计算正确，无需大量复杂但必要的计算，IF因为结果是 -1。

这之所以如此重要，是因为在 mod10 中，从 4 到 5 的距离为 1（计算结果为结束 - 开始 = 距离）。那么从 5 到 4 的距离是多少？由于 5 大于 4，并且这是 mod10 算术，因此首先移动到末尾，然后从末尾/开始处开始，移动到 4。或者 (10-5=5) + 4 = 9。

在 Excel 中使用 Mod10(4-5) 是 MOD(4-5,10)，即 MOD(-1,10) = 9。完美！只需使用 MOD(End - Start, 模数)，无需其他IFs 或部分总和（或噩梦般的重复计算）。

关于代码

  • 您可以通过修改所有出现的
    • $B$1:$D$1将 更改D为列表的最后一列字母。
    • 考虑使用记事本来查找和替换。
      从公式栏复制，然后粘贴回公式栏。
  • 有完美的绝对、相对和混合范围引用数量 - 不要更改这些。
  • 要移动公式，请选择范围 A1:D2，然后剪切并粘贴到新位置。这应保留所有绝对和相对范围引用与其输入范围的关系。
  • 此公式利用了最大列量，更改其中一个将会改变表中的所有计算。
    • 要保留已分配的值，请选择要保留的单元格，复制，然后选择性粘贴 - 仅限值返回到相同的单元格，有效地用当前计算的值替换那些单元格公式。
    • 您可能需要一个虚拟行、蓝色列条目来调整未来的分配，因为所有计算（尽管彼此独立）都是累积地基于蓝色列和最大列表分配的当前值。
  • 从旧表结束的地方开始创建新表：
    • 对于第一行使用虚拟贡献日期并使用最后分配给列的条目计算金额。
    • 自满足上一列以来，可能有多个部分条目跨越多行。对分配给最后一列的所有条目求和，但只计算自上一列获得分配以来对最后一列进行的条目。
    • 如果最后一行的多个列有值，则只对最后分配的列求和。如果最后一列有部分值，第一列也有部分值，则最后一列满足条件，第一列是最后分配的列。
    • 将前一列的最大值添加到总和中，无论它是在何时、在哪一行输入的，也无论其他列是否有多个条目。仅计算前一列的最大值。第一列没有前一列。
    • 这个总和（最后一列的贡献和它之前的最大值）是与虚拟日期一起放置的第一个蓝色列号。
  • 代码中有一个保护措施，防止在边界条件发生时将范围重叠量相加两次。这似乎是唯一需要的，但 beta 版的使用可能会决定其他情况。
    • + IF( MOD( SUM($A$2:$A2,-$A2) - SUM($B$1:B$1,-B$1), SUM($B$1:$D$1)) <> 0,
  • 要使零消失，请使用此格式化单元格技巧：选择Custom并输入此[=0]"";General

最后说明一下：模数公式有一个编码校正，允许添加大于模数范围的值。（它会自行包装。）这意味着如果输入的金额大于 11，列表仍将正确更新：

  • +INT($A10/SUM($B$1:$D$1))*D$1

编辑-发布原始解决方案后，我意识到了两件事：

  1. 可以使用基于零的起始间隔来简化该公式。
  2. 命名范围可以是相对的，因此它们可以“增长”。

将两者结合起来得到一个更清晰、更易于管理的公式：

=IF( MOD( SUM(I2c) - SUM(I1a), SUM(Modulus)) < I1b_a, IF( MOD( SUM(I1b) - SUM(I2c), SUM(Modulus)) < I2d_c, MOD( SUM(I1b) - SUM(I2c), SUM(Modulus)), I2d_c)) +IF( MOD( SUM(I2c) - SUM(I1a), SUM(Modulus)) <> 0, IF( MOD( SUM(I1a) - SUM(I2c), SUM(Modulus)) < I2d_c, IF( MOD( SUM(I2d) - SUM(I1a), SUM(Modulus)) < I1b_a, MOD( SUM(I2d) - SUM(I1a), SUM(Modulus)), I1b_a))) +INT(I2d_c/SUM(Modulus))*I1b_a

注意事项：

  • 这是公式的“零版本”，并且需要在
    两个间隔、列表间隔和输入值间隔（在蓝色列中）的开始处有一个零单元格。
    • 位于蓝色输入范围正上方且列表最大值左侧的单元格
      （第二幅图中的红色单元格 - “A1”）必须为空或 0（零）。
    • 这是两个间隔的第一个位置的零皮重，因此即使该位置是第一个间隔并且没有先前的间隔，也可以通过所有先前间隔的总和来获得开始。现在有一个大小为零的先前间隔。（起始位置不再需要按“开始 = 结束 - 距离”计算。）

  • 此版本的公式使用命名范围。

    • 要移动此公式，必须编辑“命名范围”。
    • 命名范围是“相对于”；这意味着它们相对于编辑命名范围的相对列或行之前选择的单元格。
    • 使用第二张图片姓名经理对话作为参考。图像中已选择单元格“B2”，因此名称管理器图像中的所有相对值均相对于单元格“B2”。

    • 创建名称管理器添加或编辑时：

      1. 选择第一个数据单元格（左上角的公式单元格；图像中的“B2”）。
      2. 打开名称管理器。
      3. 创建与列表的最大值相同的列关系以及
        与蓝色输入值相同的行关系。
      4. 要确定实际的非美元符号引用，请使用图像确定从“B2”到图像命名范围值向上和向左的单元格数。
      5. 将向上和向左计数应用于当前选定的单元格，以确定为命名范围新项目或编辑进行的实际输入。
    • 另一个积极的方面是，警告“公式......范围......附加......”消失了。

为了完整起见，这里是没有命名范围的归零版本，可以通过剪切 A1:D2 来移动它。还有带有命名范围的非归零版本，以防“A1”不能为空。（快速提示，将零单元格与其左侧的单元格合并，并使用合并后的单元格作为文本。这样，零单元格的总和将为 0。）

没有命名范围的归零版本：

=IF( MOD( SUM($A$1:$A1) - SUM($A$1:A$1), SUM($B$1:$D$1)) < B$1, IF( MOD( SUM($B$1:B$1) - SUM($A$1:$A1), SUM($B$1:$D$1)) < $A2, MOD( SUM($B$1:B$1) - SUM($A$1:$A1), SUM($B$1:$D$1)), $A2)) + IF( MOD( SUM($A$1:$A1) - SUM($A$1:A$1), SUM($B$1:$D$1)) <> 0, IF( MOD( SUM($A$1:A$1) - SUM($A$1:$A1), SUM($B$1:$D$1)) < $A2, IF( MOD( SUM($A$2:$A2) - SUM($A$1:A$1), SUM($B$1:$D$1)) < B$1, MOD( SUM($A$2:$A2) - SUM($A$1:A$1), SUM($B$1:$D$1)), B$1))) +INT($A2/SUM($B$1:$D$1))*B$1

没有零单元格的命名范围版本：

=IF( MOD( SUM(I2d,-I2d_c) - SUM(I1b,-I1b_a), SUM(Modulus)) < I1b_a, IF( MOD( SUM(I1b) - SUM(I2d,-I2d_c), SUM(Modulus)) < I2d_c, MOD( SUM(I1b) - SUM(I2d,-I2d_c), SUM(Modulus)), I2d_c)) +IF( MOD( SUM(I2d,-I2d_c) - SUM(I1b,-I1b_a), SUM(Modulus)) <> 0, IF( MOD( SUM(I1b,-I1b_a) - SUM(I2d,-I2d_c), SUM(Modulus)) < I2d_c, IF( MOD( SUM(I2d) - SUM(I1b,-I1b_a), SUM(Modulus)) < I1b_a, MOD( SUM(I2d) - SUM(I1b,-I1b_a), SUM(Modulus)), I1b_a))) +INT(I2d_c/SUM(Modulus))*I1b_a

相关内容