Powerquery in excel: Erklärt mit Praxisbeispielen

PowerQuery EÜR-Vorlage / Buchungsvorgänge automatisiert zuordnen

In diesem Beispiel aus deiner Praxis, zeige ich, wie man automatisiert Buchungsvorgänge zuordnen kann und sich damit jede Menge Zeit beim Erstellen der EÜR spart.

Genauer gesagt ist es ein Beispiel aus meiner Praxis als Kassier (oder Schatzmeister, je nachdem wie ihr es bezeichnen wollt 😉) eines gemeinnützigen Vereins.

So erledige ich meine EÜR mit PowerQuery.

Grundsätzlicher Aufbau der Daten

Wir benötigen 2 Datensätze dafür, oder einfacher formuliert:

Die Daten kommen von zwei verschiedenen Orten.

Zum einen ziehe ich mir die Buchungsvorgänge aus meinem Online-Banking als CSV-Datei herunter. Diese Option bietet meines Wissens jede Bank an.

Und der zweite Ort ist eine Tabelle, die wir uns erstellt haben, um den einzelnen Begünstigten (die wir in der CSV Datei unseres Online-Bankings sehen) bestimmten Kategorien unserer EÜR zuordnen.

Zum Beispiel der Metzger ist immer eine Ausgabe und fällt immer unter die Kategorie „Wareneinkauf“ mit 7% Umsatzsteuer.

Daten in PowerQuery laden

Die CSV Datei laden wir mit der Funktion „Aus Text/CSV“ in den PowerQuery:

Mit PowerQuery eine Abfrage erstellen aus einer Text-Datei oder aus einer CSV-Datei

Und unsere Tabelle mit den Begünstigten und der Zuordnung laden wir mit der Funktion „Aus Tabelle / Bereich“ in den PowerQuery:

Mit PowerQuery eine Abfrage erstellen aus einer Tabelle oder Bereich aus Excel

Abfragen als neue Abfrage zusammenführen

Wir haben nun also 2 Abfragen in unserem PowerQuery erstellt.

Nun fügen wir diese beiden Abfragen zu einer neuen Abfrage zusammen. Dazu nutzen wir die Funktion „Abfragen als neue Abfrage zusammenführen“

PowerQuery Abfragen als neue Abfrage zusammenführen

Ich nutze gerne die Option „als neue Abfrage zusammenführen“ weil ich dadurch, wie der Name schon sagt, eine neue Abfrage erstelle. Die beiden Abfragen, aus welcher die neue Abfrage entsteht, bleiben dabei unverändert bestehen und das empfinde ich als ganz angenehm so.

Um Abfragen zusammenzuführen brauchen wir ein Element, welches in den beiden Abfragen vorhanden ist.

Das verbindende Element der beiden Abfragen ist der Begünstigte bzw. Zahlungsempfänger.

Beim Zusammenfügen gehen wir so vor, dass die CSV Datei die Basis ist.

Damit meine ich, dass wir alle Zeilen haben wollen, die in der CSV-Datei vorhanden ist. Daher steht diese oben in der Maske für das Zusammenführen.

Für alle diese Zeilen wollen wir prüfen, ob wir in der Eingruppierungstabelle eine Eingruppierung für den jeweiligen Zahlungsempfänger vorgenommen haben.

Sprich, wenn in der CSV-Datei in einer Zeile unser Metzger steht, wollen wir prüfen, ob der Metzger in der Eingruppierungstabelle steht und dann von dort die Informationen zur Eingruppierung und Umsatzsteuer-Satz holen.

Um das zu erreichen, verwenden wir den linken äußeren Join:

PowerQuery Abfragen zusammenführen mit der Option Linker äußerer Join

Dieser beschreibt genau das, was wir haben wollen.

Linker äußerer Join:

Alle aus erster (also unsere CSV-Datei), übereinstimmende aus zweiter (also unsere Eingruppierungstabelle).


Wir erhalten nun eine neue Spalte ganz rechts außen: „Eingruppierung“.

Hier können wir einmal das Symbol mit den beiden Pfeilen klicken und dann in dem aufgehenden Fenster, auswählen, welche Spalten wir aus der Eingruppierungstabelle verwenden wollen.

„Ursprüngliche Spaltennamen als Präfix verwenden“

Das beschreibt, ob der Spaltenname mit dem Namen der Tabelle beginnen soll.

Also z.B. ob die Spalte „Einnahme/Ausgabe“ dann heißen soll:

„Eingruppierung_Einnahme / Ausgabe“ (dann wäre der Haken gesetzt) oder es reicht, wenn die Spalte nur „Einnahme / Ausgabe“ heißt (dann ist der Haken nicht gesetzt).

PowerQuery Ursprünglichen Spaltennamen als Präfix verwenden

Werte ersetzen

Werte ersetzen ist eine recht einfach Option in PowerQuery, um das zu tun, was der Name schon sagt, einen Wert mit einem anderen Wert zu ersetzen.

Dazu markiert man die Spalte oder die Spalten, in denen man die Werte ersetzen will.

Dann gibt man den Wert ein, der ersetzt werden soll. Und man gibt den Wert ein, durch den er ersetzt werden soll. Hier soll der Wert „null“ durch nichts ersetzt werden, also eine leere Zelle erzeugen:

PowerQuery Werte ersetzen durch Leer

Spalten auswählen

Auch Spalten auswählen ist kein Hexenwerk 😉

Man klickt auf die Funktion und hakt die Spalten an, die man haben will und lässt den Haken weg bei den Spalten, die man weglassen will.

Benutzerdefinierte Spalte Monat (Date.MonthName)

Jetzt kommen wir zum ersten mal dazu, eine Funktion zu verwenden.

Wir möchten aus dem Spalte „Buchungstag“ den Kalendermonat herauslesen lassen.

Dazu klicken wir auf „Spalte hinzufügen“ und anschließend auf „Benutzerdefinierte Spalte“

Wir verwenden die Funktion „Date.MonthName“ und wählen uns die Spalte „Buchungstag“ aus:

PowerQuery Date.MonthName Monat aus einem Datum herauslesen

PowerQuery erkennt dadurch in dem Datum aus dem Buchungstag den Kalendermonat.

Also z.B. aus dem 02.01.2024 wird dann „Januar“ herausgelesen.

Benutzerdefinierte Spalten Netto-Betrag und UST-Betrag

Nun geht es an Berechnungen.

Aus der CSV Datei aus unserem Online-Banking haben wir natürlich als Betrag den Brutto-Betrag erhalten, denn wir erhalten und überweisen ja immer Brutto-Beträge.

Daraus wollen wir nun die Umsatzsteuer errechnen und den Netto-Betrag.

Letztendlich ist das einfache Mathematik, die wir hier jetzt verwenden.

Auch hierfür klicken wir auf „Spalte hinzufügen“ und auf „Benutzerdefinierte Spalte“.

Aus dem Brutto-Betrag erhalten wir den Umsatzsteuerbetrag in dem wir folgendes rechnen:

Bruttobetrag / (100 + Umsatzsteuer-Satz in %) * Umsatzsteuer-Satz in %

Da wir aber nicht für jede Zeile einen Umsatzsteuer-Satz zuordnen konnten bauen wir darum noch eine Wenn-Funktion.

Nämlich wenn die Spalte „Umsatzsteuer-Satz“ leer ist, soll auch die Spalte Umsatzsteuer leer sein, wenn die Spalte „Umsatzsteuer-Satz“ aber gefüllt ist, dann soll die oben genannte Rechnung stattfinden.

Fertig sieht dann die Formel für unsere Benutzerdefinierte Spalte so aus:

PowerQuery Benutzerdefinierete Spalte if then Wenn-Funktion

Ähnlich gehen wir mit der Berechnung für den Netto-Betrag vor, nur brauchen wir dort keine Wenn-Funktion einbauen, sondern es reicht, wenn wir ganz banal „Brutto-Betrag“ – „Umsatzsteuer-Betrag“ rechnen.

Fazit

Der Aufbau dieses PowerQuery wirkt erst mal lang und aufwändig.

Mit Übung hast du das in vielleicht 30 Minuten oder auch schneller erledigt.

Diese Zeit wendest du einmal auf und zukünftig hast du dauerhaft eine wahnsinnige Zeitersparnis, wenn du deine EÜR erstellst und die einzelnen Buchungsvorgänge zuordnen müsstest.

Denn das macht dann zukünftig PowerQuery für dich auf Knopfdruck 😊


Und wenn du dir noch mehr Zeit sparen willst, dann lass mich für dich deine PowerQuery Abfrage erstellen:

2023 09 19 16 10 34 Powerquery in excel: Erklärt mit Praxisbeispielen PowerQuery

Ähnliche Beiträge

Wer schreibt hier?

Bernd Giehl, Excel-Profi.

In meinem Beruf als Demand Planner (zu Deutsch: Absatzplaner) beschäftige ich mich mit Zukunftsprognosen zur Absatzlage meines Arbeitgebers

Das heißt, ich versuche herauszufinden, wie viel wir in der Zukunft verkaufen werden 🕵️

Natürlich spielen hier 🔢 Zahlen, Daten, Fakten 🔢 eine große Rolle.

Und natürlich ist Excel ein großer Bestandteil meiner täglichen Arbeit. So arbeite ich gut und gerne > 30h pro Woche mit Excel. 🤓

Ich weiß, wenn man es mit all seiner Macht nutzt 💪, kann man mit Excel sehr viel Zeit sparen 

Dieses Wissen gebe ich in meinem Blog weiter, damit auch du Zeit und Nerven sparst bei deiner Arbeit mit Excel.

 

Nach oben scrollen
SENDINBLUE HTML HIER EINFÜGEN
Cookie Consent mit Real Cookie Banner