Microsoft Excel에는 여러 테이블(다른 시트 또는 다른 파일)에서 데이터를 수집하기 위한 많은 도구가 있습니다. 직접 링크, 기능 간접적 인 (간접), 파워 쿼리 및 파워 피벗 추가 기능 등 바리케이드의 이쪽에서 보면 모든 것이 좋아 보입니다.
그러나 역 문제(한 테이블에서 다른 시트로 데이터를 퍼뜨리는 것)가 발생하면 모든 것이 훨씬 더 슬퍼질 것입니다. 불행히도 현재 Excel의 무기고에는 이러한 데이터 분리를 위한 문명화된 기본 제공 도구가 없습니다. 따라서 Visual Basic에서 매크로를 사용하거나 매크로 레코더 + 파워 쿼리 조합을 약간의 "파일 미세 조정"과 함께 사용해야 합니다.
이를 어떻게 구현할 수 있는지 자세히 살펴보겠습니다.
문제의 공식화
판매용으로 5000개 이상의 행 크기를 가진 테이블과 같은 초기 데이터가 있습니다.
작업: 이 표의 데이터를 이 책의 별도 시트에 도시별로 배포합니다. 저것들. 출력에서 해당 도시에서 판매가 있었던 테이블의 행만 각 시트에 가져와야합니다.
Prepare
매크로 코드를 복잡하게 만들지 않고 최대한 이해하기 쉽게 하기 위해 몇 가지 준비 단계를 수행해 보겠습니다.
첫째, 별도의 룩업 테이블 생성, 여기서 단일 열에는 별도의 시트를 만들려는 모든 도시가 나열됩니다. 물론 이 디렉토리에는 소스 데이터에 있는 모든 도시가 포함되어 있지 않을 수 있지만 보고서가 필요한 도시만 포함될 수 있습니다. 이러한 테이블을 만드는 가장 쉬운 방법은 다음 명령을 사용하는 것입니다. 데이터 – 중복 제거 (데이터 — 중복 제거) 열 복사용 City 또는 기능 유니크 (독특한) – 최신 버전의 Excel 365가 있는 경우.
Excel의 새 시트는 기본적으로 현재(이전) 시트보다 먼저(왼쪽에) 생성되기 때문에 이 디렉토리의 도시를 내림차순(Z에서 A로)으로 정렬한 다음 생성 후에 도시를 정렬하는 것도 의미가 있습니다. 시트는 알파벳순으로 정렬됩니다.
둘째, п두 테이블을 모두 동적 테이블로 변환 ("스마트")를 사용하여 더 쉽게 작업할 수 있습니다. 우리는 명령을 사용합니다 홈 – 테이블 형식 (홈 — 표 형식) 또는 키보드 단축키 Ctrl 키+T. 표시되는 탭에서 건설자 (디자인) 그들을 부르자 테이블프로다지 и 테이블시티, 각각 :
방법 1. 시트로 분할을 위한 매크로
고급 탭에서 개발자 (개발자) 버튼을 클릭하십시오. Visual Basic을 또는 키보드 단축키를 사용 다른+F11. 열리는 매크로 편집기 창에서 메뉴를 통해 새 빈 모듈을 삽입합니다. 삽입 – 모듈 거기에 다음 코드를 복사하십시오.
Sub Splitter() In Range("таблГорода") Range("таблПродажи").AutoFilter Field:=3, Criteria1:=cell.Value Range("таблПродажи[#All]").SpecialCells(xlCellTypeVisible).Copy Sheets.Add ActiveSheet.Paste ActiveSheet.Name = cell.Value ActiveSheet.UsedRange.Columns.AutoFit 다음 셀 Worksheets("Данные").ShowAllData End Sub 여기 루프와 함께 각각 ... 다음 디렉토리의 셀을 통한 통과를 구현했습니다. 테이블시티, 각 도시에 대해 필터링되는 위치(메서드 자동 필터) 원본 판매 테이블에서 결과를 새로 만든 시트에 복사합니다. 그 과정에서 생성된 시트의 이름이 도시의 동명으로 바뀌고 미인을 위한 열 너비 자동 맞춤이 켜집니다.
생성된 매크로는 엑셀 탭에서 실행할 수 있습니다. 개발자 단추 매크로 (개발자 — 매크로) 또는 키보드 단축키 다른+F8.
방법 2. 파워 쿼리에서 여러 쿼리 만들기
이전 방법은 모든 간결함과 단순함 때문에 중요한 단점이 있습니다. 원래 판매 테이블이 변경될 때 매크로로 생성된 시트가 업데이트되지 않는다는 것입니다. 즉석에서 업데이트해야 하는 경우 VBA + 파워 쿼리 번들을 사용하거나 정적 데이터가 있는 시트뿐만 아니라 업데이트된 파워 쿼리 쿼리를 사용하여 매크로를 생성해야 합니다.
이 경우 매크로는 이전 매크로와 부분적으로 유사합니다. 각각 ... 다음 디렉터리의 도시를 반복하기 위해) 루프 내에서 더 이상 필터링 및 복사가 수행되지 않지만 파워 쿼리 쿼리를 생성하고 그 결과를 새 시트에 업로드합니다.
Sub Splitter2() For Each cell In Range("City table") ActiveWorkbook.Queries.Add Name:=cell.Value, Formula:= _ "let" & Chr(13) & "" & Chr(10) & " Source = Excel.CurrentWorkbook(){[Name=""TableSales""]}[Content]," & Chr(13) & "" & Chr(10) & " #""변경된 유형"" = Table.TransformColumnTypes(소스 , {{""Category"", type text}, {""Name"", type text}, {""City"", type text}, {""Manager"", type text}, {""거래 date "", type datetime}, {""Cost"", type number}})," & Chr(13) & "" & Chr(10) & " #""필터가 적용된 행"" = Table.Se " & _ "lectRows(#""변경된 유형"", 각각 ([도시] = """ & cell.Value & """))" & Chr(13) & "" & Chr(10) & "in " & Chr(13) & "" & Chr(10) & " #""필터가 적용된 행""" ActiveWorkbook.Worksheets.Add With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _ "OLEDB; Provider =Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=" & cell.Value & ";Extended Properties=""""" _ , Destination:=Range("$A$1")). QueryTable .CommandType = xlCmd SQL .CommandText = Array("SELECT *FROM [" & cell.Value & "]") .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .BackgroundQuery = True .RefreshStyle = xlInsertDeleteCells .SavePassword = False . SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .PreserveColumnInfo = True .ListObject.DisplayName = cell.Value .Refresh BackgroundQuery:=False End With ActiveSheet.Name = cell.Value 다음 셀 End Sub 출시 후 도시별로 동일한 시트를 볼 수 있지만 이미 생성된 파워 쿼리 쿼리가 시트를 형성합니다.
소스 데이터가 변경되면 마우스 오른쪽 버튼으로 해당 테이블을 업데이트하는 것으로 충분합니다. 업데이트 및 저장 (새로 고치다) 또는 버튼을 사용하여 한 번에 모든 도시를 일괄 업데이트 모두 업데이트 탭 Data (데이터 — 모두 새로 고침).
- 매크로 란 무엇이며 매크로를 만들고 사용하는 방법
- 통합 문서 시트를 별도의 파일로 저장
- 책의 모든 시트에서 데이터를 하나의 테이블로 수집