엑셀 와일드카드 사용법 및 실전예제 총정리
엑셀 와일드카드 사용법 목차 바로가기
요약
와일드카드는 텍스트를 검색할 때 정확히 일치하는 값이 아닌 유사 값을 검색할 때 사용하는 기호입니다.
엑셀 필터 기능과 와일드카드의 기본 사용법은 아래 5분 영상 강의에서 자세히 다루었습니다.
설명
엑셀 와일드카드는 검색하는 단어에 불분명한 문자열을 임의 문자열로 대체하여 검색할 때 사용하는 기호입니다. 실무에서는 주로 특정 조건을 만족하는 여러개의 값을 검색할 때 사용됩니다. 엑셀에는 3개의 와일드카드가 사용됩니다.
- 별표(*) : 글자수에 상관없이 모든 문자를 대체합니다.
"김*"으로 검색하면 김으로 시작하는 모든문자를 검색합니다.
"김철, 김철수, 김마리아" 모두 반환됩니다.
- 물음표(?) : 문자 1개를 대체합니다.
"김?"으로 검색하면 김으로 시작하는 두 글자 문자를 검색합니다.
"김철, 김민" 이 반환됩니다.
- 물결표(~) : 와일드카드 앞에 사용합니다. 와일드카드 기능을 없애고 와일드카드 문자(*, ?) 그대로를 검색합니다.
"김~?"으로 검색하면 "김?"이 검색됩니다.
와일드카드는 COUNTIF함수, SUMIF함수, VLOOKUP함수 등의 통계함수 및 참조함수의 조건문으로 사용할 수 있습니다. 이번 포스트에서는 와일드카드를 사용하는 3가지 사용예제를 알아봅니다.
예제파일 다운로드
오빠두엑셀의 강의 예제파일은 여러분을 위해 자유롭게 제공하고 있습니다.
- [엑셀사전] 엑셀 와일드카드 사용법 알아보기예제파일
관련 엑셀 기초함수
사용예제
와일드카드로 부분일치 문자열을 검색합니다. 와일드카드 자세히 알아보기
와일드카드 종류
* (별표) 개수에 상관없는 문자열을 대체합니다. ? (물음표) 1개의 문자열을 대체합니다. ~ (물결표) 물결표 뒤에 ?, *, ~ 을 사용할 수 있습니다. 와일드카드 앞에 물결표를 사용하면 와일드카드로서의 기능을 상실하고 *(별표), ?(물음표), ~(물결표) 문자 그대로 검색이 됩니다. 기본예시
3학년 1반 이름 목록 김수미 김영민 김영미 김소나미 이수지 이서지 김영수아 "김?미" "김수미", "김영미" 를 출력합니다. "김*미" "김수미", "김영미", "김소나미" 를 출력합니다. "김영?" "김영민", "김영미" 를 출력합니다. "이?" 아무 값도 출력하지 않습니다. "이*" "이수지", "이서지"를 출력합니다. 와일드카드 고급 예시
와일드카드 예제 설명 결과값 ? 글자수 1개인 문자 가, 나, 다, … ?? 글자수가 2개인 문자열 가지, 나비, 다리, … ??? 글자수가 3개인 문자열 가로수, 나침반, 다리미, … * 모든 문자열 가, 나비, 다리미, ... 가* 가로 시작하는 모든 문자열 가, 가지, 가로수, … *블랙 블랙으로 끝나는 모든 문자열 커피블랙, 신라면블랙, 소나타-블랙, … *텀블러* 텀블러를 포함하는 모든 문자열 스벅 텀블러 세트, 크리스마스 텀블러, … ?* 글자수가 1개 이상인 모든 문자열 (공란 제외) - ???-???? 하이픈을 포함한 7자리 문자 123-4578, 가나다-라마바사 *~? 물음표로 끝나는 모든 문자열 안녕하세요?, 잘 지내셨어요? 호환성
Windows 버전 모든 버전에서 사용 가능합니다. Mac 버전 모든 버전에서 사용 가능합니다. 와일드카드 실전예제
1. 엑셀 자동필터에서 와일드카드로 원하는 값 찾기
와일드카드를 가장 효율적으로 사용하는 방법은 무엇일까요? 바로 특정 조건을 만족하는 여러개의 문자열을 검색할 때입니다. 예를 들어 '김'으로 시작하는 직원이름 검색하기 등이 있습니다. 자동필터에서 와일드카드를 잘 사용한다면 업무처리속도를 크게 향상시킬 수 있습니다.
- 자동필터를 적용할 범위를 선택합니다.
자동필터를 적용할 범위를 선택합니다. - '데이터' - '필터'를 클릭하여 범위에 자동필터를 적용합니다. (단축키 : CTRL + SHIFT + L)
엑셀 상단의 '데이터' - '필터'를 클릭하여 자동필터를 적용합니다. - 와일드카드를 사용하여 원하는 값을 조회합니다.
와일드카드로 원하는 조건의 값을 검색합니다.
2. 와일드카드로 VLOOKUP 함수 부분일치 검색하기
와일드카드를 사용하여 VLOOKUP 함수의 참조값을 부분일치로 검색할 수 있습니다. 오피스 365를 사용중일 경우 XLOOKUP 함수를 사용하면 와일드카드 검색옵션을 통해 더욱 효율적인 검색이 가능합니다.
- VLOOKUP 함수의 참조값 앞 뒤로 별표(*)를 추가하여 참조값을 부분일치 검색합니다.
= VLOOKUP ("*"&참조값&"*", 참조범위, 열번호, [일치옵션])
와일드카드로 VLOOKUP 함수의 부분일치 조건을 검색합니다. - VLOOKUP 함수는 조건을 만족하는 값이 여러개 있을시, 맨 위의 값을 출력합니다. 따라서 부분일치 조건을 만족하는 값이 여러개 있을 경우 옳지않은 결과를 반환할 수 있으므로 주의합니다.
같은 조건을 만족하는 값이 여러개 있을 시, 옳지않은 결과를 반환합니다.
3. SUMIF/COUNTIF 함수 와일드카드 고급사용법
와일드카드를 사용하면 다양한 조건을 만족하는 셀의 합계 또는 개수를 구할 수 있습니다. SUMIF 함수 또는 COUNTIF 함수에 대한 자세한 설명은 관련 포스트를 참고해주세요.
- SUMIF 함수의 조건으로 "*텀플러*" 를 입력하면, '텀블러' 단어를 포함한 모든 제품의 합계를 계산합니다.
텀블러라는 단어를 포함한 모든 제품을 조회합니다. - 추가로 와일드카드와 셀을 조합하여 조건을 입력하면, 실시간으로 변하는 조건별 합계를 계산할 수도 있습니다.
조건으로 [ "*텀블러*"&H13 ] 을 입력합니다.
H13 셀에는 "핑크" 라는 값이 입력되어 있으므로, [ *텀블러*핑크 ] 가 SUMIF 함수의 조건으로 입력됩니다.
따라서, 제품명 범위에서 '텀블러' 를 포함하고 '핑크'로 끝나는 모든 제품의 재고수량 합계를 계산합니다.
와일드카드와 셀을 조합하여 실시간으로 변하는 값을 조회할 수 있습니다.
다른 주의사항
- 만약 특정 문자열을 포함하는 모든 셀을 검색하고 싶을 경우, 특정 문자열 앞뒤로 "*(별표)"를 입력해줍니다.
(예: "김"이라는 단어를 포함하는 모든 셀을 검색할 경우, *김* 으로 조건 검색) - 만약 와일드카드 앞에 "~(물결표)"를 붙일 경우, 와일드카드의 기능을 상실하고 실제 "*(별표)" 또는 "?(물음표)"를 포함하는 문자열이 검색됩니다.
(예: *~? 로 조건 검색하면, 물음표로 끝나는 모든 문자열("안녕하세요?", "누구세요?" 등)을 검색합니다.)
- 자동필터를 적용할 범위를 선택합니다.
함수를 아래처럼 입력해보세요^^
바로 해결되실거에요. :) 감사합니다.
~~ 두개를 입력해주세요.
불량을 포함하는 결과를 얻으시려면 아래 수식을 이용해보세요.
=INDEX(출력범위,MATCH(1,--ISNUMBER(SEARCH("불량",찾을범위)),0))
자세한 내용은 아래 링크를 참고하세요.https://www.oppadu.com/if-%ED%95%A8%EC%88%98-%ED%8A%B9%EC%A0%95-%EB%AC%B8%EC%9E%90-%ED%8F%AC%ED%95%A8/
해당 아스키코드만 남기고 모두 지우는 형태의 매크로를 작성하시면 될 듯 합니다. 내장 기능만으로는 구현이 불가능할 듯 합니다.^^;
아래 특정문자 포함 공식을 활용해서, 앞에 NOT을 붙이면 포함하지 않는 경우를 검색할 수 있습니다.
https://www.oppadu.com/if-%ED%95%A8%EC%88%98-%ED%8A%B9%EC%A0%95-%EB%AC%B8%EC%9E%90-%ED%8F%AC%ED%95%A8/
또는 엑셀 필터기능을 활용하면 되는데요. 필터를 보시면 텍스트필터 -> 포함하지 않음 조건이 있습니다. 이 조건에서 엘로우를 입력 후 검색하시면 엘로우를 포함하지 않는 경우를 필터링할 수 있습니다.^^
답변이 도움이 되셨길 바랍니다.
수식을 아래처럼 사용해보시길 바랍니다.
질문이 있습니다. 찾는 값이 이름과 같은 문자가 아닌 숫자일경우 #N/A와 같이 뜨고
서식을 숫자로 하든 일반으로해도 결과값이 뜨지 않는데 이런 경우 어쩌면 좋을까요 ㅠ ?
Substitute 함수를 사용해보세요.
함수에 대한 자세한 설명은 아래 링크를 참고해보세요.
https://www.oppadu.com/%EC%97%91%EC%85%80-substitute-%ED%95%A8%EC%88%98/
셀 주소를 참조하실때에는
"*"&C1&"*"
형태로 입력하셔야합니다.
오빠두엑셀 응용공식을 참고하여 함수를 만들어 봤습니다.
해당 그림과 같이 와일드카드를 사용한 함수를 쓰니 Do_AF와 F 이둘을 구별을 못해 저렇게 떠버리는 에러가 납니다.
사용중인 함수 :
=IFERROR(SUBSTITUTE(SUBSTITUTE(OFFSET($B4, ,MATCH("*" & E$3,$B4:$C4,0)-1),E$3,""),"+",""),"")
둘을 구별하려면 어떠한 함수를 추가해야 할까요?
님 식에서 value 함수만 끼워넣으면 숫자값은 숫자 그대로 나오고 숫자가 포함된 문자( = 문자)는 값으로 인식 못해서 오류가 나는데
Iferror(식,"") 가 마지막으로 오류값을 빈칸으로 만들어 주어서 원하는 결과값을 받을 수 있어요
몇개월 전 댓글이라 의미없을 수도 있는데 괜히 참견하고 갑니다