출처 : https://m.blog.naver.com/onwings/221279372791
아래는 위 링크를 복사한 것 : 아주 편리한 기능 ------------------------
피벗 테이블을 작성할 때 사용한 셀 범위가 변경되면 [원본 데이터 범위]를 직접 변경해주어야 합니다.
같은 데이터 목록에서 행 개수가 추가되거나 삭제 되더라도 자동으로 피벗 테이블 범위를 인식하도록 하면 아주 편리하지 않을까요?
방법은 피벗 테이블에 사용할 원본 데이터 범위를 이름으로 정의하여 사용합니다.
이 이름에는 자동 범위를 인식할 수 있는 함수가 사용되어야 하구요.
방법을 설명하겠습니다~
1. 아래 시트에는 왼쪽에 [데이터 목록]이 있고, 오른쪽에는 이 데이터로 만든 [피벗 테이블]이 있습니다.
2. 피벗 테이블에 사용할 이름을 정의해보겠습니다.
[수식]-[이름관리자]를 클릭합니다.
3. [새로 만들기]를 클릭합니다.
4. 이름에 [피벗범위]를 입력합니다.
이 이름은 공백이나 특수문자없이 자유롭게 입력할 수 있습니다.
그 다음 참조 대상에 [=OFFSET(피벗범위!$A$1,0,0,COUNTA(피벗범위!$A:$A),4)]를 입력합니다.
'피벗범위!$A$1'와 '피벗범위!$A:$A'는 셀이나 열을 클릭하면 자동으로 절대참조로 입력됩니다.
=OFFSET(피벗범위!$A$1,0,0,COUNTA(피벗범위!$A:$A),4)
- OFFSET 함수는 출발셀에서 부터 몇 칸 행과 열을 이동한 셀을 지칭하거나 셀 범위를 선택할 수 있는 함수입니다.
- 함수 형식은 : OFFSET(출발셀, 이동행수, 이동열수, 범위지정할행개수, 범위지정할열개수)
- 피벗 범위시트의 [A1]을 출발셀로 하고, 이동행수와 이동열수가 모두 '0'이므로 셀 위치는 이동하지 않아 [A1]셀에서 부터 범위가 시작됩니다.
- 그 다음 'COUNTA(피벗범위!$A:$A)'에 의해서 범위지정할 행개수가 선택되는데, A열에 데이터가 있는 개수 만큼 행 범위를 선택합니다.
- 그리고, 열 개수는 4로 지정합니다. 열 개수도 유동적으로 변경된다면 행 개수처럼 COUNTA함수를 사용해도 됩니다.
5. [새이름]에서 위의 수식을 입력한 후 [확인]을 클릭하면 다시 [이름관리자] 화면이 표시됩니다.
목록에 정의된 이름이 보입니다. [닫기]로 나옵니다.
6. 지정한 이름을 피벗 범위에 반영해보겠습니다.
피벗 테이블을 클릭한 후 [분석]-[데이터 원본 변경]을 클릭합니다.
2010버전이라면 [옵션]-[데이터 원본 변경]을 클릭합니다.
7. [표 또는 범위 선택]에서 기존 데이터 범위를 삭제하고 '피벗범위'를 입력합니다.
8. 결과를 확인해보겠습니다.
원본 데이터 범위 아래쪽에 임의대로 데이터를 추가해봅니다.
9. 피벗 테이블에서 오른쪽 마우스를 클릭하여 [새로 고침]을 선택합니다.
10. 추가한 데이터가 피벗 테이블에 자동으로 반영됩니다.
함수가 입력된 파일은 여기서 다운로드 가능합니다.
첨부파일
'Microsoft > Excel' 카테고리의 다른 글
| [펌] Set Excel file read-only MS Teams, OneDrive, or SharePoint (0) | 2021.10.17 |
|---|---|
| Excel Sheet 순서 변경 막기 (통합 문서 보호) (0) | 2021.07.30 |
| [펌] IF 함수 특정 문자 포함 조건 검색방법 :: 엑셀 공식 (0) | 2021.01.24 |
