엑셀에서 중복된 값 제거하고 고유값만 가져오기(ft. UNIQUE)

 

엑셀에서 중복된 값을 제거한다는 말은 고유한 값만 남긴다는 뜻 일 수도 있지만 중복되는 값 중 하나는 남긴다는 뜻 일 수도 있습니다. 이번 포스팅에서는 둘 다 할 수 있는 UNIQUE 함수에 대해 포스팅할 예정입니다.

 

엑셀에서 중복된 값 제거하고 고유값만 가져오기

 

위 그림은 어떤 회사의 출입시간을 가지고 오늘 출근한 사람, 퇴근하지 않은 사람을 UNIQUE 함수로 골라낸 것입니다. 하루에 출입은 출근과 퇴근만 있다고 단순히 가정을 하고 진행하겠습니다. 이름이 한 번이라도 있는 경우 출근만 하고 퇴근은 하지 않은 경우, 즉 중복되는 결과값은 하나만 남겨놓는 경우입니다. 이름이 두 번 있는 경우는 출근하고 퇴근도 한 사람이며, 이름이 한 번만 있는 경우는 출근하고 아직 퇴근하지 못한 경우입니다. 그리고 UNIQUE 함수는 FILTER를 조합하면 더 다양한 것을 표현할 수 있습니다. "출근한 사람 중 재무팀" 같은 것을 뽑아낼 수 있게 됩니다. 거기에 SORT 함수까지 사용한다면 보기 좋게 정렬까지 할 수 있습니다.

이와 같이 UNIQUE함수는 간단하게 중복을 제거할 수 있지만 엑셀 2021, 365에서 지원하므로 그 이하 버전을 사용하는 경우에는 아래 포스팅을 참고해 주시기 바라며 같은 기능을 구현해도 UNIQUE 함수를 사용하는 것 보다는 복잡해질 수 있습니다.

 

엑셀 중복 값 확인 및 제거 하기

 

---

1. UNIQUE 함수 기본 사용법

엑셀 2021, 365 이상에서만 지원하는 함수들은 특징이 결과 배열이 동적으로 생성이 될 수 있다는 점입니다. 그래서 동적으로 생성하려다가 해당 셀에 데이터가 있는 경우 #분산!(#SPILL!)오류가 발생할 수 있으며 원본을 참조할 수 없는 경우 #REF! 오류를 발생시킵니다.

 

 

UNIQUE(array,[by_col],[exactly_once])

array (필수): 중복값을 제거하고 싶은 셀의 범위.
[by_col] (선택): T/F.기본값은 FALSE이며 행을 비교하여 고유한 행을 반환합니다. TRUE는 열을 비교하여 고유한 열을 반환합니다.
[exactly_once] (선택): T/F. 기본값은 FALSE이며 중복된 항목을 한 번만 표시합니다. TRUE는 중복된 항목은 모두 제거합니다.

 

1-1. 출근한 사람 명단

UNIQUE 함수 기본 사용법- 출근한 사람 명단

 

1. 결과를 표시하고 싶은 셀에 수식을 입력합니다.

=UNIQUE(A2:A10)

 

함수 사용법이 아주 간단합니다. 이렇게 입력된 수식은 사실 =UNIQUE(A2:A10,FALSE,FALSE)로 입력한 것과 같으며 중복된 항목을 한 번만 표시한 결과가 나오게 됩니다.

 

UNIQUE 함수 기본 사용법- 출근한 사람 명단 결과

 

실제로도 원하는 결과값이 나온 것을 확인할 수 있으며 빨간색으로 표시된 사각형은 앞서 말씀드린 동적으로 생성되는 영역으로 수식의 결과에 따라 표시되는 범위가 바뀌게 됩니다.

 

1-2. 퇴근하지 못한 사람 명단

UNIQUE 함수 기본 사용법-  퇴근하지 못한 사람 명단

 

1. 결과를 표시하고 싶은 셀에 수식을 입력합니다.

=UNIQUE(A2:A10,FALSE,TRUE)

 

이번에는 명시적으로 exactly_once을 TRUE로 줌으로써 중복된 사람은 모두 삭제하도록 해봤습니다.

 

UNIQUE 함수 기본 사용법-  퇴근하지 못한 사람 명단 결과

 

출근만 하고 퇴근하지 못한 가여운 이름이 명단에 보이게 됩니다.

이제 주로 UNIQUE 함수와 함께 사용하는 두 개의 함수를 이용하여 응용해 보겠습니다.

 

---

2. UNIQUE 함수 응용법

UNIQUE 함수는 SORT 함수와 함께 사용하여 정렬할 수 있습니다. 앞에서 출근한 사람의 명단이 정렬되지 않을 것이 불편했었는데 정렬해 보겠습니다.

 

2-1. UNIQUE 함수 결과 정렬하기

UNIQUE 함수 응용법- UNIQUE 함수 결과 정렬하기

 

1. 결과를 표시하고 싶은 셀에 수식을 입력합니다.

=SORT(UNIQUE(A2:A10))

 

정렬을 하는 함수인 SORT를 출근한 사람을 뽑아내는 UNIQUE 함수의 수식(1-1의 수식) 가장 바깥에 감싸주기만 하면 됩니다.

 

UNIQUE 함수 응용법- UNIQUE 함수 결과 정렬하기 결과

 

출근한 사람의 명단이 알파벳 순으로 정렬된 것을 확인하실 수 있습니다.

 

 

2-2. 출근한 사람 중에 부서가 재무팀인 명단

출근한 사람중에 부서가 재무팀인 직원의 명단은 약간 난이도가 있습니다. 기존에는 이름만 가지고 함수에 인자로 사용했다면 이제부터는 부서를 기준으로 데이터를 만들고 UNIQUE 함수로 중복을 제거할 것이기 때문입니다. 예를 들기 위해 부서를 추가했습니다.

 

UNIQUE 함수 응용법-출근한 사람 중에 부서가 재무팀인 명단

 

1. 결과를 표시하고 싶은 셀에 수식을 입력합니다.

=UNIQUE(FILTER(B2:B10,A2:A10="재무팀"))

 

이번에는 FILTER라는 함수를 이용하여 부서가 재무팀인 경우를 찾고 그 결과를 UNIQUE 함수에 전달하여 "출근한 사람 중에 재무팀의 명단"을 구할 수 있었습니다.

 

UNIQUE 함수 응용법-FILTER 함수 결과

 

식을 차례대로 보면 FILTER의 결과는 위 빨간 사각형 영역대로 나왔을 것입니다. 그 내용을 인자로 받아서 UNIQUE 함수로 중복을 제거하여 "출근한 사람 중 재무팀" 명단을 작성할 수 있었습니다.

 

UNIQUE 함수 응용법-단계별 결과

 

이런 식으로 응용한다면 9시 이후 출근한 기획팀 중 퇴근하지 못한 사람의 명단과 같은 것도 만들 수 있을 것입니다.