Советы по 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 Sub3. Затем нажмите F5 для запуска кода, и появится окно подсказки, напоминающее вам выбрать строку заголовка, а затем нажать кнопку OK. Смотрите скриншот:
4. Во втором окне подсказки выберите данные столбца, на основе которых вы хотите разделить, затем нажмите OK. Смотрите скриншот:
5. Все данные на активном листе разделены на несколько листов на основе значений столбцов. Полученные листы именуются в соответствии со значениями в разделенных ячейках и помещаются в конец книги. Смотрите скриншот:
Разделите данные на несколько листов на основе значения столбца с помощью Kutools for Excel
Kutools for Excel приносит умную функцию – Разделить данные прямо в вашу среду Excel. Разделение данных на несколько листов больше не является проблемой. Наш интуитивно понятный инструмент автоматически делит ваш набор данных на основе выбранного значения столбца или количества строк, гарантируя, что каждая часть информации находится именно там, где она вам нужна. Попрощайтесь с утомительной задачей ручной организации таблиц и воспользуйтесь более быстрым и безошибочным способом управления данными.
После установки Kutools for Excel, выберите диапазон данных и нажмите кнопку Кутулс Плюс > Разделить данные для открытия Разделить данные на несколько листов диалоговое окно.
- Выберите Конкретный столбец вариант в Сплит на основе раздел и выберите в раскрывающемся списке значение столбца, по которому вы хотите разделить данные.
- Если ваши данные имеют заголовки и вы хотите вставлять их в каждый новый разделенный лист, установите флажок У моих данных есть заголовки вариант. (Вы можете указать количество строк заголовков на основе ваших данных. Например, если ваши данные содержат два заголовка, введите 2.)
- Затем вы можете указать имена разделенных листов под Имя нового листа В разделе «Правила» укажите правило имен рабочих листов в раскрывающемся списке «Правила», вы можете добавить Префикс or Суффикс также для имен листов.
- Нажмите OK кнопка. Смотрите скриншот:
Теперь данные на листе разделены на несколько листов в новой книге.
Разделите данные на несколько книг на основе значения столбца с помощью кода 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
3. Затем нажмите F5 для запуска кода, и появится окно подсказки, напоминающее вам выбрать строку заголовка, а затем нажать кнопку OK. Смотрите скриншот:
4. Во втором окне подсказки выберите данные столбца, на основе которых вы хотите разделить, затем нажмите OK. Смотрите скриншот:
5. После разделения все данные на активном листе делятся на несколько книг на основе значений столбцов. Все разделенные книги сохраняются в указанной вами папке. Смотрите скриншот:
Статьи по теме:
- Разделить данные на несколько листов по количеству строк
- Эффективное разделение большого диапазона данных на несколько листов Excel на основе определенного количества строк может упростить управление данными. Например, разделение набора данных каждые 5 строк на несколько листов может сделать его более управляемым и организованным. В этом руководстве предлагаются два практических метода, позволяющих быстро и легко выполнить эту задачу.
- Объединение двух или более таблиц в одну на основе ключевых столбцов.
- Предположим, у вас есть три таблицы в книге, теперь вы хотите объединить эти таблицы в одну таблицу на основе соответствующих ключевых столбцов, чтобы получить результат, как показано на скриншоте ниже. Это может быть сложной задачей для большинства из нас, но, пожалуйста, не волнуйтесь, в этой статье я расскажу о некоторых методах решения этой проблемы.
- Разделить текстовые строки по разделителю на несколько строк
- Обычно вы можете использовать функцию «Текст в столбец», чтобы разделить содержимое ячейки на несколько столбцов с помощью определенного разделителя, такого как запятая, точка, точка с запятой, косая черта и т. д. Но иногда вам может потребоваться разделить содержимое ячейки с разделителями на несколько строк. и повторите данные из других столбцов, как показано ниже. Есть ли у вас хорошие способы решения этой задачи в Excel? В этом руководстве будут представлены некоторые эффективные методы выполнения этой работы в Excel.
- Разбить содержимое многострочной ячейки на отдельные строки/столбцы.
- Предположим, у вас есть многострочное содержимое ячейки, разделенное Alt + Enter, и теперь вам нужно разделить многострочное содержимое на отдельные строки или столбцы, что вы можете сделать? В этой статье вы узнаете, как быстро разделить содержимое многострочной ячейки на отдельные строки или столбцы.
Лучшие инструменты для офисной работы
Улучшите свои навыки работы с Excel с помощью Kutools for Excel и почувствуйте эффективность, как никогда раньше. Kutools for Excel предлагает более 300 расширенных функций для повышения производительности и экономии времени. Нажмите здесь, чтобы получить функцию, которая вам нужна больше всего...
Вкладка Office: интерфейс с вкладками в Office и упрощение работы
- Включение редактирования и чтения с вкладками в Word, Excel, PowerPoint, Издатель, доступ, Visio и проект.
- Открывайте и создавайте несколько документов на новых вкладках одного окна, а не в новых окнах.
- Повышает вашу продуктивность на 50% и сокращает количество щелчков мышью на сотни каждый день!
