在很多时候,我们需要一个不重复的编号。例如,合同编号。在有些场景下,这种编号是很容易获得的,例如,身份证号,员工编号,产品的SKU号,甚至商品的条形码编号。但有时候,没有这种天然的(其实就是别人已经编制好了的)唯一编号,我们只能自己来制作。
有些同学可能会认为这还不简单,直接使用函数就可以了:
在这里,我们使用了行号来生成唯一的ID。这种方法可以保证在这个Sheet中没有重复的ID。既简单又直观。
问题是如果有多个人维护同样结构的数据,比如,有A、B、C分别负责不同的客户,每个人都维护一个客户表,每次发展了新客户,都需要在自己的表格中生成客户ID,这个方法就会导致生成3个ID为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函数)。
下面是这个函数的结果:
但是,这个看似很厉害的公式也会重复,而且重复率出乎意外的高。我做过不完整的简单测试,大概重复率在万分之三左右。即每生成10000个ID,大约有3个重复的。
这个重复率意味着这个公式其实不太好用
假设,你现在正在拟一份新合同,需要一个唯一的合同ID:
在“数据”选项卡中,点击“新建查询”,“从其他源”,“空白查询”:
在右侧,点击“源”,然后在公式栏中输入公式:= #table({"序号"},{{1}}):
在“添加列”选项卡中,点击“自定义列”:
将列命名为“ID”,公式中输入:Text.NewGUID()
总结
联系客服