xls - indirect 함수로 동적 요약 테이블 만들기

테이블

테이블은 영역에 비해서 사용하기가 수월한 편이지만, 자주 두고 사용하지 않는 데이터의 경우, 구지 테이블로 만들지 않는 경우가 많은데, 사용성과 오류방지 차원에서 가능하다면 테이블을 사용해야 한다.

아래와 같이 mtcars 데이터를 ctrl + t 단축키로 테이블로 만들어 준다.

동적 조건

구현하고자 하는 바는 아래와 같다.

[model] 컬럼에 “Mazda” 키워드가 포함되는 경우,
[model] 열부터 [am] 열까지 숫자 값에 대한 합계와 개수를 추출한다.

선택할 열에 대해서는 값을 입력하면 동적으로 적용될 수 있도록 상단에 컬럼명을 입력할 수 있는 공간을 두었다.

indirect 함수 사용

테이블에서 테이블 컬럼을 호출하는 방식은 Table1[컬럼1] 이고,
복수의 컬럼을 호출하는 것은 Table1[[컬럼1]:[컬럼x]] 가 된다.

따라서, 컬럼명을 변수로 호출하기 위해 아래와 같이 수식을 넣어주면 된다. if 조건은 Mazda 가 포함된 행을 찾는 조건, 합계 영역은 indirect로 지정된 영역이 된다.

=SUM(
IF(
(ISNUMBER(FIND(“Mazda”,Table1[model]))),
INDIRECT(“Table1[[” & O1 & “]:[” & O2 & “]]”)
)
)

이름 정의도 좋은 방법이지만, 테이블 헤더를 사용하면, 구지 그럴 필요가 없고, 특히 헤더에 월(1월, 2월 등)이 들어있는 피벗 테이블에서 연속된 월 범위를 지정하기 편하다.

필터나 쿼리 등의 신규 함수를 쓰면, 더 나아질런지 모르지만, 아직 365를 쓰지 않아서 모르겠다.

끝.