[엑셀 실무 팁] VSTACK · HSTACK · TOCOL · WRAPROWS 함수로 흩어진 12개월 시트·부서별 표를 복붙 없이 1초 만에 하나로 합치고 재구조화하는 법

직장인들이 매월 말일이나 분기 결산 시즌마다 가장 많은 야근 시간을 허비하는 작업은 무엇일까요? 바로 ‘여러 시트나 부서에서 넘어온 표를 하나의 마스터 시트로 합치는 작업’입니다. 1월_매출부터 12월_매출까지 12개의 시트, 혹은 서울지점·부산지점·대전지점 시트에 흩어져 있는 데이터를 합치기 위해 아직도 시트마다 돌아다니며 Ctrl + C, Ctrl + V를 반복하고, 누군가 원본 시트에 행 하나를 추가하면 처음부터 다시 복사해 붙여넣고 계시지는 않나요?

지난 엑셀 실무 팁 칼럼이었던 FILTER · UNIQUE · SORT 함수 3대장 조합으로 매크로 없이 ‘실시간 자동 갱신 보고서’ 만드는 법과 TEXTSPLIT · TEXTBEFORE · TEXTAFTER 함수로 지저분한 텍스트 1초 자동 분리하는 법에 이어, 오늘은 엑셀 365 및 엑셀 2024의 ‘배열 모양 재편 및 다중 표 병합 함수 4총사(VSTACK · HSTACK · TOCOL · WRAPROWS)’를 완벽하게 마스터해 보겠습니다.

이 네 가지 함수만 손에 익히면 복잡한 VBA 매크로나 무거운 파워 쿼리(Power Query) 편집기를 열지 않고도, 셀 하나에 수식 한 줄만 입력해 흩어진 12개월 시트를 1초 만에 하나로 합치고(VSTACK), 필요한 열만 골라 조립하며(HSTACK), 2차원 표와 1차원 리스트를 자유자재로 변환(TOCOL, WRAPROWS)할 수 있습니다.

1. 다중 표 병합 & 배열 재구조화 함수 4총사 핵심 구문 및 인수 비교표

먼저 오늘 다룰 핵심 함수 4가지(자매 함수 TOROW, WRAPCOLS 포함 총 6가지)가 각각 어떤 방향으로 데이터를 합치고 변형하는지 한눈에 비교해 보겠습니다.

1-1. TOCOL 함수의 핵심 비밀 병기: 두 번째 인수 [ignore(무시 옵션)]

TOCOL(array, [ignore], [scan_by_column]) 함수를 쓸 때 실무자들이 가장 열광하는 옵션이 바로 두 번째 인수인 [ignore]입니다. 범위를 1열로 펼칠 때 중간에 섞여 있는 빈 셀이나 오류값을 어떻게 처리할지 숫자 하나로 지정할 수 있습니다.

• 0 (또는 생략): 모든 값 유지 (빈 셀은 0으로 표시됨)
• 1 (가장 많이 사용!): 빈 셀(Blank)을 완벽하게 무시하고 데이터가 있는 셀만 압축해서 1열로 추출
• 2: #N/A, #DIV/0! 등 오류(Error)만 무시
• 3 (실무 마스터 추천!): 빈 셀과 오류값을 동시에 모두 무시하고 깨끗한 정상 데이터만 1열로 추출

또한 세 번째 인수인 [scan_by_column]을 생략하거나 FALSE로 두면 기본값인 ‘행 방향(왼쪽→오른쪽 읽은 뒤 다음 줄)’으로 데이터를 읽어 내리고, TRUE(또는 1)를 입력하면 ‘열 방향(첫 번째 열 위→아래 다 읽은 뒤 두 번째 열)’ 순서로 1열을 만들어 줍니다.

2. [실무 수식 블록 4선] 복사해서 바로 쓰는 VSTACK · HSTACK · TOCOL · WRAPROWS 공식

이제 실제 회사 업무에서 가장 빈번하게 마주치는 4가지 상황을 해결하는 완벽한 수식 블록을 살펴보겠습니다. 특히 기존 강좌인 3차원 참조의 활용과 엑셀의 표(테이블) 기능 활용하기를 함께 응용하면 효과가 배가됩니다.

2-1. 예제 1: 1월~12월 12개 시트의 데이터를 빈 행(0) 없이 하나로 자동 합치기 (VSTACK + 3D 참조 + FILTER)

매월 시트에 행이 몇 개 추가될지 몰라 범위를 넉넉하게 A2:E200으로 잡으면, 데이터가 없는 빈 행들이 통합 시트에 0, 0, 0, 0, 0으로 수백 줄 찍히는 문제가 발생합니다. 이때 LET 함수(또는 중첩 수식)와 FILTER를 결합하면 데이터가 실제 입력된 행만 기가 막히게 걸러내어 합쳐줍니다.

[실무 수식 1] 1월~12월 시트 3차원 일괄 병합 + 빈 행(0) 자동 제거 마스터 수식

• 방법 A: 각 시트를 엑셀 '표(Ctrl + T)'(예: 표_1월, 표_2월, 표_3월)로 만들어 둔 경우 (가장 깔끔!)
=VSTACK(표_1월[#모두], 표_2월, 표_3월)
→ 첫 번째 표만 머리글 포함([#모두])으로 가져오고, 나머지 표는 데이터 영역만 이어 붙이므로 빈 행이 아예 생기지 않으며 행 추가 시 즉시 자동 반영됨!

• 방법 B: 일반 시트 탭('1월'부터 '12월'까지 연속 배치)을 3차원 참조로 한 번에 합치면서 빈 행 제거하기
=LET(
    통합데이터, VSTACK('1월:12월'!A2:E200),
    첫열기준, CHOOSECOLS(통합데이터, 1),
    FILTER(통합데이터, 첫열기준 <> "")
)
→ 작동 원리:
  1) VSTACK('1월:12월'!A2:E200)이 1월부터 12월 시트 사이에 있는 모든 시트의 A2:E200 영역을 위아래로 합침
  2) CHOOSECOLS(통합데이터, 1)로 첫 번째 열(예: 일자 또는 주문번호)만 추출
  3) 첫 번째 열이 빈칸("")이 아닌 행만 FILTER로 걸러내어 0으로 표시되는 빈 행을 100% 제거!

2-2. 예제 2: 떨어진 열만 골라 조립하고 왼쪽에 ‘연번’ 열 자동 생성하기 (HSTACK + SEQUENCE)

원본 인사·매출 테이블에는 열이 20개(A열~T열)나 있는데, 임원 보고용 요약 표에는 왼쪽에 [No. 연번]을 붙이고 원본의 [B열(부서)], [D열(성명)], [M열(매출액)] 딱 3개 열만 가져와 나란히 조립하고 싶을 때 HSTACK을 사용합니다. XLOOKUP 함수에서 여러 불연속 열을 반환할 때도 이 HSTACK이 핵심 무기로 쓰입니다.

[실무 수식 2] HSTACK으로 불연속 열 조립 + 자동 일련번호(SEQUENCE) 붙이기

• 기본 불연속 열 조립 (부서 + 성명 + 매출액만 합치기):
=HSTACK(B2:B50, D2:D50, M2:M50)

• 왼쪽에 1, 2, 3... 자동 연번 열을 추가하면서 매출액 내림차순 정렬까지 한 번에 끝내기:
=LET(
    정렬표, SORT(HSTACK(B2:B50, D2:D50, M2:M50), 3, -1),
    순위연번, SEQUENCE(ROWS(정렬표)),
    HSTACK(순위연번, 정렬표)
)
→ 결과: [1위~49위 연번 | 부서 | 성명 | 매출액] 4열 표가 1초 만에 동적 배열로 쏟아져 나옵니다!

2-3. 예제 3 & 4: 여러 열 명단 1열 통합(TOCOL) & 1열 복붙 데이터 정식 표 변환(WRAPROWS)

[실무 수식 3] 5개 열(A2:E30)에 중구난방 적힌 참석자 명단을 빈칸 없이 1열로 모으고 중복 제거 + 가나다순 정렬

=SORT(UNIQUE(TOCOL(A2:E30, 1)))
→ TOCOL(A2:E30, 1)이 빈칸을 모두 무시하고 1열로 모은 뒤, UNIQUE가 중복 참석자를 제거하고, SORT가 가나다순으로 깔끔하게 정렬!

[실무 수식 4] PDF나 웹사이트에서 복사했더니 A열 한 줄에 [품목명 / 규격 / 단가 / 수량]이 4칸씩 반복해서 아래로 쭉 붙어버렸을 때 4열 표로 즉시 복원하기

=WRAPROWS(A2:A401, 4, "")
→ A2:A401의 400개 셀을 4개씩 끊어서 가로 행으로 배치해 100행 × 4열짜리 정식 표로 1초 만에 복원! (세 번째 인수 "" 덕분에 마지막 행 개수가 모자라도 #N/A 대신 빈칸으로 깔끔하게 처리됨)

3. 실무에서 자주 겪는 오류(#N/A · 빈 셀 0 · 열 순서 불일치) 100% 해결 가이드

VSTACK과 WRAPROWS를 처음 실무 파일에 적용할 때 당황하게 만드는 3가지 대표 오류와 해결책을 아래 표로 정리했습니다.

4. 자주 묻는 질문 (FAQ Top 4) & 실무 적용 체크리스트