엑셀 시트 취합, 함수로 1초 안에 해결! VSTACK 함수 완벽 가이드
영상 강의
예제파일 다운로드
오빠두엑셀의 강의 예제파일은 여러분을 위해 자유롭게 제공하고 있습니다.
- [실무기초] 엑셀 시트 합치기, VSTACK 함수 기초 완벽 가이드예제파일
VSTACK 함수로 여러 범위 취합하기
엑셀 VSTACK 함수는 여러 범위를 세로로 결합해주는 M365 버전에 새롭게 추가된 함수입니다. 이 함수는 범위 뿐만 아니라 함수로 작성된 배열, 여러 시트 범위를 참조할 수 있어 실무에서 발생하는 다양한 데이터 취합 업무에 활용할 수 있습니다.
=VSTACK(범위1, [범위2], ...)
// 여러 범위를 세로로 결합하여 하나의 큰 배열을 생성합니다.오빠두Tip : 엑셀 VSTACK 함수의 자세한 설명은 아래 VSTACK 함수 포스트를 참고하세요!👇
- VSACK 함수 기초 예제 : 예제파일을 실행한 후, [VSTACK 기초] 시트로 이동합니다. 먼저 가장 쉬운 예제로 시트에 작성된 각 지점별 데이터를 하나로 결합해보겠습니다. [VSTACK 기초] 시트에서 B7셀을 선택한 후, 다음과 같이 VSTACK 함수를 입력합니다.
=VSTACK(F7:H10,J7:L11,F15:H18,J15:L17)
VSTACK 함수로 여러 범위를 하나로 결합합니다. - 함수를 입력하면 각 지점별 범위가 하나로 결합됩니다.
선택한 범위가 하나의 배열로 결합됩니다. - 하지만 위와 같이 VSTACK 함수로 일반 범위를 참조하면, 새로운 데이터가 추가되었을 때 함수에 반영되지 않는 문제가 있습니다.
일반 범위를 지정할 경우, 새로운 데이터가 누락됩니다. - 이러한 경우, 아래 두가지 방법으로 범위를 지정하면 새로운 데이터가 실시간으로 반영되는 VSTACK 함수를 작성할 수 있습니다.
① 범위를 표로 변환
② 범위를 넉넉하게 선택한 후, FILTER 함수로 빈 셀 제거 - 동적 범위 취합: ① 표 활용 : 먼저 범위를 표로 변환하는 방법을 알아보겠습니다. 시트에서 강남점의 회원 목록이 작성된 F6:H10 범위를 선택한 후, [삽입] - [표] 를 클릭하고 '머리글 포함'을 체크하여 범위를 표로 변환합니다.
범위를 표로 변환합니다. - 표를 선택한 후, [테이블 디자인] 탭으로 이동하면 좌측에서 표의 이름을 지정할 수 있습니다. 이번 강의에서는 '강남점'으로 표 이름을 변경하겠습니다. 나머지 지점도 동일한 과정으로 범위를 표로 변환한 후, 표의 이름을 각 지점명으로 변경합니다.
표 이름을 변경합니다. 오빠두Tip : 표 기능 사용법과 구조적 참조에 대한 자세한 설명은 아래 기초 입문 강의에서 꼼꼼히 정리했으니 참고하세요!👇
- 이제 VSTACK 함수를 작성할 때, 각 표의 이름을 입력하여 범위를 편리하게 참조할 수 있습니다.
=VSTACK(강남점,신촌점,구로점,영등포점)
표 이름으로 범위를 편리하게 지정할 수 있습니다. - 표에 새로운 데이터를 추가하면 VSTACK 함수로 취합된 범위가 실시간으로 업데이트됩니다.
표에 새로운 데이터를 추가하면 VSTACK 함수가 실시간으로 업데이트 됩니다. - 동적 범위 취합: ② FILTER 함수 활용 : 두번째 방법으로 범위를 넉넉하게 선택한 후, FILTER 함수를 사용하여 범위에서 빈칸을 제거하는 방법을 알아보겠습니다. 예제파일에서 [VSTACK 활용] 시트로 이동한 후, 아래와 같이 범위를 넉넉하게 선택해서 VSTACK 함수를 작성합니다.
=VSTACK(F7:H15,J7:L15)
비어있는 공간을 포함하여 범위를 넉넉하게 선택합니다. - 함수를 입력하면 아래 그림과 같이 빈칸을 포함하여 범위가 결합됩니다. 이제 FILTER 함수를 사용해서, 범위의 첫번째 열의 값이 빈칸이 아닌 경우를 필터링 하도록 다음과 같이 공식을 작성합니다. 공식을 작성하기 위해, 이번 강의에서는 LET 함수를 함께 사용했습니다.
=LET(취합,VSTACK(F7:H15,J7:L15),FILTER(취합,CHOOSECOLS(취합,1)<>""))오빠두Tip : LET 함수의 자세한 사용법은 아래 영상 강의에서 꼼꼼히 정리했으니 참고해주세요!👇
- 함수를 입력하면 넉넉하게 선택한 범위에서 빈 셀은 제외하고 값이 있는 항목만 깔끔하게 결합됩니다.
빈 셀은 제외하고, 값이 있는 항목만 병합됩니다.
VSTACK 함수 활용 : 여러 시트 취합하기
VSTACK 함수를 사용하면 여러 시트에 나뉘어진 데이터를 한 번에 취합하는 과정을 편리하게 자동화 할 수 있습니다.
- 예제파일에서 [VSTACK 고급] 시트로 이동합니다. 이번에는 '구로지점'부터 '강남지점'까지 작성된 데이터를 하나로 결합해보겠습니다.
여러 시트에 나뉘어진 데이터를 하나로 결합합니다. - [VSTACK 고급] 시트에서 A2셀을 선택한 후, VSTACK 함수를 입력합니다. 병합할 첫번째 시트를 선택한 후, Shift 키를 누른 상태로 마지막 시트를 선택하면 여러 개의 시트가 동시에 선택됩니다. 그 상태에서 병합할 머리글을 제외한 데이터 범위를 넉넉하게 선택합니다. 이후 수식을 입력하면 여러 시트의 범위가 하나로 결합됩니다.
첫번째 시트를 선택한 후, Shift 키를 누른 상태로 마지막 시트를 클릭하면 여러 시트가 동시에 선택됩니다. - 이전에 알아본 LET 함수와 FILTER 함수를 활용하여, 다음과 같이 시트를 합치는 VSTACK 함수 공식을 완성합니다.
=LET(취합,VSTACK(구로지점:강남지점!A2:E13),FILTER(취합,CHOOSECOLS(취합,1)<>""))
LET + FILTER 함수로 여러 시트 범위를 하나로 결합하는 공식을 완성합니다
SHEETMERGE 함수 만들기 (LAMBDA)
마지막 단게로, LAMBDA 함수를 활용하여 여러 시트를 한 번에 병합하는 SHEETMERGE 함수를 만들어 보겠습니다. LAMBDA 함수의 자세한 사용법은 이전 영상 강의에서 꼼꼼히 정리했으니 참고해주세요.😊
- 이전 단계에서 완성한 LET 함수를 LAMBDA 함수로 묶어줍니다. 이후 SHEETMERGE 함수에서 사용할 인수로 "범위"를 추가합니다.
기존 공식을 LAMBDA 함수로 묶어서 작성합니다. - 기존 공식에서 "구로지점:강남지점!A2:E13" 범위를 지운 후, "범위"를 인수로 변경하면 LAMBDA 함수 공식이 완성됩니다.
=LAMBDA(범위,LET(취합,VSTACK(범위),FILTER(취합,CHOOSECOLS(취합,1)<>"")))
- 함수를 바로 입력하면 다음과 같이 #CALC! 오류가 반환되며, LAMBDA 함수는 작성한 함수 뒤로 괄호 안에 인수를 작성하여 함수 동작을 테스트할 수 있습니다. 따라서 다음과 같이 LAMBDA 함수 뒤로 테스트 인수를 추가해보면, 함수가 잘 동작하는 것을 확인할 수 있습니다.
=LAMBDA(범위,LET(취합,VSTACK(범위),FILTER(취합,CHOOSECOLS(취합,1)<>"")))(구로지점:강남지점!A2:E11)
- 이제 완성된 공식을 SHEETMERGE 함수로 등록하겠습니다. [수식] 탭 - [이름 관리자] - [새로 만들기] 를 차례대로 클릭한 후, 방금 작성한 LAMBDA 함수를 '참조 대상'에 붙여넣기하고 함수 이름과 설명을 다음과 같이 적절히 작성합니다.
이름 : SHEETMERGE
설명 : 여러 시트 데이터를 하나로 결합합니다.
참조 대상 : =LAMBDA(범위,LET(취합,VSTACK(범위),FILTER(취합,CHOOSECOLS(취합,1)<>"")))
완서오딘 LAMBDA 함수를 SHEETMERGE 함수로 등록합니다. 오빠두Tip : 괄호 안에 작성했던 테스트 인수는 지우고, LAMBDA 함수만 붙여넣는 것을 주의하세요!😊 - 이제 시트에서 =SHEET 를 입력하면 표시되는 'SHEETMERGE 함수'를 사용해서 여러 시트를 편리하게 결합할 수 있습니다.
SHEETMERGE 함수가 등록되었습니다. - 예제파일에서 '마스터함수' 시트에는 TAKE 함수와 IF 함수를 활용해서 여러 시트의 머리글을 포함하여 범위를 한 번에 병합하는 공식을 남겨드렸습니다. '마스터함수' 시트에 작성된 고급 공식을 만드는 방법은 멤버십 라이브 전체 영상을 참고해주세요.
- VSACK 함수 기초 예제 : 예제파일을 실행한 후, [VSTACK 기초] 시트로 이동합니다. 먼저 가장 쉬운 예제로 시트에 작성된 각 지점별 데이터를 하나로 결합해보겠습니다. [VSTACK 기초] 시트에서 B7셀을 선택한 후, 다음과 같이 VSTACK 함수를 입력합니다.
