Vba 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:
Разнести:
- Все данные - при выборе будет произведено разнесение абсолютно всех данных, расположенных на листе.
- Указанный диапазон - будут разнесены только те данные, которые расположены внутри указанного диапазона. Диапазон следует указывать без заголовка. Иначе заголовок будет определен как отдельный критерий и для него будет так же создан свой лист/книга.
Критерии:
- На основании значений - разнесение данных происходит на основании значений в ячейках. На рисунках выше таблица разносится именно на основании значений и в качестве столбца с критериями указан столбец с фамилиями руководителей. Это значит, что после выполнения команды будет создана новая книга, содержащая ровно столько листов, сколько руководителей в таблице. И в каждом листе будут содержаться только те сотрудники, которые работают в отделе, подчиненном данному руководителю.
- На основании цвета заливки - разнесение данных происходит на основании цвета заливки ячеек. В данном случае новым листам/книгам будет присвоено имя, равное числовому коду цвета заливки ячеек, а цвет ярлыков листов - равен цвету заливки ячеек.
- На основании цвета шрифта - разнесение данных происходит на основании цвета шрифта. В данном случае новым листам/книгам будет присвоено имя, равное числовому коду цвета шрифта, а цвет ярлыков листов - равен цвету шрифта.
- На фиксированное количество строк - данные будут разнесены пропорционально указанному количеству строк в окне напротив данной опции. Будут созданы листы/книги, количество строк с данными в которых будет равно указанному. При этом имена книг и листов будут называться диапазонами строк. Например, если выбрать разбиение диапазона по 10 строк, то результирующие листы или книги будут названы: 1-10 , 11-20 , 21-30 и т.д.
Поместить:
-
На разные листы одной книги - данные будут разнесены на разные листы одной книги. Книга создается в процессе выполнения команды. Количество листов соответствует количеству уникальных значений для разнесения. Имя каждого листа соответствует значению критерия, данные по которому занесены в этот лист. Если в качестве критерия выбрано На основании цвета заливки или На основании цвета шрифта, то именем листов будут числовые коды цвета заливки или шрифта ячеек.
Примечание: если в значении критерия имеются символы, недопустимые в имени листа( :/?*[] ), то эти символы удаляются. Если критерий содержит исключительно запрещенные символы, то они все будут удалены, а лист, на который будут помещены такие данные будет назван "_invalid_chars_" .
Если количество символов в значении превышает 30(максимально допустимое количество символов в имени листа - 31), то значение обрезается до 30 символов.
Примечание: если в значении критерия имеются символы, недопустимые в имени книги( :/?*"<>| ), то эти символы удаляются. Если критерий содержит исключительно запрещенные символы, то они все будут удалены, а книга, на которую будут помещены такие данные будет названа "_invalid_chars_".
Если количество символов в значении превышает 30, то значение обрезается до 30 символов(для большей "удобочитаемости", а так же для предотвращения ошибки, возникающей при длине пути к файлу, превышающей корректное определение файла операционной системой).
Номер столбца с критериями разнесения - указывается номер столбца, в котором расположены значения критериев для разнесения. Если на вкладке Основные выбрано Все данные - указывается номер столбца на листе. Т.е. если таблица данных расположена в диапазоне C3:G20 и критерии расположены в столбце D , то следует указать номер столбца 4 . Если на вкладке Основные выбрано Указанный диапазон, то указывается номер столбца внутри выбранного диапазона. Т.е. если указан диапазон C3:G20 и критерии расположены в столбце D , то следует указать номер столбца 2 .
Копировать заголовок на каждый лист - в новые листы/книги будет скопирован диапазон ячеек, указанный в поле. Диапазон для заголовков может быть расположен на любом листе любой открытой книги, а не обязательно на листе со значениями для разнесения. Рекомендуется указывать диапазон, ячейки самой нижней строки которого заполнены полностью. Это необходимо для корректного определения конца заголовка программой при вставке строк данных на лист.
Отправлять создаваемые листы/книги - если установлен, то созданные листы/книги будут отправлены на указанные адреса e-mail. Адреса могут быть указаны как в самой таблице для разнесения, так и отдельным списком соответствия.
- Брать адреса e-mail из столбца - указывается номер столбца. Если на вкладке Основные выбрано Все данные, то указывается номер столбца на листе, даже если сами данные для разнесения начинаются с 3 или любого другого столбца. Т.е. если таблица данных расположена в диапазоне C3:G20 и адреса e-mail при этом расположены в столбце G , то следует указать номер столбца 7 . Если на вкладке Основные выбрано Указанный диапазон, то указывается номер столбца внутри выбранного диапазона. Т.е. если указан диапазон C3:G20 и адреса e-mail при этом расположены в столбце G , то следует указать номер столбца 5 .
В данном случае необходимо, чтобы в самой таблице для разнесения присутствовал столбец с корректными адресами e-mail, на которые необходимо отправлять созданные файлы. При этом необязательно указывать e-mail для каждой строки - достаточно, если e-mail будет записан один раз для каждого критерия. - Адреса по списку соответствия - при выборе данного пункта необходимо заранее подготовить список соответствия адресов e-mail критериям в таблице. На примере таблицы выше список может выглядеть так:
При этом критерии и сами адреса e-mail должны располагаться в двух смежных столбцах: слева критерии, справа - адреса. На примере таблиц выше это столбцы B и C. Т.к. в качестве критерия разнесения на вкладке Основные выбраны были ФИО руководителя из столбца Руководитель, то в качестве списка соответствия необходимо указать диапазон B2:C5 .
Отправка - в этом блоке указывается способ отправки файлов и тема письма.
Тема письма - произвольный текст, который будет указан в создаваемых письмах в качестве темы. Если не указан, то в качестве темы каждого письма будет имя отправляемого файла.
Пользователь - имя пользователя. Как правило совпадает с учетной записью для входа в почту.
Пароль - пароль для входа в почту.
Порт - порт сервера SMTP. У большинства равен 25 или 465. Точное значения порта можно узнать только на самом сервере. Большинство из них размещают информацию по подключению почтовых программ к серверу, откуда можно узнать точные данные.
Как разбить данные на несколько листов на основе столбца в Excel?
Предположим, у вас есть рабочий лист с огромными строками данных, и теперь вам нужно разбить данные на несколько рабочих листов на основе Имя и фамилия столбец (см. следующий снимок экрана), а имена вводятся случайным образом. Может быть, вы можете сначала отсортировать их, а затем скопировать и вставить их один за другим в другие новые рабочие листы. Но для повторного копирования и вставки потребуется ваше терпение. Сегодня я расскажу о некоторых быстрых приемах решения этой задачи.
If you want to split a large worksheet into multiple sheets based on specific column data or rows count, the Kutools for Excel's Split Data feature can help you to solve this task quickly and easily.
Kutools for Excel: with more than 200 handy Excel add-ins, free to try with no limitation in 60 days. Download and free trial Now!
Разделить данные на несколько листов на основе столбца с кодом VBA
Если вы хотите быстро и автоматически разделить данные на основе значения столбца, следующий код VBA - хороший выбор. Пожалуйста, сделайте так:
1. Удерживайте ALT + F11 , чтобы открыть Microsoft Visual Basic для приложений окно.
2. Нажмите Вставить > Модулии вставьте следующий код в окно модуля.
3, Затем нажмите F5 нажмите клавишу для запуска кода, и появится окно подсказки, напоминающее вам о выборе строки заголовка, см. снимок экрана:
4. А затем нажмите OK и во втором поле запроса выберите данные столбца, которые вы хотите разделить на основе, см. снимок экрана:
5, Затем нажмите OK, и все данные на активном листе разбиваются на несколько листов по значению столбца. И разделенные листы названы с именами разделенных ячеек. Смотрите скриншот:
Внимание: Разделенные листы помещаются в конец книги, где находится главный рабочий лист.
Разделите данные на несколько листов на основе столбца с помощью Kutools for Excel
Как новичку в Excel, этот длинный код VBA несколько сложен для нас, и большинство из нас даже не знает, как изменить код по своему усмотрению. Здесь я представлю вам многофункциональный инструмент -Kutools for Excel, Его Разделить данные Утилита не только может помочь вам разделить данные на несколько листов на основе столбца, но также может разделить данные по количеству строк.
Примечание: Чтобы применить это Разделить данные, во-первых, вы должны скачать Kutools for Excel, а затем быстро и легко примените эту функцию.
После установки Kutools for Excel, пожалуйста, сделайте так:
1. Выберите диапазон данных, которые вы хотите разделить.
2. Нажмите Кутулс Плюс > Рабочий лист > Разделить данные, см. снимок экрана:
3. В Разделить данные на несколько листов диалоговое окно, вам необходимо:
1). Выбрать Конкретный столбец вариант в Сплит на основе раздел и выберите значение столбца, по которому вы хотите разделить данные, в раскрывающемся списке. (Если у ваших данных есть заголовки, и вы хотите вставить их в каждый новый разделенный лист, проверьте У моих данных есть заголовки опция).
2). Затем вы можете указать имена разделенных листов под Имя нового листа раздел, укажите правила имен рабочих листов из Правила раскрывающийся список, вы можете добавить Префикс or Суффикс также для имен листов.
3). Щелкните значок OK кнопка. Смотрите скриншот:
4. Теперь данные разделены на несколько листов в новой книге.
Разделите данные на несколько листов на основе столбца с помощью Kutools for Excel
Kutools for Excel включает более 300 удобных инструментов Excel. Бесплатная пробная версия без ограничений в течение 30 дней. Загрузите бесплатную пробную версию прямо сейчас!
Читайте также: