엑셀 중복 데이터의 입력 제한하기

Question

고객을 관리하는 용도로 엑셀을 사용하고 있습니다.
신규 고객을 워크시트에 등록할 때 이전에 이미 입력되어 있는 고객이면 메시지로 알리고 입력하지 못하도록 하고 싶습니다.
어떤 방법을 사용해야 합니까?

---

Answer

데이터 유효성 검사를 사용하면 됩니다.
고객에 대한 정보인 경우 주민등록번호로 이미 입력한 고객인지 아닌지 판단할 수 있습니다.
이름은 동명이인이 있을 수 있으므로 적합하지 않습니다.

 

중복데이터.xlsx

0.01MB

 

Step by Step

1. 주민등록번호가 입력될 범위를 지정하고 [데이터]-[유효성검사] 메뉴를 선택합니다.
아래 그림에서는 [D3:D11]를 범위로 지정했습니다.

 

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

3. [D8] 셀에 이미 입력한 주민등록번호를 다시 입력하면 오류 메시지가 표시되고 입력할 수 없게 됩니다.

 

TIP. 데이터유효성 검사의 제한 대상

▨ [데이터 유효성] 대화 상자의 [제한 대상]은 [모든 값], [정수], [소수점], [목록], [날짜], [시간], [텍스트 길이], [사용자 지정] 중에서 선택할 수 있습니다.

▨ [모든 값]을 선택하면 데이터 유효성 검사를 취소하는 것으로 어떤 데이터든지 입력 가능하다는 것을 의미합니다.
[모든 값] 이외의 항목을 선택하면 제한 대상 아래에서 제한 방법과 제한 값 등을 설정할 수 있습니다.

▨ 만약 0부터 100 사이의 정수만 입력을 허용하려면 [제한 대상]`을 [정수]로 지정하고 [제한 방법]은 [다음 값의 사이에 있음], [최소값]은 0, [최대값]은 100으로 설정하면 됩니다.

 

TIP. COUNTIF 함수로 데이터 개수 구하기

형식 : COUNTIF(범위, 조건)

설명 : 지정한 범위에서 조건을 만족하는 셀의 개수를 구합니다.

 

수식 =COUNTIF($D$3:$D$11,D3)<2는 D3:D11 범위에서 D3셀과 같은 값을 가진 셀의 개수를 구한 다음 결과가 2보다 작은지 검사합니다.

결과가 2보다 작으면 아직 입력되어 있는 값이 아니기 때문에 입력을 허용합니다.

만약 결과가 2와 같으면 이미 입력한 값이 되어 입력하지 못하도록 오류 메시지를 표시합니다.

여기서 주의 할 점은 COUNTIF 함수의 범위는 절대주소형식 $D$3:$D$11 으로 지정하고 조건을 나타내는 셀의 D3은 상대 주소 형식을 사용한다는 것입니다. 유효성 검사에서 작성한 수식은 지정한 범위의 지정한 범위의 첫 번째 셀을 기준으로 적용됩니다.

다른 셀에서는 수식이 복사되는 것과 같으므로 COUNTIF 함수의 범위는 항상 주민등록번호가 입력될 고정된 범위를 사용하고, 조건 셀은 현재 입력되는 값을 사용해야 하므로 상대 주소 형식을 사용합니다.

 

https://blog.naver.com/jinsan1009

엑셀 더 많은 실무 활용 요기로