Loncat ke daftar isi utama

Tips Excel: Membagi data menjadi beberapa lembar kerja/buku kerja berdasarkan nilai kolom

Pengarang: Xiaoyang Terakhir Dimodifikasi: 2024-04-26

Saat mengelola kumpulan data besar di Excel, akan sangat bermanfaat jika membagi data menjadi beberapa lembar kerja berdasarkan nilai kolom tertentu. Metode ini tidak hanya meningkatkan pengorganisasian data tetapi juga meningkatkan keterbacaan dan memfasilitasi analisis data yang lebih mudah.

Misalkan Anda memiliki catatan penjualan besar yang berisi beberapa entri seperti nama produk, jumlah penjualan pada kuartal pertama. Tujuannya adalah untuk membagi data ini ke dalam lembar kerja terpisah berdasarkan setiap nama produk sehingga kinerja penjualan individual dapat dianalisis secara terpisah.

Pisahkan data menjadi beberapa lembar kerja berdasarkan nilai kolom

Pisahkan data menjadi beberapa buku kerja berdasarkan nilai kolom dengan kode VBA

Pisahkan data menjadi beberapa lembar kerja berdasarkan nilai kolom

Biasanya, Anda bisa mengurutkan daftar data terlebih dahulu, lalu menyalin dan menempelkannya satu per satu ke lembar kerja baru lainnya. Namun ini membutuhkan kesabaran Anda untuk menyalin dan menempel berulang kali. Di bagian ini, kami akan memperkenalkan dua metode langsung untuk mengatasi tugas ini di Excel secara efisien, menghemat waktu Anda dan mengurangi potensi kesalahan.

Pisahkan data menjadi beberapa lembar kerja berdasarkan nilai kolom dengan kode VBA

1. Tahan ALT + F11 kunci untuk membuka Microsoft Visual Basic untuk Aplikasi jendela.

2. klik Menyisipkan > Modul, dan tempel kode berikut di Module Window.

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 F5 kunci untuk menjalankan kode, dan kotak prompt muncul untuk mengingatkan Anda memilih baris header, lalu klik OK. Lihat tangkapan layar:

4. Pada kotak prompt kedua, silakan pilih data kolom yang ingin Anda pisahkan berdasarkan, lalu klik OK. Lihat tangkapan layar:

5. Semua data pada lembar kerja aktif dibagi menjadi beberapa lembar kerja berdasarkan nilai kolomnya. Lembar kerja yang dihasilkan diberi nama sesuai dengan nilai dalam sel yang dipisahkan dan ditempatkan di akhir buku kerja. Lihat tangkapan layar:

 

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

Kutools untuk Excel menghadirkan fitur pintar – Data split langsung ke lingkungan Excel Anda. Memisahkan data menjadi beberapa lembar kerja bukan lagi sebuah tantangan. Alat intuitif kami secara otomatis membagi kumpulan data Anda berdasarkan nilai kolom atau jumlah baris yang dipilih, memastikan bahwa setiap informasi berada tepat di tempat yang Anda perlukan. Ucapkan selamat tinggal pada tugas membosankan dalam mengatur spreadsheet Anda secara manual dan gunakan cara yang lebih cepat dan bebas kesalahan untuk mengelola data Anda.

Note: Untuk menerapkan ini Data split, pertama, Anda harus mengunduh Kutools untuk Excel, lalu terapkan fitur dengan cepat dan mudah.

Setelah menginstal Kutools untuk Excel, pilih rentang data, lalu klik Kutools Ditambah > Data split untuk membuka Pisahkan Data menjadi beberapa lembar kerja kotak dialog.

  1. Pilih Kolom tertentu pilihan dalam Pisahkan berdasarkan bagian, dan pilih nilai kolom yang ingin Anda pisahkan datanya dari daftar drop-down.
  2. Jika data Anda memiliki header dan Anda ingin memasukkannya ke dalam setiap lembar kerja terpisah yang baru, silakan centang Data saya memiliki tajuk pilihan. (Anda dapat menentukan jumlah baris header berdasarkan data Anda. Misalnya, jika data Anda berisi dua header, silakan ketik 2.)
  3. Kemudian Anda dapat menentukan nama lembar kerja terpisah, di bawah Nama lembar kerja baru bagian, tentukan aturan nama lembar kerja dari daftar drop-down Aturan, Anda dapat menambahkan Awalan or Akhiran untuk nama sheet juga.
  4. klik OK tombol. Lihat tangkapan layar:

Sekarang, data di lembar kerja dipecah menjadi beberapa lembar kerja di buku kerja baru.

Pisahkan data menjadi beberapa buku kerja berdasarkan nilai kolom dengan kode VBA

Terkadang, daripada membagi data menjadi beberapa lembar kerja, mungkin lebih bermanfaat jika membagi data menjadi buku kerja terpisah berdasarkan kolom kunci. Berikut panduan langkah demi langkah tentang cara menggunakan kode VBA untuk mengotomatiskan proses pemisahan data menjadi beberapa buku kerja berdasarkan nilai kolom tertentu.

1. Tahan ALT + F11 kunci untuk membuka Microsoft Visual Basic untuk Aplikasi jendela.

2. klik Menyisipkan > Modul, dan tempel kode berikut di Jendela Modul.

Sub SplitDataByColToWorkbooks()
    ' Updateby Extendoffice
    Dim lr As Long
    Dim ws As Worksheet
    Dim vcol, i As Integer
    Dim myarr As Variant
    Dim title As String
    Dim titlerow As Integer
    Dim xTRg As Range
    Dim xVRg As Range
    Dim xWS As Workbook
    Dim savePath As String
    ' Set the directory to save new workbooks
    savePath = "C:\Users\AddinsVM001\Desktop\multiple files\" ' Modify this path as needed
    Application.DisplayAlerts = False
    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.Address(False, False)
    titlerow = xTRg.Row
    ws.Columns(vcol).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=ws.Cells(1, ws.Columns.Count), Unique:=True
    myarr = Application.Transpose(ws.Cells(1, ws.Columns.Count).Resize(ws.Cells(ws.Rows.Count, ws.Columns.Count).End(xlUp).Row).Value)
    ws.Cells(1, ws.Columns.Count).Resize(ws.Cells(ws.Rows.Count, ws.Columns.Count).End(xlUp).Row).ClearContents
    For i = 2 To UBound(myarr)
        Set xWS = Workbooks.Add
        ws.Range(title).AutoFilter Field:=vcol, Criteria1:=myarr(i)
        ws.Range("A" & titlerow & ":A" & lr).SpecialCells(xlCellTypeVisible).EntireRow.Copy
        xWS.Sheets(1).Cells(1, 1).PasteSpecial Paste:=xlPasteAll
        xWS.SaveAs Filename:=savePath & myarr(i) & ".xlsx"

        xWS.Close SaveChanges:=False
    Next i
    ws.AutoFilterMode = False
    Application.DisplayAlerts = True
    ws.Activate
End Sub
Note: Dalam kode di atas, Anda harus mengubah jalur file ke jalur Anda sendiri di mana buku kerja terpisah akan disimpan dalam skrip ini: savePath = "C:\Users\AddinsVM001\Desktop\beberapa file\".

3. Kemudian, tekan F5 kunci untuk menjalankan kode, dan kotak prompt muncul untuk mengingatkan Anda memilih baris header, lalu klik OK. Lihat tangkapan layar:

4. Pada kotak prompt kedua, silakan pilih data kolom yang ingin Anda pisahkan berdasarkan, lalu klik OK. Lihat tangkapan layar:

5. Setelah dipecah, semua data di lembar kerja aktif dibagi menjadi beberapa buku kerja berdasarkan nilai kolomnya. Semua buku kerja terpisah disimpan ke dalam folder yang Anda tentukan. Lihat tangkapan layar:

Artikel Terkait:

  • Pisahkan data menjadi beberapa lembar kerja dengan jumlah baris
  • Membagi rentang data yang besar secara efisien menjadi beberapa lembar kerja Excel berdasarkan jumlah baris tertentu dapat menyederhanakan pengelolaan data. Misalnya, membagi kumpulan data setiap 5 baris menjadi beberapa lembar dapat membuatnya lebih mudah dikelola dan terorganisir. Panduan ini menawarkan dua metode praktis untuk menyelesaikan tugas ini dengan cepat dan mudah.
  • Gabungkan dua tabel atau lebih menjadi satu berdasarkan kolom kunci
  • Misalkan Anda memiliki tiga tabel di workbook, sekarang, Anda ingin menggabungkan tabel ini menjadi satu tabel berdasarkan kolom kunci yang sesuai untuk mendapatkan hasil seperti gambar di bawah ini. Ini mungkin tugas yang merepotkan bagi kebanyakan dari kita, tetapi, jangan khawatir, artikel ini, saya akan memperkenalkan beberapa metode untuk menyelesaikan masalah ini.
  • Pisahkan String Teks dengan Pembatas menjadi Beberapa Baris
  • Biasanya, Anda dapat menggunakan fitur Teks ke Kolom untuk membagi konten sel menjadi beberapa kolom dengan pembatas tertentu, seperti koma, titik, titik koma, garis miring, dll. Namun, terkadang, Anda mungkin perlu membagi konten sel yang dibatasi menjadi beberapa baris dan ulangi data dari kolom lain seperti gambar di bawah ini. Apakah Anda memiliki cara yang baik untuk menangani tugas ini di Excel? Tutorial ini akan memperkenalkan beberapa metode efektif untuk menyelesaikan pekerjaan ini di Excel.
  • Pisahkan konten sel multibaris menjadi baris/kolom terpisah
  • Misalkan Anda memiliki konten sel multiline yang dipisahkan oleh Alt + Enter, dan sekarang Anda perlu membagi konten multiline menjadi baris atau kolom yang terpisah, apa yang dapat Anda lakukan? Dalam artikel ini, Anda akan mempelajari cara cepat membagi konten sel multibaris menjadi baris atau kolom yang terpisah.

Alat Produktivitas Kantor Terbaik

🤖 Kutools AI Ajudan: Merevolusi analisis data berdasarkan: Eksekusi Cerdas   |  Hasilkan Kode  |  Buat Rumus Khusus  |  Analisis Data dan Hasilkan Grafik  |  Aktifkan Fungsi Kutools...
Fitur Populer: Temukan, Sorot, atau Identifikasi Duplikat   |  Hapus Baris Kosong   |  Gabungkan Kolom atau Sel tanpa Kehilangan Data   |   Putaran tanpa Formula ...
Pencarian Super: VLookup Beberapa Kriteria  |   VLookup Nilai Berganda  |   VLookup di Beberapa Lembar   |   Pencarian Fuzzy ....
Daftar Drop-down Lanjutan: Buat Daftar Drop Down dengan Cepat   |  Daftar Drop Down yang Bergantung   |  Multi-pilih Drop Down List ....
Manajer Kolom: Tambahkan Jumlah Kolom Tertentu  |  Pindahkan Kolom  |  Alihkan Status Visibilitas Kolom Tersembunyi  |  Bandingkan Rentang & Kolom ...
Fitur Unggulan: Fokus Kisi   |  Tampilan Desain   |   Bar Formula Besar   |  Manajer Buku Kerja & Lembar   |  Perpustakaan Sumberdaya (Teks otomatis)   |  Pemetik tanggal   |  Gabungkan Lembar Kerja   |  Enkripsi/Dekripsi Sel   |  Kirim Email berdasarkan Daftar   |  Filter Super   |   Filter Khusus (filter tebal/miring/coret...) ...
15 Perangkat Teratas:  12 Teks Tools (Tambahkan Teks, Hapus Karakter, ...)   |   50 + Grafik jenis (Gantt Chart, ...)   |   40+ Praktis Rumus (Hitung usia berdasarkan ulang tahun, ...)   |   19 Insersi Tools (Masukkan Kode QR, Sisipkan Gambar dari Jalur, ...)   |   12 Konversi Tools (Angka ke Kata, Konversi Mata Uang, ...)   |   7 Gabungkan & Pisahkan Tools (Lanjutan Gabungkan Baris, Pisahkan Sel, ...)   |   ... dan banyak lagi

Tingkatkan Keterampilan Excel Anda dengan Kutools for Excel, dan Rasakan Efisiensi yang Belum Pernah Ada Sebelumnya. Kutools for Excel Menawarkan Lebih dari 300 Fitur Lanjutan untuk Meningkatkan Produktivitas dan Menghemat Waktu.  Klik Di Sini untuk Mendapatkan Fitur yang Paling Anda Butuhkan...

Deskripsi Produk

Tab Office Membawa antarmuka Tab ke Office, dan Membuat Pekerjaan Anda Jauh Lebih Mudah

  • Aktifkan pengeditan dan pembacaan tab 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 sebesar 50%, dan mengurangi ratusan klik mouse untuk Anda setiap hari!
Comments (312)
Rated 5 out of 5 · 2 ratings
How to split data into multiple workbook based on column &amp; Row In Excel?
How to split data into multiple workbook based on column & Row In Excel?
This comment was minimized by the moderator on the site
Sub SplitDataByColWorkbook()
Dim lr As Long
Dim ws As Worksheet
Dim vcol 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 Workbook
Dim wb As Workbook


Set wb = ThisWorkbook
Set ws = wb.Sheets(1) ' Assuming you want to work with the first sheet in the workbook

On Error Resume Next
Set xTRg = Application.InputBox("Please select the header rows:", "Select Header Rows", Type:=8)
If xTRg Is Nothing Then Exit Sub

On Error Resume Next
Set xVRg = Application.InputBox("Please select the column you want to split data based on:", "Select Split Column", Type:=8)
If xVRg Is Nothing Then Exit Sub

vcol = xVRg.Column
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
Set xWS = Workbooks.Add
Else
Set xWS = Workbooks.Add
End If

Set xWSTRg = xWS.Sheets(1)
xTRg.Copy
xWSTRg.Range("A1").PasteSpecial Paste:=xlPasteValues
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) & ""
Set xWS = Workbooks.Add
Set xWSTRg = xWS.Sheets(1)
xTRg.Copy
xWSTRg.Range("A1").PasteSpecial Paste:=xlPasteValues
ws.Range("A" & (titlerow + xTRg.Rows.Count) & ":A" & lr).EntireRow.Copy xWSTRg.Range("A" & (titlerow + xTRg.Rows.Count))
xWSTRg.Columns.AutoFit
xWS.SaveAs myarr(i) & ".xlsx" ' Change the file name as needed
xWS.Close SaveChanges:=False
Next

ws.AutoFilterMode = False
wb.Activate
Application.DisplayAlerts = True
End Sub
Reply
0
0
Eiitz
Eiitz
This comment was minimized by the moderator on the site
First of all, thank you for the macro.

I would like to ask if there is any way to maintain the column widths. My 'original' tab was completely formatted. However, after running the macro, it loses the column formatting and appears quite messy.

English is not my first language (sorry).

Thank you again!
Reply
0
0
Rated 5 out of 5
Sudip
Sudip
This comment was minimized by the moderator on the site
The original header is not copied in the split sheet.
Reply
0
0
Steven Edwards
Steven Edwards
This comment was minimized by the moderator on the site
This works wonderfully, thank you very much!!! Huge time-saver.
Reply
0
0
Rated 5 out of 5
Adonis A.
Adonis A.
This comment was minimized by the moderator on the site
Hello,

I am having a hard time getting this code to work. When I run it, it just creates a duplicate sheet and does not split columns into multiple sheets.

I do have values that exceed 31 characters as well as special characters such as "-" and "()" in my column, how can I account for that without a lot of manual changes?
Reply
0
0
Emily
Emily
This comment was minimized by the moderator on the site
This worked great!!! One question... my formulas didn't transfer to each sheet correctly. What do I need to do differently to transfer the formulas?
Thank you!!!!!
Reply
0
0
Guest
Guest
This comment was minimized by the moderator on the site
Nice code, but it just copied everything to the new tables, named correctly though. So, the data filtering did not work at all, just copy paste.
Reply
0
0
T1bb$
T1bb$
This comment was minimized by the moderator on the site
When I run this using a small amount of data like the example it works. I'm trying to use this on a database with 400k + rows of data. When I run the macro, a second tab is created with just the header row and no data.
Reply
Report
0
0
skyyang
skyyang     T1bb$
This comment was minimized by the moderator on the site
Hello, Ryan,

As you mentioned, the code works well for small data ranges, if there are lots of data, the code will not work properly.
In such situations, I recommend using the "Split Data" feature offered by Kutools for Excel. This powerful feature can greatly assist you in managing large amounts of data. To take advantage of this feature, you can download and install Kutools for Excel, which is available for a 30-day free trial.

Please have a try, thank you!
Reply
Report
0
0
Dane
Dane
This comment was minimized by the moderator on the site
I've come across many solutions in VBA message boards for parsing data into worksheets or columns based upon filtering a particular column, but they all require a bit of tinkering and customization. What makes this so brilliant is that it is dynamic, user-friendly even for beginners (which gives it shareable utility), and copy/paste ready.

You rock.
Reply
0
0
skyyang
skyyang     Dane
This comment was minimized by the moderator on the site
Hi, Dane,
Thanks for your comment, glad this can help you! Have a good day!
Reply
Report
0
0
Giancarlo Forero
Giancarlo Forero
This comment was minimized by the moderator on the site
When I try to split data from a different sheet, it copies and pastes the entire sheet into one sheet instead of multiple sheets. Could this be because the naming convention of the sheet I'm trying to split is similar to another sheet?
Reply
0
0
skyyang
skyyang     Giancarlo Forero
This comment was minimized by the moderator on the site
Hello, Giancarlo,

If the data in the column is same with a sheet name in the workbook, the sheet with the same name will be kept, other data will be split into separate sheet.
Thanks for your comment.
Reply
Report
0
0
There are no comments posted here yet
Load More
Please leave your comments in English
Guest
Posting as Guest
×
Rate this post:
Reset
0   Characters
Suggested Locations