Tabellen(blätter) zusammenführen

Tabellenblätter oder Intelligente Tabellen mit Power Query zusammenführen

Voraussetzungen

Die wichtigste Voraussetzung: Sie haben das Add-In Power Query in Ihr Excel integriert und aktiviert. Standardmäßig ist es in Excel 2010/13 nicht vorhanden, Sie können es aber kostenlos von Microsoft herunter laden und dann installieren. Durch die Installation wird das Add-In automatisch aktiviert. Auf einen Link verzichte ich hier, da MS öfter einmal den „Lagerort“ verändert und mein Link dann fehlerhaft wäre 😥 . Unser Tipp: Geben Sie in der Suchmaschine Ihrer Wahl die Begriffe Power Query Download ein, dann werden Sie schnell fündig. Ab Excel 2016 ist diese Funktionalität integriert, aber (natürlich) unter einem anderen Namen: Abrufen und Transformieren. - Eine weitere Voraussetzung für Power Query ist eine Windows-Version ab 7.0 und mindestens Office (Excel) 2010. Ab Excel 2016 ist die Funktionalität bereits integriert und im Menü Daten Gruppe Abrufen und Transformieren zu finden.

Die Quell-Daten (Datenbasis)

Power Query ist ein ausgesprochen mächtiges Instrument zur Analyse und Auswertung von vorhandenen Daten.  Dieses Tool beschränkt sich keineswegs auf Excel als Datenquelle; hier in diesem Tutorial wird aber ausschließlich mit Excel Arbeitsblättern bzw. Intelligenten Tabellen gearbeitet. An passender Stelle finden Sie den Link zu unseren Muster-Arbeitsmappen.

Die Quelldaten müssen sich nicht in einer (einzigen) Arbeitsmappe befinden, es können auch unterschiedliche Files sein. Die alleinige Voraussetzung für das Zusammenführen der Daten: Der Aufbau der Tabellen ist immer gleich, das bedeutet: Jede passende Spalte der anzufügenden Datei muss gleichartige Daten enthalten. Die Überschrift muss auch identisch sein, wenn die Daten angefügt werden sollen. Und dann darf es auch in einer Datenquelle Text (als numerischer Wert) und in der anderen eine „normale“ Zahl sein; das ist zwar nicht optimal kann aber im Nachhinein bereingt werden.

Hinweis/Nachtrag: Wenn Sie mehrere Tabellen/Listen aneinanderhängen wollen, also ähnlich copy/paste aneinanderfügen (was hier ja auch gefragt ist), dann müssen die Überschriften der Spalten identisch sein; andernfalls wird in der erzeugten Abfrage eine neue Spalte mit den entsprechenden Werten angefügt. Diese Bedingung entspricht dem Menüpunkt Kombinieren | Abfragen anfügen. Die Reihenfolge ist nicht relevant, Power Query identifiziert die Spalten ausschließlich anhand der Überschriften.

▲ nach oben …

Vorarbeit

Natürlich werden Sie sich erst einmal Gedanken machen, welche Daten Sie zusammenfassen wollen. Und selbstverständlich auch, in welcher Form das Ergebnis dargestellt werden soll. In unserer Mustermappe werden Umsätze verschiedener Produkte aus allen 12 Monaten eines Jahres zusammengefasst. Wollen Sie beispielsweise nur die absoluten Summen der einzelnen Produkte haben, dann reicht es, die Monate zu konsolidieren. Wollen Sie aber alle einzelnen Tagesumsätze des Jahres in einer einzigen Tabelle zusammenfassen und diese dann beispielsweise mit Pivot auszuwerten, dann ist Power Query eine gute Wahl. Natürlich geht auch copy/paste per Hand oder eine VBA-Lösung …

Die Quell-Daten finden Sie in dieser Arbeitsmappe. Wenn Sie diese öffnen werden Sie erkennen, dass dort für jeden Monat eine Auswertung erfolgt ist. Zusätzlich existiert noch eine Tabelle für die Feiertage des Jahres. Eine Jahres-Zusammenfassung gibt es nicht, aber die ist ja das Ziel dieses kleinen Seminars.  😉 

Sie werden mit zwei unterschiedlichen Vorgehensweise arbeiten. Darum sollten Sie die eben geladene Tabelle unter dem existierenden Namen auf Ihrem Rechner speichern und gleich noch einmal mit einem anderen Namen, beispielsweise Kleinbrot_12_Monate_2 oder ähnlich. Merken Sie sich den Speicherort, den Pfad beider Files, Sie werden ihn nämlich noch brauchen.

Diese eben unter dem anderen Namen gespeicherte Datei ist ja immer noch geöffnet. Und hier ist nun etwas (mehr) Fleißarbeit angesagt. Und zwar soll jeder Datenbereich aller Monate in eine Intelligente Tabelle umgewandelt werden. Das geht am schnellsten, wenn Sie per StrgPos1 zu A1 gehen (es darf aber auch eine beliebige Zelle innerhalb der Daten des Blattes sein) und dann StrgT oder StrgL betätigen. Umgehend wird dann nach dem OK daraus eine Intelligente Tabelle erstellt. Wenn Sie sich die Arbeit für später erleichtern wollen, dann geben Sie der Tabelle gleich einen aussagekräftigen Namen, beispielsweise tbl_Januar. Das geht recht einfach, wenn Sie beim kontextbezogenen Menüpunkt Tabellentools den einzigen Punkt Entwurf anklicken und dann in der ersten Gruppe Eigenschaften direkt den Tabellennamen ändern:

Ändern des Namens einer Intelligenten Tabelle

Ändern des Namens einer Intelligenten Tabelle

Bei der Gelegenheit: In diesem Beitrag werden wir immer die Bezeichnung Tabelle für eine Intelligente Tabelle wählen, das Tabellenblatt ist damit nie gemeint; das wird dann entsprechend bezeichnet. Nochmals der Hinweis: In anderen Beiträgen wird das Wort „Tabelle“ durchaus sinn-übergreifend verwendet.

Schließen Sie nun diese Mappe. Auch wenn es machbar wäre, so soll die Auswertung bzw. Zusammenfassung nicht in einer der beiden Mappen mit den Basis-Daten entstehen, es soll eine neue Mappe mit (mindestens) einer neuen Tabelle geschaffen werden, wo dann weitere Auswertungen beispielsweise per PivotTable erfolgen können. Darum als letzte „Tat“ der Vorbereitungen: Legen Sie eine neue, leere Mappe an. Wenn Sie Excel geöffnet haben geht das recht gut mit StrgN; falls Excel nicht geöffnet ist, öffnen Sie einfach nur das Programm Excel. Normalerweise haben Sie dann ja eine neue, leere Mappe mit mindestens einem Tabellenblatt auf dem Schirm.

▲ nach oben …

Daten aus der ersten Mappe (ohne Monats-Tabellen)


03.08.2017
Aufgrund eines Kommentars mit einem berechtigten Hinweis und als kleines Dankeschön für den einen oder anderen Klick auf den Donate-Button 😆 habe ich mich entschlossen, den Beitrag auch für die 2016er Version zu erweitern. Gerade für Einsteiger ist es wichtig, ein möglichst hohes Maß an visueller Übereinstimmung zu haben; und auch die unterschiedliche Namensgebung trägt nicht immer zur leichteren Handhabung bei.


Sie befinden sich im ersten Tabellenblatt einer neuen, leeren Mappe. Ein Klick auf den Menü­punkt Power Query und das entsprechende Menüband wird sich öffnen. In der ersten Gruppe (links) Externe Daten abrufen wählen Sie den Punkt Aus Datei. In dem DropDown ist die oberste Auswahl, Aus Excel die richtige:

Die Datenquelle ist eine Excel-Datei

Die Datenquelle ist eine Excel-Datei


Version 2016

Sie befinden sich im ersten Tabellenblatt einer neuen, leeren Mappe. Ein Klick auf den Menü­punkt Daten | Abrufen und transformieren und es öffnet sich das passende Menüband:

Das Menüband für Power Query Version 2016 Ausschnitt des Menüs Daten)

Das Menüband für Power Query Version 2016 Ausschnitt des Menüs Daten)

Hier kommen Sie über den Punkt Neue Abfrage …

Neue Abfrage erstellen

Neue Abfrage erstellen

… zum Untermenü und dort über die Auswahl Aus Datei zum Punkt Aus Arbeitsmappe:

Abfrage aus einer Excel-Datei erstellen (2016)

Abfrage aus einer Excel-Datei erstellen (2016)


Ab hier wieder ähnlich in allen Versionen

Nach einem Klick darauf wird sich das typische Auswahlfenster für das Öffnen von Dateien auftun. Die hier gekennzeichnete Datei (Kleinbrot_12_Monate.xlsx) ist die richtige, der Pfad wird bei Ihnen gewiss ein anderer sein:

Für den ersten Versuch die "unbehandelte" Datenquelle

Für den ersten Versuch die „unbehandelte“ Datenquelle

Nach dem OK werden Sie rasch erkennen, dass Sie nicht die Datei direkt geöffnet haben sondern ein Fenster, wo Sie offensichtlich eines oder mehrere Elemente der Mappe auswählen können:

Der erste Eindruck von Power Query

Der erste Eindruck von Power Query

Klar erkennbar ist, dass der Name jedes einzelnen Tabellenblattes dort aufgeführt ist. Und dann noch einmal Tabelle1 mit einem etwas anderen Symbol. Zugegeben, das verwirrt etwas. Hier ist es noch einigermaßen deutlich erkannbar, was was ist. Denn Tabelle1 gibt es nur 1 Mal in der Aufstellung. Wenn aber in der zu verknüpfenden Quelldatei auch ein Arbeitsblatt mit diesem Namen existiert, dann wird es etwas unübersichtlich. Immerhin,  anhand der unterschiedlichen Symbole wird auch in solchen Fällen deutlich gemacht, dass es sich dort ein Mal um das hier existierende Tabellenblatt und im zweiten Fall um das zu verknüpfende Blatt handelt. – Extrem verwirrend kann es sein, wenn der Bezug zu Intelligenten Tabellen hergestellt werden soll, die ja von Haus aus auch diese Namensgebung haben. (Das ist auch einer der Gründe, warum wir empfehlen, die Listen sofort entsprechend umzubenennen.)

Als erstes werden Sie vielleicht merken, dass Sie nur jeweils eine einzige Auswahl treffen können. Das lässt sich aber ändern, indem Sie ganz oben das Kästchen Mehrere Elemente auswählen anklicken. Dann markieren Sie das symbolische Tabellenblatt Januar, Shift und danach Dezember. Sofort sind alle entsprechenden Häkchen gesetzt:

Die Mehrfach-Auswahl ist getroffen

Die Mehrfach-Auswahl ist getroffen

Im Anschluss einfach ein Klick auf die Schaltfläche Laden |  und nach kurzer Zeit sieht Ihr Tabellenblatt bzw. das Arbeitsfenster etwa so aus:

Noch ohne Daten aber rechts mit einem Steuerungs-Bereich

Noch ohne Daten aber rechts mit einem Steuerungs-Bereich

Wenn Sie nun in diesem Dashboard (rechts) auf einen Eintrag zeigen, wird Ihnen ein Ausschnitt des verlinkten Tabellenblatts gezeigt. So können Sie bei nicht „sprechenden“ Blattnamen erkennen, ob es das richtige ist.

In Excel 2016 stellt sich das Ganze etwas anders dar:

Nach dem Import aller 12 Monate (Excel 2016)

Nach dem Import aller 12 Monate (Excel 2016)

▲ nach oben …

Zusammenfassen

Nun sollen ja alle Monate irgendwie in ein einziges Tabellenblatt gebracht werden. Es wäre ja zu schön, wenn Sie jetzt eine Markierung nach der anderen aus der Seitenleiste in das Blatt ziehen könnte. Aber das geht leider nicht. OK, dann eben der „offizielle“ Weg. In der Gruppe Kombinieren gibt es zwei Auswahlen. Und nein, Zusammenführen ist es nicht! Sie wählen den Punkt Anfügen. Da wird es dann wieder so richtig schön fachlich. Lesen Sie sich die Beschreibungen der beiden Punkte einfach einmal genau durch und versuchen Sie, diese zu verstehen. Mit unserer Hilfe werden Sie dann auch sicher zum Ziel gelangen  😉 :

Der erste Schritt zum zusammenfügen der Tabellenblätter

Der erste Schritt zum zusammenfügen der Tabellenblätter

Eine Tabelle, in diesem Umfeld ein Tabellenblatt muss den Sockel bilden, wo die anderen Daten dann angefügt werden. Das ist sinnvollerweise der Januar, das macht sich der Optik wegen besser (ist aber nicht erforderlich). Und im Feld darunter, wo Sie die anzufügende Tabelle bzw. das Tabellenblatt, welches Sie anfügen wollen, auswählen müssen, wird das naturgemäß …

Das erste wirkliche Anfügen

Das erste wirkliche Anfügen

Jetzt sollte der OK Button auch nicht mehr ausgegraut und auch anklickbar sein. Tun Sie das bitte und es wird ein neues Tabellenblatt mit einer Tabelle erstellt, wo beide Monate untereinander zusammengefasst worden sind:

Die ersten beiden Monate sind zusammengefasst

Die ersten beiden Monate sind zusammengefasst

Und der Steuerbereich rechts sieht in diesem Fenster auch etwas anders aus. Wichtig ist, dass dort bei den Eigenschaften der Name Append1 steht. Dieses ist der Hinweis darauf, dass Sie sich in einer besonderen Tabelle befinden, welche auch in einem gesonderten Fenster angezeigt wird. Sie sind im Anfügemodus.

Um nun die folgenden Monate (März bis Dezember) an diese Tabelle anzufügen, gehen Sie anschließend so vor: 

Weitere Daten aus anderen Arbeitsblättern werden so angefügt

Weitere Daten aus anderen Arbeitsblättern werden so angefügt

Sie wählen in der Gruppe Neue Abfrage (rechts) das Symbol Kombinieren. Dort klicken Sie auf den Untermenüpunkt Abfragen anfügen. In dem neuen Fenster haben Sie nun nur noch die Möglichkeit, die anzufügende Tabelle auszuwählen:

Hier wird nur nach dem anzufügenden Blatt gefragt

Hier wird nur nach dem anzufügenden Blatt gefragt

Sie werden naturgemäß den März als Tabellenblatt wählen, welches angefügt werden soll. Und genau so gehen Sie bei den Folgemonaten April bis Dezember vor.

Wenn Sie nun einmal die Zeilen dieser Liste durchscrollen wird Ihnen gewiss auffallen, dass unterhalb der Zeile mit der Monatssumme eine Zeile eingefügt worden ist, die ausschließlich leere Felder enthält. dort steht in jeder Spalte null als Wert, was rein gar nichts mit dem Wert, mit der Zahl Null zu tun hat. Um diese Zeilen automatisch entfernen zu lassen, bietet Power Query Ihnen die Möglichkeit, Leerzeilen automatisch zu entfernen:

Leerzeilen automatisch entfernen lassen

Leerzeilen automatisch entfernen lassen

Je nach Fenstergröße Ihres Excel kann sich das Menü etwas anders darstellen. Aber der Weg ist der gleiche. Automatisch werden alle Zeilen entfernt, die komplett leer sind. Die Sonn- und Feiertage enthalten zwar auch nur null-Werte in den Spalten der Produkte, aber durch das Datum ist die Zeile ja nicht komplett leer.

Im Dashboard (rechts neben dem Tabellenbereich) erkennen Sie, welche Vorgänge alle vollzogen worden sind. Und die werden auch so gespeichert. Der Hintergrund: Wenn sich in den Quelldaten etwas ändert und Sie Power Query noch einmal aufrufen, können Sie die Daten mit einem Mausklick aktualisieren. Hier bleibt als letzter Schritt dieses Abschnitts ein Klick auf die Schaltfläche ganz links, Schließen & laden und im Untermenü noch einmal genau diesen Punkt anklicken oder noch einmal auf die große Schaltfläche.

▲ nach oben …

Kleine Korrekturen

Ein neues Tabellenblatt ist automatisch angelegt worden. Auf den ersten Blick sieht das ja ganz vernünftig aus. Auf den zweiten Blick fällt auf, dass keine der Zahlen formatiert ist. Bei den Umsätzen ist das ja noch halbwegs tragbar, aber beim Datum muss das nun wirklich nicht sein. Also formatieren Sie die Spalte A als Datum lang und die restlichen Spalten als Währung. Nun hat das Ganze ein „vernünftiges“ Aussehen.

Eines allerdings stört noch: Die Summen, welche in Spalte H und in den Zeilen nach dem Monatsende angezeigt werden. Auch das lässt sich rasch bereinigen. Spalte H einfach komplett löschen. Dann Spalte A so filtern, dass nur die 12 Summen-Zeilen angezeigt werden und diese dann löschen. Danach den Filter wieder entfernen und alles sieht so aus, wie es sein soll.

▲ nach oben …

Ernüchterung

Wenn Sie sich das Worksheet Tabelle2 ansehen, dann werden Sie auf den ersten Blick sagen, dass es sich um eine Intelligente Tabelle handelt. Die grün-weiße Liniierung und die automatisch eingefügten Sortier- und Filterfunktionalitäten in der Überschriftszeile sprechen dafür. Ganz klar wird das, wenn Sie irgendwo in den Datenbereich klicken und dann die oberste Menüzeile erkunden. Ganz rechts sehen Sie den Eintrag Tabellentools und darunter zwei Menüpunkte: Abfrage und Entwurf. Aktivieren Sie einmal Abfrage und klicken dann auf das Symbol Aktualisieren und den Untermenüpunkt Aktualisieren. Kann ja nicht schaden …

Irgendwie sieht das Ganze danach doch anders aus. Richtig, die beiden Summen sind wieder da. Und unter gewissen Umständen kann es noch mehr Überraschungen geben; beispielsweise dass auch die Zahlenformatierungen wieder zurück genommen werden.

Das mit der Spalte lässt sich in diesem Rahmen durchaus regeln. Für die Zeilen mit der Summe werden Sie weiter unten noch einen anderen Weg kennen lernen, der auf Dauer gesehen so wie so der bessere ist. Aber eines nach dem anderen.  😉 

Doppelklicken Sie im Dashboard auf den untersten Eintrag Append1. Dadurch öffnet sich wieder der Abfrage-Editor, den Sie ja bereits kennen. Markieren Sie einen beliebigen Eintrag in der Spalte mit der Überschrift Summe. Nun klicken Sie in der Gruppe  Spalten verwalten auf das Symbol Spalten entfernen. Sofort wird die markierte Spalte entfernt. Nun wieder Schließen und laden und die Summe-Spalte, ehemals Spalte H ist nicht mehr da. Auch nicht nach einem Aktualisieren 😎 .

▲ nach oben …

Warum der ganze Aufwand?

Bislang haben Sie nur einen mehr oder weniger aufwendigen Weg kennen gelernt, Daten zusammen zu fügen. Vielleicht ist Ihnen bereits in den Sinn gekommen, dass copy/paste eine einfachere Lösung gewesen wäre. Nun ja, mit dem jetzigen Wissensstand ist das durchaus nachvollziehbar. Aber einer der wirklich unschlagbare Vorteile des Power Query ist darin begründet, dass einerseits die Quelldaten unverändert bleiben, auch wenn die Darstellung komplett geändert wird; andererseits sind die Daten verknüpft, und diese Dynamik ist ein enormer Vorteil.

Ein ganz kleines Beispiel: Dem Chef der Bäckerei ist gerade aufgefallen, dass der 3. Oktober zwar ein Feiertag war aber eine Filiale dennoch geöffnet sein musste, weil diese in einem Einkaufscenter gelegen ist, wo eine Öffnungsverpflichtung besteht, wenn ein verkaufsoffener Sonn- oder Feiertag ist. Und der Tag der Deutschen Einheit war ausnahmsweise solch ein Tag und die Filiale hat natürlich auch Umsatz gemacht. Also schnell die Datei Kleinbrot_12_Monate.xlsx geöffnet, das Tabellenblatt Oktober ausgewählt und am 3.10. einen (beliebigen) Umsatz eintragen. Und dann wieder schließen. Wenn Sie jetzt Ihre Tabelle aktualisieren, wird auch der eben in die Basis-Daten eingetragene Umsatz sichtbar sein.

Zugegeben, das ist nicht wirklich spektakulär. Aber stellen Sie sich vor, dass zu Beginn des Jahres alle Monate in der Quelldatei nur mit der Überschrift anlegen. Für den aktuellen Monat füllen Sie dann täglich die Umsätze in das entsprechende Tabellenblatt ein. Dadurch wird die Zusammenfassung per Power Query immer aktuell sein, ohne dass Sie irgend eine Anpassung in der Auswertung vornehmen müssen. Das ist auf Dauer gesehen schon eine feine Sache. Im Endeffekt bestimmen Sie mit der Abfrage (Query), welche Daten wie angezeigt werden sollen, und da gibt es noch einige Möglichkeiten, die erkundenswert sind.

▲ nach oben …

Daten aus der zweiten Mappe (mit Monats-Tabellen)

Sie haben die identischen Daten (natürlich noch ohne den 3.10.) in einer anderen Mappe so aufbereitet, dass jeder Monat als Intelligente Tabelle aufbereitet ist. Auch wir haben das erledigt und wenn Sie mögen, laden Sie das Muster hier herunter. Die einzelnen Tabellen haben den Monatsnamen und sind mit dem Präfix „tbl_“ versehen. Und die Schaltflächen für Filterung und Sortierung haben wir auch entfernt, auch wenn das nur der Optik dient. Insgesamt können wir Ihnen sagen, dass (Intelligente) Tabellen eine bessere Grundlage für Abfragen mit Power Query sind.

Gehen Sie erst einmal wie im obigen Abschnitt gelernt vor. Lassen Sie Ihre Datei mit den Tabellen oder auch unsere Vorlage geschlossen. Menü Power Query und rufen Sie über Externe Daten abrufen | Aus Datei | Aus Excel die entsprechende Datei (beispielsweise unser Beispiel Kleinbrot_12_Monate_tbl.xlsx) auf. Als erstes erscheint ja das Fenster mit dem Navigator. Wenn Sie es sich leichter machen wollen, geben Sie in das Textfeld ganz oben (direkt unter der Überschrift Navigator) tbl_ ein. Sofort wird die mögliche Auswahl gefiltert und es bleiben fast nur noch jene Einträge übrig, die Sie auswählen wollen. Voraussetzung ist natürlich, dass Sie entweder unsere Arbeitsmappe verwenden oder in Ihrer Datei dem Rat gefolgt sind, die erstellten Listen/Tabellen entsprechend umzubenennen. Nun werden Sie wie gehabt die Möglichkeit schaffen, mehrere Elemente auszuwählen und alle Monate wieder mit der Shift-Methode markieren. Dann nur noch auf die Laden | -Schaltfläche klicken.

Im Dashboard sind kurz danach 12 Abfragen gelistet. Vielleicht fällt Ihnen hier schon auf, dass pro Monat genau 1 Zeile mehr geladen bzw. verknüpft worden ist, als der Monat Tage hat. Das unterscheidet sich deutlich vom vorhergehenden Modell. Wie Sie es bereits gelernt haben, werden Sie nun im der Gruppe Kombinieren auf das Symbol Anfügen klicken. Die primäre Tabelle ist (natürlich) der Januar, angefügt werden soll die Tabelle tbl_Februar. Anschließend die Schaltfläche OK.

Selbstredend könnten Sie nun so wie im ersten Abschnitt die Folgemonate anfügen. Oder aber Sie sorgen jetzt schon dafür, dass die unerwünschten Summe-Zeilen entfernt werden. Die erste Zeile wäre dann ja die Zeile 32. Also klicken Sie im Menüband einmal in der Gruppe Zeilen verringern auf Zeilen entfernen. Dort wählen Sie den dritten Menüpunkt im erscheinenden DropDown:

Auswahl im DropDown Menü

Auswahl im DropDown

Im sich öffnenden Fenster sind die ersten beiden Eingaben selbst erklärend:

Zeile 32 soll in der Abfrage gelöscht werden

Zeile 32 soll in der Abfrage gelöscht werden

Was vielleicht irritiert ist die enorm hohe Zahl in dem unteren Eingabefeld. Hier geben Sie eine Zahl ein, die garantiert groß genug ist, die also mindestens alle restlichen vorhandenen Zeilen der Abfrage abdeckt. Wenn mehr Zeilen beibehalten werden als real vorhanden, dann kann das ja nicht schaden. – Nach einem OK werden Sie sehen, dass in Zeile 32 jetzt der 1. Februar aufgeführt ist. Als nächstes wählen Sie noch einmal das Symbol Zeilen entfernen und im Untermenü den Punkt Untere Zeilen entfernen. Da jetzt ja nur noch die letzte Zeile der aktuellen Abfrage Append1 zu entfernen ist, geben Sie bei der Anzahl der zu entfernenden Zeilen eine 1 ein.

Nun werden Sie nacheinander die Tabellen tbl_März bis tbl_Dezember anfügen, nach jeden einzelnen Monat aber immer die letzte Zeile wie eben aufgezeigt entfernen. Nachdem Sie alle Abfragen, alle Monate zusammengefasst haben, klicken Sie irgendwo in die Spalte Summe und anschließend ein Klick auf das Symbol Spalten entfernen in der Menü-Gruppe Spalten verwalten. Zum guten Schluss noch ganz links im Menüband das Symbol Schließen und laden klicken und die Jahresübersicht ist geschaffen. Und zwar durchgehend, ohne die nicht gewünschten Summen. Bleibt nur noch, die Spalte A als Datum (sinnvollerweise im Langformat) und  Spalte B:G als Währung zu formatieren.

▲ nach oben …

Die Basis ändern macht’s leichter

Eine alte Weisheit sagt, dass ein Üben an der Wurzel bekämpft werden muss. Nun ja, das „Übel“ ist hier gewiss stark übertrieben, aber bei Vereinfachungen möglichst weit unten anzusetzen ist schon sinnvoll. Um Ihnen vielleicht etwas Arbeit zu ersparen, haben wir bereits diese Mappe vorbereitet. Dennoch sollten Sie es einmal an einem Beispiel selber ausprobieren, einen oder zwei Monate so einzurichten, wie in unserer Mustermappe alle Monate eingerichtet sind. Öffnen Sie dazu die erste Datei, die noch vollkommen unformatiert war: Kleinbrot_12_Monate.xlsx. Normalerweise haben Sie diese Mappe aber auch noch auf Ihrem Rechner.

Aktivieren Sie nun bitte den Januar. StrgPos1, um zu A1 zu gelangen. Shift und anschließend in die letzte Zelle mit Umsätzen klicken, hier im Januar ist das G32. Als kleine Hilfe: Es ist in jedem Monat die Spalte G, die Zeile ist wegen der Überschrift immer genau 1 höher als der Monat Tage hat.

OK, jetzt ist alles außer der Spalte H und der Zeile Summe markiert. Aus dieser Markierung machen Sie nun eine Tabelle. Also StrgT, den Bereich noch überprüfen und auch gleich die Tabelle in tbl_Januar (oder wenn Sie wollen auch tbl_Jan) umbenennen. Wir haben es so für jeden Monat gemacht. Danach ist die Mappe unter einem anderen Namen (siehe oben) gespeichert worden, damit nicht das Original überschrieben wird.

Nun geht alles erst einmal seinen gewohnten Weg. Sie öffnen eine neue, leere Datei und per Power Query verbinden Sie im ersten Schritt die eben erstellte Datei mit der Mappe. Im Navigator erstellen Sie aus den Tabellen der Monate 12 Abfragen, welche sich dann im Dashboard wiederfinden. wie bereits eben durchexerziert klicken Sie auf das Anfügen-Symbol, wählen tbl_Januar als Basistabelle und fügen tbl_Februar an. Und es wird Sie nicht überraschen, dass wirklich nur die Daten übernommen wurden, die gewünscht sind. Sie brauchen also weder Zeilen noch am Ende die rechte Spalte zu löschen. Wählen Sie nun noch die restlichen Tabellen, um das Jahr zu komplettieren. Dieses Vorgehen eignet sich besonders für Tabellen, welche unterhalb der auszuwertenden Daten aber direkt im Anschluss noch Einträge hat, die nicht ausgewertet werden sollen. Wie auch in der Beispieldatei. Und selbst der 3. Oktober kann noch problemlos nachgetragen werden, ohne nach dem 2.10. eine Zeile neu einfügen zu müssen. Klicken Sie im Oktober auf die letzte Zelle der Tabelle, also G32. Dann einfach Tab und schon wird eine neue Zeile an die vorhandene Tabelle angefügt und die untere Zeile mit der Summe um 1 Zeile nach unten verschoben. Die Eingabe der Daten ist nun leicht möglich, und nach dem Datum zu sortieren ist leicht möglich, aber nicht immer erforderlich.


Gehen Sie nun einmal von folgender Annahme aus: Die Arbeitsblätter für das gesamte Jahr sind in dieser Datei vorbereitet, es ist der 11. März 2014. Die Tagesumsätze des Vortages sind noch nicht in die „normale“ Arbeitsmappe, wo Sie täglich die Daten eingeben, eingetragen. Sie haben sich gerade entschlossen, Power Query einzusetzen. Darum gehen Sie den Ihnen nun bekannten Weg und wählen alle Monate im Navigator aus. - So weit, so gut. Fügen Sie alle 12 Monate nach gehabtem Muster zusammen und erst einmal trotz der fehlenden Einträge Schließen & laden. Die Formatierungen in Sachen Datum und Währung sollten Sie vielleicht auch schon vornehmen. Und für den 10. März fügen Sie nun bitte folgende Umsätze ein:

Brot:  141,26 €
Brötchen:  108,46 €
Gebäck:  56,77 €
Kuchen:  253,97 €
Kaffee:  76,65 €
Sonstiges:  19,97 €

Die Verführung ist gewiss gegeben, das direkt in die von Power Query erstellte Tabelle (Abfrage) zu schreiben. Aber das wäre ohne Sinn und Zweck, denn bei der nächsten Aktualisierung wird solch ein Eintrag gnadenlos überschrieben. Oder probieren Sie es doch einfach einmal aus! Schreiben Sie irgend einen Umsatz in eine oder mehrere Zellen des Tages. Und jetzt öffnen Sie die Basis-Tabelle und tragen dort die korrekten Werte (siehe oben) ein. Sie schließen nun diese Tabelle; das ist wichtig, weil nur so die Änderungen gespeichert werden. – Wechseln Sie nun wieder in die Mappe mit den Power Query Abfragen. Eine beliebige Zelle der Tabelle muss aktiviert sein, damit Sie auch das Menü Tabellentools sehen. Dort das Register Abfrage und dann die Schaltfläche Aktualisieren. Sie erkennen, dass die von Ihnen eingetragenen Werte mit den eben in der Basis-Tabelle eingetragenen Werten überschrieben worden sind.

Was wahrscheinlich nicht ganz so optimal ist: Obwohl noch nicht einmal das erste Quartal des Jahres vorbei ist, werden alle restlichen Tage angezeigt, natürlich ohne Umsatzzahlen, die ja noch nicht existieren können. Da die Auswertung ja auch eine Tabelle ist, gibt es auch die entsprechenden Möglichkeiten. Ein Klick auf die Schaltfläche in A1 (Datum) und das Sortieren/Filtern-Fenster öffnet sich:

Die anzuzeigenden Monate filtern

Die anzuzeigenden Monate filtern

Wählen Sie die ersten drei Monate (oder einen Ihnen genehmen Bereich) und OK. Nun ist die sichtbare Tabelle erheblich übersichtlicher. Bei Bedarf können Sie diesen Filter natürlich Ihren Bedürfnissen entsprechend anpassen.

Zugegeben, es gibt noch einige andere Wege, solch ein Ziel zu erreichen. Insbesondere per VBA ist da einiges machbar, allerdings sind die Daten dann im Normalfall statisch. Auch „zu Fuß“ lassen sich Daten zusammenfügen. Aber Power Query kann noch mehr, als es hier ersichtlich ist. Eine Beschäftigung mit diesem Tool lohnt sich gewiss.

▲ nach oben …

Obwohl dieses Kapitel schon recht ausführlich ausgefallen ist, so haben wir nur etwas ausführlichere Grundlagen behandelt. Auch in Sachen Zusammenführen von Daten ist noch vieles nicht diskutiert worden. Besonders die Möglichkeit, ganz verschiedene Datenquellen wie beispielsweise eine Datenbank, Webabfrage und auch noch Excel-Daten der Version 2003 zusammenzufassen ist ein Vorhaben, was für Power Query keine Hürde darstellt. Hier ist aber Ihr Wille zum Selbststudium gefragt; es lohnt sich gewiss!  💡

▲ nach oben …

Hinweis: Es gibt weitere Beiträge zu exakt diesem Thema; sehr ausführlich in einem Dreiteiler hier sowie auch Lösungen ohne Power Query; einfach einmal das Stichwort zusammenführen, zusammenfassen in das Suchen-Feld eingeben.

▲ nach oben …

Hat Ihnen der Beitrag gefallen?
Erleichtert dieser Beitrag Ihre Arbeit?

Dann würde ich mich über einen Beitrag Ihrerseits z.B. 3,00  freuen …

Dieser Beitrag wurde unter Daten zusammenführen, Daten-Import / -Export, File-Handling, Musterlösungen, Ohne Makro/VBA, Power Query, Tabelle und Zelle abgelegt und mit , , , , , , verschlagwortet. Setze ein Lesezeichen auf den Permalink.