Nasveti za Excel: Razdelite podatke v več delovnih listov / delovnih zvezkov na podlagi vrednosti stolpca
Pri upravljanju velikih naborov podatkov v Excelu je lahko zelo koristno, če podatke razdelite na več delovnih listov na podlagi določenih vrednosti stolpcev. Ta metoda ne izboljša samo organizacije podatkov, temveč tudi izboljša berljivost in olajša analizo podatkov.
Recimo, da imate velik prodajni zapis, ki vsebuje več vnosov, kot je ime izdelka, prodana količina v prvem četrtletju. Cilj je razdeliti te podatke v ločene delovne liste glede na vsako ime izdelka, tako da je mogoče posamezno prodajno uspešnost analizirati ločeno.
Podatke razdelite na več delovnih listov glede na vrednost stolpca
Razdelite podatke v več delovnih zvezkov glede na vrednost stolpca s kodo VBA
Podatke razdelite na več delovnih listov glede na vrednost stolpca
Običajno lahko najprej razvrstite seznam podatkov, nato pa jih enega za drugim kopirate in prilepite v druge nove delovne liste. Toda to bo zahtevalo vašo potrpežljivost za večkratno kopiranje in lepljenje. V tem razdelku bomo predstavili dve enostavni metodi za učinkovito reševanje te naloge v Excelu, s čimer boste prihranili čas in zmanjšali možnost napak.
Podatke razdelite na več delovnih listov glede na vrednost stolpca s kodo VBA
1. Držite tipko ALT + F11 tipke za odpiranje Microsoft Visual Basic za aplikacije okno.
2. klik 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 Sub3. Nato pritisnite F5 tipko za zagon kode in prikaže se okno s pozivom, ki vas opomni, da izberete vrstico glave in nato kliknete OK. Oglejte si posnetek zaslona:
4. V drugem pozivnem polju izberite podatke stolpca, na podlagi katerih želite razdeliti, nato kliknite OK. Oglejte si posnetek zaslona:
5. Vsi podatki na aktivnem delovnem listu so razdeljeni na več delovnih listov glede na vrednosti stolpcev. Dobljeni delovni listi so poimenovani glede na vrednosti v razdeljenih celicah in so postavljeni na konec delovnega zvezka. Oglejte si posnetek zaslona:
Razdelite podatke na več delovnih listov glede na vrednost stolpca s programom Kutools for Excel
Kutools za Excel prinaša pametno funkcijo – Razdeljeni podatki naravnost v vaše Excelovo okolje. Razdelitev podatkov na več delovnih listov ni več izziv. Naše intuitivno orodje samodejno razdeli vaš nabor podatkov glede na izbrano vrednost stolpca ali število vrstic, s čimer zagotovi, da je vsak podatek točno tam, kjer ga potrebujete. Poslovite se od dolgočasne naloge ročnega organiziranja preglednic in sprejmite hitrejši način upravljanja podatkov brez napak.
Po namestitvi Kutools za Excel, izberite obseg podatkov in kliknite Kutools Plus > Razdeljeni podatki da odprete Podatke razdelite na več delovnih listov pogovorno okno.
- Izberite Poseben stolpec možnost v Razdeljeno na podlagi in na spustnem seznamu izberite vrednost stolpca, na podlagi katere želite razdeliti podatke.
- Če imajo vaši podatki glave in jih želite vstaviti v vsak nov razdeljeni delovni list, preverite Moji podatki imajo glave možnost. (Na podlagi vaših podatkov lahko določite število vrstic glave. Če vaši podatki na primer vsebujejo dve glavi, vnesite 2.)
- Nato lahko pod imenom deljeni delovni list podate imena Ime novega delovnega lista razdelku določite pravilo za imena delovnih listov s spustnega seznama Pravila, lahko dodate Prefiks or pripona tudi za imena listov.
- Kliknite OK . Oglejte si posnetek zaslona:
Zdaj so podatki na delovnem listu razdeljeni na več delovnih listov v novem delovnem zvezku.
Razdelite podatke v več delovnih zvezkov glede na vrednost stolpca s kodo VBA
Občasno je morda bolj koristno razdeliti podatke v ločene delovne zvezke, ki temeljijo na ključnem stolpcu, namesto da bi podatke razdelili na več delovnih listov. Tukaj je vodnik po korakih za uporabo kode VBA za avtomatizacijo postopka razdelitve podatkov v več delovnih zvezkov na podlagi določene vrednosti stolpca.
1. Držite tipko ALT + F11 tipke za odpiranje Microsoft Visual Basic za aplikacije okno.
2. klik Vstavi > Moduliin prilepite naslednjo kodo v Okno modula.
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. Nato pritisnite F5 tipko za zagon kode in prikaže se okno s pozivom, ki vas opomni, da izberete vrstico glave in nato kliknete OK. Oglejte si posnetek zaslona:
4. V drugem pozivnem polju izberite podatke stolpca, na podlagi katerih želite razdeliti, nato kliknite OK. Oglejte si posnetek zaslona:
5. Po razdelitvi so vsi podatki na aktivnem delovnem listu razdeljeni v več delovnih zvezkov na podlagi vrednosti stolpcev. Vsi razdeljeni delovni zvezki so shranjeni v mapo, ki ste jo določili. Oglejte si posnetek zaslona:
Sorodni članki:
- Razdelite podatke na več delovnih listov po številu vrstic
- Učinkovita razdelitev velikega obsega podatkov na več Excelovih delovnih listov na podlagi določenega števila vrstic lahko poenostavi upravljanje podatkov. Če na primer razdelite nabor podatkov vsakih 5 vrstic na več listov, ga lahko naredite bolj obvladljivega in organiziranega. Ta priročnik ponuja dve praktični metodi za hitro in enostavno izvedbo te naloge.
- Združite dve ali več tabel v eno na podlagi ključnih stolpcev
- Recimo, da imate v delovnem zvezku tri tabele, jih želite združiti v eno tabelo na podlagi ustreznih stolpcev s ključi, da dobite rezultat, kot je prikazano spodaj. To je lahko za večino od nas težavna naloga, toda, ne skrbite, v tem članku bom predstavil nekaj metod za reševanje te težave.
- Razdeli besedilne nize z ločilom v več vrstic
- Običajno lahko s funkcijo Besedilo v stolpec razdelite vsebino celice v več stolpcev z določenim ločilom, kot je vejica, pika, podpičje, poševnica itd. Toda včasih boste morda morali vsebino razmejene celice razdeliti v več vrstic in ponovite podatke iz drugih stolpcev, kot je prikazano na spodnjem posnetku zaslona. Ali imate kakšen dober način za reševanje te naloge v Excelu? Ta vadnica bo predstavila nekaj učinkovitih metod za dokončanje tega dela v Excelu.
- Razdeli večvrstično vsebino celic v ločene vrstice/stolpce
- Kaj lahko storite, če imate vsebino večvrstične celice, ki je ločena z Alt + Enter, in zdaj morate vsebino več vrstic razdeliti na ločene vrstice ali stolpce? V tej članku se boste naučili, kako hitro razdeliti vsebino večvrstične celice v ločene vrstice ali stolpce.
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!
