엑셀 고유값 개수 세기: UNIQUE와 COUNTIF 수식

=COUNTA(UNIQUE(A2:A9))는 A2:A9에 서로 다른 값이 몇 개 있는지 셉니다. UNIQUE가 각 값을 한 번씩 반환하고 COUNTA가 그 목록을 셉니다. Excel 2021이나 Microsoft 365가 필요하며, 이전 버전은 아래에서 다룹니다.

수식초기화

D2fx

=COUNTA(UNIQUE(A2:A9))

	A	B	C	D
1	Customer		Unique list	Count
2	Ana		Ana	5
3	Ben		Ben
4	Ana		Cara
5	Cara		Dan
6	Ben		Eva
7	Dan
8	Ana
9	Eva

셀을 클릭하면 수식이 보입니다. 숫자나 수식을 바꾸면 시트가 다시 계산됩니다.

주문 여덟 개가 고객 다섯 명에게서 들어왔습니다. C2는 UNIQUE로 이름 목록을 펼쳐서 무엇을 세는지 보여 주고, D2는 시트에 목록이 없어도 그것을 셉니다. A9를 Ana로 바꾸면 개수가 4로 줄고, 새 이름을 입력하면 늘어납니다.

UNIQUE는 대소문자를 무시하므로 Ana와 ana는 한 고객으로 셉니다.

이전 엑셀에서 고유값 개수 세기

Excel 2019 이하에는 UNIQUE가 없습니다. 전통적인 수식은 다음과 같습니다:

=SUMPRODUCT(1/COUNTIF(A2:A9,A2:A9))

범위 전체를 조건으로 준 COUNTIF는 행마다 그 행의 값이 몇 번 나오는지 반환합니다. 세 번 나오는 이름은 자기 행마다 3을 받으므로 1/3이 세 번 더해져 그 이름은 정확히 1이 됩니다. B열은 행별 개수를, C열은 그 분수를 보여 줍니다.

수식초기화

E2fx

=SUMPRODUCT(1/COUNTIF(A2:A9,A2:A9))

	A	B	C	D	E
1	Customer	Times	1/Times		Count
2	Ana	3	0.33		5
3	Ben	2	0.50		5.00
4	Ana	3	0.33
5	Cara	1	1.00
6	Ben	2	0.50
7	Dan	1	1.00
8	Ana	3	0.33
9	Eva	1	1.00

셀을 클릭하면 수식이 보입니다. 숫자나 수식을 바꾸면 시트가 다시 계산됩니다.

Ana의 세 행은 각각 0.33을, Ben의 두 행은 각각 0.50을, 한 번만 나오는 세 이름은 각각 1을 더합니다. 합계는 5로, 도우미 열의 SUM과 같습니다. 행이 수만 개면 이 수식은 느립니다. COUNTIF가 행마다 범위 전체를 한 번씩 훑기 때문입니다. UNIQUE에는 이런 부담이 없습니다.

고유값과 한 번만 나오는 값

"고유"라는 말은 서로 다른 두 개수에 쓰입니다. 위에서 센 것은 서로 다른 값, 즉 모든 이름을 한 번씩 센 것입니다. 다른 하나는 정확히 한 번 나오는 값, 예를 들어 한 번만 주문한 고객을 셉니다. UNIQUE는 세 번째 인수 exactly_once를 TRUE로 지정해 이것을 처리합니다.

수식초기화

D3fx

=COUNTA(UNIQUE(A2:A9,,TRUE))

	A	B	C	D
1	Customer		Count	Result
2	Ana		Distinct	5
3	Ben		Exactly once	3
4	Ana		Exactly once, older Excel	3
5	Cara
6	Ben
7	Dan
8	Ana
9	Eva

셀을 클릭하면 수식이 보입니다. 숫자나 수식을 바꾸면 시트가 다시 계산됩니다.

서로 다른 고객은 다섯 명이지만, 한 번만 주문한 고객은 Cara, Dan, Eva 세 명입니다. 이전 엑셀용 수식은 COUNTIF가 정확히 1인 행을 셉니다. 모든 값이 반복되면 exactly_once를 쓴 UNIQUE는 #CALC!를 반환하고 COUNTA는 그 오류를 1로 셉니다. SUMPRODUCT 방식은 0을 냅니다.

조건을 걸어 고유값 개수 세기

한 지역의 서로 다른 고객 수를 세려면 먼저 행을 걸러 내고 남은 것을 셉니다. FILTER가 North 행을 남기고 UNIQUE가 반복을 없앱니다.

수식초기화

E2fx

=COUNTA(UNIQUE(FILTER(A2:A9,B2:B9=D2)))

	A	B	C	D	E
1	Customer	Region		Region	Customers
2	Ana	North		North	3
3	Ben	South		South	3
4	Ana	North		North, older Excel	3
5	Cara	North
6	Ben	North
7	Dan	South
8	Ana	North
9	Eva	South

셀을 클릭하면 수식이 보입니다. 숫자나 수식을 바꾸면 시트가 다시 계산됩니다.

North에는 고객 세 명(Ana, Cara, Ben)의 주문 다섯 개가 있습니다. E3은 같은 방법으로 South를 셉니다. E4는 Excel 2019 이하용입니다. COUNTIFS가 고객과 지역 쌍을 각각 세고, 조건이 North의 분수만 남깁니다.

일치하는 행이 없으면 FILTER는 #CALC!를 반환하고 COUNTA는 그 오류를 값 하나로 셉니다. D2에 West를 입력하면 E2는 0이 아니라 1을 보여 줍니다. COUNTA는 오류를 반환하지 않으므로 수식을 IFERROR로 감싸도 소용없습니다. 대신 결과의 행 수를 세세요. 이 방법은 오류를 그대로 넘깁니다: =IFERROR(ROWS(UNIQUE(FILTER(A2:A9,B2:B9="West"))),0)는 0을 반환합니다.

빈 셀을 빼고 고유값 개수 세기

범위 안의 빈 셀은 "값" 하나가 더 늘어난 것이 됩니다. UNIQUE는 그것을 0으로 반환하고 COUNTA는 그 0을 셉니다. 그래서 Ana, 빈 셀, Ben, Ana, 빈 셀, Cara, Ben에 대해 엑셀은 다음 결과를 냅니다:

=COUNTA(UNIQUE(A2:A8))    4   three names plus the 0 for the empty cells

이전 수식에서는 빈 행 때문에 COUNTIF가 0을 반환하므로 1/0이 #DIV/0!이 됩니다. 먼저 빈 셀을 제거하세요:

수식초기화

D2fx

=COUNTA(UNIQUE(FILTER(A2:A8,A2:A8<>"")))

	A	B	C	D
1	Customer		Formula	Count
2	Ana		Skip blanks	3
3			Older Excel	3
4	Ben
5	Ana
6
7	Cara
8	Ben

셀을 클릭하면 수식이 보입니다. 숫자나 수식을 바꾸면 시트가 다시 계산됩니다.

두 수식 모두 고객 세 명을 셉니다. A2:A8<>""를 쓴 FILTER는 UNIQUE가 보기 전에 빈 셀을 빼 버립니다. 이전 수식에서는 A2:A8&""가 각 빈 셀을 빈 문자열로 바꿔 COUNTIF가 0을 반환하지 않게 하고, (A2:A8<>"")가 그 행들에 가중치 0을 줍니다.

연습: 상품 수 세기

수식초기화

E2fx

	A	B	C	D	E
1	Order	Product		Count	Result
2	1001	Apple		Products
3	1002	Pear
4	1003	Apple
5	1004	Plum
6	1005	Pear
7	1006	Apple
8	1007	Plum
9	1008	Fig

셀을 클릭하면 수식이 보입니다. 숫자나 수식을 바꾸면 시트가 다시 계산됩니다.

직접 해 보세요: B2:B9에 서로 다른 상품이 몇 개 나오는지 세세요. 수식은 E2에 쓰세요.

확인정답 보기

엑셀 버전별로 쓸 수식

피벗 테이블에서는 "Distinct Count"(고유 개수) 요약이 수식 없이 같은 일을 하지만, 피벗 테이블을 만들 때 "Add this data to the Data Model"(데이터 모델에 이 데이터 추가)을 선택한 경우에만 쓸 수 있습니다. 반복을 세는 대신 지우려면 중복 제거를 보세요.