Порада. Інші мови - перекладена Google. Ви можете відвідати English версія цього посилання.
увійти
x
or
x
x
Реєстрація
x

or

Як розділити дані на декілька робочих аркушів на основі колонки в Excel?

Припускаючи, що у вас є робочий аркуш з величезними рядами даних, і тепер вам потрібно розділити дані на кілька робочих аркушів на основі ім'я стовпчик (див. наступний знімок екрана), а імена вводяться випадковим чином. Можливо, ви можете спочатку сортувати їх, а потім копіювати і вставляти їх по черзі на інші нові аркуші. Але вам доведеться терпіти, щоб копіювати та вставляти повторно. Сьогодні я буду говорити про деякі швидкі прийоми для вирішення цього завдання.

Документ розщеплює дані по стовпцям 1

Розділити дані на декілька робочих аркушів на основі стовпця з кодом VBA

Розділити дані на декілька робочих аркушів на основі колонки з Kutools для Excel


Розділити дані на декілька робочих аркушів на основі конкретного стовпця або рядків у робочому аркуші:

Якщо ви хочете розділити великий робочий аркуш на кілька аркушів на основі певних даних стовпців або підрахунку рядків, то Kutools для Excel's Спліт даних функція може допомогти вам вирішити це завдання швидко і легко.

Документ розщеплює дані по стовпцям 6

Kutools для Excel: з додатковими надбудовами Excel більше, ніж 200, які можна безкоштовно спробувати без обмежень у днях 60. Завантажити та безкоштовну пробну версію зараз!


Розділити дані на декілька робочих аркушів на основі стовпця з кодом VBA


Якщо ви хочете швидко і автоматично розподілити дані на основі значення стовпця, наступний код VBA є гарним вибором. Будь ласка, робіть так:

1. Утримуйте клавішу ALT + F11 ключі, щоб відкрити Microsoft Visual Basic для додатків вікна.

2. Клацання вставка > Модулі, і вставте наступний код у вікно модуля.

Sub Splitdatabycol()
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
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
Sheets.Add(after:=Worksheets(Worksheets.Count)).Name = myarr(i) & ""
Else
Sheets(myarr(i) & "").Move after:=Worksheets(Worksheets.Count)
End If
xWSTRg.Range(title).Copy
Sheets(myarr(i) & "").Paste Destination:=Sheets(myarr(i) & "").Range("A1")
ws.Range("A" & (titlerow + xTRg.Rows.Count) & ":A" & lr).EntireRow.Copy Sheets(myarr(i) & "").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 ключ, щоб запустити код, і вікно підказки вискочить, щоб нагадати вам, що ви вибрали рядок заголовка, див. скріншот:

Документ розщеплює дані по стовпцям 7

4. А потім клацніть OK у другому вікні підказки виберіть дані стовпця, які потрібно розділити на основі, див. знімок екрана:

Документ розщеплює дані по стовпцям 8

5. Потім натисніть кнопку OK, а всі дані в активному аркуші розбиті на кілька таблиць за значенням стовпця. І розділені робочі таблиці називаються з іменами розділених комірок. Переглянути скріншот:

Документ розщеплює дані по стовпцям 2

примітки: Розбиті робочі листи розміщуються в кінці робочої книги, в якій знаходиться майстер робочого листа.


Розділити дані на декілька робочих аркушів на основі колонки з Kutools для Excel

Як початківець Excel, цей довгий кодекс VBA є дещо важким для нас, і більшість з нас навіть не знають, як змінити код, як нашу потребу.

Тут я представити вам багатофункціональний інструмент -Kutools для Excel, його Спліт даних Утиліта не тільки допоможе вам розділити дані на декілька робочих аркушів на основі колонки, але також зможе розділити дані за підрахунками рядків.

Kutools для Excel : з більш ніж 300 зручними надбудовами Excel, які можна безкоштовно спробувати без обмежень в днях 60.

Якщо ви встановили Kutools для Excel, будь ласка, виконайте наступне:

1. Виберіть діапазон даних, які потрібно розділити.

2. Клацання Kutools More > Робочий лист > Спліт даних, див. скріншот:

Документ розщеплює дані по стовпцям 3

3, в Спліт даних на кілька робочих аркушів діалогове вікно, вам потрібно:

1). Виберіть Конкретний стовпець опція в Спліт на основі розділ і виберіть значення стовпця, за яким ви хочете розділити дані, в розкривному списку. (Якщо у ваших даних є заголовки, і ви хочете вставити їх у кожен новий розділений аркуш, перевірте Мої дані мають заголовки варіант.)

2). Потім ви можете вказати імена розділених аркушів під заголовком Ім'я нових робочих таблиць розділ, вкажіть правила імен робочого листа з Правила випадаючий список, ви можете додати префікс or суфікс для назв аркушів також.

3). Натисніть кнопку OK кнопка Див. Знімок екрана:

Документ розщеплює дані по стовпцям 4

4. Тепер дані розділені на кілька робочих аркушів у новій робочій книзі.

Документ розщеплює дані по стовпцям 5

Натисніть, щоб завантажити Kutools для Excel і безкоштовну пробну версію зараз!


Розділити дані на декілька робочих аркушів на основі колонки з Kutools для Excel

Kutools для Excel включає більше, ніж 300 зручних інструментів Excel. Безкоштовно спробувати без обмежень в днях 60. Завантажити безкоштовну пробну версію зараз!


Пов'язана стаття:

Як розподілити дані на кілька робочих аркушів за кількістю рядків?



Рекомендовані інструменти для роботи з продуктивністю

Вкладка Office

золота зірка1 Принесіть зручні вкладки для Excel та іншого програмного забезпечення Office, як і Chrome, Firefox та нового Internet Explorer.

Kutools для Excel

золота зірка1 Дивовижний! Збільште свою продуктивність за 5 хв. Не потрібно спеціальних навичок, заощаджуйте дві години щодня!

золота зірка1 Нові можливості 300 для Excel, зробити Excel набагато простішим та потужним:

  • Об'єднати клітинки / рядки / стовпці без втрати даних.
  • Об'єднати та об'єднати декілька аркушів і книг.
  • Порівняйте діапазони, копіюйте декілька діапазонів, перетворюйте текст на дату, одиницю та валюту.
  • Розрахувати за кольорами, Інтерв'ю підказок, Розширений сортування та Супер фільтр
  • Більше Вибрати / Вставити / Видалити / Текст / Формат / Посилання / Коментарі / Робочі книги / Робочі аркуші Інструменти ...

Знімок екрану Kutools для Excel

Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Katharina · 18 days ago
    Great Script, thanks! What do I have to do to set the header row range and column to use for grouping/splitting within the script? I know it is more elegant to use selectable parameters but for my use case it is always the same. As I do know nothing about VBA, any little hint is appreciated :-) Best, Katharina
  • To post as a guest, your comment is unpublished.
    daiana · 22 days ago
    It doesn't work with 120000 rows. Is there any way to make it work?
  • To post as a guest, your comment is unpublished.
    Radoslav · 25 days ago
    Perfect!!! Works and refer to all my demands. Tnx for that source code.
  • To post as a guest, your comment is unpublished.
    Rudi Miller · 1 months ago
    Sub parse_data()
    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
    vcol = 1
    Set ws = Sheets("Sheet1")
    lr = ws.Cells(ws.Rows.Count, vcol).End(xlUp).Row
    title = "A1:C1"
    titlerow = ws.Range(title).Cells(1).Row
    icol = ws.Columns.Count
    ws.Cells(1, icol) = "Unique"
    For i = 2 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
    Sheets.Add(after:=Worksheets(Worksheets.Count)).Name = myarr(i) & ""
    Else
    Sheets(myarr(i) & "").Move after:=Worksheets(Worksheets.Count)
    End If
    ws.Range("A" & titlerow & ":A" & lr).EntireRow.Copy Sheets(myarr(i) & "").Range("A1")
    Sheets(myarr(i) & "").Columns.AutoFit
    Next
    ws.AutoFilterMode = False
    ws.Activate
    End Sub
  • To post as a guest, your comment is unpublished.
    Jason · 2 months ago
    This formula is great, works perfectly for me.
    I want to split out data based on a location, which is in column 1. Which this does.
    However, is it possible to also split out based on column 2, for example. Built and Not Built. So a secondary condition also?