계산을 편리하게 하기 위해 엑셀 피벗 테이블에서 중복 값을 세어야 할 때가 종종 있습니다. 피벗 테이블은 엑셀의 강력한 기능 중 하나인데요, 여기서 중복 개수를 세는 작업은 '고유 개수(Distinct Count)'라고도 불립니다. 이 글에서는 예시와 자세한 설명을 통해 피벗 테이블에서 중복 값을 세는 방법을 단계별로 알아보겠습니다.
연습용 워크북
아래 워크북을 다운로드하여 함께 실습해 보세요.
엑셀 피벗 테이블에서 중복 값을 세는 2가지 쉬운 방법
1. 보조 열을 삽입하여 중복 값 개수 세기
보조 열을 활용하는 것이 피벗 테이블에서 중복 값을 세는 가장 대표적인 방법입니다. 예를 들어 직원들의 지역, 판매 제품, 판매 수량 정보가 담긴 데이터(B4:E10)가 있다고 가정해 보겠습니다. 우리는 지역별로 근무하는 직원 수의 총합을 구하고자 합니다.
1단계:
- 먼저 F4:F10 범위에 '중복 개수(Count D)'라는 이름의 보조 열을 만듭니다.
- 다음으로 E5 셀을 선택합니다.
- 그리고 아래의 수식을 입력합니다.
=IF(COUNTIFS($C$5:C5,C5,$B$5:B5,B5)>1,0,1)
2단계:
- Enter 키를 누른 후 채우기 핸들(Fill Handle)을 이용해 아래 셀까지 자동으로 채웁니다.
🔎 수식의 동작 원리
- COUNTIFS($C$5:C5,C5,$B$5:B5,B5): 엑셀의 COUNTIFS 함수는 지정한 조건 범위에서 조건에 해당하는 셀의 개수를 반환합니다. 여기서는 조건 범위1 $C$5:C5 안에 C5가 몇 번 등장하는지, 그리고 조건 범위2 $B$5:B5 안에 B5가 몇 번 등장하는지를 함께 계산합니다. 범위의 시작 지점을 절대 참조($)로 고정해야 드래그해도 참조 범위가 변하지 않습니다.
- IF(COUNTIFS($C$5:C5,C5,$B$5:B5,B5)>1,0,1): 엑셀의 IF 함수는 이름이 처음 등장하면 1을, 이미 한 번 이상 나온 경우에는 0을 반환합니다.
3단계:
- 데이터 범위 내 임의의 셀을 선택합니다.
- [삽입] 탭으로 이동한 후 [표] 그룹에서 [피벗테이블]을 선택합니다.
4단계:
- 피벗테이블 생성 창이 나타나면 표 또는 범위를 지정합니다.
- 피벗 테이블을 표시할 위치를 선택합니다.
- [확인]을 클릭합니다.
5단계:
- 피벗 테이블이 거의 준비되었습니다. 이제 LOCATION(지역) 필드는 [행] 영역으로, Count D 필드는 [값] 영역으로 드래그하여 넣습니다.
6단계:
- 마지막으로 피벗 테이블이 완성되면 지역별 직원 수를 바로 확인할 수 있습니다.
함께 읽으면 좋은 글: 엑셀에서 열의 중복 값 개수 세는 방법 (3가지)
2. 데이터 모델(Data Model)을 활용하여 중복 값 개수 세기
데이터 모델은 엑셀 2013 이상 버전에서 제공되는 피벗 테이블의 새로운 기능입니다. 이 기능을 사용하면 보조 열 없이도 중복 값을 셀 수 있습니다. 마찬가지로 직원들의 지역, 판매 제품, 판매 수량이 담긴 데이터(B4:E10)가 있다고 가정하고, 지역별 근무 직원 수를 구해 보겠습니다.
1단계:
- 먼저 데이터 범위 내 임의의 셀을 선택합니다.
- [삽입] 탭으로 이동합니다.
- [표] 그룹에서 [피벗테이블]을 선택합니다.
2단계:
- 피벗테이블 생성 창에서 표 또는 범위를 지정합니다.
- 다음으로 피벗 테이블을 표시할 위치를 선택합니다.
- 반드시 '이 데이터를 데이터 모델에 추가' 옵션에 체크하세요.
- [확인]을 클릭합니다.
3단계:
- 피벗 테이블 필드 창이 나타납니다.
- LOCATION(지역)을 [행] 영역으로, EMPLOYEE(직원)를 [값] 영역으로 넣습니다.
4단계:
- 각 지역별 직원 수가 집계된 결과를 확인할 수 있습니다.
5단계:
- 이제 'EMPLOYEE 개수' 열에서 임의의 셀을 선택하고 마우스 오른쪽 버튼으로 클릭합니다.
- [값 필드 설정]을 선택합니다.
6단계:
- 값 필드 설정 대화 상자가 나타납니다.
- 드롭다운 목록에서 계산 유형을 '고유 개수(Distinct Count)'로 선택합니다.
- [확인]을 클릭합니다.
7단계:
- 완료! 이제 지역별 고유한 직원 수가 깔끔하게 정리된 것을 확인할 수 있습니다.
추가로 읽어보세요: 엑셀에서 중복 값을 한 번만 세는 방법
결론
지금까지 소개한 두 가지 방법을 활용하면 엑셀 피벗 테이블에서 중복 값을 손쉽게 세울 수 있습니다. 연습용 워크북도 함께 준비되어 있으니 직접 따라 해 보세요. 궁금한 점이 있거나 새로운 방법을 제안하고 싶으시다면 언제든지 알려주세요.
관련 글
- 엑셀에서 일별 발생 횟수 세는 방법 (4가지 빠른 방법)
- 엑셀에서 열의 각 값별 발생 횟수 세기
- 엑셀에서 중복 행 개수 세는 방법 (4가지 방법)