Как в excel таблицу разбить на страницы: Не добавляются разрывы страниц в Excel. Решение

Содержание

Разделение таблицы по листам

24326 16.04.2021 Скачать пример

В Microsoft Excel есть много инструментов для сборки данных из нескольких таблиц (с разных листов или из разных файлов): прямые ссылки, функция ДВССЫЛ (INDIRECT), надстройки Power Query и Power Pivot и т.д. С этой стороны баррикад всё выглядит неплохо.

Но если вы нарвётесь на обратную задачу — разнесения данных из одной таблицы на разные листы — то всё будет гораздо печальнее. На сегодняшний момент цивилизованных встроенных инструментов для такого разделения данных в арсенале Excel, к сожалению, нет. Так что придется задействовать макрос на Visual Basic, либо воспольоваться связкой макрорекордер+Power Query с небольшой «доработкой напильником» после.

Давайте подробно рассмотрим, как это можно реализовать.

Постановка задачи

Имеем в качестве исходных данных вот такую таблицу размером больше 5000 строк по продажам:

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

Подготовка

Чтобы не усложнять код макроса и сделать его максимально простым для понимания, выполним пару подготовительных действий.

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

Данные — Удалить дубликаты (Data — Remove duplicates) для копии столбца Город или функцию УНИК (UNIQUE) — если у вас последняя версия Excel 365.

Поскольку новые листы в Excel по умолчанию создаются перед (левее) текущего (предыдущего), то имеет смысл также отсортировать города в этом справочнике по убыванию (от Я до А) — тогда после создания листы-города расположатся по алфавиту.

Во-вторых, преобразуем обе таблицы в динамические («умные»), чтобы с ними было проще работать. Используем команду Главная — Форматировать как таблицу (Home — Format as Table) или сочетание клавиш Ctrl+T. На появившейся вкладке Конструктор (Design) назовём их таблПродажи и таблГорода, соответственно:

Способ 1. Макрос для деления по листам

На вкладке Разработчик (Developer) нажмите на кнопку Visual Basic или используйте сочетание клавиш Alt+F11. В открывшемся окне редактора макросов вставьте новый пустой модуль через меню Insert — Module и скопируйте туда следующий код:

Sub Splitter()
    For Each cell In Range("таблГорода")
        Range("таблПродажи"). AutoFilter Field:=3, Criteria1:=cell.Value
        Range("таблПродажи[#All]").SpecialCells(xlCellTypeVisible).Copy
        Sheets.Add
        ActiveSheet.Paste
        ActiveSheet.Name = cell.Value
        ActiveSheet.UsedRange.Columns.AutoFit
    Next cell
    Worksheets("Данные").ShowAllData
End Sub

Здесь с помощью цикла For Each … Next реализован проход по ячейкам справочника таблГорода

, где для каждого города происходит его фильтрация (метод AutoFilter) в исходной таблице продаж и затем копирование результатов на новый созданный лист. Попутно созданный лист переименовывается в то же имя города и на нем включается автоподбор ширины столбцов для красоты.

Запустить созданный макрос в Excel можно на вкладке Разработчик кнопкой Макросы (Developer — Macros) или сочетанием клавиш Alt+F8.

Способ 2. Создаем множественные запросы в Power Query

У предыдущего способа, при всей его компактности и простоте, есть существенный недостаток — созданные макросом листы не обновляются при изменениях в исходной таблице продаж. Если обновление «на лету» необходимо, то придется использовать связку VBA+Power Query, а точнее — создавать с помощью макроса не просто листы со статическими данными, а обновляемые запросы Power Query.

Макрос в этом случае частично похож на предыдущий (в нём тоже есть цикл For Each … Next для перебора городов в справочнике), но внутри цикла будет уже не фильтрация и копирование, а создание запроса Power Query и выгрузка его результатов на новый лист:

Sub Splitter2()

For Each cell In Range("таблГорода")
    ActiveWorkbook.Queries.Add Name:=cell.Value, Formula:= _
        "let" & Chr(13) & "" & Chr(10) & "    Источник = Excel.CurrentWorkbook(){[Name=""таблПродажи""]}[Content]," & Chr(13) & "" & Chr(10) & "    #""Измененный тип"" = Table.TransformColumnTypes(Источник,{{""Категория"", type text}, {""Наименование"", type text}, {""Город"", type text}, {""Менеджер"", type text}, {""Дата сделки"", type datetime}, {""Стоимость"", type number}})," & Chr(13) & "" & Chr(10) & "    #""Строки с примененным фильтром"" = Table.
  Se" & _
        "lectRows(#""Измененный тип"", each ([Город] = """ & cell.Value & """))" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    #""Строки с примененным фильтром"""
    ActiveWorkbook.Worksheets.Add
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=" & cell.Value & ";Extended Properties=""""" _
        , Destination:=Range("$A$1")).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array("SELECT * FROM [" & cell.Value & "]")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .   RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = cell.Value
        .Refresh BackgroundQuery:=False
    End With
    ActiveSheet.Name = cell.Value
Next cell
End Sub

После его запуска мы увидим те же листы по городам, но формировать их будут уже созданные запросы Power Query:

При любых изменениях в исходных данных достаточно будет обновить соответствующую таблицу правой кнопкой мыши — команда Обновить (Refresh) или обновить сразу все города оптом, используя кнопку Обновить всё на вкладке Данные (Data — Refresh All).

Ссылки по теме

Что такое макросы, как их создавать и использовать
Сохранение листов книги как отдельных файлов
Сборка данных со всех листов книги в одну таблицу

Разделить Excel онлайн бесплатно

редактор Зритель Преобразование Слияние Разблокировать Защищать Сплиттер Сравнение Аннотация Парсер Метаданные Водяной знак Поиск Заменять Повернуть Обеспечить регресс Диаграмма Ипотека Сборка Перевод Компресс Прозрачный ИМТ ВебКонвертер

Питаться от aspose. com & aspose.cloud

Перетащите или загрузите свои файлы

Введите адрес

*Загружая свои файлы или используя наш сервис, вы соглашаетесь с нашими Условия использования & политика конфиденциальности

Сохранить как

-XLSXXLSXLSMXLSBODS

Ваш файл успешно обработан

СКАЧАТЬ СЕЙЧАС

Сохранить в облачное хранилище:

Отправить по электронной почте On Premise API

Нажмите Ctrl+D, чтобы сохранить его в закладках, чтобы не искать его снова

Поделиться через фейсбук

Поделиться в Твиттере

Посмотреть другие приложения

Попробуйте наш облачный API

См. исходный код

Оставить отзыв

Добавить это приложение в закладки

Нажмите Ctrl + D, чтобы добавить эту страницу в избранное, или Esc, чтобы отменить действие.

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

Email:

Сделайте этот форум закрытым, чтобы он был доступен только вам и нашим разработчикам.

Вы успешно сообщили об ошибке. Вы получите уведомление по электронной почте, когда ошибка будет исправлена. Нажмите эту ссылку, чтобы посетить форумы.

Вы уверены, что хотите удалить файлы?

Обработка…

Вставка номеров страниц на рабочие листы

Если вы хотите, чтобы номера отображались на страницах при печати рабочего листа Microsoft Excel, вы можете вставить номера страниц в верхние или нижние колонтитулы страниц рабочего листа. Номера страниц, которые вы вставляете, не отображаются на листе в обычном режиме — они отображаются только в режиме «Макет страницы» и на распечатанных страницах.

Примечание. Снимки экрана в этой статье были сделаны в Excel 2016. Если у вас другая версия, представление может немного отличаться, но, если не указано иное, функциональность такая же.

Добавление номеров страниц на один рабочий лист

Щелкните рабочий лист, для которого вы хотите вставить номера страниц.
На вкладке Вставка в группе Текст щелкните Верхний и нижний колонтитулы .
Excel отображает лист в режиме макета страницы. Вы также можете нажать Макет страницы в строке состояния, чтобы отобразить это представление.
На листе щелкните Щелкните, чтобы добавить верхний колонтитул или Щелкните, чтобы добавить нижний колонтитул .
Отображает инструменты верхнего и нижнего колонтитула , добавляя дизайн таб.
Чтобы указать, где в верхнем или нижнем колонтитуле должен отображаться номер страницы, щелкните внутри поля Левая секция , Центральная секция или Правая секция верхнего или нижнего колонтитула.
На вкладке Дизайн в Элементах верхнего и нижнего колонтитула 9Группа 0006, нажмите Номер страницы .
В выбранном разделе появится заполнитель &[Страница] .
Если вы хотите добавить общее количество страниц, введите пробел после &[Page] , введите слово из , затем пробел, а затем в группе Элементы верхнего и нижнего колонтитула нажмите Количество страниц .
В выбранном разделе появляется заполнитель &[Page] of &[Pages]
Щелкните в любом месте за пределами области верхнего или нижнего колонтитула, чтобы отобразить фактические номера страниц в представлении «Макет страницы».
После завершения работы в режиме макета страницы на Вкладка View , в группе Workbook Views щелкните Normal .
Вы также можете нажать Обычный в строке состояния.
Вы можете вставить номера страниц для рабочего листа в представлении «Макет страницы», где они будут видны, или вы можете использовать диалоговое окно Параметры страницы , если вы хотите вставить номера страниц для нескольких рабочих листов одновременно. Для других типов листов, таких как листы диаграмм, вы можете вставлять номера страниц только с помощью кнопки 9.0005 Диалоговое окно «Параметры страницы ».

Добавление номеров страниц в несколько рабочих листов

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

Совет: Чтобы последовательно добавить номера страниц ко всем листам в книге, необходимо изменить номер, с которого начинается каждый лист. Дополнительную информацию см. в разделе Установка другого номера для начальной страницы.

Щелкните рабочие листы или листы диаграмм, к которым вы хотите добавить номера страниц.

Чтобы выбрать	Сделай это
Один лист	Щелкните вкладку листа. Если вы не видите нужную вкладку, нажмите кнопки прокрутки вкладок, чтобы отобразить вкладку, а затем щелкните вкладку.
Два или более смежных листа	Щелкните вкладку для первого листа. Затем, удерживая нажатой клавишу Shift, щелкните вкладку последнего листа, который вы хотите выбрать.
Два или более несмежных листа	Щелкните вкладку для первого листа. Затем, удерживая нажатой клавишу Ctrl, щелкните вкладки других листов, которые вы хотите выбрать.
Все листы книги	Щелкните правой кнопкой мыши вкладку листа и выберите Выбрать все листы в контекстном меню.

Совет: Если выбрано несколько листов, в строке заголовка в верхней части листа отображается [Группа] . Чтобы отменить выбор нескольких рабочих листов в книге, щелкните любой невыбранный рабочий лист. Если невыбранный лист не отображается, щелкните правой кнопкой мыши вкладку выбранного листа и выберите Разгруппировать листы .

На вкладке Макет страницы в группе Параметры страницы щелкните кнопку запуска диалогового окна рядом с Параметры страницы .
В диалоговом окне Параметры страницы в верхнем/нижнем колонтитуле , нажмите Пользовательский верхний колонтитул или Пользовательский нижний колонтитул .
Чтобы указать, где в верхнем или нижнем колонтитуле должен отображаться номер страницы, щелкните внутри поля Левая секция , Центральная секция или Правая секция .
Чтобы вставить цифры, нажмите кнопку Кнопка «Вставить номер страницы» .
В выбранном разделе появится заполнитель &[Страница] .
Примечания:
- Если вы хотите добавить общее количество страниц, введите пробел после &[Page] , введите слово из , затем пробел, а затем нажмите кнопку Вставить количество страниц .
- В выбранном разделе появится заполнитель &[Page] of &[Pages] .

Установить другой номер начальной страницы

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

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

Изменить порядок нумерации страниц

По умолчанию Excel нумерует и печатает страницы сверху вниз, а затем слева направо на рабочем листе, но вы можете изменить направление нумерации и печатать страницы слева направо, а затем сверху вниз.

org/ListItem»>
Щелкните рабочий лист, для которого вы хотите изменить порядок нумерации.
На вкладке Макет страницы в группе Параметры страницы щелкните кнопку запуска диалогового окна рядом с Параметры страницы .
На вкладке Лист в разделе Порядок страниц нажмите Вниз, затем над или Сверху, затем вниз .
Направление каждого параметра отображается в окне предварительного просмотра.

Удалить номера страниц

Щелкните рабочие листы или листы диаграмм, для которых вы хотите удалить номера страниц.

Чтобы выбрать	Сделай это
Один лист	Щелкните вкладку листа. Если вы не видите нужную вкладку, нажмите кнопки прокрутки вкладок, чтобы отобразить вкладку, а затем щелкните вкладку.
Два или более смежных листа	Щелкните вкладку для первого листа. Затем, удерживая нажатой клавишу Shift, щелкните вкладку последнего листа, который вы хотите выбрать.
Два или более несмежных листа	Щелкните вкладку для первого листа. Затем, удерживая нажатой клавишу Ctrl, щелкните вкладки других листов, которые вы хотите выбрать.
Все листы книги	Щелкните правой кнопкой мыши вкладку листа и выберите Выбрать все листы в контекстном меню.

На вкладке Макет страницы в группе Параметры страницы щелкните кнопку запуска диалогового окна рядом с Параметры страницы .
org/ListItem»>
На вкладке Верхний/нижний колонтитул в Верхний колонтитул или Нижний колонтитул В раскрывающемся списке щелкните (нет) .
Примечание. Возможно, вам придется прокрутить список вверх, чтобы нажать (нет) .

Какую версию вы используете?



Добавить номера страниц

На вкладке Вставка щелкните Верхний и нижний колонтитулы .
Excel автоматически переходит в режим просмотра «Разметка страницы».
Верхний и нижний колонтитулы щелкните вкладку Верхний колонтитул или Нижний колонтитул , а затем выберите нужный формат номера страницы.
Номера страниц верхнего колонтитула отображаются вверху печатной страницы, а номера страниц нижнего колонтитула — внизу.
Когда вы закончите, вы можете либо остаться в режиме макета страницы, либо переключиться в обычный режим, нажав Обычный на вкладке Вид .

Установить другой номер начальной страницы

Вы можете сделать так, чтобы первый напечатанный лист начинался с номера страницы, отличного от 1. Это полезно, если перед листом будут напечатаны другие страницы. Например, вы можете напечатать 2-страничный документ Word, в котором будут учитываться страницы 1 и 2, а лист Excel будет начинаться со страницы 3.

На вкладке Макет страницы щелкните Параметры страницы .
На вкладке Страница в поле Номер первой страницы введите номер первой страницы. Например, если вы хотите, чтобы номер первой страницы был 3 , вы должны ввести число 3 .
org/ListItem»>
Нажмите OK .

Если вы используете формат номеров страниц, в котором подсчитывается общее количество страниц, например: Стр. 3 из 7 , вам потребуется изменить второе число. Эта корректировка гарантирует, что вы не получите что-то вроде этого: Страница 10 из 7 .

В Параметры страницы щелкните вкладку Верхний/нижний колонтитул , а затем щелкните либо Настраиваемый верхний колонтитул , либо Настраиваемый нижний колонтитул .
После Page &[Page] of &[Pages] введите знак плюса (+), а затем введите количество страниц, предшествующих первому номеру страницы.
Например, если номер вашей первой страницы равен 3, вам необходимо включить 2 страницы в общее количество страниц. Таким образом, вы должны ввести +2 в конце: Страница &[Страница] из &[Страницы]+2 , а затем нажать OK .
Нажмите OK еще раз.