☰

如何将单元格范围内的条目限制为数字（整数#），最大长度为 9 个字符、无前导零且无重复？

tag-icon • tag-icon microsoft-excel microsoft-excel-2010
如何将单元格范围内的条目限制为数字（整数#），最大长度为 9 个字符、无前导零且无重复？

如何将单元格范围内的条目限制为数字（整数#），最大长度为 9 个字符、无前导零且无重复？

我还想将另一个单元格范围限制为最多 24 个字母和“-”

答案1

问题的第一部分可以通过数据验证和自定义格式来完成。为此，首先选择要验证的整个范围，然后单击数据功能区中的“数据验证”按钮。然后要设置自定义格式，请在“数据验证”对话框的“设置”选项卡中，将“允许”下拉菜单更改为“自定义”，然后将包括前导的公式粘贴或键入=到公式框中：

=AND(A1 > 0, A1 < 1000000000, A1 = INT(A1), COUNTIF(A:A, A1) <= 1)
  • AND如果公式的所有参数都为真，则 该公式返回真。
  • A1 > 0验证该数字是否为正数。
  • A1 < 1000000000验证数字长度最多为 9 个字符。
  • A1 = INT(A1)验证该数字不是小数。
  • COUNTIF(A:A, A1) <= 1验证范围仅包含 1 个数字实例。

除非范围格式化为文本，否则无需检查是否存在前导零，因为 Excel 会自动从数字中删除任何前导零。

A1此公式假设您的目标范围是 A 列。将公式中的所有 更改为目标范围中的第一个单元格，并将$A:$A更改为整个范围。引用第一个单元格时请务必使用相对引用形式，引用整个范围时请务必使用绝对引用形式（即B1第一个单元格和$B$1:$B$20整个范围）。

使用数据验证的好处是不需要使用宏，并且您可以在数据验证对话框中指定自定义输入消息和自定义错误消息。

如果没有 VBA，您的问题的第二部分就无法轻松完成。如果您正在使用表格并将数据验证应用于列，则可以使用隐藏列和类似以下内容的自定义函数轻松完成此操作：

Function LimitAlpha24(str As String) As Boolean
    Dim rx As Object
    Set rx = CreateObject("VBScript.RegExp")
    rx.Pattern = "^[A-Za-z-]{0,24}$"
    LimitAlpha24 = rx.Test(str)
End Function

答案2

将以下事件宏放置在工作表代码区域：

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim A As Range, v As Variant, L As Long, i As Long
    Dim wf As WorksheetFunction
    Set wf = Application.WorksheetFunction
    Set A = Range("A1:A10")
    If Intersect(Target, A) Is Nothing Then Exit Sub
    Application.EnableEvents = False
    v = Target.Text
    L = Len(v)

    If L > 9 Then
        MsgBox "input too long"
        GoTo errOut
    End If

    For i = 1 To L
        If Mid(v, i, 1) Like "[0-9]" Then
        Else
            MsgBox "bad character"
            GoTo errOut
        End If
    Next i

    If Left(v, 1) = "0" Then
        MsgBox "leading zero"
        GoTo errOut
    End If

    If wf.CountIf(A, v) > 1 Then
        MsgBox "duplicate value"
        GoTo errOut
    End If
    Application.EnableEvents = True
    Exit Sub

errOut:
    Target.Clear
    Target.Select
    Application.EnableEvents = True
End Sub

假定的单元格范围是A1：A10

因为它是工作表代码，所以非常容易安装和自动使用：

  1. 右键单击 Excel 窗口底部附近的选项卡名称
  2. 选择查看代码-这将打开一个 VBE 窗口
  3. 粘贴内容并关闭 VBE 窗口

如果您有任何疑虑，请首先在试用工作表上尝试一下。

如果您保存工作簿，宏将随之保存。如果您使用的是 2003 以后的 Excel 版本，则必须将文件保存为 .xlsm 而不是 .xlsx

要删除宏：

  1. 调出如上所示的 VBE 窗口
  2. 清除代码
  3. 关闭 VBE 窗口

要了解有关宏的更多信息，请参阅：

http://www.mvps.org/dmcritchie/excel/getstarted.htm

和

http://msdn.microsoft.com/en-us/library/ee814735(v=office.14).aspx

要了解有关事件宏（工作表代码）的更多信息，请参阅：

http://www.mvps.org/dmcritchie/excel/event.htm

必须启用宏才能使其工作！

相关内容