회사에서 엑셀로 데이터를 다루다 보면, 여러 시트에 흩어진 데이터를 하나로 합쳐야 하는 상황이 종종 발생합니다. 이전에는 엑셀 파워쿼리나 VBA 매크로를 써야만 자동화가 가능했지만, 엑셀 2024 이후 버전과 M365에 새롭게 추가된 HSTACK과 VSTACK 함수를 사용해서 손쉽게 시트 합치기를 자동화 할 수 있게 되었습니다.
엑셀의 함수 하나로 시트 합치기를 간단하게 자동화해보세요!
이 글에서는 엑셀 초보자도 간단하게 시트 합치기를 자동화할 수 있는 HSTACK, VSTACK 함수의 사용법을 정리합니다.
- 가로 방향 합치기 : 먼저 여러 시트로 나뉘어진 데이터를 가로 방향으로 합치는 방법을 알아보겠습니다. '시트 합치기 가로방향' 예제파일을 실행하면 제품의 주문내역이 각 월별 시트에 나뉘어 있습니다.
판매 내역이 각 월별로 시트에 나누어 작성되어 있습니다.
- [취합] 시트에서 데이터를 취합할 C1셀을 선택하고, HSACK 함수를 입력합니다.
취합 시트에서 데이터를 취합할 시작 셀에 HSTACK 함수를 작성합니다.
오빠두Tip : HSTACK 과 VSTACK 함수는 엑셀 2024 이후 및 M365 버전에서만 제공됩니다.
- 이후 취합할 시트를 선택합니다. 첫번째 시트인 '1월'을 선태하고 키보드에서 Shift 키를 누른 상태로 마지막 시트인 '12월' 시트를 클릭하면, 아래 그림과 같이 연속된 모든 시트가 선택되면서 '1월:12월!' 로 수식이 작성됩니다. 이러한 방식을 3D 참조(여러 시트를 동시에 입체적으로 참조하는 방식)이라고 합니다.
취합할 시트를 모두 선택합니다.
- 이제 취합할 범위인 C1:C10 범위를 선택하고 함수를 실행하면 아래 그림과 같이 여러 시트의 데이터가 가로 방향으로 취합됩니다.
선택한 시트의 데이터가 가로 방향으로 취합됩니다.
- 세로 방향 합치기 : 데이터를 세로 방향으로 취합하는 것도 동일하게 가능합니다. '시트 합치기 세로방향' 예제파일을 실행하고 [취합] 시트에서 데이터 취합을 시작할 A2셀에 아래 그림과 같이 VSTACK 함수를 입력합니다.
데이터를 취합할 시작 셀에 VSTACK 함수를 입력합니다.
- 이후 데이터를 취합할 1월부터 4월까지 시트를 선택하고 머리글을 제외한 데이터 범위를 넉넉하게 드래그해서 선택합니다.
취합할 시트를 모두 선택하고 머리글을 제외한 범위를 드래그해서 넉넉하게 선택합니다.
- 이어서 함수를 실행하면 아래 그림과 같이 모든 시트의 데이터가 세로 방향으로 취합됩니다. 다만 VSTACK 함수만 사용하면 범위의 비어있는 값도 함께 취합되는 문제가 있습니다. 이때 FILTER 함수를 함께 사용하면 빈 값을 제외하고 취합할 수 있습니다.
VSTACK 함수로 범위를 넉넉하게 입력하면 빈 값도 같이 취합되는 문제가 있습니다.
- 시트 취합 자동화 공식 : VSTACK 함수를 FILTER 함수로 묶은 후, 다음과 같이 공식을 작성합니다.
=FILTER(VSTACK('1월:4월'!A2:C7),VSTACK('1월:4월'!A2:A7)<>"")
/ / 1월~4월 시트의 A2:C7을 취합하되, A2:A7 의 값이 비어있지 않은 경우만 취합합니다.
FILTER 함수를 사용해서 첫번째 열의 값이 비어있지 않은 경우만 취합하도록 공식을 작성합니다.
- 작성한 공식을 실행하면 아래 그림과 같이 비어있는 값을 제외한 데이터가 취합됩니다. 이제 1월~4월 시트에서 새로운 데이터를 추가하면 취합 시트에 데이터가 실시간으로 업데이트됩니다.
시트에 새로운 데이털르 추가하면 취합 시트에 데이터가 실시간으로 업데이트 됩니다.
