엑셀 데이터를 입력하거나 수식에 의한 결과값이 중복되지 않고 1개만 나오는 경우도 있지만 동일한 데이터가 여러개 작성되는 경우도 있습니다.
이번 글에서는 동일한 값, 중복 값을 찾아서 중복값을 한번만 입력되도록 반환하는 수식을 작성해 보도록 하겠습니다.
엑셀 중복값 찾기, 중복값 제거.
위 예제 데이터를 보면 A열에 날짜, B열에 금액이 입력된 데이터에서 오른쪽표에 날짜별 금액 합계로 정리하려고 합니다.
이때 A열에 입력된 날짜는 중복으로 입력된 날짜가 있을 수 있으니 예제에서는 “2023-11-01″의 날짜가 2번 입력되어 있습니다.
따라서 날짜별 금액 합계표로 정리하고자 할때에는 중복으로 입력된 날짜를 찾아서 중복되는값을 1개만 남기고 나머지 데이터는 제거하도록 합니다.
중복값 제거 – 중복된 항목 제거 기능 사용.
중복된값을 제거하고 1개의 데이터값만 남기는 방법으로 가장 간단한 방법은 Excel에서 기본으로 제공하고 있는 “중복된 항목 제거 기능”으로 중복값을 제거할 수 있습니다.
“중복된 항목 제거 기능”은 다음의 순서에 따라 메뉴를 찾을 수 있습니다.
- 데이터탭 → 중복된 항목 제거
A열에 입력된 날짜 데이터 A2~A5셀까지 복사하여 D열에 복사한 후 “중복된 항목 제거” 메뉴를 클릭하게 되면 “2023-11-01″의 중복된값중 1개의 값만 남기고 나머지 중복값은 즉시 제거할 수 있습니다.
중복값 제거 – 수식으로 결과값 반환.
중복값을 제거하고 1개의 데이터값만 결과값으로 반환하는 방법은 “중복된 항목 제거” 기능을 사용하지 않고 수식을 작성하여 결과값으로 반환할수도 있습니다.
수식을 작성하면 다음과 같이 작성 할 수 있습니다.
| {=IFERROR(OFFSET($A$1,MIN(IF(($A$2:$A$1000<>””)*(COUNTIF(D$1:D1,$A$2:$A$1000)=0),ROW($A$2:$A$1000)))-1,),””)} |
위 수식은 배열수식으로 일반적인 수식과 다르게 수식의 맨 앞,뒤에 중괄호({,})가 들어가 있는것을 볼 수 있는데, 배열수식 기호는 직접 작성하는것이 아니고 Ctrl+Shift+Enter키로 수식을 완료해주면 배열수식으로 작성되어 중괄호 기호가 따라 붙게 됩니다.
해당수식을 D2셀에 작성했다면, 수식의 결과값이 반환되어야하는 셀 위치까지 수식을 복사합니다.
만약, 반환한 결과값이 숫자형식으로 일련번호 형식으로 반환된다면 표시형식을 날짜로 수정해주고, Ctrl+C, Ctrl+V 단축키로 수식을 복사해주는데 배열수식…과 같은 메시지와 함께 수식 복사가 되지 않을 경우에는 수식을 입력한 셀로부터 수식이 입력되어야 하는 셀 위치까지 드래그 후에 Ctrl+D 단축키로 수식을 복사할 수 있습니다.
Ctrl+Shift+Enter로 수식을 작성하면 배열수식으로 수식을 작성할 수 있지만 Excel 2021버전에서는 배열수식을 자동으로 인식하여 Enter키만으로도 배열수식이 완료되는것으로 미루어 보아 Excel 상급 버전일수록 소소한 편리성이 추가되고 있는것 같으니 참고하시면 좋겠습니다.
단, 과도한 배열수식의 작성으로 엑셀이 느려질 수 있습니다.
| 엑셀 느려졌을때 시도해 볼 수 있는 몇가지 방법. |
글을 마치며…[행아아빠 생각]
본문에서 중복된 항목을 제거하는 방법으로 Excel 기본 메뉴를 활용한 방법, 수식을 작성한 방법 2가지를 확인해 봤는데 사용자 본인이 더 편리하게 사용할 수 있는 방법이 정답이 아닐까 생각합니다.
다만 수식을 작성한 서식의 경우에는 중복된 항목을 자동으로 체크하고 하나의 결과값으로 반환되기 때문에 업무 효율 증대의 측면에서는 반복되는 문서 서식의 경우 수식을 작성해서 사용하는것이 좋을것입니다.
그러나 일회성 문서 서식, 단일 문서 서식의 경우 수식의 작성이 어려운 경우 “중복된 항목 제거” 메뉴를 활용했을때 업무 효율이 증대되지 않을까 싶습니다.
