Memisahkan meja menjadi beberapa lembar

Microsoft Excel memiliki banyak alat untuk mengumpulkan data dari beberapa tabel (dari lembar yang berbeda atau dari file yang berbeda): tautan langsung, fungsi TIDAK LANGSUNG (TIDAK LANGSUNG), Power Query dan add-on Power Pivot, dll. Dari sisi barikade ini, semuanya terlihat bagus.

Tetapi jika Anda mengalami masalah terbalik – menyebarkan data dari satu tabel ke lembar yang berbeda – maka semuanya akan jauh lebih menyedihkan. Sayangnya, saat ini, tidak ada alat bawaan yang beradab untuk pemisahan data seperti itu di gudang Excel. Jadi Anda harus menggunakan makro di Visual Basic, atau menggunakan perekam makro + kombinasi Power Query dengan sedikit "penyempurnaan file" setelahnya.

Mari kita lihat lebih dekat bagaimana ini bisa diterapkan.

Rumusan masalah

Kami memiliki sebagai data awal tabel seperti itu dengan ukuran lebih dari 5000 baris untuk penjualan:

Memisahkan meja menjadi beberapa lembar

Tugas: untuk mendistribusikan data dari tabel ini berdasarkan kota pada lembar terpisah dari buku ini. Itu. pada output, Anda hanya perlu mendapatkan di setiap lembar baris-baris dari tabel tempat penjualan berada di kota yang sesuai:

Memisahkan meja menjadi beberapa lembar

Mempersiapkan

Agar tidak memperumit kode makro dan membuatnya semudah mungkin untuk dipahami, mari lakukan beberapa langkah persiapan.

Pertama, buat tabel pencarian terpisah, di mana satu kolom akan mencantumkan semua kota tempat Anda ingin membuat lembar terpisah. Tentu saja, direktori ini mungkin tidak berisi semua kota yang ada dalam data sumber, tetapi hanya kota yang memerlukan laporan. Cara termudah untuk membuat tabel seperti itu adalah dengan menggunakan perintah Data – Hapus Duplikat (Data — Hapus duplikat) untuk salinan kolom Kota atau fungsi UNIK (UNIK) – jika Anda memiliki versi terbaru Excel 365.

Karena lembar baru di Excel dibuat secara default sebelum (di sebelah kiri) lembar saat ini (sebelumnya), masuk akal juga untuk mengurutkan kota di direktori ini dalam urutan menurun (dari Z ke A) – lalu setelah pembuatan, kota lembar akan disusun menurut abjad.

Kedua, пubah kedua tabel menjadi dinamis (“pintar”) untuk mempermudah bekerja dengan mereka. Kami menggunakan perintah Beranda – Format sebagai tabel (Beranda — Format sebagai Tabel) atau pintasan keyboard Ctrl+T. Pada tab yang muncul Pembina (Rancangan) sebut saja mereka tablProdaji и Kota Mejamasing-masing:

Memisahkan meja menjadi beberapa lembar

Metode 1. Makro untuk pembagian dengan lembar

Pada tab Lanjutan pembangun (Pengembang) klik tombolnya Visual Basic atau gunakan pintasan keyboard lain+F11. Di jendela editor makro yang terbuka, masukkan modul kosong baru melalui menu Sisipkan – Modul dan salin kode berikut di sana:

Sub Splitter() Untuk Setiap sel Dalam Range("таблГорода") Range("таблПродажи").Bidang FilterOtomatis:=3, Kriteria1:=cell.Value Range("таблПродажи[#All]").SpecialCells(xlCellTypeVisible).Copy Sheets.Add ActiveSheet.Paste ActiveSheet.Name = cell.Value ActiveSheet.UsedRange.Columns.AutoFit Next cell Worksheets("Данные").ShowAllData End Sub

Di sini dengan lingkaran Untuk Setiap ... Selanjutnya mengimplementasikan bagian melalui sel-sel direktori Kota Meja, di mana untuk setiap kota disaring (metode Filter Otomatis) di tabel penjualan asli dan kemudian menyalin hasilnya ke lembar yang baru dibuat. Sepanjang jalan, lembar yang dibuat diubah namanya menjadi nama kota yang sama dan secara otomatis memasang lebar kolom untuk kecantikan diaktifkan di atasnya.

Anda dapat menjalankan makro yang dibuat di Excel pada tab pembangun tombol Macro (Pengembang — Makro) atau pintasan keyboard lain+F8.

Metode 2. Buat beberapa kueri di Power Query

Metode sebelumnya, untuk semua kekompakan dan kesederhanaannya, memiliki kelemahan yang signifikan - lembar yang dibuat oleh makro tidak diperbarui ketika perubahan dibuat dalam tabel penjualan asli. Jika memperbarui dengan cepat diperlukan, maka Anda harus menggunakan bundel VBA + Power Query, atau lebih tepatnya, membuat menggunakan makro tidak hanya lembar dengan data statis, tetapi kueri Power Query yang diperbarui.

Makro dalam hal ini sebagian mirip dengan yang sebelumnya (juga memiliki siklus Untuk Setiap ... Selanjutnya untuk mengulangi kota di direktori), tetapi di dalam loop tidak akan ada lagi pemfilteran dan penyalinan, tetapi membuat kueri Power Query dan mengunggah hasilnya ke lembar baru:

Sub Splitter2() Untuk Setiap sel Dalam Range("City table") ActiveWorkbook.Queries.Add Name:=cell.Value, Formula:= _ "let" & Chr(13) & "" & Chr(10) & " Source = Excel.CurrentWorkbook(){[Name=""TableSales""]}[Konten]," & Chr(13) & "" & Chr(10) & " #""Jenis yang Diubah"" = Tabel.TransformColumnTypes(Sumber , {{""Kategori"", ketik teks}, {""Nama"", ketik teks}, {""Kota"", ketik teks}, {""Manager"", ketik teks}, {""Deal tanggal "", ketik datetime}, {""Biaya"", ketik nomor}})," & Chr(13) & "" & Chr(10) & " #""Baris dengan filter diterapkan"" = Tabel.Se " & _ "lectRows(#""Jenis yang diubah"", masing-masing ([Kota] = """ & sel.Nilai & """))" & Chr(13) & "" & Chr(10) & "in " & Chr(13) & "" & Chr(10) & " #""Baris dengan filter diterapkan""" ActiveWorkbook.Worksheets.Add Dengan ActiveSheet.ListObjects.Add(SourceType:=0, Sumber:= _ "OLEDB; Penyedia =Microsoft.Mashup.OleDb.1;Sumber Data=$Workbook$;Location=" & cell.Value & ";Extended Properties=""""" _ , Tujuan:=Range("$A$1")). QueryTable .CommandType = xlCmd Sql .CommandText = Array("SELECT *FROM [" & cell.Value & "]") .RowNumbers = Salah .FillAdjacentFormulas = Salah .PreserveFormatting = Benar .RefreshOnFileOpen = Salah .BackgroundQuery = Benar .RefreshStyle = xlInsertDeleteCells .SavePassword = Salah . 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

Setelah diluncurkan, kita akan melihat lembar yang sama berdasarkan kota, tetapi kueri Power Query yang sudah dibuat akan membentuknya:

Memisahkan meja menjadi beberapa lembar

Dengan perubahan apa pun pada data sumber, itu akan cukup untuk memperbarui tabel yang sesuai dengan tombol kanan mouse – perintah Perbarui & Simpan (Menyegarkan) atau perbarui semua kota sekaligus secara massal menggunakan tombol Perbarui Semua tab Data (Data — Segarkan Semua).

  • Apa itu makro, cara membuat dan menggunakannya
  • Menyimpan lembar buku kerja sebagai file terpisah
  • Mengumpulkan data dari semua lembar buku menjadi satu tabel

Tinggalkan Balasan