여러 데이터 범위의 피벗 테이블

문제의 공식화

피벗 테이블은 Excel에서 가장 놀라운 도구 중 하나입니다. 그러나 불행히도 지금까지 Excel 버전은 예를 들어 다른 시트나 다른 테이블에 있는 여러 초기 데이터 범위에 대한 요약을 작성하는 것과 같이 간단하고 필요한 작업을 즉석에서 수행할 수 없습니다.

시작하기 전에 몇 가지 사항을 명확히 하겠습니다. 선험적으로 우리 데이터에서 다음 조건이 충족된다고 생각합니다.

• 테이블에는 모든 데이터가 포함된 행이 여러 개 있을 수 있지만 헤더는 동일해야 합니다.
• 원본 테이블이 있는 시트에는 추가 데이터가 없어야 합니다. 한 장 – 한 테이블. 제어하려면 키보드 단축키를 사용하는 것이 좋습니다. Ctrl 키+종료, 워크시트에서 마지막으로 사용한 셀로 이동합니다. 이상적으로는 데이터 테이블의 마지막 셀이어야 합니다. 클릭하면 Ctrl 키+종료 테이블 오른쪽이나 아래에 있는 빈 셀이 강조 표시됩니다. 테이블 오른쪽에 있는 이러한 빈 열이나 테이블 아래에 있는 행을 삭제하고 파일을 저장합니다.

방법 1: 파워 쿼리를 사용하여 피벗에 대한 테이블 작성

Excel용 2010 버전부터 모든 데이터를 수집 및 변환한 다음 피벗 테이블 작성을 위한 소스로 제공할 수 있는 무료 파워 쿼리 추가 기능이 있습니다. 이 추가 기능의 도움으로 문제를 해결하는 것은 전혀 어렵지 않습니다.

먼저 Excel에서 새 빈 파일을 만들어 보겠습니다. 이 파일에서 어셈블리가 수행되고 그 안에 피벗 테이블이 만들어집니다.

그런 다음 탭에서 Data (Excel 2016 이상을 사용하는 경우) 또는 탭에서 파워 쿼리 (Excel 2010-2013이 있는 경우) 명령 선택 쿼리 만들기 – 파일에서 – Excel (데이터 가져오기 — 파일에서 — Excel) 수집할 테이블이 있는 소스 파일을 지정합니다.

표시되는 창에서 아무 시트나 선택하고(어느 시트든 상관 없음) 아래 버튼을 누릅니다. 변화 (수정):

파워 쿼리 쿼리 편집기 창이 Excel 위에서 열려야 합니다. 패널의 창 오른쪽에 요청 매개 변수 첫 번째 단계를 제외하고 자동으로 생성된 모든 단계 삭제 – 출처 (출처):

이제 모든 시트의 일반 목록이 표시됩니다. 데이터 시트 외에 파일에 다른 사이드 시트가 있는 경우 이 단계에서 테이블 헤더의 필터를 사용하는 다른 모든 시트를 제외하고 정보를 로드해야 하는 시트만 선택하는 것입니다.

열을 제외한 모든 열 삭제 Data열 머리글을 마우스 오른쪽 버튼으로 클릭하고 선택하여 다른 열 삭제 (제거하다 다른 열):

그런 다음 열 상단의 이중 화살표를 클릭하여 수집된 테이블의 내용을 확장할 수 있습니다(확인란 원래 열 이름을 접두사로 사용 당신은 그것을 끌 수 있습니다):

모든 작업을 올바르게 수행했다면 이 시점에서 수집된 모든 테이블의 내용이 다른 테이블 아래에 표시되어야 합니다.

버튼을 사용하여 첫 번째 행을 테이블 머리글로 올리는 것이 남아 있습니다. 첫 번째 줄을 헤더로 사용 (첫 번째 행을 헤더로 사용) 탭 홈 (집) 필터를 사용하여 데이터에서 중복 테이블 헤더를 제거합니다.

명령으로 수행한 모든 작업 저장 닫기 및 로드 – 닫기 및 로드… (닫기 및 로드 — 닫기 및 로드...) 탭 홈 (집), 열리는 창에서 옵션을 선택합니다. 연결만 (연결 전용):

모든 것. 요약을 작성하는 것만 남아 있습니다. 이렇게하려면 탭으로 이동하십시오. 삽입 – 피벗 테이블 (삽입 - 피벗 테이블), 옵션을 선택 외부 데이터 소스 사용 (외부 데이터 소스 사용)그런 다음 버튼을 클릭하여 연결을 선택, 우리의 요청. 피벗의 추가 생성 및 구성은 필요한 필드를 행, 열 및 값 영역으로 드래그하여 완전히 표준적인 방식으로 발생합니다.

나중에 소스 데이터가 변경되거나 몇 개의 상점 시트가 추가되면 명령을 사용하여 쿼리와 요약을 업데이트하는 것으로 충분합니다. 모두 새로고침 탭 Data (데이터 — 모두 새로 고침).

방법 2. 매크로에서 UNION SQL 명령으로 테이블을 통합합니다.

우리 문제에 대한 또 다른 솔루션은 다음 명령을 사용하여 피벗 테이블에 대한 데이터 세트(캐시)를 생성하는 이 매크로로 표시됩니다. 단일성 SQL 쿼리 언어. 이 명령은 배열에 지정된 모든 테이블을 결합합니다. 시트 이름 책의 시트를 단일 데이터 테이블로 만듭니다. 즉, 다른 시트에서 한 시트로 범위를 물리적으로 복사하여 붙여넣는 대신 컴퓨터의 RAM에서 동일한 작업을 수행합니다. 그런 다음 매크로는 지정된 이름(변수 결과 시트 이름) 수집된 캐시를 기반으로 본격적인(!) 요약을 생성합니다.

매크로를 사용하려면 탭에서 Visual Basic 버튼을 사용하십시오. 개발자 (개발자) 또는 키보드 단축키 다른+F11. 그런 다음 메뉴를 통해 새 빈 모듈을 삽입합니다. 삽입 – 모듈 거기에 다음 코드를 복사하십시오.

Sub New_Multi_Table_Pivot() Dim i As Long Dim arSQL() As String Dim objPivotCache As PivotCache Dim objRS As Object Dim ResultSheetName As String Dim SheetsNames As Variant '결과 피벗이 표시될 시트 이름 ResultSheetName = "Pivot" '시트의 배열 원본 테이블이 있는 이름 SheetsNames = Array("Alpha", "Beta", "Gamma", "Delta") ' SheetsNames With ActiveWorkbook ReDim arSQL(1 To (UBound(SheetsNames) + 1) ) For i = LBound(SheetsNames) To UBound(SheetsNames) arSQL(i + 1) = "SELECT * FROM [" & SheetsNames(i) & "$]" 다음 i Set objRS = CreateObject("ADODB.Recordset") objRS .Open Join$( arSQL, " UNION ALL "), _ Join$(Array("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=", _ .FullName, ";Extended Properties=""Excel 8.0;" ""), vbNullString ) End With '결과 피벗 테이블을 표시하도록 시트를 다시 생성합니다. On Error Resume Next Application.DisplayAlerts = False Worksheets(ResultSheetName).Delete Set wsPivot = Worksheets.Add wsPivo 티. Name = ResultSheetName '이 시트에 생성된 캐시 요약 표시 Set objPivotCache = ActiveWorkbook.PivotCaches.Add(xlExternal) Set objPivotCache.Recordset = objRS Set objRS = Nothing With wsPivot objPivotCache.CreatePivotTable TableDestination:=wsPivot.Range("A3") Set objPivotCache = 없음 Range("A3"). End With End Sub 선택    

완성된 매크로는 키보드 단축키로 실행할 수 있습니다. 다른+F8 또는 탭의 매크로 버튼 개발자 (개발자 — 매크로).

이 접근 방식의 단점:

• 캐시가 소스 테이블에 연결되어 있지 않기 때문에 데이터가 업데이트되지 않습니다. 소스 데이터를 변경하는 경우 매크로를 다시 실행하고 요약을 다시 작성해야 합니다.
• 매수 변경 시 매크로 코드(array 시트 이름).
  

그러나 결국 우리는 서로 다른 시트의 여러 범위를 기반으로 구축된 본격적인 피벗 테이블을 얻습니다.

자보세요!

기술 참고 : 매크로를 실행할 때 "제공자가 등록되지 않음"과 같은 오류가 발생하면 64비트 버전의 Excel이 있거나 불완전한 버전의 Office가 설치되어 있을 가능성이 큽니다(액세스 없음). 상황을 수정하려면 매크로 코드에서 조각을 교체하십시오.

	 공급자=Microsoft.Jet.OLEDB.4.0;  

받는 사람 :

	공급자=Microsoft.ACE.OLEDB.12.0;  

Microsoft 웹 사이트의 Access에서 무료 데이터 처리 엔진을 다운로드하여 설치합니다. Microsoft Access Database Engine 2010 재배포 가능 패키지

방법 3: 이전 버전의 Excel에서 피벗 테이블 마법사 통합

이 방법은 약간 구식이지만 여전히 언급할 가치가 있습니다. 공식적으로 말하면 2003년까지의 모든 버전에는 피벗 테이블 마법사에 "여러 통합 범위에 대한 피벗 작성" 옵션이 있었습니다. 그러나 이러한 방식으로 구성된 보고서는 불행히도 실제 완전한 요약의 가련한 모양일 뿐이며 기존 피벗 테이블의 많은 "칩"을 지원하지 않습니다.

이러한 피벗에서는 필드 목록에 열 머리글이 없고 유연한 구조 설정이 없으며 사용되는 기능 집합이 제한되어 있으며 일반적으로 이 모든 것이 피벗 테이블과 매우 유사하지 않습니다. 아마도 이것이 2007년부터 Microsoft가 피벗 테이블 보고서를 생성할 때 표준 대화 상자에서 이 기능을 제거한 이유일 것입니다. 이제 이 기능은 사용자 지정 버튼을 통해서만 사용할 수 있습니다. 피벗 테이블 마법사(피벗 테이블 마법사), 원하는 경우 다음을 통해 빠른 실행 도구 모음에 추가할 수 있습니다. 파일 – 옵션 – 빠른 실행 도구 모음 사용자 지정 – 모든 명령 (파일 — 옵션 — 빠른 실행 도구 모음 사용자 정의 — 모든 명령):

추가된 버튼을 클릭한 후 마법사의 첫 번째 단계에서 적절한 옵션을 선택해야 합니다.

그런 다음 다음 창에서 각 범위를 차례로 선택하고 일반 목록에 추가합니다.

그러나 다시 말하지만 이것은 완전한 요약이 아니므로 너무 많이 기대하지 마십시오. 아주 간단한 경우에만 이 옵션을 추천할 수 있습니다.

• 피벗 테이블로 보고서 만들기
• 피벗 테이블에서 계산 설정
• 매크로 란 무엇이며 사용 방법, VBA 코드를 복사하는 위치 등
• 여러 시트에서 하나로 데이터 수집(PLEX 애드온)