如果值小于指定的数字,则 Excel 分组行

如果值小于指定的数字,则 Excel 分组行

我有如下示例数据:

| 项目 | 数量 |

| -------- | -------------- |

| 一 | 18 |

| 乙 | 18 |

| C | 7 |

| 天 | 20 |

| 英文 | 13 |

| 頁 | 2 |

| 克 | 3 |

| 小时 | 20 |

我想对行进行分组,这样如果数量小于特定数字,比如说 25(列数量上的数字不会大于 25),它将被放在第 1 组。然后,如果添加到当前行的下方行仍然小于 25,那么它将被放在同一组中。否则它将在不同的组中

以下是我希望实现的价值

| 项目 | 数量 | 组 |

| --- | --- | --- |

| 一 | 18 | 1 |

| 乙 | 18 | 2 |

| C | 7 | 2 |

| D | 20 | 3 |

| 英文版 | 13 | 4 |

| F | 2 | 4 |

|克|3|4|

| 小时 | 20 | 5 |

希望我的问题没有让您感到困惑。

谢谢

答案1

我理解您想要按顺序将项目分组,每组不超过 25 个。

您需要的是运行总计列,它将告诉您每组中有多少。当您开始新的组号时,此总计将“重置”。

假设 A 列为项目,B 列为数量,C 列为总数,D 列为组号。第 1 行包含标题,数据从 A2 和 B2 开始。

C2:

=B2

D2:

=1

C3:

=IF(C2+B3 <= 25, C2+B3, B3)

D3:

=IF(C2+B3 <= 25, D2, D2+1)

现在将 C3 和 D3 中的公式复制并粘贴到最后一行。另外,如果您对总数不感兴趣,请隐藏 C 列。

答案2

有一种方法可以在不使用辅助列的情况下完成此操作。假设数据从第 2 行开始,位于 AC 列。

您正在尝试查明当前框/批次/组加上当前行的内容是否超过 25 的容量,在这种情况下我们开始一个新的框/组。

或者换句话说,此行的项目数是否多于当前框/组的剩余空间所能容纳的数量。

因此,我们需要使用 SUMIF 找出最后一个框中已有多少个项目,并仔细构造它,以便它仅计算当前行上方的行,以避免循环引用。例如,在 C19 中,这看起来像:

SUMIF($C$2:$C18,$C18,$B$2:$B18)

翻译过来就是:对 B 列中此列上方的所有单元格求和(从 $B$2 到 $B18,请注意这里没有美元符号,因此它会在每一行上发生变化),但仅限于 C 列的值与此列上方的行(当前组/框)相同的单元格。这告诉我们当前组中已有的内容。

您可以添加当前数量并检查总数是否小于或等于 25,或者先将其减去然后进行比较。如果合适,则使用上面单元格中的值,否则加 1。您需要在单元格 C2 中以“1”开头,然后在 C3 中使用:

=IF($B3<=(25-SUMIF($C$2:$C2,$C2,$B$2:$B2)),$C2,$C2+1)

或者

=IF($B3+SUMIF($C$2:$C2,$C2,$B$2:$B2))<=25,$C2,$C2+1)

它们是等效的,但在您进行故障排除时,从逻辑上讲,其中一个可能比另一个更有意义。 将其一直复制到 C 列,直到需要为止。

相关内容