Проверка данных Excel 2010 позволяет использовать определенный макет

Я хочу применить проверку данных к ссылочным номерам, чтобы принудительно использовать следующий макет (в идеале только в верхнем регистре):

XX_NNX-XX_NNN_NN-XXX

Х = Числа

Н = буквы

Пример: 12_AB1-23_ABC_AB-123

Следующая пользовательская формула допускает все это, кроме чисел — есть ли обходной путь для этого?

Я не хочу использовать *, так как он позволяет использовать больше символов, чем мне нужно.

=COUNTIF(A1,"??_???-??_???_??-???")


validation excel wildcard
person Snottus    schedule 22.03.2020    source источник

Ответы (1)


arrow_upward
1
arrow_downward

Вы можете выбрать AND, чтобы добавить условие к уже написанной функции. например ниже проверяются позиции, которые должны быть числовыми.

=AND(COUNTIF(A1,"??_???-??_???_??-???"),ISNUMBER((LEFT(A1,2)&MID(A1,6,1)&MID(A1,8,2)&RIGHT(A1,3))+0))

Примечания. Это довольно простой подход, который может потребовать некоторых настроек, если у вас есть случаи, связанные с использованием десятичных знаков и т. д.

Изменить: вы можете попробовать следующий подход для проверки текста в верхнем регистре в указанных позициях.

=AND(COUNTIF(A1,"??_???-??_???_??-???"),ISNUMBER((LEFT(A1,2)&MID(A1,6,1)&MID(A1,8,2)&RIGHT(A1,3))+0),INDEX(FREQUENCY(-CODE(MID(MID(A1,4,2)&MID(A1,11,3)&MID(A1,15,2),ROW($A$1:$A$7),1)),{-91,-65,0}),2)=7)

Edit2: это оказалось сложнее, чем я себе представлял. Следующая формула работает в DV для ячейки A1.

=AND(COUNTIF(A1,"??_???-??_???_??-???"),ISNUMBER((LEFT(A1,2)&MID(A1,6,1)&MID(A1,8,2)&RIGHT(A1,3))+0),MIN(FLOOR(CODE(MID(MID(A1,4,2)&MID(A1,11,3)&MID(A1,15,2),ROW($A$1:$A$7),1)),65))=65,MAX(CEILING(CODE(MID(MID(A1,4,2)&MID(A1,11,3)&MID(A1,15,2),ROW($A$1:$A$7),1)),90))=90)

Но из-за какой-то причуды Excel не позволяет мне просто вставить его. Поэтому я написал небольшой код для того же, который применяет DV к ячейке A1, и, что удивительно, он принимает ту же самую длинную формулу через код. Убедитесь, что вы удалили DV в ячейке, прежде чем запускать этот код.

With Range("A1")
    .Value = "12_AB1-23_ADC_AZ-123"
    .Validation.Add xlValidateCustom, , , "=AND(COUNTIF(A1,""??_???-??_???_??-???""),ISNUMBER((LEFT(A1,2)&MID(A1,6,1)&MID(A1,8,2)&RIGHT(A1,3))+0),MIN(FLOOR(CODE(MID(MID(A1,4,2)&MID(A1,11,3)&MID(A1,15,2),ROW($A$1:$A$7),1)),65))=65,MAX(CEILING(CODE(MID(MID(A1,4,2)&MID(A1,11,3)&MID(A1,15,2),ROW($A$1:$A$7),1)),90))=90)"
End With

Оказавшись там, вы можете получить его в любой другой ячейке, просто выполнив copy >> Paste Special >> Validation.

Для наглядности я использую Excel 2016.

person shrivallabha.redij    schedule 22.03.2020
comment
Спасибо за быстрый ответ! Это решает проблему с числами, но позволяет заменять буквы числами — я пробовал возиться с функцией ISTEXT, но пока безрезультатно. - person Snottus; 22.03.2020
comment
@Snottus Я опубликовал правку. Пожалуйста, проверьте и посмотрите, поможет ли это вашему делу. - person shrivallabha.redij; 23.03.2020
comment
Немного поторопился с ответом - отредактированная функция несовместима с проверкой данных (объединения, пересечения и диапазоны не разрешены), по крайней мере, в Excel 2010. - person Snottus; 24.03.2020
comment
@Snottus, ты был прав! Это оказывается сложнее, чем я думал вначале. См. редактирование 2. Это проверено и протестировано в Excel 2016, и я надеюсь, что оно сработает для вас в Excel 2010! - person shrivallabha.redij; 25.03.2020