Как разделить таблицу в эксель
Перейти к содержимому

Как разделить таблицу в эксель

  • автор:

Разделение таблицы

Таблицу в приложении Word можно разделить на несколько частей. Благодаря этому можно получить таблицы меньшего размера или добавить текст между двумя таблицами.

Таблица в Word

    Поместите курсор в строку, которая должна стать первой строкой второй таблицы. В представленном примере это третья строка.

Параметр

Если щелкнуть в любом месте таблицы, на ленте появятся две новые вкладки Работа с таблицами: КОНСТРУКТОР и МАКЕТ. Эти средства видны только в том случае, если курсор находится в таблице (например, при добавлении содержимого в ячейки).
На вкладке МАКЕТ в группе Объединение выберите команду Разделить таблицу.

Таблица разделяется на две таблицы.

Таблица будет разделена на две части.

Видео: разделение таблиц

Excel для Microsoft 365 Word для Microsoft 365 Outlook для Microsoft 365 PowerPoint для Microsoft 365 Excel 2021 Word 2021 Outlook 2021 PowerPoint 2021 Excel 2019 Word 2019 Outlook 2019 PowerPoint 2019 Excel 2016 Word 2016 Outlook 2016 PowerPoint 2016 Office 2016 Еще. Меньше

Браузер не поддерживает видео.

Совет. Видео не на вашем языке? Попробуйте выбрать Скрытые субтитры

Кнопка

Проверьте, как это работает!

Разделите таблицу, чтобы разнести ее данные по двум таблицам.

  1. Выделите ячейку в строке, которая должна быть первой в новой таблице.
  2. На вкладке Работа с таблицами | Макет нажмите кнопку Разделить таблицу.

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

В 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()[Content]," & Chr(13) & "" & Chr(10) & " #""Измененный тип"" = Table.TransformColumnTypes(Источник,, , , , , >)," & 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

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

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

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

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

Функционал для разделения таблиц по листам Excel позволяет автоматически отсортировать и разделить данные в таблице на разные листы Excel в соответствии с заданными параметрами. Он значительно упрощает процесс разделения данных и позволяет сократить время, затрачиваемое на ручное сортирование.

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

Как разбить таблицу по листам?

Для работы с этой командой во вкладке ProXL воспользуйтесь меню «Работа с таблицами» и выберите пункт «Разделение по листам«.

Выбор команды разделение по листам

В появившемся окне выберите нужный диапазон таблицы.

Выбор исходной таблицы для разделения по листам

Заполните остальные опции:

Количество строк в шапке таблицы.

Количество строк в шапке

если таблица имеет заголовок, то укажите сколько строк в заголовке таблицы.

Выберите условие, по которому будет разбиваться таблица.

Условие разбивки таблицы

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

Имена добавляемых листов

Имена добавляемых листов

Также определите, как будут именоваться добавленные листы — по значению столбца разбивки, по последовательной числовой нумерации (1, 2, 3, 4…) либо стандартно («Лист 1», «Лист 2», «Лист 3», «Лист 4»).

Прочие настройки

Если вы поставите галочку в чекбоксе «Заменить существующие листы с таким же именем», то в случае совпадения программа заменит листы на новые, если уберете — создаст новые листы с индексом на 1 больше.

Чтобы дублировать шапку на каждый из новых листов, поставьте галочку в соответствующем чекбоксе.
Галочка в чекбоксе “Сохранить форматирование” позволит использовать стили исходной таблицы во всех новых листах.

Пример 1. Разбиение по значениям столбца

В этом примере выбраны следующие параметры разбивки таблицы: 1 строка в шапке, разбивка по значению в столбце №4, нумерация по значению из столбца, замена существующих листов с такими же именами, дублирование шапки в каждом листе и сохранение форматирования.

Разбиение по значениям столбца

В результате преобразований таблица будет разбита по листам следующим образом.

По каждому уникальному значению из столбца №4 будет создана таблица на отдельном листе.

Аналогичным образом можно разбить таблицу на листы на основании значений из любого другого столбца.

Пример 2. Разбиение по листам по цвету заливки столбца

В этом примере выбраны следующие параметры разбивки таблицы: 1 строка в шапке, разбивка по цвету заливки в столбце 1, имя по значению из столбца, дублирование шапки в каждом листе с сохранением форматирования.

Разбиение по листам по заливке столбца

В результате преобразований таблица будет разбита по листам следующим образом.

Для каждого цвета заливки будет создан отдельный лист.

Пример 3. Разбиение по горизонтальным разрывам

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

Разбиение по горизонтальным разрывам

В результате преобразований таблица будет разбита по листам следующим образом.

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

Пример 4. Разбиение блоками по n строк

В этом примере выбраны следующие параметры разбивки таблицы: 1 строка в шапке, разбивка частями по 5 строк, имя по значению из столбца, дублирование шапки в каждом листе с сохранением форматирования.

В результате преобразований таблица будет разбита по листам следующим образом.

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

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *