엑셀에서 중복 값 입력 안되도록 하기(데이터 유효성 검사)

시리얼번호(일련번호)등 고유한 번호를 생성할 경우, 대부분 규칙이 있기 때문에 특별한 문제는 없습니다. 하지만 예외적인 상황은 항상 발생하기 마련이죠. 중간에 삭제되거나, 추가되거나 하는 사항이 생길 경우, 시리얼 번호가 중복되는 경우가 발생될 수 있는데요. 이럴 때 조건부 서식의 "중복 값" 항목을 이용해, 셀 색상을 변경할 수도 있지만, 입력이 안되도록 "데이터 유효성 검사"를 이용해 락을 걸 수도 있습니다.

 

이번엔 데이터 유효성을 이용한 중복 값 입력 제한을 걸어보도록 하겠습니다.

 

가장 먼저 중복 값 제한을 걸고자 하는 영역을 드래그로 선택합니다. 선택되지 않은 영역은 제한이 걸리지 않으니 참고 바랍니다.

메뉴의 데이터 유효성 검사로 이동 후 제한 대상을 사용자 지정으로 수정합니다. 사용자 지정이 되면, 아래 수식 밑의 회색 칸이 흰색으로 활성화되는데, 그곳에 아래의 수식을 기입합니다. 

 

예제 기준으로 =COUNTIF($C$3:C3, C3) = 1 를 입력합니다.

 

 

 

$C$3:C3 항목을 보면 앞자리는 절대주소($)으로 C열과 3행을 고정시켰습니다. 고정된 값은 다른 영역에 복사하거나, 드래그로 복사할 시, $C$3 그대로 복사되게 됩니다. 만약 C열과 3행 중 하나라도 절대주소가 설정이 안될 경우, 문제가 발생할 수 있습니다. 

 

그리고 두번째 C3은 상대주소로 기입했는데요. 선택된 영역만큼 확장되도록 하기 위해, 고정되지 않은 주소 값으로 기입했습니다. 아래 예제를 보면 C12까지가 선택되게 되어있는데요. 만약 중간에 셀을 여러 개 추가해 C20까지 늘어났다고 할 경우, 조건식도 $C$3:C20 영역까지 늘어날 수 있습니다.

 

 

 

 

C12 셀을 선택 후 데이터 유효성의 수식 내용을 확인해보면, COUNTIF($C$3:C12, C12)로 변경된 것을 확인할 수 있습니다. 이 결과는 위에서도 언급 드렸듯, C3:C12 사이에 줄을 삽입해 늘릴 경우, 늘린 만큼의 영역까지 확장되게 됩니다.

 

C3은 찾고자 하는 조건입니다. 예로 들면 COUNTIF($C$3:C10, C10)이 되었다고 가정할 때, C3~C10 셀에 C10에 입력된 값이 1개라도 있다면 1 이상을 표시하게 되고, 2개 이상인 경우는 중복으로 판단할 수 있기 때문에, 위와 같은 식을 사용하게 됩니다.

 

=COUNTIF(2) = 1 "만약" 동일한 값이 2개가 존재할 경우 2 = 1 이란 조건식에서 거짓이 되기 때문에, 셀엔 락이 걸리게 됩니다.