انتقل إلى المحتوى الرئيسي

كيفية تقسيم البيانات إلى أوراق عمل متعددة بناءً على عمود في Excel؟

لنفترض أن لديك ورقة عمل تحتوي على صفوف ضخمة من البيانات ، والآن تحتاج إلى تقسيم البيانات إلى أوراق عمل متعددة بناءً على الاسم العمود (انظر الصورة التالية) ، ويتم إدخال الأسماء بشكل عشوائي. ربما يمكنك فرزها أولاً ، ثم نسخها ولصقها واحدة تلو الأخرى في أوراق عمل جديدة أخرى. ولكن هذا سيحتاج إلى صبرك لنسخه ولصقه بشكل متكرر. اليوم سأتحدث عن بعض الحيل السريعة لحل هذه المهمة.

doc يقسم البيانات حسب الأعمدة 1

قسّم البيانات إلى أوراق عمل متعددة استنادًا إلى عمود برمز VBA

قم بتقسيم البيانات إلى أوراق عمل متعددة بناءً على عمود باستخدام Kutools for Excel


قسّم البيانات إلى أوراق عمل متعددة استنادًا إلى عمود برمز VBA

إذا كنت تريد تقسيم البيانات استنادًا إلى قيمة العمود بسرعة وبشكل تلقائي ، فإن كود VBA التالي يعد اختيارًا جيدًا. يرجى القيام بذلك على النحو التالي:

1. اضغط باستمرار على ALT + F11 مفاتيح لفتح ميكروسوفت فيسوال باسيك للتطبيقات نافذة.

2. انقر إدراج > وحدة، والصق التعليمة البرمجية التالية في نافذة الوحدة النمطية.

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. ثم اضغط F5 مفتاح لتشغيل الكود ، ويظهر مربع موجه لتذكيرك بتحديد صف الرأس ، انظر لقطة الشاشة:

doc يقسم البيانات حسب الأعمدة 7

4. ثم انقر فوق OK زر ، وفي مربع المطالبة الثاني ، يرجى تحديد بيانات العمود التي تريد تقسيمها بناءً على ، انظر لقطة الشاشة:

doc يقسم البيانات حسب الأعمدة 8

5. ثم اضغط OK، ويتم تقسيم كافة البيانات الموجودة في ورقة العمل النشطة إلى أوراق عمل متعددة حسب قيمة العمود. وتتم تسمية أوراق العمل المنقسمة بأسماء الخلايا المقسمة. انظر لقطة الشاشة:

doc يقسم البيانات حسب الأعمدة 2

ملاحظات: يتم وضع أوراق العمل المنقسمة في نهاية المصنف حيث توجد ورقة العمل الرئيسية.


قم بتقسيم البيانات إلى أوراق عمل متعددة بناءً على عمود باستخدام Kutools for Excel

كمبتدئ في برنامج Excel ، يعد رمز VBA الطويل هذا صعبًا إلى حد ما بالنسبة لنا ، ولا يعرف معظمنا حتى كيفية تعديل الكود حسب حاجتنا. هنا ، سأقدم لك أداة متعددة الوظائف -كوتولس ل إكسيل، في تقسيم البيانات لا يمكن أن تساعدك الأداة المساعدة فقط في تقسيم البيانات إلى أوراق عمل متعددة بناءً على العمود ، ولكن يمكنها أيضًا تقسيم البيانات حسب عدد الصفوف.

ملحوظة:لتطبيق هذا تقسيم البياناتأولاً ، يجب عليك تنزيل ملف كوتولس ل إكسيل، ثم قم بتطبيق الميزة بسرعة وسهولة.

بعد تثبيت كوتولس ل إكسيل، يرجى القيام بذلك على النحو التالي:

1. حدد نطاق البيانات التي تريد تقسيمها.

2. انقر كوتولس بلس > ورقة العمل > تقسيم البيانات، انظر لقطة الشاشة:

doc يقسم البيانات حسب الأعمدة 3

3. في تقسيم البيانات إلى أوراق عمل متعددة مربع الحوار ، تحتاج إلى:

1). تحديد عمود محدد الخيار في انقسام على أساس ، واختر قيمة العمود التي تريد تقسيم البيانات بناءً عليها في القائمة المنسدلة. (إذا كانت بياناتك تحتوي على رؤوس وتريد إدراجها في كل ورقة عمل مقسمة جديدة ، يرجى التحقق بياناتي لها رؤوس اختيار.)

2). ثم يمكنك تحديد أسماء أوراق العمل المنقسمة ، ضمن ملف اسم أوراق العمل الجديدة ، حدد قواعد أسماء أوراق العمل من قوانيـن القائمة المنسدلة ، يمكنك إضافة بادئة or لاحقة لأسماء الأوراق أيضًا.

3). انقر على OK زر. انظر لقطة الشاشة:

doc يقسم البيانات حسب الأعمدة 4

4. الآن يتم تقسيم البيانات إلى أوراق عمل متعددة في مصنف جديد.

doc يقسم البيانات حسب الأعمدة 5

انقر لتنزيل Kutools for Excel والتجربة المجانية الآن!


قم بتقسيم البيانات إلى أوراق عمل متعددة بناءً على عمود باستخدام Kutools for Excel

كوتولس ل إكسيل يتضمن أكثر من 300 أداة يدوية لبرنامج Excel. تجربة مجانية بدون قيود خلال 30 يومًا. قم بتنزيل الإصدار التجريبي المجاني الآن!


مقالة ذات صلة:

كيفية تقسيم البيانات إلى أوراق عمل متعددة عن طريق عدد الصفوف؟

أفضل أدوات إنتاجية المكتب

الميزات الشعبية: البحث عن التكرارات أو تمييزها أو تحديدها   |  حذف الصفوف الفارغة   |  دمج الأعمدة أو الخلايا دون فقدان البيانات   |   جولة بدون صيغة 
سوبر بحث: معايير متعددة VLookup    VLookup ذات القيمة المتعددة  |   VLookup عبر أوراق متعددة   |   بحث غامض ....
قائمة منسدلة متقدمة: إنشاء القائمة المنسدلة بسرعة   |  القائمة المنسدلة التابعة   |  قائمة منسدلة متعددة التحديد ....
مدير العمود: إضافة عدد محدد من الأعمدة  |  نقل الأعمدة  |  تبديل حالة رؤية الأعمدة المخفية  |  مقارنة النطاقات والأعمدة 
الميزات المميزة: التركيز على الشبكة   |  عرض تصميم   |   شريط الفورمولا الكبير    مدير المصنفات والأوراق   |  مكتبة الموارد (النص السيارات)   |  منتقي التاريخ   |  اجمع أوراق العمل   |  تشفير/فك تشفير الخلايا    إرسال رسائل البريد الإلكتروني عن طريق القائمة   |  سوبر تصفية   |   مرشح خاص (تصفية غامق / مائل / يتوسطه خط ...) ...
أفضل 15 مجموعة أدوات12 نص الأدوات (إضافة نص, إزالة الأحرف، ...)   |   +50 رسم الأنواع (مخطط جانت، ...)   |   40+ عملي الصيغ (احسب العمر على أساس تاريخ الميلاد، ...)   |   19 إدخال الأدوات (أدخل رمز الاستجابة السريعة, إدراج صورة من المسار، ...)   |   12 تحويل الأدوات (أرقام إلى كلمات, نتيجة تحويل عملة، ...)   |   7 دمج وتقسيم الأدوات (الجمع بين الصفوف المتقدمة, تقسيم الخلايا، ...)   |   ... و اكثر

عزز مهاراتك في Excel باستخدام Kutools for Excel، واختبر كفاءة لم يسبق لها مثيل. يقدم Kutools for Excel أكثر من 300 ميزة متقدمة لتعزيز الإنتاجية وتوفير الوقت.  انقر هنا للحصول على الميزة التي تحتاجها أكثر...

علامة تبويب kte 201905


يجلب Office Tab الواجهة المبوبة إلى Office ، ويجعل عملك أسهل بكثير

  • تمكين التحرير والقراءة المبوبة في Word و Excel و PowerPointوالناشر والوصول و Visio والمشروع.
  • فتح وإنشاء مستندات متعددة في علامات تبويب جديدة من نفس النافذة ، بدلاً من النوافذ الجديدة.
  • يزيد من إنتاجيتك بنسبة 50٪ ، ويقلل مئات النقرات بالماوس كل يوم!
Comments (312)
Rated 5 out of 5 · 2 ratings
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
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!
Rated 5 out of 5
This comment was minimized by the moderator on the site
The original header is not copied in the split sheet.
This comment was minimized by the moderator on the site
This works wonderfully, thank you very much!!! Huge time-saver.
Rated 5 out of 5
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?
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!!!!!
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.
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.
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!
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.
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!
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?
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.
There are no comments posted here yet
Load More
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations