안녕하세요, 스마트라이온입니다. 오늘은 “COUNTIF” 함수와 조건부 서식에 대해 알아보도록 하겠습니다. 번역 프로젝트를 진행하다 보면 엑셀 파일에서 중복되는 값을 골라내야 할 일이 많습니다. 중복되는 내용을 파악해 번역할 총 분량(net words)을 파악하거나 특히 용어집 등을 작성/수정할 때에는 중복되는 내용을 정리해 주는 것이 필요합니다. 그럴 때 이 두 가지 기능을 이용하면 작업을 조금 더 빠르고 정확하게 할 수 있습니다. 먼저 COUNTIF 함수가 어떤 계산을 해 주는지 알아봅시다.
COUNTIF 함수란?
특정 조건을 만족하는 셀 개수를 구해 주는 함수입니다.
=COUNTIF(검색 범위, 검색 조건)
위 수식으로 사용할 수 있습니다. 검색 조건에 문자열을 넣으실 때에는 반드시 큰따옴표(“”)로 묶어 주셔야 함수가 제대로 작동합니다.
사용 예시
위 예시는 COUNTIF 함수를 사용해 A열에서(A:A) abcd와 일치하는 셀(“abcd”)의 개수를 센 결과입니다. 위 예시의 A8셀을 보시면 “abcd”ef는 COUNTIF로 세어지지 않습니다. 기본적인 COUNTIF 함수로 셀 수 있는 셀은 문자열이 검색 조건과 완전히 일치하는 셀뿐입니다.
와일드카드 사용으로 COUNTIF 함수 더 편하게 사용하기
그래서 “특정 문자열을 포함하는” 셀을 검색하고자 할 때에는 “와일드카드”를 사용합니다. 와일드카드에는 크게 두 종류가 있습니다. *는 개수 제한 없이 모든 문자에 대응하며 ?는 ?의 개수만큼 모든 문자에 대응한다고 보시면 될 것 같습니다. 실제로 와일드카드를 사용한 예시를 보시겠습니다.
- *LION: “LION”으로 끝나는 모든 텍스트를 검색합니다(LION 앞에 개수 제한 없이 어떤 문자가 추가된 모든 텍스트).
▶ LION, sLION, SmartLION, smartlion, 3923LION
- *LION*: “LION”을 포함하는 모든 텍스트를 검색합니다(LION 앞과 뒤에 개수 제한 없이 어떤 문자가 추가된 모든 텍스트).
▶ LION, sLION, SmartLION, smartlion, 3923LION, LION12049, LIONL10N, LION43d20
- ?LION: “LION”앞에 한 글자가 있는 텍스트를 모두 검색합니다.
▶ sLION
- LION????: “LION” 뒤에 네 글자가 있는 텍스트를 모두 검색합니다.
▶ LIONL10N, LION4320
조건부 서식 이용하기
COUNTIF 함수에 대해 알아보았으니 이번에는 조건부 서식에 대해 알아보도록 하겠습니다. 조건부 서식은 특정 조건을 만족하는 셀에 서식을 적용해 주는 기능입니다. COUNTIF가 조건에 해당하는 셀 개수를 세 주는 함수라면 조건부 서식은 조건에 해당하는 셀을 표시하는 데 사용하는 기능입니다. Office365 Excel 기준으로 홈 리본 메뉴에서 바로 사용할 수 있습니다.
홈 리본 메뉴에서 조건부 서식 클릭
메뉴를 보시면 다양한 옵션이 있는데, 이 중 자주 사용하게 되는 것은 [셀 강조 규칙]의 “같음”, “텍스트 포함”, “중복 값”입니다. COUNTIF 함수와 다르게 메뉴 이름으로 해당 옵션이 어떤 역할을 하는지 직관적으로 알 수 있어 더 간단하게 사용할 수 있습니다. 그럼 실제로 조건부 서식을 사용해 보겠습니다.
먼저, 검색할 내용이 있는 범위를 선택하고(위 스크린샷에서는 A열 전체를 선택했습니다), [조건부 서식]→[셀 강조 규칙]→[텍스트 포함] 순서로 옵션을 선택합니다.
그러면 위와 같은 창이 표시됩니다. 왼쪽 텍스트 상자에 원하는 텍스트를 넣으면 해당 텍스트가 포함된 셀에 오른쪽 서식을 적용해 줍니다. 드롭다운 박스에서 프리셋 서식을 선택할 수도 있고, 사용자 지정으로 임의의 서식을 만들어 적용할 수도 있습니다. 위 스크린샷에서 “Smart”라는 텍스트가 포함된 셀 두 개(A3, A4)에 서식이 적용된 것을 확인할 수 있습니다.
조건부 서식과 COUNTIF 함수를 사용해서 다른 시트의 중복값 찾기
이제는 대망의 응용편입니다. 검색할 범위를 지정하고 조건부 서식을 사용해 중복값에만 강조 표시를 하는 법은 간단하지요. 하지만 시트가 여러 개 있고 다른 시트까지 참조 범위로 해서 중복값을 찾으려면 어떻게 할까요? 지금까지 알아본 COUNTIF 함수와 조건부 서식을 사용하면 이것도 간단하게 해결할 수 있습니다.
그럼 다음 문서를 예시로 Sheet3의 A열 항목 중 Sheet1의 A열 또는 Sheet2의 B열에 중복되는 값이 있을 경우 노란색으로 표시해 보도록 하겠습니다.
먼저 Sheet3의 A열을 선택하고, [조건부 서식]>[셀 강조 규칙]에서 [기타 규칙…]을 클릭합니다.
그러면 [새 서식 규칙] 창이 표시되는데, 목록 맨 마지막에 있는 [수식을 사용하여 서식을 지정할 셀 결정]을 선택해 봅시다.
미리 보기 오른쪽에 있는 [서식] 버튼을 클릭하고, 셀 서식을 지정합니다. 이번 예제에서는 노란 배경을 선택해 보겠습니다. 셀 서식 지정이 끝나면 [확인]을 누릅니다.
서식을 지정한 후, 규칙 설명 텍스트 상자에는 다음 수식을 입력합니다.
=COUNTIF(Sheet1!$A:$A,$A1)>0
이 과정까지 끝내면 위쪽 스크린샷과 같은 모습이 됩니다. 이 상태에서 [확인]을 누르면…
이렇게, Sheet1의 A열에 있던 값이 있는 셀이 노란색 배경으로 바뀌는 것을 확인할 수 있습니다. 수식만 =COUNTIF(Sheet2!$B:$B,$A1)>0으로 변경하여 같은 과정을 반복해 주시면
Sheet2의 B열에 있던 값 16도 노란색 배경으로 변했습니다. 서식이나 범위를 자유롭게 조정할 수 있으니 다양한 방법으로 응용하면 더 쉽고 편하게 중복값을 찾을 수 있겠습니다. 조건부 서식은 셀 자체의 속성을 바꾸는 것이 아니라 해당 서식으로 표시만 해 주는 것이기 때문에 표시했던 셀의 서식을 지우려면 [조건부 서식]>[규칙 지우기]로 규칙을 없애 주어야 합니다. 수식을 잘못 입력했거나 서식을 수정하고 싶다면 맨 아래쪽 [규칙 관리] 메뉴를 사용하시면 됩니다.
이렇게 중복값들을 표시하고 나면 적용한 서식을 기준으로 필터를 적용해 중복값만 모아서 볼 수도 있습니다. 중복되는 내용을 하나하나 찾아서 가공하는 것보다 훨씬 빠르고, 중복된 값이 사라지면 자동으로 서식도 해제되기 때문에 작업이 얼마나 진행되었는지도 알 수 있어서 익혀 두면 여러 모로 사용이 가능해 정말 편리한 기능입니다.
