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:

• Die Funktion SVERWEIS ohne #NV ausgeben
• Die Funktion BEREICH.VERSCHIEBEN - Datenbereich dynamisch von der Größe anlegen

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.



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.

Durch die Zuweisung des Bereichs Grunddaten als Datengrundlage wird dieser Bereich auch entsprechend aktualisiert, wenn in der entsprechenden Tabelle weitere Datenzeilen hinzugefügt werden.
 

Ausgangslage

Beim Aufruf einer Excelmappe kommt es ab Excel 2007/2010 zur Fehlermeldung "Die automatische Aktualisierung von Hyperlinks wurde deaktiviert". Hintergrund ist, dass Excel hier nicht mehr aktiv Werte aktualisiert sondern innerhalb der Excelmappe mit vertrauenswürdigen Quellen arbeitet. Innerhalb der Fehlermeldung besteht auch die Möglichkeit innerhalb der Vertrauenseinstellungen für externen Inhalt eine entsprechende Aktualisierung zuzulassen.

Dieses ist sicherlich sinnvoll bei einzelnen Arbeitsmappen. Sofern in einer Arbeitsmappe jedoch mit unterschiedlichen Quellen gearbeitet wurde und eine finale Version erstellt werden soll in der alle abgestimmten Daten enthalten sind, besteht die Notwendigkeit diese Verweise zu identifizieren und entsprechend zu ändern.


Lösung

Manchmal ist die Lösung für solche Probleme relativ einfach. Ein Verweis auf eine andere Exceltabelle als Quelle wird innerhalb Formel mit eckigen Klammern [] ausgedrückt.

Um nun solche Verweise zu finden, besteht die Möglichkeit in der gesamten Arbeitsmappe nach "[" in Formeln zu suchen. Hierzu sollten die Optionen in der Funktion "Suchen" in folgenden Punkten geändert werden:

Durchsuchen: Arbeitsmappe
Suchen in : Formeln

und nun kann nach [ gesucht werden.

In Excel 2003 ist der Suchdialog unter Bearbeiten->Suchen und in Excel 2007/2010 auf der Registerkarte Start in der Gruppe Bearbeiten durch "Suchen und Auswählen" zu finden.

Alternativ ist dieses auch über die Tastenkombination STRG + F möglich.

Nachtrag:
Es sollte tatsächlich nur nach [ in Formeln gesucht werden und nicht nach =[ da sonst Verknüpfungen nicht gefunden werden.Das = erübrigt sich durch die Option "Suchen in: Formeln".

Wie unter Winword: Weitere Ebene für Überschriften beim automatischen Inhaltsverzeichnis beschrieben besteht in Winword die Möglichkeit Überschriften als Überschriften unterschiedlicher Ebenen zu definieren. Allerdings ist in den Formatvorlagen von Winword 2007/2010 nur "Überschrift 1" und "Überschrift 2" verfügbar.


Innerhalb OpenOffice besteht die Möglichkeit auf der Schaltfläche Formatvorlagen über "weitere" auch andere Formatvorlagen auszuwählen.

Dieses ist auch unter Winword möglich, wenn auch etwas versteckter.

Dieses ist entweder durch die Gliederungsansciht und das Ändern der Ebenen von Überschriften möglich oder alternativ durch Anzeigen aller Formatvorlagen in den "Optionen für Formatvorlagenbereich"


Hierzu werden die Formatvorlagen durch den kleinen Pfeil im Bereich "Formatvorlagen" aufgerufen (alternativ ist dieses auch per ALT+STRG+UMSCHALT+S möglich). Im Screenshot ist dieses als roter Kasten markiert.


Durch einen Klick auf Optionen kann nun unter Anzuzeigende Formatvorlagen "Alle" ausgewählt werden, so dass hier dann bis zur Überschrift 10 alles an Überschriften verwendet werden kann.

Ausgangslage
Bei umfangreichen Tabellen (bspw. mehrere Tabellenblätter innerhalb einer Excelmappe) ist es oft sinnvoll im ersten Blatt eine Übersicht mit Definitionen oder ein Inhaltsverzeichnis anzulegen. Sinnvollerweise kann hier über die Funktion HYPERLINK auch auf die dahinter liegenden Excelmappen verwiesen werden. Dieses kann aber bei mehreren Tabellenblättern recht mühsam sein entsprechend diese anzulegen.



Im Beispiel gibt es ein Tabellenblatt für die Gesamtsicht und eines für einzelne Abteilungen, Fachbereiche und Verwaltung.

Lösung:
Durch einen Beitrag im VBA Entwickler-Treff von XING bin ich auf den Beitrag "Inhaltsverzeichnis für Arbeitsmappe automatisch erstellen" gestossen und habe dieses Makro für mich wie folgt angepasst.

---

Sub Inhaltsverzeichnis()
Dim intTab As Integer
Dim tbl As Worksheet
Dim intZeile As Integer

Set tbl = Worksheets.Add(before:=Worksheets(1))
intZeile = 2

' eingefügtes Blatt "Inhaltsverzeichnis" nennen
Worksheets(1).Name = "Inhaltsverzeichnis"
ActiveSheet.Name = Worksheets(1).Name
Cells(1, 1).Value = "Enthaltene Blätter"

For intTab = 2 To ActiveWorkbook.Worksheets.Count
tbl.Cells(intZeile, 1).Value = Worksheets(intTab).Name

tbl.Cells(intZeile, 1).Hyperlinks.Add _
Anchor:=tbl.Cells(intZeile, 1), Address:="", SubAddress:= _
"'" & Worksheets(intTab).Name & "'!A1", _
ScreenTip:="Klicken Sie um zur Tabelle zu gelangen", _
TextToDisplay:=Worksheets(intTab).Name


intZeile = intZeile + 1
Next intTab

' Spaltenbreite fixieren
Worksheets(1).Cells.EntireColumn.AutoFit

End Sub

---

In dieser Variante wird ein Blatt mit der Bezeichnung Inhaltsverzeichnis angelegt und mit entsprechenden Hyperlinks ergänzt. Dieses Beispiel ist im weiteren auch dokumentiert.

Angepasste Version:
Hier wird als Überschrift die Zelle A1 jeden Blattes übernommen und das erste Tabellenblatt ohne Namenszuweisung angelegt. Hierdurch kommt es zu keinen Fehler, solle ein Blatt mit der Bezeichnung Inhaltsverzeichnis schon vorhanden sein.

---

Sub Inhalt_mit_Überschrift()
Dim intTab As Integer
Dim tbl As Worksheet
Dim intZeile As Integer

Set tbl = Worksheets.Add(before:=Worksheets(1))
intZeile = 2


' Zellenüberschriften

ActiveSheet.Name = Worksheets(1).Name
Cells(1, 1).Value = "Überschrift"
Cells(1, 2).Value = "Link"
Cells(1, 1).Font.Bold = True
Cells(1, 2).Font.Bold = True


For intTab = 2 To ActiveWorkbook.Worksheets.Count

' Ausgabe der Zelle A1 eines jeden Arbeitsblattes als Überschrift


tbl.Cells(intZeile, 1).Value = "='" & Worksheets(intTab).Name & "'!a1"

'Übernahme der Registerblattfarbe als Schriftfarbe


tbl.Cells(intZeile, 1).Font.Color = Worksheets(intTab).Tab.Color


'Setzen eines Hyperlinks auf Tabellenblatt

tbl.Cells(intZeile, 2).Value = Worksheets(intTab).Name

tbl.Cells(intZeile, 2).Hyperlinks.Add _
Anchor:=tbl.Cells(intZeile, 2), Address:="", SubAddress:= _
"'" & Worksheets(intTab).Name & "'!A1", _
ScreenTip:="Klicken Sie um zur Tabelle zu gelangen", _
TextToDisplay:=Worksheets(intTab).Name


intZeile = intZeile + 1
Next intTab


Worksheets(1).Cells.EntireColumn.AutoFit

End Sub

---

Durch dieses Makro wird die vorhandene Tabelle um ein weiteres Blatt erweitert und in diesen werden alle vorhandenen Blätter mit einen entsprechenden Link aufgelistet.

Zur Erstellung von Makros ist bei Excel 2007 erst einmal die Registerkarte "Entwickleroptionen" unter den Punkt "Die am häufigsten verwendeten Optionen bei der Arbeit mit Excel" unter der Kategorie "Häufig verwende" zu aktivieren.

Nun findet sich der Makroeditor in der Symbolleiste "Entwicklertools" und kann hier direkt über Makros aufgerufen werden. In Excel 2010 ist dieser in der Symbolleiste Ansicht zu finden.

Hier kann nun im Feld Makroname ein Name bspw. Inhaltsverzeichnis eingetragen werden und durch den Klick auf "Erstellen" ein Makro angelegt werden.

Dabei wird durch sub Name und endsub() das entsprechende Makro angelegt. Hier kann das oben angegebene Makro eingefügt werden.



Nun kann das VisualBasic Fenster geschlossen werden und zur eigentlichen Tabelle zurückgekehrt werden.

Das Makro ist nun direkt gespeichert und kann über den Makroeditor wiederum durch "Ausführen" gestartet werden.



Hierbei hat das Makro unabhängig vom gewählten Makronamen die im Code mit sub Name() angegebene Bezeichnung. Im Beispiel also Inhaltsverzeichnis.

Im Ergebnis sieht das erste Tabellenblatt nun wie folgt aus:


Durch einen Klick auf einen entsprechenden Link kann auch direkt auf das Exceltabellenblatt gewechselt werden.

Der Vorteil dieses Makro ist, dass es unabhängig von bereits vorhandenen Tabellenblättern angelegt werden kann und in der Reihenfolge der Tabellenblätter eine Übersicht über die einzelnen Tabellenblätter erstellt.

Es ist sinnvoll dieses Makro in Verbindung mit den im Artikel Office Integration - Excelansicht in SAP und Daten kopieren nach Excel vorgestellten Makros zur Übernahme von Daten aus Buchhaltungssystemen in eine persönliche Makroarbeitsmappe (siehe Artikel Excel Umgang mit Makros und Visual Basic for Applications (VBA) zu übernehmen.

Zusammenfassung (Zwei Makros mit A1 oder A2 als Überschrift)

Sub Inhalt_mit_Überschrift_aus_a1()
'
' Inhalt_mit_Überschrift_aus_a1 Makro
' Erstellt ein Inhaltsverzeichnis aller Tabellenblaetter und verwendet als Überschrift die Zelle A1 eines jeden Tabellenblattes
'

'
Dim intTab As Integer
Dim tbl As Worksheet
Dim intZeile As Integer
Set tbl = Worksheets.Add(before:=Worksheets(1))
intZeile = 2
ActiveSheet.Name = Worksheets(1).Name
Cells(1, 1).Value = "Überschrift"
Cells(1, 2).Value = "Link"
Cells(1, 1).Font.Bold = True
Cells(1, 2).Font.Bold = True
For intTab = 2 To ActiveWorkbook.Worksheets.Count
tbl.Cells(intZeile, 1).Value = "='" & Worksheets(intTab).Name & "'!a1"
tbl.Cells(intZeile, 1).Font.Color = Worksheets(intTab).Tab.Color
tbl.Cells(intZeile, 2).Value = Worksheets(intTab).Name
tbl.Cells(intZeile, 2).Hyperlinks.Add _
Anchor:=tbl.Cells(intZeile, 2), Address:="", SubAddress:= _
"'" & Worksheets(intTab).Name & "'!A1", _
ScreenTip:="Klicken Sie um zur Tabelle zu gelangen", _
TextToDisplay:=Worksheets(intTab).Name
intZeile = intZeile + 1
Next intTab
Worksheets(1).Cells.EntireColumn.AutoFit
End Sub

Sub Inhalt_mit_Überschrift_aus_a2()
'
' Inhalt_mit_Überschrift_aus_a2 Makro
' Erstellt ein Inhaltsverzeichnis aller Tabellenblaetter und verwendet als Überschrift die Zelle A2 eines jeden Tabellenblattes
'

'
Dim intTab As Integer
Dim tbl As Worksheet
Dim intZeile As Integer
Set tbl = Worksheets.Add(before:=Worksheets(1))
intZeile = 2
ActiveSheet.Name = Worksheets(1).Name
Cells(1, 1).Value = "Überschrift"
Cells(1, 2).Value = "Link"
Cells(1, 1).Font.Bold = True
Cells(1, 2).Font.Bold = True
For intTab = 2 To ActiveWorkbook.Worksheets.Count
tbl.Cells(intZeile, 1).Value = "='" & Worksheets(intTab).Name & "'!a2"
tbl.Cells(intZeile, 1).Font.Color = Worksheets(intTab).Tab.Color
tbl.Cells(intZeile, 2).Value = Worksheets(intTab).Name
tbl.Cells(intZeile, 2).Hyperlinks.Add _
Anchor:=tbl.Cells(intZeile, 2), Address:="", SubAddress:= _
"'" & Worksheets(intTab).Name & "'!A2", _
ScreenTip:="Klicken Sie um zur Tabelle zu gelangen", _
TextToDisplay:=Worksheets(intTab).Name
intZeile = intZeile + 1
Next intTab
Worksheets(1).Cells.EntireColumn.AutoFit
End Sub

Adobe Acrobat Distiller nutzt zur Erzeugung von PDF aus Excel ein Extra Ribbon von dem aus die Druckfunktion (bzw. PDF Erstellungsfunktion) aufgerufen werden kann.Dieses wird durch ein Excel Add-In verwirklicht.

Nun kann es passieren, dass diese Symbolleiste ausgeblendet wird.

Innerhalb der Excel-Optionen befindet sich in der Kategorie Add-Ins beim Punkt "Verwalten:" die Auswahlfunktion "Deaktivierte Elemente" (Drop-Down Liste am Ende). Durch "Gehe zu" können hier einmal deaktivierte Elemente wieder aktiviert werden.