我有如下示例数据:
| 项目 | 数量 |
| -------- | -------------- |
| 一 | 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 列,直到需要为止。