☰

如何在单元格中显示缺失的唯一数字？

tag-icon • tag-icon microsoft-excel worksheet-function microsoft-excel-2010
如何在单元格中显示缺失的唯一数字？

例如，我在一个单元格中有一个 5 位数字23450。我想显示此数字中缺少的数字 0 到 9。在此示例中，我的另一个单元格应显示16789缺少的数字。以下是示例：

A 列 B 列（应显示缺失数字）
23456 17890
34877 125690
22344 1567890
27788 1345690

请注意，A 列中的数字可以包含同一位数字的倍数，在这种情况下，B 列将包含五位以上的缺失数字。

我不知道如何解决这个问题。

答案1

我的非 VBA 解决方案与 Máté Juhász 的解决方案类似，但没有替换；它只是构建字符串：

=IF(ISERROR(FIND("0",A1)),"0","")&IF(ISERROR(FIND("1",A1)),"1","")
 &IF(ISERROR(FIND("2",A1)),"2","")&IF(ISERROR(FIND("3",A1)),"3","")
 &IF(ISERROR(FIND("4",A1)),"4","")&IF(ISERROR(FIND("5",A1)),"5","")
 &IF(ISERROR(FIND("6",A1)),"6","")&IF(ISERROR(FIND("7",A1)),"7","")
 &IF(ISERROR(FIND("8",A1)),"8","")&IF(ISERROR(FIND("9",A1)),"9","")

答案2

使用VBA可以轻松实现：

Public Function uniquenumbers(cellvalue As String)
    originalseries = "1234567890"
    totallength = Len(cellvalue)
    For i = 1 To totallength
        thevalue = Mid(cellvalue, i, 1)
        theposition = InStr(1, originalseries, thevalue)
        If theposition > 0 Then
            originalseries = Mid(originalseries, 1, theposition - 1) & Mid(originalseries, theposition + 1)
        End If
    Next i
    uniquenumbers = originalseries
End Function

ALT使用+打开宏/VBA F11。右键单击本工作簿并插入模块. 将代码粘贴到右侧。

在手机B2上=uniquenumbers(A2)。

答案3

我的方法是先从一串全数字开始（0123456789），然后删除源字符串中存在的每个数字，例如0：

=SUBSTITUTE("0123456789",IF(ISERROR(FIND("0",A1)),"","0"),"")

然后您需要将其嵌套到下一级以处理其结果：

=SUBSTITUTE(SUBSTITUTE("0123456789",IF(ISERROR(FIND("0",A1)),"","0"),""),IF(ISERROR(FIND("1",A1)),"","1"),"")

对于所有数字：

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE("0123456789",IF(ISERROR(FIND("0",A1)),"","0"),""),IF(ISERROR(FIND("1",A1)),"","1"),""),IF(ISERROR(FIND("2",A1)),"","2"),""),IF(ISERROR(FIND("3",A1)),"","3"),""),IF(ISERROR(FIND("4",A1)),"","4"),""),IF(ISERROR(FIND("5",A1)),"","5"),""),IF(ISERROR(FIND("6",A1)),"","6"),""),IF(ISERROR(FIND("7",A1)),"","7"),""),IF(ISERROR(FIND("8",A1)),"","8"),""),IF(ISERROR(FIND("9",A1)),"","9"),"")

相关内容