如何在Excel中生成GUID？ [英] How can I generate GUIDs in Excel?

问题描述

我有一个excel文件，每一行有一个订单，我希望每个订单有一个唯一的标识符，所以将有一个唯一ID列。每次填写一行时，我希望Excel自动为我填充唯一ID列。我做了一些研究，指出了GUID的方向。我发现以下代码：

 函数GenGuid（）As String 
 Dim TypeLib As Object 
 Dim Guid As String 
 Set TypeLib = CreateObject（Scriptlet.TypeLib）
 Guid = TypeLib.Guid 
'格式为{24DD18D4-C902-497F-A64B-28B2FA741661} 
 Guid = $（$）
 Guid = Replace（Guid，}，）
 Guid = Replace（Guid， - ，）
 GenGuid = Guid 
结束函数
  

但我不知道我如何实现它。任何帮助将不胜感激。谢谢你提前。

解决方案

以下Excel表达式求值为V4 GUID：

= CONCATENATE（DEC2HEX（RANDBETWEEN（0,4294967295），8）， - ，DEC2HEX（RANDBETWEEN（0,6553 5），4）， - ，DEC2HEX RANDBETWEEN（16384,20479），4）， - ，DEC2HEX（RANDBETWEEN（32768,49151），4）， - ，DEC2HEX（RANDBETWEEN（0,65535），4），DEC2HEX（RANDBETWEEN（0,4294967295 ），8））

-or（取决于区域设置/小数和列表分隔符） -

= CONCATENATE（DEC2HEX（RANDBETWEEN（0; 4294967295）; 8）; - ; DEC2HEX（RANDBETWEEN（0; 65535）; 4）; - ; DEC2HEX（RANDBETWEEN （16384; 20479）; 4）; - ; DEC2HEX（RANDBETWEEN（32768; 49151）; 4）; - ; DEC2HEX（RANDBETWEEN（0; 65535）; 4）; DEC2HEX（RANDBETWEEN（0; 4294967295）;注意，第三组的第一个字符总是4表示V4（伪随机数生成）GUID / UUID根据RFC 4122第4.4节。

另请注意，第四个gr的第一个字符oup总是在相同的RFC之间的8和B之间。

标准免责声明：生成的GUID / UUID不是加密的。

>

I have an excel file with one order on each row, and I want each order to have a unique identifier, so there will be a Unique ID column. Every time I fill a row, I want Excel to automatically populate the Unique ID column for me. I did some research and was pointed in the direction of GUIDs. I found the following code:

Function GenGuid() As String
Dim TypeLib As Object
Dim Guid As String
Set TypeLib = CreateObject("Scriptlet.TypeLib")
Guid = TypeLib.Guid
' format is {24DD18D4-C902-497F-A64B-28B2FA741661}
Guid = Replace(Guid, "{", "")
Guid = Replace(Guid, "}", "")
Guid = Replace(Guid, "-", "")
GenGuid = Guid
End Function

but I am not sure how I can implement it. Any help would be greatly appreciated. Thank you in advance.

解决方案

The following Excel expression evaluates to a V4 GUID:

=CONCATENATE(DEC2HEX(RANDBETWEEN(0,4294967295),8),"-",DEC2HEX(RANDBETWEEN(0,6553‌​5),4),"-",DEC2HEX(RANDBETWEEN(16384,20479),4),"-",DEC2HEX(RANDBETWEEN(32768,49151‌​),4),"-",DEC2HEX(RANDBETWEEN(0,65535),4),DEC2HEX(RANDBETWEEN(0,4294967295),8))

-or (depending on locale setting/decimal and list separators)-

=CONCATENATE(DEC2HEX(RANDBETWEEN(0;4294967295);8);"-";DEC2HEX(RANDBETWEEN(0;65535);4);"-";DEC2HEX(RANDBETWEEN(16384;20479);4);"-";DEC2HEX(RANDBETWEEN(32768;49151);4);"-";DEC2HEX(RANDBETWEEN(0;65535);4);DEC2HEX(RANDBETWEEN(0;4294967295);8))

Note that the first character of the third group is always 4 to signify a V4 (pseudo-random number generated) GUID/UUID per RFC 4122 section 4.4.

Also note that the first character of the fourth group is always between 8 and B per the same RFC.

Standard disclaimer: the resulting GUIDs/UUIDs are not cryptographically strong.

这篇关于如何在Excel中生成GUID？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！