Excel-Tipps: Daten in mehrere Arbeitsblätter aufteilen / Arbeitsmappen basierend auf Spaltenwerten
Beim Verwalten großer Datensätze in Excel kann es sehr vorteilhaft sein, Daten in mehrere Arbeitsblätter aufteilen basierend auf Spalte angeben-Werten durchzuführen. Diese Methode verbessert nicht nur die Organisation der Daten, sondern erhöht auch deren Lesbarkeit und erleichtert die Datenanalyse.
Angenommen, Sie verfügen über einen umfangreichen Verkaufsdatensatz mit mehreren Einträgen – darunter Produktname und verkaufter Menge im ersten Quartal. Ziel ist es, diese Daten nach Produktnamen aufzuteilen und in separate Arbeitsblätter zu organisieren, um die individuelle Verkaufsleistung jedes Produkts gezielt analysieren zu können.
Daten in mehrere Arbeitsblätter aufteilen basierend auf Spaltenwerten
Daten aufteilen in mehrere Arbeitsmappen basierend auf Spaltenwerten mit VBA-Code
Daten in mehrere Arbeitsblätter aufteilen basierend auf Spaltenwerten
Normalerweise können Sie die Datenliste zunächst sortieren und anschließend die einzelnen Abschnitte nacheinander in andere Neues Arbeitsblatt kopieren und einfügen. Dies erfordert jedoch viel Geduld beim wiederholten Kopieren und Einfügen. In diesem Abschnitt stellen wir zwei einfache Methoden vor, um diese Aufgabe effizient in Excel zu bewältigen, Zeit zu sparen und Fehler zu vermeiden.
Daten in mehrere Arbeitsblätter aufteilen basierend auf Spaltenwerten mit VBA-Code
1. Halten Sie die Tasten ALT + F11 gedrückt, um das Fenster Microsoft Visual Basic for Applications zu öffnen.
2. Klicken Sie auf Einfügen > Modul, und fügen Sie den folgenden Code in das 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 Sub3. Drücken Sie dann die Taste F5, um den Code auszuführen. Es erscheint ein Eingabefeld, das Sie zur Auswahl der Überschriftenzeile auffordert. Klicken Sie anschließend auf OK. Siehe Screenshot:
4. Wählen Sie im zweiten Eingabefeld die Spaltendaten aus, nach denen Sie die Aufteilung vornehmen möchten, und klicken Sie dann auf OK. Siehe Screenshot:
5. Alle Daten im aktiven Arbeitsblatt werden nun basierend auf den Spaltenwerten in mehrere Arbeitsblätter aufgeteilt. Die neuen Arbeitsblätter erhalten Namen entsprechend den jeweiligen Zellwerten und werden am Ende der Arbeitsmappe eingefügt. Siehe Screenshot:
Daten in mehrere Arbeitsblätter aufteilen basierend auf Spaltenwerten mit Kutools für Excel
Kutools für Excel bringt eine intelligente Funktion – Daten aufteilen – direkt in Ihre Excel-Umgebung. Das Aufteilen von Daten in mehrere Arbeitsblätter ist kein Problem mehr! Unser intuitives Werkzeug teilt Ihren Datensatz automatisch basierend auf dem gewählten Spaltenwert oder der Zeilenanzahl auf und stellt sicher, dass jede Information genau dort landet, wo sie benötigt wird. Verabschieden Sie sich von der mühsamen manuellen Organisation Ihrer Tabellen und nutzen Sie einen schnelleren, fehlerfreien Weg zur Datenverwaltung.
Nach der Installation von Kutools für Excel wählen Sie den gewünschten Datenbereich aus und klicken Sie anschließend auf KUTOOLS PLUS > Daten aufteilen, um das Dialogfeld Daten in mehrere Arbeitsblätter aufteilen zu öffnen.
- Wählen Sie im Abschnitt Aufteilungsgrundlage die Option Spalte angeben und wählen Sie aus der Dropdown-Liste den Spaltenwert aus, nach dem die Daten aufgeteilt werden sollen.
- Falls Ihre Daten Überschriften enthalten und Sie diese in jedes neue Arbeitsblatt einfügen möchten, aktivieren Sie bitte die Option Titel einschließen. (Sie können die Anzahl der Titelzeilen entsprechend Ihren Daten festlegen. Enthalten Ihre Daten beispielsweise zwei Überschriftenzeilen, geben Sie „2“ ein.)
- Anschließend legen Sie die Aufteilungsregel im Abschnitt Name der erstellten Arbeitsblätter fest: Wählen Sie im Dropdown-Menü „Regeln“ die gewünschte Regel für die Arbeitsblattnamen aus. Außerdem können Sie ein Präfix oder ein Suffix für die Arbeitsblattnamen hinzufügen.
- Klicken Sie auf die Schaltfläche OK. Siehe Screenshot:
Nun werden die Daten aus dem Arbeitsblatt in mehrere Arbeitsblätter eines neuen Arbeitsmappen aufgeteilt.
Daten aufteilen in mehrere Arbeitsmappen basierend auf Spaltenwerten mit VBA-Code
Gelegentlich ist es vorteilhafter, Daten nicht auf mehrere Arbeitsblätter, sondern in separate Arbeitsmappen aufzuteilen – und zwar basierend auf einer Schlüsselspalte. Im Folgenden finden Sie eine Schritt-für-Schritt-Anleitung, wie Sie mithilfe von VBA-Code diesen Prozess automatisieren können, um Daten je nach Wert in einer bestimmten Spalte auf mehrere Arbeitsmappen zu verteilen.
1. Halten Sie die Taste ALT + F11 gedrückt, um das Fenster Microsoft Visual Basic for Applications zu öffnen.
2. Klicken Sie auf Einfügen > Modul und fügen Sie den folgenden Code in das 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 die Taste F5, um den Code auszuführen. Es erscheint ein Eingabefeld, das Sie auffordert, die Überschriftenzeile auszuwählen. Klicken Sie anschließend auf OK. Siehe Screenshot:
4. Wählen Sie im zweiten Eingabefeld die Spaltendaten aus, nach denen Sie die Aufteilung vornehmen möchten, und klicken Sie dann auf OK. Siehe Screenshot:
5. Nach der Aufteilung werden alle Daten im aktiven Arbeitsblatt basierend auf den Spaltenwerten in mehrere Arbeitsmappen aufgeteilt und in dem von Ihnen angegebenen Ordner gespeichert. Siehe Screenshot:
Verwandte Artikel:
- Daten in mehrere Arbeitsblätter aufteilen nach Zeilenanzahl
- Ein großer Datenbereich lässt sich effizient in mehrere Excel-Arbeitsblätter aufteilen – basierend auf einer festgelegten Zeilenanzahl – und vereinfacht so die Datenverwaltung erheblich. So können Sie beispielsweise alle 5 Zeilen einen neuen Datensatz in ein separates Arbeitsblatt übertragen, um Ihre Daten übersichtlicher und besser strukturiert zu gestalten. Dieser Leitfaden stellt zwei praktische Methoden vor, mit denen Sie diese Aufgabe schnell und einfach erledigen.
- Zusammenführen von zwei oder mehr Tabellen zu einer Tabelle basierend auf Schlüsselspalte
- Angenommen, Sie haben drei Tabellen in einer Arbeitsmappe und möchten diese basierend auf der entsprechenden Schlüsselspalte zu einer einzigen Tabelle zusammenführen, um das im Screenshot unten gezeigte Ergebnis zu erhalten. Für die meisten von uns mag das eine mühsame Aufgabe sein – doch keine Sorge: In diesem Artikel stelle ich einige Methoden vor, mit denen Sie dieses Problem ganz einfach lösen können.
- Textzeichenfolgen anhand eines Trennzeichens in mehrere Zeilen aufteilen
- Normalerweise können Sie die Funktion „Text in Spalten“ verwenden, um Zellinhalte anhand eines bestimmten Trennzeichens – wie Komma, Punkt, Semikolon, Schrägstrich usw. – in mehrere Spalten aufzuteilen. Manchmal müssen Sie jedoch den getrennten Zellinhalt stattdessen in mehrere Zeilen aufteilen und dabei die Daten aus anderen Spalten entsprechend wiederholen, wie im folgenden Screenshot dargestellt. Kennen Sie gute Möglichkeiten, um diese Aufgabe in Excel zu bewältigen? In diesem Tutorial werden einige effektive Methoden vorgestellt, um diese Aufgabe in Excel erfolgreich abzuschließen.
- Mehrzeilige Zellinhalte in separate Zeilen/Spalten aufteilen
- Angenommen, Sie haben einen mehrzeiligen Zellinhalt, der durch Alt + Enter getrennt ist, und möchten ihn in separate Zeilen oder Spalten aufteilen – wie geht das? In diesem Artikel erfahren Sie, wie Sie mehrzeilige Zellinhalte schnell und einfach in separate Zeilen oder Spalten aufteilen können.
Beste Office-Produktivitätstools
Verbessern Sie Ihre Excel-Kenntnisse mit Kutools für Excel und erleben Sie Effizienz wie nie zuvor.Kutools für Excel bietet über 300 erweiterte Funktionen zur Steigerung der Produktivität und Zeit sparen.Klicken Sie hier, um die Funktion zu erhalten, die Sie am dringendsten benötigen...
Office Tab bringt eine tabbasierte Oberfläche in Office und macht Ihre Arbeit viel einfacher
- Aktivieren Sie tabbasiertes Bearbeiten und Lesen in Word, Excel, PowerPoint, Publisher, Access, Visio und Project.
- Öffnen und erstellen Sie mehrere Dokumente in neuen Registerkarten desselben Fensters – statt jedes in einem separaten Fenster zu öffnen.
- Steigert Ihre Produktivität um 50 % und erspart Ihnen täglich Hunderte von Mausklicks!
Alle Kutools-Add-Ins – ein Installationsprogramm
Kutools for Office-Paket bündelt Add-Ins für Excel, Word, Outlook und PowerPoint sowie Office Tab Pro – ideal für Teams, die mit mehreren Office-Anwendungen arbeiten.
- Alles-in-einem-Paket— Add-Ins für Excel, Word, Outlook & PowerPoint sowie Office Tab Pro
- Ein Installationsprogramm, eine Lizenz— innerhalb weniger Minuten eingerichtet (MSI-fähig)
- Funktioniert besser zusammen— optimierte Produktivität über alle Office-Anwendungen hinweg
- 30-tägige Vollversion zum Testen— keine Registrierung, keine Kreditkarte erforderlich
- Bestes Preis-Leistungs-Verhältnis— sparen Sie im Vergleich zum Kauf einzelner Add-Ins