[엑셀] 테이블(표) 영역 선택해서, 이름정의로 사용하기

데이터 시트의 경우, 영역(range)으로 사용하는 것보다 테이블(표)로 사용하는 경우 장점이 있다.

첫번째는 최하단에 데이터를 추가했을때 연결되는 수식에서 동적으로 추가된다는 것이고,

두번째는 데이터가 좀 늘어나면, 엑셀이 힘겨워하는 경우를 심심치 않게 볼 수 있는데, 이를 좀 더 완화할 수 있을 것이라는 기대감이 있다. 즉, 확실치가 않다.

 

 

각설하고, 주어진 상황은 아래와 같다.

데이터 시트가 있고, 이를 배열 함수로 요약하는 시트가 여러장 있었다. 나 역시도 귀찮아서, 테이블 대신 주로 영역으로 사용하고 있었는데, 어느순간, 계산 중이라는 프로그래스바가 생긴다.

 

테이블(표) 내에서 원하는 영역을 선택하는 것을 Structed references 이라고 하나보다. 범용적으로 쓰는 말인지 모르겠는데, 이를 몰라서, 일반적으로 검색을 하니, 전부 영역 선택에 관련한 내용이 나온다. 결국 웹에서 검색을 포기하고, ChatGPT와 영어로 씨름하다가 원하는 답을 찾았다. 가끔 테이블에 참조를 할때 보았던 수식(?)이 어렴풋이 기억난다.

 

[#All] : Refers to the entire table, including header and data rows.
[#Headers] : Refers to the header row the table.
[#Data] : Refers to the data rows of the table, excluding the header row.
[@] : Refers to the current row in the table (used indide table formulas).
[Column Name] : Refers to a specific column by its header name.
                            _replace "Column Name" with the actual name of the column.
[[#This Row], [Column Name]] : Refers to a specific cell in the current row for the
                                                  specified column.
[[#Totals], [Column Name]] : Refers to the total row of a table.
                                             _if a total row is enabled.

 

 

자 이제 해보는 거다.

워크북에서 처음 테이블을 생성하면 기본적으로 주어지는 이름은 Table1 이다. 변경은 가능하지만, 귀찮으니, 두자.

 

Table1[#All]전체 선택 , 그냥 Table1 이라고만 해도 된다.

 

 

 

Table1[#Headers]테이블 헤더 전체 선택

Table1[[#Headers],[1월]:[2월]]테이블 헤더만 선택, 필드명으로 영역을 한정 지어 줄 수 있다.

 

 

 

Table1[[#Data],[2월]:[4월]]테이블 데이터만 선택, 필드명으로 영역을 한정 지어 줄 수 있다.

 

 

 

예시는 여기까지만 하고, Structed references로 손쉽게 영역을 선택할 수 있음을 확인했다. 이제, 이를 이름정의로 등록해보자. 아래와 같이 입력하면, 이름정의가 잘 됨을 알 수 있다.

 

 

 

간단하게 배열수식으로 확인을 해보면, 아래와 같다. 과일 조건엘에 대하여, 동일하게 테이블 참조를 사용하였는데, 수식이 좀 길어지니, 마찬가지로, 이름정의를 해두는 것이 나을 것 같다.

 

 

 

자 이제 마지막이다.

현재 2월~4월로 한정되어 있는 값에 대해서, 내가 원하는 영역을 수시로 바꿔서 확인할 수 있을까?

정의된 이름 _2월_4월에 대하여, 현재는 아래와 같은 수식이 설정되어있는데, 아래 값에서, 2월과 4월을 참조값으로 변경해주면 될 것이다.

=Table1[[#Data],[2월]:[4월]]

 

시작월과 종료월에 대한 조건 값을 입력할 수 있는 셀을 만들고, indirect 함수를 사용하여, 아래와 같이 영역을 다시설정해 줌녀 어떨까?

=indirect("Table1[[#Data],[" & Sheet2!$H$11 & "]:[" & Sheet2!$H$12 & "]]")

 

 

마찬가지로 복숭아의 2~3월 값이 합산되어 잘 들어가는 것을 확인할 수 있다.

 

 

 

두서없이 적었지만, 오늘은 여기까지. 연산속도가 빨라지는지는, 실무에 적용해보고 추가로 적기로 한다.

 

---

 

위의 글을 적은 이후, 몇 시간이 지나, 몇개의 관리 파일에 적용을 해 보았지만, 속도는 개선되지 않았다. 아니 오히려 느려진듯 한건 내 느낌인가? 서두에 적은 내용 중 두번째는 빼는걸로 하자.

 

 

 

 

 

끝.