Excel 에서 여러 기준에 따라 범위 내 고유 값 수 세기하는 방법은 무엇인가요？
실무에서는 단순히 값을 세는 것뿐 아니라 특정 조건을 충족하는 고유 항목의 개수를 파악해야 할 경우가 많습니다. 예를 들어 특정 영업 담당자가 판매한 서로 다른 제품의 수나 특정 기간 내에 접수된 고유 주문 건수를 알고 싶을 수 있습니다. Excel 에서 이러한 작업을 효율적으로 처리하려면 적절한 수식, PivotTable 와 같은 고급 기능， 또는 사용자 정의 VBA 솔루션에 익숙해져야 합니다。 본 문서에서는 하나 이상의 기준에 따라 범위 내 고유 값 수 세기하는 여러 실용적인 방법을 단계별 지침과 팁과 함께 소개합니다。
범위 내 고유 값 수 세기 PivotTable 사용하기(고유 개수， Excel 2013 이상)
VBA 코드 사용하기(범위 내 고유 값 수 세기， 복잡하거나 자동화된 경우용)
하나의 기준에 따라 범위 내 고유 값 수 세기
일반적인 사례를 살펴보겠습니다. Tom 이 판매한 서로 다른 제품의 수를 세고자 합니다。 이 방법은 단순한 데이터셋에서 단일 조건(예： 특정 인물의 판매 기록)을 기준으로 고유성을 평가할 때 적합합니다。 간단하지만 배열 수식을 신중히 사용해야 합니다。
이 시나리오에서는 다음 수식을 빈 셀(예： G2 셀)에 입력하세요：
=SUM(IF(「Tom」=$C$2:$C$20,1/(COUNTIFS($C$2:$C$20, 「Tom」, $A$2:$A$20, $A$2:$A$20)),0))
수식을 입력한 후Ctrl + Shift + Enter(Enter 만 누르지 말고)를 눌러 배열 수식으로 확인하세요。 수식 편집기에 중괄호가 나타나며 아래와 같이 결과가 즉시 표시됩니다：
참고:
- “Tom”은 결과를 필터링하는 데 사용할 조건입니다。 더 유연하게 사용하려면 “Tom”을 다른 셀 참조(예： $F$2)로 바꿀 수 있습니다。
- $C$2:$C$20 에는 평가할 영업 담당자 이름이 포함되어 있습니다。
- $A$2:$A$20 는 고유 개수를 계산하려는 제품 열입니다。
- 귀하의 데이터 범위가 변경되면 참조도 그에 따라 조정해야 합니다。
팁： Excel 365 또는 Excel 2019 이상을 사용 중이라면 더 간단한 수식을 위해UNIQUE및FILTER함수를 사용해 보세요。
#DIV/0! 오류가 발생하면 기준을 다시 확인하고 범위 길이가 동일한지 확인하세요。
두 날짜 기준에 따라 범위 내 고유 값 수 세기
특정 날짜 범위 내에서 고유 항목 수를 찾고자 할 때(예: 2016/9/1 부터 2016/9/30 사이에 판매된 고유 제품 수) 이 방법을 적용할 수 있습니다。 이는 월간， 분기별 또는 사용자 정의 날짜 범위과 같은 특정 기간 동안 데이터 추세를 분석할 때 특히 유용합니다。 다만 날짜 서식이 워크시트의 날짜 값과 일치해야 하므로 주의하세요。
결과를 표시할 빈 셀에 다음 수식을 입력하세요：
=SUM(IF($D$2:$D$20=DATE(2016,9,1)),1/COUNTIFS( $A$2:$A$20, $A$2:$A$20, $D$2:$D$20, 「=」&DATE(2016,9,1))),0)
수식 입력 후Ctrl + Shift + Enter를 눌러 배열 수식으로 실행하세요。 아래 스크린샷에서 결과를 확인할 수 있습니다：
참고:
- 2016,9,1및2016,9,30는 시작 및 종료 날짜 기준입니다。 필요에 따라 수정하거나 동적 날짜 필터를 위해 셀 참조를 사용할 수도 있습니다。
- $D$2:$D$20 에는 확인할 날짜 항목이 포함되어 있습니다。
- $A$2:$A$20 는 다시 고유하게 개수를 세고자 하는 항목 또는 제품 열입니다。
- 날짜가 텍스트 문자열이 아닌 유효한 Excel 날짜 형식으로 저장되었는지 확인하세요。 결과가 예상과 다르게 나타나면 날짜 서식과 범위를 다시 확인해 보세요。
팁： 지역별 날짜 서식 문제를 피하려면 DATE(년， 월， 일) 함수를 사용하세요。 동적 범위를 사용할 때는 명확성을 위해 이름이 지정된 범위를 고려해 보세요。
두 가지 기준에 따라 범위 내 고유 값 수 세기
Tom 이 9 월 동안 판매한 제품만 분석하고자 할 때， 이름과 날짜 범위을 결합하여 고유 개수를 계산할 수 있습니다。 이 시나리오는 기간 기반 성과 평가나 세분화된 분석에서 흔히 발생합니다。 기준이 늘어날수록 수식이 더 복잡해지고 데이터 정확성에 대한 주의가 더욱 중요해집니다。
아래 수식을 H2 와 같은 임의의 빈 셀에 입력하세요：
=SUM(IF((「Tom」=$C$2:$C$20)*($D$2:$D$20=DATE(2016,9,1))),1/COUNTIFS($C$2:$C$20, 「Tom」, $A$2:$A$20, $A$2:$A$20, $D$2:$D$20, 「=」&DATE(2016,9,1))),0)
수식 입력 후Ctrl + Shift + Enter를 눌러 확인하세요。 고유 개수가 즉시 표시되며， 다음 그림을 참고하세요：
참고사항：
- “Tom”은 이름 기준이고， “2016,9,1” 및 “2016,9,30”는 귀하의 날짜 범위 경계입니다。 필요에 따라 조정하거나 셀 참조를 사용하여 동적으로 설정할 수 있습니다。
- $C$2:$C$20 는 직원(또는 다른 첫 번째 기준) 열이고, $D$2:$D$20 는 날짜 열이며, $A$2:$A$20 에는 고유 개수를 세려는 항목이 포함되어 있습니다。
- 오류를 방지하려면 모든 범위의 길이가 동일해야 합니다。
Tom 이 판매하거나 남부 지역에서 판매된 고유 제품 수와 같이 “또는(or)” 조건을 사용하려면 다음 수식을 활용할 수 있습니다。 이는 검색 조건을 넓힐 수 있지만， 데이터가 두 기준을 모두 충족하면 결과가 중복될 수 있습니다：
=SUM(--(FREQUENCY(IF((「Tom」=$C$2:$C$20)+(「South」=$B$2:$B$20), COUNTIF($A$2:$A$20, "0))
Ctrl + Shift + Enter를 누르는 것을 잊지 마세요。 아래와 같은 결과가 표시됩니다：
팁： OR 조건을 적용할 때 동일한 레코드가 두 조건을 모두 만족하면 중복 계산될 수 있으므로 주의하세요。 대규모 데이터셋의 경우 성능에 영향을 줄 수 있습니다。
범위 내 고유 값 수 세기 세 가지 조건 기반
때로는 분석에 세 개 이상의 조건이 필요할 수 있습니다. 예를 들어, 9 월에 북부 지역에서만 톰이 판매한 고유 제품을 파악하는 경우입니다。 이는 보고서 작성이나 타깃 비즈니스 인사이트를 위한 다차원 데이터 분석에서 흔히 발생합니다。 이러한 복합 논리를 처리할 때는 참조 관리를 신중히 해야 합니다。
빈 셀(예： I2)에 이 배열 수식을 입력하세요：
=SUM(IF((「Tom」=$C$2:$C$20)*($D$2:$D$20=DATE(2016,9,1))*(「North」=$B$2:$B$20),1/COUNTIFS($C$2:$C$20, 「Tom」, $A$2:$A$20, $A$2:$A$20, $D$2:$D$20, 「=」&DATE(2016,9,1), $B$2:$B$20, "North")),0)
완료하려면Ctrl + Shift + Enter를 누르세요。 참고용 샘플 결과는 다음과 같습니다：
고급 조건의 경우 모든 범위가 일치하는지， 데이터 형식(예： 날짜 및 텍스트)이 올바른지 반드시 다시 확인하세요。 정렬 불일치는 오류나 잘못된 결과를 초래할 수 있습니다。
팁：
- 대규모 데이터셋에서 성능 문제가 발생하면 수식을 분할하거나 Excel 의PivotTable솔루션을 사용하는 것을 고려해 보세요。
- 모든 기준에 대해 이름이 지정된 범위나 셀 참조를 사용하면 가독성이 향상되고 수식 오류가 줄어듭니다。
- 자주 사용하는 경우 이러한 수식을 이름이 지정된 셀 참조나 사용자 정의 함수로 저장하는 것을 고려해 보세요。
범위 내 고유 값 수 세기 PivotTable 사용(고유 개수， Excel 2013+)
Excel 2013 이상 버전을 사용하는 경우, PivotTable 은 하나 이상의 조건에 대해 범위 내 고유 값 수 세기를 사용하는 것보다 더 직관적이고 수식 없는 대안을 제공합니다. 고유 개수 기능을 통해 대규모 데이터셋을 효율적으로 요약하고 필터링할 수 있어 동적 보고서 환경에 특히 적합합니다. 다만 이전 버전의 Excel 에서는 PivotTable 내에서 고유 개수 기능을 지원하지 않습니다。
이 방법 사용법：
- 데이터셋을 선택하고삽입>피벗테이블로 이동하세요。
- 피벗테이블 만들기 대화상자에서 피벗테이블을 배치할 위치를 선택하고， “이 데이터를 데이터 모델에 추가” 확인란을 선택한 후확인을 클릭하세요。
- 고유 개수를 세려는 필드(예： 제품)를 값 영역으로 끌어다 놓으세요。 기본적으로 “개수。。。”로 표시됩니다。
- 값 영역의 필드를 클릭하고값 필드 설정을 선택하세요。
- 팝업 대화상자에서 아래로 스크롤하여고유 개수를 선택하세요。 (이 옵션은 Excel 2013 이상에서만 사용 가능하며， 피벗테이블을 “이 데이터를 데이터 모델에 추가” 옵션을 활성화하여 만들었을 때 나타납니다。)
- 필터 또는 행/열 영역에 기준 필드(예： 영업 담당자， 지역， 날짜)를 추가하여 단일 또는 다중 조건을 적용하세요。
- 이제 피벗테이블에 선택한 기준으로 필터링된 고유 값 개수가 표시됩니다。
장점：시각적으로 매우 직관적이며 수식 수정 없이 필터를 쉽게 조정할 수 있고， 인터랙티브 보고서에 적합합니다。
단점：Excel 2010 이하 버전에서는 사용할 수 없으며， 새 데이터를 추가할 때 피벗테이블을 수동으로 새로 고쳐야 합니다。
실무 팁：동일 레코드 내에 의도하지 않은 중복이 없도록 항상 원본 데이터를 확인하세요。 고유 개수 옵션이 표시되지 않으면 피벗테이블을 다시 만들고 ‘이 데이터를 데이터 모델에 추가’ 옵션을 선택했는지 확인하세요。
범위 내 고유 값 수 세기 VBA 코드 사용(복잡하거나 자동화가 필요한 경우)
매우 큰 데이터셋을 처리하거나 분석을 자주 반복해야 할 때 다양한 조건에 따라 자동으로 범위 내 고유 값 수 세기해야 하는 경우가 있습니다. 이런 상황에는 VBA 매크로가 이상적이며, 설정 후 수동 개입 없이 다중 조건 필터링을 포함한 다양한 논리를 신속하게 처리할 수 있습니다. 그러나 VBA 는 일반 Excel 기능보다 고급 기술이므로 매크로 사용에 익숙하거나 지속적인 분석 요구가 있는 사용자에게 가장 적합합니다。
작업 단계：
- VBA 편집기를 열려면Alt + F11을 누르세요。 편집기에서삽입>모듈을 선택하여 새 모듈을 만드세요。
- 다음 VBA 코드를 모듈에 복사하여 붙여넣으세요：
Sub CountUniqueWithCriteria()
Dim DataRange As Range
Dim CriteriaRange As Range
Dim CriteriaValue As Variant
Dim Dict As Object
Dim i As Long
Dim UniqueCount As Long
Dim ResultCell As Range
Set Dict = CreateObject("Scripting.Dictionary")
' Prompt for range settings
Set DataRange = Application.InputBox("Select data range (items to count):", "KutoolsforExcel", Type:=8)
Set CriteriaRange = Application.InputBox("Select criteria range (e.g. Salesperson):", "KutoolsforExcel", Type:=8)
CriteriaValue = Application.InputBox("Enter criteria value:", "KutoolsforExcel", "", Type:=2)
Set ResultCell = Application.InputBox("Select cell for result output:", "KutoolsforExcel", Type:=8)
On Error Resume Next
For i = 1 To DataRange.Rows.Count
If CriteriaRange.Cells(i, 1).Value = CriteriaValue Then
If Not Dict.Exists(DataRange.Cells(i, 1).Value) Then
Dict.Add DataRange.Cells(i, 1).Value, 1
End If
End If
Next i
UniqueCount = Dict.Count
ResultCell.Value = UniqueCount
MsgBox "Unique count for '" & CriteriaValue & "': " & UniqueCount, vbInformation, "KutoolsforExcel"
End Sub- VBA 편집기를 닫고 워크시트로 돌아오세요。Alt + F8을 누르고，CountUniqueWithCriteria를 선택하여 매크로를 실행하세요。
- 데이터에 따라 범위와 기준을 지정하라는 입력 프롬프트에 따라 진행하세요。 결과는 선택한 셀과 메시지 상자에 모두 표시됩니다。
매개변수 설명 및 참고 사항：
- 현재 이 매크로는 하나의 기준에 대해 설정되어 있습니다。 여러 기준을 지원하려면 루프 내부의
If ... Then논리를 수정하세요。 - 매크로 실행 후 변경 내용은 취소할 수 없으므로 항상 매크로 실행 전에 통합 문서를 저장하세요。
- 실행 오류가 발생하면 Excel 설정에서 매크로를 활성화하세요。
- 이 방법은 수동 수식이 번거로운 대규모 또는 자주 업데이트되는 데이터에 적합합니다。
장점：매우 높은 맞춤성과 자동화 가능성을 제공하며， 크고 변화하는 데이터셋을 효율적으로 처리합니다。 고급 또는 반복 작업 워크플로에 적합합니다。
단점：매크로 권한이 필요하며， 초보자는 VBA 작업에 익숙해지기까지 시간이 걸릴 수 있습니다。
조건 기반 고유 값 개수를 작업할 때는 항상 범위 참조를 확인하고 모든 조건 열이 크기 면에서 정렬되어 있는지 확인하세요. 범위 불일치는 오류나 부정확한 결과의 흔한 원인입니다. 수식이 예상과 다른 결과를 반환하면 숨겨진 서식 문제나 빈 셀을 점검하세요. 성능이 중요한 상황에서는 PivotTable 및 VBA 가 배열 수식에 대한 강력한 대안을 제공합니다. 자신의 숙련도와 데이터셋의 복잡성에 가장 적합한 솔루션을 선택하세요. 또한 Kutools for Excel 은 복잡한 워크북에서 이러한 작업들을 더욱 효율적으로 처리할 수 있는 추가 유틸리티와 단축 기능을 제공한다는 점을 기억하세요。
최고의 Office 생산성 도구
| 🤖 | KUTOOLS AI 도우미： 다음을 기반으로 데이터 분석 혁신하기：지능형 실행 | 코드 생성| 사용자 지정 수식 생성 | 데이터 분석 및 차트 생성| 향상된 함수 호출… |
| 인기 기능:찾기， 강조 표시 또는 중복 표시 | 빈 행 삭제 | 데이터 손실 없이 열 결합 또는 셀 제거 | 공식을 사용하지 않는 반올림... | |
| 슈퍼 LOOKUP:다중 조건 VLookup | 다중 값 VLookup | 여러 시트에서 VLookup | 퍼지 매치.... | |
| 고급 드롭다운 목록:드롭다운 목록 빠르게 생성 | 종속형 드롭다운 목록 | 다중 선택 드롭다운 목록.... | |
| 열 관리자:특정 수의 열 추가|열 이동|숨겨진 열의 표시 상태 전환|범위 및 열 비교... | |
| 주요 기능:그리드 포커스 | 디자인 보기 |향상된 수식 표시줄 | 워크북 및 시트 관리자 | 자원 라이브러리(자동 텍스트)| 날짜 선택기 | 워크시트 병합 | 암호화/셀 해독 | 목록으로 이메일 보내기 | 슈퍼 필터 | 특수 필터(굵은 글꼴이 있는 셀 필터링/기울임꼴/취소선。。。) 。。。 | |
| 상위 15 도구 모음:12 텍스트도구(텍스트 추가,특정 문자 삭제, ...)| 50+차트유형(간트 차트, ...)| 40+ 실용적인수식(생일을 기준으로 나이 계산, ...)| 19 삽입도구(QR 코드 삽입,경로에서 그림 삽입, ...)| 12 변환도구(단어로 변환하기,환율 변환, ...)| 7 병합 및 분할도구(고급 행 병합,셀 분할, ...)|그 외 더 많은 기능 |
Kutools for Excel 로 Excel 역량을 한 단계 업그레이드하고 전례 없는 효율성을 경험하세요。Kutools for Excel 는 생산성과 저장 시간을 향상시키는 300 개 이상의 고급 기능을 제공합니다。가장 필요한 기능을 지금 바로 확인하세요。。。
Office Tab 가 Office 에 탭 인터페이스를 제공하여 작업을 훨씬 쉽게 만들어 줍니다
- Word, Excel, PowerPoint 에서 탭 기반 편집 및 읽기 기능을 활성화합니다, Publisher, Access, Visio 및 Project 에서도 사용 가능합니다。
- 새 창이 아닌 동일한 창의 새 탭에서 여러 문서를 열고 생성할 수 있습니다。
- 50% 만큼 생산성을 높이고 매일 수백 번의 마우스 클릭을 줄여줍니다！
모든 Kutools 애드인。 하나의 설치 프로그램
Kutools for Office스위트 번들은 Excel, Word, Outlook 및 PowerPoint 용 애드인과 Office Tab Pro 를 포함하며， 다양한 Office 앱을 사용하는 팀에 이상적입니다。
- 올인원 스위트— Excel， Word， Outlook 및 PowerPoint 애드인 + Office Tab Pro
- 하나의 설치 프로그램， 하나의 라이선스— 몇 분 안에 설정 완료(MSI 지원)
- 함께 사용할수록 더 효과적입니다— Office 앱 전반에서 생산성 향상
- 30 일간 모든 기능 무료 체험— 등록이나 신용카드 필요 없음
- 최고의 가성비— 개별 애드인 구매 대비 절약