Как разделить таблицу?
В любой деятельности для проведения анализа и подведения итогов формируются сводные отчеты и итоговые таблицы, но возникают ситуации, когда необходимо проделать обратную операцию и разделить итоговую таблицу на несколько частей. Для разделения таблиц на составные части, как правило, используются такие стандартные инструменты Excel как фильтрация, копирование и последующая вставка на отдельные листы или отдельные рабочие книги, что достаточно трудоемко, утомительно и часто требует ручного форматирования.
Для решения задач по разъединению таблиц на части в зависимости от значения в заданном столбце можно использовать готовое решение в виде надстройки для Excel.
Надстройка позволяет разделить таблицу на части, используя в качестве критерия для разделения, значения заданного столбца, например, разложить объединенную таблицу с наименованиями материалов на составные части, соответствующие номерам складов. В этом случае надстройка создает новые листы или новые рабочие книги в зависимости от выбранной опции и переносит на эти листы соответсвующие строки таблицы.
Номер столбца задается пользователем в диалоговом окне надстройки. Для безошибочного определения номера столбца, имеющего буквенное обозначение, в диалоговом окне надстройки предусмотрена возможность быстрого переключения стиля ссылок с A1 на R1C1 и обратно. Чтобы шапка таблицы не подверглась разделению вместе с остальными строками таблицы и присутствовала на каждом листе, в диалоговом окне надстройки указывается номер строки, с которой должно начаться разделение. Номер конечной строки определяется автоматически.
Как разделить таблицу по разным листам?
Чтобы разделить таблицу по разным листам, достаточно выбрать опцию «По листам», указать столбец с разделяемыми значениями, номер начальной строки и нажать кнопку «Пуск». В этом случае в рабочей книге для каждого разделяемого значения создается новый лист, имя которого соответствуют этому значению. В результате на каждом отдельном листе остаются строки с одним из разделяемых значений на пересечении со столбцом, значения которого используются в качестве критерия для разделения. Для того, чтобы упорядочить новые листы, можно установить флажок в поле «Сортировать листы по возрастанию».
Имя листа может состоять не более чем из 31 символа, поэтому если длина значения больше этой величины, то в имени листа оно обрезается до 31 символа. В строках листа значение остается без изменений.
Как разделить таблицу по разным рабочим книгам?
Для разделения таблицы по разным рабочим книгам, необходимо выбрать опцию «По книгам», задать номер столбца, значения которого подлежат разделению, номер начальной строки, выбрать папку, в которую будут сохранены новые рабочие книги и запустить программу. По умолчанию все новые рабочие книги остаются открытыми, но в работе надстройки предусмотрена возможность автоматического закрытия рабочих книг с разнесенными по ним строками. Для этого необходимо установить флажок в поле «Закрывать рабочие книги». Имена новых рабочих книг соответствуют разделяемым значениям.
Имя рабочей книги может состоять не более чем из 189 символов, поэтому в случае, когда длины значений больше этой величины, имена рабочих книг обрезаются до 189 символов. В строках листа эти значения остаются неизменными.
Разделение таблицы по листам
В 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 и скопируйте туда следующий код:
Здесь с помощью цикла For Each . Next реализован проход по ячейкам справочника таблГорода, где для каждого города происходит его фильтрация (метод AutoFilter) в исходной таблице продаж и затем копирование результатов на новый созданный лист. Попутно созданный лист переименовывается в то же имя города и на нем включается автоподбор ширины столбцов для красоты.
Запустить созданный макрос в Excel можно на вкладке Разработчик кнопкой Макросы (Developer — Macros) или сочетанием клавиш Alt + F8 .
Способ 2. Создаем множественные запросы в Power Query
У предыдущего способа, при всей его компактности и простоте, есть существенный недостаток — созданные макросом листы не обновляются при изменениях в исходной таблице продаж. Если обновление «на лету» необходимо, то придется использовать связку VBA+Power Query, а точнее — создавать с помощью макроса не просто листы со статическими данными, а обновляемые запросы Power Query.
Макрос в этом случае частично похож на предыдущий (в нём тоже есть цикл For Each . Next для перебора городов в справочнике), но внутри цикла будет уже не фильтрация и копирование, а создание запроса Power Query и выгрузка его результатов на новый лист:
После его запуска мы увидим те же листы по городам, но формировать их будут уже созданные запросы Power Query:
При любых изменениях в исходных данных достаточно будет обновить соответствующую таблицу правой кнопкой мыши — команда Обновить (Refresh) или обновить сразу все города оптом, используя кнопку Обновить всё на вкладке Данные (Data — Refresh All) .
Как разделить страницы для печати в Excel Online
Excel Online является очень удобным инструментом для работы с таблицами не только из-за его широкого функционала и набора инструментов для создания и редактирования таблиц. Также сервис предлагает пользователям гибкие настройки отображения данных, возможность удобно делиться файлом с другими людьми и работать над одним файлом вместе, а также, конечно, параметры печати. Можно настроить даже печать одной таблицы на нескольких страницах. Сегодня рассказываем, как это сделать.
Печать в Эксель Онлайн
Иногда возникает необходимость распечатать и иметь на бумаге даже удобные для онлайн-редактирования и просмотра таблицы. В Excel Online можно гибко настроить параметры печати, которых в сервисе сразу несколько:
- Количество копий одной страницы;
- Масштаб страницы – ее полные размеры, ширина или высота;
- Количество ячеек для печати – можно выбрать всю таблицу на одном листе, все листы (если их несколько) или отдельный диапазон ячеек;
- Разделение таблицы на несколько страниц – с помощью разрыва страницы можно разделить документ на несколько страниц.
Все параметры можно за несколько кликов настроить в меню печати, с помощью списков и полей для ввода.
Как в Экселе разделить страницы для печати: все способы
В некоторых случаях при печати таблицу нужно разделить на несколько разделов, каждый из которых необходимо напечатать отдельно. Чтобы не переносить данные по разным листам или вручную растягивать строки и столбцы так, чтобы часть таблицы «переехала» на другой лист, пользователь может воспользоваться одним из двух способов разделения страницы:
- Разрыв страницы;
- Масштабирование.
В первом случае в настройках печати можно просто разграничить таблицу так, как вам нужно. При этом, все разделы будет распечатаны на разных страницах. Второй метод предполагает изменение размеров страницы таким образом, чтобы часть таблицы «ушла» на другой лист.
Разделение страниц для печати функцией разрыва страницы
Самый простой и удобный способ разделить страницу для печати – воспользоваться функцией разрыва страница. В настройках печати можно разделить таблицу на любые разделы, каждый из которых останется на отдельной странице печати. Инструкция, как это сделать:
- Открыть документ, который необходимо распечатать;
- Нажать на значок печати в меню инструментов сверху или выбрать пункт «Печать», открыв раздел «Файл»;
- В открывшемся меню перейти к пункту «Задать разрывы страниц»;
- Перед вами откроется ваша таблица, которую при помощи пунктирный линий можно разделить любым способом.
Здесь же можно настроить поля, форматирование, размер и ориентацию страницы.
Разделяем страницы для печати в Excel при помощи масштабирования
Менее удобный способ, который, впрочем, позволяет настроить размер таблицы прямо во время печати – масштабирование. Суть в том, что в настройках печати можно менять размер таблицы – выровнять весь документ, ширину или высоту по странице. Также можно задать пользовательский размер, и сделать так, чтобы часть таблицы осталась на одной странице, а вторая часть – на другой. Для этого нужно:
Как разделить таблицу в эксель
Как разбить большую таблицу на несколько небольших таблиц в Excel?
Если у вас есть большой рабочий лист, содержащий несколько столбцов и сотни или тысячи данных строк, теперь вы хотите разделить эту большую таблицу на несколько небольших таблиц на основе значения столбца или количества строк, чтобы получить следующие результаты. Как бы вы могли справиться с этой задачей в Excel?
| Главный стол | Разделить таблицу на несколько таблиц по значению столбца | Разделить таблицу на несколько таблиц по количеству строк | |
 |
 |
 |
 |
Разделите большую таблицу на несколько таблиц на основе значения столбца с кодом VBA
Чтобы разделить эту большую таблицу на несколько таблиц на основе определенного значения столбца, следующий код VBA может оказать вам услугу. Пожалуйста, сделайте так:
1. Удерживайте ALT + F11 , чтобы открыть Microsoft Visual Basic для приложений окно.
2. Нажмите Вставить > Модулии вставьте следующий код в Модули Окно.
Код VBA: разделите большую таблицу на несколько таблиц по ключевому столбцу:
3. После вставки кода нажмите F5 нажмите клавишу для запуска этого кода, и появится окно подсказки, выберите строку заголовка из ваших данных, см. снимок экрана:
4, Затем нажмите OK, и появится другое диалоговое окно, выберите данные столбца, на основе которых вы хотите разделить таблицу, см. снимок экрана:
5. Нажмите OK, эта большая таблица была разделена на несколько листов по значению столбца, которое расположено после основного листа. И новым листам присваивается имя со значением столбца. Смотрите скриншот:
Разделите большую таблицу на несколько таблиц на основе определенного количества строк с кодом VBA
Если вам нужно разделить таблицу на несколько таблиц в зависимости от количества строк, следующий код VBA может вам помочь.
1. Удерживайте ALT + F11 , чтобы открыть Microsoft Visual Basic для приложений окно.
2. Нажмите Вставить > Модулии вставьте следующий код в Модули Окно.
Код VBA: разделите большую таблицу на несколько таблиц по количеству строк:
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 кнопку, и теперь большая таблица была разделена на несколько небольших таблиц в новой книге. Смотрите скриншоты: