이 튜토리얼에서는 엑셀에서 목록에 있는 텍스트가 포함된 셀을 강조 표시하는 방법을 소개합니다. 셀 강조에는 조건부 서식(Conditional Formatting)을 활용하며, 여러 엑셀 함수와 수식을 조건부 서식 규칙에 적용하는 다양한 방법을 단계별로 설명합니다.
본문에서 사용한 연습용 워크북 파일을 다운로드하여 직접 따라 해볼 수도 있습니다.
목록의 텍스트가 포함된 셀을 강조 표시하는 7가지 방법
1. COUNTIF 함수를 활용한 셀 강조 표시
COUNTIF 함수를 사용하면 목록에 있는 텍스트가 포함된 셀을 손쉽게 강조할 수 있습니다. 예를 들어 Fruit 1(B4:B14) 범위에 여러 과일 이름이 있고, 다른 목록인 Fruit 2(D4:D9)에도 과일 이름이 있다고 가정해 보겠습니다. 이제 Fruit 2 목록의 과일 이름이 Fruit 1 목록에서 해당되는 셀을 강조 표시해 보겠습니다.
단계:
- 먼저 데이터 범위(B5:B14)를 선택합니다.
- 홈 탭에서 조건부 서식(스타일 그룹)으로 이동합니다.
- 조건부 서식 드롭다운 메뉴에서 새 규칙을 선택합니다.
- 새 서식 규칙 창이 나타나면 규칙 유형에서 수식을 사용하여 서식을 지정할 셀 결정을 선택하고, 다음 수식이 참인 값의 서식 지정 입력란에 아래 수식을 입력한 뒤 서식 버튼을 클릭합니다.
=COUNTIF($D$5:$D$9,B5)
- 채우기 탭에서 원하는 강조 색상을 선택하고 확인을 클릭합니다.
- 대화 상자를 닫기 위해 한 번 더 확인을 클릭합니다.
- 이제 Fruit 2 목록에 있는 텍스트가 포함된 모든 셀이 강조 표시된 것을 확인할 수 있습니다.
2. MATCH 함수를 활용한 셀 강조 표시
이번에는 MATCH 함수를 사용하여 목록의 텍스트가 포함된 셀을 강조 표시해 보겠습니다.
단계:
- 먼저 데이터 범위(B5:C14)를 선택합니다.
- 홈 > 조건부 서식 > 새 규칙으로 이동합니다.
- 새 서식 규칙 창이 열리면 수식을 사용하여 서식을 지정할 셀 결정을 선택하고 아래 수식을 입력합니다. 그런 다음 서식 버튼을 클릭하고 채우기 탭에서 강조 색상을 지정합니다.
=MATCH($B5,$E$5:$E$9,0)- 확인 > 확인을 클릭하여 대화 상자를 닫습니다.
- 그 결과 Fruit 2 목록의 텍스트가 포함된 셀들이 Fruit 1 목록에서 강조 표시됩니다.
3. SUM과 COUNTIF 함수 조합을 활용한 셀 강조 표시
이번에는 여러 함수를 조합하여 다른 목록의 텍스트가 포함된 셀을 강조해 보겠습니다. 예를 들어 SUM 함수와 COUNTIF 함수를 함께 사용합니다. 여기서는 도서 이름이 담긴 데이터 집합 Books(B4:B12)와 개별 단어 목록인 Text(D5:D8)이 있습니다. Books 데이터 중 Text 목록의 단어가 포함된 셀을 강조 표시하겠습니다.
단계:
- 먼저 데이터 범위(B5:B12)를 선택합니다.
- 홈 탭에서 조건부 서식 드롭다운을 선택합니다.
- 드롭다운 메뉴에서 새 규칙을 클릭합니다.
- 새 서식 규칙 창이 나타나면 수식을 사용하여 서식을 지정할 셀 결정을 선택하고 아래 수식을 입력한 뒤, 서식 버튼을 눌러 채우기 탭에서 강조 색상을 지정합니다.
=SUM(COUNTIF(B5,"*"&$D$5:$D$8&"*"))- 확인 > 확인을 클릭하여 대화 상자를 닫습니다.
- 마지막으로 Text 목록의 텍스트가 포함된 셀들이 Books 데이터에서 강조 표시됩니다.
여기서 COUNTIF 함수는 주어진 조건을 만족하는 범위 내 셀의 개수를 반환하고, SUM 함수는 해당 숫자들을 합산합니다. 와일드카드(*)를 활용하므로 셀 전체가 아니라 부분적으로 단어가 포함된 경우에도 감지할 수 있다는 점이 특징입니다.
4. COUNT와 SEARCH 함수 조합을 활용한 셀 강조 표시
방법 3과 마찬가지로 함수 조합을 사용합니다. 이번에는 COUNT 함수와 SEARCH 함수를 결합하여 다른 목록의 텍스트가 포함된 셀을 강조해 보겠습니다. 앞서와 동일하게 두 개의 과일 이름 목록을 데이터 집합으로 사용합니다.
단계:
- 먼저 데이터 범위(B5:B14)를 선택합니다.
- 홈 > 조건부 서식 > 새 규칙으로 이동합니다.
- 새 서식 규칙 대화 상자가 나타나면 수식을 사용하여 서식을 지정할 셀 결정을 선택하고 아래 수식을 입력합니다.
=COUNT(SEARCH(D$5:D$9,B5))- 서식 버튼을 클릭해 채우기 탭에서 강조 색상을 선택한 뒤, 확인 > 확인을 눌러 대화 상자를 닫습니다.
- 그 결과 Fruit 2 목록의 과일 이름이 포함된 셀들이 Fruit 1에서 강조 표시됩니다.
여기서 SEARCH 함수는 특정 문자열이 처음 발견되는 위치(왼쪽부터 오른쪽으로 읽음)를 반환하고, COUNT 함수는 범위에서 숫자가 포함된 셀의 개수를 계산합니다.
5. 간단한 수식과 드롭다운 목록을 활용한 셀 강조 표시
이번에는 간단한 수식과 드롭다운 목록을 함께 사용해 보겠습니다. 먼저 드롭다운 목록을 만든 후, 조건부 서식 규칙에 수식을 적용합니다. 역시 두 개의 과일 이름 목록을 데이터 집합으로 사용합니다.
단계:
- 먼저 셀 D5에 과일 이름으로 구성된 드롭다운 목록을 생성합니다.
- 데이터 범위(B5:B14)를 선택합니다.
- 홈 > 조건부 서식으로 이동합니다.
- 드롭다운에서 새 규칙을 선택합니다.
- 새 서식 규칙 창에서 수식을 사용하여 서식을 지정할 셀 결정을 고르고 아래 수식을 입력한 후, 서식 버튼을 눌러 채우기 탭에서 색상을 지정합니다.
=$B5=$D$5- 확인 > 확인을 클릭하여 대화 상자를 닫습니다.
- 드롭다운 목록의 과일 이름이 포함된 셀들이 강조 표시됩니다.
- 이 방법은 동적으로 작동합니다. 즉, 드롭다운 목록에서 과일 이름을 변경하면 강조 표시되는 셀도 자동으로 바뀝니다.
6. ISNUMBER와 FIND 함수를 활용한 셀 강조 표시
이번에는 함수 조합과 드롭다운 목록을 함께 사용합니다. ISNUMBER 함수와 FIND 함수를 결합하여 다른 목록의 텍스트가 포함된 셀을 강조해 보겠습니다.
단계:
- 먼저 셀 D5에 과일 이름 드롭다운 목록을 만듭니다.
- 데이터 범위(B5:B14)를 선택합니다.
- 홈 > 조건부 서식 > 새 규칙으로 이동합니다.
- 새 서식 규칙 창에서 수식을 사용하여 서식을 지정할 셀 결정을 선택하고 아래 수식을 입력합니다. 이후 서식 버튼을 눌러 채우기 탭에서 강조 색상을 지정합니다.
=ISNUMBER(FIND($D$5,B5))- 색상을 지정한 후 확인을 클릭하고, 다시 확인을 눌러 대화 상자를 닫습니다.
- 목록의 텍스트가 포함된 셀들이 강조 표시됩니다.
여기서 FIND 함수는 한 문자열이 다른 문자열 내에서 시작되는 위치를 반환합니다(대소문자 구분). 이후 ISNUMBER 함수는 값이 숫자인지 확인하여 TRUE 또는 FALSE를 반환합니다.
7. SUMPRODUCT, ISNUMBER, SEARCH 함수 조합을 활용한 셀 강조 표시
마지막 방법은 SUMPRODUCT 함수와 ISNUMBER 함수, SEARCH 함수를 함께 사용하는 것입니다. Books(B4:B12) 목록의 도서 이름 중 Text(B5:B8) 목록의 단어가 포함된 셀을 강조해 보겠습니다.
단계:
- 먼저 데이터 범위(B5:B12)를 선택합니다.
- 홈 > 조건부 서식 드롭다운으로 이동합니다.
- 드롭다운에서 새 규칙을 선택합니다.
- 새 서식 규칙 대화 상자가 나타나면 수식을 사용하여 서식을 지정할 셀 결정을 선택하고 아래 수식을 입력합니다. 그런 다음 서식 버튼을 클릭해 채우기 탭에서 강조 색상을 지정합니다.
=SUMPRODUCT(--ISNUMBER(SEARCH($D$5:$D$8,B5)))>0- 확인 > 확인을 클릭하여 대화 상자를 닫습니다.
- 마지막으로 Text 목록의 텍스트가 포함된 도서 이름 셀들이 강조 표시됩니다.
🔎 수식 작동 원리 살펴보기
➤ SEARCH($D$5:$D$8,B5)
수식의 이 부분은 다음 결과를 반환합니다.
{#VALUE!;#VALUE!;1;5}
SEARCH 함수는 값의 위치를 반환하지만, 값을 찾지 못하면 오류(#VALUE!)를 반환합니다.
➤ ISNUMBER(SEARCH($D$5:$D$8,B5))
ISNUMBER 함수는 오류 결과는 모두 FALSE로, 정상 결과는 TRUE로 변환합니다.
이 부분의 결과는 다음과 같습니다.
{FALSE;FALSE;TRUE;TRUE}
➤ --ISNUMBER(SEARCH($D$5:$D$8,B5))
이 부분은 다음을 반환합니다.
{0;0;1;1}}
이중 마이너스(--) 기호는 모든 FALSE와 TRUE 값을 각각 0과 1로 변환합니다.
➤ SUMPRODUCT(--ISNUMBER(SEARCH($D$5:$D$8,B5)))>0
이 부분의 결과는 다음과 같습니다.
{TRUE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;TRUE}
마지막으로 SUMPRODUCT 함수는 이전 결과를 0과 비교하여, 조건부 서식 규칙이 해당 셀들을 강조 표시하도록 안내합니다.
결론
지금까지 목록에 있는 텍스트가 포함된 셀을 강조 표시하는 다양한 방법을 자세히 살펴보았습니다. COUNTIF, MATCH, SEARCH 등 익숙한 함수부터 SUMPRODUCT를 활용한 복잡한 수식까지 상황에 맞게 선택하여 활용하시기 바랍니다. 이 방법들과 설명이 문제 해결에 충분히 도움이 되기를 바랍니다. 궁금한 점이 있다면 언제든지 문의해 주세요.
관련 글
- 엑셀 셀 색상 추가, 편집, 활용 및 제거 방법
- 엑셀 수식으로 셀에 색상 채우기 (5가지 쉬운 방법)
- 엑셀에서 행 강조 표시하기 (5가지 빠른 방법)
- 값에 따라 셀을 강조하는 엑셀 VBA (예제 5가지)
- 값 기반으로 셀 색상을 변경하는 VBA (간단한 예제 3가지)