logo标签列表

Excel 2007 - 根据文本生成唯一ID？

excelexcel-2007uniqueidentifier
7

我有一张表格，其中B列是名字列表，A列是ID。我想知道是否有某种公式可以根据该行中B列的值生成基于文本的ID？每个名称也是唯一的，在任何情况下都不会重复。

最好不要使用VBA，但如果必须，也无妨。

ID 中字符的长度或使用有任何要求吗？ - Excellll
6个回答
4

无需使用VBA的解决方案。

逻辑基于单元格中前8个字符和字符数。

= CODE(cell) 返回第一个字母的代码编号

= CODE(MID(cell,2,1)) 返回第二个字母的代码编号

= IFERROR(CODE(MID(cell,9,1)) 如果第9个字符不存在，则返回0

= LEN(cell) 单元格中字符数

连接前8个代码 + 在末尾添加字符长度

如果8个字符不足，则为字符串中的下一个字符复制额外的代码。

最终函数：

=CODE(B2)&IFERROR(CODE(MID(B2,2,1)),0)&IFERROR(CODE(MID(B2,3,1)),0)&IFERROR(CODE(MID(B2,4,1)),0)&IFERROR(CODE(MID(B2,5,1)),0)&IFERROR(CODE(MID(B2,6,1)),0)&IFERROR(CODE(MID(B2,7,1)),0)&IFERROR(CODE(MID(B2,8,1)),0)&LEN(B2)

enter image description here

这并不是独一无二的，因为单个字符的代码可以是2位或3位数字；因此，一个由6个字母组成的组合可能与另外5个字母组成的组合具有相同的代码。 - Grade 'Eh' Bacon
提供这样的示例。 - milan minarovic
尝试将这个ASCII代码字符串转换回字母；通过翻转1/2/3位数字字符，我至少可以找到6种方法来将此字符串制作成适当的名称：6510097109236666111983283116463280101116101 [尝试从这个模式开始：232331232223222333]。记住 - 处理边角情况始终是处理用户输入计算的关键。如果您的数据输入无法处理所有情况，则不太可能的用户输入会造成最大的痛苦。 - Grade 'Eh' Bacon
1
你在谈论反转函数。任务是为一组真实独特的名称分配ID。但增强我的解决方案非常简单，以避免混淆2位和3位ASCII码： =（1000+CODE(B2))&IFERROR(1000+CODE(MID(B2,2,1)),0)&IFERROR(1000+CODE(MID(B2,3,1)),0)&IFERROR(1000+CODE(MID(B2,4,1)),0)&IFERROR(1000+CODE(MID(B2,5,1)),0)&IFERROR(1000+CODE(MID(B2,6,1)),0)&IFERROR(1000+CODE(MID(B2,7,1)),0)&IFERROR(1000+CODE(MID(B2,8,1)),0)&LEN(B2) - milan minarovic
这是一个有趣的解决方案 - 强制所有代码都成为4位数字，而不是2或3位。易于实现和易于阅读，因为如果前两个数字是10，则剩下的是2位代码，否则剩下的是3位代码。我很喜欢它。 - Grade 'Eh' Bacon
1

抱歉，我没有找到仅使用公式的解决方案，即使this thread可能会有所帮助（尝试计算scrabble游戏中的得分），但我没有找到确保生成的哈希值是唯一的方法。

然而，这是我的解决方案，基于一个UDF（用户定义函数）：

将代码放入模块中：

Public Function genId(ByVal sName As String) As Long
'Function to create a unique hash by summing the ascii value of each character of a given string
    Dim sLetter As String
    Dim i As Integer
    For i = 1 To Len(sName)
        genId = Asc(Mid(sName, i, 1)) * i + genId
    Next i
End Function

然后在您的工作表中像公式一样调用它：

=genId(A1)

[编辑] 添加了* i以考虑顺序。它在我的单元测试中运行正常。
嗨！这个很好用 :) 尽管，如果名字有相同数量的字符，我会得到一些相同的结果。我想我只需要拆分字符串并选择每个字符串的第一个字母，然后添加这个ID。应该是唯一的 :) - Kenny Bones
1
似乎算法缺少顺序！（它将为“詹姆斯·多”和“多·詹姆斯”生成相同的ID。我将编辑我的答案以改进我的函数（顺便说一句，我已经将ID乘以索引，以便它在某种程度上考虑了顺序。我希望这足够了）。 - JMax
@chrisneilsen：为什么不呢？我知道这种方法没有使用任何标准库来创建哈希，但我想了解在哪种情况下它无法工作。 - JMax
@JMax 请考虑一下：对于简单的三个字母的单词，有52^3 = 140608种可能的单词。您的算法将产生最大数量为732 - 显然，您无法使用<700个值为140000个单词生成唯一的ID！随着单词长度的增加，比率会变得更糟。 - chris neilsen
@chrisneilsen：好的，我明白了。感谢您抽出时间来回答。 - JMax
0

也许对你的需求有些过头了，但你可以调用 CoCreateGuid 来获取一个真正的 GUID

Private Declare Function CoCreateGuid Lib "ole32" (ID As Any) As Long

Function GUID() As String
    Dim ID(0 To 15) As Byte
    Dim i As Long

    If CoCreateGuid(ID(0)) = 0 Then
        For i = 0 To 15
            GUID = GUID & Format(Hex$(ID(i)), "00")
        Next
    Else
        GUID = "Error while creating GUID!"
    End If

End Function

测试使用

Sub testGUID()
    MsgBox GUID
End Sub

如何最好地实现取决于您的需求。一种方法是编写一个宏来获取GUID并填充存在名称的列。（请注意，将其用作UDF不好，因为重新计算时会返回新的GUID）
编辑 请参见此答案以创建字符串的SHA1哈希。
0

你是否只想在你的值旁边有一个递增的数字 id 列？如果是这样，并且你的值始终是唯一的，你可以很容易地使用公式来实现。

如果你的值在 B 列中，例如从标题下面的 B2 开始，那么在 A2 中，你将输入公式 "=IF(B2="","",1+MAX(A$1:A1))"。你可以将其复制并粘贴到数据扩展的范围内，它将为 B 列中每一行递增一个数字标识符，该行不为空。

如果你需要做更复杂的事情，比如识别和重新识别重复的值，或者使标识符在填充后“冻结”，请让我知道。目前，当你清除或添加值到列表时，标识符将上下切换，因此如果你的数据发生变化，你需要小心。

0

基于文本中特定字符数量的唯一标识符。我使用了基于元音和数字的标识符。

=LEN($J$14)-LEN(SUBSTITUTE($J$14;"a";""))&LEN($J$14)-LEN(SUBSTITUTE($J$14;"e";""))&LEN($J$14)-LEN(SUBSTITUTE($J$14;"i";""))&LEN($J$14)-LEN(SUBSTITUTE($J$14;"j";""))&LEN($J$14)-LEN(SUBSTITUTE($J$14;"o";""))&LEN($J$14)-LEN(SUBSTITUTE($J$14;"u";""))&LEN($J$14)-LEN(SUBSTITUTE($J$14;"y";""))&LEN($J$14)-LEN(SUBSTITUTE($J$14;"1";""))&LEN($J$14)-LEN(SUBSTITUTE($J$14;"2";""))&LEN($J$14)-LEN(SUBSTITUTE($J$14;"3";""))&LEN($J$14)-LEN(SUBSTITUTE($J$14;"4";""))&LEN($J$14)-LEN(SUBSTITUTE($J$14;"5";""))&LEN($J$14)-LEN(SUBSTITUTE($J$14;"6";""))&LEN($J$14)-LEN(SUBSTITUTE($J$14;"7";""))&LEN($J$14)-LEN(SUBSTITUTE($J$14;"8";""))&LEN($J$14)-LEN(SUBSTITUTE($J$14;"9";""))&LEN($J$14)-LEN(SUBSTITUTE($J$14;"0";""))
请注意，仅当没有具有相同字符但顺序不同的字符串时，此方法才有效。例如：21 Jump Street和12 Jump Street在此方法中将被视为相同。 - Grade 'Eh' Bacon
0

你说你有信心你的单词中没有重复的值。更进一步，你确定任何单词的前8个字符都是唯一的吗？

如果是这样，您可以使用以下公式。它通过逐个获取每个字符的ASCII代码-40 [假设正常字符，这将使数字在8和57之间，字母在57和122之间]，并将该字符代码乘以10 ^ [该字符的数字位置在单词中]。基本上，它取该字符代码[-40]，并将每个代码连接到下一个。

编辑请注意，此代码不再需要至少存在8个字符才能防止错误，因为实际要编码的单词已附加了8个“0”。

=TEXT(SUM((CODE(MID(LOWER(RIGHT(REPT("0",8)&A3,8)),{1,2,3,4,5,6,7,8},1))-40)*10^{0,2,4,6,8,10,12,14}),"#")

请注意，由于此方法使用字符的ASCII值，因此ID #可以用于直接识别名称 - 这并不真正创建匿名性，它只是将8个唯一字符转换为唯一数字。它通过-40进行混淆，但在这个意义上并不真正“安全”。-40只是为了使普通字母和数字处于2位数范围内，以便乘以10^0、2、4等会创建一个2位数的唯一附加代码。 备选方法的编辑 我之前尝试过这样做，让它查看字母表中的每个字母，在单词中计算其出现次数，然后将其乘以10 * [该字母在字母表中的位置]。这样做的问题（请参见下面的公式）是需要10 ^ 26-1的数字，这超出了Excel的浮点精度。但是，我有一个修改过的版本：
通过限制字母表中允许的字符数，我们可以获得最大总大小为10 ^ 15-1，这是Excel可以正确计算的。公式如下：
=RIGHT(REPT("0",15)&TEXT(SUM(LEN(A3)*10^{0,1,2,3,4,5,6,7,8,9,10,11,12,13,14}-LEN(SUBSTITUTE(A3,MID(Alphabet,{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15},1),""))*10^{0,1,2,3,4,5,6,7,8,9,10,11,12,13,14}),"#"),15)

[公式中的RIGHT("00000000000000"…部分旨在保持所有代码具有相同数量的字符]

请注意，这里的Alphabet是一个命名字符串，其中包含字符：“abcdehilmnorstu”。例如，使用上述公式，“asdf”单词计算a、s和d的实例，但不计算不在我的缩写字母表中的“f”。 “asdf”的代码将是：

001000000001001

这仅适用于以下假设：

未列出的字母（也不包括数字/特殊字符）不需要使每个名称唯一。例如，asdf和asd在上述方法中具有相同的代码。

并且，

字母的顺序不需要使每个名称唯一。例如，asd和dsa在上述方法中具有相同的代码。

注意：我曾尝试通过逐个计算每个单词中a-z字符的数量，并将该数字（假设为0-9）放置在10^26数字的位数中，如果10^26不超出Excel浮点值的精度，则可以实现。如下所示：=TEXT(SUM(LEN(A3)*10^{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26}-LEN(SUBSTITUTE(A3,MID(Alphabet,{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26},1),""))*10^{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26}),"#") - Grade 'Eh' Bacon
1
在上面的例子中，Alphabet是一个命名范围，其中包含一个字符串“abcd...z”。 - Grade 'Eh' Bacon
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接