Kako razdeliti podatke na več delovnih listov glede na stolpec v Excelu?
Recimo, da imate delovni list z ogromnimi vrstami podatkov, zdaj pa morate podatke razdeliti na več delovnih listov, ki temeljijo na Ime stolpca (glej naslednjo sliko zaslona), imena pa se vnesejo naključno. Morda jih lahko najprej razvrstite, nato pa kopirate in prilepite enega za drugim v druge nove delovne liste. Vendar boste za potrpljenje večkrat potrebovali potrpljenje. Danes bom govoril o nekaj hitrih trikih za rešitev te naloge.
Razdelite podatke na več delovnih listov na podlagi stolpca s kodo VBA
Razdelite podatke na več delovnih listov na podlagi stolpca s programom Kutools za Excel
Razdelite podatke na več delovnih listov na podlagi stolpca s kodo VBA
Če želite podatke na podlagi vrednosti stolpca razdeliti hitro in samodejno, je naslednja koda VBA dobra izbira. Naredite to:
1. Držite tipko ALT + F11 tipke za odpiranje Microsoft Visual Basic za aplikacije okno.
2. Kliknite Vstavi > Moduliin prilepite naslednjo kodo v okno modula.
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. Nato pritisnite F5 tipko za zagon kode in izpiše se pozivno polje, ki vas opomni, da izberete vrstico glave, glejte sliko zaslona:
4. In nato kliknite OK in v drugem pozivu izberite podatke stolpca, na podlagi katerih želite razdeliti, glejte sliko zaslona:
5. Nato kliknite OK, vsi podatki v aktivnem delovnem listu pa so glede na vrednost stolpca razdeljeni na več delovnih listov. In deljeni delovni listi so poimenovani z imeni deljenih celic. Oglejte si posnetek zaslona:
Opombe: Razdeljeni delovni listi so nameščeni na koncu delovnega zvezka, kjer je glavni delovni list.
Razdelite podatke na več delovnih listov na podlagi stolpca s programom Kutools za Excel
Kot začetnik Excela je ta dolga koda VBA za nas nekoliko težka in večina od nas niti ne ve, kako spremeniti kodo, kot jo potrebujemo. Tukaj vam bom predstavil večnamensko orodje -Kutools za Excel, njeno Razdeljeni podatki pripomoček vam lahko pomaga razdeliti podatke na več delovnih listov glede na stolpec, temveč lahko podatke razdeli po številu vrstic.
Opomba:Če želite uporabiti to Razdeljeni podatki, najprej bi morali prenesti Kutools za Excelin nato funkcijo hitro in enostavno uporabite.
Po namestitvi Kutools za Excel, naredite tako:
1. Izberite obseg podatkov, ki jih želite razdeliti.
2. Kliknite Kutools Plus > Delovni list > Razdeljeni podatki, glej posnetek zaslona:
3. v Razdelite podatke na več delovnih listov pogovorno okno, morate:
1). Izberite Poseben stolpec možnost v Razdeljeno na podlagi in izberite vrednost stolpca, na podlagi katerega želite razdeliti podatke na spustnem seznamu. (Če imajo vaši podatki glave in jih želite vstaviti v vsak nov deljeni delovni list, preverite Moji podatki imajo glave možnost.)
2). Nato lahko pod imenom imena delovnega lista določite Ime novega delovnega lista v razdelku določite pravila za imena delovnih listov iz Pravila spustni seznam, lahko dodate Prefiks or pripona tudi za imena listov.
3). Kliknite OK . Oglejte si posnetek zaslona:
4. Zdaj so podatki razdeljeni na več delovnih listov v novem delovnem zvezku.
Kliknite za prenos Kutools za Excel in brezplačno preskusno različico zdaj!
Razdelite podatke na več delovnih listov na podlagi stolpca s programom Kutools za Excel
Kutools za Excel vključuje več kot 300 priročnih orodij Excel. Brezplačno poskusite brez omejitev v 30 dneh. Prenesite brezplačno preskusno različico zdaj!
Povezani članek:
Kako razdeliti podatke na več delovnih listov po številu vrstic?
Najboljša pisarniška orodja za produktivnost
Napolnite svoje Excelove spretnosti s Kutools za Excel in izkusite učinkovitost kot še nikoli prej. Kutools za Excel ponuja več kot 300 naprednih funkcij za povečanje produktivnosti in prihranek časa. Kliknite tukaj, če želite pridobiti funkcijo, ki jo najbolj potrebujete...
Kartica Office prinaša vmesnik z zavihki v Office in poenostavi vaše delo
- Omogočite urejanje in branje z zavihki v Wordu, Excelu, PowerPointu, Publisher, Access, Visio in Project.
- Odprite in ustvarite več dokumentov v novih zavihkih istega okna in ne v novih oknih.
- Poveča vašo produktivnost za 50%in vsak dan zmanjša na stotine klikov miške za vas!
