엑셀로 데이터 입력, 편집, 분석할 때 중복값을 찾거나 제거하는 경우가 많습니다. 아예 처음부터 엑셀에서 동일한 값을 입력 못하게 설정해 놓는다면 좀 더 수월하게 데이터를 관리할 수 있습니다. 예전에 엑셀 중복값 찾기와 엑셀 중복값 제거에 대하여 포스팅한 것이 있는데 아래 링크를 참조하시기 바랍니다.
오늘은 똑같은 데이터를 처음부터 입력하지 못하게 설정하는 방법에 대하여 알아보겠습니다. 엑셀의 데이터 유효성 검사 기능을 이용하여 설정할 수 있습니다. 동명이인은 있을 수 있어도 전화번호 마저 같을 수는 없습니다. 이 경우를 방지하기 위한 작업을 해 보겠습니다.
1. 똑같은 데이터를 입력하지 못하게 할 셀들을 마우스 드래그하여 범위 지정한다.
2. 리본메뉴의 [데이터]탭에서 [데이터 유효성 검사]를 클릭한다.
3. 대화상자가 뜨면 [유효성 조건] > [제한대상]에서 [사용자 지정]을 선택합니다.
4. [수식] 입력칸에 countif 함수를 적습니다. =countif(
5. 중복 데이터를 입력하지 못하게 할 셀들의 범위를 마우스로 드래그하여 함수인수로 집어 넣습니다.
=countif(B2:B6
6. 그리고 키보드의 F4를 눌러서 이 범위를 절대참조로 만들어 줍니다.
=countif($B$2:$B$6,
7. 두번째 함수 인수로 중복 데이터를 입력하지 못하게 할 셀 중에서 첫번째 셀의 주소를 마우스로 콕 찍어 입력합니다.
=countif($B$2:$B$6, B2)
8. 마지막으로 =1 을 입력합니다. 이것은 지정한 범위안에서 특정한 값의 개수가 1개인것만 인정한다는 명령어 입니다. 만약 2개까지 인정하겠다면 <3을 입력해야 겠죠^^
=countif($B$2:$B$6, B2)=1
9. 부가적으로 [오류 메시지]탭을 선택하여 동일한 데이터를 입력했을 때 오류 메시지의 내용을 적어 봅시다. 아래 그림처럼 해보세요!
10. 이제 실제로 중복값 입력해서 설정이 제대로 되는지 확인해보겠습니다. 아래 표를 보면 "유비"가 동명이인 입니다. 데이터가 짧아 쉽게 눈에 들어 오지만 길다면 엑셀 중복값 찾기(조건부서식 이용)를 활용하시기 바랍니다. 동명이인 이더라도 전화번호가 같을 수는 없습니다.
11. 첫번째 유비의 전화번호로 010-1111-1111을 입력하고 두번째 유비의 전화번호로 동일한 전화번호를 입력해 봅시다. 아래와 같이 입력할 수 없고 오류메시지창이 뜹니다.
사전에 위와 같은 방법으로 동일한 데이터를 입력하지 못하게 설정해 놓으면, 데이터를 관리할때 엑셀 중복값 찾기 및 엑셀 중복값 제거가 필요 없게 됩니다.^^
이 방법을 처음 접하시는 분은 [사용자 지정]에서 입력한 함수식을 이해하기 어려울 수 있습니다. 간단하게 설명드리면 아래와 같습니다.
● countif는 조건을 만족하는 것이 몇개이냐 개수를 세어주는 함수입니다.
=countif(
● 개수를 셀 범위를 첫번째 인수로 집어 넣습니다. 그리고 이것을 키보드의 F4를 눌러 절대참조로 설정합니다.
=countif($B$2:$B$6,
● 두번째 인수로 범위의 첫번째 셀의 주소를 입력합니다. 이것은 그대로 상대참조로 내버려 둡니다.
=countif($B$2:$B$6, B2)
● 함수식을 닫고 =1을 입력합니다. 이것은 countif로 구한 개수가 1과 일치할 때 참이라는 명령어 입니다. 즉 동일한 데이터가 있으면 countif로 구한 값이 2일 것이고 이것은 1이 아니니까 거짓이 되므로 입력할 수 없게 되는 것 입니다.
=countif($B$2:$B$6, B2)=1
● 참고로 위의 표의 B2 ~ B6까지에 적용된 수식을 표시하면 아래와 같습니다.
B2셀의 수식 =countif(B2:B6, B2)=1
B3셀의 수식 =countif(B2:B6, B3)=1
B4셀의 수식 =countif(B2:B6, B4)=1
B5셀의 수식 =countif(B2:B6, B5)=1
B6셀의 수식 =countif(B2:B6, B6)=1
countif 함수의 첫번째 인수로 범위를 넣어는데 절대참조로 설정하여 각 셀에서 범위는 동일합니다. countif의 두번째 인수로 범위의 첫번째 셀의 주소를 넣었는데 절대참조 표시가 없는 이것은 상대참조이기 때문에 각 셀에서 상대적으로 셀주소가 바뀌어 집니다.