엑셀 VLOOKUP과 여러 파일 합치기

상품 목록이 수만 행이 되면 어떤 함수를 쓰느냐에 따라 엑셀이 몇 초 만에 끝나기도 하고 멈춘 것처럼 보이기도 해서, 조회 방식과 합치는 방식을 미리 정해 두는 것이 좋습니다.

1. 왜 상품 목록 정리에 엑셀이 필요한가

도매처에서 받은 상품 파일, 마켓에 올릴 등록 양식, 배송비 표, 가격 표는 대개 서로 다른 파일입니다. 이 사이를 오가며 "이 상품코드의 배송비는 얼마인가", "이 상품이 어느 도매처 것인가"를 찾아 붙이는 작업이 반복됩니다. 상품이 수백 개일 때는 손으로 해도 되지만 수만 행이 되면 함수와 매크로가 없으면 사실상 불가능합니다.

2. VLOOKUP, XLOOKUP, INDEX-MATCH 중 무엇을 쓸까

세 가지 모두 "찾는 값을 기준으로 다른 표에서 값을 가져오는" 함수입니다. 작업 노트를 정리해 보면, 5만 행 안팎의 데이터에서는 INDEX와 MATCH를 함께 쓰는 방식과 XLOOKUP이 빠르고, SUMIFS가 그다음, VLOOKUP은 대량 데이터에서 상대적으로 비효율적이라는 결과였습니다. 다만 이 순서는 엑셀 버전과 행 수, 컴퓨터 사양에 따라 달라질 수 있습니다. XLOOKUP은 Microsoft 365와 엑셀 2021 이후 버전에서 쓸 수 있습니다. 그보다 오래된 엑셀에서 파일을 열면 #NAME? 오류가 나므로, 파일을 받을 사람의 버전도 먼저 확인하세요.

속도 말고도 중요한 차이가 있습니다. VLOOKUP은 가져올 열을 번호로 직접 입력하는데, 중간에 열을 끼워 넣거나 지우면 엉뚱한 열을 가리키게 되어 값이 조용히 틀립니다. 이 약점은 상품 양식을 자주 고치는 셀러에게 특히 위험해서, 열이 바뀌어도 안전한 방식을 쓰는 것이 낫습니다. 열 번호 대신 MATCH로 열 위치를 찾게 하면 열 변경에 강해집니다.

함수별 제공 버전과 동작은 마이크로소프트 엑셀 공식 안내 기준입니다. 올리기 전에 다시 확인하세요.

3. 원본 파일이 닫혀 있을 때 주의

다른 파일을 참조하는 함수는 원본 파일이 닫혀 있으면 새 값을 읽지 못합니다. SUMIFS는 원본이 닫혀 있으면 아예 동작하지 않고, 나머지는 이전에 저장된 값을 그대로 유지합니다. 그래서 결과가 최신인 줄 알고 썼다가 옛 값으로 등록하는 실수가 생길 수 있습니다. 실시간 반영이 필요하면 원본을 열어 둔 상태에서 계산하거나, 파워쿼리, 매크로 같은 다른 방법을 써야 합니다.

4. 수십만 행에서는 조회를 줄인다

저희가 직접 해 보니, 같은 조회를 모든 행에 반복하는 방식은 행 수가 늘수록 급격히 느려졌습니다. 해결책은 조회 횟수를 줄이는 것이었습니다.

  1. 먼저 업체 구분 같은 큰 기준만 조회해서 보조 열에 적어 둔다.
  2. 그다음 해당 업체에 속한 행만 배송비를 조회한다.
  3. 같은 계산을 여러 번 쓰는 값은 보조 열에 미리 계산해 두고 참조한다.
  4. 그래도 느리면 매크로로 목록을 한 번에 불러 처리하는 방법을 검토한다.

핵심은 "느린 함수를 빠른 함수로 바꾸기"보다 "같은 조회를 덜 하게 만들기"입니다.

5. 여러 파일과 시트를 한 번에 합치기

도매처가 파일을 여러 개로 나눠 주거나 시트마다 상품이 나뉘어 있을 때는 매크로로 합치는 방법이 유용합니다. 흐름은 다음과 같습니다. 파일 선택 창에서 합칠 파일을 여러 개 고르고, 시트 이름이 같은 것끼리 묶어서 "통합" 시트 한 곳에 모읍니다. 통합 시트에는 번호 열을 붙여 원본 순서가 섞이지 않게 하고, 원본 파일은 읽기 전용으로 열어 실수로 수정하지 않게 합니다.

저희는 처음 만든 매크로를 쓰면서 시트마다 행 높이가 제각각 늘어나는 서식 문제가 생겨 이를 다듬었고, 합친 결과에서 어느 행이 어느 파일에서 왔는지 알 수 있도록 번호 열을 추가하는 식으로 개선했습니다. 매크로 코드는 사용하는 엑셀 환경에 맞게 직접 확인하고 시험한 뒤에 쓰세요. 반드시 원본을 복사해 둔 상태에서 시작해야 합니다.

6. 정리할 때의 습관

상품코드 열은 앞뒤 공백이 없는지, 숫자인지 문자인지 형식이 같은지 먼저 확인하세요. 조회가 안 되는 가장 흔한 원인은 함수가 아니라 값의 형식 차이입니다. 그리고 큰 작업을 하기 전에는 항상 파일을 복사해 두세요. 한 번 잘못 덮어쓰면 되돌리기 어렵습니다.