Inhalter
Microsoft Excel huet vill Tools fir Daten aus verschiddenen Dëscher ze sammelen (vu verschiddene Blieder oder aus verschiddene Dateien): direkt Linken, Funktioun INDIREKTE (INDIREKT), Power Query a Power Pivot Add-ons, etc. Vun dëser Säit vun der Barrikade gesäit alles gutt aus.
Awer wann Dir an en inverse Problem leeft - d'Verbreedung vun Daten vun enger Tabell op verschidde Blieder - da wäert alles vill méi traureg sinn. Am Moment ginn et leider keng ziviliséiert agebaute Tools fir sou Datestrennung am Arsenal vun Excel. Also musst Dir e Macro an Visual Basic benotzen, oder de Macro Recorder benotzen + Power Query Kombinatioun mat e bëssen "Fichierraffinement" na.
Loosst eis e bësse méi no kucken wéi dat ëmgesat ka ginn.
Formuléierung vum Problem
Mir hunn als initial Donnéeën sou eng Tabell mat enger Gréisst vu méi wéi 5000 Reihen fir Verkaf:
Aufgab: d'Donnéeën aus dëser Tabell no Stad op getrennten Blieder vun dësem Buch ze verdeelen. Déi. bei der Ausgab musst Dir op all Blat nëmmen déi Reihen aus der Tabell kréien, wou de Verkaf an der entspriechender Stad war:
Ze preparéieren
Fir de Makrocode net ze komplizéieren an et sou einfach wéi méiglech ze verstoen, loosst eis e puer Virbereedungsschrëtt maachen.
Éischtens eng separat Lookup Dësch erstellen, wou eng eenzeg Kolonn all d'Stied oplëscht fir déi Dir separat Blieder erstellen wëllt. Natierlech kann dëse Verzeechnes net all d'Stied enthalen, déi an de Quelldaten präsent sinn, awer nëmmen déi, fir déi mir Rapporte brauchen. Deen einfachste Wee fir sou en Dësch ze kreéieren ass de Kommando ze benotzen Daten - Ewechzehuelen Duplikaten (Daten - Duplikate läschen) fir Kolonn Kopie Gefiller oder Funktioun UNIK (UNIQUE) - wann Dir déi lescht Versioun vun Excel 365 hutt.
Well nei Blieder an Excel par défaut virun (lénks vun) der aktueller (virdrun) erstallt ginn, mécht et och Sënn fir d'Stied an dësem Verzeechnes an ofstigend Uerdnung (vun Z bis A) ze sortéieren - dann no der Schafung, d'Stad. Blieder ginn alphabetesch arrangéiert.
Zweetens, den пkonvertéiert béid Dëscher op dynamesch ("Smart") fir et méi einfach mat hinnen ze schaffen. Mir benotzen de Kommando Home - Format als Dësch (Home - Format als Table) oder Tastatur Ofkiirzung Ctrl+T. Op der Tab déi erschéngt Builder (Design) ruffe mer hinnen un tablProdaji и TableCity, respektiv:
Method 1. Macro fir Divisioun duerch Blieder
Op der Advanced Tab Entwéckler (Entwéckler) klickt op de Knäppchen visuell Grondleegend oder benotzt Tastatur Ofkiirzung Alt+F11. An der Macro Editor Fënster déi opmaacht, setzt en neien eidele Modul duerch de Menü Insert - Modul a kopéiert de folgende Code do:
Sub Splitter() For Every cell In Range("таблГорода") Range("таблПродажи").AutoFilter Field:=3, Criteria1:=cell.Value Range("таблПродажи[#All]").SpecialCells(xlCell). Sheets.Add ActiveSheet.Paste ActiveSheet.Name = cell.Value ActiveSheet.UsedRange.Columns.AutoFit Next cell Worksheets("Данные").ShowAllData End Sub Hei mat enger Loop Fir all ... Nächst implementéiert de Passage duerch d'Zellen vum Verzeichnis TableCity, wou fir all Stad se gefiltert gëtt (Method AutoFilter) an der ursprénglecher Verkafstabell a kopéiert dann d'Resultater op dat nei erstallt Blat. Niewt dem Wee gëtt dat erstallt Blat mam selwechten Numm vun der Stad ëmbenannt an automatesch d'Breet vun de Sailen fir d'Schéinheet ugeet.
Dir kënnt de erstallt Makro an Excel op der Tab lafen Entwéckler Knäppchen macros (Entwéckler - Macros) oder Tastatur Ofkiirzung Alt+F8.
Method 2. Erstellt verschidde Ufroen an Power Query
Déi viregt Method, fir all seng Kompaktheet an Einfachheet, huet e wesentlechen Nodeel - d'Blieder, déi vum Makro erstallt ginn, ginn net aktualiséiert wann Ännerungen an der ursprénglecher Verkafstabelle gemaach ginn. Wann d'Aktualiséierung op der Flucht noutwendeg ass, da musst Dir de VBA + Power Query Bündel benotzen, oder éischter, erstellen mat engem Makro net nëmme Blieder mat statesche Daten, awer aktualiséiert Power Query Ufroen.
De Makro an dësem Fall ass deelweis ähnlech wéi dee virdrun (en huet och en Zyklus Fir all ... Nächst fir iwwer Stied am Verzeechnes ze iteréieren), awer bannent der Loop gëtt et net méi gefiltert a kopéiert, awer eng Power Query Ufro erstallt a seng Resultater op en neit Blat eropgelueden:
Sub Splitter2() For Every cell In Range("City table") ActiveWorkbook.Queries.Add Name:=cell.Value, Formula:= _ "lass" & Chr(13) & "" & Chr(10) & " Source = Excel.CurrentWorkbook(){[Name=""TableSales""]}[Content]," & Chr(13) & "" & Chr(10) & " #""Changed Type"" = Table.TransformColumnTypes(Source , {{""Category"", type text}, {""Name"", type text}, {""City"", type text}, {""Manager"", type text}, {""Deal" date "", type datetime}, {""Cost"", type number}})," & Chr(13) & "" & Chr(10) & " #""Reihen mit Filter applizéiert"" = Table.Se " & _ "lectRows(#""Geännert Typ"", jeweils ([City] = """ & cell.Value & """))" & Chr(13) & "" & Chr(10) & "in " & Chr(13) & "" & Chr(10) & " #""Reihen mat Filter applizéiert""" ActiveWorkbook.Worksheets.Add With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _ "OLEDB; Provider =Microsoft.Mashup.OleDb.1;Datenquell=$Workbook$;Location=" & cell.Value & ";Extended Properties=""""" _, Destination:=Range("$A$1")). QueryTable .CommandType = xlCmd Sql .CommandText = Array("SELECT *VUN [" & cell.Value & "]") .RowNumbers = Falsch .FillAdjacentFormulas = Falsch .PreserveFormatting = Richteg .RefreshOnFileOpen = Falsch .BackgroundQuery = True .RefreshStyle = xlInsertDeleteCells = .SavePassword. SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .PreserveColumnInfo = True .ListObject.DisplayName = cell.Value .Refresh BackgroundQuery:=False End With ActiveSheet.Name = cell.Value Next cell End Sub No sengem Start wäerte mir déiselwecht Blieder no Stad gesinn, awer scho erstallt Power Query Ufroen bilden se:
Mat all Ännerunge vun der Quelldaten ass et genuch fir déi entspriechend Tabell mat der rietser Maus Knäppchen ze aktualiséieren - de Kommando Update & späicheren (Erfrëschen) oder update all Stied op eemol am bulk de Knäppchen benotzt Update All Reiter Daten (Daten - Alles erfrëschen).
- Wat sinn Makroen, wéi se erstellen a benotzen
- Spueren Aarbechtsbicher Blieder als separat Fichieren
- Sammelen Daten aus all Blieder vum Buch an een Dësch