Excel에서 확장되는 데이터를 자동으로 새로 고치는 동적 피벗테이블을 어떻게 생성합니까?

일반적으로 피벗테이블은 소스 데이터 범위의 업데이트된 데이터로 새로 고칠 수 있습니다. 그러나 소스 범위에 새 데이터를 추가하는 경우, 예를 들어 소스 범위의 하단이나 오른쪽에 새 행 또는 열 데이터를 추가하면, 확장된 데이터는 수동으로 피벗테이블을 새로 고쳐도 피벗테이블에 추가되지 않습니다. Excel에서 확장 데이터로 피벗테이블을 새로 고치려면 어떻게 해야 합니까? 이 문서의 방법들이 도움이 될 수 있습니다.

소스 범위를 테이블 범위로 변환하여 동적 피벗테이블 생성하기
OFFSET 수식을 사용하여 동적 피벗테이블 생성하기

소스 범위를 테이블 범위로 변환하여 동적 피벗테이블 생성하기

소스 데이터를 테이블로 변환하면 Excel에서 확장 데이터로 피벗테이블을 새로 고치는 데 도움이 될 수 있습니다. 아래 단계를 따르세요.

1. 데이터 범위를 선택하고 Ctrl + T 키를 동시에 누릅니다. 열리는 테이블 생성 대화 상자에서 확인 버튼을 클릭합니다.

2. 그런 다음 소스 데이터가 테이블 범위로 변환됩니다. 테이블 범위를 계속 선택한 상태에서 삽입 > 피벗테이블을 클릭합니다.

3. 피벗테이블 생성 창에서 피벗테이블을 배치할 위치를 선택하고 확인을 클릭합니다 (이 경우 현재 워크시트에 피벗테이블을 배치합니다).

4. 피벗테이블 필드 창에서 필드를 해당 영역으로 드래그합니다.

5. 이제 소스 범위의 하단이나 오른쪽에 새 데이터를 추가한 경우 피벗테이블로 이동하여 마우스 오른쪽 버튼을 클릭한 다음 컨텍스트 메뉴에서 새로 고침을 클릭합니다.

아래 스크린샷에 표시된 것처럼 피벗테이블이 확장 데이터로 새로 고쳐진 것을 볼 수 있습니다.

OFFSET 함수를 사용하여 동적 피벗테이블 생성하기

이 섹션에서는 OFFSET 함수의 도움으로 동적 피벗테이블을 생성하는 방법을 보여드리겠습니다.

1. 소스 데이터 범위를 선택하고 수식 > 이름 관리자를 클릭합니다. 스크린샷 보기:

2. 이름 관리자 창에서 새로 만들기 버튼을 클릭하여 이름 편집 대화 상자를 엽니다. 이 대화 상자에서 다음을 수행해야 합니다:

• 범위에 대한 이름을 이름 상자에 입력합니다;
• 아래 수식을 복사하여 참조 대상 상자에 붙여넣습니다;
  =OFFSET('dynamic pivot with table'!$A$1,0,0,COUNTA('dynamic pivot with table'!$A:$A),COUNTA('dynamic pivot with table'!$1:$1))
• 확인 버튼을 클릭합니다.

참고: 수식에서 'dynamic pivot with table'은 소스 범위를 포함하는 워크시트의 이름입니다; $A$1은 범위의 첫 번째 셀입니다; $A$A는 범위의 첫 번째 열입니다; $1$1은 범위의 첫 번째 행입니다. 자신의 소스 데이터 범위에 따라 변경하십시오.

3. 그러면 이름 관리자 창으로 돌아가며 새로 생성된 이름 범위가 창에 표시됩니다. 닫아 주세요.

4. 삽입 > 피벗테이블을 클릭합니다.

5. 피벗테이블 생성 창에서 2단계에서 지정한 범위 이름을 입력하고 피벗테이블을 배치할 위치를 선택한 다음 확인 버튼을 클릭합니다.

6. 피벗테이블 필드 창에서 필드를 해당 영역으로 드래그합니다.

7. 소스 범위에 새 데이터를 추가한 후 피벗테이블의 데이터는 새로 고침 옵션을 클릭하여 업데이트됩니다.

관련 기사