Як розділити дані на кілька аркушів на основі стовпця в Excel?
Припустимо, у вас є аркуш із величезними рядками даних, і тепер вам потрібно розділити дані на кілька аркушів на основі ІМ'Я стовпець (див. наступний знімок екрана), а імена вводяться випадковим чином. Можливо, ви можете спочатку відсортувати їх, а потім скопіювати та вставити по одному в інші нові аркуші. Але для цього потрібно буде терпіти, щоб повторно копіювати та вставляти. Сьогодні я розповім про кілька швидких прийомів для вирішення цього завдання.
Розділіть дані на кілька аркушів на основі стовпця з кодом VBA
Розділіть дані на кілька аркушів на основі стовпців за допомогою Kutools для Excel
Розділіть дані на кілька аркушів на основі стовпця з кодом VBA
Якщо ви хочете швидко та автоматично розділити дані на основі значення стовпця, наступний код VBA є хорошим вибором. Будь ласка, зробіть так:
1. Утримуйте клавішу ALT + F11 ключі, щоб відкрити Microsoft Visual Basic для додатків вікна.
2. Клацання Insert > Модуліта вставте наступний код у вікно модуля.
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 клавіша для запуску коду, і з'явиться вікно запиту, щоб нагадати вам вибрати рядок заголовка, див. знімок екрана:
4. А потім натисніть OK , а у другому вікні підказки виберіть дані стовпців, які потрібно розділити, див. знімок екрана:
5. Потім натисніть кнопку OK, а всі дані на активному аркуші поділяються на кілька аркушів за значенням стовпця. А розділені робочі аркуші називаються іменами розділених комірок. Дивіться знімок екрана:
примітки: Розділені аркуші розміщуються в кінці книги, де знаходиться головний аркуш.
Розділіть дані на кілька аркушів на основі стовпців за допомогою Kutools для Excel
Для початківця Excel цей довгий код VBA для нас дещо складний, і більшість з нас навіть не знають, як змінити код, як потрібно. Тут я представив вам багатофункціональний інструмент--Kutools для Excel, його Розділити дані Утиліта не тільки може допомогти вам розділити дані на кілька робочих аркушів на основі стовпців, але також може розділити дані за кількістю рядків.
Примітка:Щоб застосувати це Розділити дані, по-перше, вам слід завантажити Kutools для Excel, а потім швидко та легко застосувати функцію.
після установки Kutools для Excel, будь ласка, зробіть так:
1. Виберіть діапазон даних, які потрібно розділити.
2. Клацання Kutools Plus > Робочий аркуш > Розділити дані, див. скріншот:
3, в Розділіть дані на кілька аркушів у діалоговому вікні потрібно:
1). Виберіть Конкретний стовпець опція в Спліт на основі розділу та оберіть значення стовпця, за яким потрібно розділити дані, виходячи зі спадного списку. (Якщо ваші дані мають заголовки, і ви хочете вставити їх у кожен новий розділений аркуш, перевірте Мої дані мають заголовки варіант.)
2). Потім ви можете вказати назви розділених робочих аркушів під Назва нових аркушів у розділі вкажіть правила імен робочого аркуша з Правила випадаючий список, ви можете додати префікс or суфікс для назв аркушів також.
3). Клацніть на OK кнопку. Дивіться знімок екрана:
4. Тепер дані розділено на кілька аркушів у новій книзі.
Натисніть, щоб завантажити Kutools для Excel та безкоштовну пробну версію зараз!
Розділіть дані на кілька аркушів на основі стовпців за допомогою Kutools для Excel
Kutools для Excel включає більше 300 зручних інструментів Excel. Безкоштовно спробувати без обмежень протягом 30 днів. Завантажте безкоштовну пробну версію зараз!
Пов'язана стаття:
Як розділити дані на кілька аркушів за підрахунком рядків?
Найкращі інструменти продуктивності офісу
Покращуйте свої навички Excel за допомогою Kutools для Excel і відчуйте ефективність, як ніколи раніше. Kutools для Excel пропонує понад 300 додаткових функцій для підвищення продуктивності та економії часу. Натисніть тут, щоб отримати функцію, яка вам найбільше потрібна...
Вкладка Office Передає інтерфейс із вкладками в Office і значно полегшує вашу роботу
- Увімкніть редагування та читання на вкладках у Word, Excel, PowerPoint, Publisher, Access, Visio та Project.
- Відкривайте та створюйте кілька документів на нових вкладках того самого вікна, а не в нових вікнах.
- Збільшує вашу продуктивність на 50% та зменшує сотні клацань миші для вас щодня!