작성일자 : 2024-09-02
Ver 0.1.1
0. 배경
Excel을 하다보면 컬럼에 있는 고유 값을 세고 싶을때가 있다.
SQL에서는 'SELECT COUNT(DISTINCT <COL_NAME>) FROM TABLE' 을 하면 되지만,
Excel에서는 두가지 함수를 함께 사용하면 고유 값의 개수를 셀 수 있는데, 주인공은 바로 SUMPRODUCT와 COUNTIF 함수이다.
- SUMPRODUCT 함수는 배열 간의 요소별 곱셈을 수행한 후 그 결과를 모두 더하는 함수
- COUNTIF 함수는 선택된 범위에서 조건을 충족하는 셀의 개수를 세는 함수
이 함수는 고유값을 세는 데도 활용될 수 있다. 특히, 조건을 설정하여 특정 범위 내의 고유값의 개수를 구할 때 유용하다.
1. Syntax
두 함수를 활용하여 고유 값을 세는 Syntax는 아래와 같다.
= SUMPRODUCT(1/COUNTIF(범위 시작 : 범위 끝 , 범위 시작 : 범위 끝))
이해를 위해 아래 예제를 통해 원리와 결과에 대해서 살펴보도록 하자.
2. 예제 및 원리
2-1. 원리
고유값을 세기 위해 SUMPRODUCT 함수를 사용하면 다음과 같은 원리를 이용다:
- COUNTIF 함수를 이용하여 각 항목의 개수 계산:
COUNTIF함수를 사용하여 특정 범위에서 각 값이 몇 번 나타나는지 계산한다. - 고유 여부 확인: 고유값을 확인하기 위해
COUNTIF의 결과가 1인 경우를 찾는다. - SUMPRODUCT로 합계 계산: 위에서 계산한 고유값의 여부를
SUMPRODUCT를 통해 더해준다.
2-2. 예제
예제 데이터
다음과 같은 데이터가 있다고 가정한다. 이 데이터는 A1:A10 셀에 있습니다:
| A |
|---|
| 1 |
| 2 |
| 2 |
| 3 |
| 4 |
| 4 |
| 5 |
| 5 |
| 5 |
| 6 |
고유값 개수를 세는 공식
이 데이터에서 고유값(중복 없이 한 번만 나타나는 값)의 개수를 세기 위해 다음과 같은 공식을 사용할 수 있다:
= SUMPRODUCT(1/COUNTIF(A1:A10, A1:A10))이 공식을 설명하면:
COUNTIF(A1:A10, A1:A10): 이 부분은 범위 A1:A10에서 각 셀의 값을 기준으로 몇 번 나타나는지를 계산하여 배열을 만든다. 예를 들어,A1:A10에 있는 값이 각각 몇 번 등장하는지를 세어서[1, 2, 2, 1, 2, 2, 3, 3, 3, 1]과 같은 배열을 생성한다.1/COUNTIF(A1:A10, A1:A10): 위의 배열의 각 요소에 대해 1을 나눈다. 결과적으로[1, 0.5, 0.5, 1, 0.5, 0.5, 0.333, 0.333, 0.333, 1]와 같은 배열이 생성된다. 각 값은 고유값일 때는 1, 중복값일 때는 그 중복 수의 역수가 된다.SUMPRODUCT: 이 배열을 모두 더한다. 고유값은 1로 더해지고, 중복값은 그 역수가 더해져서 고유값의 개수를 정확하게 계산할 수 있다.
결과
위 공식을 실행하면 고유값의 개수는 6이 된다(1, 2, 3, 4, 5, 6).
3. 참고
위 방법은 일반적으로 숫자, 문자 데이터에 모두 적용될 수 있으며, 고유값을 찾는 데 유용하다.
다만 이 공식은 정밀도에 의해 일부 경우 오차가 발생할 수 있으므로, 데이터에 따라 ROUND 함수를 활용하여 처리할 수도 있다.
