список проверки данных Excel только в верхнем регистре

894
XtremeBaumer

Я хочу сделать некоторые проверки данных в моей книге Excel. Я хочу использовать как можно больше централизованных значений для проверки данных, так как одни и те же значения используются для нескольких листов. Теперь я добавил некоторые проверки для кодов валют (USD ...). проверка основана на списке на другом листе, и его работа вроде хорошая. Моя проблема в том, что я все еще могу вводить строчные значения, а не только точные значения. Я бы предпочел решение, которое не использует VBA возможно. Если это возможно только с VBA, пожалуйста, добавьте некоторую информацию о том, как добавить его в рабочую книгу и тому подобное.

Значения, которые я хочу разрешить:

CHF EUR GBP USD DKK SEK NOK

и проверка данных выглядит следующим образом: =Sheet1!$S$2:$S$8но также допускает значения выше в нижнем регистре, что не то, что я хочу (я вижу, что я могу перечислить значения внутри проверки данных, и тогда он принимает только точные совпадения, но ради не имея много работы, если значения меняются, я хочу, чтобы они были централизованы).

Я думаю, что было бы также достаточно преобразовать значения в верхний регистр, но это должно происходить в той же ячейке, например. Я ввожу eurв ячейку, J6то это должно преобразовать значение в верхний регистр и записать его также вJ6

0
Если вы вводите список в поле со списком вместо диапазона, он * должен * соответствовать регистру. mcalex 6 лет назад 0
Является ли ваша область ввода согласованным диапазоном? IQV 6 лет назад 0
@mcalex это правда, но если я хочу добавить / удалить значение, это большая работа. в идеале я хотел бы иметь 100 ячеек в качестве диапазона проверки, но должны отображаться только непустые ячейки (нет пустых значений в списке, даже если только 5 из 100 ячеек содержат значение). также он должен соответствовать регистру с использованием ссылки на ячейку в использовании XtremeBaumer 6 лет назад 0
@IQV - это именованный диапазон, но я подумал об использовании именованной таблицы, но я не могу ссылаться на таблицы из мастера проверки данных XtremeBaumer 6 лет назад 0

2 ответа на вопрос

1
Andi Mohr

Если ячейка, которую вы хотите проверить, находится в ячейке A1, установите для параметра Тип проверки данных значение Пользовательский вместо Списка и вставьте эту формулу в:

=AND(SUMPRODUCT(--((CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))>64)),--(CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))<91))=LEN(A1),--(IFERROR(IF(MATCH(A1,Sheet1!$S$2:$S$8,0),1),0)))

Прохождение

Эта формула использует два условия в сочетании с AND()- во-первых, проверкой того, является ли значение верхним регистром, во-вторых, проверкой того, что значение соответствует вашему списку разрешенных опций.

 =AND( SUMPRODUCT( --( (CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))>64) ) ,--(CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))<91) )=LEN(A1) ,--(IFERROR(IF(MATCH(A1,Sheet1!$S$2:$S$8,0),1),0)))

Биты, которые выглядят CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))>64, проверяют код ASCII для каждого отдельного символа. Если они между кодами ASCII 64 и 91, мы знаем, что они прописные. Если количество символов в верхнем регистре соответствует длине строки значения, мы знаем, что все они в верхнем регистре.

В MATCH()части проверяет значение отображается в списке валют.

Примечание: кричите на этот пост для проверки заглавных букв .

я получаю сообщение об ошибке, когда пытаюсь использовать форум в качестве проверки данных в пользовательском режиме: есть проблема с этой формулой. = AND (PRODUCT (- ((CODE (MID (W6, ROW (INDIRECT ("1:" & LEN) (W6))), 1))> 64)), - (КОД (MID (W6, СТРОКА (ДВССЫЛ ( "1": & LEN (W6))), 1)) <91)) = LEN (W6) , - (IFERROR (IF (MATCH (W6, Sheet1! $ S $ 2: $ S $ 8,0), 1), 0))) `должно быть правильным для примера XtremeBaumer 6 лет назад 0
@XtremeBaumer у вас, похоже, есть опечатка в начале формулы. Это должно быть `SUMPRODUCT (...`, а не `PRODUCT (...`. Все остальное выглядит хорошо. Andi Mohr 6 лет назад 0
Также, если вы хотите заменить `Sheet1! $ S $ 2: $ S $ 8` именованным диапазоном, чтобы вам не приходилось редактировать диапазон при добавлении новых значений, вы можете сделать это. Andi Mohr 6 лет назад 0
1
IQV

Я нашел решение с помощью VBA:
щелкните правой кнопкой мыши вкладку вашего рабочего листа и выберите «Показать код ...». В VBA-редакторе введите эти макросы:

Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("A1:C100")) Is Nothing Then On Error GoTo ErrorHandler Application.EnableEvents = False Target.Value = UCase(Target) ErrorHandler: Application.EnableEvents = True End If End Sub

Измените диапазон, соответствующий вашим потребностям. Вы должны сохранить свой файл как *.xlsm. Затем каждый текст в диапазоне преобразуется в верхний регистр.

диапазон в основном столбец правильно? диапазон начинается с J6 и продолжается столько времени, сколько необходимо. так что я бы изменил диапазон на `J6: J`? XtremeBaumer 6 лет назад 0
Вы можете использовать `J: J`. IQV 6 лет назад 0
ну, у столбца есть заголовок в строке 3, который не соответствует никаким значениям, и поэтому я хотел бы просто начать в строке 6, потому что там начинаются данные XtremeBaumer 6 лет назад 0
Затем вы должны использовать `J6: J1048576` или номер строки для конца, который соответствует вашим потребностям. IQV 6 лет назад 0
чтобы включить функциональность для нескольких листов, я должен добавить код для каждого листа, верно? XtremeBaumer 6 лет назад 0
Нужно некоторое время, чтобы проверить и проверить это ... IQV 6 лет назад 0
nvm просто добавил его на каждый лист вручную, потому что столбцы отличаются. в любом случае спасибо. теперь у меня есть значение, которое будет проверено списком, и если оно сработает, оно будет преобразовано в верхний регистр XtremeBaumer 6 лет назад 0

Похожие вопросы