엑셀 시트 합치기: VSTACK 함수로 여러 시트 데이터를 하나로 합치는 방법

엑셀의 VSTACK 함수를 쓰면 같은 파일 안에 나뉜 여러 시트의 데이터를 위아래로 이어 하나의 목록으로 볼 수 있습니다. 합칠 범위를 시트별로 지정하면, 수식이 그 순서대로 행을 모아 줍니다.

월별 자재 입고 내역을 시트마다 따로 기록하고 있다면 이 방법이 특히 유용합니다. 열두 장의 시트를 오가며 복사해 붙이지 않아도, 한곳에서 1년 치 내역을 확인할 수 있죠.

단, VSTACK은 Microsoft 365와 Excel 2024에서 쓸 수 있는 함수로, Excel 2021 이전 버전에는 없습니다. 수식을 입력했는데 #NAME? 오류가 나온다면 지원하지 않는 버전일 가능성이 높으니, 아래 ‘VSTACK이 없는 엑셀에서 시트 합치는 방법’으로 바로 넘어가세요.

제조 현장의 자료 취합을 자동화해 온 조슈아앤컴퍼니가 두 시트를 합치는 기본 수식부터 결과가 이상할 때 확인할 것, 시트와 행이 계속 늘어날 때 쓰는 수식, 합친 뒤 품목별 수량을 보는 방법까지 차례로 정리했습니다.

VSTACK 함수란? 여러 시트를 위아래로 이어 붙이는 원리

VSTACK은 지정한 범위의 행을 차례대로 아래에 붙이는 함수입니다. 기본 형태는 다음과 같습니다.

=VSTACK(첫 번째 범위,두 번째 범위)

이때 VSTACK은 열 제목이 아니라 열의 위치를 기준으로 붙입니다. 첫 번째 범위의 A열 아래에는 두 번째 범위의 A열이 그대로 들어가죠. 그래서 합치기 전에 각 시트의 열 순서를 똑같이 맞춰 둬야 합니다.

또 VSTACK은 행을 이어 붙일 뿐, 같은 품목을 하나로 합산하지는 않습니다. 볼트가 1월과 2월에 한 번씩 입고됐다면 결과에도 볼트 행이 두 개 생깁니다. 품목별 합계를 보는 방법은 뒤에서 따로 설명합니다.

VSTACK으로 엑셀 시트 합치기: 예시로 따라 하는 4단계

이제 예시로 직접 합쳐 보겠습니다. 하나의 파일에 1월과 2월 시트가 있고, 두 시트 모두 A1:C3에 같은 순서로 입고 내역이 기록돼 있다고 가정합니다.

1월 시트

2월 시트

실제 업무 파일로 연습한다면 원본을 복사해 두고 진행하세요.

1. 원본 시트를 준비합니다. 1월과 2월 시트의 A1:C1에 입고일, 품목, 수량(개)을 같은 순서로 입력하고, 그 아래 두 행에 위 예시 데이터를 넣습니다.

2. 결과용 시트를 만듭니다. 새 시트를 추가해 이름을 통합으로 바꾸고, A1부터 오른쪽과 아래쪽을 비워 둡니다. 이 자리에는 Ctrl+T로 만든 엑셀 표가 아니라 일반 셀이 있어야 합니다.

3. 통합 시트 A1에 수식을 입력합니다. =VSTACK('1월'!A1:C3,'2월'!A2:C3)을 넣고 Enter를 누릅니다. 첫 번째 범위는 제목행을 포함하고, 두 번째 범위는 제목행을 뺀 A2부터 시작합니다. 실제 파일에서는 시트 이름과 범위를 파일에 맞게 바꿔 주세요.

4. 결과를 원본과 대조합니다. A1:C5에 제목 한 줄과 입고 내역 네 줄이 나타나는지, 각 행의 입고일·품목·수량이 원본과 같은지 확인합니다. 날짜가 숫자로 보이면 날짜 열을 선택해 표시 형식을 ‘날짜’로 바꾸세요.

제대로 입력했다면 결과는 다음과 같습니다.

수식은 A1 한 칸에만 들어 있지만, 결과는 여러 칸에 걸쳐 펼쳐집니다. 원본 시트의 수량을 고치면 이 결과도 바로 바뀝니다. 대신 펼쳐진 결과 중 한 칸만 골라 직접 고칠 수는 없습니다. 결과를 고정해 두고 따로 편집하고 싶다면, 결과 범위를 복사한 뒤 붙여넣기 옵션에서 값을 선택해 붙여넣으세요. 이렇게 붙여넣은 값은 원본이 바뀌어도 따라 바뀌지 않습니다.

VSTACK 오류와 결과가 이상할 때 해결 방법

수식을 입력했는데 결과가 예상과 다르다면, 아래 증상 가운데 해당하는 것부터 확인해 보세요.

#NAME? 오류: 함수 이름과 엑셀 버전 확인

함수 이름을 잘못 입력했거나, 사용 중인 엑셀이 VSTACK을 지원하지 않을 때 나타나는 오류입니다. 오타가 없는데도 같은 오류가 난다면 엑셀 버전을 확인하고, 아래 ‘VSTACK이 없는 엑셀에서 시트 합치는 방법’을 참고하세요.

#SPILL! 오류: 결과가 펼쳐질 자리 비우기

결과가 펼쳐질 자리를 다른 값이 막고 있을 때 나타나는 오류입니다. 펼쳐진 결과 칸에 나중에 무언가를 입력해도 같은 오류가 납니다. 수식을 엑셀 표 안에 넣었을 때도 마찬가지입니다. 결과가 들어갈 영역을 비우거나, 표 밖의 빈 셀로 수식을 옮기세요.

열이 섞이거나 #N/A 오류가 나올 때

한 시트는 입고일·품목·수량, 다른 시트는 품목·입고일·수량 순서로 적혀 있다면, 두 번째 시트의 품목이 결과의 입고일 열에 들어갑니다. VSTACK은 열 제목을 보고 순서를 바로잡아 주지 않으니, 원본의 열 순서부터 맞추세요.

가져오는 범위의 열 개수가 서로 다를 때도 문제가 생깁니다. 열이 적은 쪽의 빈자리에 #N/A가 나타나죠. 오류 표시를 감추기 전에, 원본에서 빠진 열이 있는지부터 확인하는 것이 좋습니다.

합친 결과 중간에 제목행이 반복될 때

모든 시트에서 A1:C3처럼 제목행까지 선택하면, 시트마다 제목이 한 번씩 결과에 들어갑니다. 첫 번째 시트만 제목행을 포함하고, 나머지 시트는 A2부터 선택하세요.

빈 행이나 0이 함께 나올 때

앞으로 입력할 행까지 생각해 범위를 넉넉하게 잡으면, 아직 비어 있는 행도 결과에 함께 들어옵니다. 이때 빈칸은 0으로 보일 수 있습니다. 빈 행을 자동으로 걸러내는 수식은 바로 다음 절에서 소개합니다.

다만 0을 무조건 지우기 전에, 원본에서 입력이 빠진 것인지 실제 입고 수량이 0인지부터 구분해야 합니다.

시트와 행이 계속 늘어날 때 쓰는 VSTACK 수식

기본 수식은 지정한 범위만 가져옵니다. 2월 시트에 네 번째 행을 추가해도, 수식이 A2:C3까지만 가리키고 있다면 새 행은 결과에 들어오지 않습니다. 새 시트를 만들었을 때도 마찬가지입니다. 실무에서는 시트도 행도 계속 늘어나니, 처음부터 아래 방법으로 수식을 짜 두는 것이 좋습니다.

시트가 많다면: 3D 참조로 여러 시트 한 번에 합치기

1월부터 12월까지 시트가 있다고 해서 '1월'!…,'2월'!…을 열두 번 적을 필요는 없습니다. 첫 시트와 마지막 시트의 이름을 콜론(:)으로 이으면 그 사이의 시트를 한 번에 지정할 수 있습니다. 이런 참조 방식을 3D 참조라고 하며, VSTACK에서도 쓸 수 있습니다.

=VSTACK('1월:12월'!A2:C100)

이 수식은 1월 탭부터 12월 탭까지, 그 사이에 있는 모든 시트의 A2:C100을 차례로 이어 붙입니다. 쓰기 전에 세 가지를 확인하세요.

• 모든 시트의 데이터가 같은 위치에 있어야 합니다. 시트마다 시작 행이나 열 순서가 다르면 결과가 어긋납니다.

• 시트 탭의 순서가 기준입니다. 1월과 12월 탭 사이에 끼워 넣은 시트는 자동으로 포함되고, 그 바깥으로 옮긴 시트는 빠집니다. 그러니 통합 시트는 맨 앞 탭처럼 이 범위 바깥에 두세요. 범위 안에 있으면 수식이 자기 자신을 참조하게 됩니다.

• 제목은 결과 시트에 직접 적습니다. 범위를 A2부터 잡았으므로, 통합 시트 A1:C1에 제목을 한 번 입력하고 수식은 A2에 넣습니다.

행이 늘어난다면: FILTER 함수로 빈 행 제거하기

위 수식처럼 범위를 A2:C100까지 넉넉하게 잡으면 앞으로 입력할 행도 반영됩니다. 하지만 아직 비어 있는 행까지 결과에 들어와서, 시트가 열두 장이면 빈 행이 1,000개를 넘을 수도 있죠. 이럴 때는 FILTER 함수로 빈 행을 걸러냅니다.

=LET(d,VSTACK('1월:12월'!A2:C100),FILTER(d,CHOOSECOLS(d,1)<>""))

길어 보이지만 구조는 단순합니다.

• LET(d, …): VSTACK으로 합친 결과에 d라는 이름을 붙입니다.

• CHOOSECOLS(d,1)<>"": 합친 결과의 첫 번째 열, 즉 입고일이 비어 있지 않은 행을 고릅니다.

• FILTER: 그 조건에 맞는 행만 남깁니다.

이 수식은 입고일이 비어 있으면 빈 행으로 보고 제외합니다. 입고일을 빠뜨리고 품목과 수량만 적은 행도 함께 빠지니, 원본에는 입고일을 항상 입력하도록 정해 두세요. 한 시트에 쌓인 행이 100개를 넘으면 범위의 끝 숫자도 늘려야 합니다.

시트마다 위치가 다르다면: 엑셀 표 이름으로 합치기

시트마다 데이터가 시작되는 위치가 제각각이라면, 각 원본 범위를 엑셀 표로 만들어 표 이름으로 참조하는 방법이 있습니다. 데이터 범위를 선택하고 Ctrl+T를 눌러 표를 만든 뒤, 표 이름을 입고1월, 입고2월처럼 바꿉니다. 그다음 통합 시트 A1:C1에 제목을 한 번 적고, A2에 아래 수식을 입력합니다.

=VSTACK(입고1월,입고2월)

표 바로 아래 행에 이어서 입력하면 표의 범위가 함께 늘어나고, 결과에도 자동으로 반영됩니다. 표에서 떨어진 곳에 입력한 내용은 포함되지 않으니, 새 행이 표 안에 들어갔는지 확인하세요. 새 시트를 만들었다면 그 시트의 표 이름을 수식에 직접 추가해야 합니다.

합친 데이터로 품목별 합계 구하기: UNIQUE와 SUMIFS

품목별 합계는 합친 목록을 바탕으로 따로 계산합니다. 기본 예시의 결과가 통합 시트 A1:C5에 있다면, E1과 F1에 품목, 입고 합계라고 적고 아래 두 수식을 입력해 보세요.

E2: =UNIQUE(B2:B5)
F2: =SUMIFS(C2:C5,B2:B5,E2#)

E2의 UNIQUE는 품목 이름을 중복 없이 하나씩 꺼내고, F2의 SUMIFS는 품목마다 수량을 더합니다. E2#은 E2에서 펼쳐진 품목 목록 전체를 가리키는 표시입니다. 결과는 볼트 180개(100+80), 너트 90개(50+40)입니다. 합친 결과의 행이 늘어나면 B2:B5, C2:C5의 끝 행도 결과의 마지막 행까지 넓혀 주세요.

이 숫자는 예시 기간 동안의 입고 합계일 뿐, 출고나 기존 재고까지 반영한 현재고는 아닙니다. 또 집계하기 전에는 같은 품목이 시트마다 같은 이름과 단위로 적혀 있는지 확인하세요. ‘볼트’와 ‘볼트(M8)’처럼 이름이 조금만 달라도 다른 품목으로 계산됩니다.

VSTACK이 없는 엑셀에서 시트 합치는 방법

VSTACK은 Microsoft 365용 Excel과 Excel 2024(Windows·Mac)에서 쓸 수 있는 함수입니다. Excel 2021, 2019, 2016 같은 이전 버전에는 이 함수가 없습니다. 회사 PC의 엑셀 버전은 Windows 기준으로 파일 > 계정 화면에서 확인할 수 있습니다.

이전 버전을 쓰고 있다면, 얼마나 자주 합치는지에 따라 아래 방법 가운데 하나를 고르면 됩니다.

한 번만 합친다면: 복사해 붙여넣기

시트가 몇 장뿐이라면 새 시트에 각 시트의 데이터를 차례로 복사해 붙이고, 중간에 들어간 제목행을 지우면 됩니다. 간단하지만 원본이 바뀔 때마다 다시 해야 합니다.

매달 반복해서 합친다면: 파워 쿼리 ‘추가’ 기능

Windows용 Excel 2016 이상에서는 파워 쿼리의 추가 기능으로 여러 시트의 데이터를 하나로 이어 붙일 수 있습니다. 각 시트의 데이터를 엑셀 표로 만들어 파워 쿼리로 불러온 뒤 하나로 합치는 방식입니다. 자세한 절차는 Microsoft의 쿼리 추가 안내를 참고하세요.

파워 쿼리는 VSTACK과 달리 열 제목을 기준으로 같은 열을 찾아 붙입니다. 그래서 시트마다 열 순서가 달라도 제목만 같으면 제자리에 들어가죠. 대신 원본을 고쳐도 결과가 바로 바뀌지 않으므로, 원본을 수정한 뒤에는 새로 고침을 눌러야 합니다.

파일이 여러 개라면: 파워 쿼리 폴더 가져오기

월별 자료가 시트가 아니라 별도 파일로 나뉘어 한 폴더에 쌓여 있다면, 파워 쿼리의 폴더 가져오기 기능이 더 알맞습니다. 절차는 여러 엑셀 파일 합치기: 파워 쿼리로 자재 입고 자료 취합하는 방법에서 확인하세요.

반복되는 엑셀 취합, 어떤 자료부터 살펴볼까요?

VSTACK이든 파워 쿼리든, 시트를 합치는 방법은 각 자료의 기준이 같아야 제대로 작동합니다. 열 순서나 품목 이름, 단위가 시트마다 다르면 합친 뒤에도 다시 대조하고 고치는 일이 남죠. 부서마다 같은 품목을 다른 이름으로 적거나, 매번 다른 시스템에서 자료를 내려받아 붙이고 있다면 더욱 그렇습니다.

그래서 반복 취합을 줄이려면 수식보다 먼저 어떤 자료를 누가 언제 갱신하는지, 품목코드와 단위가 서로 맞는지를 살펴봐야 합니다.

조슈아앤컴퍼니가 TSI의 조립 공정 생산관리 프로젝트에서 가장 많은 시간을 들인 일도 이 기준을 맞추는 것이었습니다. 당시 상세일정과 현장 작업 문서, ERP에서 내려받은 자재 자료는 같은 작업과 자재를 서로 다른 이름과 기준으로 관리하고 있었습니다. 그래서 세 자료를 잇는 공통 작업 기준부터 만들었고, 기존 엑셀 양식과 ERP는 그대로 둔 채 그 위에서 작업계획 발행과 실적 집계를 자동화했습니다.

이 글의 예시처럼 입고 내역을 일일이 찾아 대조하던 일도 달라졌습니다. 최종 보고 기준으로, 2시간 넘게 걸리던 자재 입고 확인은 ERP 자료를 반영하면 작업계획 옆에 자재 상태가 표시되도록 바뀌었습니다. 자세한 과정은 TSI 제조업 AI 적용 사례에서 볼 수 있습니다.

매번 취합하는 엑셀 파일이나 ERP 자료가 있다면, 지금 쓰는 문서와 반복해서 확인하는 항목을 알려 주세요. 어떤 기준을 맞추고 어떤 작업을 줄일 수 있을지 함께 살펴보겠습니다.

출처

• Microsoft 지원 — VSTACK 함수

• Microsoft 지원 — 동적 배열과 펼침 동작

• Microsoft 지원 — 여러 워크시트의 같은 셀 범위에 3D 참조 만들기

• Microsoft 지원 — Excel 표의 구조화 참조

• Microsoft 지원 — 쿼리 추가(Power Query)