Freitag, 29. November 2013
17:27 Uhr
17:27 Uhr
SVERWEIS ohne NV und dynamische Größen für Datenbereiche
Im Rahmen eines Rechercheberichtes soll an mehreren Standorten eine vorgegebene Bilanzversion ausgewertet werden und die einzelnen Bilanzpositionen dann in einer einheitlichen Form in Excel dargestellt werden. Im Artikel Rechercheberichte im Modul FI (Bilanzanalyse) wurde schon beschrieben, wie eine Bilanzversion in SAP ausgewertet werden kann.
Der entsprechende Recherchebericht kann dann nach Excel exportiert werden und hier als Datengrundlage verwendet werden.
Jedoch sind hier, bedingt durch die Darstellung der Daten zwei Probleme zu beachten:
Ausgangslage
Problematisch ist, dass in einen Recherchebericht nur Merkmale (Bilanzpositionen) ausgegeben werden, die auch gebucht worden sind.
Um hier einen einheitlichen Namen auszugeben besteht die Möglichkeit die Darstellung der einzelnen Bilanzpositionen von der Darstellung des Namens auch auf die Schlüsselbezeichnung umzustellen.
Dieses ist im ausgeführten Recherchebericht unter
Dieser Recherchebericht kann dann nach Excel exportiert werden:
Hier werden dann die Kopfdaten ausgegeben und die einzelnen Bilanzpositionen untereinander. Hierbei werden dann alle GuV-Knoten mit ausgegeben.
Sieht der Bericht zum Beispiel einen Knoten 921 vor, der aber am entsprechenden Standort nicht gebucht wurde, so werden hier in der Grundtabelle auch keine Werte ausgegeben.
Weitere Beispiele wären:
Sofern sich die Struktur der Daten jedoch nicht weiter ändert und die Einstiegshürde relativ gering gehalten werden soll ist hier auch Excel eine gute Lösung.
Die Funktion SVERWEIS ohne #NV ausgeben
Grundsätzlich ist der Aufbau der Formel SVERWEIS wie folgt:
=SVERWEIS(Suchkriterium;Matrix;Spaltenindex;Bereich Verweis(WAHR ungefähre Übereinstimmung FALSCH genaue Übereinstimmung)
Wird nun in Excel mit der Formel SVERWEIS gearbeitet, so wird hier als Ergebnis #NV ausgegeben, sofern das Suchkriterium nicht innerhalb der Matrix vorhanden ist.
Um dieses zu vermeiden besteht die Möglichkeit über eine Wenn-Dann Funktion die SVERWEIS Ausgabe zu überprüfen. Hier könnte ab Excel 2007 die Funktion WENNFEHLER angewandt werden. Diese gibt nur einen Wert einer Funktion aus, sofern es keine Fehlermeldung gibt.
Beispiel:
=WENNFEHLER(FORMEL;Wert_falls_Fehler)
Sofern auch frühere Excel Versionen genutzt werden, sollte eine Wenn-Dann Funktion in Kombination mit der Formel ISTNV genutzt werden.
Nehmen wir an, dass die technische GuV Position im Beispiel SKR04/400 lautet und die Werte innerhalb einer Matrix der wir den Namensraum Grunddaten gegeben haben liegen, dann würde die Formel:
=WENN(ISTNV(SVERWEIS("*SKR04/400*";Grunddaten;$B$7;FALSCH));0;SVERWEIS("*SKR04/400*";Grunddaten;$B$7;FALSCH))
Wobei hier der auszugebende Wert in der Spalte der Matrix definiert ist, der in der Zelle B7 angegeben ist. Sofern im genutzten Recherchebericht die Ist-Werte in der 2. Spalte (und 2 in der Zelle B7 eingetragen ist) liegen wird entweder der gefundenen Wert in der 2, Spalte der Matrix ausgegeben, oder eine 0 ausgeben.
Allgemein ausgedrückt bieten sich hier also folgende Varianten an:
Die Funktion BEREICH.VERSCHIEBEN - Datenbereich dynamisch von der Größe anlegen
Im Beispiel wird die Namensbezeichnung Grunddaten verwendet. Dieses kann sinnvoll sein, wenn bei den eingespielten Grunddaten (im Beispiel eine GuV) nicht klar ist, wie viel Zeilen und Spalten der Datenbereich hat. Je nach genutzten Recherchebericht können hier unterschiedliche Spalten und natürlich auch Zellen vorhanden sein.
Daher kann es hilfreich sein, diesen Bereich über eine dynamische Formel zuzuweisen.
Unter der Annahme, dass der Recherchebericht in das Tabellenblatt Grunddaten kopiert wird, kann über den Namensmanager per Formel ein dynamischer Bereich definiert werden.
Der Namensmanager findet sich unter Excel 2003 war unter Einfügen, Namen, Definieren möglich. Ab Excel 2007 ist diese Funktion innerhalb des Ribbon Formel auf der Schaltfläche Namen definieren zu finden.
Hier hat der Name Grunddaten folgende Formel als Wert hinterlegt:
=Bereich.Verschieben($A$1;;;Anzahl2($A:$A);Anzahl2($5:$5))
Durch die Formel Bereich Verschieben wird ab der ZELLE1 der Bezug auf die Höhe als Anzahl der Zeilen in der Spalte A und auf Breite als Anzahl der Spalten in der Zeile 5 gezählt.
Dieses ist erforderlich, da im Beispiel erst ab Zeile 5 die Überschriften des Rechercheberichtes erscheinen und in den Spalten 1 bis 4 noch die Kopfdaten des Rechercheberichtes angegeben werden.
Durch die Formel Anzahl2 wird die Anzahl der "nicht leerer Zellen" eines Bereiches festgelegt. Im Beispiel erfolgt die Zählung ab Zeile 5.
Die Funktion BEREICH.VERSCHIEBEN
Zur Erklärung lautet der eigentliche Syntax der Formel BEREICH.VERSCHIEBEN wie folgt
BEREICH.VERSCHIEBEN(Bezug; Zeilen; Spalten; [Höhe]; [Breite])
Bezug ist hierbei ab welche Position der Bereich verschoben werden soll.
Zeilen wäre um welche Anzahl von Zeilen der Bereich verschoben werden soll, da aber alle Daten erfasst werden sollen, ist dieser im Beispiel durch ;; auf 0 gesetzt.
Spalten wäre um welche Anzahl von Spalten der Bereich verschoben werden soll, da aber alle Daten erfasst werden sollen, ist dieser im Beispiel durch ;; auf 0 gesetzt.
Höhe beschreibt den Bezug in Zeilen des neuen Bereiches (daher wurden hier die Anzahl der nicht leeren Zellen gezählt um den gesamten Bericht als Bezug zu erhalten)
Breite beschreibt den Bezug in Spalten des neuen Bereiches (daher wurden hier die Anzahl der nicht leeren Zellen gezählt um den gesamten Bericht als Bezug zu erhalten)
Sowohl Höhe als auch Breite sind in dieser Formel optional erfüllen jedoch exakt den Zweck, den die Zuweisung des Bereiches für die Grunddaten nützlich macht.
FAZIT
Gerade der dynamische Größenbereich für Datenbereiche ist nicht nur für dieses Beispiel geeignet sondern auch sehr nützlich um den Datenbereich einer Pivot-Tabelle zu definieren.
Hier bietet sich aber oftmals folgende Formelzuweisung als Datengrundlage einer Pivot-Tabelle an:
Durch die Zuweisung des Bereichs Grunddaten als Datengrundlage wird dieser Bereich auch entsprechend aktualisiert, wenn in der entsprechenden Tabelle weitere Datenzeilen hinzugefügt werden.
Hinweis: Aktuelle Buchempfehlungen besonders SAP Fachbücher sind unter Buchempfehlungen inklusive ausführlicher Rezenssionen und Bestellmöglichkeit zu finden.
Der entsprechende Recherchebericht kann dann nach Excel exportiert werden und hier als Datengrundlage verwendet werden.
Jedoch sind hier, bedingt durch die Darstellung der Daten zwei Probleme zu beachten:
- Die Funktion SVERWEIS ohne #NV ausgeben
- Die Funktion BEREICH.VERSCHIEBEN - Datenbereich dynamisch von der Größe anlegen
Ausgangslage
Problematisch ist, dass in einen Recherchebericht nur Merkmale (Bilanzpositionen) ausgegeben werden, die auch gebucht worden sind.
Um hier einen einheitlichen Namen auszugeben besteht die Möglichkeit die Darstellung der einzelnen Bilanzpositionen von der Darstellung des Namens auch auf die Schlüsselbezeichnung umzustellen.
Dieses ist im ausgeführten Recherchebericht unter
- Einstellungen
- Merkmalsdarstellung
- Durch den Punkt "Schlüssel und Bezeichnung"
Dieser Recherchebericht kann dann nach Excel exportiert werden:
Hier werden dann die Kopfdaten ausgegeben und die einzelnen Bilanzpositionen untereinander. Hierbei werden dann alle GuV-Knoten mit ausgegeben.
Sieht der Bericht zum Beispiel einen Knoten 921 vor, der aber am entsprechenden Standort nicht gebucht wurde, so werden hier in der Grundtabelle auch keine Werte ausgegeben.
Weitere Beispiele wären:
- Liste mit Plandaten, die nicht an jeder Kostenstelle auftreten
- Korrekturwerte für einzelne Bereiche
- Budgetwerte die noch nicht im System eingebucht worden sind
Sofern sich die Struktur der Daten jedoch nicht weiter ändert und die Einstiegshürde relativ gering gehalten werden soll ist hier auch Excel eine gute Lösung.
Die Funktion SVERWEIS ohne #NV ausgeben
Grundsätzlich ist der Aufbau der Formel SVERWEIS wie folgt:
=SVERWEIS(Suchkriterium;Matrix;Spaltenindex;Bereich Verweis(WAHR ungefähre Übereinstimmung FALSCH genaue Übereinstimmung)
Wird nun in Excel mit der Formel SVERWEIS gearbeitet, so wird hier als Ergebnis #NV ausgegeben, sofern das Suchkriterium nicht innerhalb der Matrix vorhanden ist.
Um dieses zu vermeiden besteht die Möglichkeit über eine Wenn-Dann Funktion die SVERWEIS Ausgabe zu überprüfen. Hier könnte ab Excel 2007 die Funktion WENNFEHLER angewandt werden. Diese gibt nur einen Wert einer Funktion aus, sofern es keine Fehlermeldung gibt.
Beispiel:
=WENNFEHLER(FORMEL;Wert_falls_Fehler)
Sofern auch frühere Excel Versionen genutzt werden, sollte eine Wenn-Dann Funktion in Kombination mit der Formel ISTNV genutzt werden.
Nehmen wir an, dass die technische GuV Position im Beispiel SKR04/400 lautet und die Werte innerhalb einer Matrix der wir den Namensraum Grunddaten gegeben haben liegen, dann würde die Formel:
=WENN(ISTNV(SVERWEIS("*SKR04/400*";Grunddaten;$B$7;FALSCH));0;SVERWEIS("*SKR04/400*";Grunddaten;$B$7;FALSCH))
Wobei hier der auszugebende Wert in der Spalte der Matrix definiert ist, der in der Zelle B7 angegeben ist. Sofern im genutzten Recherchebericht die Ist-Werte in der 2. Spalte (und 2 in der Zelle B7 eingetragen ist) liegen wird entweder der gefundenen Wert in der 2, Spalte der Matrix ausgegeben, oder eine 0 ausgeben.
Allgemein ausgedrückt bieten sich hier also folgende Varianten an:
=WENNFEHLER (SVERWEIS(Kriterium;Matrix;Spaltenindex;falsch))
oder über die Hilfsfunktion ISTNV
=WENN(ISTNV (SVERWEIS(Kriterium;Matrix;Spaltenindex;falsch);""; SVERWEIS(Kriterium;Matrix;Spaltenindex;falsch))
Die Funktion BEREICH.VERSCHIEBEN - Datenbereich dynamisch von der Größe anlegen
Im Beispiel wird die Namensbezeichnung Grunddaten verwendet. Dieses kann sinnvoll sein, wenn bei den eingespielten Grunddaten (im Beispiel eine GuV) nicht klar ist, wie viel Zeilen und Spalten der Datenbereich hat. Je nach genutzten Recherchebericht können hier unterschiedliche Spalten und natürlich auch Zellen vorhanden sein.
Daher kann es hilfreich sein, diesen Bereich über eine dynamische Formel zuzuweisen.
Unter der Annahme, dass der Recherchebericht in das Tabellenblatt Grunddaten kopiert wird, kann über den Namensmanager per Formel ein dynamischer Bereich definiert werden.
Der Namensmanager findet sich unter Excel 2003 war unter Einfügen, Namen, Definieren möglich. Ab Excel 2007 ist diese Funktion innerhalb des Ribbon Formel auf der Schaltfläche Namen definieren zu finden.
Hier hat der Name Grunddaten folgende Formel als Wert hinterlegt:
=Bereich.Verschieben($A$1;;;Anzahl2($A:$A);Anzahl2($5:$5))
Durch die Formel Bereich Verschieben wird ab der ZELLE1 der Bezug auf die Höhe als Anzahl der Zeilen in der Spalte A und auf Breite als Anzahl der Spalten in der Zeile 5 gezählt.
Dieses ist erforderlich, da im Beispiel erst ab Zeile 5 die Überschriften des Rechercheberichtes erscheinen und in den Spalten 1 bis 4 noch die Kopfdaten des Rechercheberichtes angegeben werden.
Durch die Formel Anzahl2 wird die Anzahl der "nicht leerer Zellen" eines Bereiches festgelegt. Im Beispiel erfolgt die Zählung ab Zeile 5.
Die Funktion BEREICH.VERSCHIEBEN
Zur Erklärung lautet der eigentliche Syntax der Formel BEREICH.VERSCHIEBEN wie folgt
BEREICH.VERSCHIEBEN(Bezug; Zeilen; Spalten; [Höhe]; [Breite])
Bezug ist hierbei ab welche Position der Bereich verschoben werden soll.
Zeilen wäre um welche Anzahl von Zeilen der Bereich verschoben werden soll, da aber alle Daten erfasst werden sollen, ist dieser im Beispiel durch ;; auf 0 gesetzt.
Spalten wäre um welche Anzahl von Spalten der Bereich verschoben werden soll, da aber alle Daten erfasst werden sollen, ist dieser im Beispiel durch ;; auf 0 gesetzt.
Höhe beschreibt den Bezug in Zeilen des neuen Bereiches (daher wurden hier die Anzahl der nicht leeren Zellen gezählt um den gesamten Bericht als Bezug zu erhalten)
Breite beschreibt den Bezug in Spalten des neuen Bereiches (daher wurden hier die Anzahl der nicht leeren Zellen gezählt um den gesamten Bericht als Bezug zu erhalten)
Sowohl Höhe als auch Breite sind in dieser Formel optional erfüllen jedoch exakt den Zweck, den die Zuweisung des Bereiches für die Grunddaten nützlich macht.
FAZIT
Gerade der dynamische Größenbereich für Datenbereiche ist nicht nur für dieses Beispiel geeignet sondern auch sehr nützlich um den Datenbereich einer Pivot-Tabelle zu definieren.
Hier bietet sich aber oftmals folgende Formelzuweisung als Datengrundlage einer Pivot-Tabelle an:
Hier werden dann tatsächlich die Anzahl der Zeilen und die Anzahl der Spalten ab der Zelle A1 gezählt. Durch die Möglichkeit diese Formel ebenfalls per Namensmanager einen Namen zuzuweisen kann beim Einfügen einer Pivot-Tabelle über Ribbon Einfügen und hier auf Pivottabelle einfügen der entsprechende Name als Grundlage zugewiesen werden.=Bereich.Verschieben($A$1;;;Anzahl2($A:$A);Anzahl2($1:$1))
Durch die Zuweisung des Bereichs Grunddaten als Datengrundlage wird dieser Bereich auch entsprechend aktualisiert, wenn in der entsprechenden Tabelle weitere Datenzeilen hinzugefügt werden.
ein Angebot von Espresso Tutorials
unkelbach.link/et.books/
unkelbach.link/et.reportpainter/
unkelbach.link/et.migrationscockpit/
Diesen Artikel zitieren:
Unkelbach, Andreas: »SVERWEIS ohne NV und dynamische Größen für Datenbereiche« in Andreas Unkelbach Blog (ISSN: 2701-6242) vom 29.11.2013, Online-Publikation: https://www.andreas-unkelbach.de/blog/?go=show&id=487 (Abgerufen am 19.5.2024)
Ein Kommentar - Permalink - Office
Artikel datenschutzfreundlich teilen
🌎 Facebook 🌎 Twitter 🌎 LinkedIn