Autor: Beate Schmitz  --- Aus Excel Standard - Gruppe: Spezielle Menübefehle

Konsolidieren

Autor: Beate Schmitz - Erstellt: 2006-03      - Letzte Revision: ?
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:
SummeAnzahlMittelwertMaximumMinimumProdukt
Anzahl
Zahlen
Standardabweichung
(Stichprobe)
Standardabweichung
(Grundgesamtheit)
Varianz
(Grundgesamtheit)
Varianz
(Stichprobe)
 
 

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

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:
 
 AB
1NameKurs
2Baum1
3Krüger1
4Richter1
5Sommer1
6Winter1
7Asbach20
8Jonas20
9Michels20
10Richter20
11Sommer20
12Winter20
13Sommer300
14Bungert300
15Asbach300
16Jonas300
17Krause300
 

  • 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:
 
 ABC
1KundeOrtUmsatz
2BaumMünchen186,99 €
3HürtgenKiel93,84 €
4NeumannKöln133,20 €
5RustiBonn23,81 €
6WolffBerlin94,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

Fragen stellen im Online-Excel Forum

Neu in Online-Excel: Online-Excel Newsletter

Weitere Artikel der Gruppe: Spezielle Menübefehle Aus Excel Standard
Nach oben