Excel实用技巧之如何Excel中如何创建唯一的编号

我们，让Excel变简单

在很多时候，我们需要一个不重复的编号。例如，合同编号。在有些场景下，这种编号是很容易获得的，例如，身份证号，员工编号，产品的SKU号，甚至商品的条形码编号。但有时候，没有这种天然的（其实就是别人已经编制好了的）唯一编号，我们只能自己来制作。

有些同学可能会认为这还不简单，直接使用函数就可以了：

在这里，我们使用了行号来生成唯一的ID。这种方法可以保证在这个Sheet中没有重复的ID。既简单又直观。

问题是如果有多个人维护同样结构的数据，比如，有A、B、C分别负责不同的客户，每个人都维护一个客户表，每次发展了新客户，都需要在自己的表格中生成客户ID，这个方法就会导致生成3个ID为1的客户。当这些数据要一起分析时，就会产生问题。

所以我们需要使用另外的方法。

方法1 函数法

排除了Row函数，很多同学会自然想到使用RAND或者RANDBETWEEN函数来随机生成。

例如，我们可以使用RANDBETWEEN来随机生成：

但是这么做有一个问题，就是也会重复。首先，这个RANDBETWEEN是在一个范围内取值，比如，最大值是1000000，一旦超过1000000，肯定会重复。另外，在实际上，根本用不了这么多，只要超过1000个基本上肯定会有重复值。

为了生成唯一的不重复ID，很多人想了很多办法，网上使用最多的一个是下面的公式：

=LOWER(CONCATENATE(DEC2HEX(RANDBETWEEN(0,POWER(16,8)),8),"-",DEC2HEX(RANDBETWEEN(0,POWER(16,4)),4),"-","4",DEC2HEX(RANDBETWEEN(0,POWER(16,3)),3),"-",DEC2HEX(RANDBETWEEN(8,11)),DEC2HEX(RANDBETWEEN(0,POWER(16,3)),3),"-",DEC2HEX(RANDBETWEEN(0,POWER(16,8)),8),DEC2HEX(RANDBETWEEN(0,POWER(16,4)),4)))

太复杂了！

但是这个函数的基本原理还是使用RANDBETWEEN（上面公式中红色字体的函数），只不过使用了多个随机函数，这样重复的概率就会显著降低。

简单解释一下这个函数，就是先生成一个随机数，然后将这个随机数转换为十六进制（DEC2HEX函数），将这些十六进制随机数用“-”连接成一个字符串（CONCATENATE函数），最后将字符串转换为小写字母（LOWER函数）。

下面是这个函数的结果：

这是一个标准格式的唯一ID串，很多程序和场景中都会使用这样的串作为标识。

但是，这个看似很厉害的公式也会重复，而且重复率出乎意外的高。我做过不完整的简单测试，大概重复率在万分之三左右。即每生成10000个ID，大约有3个重复的。

这个重复率意味着这个公式其实不太好用

方法2 Power Query

假设，你现在正在拟一份新合同，需要一个唯一的合同ID：

在“数据”选项卡中，点击“新建查询”，“从其他源”，“空白查询”：

在右侧，点击“源”，然后在公式栏中输入公式：= #table({"序号"},{{1}})：

在“添加列”选项卡中，点击“自定义列”：

将列命名为“ID”，公式中输入：Text.NewGUID()

得到表格：

点击关闭并上载：

结果就传到Excel中：

将B2单元格中的ID复制到你的表格中，就得到了一个不重复的ID。

下次你需要新的ID的时候，只要在这个表上点击鼠标右键，然后刷新：

就可以得到一个新的ID了。

总结

方法2是最简单的生成不重复ID的方法了。其他方法要么重复率太高不能用，要么需要编程。

需要说明的一点是，这个方法2从理论上也不能100%确保不重复，但是重复率低到可以忽略不计。我做的简单测试中，没有发现重复的现象。

END

关注ExcelEasy

关于Excel的一切问题，你都可以在这里找到答案