Разбить таблицу: быстрое разнесение данных таблицы или диапазона по нескольким листам

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

Надстройка «Разбить таблицу» автоматически разнесёт данные из одного листа по нескольким листам:

  • Быстро разбить данные таблицы или диапазона на разные листы
  • Выбор метода разделения: по значениям столбца или по числу строк
  • Выбор способа именования листов результата
  • Сохранение заголовков и форматирования в таблицах результата
  • Автоматическое разделение объединённых ячеек с дублированием значений

 

Добавить «Разбить таблицу» в Excel 2016, 2013, 2010, 2007

Подходит для: Microsoft Excel 2016 – 2007, desktop Office 365 (32-бит и 64-бит).

Скачать надстройку XLTools

Кнопка Разбить таблицу в Excel

 

Как работать с надстройкой:

 

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

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

1. Нажмите кнопку «Разбить таблицу» на панели XLTools > Откроется диалоговое окно.

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

3. Отметьте флажком «Таблица с заголовками», если это так.

  • Если в таблице есть заголовок, он будет продублирован в таблицах результата.
    Внимание: для лучшего результата, убедитесь, что в заголовке нет пустых ячеек.
  • Если в таблице нет заголовка, его также не будет в таблицах результата.

4. Выберите разбить по «Значениям в этом столбце» в качестве метода разделения > В выпадающем списке найдите и выберите ключевой столбец:

  • Если в таблице есть заголовок, найдите столбец по его названию в заголовке.
  • Если в таблице нет заголовка, найдите столбец по его общему буквенному обозначению (A, B, C, т.д.)

5. Задайте способ именования листов результата:

  • Выберите «Значение в столбце», чтобы вкладкам присваивались имена по значениям ключевого столбца.
    Внимание: если некоторые ячейки в вашем ключевом столбце пустые, пожалуйста, заполните пропуски или используйте другой способ именования листов.
  • Или: выберите «Числовой ряд», чтобы вкладкам назначались имена последовательными числами (1, 2, 3…)
  • При необходимости, добавьте префикс или суффикс. Они будут повторяться в названии каждой вкладки.
    Совет: рекомендуем использовать содержательные префиксы и суффиксы — позже будет проще искать и переключаться между листами.

6. Нажмите OK > Готово. Обработка больших таблиц может занять некоторое время.

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

Разбить таблицу по листам по значению столбца

 

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

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

1. Нажмите кнопку «Разбить таблицу» на панели XLTools > Откроется диалоговое окно.

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

3. Отметьте флажком «Таблица с заголовками», если это так.

  • Если в таблице есть заголовок, он будет продублирован в таблицах результата.
  • Если в таблице нет заголовка, его также не будет в таблицах результата.

4. Выберите «По числу строк» в качестве метода разделения > Укажите фиксированное число строк для разделения таблицы.

5. Задайте способ именования листов результата:

  • Выберите «Числовой ряд», чтобы вкладкам назначались имена последовательными числами (1, 2, 3…)
  • При необходимости, добавьте префикс или суффикс. Они будут повторяться на каждой вкладке.
    Совет: рекомендуем использовать содержательные префиксы и суффиксы — позже будет проще искать и переключаться между листами.

6. Нажмите OK > Готово. Обработка больших таблиц может занять некоторое время.

В результате: новые листы размещены по порядку сразу после исходного листа. Каждая вкладка содержит таблицу с фиксированным числом строк. Исходные данные в сохранности и не подвергались изменениям.

Разбить таблицу по листам по количеству строк

 

Как образом данные копируются на новые листы

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

  • Формулы и ссылки на ячейки:
    Чтобы избежать искажения данных, вместо ссылок на ячейки, функций или формул на исходном листе, в листы результата надстройка XLTools «Разбить таблицу» вставляет их значения.
  • Форматирование:
    Надстройка «Разбить таблицу» сохраняет форматирование ячеек и таблиц такими же, как на исходном листе. Это относится к формату ячеек (число, дата, текст, т.д.), ширине столбцов, высоте строк, цвету заливки, т.д. Тем не менее, если к вашей исходной таблице применен стиль, то таблицы результата будут вставлены как диапазоны.
  • Объединённые ячейки:
    Если в таблице есть объединённые ячейки, объединение автоматически снимается, а соответствующие значения дублируются.

 

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

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

Появились вопросы или предложения? Оставьте комментарий ниже.

2 Комментариев к Разбить таблицу: быстрое разнесение данных таблицы или диапазона по нескольким листам

  1. Denis says:

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

    2) В дополнение к п.1. будет актуальной выгрузка создаваемых листов в одну новую книгу.

    3) Было бы удобнее иметь возможность выбора из имеющихся столбцов какие использовать в качестве ключа для нарезки. Т.е. при нарезке воспринимать такие столбцы как сцепленные в ключ нарезки

    4) Выбор способа переноса формул также актуален. Когда у меня формула ссылается на диапазон, который должен будет выделиться на отдельный лист/книгу, я бы хотел иметь возможность сохранить формулу при разделении таблицы.

    5) Чаще всего разбивка на отдельные файлы применяется в связке с групповой рассылкой таких файлов. Добавьте, пожалуйста, к этому инструменту механизм организации групповой рассылки применительно к сформированным файлам.

    Жду обновлений! Вдохновения вам в разработке и хороших продаж.

    • Maria Balobanova says:

      Denis, спасибо за подробные предложения! Постараемся реализовать в следующих версиях. Что касается п.1-2, то пока это можно выполнить в Органайзере книг, т.е. после разбивки на листы эти листы можно сохранить как отдельные файлы или скопировать/перенести в новую книгу.

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

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