Note: The other languages of the website are Google-translated. Back to English

Bagaimana cara membagi data menjadi beberapa lembar kerja berdasarkan kolom di Excel?

Misalkan Anda memiliki lembar kerja dengan banyak baris data, dan sekarang, Anda perlu membagi data menjadi beberapa lembar kerja berdasarkan Nama kolom (lihat tangkapan layar berikut), dan nama-nama dimasukkan secara acak. Mungkin Anda dapat mengurutkannya terlebih dahulu, lalu menyalin dan menempelkannya satu per satu ke lembar kerja baru lainnya. Namun hal ini membutuhkan kesabaran Anda untuk melakukan copy paste berulang kali. Hari ini, saya akan berbicara tentang beberapa trik cepat untuk menyelesaikan tugas ini.

doc membagi data dengan kolom 1

Pisahkan data menjadi beberapa lembar kerja berdasarkan kolom dengan kode VBA

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


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 baik. Harap lakukan seperti ini:

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. Lalu tekan F5 kunci untuk menjalankan kode, dan kotak prompt muncul untuk mengingatkan Anda memilih baris header, lihat tangkapan layar:

doc membagi data dengan kolom 7

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

doc membagi data dengan kolom 8

5. Lalu klik OK, dan semua data di lembar kerja aktif dipecah menjadi beberapa lembar kerja berdasarkan nilai kolom. Dan lembar kerja yang dipisahkan diberi nama dengan nama sel yang dipisahkan. Lihat tangkapan layar:

doc membagi data dengan kolom 2

Note: Lembar kerja terpisah ditempatkan di akhir buku kerja tempat lembar kerja master berada.


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

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

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

Setelah menginstal Kutools untuk Excel, lakukan seperti ini:

1. Pilih rentang data yang ingin Anda pisahkan.

2. Klik Kutools Plus > Lembar Kerja > Data split, lihat tangkapan layar:

doc membagi data dengan kolom 3

3. di Pisahkan Data menjadi Beberapa Lembar Kerja kotak dialog, Anda perlu:

1). Pilih Kolom tertentu pilihan dalam Pisahkan berdasarkan bagian, dan pilih nilai kolom yang ingin Anda pisahkan datanya berdasarkan di daftar turun bawah. (Jika data Anda memiliki header dan Anda ingin memasukkannya ke dalam setiap lembar kerja split baru, harap periksa Data saya memiliki tajuk pilihan.)

2). Kemudian Anda dapat menentukan nama lembar kerja terpisah, di bawah Nama lembar kerja baru bagian, tentukan aturan nama lembar kerja dari Peraturan daftar drop-down, Anda dapat menambahkan Awalan or Akhiran untuk nama sheet juga.

3). Klik OK tombol. Lihat tangkapan layar:

doc membagi data dengan kolom 4

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

doc membagi data dengan kolom 5

Klik untuk Mengunduh Kutools for Excel dan uji coba gratis Sekarang!


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

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


Artikel terkait:

Bagaimana cara membagi data menjadi beberapa lembar kerja dengan jumlah baris?


Alat Produktivitas Kantor Terbaik

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

  • Reuse: Masukkan dengan cepat rumus, bagan yang kompleks dan apa pun yang pernah Anda gunakan sebelumnya; Enkripsi Sel dengan kata sandi; Buat Milis dan mengirim email ...
  • Bilah Formula Super (dengan mudah mengedit beberapa baris teks dan rumus); Membaca Tata Letak (membaca dan mengedit sel dalam jumlah besar dengan mudah); Tempel ke Rentang yang Difilter...
  • Gabungkan Sel / Baris / Kolom tanpa kehilangan Data; Pisahkan Konten Sel; Gabungkan Baris / Kolom Duplikat... Mencegah Sel Duplikat; Bandingkan Rentang...
  • Pilih Duplikat atau Unik Baris; Pilih Baris Kosong (semua sel kosong); Temukan Super dan Temukan Fuzzy di Banyak Buku Kerja; Pilih Acak ...
  • Salinan Tepat Beberapa Sel tanpa mengubah referensi rumus; Buat Referensi Otomatis ke Beberapa Lembar; Sisipkan Poin, Kotak Centang, dan lainnya ...
  • Ekstrak Teks, Tambahkan Teks, Hapus berdasarkan Posisi, Hapus Space; Membuat dan Mencetak Subtotal Paging; Konversi Konten Antar Sel dan Komentar...
  • Filter Super (simpan dan terapkan skema filter ke sheet lain); Penyortiran Lanjutan menurut bulan / minggu / hari, frekuensi dan lainnya; Filter Khusus dengan huruf tebal, miring ...
  • Gabungkan Workbooks dan WorkSheets; Gabungkan Tabel berdasarkan kolom kunci; Pisahkan Data menjadi Beberapa Lembar; Konversi Batch xls, xlsx dan 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.
tab kte 201905

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!
officetab bawah
Urutkan komentar berdasarkan
komentar (299)
Belum ada peringkat. Jadilah yang pertama memberi peringkat!
Komentar ini diminimalkan oleh moderator di situs
Membagi data menjadi beberapa lembar kerja berdasarkan kolom dengan kode VBA menunjukkan beberapa kesalahan. coba perbaiki & perbarui yang sama. Jika Anda memberikan contoh file excel, itu akan sangat membantu.
Komentar ini diminimalkan oleh moderator di situs
Hai, terima kasih atas kode yang berfungsi untuk saya! Saya mencoba menemukan kode yang membagi satu lembar master menjadi beberapa lembar berdasarkan tanggal
Komentar ini diminimalkan oleh moderator di situs
Itu luar biasa! Proses ini akan memakan waktu lebih dari satu jam untuk saya, tetapi itu dilakukan dalam 30 detik. Yang ini akan saya simpan untuk perpustakaan VBA saya. Terima kasih!
Komentar ini diminimalkan oleh moderator di situs
Hai, saya punya 30000 sel di lembar kerja saya dan perlu membaginya dalam beberapa bulan. apakah ada kode yang bisa saya gunakan untuk melakukannya lebih cepat. Saya sudah mendapat 8 kolom dan tanggal adalah kolom B. Saya telah bermain-main dengan kode di atas yang diberikan tetapi gagal berkali-kali. Bisakah Anda membantu saya dengan ini. Terima kasih sebelumnya
Komentar ini diminimalkan oleh moderator di situs
Saya mendapatkan kesalahan berikut: Runtime error '6' overflow Setelah debugging tampilkan baris For i = 2 To Ir Baris excel saya lebih dari 500,000. Apakah ada solusi Terima kasih banyak untuk kodenya. Salam Hormat Lok
Komentar ini diminimalkan oleh moderator di situs
Halo, Terima kasih banyak untuk kodenya. saya mendapatkan error berikut: Runtime error '6' overflow di baris For i = 2 To Ir Any solution for this. Terima kasih
Komentar ini diminimalkan oleh moderator di situs
Saya mendapatkan kesalahan saat menekan F5 - GoTo Box meminta referensi??
Komentar ini diminimalkan oleh moderator di situs
Proses VBA bekerja dengan sempurna, terima kasih banyak telah berbagi keahlian Anda dan menghemat banyak waktu saya!
Komentar ini diminimalkan oleh moderator di situs
Kode VBA bekerja dengan sempurna. Tampaknya tidak memperbarui lembaran karena perubahan dilakukan pada Sheet1. Tolong bantu.
Komentar ini diminimalkan oleh moderator di situs
Hai, Terima kasih atas kode yang berfungsi untuk saya! Saya hanya punya dua pertanyaan/komentar. 1 data yang disalin tidak termasuk tata letak file asli. Apakah mungkin untuk menyalin data sebagai tabel dengan autofilter? 2 data yang disalin tampaknya tidak dibatasi oleh rentang judul. Apakah mungkin untuk menyesuaikan kode untuk rentang atau nama tabel tertentu? Penyesuaian ini akan sangat membantu. Salam, Pieter
Komentar ini diminimalkan oleh moderator di situs
Bekerja seperti pesona! Terima kasih.
Komentar ini diminimalkan oleh moderator di situs
Bekerja seperti pesona... Terima kasih untuk kode premium... :lol:
Komentar ini diminimalkan oleh moderator di situs
Terima kasih banyak, ini bekerja dengan baik. Namun, apa yang terjadi jika saya ingin data dalam setiap tab diurutkan lagi (menggunakan kolom lain)? Pada dasarnya, VBA ini memecahnya menjadi beberapa tab tetapi saya mungkin ingin memecahnya lebih lanjut..apakah itu mungkin?
Komentar ini diminimalkan oleh moderator di situs
Hai Jonathan, Komentar lama saya tahu, tetapi mungkin bermanfaat bagi orang lain di masa mendatang: Saya perlu melakukan ini tetapi tidak dapat menemukan cara mudah dengan VBA. Namun, saya menemukan bahwa jika Anda membuat Kolom baru di Spreadsheet Anda sebagai penggabungan dari 2, jadi misalnya =A1&" "&A2 Ini kemudian memberi Anda 1 Sel dengan kedua set informasi. Anda kemudian dapat menjalankan modul di atas dan berfungsi dengan baik! Sunting - Data dalam kolom harus lebih pendek dari 30 Karakter, jika tidak, data tidak akan disalin (ditampilkan sebagai kesalahan pada Modul) dan Anda mendapatkan lembar kosong di tengah Lembar baru Anda.
Komentar ini diminimalkan oleh moderator di situs
Luar biasa.. Ini menakjubkan. Saya berjuang dengan masalah ini begitu lama dan kode ini datang sebagai jeda. Terima kasih telah membagikannya.
Komentar ini diminimalkan oleh moderator di situs
Luar biasa. Terima kasih telah memposting.
Komentar ini diminimalkan oleh moderator di situs
Kode berfungsi seperti pesona untuk data yang lebih kecil (kurang dari 1200 baris). Saya mencoba menggunakan pada lembar kerja yang lebih besar dengan (17000 baris) dan hanya macet setelah dipecah menjadi 10-12 lembar. Jadi kami mencoba membagi data asli menjadi 3 buku kerja yang berbeda dan tetap menutup kami. Kami memiliki Windows 7 dan komputer kami juga tidak terlalu lambat. Apakah Anda menyarankan baris terbatas pada data untuk menggunakan kode ini dengan aman? Setiap saran akan dihargai.
Komentar ini diminimalkan oleh moderator di situs
Kode berfungsi seperti pesona untuk data yang lebih kecil (kurang dari 1200 baris). Saya mencoba menggunakan pada lembar kerja yang lebih besar dengan (17000 baris) dan hanya macet setelah dipecah menjadi 10-12 lembar. Jadi kami mencoba membagi data asli menjadi 3 buku kerja yang berbeda dan tetap menutup kami. Kami memiliki Windows 7 dan komputer kami juga tidak terlalu lambat. Apakah Anda menyarankan baris terbatas pada data untuk menggunakan kode ini dengan aman? Setiap saran akan dihargai. Yang saya tidak yakin adalah: Berapa jumlah maksimum baris yang dapat didukung makro? Saya bisa bermain-main dengannya... Itu antara 20k dan 40k![/quote]
Komentar ini diminimalkan oleh moderator di situs
Menghadapi masalah yang sama. Kode berfungsi untuk lembar di mana data lebih sedikit baris, namun untuk data yang lebih besar itu menunjukkan kesalahan sebagai "Excel tidak dapat menyelesaikan tugas ini dengan sumber daya yang tersedia. Pilih lebih sedikit data atau tutup aplikasi lain" (tidak ada aplikasi lain yang bekerja pada waktu yang sama) Kode bekerja seperti pesona untuk data yang lebih kecil (kurang dari 1200 baris). Saya mencoba menggunakan pada lembar kerja yang lebih besar dengan (17000 baris) dan hanya macet setelah dipecah menjadi 10-12 lembar. Jadi kami mencoba membagi data asli menjadi 3 buku kerja yang berbeda dan tetap menutup kami. Kami memiliki Windows 7 dan komputer kami juga tidak terlalu lambat. Apakah Anda menyarankan baris terbatas pada data untuk menggunakan kode ini dengan aman? Setiap saran akan dihargai.
Komentar ini diminimalkan oleh moderator di situs
Anda adalah pahlawan saya sepanjang masa! Saya telah berburu untuk ini selama berbulan-bulan tanpa hasil. Saya harus melakukan pemecahan laporan mingguan/bulanan ini menjadi 147+ lembar kerja dan mereka tidak akan memberi saya kutools. Pada catatan itu .. Saya benar-benar perlu belajar coding. :( Tapi terima kasih!
Komentar ini diminimalkan oleh moderator di situs
HI , Saya memiliki lembar yang memiliki 65000 Catatan dan memiliki 8- kasing berbeda, jadi pada dasarnya itu harus menghasilkan 80 lembar berbeda. Saya mencoba menjalankan kode ini, tetapi memunculkan Runtime Error 6 Overflow. Bisakah kode ini diubah untuk menyelesaikan masalah saya? Tolong bantuan Anda akan sangat dihargai.
Komentar ini diminimalkan oleh moderator di situs
[quote]HI , Saya memiliki lembar yang memiliki 65000 Catatan dan memiliki 8- kasus berbeda, jadi pada dasarnya itu harus menghasilkan 80 lembar berbeda. Saya mencoba menjalankan kode ini, tetapi memunculkan Runtime Error 6 Overflow. Bisakah kode ini diubah untuk menyelesaikan masalah saya? Tolong bantuan Anda akan sangat dihargai.Oleh ACE[/quote] Coba ubah Dim vcol, i As Integer menjadi Dim vcol, i As Long
Komentar ini diminimalkan oleh moderator di situs
Hai Saya mencoba mengubah DIM vcol ke LOng dan itu berfungsi dengan baik tetapi tiba-tiba karena kesalahan tidak ada cukup memori untuk menyelesaikan tindakan ini coba gunakan lebih sedikit data atau tutup aplikasi lain. Meskipun saya tidak memiliki aplikasi lain yang terbuka. Saya memiliki sedikit lebih dari 100 k baris dan kira-kira. Ukuran file 16mb. bantuan apa pun akan sangat dihargai. Terima kasih Mustafa
Komentar ini diminimalkan oleh moderator di situs
Sedikit kode yang luar biasa - berfungsi dengan baik (jika Anda mengubah variabel ke yang dibutuhkan spreadsheet Anda)
Komentar ini diminimalkan oleh moderator di situs
Saya memiliki lembar dengan jumlah baris yang bervariasi. Salah satu kolom adalah tanggal mulai dari 2010 dan seterusnya. Kolom lainnya adalah nama dana dengan data NAB untuk setiap dana terhadap tanggal. Jadi saya tidak ingin membagi kolom menjadi lembar yang berbeda, saya ingin membagi setiap NAMA DANA menjadi lembar sendiri dengan data NAV per tanggal Akhir Bulan, bukan tanggal harian. Bisakah ini dilakukan atau tidak mungkin?
Komentar ini diminimalkan oleh moderator di situs
Saya memiliki lembar kerja yang saya gunakan dan saya mencoba menemukan kode vba yang akan menyesuaikan nama akun dan menyalin baris tertentu ke buku kerja dan lembar baru dengan nama yang sama dapatkah Anda membantu?
Komentar ini diminimalkan oleh moderator di situs
Besar! Kode VBA berfungsi, terima kasih! Saya perlu lembar kerja keluaran ini berada dalam file excel individual alih-alih lembar kerja dan ada kesalahan ketika saya membagi ke banyak lembar kerja.
Komentar ini diminimalkan oleh moderator di situs
Starscor dan Tim jika Anda ingin membagi lembaran file menjadi beberapa file menggunakan nama baris ada kode makro kecil di halaman web yang sama yang melakukannya, cukup cari "split a workbook to memisahkan file Excel" Anda akan menemukannya. Tambahkan kode contoh itu ke akhir yang satu ini dengan menghapus sub akhir dan sub duplikat tentu saja dan Anda akan mendapatkan satu file untuk masing-masing.
Komentar ini diminimalkan oleh moderator di situs
adakah yang bisa membantu saya cara mengurutkan kolom di lembar berbeda di buku kerja yang sama pada satu waktu dan juga menghapus duplikat di lembar berbeda karena saya memiliki sekitar 65 lembar di buku kerja yang sama
Komentar ini diminimalkan oleh moderator di situs
ini sangat menyenangkan! Terima kasih. Saya sudah mencari ini untuk beberapa waktu.
Komentar ini diminimalkan oleh moderator di situs
Luar biasa - terima kasih telah berbagi ini. Bahkan menyebarkan sorotan/format ke lembar kerja baru!
Belum ada komentar yang diposting di sini
Muat Lebih
Tinggalkan komentar anda
Posting sebagai Tamu
×
Beri peringkat pos ini:
0   Karakter
Lokasi yang Disarankan

Ikuti kami

Hak Cipta © 2009 - www.extendoffice.com. | Seluruh hak cipta. Dipersembahkan oleh ExtendOffice. | Peta Situs
Microsoft dan logo Office adalah merek dagang atau merek dagang terdaftar dari Microsoft Corporation di Amerika Serikat dan / atau negara lain.
Dilindungi oleh Sectigo SSL