실무에서는 같은 사람이나 항목에 대한 정보가 서로 다른 엑셀 통합 문서에 나뉘어 저장되어 있는 경우가 많습니다. 예를 들어 한 통합 문서에는 직원의 이름과 직책이, 다른 통합 문서에는 이름과 급여가 저장되어 있다면, 열(이름)을 기준으로 두 파일을 병합하여 하나의 워크시트에서 이름·직책·급여를 한눈에 확인할 수 있습니다.
아래 이미지는 Merge Files라는 파일에 저장된 이름과 해당 직책 데이터입니다.
그리고 다음 그림은 Merge Files (lookup) 파일에 저장된 이름과 급여 데이터입니다.
열 기준으로 엑셀 파일을 병합하는 3가지 방법
1. VLOOKUP 함수로 열 기준 병합하기
VLOOKUP 함수는 열을 기준으로 엑셀 파일을 병합할 때 가장 널리 쓰이는 강력한 방법입니다. 여기서는 Merge Files (lookup) 파일의 급여(Salary) 열을 가져와 Merge Files 파일에 추가하겠습니다. 아래 순서대로 따라 해 보세요.
단계:
- 먼저 Merge Files 파일에 급여 열을 새로 만들고, D5 셀에 아래 수식을 입력합니다.
=VLOOKUP($B5,'[Merge Files (lookup).xlsx]lookup'!$B$5:$C$11,2,FALSE)
VLOOKUP 함수는 B5 셀의 값을 기준으로 Merge Files (lookup) 파일의 B5:C11 범위에서 해당 값을 검색한 뒤, 일치하는 행의 급여를 반환합니다. 이때 조회 범위가 밀리지 않도록 반드시 절대 셀 참조($)를 사용해야 합니다. 열 인덱스 번호를 2로 지정한 것은 급여가 조회 범위의 두 번째 열에 있기 때문이며, 이름이 정확히 일치하는 값만 가져오도록 FALSE(정확한 일치)를 선택했습니다.
- Enter 키를 누르면 B5 셀에 있는 Jason Campbell의 급여가 표시됩니다.
- 이후 채우기 핸들(Fill Handle)을 드래그하여 아래 셀까지 자동 채우기(AutoFill) 합니다.
이렇게 하면 VLOOKUP 함수만으로도 열을 기준으로 엑셀 파일을 간단히 병합할 수 있습니다.
함께 읽기: 엑셀 파일을 우편 라벨로 병합하는 방법(쉬운 단계별 가이드)
2. INDEX와 MATCH 함수 조합으로 열 기준 병합하기
INDEX 함수와 MATCH 함수를 조합하면 VLOOKUP보다 유연하게 파일을 병합할 수 있습니다. 마찬가지로 Merge Files (lookup) 파일의 급여 열을 Merge Files 파일로 불러오겠습니다.
단계:
- Merge Files 파일에 급여 열을 만들고, D5 셀에 아래 수식을 입력합니다.
=INDEX('[Merge Files (lookup).xlsx]lookup'!$C$5:$C$11,MATCH($B5,'[Merge Files (lookup).xlsx]lookup'!$B$5:$B$11,0))
여기서 MATCH 함수는 B5 셀의 값이 Merge Files (lookup) 파일의 이름 목록에서 몇 번째 행에 있는지 행 번호를 구하고, INDEX 함수는 그 행 번호에 해당하는 C5:C11 범위의 급여 값을 반환합니다. 역시 절대 셀 참조를 반드시 사용해야 하며, 이를 빠뜨리면 수식을 복사할 때 범위가 어긋나 예기치 않은 오류가 발생할 수 있습니다.
- Enter 키를 누르면 B5 셀의 Jason Campbell의 급여가 표시됩니다.
- 채우기 핸들을 이용해 나머지 셀까지 자동 채우기 합니다.
이렇게 INDEX와 MATCH 함수의 조합으로도 열을 기준으로 엑셀 파일을 깔끔하게 병합할 수 있습니다.
함께 읽기: CMD 명령어로 엑셀 파일을 하나로 합치는 방법(4단계)
관련 글
- 여러 워크시트를 하나의 통합 문서로 결합하는 방법
- 엑셀 파일을 Word 문서로 병합하는 방법
3. 파워 쿼리 편집기(Power Query Editor)로 열 기준 병합하기
수식 입력이 다소 어렵게 느껴진다면, 데이터 탭의 파워 쿼리 편집기를 활용하는 것이 좋습니다. 클릭 몇 번만으로 파일을 열 기준으로 병합할 수 있습니다. 아래 과정을 따라 해 보세요.
단계:
- 새 워크시트를 열고 데이터 >> 데이터 가져오기 >> 파일에서 >> Excel 통합 문서에서를 차례로 선택합니다.
- 데이터 가져오기 창이 나타나면 Merge File을 선택하고 열기를 클릭합니다.
- 탐색기(Navigator) 창이 열리면 이름과 직책이 저장된 power query 시트를 선택합니다.
- 로드 >> 로드 대상을 선택합니다.
- 대화 상자가 나타나면 연결 만들기만을 선택한 뒤 확인을 클릭합니다.
이 작업을 마치면 Merge File 파일의 power query 시트가 쿼리 및 연결(Queries & Connections) 창에 추가됩니다.
- 같은 방법으로 다시 데이터 >> 데이터 가져오기 >> 파일에서 >> Excel 통합 문서에서를 선택합니다.
- 데이터 가져오기 창에서 Merge Files (lookup)을 선택하고 열기를 클릭합니다.
- 탐색기 창에서 이름과 급여가 저장된 salary 시트를 선택합니다.
- 로드 >> 로드 대상을 선택합니다.
- 대화 상자에서 연결 만들기만을 고르고 확인을 클릭합니다.
이제 Merge Files (lookup) 파일의 salary 시트도 쿼리 및 연결 창에 추가되었을 것입니다.
- 이번에는 데이터 >> 데이터 가져오기 >> 쿼리 병합 >> 병합을 선택합니다.
- 병합(Merge) 창이 열리면 첫 번째 드롭다운에서 power query를, 두 번째 드롭다운에서 salary를 선택합니다.
- 두 쿼리에서 공통 기준이 되는 Name 열을 각각 클릭합니다.
- 확인을 클릭합니다.
그러면 파워 쿼리 편집기에 아래와 같은 테이블이 나타납니다.
- salary 열의 확장 아이콘을 클릭하고 Salary를 선택합니다.
- 확인을 클릭합니다.
이제 파워 쿼리 편집기 안에서 이름(Name), 직책(Designation), 급여(Salary)가 한 테이블에 함께 표시됩니다.
- 마지막으로 닫기 및 로드(Close & Load)를 선택합니다.
이 작업이 완료되면 새 시트에 새 Excel 테이블 형태로 병합된 정보가 나타납니다.
이처럼 파워 쿼리 편집기를 사용하면 수식 없이도 열을 기준으로 엑셀 파일을 손쉽게 병합할 수 있습니다.
함께 읽기: VBA로 여러 엑셀 파일을 한 시트에 병합하는 방법(3가지 기준)
연습하기
아래는 이 글에서 사용한 데이터 세트입니다. 위에서 배운 세 가지 방법을 직접 적용해 보며 실력을 점검해 보세요.
마치며
지금까지 열을 기준으로 엑셀 파일을 병합하는 세 가지 쉬운 방법을 살펴보았습니다. 데이터를 손으로 하나하나 옮기는 작업은 시간도 많이 들고 실수가 생기기 쉽습니다. 오늘 소개한 VLOOKUP, INDEX+MATCH, 파워 쿼리 방법을 상황에 맞게 활용하면 반복 작업을 크게 줄일 수 있습니다. 더 좋은 아이디어나 피드백이 있다면 댓글로 공유해 주세요. 소중한 의견은 앞으로 더 알찬 글을 만드는 데 큰 도움이 됩니다.
관련 글
- 엑셀 통합 문서 비교 후 병합하는 방법(간단한 3단계)
- 여러 엑셀 파일을 한 시트로 병합하는 방법(4가지)
- 엑셀에서 여러 통합 문서를 하나로 결합하는 방법(6가지)