Перейти до основного матеріалу

Як розділити велику таблицю на кілька маленьких таблиць в Excel?

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

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

Розбийте велику таблицю на кілька таблиць на основі значення стовпця за допомогою коду VBA

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

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


Розбийте велику таблицю на кілька таблиць на основі значення стовпця за допомогою коду VBA

Щоб розділити цю велику таблицю на кілька таблиць на основі певного значення стовпця, наступний код VBA може зробити вам послугу. Будь ласка, зробіть так:

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

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

Код VBA: Розбийте велику таблицю на кілька таблиць за стовпчиком ключа:

Sub Splitdatabycol()
'by 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
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 клавішу для запуску цього коду, і з'явиться вікно запиту, виберіть рядок заголовка з ваших даних, див. знімок екрана:

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

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


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

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

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

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

Код VBA: Розбийте велику таблицю на кілька таблиць за кількістю рядків:

Sub Splitdatabyrows()
'Updated by Extendoffice 
Dim WorkRng As Range
Dim xRow As Range
Dim SplitRow As Integer
Dim xWs As Worksheet
Dim xTRg As Range
Dim xNTRg As Range
Dim xIER
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set xTRg = Application.InputBox("Please select the header row:", xTitleId, "", Type:=8)
If TypeName(xTRg) = "Nothing" Then Exit Sub
Set WorkRng = Application.InputBox("Please select the data range(exclude the header row):", xTitleId, WorkRng.Address, Type:=8)
If TypeName(WorkRng) = "Nothing" Then Exit Sub
SplitRow = Application.InputBox("Split Row Num", xTitleId, Type:=1)
If SplitRow = 0 Then Exit Sub
Set xWs = WorkRng.Parent
Set xRow = WorkRng.Rows(1)
xIER = WorkRng.Rows.Count
xIER = WorkRng.Row + xIER - 1
Application.ScreenUpdating = False
For i = 1 To WorkRng.Rows.Count Step SplitRow
    resizeCount = SplitRow
    If (xIER - xRow.Row + 1) < SplitRow Then
        resizeCount = (xIER - xRow.Row + 1)
    End If
    xRow.Resize(resizeCount).Copy
    Set xWs = Application.Worksheets.Add(after:=Application.Worksheets(Application.Worksheets.Count))
    If xIER > (xRow.Row + SplitRow - 1) Then
        xWs.Name = xRow.Row & " - " & (xRow.Row + SplitRow - 1)
    ElseIf xIER = xRow.Row Then
        xWs.Name = xRow.Row
    Else
        xWs.Name = xRow.Row & " - " & xIER
    End If
    Application.ActiveSheet.Range("A1").PasteSpecial
    Set xNTRg = Application.ActiveSheet.Range("A1")
    xTRg.Copy
    xNTRg.Insert
    Set xRow = xRow.Offset(SplitRow)
Next
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

3. Потім натисніть F5 ключ, у спливаючому діалоговому вікні виберіть рядок заголовка, див. знімок екрана:

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

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

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


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

Можливо, вищезазначені коди важкі для більшості користувачів, тут я представив дивовижну особливість -Розділити дані of Kutools для Excel. За допомогою цієї утиліти ви можете швидко та легко розділити велику таблицю на кілька таблиць за стовпчиками ключів або кількістю рядків.

Порада:Щоб застосувати це Розділити дані По-перше, вам слід завантажити Kutools для Excel, а потім швидко та легко застосувати функцію.

після установки Kutools для Excel, будь ласка, зробіть так:

1. Виберіть діапазон даних, який потрібно розділити, а потім клацніть Kutools Plus > Розділити дані, див. скріншот:

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

(1.) Виберіть Конкретний стовпець or Фіксовані ряди від Спліт на основі розділ, як вам потрібно;

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

3. Потім натисніть кнопку Ok і тепер велика таблиця розділена на кілька маленьких таблиць у новій книзі. Дивіться скріншоти:

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

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


Більше відносних статей:

  • Розділіть книгу, щоб відокремити файли Excel в Excel
  • Можливо, вам доведеться розділити велику книгу, щоб розділити файли Excel із збереженням кожного аркуша книги як окремого файлу Excel. Наприклад, ви можете розділити книгу на декілька окремих файлів Excel, а потім доставити кожен файл іншій особі, яка буде з нею обробляти. Роблячи це, ви можете змусити певних осіб обробляти конкретні дані та захищати ваші дані. У цій статті будуть представлені способи розділення великої книги на окремі файли Excel на основі кожного аркуша.
  • Поділіть повне ім’я на ім’я та прізвище в Excel
  • Припустимо, у вас є список імен, як показано на першому знімку екрана в одному стовпці нижче, і вам потрібно розділити повне ім'я на стовпець з першим іменем, стовпцем із середнім іменем та стовпцем прізвища, як показано на наступному знімку екрана. Ось кілька хитрих методів, які допоможуть вам вирішити цю проблему.
  • Розділіть довгий список на рівні групи в Excel
  • Якщо у вас є довгий список даних, який потрібно розділити на кілька рівних груп, як показано на наступному знімку екрана, як би ви могли швидко та легко впоратися з цим завданням в Excel?

 


Найкращі інструменти для підвищення продуктивності офісу

Kutools для Excel вирішує більшість ваших проблем і збільшує продуктивність на 80%

  • Супер формула бар (легко редагувати кілька рядків тексту та формули); Макет читання (легко читати та редагувати велику кількість комірок); Вставте у відфільтрований діапазон...
  • Об’єднати клітинки / рядки / стовпці та Ведення даних; Вміст розділених комірок; Поєднуйте повторювані рядки та суму / середнє... Запобігання дублюючим клітинам; Порівняйте діапазони...
  • Виберіть Повторюваний або Унікальний Рядки; Виберіть Пусті рядки (усі клітинки порожні); Супер знахідка та нечітка знахідка у багатьох робочих зошитах; Випадковий вибір ...
  • Точна копія Кілька клітинок без зміни посилання на формулу; Автоматичне створення посилань на кілька аркушів; Вставте кулі, Прапорці та інше ...
  • Улюблені та швидко вставлені формули, Діапазони, діаграми та зображення; Шифрувати комірки з паролем; Створити список розсилки та надсилати електронні листи ...
  • Витяг тексту, Додати текст, Видалити за позицією, Видаліть пробіл; Створення та друк проміжних підсумків підкачки; Перетворення вмісту комірок та коментарів...
  • Супер фільтр (зберегти та застосувати схеми фільтрів до інших аркушів); Розширене сортування за місяцем / тижнем / днем, частотою та іншим; Спеціальний фільтр жирним, курсивом ...
  • Поєднайте робочі зошити та робочі аркуші; Об’єднати таблиці на основі ключових стовпців; Розділіть дані на кілька аркушів; Пакетне перетворення xls, xlsx та PDF...
  • Групування зведеної таблиці за номер тижня, день тижня та багато іншого ... Показати розблоковані, заблоковані клітини за різними кольорами; Виділіть клітини, які мають формулу / назву...
вкладка kte 201905
  • Увімкніть редагування та читання на вкладках у Word, Excel, PowerPoint, Publisher, Access, Visio та Project.
  • Відкривайте та створюйте кілька документів на нових вкладках того самого вікна, а не в нових вікнах.
  • Збільшує вашу продуктивність на 50% та зменшує сотні клацань миші для вас щодня!
дно офісної таблиці

 

Comments (13)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
Thank you very much.....
This comment was minimized by the moderator on the site
Super Sache

Frage:

Ist es möglich die Tabelle immer neu zu füllen und neu zu berechnen.
Bin absoluter anfänger. :-)
Danke im Voraus
This comment was minimized by the moderator on the site
Hello, Lukas,
I'm sorry, the methods in this article can't support to update the new data when the original data is changed.
So, you need to run the code again to get the latest data if there are changes in your data.
Thank you!
This comment was minimized by the moderator on the site
I can't get this macro to work (Split A Large Table Into Multiple Tables Based On Column Value With VBA Code)
My table has 5 columns and 639,165 rows. Is it too big?
This comment was minimized by the moderator on the site
Hello, Rebekah

If the data is too large, the code will not work perfectly.
Here, I recommend our Kutools for Excel' Split Data feature for you. With this feature, you can split large data to multiple sheets quickly and easily.
You can try it for 30 days freely. Please download it ffrom: https://www.extendoffice.com/download/kutools-for-excel.html
Please have a try, hope it can help you!
This comment was minimized by the moderator on the site
Hallo zusammen. Ich habe das gleiche Problem wie cGast - gibt es dazu eine Lösung?
This comment was minimized by the moderator on the site
Hi, Miriam,

The VBA code has been updated to a new one in this article, please try it again, if you have any other problem, please comment here. Thank you!
This comment was minimized by the moderator on the site
I tried "Split a large table into multiple tables based on the specific number of rows with VBA code" with my data of 103,000 rows split in groups of 15000 which should have returned 8 sheets, however it didnt work, it just produced 8 sheets with the headers only. but it does work when i use it with less that 10000rows. any help there?
This comment was minimized by the moderator on the site
Hello cguest,
Yes, as you said, the VBA code does not work correctlly when there are lots of data, here, I provide a new code, please try:
Sub Splitdatabyrows()
'Updated by Extendoffice 
Dim WorkRng As Range
Dim xRow As Range
Dim SplitRow As Integer
Dim xWs As Worksheet
Dim xTRg As Range
Dim xNTRg As Range
Dim xIER
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection


Set xTRg = Application.InputBox("Please select the header row:", xTitleId, "", Type:=8)
If TypeName(xTRg) = "Nothing" Then Exit Sub
Set WorkRng = Application.InputBox("Please select the data range(exclude the header row):", xTitleId, WorkRng.Address, Type:=8)
If TypeName(WorkRng) = "Nothing" Then Exit Sub


SplitRow = Application.InputBox("Split Row Num", xTitleId, Type:=1)
If SplitRow = 0 Then Exit Sub

Set xWs = WorkRng.Parent
Set xRow = WorkRng.Rows(1)
xIER = WorkRng.Rows.Count
xIER = WorkRng.Row + xIER - 1


Application.ScreenUpdating = False
For i = 1 To WorkRng.Rows.Count Step SplitRow
    resizeCount = SplitRow
    If (xIER - xRow.Row + 1) < SplitRow Then
        resizeCount = (xIER - xRow.Row + 1)
    End If
    xRow.Resize(resizeCount).Copy
    Set xWs = Application.Worksheets.Add(after:=Application.Worksheets(Application.Worksheets.Count))
    If xIER > (xRow.Row + SplitRow - 1) Then
        xWs.Name = xRow.Row & " - " & (xRow.Row + SplitRow - 1)
    ElseIf xIER = xRow.Row Then
        xWs.Name = xRow.Row
    Else
        xWs.Name = xRow.Row & " - " & xIER
    End If
    Application.ActiveSheet.Range("A1").PasteSpecial
    Set xNTRg = Application.ActiveSheet.Range("A1")
    xTRg.Copy
    xNTRg.Insert
    Set xRow = xRow.Offset(SplitRow)
Next
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub


Hope it can help you, Thank you!
This comment was minimized by the moderator on the site
"Teilen Sie eine große Tabelle basierend auf der spezifischen Anzahl von Zeilen mit VBA-Code in mehrere Tabellen auf"Funktioniert echt Super. Ist es möglich dass auch Verbundene Zellen erkannt und die Anzahl der Zeilen entsprechend angepasst wird so dass die Verbundenen Zellen beim Seitenumbruch nicht getrennt werden?
This comment was minimized by the moderator on the site
Если разбиваете по строкам и строк больше, чем 32 767 то поменяйте тип данных у переменных xIER и SplitRow с Integer на Long
This comment was minimized by the moderator on the site
Ich habe die erste Variante mit 456.913 Zeilen und 8 Spalten probiert - leider ohne dass irgendwas ausgeführt wurde.
This comment was minimized by the moderator on the site
Всем привет. Столкнулся с проблемой. У меня таблица из 7 колонок и 235000 строк. Макрос не разбивает на страницы. Вернее он страницы создает но они внутри пустые. Тестировал с меньшим количеством строк примерно 1000. Макрос срабатывал. Подскажите метод решения.
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations