如何检查我是否在 Excel 中输入了所有必要的数字？

Question

我在 Excel 表中输入了 1-1074 之间的数字，我想检查是否漏掉了该范围内的任何数字。我该如何检查？

Answer 1

即使条目未排序，此宏也应该有效：

Sub DataCheck()
    Dim A As Range, i As Long, msg As String, r As Range
    msg = ""
    Set A = Range("A:A")

    For i = 1 To 1074
        Set r = A.Find(what:=CStr(i), After:=A(1), lookat:=xlWhole)
        If r Is Nothing Then
            msg = msg & vbCrLf & i
        End If
    Next i

    If msg = "" Then
        MsgBox "nothing missing"
    Else
        MsgBox "These are missing:" & msg
    End If
End Sub

Answer 2

如果您只需要“是/否”答案，那么此公式应该为您返回 TRUE/FALSE：

=SUMPRODUCT(--(COUNTIF(myRange,ROW(INDIRECT("1:1074")))>0))=1074