[엑셀] 데이터 유효성 검사 - 중복 데이터 입력 제한하기

[엑셀] 데이터 유효성 검사 - 중복 데이터 입력 제한하기

 

엑셀 문서 파일을 다른 사람과 공유해서 데이터를 관리할 때 중복 값이 입력되지 않도록 제한할 필요가 있는 경우가 있다. 

 

가령, 직원의 사내 인트라넷 계정 아이디를 등록할 때와 같은 경우가 있을 수 있다.

이러한 경우에 데이터 유효성 검사 기능을 활용하면 쉽게 관리할 수 있다.

데이터 유효성 검사에 대해서는 아래 포스팅을 확인해 주기 바란다.

 

[엑셀] 데이터 유효성 검사 - 한글/영어 자동 변환하기

---

중복 데이터 입력 제한하기

아래와 같은 명단 시트가 있고, [B] 열이 '아이디'이며 해당 열에는 중복된 값이 입력되면 안 될 것이다.

[엑셀] 중복 데이터 입력 제한하기 - 예제 화면

여기서 [데이터 유효성 검사] 기능을 활용해 중복 데이터 입력을 제한하는 방법은 다음과 같다.

[엑셀] 중복 데이터 입력 제한하기 - 예제 화면

■ 범위 선택하기

위 화면 ①번과 같이 [B]열 머리글을 클릭해서 [B]열 모두를 선택한다.

이때, 중복 데이터 입력을 제한할 범위가 열 전체가 아닌, 일부일 경우 해당하는 범위만 선택하면 된다.

 

■  '데이터 유효성' 대화 상자 열기

위 화면 ②번과 같이 '데이터 유효성' 대화 상자를 실행한다. 

'데이터 유효성' 대화 상자를 실행하는 방법은 아래 화면과 같이 [데이터] 탭 (아래 ①번)에서 [데이터 도구] 그룹의 [데이터 유효성 검사] 메뉴(아래 ②번)를 클릭하면 된다. 

[엑셀] 데이터 유효성 검사 메뉴

■  '데이터 유효성' 설정 하기

'데이터 유효성' 대화 상자에서 다음과 같이 설정한다.

[엑셀] 중복 데이터 입력 제한하기 - 예제 화면

①​ [설정] 탭을 클릭한다. (이미 [설정] 탭이라면 생략)

② '제한 대상' 옵션을 '사용자 지정'으로 설정한다.

③ '수식' 입력란이 나타나는데 아래의 수식을 입력한다.

중복 데이터 입력 제한 수식
=COUNTIF($B:$B, B1)<=1 

 

COUNTIF 함수는 AVERAGE 함수와 같은 통계 함수 중 하나이며, 기준을 충족하는 셀의 개수를 계산할 때 사용한다.

COUNTIF 함수 구문은 다음과 같다.

COUNTIF 함수 구문
COUNTIF(찾으려는 위치, 찾으려는 항목) 

COUNTIF($B:$B, B1) 수식에서 '찾으려는 위치'는 $B:$B라고 했는데, 이는 [B]열 전체를 의미한다. 

'찾으려는 항목'으로 B1이라고 했는데 이는 '현재 활성화된 셀'을 의미한다.

 

이 활성화된 셀의 주소는 수식 입력줄 오른쪽에 있는 이름 상자에서 확인할 수 있다.(위 화면의 파란색 표시)

 

가령, 아래와 같이 특정 범위를 선택했다면 '현재 활성화된 셀'을 수식 입력줄 오른쪽의 이름 상자에서 확인할 수 있는 것이다.

[엑셀] 선택 범위에서 '현재 활성화된 셀' 주소 확인하기

다시 수식 설명으로 돌아오면

=COUNTIF($B:$B, B1)<=1

이 수식은 [B]열 전체($B:$B)에서 '현재 활성화된 셀'(B1)과 값이 같은 셀의 개수가 1개 이하일 때 TRUE(참)을 반환하는 것이다.

 

[설정] 탭에서 '수식'까지 입력했다면 다음으로 아래 화면과 같이 이어서 진행한다.

[엑셀] 중복 데이터 입력 제한하기 - 예제 화면

④ [오류 메시지] 탭을 클릭한다.

⑤ '제목'에 적당히 입력하자. 나는 '사용중인 아이디'라고 입력했다.

⑥ '오류 메시지' 항목에 적당한 내용으로 메시지를 입력하면 된다.

⑦ [확인] 버튼을 클릭해 '데이터 유효성' 대화 상자를 닫는다.

---

①번~⑦번까지 설정을 마쳤다면 이제 제대로 동작하는지 확인해 보자.

[엑셀] 중복 데이터 입력 제한하기 - 예제 화면

위 화면을 보면 [B5] 셀에서 [B2]와 같은 아이디를 입력한 후 [Enter]를 누르면 아래와 같이 오류 메시지가 나타나는 것을 알 수 있다. 

[엑셀] 중복 데이터 입력 제한하기 - 오류 메시지

이렇게 데이터 유효성 검사를 활용해 중복된 값이 입력되지 않도록 하는 방법을 살펴봤는데, 실무에서도 활용할 일이 종종 있을 것이라 생각한다.

 

- 끝 -

 

상기 상품 배너는 쿠팡 파트너스 활동의 일환으로, 이에 따른 일정액의 수수료를 제공받습니다.