정규 표현식 (Regex)을 사용한 Excel 데이터 검증-excel-php.cn

Excel 워크 시트에서 사용자 입력을 제한 할 때 데이터 검증은 필수 불가능합니다. 주어진 셀에서 숫자 또는 날짜 만 허용하고 싶습니까? 아니면 텍스트 값을 특정 길이로 제한 하시겠습니까? 아니면 주어진 범위 밖에서 시간을 허용하지 않습니까? 문제 없습니다.이 모든 것은 사전 설정 또는 사용자 정의 검증 기준으로 쉽게 수행 할 수 있습니다. 그러나 특정 패턴과 일치하는 유효한 이메일 주소 또는 문자열 만 허용하려면 어떻게해야합니까? 아아, 불가능합니다. 성폭력 당신은 말합니까? 흠… 작동 할 수도 있습니다!

Regex를 사용하여 Excel 데이터 검증을 수행하는 방법

유감스럽게도, 내장 된 Excel 기능 중 어느 것도 REGEXE를 지원하지 않으며 데이터 검증도 예외는 아닙니다. 정규 표현식을 사용하여 셀 입력을 검증하려면 먼저 사용자 정의 Regex 기능을 작성해야합니다. 또 다른 합병증은 VBA 사용자 정의 기능을 데이터 검증에 직접 제공 할 수 없다는 것입니다. 이름이 지정된 공식 형태의 중재자가 필요합니다.

위의 내용을 고려할 때 Regexes를 사용하여 Excel의 데이터를 검증하기 위해 수행 할 단계를 간략하게 설명하겠습니다.

입력 값이 정규 표현식과 일치하는지 확인하는 사용자 정의 Regex 함수를 만듭니다.
REGEX 공식의 이름을 정의하십시오.
이름이 지정된 공식을 기반으로 데이터 유효성 검사 규칙을 구성하십시오.
유효성 검사 설정을 원하는만큼의 셀에 복사하십시오.

계획처럼 들리나요? 실제로 구현해 보겠습니다!

사용자 정규 표현식을 사용한 Excel 데이터 검증

이 예제는 매우 일반적인 경우를 다룹니다. 특정 패턴의 값만 허용하는 방법.

워크 시트에 SKU 코드를 보관하고 주어진 패턴과 일치하는 코드 만 목록에 들어가는 지 확인하고 싶다고 가정합니다. 각 SKU는 하이픈으로 분리 된 2 개의 그룹의 문자로 구성되며, 첫 번째 그룹은 3 개의 대문자와 두 번째 그룹 -3 자리를 포함하여 아래의 값을 사용하여 이러한 값을 식별 할 수 있습니다.

패턴 : ^[az] {3}-\ d {3} $

문자열의 시작 (^) 및 끝 ($)이 고정되어 있으므로 패턴 이외의 다른 문자는 셀에 입력 할 수 없습니다.

1. 사용자 정의 Regex 매치 함수를 추가하십시오

통합 문서에 regexpmatch 함수를 삽입하면서 시작하십시오. 이 코드는 이미 Excel Gurus에 의해 작성되었으므로 위의 연결된 페이지에서 복사하여 VBA 편집기에 붙여 넣기 만하면됩니다.

참조를위한 기능의 구문은 다음과 같습니다.

regexpmatch (텍스트, 패턴, [match_case])

어디:

텍스트 (필수) - 소스 문자열 (우리의 컨텍스트 - 검증 된 셀).
패턴 (필수) - 일치하는 정규 표현.
match_case (선택 사항) - 매치 유형. 참 또는 생략 - 사례에 민감한; 거짓 - 사례에 민감합니다.

팁. 귀하가 Ultimate Suite의 사용자 인 경우 통합 문서에 VBA 코드를 추가하지 않고도 Excel에서 REGEX 데이터 검증을 수행 할 수 있습니다. REGEX 도구에 포함 된 사용자 정의 ablebitsregexmatch 함수를 활용하십시오.

2. 명명 된 공식을 만듭니다

대상 워크 시트에서 셀 A1 (내용과 실제로 어떤 셀에 관계없이 어떤 셀에 관계없이 어떤 셀에 관계없이) CTRL F3을 눌러 이름 관리자를 열고이 공식의 이름을 정의하십시오.

=RegExpMatch(Sheet1!A1, "^[AZ]{3}-\d{3}$")

또는 일부 셀에 Regex를 입력하고 (이 예에서 A2) 두 번째 인수에 $ 2를 공급할 수 있습니다.

=RegExpMatch(Sheet1!A1, Sheet1!$A$2)

공식이 올바르게 작동하려면 텍스트 인수 (A1)에 대한 상대 참조와 패턴 에 대한 절대 참조 ($ 2)에 대한 절대 참조를 사용하십시오.

우리의 공식은 SKU 번호를 확인하기 위해 의도되어 있다는 점을 감안할 때, 우리는 그에 따라 이름을 유지합니다. validate_sku .

정규 표현식 (Regex)을 사용한 Excel 데이터 검증

중요한 메모! 공식을 정의 할 때 첫 번째 인수가 현재 선택된 셀을 지칭하는지 확인하십시오. 그렇지 않으면 공식이 작동하지 않습니다. 예를 들어, 셀 A1이 시트에서 선택되면 첫 번째 인수에 A1을 넣습니다 (권장 사항에 따라). B2가 선택되면 첫 번째 인수에 B2를 사용하십시오. 현재 선택된 셀과 일치하는 한 특정 참조를 사용하는 것은 중요하지 않습니다.

단계별 지침은 Excel에서 명명 된 공식을 만드는 방법을 참조하십시오.

3. 데이터 유효성 검사를 설정합니다

확인할 첫 번째 셀을 선택하고 (당사의 경우 A5) 이름을 지정하여 명명 된 공식을 기반으로 사용자 정의 데이터 검증 규칙을 작성하십시오. 이를 위해 다음을 수행하십시오.

데이터 탭> 데이터 유효성 검사를 클릭하십시오.
허용 드롭 다운 목록에서 사용자 정의를 선택하십시오.
해당 상자에 아래 공식을 입력하십시오.
=Validate_SKU
빈 옵션 무시 옵션을 선택 해제하십시오. 그렇지 않으면 규칙이 작동하지 않습니다.

정규 표현식 (Regex)을 사용한 Excel 데이터 검증

선택적으로 셀에 잘못된 데이터를 입력 할 때 표시 할 사용자 정의 오류 메시지를 입력 할 수 있습니다.

정규 표현식 (Regex)을 사용한 Excel 데이터 검증

자세한 단계가 필요하다고 생각되면 여기에서 다음과 같습니다. Excel에서 사용자 정의 데이터 검증을 설정하는 방법.

4. 데이터 검증을 더 많은 셀에 복사하십시오

검증 설정을 더 많은 셀에 복사하려면 이것이 다음과 같습니다.

데이터 검증이있는 셀을 선택하고 CTRL C를 눌러 복사하십시오.
검증하려는 다른 셀을 선택하고 마우스 오른쪽 버튼을 클릭하고 스페셜 붙여 넣기를 클릭 한 다음 유효성 검사 옵션을 선택하십시오.
확인을 클릭하십시오.

자세한 내용은 데이터 검증을 복사하는 방법에 대해 확인할 수 있습니다.

이제 누군가가 검증 된 셀에 유효하지 않은 SKU를 입력하려고 시도 할 때마다 다음 경고 메시지가 나타납니다.

정규 표현식 (Regex)을 사용한 Excel 데이터 검증

Regex로 이메일 유효성 검사

이메일 검증을 수행하려면 이메일 주소와 일치하는 정규 표현식을 작성하는 것으로 시작합니다.

패턴 : ^[\ w \. \-] @[a-za-z0-9] [a-za-z0-9 \. \-]*[a-za-z0-9] \. [a-za-z] {2,24} $

구문에 대한 자세한 설명은 유효한 이메일 주소와 일치하려면 Regex를 참조하십시오.

이제 이미 친숙한 단계를 수행하여 검증 기준을 지정합니다.

B2에 위의 정규를 입력하십시오.
셀 A1을 선택하고 다음을 나타내는 Validate_Email 이라는 이름을 정의하십시오.
=RegExpMatch(Sheet1!A1, Sheet1!$B$2)
셀 B5의 경우 아래 공식을 사용하여 사용자 정의 데이터 검증을 적용하십시오. 빈 옵션을 무시하지 않아야합니다.
=Validate_Email

또한 사용자에게 유효한 이메일 주소를 입력하도록하는 사용자 정의 오류 메시지를 구성 할 수 있습니다.
규칙을 아래 셀에 복사하십시오.

검증 된 셀에 입력 한 이메일 주소가 Regex 패턴과 일치하지 않으면 다음 경고가 나타납니다.

정규 표현식 (Regex)을 사용한 Excel 데이터 검증

일반 표현식을 사용하여 암호 검증

비밀번호 검증에 Regex를 사용할 때 가장 먼저 결정해야 할 것은 정규 표현식이 확인 해야하는 것입니다. 다음은 올바른 길을 가질 수있는 몇 가지 예입니다.

비밀번호의 길이는 6 자 이상이어야하며 문자 (대문자 또는 소문자)와 숫자 만 포함 할 수 있습니다.

패턴 : ^[a-za-z0-9] {6,} $

비밀번호의 길이는 최소 6 자이어야하며 하나 이상의 문자와 한 자리를 포함해야합니다.

패턴 : ^(? =.*[a-za-z]) (? =.*\ d) [a-za-z \ d] {6,} $

비밀번호의 길이는 최소 6 자이어야하며 하나 이상의 대문자 문자, 1 개의 소문자 및 1 자리가 포함되어야합니다.

패턴 : ^(? =.*[az]) (? =.*[az]) (? =.*\ d) [a-za-z \ d] {6,} $

비밀번호의 길이는 최소 6 자이어야하며 하나 이상의 문자, 하나의 자리 및 하나의 특수 문자가 포함되어야합니다.

패턴 : ^(? =.*[a-za-z]) (? =.*\ d) (? =.*[@$!%*#? & _-]) [a-za-z \ d@$!%*#? & _-] {6,} $

패턴이 설정되면 데이터 검증 설정으로 이동할 수 있습니다.

C2에 비밀번호 Regex를 입력하십시오.
셀 A1을 선택하고 validate_password 라는 이름의 지정된 공식을 만듭니다.
=RegExpMatch(Sheet1!A1, Sheet1!$C$2)
셀 C5의 경우 아래 공식으로 사용자 정의 검증 규칙을 작성하십시오. 빈 점검 상자를 선택하지 않도록하십시오.
=Validate_Password
규칙을 원하는만큼의 셀에 복사하십시오.

이제 목록에 새 비밀번호를 안전하게 추가 할 수 있습니다. 입력 문자열이 Regex와 일치하지 않으면 다음 경고는 어떤 종류의 값이 허용되는지 상기시킵니다.

정규 표현식 (Regex)을 사용한 Excel 데이터 검증

REGEX 데이터 검증이 작동하지 않습니다

REGEX 데이터 유효성 검사가 Excel에서 작동하지 않는 경우 다음과 같은 이유 중 하나 때문일 수 있습니다.

regexpmatch 함수 누락

데이터 유효성 검사를 적용하기 전에 통합 문서에 Regexpmatch 함수의 코드를 삽입하십시오.

잘못된 정규 표현

REGEX가 예상대로 작동하는지 확인하려면 일부 셀에서 RegexPMatch 공식을 입력하고 결과를 검사 할 수 있습니다. 자세한 내용은 예제와 일치하는 Excel 정규 표현식을 참조하십시오.

정규 표현식을 분석하고 디버깅하려면 Regex101 또는 RegexR과 같은 무료 온라인 Regex 테스트 서비스를 사용할 수 있습니다.

잘못 지명 된 공식

데이터 유효성 검사 실패의 매우 일반적인 이유는 잘못된 셀을 언급하는 공식이라는 이름이 지정되어 있습니다. 모든 예에서, 우리는 a1을 참조하는 공식을 정의하는 것이 좋습니다.

=RegExpMatch(A1, regex)

이름을 정의 할 때 셀 A1이 활성화 되고 상대 참조 ($ 부호없이)가 사용되는 경우에만 작동합니다.

아이디어는 공식 (A1)에 지정된 상대 참조가 검증 된 셀의 상대 위치에 따라 자동으로 변경 될 것입니다. 다시 말해, 셀 A1은 편의성과 일관성을 위해 선택됩니다. 실제로 셀 B1을 선택하고 B1을 참조하고 셀 C1을 선택하고 C1 등을 참조하십시오. 핵심은 참조 된 셀이 활성 셀 이어야한다는 것입니다.

이름이 지정된 공식이 올바른지 확인하려면 워크 시트의 셀을 선택하고 이름 관리자를 열고 공식의 셀을 확인하십시오. 그것이 현재 선택된 셀을 지칭하는 경우, 공식이 옳습니다. 그렇지 않으면 첫 번째 인수에서 참조를 변경해야합니다.

아래의 스크린 샷에서 셀 A7이 선택되며, 이는 첫 번째 인수에 이름 지정된 공식이 A7을 가져야합니다. 두 번째 인수 ($ a $ 2)는 Regex를 나타냅니다.이 참조는 일정하게 유지되어야하므로 $ 부호로 잠겨 있습니다.

정규 표현식 (Regex)을 사용한 Excel 데이터 검증