Как ограничить вход в диапазон ячеек числовой (целой #) максимальной длиной 9 символов, без начальных нулей и без дубликатов?

Question

Как ограничить вход в диапазон ячеек числовой (целой #) максимальной длиной 9 символов, без начальных нулей и без дубликатов?

Я также хочу ограничить другой диапазон ячеек максимум 24 альфа и только "-"

Answer 1

Первая часть вашего вопроса может быть выполнена с проверкой данных и пользовательским форматом. Для этого сначала выберите весь диапазон для проверки, а затем нажмите кнопку "Проверка данных" на ленте "Данные". Затем, чтобы установить пользовательский формат, на вкладке "Настройки" диалогового окна "Проверка данных" измените раскрывающийся список "Разрешить" на "Пользовательский" и вставьте или введите формулу, включая начальный = в поле "Формула":

=AND(A1 > 0, A1 < 1000000000, A1 = INT(A1), COUNTIF(A:A, A1) <= 1)

• Формула AND возвращает true, если все ее аргументы верны.
• A1 > 0 подтверждает, что число положительное.
• A1 < 1000000000 подтверждает, что число длиной не более 9 символов.
• A1 = INT(A1) подтверждает, что число не является десятичным.
• COUNTIF(A:A, A1) <= 1 подтверждает, что диапазон содержит только 1 экземпляр числа.

Нет необходимости проверять отсутствие начальных нулей, если диапазон не отформатирован как текст, поскольку Excel автоматически удаляет любые начальные нули из чисел.

В этой формуле предполагается, что целевым диапазоном является столбец А. Измените все экземпляры A1 в формуле на первую ячейку в целевом диапазоне, а $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

Answer 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. выберите View Code - откроется окно VBE
3. вставьте материал и закройте окно VBE

Если у вас есть какие-либо проблемы, сначала попробуйте на пробную версию.

Если вы сохраните книгу, макрос будет сохранен вместе с ней. Если вы используете версию Excel более поздней, чем в 2003 году, вы должны сохранить файл как .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

Макросы должны быть включены, чтобы это работало!