엑셀 / 피벗 테이블 / 외부 파일 시트의 데이터로 피벗 테이블 만드는 방법

피벗 테이블은 다른 Excel 파일의 표나 범위를 원본으로 사용할 수 있습니다. 다만 새로 고침이 원본 파일을 닫은 상태에서 가능한지는 연결 방식과 환경에 따라 다릅니다. 원본과 보고서 파일을 분리할 때는 경로·권한·원본 범위와 갱신 방법을 함께 관리해야 합니다.

원본 파일 준비

Excel_1의 데이터를 표로 구성한 예입니다. 각 열에 고유한 제목을 두고 한 행에 한 건을 기록합니다. 파일을 저장하고 보고서를 만들 Excel_2도 준비합니다.

원본 파일을 보고서와 함께 이동하면 경로가 달라질 수 있습니다. 반복 갱신이 필요하다면 네트워크·클라우드 접근 권한도 확인하고 최신 데이터의 저장 시점을 정해 두세요.

연결된 표를 선택하는 예

1. Excel_2에서 [삽입] → [피벗 테이블]을 엽니다.

2. [외부 데이터 원본 사용] → [연결 선택]을 선택합니다. 연결 목록은 환경과 열린 파일·기존 연결에 따라 달라집니다.

3. 사용할 수 있는 [테이블] 목록에서 Excel_1의 대상 표를 선택합니다. 같은 이름의 표가 다른 파일에 있을 수 있으므로 원본 경로를 확인합니다.

4. 보고서 위치와 원본을 확인하고 생성합니다.

빈 피벗 보고서와 필드 목록이 나타난 예입니다.

5. 분석할 항목을 행·열·값·필터에 넣습니다. 금액 집계가 합계인지 개수인지 확인합니다.

6. 원본 자료를 바꾼 뒤 저장하고 보고서에서 [새로 고침]을 실행합니다. 화면이 바뀌었다는 것만 보지 말고 변경한 숫자가 집계에 반영됐는지 확인합니다.

원본 파일 상태에 따라 새로 고침 오류가 나는 기존 연결 예입니다. 이 방식에서는 원본을 연 상태로 다시 확인할 수 있지만, 모든 외부 연결이 반드시 열린 파일을 요구한다는 뜻은 아닙니다.

다른 파일의 범위를 직접 지정하는 예

1. [표 또는 범위 선택]으로 보고서를 만드는 경로를 선택합니다. 새 버전에서는 메뉴가 ‘표/범위에서’처럼 구분되어 표시될 수 있습니다.

2. 원본 파일에서 제목을 포함한 데이터 범위를 선택합니다.

3. 보고서 위치를 선택하고 생성을 마칩니다. 보고서 파일이 아닌 원본 파일에 만들지 않도록 확인합니다.

외부 범위로 만들어진 피벗 테이블 예입니다.

필요한 필드를 배치한 보고서 예입니다. 원본이 고정 범위인지 표 참조인지도 관리해야 합니다.

4. 원본 파일을 닫은 상태에서 새로 고침이 되는지 해당 환경에서 확인합니다. 기존 예제의 동작을 모든 Excel 버전·저장 위치에 일반화하지 마세요.

5. 원본 데이터를 수정한 뒤 먼저 파일을 저장합니다. 저장되지 않은 변경이 닫힌 파일을 읽는 연결에 반영되는 것은 아닙니다.

6. 보고서 파일에서 새로 고침하고 집계·원본 행 수·변경된 항목을 확인합니다. 오류가 없다 해도 이전 캐시를 표시하는 것인지 최신 값인지 대조합니다.

기능과 적용 제품은 Microsoft의 피벗 테이블 만들기 안내, Microsoft의 피벗 원본 변경 안내에서 확인할 수 있습니다.

더 안정적인 갱신을 위해

파일명·경로가 바뀌면 원본 참조·연결 설정을 수정합니다. 인증이 필요한 저장소는 권한과 자격 증명 조건을 확인하고 보안 경고를 무조건 해제하지 마세요. 반복해서 외부 파일을 읽는 작업에는 [데이터]의 Power Query로 파일 데이터를 불러와 정리한 뒤 피벗을 만드는 방법도 검토할 수 있습니다.

고정 범위 아래에 새 행을 추가해도 자동 포함되지 않을 수 있습니다. 갱신 전 원본 범위와 보고서 합계가 맞는지 확인하세요. 공유하는 보고서에는 캐시로 원본 데이터가 남을 수 있으므로 기밀 자료를 분리했다고 자동 보호되는 것은 아닙니다.