Meulah méja jadi lambar

Microsoft Excel gaduh seueur alat pikeun ngumpulkeun data tina sababaraha tabel (tina lambaran anu béda atanapi tina file anu béda): tautan langsung, fungsi. TEU LANGSUNG (Henteu adil), Power Query sareng Power Pivot add-ons, jsb. Ti sisi barricade ieu, sadayana katingali saé.

Tapi lamun ngajalankeun kana hiji masalah tibalik - nyebarkeun data tina hiji méja ka lambar béda - lajeng sagalana bakal loba sadder. Ayeuna, teu aya alat anu diwangun dina peradaban pikeun pamisahan data sapertos kitu dina arsenal Excel, hanjakalna. Janten anjeun kedah nganggo makro dina Visual Basic, atanapi nganggo pangrékam makro + kombinasi Power Query sareng sakedik "pemurnian file" saatos.

Hayu urang nempo leuwih deukeut kumaha ieu bisa dilaksanakeun.

Rumusan masalah

Kami gaduh data awal sapertos tabel kalayan ukuran langkung ti 5000 baris pikeun penjualan:

Pancén: ngadistribusikaeun data tina tabél ieu dumasar kota dina lambaran misah tina buku ieu. Jelema. Dina kaluaran, anjeun kedah nampi dina unggal lambar ngan ukur barisan tina méja dimana penjualan éta di kota anu saluyu:

nyiapkeun

Pikeun henteu ngahesekeun kodeu makro sareng ngajantenkeun sagampangna ngartos, hayu urang ngalaksanakeun sababaraha léngkah persiapan.

Kahiji, nyieun tabel lookup misah, dimana hiji kolom bakal daptar sadaya kota nu Anjeun hoyong jieun cadar misah. Tangtosna, diréktori ieu tiasa waé henteu ngandung sadaya kota anu aya dina sumber data, tapi ngan ukur anu urang peryogi laporan. Cara panggampangna pikeun nyiptakeun méja sapertos kitu nyaéta nganggo paréntah Data - Cabut duplikat (Data - Hapus duplikat) pikeun salinan kolom parasaan atawa fungsi UNIK (UNIK) - upami anjeun gaduh versi panganyarna tina Excel 365.

Kusabab lembar anyar dina Excel didamel sacara standar sateuacan (ka kénca) anu ayeuna (saméméhna), éta ogé masuk akal pikeun nyortir kota-kota dina diréktori ieu dina urutan nurun (ti Z ka A) - teras saatos nyiptakeun, kota. lambaranana bakal disusun dumasar abjad.

Kadua, anu пngarobah duanana tabel kana dinamis ("pinter") pikeun ngagampangkeun damel sareng aranjeunna. Urang make paréntah Imah - Format salaku méja (Imah - Format salaku Méja) atawa potong kompas keyboard Ctrl+T. Dina tab nu nembongan Constructor (Desain) hayu urang nelepon aranjeunna tablProdaji и TableCitymasing-masing:

Métode 1. Makro pikeun ngabagi ku lembar

Dina tab Advanced pamekar (Pamekar) pencét dina tombol visual Basic atanapi nganggo potong kompas keyboard Alt+F11. Dina jandela pangropéa makro nu muka, selapkeun modul kosong anyar ngaliwatan menu nu Selapkeun - Modul sareng salin kodeu ieu di dinya:

Sub Splitter() Pikeun Unggal sél Dina Range("таблГорода") Rentang("таблПродажи").Filter Otomatis Widang:=3, Kriteria1:=cell.Value Range("таблПродажи[#All]").Sel Khusus(xlCellTypeVisible). Sheets.Add ActiveSheet.Paste ActiveSheet.Name = cell.Value ActiveSheet.UsedRange.Columns.AutoFit Next Cell Worksheets("Data").ShowAllData End Sub	  

Di dieu kalawan loop a Pikeun Masing… Next dilaksanakeun petikan ngaliwatan sél diréktori TableCity, dimana pikeun tiap kota disaring (metode Filter Otomatis) dina tabel penjualan asli teras nyalin hasil kana lambaran anu nembé diciptakeun. Sapanjang jalan, lambaran anu diciptakeun diganti nami nami kota anu sami sareng pas lebar kolom pikeun kageulisan dihurungkeun.

Anjeun tiasa ngajalankeun makro anu diciptakeun dina Excel dina tab pamekar kancing Macros (Pamekar - Macros) atawa potong kompas keyboard Alt+F8.

Métode 2. Jieun sababaraha queries dina Power Query

Metodeu saméméhna, pikeun sakabéh compactness sarta kesederhanaan, boga aral signifikan - lambaran dijieun ku makro teu diropéa nalika parobahan dijieun dina tabel jualan aslina. Lamun ngamutahirkeun on laleur perlu, mangka anjeun kudu make VBA + Power Query kebat, atawa rada, nyieun maké makro teu ngan lembar jeung data statik, tapi diropéa query Power Query.

Makro dina hal ieu sawaréh sami sareng anu sateuacana (ogé gaduh siklus Pikeun Masing… Next pikeun iterate leuwih kota dina diréktori), tapi di jero loop moal aya deui nyaring jeung nyalin, tapi nyieun query Power Query sarta unggah hasilna kana lembar anyar:

Sub Splitter2 () Pikeun Unggal sél Dina Range ("Tabel Kota") ActiveWorkbook.Queries.Add Ngaran: = cell.Value, Formula: = _ "hayu" & Chr (13) & "" & Chr (10) & " Sumber = Excel.CurrentWorkbook(){[Name=""TableSales""]}[Eusi]," & Chr(13) & "" & Chr(10) & "#""Tipe Dirobah"" = Table.TransformColumnTypes(Sumber , {{""Kategori"", ketik téks}, {""Ngaran"", ketik téks}, {""Kota"", ketik téks}, {""Manajer"", ketik téks}, {""Deal titimangsa "", tipe datetime}, {""Biaya"", tipe nomer}})," & Chr(13) & "" & Chr (10) & " #"" Baris jeung filter dilarapkeun" = Table.Se "& _"lectRows(#""Tipe robah"", unggal ([Kota] = """ & cell.Value & """))" & Chr(13) & "" & Chr(10) & "di " & Chr (13) & "" & Chr (10) & " # "" Baris jeung filter dilarapkeun """ ActiveWorkbook.Worksheets.Add Jeung ActiveSheet.ListObjects.Add (SourceType: = 0, Source: = _ "OLEDB; Panyadia =Microsoft.Mashup.OleDb.1;Sumber Data=$Workbook$;Location=" & cell.Value & ";Extended Properties=""""" _ , Tujuan:=Rentang("$A$1")). QueryTable .CommandType = xlCmd Sql .CommandText = Array("PILIH *FROM [" & cell.Value & "]") .RowNumbers = Palsu .FillAdjacentFormulas = Palsu .PreserveFormatting = Leres .RefreshOnFileOpen = Palsu .BackgroundQuery = Leres .RefreshStyle = xlInsertDeleteCells .FavePasssword SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .PreserveColumnInfo = True .ListObject.DisplayName = cell.Value .Refresh BackgroundQuery:=Salah Tungtung Jeung ActiveSheet.Name = cell.Value Next Cell End Sub  

Saatos peluncuranna, urang bakal ningali lambaran anu sami dumasar kota, tapi parantos nyiptakeun Power Query query bakal ngabentuk:

Kalayan sagala parobahan dina data sumber, éta bakal cukup pikeun ngapdet tabel pakait jeung tombol mouse katuhu - paréntah Apdet & Simpen (Refresh) atanapi ngapdet sadaya kota sakaligus dina jumlah ageung nganggo tombol Apdét Sadaya tab data (Data - Refresh Sadaya).

• Naon macros, kumaha carana nyieun sarta ngagunakeun éta
• Nyimpen lambaran workbook salaku file misah
• Ngumpulkeun data tina sakabéh lambar buku kana hiji méja