Excel 시트의 열에 있는 목록에서 고유 값(unique)과 서로 다른 값(distinct)의 개수를 세는 작업은 다양한 상황에서 유용하게 활용됩니다. 예를 들어 특정 종목(또는 여러 종목)에서 우승한 선수 수를 계산하거나, 고객에게 판매된 고유 품목 수를 집계해야 할 때 이러한 집계 방법이 큰 도움이 됩니다.
Excel에서 열의 고유 값 개수 계산하기
배열 수식으로 열 목록의 고유 값 개수 세기
배열 수식을 사용해 열 목록에서 고유 값(정확히 한 번만 나타나는 값)의 개수를 세는 수식 구문은 다음과 같습니다.
=SUM(IF(COUNTIF(<개수를 셀 첫 번째 셀>:<개수를 셀 마지막 셀>,<개수를 셀 첫 번째 셀>:<개수를 셀 마지막 셀>)=1,1,0))
각 인수의 의미는 다음과 같습니다.
- <개수를 셀 첫 번째 셀>: 개수를 세기 시작할 열 범위의 첫 번째 셀입니다.
- <개수를 셀 마지막 셀>: 개수를 셀 열 범위의 마지막 셀입니다.
예를 들어, 스포츠 종목과 각 종목에서 우승한 선수 목록이 있고 우승 선수 이름이 B3부터 B12까지 입력되어 있다면, 고유 값 개수를 구하는 수식은 다음과 같습니다.
=SUM(IF(COUNTIF(B3:B12,B3:B12)=1,1,0))
이 수식을 고유 값 개수가 필요한 셀(예: D3)에 입력한 후 CTRL+SHIFT+ENTER를 누릅니다. 그러면 배열 수식이 활성화되며, 이후 셀 바깥쪽 아무 곳이나 클릭하면 원하는 고유 값 개수를 확인할 수 있습니다.
배열 수식으로 열 목록의 서로 다른 값(distinct) 개수 세기
배열 수식을 사용해 열 목록에서 서로 다른 값(중복 여부와 관계없이 종류별로 하나씩만 세는 값)의 개수를 구하는 수식 구문은 다음과 같습니다.
=SUM(IF(<개수를 셀 첫 번째 셀>:<개수를 셀 마지막 셀><>"",1/COUNTIF(<개수를 셀 첫 번째 셀>:<개수를 셀 마지막 셀>,<개수를 셀 첫 번째 셀>:<개수를 셀 마지막 셀>), 0))
각 인수의 의미는 다음과 같습니다.
- <개수를 셀 첫 번째 셀>: 개수를 세기 시작할 열 범위의 첫 번째 셀입니다.
- <개수를 셀 마지막 셀>: 개수를 셀 열 범위의 마지막 셀입니다.
앞선 예시처럼 우승 선수 이름이 B3부터 B12까지 입력되어 있다면, 서로 다른 값의 개수를 구하는 수식은 다음과 같습니다.
=SUM(IF(B3:B12<>"",1/COUNTIF(B3:B12, B3:B12), 0))
이 수식을 서로 다른 값 개수가 필요한 셀(예: E3)에 입력한 후 CTRL+SHIFT+ENTER를 누르면 배열 수식이 활성화됩니다.
이제 셀 바깥쪽 아무 곳이나 클릭하면 원하는 서로 다른 값의 개수를 확인할 수 있습니다.
참고: Excel 365 또는 Excel 2021 사용자라면
최신 버전의 Excel을 사용하고 있다면 복잡한 배열 수식 대신 UNIQUE 함수를 활용하는 것이 더 간편합니다. 서로 다른 값의 개수는 =COUNTA(UNIQUE(B3:B12)), 정확히 한 번만 나타나는 고유 값의 개수는 =COUNTA(UNIQUE(B3:B12,,TRUE))로 손쉽게 구할 수 있으며, CTRL+SHIFT+ENTER 없이 Enter 키만 눌러도 됩니다.
이 글이 도움이 되기를 바랍니다!