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

Май
25

В любой деятельности для проведения анализа и подведения итогов формируются сводные отчеты и итоговые таблицы, но возникают ситуации, когда необходимо проделать обратную операцию и разделить итоговую таблицу на несколько частей. Для разделения таблиц на составные части, как правило, используются такие стандартные инструменты Excel как фильтрация, копирование и последующая вставка на отдельные листы или отдельные рабочие книги, что достаточно трудоемко, утомительно и часто требует ручного форматирования.

Для решения задач по разъединению таблиц на части в зависимости от значения в заданном столбце можно использовать готовое решение в виде надстройки для Excel.

надстройка для разнесения строк таблицы по разным листам или рабочим книгам

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

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

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

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

Имя листа может состоять не более чем из 31 символа, поэтому если длина значения больше этой величины, то в имени листа оно обрезается до 31 символа. В строках листа значение остается без изменений.

Как разделить таблицу по разным рабочим книгам?

Для разделения таблицы по разным рабочим книгам, необходимо выбрать опцию «По книгам», задать номер столбца, значения которого подлежат разделению, номер начальной строки, выбрать папку, в которую будут сохранены новые рабочие книги и запустить программу. По умолчанию все новые рабочие книги остаются открытыми, но в работе надстройки предусмотрена возможность автоматического закрытия рабочих книг с разнесенными по ним строками. Для этого необходимо установить флажок в поле «Закрывать рабочие книги». Имена новых рабочих книг соответствуют разделяемым значениям.

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

Видео по работе с надстройкой

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

Для того чтобы разделить таблицу необходимо:

• открыть таблицу (перейти в редактор таблиц);

• нажать на кнопку для вызова диалогового окна Деление на страницы;

• в диалоговом окне Деление на страницы доступны следующие варианты:

• переключатель Одной страницей отключает деление таблицы;
• выбрать способ деления таблицы:

1. Произвольный — разбиение таблицы на произвольные части.

   

   Разрыв страницы перед строкой задается командой контекстного меню Начать новую страницу.

   

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

   

   Таблица с разрывом на чертеже будет выглядеть следующим образом:

   

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

   

2. По числу строк — в поле ввода укажите количество строк в странице, через которое будет производиться деление.

   

   Строки заголовка таблицы не учитываются. В примере таблица будет разделена на части таким образом, чтобы в каждой части было 5 строк со значениями:

   

3. По высоте — в поле ввода укажите суммарную высоту строк, через которую будет производиться деление. Значение вводится в миллиметрах в текущем масштабе.

   

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

   

Разделенная на части таблица допускает независимое перемещение каждой части.

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

Другие интересные материалы

Иногда, при работе с большими таблицами (или просто с разными данными помещенными на одном листе MS Excel) довольно просто запутаться с цифрах, так как постоянно приходится «перескакивать» с одного места документа на другое. Можно, конечно, выписать нужные данные на листочек и сверятся с ним, можно даже скопировать часть этих данных в другой документ «экселя» и держать его открытым….

Деление окна табличного редактора на независимые области

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

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

Чтобы включить режим разделенного окна, на панели «Вид», в группе «Окно» найдите инструмент «Разделить» и щелкните на его значок.

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

Правда, сразу скажу — лично мне деление окна аж на 4 части кажется не совсем удобным. Гораздо более актуальна задача — сравнить не 4, а 2 фрагмента, верно? И в этом нет ничего сложного — двойной щелчок левой кнопки мыши на любой из границ разделения (горизонтальной или вертикальной) скроет её, и на экране вместо 4-х областей листа MS Excel, окажется только два — слева и справа или сверху и снизу, как вам удобнее.

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

Таким образом, сокращая разделение с 4-х до 2-х частей, имейте ввиду — при делении на «лево и право» синхронная прокрутка будет доступна по вертикальной, а при делении на «верх и низ», по горизонтальной осям.

Чтобы отключить режим разделения окна в MS Excel, снова перейдите на панель «Вид», и повторно выберите инструмент «Разделить».

Деление листа исчезнет.

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

Разделите большую таблицу на несколько таблиц на основе значения столбца с кодом VBA

Разделите большую таблицу на несколько таблиц на основе определенного количества строк с кодом VBA

Разделите большую таблицу на несколько таблиц на основе значения столбца или количества строк с удивительной функцией

Разделите большую таблицу на несколько таблиц на основе значения столбца с кодом VBA

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

1. Удерживайте ALT + F11 , чтобы открыть Microsoft Visual Basic для приложений окно.

2. Нажмите Вставить > Модулии вставьте следующий код в Модули Окно.

Код VBA: разделите большую таблицу на несколько таблиц по ключевому столбцу:

Sub Splitdatabycol()
'by 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
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
Sheets.Add(after:=Worksheets(Worksheets.Count)).Name = myarr(i) & ""
Else
Sheets(myarr(i) & "").Move after:=Worksheets(Worksheets.Count)
End If
xWSTRg.Range(title).Copy
Sheets(myarr(i) & "").Paste Destination:=Sheets(myarr(i) & "").Range("A1")
ws.Range("A" & (titlerow + xTRg.Rows.Count) & ":A" & lr).EntireRow.Copy Sheets(myarr(i) & "").Range("A" & (titlerow + xTRg.Rows.Count))
Sheets(myarr(i) & "").Columns.AutoFit
Next
xWSTRg.Delete
ws.AutoFilterMode = False
ws.Activate
Application.DisplayAlerts = True
End Sub

3. После вставки кода нажмите F5 нажмите клавишу для запуска этого кода, и появится окно подсказки, выберите строку заголовка из ваших данных, см. снимок экрана:

4, Затем нажмите OK, и появится другое диалоговое окно, выберите данные столбца, на основе которых вы хотите разделить таблицу, см. снимок экрана:

5. Нажмите OK, эта большая таблица была разделена на несколько листов по значению столбца, которое расположено после основного листа. И новым листам присваивается имя со значением столбца. Смотрите скриншот:

Разделите большую таблицу на несколько таблиц на основе определенного количества строк с кодом VBA

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

1. Удерживайте ALT + F11 , чтобы открыть Microsoft Visual Basic для приложений окно.

2. Нажмите Вставить > Модулии вставьте следующий код в Модули Окно.

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

Sub Splitdatabyrows()
'Updated by Extendoffice 
Dim WorkRng As Range
Dim xRow As Range
Dim SplitRow As Integer
Dim xWs As Worksheet
Dim xTRg As Range
Dim xNTRg As Range
Dim xIER
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set xTRg = Application.InputBox("Please select the header row:", xTitleId, "", Type:=8)
If TypeName(xTRg) = "Nothing" Then Exit Sub
Set WorkRng = Application.InputBox("Please select the data range(exclude the header row):", xTitleId, WorkRng.Address, Type:=8)
If TypeName(WorkRng) = "Nothing" Then Exit Sub
SplitRow = Application.InputBox("Split Row Num", xTitleId, Type:=1)
If SplitRow = 0 Then Exit Sub
Set xWs = WorkRng.Parent
Set xRow = WorkRng.Rows(1)
xIER = WorkRng.Rows.Count
xIER = WorkRng.Row + xIER - 1
Application.ScreenUpdating = False
For i = 1 To WorkRng.Rows.Count Step SplitRow
    resizeCount = SplitRow
    If (xIER - xRow.Row + 1) < SplitRow Then
        resizeCount = (xIER - xRow.Row + 1)
    End If
    xRow.Resize(resizeCount).Copy
    Set xWs = Application.Worksheets.Add(after:=Application.Worksheets(Application.Worksheets.Count))
    If xIER > (xRow.Row + SplitRow - 1) Then
        xWs.Name = xRow.Row & " - " & (xRow.Row + SplitRow - 1)
    ElseIf xIER = xRow.Row Then
        xWs.Name = xRow.Row
    Else
        xWs.Name = xRow.Row & " - " & xIER
    End If
    Application.ActiveSheet.Range("A1").PasteSpecial
    Set xNTRg = Application.ActiveSheet.Range("A1")
    xTRg.Copy
    xNTRg.Insert
    Set xRow = xRow.Offset(SplitRow)
Next
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

3, Затем нажмите F5 нажмите, в появившемся диалоговом окне выберите строку заголовка, см. снимок экрана:

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

5. А затем продолжайте нажимать OK кнопку, в третьем поле запроса введите количество строк, которые вы хотите разделить, см. снимок экрана:

6, Затем нажмите OK Кнопка, основная таблица была разделена на несколько листов в зависимости от количества строк, как показано ниже:

Разделите большую таблицу на несколько таблиц на основе значения столбца или количества строк с удивительной функцией

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

Советы:Чтобы применить это Разделить данные функция, во-первых, вы должны скачать Kutools for Excel, а затем быстро и легко примените эту функцию.

После установки Kutools for Excel, пожалуйста, сделайте так:

1. Выберите диапазон данных, который вы хотите разделить, а затем щелкните Кутулс Плюс > Разделить данные, см. снимок экрана:

2. В Разделить данные на несколько листов диалоговом окне укажите необходимые параметры:

(1.) Выберите Конкретный столбец or Фиксированные строки из Сплит на основе раздел по мере необходимости;

(2.) Укажите имя нового листа из Правила раскрывающийся список, вы можете добавить Префикс or Суффикс к именам листов.

3, Затем нажмите Ok кнопку, и теперь большая таблица была разделена на несколько небольших таблиц в новой книге. Смотрите скриншоты:

Нажмите, чтобы скачать Kutools for Excel и бесплатная пробная версия прямо сейчас!

Больше относительных статей: