데이터 합치기의 신세계! VSTACK과 HSTACK 함수 완벽 활용법

1. VSTACK과 HSTACK, 기본 문법 이해하기

이 두 함수 모두 여러 개의 배열(또는 셀 범위)을 인수로 받아 하나의 배열로 합쳐 준다는 점은 같지만, 합치는 '방향'이 다릅니다. VSTACK은 배열을 위아래로 쌓고, HSTACK은 배열을 좌우로 이어 붙입니다.

VSTACK: 배열(범위)을 세로로 쌓기

VSTACK은 여러 배열을 세로 방향으로 순서대로 이어 붙입니다. 구문은 다음과 같습니다.

=VSTACK(배열1, [배열2], ...)

첫 번째 인수인 배열1이 결과의 맨 위에 놓이고, 그 아래로 배열2, 배열3이 순서대로 쌓이는 방식입니다. 최대 253개까지의 배열을 한 번에 지정할 수 있습니다.

HSTACK: 배열(범위)을 가로로 잇기

HSTACK은 방향만 다를 뿐 사용법은 VSTACK과 동일합니다.

=HSTACK(배열1, [배열2], ...)

배열1이 결과의 가장 왼쪽에 놓이고, 그 오른쪽으로 배열2, 배열3이 차례로 이어집니다. 이 함수 역시 최대 253개의 배열까지 지정할 수 있습니다.

2. 실제 예제로 감 잡기

VSTACK과 HSTACK은 일반 셀 범위는 물론, Excel 표나 이름 정의된 범위(명명된 범위)에도 똑같이 적용할 수 있습니다.

VSTACK으로 세로 방향 합치기

세 개의 분기별 실적(A2:C6, A9:C13, A16:C20)이 서로 떨어진 위치에 있다고 가정해 보겠습니다. E1 셀에 다음 수식을 입력하면 세 범위가 위에서 아래로 순서대로 이어 붙습니다.

=VSTACK(A2:C6, A9:C13, A16:C20)

HSTACK으로 가로 방향 합치기

이번에는 A2:C6과 G2:G6, 두 범위를 나란히 붙이려면 다음과 같이 사용합니다.

=HSTACK(A2:C6, G2:G6)

수식을 입력한 셀을 기준으로, A2:C6의 오른쪽에 G2:G6 열이 자연스럽게 이어져 표시됩니다.

셀 주소(A2:C6 같은 형태) 대신 이름 정의된 범위(예: 실적_8월)를 사용하면 수식을 읽고 이해하기가 훨씬 쉬워집니다. 마우스로 범위를 일일이 드래그하지 않고도 수식 안에 이름을 빠르게 입력할 수 있다는 장점도 있습니다.

원본 데이터를 Excel 표로 만들면 더욱 강력해집니다

앞선 예제들은 원본 범위에 새로운 행이나 열이 추가되어도 VSTACK과 HSTACK이 이를 자동으로 인식하지 못합니다. 수식이 고정된 셀 범위를 참조하고 있기 때문입니다. 하지만 원본 데이터를 Excel 표로 지정해 두면 상황이 달라집니다. 표 이름을 활용한 구조적 참조를 사용하면, 표에 새로운 행이 추가될 때마다 결과에도 자동으로 반영됩니다.

=VSTACK(T_1Q, T_2Q, T_3Q)

이 방식은 여러 워크시트에 흩어진 데이터를 하나로 모을 때 특히 빛을 발합니다. 일반 범위라면 시트를 일일이 오가며 범위를 지정해야 하고, 시트 구조가 동일하다면 3차원 참조까지 고려해야 합니다. 표로 만들어 두면 통합 문서 안에서 표 이름이 서로 겹치지 않으므로 어느 시트에 있는 표인지 따로 지정할 필요조차 없습니다. 여기서 T_1Q는 1분기 실적에 대해 표로 만들고 표에 이름을 지정한 것입니다. 수식 입력 중 표 이름의 첫 글자만 입력하고 화살표 키로 골라 Tab 키를 누르면 바로 연결됩니다.

3. VSTACK과 HSTACK을 함께 조합하기

때로는 하나의 결과표를 만들기 위해 세로 방향 합치기와 가로 방향 합치기를 동시에 적용해야 하는 경우가 있습니다. 예를 들어 1열에는 지역, 2열에는 팀, 3열에는 실적, 4열에는 인센티브가 들어가는 새로운 표를 만들고 싶다고 가정해 보겠습니다. 지역별 정보(A2:C6, A9:C13, A16:C20)와 인센티브 정보(G2:G6, G9:G13, G16:G20)가 각각 따로 떨어져 있는 상황입니다.

이럴 때는 HSTACK 안에 VSTACK을 중첩해서 해결할 수 있습니다.

=HSTACK(
    VSTACK(A2:C6, A9:C13, A16:C20), 
    VSTACK(G2:G6, G9:G13, G16:G20)
)

이 수식이 처리되는 순서를 풀어서 보면, 먼저 안쪽의 VSTACK 두 개가 각각 지역 정보와 인센티브 정보를 세로로 쌓습니다. 그런 다음 바깥쪽의 HSTACK이 이 두 세로 배열을 가로로 이어 붙여 최종 표를 완성합니다.

이와 반대로, VSTACK 안에 HSTACK을 중첩할 수도 있습니다.

=VSTACK(
    HSTACK(A2:C6, G2:G6), 
    HSTACK(A9:C13, G9:G13), 
    HSTACK(A16:C20, G16:G20)
)

이번에는 지역별로 팀 정보와 인센티브 정보를 먼저 가로로 합친 다음, 세 지역의 결과를 다시 세로로 쌓아 올리는 순서입니다.

즉, 두 함수 중 어느 쪽을 바깥에 두고 어느 쪽을 안에 넣을지는 '어느 방향의 합치기 작업이 더 여러 번 필요한가'를 기준으로 판단하면 됩니다.

4. 크기가 다른 배열을 합칠 때 생기는 문제와 해결법

실무 데이터는 항상 깔끔하게 같은 크기로 정리되어 있지 않는 경우가 더 많습니다. VSTACK과 HSTACK으로 크기가 서로 다른 배열을 합치면 어떤 일이 벌어지는지 살펴보겠습니다.

VSTACK에서 너비가 다른 배열을 합칠 때

VSTACK으로 배열을 세로로 쌓으면, 결과의 전체 행 수는 합친 모든 배열의 행 수를 더한 값이 됩니다. 반면 결과의 열 수는 합쳐진 배열 중 가장 넓은(열이 많은) 배열의 열 수에 맞춰집니다.

=VSTACK(A2:D6, A9:C13, A16:C20)

예를 들어 A2:D6은 4개 열, 나머지 두 배열은 3개 열이라면, 결과는 4개 열 너비로 만들어집니다. 이때 열 수가 부족한 배열들은 남는 자리를 #N/A 오류로 채워 넣습니다. 데이터가 없어서가 아니라 크기를 억지로 맞추다 보니 생기는 오류이므로, 다음과 같이 IFNA 함수로 전체 수식을 감싸 오류 대신 빈 문자열로 바꿔 주는 것이 좋습니다.

=IFNA(VSTACK(A2:D6, A9:C13, A16:C20), "")

HSTACK에서 높이가 다른 배열을 합칠 때

HSTACK도 원리는 같습니다. 다만 방향이 반대라서, 전체 열 수는 각 배열의 열 수를 더한 값이 되고, 전체 행 수는 합쳐진 배열 중 가장 높은(행이 많은) 배열의 행 수에 맞춰집니다. 높이가 부족한 배열은 나머지 칸이 #N/A 오류로 채워지므로, 이때도 IFNA로 감싸 주면 결과가 훨씬 깔끔해집니다.

=IFNA(HSTACK(A2:C7, G2:G6), "")

5. 빈 셀과 0을 다르게 처리하기

VSTACK과 HSTACK을 사용할 때 종종 헷갈리는 부분이 하나 더 있습니다. 원본 데이터에 있는 빈 셀과 숫자 0을 두 함수가 구분하지 못하고 모두 0으로 바꿔서 표시한다는 점입니다.

예를 들어 어떤 지역은 실적이 실제로 0원인 반면(영업 활동은 했지만 매출이 발생하지 않은 경우), 다른 지역은 아직 이번 달 마감 집계가 입력되지 않아 셀이 비어 있는 상황을 생각해 보겠습니다. 원본에서는 이 둘의 의미가 분명히 다르지만, VSTACK으로 데이터를 합치면 두 경우 모두 0으로 똑같이 표시되어 어떤 지역이 진짜 실적 0원을 기록한 것인지, 어떤 지역이 아직 집계가 안 된 것인지 구분할 수 없게 됩니다.

이 문제는 SUBSTITUTE 함수로 VSTACK(또는 HSTACK) 수식 전체를 감싸고, 빈 문자열을 빈 문자열로 치환하는 다소 특이한 방법으로 해결할 수 있습니다.

=SUBSTITUTE(VSTACK(A2:C6, A9:C13, A16:C20), "", "")

SUBSTITUTE의 '찾을 문자열'과 '바꿀 문자열'이 둘 다 빈 문자열이라는 점이 의아하게 느껴질 수 있지만, 이렇게 하면 원래 비어 있던 셀은 빈 문자열로, 실제로 0이 입력된 셀은 그대로 0으로 표시되어 두 상태를 정확히 구분할 수 있게 됩니다.

6. 열 머리글을 함께 표시하기

VSTACK을 사용할 때는 원칙적으로 각 배열에서 열 머리글(제목 행)을 제외하고 데이터 부분만 참조해야 합니다. 머리글까지 포함해서 쌓으면 "부품명", "수량" 같은 문자열이 결과 중간중간에 반복해서 섞여 들어가고, 이는 나중에 정렬이나 필터링을 할 때 실제 데이터처럼 취급되어 결과가 뒤죽박죽될 수 있습니다.

다만 머리글 없이 데이터만 쌓다 보면, 결과표만 봐서는 각 열이 무엇을 의미하는지 알아보기 어렵다는 단점이 생깁니다. 이럴 때는 VSTACK 결과 바로 위 셀에 원본 머리글을 그대로 참조하는 수식을 하나 더 넣어 주면 됩니다.

=A1:C1

이렇게 해 두면 원본의 머리글 행(A1:C1)이 수정될 때마다 위쪽에 표시된 머리글도 자동으로 함께 바뀌어, 항상 최신 상태의 제목을 유지할 수 있습니다.

HSTACK을 쓸 때도 같은 방식으로 행 머리글을 표시할 수 있습니다. 다만 대부분의 잘 정리된 데이터는 '열'이 항목(필드)을, '행'이 하나하나의 기록(레코드)을 나타내는 구조이므로, 굳이 행 머리글이 필요한 상황이라면 차라리 데이터를 전치(행과 열을 뒤바꿈)한 뒤 열 머리글 방식으로 표시하는 편이 더 자연스러울 수 있습니다.

원본이 Excel 표라면 셀 주소 대신 표의 머리글 행을 가리키는 구조적 참조를 사용할 수 있습니다.

=T_1Q[#Headers]

이 방법의 장점은 나중에 표에 열이 추가되더라도, 구조적 참조를 사용한 머리글 수식과 VSTACK 수식 모두에 자동으로 반영된다는 점입니다. VSTACK 결과의 너비는 어차피 참조한 배열 중 가장 넓은 배열에 맞춰지므로, 표에 새로운 열을 추가하고 데이터를 채워 넣기만 하면 나머지는 수식이 알아서 처리해 줍니다.

7. 합쳐진 결과를 원하는 순서로 정렬하기

VSTACK의 가장 큰 장점 중 하나는, 서로 다른 범위에 흩어져 있던 같은 형식의 데이터를 하나의 연속된 표로 모아 비교·분석을 훨씬 수월하게 만들어 준다는 점입니다. 그런데 이렇게 합친 결과를 원하는 기준으로 정렬하고 싶다면 어떻게 해야 할까요?

엑셀의 [정렬] 기능으로는 동적 배열의 정렬 순서를 바꿀 수 없습니다. 수식이 입력된 첫 번째 셀만 '진짜' 활성 셀로 인식되고, 나머지 스필된 셀들은 그 결과가 흘러넘친 형태라서 정렬할 수 있는 독립된 값으로 취급되지 않기 때문입니다.

이런 경우, SORT 함수 안에 VSTACK 수식 전체를 중첩하면 원하는 정렬을 적용할 수 있습니다.

=SORT(VSTACK(T_1Q, T_2Q, T_3Q), 3, -1)

여기서 두 번째 인수인 3은 '합쳐진 배열의 3번째 열을 기준으로 정렬하라'는 뜻이고, 세 번째 인수인 -1은 '내림차순으로 정렬하라'는 의미입니다. 이렇게 하면 세 개의 표를 하나로 합침과 동시에, 특정 열(예: 실적)이 높은 순서대로 자동 정렬된 결과표를 얻을 수 있습니다.

HSTACK과 SORT를 함께 쓸 때는 인수의 의미가 조금 달라집니다. 두 번째 인수는 정렬 기준이 되는 '행' 번호를, 세 번째 인수는 오름차순이면 1, 내림차순이면 -1을 지정하며, 네 번째 인수는 반드시 TRUE로 지정해야 엑셀이 '세로가 아니라 가로 방향으로 정렬하라'는 의미가 됩니다.

MVP TIP — VSTACK·HSTACK 핵심 요약

지금까지 다룬 내용 중 실무에서 자주 마주치는 상황과 해결 방법을 표로 정리했습니다.

상황	해결 방법
배열을 세로로 합치고 싶을 때	VSTACK(배열1, 배열2, ...)
배열을 가로로 합치고 싶을 때	HSTACK(배열1, 배열2, ...)
크기가 달라 #N/A가 나올 때	IFNA로 감싸 빈 문자열 처리
빈 셀과 0을 구분해야 할 때	SUBSTITUTE(결과, "", "")
합친 결과를 정렬하고 싶을 때	SORT(VSTACK(...), 열번호, 방향)

마치며

VSTACK과 HSTACK은 여러 곳에 흩어진 데이터를 한자리에 모으는 작업을 수식 한 줄로 처리할 수 있는 매우 실용적인 함수입니다. 보조 셀이나 복잡한 중첩 수식 없이도 원본이 바뀌면 결과가 자동으로 갱신되는 '살아 있는 통합 표'를 만들 수 있으므로 반복적으로 데이터를 취합해야 하는 업무 환경에서 특히 진가를 발휘합니다.

다만 두 함수가 엑셀 데이터를 재구성하는 유일한 도구는 아닙니다. 배열을 하나의 열이나 행으로 풀어 주는 TOCOL과 TOROW, 1차원 데이터를 원하는 크기의 2차원 배열로 감싸 주는 WRAPCOLS와 WRAPROWS, 그리고 별도의 피벗 테이블 없이도 수식만으로 데이터를 그룹화하고 집계할 수 있는 PIVOTBY 함수도 함께 알아 두면, 상황에 맞는 더 다양한 방식으로 데이터를 다룰 수 있게 됩니다.

자주 묻는 질문 (FAQ)