Tutorials

Das Abgleichen von Listen gehört zum Excel-Alltag: So müssen beispielsweise den Rechnungen die Zahlungseingänge gegenübergestellt werden. Es kostet dann einigen Aufwand, bis der Überblick über die offenen Posten fertig ist. Mit Power Query lässt sich das automatisieren und somit deutlich schneller erledigen. Das ständige Suchen nach offenen Rechnungen oder Teilrechnungen gehört der Vergangenheit an. Die Lösung wird in den folgenden 3 Schritten aufgebaut.

Hier gibt es das Tutorial als Schritt-für-Schritt-Anleitung

Schritt 1: Die Listen in »intelligente« Tabellen umwandeln

Führen Sie die Listen mit den Rechnungen und Zahlungen jeweils als »intelligente« Tabelle. Das hat den Vorteil, dass sich beim Hinzufügen neuer Daten die Listen automatisch erweitern und Sie stets über den kompletten Datenbestand verfügen.

1. Klicken Sie in jede Liste und wandeln Sie sie mit Strg+T in eine »intelligente« Tabelle um.
2. Benennen Sie über die Registerkarte Tabellentools ganz links die Tabellen um in Rechnungen und Zahlungen.

Schritt 2: Die Zwischenspeicherabfragen einrichten

Erstellen Sie mit Power Query zu jeder der beiden Tabellen eine Abfrage. In Schritt 3 fassen Sie dann diese beiden Abfragen zusammen und ermitteln so die offenen Posten.

1. Klicken Sie in die Tabelle Zahlungen und wählen Sie in Excel 2016 auf der Registerkarte Daten in der Gruppe Abrufen und transformieren den Befehl Aus Tabelle. In Excel 2010 und 2013 klicken Sie auf der Registerkarte Power Query in der Gruppe Excel Daten den Befehl Von Tabelle.
   

   Der erste Schritt zum Erstellen einer Zwischenspeicherabfrage

2. Klicken Sie auf der Registerkarte Datei auf Schließen & laden in. Im Dialog Laden in wählen Sie Nur Verbindung erstellen und schließen per Klick auf Laden ab.
   

   Da Sie das Ergebnis der Abfrage nicht auf einem Tabellenblatt, sondern in einer anderen Abfrage benötigen, stellen Sie über diesen Dialog lediglich eine Verbindung her

3. Wiederholen Sie die Schritte 1 und 2 für die Tabelle Rechnungen.

Zwischenfazit

Ihre Arbeitsmappe enthält jetzt je eine Verbindung zu den beiden »intelligenten« Tabellen Rechnungen und Zahlungen. Diese werden im Aufgabenbereich Arbeitsmappenabfragen am rechten Bildschirmrand angezeigt. Fehlt dieser Aufgabenbereich, blenden Sie ihn über Daten > Abrufen und transformieren > Abfragen anzeigen ein. In Excel 2010/2013 lautet die Befehlsfolge Power Query > Arbeitsmappenabfragen > Bereich anzeigen.

Schritt 3: Die Offene-Poste-Liste erstellen

In der Offenen-Posten-Liste werden von den Rechnungsbeträgen die Zahlungseingänge abgezogen. Übrig bleibt eine Liste mit nicht oder nicht vollständig bezahlten Rechnungen.

1. Per Rechtsklick auf die Abfrage Rechnungen im Aufgabenbereich Arbeitsmappenabfragen wählen Sie Zusammenführen.
   

   Der erste Schritt zum Kombinieren der Abfragen Rechnungen und Zahlungen

2. In der Mitte des Dialogfelds Zusammenführen wählen Sie im Listenfeld die Abfrage Zahlungen und markieren die Spalte Re-Nr in beiden Tabellen.
3. Im Listenfeld Join-Art belassen Sie die Auswahl bei Linker äußerer Join und schließen mit einem Klick auf OK ab.
   

   Die beiden Abfragen zusammenführen

4. Im Ergebnis erhalten Sie eine neue Abfrage namens Merge1. Sie enthält alle Spalten der Tabelle Rechnungen und eine zusätzliche Spalte NewColumn.
5. Die Uhrzeit in der Spalte Re-Datum wird nicht benötigt. Per Rechtsklick auf den Spaltentitel Re-Datum wählen Sie daher Typ ändern und stellen Datum ein.
6. Klicken Sie auf den Pfeil im Spaltenkopf von NewColumn und wählen Sie die Option Aggregieren. Aktivieren Sie das Kontrollkästchen vor Summe von Zahlbetrag. Dadurch werden für jede Rechnungsnummer die zugehörigen Zahlungseingänge aus der Tabelle Zahlungen automatisch zu einer Gesamtsumme zusammengefasst. Entfernen Sie das Häkchen bei Ursprünglichen Spaltennamen als Präfix verwenden und klicken Sie abschließend auf OK.
   

   Die Zahlungen nach Rechnungsnummern zusammenfassen

7. Benennen Sie die neue Spalte per Doppelklick auf den Spaltenkopf in Zahlbetrag um.
8. Für Rechnungen ohne Zahlungseingang wird der Zahlbetrag mit null angegeben. Ersetzen Sie per Rechtsklick auf null den Wert durch die Zahl 0.
   

   Da sich mit null nicht rechnen lässt, ersetzen Sie diese Werte durch die Zahl 0

9. Stellen Sie jetzt den korrekten Datentyp für die Spalte Zahlbetrag ein, in dem Sie per Rechtsklick auf den Spaltentitel Typ ändern > Dezimalzahl wählen.

Die offenen Rechnungsbeträge berechnen und ausgeben

1. Markieren Sie nacheinander mit gedrückter Strg-Taste die Spalten Re-Betrag und Zahlbetrag.Wichtig: Nur bei dieser Klickreihenfolge werden im nun folgenden Schritt die Zahlbeträge von den Rechnungsbeträgen abgezogen.
2. Klicken Sie auf Spalte hinzufügen > Aus Zahl > Standard > Subtrahieren.
   

   Den offenen Betrag ermitteln

3. Benennen Sie die neue Spalte per Doppelklick auf den Spaltenkopf in offen um.
4. Um alle vollständig bezahlten Rechnungen aus der Liste zu entfernen, klicken Sie auf den Dropdownpfeil der Spalte offen und entfernen Sie das Häkchen bei 0.
   

   Alle vollständig bezahlten Rechnungen ausblenden

5. Benennen Sie die Abfrage in den Abfrageeinstellungen in Offene_Posten um.
6. Klicken Sie auf Datei > Schließen & laden. Sie erhalten ein neues Arbeitsblatt mit einer »intelligenten« Tabelle die den Namen Offene_Posten trägt. Benennen Sie auch das Arbeitsblatt um, z. B. in Offene Posten.
7. Fügen Sie der »intelligenten« Tabelle mit der Tastenkombination Strg+Umschalt+T eine Ergebniszeile hinzu. Die Werte der Spalte offen werden automatisch summiert.
   
8. Dank der Abfrage mit Power Query ist Ihre Offene-Posten-Liste ab sofort mit nur einem einzigen Mausklick up-to-date. Wählen Sie dazu nach dem Erfassen neuer Rechnungen oder Zahlungen per Rechtsklick auf eine Zelle in Ihrer Liste Aktualisieren. Sie erhalten sofort eine aktuelle Offene-Posten-Liste.