Разбить лист Excel на несколько листов по значению столбца

Разбить лист Excel на несколько листов по значению столбца — значит разнести строки таблицы так, чтобы у каждого города, менеджера или месяца появился свой лист. Встроенной кнопки для этого в Excel нет. Самый быстрый способ — процедура надстройки !SEMTools «Разбить/Сцепить → Разбить → Текущий лист на разные листы по значению колонки»: выделите столбец с ключом, ответьте, копировать ли заголовки, — и листы создадутся сами. Без надстройки помогут сводная таблица, функция ФИЛЬТР или короткий макрос.

Ниже — все способы на одном примере: журнал продаж, который нужно разделить по городам.

Разбить лист на листы в !SEMTools

1. Откройте лист, который нужно разделить.
2. На вкладке !SEMTools нажмите Разбить/Сцепить → Разбить → Текущий лист на разные листы по значению колонки.
3. В окне выбора диапазона выделите ячейки столбца-ключа без заголовка, например B2:B6, и нажмите «ОК».
4. На вопрос «Скопировать заголовки на каждый лист?» ответьте «Да», если первая строка листа — шапка таблицы.

Надстройка создаст по листу на каждое уникальное значение — «Москва», «Казань», «Тула» — и перенесёт на него все строки с этим значением, все столбцы таблицы. Исходный лист не меняется.

Что важно знать о процедуре:

• Переносятся значения, а не формулы и оформление: формулы на новых листах превратятся в результаты, форматирование и ширину столбцов придётся настроить заново.
• Имя листа берётся из значения ячейки, но не длиннее 30 символов. Для пустых ячеек создаётся лист «blank value».
• Значения с символами, недопустимыми в именах листов (: \ / ? * [ ]), и значения, совпадающие с именами уже существующих листов, вызовут ошибку. Перед разбивкой замените такие символы или переименуйте листы — поможет массовая замена.
• Если делите по датам, сначала сделайте рядом текстовый столбец формулой =ТЕКСТ(A2;"ДД.ММ.ГГГГ") или =ТЕКСТ(A2;"ММММ ГГГГ") и разбивайте по нему.
• Процедура есть в версиях !SEMTools Lite и Pro.

Без надстройки: сводная таблица «Показать страницы фильтра отчёта»

В Excel есть малоизвестная команда, которая создаёт по листу на каждое значение поля. Работает она со сводными таблицами:

1. Выделите таблицу и выберите Вставка → Сводная таблица.
2. Перетащите поле, по которому делите (Город), в область Фильтры, а нужные столбцы — в область Строки и Значения.
3. На вкладке Анализ сводной таблицы нажмите стрелку у кнопки «Параметры» → Показать страницы фильтра отчёта и выберите поле.

Excel создаст лист на каждый город со своей сводной таблицей. Ограничение: на листах будут сводные таблицы, а не исходные строки. Чтобы получить что-то похожее на исходную таблицу, в конструкторе выберите макет «В табличной форме», «Повторять все подписи элементов» и отключите промежуточные итоги.

Формула ФИЛЬТР: лист, который обновляется сам

В Excel 365 и 2021 можно не копировать данные, а вывести их формулой. Создайте лист, в ячейку A1 впишите значение ключа, а в A2 — формулу =ФИЛЬТР(Продажи!A2:D100;Продажи!B2:B100=A1). Строки этого города разольются ниже и будут обновляться при изменении исходного листа.

Минус способа — листы и формулы для каждого значения придётся создать вручную. Для 3–5 значений это удобно, для 50 — уже нет.

Фильтр и копирование вручную

Классический способ без формул: включите фильтр (Ctrl+Shift+L), отберите в столбце одно значение, выделите видимые строки, скопируйте (Excel копирует только видимые строки отфильтрованного диапазона), вставьте на новый лист и повторите для следующего значения. Подходит, когда значений два-три.

Макрос VBA для разбивки листа

Если разбивать таблицы приходится регулярно, а надстройки нет, используйте макрос. Он создаёт лист на каждое значение столбца B, копирует туда заголовок и строки, причём вместе с форматированием. Номер столбца-ключа задаётся в строке с KEY_COL.

Sub SplitSheetByColumn()
    ' Разносит строки активного листа по новым листам по значениям столбца B
    Dim src As Worksheet, ws As Worksheet, lastRow As Long, r As Long, key As String
    Const KEY_COL As Long = 2          ' номер столбца, по которому делим
    Set src = ActiveSheet
    lastRow = src.Cells(src.Rows.Count, KEY_COL).End(xlUp).Row
    Application.ScreenUpdating = False
    For r = 2 To lastRow
        key = Left(CStr(src.Cells(r, KEY_COL).Value), 31)
        If key = "" Then key = "Пусто"
        Set ws = Nothing
        On Error Resume Next: Set ws = Worksheets(key): On Error GoTo 0
        If ws Is Nothing Then
            Set ws = Worksheets.Add(After:=Worksheets(Worksheets.Count))
            ws.Name = key
            src.Rows(1).Copy ws.Rows(1)       ' заголовки
        End If
        src.Rows(r).Copy ws.Cells(ws.Cells(ws.Rows.Count, 1).End(xlUp).Row + 1, 1)
    Next r
    Application.ScreenUpdating = True
End Sub

Вставьте код в модуль (Alt+F11 → Insert → Module) и запустите на нужном листе. Чтобы макрос был доступен во всех книгах, сохраните его в личной книге макросов.

Как разделить листы на отдельные файлы

Если после разбивки каждый лист нужно отправить отдельным файлом, сохраните книгу и запустите этот макрос — он сохранит каждый лист в отдельную книгу .xlsx рядом с исходным файлом:

Sub SaveSheetsAsFiles()
    ' Сохраняет каждый лист книги отдельным файлом рядом с исходной книгой
    Dim ws As Worksheet
    Application.ScreenUpdating = False
    For Each ws In ThisWorkbook.Worksheets
        ws.Copy
        ActiveWorkbook.SaveAs ThisWorkbook.Path & "\" & ws.Name & ".xlsx", FileFormat:=51
        ActiveWorkbook.Close SaveChanges:=False
    Next ws
    Application.ScreenUpdating = True
End Sub

Вручную то же делается командой «Переместить или скопировать» на ярлыке листа: в поле «В книгу» выберите «(новая книга)» и поставьте галочку «Создать копию».

Разделить таблицу на страницы и лист на области

Иногда под «разделить лист» имеют в виду другое:

• Разделить таблицу на страницы для печати: выделите строку, с которой должна начаться новая страница, и выберите Разметка страницы → Разрывы → Вставить разрыв страницы. Посмотреть и подвинуть границы страниц удобно в режиме Вид → Страничный режим.
• Разделить окно на области, чтобы видеть разные части листа одновременно: выделите ячейку и нажмите Вид → Разделить. Лист разделится на две или четыре прокручиваемые области; повторное нажатие убирает разделение. Чтобы просто зафиксировать шапку, используйте Вид → Закрепить области.
• Разделить таблицу на две части по строкам или столбцам: вырежьте нужную часть (Ctrl+X) и вставьте на другой лист — или используйте способы выше, если граница определяется значением в столбце.

Обратная задача

Чтобы снова собрать листы в один, используйте процедуру «Объединить все листы в один». А если нужно разделить не таблицу, а содержимое ячеек — смотрите статьи о том, как разделить ячейку в Excel и как разделить ячейку на строки.

Почему лист не разбивается — частые ошибки

• Ошибка при создании листа. В значении есть символы : \ / ? * [ ] или оно совпадает с именем существующего листа — исправьте значения или переименуйте лист.
• Появился лист с названием заголовка столбца. В диапазон попала строка заголовков — выделяйте ячейки начиная со второй строки.
• Пропали формулы и оформление. Процедура переносит значения; если нужны форматы, используйте макрос из статьи.
• Разные листы для одного города. Значения отличаются пробелами или регистром («Москва» и «Москва ») — сначала удалите лишние пробелы.
• Слишком много листов. У ключа сотни уникальных значений — проверьте, тот ли столбец выбран, или сгруппируйте значения заранее.

Как разбить лист Excel по значению столбца — частые вопросы

Как разделить таблицу Excel на несколько листов по значению столбца?

В !SEMTools: Разбить/Сцепить → Разбить → «Текущий лист на разные листы по значению колонки». Без надстройки — сводная таблица с командой «Показать страницы фильтра отчёта», формула ФИЛЬТР на каждом листе или макрос VBA.

Есть ли в Excel встроенная функция разбивки листа?

Отдельной кнопки нет. Ближе всего команда сводной таблицы «Показать страницы фильтра отчёта», но она создаёт сводные таблицы, а не копии строк.

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

Командой «Переместить или скопировать» → «(новая книга)» для каждого листа или макросом, который сохраняет каждый лист отдельным файлом.

Как разделить таблицу Excel на страницы?

Разметка страницы → Разрывы → Вставить разрыв страницы; границы удобно двигать в режиме «Страничный».

Как разделить лист Excel на области?

Вид → Разделить: окно делится на прокручиваемые области в точке выделенной ячейки.

Итоги: как разбить лист Excel на несколько листов

Надеюсь, на этой странице вы нашли то, что искали и смогли разбить таблицу по листам. Другие инструменты для разделения и объединения данных собраны в разделе «Разбить / Сцепить».