[엑셀] 배열수식 테이블 슬라이스 - 동적인 합계 영역 설정

좀.. 딥한 내용이기는하지만, 단순하게 적어본다.

아래와 같은 테이블이 있을때, 원하는 과일에 대한 조건을 주고, (여기서는 이름으로만 처리), 월별 생산량을 구하는 수식을 짠다고 하면, 최초에 세팅한 합계영역이 계산되어 나올 것이다.

 

 

 

아래 그림처럼, "수량"이라는 이름으로 정의를 해놓았다면, 보라색 영역이 변경되지 않는다는 말이다. "수량"이라는 영역을 슬라이스처럼 동적으로 만들 수 있을까? 일단 생각나는데로 해본다.

 

 

 

월에 따라, 열문자가 변경되어야 하므로 이를 참조할 테이블을 하나 만든다.

 

 

 

1월부터 12월까지, 해당하는 열문자를 기재해줬다. 그냥 놔두면 흉하니까, 나는 "열번호"라는 이름으로 이름정의를 해둘 생각이다. 테이블을 이름정의로 바꾸는 매크로는 이전 글에 적었던 적이 있는 것 같다.

 

 

[엑셀VBA] 참조표(테이블)을 이름정의에 사용하기 위한 배열로 변환

 

 

이제 본격적으로 슬라이서를 만들어보자.

집계 시작월은 4월, 종료월은 5월로 설정을 한다면, 위에 만들어놓은 열번호 이름정의를 활용하여, 아래와 같이 열번호를 열문자로 변환하는 vlookup함수를 사용한다.

 

 

 

이제 다 왔다. 아래 그림에서 원하는 영역은 $E$2:$F$5 이므로, E열과 F열을 동적으로 만들기 위해 함수를 끼워 넣어서, 수식을 텍스트로 작성해  주고, INDIRECT 함수로 감싸준다.

 

=INDIRECT("$"&VLOOKUP($B$24,열번호,2,FALSE)&"$2:$"&VLOOKUP($B$25,열번호,2,FALSE)&"$5")

 

 

 

새로 참조할 수량2 이름정의를 생성해 주고, 해당 수식을 넣어준다.

 

 

 

자, 이제 시작월과 종료월의 수치를 변경하면, 합산 수치도 변경이 된다.

INDIRECT 함수 대신 EVALUATE 함수를 사용해도 되지만, 반드시 매크로문서로 저장해야하므로, 무시한다.

 

끝.