엑셀365, 각 시트를 취합한 통합시트작성

출처

우노님이 최근에 올리신 포스팅을 보고,

크... 그 기가 막힌 활용법에 감탄하고 포스팅.....

같은 내용을 재포스팅하는것에 대해 따로 허락은 안받았지만, 출처링크를 남깁니다 (_ _)

일단 원본 주소..

https://www.youtube.com/watch?v=K6l0lMEV6J4 

적지 않으신 춘추이심에도 꾸준히 새로운 주제로 포스팅하시는 열정을 보면...

이 말이 떠오른다.

"젊음은 젊은이에게 주기에는 너무 아깝다." - 조지 버나드 쇼

---

본론

예시데이터

엑셀 데이터들을 다루다보면, 주제나 날짜별로 시트별로 정리된 데이터들이 있고...

수식을 적용하거나, 분석하기 용이하게 하기 위함으로 이 데이터들을 통합한 하나의 데이터로 보고 싶을 때가 있다.

(실무에서 진짜 많이 쓰임...)

 

같은 형식의 데이터들이 "월" 기준으로 시트구분되어 있다.

수식적용 애로사항

먼저 소개했던 VSTACK 함수로 배열을 세로로 쌓는것은 가능하지만,

1. 데이터의 갯수가 상이할 때 적용하기에 까다로운 부분 (동적 범위 참조)

2. 다수의 시트를 수식에 넣기 조잡한 부분

이 있었고....

 

솔루션 수식

이 모든 것을 해결한 수식.

 

=LET(t,VSTACK({"품목","개수","가격"},'1월:3월'!$A$2:$C$100000),FILTER(t,CHOOSECOLS(t,1)<>0))

단 한줄의 수식으로 통합시트 커버가 가능하다. (ㅅ..스고이)

원하던게 이거였어 ㅠㅠ

솔루션의 핵심은,

1. 다수의 시트 역시 '범위연산자' 라고 칭하신(?) " : " 기호가 시트내 범위 뿐만아니라 워크시트에서도 적용된다는 점

    (위 수식에서 1월과 3월 사이의 ":"를 말하는 중이다)

    이렇게 되면, 1월과 3월 사이의 모든 시트를 참조하게 된다.

2. CHOOSECOLS 신규함수로 범위 내 칼럼을 쉽게 참조했다는 점(인덱스 함수로도 활용가능하긴 하지만..)

3. 배열상수로 표의 타이틀을 지정해준 방식

4. LET 함수로 반복되는 수식을 변수화 시켜준 것

 

수식 설명

1. VSTACK함수로 제목행과 각 시트의 범위를 합쳐준다.

    여기서 각시트의 범위의 첫번째는 제목행으로 중복되면 안되기에, 2행부터 참조했으며, 적당히 큰 범위(예제에서는 10만으로 설정함)를 참조

2. 여기서 데이터가 없는 것까지 모두 합쳐지는 것을 방지하기 위해 필터함수로 값이 없는 데이터를 제외해준다.

    필터로 첫번째 열(꼭 첫번째가 아니어도 상관없긴하지만,) 의 값이 0인것은 제외하고 가져오도록 한다.

    첫번째열을 지정하기 위해 CHOOSECOLS 함수로 배열의 첫번째가 0이 아닌것만 가져오도록 해준다.

3. 필터함수를 적용하기위해서는 1번의 수식이 계속 반복되므로, LET 함수로 그 범위를 t로 지정하여 수식을 고쳐준다.

 

마무리

각각의 기능들을 몰랐던 것은 아니지만, (시트를 범위연산자로 표현하는 방식은 빼고 ㄷㄷ)

파워쿼리로 해결했었던 예전 방식보다 훨씬 간결하고 빠르게 적용시킬 수 있다.

당연히, 데이터가 추가되거나 심지어 시트가 추가되는 상황에서도 실시간으로 통합된 데이터를 보여준다 (크)

기본함수로 점점 VBA를 대체할 수 있게 되는 것 같다. 엑셀 재밌네...

우노 선생님 감사합니다!