Разделить книгу excel на листы
Как разбить данные на несколько листов по количеству строк в Excel?
Например, у меня есть диапазон списка данных, теперь я хочу разделить его на несколько листов по количеству строк, в этом примере я разделю его на каждые 5 строк (см. Следующие снимки экрана). Есть ли способы помочь нам быстро решить эту проблему? Здесь я расскажу о двух приемах, которые помогут вам легко это сделать.
Вкладка Office позволяет редактировать и просматривать в Office с вкладками и значительно упрощает работу .
- Повторное использование чего угодно: Добавляйте наиболее часто используемые или сложные формулы, диаграммы и все остальное в избранное и быстро используйте их в будущем.
- Более 20 текстовых функций: Извлечь число из текстовой строки; Извлечь или удалить часть текстов; Преобразование чисел и валют в английские слова.
- Инструменты слияния : Несколько книг и листов в одну; Объединить несколько ячеек / строк / столбцов без потери данных; Объедините повторяющиеся строки и сумму.
- Разделить инструменты : Разделение данных на несколько листов в зависимости от ценности; Из одной книги в несколько файлов Excel, PDF или CSV; От одного столбца к нескольким столбцам.
- Вставить пропуск Скрытые / отфильтрованные строки; Подсчет и сумма по цвету фона ; Отправляйте персонализированные электронные письма нескольким получателям массово.
- Суперфильтр: Создавайте расширенные схемы фильтров и применяйте их к любым листам; Сортировать по неделям, дням, периодичности и др .; Фильтр жирным шрифтом, формулы, комментарий .
- Более 300 мощных функций; Работает с Office 2007-2019 и 365; Поддерживает все языки; Простое развертывание на вашем предприятии или в организации.
Разделение данных на несколько листов по количеству строк с кодом VBA
Удивительный! Использование эффективных вкладок в Excel, таких как Chrome, Firefox и Safari!
Экономьте 50% своего времени и сокращайте тысячи щелчков мышью каждый день!
Следующий код VBA может помочь вам разделить строки на несколько листов по количеству строк, сделайте следующее:
1. Удерживайте ALT + F11 , чтобы открыть Microsoft Visual Basic для приложений окно.
2. Нажмите Вставить > Модулии вставьте следующий код в окно модуля.
VBA: разделение данных на листы по количеству строк в Excel.
3, Затем нажмите F5 нажмите клавишу для запуска кода, и появится диалоговое окно для выбора диапазона для разделения, а затем нажмите OK, и еще один диалог, в котором вы можете указать количество строк. Смотрите скриншот:
4. Нажмите OK, а диапазон разбивается на несколько листов по количеству строк.
Внимание: Разделенные листы помещаются в конце главного рабочего листа.
Разделите данные на несколько листов по количеству строк с помощью Kutools for Excel
Приведенный выше код может разделить данные только по указанному количеству строк, и он не будет добавлять заголовок для каждого рабочего листа, если ваши данные имеют заголовок.
Но с Kutools for ExcelАвтора Разделить данные, вы не только можете вставлять заголовки для данных каждого рабочего листа, но также можете указать имена новых разделенных листов.
Kutools for Excel включает более 300 удобных инструментов Excel. Бесплатная пробная версия без ограничений в течение 30 дней. Получить сейчас
Если вы установили Kutools for Excel, сделайте следующее:
1. Выберите диапазон данных, которые вы хотите разделить.
2. Нажмите Предприятие > Инструменты рабочего листа > Разделить данные (или Предприятие > Разделить данные ), см. снимок экрана:
3. В Разделить данные на несколько листов диалоговое окно:
- Выберите Фиксированные строки из Сплит на основе вариант и введите количество строк, которое вы хотите разделить для каждого листа. (Если у ваших данных есть заголовки, и вы хотите вставить их в каждый новый разделенный лист, проверьте У моих данных есть заголовки опция).
- Затем вы можете указать имена разделенных листов под Имя нового листа раздел, выберите правила имен рабочих листов из Правила раскрывающийся список, вы можете добавить Префикс or Суффикс также для имен листов. Смотрите скриншот:
4. После указания параметров нажмите OK, и выбранные данные были разделены на несколько листов новой книги каждые 5 строк. И заголовки вставляются для каждого нового рабочего листа. Смотрите скриншот:
В 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, которую нужно "разобрать", т.е. сохранить каждый лист как отдельный файл для дальнейшего использования.
Примеров подобного из реальной жизни можно привести массу. Например, файл-отчет с листами-филиалами нужно разделить на отдельные книги по листам, чтобы передать затем данные в каждый филиал и т.д.
Если делать эту процедуру вручную, то придется для каждого листа выполнить немаленькую цепочку действий (выбрать лист, правой кнопкой по ярлычку листа, выбрать Копировать, указать отдельный предварительно созданный пустой файл и т.д.) Гораздо проще использовать короткий макрос, автоматизирующий эти действия.
Способ 1. Простое разделение
Нажмите сочетание Alt+F11 или выберите в меню Сервис - Макрос - Редактор Visual Basic (Tools - Macro - Visual Basic Editor) , вставьте новый модуль через меню Insert - Module и скопируйте туда текст этого макроса:
Если теперь выйти из редактора Visual Basic и вернуться в Excel, а затем запустить наш макрос (Alt+F8), то все листы из текущей книги будут разбиты по отдельным новым созданным книгам.
Способ 2. Разделение с сохранением
При необходимости, можно созданные книги сразу же сохранять под именами листов. Для этого макрос придется немного изменить, добавив команду сохранения в цикл:
Этот макрос сохраняет новые книги-листы в ту же папку, где лежал исходный файл. При необходимости сохранения в другое место, замените wb.Path на свой путь в кавычках, например "D:\Отчеты\2012" и т.п.
Если нужно сохранять файлы не в стандартном формате книги Excel (xlsx), а в других (xls, xlsm, xlsb, txt и т.д.), то кроме очевидного изменения расширения на нужное, потребуется добавить еще и уточнение формата файла - параметр FileFormat:
Для основных типов файлов значения параметра FileFormat следующие:
- XLSX = 51
- XLSM = 52
- XLSB = 50
- XLS = 56
- TXT = 42
Способ 3. Сохранение в новые книги только выделенных листов
Если вы хотите раскидать по файлам не все листы в вашей книге, а только некоторые, то макрос придется немного изменить. Выделите нужные вам листы в книге, удерживая на клавиатуре клавишу Ctrl или Shift и запустите приведенный ниже макрос:
Создавать новое окно и копировать через него, а не напрямую, приходится потому, что Excel не умеет копировать группу листов, если среди них есть листы с умными таблицами. Копирование через новое окно позволяет такую проблему обойти.
Способ 4. Сохранение только выделенных листов в новый файл
Во всех описанных выше способах каждый лист сохранялся в свой отдельный файл. Если же вы хотите сохранить в отдельный новый файл сразу группу выделенных предварительно листов, то нам потребуется слегка видоизменить наш макрос:
Способ 5. Сохранение листов как отдельных PDF-файлов
- для этого используется уже другой метод (ExportAsFixedFormat а не Copy)
- листы выводятся в PDF с параметрами печати, настроенными на вкладке Разметка страницы (Page Layout)
- книга должна быть сохранена на момент экспорта
Нужный нам код будет выглядеть следующим образом:
Способ 6. Готовый макрос из надстройки PLEX
Если лень или нет времени внедрять все вышеописанное, то можно воспользоваться готовым макросом из моей надстройки PLEX:
Про сборку листов из нескольких книг в одну текущую я уже писал здесь. Теперь разберем решение обратной задачи: есть одна книга Excel, которую нужно "разобрать", т.е. сохранить каждый лист как отдельный файл для дальнейшего использования.
Примеров подобного из реальной жизни можно привести массу. Например, файл-отчет с листами-филиалами нужно разделить на отдельные книги по листам, чтобы передать затем данные в каждый филиал и т.д.
Если делать эту процедуру вручную, то придется для каждого листа выполнить немаленькую цепочку действий (выбрать лист, правой кнопкой по ярлычку листа, выбрать Копировать, указать отдельный предварительно созданный пустой файл и т.д.) Гораздо проще использовать короткий макрос, автоматизирующий эти действия.
Способ 1. Простое разделение
Нажмите сочетание Alt+F11 или выберите в меню Сервис - Макрос - Редактор Visual Basic (Tools - Macro - Visual Basic Editor) , вставьте новый модуль через меню Insert - Module и скопируйте туда текст этого макроса:
Если теперь выйти из редактора Visual Basic и вернуться в Excel, а затем запустить наш макрос (Alt+F8), то все листы из текущей книги будут разбиты по отдельным новым созданным книгам.
Способ 2. Разделение с сохранением
При необходимости, можно созданные книги сразу же сохранять под именами листов. Для этого макрос придется немного изменить, добавив команду сохранения в цикл:
Этот макрос сохраняет новые книги-листы в ту же папку, где лежал исходный файл. При необходимости сохранения в другое место, замените wb.Path на свой путь в кавычках, например "D:\Отчеты\2012" и т.п.
Если нужно сохранять файлы не в стандартном формате книги Excel (xlsx), а в других (xls, xlsm, xlsb, txt и т.д.), то кроме очевидного изменения расширения на нужное, потребуется добавить еще и уточнение формата файла - параметр FileFormat:
Для основных типов файлов значения параметра FileFormat следующие:
- XLSX = 51
- XLSM = 52
- XLSB = 50
- XLS = 56
- TXT = 42
Способ 3. Сохранение в новые книги только выделенных листов
Если вы хотите раскидать по файлам не все листы в вашей книге, а только некоторые, то макрос придется немного изменить. Выделите нужные вам листы в книге, удерживая на клавиатуре клавишу Ctrl или Shift и запустите приведенный ниже макрос:
Создавать новое окно и копировать через него, а не напрямую, приходится потому, что Excel не умеет копировать группу листов, если среди них есть листы с умными таблицами. Копирование через новое окно позволяет такую проблему обойти.
Способ 4. Сохранение только выделенных листов в новый файл
Во всех описанных выше способах каждый лист сохранялся в свой отдельный файл. Если же вы хотите сохранить в отдельный новый файл сразу группу выделенных предварительно листов, то нам потребуется слегка видоизменить наш макрос:
Способ 5. Сохранение листов как отдельных PDF-файлов
- для этого используется уже другой метод (ExportAsFixedFormat а не Copy)
- листы выводятся в PDF с параметрами печати, настроенными на вкладке Разметка страницы (Page Layout)
- книга должна быть сохранена на момент экспорта
Нужный нам код будет выглядеть следующим образом:
Способ 6. Готовый макрос из надстройки PLEX
Если лень или нет времени внедрять все вышеописанное, то можно воспользоваться готовым макросом из моей надстройки PLEX:
Как разделить книгу на отдельные файлы Excel в Excel?
Возможно, вам придется разделить большую книгу на отдельные файлы Excel, сохранив каждый рабочий лист книги как отдельный файл Excel. Например, вы можете разделить книгу на несколько отдельных файлов Excel, а затем передать каждый файл другому человеку для обработки. Поступая таким образом, вы можете заставить определенных людей обрабатывать определенные данные и сохранить ваши данные в безопасности. В этой статье будут представлены способы разделения большой книги на отдельные файлы Excel на основе каждого рабочего листа.
- Разделение книги на отдельные файлы Excel с копированием и вставкой
- Разделите книгу на отдельные файлы Excel с помощью функции перемещения или копирования
- Разделить книгу на отдельные файлы Excel с кодом VBA
- Разделите книгу на отдельные файлы Excel / PDF / CSV / TXT с помощью Kutools for Excel легко
Разделение книги на отдельные файлы Excel с копированием и вставкой
Обычно, используя Копировать Команда и макаронные изделия Команда может вручную сохранить книгу как отдельный файл Excel. Во-первых, выберите весь лист, который вы хотите сохранить как отдельный файл, создайте новую книгу, а затем вставьте ее в новую книгу, в конце сохраните ее.
Это простой в использовании способ, если вам нужно разделить всего несколько листов в отдельные файлы. Однако разделение множества листов путем копирования и вставки вручную должно занимать много времени и утомительно.
Разделите книгу на отдельные файлы Excel с помощью функции перемещения или копирования
Этот метод представит функцию перемещения или копирования для перемещения или копирования выбранных листов в новую книгу и сохранения в виде отдельной книги. Пожалуйста, сделайте следующее:
1. Выберите листы на панели вкладок «Лист», щелкните правой кнопкой мыши и выберите Переместить или скопировать из контекстного меню. Смотрите скриншот:
Внимание: Держа Ctrl клавишу, вы можете выбрать несколько несмежных листов, щелкая их один за другим на панели вкладок Лист; держа Shift клавишу, вы можете выбрать несколько соседних листов, щелкнув первый и последний на панели вкладок «Лист».
2. В диалоговом окне "Перемещение или копирование" выберите (новая книга) из Бронировать раскрывающийся список, проверьте Создать копию и нажмите OK кнопка. Смотрите скриншот:
3. Теперь все выбранные листы копируются в новую книгу. Нажмите Файл > Сохраните чтобы сохранить новую книгу.
Быстро разбейте книгу на отдельные файлы Excel / PDF / TXT / CSV в Excel
Обычно мы можем разделить книгу на отдельные файлы Excel с помощью Переместить или скопировать функция в Excel. Но Kutools для Excel Разделить книгу Утилита может помочь вам легко разделить книгу и сохранить каждый лист как отдельный файл PDF / TEXT / CSV или книгу в Excel. Полнофункциональная бесплатная 30-дневная пробная версия!
Kutools for Excel - Включает более 300 удобных инструментов для Excel. Полнофункциональная бесплатная 30-дневная пробная версия, кредитная карта не требуется! Get It Now
Разделить книгу на отдельные файлы Excel с кодом VBA
Следующий код VBA может помочь вам быстро разделить несколько листов текущей книги на отдельные файлы Excel, пожалуйста, сделайте следующее:
1. Создайте новую папку для книги, которую вы хотите разделить, так как разделенные файлы Excel останутся в той же папке, что и эта основная книга.
2. Удерживайте ALT + F11 ключи в Excel, и он открывает Microsoft Visual Basic для приложений окно.
3. Нажмите Вставить > Модулии вставьте следующий код в окно модуля.
VBA: разделите книгу на несколько книг и сохраните в одной папке
4, нажмите F5 ключ для запуска этого кода. И книга разделена на отдельные файлы Excel в той же папке, что и исходная книга. Смотрите скриншот:
Внимание: Если один из листов имеет то же имя, что и книга, этот VBA не может работать.
Разделите книгу на отдельные файлы Excel / PDF / CSV / TXT с помощью Kutools for Excel легко
Если у вас есть Kutools for Excel установлен, его Разделить книгу Инструмент может легко и быстро разделить несколько листов в отдельные файлы Excel всего за несколько кликов.
Kutools for Excel - Включает более 300 удобных инструментов для Excel. Полнофункциональная бесплатная 30-дневная пробная версия, кредитная карта не требуется! Get It Now
1. После установки Kutools for Excel, нажмите Кутулс Плюс > Разделить книгу , см. снимок экрана:
2. В Разделить книгу диалоговом окне выполните следующие операции:
(1) По умолчанию отмечены все имена рабочих листов. Если вы не хотите разделять некоторые рабочие листы, вы можете снять с них отметку;
(2) Проверьте Сохранить тип вариант;
(3) Из Сохранить как в раскрывающемся списке выберите один тип файла, который вы хотите разделить и сохранить.
(4) Затем нажмите Трещина кнопку.
Внимание: Если вы не хотите разделять скрытые или пустые листы, вы можете проверить Пропустить скрытые листы or Пропускать пустые листы пунктом.
3. В диалоговом окне «Обзор папки» укажите папку назначения для сохранения разделенных отдельных файлов и нажмите кнопку OK кнопку.
Теперь проверенные листы сохраняются как новые отдельные книги. Каждая новая книга названа в соответствии с именем исходного листа. Смотрите скриншот:
Kutools for Excel's Разделить книгу Инструмент позволяет легко разделить активную книгу на отдельные файлы Excel (один файл содержит один рабочий лист), файлы CSV, файлы TXT или файлы PDF по мере необходимости. Вы можете настроить пропуск всех пустых и скрытых листов. Получите бесплатную пробную версию!
Демонстрация: разделите или сохраните каждый рабочий лист одной книги как отдельные файлы excel / txt / csv / pdf
Читайте также: