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

실습 가이드

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

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

실무에서 VLOOKUP 함수를 사용할 때, 강의나 책에서 거의 빠짐없이 강조하는 주의사항이 있습니다.

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

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

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

예를 들어 아래 예제를 보면, 과일 목록에서 찾을 값으로 '포두'를 유사일치로 검색했을 때 망고의 가격인 1,800원이 알 수 없는 값으로 반환됩니다. 실무에서는 잘못된 값이 입력될 경우 #N/A 오류를 표시해 사용자가 인지할 수 있도록 처리해야 합니다.

이러한 이유로 VLOOKUP 함수의 유사일치 옵션은 소득세 계산처럼 매우 제한된 상황에서만 사용하고, 실무의 99.9% 이상은 VLOOKUP 함수의 일치 옵션을 반드시 '정확히 일치'로 지정해 사용합니다.

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

파워쿼리에서 제공하는 유사일치(Fuzzy Match)는 이름 그대로 실제 유사한 값을 찾아주는 기능입니다. 또한 유사도(0~1)를 직접 조정해, 얼마나 정확하게 일치하는 단어까지 인식할지 사용자가 설정할 수 있습니다.

파워쿼리 유사일치는 실무의 다양한 상황에 활용할 수 있습니다. 동일한 제품임에도 거래처마다 다른 코드로 입고되는 경우(예: ABC-123, ABC-123-Q, ABC-123-A 등)나, 오탈자로 인해 VLOOKUP 함수로 매칭이 어려운 경우(예: 삼성생명, 삼송생명 등)에 활용하면 반복적인 데이터 정리 작업 시간을 크게 단축할 수 있습니다.

1. 범위를 표로 변환하기 : 파워쿼리를 사용하려면 먼저 범위를 표로 변환해야 합니다. 예제파일을 실행한 뒤, 업체명의 고유값이 입력된 A1:B5 범위와 찾을값이 입력된 D1:D9 범위를 표로 변환합니다.

   

   오빠두Tip : 범위를 선택한 뒤 [삽입] - [표]를 클릭하면 범위를 표로 변환할 수 있습니다. 엑셀 표 기능에 대한 자세한 설명은 아래 기초 입문 강의를 참고하세요.

   엑셀 표 기능의 모든 것, 기초-응용 완벽정리

2. 변환한 표를 선택한 뒤, [표 디자인] 탭에서 표 이름을 아래와 같이 변경합니다.
   

   범위	표 이름
   A1:B5	표_업체목록
   D1:E9	표_업체

   오빠두Tip : 표 이름은 '표 디자인' 탭 왼쪽에서 변경할 수 있습니다.
3. 표를 쿼리로 변환하기 : 각 표를 선택한 다음, [데이터] 탭 - [테이블/범위에서]를 클릭하면 파워쿼리 편집기가 실행되면서, 선택한 표가 쿼리로 변환됩니다.

   

   오빠두Tip : [데이터] 탭의 레이아웃은 사용 중인 엑셀 버전에 따라 다를 수 있습니다.
4. 파워쿼리 편집기에서 [파일] - [닫기 및 다음으로 로드]를 클릭해 편집기를 종료합니다.

   

5. [데이터 가져오기] 대화상자가 나오면 '연결만 만들기'를 선택한 후 [확인] 버튼을 클릭합니다.
   
6. 쿼리가 연결만으로 만들어졌습니다. 생성된 쿼리 목록은 화면 오른쪽 "쿼리 및 연결" 탭에서 확인할 수 있습니다.

   

   오빠두Tip : 화면 오른쪽에 "쿼리 및 연결" 창이 보이지 않는다면, [데이터] 탭 - [쿼리 및 연결] 버튼을 클릭해 표시할 수 있습니다.
7. 다른 표도 동일한 방법으로 쿼리를 추가해 총 2개의 쿼리를 생성합니다.

   

1. 쿼리 병합하기 : [데이터] 탭 - [데이터 가져오기] - [쿼리 결합] - [병합]을 선택합니다. 쿼리 결합에는 '병합'과 '추가' 두 가지 방식이 있습니다.
   

   쿼리 결합	설명
   병합 (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. [파일] - [닫기 및 다음으로 로드]를 클릭한 후, 표 형태로 기존 워크시트에 출력하면 유사일치 병합이 완료됩니다.

   

파워쿼리 유사일치는 매우 유용한 기능이지만, 사용 전에 확인해야 할 두 가지 주의사항이 있습니다.

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

   

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

   와 같은 오류 메시지가 출력될 경우, 유사일치로 비교하는 필드의 데이터 형식을 '텍스트'로 변환한 뒤 다시 시도합니다.