Pisah meja dadi lembaran

Microsoft Excel nduweni akeh alat kanggo ngumpulake data saka sawetara tabel (saka lembar sing beda utawa saka file sing beda): pranala langsung, fungsi LANGSUNG (Ora adil), Power Query lan Power Pivot add-ons, etc. Saka sisih barricade iki, kabeh katon apik.

Nanging yen sampeyan nemoni masalah kuwalik - nyebar data saka siji meja menyang lembaran sing beda-beda - banjur kabeh bakal luwih sedhih. Saiki, ora ana alat sing dibangun ing peradaban kanggo pamisahan data kasebut ing arsenal Excel, sayangé. Dadi sampeyan kudu nggunakake makro ing Visual Basic, utawa nggunakake recorder makro + kombinasi Power Query karo "refinement file" sethitik sawise.

Ayo padha nliti carane iki bisa dileksanakake.

Formulasi masalah

Kita duwe data awal kayata tabel kanthi ukuran luwih saka 5000 baris kanggo dodolan:

Tugas: nyebarake data saka tabel iki miturut kutha ing lembaran sing kapisah saka buku iki. Sing. ing output, sampeyan kudu njaluk ing saben sheet mung sing larik saka meja ngendi Advertisement ing kutha sing cocog:

Siapke

Supaya ora nggawe rumit kode makro lan supaya gampang dingerteni, ayo nindakake sawetara langkah persiapan.

Pisanan, sing nggawe tabel lookup kapisah, ing ngendi kolom siji bakal nyathet kabeh kutha sing pengin digawe sheet kapisah. Mesthine, direktori iki bisa uga ora ngemot kabeh kutha sing ana ing data sumber, nanging mung sing mbutuhake laporan. Cara paling gampang kanggo nggawe tabel kasebut yaiku nggunakake perintah kasebut Data - Mbusak Duplikat (Data - Mbusak duplikat) kanggo salinan kolom City utawa fungsi UNIK (UNIK) – yen sampeyan duwe versi paling anyar saka Excel 365.

Wiwit lembar anyar ing Excel digawe kanthi gawan sadurunge (ing sisih kiwa) sing saiki (sadurunge), iku uga penting kanggo ngurutake kutha ing direktori iki kanthi urutan mudhun (saka Z nganti A) - banjur sawise nggawe, kutha sheets bakal disusun miturut abjad.

Kapindho, ing пNgonversi loro tabel dadi dinamis ("pinter") supaya luwih gampang nggarap dheweke. Kita nggunakake printah Ngarep - Format minangka tabel (Ngarep - Format minangka Tabel) utawa trabasan keyboard Ctrl+T. Ing tab sing katon Constructor (Desain) ayo padha nelpon tablProdaji и TabelKota, miturut:

Cara 1. Makro kanggo divisi kanthi lembaran

Ing tab Lanjut pangembang (Pengembang) klik ing tombol visual Basic utawa nggunakake trabasan keyboard Alt+F11. Ing jendhela editor makro sing mbukak, lebokake modul kosong anyar liwat menu Insert - Modul lan salin kode ing ngisor iki ana:

Sub Splitter() Kanggo Saben sel Ing Range("таблГорода") Range("таблПродажи").Filter Otomatis Bidang:=3, Kriteria1:=cell.Value Range("таблПродажи[#All]").SpecialCells(xlCellTypeVisible).CopyVisible. Sheets.Add ActiveSheet.Paste ActiveSheet.Name = cell.Value ActiveSheet.UsedRange.Columns.AutoFit Next cell Worksheets("Data").ShowAllData End Sub	  

Kene karo loop Kanggo Saben ... Sabanjure ngleksanakake wacana liwat sel direktori TabelKota, ing ngendi kanggo saben kutha disaring (metode Filter otomatis) ing tabel dodolan asli banjur nyalin asil menyang sheet sing mentas digawe. Ing sadawane dalan, lembaran sing digawe diganti jeneng kanthi jeneng kutha sing padha lan kanthi otomatis nyetel jembar kolom kanggo kaendahan diuripake.

Sampeyan bisa mbukak makro sing digawe ing Excel ing tab pangembang tombol macro (Pangembang - Makro) utawa trabasan keyboard Alt+F8.

Cara 2. Nggawe pirang-pirang pitakon ing Power Query

Cara sadurunge, kanggo kabeh kekompakan lan kesederhanaan, nduweni kekurangan sing signifikan - lembaran sing digawe dening makro ora dianyari nalika owah-owahan digawe ing tabel penjualan asli. Yen nganyari on the fly perlu, sampeyan kudu nggunakake VBA + Power Query bundle, utawa luwih, nggawe nggunakake makro ora mung sheets karo data statis, nanging nganyari Power Query query.

Makro ing kasus iki meh padha karo sing sadurunge (uga duwe siklus Kanggo Saben ... Sabanjure kanggo ngubengi kutha-kutha ing direktori), nanging ing jero daur ulang ora bakal ana maneh nyaring lan nyalin, nanging nggawe pitakon Power Query lan ngunggah asile menyang lembar anyar:

Sub Splitter2() Kanggo Saben sel Ing Range("Tabel kutha") ActiveWorkbook.Queries.Add Jeneng:=cell.Value, Formula:= _ "ayo" & Chr(13) & "" & Chr(10) & " Sumber = Excel.CurrentWorkbook(){[Name=""TableSales""]}[Content]," & Chr(13) & "" & Chr(10) & " #""Changed Type"" = Table.TransformColumnTypes(Sumber , {{""Kategori"", ketik teks}, {""Jeneng"", ketik teks}, {""Kutha"", ketik teks}, {""Manajer"", ketik teks}, {""Deal tanggal "", ketik datetime}, {""Biaya"", ketik nomer}})," & Chr(13) & "" & Chr(10) & " #""Baris karo filter sing ditrapake"" = Tabel.Se "& _ "lectRows(#""Jenis sing diganti"", saben ([Kutha] = """ & cell.Value & """))" & Chr(13) & "" & Chr(10) & "in " & Chr (13) & "" & Chr (10) & " # "" Baris karo filter ditrapake """ ActiveWorkbook.Worksheets.Add Kanthi ActiveSheet.ListObjects.Add (SourceType: = 0, Source: = _ "OLEDB; Provider =Microsoft.Mashup.OleDb.1;Sumber Data=$Workbook$;Location=" & cell.Value & ";Extended Properties=""""" _ , Destination:=Range("$A$1")). QueryTable .CommandType = xlCmd Sql .CommandText = Array("SELECT *FROM [" & cell.Value & "]") .RowNumbers = Palsu .FillAdjacentFormulas = Palsu .PreserveFormatting = True .RefreshOnFileOpen = Palsu .BackgroundQuery = True .RefreshStyle = xlInsertDeleteCells .SavePasssword SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .PreserveColumnInfo = True .ListObject.DisplayName = cell.Value .Refresh BackgroundQuery:=False End With ActiveSheet.Name = cell.Value Next sel End Sub  

Sawise diluncurake, kita bakal weruh lembaran sing padha miturut kutha, nanging pitakon Power Query sing wis digawe bakal nggawe:

Kanthi owah-owahan ing data sumber, bakal cukup kanggo nganyari tabel sing cocog karo tombol mouse tengen - printah Nganyari & Simpen (Refresh) utawa nganyari kabeh kutha bebarengan ing akeh nggunakake tombol Nganyari Kabeh tab Data (Data - Refresh Kabeh).

• Apa macro, carane nggawe lan nggunakake
• Nyimpen lembar buku kerja minangka file sing kapisah
• Nglumpukake data saka kabeh lembar buku dadi siji tabel