Andreas Unkelbach
Werbung


Freitag, 29. November 2013
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: Beide Probleme werden anhand des beschriebenen Berichtes erläutert.



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"
möglich.

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
. Der erste Gedanke wäre sicherlich hier eine Datenbanklösung zu basteln und entsprechende Abfragen miteinander zu verknüpfen.

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.

Namensmanager bezieht sich auf Bereich verschieben

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:

=Bereich.Verschieben($A$1;;;Anzahl2($A:$A);Anzahl2($1:$1))

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.
Pivottabelle erstellen - Bereich festlegen
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.
Espresso Tutorial - die digitale SAP Bibliothek

Tags: Excel

Ein Kommentar - - Office

Artikel datenschutzfreundlich teilen

🌎 Facebook 🌎 Twitter 🌎 Google
Diesen und weitere Texte von finden Sie auf http://www.andreas-unkelbach.de


Kommentare

Anonym am 2.12.2014 um 12:23 Uhr
Habe ich dringend gesucht!


Auch kommentieren?


Beim Versenden eines Kommentars wird mir ihre IP mitgeteilt. Diese wird jedoch nicht dauerhaft gespeichert (siehe auch XII. Fremde Nutzung / Kommentarfunktion in der Datenschutzerklärung).

Eine Rückmeldung ist entweder per Schnellkommentar oder (weiter unten) als normalen Kommentar möglich. Eine persönliche Rückmeldung (gerne auch Fragen zum Thema) würde mich sehr freuen.

Schnellkommentar (Kurzes Feedback, ausführliche Kommentare bitte unten als normaler Kommentar)

Name (sofern kein Name angegeben wird erscheint Anonym):


Normaler Kommentar

Name:

E-Mail (wird nicht veröffentlicht):

Homepage:

Kommentar:


Hinauf




Werbung



Logo Andreas-Unkelbach.de
© 2004 - 2018 Andreas Unkelbach
Gießener Straße 75,35396 Gießen,Germany
andreas.unkelbach@posteo.de
Andreas Unkelbach

Stichwortverzeichnis
(Tagcloud)


Aktuelle Infos (Abo)

Facebook Twitter Google+

»Schnelleinstieg ins SAP Controlling (CO)« und »Berichtswesen im SAP ® ERP Controlling«
Privates

Kaffeekasse 📖 Wunschliste