Note: The other languages of the website are Google-translated. Back to English

Как разбить данные на несколько листов на основе столбца в Excel?

Предположим, у вас есть рабочий лист с огромными строками данных, и теперь вам нужно разбить данные на несколько рабочих листов на основе Имя и фамилия столбец (см. следующий снимок экрана), а имена вводятся случайным образом. Может быть, вы можете сначала отсортировать их, а затем скопировать и вставить их один за другим в другие новые рабочие листы. Но для повторного копирования и вставки потребуется ваше терпение. Сегодня я расскажу о некоторых быстрых приемах решения этой задачи.

документ разбивает данные по столбцам 1

Разделить данные на несколько листов на основе столбца с кодом VBA

Разделите данные на несколько листов на основе столбца с помощью Kutools for Excel


Разделить данные на несколько листов на основе столбца с кодом VBA

Если вы хотите быстро и автоматически разделить данные на основе значения столбца, следующий код VBA - хороший выбор. Пожалуйста, сделайте так:

1. Удерживайте ALT + F11 , чтобы открыть Microsoft Visual Basic для приложений окно.

2. Нажмите Вставить > Модулии вставьте следующий код в окно модуля.

Sub Splitdatabycol()
'updateby Extendoffice
Dim lr As Long
Dim ws As Worksheet
Dim vcol, i As Integer
Dim icol As Long
Dim myarr As Variant
Dim title As String
Dim titlerow As Integer
Dim xTRg As Range
Dim xVRg As Range
Dim xWSTRg As Worksheet
Dim xWS As Worksheet
On Error Resume Next
Set xTRg = Application.InputBox("Please select the header rows:", "Kutools for Excel", "", Type:=8)
If TypeName(xTRg) = "Nothing" Then Exit Sub
Set xVRg = Application.InputBox("Please select the column you want to split data based on:", "Kutools for Excel", "", Type:=8)
If TypeName(xVRg) = "Nothing" Then Exit Sub
vcol = xVRg.Column
Set ws = xTRg.Worksheet
lr = ws.Cells(ws.Rows.Count, vcol).End(xlUp).Row
title = xTRg.AddressLocal
titlerow = xTRg.Cells(1).Row
icol = ws.Columns.Count
ws.Cells(1, icol) = "Unique"
Application.DisplayAlerts = False
If Not Evaluate("=ISREF('xTRgWs_Sheet!A1')") Then
Sheets.Add(after:=Worksheets(Worksheets.Count)).Name = "xTRgWs_Sheet"
Else
Sheets("xTRgWs_Sheet").Delete
Sheets.Add(after:=Worksheets(Worksheets.Count)).Name = "xTRgWs_Sheet"
End If
Set xWSTRg = Sheets("xTRgWs_Sheet")
xTRg.Copy
xWSTRg.Paste Destination:=xWSTRg.Range("A1")
ws.Activate
For i = (titlerow + xTRg.Rows.Count) To lr
On Error Resume Next
If ws.Cells(i, vcol) <> "" And Application.WorksheetFunction.Match(ws.Cells(i, vcol), ws.Columns(icol), 0) = 0 Then
ws.Cells(ws.Rows.Count, icol).End(xlUp).Offset(1) = ws.Cells(i, vcol)
End If
Next
myarr = Application.WorksheetFunction.Transpose(ws.Columns(icol).SpecialCells(xlCellTypeConstants))
ws.Columns(icol).Clear
For i = 2 To UBound(myarr)
ws.Range(title).AutoFilter field:=vcol, Criteria1:=myarr(i) & ""
If Not Evaluate("=ISREF('" & myarr(i) & "'!A1)") Then
Set xWS = Sheets.Add(after:=Worksheets(Worksheets.Count))
xWS.Name = myarr(i) & ""
Else
xWS.Move after:=Worksheets(Worksheets.Count)
End If
xWSTRg.Range(title).Copy
xWS.Paste Destination:=xWS.Range("A1")
ws.Range("A" & (titlerow + xTRg.Rows.Count) & ":A" & lr).EntireRow.Copy xWS.Range("A" & (titlerow + xTRg.Rows.Count))
Sheets(myarr(i) & "").Columns.AutoFit
Next
xWSTRg.Delete
ws.AutoFilterMode = False
ws.Activate
Application.DisplayAlerts = True
End Sub

3, Затем нажмите F5 нажмите клавишу для запуска кода, и появится окно подсказки, напоминающее вам о выборе строки заголовка, см. снимок экрана:

документ разбивает данные по столбцам 7

4. А затем нажмите OK и во втором поле запроса выберите данные столбца, которые вы хотите разделить на основе, см. снимок экрана:

документ разбивает данные по столбцам 8

5, Затем нажмите OK, и все данные на активном листе разбиваются на несколько листов по значению столбца. И разделенные листы названы с именами разделенных ячеек. Смотрите скриншот:

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

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


Разделите данные на несколько листов на основе столбца с помощью Kutools for Excel

Как новичку в Excel, этот длинный код VBA несколько сложен для нас, и большинство из нас даже не знает, как изменить код по своему усмотрению. Здесь я представлю вам многофункциональный инструмент -Kutools for Excel, Его Разделить данные Утилита не только может помочь вам разделить данные на несколько листов на основе столбца, но также может разделить данные по количеству строк.

Примечание:Чтобы применить это Разделить данные, во-первых, вы должны скачать Kutools for Excel, а затем быстро и легко примените эту функцию.

После установки Kutools for Excel, пожалуйста, сделайте так:

1. Выберите диапазон данных, которые вы хотите разделить.

2. Нажмите Кутулс Плюс > Рабочий лист > Разделить данные, см. снимок экрана:

документ разбивает данные по столбцам 3

3. В Разделить данные на несколько листов диалоговое окно, вам необходимо:

1). Выбрать Конкретный столбец вариант в Сплит на основе раздел и выберите значение столбца, по которому вы хотите разделить данные, в раскрывающемся списке. (Если у ваших данных есть заголовки, и вы хотите вставить их в каждый новый разделенный лист, проверьте У моих данных есть заголовки опция).

2). Затем вы можете указать имена разделенных листов под Имя нового листа раздел, укажите правила имен рабочих листов из Правила раскрывающийся список, вы можете добавить Префикс or Суффикс также для имен листов.

3). Щелкните значок OK кнопка. Смотрите скриншот:

документ разбивает данные по столбцам 4

4. Теперь данные разделены на несколько листов в новой книге.

документ разбивает данные по столбцам 5

Нажмите, чтобы загрузить Kutools for Excel и бесплатную пробную версию сейчас!


Разделите данные на несколько листов на основе столбца с помощью Kutools for Excel

Kutools for Excel включает более 300 удобных инструментов Excel. Бесплатная пробная версия без ограничений в течение 30 дней. Загрузите бесплатную пробную версию прямо сейчас!


Связанная статья:

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


Лучшие инструменты для работы в офисе

Kutools for Excel решает большинство ваших проблем и увеличивает вашу производительность на 80%

  • Снова использовать: Быстро вставить сложные формулы, диаграммы и все, что вы использовали раньше; Зашифровать ячейки с паролем; Создать список рассылки и отправлять электронные письма ...
  • Бар Супер Формулы (легко редактировать несколько строк текста и формул); Макет для чтения (легко читать и редактировать большое количество ячеек); Вставить в отфильтрованный диапазон...
  • Объединить ячейки / строки / столбцы без потери данных; Разделить содержимое ячеек; Объединить повторяющиеся строки / столбцы... Предотвращение дублирования ячеек; Сравнить диапазоны...
  • Выберите Дубликат или Уникальный Ряды; Выбрать пустые строки (все ячейки пустые); Супер находка и нечеткая находка во многих рабочих тетрадях; Случайный выбор ...
  • Точная копия Несколько ячеек без изменения ссылки на формулу; Автоматическое создание ссылок на несколько листов; Вставить пули, Флажки и многое другое ...
  • Извлечь текст, Добавить текст, Удалить по позиции, Удалить пробел; Создание и печать промежуточных итогов по страницам; Преобразование содержимого ячеек в комментарии...
  • Суперфильтр (сохранять и применять схемы фильтров к другим листам); Расширенная сортировка по месяцам / неделям / дням, периодичности и др .; Специальный фильтр жирным, курсивом ...
  • Комбинируйте книги и рабочие листы; Объединить таблицы на основе ключевых столбцов; Разделить данные на несколько листов; Пакетное преобразование xls, xlsx и PDF...
  • Более 300 мощных функций. Поддерживает Office / Excel 2007-2019 и 365. Поддерживает все языки. Простое развертывание на вашем предприятии или в организации. Полнофункциональная 30-дневная бесплатная пробная версия. 60-дневная гарантия возврата денег.
вкладка kte 201905

Вкладка Office: интерфейс с вкладками в Office и упрощение работы

  • Включение редактирования и чтения с вкладками в Word, Excel, PowerPoint, Издатель, доступ, Visio и проект.
  • Открывайте и создавайте несколько документов на новых вкладках одного окна, а не в новых окнах.
  • Повышает вашу продуктивность на 50% и сокращает количество щелчков мышью на сотни каждый день!
офисный дно
Сортировать комментарии по
Комментарии (295)
Оценок пока нет. Оцените первым!
Этот комментарий был сведен к минимуму модератором на сайте
Разделение данных на несколько листов на основе столбца с кодом VBA показывает некоторую ошибку. пожалуйста, попробуйте исправить это и обновить то же самое. Если вы предоставите примеры файлов Excel, это будет очень полезно.
Этот комментарий был сведен к минимуму модератором на сайте
Привет, спасибо за код, он работает для меня! Я пытаюсь найти код, который разбивает один мастер-лист на несколько листов в зависимости от даты.
Этот комментарий был сведен к минимуму модератором на сайте
Это было невероятно! Этот процесс занял бы у меня больше часа, но он был сделан в течение 30 секунд. Этот я сохраню для своей библиотеки VBA. Благодарю вас!
Этот комментарий был сведен к минимуму модератором на сайте
Привет, у меня на листе 30000 ячеек, и мне нужно разделить их за несколько месяцев. есть ли код, который я могу использовать, чтобы сделать это быстрее. У меня есть 8 столбцов, а дата - столбец B. Я играл с приведенным выше кодом, но не смог. Не могли бы вы помочь мне с этим. заранее спасибо
Этот комментарий был сведен к минимуму модератором на сайте
Я получаю следующую ошибку: Ошибка выполнения '6' overflow После отладки покажите строку For i = 2 To Ir Мои строки excel превышают 500,000 XNUMX. Есть ли решение Большое спасибо за код. С наилучшими пожеланиями, Лок
Этот комментарий был сведен к минимуму модератором на сайте
Привет, большое спасибо за код. Я получаю следующую ошибку: Ошибка выполнения '6' переполняется в строке For i = 2 To Ir Любое решение для этого. Спасибо
Этот комментарий был сведен к минимуму модератором на сайте
Я получаю сообщение об ошибке при нажатии F5 - GoTo Box запрашивает ссылку??
Этот комментарий был сведен к минимуму модератором на сайте
Процесс VBA работал отлично, большое спасибо за то, что поделились своим опытом и сэкономили мне много времени!
Этот комментарий был сведен к минимуму модератором на сайте
Код VBA работал отлично. Кажется, что листы не обновляются, так как изменения вносятся в Sheet1. Пожалуйста помогите.
Этот комментарий был сведен к минимуму модератором на сайте
Привет, спасибо за код, он работает для меня! У меня только два вопроса/замечания. 1 скопированные данные не включают макет исходного файла. Можно ли скопировать данные в виде таблицы с автофильтром? 2 скопированные данные не ограничены диапазоном заголовков. Можно ли настроить код для определенного диапазона или имени таблицы? Эти корректировки были бы очень полезны. С уважением, Питер
Этот комментарий был сведен к минимуму модератором на сайте
Работает как шарм! Спасибо.
Этот комментарий был сведен к минимуму модератором на сайте
Работает как шарм... Спасибо за премиум-код... :lol:
Этот комментарий был сведен к минимуму модератором на сайте
Большое спасибо, это сработало отлично. Однако что произойдет, если я захочу, чтобы данные на каждой вкладке снова отсортировались (с использованием другого столбца)? По сути, этот VBA разбивает его на вкладки, но я могу захотеть разбить его дальше ... возможно ли это?
Этот комментарий был сведен к минимуму модератором на сайте
Привет, Джонатан, старый комментарий, который я знаю, но может быть полезен для других в будущем: мне нужно было это сделать, но я не смог найти простой способ с помощью VBA. Однако я обнаружил, что если вы создадите новый столбец в своей электронной таблице как объединение двух, например, =A2&" "&A1, это даст вам 2 ячейку с обоими наборами информации. Затем вы можете запустить вышеуказанный модуль, и он отлично работает! Изменить. Данные в столбцах должны быть короче 1 символов, иначе данные не будут скопированы (отображается как ошибка в модуле), и вы получите пустой лист в середине ваших новых листов.
Этот комментарий был сведен к минимуму модератором на сайте
Потрясающе.. Это было потрясающе. Я так долго боролся с этой проблемой, и этот код пришел как передышка. Спасибо, что поделились.
Этот комментарий был сведен к минимуму модератором на сайте
Удивительно. Спасибо за публикацию.
Этот комментарий был сведен к минимуму модератором на сайте
Код работал как шарм для меньших данных (менее 1200 строк). Я пытался использовать на большом листе (17000 строк), и он просто разбился после разделения на 10-12 листов. Поэтому мы попытались разделить исходные данные на 3 разные книги и все же закрыли нас. У нас Windows 7, и наши компьютеры не такие уж и медленные. Предлагаете ли вы какие-либо ограниченные строки данных для безопасного использования этого кода? Любое предложение будет оценено.
Этот комментарий был сведен к минимуму модератором на сайте
Код работал как шарм для меньших данных (менее 1200 строк). Я пытался использовать на большом листе (17000 строк), и он просто разбился после разделения на 10-12 листов. Поэтому мы попытались разделить исходные данные на 3 разные книги и все же закрыли нас. У нас Windows 7, и наши компьютеры не такие уж и медленные. Предлагаете ли вы какие-либо ограниченные строки данных для безопасного использования этого кода? Любое предложение будет оценено. В чем я не уверен: какое максимальное количество строк может поддерживать макрос? Я мог бы поиграть с этим... Это где-то между 20k и 40k![/quote]
Этот комментарий был сведен к минимуму модератором на сайте
Столкнувшись с той же проблемой. Код работает для листов, где данных меньше строк, однако для больших данных отображается ошибка «Excel не может выполнить эту задачу с доступными ресурсами. Выберите меньше данных или закройте другие приложения» (нет другого приложения, работающего одновременно) Код работал как шарм для меньших данных (менее 1200 строк). Я пытался использовать на большом листе (17000 строк), и он просто разбился после разделения на 10-12 листов. Поэтому мы попытались разделить исходные данные на 3 разные книги и все же закрыли нас. У нас Windows 7, и наши компьютеры не такие уж и медленные. Предлагаете ли вы какие-либо ограниченные строки данных для безопасного использования этого кода? Любое предложение будет оценено.
Этот комментарий был сведен к минимуму модератором на сайте
Ты мой герой на все времена! Я охотился за этим месяцами, но безуспешно. Я должен делать это еженедельно/ежемесячно, разбивая отчеты на более чем 147 рабочих листов, и они не получат мне kutools. На этой ноте... Мне действительно нужно научиться программировать. :( Но спасибо!
Этот комментарий был сведен к минимуму модератором на сайте
Привет, у меня есть лист с 65000 записей и 8-разными случаями, поэтому в основном он должен генерировать 80 разных листов. Я пытался запустить этот код, но он выдает Runtime Error 6 Overflow. Можно ли изменить этот код, чтобы решить мою проблему? Пожалуйста, ваша помощь будет высоко оценена.
Этот комментарий был сведен к минимуму модератором на сайте
[quote]Привет, у меня есть лист с 65000 записей и 8-разными случаями, поэтому в основном он должен генерировать 80 разных листов. Я пытался запустить этот код, но он выдает Runtime Error 6 Overflow. Можно ли изменить этот код, чтобы решить мою проблему? Пожалуйста, ваша помощь будет высоко оценена.Автор ACE[/quote] Попробуйте изменить Dim vcol, i As Integer на Dim vcol, i As Long
Этот комментарий был сведен к минимуму модератором на сайте
Привет, я попытался изменить DIM vcol на LOng, и он работал хорошо, но внезапно из-за ошибки не хватило памяти для выполнения этого действия, попробуйте использовать меньше данных или закрыть другие приложения. Хотя у меня нет других открытых приложений. У меня чуть больше 100 тыс. строк и прибл. Размер файла 16 мб. любая помощь будет оценена. Спасибо Мустафа
Этот комментарий был сведен к минимуму модератором на сайте
Удивительный кусок кода - работает отлично (если вы измените переменные на те, которые нужны вашей электронной таблице)
Этот комментарий был сведен к минимуму модератором на сайте
У меня есть лист с переменным количеством строк. В одном из столбцов указаны даты, начиная с 2010 года. В других столбцах указаны названия фондов с данными NAV для каждого фонда по отношению к дате. Поэтому я не хочу разбивать столбцы на разные листы, я хочу разделить каждое НАЗВАНИЕ ФОНДОВ на отдельный лист с данными NAV на дату конца месяца, а не на ежедневные даты. Можно ли это сделать или это невозможно?
Этот комментарий был сведен к минимуму модератором на сайте
У меня есть рабочий лист, который я использую, и я пытаюсь найти код vba, который изменит имя учетной записи и скопирует конкретную строку в новую книгу и лист с тем же именем, вы можете помочь?
Этот комментарий был сведен к минимуму модератором на сайте
Большой! Код VBA работает, спасибо! Мне нужно, чтобы эти выходные рабочие листы находились в отдельных файлах Excel вместо рабочих листов, и возникает ошибка, когда я разбиваю на множество рабочих листов.
Этот комментарий был сведен к минимуму модератором на сайте
Starscor и Тим, если вы хотите разделить листы файла на несколько файлов, используя имена строк, на этой же веб-странице есть небольшой код макроса, который делает это, просто выполните поиск «разделить книгу на отдельные файлы Excel». найдет это. Добавьте код этого примера в конец этого, удалив дубликат end sub и sub, конечно, и вы получите по одному файлу для каждого.
Этот комментарий был сведен к минимуму модератором на сайте
Может ли кто-нибудь помочь мне, как одновременно сортировать столбцы на разных листах в одной книге, а также удалять дубликаты на разных листах, поскольку у меня около 65 листов в одной книге.
Этот комментарий был сведен к минимуму модератором на сайте
это так захватывающе! Спасибо. Я искал это в течение некоторого времени.
Этот комментарий был сведен к минимуму модератором на сайте
Отлично - спасибо, что поделились этим. Даже распространяет выделение/формат на новые рабочие листы!
Здесь еще нет комментариев
Загрузить ещё
Оставляйте свои комментарии
Публикация как гость
×
Оценить этот пост:
0   Характеристики
Предлагаемые места