Excel – 데이터 필터링 – 조건부 서식으로 열 간 일치 값 쉽게 찾기

열 간 일치 값 찾기

조건부 서식 ‘=COUNTIF($C$3:$C$47,B3)>0’ 함수를 활용하면 엑셀에서 서로 다른 열 간의 일치하는 값을 쉽게 찾아 시각적으로 강조할 수 있다.

방대한 데이터를 다루다 보면 두 개의 데이터 목록에서 중복된 항목이나 일치하는 값을 찾아야 할 때가 많다.
이런 작업을 수동으로 진행하면 시간이 많이 소요되고 오류가 발생할 가능성도 높다.
조건부 서식과 COUNTIF 함수를 함께 사용하면 이러한 문제를 간단히 해결할 수 있다.

이 글에서는 조건부 서식에 COUNTIF 함수를 적용하여 열과 열 사이의 일치하는 값을 효과적으로 찾고 강조하는 방법을 자세히 알아보자.

COUNTIF 함수

COUNTIF 함수는 엑셀에서 특정 조건을 만족하는 셀의 개수를 세는 데 사용하는 강력한 함수다.

기본 구문은 =COUNTIF(범위, 조건)으로 이루어진다.
여기서 ‘범위’는 검색할 셀 영역이고, ‘조건’은 개수를 세기 위한 기준이 된다.

예를 들어, =COUNTIF(A1:A10, “사과”)는 A1부터 A10까지의 셀 중에서 “사과”라는 값을 가진 셀의 개수를 반환한다.

COUNTIF 함수는 단순히 특정 값의 개수를 세는 것뿐만 아니라, 비교 연산자를 사용하여 다양한 조건에 맞는 셀의 개수도 셀 수 있다.

조건부 서식 ‘=COUNTIF($C$3:$C$47,B3)>0’ 공식에서는 COUNTIF 함수가 C3부터 C47까지의 범위에서 B3 셀의 값과 일치하는 항목이 있는지 확인하는 데 사용된다.

조건부 서식

조건부 서식은 엑셀에서 특정 조건을 만족하는 셀에 시각적인 서식을 적용하는 기능이다.

이 기능을 사용하면 중요한 데이터나 특정 패턴을 쉽게 식별할 수 있어 데이터 분석과 해석이 훨씬 용이해진다.

조건부 서식은 다음과 같은 요소로 구성된다:

1. 조건

   • 특정 값이나 수식을 기준으로 서식을 적용할지 결정하는 규칙

2. 서식

   조건이 충족될 때 적용할 시각적 형식(색상, 아이콘, 데이터 막대 등)

3. 적용 범위

   • 조건부 서식을 적용할 셀 영역

엑셀에서 조건부 서식을 설정하려면 ‘홈’ 탭에서 ‘조건부 서식’ 버튼을 클릭한 후 원하는 서식 유형을 선택하면 된다.

조건부 서식에는 다양한 유형이 있지만, 이 글에서는 사용자 지정 수식을 활용한 조건부 서식에 초점을 맞추고 있다.

COUNTIF 활용

이제 조건부 서식 ‘=COUNTIF($C$3:$C$47,B3)>0’을 실제로 적용하는 방법을 단계별로 알아보자.

1. 서식을 적용할 셀 범위 선택

   • B열의 데이터 중 C열과 비교하려는 셀 범위(예: B3:B100)를 선택한다

2. 조건부 서식 대화상자 열기

   • 홈 탭 → 스타일 그룹 → 조건부 서식 → 새 규칙을 클릭한다

3. 규칙 유형 선택

   • “수식을 사용하여 서식을 지정할 셀 결정” 옵션을 선택한다

4. 수식 입력

   • 수식 입력란에 ‘=COUNTIF($C$3:$C$47,B3)>0’을 입력한다

5. 서식 지정

   • “서식” 버튼을 클릭하고 조건이 충족될 때 적용할 서식(색상, 글꼴 스타일 등)을 선택한다

6. 확인 버튼 클릭

   • 설정을 완료하고 조건부 서식을 적용한다

이 과정을 완료하면 B열의 각 셀에 대해, 해당 값이 C3:C47 범위 내에 존재하는 경우 지정한 서식이 적용된다.

예를 들어, B5 셀의 값이 “사과”이고 C3:C47 범위 내에 “사과”가 하나라도 있다면, B5 셀은 지정한 서식(예: 배경색 노란색)으로 강조된다.

공식 분석

조건부 서식 ‘=COUNTIF($C$3:$C$47,B3)>0’ 공식을 자세히 분석해보자.

1. $C$3:$C$47

   • 달러 기호($)가 붙은 절대 참조로, 조건부 서식이 다른 셀로 확장되어도 항상 C3부터 C47까지의 범위를 참조한다

2. B3

   • 달러 기호가 없는 상대 참조로, 조건부 서식이 적용된 현재 셀의 값을 나타낸다. 예를 들어 B4 셀에는 B4, B5 셀에는 B5로 자동 변경된다

3. COUNTIF($C$3:$C$47,B3)

   • C3:C47 범위에서 현재 셀(예: B3)의 값과 일치하는 셀의 개수를 계산한다

4. >0

   • COUNTIF 함수의 결과가 0보다 크면(즉, 일치하는 항목이 하나 이상 있으면) TRUE를 반환하고, 그렇지 않으면 FALSE를 반환한다

이 공식은 “현재 셀의 값이 C3:C47 범위 내에 하나 이상 존재하는가?”라는 질문에 답하는 것이다.

만약 B3 셀의 값이 C3:C47 범위 내에 하나라도 있다면, COUNTIF 함수는 0보다 큰 값을 반환하고, 조건부 서식이 적용된다.

이 방식은 두 개의 데이터 목록에서 일치하는 항목을 시각적으로 빠르게 식별하는 데 매우 유용하다.

활용 사례

조건부 서식 ‘=COUNTIF($C$3:$C$47,B3)>0’ 공식은 다양한 실무 상황에서 유용하게 활용될 수 있다.

• 재고 관리

  • 현재 재고 목록(B열)과 주문 목록(C열)을 비교하여 이미 주문된 품목을 강조 표시할 수 있다

• 고객 데이터 분석

  • 잠재 고객 목록(B열)과 기존 고객 목록(C열)을 비교하여 중복된 고객을 식별할 수 있다

• 학생 출석 관리

  • 전체 학생 목록(B열)과 출석한 학생 목록(C열)을 비교하여 출석한 학생을 표시할 수 있다

• 프로젝트 관리

  • 전체 작업 목록(B열)과 완료된 작업 목록(C열)을 비교하여 진행 상황을 시각화할 수 있다

• 판매 데이터 분석

  • 전체 제품 목록(B열)과 판매된 제품 목록(C열)을 비교하여 인기 제품을 파악할 수 있다

예를 들어, 온라인 쇼핑몰에서 전체 상품 목록과 할인 중인 상품 목록을 비교하는 경우를 생각해보자.

=COUNTIF($C$3:$C$47,B3)>0

이 공식을 사용하면 전체 상품 목록에서 현재 할인 중인 상품만 시각적으로 강조할 수 있어, 마케팅 담당자나 고객 서비스 직원이 쉽게 할인 정보를 확인할 수 있다.

추가 팁

조건부 서식 ‘=COUNTIF($C$3:$C$47,B3)>0’을 더 효과적으로 활용하기 위한 팁과 자주 발생하는 문제 해결 방법을 알아보자.

• 대소문자 구분 문제

  • COUNTIF 함수는 기본적으로 대소문자를 구분하지 않는다. 대소문자를 구분해야 하는 경우 EXACT 함수와 SUMPRODUCT 함수를 조합하여 사용한다

• 부분 일치 검색

  • 와일드카드 문자(*, ?)를 사용하여 부분 일치 항목을 찾을 수 있다. 예: =COUNTIF($C$3:$C$47,”*”&B3&”*”)>0

• 여러 조건 조합

  • COUNTIFS 함수를 사용하여 여러 조건을 동시에 적용할 수 있다

• 서식 지우기

  • 조건부 서식을 제거하려면 ‘홈’ 탭 → ‘조건부 서식’ → ‘규칙 지우기’ → ‘선택한 셀에서 규칙 지우기’를 선택한다

• 성능 최적화

  • 대용량 데이터에서는 조건부 서식이 파일 크기를 증가시키고 성능을 저하시킬 수 있다. 필요한 범위에만 적용하는 것이 좋다

조건부 서식 ‘=COUNTIF($C$3:$C$47,B3)>0’이 제대로 작동하지 않는 경우 다음 사항을 확인해보자:

• 참조 범위가 올바른지 확인한다.
• 공백이나 보이지 않는 문자가 포함되어 있는지 확인한다.
• 데이터 형식이 일치하는지 확인한다(텍스트와 숫자는 다르게 처리됨).
• 수식에 오타가 있는지 확인한다.
• 조건부 서식 규칙의 우선순위를 확인한다.

보다 복잡한 비교를 위해서는 다음과 같은 고급 수식을 활용할 수 있다:

=SUMPRODUCT(--EXACT($C$3:$C$47,B3))>0

조건부 서식 ‘=COUNTIF($C$3:$C$47,B3)>0’ 공식은 엑셀에서 서로 다른 데이터 목록 간의 일치하는 값을 시각적으로 강조하는 강력한 도구다.

이 기능을 활용하면 대량의 데이터에서도 빠르게 중복 항목이나 일치하는 값을 찾을 수 있어 업무 효율성을 크게 향상시킬 수 있다.

지금 바로 자신의 엑셀 파일에 이 조건부 서식 기법을 적용해보고, 데이터 분석 능력을 한 단계 업그레이드해보자.

Mingg`s Diary

밍구

공부 목적 블로그

https://maz.kr

모든 포스트 보기