Excel-Tipps: Daten in mehrere Arbeitsblätter / Arbeitsmappen basierend auf Spaltenwert aufteilen

Beim Verwalten großer Datensätze in Excel kann es sehr vorteilhaft sein, Daten basierend auf bestimmten Spaltenwerten in mehrere Arbeitsblätter aufzuteilen. Diese Methode verbessert nicht nur die Organisation der Daten, sondern erhöht auch die Lesbarkeit und erleichtert die Datenanalyse.

Angenommen, Sie haben einen großen Verkaufsdatensatz mit mehreren Einträgen wie Produktnamen und der im ersten Quartal verkauften Menge. Das Ziel ist es, diese Daten basierend auf jedem Produktnamen in separate Arbeitsblätter aufzuteilen, damit die individuelle Verkaufsleistung separat analysiert werden kann.

Daten in mehrere Arbeitsblätter basierend auf Spaltenwert aufteilen

• Durch die Verwendung von VBA-Code
• Durch die Verwendung einer leistungsstarken Funktion - Kutools für Excel

Daten in mehrere Arbeitsmappen basierend auf Spaltenwert mit VBA-Code aufteilen

Daten in mehrere Arbeitsblätter basierend auf Spaltenwert aufteilen

Normalerweise können Sie die Datenliste zuerst sortieren und sie dann nacheinander in andere neue Arbeitsblätter kopieren und einfügen. Aber das erfordert Ihre Geduld, um wiederholt zu kopieren und einzufügen. In diesem Abschnitt stellen wir zwei einfache Methoden vor, um diese Aufgabe in Excel effizient zu bewältigen, Zeit zu sparen und das Fehlerpotenzial zu reduzieren.

Daten in mehrere Arbeitsblätter basierend auf Spaltenwert mit VBA-Code aufteilen

1. Halten Sie die Tasten ALT + F11 gedrückt, um das Microsoft Visual Basic for Applications-Fenster zu öffnen.

2. Klicken Sie auf Einfügen > Modul und fügen Sie den folgenden Code im Modulfenster ein.

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. Drücken Sie dann F5 Taste, um den Code auszuführen, und ein Eingabeaufforderungsfenster erscheint, das Sie daran erinnert, die Kopfzeile auszuwählen, und klicken Sie dann auf OK. Siehe Screenshot:

4. Wählen Sie im zweiten Eingabeaufforderungsfenster die Spaltendaten aus, die Sie aufteilen möchten, und klicken Sie dann auf OK. Siehe Screenshot:

5. Alle Daten im aktiven Arbeitsblatt werden basierend auf den Spaltenwerten in mehrere Arbeitsblätter aufgeteilt. Die resultierenden Arbeitsblätter werden entsprechend den Werten in den geteilten Zellen benannt und am Ende der Arbeitsmappe platziert. Siehe Screenshot:

Daten in mehrere Arbeitsblätter basierend auf Spaltenwert mit Kutools für Excel aufteilen

Kutools für Excel bringt eine intelligente Funktion – Split Data direkt in Ihre Excel-Umgebung. Das Aufteilen von Daten in mehrere Arbeitsblätter ist keine Herausforderung mehr. Unser intuitives Tool teilt Ihren Datensatz automatisch basierend auf dem gewählten Spaltenwert oder Zeilenanzahl auf und stellt sicher, dass jede Information genau dort ist, wo Sie sie benötigen. Verabschieden Sie sich von der mühsamen Aufgabe, Ihre Tabellen manuell zu organisieren, und nutzen Sie einen schnelleren, fehlerfreien Weg zur Verwaltung Ihrer Daten.

Nach der Installation von Kutools für Excel wählen Sie den Datenbereich aus und klicken Sie dann auf Kutools Plus > Split Data, um das Dialogfeld „Split Data into multiple worksheets“ (Daten in mehrere Arbeitsblätter aufteilen) zu öffnen.

1. Wählen Sie die Option „Specific column“ (Bestimmte Spalte) im Abschnitt „ Split based on“ (Aufteilungsgrundlage) aus und wählen Sie den Spaltenwert aus, nach dem Sie die Daten aufteilen möchten, aus der Dropdown-Liste.
2. Wenn Ihre Daten Überschriften enthalten und Sie diese in jedes neue geteilte Arbeitsblatt einfügen möchten, aktivieren Sie bitte die Option „My data has headers“. (Sie können die Anzahl der Titelzeilen basierend auf Ihren Daten angeben. Wenn Ihre Daten beispielsweise zwei Überschriften enthalten, geben Sie bitte 2 ein.)
3. Dann können Sie die Namen der geteilten Arbeitsblätter unter dem Abschnitt „New worksheets name“ (Namen der neuen Arbeitsblätter) festlegen. Wählen Sie die Regel für die Arbeitsblattnamen aus der Dropdown-Liste „Rules“ aus. Sie können auch Präfixe oder Suffixe für die Blattnamen hinzufügen.
4. Klicken Sie auf die OK Schaltfläche. Siehe Screenshot:
   

Jetzt sind die Daten im Arbeitsblatt in mehrere Arbeitsblätter in einer neuen Arbeitsmappe aufgeteilt.

Daten in mehrere Arbeitsmappen basierend auf Spaltenwert mit VBA-Code aufteilen

Gelegentlich kann es vorteilhafter sein, die Daten statt in mehrere Arbeitsblätter in separate Arbeitsmappen basierend auf einer Schlüsselspalte aufzuteilen. Hier ist eine Schritt-für-Schritt-Anleitung, wie Sie mit VBA-Code den Prozess des Aufteilens von Daten in mehrere Arbeitsmappen basierend auf einem bestimmten Spaltenwert automatisieren können.

1. Halten Sie die Tasten ALT + F11 gedrückt, um das Microsoft Visual Basic for Applications-Fenster zu öffnen.

2. Klicken Sie auf Einfügen > Modul und fügen Sie den folgenden Code im Modulfenster ein.

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. Drücken Sie dann F5 Taste, um den Code auszuführen, und ein Eingabeaufforderungsfenster erscheint, das Sie daran erinnert, die Kopfzeile auszuwählen, und klicken Sie dann auf OK. Siehe Screenshot:

4. Wählen Sie im zweiten Eingabeaufforderungsfenster die Spaltendaten aus, die Sie aufteilen möchten, und klicken Sie dann auf OK. Siehe Screenshot:

5. Nach dem Aufteilen werden alle Daten im aktiven Arbeitsblatt basierend auf den Spaltenwerten in mehrere Arbeitsmappen aufgeteilt. Alle geteilten Arbeitsmappen werden im von Ihnen angegebenen Ordner gespeichert. Siehe Screenshot:

Verwandte Artikel: