Power query membagi data menjadi beberapa lembar

Question

Power Query bukanlah alat yang tepat untuk ini karena tidak menghasilkan file Excel yang berbeda. Anda harus melakukan pekerjaan manual dan menyimpan semua file satu per satu setelah menerapkan berbagai filter di PQ

Table of Contents Show

Alat Produktivitas Kantor Terbaik
Bisakah power query membagi data menjadi beberapa lembar?
Bagaimana cara membagi data menjadi beberapa lembar kerja?
Bagaimana cara menggabungkan data dari beberapa lembar kerja menggunakan Power Query?

Alat yang tepat untuk ini adalah Excel itu sendiri jika Anda menginginkan versi otomatis.

Saya telah menulis makro VBA di mana Anda perlu memilih kolom dan itu akan menghasilkan banyak file yang sesuai dengan entri yang berbeda. Karenanya, jika Anda menjalankan makro setelah memilih kolom Subjek atau hanya memilih sel mana saja di kolom Subjek, Anda akan mendapatkan dua file

Pasal 48 – Membagi Buku Kerja menjadi Beberapa Buku Kerja berdasarkan Kolom

Jika Anda melakukan beberapa transformasi di PQ, lakukan dan simpan hasilnya di Excel. Kemudian jalankan makro di atas

Misalkan Anda memiliki lembar kerja dengan deretan data yang sangat besar, dan sekarang, Anda perlu membagi data menjadi beberapa lembar kerja berdasarkan kolom Nama (lihat tangkapan layar berikut), dan nama-nama tersebut dimasukkan secara acak. Mungkin Anda bisa mengurutkannya terlebih dahulu, lalu menyalin dan menempelkannya satu per satu ke lembar kerja baru lainnya. Tetapi ini membutuhkan kesabaran Anda untuk menyalin dan menempel berulang kali. Hari ini, saya akan berbicara tentang beberapa trik cepat untuk menyelesaikan tugas ini

Pisahkan data menjadi beberapa lembar kerja berdasarkan kolom dengan kode VBA

Jika Anda ingin membagi data berdasarkan nilai kolom dengan cepat dan otomatis, kode VBA berikut adalah pilihan yang tepat. Tolong lakukan seperti ini

1. Tahan tombol ALT + F11 untuk membuka jendela Microsoft Visual Basic for Applications

2. Klik Sisipkan > Modul, dan tempelkan kode berikut di Jendela Modul

Sub Splitdatabycol()
'updateby Extendoffice
Dim lr As Long
Dim ws As Worksheet
Dim vcol, i As Integer
Dim icol As Long
Dim myarr As Variant
Dim title As String
Dim titlerow As Integer
Dim xTRg As Range
Dim xVRg As Range
Dim xWSTRg As Worksheet
Dim xWS As Worksheet
On Error Resume Next
Set xTRg = Application.InputBox("Please select the header rows:", "Kutools for Excel", "", Type:=8)
If TypeName(xTRg) = "Nothing" Then Exit Sub
Set xVRg = Application.InputBox("Please select the column you want to split data based on:", "Kutools for Excel", "", Type:=8)
If TypeName(xVRg) = "Nothing" Then Exit Sub
vcol = xVRg.Column
Set ws = xTRg.Worksheet
lr = ws.Cells(ws.Rows.Count, vcol).End(xlUp).Row
title = xTRg.AddressLocal
titlerow = xTRg.Cells(1).Row
icol = ws.Columns.Count
ws.Cells(1, icol) = "Unique"
Application.DisplayAlerts = False
If Not Evaluate("=ISREF('xTRgWs_Sheet!A1')") Then
Sheets.Add(after:=Worksheets(Worksheets.Count)).Name = "xTRgWs_Sheet"
Else
Sheets("xTRgWs_Sheet").Delete
Sheets.Add(after:=Worksheets(Worksheets.Count)).Name = "xTRgWs_Sheet"
End If
Set xWSTRg = Sheets("xTRgWs_Sheet")
xTRg.Copy
xWSTRg.Paste Destination:=xWSTRg.Range("A1")
ws.Activate
For i = (titlerow + xTRg.Rows.Count) To lr
On Error Resume Next
If ws.Cells(i, vcol) <> "" And Application.WorksheetFunction.Match(ws.Cells(i, vcol), ws.Columns(icol), 0) = 0 Then
ws.Cells(ws.Rows.Count, icol).End(xlUp).Offset(1) = ws.Cells(i, vcol)
End If
Next
myarr = Application.WorksheetFunction.Transpose(ws.Columns(icol).SpecialCells(xlCellTypeConstants))
ws.Columns(icol).Clear
For i = 2 To UBound(myarr)
ws.Range(title).AutoFilter field:=vcol, Criteria1:=myarr(i) & ""
If Not Evaluate("=ISREF('" & myarr(i) & "'!A1)") Then
Set xWS = Sheets.Add(after:=Worksheets(Worksheets.Count))
xWS.Name = myarr(i) & ""
Else
xWS.Move after:=Worksheets(Worksheets.Count)
End If
xWSTRg.Range(title).Copy
xWS.Paste Destination:=xWS.Range("A1")
ws.Range("A" & (titlerow + xTRg.Rows.Count) & ":A" & lr).EntireRow.Copy xWS.Range("A" & (titlerow + xTRg.Rows.Count))
Sheets(myarr(i) & "").Columns.AutoFit
Next
xWSTRg.Delete
ws.AutoFilterMode = False
ws.Activate
Application.DisplayAlerts = True
End Sub

3. Kemudian, tekan tombol F5 untuk menjalankan kode, dan kotak perintah akan muncul untuk mengingatkan Anda memilih baris tajuk, lihat tangkapan layar

4. Dan kemudian, klik tombol OK, dan di kotak prompt kedua, pilih data kolom yang ingin Anda bagi berdasarkan, lihat tangkapan layar

5. Kemudian, klik OK, dan semua data di lembar kerja aktif dibagi menjadi beberapa lembar kerja berdasarkan nilai kolom. Dan lembar kerja terpisah diberi nama dengan nama sel terpisah. Lihat tangkapan layar

Catatan. Lembar kerja terpisah ditempatkan di akhir buku kerja tempat lembar kerja utama berada

Pisahkan data menjadi beberapa lembar kerja berdasarkan kolom dengan Kutools for Excel

Sebagai pemula Excel, kode VBA yang panjang ini agak sulit bagi kita, dan kebanyakan dari kita bahkan tidak tahu cara memodifikasi kode sesuai kebutuhan kita. Di sini, saya akan memperkenalkan Anda alat multifungsi--Kutools for Excel, itu Membagi Data utilitas tidak hanya dapat membantu Anda membagi data menjadi beberapa lembar kerja berdasarkan kolom, tetapi juga dapat membagi data dengan jumlah baris

Catatan. Untuk menerapkan Data Terpisah ini, pertama-tama Anda harus mengunduh Kutools for Excel, lalu terapkan fitur tersebut dengan cepat dan mudah.

Setelah menginstal Kutools for Excel, silakan lakukan seperti ini

1. Pilih rentang data yang ingin Anda bagi

2. Klik Kutools Plus > Lembar kerja > Pisahkan Data, lihat tangkapan layar

3. Di kotak dialog Split Data into Multiple Worksheets, Anda perlu melakukannya

1). Pilih opsi kolom Spesifik di bagian Pisahkan berdasarkan, dan pilih nilai kolom yang ingin Anda pisahkan berdasarkan data di daftar drop-down. (Jika data Anda memiliki header dan Anda ingin memasukkannya ke dalam setiap lembar kerja terpisah yang baru, harap centang opsi Data saya memiliki header. )

2). Kemudian Anda dapat menentukan nama lembar kerja terpisah, di bawah bagian Nama lembar kerja baru, tentukan aturan nama lembar kerja dari daftar tarik-turun Aturan, Anda juga dapat menambahkan Awalan atau Akhiran untuk nama lembar kerja

3). Klik tombol OK. Lihat tangkapan layar

4. Sekarang data dibagi menjadi beberapa lembar kerja di buku kerja baru

Klik untuk Mengunduh Kutools untuk Excel dan uji coba gratis Sekarang

Pisahkan data menjadi beberapa lembar kerja berdasarkan kolom dengan Kutools for Excel

Kutools for Excel mencakup lebih dari 300 alat Excel yang praktis. Gratis untuk dicoba tanpa batasan dalam 30 hari. Unduh uji coba gratis sekarang

Alat Produktivitas Kantor Terbaik

Kutools for Excel Memecahkan Sebagian Besar Masalah Anda, dan Meningkatkan Produktivitas Anda hingga 80%

Gunakan kembali. Masukkan dengan cepat rumus kompleks, bagan dan apa pun yang pernah Anda gunakan sebelumnya; . Encrypt Cells with password; Create Mailing List and send emails...
Super Formula Bar (mengedit beberapa baris teks dan formula dengan mudah); . Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
Menggabungkan Sel/Baris/Kolom tanpa kehilangan Data; . Cegah Sel Duplikat; . Combine Duplicate Rows/Columns.. Prevent Duplicate Cells; Compare Ranges...
Pilih Baris Duplikat atau Unik ; . Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
Exact Copy Beberapa Sel tanpa mengubah referensi rumus; . Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
Ekstrak Teks , Tambah Teks, Hapus berdasarkan Posisi, Hapus Spasi ; . Convert Between Cells Content and Comments...
Filter Super (simpan dan terapkan skema filter ke sheet lain); . Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
Menggabungkan Buku Kerja dan Lembar Kerja ; . Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
Lebih dari 300 fitur canggih . Mendukung Office / Excel 2007-2021 dan 365. Mendukung semua bahasa. Penerapan yang mudah di perusahaan atau organisasi Anda. Fitur lengkap Uji coba gratis 30 hari. Jaminan uang kembali 60 hari.

Baca selengkapnya. Download Gratis. Pembelian.

Office Tab Menghadirkan antarmuka Tab ke Office, dan Membuat Pekerjaan Anda Lebih Mudah

Aktifkan pengeditan dan pembacaan bertab di Word, Excel, PowerPoint , Publisher, Access, Visio, dan Project.
Buka dan buat banyak dokumen di tab baru di jendela yang sama, bukan di jendela baru
Meningkatkan produktivitas Anda hingga 50%, dan mengurangi ratusan klik mouse untuk Anda setiap hari

Baca selengkapnya. Download Gratis. Pembelian.

Terlama Pertama

Urutkan komentar menurut

Terlama Pertama

Pertama Terbaru

Komentar ( 304 )

Belum ada peringkat. Jadilah yang pertama menilai

Jagadesh. K

sekitar 9 tahun yang lalu