이 글에서는 엑셀에서 와일드카드(Wildcard)를 활용하는 방법을 단계별로 자세히 알아보겠습니다.
별표(*), 물음표(?), 물결표(~) 같은 와일드카드 문자를 활용하면 엑셀에서 검색, 집계, 데이터 분석 기능을 한층 더 강력하게 확장할 수 있습니다. 엑셀의 와일드카드는 AVERAGEIF, SUMIF, COUNTIF 함수는 물론 엑셀의 다양한 데이터베이스 함수와 함께 사용할 수 있습니다.
각 와일드카드 문자의 역할은 다음과 같습니다.
- 별표(*): 개수에 상관없이 임의의 여러 문자를 대체합니다.
- 물음표(?): 정확히 한 글자를 대체합니다.
- 물결표(~): 텍스트 안에 실제로 포함된 별표나 물음표 문자를 찾을 때 사용합니다.
말로만 들으면 잘 와닿지 않으시죠? 간단한 예제와 함께 하나씩 살펴보겠습니다.
아래 예제 파일을 내려받아 직접 실습해 보세요.
엑셀에서 와일드카드를 활용하는 4가지 방법
새내기 부모님들에게 아기 이름 짓기는 설레는 일입니다. 국가별 인기 아기 이름 데이터도 다양하게 공개되어 있는데요. 약 40만 명의 부모를 대상으로 Babycenter가 진행한 조사에 따르면, 2016년 여자 아기 이름 1위는 'Sophia', 남자 아기 이름 1위는 'Jackson'이었습니다.
이번 예제에서는 두 명의 예비 부모가 자신들의 선호와 지인들의 의견을 바탕으로 아기 이름 후보 목록을 만들었다고 가정해 보겠습니다. 딸을 출산을 앞두고, 이들은 준비된 이름 목록에서 원하는 이름을 더 효율적으로 검색하기 위해 와일드카드를 활용하기로 했습니다.
예제에 사용될 원본 데이터는 아래와 같습니다.
방법 1. COUNTIF 함수로 특정 단어로 끝나는 이름 개수 세기
첫 번째 방법은 COUNTIF 함수를 이용해 특정 단어로 끝나는 이름을 찾는 것입니다. COUNTIF 함수는 데이터 범위에서 조건에 맞는 결과가 몇 개인지 그 개수를 반환합니다.
실행 단계:
- 엄마는 'Joanne'처럼 'anne'으로 끝나는 이름의 느낌을 좋아해서, 현재 목록에 해당하는 이름이 몇 개 있는지 확인하고 싶어 합니다. 이때 'anne' 앞에 몇 글자가 오는지 알 수 없으므로 별표(*) 와일드카드를 사용하면 됩니다.
- 따라서 셀 E5에 아래와 같이 별표 와일드카드를 포함한 수식을 입력합니다.
=COUNTIF(B5:B22,"*anne")
- Ctrl + Enter 키를 눌러 수식을 실행합니다.
- 결과값으로 3이 반환되었고, 이는 엄마의 조건에 맞는 이름이 목록에 세 개 있다는 의미입니다.
방법 2. 조건부 서식과 와일드카드로 조건에 맞는 이름 강조하기
COUNTIF로 개수만 확인했다면, 이번에는 조건부 서식을 활용해 조건에 맞는 실제 이름을 화면에서 바로 확인해 보겠습니다. 조건부 서식에 새 규칙을 적용하면 원하는 결과를 자동으로 강조 표시할 수 있습니다.
실행 단계:
- 조건에 맞는 실제 이름을 확인하기 위해 조건부 서식과 와일드카드를 조합하여 해당 이름들을 강조합니다.
- 먼저 데이터 범위를 선택한 뒤 홈 > 스타일 > 조건부 서식 메뉴로 이동합니다.
- 조건부 서식 옆의 드롭다운 화살표를 클릭하고 셀 규칙 강조 > 특정 텍스트 포함을 선택합니다.
- 텍스트 입력란에 아래 그림과 같이 *anne을 입력합니다.
- 확인을 클릭하면 'anne'으로 끝나는 모든 이름이 빨간색으로 강조 표시됩니다.
이번에는 아빠 차례입니다. 아빠는 한 글자만 다른 Isabelle과 Isabella 두 이름을 모두 좋아한다고 가정해 보겠습니다. 이럴 때 물음표(?) 와일드카드가 유용합니다.
- 먼저 기존 조건부 서식을 제거합니다. 홈 > 스타일 > 조건부 서식 > 규칙 지우기 > 전체 시트의 규칙 지우기를 클릭하세요.
- 다시 데이터 범위를 선택하고 홈 > 스타일 > 조건부 서식 > 셀 규칙 강조 > 특정 텍스트 포함으로 이동한 뒤 Isabell?을 입력합니다. 두 이름은 마지막 한 글자만 다르므로 물음표 와일드카드로 두 이름을 동시에 찾을 수 있습니다. 확인을 클릭합니다.
- 그러면 한 글자만 다른 Isabelle과 Isabella 두 이름만 깔끔하게 강조 표시됩니다.
방법 3. COUNTIF 함수로 별표(*)가 포함된 이름 개수 세기
이번에는 할머니·할아버지가 별표(*)로 표시해 둔 선호 이름을 COUNTIF 함수로 찾는 방법을 살펴보겠습니다. 앞서 설명한 과정과 동일한 원리로 작동하며, 단계별로 차근차근 진행해 보겠습니다.
실행 단계:
- 다음 시트에는 조부모님이 선호하는 이름들이 아래 그림처럼 별표(*)로 표시되어 있습니다.
- 목록 중 조부모님이 선호하는 이름이 몇 개인지 세어 보겠습니다. 셀 D5에 아래 수식을 입력합니다.
=COUNTIF(B5:B22,"*~*")
여기서 물결표(~)는 뒤에 오는 두 번째 별표가 와일드카드가 아니라 텍스트에 실제 포함된 문자임을 알려주는 역할을 합니다. 반면 첫 번째 별표는 와일드카드로 작동하여, 실제 별표 문자 앞에 어떤 문자가 몇 개든 올 수 있음을 의미합니다.
- Ctrl + Enter를 누르면 결과값 3이 나옵니다. 즉, 조부모님이 선호하는 이름이 세 개였던 것입니다.
방법 4. 조건부 서식으로 별표(*)가 포함된 이름 찾기
마찬가지로 조건부 서식에 새 규칙을 추가하여 별표가 포함된 이름을 시각적으로 확인할 수 있습니다. 단계별로 살펴보겠습니다.
실행 단계:
- 조건부 서식으로 실제 이름을 확인하기 위해 데이터 범위를 다시 선택합니다. 홈 > 스타일 > 조건부 서식 > 셀 규칙 강조 > 특정 텍스트 포함으로 이동한 뒤 *~*을 입력하고 확인을 클릭합니다.
- 그러면 조부모님이 실제 별표 문자로 표시해 둔 세 개의 이름이 아래와 같이 강조 표시됩니다.
필터 기능에서 와일드카드 활용하기
와일드카드는 필터 기능에서도 손쉽게 활용할 수 있습니다. 전체 데이터를 필터링하면 원하는 결과만 골라낼 수 있습니다. 필터에 와일드카드를 적용하는 방법을 살펴보겠습니다.
실행 단계:
- 먼저 전체 데이터 범위를 선택합니다.
- 상단 리본 메뉴에서 데이터 탭으로 이동합니다.
- 정렬 및 필터 그룹에서 필터 옵션을 찾아 클릭합니다.
- 데이터 제목 행의 오른쪽 위에 필터 아이콘이 생성됩니다. 해당 아이콘을 클릭합니다.
- 아래 그림과 같은 필터 창이 열립니다.
- 검색 상자에 필터링하고 싶은 조건(와일드카드 포함)을 입력한 뒤 확인 버튼을 클릭합니다.
- 마지막으로 필터링된 결과를 확인할 수 있습니다.
찾기 및 바꾸기에서 와일드카드 활용하기
엑셀의 찾기 및 바꾸기 기능에서도 와일드카드를 활용하면 몇 번의 클릭만으로 데이터를 빠르게 수정할 수 있습니다.
실행 단계:
- 먼저 Ctrl + H 키를 누릅니다. 아래와 같은 팝업 창이 열립니다.
- 'Olivia'를 'Watson'으로 바꾸려면 찾을 내용 상자에 Olivia를 입력합니다.
- 그다음 바꿀 내용 상자에 Watson을 입력합니다.
- 이후 바꾸기 버튼을 클릭합니다.
- Olivia라는 이름이 Watson으로 모두 변경된 것을 확인할 수 있습니다.
엑셀 와일드카드가 작동하지 않을 때 점검 사항
와일드카드가 제대로 작동하지 않는다면 데이터에 잘못된 문자가 섞여 있는지 먼저 확인하세요. 텍스트 값을 집계할 때는 데이터에 불필요한 앞뒤 공백, 곧은 따옴표와 굽은 따옴표('' vs ")의 혼용, 보이지 않는 특수문자 등이 없어야 합니다. 이런 요소들이 남아 있으면 COUNTIF 함수가 예상과 다른 결과를 반환할 수 있습니다.
마무리
와일드카드를 활용하면 엑셀 함수의 활용 범위가 크게 넓어집니다. 부분 일치 검색은 물론, 부분적인 조건 충족까지 가능해져 데이터 관리가 한결 수월해집니다.
여러분은 어떤 상황에서 와일드카드를 활용하시나요? 검색 효율을 높여 준 와일드카드 조합이나, 거주 지역의 인기 아기 이름 등 다양한 의견을 댓글로 공유해 주세요.
함께 읽으면 좋은 글
- 엑셀에서 와일드카드로 값 찾기 및 바꾸기
- 엑셀 INDEX MATCH 다중 조건 + 와일드카드 완벽 가이드
- 엑셀 고급 필터 [다중 열·조건, 수식 활용, 와일드카드]