Membahagikan meja menjadi helaian

Microsoft Excel mempunyai banyak alat untuk mengumpul data daripada beberapa jadual (dari helaian berbeza atau daripada fail berbeza): pautan terus, fungsi TIDAK LANGSUNG (LANGSUNG), Power Query dan alat tambah Power Pivot, dsb. Dari bahagian penghadang ini, semuanya kelihatan baik.

Tetapi jika anda menghadapi masalah songsang - menyebarkan data dari satu jadual ke helaian yang berbeza - maka semuanya akan menjadi lebih menyedihkan. Pada masa ini, tidak ada alat terbina dalam bertamadun untuk pemisahan data sedemikian dalam senjata Excel, malangnya. Oleh itu, anda perlu menggunakan makro dalam Visual Basic, atau gunakan perakam makro + gabungan Power Query dengan sedikit "pemurnian fail" selepas itu.

Mari kita lihat lebih dekat bagaimana ini boleh dilaksanakan.

Perumusan masalah

Kami mempunyai data awal seperti jadual dengan saiz lebih daripada 5000 baris untuk jualan:

Tugas: untuk mengedarkan data daripada jadual ini mengikut bandar pada helaian berasingan buku ini. Itu. pada output, anda perlu mendapatkan pada setiap helaian hanya baris-baris tersebut daripada jadual tempat jualan itu berada di bandar yang sepadan:

Sediakan

Untuk tidak merumitkan kod makro dan menjadikannya semudah mungkin untuk difahami, mari kita lakukan beberapa langkah persediaan.

Pertama, buat jadual carian berasingan, di mana satu lajur akan menyenaraikan semua bandar yang anda ingin buat helaian berasingan. Sudah tentu, direktori ini mungkin tidak mengandungi semua bandar yang terdapat dalam data sumber, tetapi hanya bandar yang kami perlukan laporan. Cara paling mudah untuk membuat jadual sedemikian adalah dengan menggunakan arahan Data – Alih Keluar Pendua (Data — Alih keluar pendua) untuk salinan lajur City atau fungsi UNIK (UNIK) – jika anda mempunyai versi terkini Excel 365.

Memandangkan helaian baharu dalam Excel dicipta secara lalai sebelum (di sebelah kiri) helaian semasa (sebelumnya), ia juga masuk akal untuk mengisih bandar dalam direktori ini dalam tertib menurun (dari Z ke A) – kemudian selepas penciptaan, bandar helaian akan disusun mengikut abjad.

Kedua, пmenukar kedua-dua jadual kepada dinamik (“pintar”) untuk memudahkan anda bekerja dengan mereka. Kami menggunakan arahan Laman Utama – Format sebagai jadual (Laman Utama — Format sebagai Jadual) atau pintasan papan kekunci Ctrl+T. Pada tab yang muncul Pembina (Reka Bentuk) mari kita panggil mereka tablProdaji и TableCity, masing-masing:

Kaedah 1. Makro untuk pembahagian mengikut helaian

Pada tab Lanjutan pemaju (Pembangun) klik pada butang Visual Basic atau gunakan pintasan papan kekunci Alt+F11. Dalam tetingkap editor makro yang terbuka, masukkan modul kosong baharu melalui menu Sisipan – Modul dan salin kod berikut di sana:

Sub Splitter() Untuk Setiap sel Dalam Julat("таблГорода") Julat("таблПродажи"). Medan AutoFilter:=3, Kriteria1:=sel.Julat Nilai("таблПродажи[#Semua]").SpecialCells(xlCellTypeVisible). Helaian.Tambah ActiveSheet.Tampal ActiveSheet.Name = cell.Value ActiveSheet.UsedRange.Columns.AutoFit sel Seterusnya Lembaran Kerja("Данные").ShowAllData End Sub	  

Di sini dengan gelung Untuk Setiap … Seterusnya melaksanakan laluan melalui sel direktori TableCity, di mana untuk setiap bandar ia ditapis (kaedah Penapis Auto) dalam jadual jualan asal dan kemudian menyalin hasilnya ke helaian yang baru dibuat. Sepanjang perjalanan, helaian yang dibuat dinamakan semula kepada nama bandar yang sama dan pelengkapan automatik lebar lajur untuk kecantikan dihidupkan padanya.

Anda boleh menjalankan makro yang dibuat dalam Excel pada tab pemaju butang Makro (Pembangun — Makro) atau pintasan papan kekunci Alt+F8.

Kaedah 2. Buat berbilang pertanyaan dalam Power Query

Kaedah sebelumnya, untuk semua kekompakan dan kesederhanaannya, mempunyai kelemahan yang ketara – helaian yang dibuat oleh makro tidak dikemas kini apabila perubahan dibuat dalam jadual jualan asal. Jika mengemas kini dengan cepat adalah perlu, maka anda perlu menggunakan himpunan VBA + Power Query, atau lebih tepat, buat menggunakan makro bukan sahaja helaian dengan data statik, tetapi pertanyaan Power Query yang dikemas kini.

Makro dalam kes ini sebahagiannya serupa dengan yang sebelumnya (ia juga mempunyai kitaran Untuk Setiap … Seterusnya untuk mengulangi bandar dalam direktori), tetapi di dalam gelung tidak akan ada lagi penapisan dan penyalinan, tetapi mencipta pertanyaan Power Query dan memuat naik hasilnya ke helaian baharu:

Sub Splitter2() Untuk Setiap sel Dalam Julat("Jadual bandar") ActiveWorkbook.Queries.Add Name:=cell.Value, Formula:= _ "let" & Chr(13) & "" & Chr(10) & " Sumber = Excel.CurrentWorkbook(){[Name=""TableSales""]}[Kandungan]," & Chr(13) & "" & Chr(10) & " #""Changed Type"" = Table.TransformColumnTypes(Sumber , {{""Category"", type text}, {""Name"", type text}, {""City"", type text}, {""Manager"", type text}, {""Deal tarikh "", taip datetime}, {""Kos"", taip nombor}})," & Chr(13) & "" & Chr(10) & " #""Baris dengan penapis digunakan"" = Table.Se " & _ "lectRows(#""Jenis ditukar"", setiap satu ([City] = """ & sel.Nilai & """))" & Chr(13) & "" & Chr(10) & "in " & Chr(13) & "" & Chr(10) & " #""Baris dengan penapis digunakan""" ActiveWorkbook.Worksheets.Add With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _ "OLEDB; Pembekal =Microsoft.Mashup.OleDb.1;Sumber Data=$Workbook$;Location=" & cell.Value & ";Extended Properties=""""" _ , Destination:=Julat("$A$1")). QueryTable .CommandType = xlCmd Sql .CommandText = Array("SELECT *DARI [" & sel.Nilai & "]") .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 cell End Sub  

Selepas pelancarannya, kami akan melihat helaian yang sama mengikut bandar, tetapi pertanyaan Power Query yang telah dibuat akan membentuknya:

Dengan sebarang perubahan dalam data sumber, ia akan mencukupi untuk mengemas kini jadual yang sepadan dengan butang kanan tetikus - arahan Kemas kini & Simpan (Segarkan semula) atau kemas kini semua bandar sekali gus secara pukal menggunakan butang Kemaskini semua tab Tarikh (Data — Muat semula Semua).

• Apakah makro, cara mencipta dan menggunakannya
• Menyimpan helaian buku kerja sebagai fail berasingan
• Mengumpul data daripada semua helaian buku ke dalam satu jadual