엑셀에서 열 전체의 중복 항목 개수를 파악하는 것은 여러 가지 이유로 매우 유용합니다. 먼저 데이터에 중복이 있는지 검토할 때 필요하고, 중복 자체가 의도된 데이터라면 각 항목이 몇 번 나타나는지 정확히 알아야 할 때도 있기 때문입니다. 엑셀에서 열의 중복 값을 세어야 한다면 이 글을 끝까지 읽어보세요.
엑셀에서 열의 중복 값 개수 구하기
이 작업은 COUNTIF 함수 하나로 간단하게 처리할 수 있습니다. 중복 값의 빈도(전체 반복 횟수)를 구할 수도 있고, 각 항목이 몇 번째로 나타나는지(발생 순서)를 구할 수도 있습니다.
1. 열 전체에서 중복 값의 빈도 계산하기
선택한 목록에서 특정 값이 몇 번 반복되는지만 알고 싶은 상황을 가정해 보겠습니다. 각 값의 단순 반복 횟수를 확인하려면 아래 수식을 사용하세요.
=COUNTIF($<값 목록의 첫 번째 셀이 있는 열>$<값 목록의 첫 번째 셀이 있는 행>:$<값 목록의 첫 번째 셀이 있는 열>$<값 목록의 마지막 셀이 있는 행>,<값 목록의 첫 번째 셀 주소>)
각 인수의 의미는 다음과 같습니다.
- <값 목록의 첫 번째 셀이 있는 열>: 값 목록이 입력된 열입니다.
- <값 목록의 첫 번째 셀이 있는 행>: 값이 시작되는 첫 번째 셀의 행 번호입니다.
- <값 목록의 마지막 셀이 있는 행>: 값이 끝나는 마지막 셀의 행 번호입니다.
- <값 목록의 첫 번째 셀 주소>: 열에서 값이 입력된 첫 번째 셀의 주소입니다.
예시: B열의 B3부터 B11까지 항목 목록이 있고, C열(C3~C11)에 각 값의 중복 횟수를 표시하고 싶다면 수식은 다음과 같습니다.
=COUNTIF($B$3:$B$11,B3)
이 수식을 C3 셀에 입력한 뒤, C3 셀 바깥쪽을 한 번 클릭했다가 다시 C3 셀을 클릭하세요. 그러면 채우기(Fill) 기능이 활성화됩니다. 선택된 셀의 오른쪽 아래 모서리에 있는 작은 점(채우기 핸들)을 누른 상태로 C11까지 끌어내리면 수식이 함께 복사됩니다.
2. 중복 항목의 발생 순서 계산하기
앞선 예제와 달리, 같은 항목이 반복해서 나타날 때마다 개수가 1, 2, 3…처럼 순차적으로 증가하길 원한다면 다음 수식을 사용해 보세요.
=COUNTIF($<값 목록의 첫 번째 셀이 있는 열>$<값 목록의 첫 번째 셀이 있는 행>:<값 목록의 첫 번째 셀 주소>,<값 목록의 첫 번째 셀 주소>)
각 인수의 의미는 다음과 같습니다.
- <값 목록의 첫 번째 셀이 있는 열>: 값 목록이 입력된 열입니다.
- <값 목록의 첫 번째 셀이 있는 행>: 값이 시작되는 첫 번째 셀의 행 번호입니다.
- <값 목록의 첫 번째 셀 주소>: 열에서 값이 입력된 첫 번째 셀의 주소입니다.
예시: 앞의 예제를 그대로 사용해 B3~B11 범위의 항목에 대해 C열에 발생 횟수를 구한다면 수식은 다음과 같습니다.
=COUNTIF($B$3:$B3,B3)
이 수식을 C3 셀에 입력한 후, 채우기 기능으로 수식을 C11까지 아래로 끌어내립니다.
앞선 방식과 달리, 각 항목의 첫 번째 등장에는 1, 두 번째 등장에는 2처럼 순번이 차례로 매겨지는 것을 확인할 수 있습니다. 이 방법은 범위의 끝 부분($B$3:$B3)을 고정하지 않기 때문에 수식이 내려갈수록 참조 범위가 함께 확장되며 발생 순서가 계산되는 원리입니다.
함께 읽으면 좋은 글: 엑셀에서 셀 병합 및 병합 해제하는 방법
이 글이 도움이 되기를 바랍니다!