Sfaturi Excel: Împărțiți datele în mai multe foi de lucru / registre de lucru pe baza valorii coloanei
Când gestionați seturi de date mari în Excel, poate fi foarte benefic să împărțiți datele în mai multe foi de lucru pe baza unor valori specifice coloanei. Această metodă îmbunătățește nu numai organizarea datelor, ci și lizibilitatea și facilitează analiza mai ușoară a datelor.
Să presupunem că aveți o înregistrare mare de vânzări care conține mai multe intrări, cum ar fi numele produsului, cantitatea vândută din primul trimestru. Scopul este de a împărți aceste date în foi de lucru separate pe baza fiecărui nume de produs, astfel încât performanța individuală a vânzărilor să poată fi analizată separat.
Împărțiți datele în mai multe foi de lucru pe baza valorii coloanei
Împărțiți datele în mai multe registre de lucru pe baza valorii coloanei cu cod VBA
Împărțiți datele în mai multe foi de lucru pe baza valorii coloanei
În mod normal, puteți sorta mai întâi lista de date, apoi le puteți copia și lipi una câte una în alte foi de lucru noi. Dar acest lucru va avea nevoie de răbdare pentru a copia și lipi în mod repetat. În această secțiune, vom introduce două metode simple pentru a aborda eficient această sarcină în Excel, economisind timp și reducând potențialul de erori.
Împărțiți datele în mai multe foi de lucru pe baza valorii coloanei cu cod VBA
1. Țineți apăsat butonul ALT + F11 tastele pentru a deschide Microsoft Visual Basic pentru aplicații fereastră.
2. clic Insera > Moduleși lipiți următorul cod în fereastra modulului.
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. Apoi, apăsați F5 tasta pentru a rula codul și apare o casetă promptă pentru a vă aminti că selectați rândul antetului, apoi faceți clic pe OK. Vedeți captura de ecran:
4. În a doua casetă promptă, selectați datele coloanei pe care doriți să le împărțiți, apoi faceți clic OK. Vedeți captura de ecran:
5. Toate datele din foaia de lucru activă sunt împărțite în mai multe foi de lucru pe baza valorilor coloanei. Foile de lucru rezultate sunt denumite în funcție de valorile din celulele împărțite și sunt plasate la sfârșitul registrului de lucru. Vedeți captura de ecran:
Împărțiți datele în mai multe foi de lucru pe baza valorii coloanei cu Kutools pentru Excel
Kutools pentru Excel aduce caracteristici inteligente - Împărțiți datele direct în mediul dumneavoastră Excel. Împărțirea datelor în mai multe foi de lucru nu mai este o provocare. Instrumentul nostru intuitiv împarte automat setul de date în funcție de valoarea de coloană aleasă sau de numărul de rânduri, asigurându-se că fiecare informație este exact acolo unde aveți nevoie. Spuneți la revedere sarcinii obositoare de a vă organiza manual foile de calcul și adoptați o modalitate mai rapidă și fără erori de a vă gestiona datele.
După instalare Kutools pentru Excel, selectați intervalul de date, apoi faceți clic Kutools Plus > Împărțiți datele pentru a deschide Împărțiți datele în mai multe foi de lucru căsuță de dialog.
- Selectați Coloană specifică opțiune în Split pe baza secțiunea și alegeți valoarea coloanei pe care doriți să împărțiți datele în funcție de lista derulantă.
- Dacă datele dvs. au antete și doriți să le inserați în fiecare nouă foaie de lucru împărțită, vă rugăm să verificați Datele mele au anteturi opțiune. (Puteți specifica numărul de rânduri de antet pe baza datelor dvs. De exemplu, dacă datele dvs. conțin două anteturi, introduceți 2.)
- Apoi puteți specifica numele foilor de lucru împărțite, sub Numele noilor foi de lucru secțiunea, specificați regula numelor foilor de lucru din lista derulantă Reguli, puteți adăuga Prefix or Sufix și pentru numele foilor.
- Apasă pe OK buton. Vedeți captura de ecran:
Acum, datele din foaia de lucru sunt împărțite în mai multe foi de lucru într-un nou registru de lucru.
Împărțiți datele în mai multe registre de lucru pe baza valorii coloanei cu cod VBA
Ocazional, mai degrabă decât împărțirea datelor în mai multe foi de lucru, poate fi mai benefic să împărțiți datele în registre de lucru separate, pe baza unei coloane cheie. Iată un ghid pas cu pas despre cum să utilizați codul VBA pentru a automatiza procesul de împărțire a datelor în mai multe registre de lucru pe baza unei anumite valori de coloană.
1. Țineți apăsat butonul ALT + F11 tastele pentru a deschide Microsoft Visual Basic pentru aplicații fereastră.
2. clic Insera > Moduleși lipiți următorul cod în Fereastra modulului.
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. Apoi, apăsați F5 tasta pentru a rula codul și apare o casetă promptă pentru a vă aminti că selectați rândul antetului, apoi faceți clic pe OK. Vedeți captura de ecran:
4. În a doua casetă promptă, selectați datele coloanei pe care doriți să le împărțiți, apoi faceți clic OK. Vedeți captura de ecran:
5. După împărțire, toate datele din foaia de lucru activă sunt împărțite în mai multe registre de lucru pe baza valorilor coloanei. Toate registrele de lucru împărțite sunt salvate în folderul pe care l-ați specificat. Vedeți captura de ecran:
Articole pe aceeaşi temă:
- Împărțiți datele în mai multe foi de lucru după numărul de rânduri
- Împărțirea eficientă a unui interval mare de date în mai multe foi de lucru Excel bazate pe un anumit număr de rânduri poate simplifica gestionarea datelor. De exemplu, împărțirea unui set de date la fiecare 5 rânduri în mai multe foi îl poate face mai ușor de gestionat și organizat. Acest ghid oferă două metode practice pentru a realiza această sarcină rapid și ușor.
- Îmbinați două sau mai multe tabele într-unul singur pe baza coloanelor cheie
- Presupunând că aveți trei tabele într-un registru de lucru, acum, doriți să îmbinați aceste tabele într-un singur tabel pe baza coloanelor cheie corespunzătoare pentru a obține rezultatul așa cum este prezentat mai jos. Aceasta poate fi o sarcină supărătoare pentru majoritatea dintre noi, dar, vă rugăm să nu vă faceți griji, acest articol, voi introduce câteva metode pentru rezolvarea acestei probleme.
- Împărțiți șirurile de text după delimitator în mai multe rânduri
- În mod normal, puteți folosi funcția Text în coloană pentru a împărți conținutul celulei în mai multe coloane printr-un anumit delimitator, cum ar fi virgulă, punct, punct și virgulă, bară oblică etc. Dar, uneori, poate fi necesar să împărțiți conținutul celulei delimitate în mai multe rânduri. și repetați datele din alte coloane așa cum se arată mai jos. Aveți modalități bune de a face față acestei sarcini în Excel? Acest tutorial va introduce câteva metode eficiente pentru a finaliza această lucrare în Excel.
- Împărțiți conținutul celulelor cu mai multe linii în rânduri/coloane separate
- Presupunând că aveți conținut de celule cu mai multe linii, care este separat de Alt + Enter, iar acum trebuie să împărțiți conținutul cu mai multe linii în rânduri sau coloane separate, ce puteți face? În acest articol, veți învăța cum să împărțiți rapid conținutul celulelor cu mai multe linii în rânduri sau coloane separate.
Cele mai bune instrumente de productivitate de birou
Îmbunătățiți-vă abilitățile Excel cu Kutools pentru Excel și experimentați eficiența ca niciodată. Kutools pentru Excel oferă peste 300 de funcții avansate pentru a crește productivitatea și a economisi timp. Faceți clic aici pentru a obține funcția de care aveți cea mai mare nevoie...
Fila Office aduce interfața cu file în Office și vă face munca mult mai ușoară
- Activați editarea și citirea cu file în Word, Excel, PowerPoint, Publisher, Access, Visio și Project.
- Deschideți și creați mai multe documente în filele noi ale aceleiași ferestre, mai degrabă decât în ferestrele noi.
- Vă crește productivitatea cu 50% și reduce sute de clicuri de mouse pentru dvs. în fiecare zi!
