如何在 Excel 中将排序列重新编号为从零开始

Question

我在 excel 中有 2 列数字。对它们进行排序后，我想将数字转换为从零开始的排序数字，其中最小数字转换为 0，第二个数字转换为 1 等等。例如，我有以下两列：

我想要这样的结果：

有人可以帮帮我吗？！

谢谢

Answer 1

假设您的数据（如图所示）在A1:B3

1. 在D1， 进入 =SUMPRODUCT((A1>$A$1:$B$3)/COUNTIF($A$1:$B$3,$A$1:$B$3))

2. 填充公式至D3，然后转到E3。

如果您想要破坏性地替换原始排序数据，请继续执行可选步骤 3：

3. 选择+复制D1:E3--> 粘贴为值A1。

---

公式解释：

理解这个公式有两个关键部分。

1. (A1>$A$1:$B$3)生成一个布尔值数组：TRUE对于 A1:B3 中小于 A1 的值，FALSE对于大于 A1 的值。由于此数组在公式中被除法，Excel 会将这些布尔值转换为 1 和 0。
2. SUMPRODUCT(1/COUNTIF($A$1:$B$3,$A$1:$B$3))是用于计算数组中唯一值的构造。它通过对每个值加权来实现1/(the number of times the number appears in the array)。这保证了与该值相关的所有值的总和将等于1。这样想。如果 X 出现n次，每次计为1/n，则这些计数的总和为n*(1/n)=1。也就是说，每个唯一值只计一次。

因此，考虑到这一点，请考虑示例公式=SUMPRODUCT((B1>$A$1:$B$3)/COUNTIF($A$1:$B$3,$A$1:$B$3))，该公式将唯一 ID 分配3给示例中的值2000。逐步地，以下是公式的评估方式：

1. =SUMPRODUCT((2000>{1001,1001,1002,2000,1002,1003})/COUNTIF($A$1:$B$3,$A$1:$B$3))
2. =SUMPRODUCT({TRUE,TRUE,TRUE,FALSE,TRUE,TRUE}/COUNTIF($A$1:$B$3,$A$1:$B$3))
3. =SUMPRODUCT({TRUE,TRUE,TRUE,FALSE,TRUE,TRUE}/COUNTIF({1001,1001,1002,2000,1002,1003},$A$1:$B$3))
4. =SUMPRODUCT({TRUE,TRUE,TRUE,FALSE,TRUE,TRUE}/{2,2,2,1,2,1})
5. =SUMPRODUCT({1/2,1/2,1/2,0/1,1/2,1/1})
6. =3

Answer 2

1. 将 A 列复制到 D 列
2. 将 B 列的所有值复制到 D 列，其目标位置从 A 列下方的第一个空单元格开始（现在您拥有 D 列中的所有值）
3. 选择 D 列的内容并删除重复项
4. 0在单元格 E1 中输入值
5. 1在单元格 E2 中输入值
6. 选择单元格 E1:E2
7. 双击选择边界右下角的小方块（数字序列现已延伸至最后一个数字）
8. 在单元格 F1 中输入=VLOOKUP(A1;$D:$E;2;FALSE)
9. 聚焦单元格 G1 并按下Ctrl+R
10. 选择单元格 F1:G1
11. 双击选择边界右下角的小方块

现在，您在 F 列和 G 列中看到了结果。（忽略#N/A这些列底部的剩余值。）

如果有什么不清楚的话可以随时询问。