Советы по Excel: Разделение данных на несколько листов / книг на основе значения столбца

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

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

Разделение данных на несколько листов на основе значения столбца

• Используя код VBA
• Используя мощную функцию - Kutools для Excel

Разделение данных на несколько книг на основе значения столбца с помощью кода VBA

Разделение данных на несколько листов на основе значения столбца

Обычно вы можете сначала отсортировать список данных, а затем копировать и вставлять их по одному в другие новые листы. Но это потребует вашего терпения для многократного копирования и вставки. В этом разделе мы представим два простых метода для эффективного выполнения этой задачи в Excel, экономя ваше время и снижая вероятность ошибок.

Разделение данных на несколько листов на основе значения столбца с помощью кода VBA

1. Удерживайте клавиши ALT + F11, чтобы открыть окно Microsoft Visual Basic for Applications.

2. Нажмите Insert > Module и вставьте следующий код в окно модуля.

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 добавляет интеллектуальную функцию – Split Data прямо в вашу среду Excel. Разделение данных на несколько листов больше не является проблемой. Наш интуитивно понятный инструмент автоматически делит ваш набор данных на основе выбранного значения столбца или количества строк, гарантируя, что каждая часть информации находится именно там, где она нужна. Прощайтесь с утомительной задачей ручной организации ваших таблиц и используйте более быстрый, безошибочный способ управления данными.

После установки Kutools для Excel выберите диапазон данных, затем нажмите Kutools Plus > Split Data, чтобы открыть диалоговое окно Split Data into multiple worksheets.

1. Выберите опцию Specific column в разделе Split based on и выберите значение столбца, на основе которого вы хотите разделить данные, из раскрывающегося списка.
2. Если ваши данные содержат заголовки и вы хотите вставить их в каждый новый разделенный лист, отметьте опцию My data has headers. (Вы можете указать количество строк заголовков на основе ваших данных. Например, если ваши данные содержат два заголовка, введите 2.)
3. Затем вы можете указать имена разделенных листов, в разделе New worksheets name укажите правило именования листов из выпадающего списка Rules, вы также можете добавить префикс или суффикс для имен листов.
4. Нажмите кнопку OK . См. скриншот:
   

Теперь данные в листе разделяются на несколько листов в новой книге.

Разделение данных на несколько книг на основе значения столбца с помощью кода VBA

Иногда вместо разделения данных на несколько листов может быть более выгодным разделить данные на отдельные книги на основе ключевого столбца. Вот пошаговое руководство о том, как использовать код VBA для автоматизации процесса разделения данных на несколько книг на основе конкретного значения столбца.

1. Удерживайте клавиши ALT + F11, чтобы открыть окно Microsoft Visual Basic for Applications.

2. Нажмите Insert > Module и вставьте следующий код в окно модуля.

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

3. Затем нажмите F5 для запуска кода, и появится диалоговое окно, напоминающее вам выбрать строку заголовков, после чего нажмите OK. См. скриншот:

4. Во втором диалоговом окне выберите данные столбца, которые вы хотите разделить, затем нажмите OK. См. скриншот:

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

Связанные статьи: