엑셀 중복 값 개수 세기 및 중복여부 표시하기 (COUNTIF 함수)

엑셀을 하다 보면 열값에 중복값이 있어 걸러내야 하거나 몇 개의 데이터가 중복인지 개수를 체크해야 할 때가 있습니다. 이럴 때 사용하는 함수인 COUNTIF 함수를 사용하면 편하게 작업이 가능합니다.

엑셀 열 중복값 개수 세기

아래 예시의 경우 A열에 ITEM_ID를 중복값이 있는지를 체크하는 예시입니다.

=COUNTIF( A2:A15, A2)

 

COUNTIF함수를 사용하여, A2:A15 까지의 범위를 설정하고 비교할 값을 열을 넣어 중복값의 개수를 출력하였습니다.

1은 고유하게 1개만 사용된 것이며, 2 이상은 중복으로 입력된 데이터를 나타냅니다.

 

값을 나타내야 하는 가장 상단의 B2열에 COUNTIF 함수를 사용한 수식을 입력 후, CTRL 버튼을 누르고 드래그하면 수식이 적용되어 값을 반환입니다.

 

중복값 있을 경우 '중복' 문구 반환하기

작업을 하다보면 중복 여부만 필요할 때가 있습니다. 물론 위의 방법을 사용하여 2 이상은 중복으로 체크할 수 있지만 데이터 양이 많을 경우에는 한눈에 보기 어려운 경우 중복 여부만 체크 할 수 있는 '중복' 문구만 나타내는 법을 알아보겠습니다.

 

예시를 들면 아래와 같은 결과를 반환하는 것입니다.

 

여기서 사용되는 함수는 COUNTIF와 IF 함수입니다.

=IF(COUNTIF($A$2:$A$15, A2)=1, "", "중복")

 

위 수식을 풀어보면 아래와 같습니다. 함수 및 수식에 아직 능숙하지 않다면 아래 순서로 이해하시면 도움이 됩니다.

1. 중복개수 결과에 사용했던 COUNT($A$2:$A$15, A2) 함수는 유지하고,
2. 수식 입력창에서 (1)번의 수식 앞에 IF 함수를 추가합니다.
3. IF를 입력하면 하단으로 관련 함수 리스트가 레이어 팝업이 나오는데 TAB 버튼을 누르면 선택됩니다.
4. (1)번의 수식 뒤에 =1을 넣고, 그 뒤로는 "참 결과", "거짓 결과"를 입력하면 됩니다.

수식을 풀이하면, (1)번의 수식으로 중복 개수를 우선 구했습니다. IF 조건문을 추가하여 (1)번의 수식값이 =1이라면, "참 결과" 나타내고, 거짓이라면 "거짓 결과"를 반환하라는 수식 구조입니다.

 

▫️결과 반환 시 유의할 점

위 수식을 보면 따옴표(")를 사용하여, 값을 작성하였습니다. 따옴표로 감싼 이유는 숫자가 아닌 문자(텍스트)를 출력을 하기 때문입니다. 따옴표를 작성하지 않으면 문자 출력 에러가 발생하게 됩니다.

 

만약 숫자를 결과값으로 참 일 때 1, 거짓일 때 0을 나타내야 한다면 IF(COUNTIF($A$2:$A$15, A2)=1, 1, 0)으로 따옴표를 제외하면 숫자로 출력이 됩니다.

 

"0"으로 입력해도 출력이 되지만, 눈으로 보이는 값은 동일한 숫자로 보이지만 숫자 1과 문자 1로 표시된것으로 데이터형식은 다르게 보셔야 합니다.

 

쉽게 설명하면, 0100192를 숫자로 출력하면 100192가 출력되며 문자로 출력시 0100192가 출력됩니다. 숫자는 앞자리에 0은 무의미하기에 제외되어 출력되고 문자는 텍스트 자체로 출력되는 것입니다.

 

• 엑셀 중복값중 고유한 값만 추려내기 (UNIQUE 함수)
• 엑셀 IF 조건문으로 조건에 맞는 값 반환하기