여러 장의 시트를 하나로! VSTACK과 HSTACK으로 데이터 수직/수평 병합하기

여러 장의 시트를 하나로! VSTACK과 HSTACK 함수로 데이터 수직/수평 병합하기

안녕하세요. 대한민국 직장인들의 피 같은 퇴근 시간을 지켜드리고, 엑셀 실무의 본질적인 구조를 혁신해 드리는 직장인 엑셀 꿀팁 저장소입니다! 오늘은 엑셀을 다루는 모든 실무자들이 일상적으로 겪는 가장 고통스러운 노가다, 바로 ‘수십 장으로 쪼개진 시트 데이터를 하나의 마스터 시트로 합치는 작업’을 단 1초 만에, 그것도 완벽하게 자동화해 주는 혁명적인 동적 배열 함수, VSTACK 함수와 HSTACK 함수에 대해 아주 깊고 상세하게 파헤쳐 보겠습니다.

지난 시간에는 정규표현식을 활용해 지옥 같은 비정형 텍스트 데이터 속에서 원하는 알맹이만 레이저처럼 쏙쏙 뽑아내는 REGEXEXTRACT 함수 의 놀라운 세계를 경험했습니다. 데이터의 ‘정제(Cleaning)’ 기술을 마스터했으니, 이제는 정제된 그 수많은 데이터 조각들을 하나의 거대한 데이터베이스로 ‘결합(Merging)’할 차례입니다.

매월 말일, 혹은 연말 결산 시즌이 다가오면 영업지원팀 최 사원의 얼굴에는 짙은 다크서클이 내려앉습니다. 전국 50개 지점에서 각각 엑셀 파일로 보내온 ‘월별 매출 실적’ 시트 50장을 하나의 통합 마스터 시트로 합쳐야 하기 때문입니다. 최 사원이 이 작업을 수행하는 방식은 아주 원초적입니다. 1번 지점 시트를 열고 Ctrl + C, 통합 시트로 와서 맨 아래 줄을 찾아 Ctrl + V. 2번 지점 시트를 열고 다시 복사, 통합 시트 맨 아래에 붙여넣기… 이 짓을 무려 50번 반복합니다. 마우스 휠을 내리느라 검지손가락에는 쥐가 날 지경이고, 실수로 한 줄을 빼먹거나 덮어쓰기라도 하는 날에는 수십억 원의 매출 데이터가 증발하는 대참사가 벌어집니다.

더 끔찍한 상황은 그다음입니다. 50개의 시트를 꼬박 3시간에 걸쳐 다 합쳐서 팀장님께 보고를 올렸는데, 부산지점에서 다급한 전화가 옵니다. “최 사원님! 저희 매출액 하나가 잘못 입력돼서 파일 다시 보냈습니다! 업데이트 부탁드려요!” 최 사원은 입술을 깨물며 합쳐둔 통합 시트에서 부산지점 데이터를 찾아 지우고, 새로 온 데이터를 다시 복사해서 끼워 넣습니다. 데이터가 ‘정적(Static)’으로 죽어있기 때문에 발생하는 비극입니다. 원본이 바뀌면 복사본도 처음부터 다시 만들어야 하는 것이죠.

과거의 엑셀 고수들은 이 문제를 해결하기 위해 VBA(매크로) 코딩을 배우거나, 파워 쿼리(Power Query)라는 복잡한 추가 기능을 동원해야만 했습니다. 둘 다 배우기 어렵고, 진입 장벽이 높으며, 파일이 무거워지거나 보안 경고가 뜨는 단점이 있었습니다. 하지만 이제 시대가 바뀌었습니다. 마이크로소프트가 오직 이 ‘데이터 병합’만을 위해 탄생시킨 궁극의 무기, VSTACK과 HSTACK 함수를 출시했기 때문입니다.

엑셀 데이터 구조의 기본: 세로 병합(VSTACK)과 가로 병합(HSTACK)

새로운 함수를 무작정 외우기 전에, 데이터 병합의 두 가지 방향성에 대해 명확히 이해해야 합니다. 실무에서 데이터를 합치는 방식은 크게 두 가지로 나뉩니다.

• 수직 병합 (Vertical Stacking): 1월 데이터 밑에 2월 데이터를 붙이고, 그 밑에 3월 데이터를 붙여서 행(Row)을 길게 늘리는 방식입니다. 데이터베이스를 구축할 때 가장 기본이 되는 방식이며, VSTACK 함수가 이 역할을 담당합니다.
• 수평 병합 (Horizontal Stacking): A열에 있는 ‘직원 명부(이름, 사번)’ 데이터의 바로 오른쪽인 C열부터 ‘급여 대장(기본급, 수당)’ 데이터를 이어 붙여 열(Column)을 넓히는 방식입니다. 항목을 확장할 때 사용하며, HSTACK 함수가 이 역할을 담당합니다.

두 함수의 이름에 들어간 ‘STACK’은 프로그래밍 용어로 ‘차곡차곡 쌓아 올리다’라는 뜻입니다. 이름 그대로 지정한 범위들을 위에서 아래로, 혹은 왼쪽에서 오른쪽으로 테트리스 블록 쌓듯 차곡차곡 이어 붙여주는 마법의 동적 배열 함수입니다.

VSTACK 함수 완벽 해부: 수십 장의 시트를 1초 만에 합치기

먼저 실무 활용도가 99%에 달하는 세로 병합의 끝판왕, VSTACK 함수부터 상세히 뜯어보겠습니다. VSTACK의 기본 문법은 엑셀 함수 중에서도 손에 꼽힐 정도로 직관적이고 단순합니다.

=VSTACK(array1, [array2], [array3], …)
=VSTACK(합칠범위1, 합칠범위2, 합칠범위3, …)

단순히 괄호 안에 합치고 싶은 범위들을 쉼표(,)로 구분해서 계속 넣어주기만 하면 됩니다. 인수는 최대 253개까지 넣을 수 있습니다. 하지만 이 방식대로라면 50개 지점의 시트를 합칠 때 =VSTACK(서울!A:D, 부산!A:D, 대구!A:D, ...) 식으로 50번을 써야 할까요? 절대 아닙니다. 여기서 엑셀 고수들만 안다는 전설의 기술, ‘3D 참조(3D Reference)’ 기법이 등장합니다.

실전 예제 1: 3D 참조를 활용한 12개월 월별 시트 초고속 병합

최 사원의 엑셀 파일에는 [1월], [2월], [3월] … [12월] 이라는 이름의 시트가 12장 있고, 각 시트의 A2부터 F100까지 월별 매출 데이터가 들어있다고 가정해 보겠습니다. 통합 마스터 시트인 [연간결산] 시트의 A2 셀을 클릭하고 다음과 같이 수식을 입력합니다.

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

이 한 줄의 수식이 의미하는 바는 엄청납니다. “엑셀아, 첫 번째 시트인 ‘1월’부터 마지막 시트인 ’12월’ 사이에 있는 모든 시트의 A2:F100 범위를 통째로 복사해서, 여기 연간결산 시트에 위에서 아래로 차례대로 다 쏟아부어줘!”라는 뜻입니다.

이 수식을 입력하는 방법은 더 쉽습니다. =VSTACK( 까지 타이핑한 후, 마우스로 하단 시트 탭의 [1월]을 클릭합니다. 그리고 키보드의 Shift 키를 꾹 누른 상태로 하단 시트 탭의 마지막 [12월]을 클릭합니다. 그러면 1월부터 12월까지의 모든 시트가 그룹으로 다중 선택됩니다. 그 상태에서 시트 내부의 A2:F100 범위를 마우스로 드래그하고 엔터를 치면 끝납니다. 단 5초의 조작으로 1년 치, 수만 줄의 데이터가 완벽하게 하나의 표로 수직 병합됩니다.

실전 예제 2: 죽어있는 복붙을 넘어서는 ‘동적 연동’의 마법

이 함수의 진정한 사기성(?)은 단순히 한 번 합치고 끝나는 것이 아니라, 원본 데이터와의 실시간 동기화(Live Sync)에 있습니다.

앞서 최 사원의 에피소드에서, 부산지점(예를 들어 3월 시트)의 원본 데이터에 오류가 있어 숫자를 수정해야 하는 상황을 기억하시나요? 과거에 수동 복사-붙여넣기를 했다면 통합 시트도 수동으로 고쳐야 했습니다. 하지만 VSTACK으로 연결해 둔 상태라면? 그저 3월 시트에 가서 숫자를 ‘수정’만 하시면 됩니다. 그러면 VSTACK 수식이 걸려있는 통합 마스터 시트의 값도 실시간으로, 즉각적으로, 완전히 자동으로 업데이트됩니다.

이것은 엑셀 작업의 패러다임이 ‘일회성 노가다’에서 ‘완전 자동화 시스템 구축’으로 넘어갔음을 의미합니다. 여러분은 그저 매월 새로운 시트를 템플릿에 맞춰 추가하기만 하면, 마스터 시트는 알아서 덩치를 키우며 최신 상태를 유지하게 됩니다.

실무 적용 시 발생하는 치명적 문제: 빈 셀(0)과 #N/A 에러 해결법

하지만 실무 엑셀은 교과서처럼 호락호락하지 않습니다. VSTACK 함수를 막상 실무에 적용해 보면 두 가지 치명적인 장벽에 부딪히게 됩니다. 이 장벽을 넘어야만 진정한 엑셀 마스터로 인정받을 수 있습니다.

문제 1: 끝없이 나타나는 지옥의 ‘0 (Zero)’ 값 파티

앞선 예제에서 우리는 넉넉하게 A2:F100이라는 범위를 잡았습니다. 그런데 1월 시트에는 데이터가 50행까지만 있고, 51행부터 100행까지는 ‘빈 셀’이라면 어떻게 될까요? VSTACK 함수는 빈 셀을 무시하지 않고 친절하게도(?) 숫자 0으로 변환해서 그대로 통합 시트에 합쳐버립니다. 그 결과, 1월 데이터 50줄 밑에 0이 50줄 깔리고, 그 밑에 2월 데이터가 시작되는 끔찍한 이빨 빠진 표가 탄생합니다.

이 ‘빈 셀 = 0’ 문제를 해결하기 위해서는 앞서 Code 6번에서 배웠던 FILTER 함수와의 콜라보레이션이 필수적입니다. 필터 함수를 이용해 “합친 데이터 중에서 빈칸인 것들은 싹 다 걸러내고 가져와!”라고 명령을 한 겹 씌워주는 것입니다.

=FILTER(VSTACK('1월:12월'!A2:F100), VSTACK('1월:12월'!A2:A100)<>"")

수식이 조금 길어졌지만 원리는 간단합니다. FILTER(가져올 전체 데이터, 조건) 구조에서, 조건 부분에 “A열(보통 날짜나 순번이 들어가는 필수 열)이 공백("")이 아닌(<>) 데이터만 가져와라!”라고 지정한 것입니다. 이 수식을 적용하는 순간 중간중간 끼어있던 수백 개의 0 데이터들이 연기처럼 사라지고, 오직 값이 입력된 진짜 데이터들만 빈틈없이 압축되어 수직으로 딱 달라붙게 됩니다. 실무에서 VSTACK을 쓸 때는 이 FILTER 함수 조합이 거의 필수 공식(Formula)이라고 암기하셔도 좋습니다.

문제 2: 머리글(헤더)이 자꾸 중간에 끼어드는 현상

각 월별 시트의 1행에는 보통 [날짜, 담당자, 품목, 수량, 매출액] 같은 머리글(Header)이 적혀있습니다. 3D 참조 범위를 A1부터 잡아버리면 통합 시트 중간중간에 저 머리글이 계속 반복해서 등장하는 지저분한 결과가 나옵니다.

이를 방지하려면 범위를 잡을 때 반드시 머리글을 제외한 순수 데이터 범위(예: A2부터)만 지정해야 합니다. 통합 마스터 시트의 1행에는 사용자가 직접 머리글을 한 번만 타이핑해 두고, 2행부터 VSTACK 수식을 걸어 데이터를 쏟아지게 만드는 것이 가장 깔끔한 데이터 아키텍처 설계 방식입니다.

HSTACK 함수: 가로로 쭉쭉 이어 붙이기 (수평 병합)

세로로 데이터를 쌓는 방법을 완벽히 익혔으니, 이제 가로로 확장하는 HSTACK 함수를 살펴보겠습니다. 문법은 VSTACK과 100% 동일하며, 방향만 왼쪽에서 오른쪽으로 진행될 뿐입니다.

=HSTACK(array1, [array2], [array3], …)

실전 예제: 인사팀과 재무팀 데이터의 가로 결합

당신은 경영지원본부의 총괄 담당자입니다. 인사팀에서 관리하는 ‘직원 기본 정보(A열 사번, B열 이름, C열 부서)’ 파일이 있고, 재무팀에서 관리하는 ‘직원 연봉 정보(D열 사번, E열 기본급, F열 성과급)’ 파일이 따로 분리되어 있습니다. 경영진 보고를 위해 이 두 표를 좌우로 나란히 이어 붙여 하나의 거대한 직원 종합 대장을 만들어야 합니다. (단, 두 표의 사원 정렬 순서는 이미 동일하게 맞춰져 있다고 가정합니다.)

새로운 시트를 열고 =HSTACK(A1:C4, E1:F4) 이라고 입력합니다.

엔터를 치면? 인사팀의 3개 열 바로 오른쪽에 재무팀의 2개 열이 찰싹 달라붙어 총 5개 열로 이루어진 완벽한 와이드 폼(Wide Form) 형태의 데이터베이스가 순식간에 완성됩니다. 과거에 이 작업을 하려면 VLOOKUP이나 XLOOKUP을 열마다 일일이 걸어서 데이터를 끌어와야 했지만, 정렬 순서만 같다면 HSTACK 함수 하나로 수백 개의 열을 한 번에 가로로 병합할 수 있습니다.

엑셀 마스터 클래스: VSTACK + SORT + UNIQUE 궁극의 대시보드 만들기

지금까지 배운 VSTACK의 3D 참조와 타 함수들의 중첩을 활용하면, 과거에는 상상도 못 했던 실시간 자동화 대시보드를 함수 단 한 줄로 구축할 수 있습니다.

12개월 치의 판매 일지가 있는 상황에서, 팀장님이 “올해 우리 제품을 구매한 전체 고객사(거래처) 명단을 가나다순으로 중복 없이 딱 1번씩만 깔끔하게 뽑아줘”라고 지시했다고 가정해 보겠습니다. 12개 시트에 흩어져 있는 수만 개의 거래처 이름을 모두 모아서, 중복을 제거하고, 오름차순 정렬까지 해야 하는 엄청난 난이도의 작업입니다.

기존 방식이라면 12개 시트 데이터를 모두 복사 붙여넣기로 합친 뒤 -> [데이터] 탭에서 [중복된 항목 제거]를 클릭하고 -> 다시 [정렬] 기능을 클릭하는 수동 과정을 거쳐야 합니다. 하지만 마이크로소프트 365의 동적 배열 함수 3대장을 엮어주면 단 한 번의 수식으로 이 과정이 영구적으로 자동화됩니다.

=SORT(UNIQUE(FILTER(VSTACK('1월:12월'!C2:C100), VSTACK('1월:12월'!C2:C100)<>"")))

1. VSTACK & FILTER: 먼저 1월부터 12월까지의 C열(거래처명 열) 데이터를 수직으로 모두 모은 뒤, 빈칸을 깔끔하게 필터링하여 날려버립니다.
2. UNIQUE 함수: 그렇게 모인 수만 개의 거래처 이름 중에서 중복되는 것들을 전부 삭제하고 고유한(Unique) 이름만 남깁니다.
3. SORT 함수: 마지막으로 고유한 거래처 이름들을 가나다순(오름차순)으로 예쁘게 정렬하여 화면에 스필(Spill)해 줍니다.

이 수식의 위대함은 내일 11월 시트에 ‘새로운 신규 거래처’가 한 줄 추가입력되는 순간, 이 대시보드의 명단에도 해당 거래처가 가나다순 정렬 위치에 맞게 자동으로 쏙 들어온다는 데 있습니다. 인간의 개입이 전혀 필요 없는, 그야말로 살아 숨 쉬는 데이터 파이프라인(Data Pipeline)이 완성된 것입니다.

사용 전 반드시 확인해야 할 버전 호환성 체크

이토록 눈물 나게 편리한 VSTACK과 HSTACK 함수는 너무나 아쉽게도 엑셀의 최신 기술 생태계에만 존재하는 신기능입니다. 이 엄청난 혜택은 현재 Microsoft 365 구독형 버전과 Excel 2024 (영구 소장용 최신 버전) 그리고 무료로 제공되는 웹용 Excel(Excel for the Web) 사용자들에게만 허락되어 있습니다.

만약 여러분의 회사 컴퓨터에 설치된 엑셀이 Excel 2016이나 2019, 2021 같은 구형 버전이라면, 셀에 =VSTACK을 입력해도 #NAME?이라는 에러를 뱉어내며 작동하지 않을 것입니다. 이 함수 하나만으로도 기업에서 비용을 지불하고 구형 엑셀을 M365 버전으로 업그레이드할 가치는 충분히 차고도 넘친다고 단언할 수 있습니다. 그만큼 이 함수가 실무자들의 야근 시간을 혁신적으로 줄여주기 때문입니다.

💡 동적 배열 함수의 한계를 넘어서는 공식 가이드를 확인하세요!
VSTACK과 HSTACK 함수는 단순한 병합을 넘어, 앞서 다룬 UNIQUE, SORT, FILTER 등 동적 배열(Dynamic Array) 함수들과 결합할 때 그 파괴력이 수백 배 증가합니다. 마이크로소프트가 제공하는 더 복잡한 배열 조작 예제와 배열 함수 간의 호환성 정보, 심화 활용법이 궁금하시다면 마이크로소프트 공식 VSTACK 및 배열 기술 지원 문서를 반드시 탐독해 보시기를 강력히 추천해 드립니다.

마치며

지금까지 엑셀 데이터 관리의 가장 큰 골칫거리였던 ‘다중 시트 데이터 병합’ 작업을 단 한 줄의 수식으로 완벽하게 자동화해 주는 VSTACK 함수와 HSTACK 함수의 경이로운 활용법에 대해 아주 깊고 상세하게 알아보았습니다.

실무 현장에서 엑셀을 다루다 보면, 본질적인 데이터 분석이나 기획 업무보다는 이렇게 흩어진 데이터를 긁어모으고, 복사하고, 붙여넣고, 빈칸을 지우는 등 의미 없는 ‘데이터 클렌징(Data Cleansing) 및 취합’ 단순 반복 작업에 전체 업무 시간의 80% 이상을 빼앗기는 경우가 허다합니다. 영업팀 최 사원이 금요일 저녁마다 50개의 시트를 열어놓고 모니터를 멍하니 바라보며 한숨을 쉬었던 것처럼 말입니다.

하지만 오늘 배운 동적 배열 병합 기술, 특히 Shift 키를 활용한 3D 참조 범위 지정 기법과 FILTER 함수를 활용한 빈 셀(0) 제거 테크닉을 여러분의 업무 파일에 완벽하게 세팅해 두신다면, 매월 3시간씩 걸리던 취합 업무가 단 1초의 스필(Spill) 현상으로 종결되는 기적을 매일 아침 경험하시게 될 것입니다. 여러분은 그저 엑셀이 실시간으로 합쳐주는 완벽한 마스터 데이터를 바탕으로, 남들보다 더 빠르고 정확하게 핵심 인사이트를 도출해 내기만 하면 됩니다. 그것이 바로 단순 오퍼레이터(Operator)와 대체 불가능한 엑셀 스페셜리스트를 가르는 결정적인 차이입니다.

자, 이제 데이터를 정제하고, 병합하고, 아름다운 차트로 시각화하는 기술까지 엑셀 실무의 핵심 파이프라인을 모두 구축했습니다. 그런데 말입니다. 만약 우리가 매일 반복해서 사용하는 아주 길고 복잡한 수식 덩어리(예를 들어 앞서 만든 VSTACK+FILTER+UNIQUE+SORT 조합)를 매번 타이핑하지 않고, “나만의 아주 짧고 직관적인 새로운 함수 이름”으로 저장해 두고 단축키처럼 불러와 쓸 수는 없을까요?

다음 시간에는 엑셀 사용자들을 충격과 공포(?)에 빠뜨렸던 궁극의 종착역, 매번 바뀌는 보고서 범위와 복잡한 수식을 내 마음대로 정의해서 나만의 커스텀 함수(사용자 지정 함수)로 만들어버리는 마법, LAMBDA(람다) 함수의 세계로 여러분을 초대하겠습니다. 엑셀의 한계를 부수고 개발자의 영역에 도전하는 다음 포스팅도 절대 놓치지 마세요! 지금까지 여러분의 야근 제로를 응원하는 직장인 엑셀 꿀팁 저장소였습니다. 감사합니다!