동적 범위로 피벗테이블을 만들어 보겠습니다.
피벗 테이블은 기존 목록이나 표에 있는 데이터를 요약하여 분석해 주는 대화형 테이블입니다.
일반적으로 피벗 테이블로 자료를 만들 때 특정범위를 미리
선택하여 피벗테이블을 만듭니다. 그런데 해당 자료가 계속 추가 또는 삭제되어 그 범위가 계속 바뀌게
된다면 일반적으로 해당 피벗테이블을 새로 만들게 됩니다. 경우에 따라서 해당범위를 처음부터 넉넉히
선택하여 데이터가 추가 또는 삭제 되더라도 해당 피벗 테이블의 데이터 요약에 영향을 미치게 할 수는
있습니다.
앞서 설명한 특정한 범위 또는 여유 있게 범위를 선택하여 피벗
테이블을 작성하는 것이 아니라 동적 범위를 이용하여 피벗테이블을 만들어 보도록 하겠습니다.
'동적 범위'라는 것을 이용한다고 하면 생각나는 것이 있어야
되는데…,
혹 무엇을 이야기 하고자 하는지 벌써 눈치를 챈 분도 있겠네요. ^^*
함수로는 OFFSET 등 이고 또한 이름정의를
사용합니다.
우선 아래와 같은 예제데이터를 가지고 피벗테이블을 만들고 설명
하겠습니다.
<1> 위의 데이터를 이용하여 피벗 테이블을
만들기 전에 동적 범위를 설정하기 위해 수식을 작성하여 이름 정의를 해야 합니다.
위에서도 잠시 언급했듯이 OFFSET 함수를 사용하여 수식을
=OFFSET($A$2,0,0,COUNTA($A$2:$A$65536),7)
으로 작성하고 해당되는 이름을 db_Table 이라고 붙이겠습니다.
* OFFSET
OFFSET(reference, rows, cols, height, width)
지정한 행 또는 열 수만큼 떨어진 곳에
있는 특정 높이와 너비의 참조 영역을 표시합니다. 참조 영역은 한 셀이나 여러 셀이 될
수 있습니다. |
이름정의를 하기 위하여 삽입메뉴의 이름의 정의(단축키:
Ctrl+F3) 메뉴를 선택합니다.
이름 정의 대화상자가 표시되면 참조 대상 입력란에
=OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A$2:$A$65536),7)라고
입력합니다.
통합문서에 있는 이름 입력란에 db_Table라고 입력하고 추가 단추를
클릭합니다.
* Sheet1에 원본 데이터 목록이 들어 있으므로 Sheet1이 범위 이름 앞에 자동으로 붙게
됩니다.
<2> 이제 동적 범위를 이용한 피벗 테이블을
만들기 위한 작업은 끝났으니 피벗 테이블만 만드시면 됩니다.
피벗 테이블을 만들기 위해 데이터메뉴의 피벗 테이블/피벗
차트 보고서 메뉴를 선택합니다.
마법사 1단계에서 분석할 데이터 위치를 'Microsoft Office Excel 목록이나
데이터베이스'를 선택합니다.
마법사 2단계에서는 범위 입력란에 위에서 정의한 'db_Table'을 입력하면 됩니다.
마지막 3단계에서는 만들고자 하는 피벗 테이블의 레이아웃을
설정한 후 확인 단추를 클릭합니다.
[참고] 피벗 테이블 만드는 방법
<3> 다음과 같이 새로운 시트(Sheet4)에
피벗 테이블이 완성됩니다.
<4> 모든 작업이 끝났으니 위의 예제
데이터목록의 마지막에 새로운 데이터를 입력 한 후 피벗 테이블 도구모음의 데이터 새로 고침( )을
누르시면 피벗테이블의 데이터가 추가된 것을 확인할 수 있습니다.
- 위의 예제 맨 아래에 아래 데이터를 추가 합니다.
- 피벗 테이블 도구 모음에 있는 데이터 새로 고침()을
클릭합니다.
- 아래 그림처럼 피벗 테이블에 새로 추가한 데이터가 반영되어 표시됩니다.
위에서 설명했듯이 동적 범위로 피벗 테이블을 만드는 데는
수식을 이름 정의하여 사용하면 됩니다.
|
OfficeTutor 365