Разделение таблицы по листам
В 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:
При любых изменениях в исходных данных достаточно будет обновить соответствующую таблицу правой кнопкой мыши — команда Обновить (Refresh) или обновить сразу все города оптом, используя кнопку Обновить всё на вкладке Данные (Data — Refresh All) .
Ссылки по теме
- Что такое макросы, как их создавать и использовать
- Сохранение листов книги как отдельных файлов
- Сборка данных со всех листов книги в одну таблицу
покупка
Как разделить книгу на отдельные файлы 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. Теперь все выбранные листы копируются в новую книгу. Нажмите Файл > Save. чтобы сохранить новую книгу.
Быстро разбейте книгу на отдельные файлы Excel / PDF / TXT / CSV в Excel
Обычно мы можем разделить книгу на отдельные файлы Excel с помощью Переместить или скопировать функция в Excel. Но Kutools for Excel’s Разделить книгу Утилита может помочь вам легко разделить книгу и сохранить каждый лист как отдельный файл PDF / TEXT / CSV или книгу в Excel.
Получить 30 -дневная полнофункциональная бесплатная пробная версия прямо сейчас!
Kutools for Excel — Дополните Excel более чем 300 основными инструментами. Наслаждайтесь полнофункциональным 30 -дневная БЕСПЛАТНАЯ пробная версия без необходимости использования кредитной карты! Get It Now
Разделить книгу на отдельные файлы Excel с кодом VBA
Следующий код VBA может помочь вам быстро разделить несколько листов текущей книги на отдельные файлы Excel, пожалуйста, сделайте следующее:
1. Создайте новую папку для книги, которую вы хотите разделить, так как разделенные файлы Excel останутся в той же папке, что и эта основная книга.
2. Удерживайте ALT + F11 ключи в Excel, и он открывает Microsoft Visual Basic для приложений окно.
3. Нажмите Вставить > Модулии вставьте следующий код в окно модуля.
VBA: разделите книгу на несколько книг и сохраните в одной папке
Sub Splitbook() 'Updateby20140612 Dim xPath As String xPath = Application.ActiveWorkbook.Path Application.ScreenUpdating = False Application.DisplayAlerts = False For Each xWs In ThisWorkbook.Sheets xWs.Copy Application.ActiveWorkbook.SaveAs Filename:=xPath & "\" & xWs.Name & ".xlsx" Application.ActiveWorkbook.Close False Next Application.DisplayAlerts = True Application.ScreenUpdating = True End Sub
4, нажмите F5 ключ для запуска этого кода. И книга разделена на отдельные файлы Excel в той же папке, что и исходная книга. Смотрите скриншот:
Внимание: Если один из листов имеет то же имя, что и книга, этот VBA не может работать.
Разделите книгу на отдельные файлы Excel / PDF / CSV / TXT с помощью Kutools for Excel легко
Если у вас есть Kutools for Excel установлен, его Разделить книгу Инструмент может легко и быстро разделить несколько листов в отдельные файлы Excel всего за несколько кликов.
Kutools for Excel — Дополните Excel более чем 300 основными инструментами. Наслаждайтесь полнофункциональным 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
Kutools for Excel: Более 300 удобных инструментов у вас под рукой! Начните свой 30 Бесплатная пробная версия на день без каких-либо ограничений сегодня. Скачать
Как разделить файл Excel на отдельные файлы (по листам)
Такое бывает довольно часто, делаешь ежегодный отчет, а в нем 12 месяцев, соответственно 12 листов. И нужно разделить этот файл таким образом, чтобы каждый лист стал отдельным файлом.
И конечно же, можно сделать это руками, но это крайне долго и неэффективно.
Я продемонстрирую вам простой код Visual Basic, который выполнит задачу за вас.
Делим файл Excel на несколько файлов по листам
Допустим, у нас есть ежегодный отчет, в котором по листам расписаны показатели компании за каждый месяц. Как на картинке ниже:
Код Visual Basic, который разделит таблицу на несколько файлов по месяцам:
Sub SplitEachWorksheet() Dim FPath As String FPath = Application.ActiveWorkbook.Path Application.ScreenUpdating = False Application.DisplayAlerts = False For Each ws In ThisWorkbook.Sheets ws.Copy Application.ActiveWorkbook.SaveAs Filename:=FPath & "\" & ws.Name & ".xlsx" Application.ActiveWorkbook.Close False Next Application.DisplayAlerts = True Application.ScreenUpdating = True End Sub Перед тем, как запустите этот код, сделайте следущее:
Узнайте Excel как свои пять пальцев на курсе по таблицам от Skillbox
- Создайте новую папку, куда поместите результаты работы этого кода;
- А также, на всякий случай, сделайте копию оригинального файла.
Теперь создайте функцию в Visual Basic и смело запускайте код.
Этот код сам найдет путь до папки с файлом.
Как он работает?
Довольно просто, он открывает каждый лист и сохраняет его как отдельный файл с тем же названием.
Куда поместить этот код?
- Щелкните на «Разработчик»;
- Далее откройте VBA;
- Правой кнопкой мышки на любой лист;.
- Щелкните на «Insert» -> «Module»;
- Поместите наш код в открывшееся окошко;
- Теперь запустите код.
Итак, как только вы запустите код, он сразу же разделит ваш файл на несколько файлов по листам. Это крайне удобно, советую его сохранить. Даже если сейчас он вам не нужен, в будущем обязательно пригодится.
Как я говорил ранее, имя файла такое же, как и имя листа.
Также не забудьте сохранить файл с соответствующим расширением(.XLSM), так как мы используем функции Visual Basic.
В коде я специально сделал так, чтобы вы не видели все что происходит и это вам не мешало. Вы можете исправить это если вам наоборот нужно видеть то, что происходит.
Но также, опять повторюсь, обязательно сделайте копию вашего файла перед использованием функции! Потому что если работа Excel завершится из-за какой-либо ошибки или произойдет еще что-то неожиданное вы можете потерять свои данные!
Делим файл Excel на несколько PDF файлов по листам
Вот код для такого случая:
Sub SplitEachWorksheet() Dim FPath As String FPath = Application.ActiveWorkbook.Path Application.ScreenUpdating = False Application.DisplayAlerts = False For Each ws In ThisWorkbook.Sheets ws.Copy Application.ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=FPath & "\" & ws.Name & ".xlsx" Application.ActiveWorkbook.Close False Next Application.DisplayAlerts = True Application.ScreenUpdating = True End Sub И снова, не забудьте сначала создать папку и обязательно сделать копию оригинального файла. Код разделит вашу табличку по страницам и создаст для каждой страницы отдельный PDF файл.
Разделите только те рабочие листы, в которых содержится слово/фраза, на отдельные файлы Excel
Бывают и такие ситуации, что отдельный файл нужно создать только для тех страниц, в названии которых есть определенный текст.
Допустим, у вас есть страницы отчета за разные года, в названии каждого листа указан год и месяц. Но вам нужно сохранить только те листы, которые относятся к 2020 году. Как это сделать?
Рекомендуем курс Excel по анализу данных от Skypro — очень глубокое и яркое погружение в Эксель.
Вот код Visual Basic:
Sub SplitEachWorksheet() Dim FPath As String Dim TexttoFind As String TexttoFind = "2020" FPath = Application.ActiveWorkbook.Path Application.ScreenUpdating = False Application.DisplayAlerts = False For Each ws In ThisWorkbook.Sheets If InStr(1, ws.Name, TexttoFind, vbBinaryCompare) <> 0 Then ws.Copy Application.ActiveWorkbook.SaveAs Filename:=FPath & "\" & ws.Name & ".xlsx" Application.ActiveWorkbook.Close False End If Next Application.DisplayAlerts = True Application.ScreenUpdating = True End Sub Как вы могли заметить, прямо в коде, мы создали переменную и задали ей значение 2020.
Затем этот код просто проходится по каждой странице и проверяет есть ли в имени нужная нам часть (то есть нужный год). А далее сохраняет отдельно только те листы, в имени которых он нашел совпадения.
Если совпадения не будут найдены — результат будет 0.
В этом коде используется цикл «Если/То». Если он находит нужное текстовое значение в имени листа, то сохраняет его отдельно, если не находит — просто пропускает.
Узнайте Excel как свои пять пальцев на курсе по таблицам от Skillbox
Экспортировать листы как отдельные файлы.
× The language used in menus and dialogs (Язык, используемый в меню и диалоговых окнах) in ASAP Utilities can be set to «Русский (Russian)».
Таблицы › 10. Экспортировать листы как отдельные файлы.
С помощью этой утилиты можно сохранить все или выделенные листы в книге как отдельные файлы.
Вы можете выбрать папку, в которую хотите сохранить листы.
Последние использованные параметры будут сохранены.
Снимки экрана
Пример снимка экрана: 1
Пример снимка экрана: 2
Пример снимка экрана: 3
Пример снимка экрана: 4
Practical tricks on how this can help you
Запуск этой утилиты
- Нажмите кнопку ASAP Utilities › Таблицы › 10. Экспортировать листы как отдельные файлы.
- Укажите сочетание клавиш: ASAP Utilities › Избранное и сочетания клавиш › Редактировать избранное и сочетания клавиш.
Additional keywords for this tool:
индивидуальный, знак табуляции, знаки табуляции, каждый лист как отдельный файл, разделить файл excel, разделить книгу, разделить книгу в новом файле для каждого листа, за один раз, одновременно, количество листов как отдельная книга, textfile, разделитель файлов, создать, сохранить, возможность сохранить только отфильтрованные результаты, UTF8, UTF16, UTF-8, UTF-16