데이터 분석에서 서로 다른 두 개의 표를 하나로 합쳐야 하는 경우가 자주 발생합니다. 이때 가장 유용하게 활용되는 기능이 바로 외부 조인(Outer Join)입니다. 이 글에서는 엑셀에서 외부 조인을 수행하는 두 가지 검증된 방법과 함께, 풀 아우터 조인(Full Outer Join), 내부 조인(Inner Join)까지 단계별로 자세히 살펴보겠습니다.
먼저 "제품 ID", "고객", "날짜" 열로 구성된 다음과 같은 고객 주문 목록이 있다고 가정해 보겠습니다.
그리고 "제품 ID", "품목", "판매액(USD)" 열로 구성된 제품 및 판매 목록도 준비되어 있습니다.
두 데이터셋의 공통 열인 "제품 ID"를 기준으로 외부 조인을 수행해 보겠습니다.
방법 1 – IFERROR 함수와 VLOOKUP 함수 활용하기
단계:
- B4 셀로 이동한 후 CTRL + T를 눌러 표(Table)를 생성하고 삽입합니다.
- 생성된 표의 이름을 "Order_Tbl"로 지정합니다.
- 같은 방식으로 두 번째 표를 만들고 이름을 "Product_Tbl"로 지정합니다.
- C5 셀로 이동하여 아래 수식을 입력합니다.
=IFERROR(VLOOKUP([@[Product ID]],Order_Tbl,2,FALSE),"")
여기서 Product ID는 열 머리글을 의미하며, Order_Tbl은 조회 대상 표 범위입니다.
수식 구성 요소 분석
- VLOOKUP([@[Product ID]],Order_Tbl,2,FALSE) → 표의 가장 왼쪽 열에서 값을 찾아 같은 행의 지정된 열 값을 반환합니다. 여기서 [@[Product ID]](lookup_value 인수)는 "Order" 워크시트의 Order_Tbl(table_array 인수) 배열에서 매핑됩니다. 2(col_index_num 인수)는 조회 값의 열 번호를 나타내며, FALSE(range_lookup 인수)는 조회 값의 정확한 일치를 의미합니다.
- 결과 → "Sam"
- IFERROR(VLOOKUP([@[Product ID]],Order_Tbl,2,FALSE),"") → 위 수식을 감싸는 형태로 동작합니다.
- IFERROR("Sam","") → 수식에 오류가 있으면 value_if_error 값을 반환하고, 그렇지 않으면 수식의 결과값을 반환합니다. 여기서 "Sam"은 value 인수, ""(빈 값)는 value_if_error 인수이므로 최종적으로 "Sam"이 반환됩니다.
- 결과 → "Sam"
- D5 셀로 이동하여 아래 수식을 입력합니다.
=IFERROR(VLOOKUP([@[Product ID]],Order_Tbl,3,FALSE),"")
- E5 셀에 아래 수식을 입력합니다.
=IFERROR(VLOOKUP([@[Product ID]],Product_Tbl,2,FALSE),"")
Product_Tbl은 제품 데이터의 표 범위를 나타냅니다.
- F5 셀을 선택하고 수식 입력줄에 아래 식을 입력합니다.
=IFERROR(VLOOKUP([@[Product ID]],Product_Tbl,3,FALSE),"")
완성된 결과물은 다음과 같습니다.
방법 2 – 파워 쿼리 편집기(Power Query Editor) 활용하기
단계:
- "Order" 워크시트에서 B4 셀을 선택한 후 데이터 탭으로 이동하여 테이블/범위에서(From Table/Range)을 선택합니다.
- 닫기 및 로드(Close & Load)를 클릭한 후 닫기 및 로드 대상(Close & Load To)을 선택합니다.
- 연결만 만들기(Only Create Connection)에 체크한 후 확인을 클릭합니다.
- 동일한 절차를 반복하여 "Order" 워크시트의 표에도 연결을 설정합니다.
- 데이터 가져오기(Get Data)로 이동한 후 쿼리 결합(Combine Queries)에서 병합(Merge)을 선택합니다.
- 아래 GIF 이미지에 나타난 단계를 따라 진행합니다.
- 닫기 및 로드 대상을 클릭합니다.
- 데이터 가져오기(Import Data) 창에서 테이블(Table)과 새 워크시트(New Worksheet)를 체크한 후 확인을 클릭합니다.
최종 결과물은 다음과 같습니다.
엑셀에서 풀 아우터 조인(Full Outer Join) 실행하는 방법
풀 아우터 조인은 두 데이터셋(표)의 모든 행을 하나로 결합하는 방식입니다. 양쪽 테이블에 일치하지 않는 행까지 모두 포함되므로, 데이터 누락 없이 전체 현황을 파악할 때 매우 유용합니다.
단계:
- "Best Sellers" 워크시트로 이동하여 표를 생성하고 이름을 "Best_Sellers_Tbl"로 지정합니다.
- "Quantity" 워크시트에서도 동일한 작업을 반복하여 "Quantity_Tbl"을 생성합니다.
- 앞서 설명한 절차를 따라 표들을 로드하고 연결을 설정합니다.
- 데이터 가져오기로 이동한 후 쿼리 결합에서 병합을 선택합니다.
- 아래 GIF 이미지의 단계를 따라 진행합니다.
- 데이터를 새 워크시트에 표 형태로 로드합니다.
실행 결과는 다음과 같습니다.
더 읽어보기: 엑셀에서 레프트 조인(Left Join) 수행하는 방법
엑셀에서 내부 조인(Inner Join) 실행하는 방법
단계:
- 표들을 파워 쿼리 편집기로 불러온 후 아래와 같이 두 쿼리를 병합(Merge)합니다.
- 변환된 데이터를 새 워크시트로 닫기 및 로드(Close & Load)합니다.
더 읽어보기: 엑셀에서 내부 조인(Inner Join) 수행하는 방법
연습 섹션
직접 실습하면서 배운 내용을 익혀 보세요.
연습용 통합 문서 다운로드
관련 글
- 엑셀에서 파워 쿼리로 두 개의 표 결합하는 방법
- 엑셀에서 왼쪽 외부 조인(Left Outer Join) 수행하는 방법
- 엑셀에서 교차 조인(Cross Join) 만드는 방법
<< 파워 쿼리 엑셀로 돌아가기 | 엑셀 배우기
무료 고급 엑셀 연습 문제와 해답을 받아보세요!