☰

如何在 Excel 中对数据进行分组后添加包含 ID 数字范围的列

tag-icon • tag-icon microsoft-excel text-manipulation
如何在 Excel 中对数据进行分组后添加包含 ID 数字范围的列

在数据中，我有字段“ItemName”和“ID”。我需要按“ItemName”对行进行分组，并以某种方式在“ID”中创建一个“人类可读”的连续数字范围。我尝试使用 Power Query 来做到这一点，但没有成功。我需要知道哪些工具或操作方法才能获得如下例图中所示的效果。

在此处输入图片描述

答案1

需要做一些工作，但只要 A 列和 B 列中的数据正确排序，这种方法就有效。C 列到 E 列有条件格式，只需目测结果即可更好地确定 F 列所需的规则。

C2单元格公式：（确定与前一行相比是否出现新的项目名称）：

=IF(B2=B1,1,0)

D2 单元格公式：（确定与前一行相比 ID 是否按顺序排列）：

=IFERROR(IF(A1+1=A2,1,0),0)

E2 单元格公式：（过时的列，确定 ID 是否与下一行按顺序排列。只添加了此列，因为我不知道在构建时是否需要它）：

=IFERROR(IF(A2+1=A3,1,0),0)

单元格 F2 公式：（确定我们是否开始一个新序列，当 C 为 0 或 D 为 0 时触发）：

=IF(OR(C2=0,D2=0),1,0)

接下来，G 列将根据之前制定的名称和顺序规则构建一个组。在 G2 中，我只需输入硬编码值 101。其下方的单元格将使用公式。

G3细胞公式：

=F3+G2

将所有公式拖下来。接下来，复制 G 列并将其作为特殊值粘贴到 I 列，然后删除重复项。

J2 细胞公式：（获取与序列组关联的名称）：

=INDEX(B:B,MATCH(I2,G:G))

K2 细胞公式：（获取与序列组关联的最小ID）：

=MINIFS(A:A,B:B,J2,G:G,I2)

L2 单元格公式：（获取与序列组相关的最大ID）：

=MAXIFS(A:A,B:B,J2,G:G,I2)

细胞M2公式：（构建你的序列范围）：

=IF(K2=L2,K2,CONCAT(K2, " - ", L2))

细胞N2公式：（带上名字）：

=J2

向下拖动公式。最终输出位于 M 列和 N 列中。

在此处输入图片描述

答案2

要解决这个问题，你需要一个辅助数据列：

：：警告：：

  • 根据名称创建组是关键问题，因为 ID 必须根据相同的名称分为 2 个组Name1，因此我应用的最佳方法是 HELPER DATA 列。

  • 希望有人能分享更好、更有效的方法。

在此处输入图片描述

怎么运行的：

  • 单元格 C65 中的公式：

    =IF(ISBLANK(A65),"",(IF(AND(LEN(A65)=4,A65<1300),"NMG1",IF(AND(LEN(A65)=4,A65>=1300),"NMG2","NMG3"))))

  • 单元格 E65 中的数组 (CSE) 公式：

    {=IFERROR(INDEX($C$65:$C$75, MATCH(0,COUNTIF($E$64:E64, $C$65:$C$75), 0)),"")}

  • 单元格 D65 中的数组（CSE）公式：

    {=IF(E65<>"",MIN(IF($C$65:$C$75=E65,$A$65:$A$75))&" - "&MAX(($C$65:$C$75=$E65)*$A$65:$A$75),"")}

注意：

  • 使用以下方式完成数组（CSE）公式Ctrl+Shift+Enter。

  • 根据需要调整公式中的单元格引用。

相关内容