Konsolidieren - Zusammenfassen von Listen

Unter dem Menüpunkt Daten > Konsolidieren.... wird uns die Möglichkeit angeboten, Listen zusammenzufassen. Entweder verschiedene, identisch aufgebaute Listen oder eine Liste, die Duplikate enthält, die Sie kombinieren möchten.

Es wird uns damit praktisch eine Alternative zu Pivottabellen und Teilergebnis Menü geboten, Sie müssen anhand Ihres Einzelfalls entscheiden, welches für Ihren Zweck das passende Instrument ist.


Zunächst Grundlagen, die Sie kennen und beachten sollten:

• die Ausgangslisten müssen identisch aufgebaut sein, dürfen keine Leerzeilen enthalten und müssen identische Überschriften haben
• legen Sie die Zusammenfassung in ein separates Tabellenblatt
• die Zusammenfassung muss die gleichen Überschriften wie die Ausgangstabellen haben (am besten kopieren)
• die Zusammenfassung ist statisch, die Zellinhalte sind Werte. Änderungen in den Ausgangstabellen wirken sich nicht aus. Bei Änderungen ist die Zusammenfassung neu zu erstellen
• von der Nutzung des Ankreuzfelds Verknüpfungen mit Quelldaten rate ich ab, da nicht berücksichtigt wird, wenn Ausgangslisten sich verlängern - was Ihnen im Zusammenfassungsblatt falsche Ergebnisse vorgaukeln würde. Erstellen Sie lieber ein frisches Zusammenfassungsblatt. Eine Erleichterung bietet der Einsatz dynamischer Namen für die Ausgangsbereiche, Sie müssen dann die Konsolidierung zwar auch neu vornehmen, brauchen die Verweise aber nicht anpassen, da die Ausgangsbereiche dynamisiert sind.
• Spalten, die in den Ausgangslisten Text enthalten, aber nicht als Beschriftungsspalte ausgewählt sind, werden in der Zusammenfassung leer erscheinen. Sie können diese aber über Formeln korrekt befüllen und dann in Werte wandeln. Lösung hier an einem Beispiel demonstiert
• Sie können vorhandene Verweise auch löschen, indem Sie diese im Dialogfenster Konsolidieren im unteren Textfeld markieren und auf die Schaltfläche Löschen klicken; da Excel einmal definierte Verweise mit der Datei speichert, ist das wichtig, wenn Ausgangsbereiche sich geändert haben oder nicht mehr in die Zusammenfassung fallen sollen
• addieren Sie in der Zusammenfassung z.B. Umsätze nach Kunden und in den Ausgangslisten ist z.B. die Spalte Rechnungsnummer enthalten, dann werden auch die Rechnungsnummern addiert, was natürlich keinen Sinn macht. In der Zusammenfassung sind solche Spalten anschließend zu löschen
• werden mehrere Listen zusammengefasst, ist die Zusammenfassung zunächst unsortiert. Setzen Sie nach der erfolgten Zusammenfassung dafür einfach den Cursor in die zu sortierende Spalte und klicken Sie auf das Icon auf-/oder absteigende Sortierung in der Standard-Symbolleiste
• bei einer Ausgangsliste, aus der Duplikate kombiniert werden sollen, ist die Zusammenfassung in der Reihenfolge der Ausgangsliste - ist aber bei Bedarf ebenfalls sortierbar
• wenn Sie zukünftig nur noch mit der neuen Zusammenfassungsliste arbeiten wollen, können sie die Ausgangslisten löschen, um Verwirrung und Datenredundanz zu vermeiden

Diese 11 Funktionen stehen für die Zusammenfassung zur Verfügung: Summe Anzahl Mittelwert Maximum Minimum Produkt Anzahl Zahlen Standardabweichung (Stichprobe) Standardabweichung (Grundgesamtheit) Varianz (Grundgesamtheit) Varianz (Stichprobe)

An drei Beispielen zeige ich Ihnen den praktischen Einsatz des Menübefehls Konsolidieren:

• Mehrere Listen in einer neuen Liste zusammenfassen
• Duplikate einer Liste in einer neuen Liste kombinieren
• Listenzuordnung erstellen in einer Übersichtsliste

Zum Blattanfang

Beispiel: Mehrere Listen in einer zusammenfassen

In meinem Beispiel gehe ich von zwei Ausgangslisten aus (die auch in verschiedenen Dateien liegen können), das können aber auch mehr sein, wichtig ist der identische Tabellenaufbau aller Ausgangslisten.

Zwei Kolleginnen hätten Ausgangsrechnungen erfasst, dabei kommen in beiden Ausgangslisten gleiche Kunden vor. Sie möchten nun eine zusammenfassende Liste daraus erstellen, um den Umsatz je Kunde zu sehen.

Hier eine Abbildung der beiden Ausgangstabellen und der Konsolidierungsliste vor und nach weiterer Bearbeitung:



Vorgehensweise:

• legen sie ein neues Tabellenblatt an, im Beispiel Konsolidierung
• kopieren Sie die Überschriftenzeile aus einer Ausgangstabelle ins Blatt Konsolidierung
• setzen Sie den Cursor ins Blatt Konsolidierung in die erste Zelle der Überschrift
• gehen Sie über Menü > Daten > Konsolidieren... - das Dialogfenster springt auf
• wählen Sie unter Funktion im Dropdown die gewünschte Funktion aus, in unserem Fall Summe
• nun fügen wir nacheinander die verschiedenen Ausgangstabellen bzw. die entsprechenden Bereiche als Verweise hinzu. Klicken Sie dazu in das Textfeld unter Verweis und wechseln ins Ausgangstabelle1 und markieren dort den Bereich A1:C8. Dadurch schreibt sich der Bereich passend in das Textfeld. Klicken Sie nun auf die Schaltfläche Hinzufügen . Wiederholen Sie den Vorgang für Ausgangstabelle2 und markieren dort den Bereich A1:C10 und fügen auch diesen Verweis durch Anklicken der Schaltfläche hinzu
• haken Sie beides an: Beschriftung aus Oberster Zeile und Linker Spalte
• schließen Sie das Dialogfenster über OK ab
• nun liegt Ihnen die Konsolidierungsliste in unsortierter Form mit der sinnlos addierten Spalte Rg-Nr. vor
• klicken Sie in der Konsolidierungsliste in den Datenbereich der Spalte A und sortieren die Kundennamen über das aufsteigend sortieren Icon aus der Symbolleiste Standard
• löschen Sie die Spalte Rg-Nr. - nun entspricht die Konsolidierungstabelle der Abbildung oben rechts

Zum Blattanfang

Beispiel: Duplikate einer Liste in einer neuen Liste kombinieren

Ausgangssituation: Sie haben eine Liste, die viele Duplikate (hier Kundennamen mehrfach) enthält. Sie möchten eine Zusammenfassung erstellen, in der jeder Kunde nur mit seinem Gesamtumsatz erscheint.

Hier eine Abbildung der Ausgangstabelle und der Konsolidierungsliste vor und nach weiterer Bearbeitung:



Vorgehensweise:

• legen sie ein neues Tabellenblatt an, im Beispiel Konsolidierung
• kopieren Sie die Überschriftenzeile aus der Ausgangstabelle ins Blatt Konsolidierung
• setzen Sie den Cursor ins Blatt Konsolidierung in die erste Zelle der Überschrift
• gehen Sie über Menü > Daten > Konsolidieren... - das Dialogfenster springt auf
• wählen Sie unter Funktion im Dropdown die gewünschte Funktion aus, in unserem Fall Summe
• nun fügen Sie die Ausgangstabelle bzw. den entsprechenden Bereich als Verweis hinzu. Klicken Sie dazu in das Textfeld unter Verweis und wechseln in die Ausgangstabelle und markieren dort den Bereich A1:C9. Dadurch schreibt sich der Bereich passend in das Textfeld. Klicken Sie nun auf die Schaltfläche Hinzufügen
• haken Sie beides an: Beschriftung aus Oberster Zeile und Linker Spalte
• schließen Sie das Dialogfenster über OK ab
• nun liegt Ihnen die Konsolidierungsliste ohne Duplikate in unsortierter Form mit der sinnlos addierten Spalte Rg-Nr. vor
• klicken Sie in der Konsolidierungsliste in den Datenbereich der Spalte A und sortieren die Kundennamen über das aufsteigend sortieren Icon aus der Symbolleiste Standard
• löschen Sie die Spalte Rg-Nr. - nun entspricht die Konsolidierungstabelle der Abbildung oben rechts

Zum Blattanfang

Beispiel: Listenzuordnung erstellen in einer Übersichtsliste

Ausgangssituation: Ihre Datei umfasst einige Blätter (in meinem Beispiel Kurs1, Kurs2, Kurs3), in jedem der Blätter sind die Teilnehmer der Kurse erfasst. Sie wünschen eine Übersicht, in der alle Teilnehmer ersichtlich sind mit Information, welche Kurse sie belegt haben.

Hierfür ist etwas zusätzliche Handarbeit vor der Konsolidierung erforderlich - vorab hier eine Abbildung der drei Kurstabellen und der Konsolidierungsliste vor und nach weiterer Bearbeitung:



Die vorkommenden Nummern in der endgültigen Liste sagen aus, welche und somit auch wie viele Kurse belegt sind. Eine 0 steht jeweils für nicht belegt. In der Form kann man das auf bis zu 9 Kursen erweitern.

Vorgehensweise:

• legen sie ein neues Tabellenblatt an, im Beispiel Konsolidierung
• kopieren Sie die Teilnehmer aus Kurs1 mit Überschrift in die Tabelle Konsolidierung, der Nachbarspalte erteilen Sie die Überschrift Kurs und schreiben darunter eine 1 neben jeden Teilnehmer
• kopieren Sie die Teilnehmer aus Kurs2 ohne Überschrift in diese neue Tabelle in die erste freie Zeile der Teilnehmerspalte, in die Kursspalte schreiben Sie eine 20 neben jeden Teilnehmer
• kopieren Sie die Teilnehmer aus Kurs3 ohne Überschrift in diese neue Tabelle in die erste freie Zeile der Teilnehmerspalte, in die Kursspalte schreiben Sie eine 300 neben jeden Teilnehmer

Danach sieht das Blatt so aus:

A B 1 Name Kurs 2 Baum 1 3 Krüger 1 4 Richter 1 5 Sommer 1 6 Winter 1 7 Asbach 20 8 Jonas 20 9 Michels 20 10 Richter 20 11 Sommer 20 12 Winter 20 13 Sommer 300 14 Bungert 300 15 Asbach 300 16 Jonas 300 17 Krause 300

• kopieren Sie die Überschriftenzeile aus Spalte A und B in Spalten D und E im Blatt Konsolidierung
• setzen Sie den Cursor im Blatt Konsolidierung in die erste Zelle der Überschrift Spalte D
• gehen Sie über Menü > Daten > Konsolidieren... - das Dialogfenster springt auf
• wählen Sie unter Funktion im Dropdown die gewünschte Funktion aus, in unserem Fall Summe
• nun fügen wir die Ausgangstabelle bzw. den entsprechenden Bereich als Verweis hinzu. Klicken Sie dazu in das Textfeld unter Verweis und markieren dort den Bereich A1:B17. Dadurch schreibt sich der Bereich passend in das Textfeld. Klicken Sie nun auf Hinzufügen
• haken Sie beides an: Beschriftung aus Oberster Zeile und Linker Spalte
• schließen Sie das Dialogfenster über OK ab
• nun liegt Ihnen die Konsolidierungsliste in unsortierter Form in Spalten D und E vor
• löschen Sie nun die Spalten A:C - nun sieht unsere Zusammenfassungstabelle aus, wie oben die zweite von rechts, noch unsortiert und die Kursnummern unformatiert
• klicken Sie in der Konsolidierungsliste in den Datenbereich der Spalte A und sortieren die Teilnehmernamen über das aufsteigend sortieren Icon aus der Symbolleiste Standard
• Markieren Sie Spalte B und gehen über Menü > Format > Zellen... > Registerblatt Zahlen, klicken auf Benutzerdefiniert und geben im Textfeld Typ so viel Nullen ein, wie wir Kurse haben, in unserm Fall 000. Schließen Sie den Dialog über den OK

Nun sieht Ihre Übersichtstabelle gemäß unserer obigen Endversion aus.

Zum Blattanfang

Beispiel: Leere Textspalten in Konsolidierungslisten befüllen

Spalten, die in den Ausgangslisten Text enthalten, aber nicht als Beschriftungsspalte ausgewählt sind, erscheinen in der Zusammenfassung leer. Sie können diese aber über Formeln korrekt befüllen und dann in Werte wandeln.

Hier eine Abbildung der Ausgangstabelle und der Konsolidierungsliste vor und nach weiterer Bearbeitung:



Vorgehensweise:

• die Konsolidierung selbst erstellen Sie wie hier beschrieben
• zusätzlich fügen Sie nun nach Konsolidierung und Sortierung in Spalte B des Blatts Konsolidierung folgende SVERWEIS-Formel ein, den Bereich natürlich Ihren Bedürfnissen anpassen, die Formel aus B2 ist runterkopierbar:

A B C 1 Kunde Ort Umsatz 2 Baum München 186,99 € 3 Hürtgen Kiel 93,84 € 4 Neumann Köln 133,20 € 5 Rusti Bonn 23,81 € 6 Wolff Berlin 94,06 € Formeln der Tabelle B2 : =SVERWEIS(A2;Ausgangstabelle!A$2:B$9;2;0)

• anschließend kopieren Sie Spalte B und fügen an gleicher Stelle (über Menü>Bearbeiten>Inhalte einfügen... Werte anhaken, mit OK bestätigen) die Formelergebnisse als Werte ein
• nun ist Ihre Konsolidierungsliste in der bisher leeren Textspalte befüllt, wie in unserem obigen Beispiel