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

Автор: Сяоян Остання зміна: 2024-04-26

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

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

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

За допомогою коду VBA
Використовуючи потужну функцію - Kutools для Excel

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

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

Зазвичай ви можете спочатку відсортувати список даних, а потім скопіювати та вставити їх один за одним на інші нові аркуші. Але для цього знадобиться ваше терпіння, щоб копіювати та вставляти кілька разів. У цьому розділі ми представимо два прості методи ефективного вирішення цього завдання в Excel, що заощадить ваш час і зменшить можливість помилок.

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

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

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

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

Kutools для Excel приносить розумну функцію – Розділити дані прямо у ваше середовище Excel. Розбиття даних на кілька аркушів більше не є проблемою. Наш інтуїтивно зрозумілий інструмент автоматично розподіляє ваш набір даних на основі вибраного значення стовпця або кількості рядків, гарантуючи, що кожна частина інформації знаходиться саме там, де вона вам потрібна. Попрощайтеся з стомлюючою роботою впорядкування електронних таблиць вручну та скористайтеся швидшим і безпомилковим способом керування даними.

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

після установки Kutools для Excel, виберіть діапазон даних і натисніть Kutools Plus > Розділити дані відкрити Розділіть дані на кілька аркушів діалогове вікно.

Select Конкретний стовпець опція в Спліт на основі і виберіть зі спадного списку значення стовпця, на основі якого потрібно розділити дані.
Якщо ваші дані мають заголовки, і ви хочете вставляти їх у кожен новий розділений аркуш, перевірте Мої дані мають заголовки варіант. (Ви можете вказати кількість рядків заголовків на основі своїх даних. Наприклад, якщо ваші дані містять два заголовки, введіть 2.)
Потім ви можете вказати назви розділених аркушів у розділі Назва нових аркушів у розділі «Правила» вкажіть правило імен робочих аркушів зі спадного списку «Правила», ви можете додати префікс or суфікс для назв аркушів також.
Натисніть OK кнопку. Дивіться знімок екрана:

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

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

Іноді замість того, щоб розділяти дані на кілька робочих аркушів, може бути кориснішим розділити дані на окремі робочі книги на основі ключового стовпця. Ось покрокова інструкція щодо використання коду VBA для автоматизації процесу поділу даних на кілька робочих книг на основі певного значення стовпця.

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

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

Sub SplitDataByColToWorkbooks()
    ' Updateby Extendoffice
    Dim lr As Long
    Dim ws As Worksheet
    Dim vcol, i As Integer
    Dim myarr As Variant
    Dim title As String
    Dim titlerow As Integer
    Dim xTRg As Range
    Dim xVRg As Range
    Dim xWS As Workbook
    Dim savePath As String
    ' Set the directory to save new workbooks
    savePath = "C:\Users\AddinsVM001\Desktop\multiple files\" ' Modify this path as needed
    Application.DisplayAlerts = False
    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.Address(False, False)
    titlerow = xTRg.Row
    ws.Columns(vcol).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=ws.Cells(1, ws.Columns.Count), Unique:=True
    myarr = Application.Transpose(ws.Cells(1, ws.Columns.Count).Resize(ws.Cells(ws.Rows.Count, ws.Columns.Count).End(xlUp).Row).Value)
    ws.Cells(1, ws.Columns.Count).Resize(ws.Cells(ws.Rows.Count, ws.Columns.Count).End(xlUp).Row).ClearContents
    For i = 2 To UBound(myarr)
        Set xWS = Workbooks.Add
        ws.Range(title).AutoFilter Field:=vcol, Criteria1:=myarr(i)
        ws.Range("A" & titlerow & ":A" & lr).SpecialCells(xlCellTypeVisible).EntireRow.Copy
        xWS.Sheets(1).Cells(1, 1).PasteSpecial Paste:=xlPasteAll
        xWS.SaveAs Filename:=savePath & myarr(i) & ".xlsx"

        xWS.Close SaveChanges:=False
    Next i
    ws.AutoFilterMode = False
    Application.DisplayAlerts = True
    ws.Activate
End Sub

примітки: у наведеному вище коді вам слід змінити шлях до файлу на свій власний, де зберігатимуться розділені робочі книги в цьому сценарії: savePath = "C:\Users\AddinsVM001\Desktop\multiple files\".

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

Статті по темі:

Розділіть дані на кілька аркушів за кількістю рядків
Ефективний розподіл великого діапазону даних на кілька аркушів Excel на основі певної кількості рядків може спростити керування даними. Наприклад, розділення набору даних кожні 5 рядків на кілька аркушів може зробити його більш керованим і організованим. Цей посібник пропонує два практичних способи швидкого та легкого виконання цього завдання.

Об’єднайте дві або більше таблиць в одну на основі ключових стовпців
Припустимо, у вас є три таблиці в книзі, тепер ви хочете об'єднати ці таблиці в одну таблицю на основі відповідних стовпців ключів, щоб отримати результат, як показано на знімку екрана нижче. Для більшості з нас це може бути клопітким завданням, але, будь ласка, не хвилюйтеся, у цій статті я представив деякі методи вирішення цієї проблеми.

Розділіть текстові рядки роздільником на кілька рядків
Зазвичай ви можете використовувати функцію «Текст у стовпець», щоб розділити вміст клітинки на кілька стовпців за певним роздільником, таким як кома, крапка, крапка з комою, скісна риска тощо. Але інколи вам може знадобитися розділити вміст клітинки на кілька рядків. і повторіть дані з інших стовпців, як показано на знімку екрана нижче. Чи є у вас хороші способи вирішення цього завдання в Excel? Цей підручник познайомить вас з деякими ефективними методами виконання цієї роботи в Excel.

Розділіть вміст багаторядкової клітинки на окремі рядки/стовпці
Припустимо, що у вас є багаторядковий вміст комірки, розділений Alt + Enter, і тепер вам потрібно розділити багаторядковий вміст на окремі рядки або стовпці, що ви можете зробити? У цій статті ви дізнаєтеся, як швидко розділити вміст багаторядкової клітинки на окремі рядки або стовпці.

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

🤖	Kutools AI Aide: Революціонізуйте аналіз даних на основі: Інтелектуальне виконання \| Згенерувати код \| Створення спеціальних формул \| Аналізуйте дані та створюйте діаграми \| Викликати функції Kutools...
	Популярні функції: Знайдіть, виділіть або визначте дублікати \| Видалити порожні рядки \| Об’єднайте стовпці або клітинки без втрати даних \| Раунд без Формули ...
	Супер пошук: VLookup за кількома критеріями \| Багатозначний VLookup \| VLookup на кількох аркушах \| Нечіткий пошук ....
	Розширений розкривний список: Швидке створення випадаючого списку \| Залежний спадний список \| Виберіть розкривний список, що вибирається ....
	Менеджер колонок: Додайте конкретну кількість стовпців \| Перемістити стовпці \| Перемкнути статус видимості прихованих стовпців \| Порівняйте діапазони та стовпці ...
	Особливості: Фокус сітки \| Перегляд дизайну \| Велика панель формул \| Диспетчер робочих книг і аркушів \| Бібліотека ресурсів (автотекст) \| Вибір дати \| Об’єднайте робочі аркуші \| Шифрування/розшифрування клітинок \| Надсилайте листи за списком \| Супер фільтр \| Спеціальний фільтр (фільтр жирний/курсив/закреслений...) ...
	Топ-15 наборів інструментів: 12 текст Tools (додати текст, Видалити символи, ...) \| 50 + Графік типи (діаграма Ганта, ...) \| 40+ Практичний Формули (Розрахуйте вік на основі дня народження, ...) \| 19 вставка Tools (Вставте QR-код, Вставити зображення зі шляху, ...) \| 12 Перетворення Tools (Числа до слів, Валютна конверсія, ...) \| 7 Злиття та розділення Tools (Розширені комбіновані ряди, Розділені клітини, ...) \| ... і більше

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

Опис

Детальніше ... Завантажити безкоштовно...

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

Увімкніть редагування та читання на вкладках у Word, Excel, PowerPoint, Publisher, Access, Visio та Project.
Відкривайте та створюйте кілька документів на нових вкладках того самого вікна, а не в нових вікнах.
Збільшує вашу продуктивність на 50% та зменшує сотні клацань миші для вас щодня!

Детальніше ... Завантажити безкоштовно...

Comments (312)

Rated 5 out of 5 · 2 ratings

How to split data into multiple workbook based on column & Row In Excel?

about 2 months ago

#44807