KutoolsforOffice — Одно решение — пять мощных инструментов.Меньше усилий — больше результата.

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

АвторСяоянДата изменения

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

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

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

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

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

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

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

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

1. Удерживая клавиши ALT + F11, откройте окно Microsoft Visual Basic для приложений.

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

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, чтобы запустить код. Появится диалоговое окно с запросом выбрать строку заголовков — после этого нажмите ОК. См. скриншот:
разделение данных на листы с помощью кода VBA для выбора строки заголовка

4. Во втором диалоговом окне выберите столбец данных, на основе которого вы хотите выполнить разделение, затем нажмите ОК. См. скриншот:
разделение данных на листы с помощью кода VBA для выбора диапазона данных

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

 

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

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

Примечание:Чтобы использовать эту функцию, Разделить данныесначала необходимо загрузить Kutools для Excelи затем применять её быстро и легко.

После установки Kutools для Excel выберите диапазон данных и нажмите KUTOOLS PLUS > Разделить данные, чтобы открыть диалоговое окно Разделить данные на несколько листов.

  1. Выберите Укажите столбец в разделе Основа разделения и укажите значение столбца, по которому необходимо разделить данные, в поле «Раскрывающийся список».
  2. Если ваши данные содержат заголовки и вы хотите вставить их на каждый новый разделённый лист, установите флажок Включить заголовки (вы можете указать количество строк заголовков в зависимости от ваших данных; например, если в ваших данных два заголовка, введите 2).
  3. Затем вы можете задать правила именования листов: в разделе Имя создаваемых листов выберите правило «Имя листа» из выпадающего списка «Правила», а также добавьте Префикс или Суффикс к именам листов.
  4. Нажмите кнопку ОК. См. скриншот:
    разделение данных на листы с помощью Kutools для настройки операций

Теперь данные с листа разделены по нескольким листам в новой рабочей книге.
разделение данных на листы с помощью Kutools для получения результата

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

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

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

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

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\».

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

4. Во втором диалоговом окне Пожалуйста, выберите столбец данных, по которому вы хотите Основа разделения, затем нажмите ОК. См. скриншот:
разделение данных на разные книги с помощью кода VBA для выбора диапазона данных

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

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

  • Разделить данные на несколько листов по количеству строк
  • Эффективное разделение большого Диапазон данных на несколько листов Excel на основе заданного количества строк может упростить управление данными. Например, разбивка набора данных каждые 5 строк на отдельные листы делает его более удобным и упорядоченным. В этом руководстве представлены два практических метода для быстрого и легкого выполнения этой задачи.
  • Объединение двух или более таблиц в одну на основе Ключевой столбец
  • Предположим, у вас в книге есть три таблицы, и вы хотите объединить их в одну на основе соответствующего ключевого столбца, чтобы получить результат, как на скриншоте ниже. Для большинства из нас это может показаться непростой задачей — но не переживайте: в этой статье я расскажу о нескольких эффективных способах её решения.
  • Разделение текстовых строк по разделителю на несколько строк
  • Обычно функция «Текст по столбцам» позволяет легко разбить содержимое ячеек на несколько столбцов с помощью заданного разделителя — например, запятой, точки, точки с запятой, слэша и т.д. Однако иногда возникает задача разделить содержимое ячейки с такими разделителями не по столбцам, а по строкам, одновременно продублировав данные из других столбцов, как показано на скриншоте ниже. Знаете ли вы эффективные способы выполнить это в Excel? В этом руководстве мы расскажем о нескольких практичных методах решения этой задачи.

Лучшие инструменты повышения продуктивности в Office

🤖KUTOOLS AI Помощник: Преобразуйте Анализ данных с помощью:Интеллектуального выполнения   |  Генерации кода|  Создания пользовательские формулы  |  Анализа данных и построения диаграмм|  Вызова Расширенные функции…
Популярные функции:Поиск, выделение или Отметить дубликаты   |  Удалить пустые строки   |  Объединить столбцы или ячеек без потери данных   |  Округление без использования формул…
Супер ПОИСК:VLookup по нескольким критериям  |  VLookup по нескольким значениям  |   VLookup по нескольким листам   |   Распознавание нечетких соответствий…
Расширенный раскрывающийся список:Быстрое создание выпадающего списка   |  Зависимый выпадающий список   |  Выпадающий список с множественным выбором…
Управление столбцами:Добавление заданного количества столбцов|Перемещение столбцов|Переключение видимости скрытых столбцов|Сравнение диапазонов и столбцов…
Избранные функции:Сетка фокусировки   |  Просмотр дизайна   |Улучшенная строка формулы   | Управление рабочими книгами и листами   |  Библиотека ресурсов(автотекст)|  Выбор даты   |  Объединить листы  |  Шифрование/Расшифровать ячейки   | Отправка писем по списку   |  Супер фильтр   |   Специальный фильтр(Фильтр ячеек с жирным шрифтом/курсив/зачёркивание…) …
Лучшие наборы инструментов 15:12 Текстовыеинструменты(Добавить текст,Удалить определенные символы, …)|   50+Типыдиаграмм(Диаграмма Ганта, …)|   40+ Практические формулы(Рассчитать возраст на основе даты рождения, …)|   19 Инструментывставки(Вставить QR-код,Вставка изображения по пути, …)|   12 Инструментыпреобразования(Преобразовать в слова,Конвертация валют, …)|   7 Объединить и разделитьИнструменты(Расширенное объединение строк,Разделить ячейки, …)|… и многое другое
Используйте Kutools на предпочитаемом языке — поддержка английского, испанского, немецкого, французского, китайского и ещё 40+ языков!

Раскройте весь потенциал Excel с помощью Kutools для Excel и ощутите эффективность как никогда раньше.Kutools для Excel предлагает более 300 расширенных функций для повышения продуктивности и Экономия времени.Нажмите здесь, чтобы получить нужную Вам функцию…

Office Tab добавляет в Office вкладки и значительно упрощает Вашу работу

  • Включите редактирование и чтение во вкладках в Word, Excel, PowerPoint, Publisher, Access, Visio и Project.
  • Открывайте и создавайте несколько документов во вкладках одного окна — вместо того чтобы использовать отдельные окна.
  • Повышает вашу продуктивность на 50 % и экономит сотни кликов мышью каждый день!

Все надстройки Kutools — один установщик

Kutools for Office — набор надстроек для Excel, Word, Outlook и PowerPoint, а также Office Tab Pro, идеально подходящий командам, работающим с разными приложениями Office.

ExcelWordOutlookTabsPowerPoint
  • Единый комплект— надстройки для Excel, Word, Outlook и PowerPoint + Office Tab Pro
  • Один установщик, одна лицензия— настройка занимает считанные минуты (готово для MSI)
  • Лучше работать вместе— оптимизированная продуктивность во всех приложениях Office
  • 30-дневная полнофункциональная пробная версия— без регистрации и кредитной карты
  • Лучшее соотношение цены и качества— экономия по сравнению с покупкой отдельных надстроек