[ 엑셀 Tip ] 엑셀 중복된 값 찾기

엑셀과 파워 쿼리를 이용한 다양한 중복된 값을 찾는 방법을 해 보도록 하겠습니다.

중복된 값 때문에 데이터 관리에 어려움이 있는 경우에는 vlookup을 사용하거나 데이터 모델링을 하는 경우 발생할 것 같습니다.

Case 1

 

다음과 같은 데이터에서 두 번째에 있는 b의 값을 가져오고 싶어서 vlookup 함수를 사용한다면 아래와 같이 에러가 발생합니다

왜냐하면 vlookup은 가장 위에 있는 값에서 값을 찾으면 종료가 되기 때문입니다. 이럴 때는 b의 값을 찾아서 첫번째 값은 지워 줘야 올바른 결과를 얻을 수 있습니다.

물론 b의 모든 값이 합이 필요하다면 sumif 함수를 사용하시면 됩니다.

[ 엑셀 Tip ] 특정 금액 사이의 합계 구하기 ( feat. sumifs )

 

Case 2. 고급 기능에 대한 설명으로 필요 시 봐주십시오

파워 피벗에서 데이터 모델링을 진행하는 경우 디멘션 테이블에 중복값이 있는 경우 모델링이 진행되지 않는 경우가 있습니다.

파워 bi에서는 다:다 기능이 제공되기 때문에 진행되는 경우가 있지만 다:다는 파워 bi 엔진 구동에 대한 세부 지식이 없는 경우

출력되는 결과는 2가지의 경우로 나누어집니다.

정답 ( 50% ) : 우연 ( 50% )

되도록 1:다 운영을 위해서 중복된 값을 제거해 주고 진행하는 것이 효율적일 것으로 생각됩니다.

---

 조건부 서식을 이용한 중복값 찾기

첫 번째로 조건부 서식을 이용한 중복된 값을 찾아 보도록 하겠습니다.

다음과 같은 가공의 데이터를 만들어 주었습니다.

다음과 같이 조건부 서식에서 중복 값을 입력해 눌러 주면 중복 된 값에 음영이 표기되는 것을 볼 수 있습니다.

※ 꼭 구분만 선택해 주셔야 오른쪽과 같은 결과가 나오게 됩니다.

[ 엑셀 Tip ] 조건부 서식 활용법_( 1. 셀 강조 규칙 )

보고서를 받아보는 입장과 데이터를 관리하는 입장의 2가지 상황에서 전자라면 색을 표기해서 보여 주는 것이 효율적이나

후자의 입장에서는 이렇게 데이터를 색으로 표기하는 것은 추천하지 않습니다.

색으로 표기한 데이터는 결과를 의미합니다. 그 음영의 데이터를 가지고 그다음 작업을 할 수 있는 경우는 음영이 있는 식을 정리 정도만 가능하기 때문입니다.

 

중복된 항목 제거 이용

중복된 데이터를 찾는 가장 큰 이유는 중복된 데이터를 제거하고 싶은 경우가 많습니다. 이때 활용하면 매우 유용한 방법입니다.

다음과 같이 데이터를 선택해 주고 데이터 탭에서 중복된 항목 제거를 누면 오른쪽과 같이 중복된 값이 제거된 값이 나타나는 것을 볼 수 있습니다.

위에서 배운 sumif를 사용해서 결과를 구하면 동일한 결과가 나오게 되는 것을 볼 수 있습니다.

countif 함수를 이용한 방법

countif 함수를 사용해서 중복된 값을 찾는 방법을 해 보도록 하겠습니다. 중복이라는 것은 1 이상의 개수를 가지는 것을 의미합니다.

이러한 규칙을 이용해서 countif 함수를 사용해 보도록 하겠습니다.

다음과 같이 countif 함수를 활용해서 범위는 구분의 전체, 조건은 각 열의 구분의 값을 선택해 주면 다음과 같은 결과가 나오게 됩니다.

아까 이야기한 것처럼 중복은 2 이상의 값이기 때문에 보기 편하게 하기 위해서 ( for 보는 사람 ) 조건부 서식을 이용해서 2 이상의 값에 음영을 표기해 보았습니다.

중복된 값에 대해서 간단하게 확인할 수 있습니다.

[ 엑셀 Tip ] countif 함수를 이용한 조건에 맞는 개수 구하기

피벗 테이블 이용

피벗 테이블을 이용해서 위에 있는 구분의 고유값을 구하는 방법과 countif 함수를 사용한 효과를 동시에 해 보도록 하겠습니다.

[ 엑셀 Tip ] 피벗테이블 활용 ( feat. 표기능 )

왜 피벗테이블이 활용도가 높은지 다시 한번 생각하는 기회가 되지 않을까 생각됩니다.

반응형

다음과 같이 피벗 테이블을 만들어 주었습니다.

• 구분의 고유값 구하기

• 구분의 개수 구하기

피벗테이블 행에 구분을 넣어 주었더니 고유 값이 나오는 것을 볼 수 있습니다. 그럼 countif의 효과도 해 보도록 하겠습니다.

우선 값을 값 필드에 넣어 주었더니 합계가 나오는 것을 볼 수 있습니다. 이전에 합 기억나시나요?

같은 결과가 나오는 것을 볼 수 있습니다. 그럼 합계를 개수로 변경해 주도록 하겠습니다.

아마도 구분의 값에 대한 개수의 결과가 나오지 않을까 생각됩니다.

아까 countif의 결과와 구분의 순서가 오름차순으로 정렬되어 있어서 결과가 달라 보일 수 있지만 비교해보면 같은 결과가 나오는 것을 볼 수 있습니다.

피벗테이블에서 vlookup을 사용해서 비교해 보시기 바랍니다.

※ 꼭 수식을 입력할 때는 파일 - 옵션 - 수식 - 피벗 테이블 참조에 GetPivotData 함수 사용(P) 체크 해재

파워쿼리를 이용한 중복값 제거

파워쿼리를 이용한 중복된 데이터를 제거하는 방법입니다. 데이터를 파워쿼리 편집창으로 올리는 것은 아래 글을 참조해 주십시오.

[ 파워 BI ] 파워 쿼리를 이용한 데이터 합치기 ( 파일 합치기 )

파워쿼리 편집창에서 구분 우클릭 후 다른 열 제거를 눌러줍니다. 그 후 다시 우클릭 중복된 항목 제거를 눌러주면 결과가 나오게 됩니다.

닫기 및 다음으로 로드를 누른 후 표를 선택해 주고 기존 워크시트에 E1을 선택해 주고 확인을 눌러주면 구분의 고유값이 나오게 되고 다시 한번 검을 하면 같은 결과가 나오게 되는 것을 볼 수 있습니다.