VSTACK으로 여러 엑셀 시트를 자동 취합하고 정렬하는 방법은?

TL;DR

여러 시트의 동일한 구조 데이터를 VSTACK으로 쌓고, LET과 FILTER 및 CHOOSECOLS를 조합하면 빈 행을 제외한 통합 목록이 자동으로 갱신됩니다. 원본 범위를 표로 변환하거나 충분히 넓게 지정할 수 있으며, 연속된 시트 범위를 참조하면 그 사이에 추가한 시트도 취합됩니다. 완성 공식은 LAMBDA와 이름 관리자를 이용해 SHEETMERGE 같은 사용자 함수로 등록할 수 있습니다.

Transcript

여러분 안녕하세요 오빠도 엑셀 전원입니다 자 오늘은 직장인이라면 한 번쯤 고통받게 되는 그 작업인데요 바로 시트 취압 업무를 함수 하나만 사용해서 자동화하는 방법을 소개해드리려 합니다 그래서 오늘 강계를 모두 수강하시면 이런 식으로 데이터가 여러 개의 시트로 나뉘어서 입력되어 있을 때 함수를 사용할 건데요 시트 월제 함수를 입력하신 다음에 시트를 선택하는데요 첫 번째 시트를 선택하신 다음에 시프트 키를 누른 상태로 마지막 시트를 클릭하시고 그 상태에서 범위를 넉넉하게 선택합니다 자 그리고 엔터키로 함수를 실행하시면 이런 식으로 데이터가 한 번에 병합되는 함수를 만들 수 있습니다 자 그리고 데이터를 한번 추가해 볼 건데요... Read More

Key Insights

• VSTACK은 여러 범위를 지정된 순서대로 세로로 쌓아 하나의 동적 배열로 반환하며, 강의에서는 M365 최신 버전에서 제공된다고 설명한다.
• 일반 셀 범위를 직접 참조한 VSTACK 공식은 원본 아래에 새 데이터가 추가돼도 지정 범위 밖의 행을 자동으로 포함하지 않는다.
• 원본 데이터를 표로 변환하고 이름을 지정하면 표 범위가 새 행까지 확장되므로 VSTACK의 통합 결과도 실시간으로 갱신된다.
• 새 시트나 파일이 계속 쌓이는 업무에서는 매번 범위를 표로 바꿔야 하므로, 넓은 범위를 미리 참조하는 방식이 더 현실적일 수 있다.
• LET으로 VSTACK 결과를 변수에 저장한 뒤 FILTER와 CHOOSECOLS를 적용하면 첫 번째 열이 빈 행을 통합 목록에서 제외할 수 있다.
• 연속된 첫 시트와 마지막 시트를 함께 선택해 동일 범위를 지정하면 그 사이 여러 시트의 데이터를 하나의 공식으로 취합할 수 있다.
• 여러 시트의 전체 열을 참조하면 엑셀의 약 104만 행 처리 한계를 넘을 수 있으므로 필요한 범위를 적절한 크기로 제한해야 한다.
• LAMBDA로 통합 공식을 감싸고 이름 관리자에 등록하면 범위만 인수로 받는 SHEETMERGE 사용자 함수를 반복해서 사용할 수 있다.
• 🎬 이 채널의 다른 영상:
  • AI 에이전트 시대에 일자리 변화에 대비하려면 무엇을 준비해야 하나?
  • ChatGPT로 엑셀 시트 검색과 거래 내역을 어떻게 자동화할까?
  • 엑셀 초보자가 VBA로 반복 업무를 자동화하는 방법은 무엇인가?
• 📺 이 채널: 오빠두엑셀 l 엑셀 강의 대표채널

Questions & Answers

Q: VSTACK으로 여러 엑셀 시트의 데이터를 어떻게 합치나요?

VSTACK 함수에 구조가 같은 각 시트의 범위를 쉼표로 구분해 입력하면 데이터가 지정된 순서대로 세로로 합쳐집니다. 연속된 여러 시트를 한꺼번에 취합하려면 첫 시트를 선택한 뒤 Shift 키를 누른 상태로 마지막 시트를 선택하고, 머리글을 제외한 동일 범위를 충분히 넉넉하게 지정합니다.

Q: 새 행이 추가될 때 VSTACK 결과를 자동으로 갱신하려면 어떻게 하나요?

원본 범위를 표로 변환하거나 앞으로 데이터가 들어올 빈 행까지 포함해 참조 범위를 넓게 지정하면 됩니다. 표 방식에서는 Ctrl+T로 각 범위를 표로 만들고 이름을 지정한 뒤 그 표 이름들을 VSTACK 인수로 사용하며, 새 행이 표에 추가되면 취합 결과에도 자동으로 반영됩니다.

Q: VSTACK으로 넓은 범위를 합칠 때 생기는 빈 행은 어떻게 제거하나요?

LET으로 VSTACK 결과를 취합 같은 변수에 저장한 다음 FILTER로 첫 번째 열이 빈칸이 아닌 행만 반환하면 됩니다. 이때 CHOOSECOLS를 이용해 취합 범위의 첫 번째 열을 검사 조건으로 지정하면, 참조 범위에 포함된 빈 행은 제외되고 실제 값이 있는 데이터만 남습니다.

Q: 엑셀 표를 사용한 동적 취합 방식에는 어떤 제한이 있나요?

표 방식은 새 행을 자동으로 포함해 사용하기 편리하지만, 새로운 시트나 파일이 계속 추가되는 환경에서는 각 데이터 범위를 매번 표로 변환하고 이름을 지정해야 합니다. 강의에서는 이런 반복 작업이 실무상 번거로울 수 있어, 빈칸까지 포함한 넓은 범위와 FILTER를 결합하는 방식을 대안으로 제시합니다.

Q: 나중에 추가한 시트도 자동 취합되게 만들 수 있나요?

처음 시트부터 마지막 시트까지 연속된 시트 범위를 VSTACK에서 참조하고, 새 시트를 그 두 시트 사이에 배치하면 추가된 시트의 동일 범위도 자동으로 취합됩니다. 강의 예시에서는 구로점부터 강남점까지 참조한 뒤 여의도점 시트를 그 사이로 이동해 데이터가 통합 결과에 반영되는 것을 확인합니다.

Q: 여러 시트를 VSTACK으로 합칠 때 전체 열을 선택해도 되나요?

여러 시트를 동시에 참조할 때 전체 열을 선택하는 것은 피해야 합니다. 강의에서는 엑셀이 처리할 수 있는 최대 행 수가 약 104만 개이므로, 여러 시트의 전체 행을 한꺼번에 쌓으면 이 한계를 초과해 오류가 발생할 수 있다고 설명하며 필요한 예상 범위까지만 지정하도록 안내합니다.

Q: 취합된 데이터를 가입일이나 담당자 기준으로 정렬하려면 어떻게 하나요?

완성된 취합 공식을 SORT 함수로 감싸고 정렬 기준 열을 지정하면 됩니다. 강의에서는 세 번째 열인 가입일을 기준으로 오름차순 정렬하는 예와, 다섯 번째 열인 담당자 또는 회원권 열을 기준으로 바꾸어 데이터를 정렬하고 분석하는 방법을 제시합니다.

Q: VSTACK 취합 공식을 사용자 함수로 등록하려면 어떻게 하나요?

LET과 FILTER로 만든 취합 공식을 LAMBDA로 감싸고 병합할 범위를 인수로 받도록 구성한 뒤, 수식 탭의 이름 관리자에서 새 이름으로 등록하면 됩니다. 강의에서는 이름을 SHEETMERGE로 지정하고 LAMBDA 공식을 참조 대상에 붙여 넣어, 이후 셀에서 범위만 선택해 여러 시트를 합치도록 설정합니다.

Summary & Key Takeaways

• VSTACK에 강남점, 신촌점, 구로점, 영등포점처럼 구조가 같은 여러 범위를 쉼표로 지정하면 데이터를 세로로 이어 붙일 수 있다. 일반 셀 범위만 참조하면 새 행이 추가될 때 참조 범위를 직접 늘려야 하므로 자동 갱신에 한계가 있다.

• 동적 취합은 원본 범위를 표로 변환하거나 빈 행을 포함해 넉넉하게 참조하는 방식으로 구현한다. 넓은 범위를 쓸 때는 VSTACK 결과를 LET 변수로 저장하고, FILTER와 CHOOSECOLS로 첫 번째 열이 비어 있지 않은 행만 남긴다.

• 첫 시트부터 마지막 시트까지 연속 시트의 동일 범위를 VSTACK에 지정하면 여러 시트를 한꺼번에 합칠 수 있다. SORT로 원하는 열을 기준으로 정렬하고, LET 기반 공식을 LAMBDA로 감싼 뒤 이름 관리자에 등록하면 재사용 가능한 SHEETMERGE 함수를 만들 수 있다.