지금까지 엑셀에서 중복값을 제외한 고유값의 개수를 구하기 위해 피벗테이블을 사용하셨나요? 피벗테이블을 사용하는 경우에는 간편하지만 어느 정도의 편집이 필요할 수 있기 때문에 약간의 귀차니즘이 발생할 수 있습니다. 그렇다 보니 피벗 테이블을 활용하지 않고 함수만 이용해서 중복값을 제외한 고유값을 구하는 과정에 대해 포스팅을 해보도록 하겠습니다. 이 과정이 배열함수를 사용하지만 훨씬 쉬운 방법이니 잘 활용하시길 바랍니다.
1. SUMPRODUCT 함수 + COUNTIF 함수를 사용해서 고유값 개수 구하기
함수를 구성하는 방법부터 설명하겠습니다. 함수가 나오는 과정에 대해서는 뒤에 설명하도록 하겠으니 함수 구성만 빠르게 확인하고 싶으신 분들은 아래만 참고하여 주세요.
먼저, 아래 예시에서 중복값을 제외한 고유값의 개수를 구해보도록 하겠습니다.
육안으로 식별하여도 A,B,C,D 라는 4개의 고유값이 확인이 됩니다. 하지만 데이터가 많을 경우 고유값을 찾기가 힘들죠. 그럴 때는 함수를 아래와 같이 구성해 보세요.
=SUMPRODUCT(1/COUNTIF(중복값이 있는 리스트 범위,중복값이 있는 리스트 범위))
=SUMPRODUCT(1/COUNTIF(B2:B11,B2:B11))
이 함수 구성으로 중복값을 제외한 고유값을 쉽게 계산해 낼 수 있습니다.
2. 함수에 대한 설명
위의 함수로 어떻게 중복값을 제외한 고유값을 계산해 낼 수 있을까요?
먼저, 위의 예시를 다시 가져와서 설명해 보겠습니다. 나열되어 있는 LIST 옆에 COUNTIF 함수를 사용해서 전체 LIST에서 각 셀의 값이 차지하는 개수를 먼저 변환합니다. 즉, 아래 사진과 같이 나열하는 것이죠.
그리고 이때 나온 COUNTIF 값의 역수를 계산합니다. 다시 설명하자면 1/COUNIF 함수로 재 구성하는 것입니다.
이제 대충 눈치를 채셨나요? COUNTIF 함수의 역수인 1/COUNIF 함수는 결론적으로는 각 중복된 값의 비율이라고 볼 수 있습니다.
예를 들어 앞의 D2,3,4 셀에서 나온 0.33의 경우, A의 중복값 중 A 하나가 가지는 비율이 0.33이라는 의미입니다. 이 0.33을 다 더하면 1이 되는 것이죠. 즉 D 열에서 나온 값을 모두 더하면 중복값을 제외한 고유값의 개수가 나오게 되는 것입니다.
그리고 이 모든 과정을 배열이라고 볼 수 있습니다. 그렇기 때문에 배열을 하는 과정에서는 단순히 SUM 함수가 아닌 배열 함수인 SUMPRODUCT 함수를 사용해야 됩니다. 그래서 위의 과정을 함수로 줄인다면 아래와 같이 다시 나타낼 수 있습니다.
=SUMPRODUCT(1/COUNTIF(중복값이 있는 리스트 범위, 중복값이 있는 리스트 범위)
=SUMPRODUCT(1/COUNTIF(B2:B11,B2:B11))
※여기서 잠깐
SUM 함수도 배열 수식으로 교체할 수 있습니다. SUM 함수로 나타내기 위해서는 SUMPRODUCT를 SUM으로 교체 뒤 CTRL+SHIFT+ENTER 키를 눌러 수식을 배열 수식으로 교체하여 주면 동일한 결과값을 도출할 수 있습니다.
'엑셀잡담' 카테고리의 다른 글
| 엑셀 EXCEL 색상별로 셀 합계 구하는 방법(+이름 정의 방법) (1) | 2023.06.28 |
|---|---|
| 엑셀 EXCEL 표 그림으로 붙여넣기 하는 방법(결재란 만들기) (1) | 2023.06.26 |
| 엑셀 EXCEL 천단위, 백만단위, 일억단위로 절사하는 방법 (1) | 2023.06.23 |
| 엑셀 EXCEL 드롭다운 다중 목록 만드는 방법 (7) | 2023.06.23 |
| 엑셀 EXCEL NETWORKDAY 함수로 근무일수 계산하기 (2) | 2023.06.22 |