데이터를 정리하다 보면 중복값을 확인하고 제거해야 하는 일이 생각보다 많이 있습니다.
그런데 데이터 열이 1,000개가 넘어가면 중복값을 어떻게 찾아야 되나 막막한 경우가 많이 있습니다.
스프레드시트 내에서도 간단하게 중복값을 정리할 수 있는 기능이 있긴 합니다.
1) 중복값이 들어있는 범위 선택
2) Data - Data cleanup - Remove duplicates 클릭 후 확인
하지만 위와 같이 할 경우, 어떤 값이 중복인지 알려주지 않고 바로 삭제해 버립니다.
중복값을 지우고 싶어서 표시하는 경우도 있지만, 원하는 값이 포함되어 있는 셀을 표시하고 싶은 경우도 많이 있습니다.
그럴 때 유용하게 사용할 수 있는 기능인 조건부 서식을 소개해드리고자 합니다.
조건부서식: 중복값 표시하기
1) 제일 먼저 조건부서식을 적용할 부분을 선택합니다.
전체 데이터 테이블에 모두 적용하고 싶으므로, [Ctrl + a] or [Cmd + a]를 눌러주세요.
2) 그다음 Format - Conditional formatting을 클릭합니다.
3) 아래와 같이 기본값이 설정되어 있을 텐데요.
Format rules - Format cells if... 에 있는 [Is not empty]를 선택하고, 최하단에 있는 [Custom formula is]를 클릭해 줍니다.
4) 하단 Value or formula에 아래와 같은 수식을 입력해 줍니다.
=countif($E$1:$E$101,$E1)>1
어떤 중복값을 구하고 싶은지에 따라 수식은 달라지는데, 저는 [Units Sold]에서 중복값이 있는지 알고 싶어서 위와 같이 식을 적었습니다.
다른 글에서 설명드린 countif 함수를 활용했으며, 함수에 대해서 조금 더 설명드리겠습니다.
- countif 함수는 범위 내에서 조건에 해당하는 셀의 개수를 구하고 싶을 때 사용합니다.
- =countif($E$1:$E$101,$E1)>1
- $E1:$E101는 조건 범위를 의미합니다. 범위 절대값($)을 적용했기 때문에, 범위 전체에 조건부 서식이 적용됩니다.
- $E1는 조건을 의미합니다. E행에 절대값($)을 적용해서, E행 내 값에 따라 조건부 서식이 적용됩니다.
- 범위 내에서 해당하는 값이 1개만 있을 경우, countif=1로 계산되게 됩니다.
- 중복 데이터가 있는 경우, 당연히 2 이상으로 표시될 테니 [>1]의 조건을 걸어서 중복값을 걸러낼 수 있게 됩니다.
5) 마지막으로 Formatting style 부분에서 원하는 서식을 적용할 수 있습니다.
6) Done을 누르면 적용 완료!
7) 제대로 표시가 된 건지 확인해 보기 위해서, 셀 전체 선택 후 [우클릭 - View more cell actions - Sort range]를 클릭합니다.
8) 첫 째줄에 타이틀 헤더가 있으므로 [Data has header row]를 클릭하고, [Units Sold] 선택 후 Sort를 클릭합니다.
9) 아래와 같이 Units Sold 순으로 정렬되어서, 중복되는 값을 편하게 확인해 볼 수 있습니다.
조건부서식: 원하는 값이 포함된 셀 표시하기
1) 위와 동일하게 제일 먼저 조건부서식을 적용할 부분을 선택합니다.
전체 데이터 테이블에 모두 적용하고 싶으므로, [Ctrl + a] or [Cmd + a]를 눌러주세요.
2) 그다음 Format - Conditional formatting을 클릭합니다.
3) 이전에 등록한 조건부 서식이 있으므로, [+ Add another rule]을 클릭합니다.
3) Format rules - Format cells if... 에 있는 [Is not empty]를 선택하고, 최하단에 있는 [Custom formula is]를 클릭해 줍니다.
4) 하단 Value에 아래 수식을 입력해 줍니다.
=$B1="Product C"
원하는 값에 따라 수식은 달라지는데, 저는 [Product]에서 Product C가 포함된 열을 알고 싶어서 위와 같이 식을 적었습니다.
위 수식의 경우에 B행은 고정으로 하기에 B 앞에 절대값이 붙었고, Product C는 텍스트로 인지해야 하기 때문에 "Product C"로 표시했습니다.
추가로 Formatting style도 설정하고 완료를 하면 됩니다.
5) 거의 다 완료가 된 것처럼 보이지만, 2행 3행을 보면 Product C가 있는데도 서식이 적용 안된 모습이 보입니다.
이럴 경우 원하는 수식에 마우스 커서를 갖다 놓으면 좌측에 .... 표시가 생기는데 드래그한 채로 위에서 놓으면 변경이 가능합니다.
6) 변경할 경우, 아래와 같이 Product C의 조건이 우선시 되어서 적용된 모습을 확인할 수 있습니다.
조건부 서식을 잘만 활용하면 매번 서식을 변경할 필요 없이 자동으로 표시되어 아주 편리하게 사용할 수 있습니다!
개인적으로는 월말마다 스프레드시트로 가계부를 만들어서 관리하고 있는데, 조건부서식을 활용해서 입금 출금 내역을 분리하여 표시하여 편리하게 사용하고 있습니다 :)
직접 따라 해보시고 궁금한 점이 있다면 편하게 댓글 남겨 주세요!
'엑셀 | Spreadsheet' 카테고리의 다른 글
| 데이터에서 원하는 텍스트 합치기 함수 (0) | 2024.08.02 |
|---|---|
| 원하는 조건을 마음대로 적용할 수 있는 if / ifs 함수 (0) | 2024.08.01 |
| 원하는 조건의 합계 구하기 sumif 함수 (2) | 2024.07.29 |
| 쉽고 빠르게 개수를 셀 수 있는 count 함수 (2) | 2024.07.24 |
| 특정 조건에 일치하는 데이터 찾기 xlookup 함수 (vlookup 보다 편한) (2) | 2024.07.23 |