Excel-tippek: Oszd fel az adatokat több munkalapra/munkafüzetre az oszlopérték alapján
Ha nagy adatkészleteket kezel Excelben, nagyon előnyös lehet az adatokat több munkalapra felosztani meghatározott oszlopértékek alapján. Ez a módszer nemcsak az adatok szervezését javítja, hanem javítja az olvashatóságot és megkönnyíti az adatok elemzését is.
Tegyük fel, hogy van egy nagy értékesítési rekordja, amely több bejegyzést tartalmaz, például a termék nevét, az első negyedév eladott mennyiségét. A cél az, hogy ezeket az adatokat külön munkalapokra bontsák az egyes termékek neve alapján, így az egyes értékesítési teljesítmények külön-külön elemezhetők.
Oszd fel az adatokat több munkalapra az oszlopérték alapján
Oszd fel az adatokat több munkafüzetre az oszlopérték alapján VBA-kóddal
Oszd fel az adatokat több munkalapra az oszlopérték alapján
Általában először rendezheti az adatlistát, majd egyenként másolja és illessze be őket más új munkalapokra. Ehhez azonban türelmedre lesz szükséged ahhoz, hogy ismételten másolhasd és illessz be. Ebben a részben két egyszerű módszert mutatunk be ennek a feladatnak az Excelben történő hatékony megoldására, időt takaríthat meg, és csökkenti a hibalehetőséget.
Oszd fel az adatokat több munkalapra az oszlopérték alapján VBA-kóddal
1. Tartsa lenyomva a ALT + F11 billentyűk megnyitásához Microsoft Visual Basic for Applications ablak.
2. kettyenés betétlap > Modulok, és illessze be a következő kódot a Modul ablakba.
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. Ezután nyomja meg a gombot F5 gombot a kód futtatásához, és megjelenik egy prompt mező, amely emlékezteti Önt a fejlécsor kiválasztására, majd kattintson a OK. Lásd a képernyőképet:
4. A második prompt mezőben válassza ki a felosztani kívánt oszlopadatokat, majd kattintson a gombra OK. Lásd a képernyőképet:
5. Az aktív munkalapon lévő összes adat több munkalapra van felosztva az oszlopértékek alapján. Az eredményül kapott munkalapokat a felosztott cellákban lévő értékek szerint nevezik el, és a munkafüzet végén helyezik el. Lásd a képernyőképet:
Oszd fel az adatokat több munkalapra az oszlopérték alapján a Kutools for Excel segítségével
Kutools az Excel számára intelligens funkciót hoz - Osztott adatok közvetlenül az Excel környezetbe. Az adatok több munkalapra bontása már nem jelent kihívást. Intuitív eszközünk automatikusan felosztja adatkészletét a kiválasztott oszlopérték vagy sorszám alapján, így biztosítva, hogy minden információ pontosan ott legyen, ahol szüksége van rá. Mondjon búcsút a táblázatok kézi rendszerezésének fárasztó feladatának, és válasszon egy gyorsabb, hibamentes módot az adatok kezeléséhez.
Telepítése után Kutools az Excel számára, válassza ki az adattartományt, majd kattintson a gombra Kutools Plus > Osztott adatok megnyitni Az adatok felosztása több munkalapra párbeszédablak.
- választ Konkrét oszlop lehetőség a Felosztása alapján szakaszt, és a legördülő listából válassza ki azt az oszlopértéket, amely alapján fel szeretné osztani az adatokat.
- Ha az adatoknak vannak fejlécek, és minden egyes új felosztott munkalapba szeretné beilleszteni őket, ellenőrizze Adataim fejléceket tartalmaznak választási lehetőség. (Az adatok alapján megadhatja a fejlécsorok számát. Ha például az adatok két fejlécet tartalmaznak, kérjük, írjon be 2-t.)
- Ezután megadhatja az osztott munkalap nevét a Új munkalapok neve szakaszban adja meg a munkalapnevek szabályát a Szabályok legördülő listából, hozzáadhatja a prefix or képző a lapnevekhez is.
- Kattintson az OK gomb. Lásd a képernyőképet:
Most a munkalapon lévő adatok több munkalapra vannak felosztva egy új munkafüzetben.
Oszd fel az adatokat több munkafüzetre az oszlopérték alapján VBA-kóddal
Alkalmanként az adatok több munkalapra történő felosztása helyett előnyösebb lehet az adatokat külön munkafüzetekre osztani egy kulcsoszlop alapján. Íme egy lépésről lépésre bemutatott útmutató arról, hogyan használhatja a VBA-kódot az adatok több munkafüzetre való felosztásának automatizálására egy adott oszlopérték alapján.
1. Tartsa lenyomva a ALT + F11 billentyűk megnyitásához Microsoft Visual Basic for Applications ablak.
2. kettyenés betétlap > Modulok, és illessze be a következő kódot a Modul ablak.
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. Ezután nyomja meg a gombot F5 gombot a kód futtatásához, és megjelenik egy prompt mező, amely emlékezteti Önt a fejlécsor kiválasztására, majd kattintson a OK. Lásd a képernyőképet:
4. A második prompt mezőben válassza ki a felosztani kívánt oszlopadatokat, majd kattintson a gombra OK. Lásd a képernyőképet:
5. A felosztás után az aktív munkalapon lévő összes adat több munkafüzetre van osztva az oszlopértékek alapján. Az összes felosztott munkafüzet az Ön által megadott mappába kerül. Lásd a képernyőképet:
Kapcsolódó cikkek:
- Az adatok felosztása több munkalapra sorszám szerint
- Egy nagy adattartomány hatékony felosztása több Excel munkalapra egy adott sorszám alapján egyszerűsítheti az adatkezelést. Például, ha egy adatkészletet 5 soronként több lapra oszt fel, kezelhetőbbé és szervezettebbé teheti azt. Ez az útmutató két gyakorlati módszert kínál ennek a feladatnak a gyors és egyszerű elvégzésére.
- Egyesítsen két vagy több táblázatot egybe a kulcsoszlopok alapján
- Ha feltételezzük, hogy három táblázata van egy munkafüzetben, akkor ezeket a táblázatokat egy táblába kívánja egyesíteni a megfelelő kulcsoszlopok alapján, hogy az eredményt az alábbi képernyőképen láthatja. Ez a legtöbbünk számára problémás feladat lehet, de, ne aggódjon, ez a cikk bemutat néhány módszert a probléma megoldására.
- Szövegkarakterláncok felosztása határolóval több sorra
- Általában a Szöveg oszlopba funkciót használhatja a cellák tartalmának több oszlopra történő felosztására egy adott határolóval, például vesszővel, ponttal, pontosvesszővel, perjellel stb. De előfordulhat, hogy a határolt cellatartalmat több sorra kell felosztania. és ismételje meg a többi oszlopból származó adatokat az alábbi képernyőképen látható módon. Van valami jó módja ennek a feladatnak az Excelben való kezelésére? Ez az oktatóanyag bemutat néhány hatékony módszert ennek a feladatnak az Excelben való elvégzéséhez.
- A többsoros cellatartalom felosztása elválasztott sorokra/oszlopokra
- Tegyük fel, hogy többsoros cellatartalma van, amelyet Alt + Enter választ el, és most szét kell osztania a többsoros tartalmat elválasztott sorokra vagy oszlopokra, mit tehet? Ebből a cikkből megtudhatja, hogyan lehet gyorsan felosztani a többsoros cellatartalmat külön sorokra vagy oszlopokra.
A legjobb irodai hatékonyságnövelő eszközök
Töltsd fel Excel-készségeidet a Kutools for Excel segítségével, és tapasztald meg a még soha nem látott hatékonyságot. A Kutools for Excel több mint 300 speciális funkciót kínál a termelékenység fokozásához és az időmegtakarításhoz. Kattintson ide, hogy megszerezze a leginkább szükséges funkciót...
Az Office lap füles felületet hoz az Office-ba, és sokkal könnyebbé teszi a munkáját
- Füles szerkesztés és olvasás engedélyezése Wordben, Excelben és PowerPointban, Publisher, Access, Visio és Project.
- Több dokumentum megnyitása és létrehozása ugyanazon ablak új lapjain, mint új ablakokban.
- 50% -kal növeli a termelékenységet, és naponta több száz kattintással csökkenti az egér kattintását!