Перейти к основному содержанию
 

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

Автор: Сяоян Последнее изменение: 2024 июля 08 г.

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

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

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

 

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

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

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

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

  1. Выберите Конкретный столбец вариант в Сплит на основе раздел и выберите в раскрывающемся списке значение столбца, по которому вы хотите разделить данные.
  2. Если ваши данные имеют заголовки и вы хотите вставлять их в каждый новый разделенный лист, установите флажок У моих данных есть заголовки вариант. (Вы можете указать количество строк заголовков на основе ваших данных. Например, если ваши данные содержат два заголовка, введите 2.)
  3. Затем вы можете указать имена разделенных листов под Имя нового листа В разделе «Правила» укажите правило имен рабочих листов в раскрывающемся списке «Правила», вы можете добавить Префикс or Суффикс также для имен листов.
  4. Нажмите OK кнопка. Смотрите скриншот:
    разделить данные на листы с помощью 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 для запуска кода, и появится окно подсказки, напоминающее вам выбрать строку заголовка, а затем нажать кнопку OK. Смотрите скриншот:
разделить данные на книги с помощью кода VBA, чтобы выбрать строку заголовка

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

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

Статьи по теме:

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

Лучшие инструменты для офисной работы

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

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


Вкладка Office: интерфейс с вкладками в Office и упрощение работы

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