Wie verkette ich Zeilen in einer Zelle basierend auf einer Gruppe in Excel?
Hier ist ein Bereich von zwei Spalten, eine ist die Klassenliste und die andere ist die Liste der Schülernamen. Wie Sie sehen, sind einige Schüler in derselben Klasse, andere nicht. Jetzt möchte ich die Schüler derselben Klasse in einer Zelle zusammenfassen (siehe Abbildung unten). Wie kann ich in Excel schnell damit umgehen?
Gruppieren und verketten Sie mit Formeln und Filterfunktionen
Gruppieren und verketten Sie mit VBA-Code
Gruppieren und verketten Sie mit Advanced Combine Rows
Gruppieren und verketten Sie mit Formeln und Filterfunktionen
In Excel können Sie Formeln auf verkettete Zeilen basierend auf einer Spalte anwenden und dann die Filterfunktion verwenden, um nur das Ergebnis anzuzeigen.
Note: Sie müssen Ihre Daten nach der Klasse sortieren, bevor Sie die folgenden Schritte ausführen können.
1. Geben Sie diese Formel in eine leere Zelle neben dem Datenbereich ein, z. B. C13 =IF(A13=A12,C12&", "&B13,B13), Drücken Sie Enter Geben Sie die Formel in die Zellen ein und füllen Sie sie mit einem Ziehpunkt.
In der Formel sind A13 die ersten Daten in der Spalte "Klasse", B13 die ersten Daten in der Spalte "Name", "," das Trennzeichen zur Begrenzung des verketteten Inhalts.
2. Geben Sie dann in die nächste Spalte, D13, diese Formel ein =IF(A13<>A14,"Last","") Ziehen Sie den Füllpunkt nach unten, um die Formel auf die benötigten Zellen anzuwenden.
3. Wählen Sie nun den gesamten Datenbereich einschließlich der Formeln aus und klicken Sie auf Datum > Filter hinzufügen Filter icons zu den Daten.
4. Klicken Sie auf die Filter icon Überprüfen Sie im letzten Formelkopf Last Aktivieren Sie das Kontrollkästchen nur in der Dropdown-Liste und klicken Sie auf OK.
Jetzt wird das Ergebnis wie folgt angezeigt: Sie können die letzte Hilfsspalte entfernen, wenn Sie sie nie benötigen.
Gruppieren und verketten Sie mit VBA-Code
Hier ist ein VBA-Code, der auch diesen Job verarbeiten kann.
1. Drücken Sie Alt + F11 Tasten zum Aktivieren der Microsoft Visual Basic for Applications Fenster.
2. Klicken Sie dann im Fenster auf Tools > References ermöglichen References Dialog und überprüfen Microsoft Scripting Runtime. Siehe Screenshot:
3 Klicken OKUnd klicken Sie auf Insert > Module im VBA-Fenster und kopieren Sie den folgenden VBA-Code und fügen Sie ihn in das ein Module Skript. Siehe Screenshot:
VBA: Verketten Sie Zeilen basierend auf der Gruppe zu einer Zelle
Sub ConcatenateCellsIfSameValues()
'UpdatebyExtendoffice20180201
Dim I As Long
Dim J As Long
Dim xRg As Range
Dim xRgKey As Range
Dim xRgVal As Range
Dim xStr As String
Dim xDic As New Dictionary
On Error Resume Next
Set xRg = Application.InputBox("Select data range", "KuTools for Excel", Selection.Address, , , , , 8)
If xRg Is Nothing Then Exit Sub
Set xRgKey = Application.InputBox("Select key column", "KuTools for Excel", xRg.Columns(1).Address, , , , , 8)
If xRgKey Is Nothing Then
MsgBox "Key column cannot be empty", vbInformation, "KuTools for Excel"
End If
Set xRgVal = xRg(1).Offset(, 1).Resize(xRg.Rows.Count, xRg.Columns.Count - 1)
For I = 1 To xRgKey.Count
If I > xRgKey.Count Then Exit For
xStr = ""
For J = 1 To xRgVal.Columns.Count
xStr = xStr & " " & xRgVal(I, J)
Next
If xDic.Exists(xRgKey(I).Text) Then
xDic(xRgKey(I).Text) = xDic(xRgKey(I).Text) & xStr
xRgKey(I).EntireRow.Delete
I = I - 1
Else
xDic.Add xRgKey(I).Text, xStr
End If
Next
For I = 1 To xRgVal.Count
xRgVal(I).Value = xDic(xRgKey(I).Text)
Next
End Sub
4. Drücken Sie F5 Drücken Sie die Taste und wählen Sie im Popup-Dialogfeld den Datenbereich aus, den Sie verwenden möchten.
5 Klicken OK , um die Schlüsselspalte auszuwählen, nach der Sie gruppieren möchten.
6 Klicken OK, nun wird das Ergebnis wie folgt angezeigt:
Gruppieren und verketten Sie mit Advanced Combine Rows
Hier ist ein Dienstprogramm in Kutools for Excel, Advanced Combine Reihen, mit dem Zeilen kombiniert oder Berechnungen basierend auf einer Schlüsselspalte in Excel durchgeführt werden können.
| Kutools for Excel, mit mehr als 300 praktische Funktionen erleichtern Ihre Arbeit. |
Nach der Installation Kutools für Excel, bitte gehen Sie wie folgt vor:（Kutools für Excel jetzt kostenlos herunterladen!)
1. Wählen Sie den von Ihnen verwendeten Datenbereich aus und klicken Sie auf Kutools > Zusammenführen & Teilen > Erweiterte Zeilen kombinieren.
2. In dem Advanced Combine Rows Wählen Sie im Fenster die Spalte aus, auf deren Grundlage Sie Zeilen kombinieren möchten, und klicken Sie auf Primary Key um es als Schlüsselspalte festzulegen.
3. Wählen Sie die Spalte aus, die Sie kombinieren möchten, und klicken Sie auf Combine, und wählen Sie ein Trennzeichen aus, das Sie zum Trennen der kombinierten Inhalte verwenden.
 |
 |
 |
4 Klicken Ok. Das Ergebnis wird wie folgt angezeigt:
Note: Bevor Sie das Dienstprogramm anwenden, sollten Sie eine Kopie der Originaldaten haben.
Demo
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, um die Produktivität zu steigern und Zeit zu sparen. Klicken Sie hier, um die Funktion zu erhalten, die Sie am meisten benötigen ...
Office Tab Bringt die Oberfläche mit Registerkarten in Office und erleichtert Ihnen die Arbeit erheblich
- Aktivieren Sie das Bearbeiten und Lesen von Registerkarten in Word, Excel und PowerPoint, Publisher, Access, Visio und Project.
- Öffnen und erstellen Sie mehrere Dokumente in neuen Registerkarten desselben Fensters und nicht in neuen Fenstern.
- Steigert Ihre Produktivität um 50 % und reduziert jeden Tag Hunderte von Mausklicks für Sie!
