엑셀 피벗테이블 중복 제거 개수 계산 방법
업무나 다양한 환경 속에서 많은 데이터들을 취합을 하고 원하는 데이터를 추출, 가공한 데이터를 얻어 낼 수 있는 것이 바로 엑셀입니다. 다양한 기능 중 피벗테이블이라는 기능은 원본만 있으면 쉽게 데이터를 추출할 수 있는 강력한 능력이라고 할 수 있습니다.
업무를 보다보면 엑셀 능력에 따라 누구는 3시간 동안 할 업무를 피벗테이블을 활용하여 단 10분 만에 끝낼 수도 있는데요. 피벗테이블을 사용하며 겪었던 문제를 해결하는 포스팅을 통해 구독자 분들에게 도움을 드리고자 합니다.
피벗 테이블을 통해 동일한 값에 대해 갯수는 구할 수 있지만 중복 값을 제거하고는 수량을 확인할 수가 없습니다. 이를 해결하기 위해서는 기존 원본 시트에 간단한 조건을 추가해줘야 해결할 수 있을 텐데요 아래를 보시면서 따라 하시면 됩니다.
피벗테이블 삽입 하기
위 예제를 보시면 A 상품에 품목수가 몇개인지 알고 싶으나 피벗테이블에는 중복 값이 포함된 3개가 나옵니다. 하지만 실제 품목은 A상품은 책, 연필 2가지 이죠
2라는 값을 얻기 위해 추가적인 작업을 진행 할껀데요. 값을 얻기에는 원본 데이터에 중복 값 제거 후 피벗 하기 등 여러 가지 방법이 있습니다만 원본을 최대한 유지하면서 작업하는 방법을 알려드리겠습니다.
지역수를 구할 카운팅 함수 설정 하기
- 우측 빈셀에 열을 하나 추가해줍니다.
- 그다음 COUNTIFS 함수를 이용하여 해당 상품코드별 품목을 1로 설정시킵니다
예제에 사용되는 함수는 =IF(COUNTIFS($A$2:$A2,A2,$B$2:$B2,B2)=1,1,0)
절댓값 $ 맞춰 사용하시길 바랍니다.
=IF(COUNTIFS(상품코드$$절대값:상품코드$절대값,상품코드,품목$$절대값: 품목$절대값,품목)=1,1,0)피벗테이블에 품목수 추가하기 (데이터 원본 변경)
- 품목수를 추가한 뒤 데이터 원본 행이 추가되었으니 피벗테이블상 원본도 수정해주어야 합니다.
- 상단 메뉴 → 분석 → 데이터 원본 변경 → 원본 드래그 확인
- 피벗 테이블 필드상 품목수 코드가 추가로 생성된 것을 확인 후
- 값으로 옮겨주면 끝입니다.
- 품목수를 보시면 A상품코드에 품목 2개(책, 연필)가 설정됨
자 여기까지 따라오시면 피벗테이블 중복 제거하여 개수 구하는 방법을 아셨을 텐데요. Countifs 함수가 약간 헷갈리긴 하기만 피벗테이블 중복 제거가 필요하신 분들은 이 정도는 수준은 되실 거라 생각됩니다.
혹시나 궁금하신 거나 문의하실 게 있으시면 아래 댓글로 남겨주시면 성실히 답변해드리도록 하겠습니다.
