Beitrag aus SmartTools Excel Weekly
Doppelte Listeneinträge mit PowerQuery kombinieren
Excel 365 2024 2021 2019 2016
Vor kurzem haben wir gezeigt, wie sich Listen mit mehrfach vorkommenden Einträgen unter Summierung der jeweiligen Eintragswerte zusammenfassen lassen. Dabei haben wir die Konsolidieren-Funktion von Excel verwendet. Bei dieser Technik ist es allerdings nicht möglich, mehr als eine Beschriftungsspalte in den Ausgabebereich zu übernehmen. Analog zu unserem damaligen Beispiel mit Spalten für "Datum", "Projekt" und "Stunden" kommen vielleicht noch Spalten für "Projektnr." und "Ort" hinzu. Diese beiden Spalten sollten neben den Projektnamen im Ausgabebereich erscheinen. Das funktioniert allerdings nicht, denn mit der Konsolidieren-Funktion lässt sich nur eine Beschriftungsspalte in den Ausgabebereich übernehmen. Wenn Sie mehr Spalten übernehmen wollen, müssen Sie auf Power Query-Funktionen zurückgreifen, die seit Excel 2016 direkt in Excel integriert sind. Wir zeigen in diesem Tipp wie das im Detail funktioniert.
Wichtig ist zunächst einmal, dass alle Spalteninhalte bei der Duplikatsuche einbezogen werden. Wenn Ihre Originalliste zum Beispiel mehrere Einträge mit der Projektnr. "ABC-123", dem Projektnamen "Werbung Drogeriemarkt" und der Ortsangabe "Bochum" enthält, ist das kein Problem. Die Stunden aus allen Listenzeilen mit diesen Projektinformationen werden wie erwartet zusammengefasst.
Sollte es auch Einträge mit gleicher Projektnummer und gleichem Projektnamen, aber abweichender Ortsangabe oder Einträge mit gleichlautenden Projektnamen und Ortsangaben, aber unterschiedlicher Projektnummer geben, so handelt es sich jeweils um unterschiedliche Einträge, die in der Ergebnisliste separat aufgeführt werden.
Und noch etwas müssen Sie beim Einsatz der Power Query-Funktion beachten: Wenn es sich bei der Originalliste nicht schon um eine Excel-Tabelle handelt, wird der Bereich automatisch in eine Tabelle umgewandelt. Falls Sie das nicht wollen, müssen Sie Ihrer Projektliste mittels Formeln-Definierte Namen-Namen definieren einen Namen zuweisen.
Sofern Sie mit diesen Vorbedingungen zurechtkommen, gehen Sie folgendermaßen vor, um eine Zusammenfassung Ihrer Originalliste zu erstellen:
- Setzen Sie den Cursor in eine Zelle der Originalliste, der Liste, für die Sie die Gesamtsummen gleichlautender Einträge bilden wollen.
- Wählen Sie in Excel 365, 2024, 2021 und 2019 Daten-Daten abrufen und transformieren-Vom Blatt an oder in Excel 2016 Daten-Abrufen und transformieren-Aus Tabelle.
- Falls Excel jetzt ein Fenster mit dem Titel Tabelle erstellen anzeigt, kontrollieren Sie, ob der angegebene Zellbereich Ihre gesamte Liste einschließt und klicken Sie dann auf Ok.
- Nun gelangen Sie in den Power Query-Editor, der eine Kopie Ihrer Originalliste anzeigt.
- Wählen Sie Start-Transformieren-Gruppieren nach an.
- Im Dialogfenster Gruppieren nach aktivieren Sie als erstes die Option Weitere.
- Direkt darunter wählen Sie die Spalten aus, die als Beschriftungsspalten in die Zusammenfassung Ihrer Originalliste übernommen werden sollen. Klicken Sie jeweils auf Gruppierung hinzufügen, um eine weitere Spalte auswählen zu können. – Im Beispiel könnten Sie so die Spalten "Projektnr.", "Projekt" und "Ort" aufnehmen.
- Im Feld Neuer Spaltenname geben Sie eine Überschrift für die kumulierten Summen des Ausgabebereichs ein – hier etwa "Gesamtstunden".
- Im Feld Vorgang wählen Sie die Option "Summe" aus.
- Und im Feld Spalte wählen Sie den Spaltennamen aus, dessen Werte im Ausgabebereich für jeden Listeneintrag summiert werden sollen – hier also "Stunden".
- Danach klicken Sie auf Ok.
- Zurück im Power Query-Editor klicken Sie auf dem Menübandregister Start in der Gruppe Schließen auf den Dropdownpfeil der Schaltfläche Schließen & laden und wählen dann Schließen & laden in an.
- Im danach angezeigten Dialogfenster aktivieren Sie die Option Bestehendes Arbeitsblatt. Dann klicken Sie einmal in das darunterstehende Auswahlfeld und markieren die gewünschte obere linke Eckzelle des Ausgabebereichs per Mausklick im aktuellen Excel-Tabellenblatt. Falls das Dialogfenster im Weg ist, ziehen Sie es vorher mit der Maus an eine andere Position.
- Dann bestätigen Sie Ihre Auswahl je nach Excel-Version mit Ok oder Laden.
So erhalten Sie ganz automatisch eine zusammenfassende Liste all Ihrer Projekte mit den Gesamtstunden. Und neben den Projektnamen werden auch die Projektnummern und Ortsangaben angezeigt.