파워쿼리 유사일치(Fuzzy Match), 이렇게만 사용해보세요! [VLOOKUP #N/A 오류 완벽 해결]

엑셀 VLOOKUP 함수를 사용할 때 발생하는 #N/A 오류를 해결하기 위한 엑셀의 새로운 기능! - 파워쿼리 유사일치 (Fuzzy Match) 기능 기초 사용법 총정리

홈페이지 » 파워쿼리 유사일치(Fuzzy Match), 이렇게만 사용해보세요!

파워쿼리 유사일치(Fuzzy Match), 이렇게 사용해보세요!

엑셀 파워쿼리 유사일치 목차 바로가기
영상 강의
큰 화면으로 보기

예제파일 다운로드

오빠두엑셀의 강의 예제파일은 여러분을 위해 자유롭게 제공하고 있습니다.

  • [엑셀고급] 엑셀 파워쿼리 유사일치 (Fuzzy Match) 기초
    예제파일
  • [엑셀고급] 엑셀 파워쿼리 유사일치 (Fuzzy Match) 기초
    완성파일

.

라이브 강의 전체영상도 함께 확인해보세요!

위캔두 회원이 되시면 매주 오빠두엑셀에서 진행하는 라이브강의 풀영상을 확인하실 수 있습니다.


VLOOKUP 함수 유사일치, 주의사항!

우리가 실무에서 VLOOKUP 함수를 배울 때, 주의사항으로 항상 짚고 넘어가는 내용이 있습니다.

VLOOKUP 함수의 마지막 인수는, 반드시 0(또는 FALSE)로 입력한다! (=정확히 일치로 사용한다!)
VLOOKUP 공부할 때 항상 나오는 내용이죠?😉

이 내용은 엑셀을 어느정도 사용해보셨다면, 익히 잘 알고 계실 내용일거라 생각됩니다. VLOOKUP 함수의 기본 일치 옵션은 분명히 '유사일치(Approximate Match)' 라고 적혀 있지만, 실무에서는 (아주 특별한 상황을 제외하고) 반드시 '정확히 일치'로만 사용해야 합니다.

그 이유는 바로, VLOOKUP 함수의 유사일치는 유사일치가 아니기 때문인데요. VLOOKUP 함수의 유사일치는 '정확히 일치하는 값이 없을 경우 그 위에 있는 값을 반환' 하도록 동작합니다. 그마저도 범위가 반드시 '오름차순'으로 정렬되어 있을 때에만 올바르게 사용할 수 있습니다.

  1. VLOOKUP 유사일치는 '정확히 일치하는 값이 없을 경우 그 위의 값을 반환'한다.
  2. 유사일치 옵션을 사용하려면 범위가 반드시 '오름차순' 정렬 되어있어야 한다.

예를들어, 아래 예제를 보겠습니다. 과일 목록에서 찾을 값으로 '포두'를 유사일치로 검색하면, 망고의 가격인 1,800원으로 알수 없는 값이 반환됩니다. (실무에서는 잘못된 값이 입력되었기 때문에, #N/A 오류가 나오도록 처리해야하겠죠?)

VLOOKUP 유사일치 오류
파워쿼리 유사일치는 제한된 상황에서만 사용할 수 있습니다.

그렇기 때문에, VLOOKUP 함수의 유사일치 옵션은 실무에서 제한된 상황(소득세 계산 정도..?)에서만 사용하고, 99.9% 이상 대부분의 경우 VLOOKUP 함수의 일치옵션은 반드시 '정확히 일치'로 사용합니다.

파워쿼리는 정말 유사일치로 검색합니다!

파워쿼리에서 제공하는 유사일치(Fuzzy Match)는 단어 그대로 정말 유사일치 검색을 합니다. 뿐만 아니라 유사도(0~1)를 변경해서, 얼마나 정확하게 일치하는 단어를 검색할지도 설정할 수 있습니다.

파워쿼리 유사일치는 실무의 다양한 상황에서 활용할 수 있습니다. 예를 들어, 동일한 제품이지만 거래처마다 다른 제품코드으로 입고되는 경우(예: ABC-123, ABC-123-Q, ABC-123-A, 등..) 또는 오탈자로 인해 VLOOKUP 함수를 사용할 수 없는 경우(예: 삼성생명, 삼송생명 등..)에 사용하면, 작업시간을 획기적으로 단축할 수 있습니다.

엑셀 제품코드 유사일치 비교
파워쿼리 유사일치 기능은 실무 다양한 상황에서 활용할 수 있습니다.
오빠두Tip : 2021년 8월 기준, 파워쿼리 유사일치(Fuzzy Match) 기능은 M365 버전에서만 사용 가능합니다. 엑셀 2016, 2019 버전은 파워쿼리는 제공되지만 유사일치 기능은 사용할 수 없습니다.

파워쿼리 유사일치 - 1. 쿼리 만들기

  1. 범위를 표로 변환하기 : 파워쿼리를 사용하려면, 범위를 표로 변환해야 합니다. 예제파일을 실행한 뒤, 업체명의 고유값이 입력된 A1:B5 범위와 찾을값이 입력된 D1:D9 범위를 표로 변환합니다.
    엑셀 범위를 표로 변환
    각 범위를 선택 한 뒤, 범위를 표로 변환합니다.
    오빠두Tip : 범위 선택 후, [삽입] - [표]를 클릭하면 범위를 표로 변환할 수 있습니다. 엑셀 표 기능에 대한 자세한 설명은 엑셀 표기능의 모든 것 기초 입문강의를 참고하세요.
  2. 변환한 표를 선택한 뒤, [표 디자인] 탭에서 표 이름 아래와 같이 변경합니다.
    범위 표 이름
    A1:B5 표_업체목록
    D1:E9 표_업체
    오빠두Tip : 표 이름은 '표 디자인'탭 왼쪽에서 변경할 수 있습니다.
  3. 표를 쿼리로 변환하기 : 각 표를 선택한 다음, [데이터] 탭 - [테이블/범위에서]를 클릭하면 파워쿼리 편집기가 실행되면서, 선택한 표가 쿼리로 변환됩니다.
    엑셀 파워쿼리 만들기
    표를 선택 - 데이터 - 테이블/범위에서 를 선택하여 표를 쿼리로 변환합니다.
    오빠두Tip : [데이터] 탭의 레이아웃은 사용 중인 엑셀 버전마다 다를 수 있습니다.
  4. 파워쿼리 편집기에서 [파일] - [닫기 및 다음으로 로드]를 클릭해서 파워쿼리 편집기를 종료합니다.

    파워쿼리 닫기 및 다음으로 로드
    쿼리 - 파일 - 닫기 및 다음으로 로드를 클릭합니다.

  5. [데이터 가져오기] 대화상자가 나오면, '연결만 만들기' 를 선택 후, [확인] 버튼을 클릭합니다.
    쿼리 연결만 만들기
    연결만 만들기를 선택 후, [확인] 버튼을 클릭하면 쿼리가 연결로만 만들어집니다.
  6. 쿼리가 연결로만 만들어졌습니다. 생성된 쿼리 목록은 화면 오른쪽 "쿼리 및 연결" 탭에서 확인할 수 있습니다.
    쿼리 연결 목록 확인
    쿼리 및 연결에서 만들어진 쿼리를 확인할 수 있습니다.
    오빠두Tip : 화면 오른쪽에 "쿼리 및 연결" 창이 안 보일 경우, [데이터] 탭 - [쿼리 및 연결] 버튼을 클릭합니다.
  7. 다른 표도 동일한 방법으로 쿼리를 추가하면 총 2개의 쿼리가 만들어집니다.

    엑셀 파워쿼리 유사일치 병합 목록
    나머지 표도 쿼리로 추가합니다.

파워쿼리 유사일치 - 2. 유사일치 병합

  1. 쿼리 병합하기 : [데이터]탭 - [데이터 가져오기] - [쿼리 결합] - [병합]을 선택합니다. 쿼리 결합에는 '병합'과 '추가' 2가지 종류가 있습니다.
    엑셀 파워쿼리 쿼리 결합 병합
    [데이터] - [데이터 가져오기] - [쿼리 결합] 에서 병합을 선택합니다.
    쿼리 결합 설명
    병합 (Merge)
    [VLOOKUP과 유사]
    서로 다른 두 쿼리의 특정 필드 참조해서 쿼리를 병합합니다. 병합한 쿼리에는 새로운 필드가 추가됩니다.
    추가 (Append)
    [시트 합치기와 유사]
    기존 쿼리 아래에 새로운 쿼리를 추가합니다. 새 쿼리는 기존 쿼리 아래에 새로운 데이터로 추가됩니다.
  2. 병합 대화상자가 나오면 병합할 쿼리를 선택합니다. 병합 방식에 따라 달라지지만, 일반적으로 위쪽 쿼리는 '출력할 쿼리' 아래쪽 쿼리는 '값을 찾을 쿼리'를 선택하면 됩니다. 관계형 DB에 대한 자세한 설명은 아래 관계형 데이터베이스 5분 살펴보기 영상 강의를 참고하세요.
    파워쿼리 병합 쿼리 선택
    병합할 쿼리를 차례대로 선택합니다.
    오빠두Tip : VLOOKUP 함수와 비교했을 때, 위쪽 쿼리는 '찾을값', 아래쪽 쿼리는 '범위'가 됩니다.
  3. 각 쿼리에서 참조할 필드로 업체명을 선택합니다. 각 쿼리의 업체명 필드를 선택하면 병합 대화상자 아래로 '0/8개의 테이블과 일치한다'고 메시지가 표시됩니다. (일치하는 값이 없음)

    파워쿼리 쿼리 필드 선택
    각 쿼리에서 병합할 기준이 입력된 필드를 선택합니다.

  4. 유사일치 병합 옵션 설정 : 병합 대화상자 아래에 '유사 일치를 사용하여 병합 비교' 를 체크하면 대화상자 아래에 메시지가 '선택 영역은 첫번째 테이블에서 8/8개의 테이블과 일치한다'라고 메시지가 바뀝니다. (모든 항목이 일치함)

    파워쿼리 유사 일치 병합 비교
    유사일치를 사용하여 병합 비교를 선택합니다.

  5. 아래 '유사 항목 일치 옵션' 버튼을 클릭하면 유사일치 병합 옵션을 설정할 수 있습니다. 각 유사항목 일치 옵션 사용법은 아래 표와 같습니다.
    유사일치 옵션 설명
    유사성 임계값 (유사도) 0~1 사이의 소수를 입력합니다. 기본값은 0.8 이며, 1에 가까울수록 더욱 정확한 값을 일치합니다. 실무에서는 0.3~0.8 사이의 값을 사용합니다.
    대/소문자 무시 TRUE일 경우 "apple" = "Apple" 을 동일한 값으로 처리합니다. 기본값은 TRUE 입니다.
    텍스트 부분을 결합하여 일치 TRUE일 경우 띄어쓰기가 된 단어와 그렇지 않은 단어를 동일한 값으로 봅니다. 예를 들어, "사과 나무" = "사과나무"를 동일한 값으로 처리합니다. 기본값은 TRUE 입니다.
    최대 일치 항목 수 유사일치로 일치하는 값이 여러개 있을 경우, 최대 몇개의 값을 출력할 지 입력합니다. 기본값은 2억개(거의 무한대)로 출력할 수 있습니다.
    변환 테이블 줄임말과 같은 유사도로 검색할 수 없는 단어(예: 차도남 -> 차가운 도시 남자)가 있을 경우, 각 단어를 인식할 수 있는 변환테이블을 추가합니다. 변환테이블(쿼리)의 머리글은 반드시 From과 To로 작성되어야 합니다.
  6. 실무에서는 유사성 임계값만 0.3~0.8 사이로 수정해서 사용하는 것이 일반적입니다. 유사일치 옵션의 기본값으로 모두 일치하기 때문에, [확인] 버튼을 눌러 병합된 쿼리를 편집합니다.
  7. 병합된 쿼리 편집하기 : 확인 버튼을 누르면 병합된 쿼리가 파워쿼리 편집기로 표시됩니다. 쿼리의 마지막 필드 [표_업체목록] 오른쪽으로 확장버튼을 클릭하면 표를 확장할 수 있습니다. 업체명과 담당자를 모두 선택한 뒤, [확인] 버튼을 누르면 유사일치로 병합된 쿼리를 확인할 수 있습니다.

    파워쿼리 유사일치 병합 확장
    확장 버튼을 클릭하여 병합된 쿼리에서 출력할 필드를 선택합니다.

  8. 샹숑전자, 삼싱전자 등이 모두 '삼성전자'로 일치된 것을 볼 수 있습니다. 이제 쿼리에서 불필요한 필드는 제거하고, 머리글을 각각 "기존업체명"과 "담당자"로 변경하면 쿼리 병합이 완료됩니다.
    엑셀 파워쿼리 유사일치 병합 완료
    필드가 확장되면서 유사일치 병합이 완료되었습니다.
    오빠두Tip : 파워쿼리에 대한 기본 사용법 및 기초 응용 예제는 아래 파워쿼리 입문 영상강의를 참고하세요.
  9. [파일] - [닫기 및 다음으로 로드] 클릭 후, 표 형태로 기존 워크시트에 출력하면 유사일치 병합이 완료됩니다.

    파워쿼리 유사일치 병합 표 만들기
    "닫기 및 다음으로 로드" 에서 병합한 쿼리를 시트 위 표로 출력합니다.

파워쿼리 유사일치 사용시 주의사항

유사일치는 매우 유용한 기능이지만 2가지 주의사항이 있습니다.

  1. 파워쿼리 유사일치는 띄어쓰기가 되지 않은 단어를 하나의 완성된 단어로 인식합니다.
    : "사과", "사과나무", "사과 나무", "사과 나무 열매"를 "사과"와 유사일치하면, 띄어쓰기가 되지 않은 "사과나무"는 하나의 단어로 인식되어 "사과"와 매칭되지 않습니다.

    파워쿼리 유사일치 띄어쓰기 주의
    파워쿼리 유사일치는 띄어쓰기가 안 된 단어를 하나의 완성된 단어로 인식합니다.

  2. 파워쿼리 유사일치는 '텍스트'필드에서만 사용할 수 있습니다.
    : 파워쿼리 유사일치 사용 중 "유사 항목 조인 작업의 경우 텍스트 열만 지원됩니다.
    - 열 'XXX'이(가) 텍스트 형식이 아닙니다."
    - "선택 항목에 따라 반환되는 일치 항목의 수를 확인할 수 없습니다."

    라는 오류가 출력될 경우, 유사일치로 비교하는 필드의 데이터 형식을 '텍스트'로 변환합니다.

    파워쿼리 유사일치 병합 주의사항
    파워쿼리 유사일치로 비교할 필드는 반드시 데이터 형식이 '텍스트'여야 합니다.


.

다음 강의 바로가기 👇👇

이번 강의에 이어서 유사일치 기능을 활용한 키워드 분석 방법을 소개해드립니다.


5 6 투표
게시글평점
guest
9 댓글
Inline Feedbacks
모든 댓글 보기
황금마차
황금마차
2021년 8월 17일 4:58 오전
게시글평점 :
     

거래처 관리할 때 조금씩 이름이 다른 거래처가 있어서 vlookup 쓰기전에 매번 이름 바꾸느라 고생했는데.. 해결책을 찾은 것 같아 너무 좋습니다. 좋은 내용 공유해주셔서 정말 감사드립니다.

곽동찬
곽동찬
2021년 8월 17일 7:10 오전
게시글평점 :
     

많이 배워갑니다. 설명 감사합니다.

f****
f****
2021년 8월 28일 12:41 오후
게시글평점 :
     

평소에 유튜브와 홈페이지 강의에서 많은 도움을 받고 있습니다. '변환 테이블'을 지정해서 필드명, 텍스트형식명 모두 'From'으로 지정해도 아래와 같은 에러가 나옵니다. 혹시 조언 가능하실지요? "Expression.Error: 선택한 유사 항목 변환 테이블에는 이름이… 더보기 »

h2010
h2010
2021년 9월 10일 2:36 오후
게시글평점 :
     

2016 버전에서는 유사일치 체크박스가 아에 안나오는데 버전별 기능 차이가 있나요?

서래부동산
서래부동산
2021년 9월 16일 3:20 오후
게시글평점 :
     

좋은 자료 잘 보고 있습니다.

으아아아아아아
으아아아아아아
2021년 9월 16일 7:00 오후
게시글평점 :
     

안녕하세요, 선생님 올려주신 강의 덕분에 엑셀 실력이 점차 늘어가고 있습니다. 감사드립니다. 혹시나 보실지는 모르겠으나, 올려주신 파워쿼리 유사일치 사용 주의사항과 같이 [텍스트 형식이 아니라는 오류]가 떠서, 텍스트 형식으로 바꿨음에도 지속적인 동일한… 더보기 »

9
0
여러분의 생각을 댓글로 남겨주세요.x