Verpassen Sie Ihrem Excel-Reporting mit der Formel sverweis einen Boost! Dank sverweis wird das Reporting dynamisch und der monatliche oder quartalsweise Nachtrag wird zum Kinderspiel.
Der Anwendungsfall ist wie folgt:
- Sie ziehen einen Report aus der Buchhaltung, den Sie in Excel weiterverarbeiten möchten.
- Im exportierten Report ist ein fester Bezug auf eine Zelle nicht möglich: einzelne Konti sind nicht in jeder Periode bebucht und werden im Report nicht ausgegeben, einzelne Artikel wurden nicht verkauft und in der Liste nicht enthalten, …
- Daher müssen Sie die aufdatierten Zahlen aus der Buchhaltung zur weiteren Auswertung einzeln in eine Excel Datei übertragen. Das ist zeitintensiv und fehleranfällig.
Dieses Problem lösen Sie mit der Formel sverweis: die Formel liest die korrekten Zahlen aus einem aus der Buchhaltung exportierten Tabellenblatt und fügt sie an der richtigen Stelle im Reporting ein.
1. Report aus der Buchhaltung kopieren
Kopieren Sie den Report pro Periode am besten in ein eigenes Tabellenblatt. Sie können pro Periode ein Tabellenblatt vorsehen.
2. Excel Report erstellen
In einer weiteren Tabelle Ihrer Arbeitsmappe entwerfen Sie den Excel-Report mit den Zeilen, die Sie darstellen möchten.
3. sverweis Formel in den Report einbauen
In der Spalte C soll der entsprechende Saldo zum vorne aufgelisteten Konto angezeigt werden.
Die Formel sverweis ist wie folgt aufgebaut:
SVERWEIS(SUCHKRITERIUM;MATRIX;SPALTENINDEX;[BEREICH_VERWEIS])
- SUCHKRITERIUM: Als Suchkriterium verwenden Sie eine Zelle im anzuzeigenden Report. Eine Kontonummer ist ein eindeutiger Schlüssel, weshalb wir in unserem Beispiel die Kontonummer als Suchkriterium verwenden. Damit weiss Excel, wonach im Import-Tab in der ersten Spalte der MATRIX gesucht werden muss.
- MATRIX: Tragen Sie dort den gesamten Bereich ein, in dem nach dem SUCHKRITERIUM und nach dem im Report darzustellenden Wert gesucht werden soll.
- SPALTENINDEX: Hier müssen Sie die Nummer der Spalte aus der Matrix eingeben, aus der der entsprechende Wert zurückgegeben werden soll. Im Beispiel ist dies «6», weil die Spalte F der 6. Spalte aus der MATRIX A1:F12 entspricht.
- BEREICH_VERWEIS: damit geben Sie an, ob mit der Funktion eine genaue oder ungefähre Entsprechung gesucht werden soll. Mit dem Wert 0 geben Sie an, dass Sie nach dem exakten Wert suchen wollen. Wird der Wert nicht gefunden, wird der Fehlerwert «#NV» ausgegeben.
Da der Bezug auf die Buchhaltungs-Daten immer in der selben Matrix steht, ergänzen Sie diese mit dem «$» Zeichen. So können Sie die Formel einfacher kopieren.
=SVERWEIS(A5;’Input Q2′!$A$1:$F$12;6;0)
4. Einbezug von istfehler
Im Beispiel oben wird auf der Zeile 9 der Fehlerwert «#NV» ausgegeben. Dies, weil im Export aus der Buchhaltung das Konto 1175 nicht existierte resp. in der betreffenden Periode nicht bebucht wurde. Damit solche Fehler nicht angezeigt werden, können Sie die «istfehler» ergänzend mit der «wenn» Funktion einsetzen und an Stelle des Fehlerwerts «#NV» eine 0 ausgeben:
=WENN(ISTFEHLER(SVERWEIS(A5;’Input Q2′!$A$1:$F$12;6;0));0;SVERWEIS(A5;’Input Q2′!$A$1:$F$12;6;0))
Beispiele
Unsere Datei mit dem Beispiel können Sie zur Anschauung hier herunterladen. In der Run my Accounts Community stellen wir Ihnen zahlreiche weitere Beispiel-Reports zur Verfügung: Vom Budget-Vergleich über die Liquiditätsplanung mit oder der Berechnung des Cash Flows. Diese Vorlagen passen perfekt bei der Anwendung der Online-Buchhaltungs-Software von Run my Accounts. Die Vorlagen dürften Ihnen aber auch bei der Verwendung von Abacus, Banana oder Sage nützlich sein. Die meisten dieser Anwendungen basieren auf der Formel sverweis und stehen Ihnen zur freien Verwendung zur Verfügung.
