엑셀에서 다른 시트의 데이터 매핑하는 6가지 유용한 방법

엑셀 사용자라면 여러 개의 워크시트를 오가며 작업하는 일이 매우 흔합니다. 하지만 이렇게 흩어진 시트 사이에서 데이터를 일일이 옮기고 연결하는 작업은 생각보다 많은 시간을 소모하게 됩니다. 따라서 다른 시트의 데이터를 효율적으로 매핑하는 방법을 익혀두면 업무 속도가 크게 향상됩니다. 이 글에서는 엑셀에서 다른 시트의 데이터를 매핑할 수 있는 6가지 유용한 방법을 하나씩 자세히 소개하겠습니다.

예제 파일 내려받기

아래 샘플 파일을 내려받아 직접 실습해 보세요.

다른 시트 데이터를 매핑하는 6가지 방법

설명을 위해 두 개의 데이터 세트를 준비했습니다. 첫 번째 데이터 세트에는 어떤 회사의 주문번호, 고객명, 고객 국가 정보가 들어 있습니다.

두 번째 데이터 세트에는 같은 주문번호를 기준으로 제품명, 수량, 가격 정보가 담겨 있습니다.

보시다시피 두 데이터 세트는 '주문번호'를 공통 기준으로 서로 연결되어 있습니다. 이제 첫 번째 데이터 세트의 데이터를 두 번째 시트로 불러와 한 화면에서 완전한 정보를 확인할 수 있도록 만들어 보겠습니다. 각 방법을 순서대로 살펴봅니다.

1. 셀 연결(Link)로 데이터 매핑하기

가장 먼저 소개할 방법은 가장 단순한 셀 연결 방식입니다. 참조할 셀을 직접 지정해 데이터를 불러오는 방법입니다. 아래 절차를 따라 해 보세요.

• 먼저 F5 셀에 등호(=)를 입력합니다.

• 그다음 원본 데이터가 있는 워크시트로 이동해 D5 셀을 클릭합니다.

• 이제 다시 원래 워크시트로 돌아오면 F5 셀이 D5 셀과 연결된 것을 확인할 수 있습니다.
• 이어서 Enter 키를 누릅니다.

• 이렇게 하면 다른 시트의 데이터를 성공적으로 불러온 것입니다.

• 이어서 채우기 핸들(AutoFill) 기능을 이용해 원본 데이터 D열의 나머지 값들을 한꺼번에 가져옵니다.

• 같은 방식으로 열 제목(헤더)도 입력합니다.
• 마지막으로 몇 가지 서식만 다듬으면 아래와 같은 최종 결과물이 완성됩니다.

2. VLOOKUP 함수로 데이터 매핑하기

두 번째 방법은 대표적인 조회 함수인 VLOOKUP을 활용하는 것입니다. 현재 워크시트에는 국가(Country) 열의 데이터가 비어 있는 상태입니다.

이 데이터를 Dataset 1 시트에서 가져와 보겠습니다.

아래 단계를 따라 해 보세요.

• 먼저 F5 셀에 다음 수식을 입력합니다.

여기서 B5는 두 워크시트에 공통으로 존재하는 값이므로 Lookup Value(찾을 값) 역할을 합니다. 'Dataset 1'!B4:D9는 Table Array(참조 범위)로, 원본 시트 이름과 해당 시트의 셀 범위를 뜻합니다. 그다음 필요한 데이터가 Dataset 1 시트의 세 번째 열에 있으므로 Column_Index_Num은 3으로 지정합니다. 마지막으로 정확한 일치를 찾기 위해 FALSE를 입력합니다.

• 이제 Enter 키를 누릅니다.
• VLOOKUP 함수로 데이터가 성공적으로 매핑된 것을 확인할 수 있습니다.

• 이어서 채우기 핸들(AutoFill)을 사용해 나머지 데이터를 한 번에 채웁니다.
• 최종 결과는 아래와 같습니다.

3. INDEX-MATCH 함수 조합으로 다른 시트 데이터 불러오기

INDEX 함수와 MATCH 함수의 조합 역시 다른 시트에서 데이터를 가져오는 데 매우 편리한 방법입니다. 아래 단계를 따라 해 보세요.

• 먼저 G14 셀에 다음 수식을 입력합니다.

여기서 INDEX 함수가 값을 반환하고, MATCH 함수가 원본 데이터 배열에서 정확히 일치하는 위치를 찾아주는 역할을 합니다.

• 이제 Enter 키를 누릅니다.
• 원하는 값이 화면에 표시되는 것을 확인할 수 있습니다.

• 마지막으로 채우기 핸들(AutoFill)을 사용해 전체 값을 채워 넣습니다.

4. 고급 필터(Advanced Filter)로 다른 시트 데이터 매핑하기

다른 시트의 데이터를 매핑하는 또 다른 유용한 방법은 엑셀의 고급 필터(Advanced Filter)를 활용하는 것입니다. 진행 과정을 살펴보겠습니다.

• 먼저 결과를 입력할 두 번째 워크시트를 선택합니다.
• 데이터(Data) 탭으로 이동한 뒤 정렬 및 필터(Sort & Filter) 그룹에서 고급(Advanced)을 선택합니다.

• 그러면 고급 필터 창이 새로 열립니다.
• 이 창에서 다른 위치에 복사(Copy to another location) 옵션을 선택합니다.
• 이어서 Dataset 1 워크시트의 범위를 목록 범위(List range)에 입력합니다.

• 다음으로 두 번째 시트의 범위를 조건 범위(Criteria range)에 입력합니다.

• 마지막으로 복사 위치(Copy to) 상자에 셀 정보를 입력합니다.

• 이제 확인(OK)을 누릅니다.
• 원본 워크시트의 데이터가 성공적으로 복사된 것을 확인할 수 있습니다.

5. VLOOKUP & INDIRECT 함수 조합으로 데이터 매핑하기

이번에는 VLOOKUP과 INDIRECT 함수를 함께 사용해 데이터를 매핑하는 방법을 알아보겠습니다. 아래는 제목은 같지만 값이 서로 다른 두 개의 워크시트입니다.

이것이 다른 워크시트입니다.

이제 두 워크시트의 데이터를 매핑하는 단계별 방법을 살펴보겠습니다.

• 먼저 새 워크시트를 열고 C5 셀에 다음 수식을 입력합니다.

여기서 INDIRECT 함수는 문자열을 엑셀이 인식할 수 있는 참조 이름으로 변환하여 VLOOKUP의 table_array 인수에 넣어주는 역할을 합니다.

• 이제 Enter 키를 누릅니다.
• 특정 주문번호에 해당하는 수량이 표에 표시됩니다.

• 마지막으로 채우기 핸들(AutoFill)을 사용하면 워크시트 전체의 결과를 얻을 수 있습니다.

6. HLOOKUP 함수로 다른 시트 데이터 매핑하기

마지막 방법은 HLOOKUP 함수를 사용해 다른 시트의 데이터를 매핑하는 것입니다. 작동 방식을 살펴보겠습니다.

• 먼저 F5:F9 셀 범위 옆에 순서 번호를 입력합니다.

• 이제 F5 셀에 다음 수식을 입력합니다.

• 이어서 Enter 키를 누릅니다.
• 새 워크시트에 원하는 데이터가 나타납니다.

• 채우기 핸들(AutoFill)을 적용해 전체 결과를 완성합니다.

• 작업이 끝나면 임시로 입력했던 순서 번호는 반드시 삭제하세요.

마치며

지금까지 엑셀에서 다른 시트의 데이터를 매핑하는 6가지 유용한 방법을 살펴보았습니다. 셀 연결처럼 간단한 방법부터 VLOOKUP, INDEX-MATCH, 고급 필터, INDIRECT, HLOOKUP까지 상황에 맞는 방법을 골라 활용하면 반복 작업 시간을 크게 줄일 수 있습니다. 이 글이 도움이 되었다면 피드백을 남겨주세요. 더 많은 엑셀 팁은 ExcelDemy 블로그에서 확인하실 수 있습니다.