중복 값 제거하고 세기

1. 문제

 

아래와 같이 과일별 업체별 출시일 자료가 있을 때

과일별 업체수를 중복을 제거하고 세려고 합니다.

다시 말해 바나나를 취급하는 업체수는 2개입니다.

 

중복제거후 세기.xlsx

0.01MB

 

 

2. 해법

서울 가는 길이 하나가 아니듯이 엑셀도 문제를 해결하는 방법이 여러 개입니다.

데이터 탭에서 중복된 항목 제거 명령을 실행한 후 CountA함수를 이용해 셀 수도 있고,

Filter 함수와 Unique 함수를 이용해 업체의 중복을 제거한 후 셀 수도 있으며,

최신 함수인 GroupBy함수를 이용할 수도 있습니다.

 

가. 중복된 항목 제거

중복된 항목 제거를 하면 기존 데이터가 사라지는 문제점이 있으므로

 

데이터를 다른 영역에 붙여 넣은 후 실행해야 합니다.

 

아래와 같이 E열 이후에 붙여 넣고

데이터 탭에서 중복된 항목 제거 명령을 누르면

제거할 기준열에 열 E, F, G가 모두 선택되어 있는데, 

 

G는 체크될 필요가 없으므로 체크를 해제하고

확인 버튼을 누르면

 

"중복된 항목 2개가 발견되어 제거되었습니다."란 메시지가 표시됩니다. A사와 C사가 2개여서 한 개씩 제거된 것입니다. 확인 버튼을 누릅니다.

 

이제 부분합을 해도 되고, 피벗 테이블을 만들어 개수를 세도 됩니다.

피벗 테이블이 편하니 피벗 테이블로 하겠습니다.

 

삽입 탭에서 피벗 테이블을 누르면

아래와 같이 범위가 맞게 선택되고, 배치할 위치가 새 워크시트로 선택되어 있는데, 

 

 기존 워크 시트를 클릭한 후 i1셀을 클릭합니다.

 

그리고 확인 버튼을 누르면 왼쪽에 피벗 테이블 영역이 생기고, 오른쪽에 피벗 테이블 필드 설정 창이 보이는데,

 

과일과 담당 업체를 클릭하면 둘 다 행 영역에 배치되는데,

 

담당 업체를 끌어서 값 영역에 놓습니다.

그러면 바나라가 2개 업체, 사과가 1개 업체, 총 3개 업체라고 표시됩니다.

 

나. Unique, Filter, CountA 함수

Unique함수는 중복을 제거하는 함수이고, Filter는 해당하는 조건에 해당하는 데이터만 걸러서 보여주는 함수입니다.

=COUNTA(UNIQUE(FILTER(B2:B6,A2:A6="바나나")))

 

=COUNTA(UNIQUE(FILTER(B2:B6,A2:A6="바나나"))) 에서

FILTER(B2:B6,A2:A6="바나나")는 과일이 바나나인 업체만 표시하도록 하는 것이며,

 

이렇게 하면 A사가 2번 표시되므로 중복을 제거하기 Unique함수를 사용한 것이며,

 

문자의 개수를 세야 하므로  CountA 함수를 사용한 것입니다.

 

다. GroupBy, Count 함수

GroupBy 함수는 최근에 제공하는 함수로 안 되는 엑셀 버전이 많겠지만 이런 것도 있다는 차원에서 작성합니다.

 

GroupBy 함수의 구문은

GROUPBY(row_fields,values,function,[field_headers],[total_depth],[sort_order],[filter_array])

로서, 인수는

row_fields  : 그룹으로 묶을 기준이 되는 열들,

values : 집계의 기준이 되는 값이 있는 열,

function : 집계 함수인 Sum, Count, CountA 등,

[field_headers] : 머리글이 포함되어 있는지 여부,

[total_depth] : 총계 및 소계를 표시할지 여부,

 

[sort_order] : 정렬 순서,

[filter_array] : 필터 조건

입니다.

 

따라서, 과일명이 바나나여야 하고, 업체명을 셀 것이고, 머리글이 포함되어 있다면,

=GROUPBY(B1:B6,B1:B6,COUNTA,3,0,,A1:A6="바나나")이 됩니다. 

 

이때 CountA를 사용하면 문자, 숫자와 관계없이 세므로 6이 반환되므로

 

숫자의 개수를 세려면 Count함수를 사용해야 합니다.

 

중복제거후 세기(완성).xlsx

0.01MB