엑셀 시트 취합, 함수로 1초 만에 해결하기 : VSTACK 함수 완벽 가이드

실습 가이드

VSTACK 함수로 여러 범위 취합하기

엑셀 VSTACK 함수는 여러 범위를 세로로 결합해 주는 M365 신규 함수입니다. 일반 셀 범위는 물론 함수가 반환한 동적 배열, 여러 시트의 범위까지 함께 참조할 수 있어, 실무에서 자주 발생하는 데이터 취합 업무를 한 번에 처리할 수 있습니다.

1. VSACK 함수 기초 예제 : 예제파일을 실행한 후 [VSTACK 기초] 시트로 이동합니다. 먼저 가장 기본적인 형태로 각 지점별 데이터를 하나의 범위로 결합해 보겠습니다. [VSTACK 기초] 시트의 B7셀을 선택한 뒤 다음과 같이 VSTACK 함수를 입력합니다.
   =VSTACK(F7:H10,J7:L11,F15:H18,J15:L17)

   

2. 함수를 입력하면 각 지점의 범위가 하나로 결합되어 출력됩니다.

   

3. 다만 위와 같이 VSTACK 함수로 일반 셀 범위를 참조하면, 이후 새로운 데이터가 추가되어도 결과에 자동으로 반영되지 않는 한계가 있습니다.

   

4. 이런 경우에는 아래 두 가지 방법으로 범위를 지정해, 새로운 데이터가 실시간으로 반영되는 VSTACK 공식을 만들 수 있습니다.
   ① 범위를 표로 변환
   ② 범위를 넉넉하게 선택한 후, FILTER 함수로 빈 셀 제거
5. 동적 범위 취합: ① 표 활용 : 먼저 범위를 표로 변환하는 방법부터 살펴보겠습니다. 강남점 회원 목록이 입력된 F6:H10 범위를 선택한 뒤 [삽입] - [표] 를 클릭하고, '머리글 포함'에 체크한 상태로 범위를 표로 변환합니다.

   

6. 표를 선택한 상태에서 [테이블 디자인] 탭으로 이동하면 좌측에서 표의 이름을 지정할 수 있습니다. 이번 강의에서는 표 이름을 '강남점'으로 변경합니다. 나머지 지점도 동일한 방식으로 범위를 표로 변환한 뒤, 각 표의 이름을 지점명으로 지정합니다.

   

   오빠두Tip : 표 기능 사용법과 구조적 참조에 대한 자세한 설명은 아래 기초 입문 강의에서 정리해 두었으니 참고하세요!👇
   
7. 이제 VSTACK 함수에 각 표의 이름을 인수로 입력해 범위를 간결하게 참조할 수 있습니다.
   =VSTACK(강남점,신촌점,구로점,영등포점)

   

8. 각 표에 새로운 데이터를 추가하면 VSTACK 함수로 취합한 범위가 실시간으로 갱신됩니다.

   

9. 동적 범위 취합: ② FILTER 함수 활용 : 두 번째 방법은 범위를 넉넉하게 선택한 뒤 FILTER 함수로 빈칸을 제거하는 방식입니다. 예제파일에서 [VSTACK 활용] 시트로 이동한 후, 아래와 같이 범위를 충분한 크기로 선택해 VSTACK 함수를 작성합니다.
   =VSTACK(F7:H15,J7:L15)

   

10. 함수를 입력하면 빈칸을 포함한 채로 범위가 결합됩니다. 이제 FILTER 함수로 첫 번째 열의 값이 빈칸이 아닌 행만 추려내도록 다음과 같이 공식을 작성합니다. 이때 중간 결과를 변수로 저장하기 위해 LET 함수를 함께 사용했습니다.
    =LET(취합,VSTACK(F7:H15,J7:L15),FILTER(취합,CHOOSECOLS(취합,1)<>""))
    오빠두Tip : LET 함수의 자세한 사용법은 아래 영상 강의에서 정리해 두었으니 참고하세요!
    
11. 공식을 입력하면 넉넉하게 선택한 범위에서 빈 셀은 제외되고 값이 입력된 행만 깔끔하게 결합됩니다.

    

VSTACK 함수 활용 : 여러 시트 취합하기

VSTACK 함수를 사용하면 여러 시트에 분산된 데이터를 한 번에 취합하는 작업도 간단한 공식 하나로 자동화할 수 있습니다.

1. 예제파일에서 [VSTACK 고급] 시트로 이동합니다. 이번에는 '구로지점'부터 '강남지점'까지 작성된 데이터를 하나로 결합해 보겠습니다.

   

2. [VSTACK 고급] 시트의 A2셀을 선택한 뒤 VSTACK 함수를 입력합니다. 결합할 첫 번째 시트를 선택한 상태에서 Shift 키를 누른 채 마지막 시트를 클릭하면 여러 시트가 동시에 선택됩니다. 그 상태에서 결합할 머리글을 제외한 데이터 범위를 넉넉하게 선택한 뒤 수식을 입력하면, 여러 시트의 범위가 하나로 결합됩니다.

   

3. 앞서 살펴본 LET 함수와 FILTER 함수를 함께 사용해, 다음과 같이 여러 시트를 결합하는 VSTACK 공식을 완성합니다.
   =LET(취합,VSTACK(구로지점:강남지점!A2:E13),FILTER(취합,CHOOSECOLS(취합,1)<>""))

   

SHEETMERGE 함수 만들기 (LAMBDA)

마지막 단계로, LAMBDA 함수를 활용해 여러 시트를 한 번에 병합하는 SHEETMERGE 함수를 직접 만들어 보겠습니다. LAMBDA 함수의 자세한 사용법은 이전 영상 강의에서 정리해 두었으니 참고하세요.

1. 이전 단계에서 작성한 LET 함수 전체를 LAMBDA 함수로 묶어 줍니다. 그리고 SHEETMERGE 함수에서 사용할 인수로 "범위"를 추가합니다.

   

2. 기존 공식에서 "구로지점:강남지점!A2:E13" 범위를 지운 뒤 그 자리에 인수 "범위"를 입력하면 LAMBDA 함수 공식이 완성됩니다.
   =LAMBDA(범위,LET(취합,VSTACK(범위),FILTER(취합,CHOOSECOLS(취합,1)<>"")))
3. 완성한 LAMBDA 함수를 시트에 그대로 입력하면 #CALC! 오류가 반환됩니다. LAMBDA 함수는 작성한 함수 뒤로 괄호를 추가하고 그 안에 테스트 인수를 입력하는 방식으로 동작을 검증할 수 있습니다. 다음과 같이 LAMBDA 함수 뒤에 테스트 인수를 추가해 보면, 함수가 정상적으로 동작하는 것을 확인할 수 있습니다.
   =LAMBDA(범위,LET(취합,VSTACK(범위),FILTER(취합,CHOOSECOLS(취합,1)<>"")))(구로지점:강남지점!A2:E11)
4. 이제 완성된 공식을 SHEETMERGE 함수로 등록해 보겠습니다. [수식] 탭 - [이름 관리자] - [새로 만들기] 를 차례대로 클릭한 뒤, 작성한 LAMBDA 함수를 '참조 대상'에 붙여넣고 함수 이름과 설명을 다음과 같이 입력합니다.
   이름 : SHEETMERGE
   설명 : 여러 시트 데이터를 하나로 결합합니다.
   참조 대상 : =LAMBDA(범위,LET(취합,VSTACK(범위),FILTER(취합,CHOOSECOLS(취합,1)<>"")))

   

   오빠두Tip : 참조 대상에는 LAMBDA 함수만 붙여넣고, 동작 검증에 사용했던 괄호 안의 테스트 인수는 반드시 제거하세요.
5. 이제 시트에서 =SHEET 를 입력하면 자동완성 목록에 표시되는 SHEETMERGE 함수로 여러 시트의 데이터를 간단하게 결합할 수 있습니다.

   

6. 예제파일의 '마스터함수' 시트에는 TAKE 함수와 IF 함수를 활용해 여러 시트의 머리글까지 함께 병합하는 고급 공식을 남겨 두었습니다. '마스터함수' 시트의 고급 공식을 만드는 과정은 멤버십 라이브 전체 영상을 참고해 주세요.