엑셀 이름, 전화번호, 휴대폰 번호의 유효성 검사 만들기

Question

업무상 양식을 하나 만들어 여러 지점에 보내면 지점별로 데이터를 입력하여 다시 저 한테 보내도록 되어 있습니다. 저는 지점에서 보내온 데이터로 여러 작업을 처리해야 합니다. 아무리 입력 형식을 설명하고 메모를 붙여 보내도 엉뚱함 형식으로 데이터를 입력해서 보내는 경우가 많이 발생해서 유효성 검사를 적극적으로 활용하는 편인데 이름, 전화 번호 등에는 유효성 검사를 설정하기가 쉽지 않습니다.

---

Answer

텍스트 길이에 대한 제한이나 특정 목록에서만 입력할 수 있도록 제한하는 것이 아니라 텍스트의 입력형태를 제한 하려면 유효성 검사에서 수식을 이용해야 합니다. 예를 들어 이름을 입력할때 성과 이름을 반드시 공백으로 구분하여 입력하게 하려면 입력되는 값의 두 번째 글자가 공백이어야 한다는 수식으로 유효성 검사를 설정합니다.

 

유효성검사.xlsx

0.01MB

Step by Step

1. 아래 그림에서 (C4:C8) 영역에 이름을 입력할 때 성과 이름을 공백으로 구분하도록 제한하려고 합니다. (C4:C8)을 범위로 지정하고 [데이터]-[유효성 검사] 메뉴를 선택합니다.

2. [데이터 유효성] 대화 상자의 [설정] 탭에서 [제한 대상] 을 [ 사용자 지정]으로 선택합니다. [수식] 입력 상자에 =MID(C4,2,1)=" "을 입력하고 [확인] 단추를 클릭합니다.

MID(텍스트,수1,수2 함수는 텍스트의 수1 위치에서 수2만큼 문자를 추출합니다.

즉 C4의 2번째 부터 1글자가 공백" " 이어야 한다는 수식이 됩니다. 유효성 검사에서 수식을 작성할 때는 선택범위의 첫 번째 셀 C4를 기준으로 작성합니다.

 

3. 전화번호는 352-0020 형식으로 국번과 번호 사이를 하이픈 - 으로 구분하여 입력하도록 할 것입니다.

국번은 3자리 또는 4자리로 입력될 것이고 번호는 항상 4자리라는 것을 염두에 두고 수식을 만들면 됩니다. [D4:D8]을 범위로 지정하고 [데이터 ]-[유효성 검사] 메뉴를 선택한 다음 [사용자 지정]을 [제한 대상]으로 선택하고 [수식]을 다음과 같이 입력하면 됩니다.

LEFT(RIGHT((D4,5),1)="-"

LEFT(텍스트,1)은 텍스트의 왼쪽 1글자를 추출합니다. 텍스트 자리에 RIGHT(D4,5)를 입력했으므로 [D4] 셀의 오른쪽으로 5글자를 추출한 다음 추출된 5글자에서 왼쪽 1글자만 추출한다는 뜻이 됩니다. 이것이 "-"과 같아야 합니다. 즉 입력한 전화번호의 오른쪽에서 5번째 글자가 하이픈 - 이어야 한다는 의미가 됩니다.

 

4. 휴대폰 번호는 조금 더 복잡해 집니다. 생각 할 수 있는 첫번째 조건은 처음에 01로 시작된다는 것 두번쨰 조건은 4번째 글자는 항상 하이픈 - 이라는 것

세 번쨰 조건은 오른쪽에서 5번째 글자가 항상 하이픈 - 이라는 것입니다.

예를 들어 010-7764-0020 형식으로 휴대폰 번호가 입력될 것입니다. [E4:E8] 범위를 지정하고 [데이터]-[유효성 검사] 메뉴를 선택합니다. [제한 대상]을 [사용자 지정]으로 선택하고 다음과 같이 수식을 입력합니다.

=AND(LEFT(E4,2)="01",MID(E4,4,1)="-",LEFT(RIGHT(E4,5),1="-"

- AND(조건1,조건2,조건3...) 함수는 지정한 조건이 모두 참 일때 결과가 참이 됩니다.

하나라도 거짓인 조건이 있으면 결과는 거짓이 됩니다. AND 함수를 사용하여 세 개의 조건을 검사하는 형식의 수식입니다.

- LEFT(E4,2)="01" : E4 셀의 왼쪽에서 두 글자 즉 시작하는 두 글자가 01인지 검사합니다.

-MID(E4,4,1)="-" E4셀의 4번째부터 한 글자가 하이픈 - 인지 검사합니다.

-LEFT(RIGHT(E4,5),1)="-" E4 셀의 오른쪽 5글자 중 첫 번째 글자 즉 오른쪽에서 5번째 글자가 하이픈 - 인지 검사합니다.