Съвети за Excel: Разделете данните в множество работни листове / работни книги въз основа на стойността на колоната
Когато управлявате големи набори от данни в Excel, може да бъде много полезно да разделите данните в множество работни листове въз основа на конкретни стойности на колони. Този метод подобрява не само организацията на данните, но също така подобрява четливостта и улеснява по-лесния анализ на данните.
Да предположим, че имате голям запис на продажби, съдържащ множество записи като името на продукта, продаденото количество за първото тримесечие. Целта е тези данни да се разделят на отделни работни листове въз основа на името на всеки продукт, така че отделните резултати от продажбите да могат да бъдат анализирани отделно.
Разделете данните в няколко работни листа въз основа на стойността на колоната
Разделяне на данни в множество работни книги въз основа на стойност на колона с VBA код
Разделете данните в няколко работни листа въз основа на стойността на колоната
Обикновено можете първо да сортирате списъка с данни и след това да ги копирате и поставите един по един в други нови работни листове. Но това ще изисква вашето търпение, за да копирате и поставяте многократно. В този раздел ще представим два лесни метода за ефективно справяне с тази задача в Excel, спестявайки ви време и намалявайки потенциала за грешки.
Разделяне на данни в множество работни листове въз основа на стойност на колона с 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 Sub3. След това натиснете F5 клавиш, за да изпълните кода, и изскача поле за подкана, за да ви напомни, че изберете заглавния ред и след това щракнете върху OK. Вижте екранна снимка:
4. Във второто поле за подкана, моля, изберете данните от колоната, въз основа на които искате да разделите, след което щракнете върху OK. Вижте екранна снимка:
5. Всички данни в активния работен лист са разделени на няколко работни листа въз основа на стойностите на колоните. Получените работни листове се наименуват според стойностите в разделените клетки и се поставят в края на работната книга. Вижте екранна снимка:
Разделете данните в множество работни листове въз основа на стойността на колоната с Kutools за Excel
Kutools за Excel носи интелигентна функция – Разделени данни направо във вашата среда на Excel. Разделянето на данни в множество работни листове вече не е предизвикателство. Нашият интуитивен инструмент автоматично разделя вашия набор от данни въз основа на избраната стойност на колона или брой редове, като гарантира, че всяка част от информацията е точно там, където ви е необходима. Кажете сбогом на досадната задача да организирате ръчно вашите електронни таблици и прегърнете по-бърз начин без грешки за управление на вашите данни.
След инсталиране Kutools за Excel, изберете диапазона от данни и след това щракнете Kutools плюс > Разделени данни за да отворите Разделяне на данни в множество работни листове диалогов прозорец.
- Изберете Конкретна колона опция в Разделяне въз основа на и изберете стойността на колоната, въз основа на която искате да разделите данните, от падащия списък.
- Ако вашите данни имат заглавки и искате да ги вмъкнете във всеки нов разделен работен лист, моля, проверете Данните ми имат заглавки опция. (Можете да посочите броя на заглавните редове въз основа на вашите данни. Например, ако вашите данни съдържат две заглавки, моля, въведете 2.)
- След това можете да посочите имената на разделените работни листове под Име на нови работни листове раздел, задайте правилото за имена на работни листове от падащия списък Правила, можете да добавите Префикс or Наставка и за имената на листовете.
- Щракнете върху OK бутон. Вижте екранна снимка:
Сега данните в работния лист са разделени на няколко работни листа в нова работна книга.
Разделяне на данни в множество работни книги въз основа на стойност на колона с VBA код
Понякога, вместо да разделяте данните в множество работни листове, може да е по-полезно да разделите данните в отделни работни книги въз основа на ключова колона. Ето ръководство стъпка по стъпка за това как да използвате VBA код за автоматизиране на процеса на разделяне на данни в множество работни книги въз основа на конкретна стойност на колона.
1. Задръжте натиснат ALT + F11 за да отворите Microsoft Visual Basic за приложения прозорец.
2. кликване Поставете > Модулии поставете следния код в Прозорец на модула.
Sub SplitDataByColToWorkbooks()
' Updateby Extendoffice
Dim lr As Long
Dim ws As Worksheet
Dim vcol, i As Integer
Dim myarr As Variant
Dim title As String
Dim titlerow As Integer
Dim xTRg As Range
Dim xVRg As Range
Dim xWS As Workbook
Dim savePath As String
' Set the directory to save new workbooks
savePath = "C:\Users\AddinsVM001\Desktop\multiple files\" ' Modify this path as needed
Application.DisplayAlerts = False
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.Address(False, False)
titlerow = xTRg.Row
ws.Columns(vcol).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=ws.Cells(1, ws.Columns.Count), Unique:=True
myarr = Application.Transpose(ws.Cells(1, ws.Columns.Count).Resize(ws.Cells(ws.Rows.Count, ws.Columns.Count).End(xlUp).Row).Value)
ws.Cells(1, ws.Columns.Count).Resize(ws.Cells(ws.Rows.Count, ws.Columns.Count).End(xlUp).Row).ClearContents
For i = 2 To UBound(myarr)
Set xWS = Workbooks.Add
ws.Range(title).AutoFilter Field:=vcol, Criteria1:=myarr(i)
ws.Range("A" & titlerow & ":A" & lr).SpecialCells(xlCellTypeVisible).EntireRow.Copy
xWS.Sheets(1).Cells(1, 1).PasteSpecial Paste:=xlPasteAll
xWS.SaveAs Filename:=savePath & myarr(i) & ".xlsx"
xWS.Close SaveChanges:=False
Next i
ws.AutoFilterMode = False
Application.DisplayAlerts = True
ws.Activate
End Sub
3. След това натиснете F5 клавиш, за да изпълните кода, и изскача поле за подкана, за да ви напомни, че изберете заглавния ред и след това щракнете върху OK. Вижте екранна снимка:
4. Във второто поле за подкана, моля, изберете данните от колоната, въз основа на които искате да разделите, след което щракнете върху OK. Вижте екранна снимка:
5. След разделянето всички данни в активния работен лист се разделят на множество работни книги въз основа на стойностите на колоните. Всички разделени работни книги се записват в указаната от вас папка. Вижте екранна снимка:
Свързани членове:
- Разделете данните в няколко работни листа по брой редове
- Ефективното разделяне на голям диапазон от данни на множество работни листове на Excel въз основа на конкретен брой редове може да рационализира управлението на данни. Например, разделянето на набор от данни на всеки 5 реда на множество листа може да го направи по-управляем и организиран. Това ръководство предлага два практически метода за бързо и лесно изпълнение на тази задача.
- Обединете две или повече таблици в една въз основа на ключови колони
- Да предположим, че имате три таблици в работна книга, сега искате да обедините тези таблици в една таблица въз основа на съответните ключови колони, за да получите резултата, както е показано на екранната снимка по-долу. Това може да е неприятна задача за повечето от нас, но, моля, не се притеснявайте, в тази статия ще ви представя някои методи за решаване на този проблем.
- Разделяне на текстови низове чрез разделител на няколко реда
- Обикновено можете да използвате функцията „Текст в колона“, за да разделите съдържанието на клетките на множество колони чрез специфичен разделител, като запетая, точка, точка и запетая, наклонена черта и т.н. Но понякога може да се наложи да разделите съдържанието на клетките с разделители на множество редове и повторете данните от други колони, както е показано на екранната снимка по-долу. Имате ли добри начини за справяне с тази задача в Excel? Този урок ще представи някои ефективни методи за завършване на тази работа в Excel.
- Разделяне на многоредово съдържание на клетка в отделни редове/колони
- Да предположим, че имате многоредово съдържание на клетка, което е разделено с Alt + Enter, и сега трябва да разделите многоредовото съдържание на отделни редове или колони, какво можете да направите? В тази статия ще научите как бързо да разделяте съдържанието на многоредова клетка в отделни редове или колони.
Най-добрите инструменти за продуктивност в офиса
Усъвършенствайте уменията си за Excel с Kutools за Excel и изпитайте ефективност, както никога досега. Kutools за Excel предлага над 300 разширени функции за повишаване на производителността и спестяване на време. Щракнете тук, за да получите функцията, от която се нуждаете най-много...
Раздел Office Внася интерфейс с раздели в Office и прави работата ви много по-лесна
- Разрешете редактиране и четене с раздели в Word, Excel, PowerPoint, Publisher, Access, Visio и Project.
- Отваряйте и създавайте множество документи в нови раздели на един и същ прозорец, а не в нови прозорци.
- Увеличава вашата производителност с 50% и намалява стотици кликвания на мишката за вас всеки ден!
