Как создать новые листы для каждой строки в Excel?

При работе с управлением данными или задачами создания отчетов иногда может потребоваться разделить каждую запись из таблицы на отдельные рабочие листы. Например, если вы ведете список данных студентов в Excel, где каждая строка представляет информацию о разных студентах, вам может понадобиться создать отдельный лист для каждого студента, содержащий его конкретные данные. Или вы можете просто захотеть разделить каждую строку независимо от её содержания, чтобы каждая была помещена в новый рабочий лист. Создание и копирование данных вручную может быть трудоемким и подверженным ошибкам, особенно при работе с большим набором данных. Автоматизация этого процесса экономит значительное время и снижает риск ошибок. Данное руководство проведет вас через два практических решения для создания новых листов для каждой строки в Excel и объяснит плюсы и минусы каждого метода, чтобы помочь выбрать лучший подход для вашего случая.

Создание новых листов для каждой строки с помощью кода VBA
Создание новых листов для каждой строки с помощью функции Разделить данные Kutools для Excel

Создание новых листов для каждой строки с помощью кода VBA

Использование VBA (Visual Basic для приложений) является эффективным способом автоматизации задач Excel, которые иначе были бы утомительными и повторяющимися. С помощью VBA можно быстро создавать новые рабочие листы для каждой строки ваших данных, либо группируя строки на основе столбца (например, имени студента), либо создавая один лист для каждой строки независимо от содержимого. Этот подход идеален для пользователей, знакомых с редактором VBA и предпочитающих высокую степень настройки. Однако для этого требуется редактирование и запуск кода, поэтому он может не подойти пользователям, не имеющим опыта работы с VBA или не обладающим разрешениями на выполнение макросов в своей среде.

1. Нажмите "Alt" + "F11", чтобы открыть окно Microsoft Visual Basic for Applications, которое позволяет получить доступ к коду VBA для автоматизации операций Excel.

2. В окне Microsoft Visual Basic for Applications нажмите Вставка > Модуль, чтобы добавить новый модуль кода. Затем скопируйте и вставьте следующий код в окно Модуля.

Код VBA: создание нового листа для каждой строки на основе столбца

Sub parse_data()
'Update by Extendoffice 2018/3/2
    Dim xRCount As Long
    Dim xSht As Worksheet
    Dim xNSht As Worksheet
    Dim I As Long
    Dim xTRrow As Integer
    Dim xCol As New Collection
    Dim xTitle As String
    Dim xSUpdate As Boolean
    Set xSht = ActiveSheet
    On Error Resume Next
    xRCount = xSht.Cells(xSht.Rows.Count, 1).End(xlUp).Row
    xTitle = "A1:C1"
    xTRrow = xSht.Range(xTitle).Cells(1).Row
    For I = 2 To xRCount
        Call xCol.Add(xSht.Cells(I, 1).Text, xSht.Cells(I, 1).Text)
    Next
    xSUpdate = Application.ScreenUpdating
    Application.ScreenUpdating = False
    For I = 1 To xCol.Count
        Call xSht.Range(xTitle).AutoFilter(1, CStr(xCol.Item(I)))
        Set xNSht = Nothing
        Set xNSht = Worksheets(CStr(xCol.Item(I)))
        If xNSht Is Nothing Then
            Set xNSht = Worksheets.Add(, Sheets(Sheets.Count))
            xNSht.Name = CStr(xCol.Item(I))
        Else
            xNSht.Move , Sheets(Sheets.Count)
        End If
        xSht.Range("A" & xTRrow & ":A" & xRCount).EntireRow.Copy xNSht.Range("A1")
        xNSht.Columns.AutoFit
    Next
    xSht.AutoFilterMode = False
    xSht.Activate
    Application.ScreenUpdating = xSUpdate
End Sub

Объяснение параметров и советы по использованию:

• A1:C1 — это диапазон заголовков для вашей таблицы данных. При необходимости измените эту ссылку, чтобы она соответствовала фактической области заголовка ваших данных.
• Код собирает уникальные значения из указанного столбца (по умолчанию, столбец A). Для каждого уникального значения создается отдельный рабочий лист с соответствующими строками.
• Если ваша таблица шире или начинается с другой строки, соответственно обновите xTitle и ссылки на ячейки.
• Если ваш рабочий лист содержит формулы, которые ссылаются на другие листы, проверьте ссылки после разделения.

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

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

Код VBA: Прямое создание нового листа для каждой строки

Sub RowToSheet()
	Dim xRow As Long
	Dim I As Long
	With ActiveSheet
		xRow = .Range("A" & Rows.Count).End(xlUp).Row
		For I = 1 To xRow
			Worksheets.Add(, Sheets(Sheets.Count)).Name = "Row " & I
			.Rows(I).Copy Sheets("Row " & I).Range("A1")
		Next I
	End With
End Sub

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

Устранение неполадок и практические советы:
- Если вы столкнулись с ошибкой, такой как «Индекс вне диапазона», убедитесь, что названия ваших листов не содержат недопустимых символов или дубликатов.
- Большие наборы данных могут привести к большому количеству листов, что может замедлить работу Excel или затруднить навигацию. Рассмотрите, необходимо ли разделять по строкам для очень больших таблиц.
- Всегда сохраняйте резервную копию ваших данных перед запуском кода VBA, чтобы предотвратить случайную потерю данных.

Преимущества: Бесплатно; высокая степень настройки.
Недостатки: Требуется включение VBA/макросов и некоторое знакомство с редактором VBA; возможны ошибки кода при работе со сложными или необычными данными.
Подходит для: Пользователи, которым нужна гибкость скриптов, и для одноразовых или нерегулярных задач.

Создание новых листов для каждой строки с помощью функции Разделить данные Kutools для Excel

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

1. Выберите таблицу, содержащую данные, которые вы хотите разделить на отдельные рабочие листы. Затем нажмите вкладку Kutools Plus и выберите Разделить данные. См. скриншот ниже для наглядности:

2. В диалоговом окне Разделить данные на несколько рабочих листов выберите соответствующие настройки на основе ваших потребностей в разделении.

A. Чтобы создать новые листы на основе значений столбцов:

B. Чтобы напрямую создать новые листы для каждой строки:

После выполнения будет создана новая книга, содержащая все новые рабочие листы — каждый заполнен согласно выбранным вами настройкам.

Создание новых листов для каждой строки на основе значений столбцов:

Создание нового листа для каждой строки без учета значений столбцов:

Меры предосторожности и практические советы:
- Перед использованием функции Разделить данные убедитесь, что в вашей таблице нет пустых строк, так как они могут привести к созданию пустых листов.
- Проверьте соглашения об именовании листов, чтобы избежать дублирующихся или недопустимых названий рабочих листов; Kutools автоматически скорректирует название в случае конфликта.
- Процесс может создать большое количество рабочих листов, если ваша таблица очень большая, поэтому учтите это перед началом.

Преимущества: Легко использовать без необходимости написания кода; подходит для пакетной обработки и частых задач.
Недостатки: Требуется установка надстройки Kutools.
Подходит для: Пользователи Excel всех уровней, особенно те, кто работает с регулярным сегментированием данных.

Демонстрация: Создание новых листов для каждой строки с помощью функции Разделить данные Kutools для Excel