회사에서 엑셀 데이터를 다루다보면, 월별·지점별로 시트가 여러 개로 나뉘어 있는 경우가 많습니다. 그럴 때마다 피벗 테이블을 만들려고 데이터를 하나씩 복사해 붙여넣는 작업은 번거롭습니다.
이럴 때 GROUPBY 함수와 VSTACK 함수만 사용하면 여러 시트 데이터를 실시간으로 취합하는 자동화 보고서를 만들 수 있습니다. 아래 완성 공식을 참고해 따라 해 보세요.
- 먼저 GROUPBY 함수의 기본 동작을 알아봅니다. GROUPBY는 집계 보고서를 만드는 함수로, 첫 번째 인수에 집계 기준 필드, 두 번째에 값 필드, 세 번째에 집계 방식을 지정하면 피벗 테이블과 같은 보고서가 한 번에 만들어집니다.
=GROUPBY(VSTACK(시트1:시트N!기준범위), VSTACK(시트1:시트N!값범위), SUM)
GROUPBY 함수 하나로 피벗 보고서가 완성됩니다.
- 이제 여러 시트를 합치기 위해 GROUPBY 함수의 첫 번째 인수 자리에 VSTACK 함수를 입력합니다. VSTACK은 여러 범위를 세로로 쌓아 하나의 배열로 만들어주는 함수입니다.
GROUPBY의 첫 번째 인수 자리에 VSTACK 함수를 작성합니다.
- VSTACK 함수 안에서 합칠 시트들을 한 번에 선택합니다. 데이터의 시작 시트 탭을 클릭한 다음, Shift 키를 누른 상태로 마지막 시트 탭을 클릭하면 여러 시트가 동시에 선택됩니다.
Shift 키를 누른 상태로 시트 탭을 클릭해 범위를 묶습니다.
- 시트가 묶인 상태에서 머리글 행을 제외한 기준 필드 범위를 넉넉하게 선택합니다. 데이터가 추가될 것을 고려해 빈 행까지 포함해 선택해두면, 이후 행이 늘어나도 자동으로 반영됩니다.
머리글을 제외한 기준 필드 범위를 넉넉하게 선택합니다.
- 이번에는 GROUPBY의 두 번째 인수에도 VSTACK 함수를 입력해 값 필드 범위를 지정합니다. 같은 방식으로 여러 시트를 동시에 선택한 다음, 수량·금액과 같은 값 필드 범위를 넉넉하게 선택해줍니다.
두 번째 인수에도 VSTACK 함수를 사용해 값 필드 범위를 지정합니다.
- 마지막으로 세 번째 인수에 집계 방식으로 SUM 함수를 입력하고 수식을 실행합니다. 그러면 여러 시트를 한 번에 취합한 집계 보고서가 완성됩니다.
SUM을 지정하고 수식을 실행하면 통합 보고서가 만들어집니다.
- 이 보고서의 가장 큰 장점은 각 시트에 새로운 데이터를 추가하면 결과가 실시간으로 업데이트된다는 점입니다. 따라서 매월 데이터가 늘어나도 수식을 다시 만들 필요 없이, 자동화된 피벗 보고서로 활용할 수 있습니다.
시트에 새 데이터를 추가하면 보고서가 실시간으로 정리됩니다.
