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

Як розділити дані на кілька аркушів на основі стовпця в Excel?

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

doc розділити дані за стовпцями 1

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

doc розділити дані за стовпцями 7

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

doc розділити дані за стовпцями 8

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

doc розділити дані за стовпцями 2

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


Розділіть дані на кілька аркушів на основі стовпця за допомогою Kutools для Excel

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

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

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

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

2. Клацання Kutools Plus > Робочий аркуш > Розділити дані, див. скріншот:

doc розділити дані за стовпцями 3

3, в Розділіть дані на кілька аркушів у діалоговому вікні потрібно:

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

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

3). Клацніть на OK кнопку. Дивіться знімок екрана:

doc розділити дані за стовпцями 4

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

doc розділити дані за стовпцями 5

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


Розділіть дані на кілька аркушів на основі стовпця за допомогою Kutools для Excel

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


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

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


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

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

  • Повторне використання: Швидко вставте складні формули, діаграми і все, що ви використовували раніше; Шифрувати комірки з паролем; Створити список розсилки та надсилати електронні листи ...
  • Супер формула бар (легко редагувати кілька рядків тексту та формули); Макет читання (легко читати та редагувати велику кількість комірок); Вставте у відфільтрований діапазон...
  • Об’єднати клітинки / рядки / стовпці без втрати даних; Вміст розділених комірок; Об'єднати повторювані рядки / стовпці... Запобігання дублюючим клітинам; Порівняйте діапазони...
  • Виберіть Повторюваний або Унікальний Рядки; Виберіть Пусті рядки (усі клітинки порожні); Супер знахідка та нечітка знахідка у багатьох робочих зошитах; Випадковий вибір ...
  • Точна копія Кілька клітинок без зміни посилання на формулу; Автоматичне створення посилань на кілька аркушів; Вставте кулі, Прапорці та інше ...
  • Витяг тексту, Додати текст, Видалити за позицією, Видаліть пробіл; Створення та друк проміжних підсумків підкачки; Перетворення вмісту комірок та коментарів...
  • Супер фільтр (зберегти та застосувати схеми фільтрів до інших аркушів); Розширене сортування за місяцем / тижнем / днем, частотою та іншим; Спеціальний фільтр жирним, курсивом ...
  • Поєднайте робочі зошити та робочі аркуші; Об’єднати таблиці на основі ключових стовпців; Розділіть дані на кілька аркушів; Пакетне перетворення xls, xlsx та PDF...
  • Понад 300 потужних функцій. Підтримує Office / Excel 2007-2021 і 365. Підтримує всі мови. Легке розгортання на вашому підприємстві чи в організації. 30-денна безкоштовна пробна версія повних функцій. 60-денна гарантія повернення грошей.
вкладка kte 201905

Вкладка Office забезпечує інтерфейс з вкладками для Office і значно спрощує вашу роботу

  • Увімкніть редагування та читання на вкладках у Word, Excel, PowerPoint, Publisher, Access, Visio та Project.
  • Відкривайте та створюйте кілька документів на нових вкладках того самого вікна, а не в нових вікнах.
  • Збільшує вашу продуктивність на 50% та зменшує сотні клацань миші для вас щодня!
дно офісної таблиці
Коментарі (303)
Поки немає оцінок. Оцініть першим!
Цей коментар був мінімізований модератором на сайті
Розбиття даних на кілька аркушів на основі стовпця з кодом VBA показує деяку помилку. будь ласка, спробуйте виправити це та оновіть те саме. Якщо ви надасте зразки файлів Excel, це буде дуже корисно.
Цей коментар був мінімізований модератором на сайті
Привіт, дякую за код, він у мене працює! Я намагаюся знайти код, який розбиває один основний аркуш на кілька аркушів на основі дати
Цей коментар був мінімізований модератором на сайті
Це було неймовірно! Цей процес зайняв у мене більше години, але це було зроблено протягом 30 секунд. Цей я збережу для своєї бібліотеки VBA. Дякую!
Цей коментар був мінімізований модератором на сайті
Привіт, у мене на аркуші 30000 8 клітинок, і мені потрібно розділити їх на місяці. чи є код, який я можу використати, щоб зробити це швидше. У мене є XNUMX стовпців, а дата — стовпець B. Я бавився з наведеним вище кодом, але не вдався. Не могли б ви допомогти мені з цим. Спасибі заздалегідь
Цей коментар був мінімізований модератором на сайті
Я отримую таку помилку: Помилка виконання '6' overflow Після налагодження показати рядок For i = 2 To Ir Мої рядки excel мають понад 500,000 XNUMX. Чи є рішення. Щиро дякую за код. З найкращими побажаннями Лок
Цей коментар був мінімізований модератором на сайті
Привіт, дуже дякую за код. Я отримую таку помилку: Помилка виконання '6' переповнення в рядку For i = 2 To Ir Будь-яке рішення для цього. Дякую
Цей коментар був мінімізований модератором на сайті
Я отримую помилку при натисканні F5 - GoTo Box із запитом на посилання??
Цей коментар був мінімізований модератором на сайті
Процес VBA працював ідеально, дуже дякую, що поділилися своїм досвідом та заощадили багато часу!
Цей коментар був мінімізований модератором на сайті
Код VBA працював ідеально. Схоже, що аркуші не оновлюються, оскільки в Аркуш 1 вносяться зміни. Будь ласка, допоможіть.
Цей коментар був мінімізований модератором на сайті
Привіт, дякую за код, він працює для мене! У мене лише два запитання/зауваження. 1 скопійовані дані не включають макет оригінального файлу. Чи можна було б скопіювати дані як таблицю з автофільтром? 2 скопійовані дані, здається, не обмежені діапазоном заголовків. Чи можна налаштувати код для певного діапазону або імені таблиці? Ці коригування були б дуже корисними. З повагою, Пітер
Цей коментар був мінімізований модератором на сайті
Працює як шарм! Дякую.
Цей коментар був мінімізований модератором на сайті
Працює як шарм... Дякую за преміум-код... :lol:
Цей коментар був мінімізований модератором на сайті
Дуже дякую, це чудово спрацювало. Однак що станеться, якщо я захочу, щоб дані на кожній вкладці були відсортовані знову (використовуючи стовпець пиляка)? В основному, цей VBA розбиває його на вкладки, але я можу хотіти, щоб він розбив далі.. це можливо?
Цей коментар був мінімізований модератором на сайті
Привіт, Джонатан! Старий коментар, який я знаю, але може стати в нагоді іншим у майбутньому: мені потрібно було це зробити, але я не міг знайти простий шлях із VBA. Однак я виявив, що якщо ви створюєте новий стовпець у вашій електронній таблиці як об’єднання 2, наприклад =A1&" "&A2, це дає вам 1 клітинку з обома наборами інформації. Потім ви можете запустити наведений вище модуль, і він працює нормально! Редагувати – дані в стовпцях мають бути коротшими за 30 символів, інакше дані не копіюються (відображаються як помилка в модулі), і ви отримаєте порожній аркуш в середині ваших нових аркушів.
Цей коментар був мінімізований модератором на сайті
Чудово.. Це було приголомшливо. Я так довго боровся з цією проблемою, і цей код прийшов як відпочинок. Дякую, що поділилися.
Цей коментар був мінімізований модератором на сайті
Дивовижний. Дякую за публікацію.
Цей коментар був мінімізований модератором на сайті
Код працював як шарм для менших даних (менше 1200 рядків). Я спробував використовувати на більшому робочому аркуші (17000 рядків), і він просто розбився після розділення на 10-12 аркушів. Тому ми спробували розділити вихідні дані на 3 різні робочі книги і все одно закрили нас. У нас Windows 7, і наші комп’ютери теж не такі повільні. Чи пропонуєте ви якісь обмежені рядки даних для безпечного використання цього коду? Буду вдячний за будь-яку пропозицію.
Цей коментар був мінімізований модератором на сайті
Код працював як шарм для менших даних (менше 1200 рядків). Я спробував використовувати на більшому робочому аркуші (17000 рядків), і він просто розбився після розділення на 10-12 аркушів. Тому ми спробували розділити вихідні дані на 3 різні робочі книги і все одно закрили нас. У нас Windows 7, і наші комп’ютери теж не такі повільні. Чи пропонуєте ви якісь обмежені рядки даних для безпечного використання цього коду? Буду вдячний за будь-яку пропозицію. Я не впевнений: яку максимальну кількість рядків може підтримувати макрос? Я міг би погратися з цим... Це десь між 20 і 40 тисячами![/quote]
Цей коментар був мінімізований модератором на сайті
Зіткнувся з такою ж проблемою. Код працює для аркушів, де дані містять менше рядків, однак для більших даних він показує помилку «Excel не може виконати це завдання з доступними ресурсами. Виберіть менше даних або закрийте інші програми» (немає жодної іншої програми, яка працює одночасно) Код працював як шарм для менших даних (менше 1200 рядків). Я спробував використовувати на більшому робочому аркуші (17000 рядків), і він просто розбився після розділення на 10-12 аркушів. Тому ми спробували розділити вихідні дані на 3 різні робочі книги і все одно закрили нас. У нас Windows 7, і наші комп’ютери теж не такі повільні. Чи пропонуєте ви якісь обмежені рядки даних для безпечного використання цього коду? Буду вдячний за будь-яку пропозицію.
Цей коментар був мінімізований модератором на сайті
Ти мій герой на всі часи! Я безрезультатно шукав цього місяцями. Я повинен робити ці щотижневі/місячні звіти з розбивкою на 147+ робочих аркушів, і вони не отримають мені kutools. На цій ноті... Мені справді потрібно навчитися кодувати. :( Але дякую!
Цей коментар був мінімізований модератором на сайті
Привіт, у мене є аркуш із 65000 8 записів і 80-ма різними справами, тому в основному він повинен генерувати 6 різних аркушів. Я спробував запустити цей код, але він видає помилку виконання XNUMX Overflow. Чи можна змінити цей код, щоб вирішити мою проблему? Будь ласка, ваша допомога буде високо оцінена.
Цей коментар був мінімізований модератором на сайті
[quote]Привіт, у мене є аркуш із 65000 8 записів і 80-ма різними справами, тому в основному він повинен генерувати 6 різних аркушів. Я спробував запустити цей код, але він видає помилку виконання XNUMX Overflow. Чи можна змінити цей код, щоб вирішити мою проблему? Будь ласка, ваша допомога буде високо оцінена.Автор ACE[/quote] Спробуйте змінити Dim vcol, i As Integer на Dim vcol, i As Long
Цей коментар був мінімізований модератором на сайті
Привіт, я спробував змінити DIM vcol на LOng, і він працював добре, але раптом через помилку не вистачає пам'яті, щоб виконати цю дію, спробуйте використовувати менше даних або закрийте інші програми. Хоча у мене немає інших відкритих програм. У мене трохи більше 100 тисяч рядків і прибл. Розмір файлу 16 Мб. будь-яка допомога буде вдячна. Дякую Мустафа
Цей коментар був мінімізований модератором на сайті
Чудовий фрагмент коду - працює ідеально (якщо змінити змінні на ту, яка потрібна вашій електронній таблиці)
Цей коментар був мінімізований модератором на сайті
У мене є аркуш зі змінною кількістю рядків. Одна з колонок – це дати, починаючи з 2010 року. Інші стовпці – це назви фондів із даними NAV для кожного фонду відповідно до дати. Тому я не хочу розділяти стовпці на різні аркуші, я хочу розділити кожну НАЗВУ ФОНДУ на окремий аркуш із даними NAV на дату кінця місяця, а не на щоденні дати. Чи можна це зробити чи це неможливо?
Цей коментар був мінімізований модератором на сайті
У мене є робочий аркуш, який я використовую, і я намагаюся знайти код vba, який розпізнає назву облікового запису та скопіює конкретний рядок до нової книги та аркуша з такою ж назвою, чи можете ви допомогти?
Цей коментар був мінімізований модератором на сайті
Чудово! Код VBA працює, дякую! Мені потрібно, щоб ці вихідні аркуші були в окремих файлах Excel, а не в аркушах, і виникає помилка, коли я розділяю на багато багатьох аркушів.
Цей коментар був мінімізований модератором на сайті
Starscor і Tim, якщо ви хочете розділити аркуші файлу на кілька файлів, використовуючи імена рядків, на цій самій веб-сторінці є невеликий код макросу, який робить це, просто знайдіть «розділити книгу на окремі файли Excel». знайде це. Додайте код цього прикладу в кінець цього, видаливши, звичайно, дублікат кінцевого підпорядкуваного та підпорядкованого, і ви отримаєте по одному файлу для кожного.
Цей коментар був мінімізований модератором на сайті
чи може хтось допомогти мені, як відсортувати стовпці на різних аркушах в одній книзі одночасно, а також видалити дублікати на різних аркушах, оскільки у мене близько 65 аркушів в одній книзі
Цей коментар був мінімізований модератором на сайті
це так захоплююче! Дякую. Я шукав це деякий час.
Цей коментар був мінімізований модератором на сайті
Чудово - дякую, що поділилися цим. Навіть поширює виділення/форматування на нові аркуші!
There are no comments posted here yet
Load More
Залишайте свої коментарі
Публікація як гість
×
Оцініть цю публікацію:
0   Персонажі
Рекомендовані місця

Слідуй за нами

Copyright © 2009 - WWW.extendoffice.com. | Всі права захищені. На основі ExtendOffice. | Карта сайту
Microsoft та логотип Office є товарними знаками або зареєстрованими товарними знаками Microsoft Corporation у США та / або інших країнах.
Захищений Sectigo SSL