엑셀 중복 제거, 하나씩 남길까 반복된 행을 전부 뺄까?

하나씩 남기는 목록과 한 번만 나온 목록

엑셀에서 같은 기록을 한 건씩 남길 때는 ‘중복된 항목 제거’나 UNIQUE의 기본 동작을 쓴다. 원본을 고쳐도 되는 일회성 정리는 사본에서 중복을 제거하고, 원본을 보존하며 목록을 갱신할 작업은 UNIQUE로 별도 결과를 만든다. 반면 두 번 이상 나온 기록을 모두 제외하려면 =UNIQUE(범위,FALSE,TRUE)처럼 세 번째 인수를 TRUE로 지정한다. Microsoft의 UNIQUE 설명에서 이 인수의 이름은 exactly_once다.

예를 들어 ‘PEN, PEN, BOOK’에서 품목 목록을 만들면 PEN과 BOOK이 남아야 한다. 그러나 한 번만 등장한 품목을 찾는 작업이라면 BOOK만 남는다. 전자는 품목 종류를 세거나 선택 목록을 만드는 데 맞고, 후자는 반복되지 않은 항목을 따로 검토하는 데 맞다. 둘 다 흔히 ‘중복 제거’라고 부르지만 필요한 결과가 다르다.

아래는 설명용 주문 자료다. A1:C8에 열 제목과 일곱 행을 입력했다고 가정한다. 왼쪽의 시트 행 번호는 위치 설명용이며 실제 데이터 열에는 넣지 않는다. 주문번호는 한 주문 안의 여러 상품에서 반복될 수 있고, 상품코드는 서로 다른 주문에서도 반복될 수 있다.

시트 행	A: 주문번호	B: 상품코드	C: 수량
2	O-410	PEN	2
3	O-410	BOOK	1
4	O-410	PEN	2
5	O-411	PEN	1
6	O-412	FILE	1
7	O-412	FILE	3
8	O-413	BOOK	1

세 열이 모두 같은 것은 2행과 4행이다. 이를 한 행으로 줄이면 여섯 행이 남는다. 두 행을 모두 제외하면 나머지 다섯 행이 남는다. 하지만 주문번호만 비교해 하나씩 남기면 네 행으로 줄고, O-410 주문의 BOOK까지 사라진다. 엑셀이 상품을 잘못 읽은 것이 아니라 주문번호 하나를 같은 기록의 기준으로 삼은 결과다.

직접 지울 때는 대상 범위와 비교 열을 나눈다

Windows용 Excel에서 원본 시트를 복사한 뒤, 사본의 A1:C8 전체를 선택하고 데이터 → 중복된 항목 제거로 들어간다. ‘내 데이터에 머리글 표시’를 켜고 주문번호·상품코드·수량을 모두 비교 열로 선택한다. 확인을 누르면 이 예제에서는 동일한 세 값을 가진 두 행 중 먼저 나온 행이 남는다. 삭제된 항목 수가 한 개이고 결과가 여섯 행인지 확인한다.

대화상자의 열 체크는 삭제할 열을 고르는 기능이 아니다. 체크한 열은 같은 기록인지 비교하는 기준이며, 중복으로 판정된 행은 처음 선택한 범위 안에서 함께 제거된다. Microsoft의 중복 제거 안내는 비교 열과 제거 대상 범위를 구분하고, 첫 등장 값을 남긴다고 설명한다.

따라서 A:C를 선택한 뒤 주문번호와 상품코드만 체크하면 O-412/FILE의 수량 1과 3도 같은 그룹으로 처리된다. 위쪽의 수량 1이 남고 뒤쪽의 수량 3이 빠진다. 이는 최신 수량이나 맞는 수량을 고른 결과가 아니다. 두 줄이 정정 전후 기록인지, 별도 주문 항목인지 원자료에서 확인하지 않았다면 이 조합으로 행을 지울 근거가 부족하다.

선택 범위 밖의 셀은 같은 방식으로 이동하지 않는다. 예를 들어 D열에 각 주문의 담당자 메모를 적어 놓고 A:C만 줄이면, D열의 메모가 원래 주문과 나란히 남는다고 기대할 수 없다. 그 메모도 한 기록의 일부라면 D열까지 대상 범위에 넣고, 비교 기준으로 쓸지는 대화상자에서 따로 정한다. 관련 열을 함께 잡는 이유는 값끼리의 연결을 보존하기 위해서다.

원본은 두고 필요한 열만 반환하는 UNIQUE

UNIQUE는 Microsoft 365, Excel 2021·2024 및 웹용 Excel 등에서 지원한다. 다음 수식들은 원본 일곱 행을 그대로 둔 상태에서 각각 빈 영역에 입력하는 예제다. 같은 자리에 여러 수식을 겹쳐 넣지 않는다. F2에서 시작한다면 세 열짜리 결과가 나오는 수식은 F:H와 아래쪽 행에 빈 공간이 필요하다.

필요한 결과	수식	예제 결과
주문번호 종류다.	=UNIQUE(A2:A8)	주문번호 네 개다.
주문별 상품 조합이다.	=UNIQUE(A2:B8)	두 열짜리 조합 다섯 개다.
수량까지 같은 행을 한 건씩 남긴다.	=UNIQUE(A2:C8)	세 열짜리 여섯 행이다.
원본에서 딱 한 번 나온 전체 행이다.	=UNIQUE(A2:C8,FALSE,TRUE)	2·4행을 뺀 다섯 행이다.

수식에 A:B를 넘겼다면 결과에도 주문번호와 상품코드 두 열만 나온다. C열의 수량은 자동으로 따라붙지 않는다. 주문·상품별로 수량까지 하나 골라 붙이려면 어떤 수량을 남길지 결정하는 작업이 추가로 필요하다. O-412의 두 수량이 다르다는 사실을 유지하려면 전체 A:C를 반환해 두 행을 함께 보는 편이 맞다.

구문은 UNIQUE(array,[by_col],[exactly_once])다. 두 번째 인수 FALSE는 행끼리 비교하라는 뜻이며 생략했을 때도 같은 방향으로 비교한다. TRUE로 바꾸면 열끼리 비교한다. 그러므로 =UNIQUE(A2:C8,TRUE)는 ‘한 번만 나온 행’ 수식이 아니다. 한 번만 나온 행을 원한다면 FALSE 뒤에 세 번째 TRUE를 둔다.

또한 ‘한 번’은 수식에 넣은 범위와 열을 기준으로 한다. O-412/FILE/1과 O-412/FILE/3은 세 열 전체로는 각각 한 번씩 나타난다. 따라서 전체 행의 exactly_once 결과에는 둘 다 남는다. 주문·상품 조합 자체가 반복되지 않은 것만 원한다면 =UNIQUE(A2:B8,FALSE,TRUE)를 사용하며, 이때는 원래 3·5·8행에 해당하는 세 조합만 나온다.

같은 주문·상품인데 수량이 다를 때

반복된 조합을 먼저 표시하면 어떤 행을 지울지 정하기 전에 원자료를 검토할 수 있다. 삭제하지 않은 예제 원본으로 돌아가 D열이 비어 있는 상태에서 D1을 ‘조합 횟수’로 두고, D2에 다음 수식을 입력한 뒤 D8까지 채운다. 앞선 메모 사례처럼 D열에 자료가 있다면 보조 열은 다른 빈 열에 만든다. 수식은 A열과 B열이 현재 행의 주문번호·상품코드와 동시에 같은 행을 센다.

=COUNTIFS($A$2:$A$8,A2,$B$2:$B$8,B2)

결과는 위에서부터 2, 1, 2, 1, 2, 2, 1이다. 이 숫자로 O-410/PEN과 O-412/FILE 두 그룹을 찾아 C열 수량을 읽는다. 앞 그룹은 수량까지 같고, 뒤 그룹은 수량이 다르다. 두 경우를 모두 ‘같은 주문 두 건’으로 지우는 대신 어떤 필드에서 차이가 발생했는지 드러내는 용도다.

수량까지 같은 전체 행의 반복 횟수를 보려면 조건을 하나 더 붙인다. 아래 수식에서는 2행과 4행만 2이고 나머지는 1이다. COUNTIFS 공식 문서에 따라 조건 범위의 행 수는 모두 같게 맞춘다. 달러 기호로 검색 범위를 고정했기 때문에 수식을 아래로 채워도 검색 대상은 2~8행에 머물고, 비교할 현재 행만 바뀐다.

=COUNTIFS($A$2:$A$8,A2,$B$2:$B$8,B2,$C$2:$C$8,C2)

O-412의 두 줄이 같은 품목의 수정 이력이라면 어느 값이 최종인지 주문 시스템에서 찾는다. 반대로 같은 상품을 두 항목으로 나눠 주문한 자료라면 상품코드만으로 합칠 수 없다. 주문 항목 번호 같은 구분값을 비교 열에 추가해야 두 항목을 따로 보존한다. 이 예제는 그런 번호가 없으므로 수량 1과 3 중 어느 것이 맞는지 자동으로 판정하지 않는다.

새 행이 들어오는 목록은 표로 유지한다

A2:C8은 지정한 일곱 행만 가리킨다. 다음 주문이 9행에 들어올 때마다 수식 범위를 수동으로 늘리는 일을 줄이려면, 원본 A1:C8을 선택하고 삽입 → 표로 만든다. 머리글이 있음을 확인하고 표 디자인의 표 이름을 Orders로 바꾼다. 그다음 표 밖의 빈 셀에 다음 수식을 입력한다.

=UNIQUE(Orders[[주문번호]:[수량]])

대괄호 안의 이름은 실제 머리글과 같아야 한다. 이 수식은 Orders 표의 주문번호부터 수량까지 연속된 세 열을 참조한다. 표에 데이터 행을 추가하면 구조화된 참조도 함께 조정된다. 표 이름과 구조화된 참조 안내가 이 동작을 설명한다. COUNTIFS 보조 열을 표에 추가했더라도 명시한 수량 열까지만 가져오므로 횟수 열이 중복 판정에 끼어들지 않는다.

원본을 표로 만드는 것과 UNIQUE 결과를 표 안에 넣는 것은 다르다. 여러 셀로 펼쳐지는 동적 배열 수식은 표 안에서 지원되지 않으므로 출력은 표 밖에 둔다. 결과 셀들은 첫 수식에서 함께 만들어진다. 목록의 두 번째 줄만 직접 고치는 대신 원본 데이터를 고치거나, 결과의 맨 왼쪽 위 수식 셀에서 참조 범위를 바꾼다. 배열 결과의 편집 방식에 따른다.

같아 보이는 값과 결과를 펼칠 공간

상품코드가 같아 보이는데 별도 행으로 남으면 앞뒤 공백을 살핀다. 예를 들어 PEN과 끝에 일반 공백이 붙은 PEN 은 원본 문자열이 다르다. 빈 보조 열에 =TRIM(B2)를 넣고 아래로 채워 정리된 값을 비교한다. 원본을 바로 덮어쓰지 않으면 무엇이 달라졌는지 나란히 읽을 수 있다.

TRIM은 앞뒤 일반 공백을 없애고 단어 사이의 연속 공백을 하나로 줄인다. 웹에서 복사한 줄 바꿈 없는 공백, 문자 코드 160은 TRIM만으로 제거되지 않는다. 공식 TRIM 설명에 명시된 한계다. 상품코드의 내부 공백이 구분 의미를 갖는 자료라면 정리된 결과를 승인하기 전에 원래 코드와 대조한다. 모든 공백을 무조건 지우는 치환은 서로 다른 코드를 합칠 수 있다.

#SPILL!은 UNIQUE가 반환할 셀 자리에 다른 값이 있을 때도 발생한다. 오류 셀을 선택하면 펼치려는 범위가 표시되고, 오류 메뉴의 ‘방해하는 셀 선택(Select Obstructing Cells)’으로 막는 셀을 찾을 수 있다. 그 셀이 필요한 데이터라면 지우지 말고 결과 수식을 더 넓은 빈 영역으로 옮긴다. Microsoft의 출력 범위 오류 안내에 나온 절차다.

예제의 여섯 행·세 열 결과를 F2에 넣으려면 F2:H7이 비어 있어야 한다. G4에 메모가 있으면 그 자리에 결과를 덮어쓰지 못한다. 이때 비교 열을 줄여 목록을 억지로 작게 만들면 해결하려던 질문도 바뀐다. 입력 범위는 유지하고 메모나 출력 위치를 옮겨 필요한 결과가 펼쳐지게 한다.

갱신할 수식과 제출할 값

원본에 새 주문을 추가하며 계속 쓸 파일은 UNIQUE 수식을 유지한다. 반면 특정 시점의 확정 목록만 제출한다면 결과 범위 전체를 복사해 새 시트의 빈 영역에 홈 → 붙여넣기 → 값으로 붙인다. 값 붙여넣기는 수식 자체가 아니라 계산 결과를 전달한다. 여섯 행·세 열짜리 결과라면 첫 수식 셀 하나가 아니라 펼쳐진 전체 범위를 복사한다.

값으로 만든 사본은 이후 원본 변경을 따라가지 않는다. 수식을 값으로 바꾸면 수식이 제거되므로 갱신용 파일을 보관하고 제출용 파일명을 따로 정한다. 예제에서는 제출본의 열이 주문번호·상품코드·수량 세 개인지, O-410/BOOK이 남았는지, O-412의 서로 다른 수량을 어떻게 처리했는지 읽는다. 품목 목록인지, 한 번만 등장한 행 목록인지도 수신자가 알 수 있도록 시트 이름에 적는다.

UNIQUE가 없는 Excel 2019 같은 환경에서 원본을 남기고 한 건씩 뽑으려면 Windows 데스크톱의 고급 필터를 쓴다. A1:C8을 선택하고 데이터 → 고급 → 다른 장소에 복사를 고른 뒤, 복사 위치를 빈 셀로 지정하고 ‘고유 레코드만’을 선택한다. 이 결과는 한 번씩만 나타난 행을 고르는 exactly_once와 달리 동일한 행을 하나씩 남긴 목록이다. 원본이 바뀌면 같은 추출 작업을 다시 수행한다.