Как ограничить вход в диапазон ячеек числовой (целой #) максимальной длиной 9 символов, без начальных нулей и без дубликатов?
Я также хочу ограничить другой диапазон ячеек максимум 24 альфа и только "-"
Как ограничить вход в диапазон ячеек числовой (целой #) максимальной длиной 9 символов, без начальных нулей и без дубликатов?
Я также хочу ограничить другой диапазон ячеек максимум 24 альфа и только "-"
Первая часть вашего вопроса может быть выполнена с проверкой данных и пользовательским форматом. Для этого сначала выберите весь диапазон для проверки, а затем нажмите кнопку "Проверка данных" на ленте "Данные". Затем, чтобы установить пользовательский формат, на вкладке "Настройки" диалогового окна "Проверка данных" измените раскрывающийся список "Разрешить" на "Пользовательский" и вставьте или введите формулу, включая начальный =
в поле "Формула":
=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
Поместите следующий макрос событий в область кода рабочего листа:
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
Поскольку это код рабочего листа, его очень легко установить и использовать автоматически:
Если у вас есть какие-либо проблемы, сначала попробуйте на пробную версию.
Если вы сохраните книгу, макрос будет сохранен вместе с ней. Если вы используете версию Excel более поздней, чем в 2003 году, вы должны сохранить файл как .xlsm, а не .xlsx
Чтобы удалить макрос:
Чтобы узнать больше о макросах в целом, смотрите:
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
Макросы должны быть включены, чтобы это работало!