엑셀 중복값 찾기, 합치기, 추출, 개수를 알아내는 방법

안녕하세요. 실무에서 엑셀은 빼놓을 수 없는 중요한 프로그램이죠.

그 중 엑셀에서 중복값을 찾고, 합치고, 추출해 내고, 갯수를 세는 방법을 알아보도록 하겠습니다.

 

엑셀 중복값 찾기

엑셀 중복값 찾기는 조건부 서식을 사용하면 편합니다.
중복값 찾기에는 순수하게 1)중복된 값만 찾는 것이 있으며, 
중복된 값 중 2)특정 값만 찾는 것이 있습니다.

▲중복값 찾기 예제

위의 '중복값찾기'와 '엑셀예제'라는 텍스트를 예시로 적어넣었습니다.

 

 

1) 여기에서 '중복값찾기'만 찾아보도록 하겠습니다.

 

 

 

▲조건부서식

찾고자 하는 값이 있는 범위 혹은 열전체를 선택한 후

위의 그림과 같이 홈>조건부 서식을 클릭해줍니다.

 

▲조건부서식-새규칙

조건부서식을 눌렀다면 위와 같이 여러가지 기능이 나옵니다.

여기에서 새규칙(N)을 눌러주세요. 규칙 관리(R)를 통해서도 가능하나,

우리는 새로 조건을 지정할 것이기 때문에 새규칙으로 들어가줍니다.

▲고유 또는 중복 값만 서식 지정

새규칙을 누르면, 위와 같이 '새 서식 규칙'이라는 것이 열리며 규칙 유형을 선택할 수 있습니다.

규칙유형 중 ▶고유 또는 중복 값만 서식 지정 이라는 것을 선택한 후 아래쪽에 서식(F)라는 것을 눌러줍니다.

 

▲조건부 서식 중복값 찾기

그러면 위와 같이 표시 형식/글꼴/테두리/채우기 탭이 나옵니다.

저는 개인적으로 채우기 탭으로 색을 지정하는 것을 좋아합니다.

위의 베이지색을 선택하고 확인을 누르겠습니다.

▲서식 색상 지정

그러면 위와 같이 서식 생상이 지정됩니다. 미리보기에서는 현재 글자와 채우기가 보여집니다.

글꼴이나 테두리 등을 바꿨다면 그것들도 미리보기로 보여지겠죠?

 

이제 확인을 눌러줍니다.

 

▲단순 중복값 찾기

이것은 단순하게 중복값을 찾을 때 사용합니다. 

동명이인, 혹은 같은 값을 찾을 때 사용하기에 적합합니다.

 

하지만 위와 같이, 모두 중복값으로 되어있거나 중복값이 많은 경우 내가 찾고자 하는 값만 중복값이 있나 확인하는 기능이 필요하겠죠?

 

 

 

 

이제 2)원하는 중복값만 찾는 방법을 알아보겠습니다.

 

1)과 동일하게 새규칙을 누릅니다.

 

▲포함한 셀만 지정

위와 같이 ▶다음을 포함하는 셀만 서식 지정을 누른 뒤

특정 텍스트>포함을 누르고 찾고자 하는 텍스트 값을 입력하면 됩니다.

그런 후 서식에서 색상을 지정하세요.

'엑셀예제'의 값만 중복값이 어떤건지 찾아보도록 하겠습니다.

 

▲찾고자 하는 중복값만 찾기

위와 같이, 찾고자 하는 값만 색상이 채워집니다.

이 규칙을 여러개 한다면, A라는 값은 빨간색 B라는 값은 주황색 등 다양하게 규칙을 정할 수 있습니다.

또한 이렇게 정한 규칙은 정렬을 할 수 있는데요.

 

▲필터 추가

 

필터를 하는 방법은 여러가지나, 열과 행이 많을 경우 필터를 따로 설정하는 것이 추후 데이터관리에 용이합니다.

위의 사진과 같이, 홈에서 우측상단에 있는 정렬 및 필터를 눌러주신 후 필터(F)를 클릭해주세요.

 

▲필터지정

위와 같이 필터를 설정하게 되면 화살표가 나오게 됩니다. 구분이라는 것은, 보통 열의 제목이 있기 때문에 부여했으며, 해당 행에 제목이 있어야 필터가 적용되니 행에 텍스트가 없다면 부여해주세요.

 

▲필터로 정렬

위와 같이 '색 기준 정렬(T)'에 마우스를 대면 '셀 색 기준 정렬'이라는 곳이 보일 것입니다.

우리가 지정한 베이지색을 클릭해준다면, 제일 위에 베이지색의 데이터들이 오게 됩니다.

 

▲색기준 정렬 사용

위와 같이 베이지색의 데이터 값인 '엑셀예제'가 위로 정렬된 것을 확인할 수 있습니다.

 

이것이 중복값 찾는 두 번째 방법입니다. 

 

둘 다 상황에 맞게 활용하시면 좋습니다.

엑셀 중복값 합치기, 중복값 추출하기, 중복값 제거하기

중복값 합치기의 경우, 데이터관리에서 중복값 제거라는 기능을 사용하는 것입니다.
중복된 값들을 가지고 목록데이터를 만들고, 그것으로 매출통계나 기타 통계 등을 내기 위해 사용되죠.

▲중복갑 합치기

위와 같이, 중복된 값이 너무 많은데 여기에서 어느 값들만 있는지 알고 싶을 경우가 있습니다.

 

만약에 회사에서 점심시간에 도시락을 배달시킨다고 가정합니다.

부장님은 A도시락, 차장님은 B도시락, 팀장님은 A도시락, 대리님은 C도시락 등 주문하는 것들이 다 다르죠?

하지만 주문할 때는 A도시락 2개, B도시락 1개, C도시락 1개로 주문을 해야합니다.

 

몇개 안된다면 암산으로도 끝날 문제지만.. 이러한 것들이 많아진다면요? 

재고나 매출, 기타 데이터들의 수가 수천 수만가지면 이것들을 일일히 찾을 수 없습니다.

 

그렇기에 중복값 합치기를 통해서 중복값을 추출해냅니다.

 

▲중복된 항목 제거

적용하고자 할 셀을 선택한 후 '데이터' 탭의 '중복된 항목 제거'라는 것을 눌러줍니다.

 

▲중복된 항목 제거 확인

확인을 눌러주면 아래와 같은 팝업창이 뜹니다.

 

▲중복값 제거 팝업

이것을 눌러주면 바로 중복된 값들이 없어지면서 고유값들이 1개만 남는 것을 알 수 있습니다.

 

▲중복값 제거 후 모습

위와 같이, 그 많던 데이터들이 사라지고 고유값들만 남아있네요.

나중에 이값들로 vlookup, sumif등을 활용해 더욱 많은 데이터 작업을 할 수 있습니다.

 

 

엑셀 중복값 개수 알아내기

중복값 개수 알아내기는 Countif라는 함수를 사용하는 것입니다. Countif는 중복값의 개수를 나타내줍니다.

 

▲중복값 추출 후

우리는 중복값 추출에 대하여 배웠습니다. 그런 후 위의 빨간색과 같은 데이터를 얻었고요.

그러면 좌측에 있는 무수한 데이터와, 우측에 있는 고유 데이터를 두고

좌측에 있는 중복값들이 몇개 있는지 우측에 있는 고유 목록데이터에 개수를 표시하도록 하겠습니다.

엑셀예제 우측에 =Countif(찾고자 하는 범위, 기준값)을 지정해줍니다.

=Countif(range,criteria)

 

▲Countif 함수 범위설정

위와 같이 =countif( 이후 찾고자 하는 셀의 범위를 선택한 상태입니다.

여기에서 F4를 한 번 눌러서 절대참조를 지정해줬습니다.

엑셀의 함수를 사용하게 되면 범위는 꼭 절대참조로 해주세요.

함수가 복사되거나 값들이 이동하면서 찾고자하는 범위 자체가 흔들리기 때문이죠.

 

이후 기준 값 뒤에 ,라는 콤마를 눌러줍니다.

그렇다면 =Countif($E:$E), 까지 따라오셨겠군요. 이 다음에는 찾고자 하는 범위를 지정해줍니다. 

 

▲Countif함수 찾고자 하는 값 설정

 

위와 같이, 찾고자 하는 기준 값'엑셀예제'를 클릭해주세요. 여기에서 절대참조나 혼합참조를 해도 되지만,

값이 두개니까 그냥 냅둘게요~ 보통은 엑셀을 위아래 아래로 데이터가 쌓이기 때문에 열에 절대참고를 거는 편입니다.

$열,행 이런식으로요. F4를 세 번 누르면 돼요~!

 

▲중복값 개수 찾기 완료

위와 같이 함수 수식이 나오며, 셀안에는 '20'이라는 결과값이 나옵니다.

아주 쉽게 중복값의 개수를 찾은 것이지요.

이제 '중복값찾기'라는 것의 개수도 찾아볼까요?

 

▲중복값 개수 찾기

위와 같이, 수식을 복사한 후 아래에 붙여넣기를 하면 됩니다.

수식복사는 컨트롤 C를 한 후 복사할 곳에 우클릭해서 수식복사를 하시면 됩니다.

 

저는 따로 단축키를 만들어 사용하용합니다. 값, 수식, 서식의 복사/붙여넣기는 자주 사용하기 때문이죠.

▲엑셀 기타명령

위와 같이 기타명령을 클릭한 후, 본인이 원하시는 명령값을 정하시면 됩니다.

이것까지 설명하면 너무 길어지니 이것은 직접 눌러봐주세요~

 

아무튼 수식복사을 복사한다면, 엑셀을 똑똑하기에 왼쪽에 있는 기준값을 최신것으로 가져옵니다.

따로 절대참조로 행을 고정하지 않는 이상 수식의 위치가 바뀔 때마다 참조하는 값도 바뀌는 것이지요!

 

 

이렇게 중복값 찾기와 중복값 추출, 중복값 개수 찾기에 대해 알아보았습니다.

 

엑셀을 사용하면 사용할수록 데이터를 효율적으로 관리하게 해줍니다.

 

다들 엑셀로 업루를 효과적으로 처리하세요~!