Dieser Artikel stellt anhand eines Beispiels eine Einführung in das Thema VBA und Makros (inklusive der persönlichen Makroarbeitsmappe) in Excel dar. Eine Einführung zum Thema Makros und VBA in der Datenbankverwaltung Access ist im Artikel "Dynamisches Importieren von Daten aus Excel nach Access unter Abfrage Speicherpfad per Makro und VBA" beschrieben.

Ausgangslage
In einen Bericht werden einzelne Zeilen ausgegeben, sofern ein Datensatz vorhanden ist, ansonsten bleibt die Zeile in Excel leer. Dieses funktioniert über eine Auswertung die im Artikel SVERWEIS ohne NV und dynamische Größen für Datenbereiche beschrieben wurde.

Hier wird im Tabellenblatt "Endtabelle" eine Vorlage für eine CSV Datei erstellt, die später in ein anderes System eingebunden werden soll.

Das Ergebnis sieht dabei wie folgt aus:


Über die Wenn Funktion werden hier nur Zeilen ausgegeben, wenn entsprechende Daten auch vorhanden sind. Im Beispiel sind daher die Zeilen 5 und 9 ohne Wert bzw. leer.

Bei einer Einspielung in ein anderes System würde der Einspielvorgang bei einer Leerzeile abgebrochen werden. Daher müssten die Leerzeilen aufwändig gelöscht werden.

Hier bietet Excel jedoch die Möglichkeit über Makros diesen Vorgang zu automatisieren.

Makro aufzeichnen

Die Makrofunktion von Excel ist an zwei Stellen versteckt. Zum einen findet sich diese in der Registerkarte Ansicht in der Gruppe Makros:



als auch in der sogenannten Entwicklerleiste.

Dieses Symbolleiste mit verschiedenen Symbolen zur Entwicklung von Makros und VBA Elementen kann wie folgt aktiviert werden:

• Excel 2010
  • Registerkarte Datei
  • Optionen
  • Menüband anpassen
  • Unter der Auswahl Menüband anpassen
    Hauptregisterkarten
  • Aktivieren des Hacken bei Entwicklertools
• Excel 2007
  • Microsoft Office Schaltfläche(Windows Ball linke Ecke)
  • Excel Optionen
  • Häufig verwendet
  • Aktivieren von "Entwicklerregisterkarte in der Multifunktionsleiste anzeigen"

Nun ist neben der Registerkarte Ansicht auch eine Registerkarte "Entwicklertools" vorhanden.


Hier kann nun die Funktion "Makro aufzeichnen" gewählt werden.

Dieses funktioniert am einfachsten tatsächlich innerhalb der Symbolleiste "Entwicklertools" über die Funktion "Makro aufzeichnen".



Im erscheinenden Dialog kann eine Makroname und eine Beschreibung des Makros gewählt werden.
 

---

EXKURS: Persönliche Makroarbeitsmappe

Eine Besonderheit ist noch der Ort an dem das Makro gespeichert werden soll. In der Regel dürfte dieses die vorhandene Arbeitsmappe sein. Daher kann hier die Option "Diese Arbeitsmappe" bestehen bleiben.

Alternativ kann aber auch als Speicherort "Persönliche Arbeitsmappe" gewählt werden.

Die Makros in der persönlichen Arbeitsmappe stehen immer zur Verfügung und sind nicht an einer geöffneten Excelmappe gebunden.

Hierzu muss die Mappe jedoch einmalig eingerichtet werden. Dieses ist am einfachsten möglich indem ein "Dummy-Makro" (ein beliebiges Makro) aufgezeichnet wird und in der persönlichen Arbeitsmappe gespeichert wird.

Darauf werden unter C:\Users\Benutzername\AppData\Roaming\Microsoft\Excel\XLSTART die Datei PERSONAL.XLSB angelegt, welche beim Start von Excel für den entsprechenden Benutzername als ausgeblendete Arbeitsmappe angelegt und kann entweder direkt geöffnet werden oder über die Symbolleiste Ansicht in der Gruppe Fenster über die Funktion Einblenden angezeigt werden.

Ein in meinen Augen sehr sinnvolles Makro für die Persönliche Makroarbeitsmappe ist das Makro Excel Blattschutz aufheben, Inhaltsverzeichnis in Excel über vorhandene Tabellenblätter oder auch die im Artikel Office Integration - Excelansicht in SAP und Daten kopieren nach Excel beschriebene Makros, welche nicht in der Zieldatei gespeichert werden müssen aber sinnvollerweise immer einmal wieder angewendet werden können.
 

---

Nach der Auswahl der Funktion "Makro aufzeichnen" wird die Schaltfläche mit der Funktion "Aufzeichnung beenden" ausgetauscht.

Dieses gilt sowohl für die Entwicklersymbolleiste:



als auch in der allgemeinen Makrofunktion in der Symbolleiste Ansicht



Während die Makroaufzeichnung läuft können verschiedene Funktionen ausgewählt werden.

Im Beispiel wird das Tabellenblatt Endtabelle kopiert und die darin befindlichen Daten nach über die Spalte "BUKR" sortiert und als Export.CSV umbenannt.

Danach wird die Aufzeichnung über die Funktion "Aufzeichnung beendet" abgeschlossen.

Über die Schaltfläche Makros kann nun das Makro aufgerufen werden.



Im Beispiel hat das Makro nun die Bezeichnung Export und kann entweder direkt ausgeführt werden oder noch im VBA Editor bearbeitet werden.



Der entsprechende VBA Code für das Export Makro lautet dabei wie folgt:
 

• Sub Export()
  ' Dieses weist den Namen Export zu
  Sheets("Endtabelle").Select
  Sheets("Endtabelle").Copy After:=Sheets(4)
  
  ' Diese Anweisung selektiert das Blatt Endtabelle und kopiert es hinter das 4. Arbeitsblatt
  
  Sheets("Endtabelle (2)").Select
  Sheets("Endtabelle (2)").Name = "Export.csv"
  
  ' Nun wird die Kopie in Export.csv umbenannt
  
  ActiveWorkbook.Worksheets("Export.csv").Sort.SortFields.Clear
  ActiveWorkbook.Worksheets("Export.csv").Sort.SortFields.Add Key:=Range( _
  "A2:A1840"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
  xlSortNormal
  With ActiveWorkbook.Worksheets("Export.csv").Sort
  .SetRange Range("A1:G1840")
  .Header = xlYes
  .MatchCase = False
  .Orientation = xlTopToBottom
  .SortMethod = xlPinYin
  .Apply
  End With
  
  
  'Nun werden die Zellen A1 bis G1840 nach der Spalte A ab Zeile 2 bis 1840 sortiert
  
  ' Soweit stammt der Code auch aus der Makroaufzeichnung. Nun wird dieser noch um eine Anweisung ergänzt, dass das Tabellenblatt unter Verwendung des Buchungskreises in der Spalte A umbenannt wird
  
  ActiveSheet.Name = "Export_" & Range("A2") & ".csv"
  
  ' Die Bezeichnung wird aus der Zelle A2 des gerade sortierten Tabellenblatt übernommen
  
  
  'Durch End Sub kann der Code dann abgeschlossen werden
  End Sub


 

Wird nun das Makro aufgerufen, wird direkt ein neues Blatt angefügt, welches von der Bezeichnung her den entsprechenden Buchungskreis mit in der Bezeichnung hat.



Als Ergebnis kann dann dieses Arbeitsblatt mit dieser Bezeichnung als CSV Datei gespeichert werden und entsprechend weiter verarbeitet werden.

Formularsteuerelemente einfügen

Um die Handhabung der Makros zu verbessern ist es nicht zwingend erforderlich die Makros über die Schaltfläche Makros zu starten sondern es besteht auch die Möglichkeit entsprechende Schaltflächen (oder auch Bilder) mit einen Makro zu verknüpfen.

Hierzu kann wiederum in der Symbolleiste "Entwicklertools" über die Schaltfläche Einfügen in der Gruppe "Steuerelemente" eine Schaltfläche eingefügt werden.



Hier kann nun eine entsprechende Schaltfläche gezeichnet werden und danach ein Makro dieser Schaltfläche zugewiesen werden.



Danach kann über diese Schaltfläche das entsprechende Makro direkt gestartet werden.

Über die rechte Maustaste kann der Text der Schaltfläche bearbeitet werden.



Denkbar ist beispielsweise eine entsprechende Arbeitsanweisung für dieses Makro.



Somit ist die Tabelle mittlerweile fertig.

Hinweis: Speichern Excel Arbeitsmappe mit Makros

Mit Einführung von Excel 2007 ist es erforderlich Excel-Arbeitsmappen mit Makros nicht mehr als XLS (beziehungsweise XSLX) zu speichern sondern als XLSM "Excel-Arbeitsmappe mit Makros".

Diese aus Sicherheitsgründen eingeführte Funktion bedarf daher, dass diese Mappe dann auch als XLSM Datei gespeichert wird. Entsprechend ist das Dateisymbol von Makroarbeitsmappen mit einen warnenden Ausrufezeichen versehen.



Die Kombination aus VBA und Makros ist eine tatsächliche Bereicherung von Excel und ermöglicht verschiedene Arbeitsschritte zu automatisieren aber auch Arbeitsmappen um weitere Funktionen zu erweitern.

Ein weiteres schönes Makro ist im Artikel "Urlaubsplaner und dynamischer Kalender mit Monatsansicht" beschrieben.

Darüberhinaus helfen diverse Makros auch beim Zusammenführen von Daten aus SAP nach Excel. Dieses ist im Artikel "Office Integration - Excelansicht in SAP und Daten kopieren nach Excel" beschrieben.

Liebe Lesende,

ich wünsche Ihnen/Euch ein schönes neues Jahr und einen erfolgreichen Wechsel ins neue Jahr.



Weihnachten ist die Zeit der Ruhe und des Friedens und so hoffe ich, dass neben allen Zahlen, Formeln und Berechnungen die so am Jahresende anfallen auch Ruhe und Frieden einkehren kann.

Ich wünsche daher allen ein schöne Weihnachtszeit und einen erfolgreichen Wechsel ins neue Jahr.

Vielleicht verirrt sich ja auch die ein oder andere Person von der Arbeit auf diese Seite, daher auch von dieser Seite aus, sofern noch nicht geschehen, die herzlichsten Weihnachtsgrüße. Irgendwie wollte ich nicht in das jährliche Mailgestöber mit einfallen ;-).

Viele Grüße
Andreas Unkelbach

NFC dient zur kontaktlosen Übertragung von Daten per Funk über kurze Strecken von wenigen Zentimetern. Mittlerweile sind in einigen Smartphones ebenfalls NFC Leser integriert, so dass mit der passenden App und NFC Tags ein Datenaustausch zwischen Chip und Smartphone erfolgen kann.

Ein Anwendungsgebiet ist hier bspw. das Steuern eines Smartphones per Funkchip, der zum Beispiel am Nachtisch festgeklebt ist um das Smartphone nachts auf lautlos zu stellen.
Hierzu ist die APP Trigger sehr gut geeignet:

Kurzvorstellung

• Trigger
  Diese App ermöglicht es über bestimmte Auslöser (bspw. NFC Tags) Aktionen am Smartphone auszufphren
  Marketlink
  • Trigger

Ein NFC Tag mit Funktechnik kann nicht nur in einer Chipkarte oder Schlüsselanhänger sondern auch in einen normalen Aufkleber verborgen sein.

Solche Aufkleber können zum Beispiel wie folgt aussehen:

Amazon Link: 5 NFC Tagsfür etwa 3 Euro

Diese NFC Tags sind mehrfach beschreibbar und können mit der APP mit entsprechenden Funktionen versehen werden.

Beim Start der APP kann über die linke obere Ecke auf "Meine Aufgaben" gewechselt werden.



Über das "+" kann eine neue Aufgabe hinzugefügt werden.



Nun kann ein Trigger als Auslöser dieser Aufgabe genutzt werden.



In der kostenlosen Version der App können NFC, Bluetooth oder WLAN als Auslöser genutzt werden. In der Bezahlversion (in App Kauf für rund 2,19 €) stehen auch Akkustand, Geoposition oder Zeit zur Verfügung.

Da jedoch hier NFC genutzt werden soll, kann NFC gewählt werden. Eine Steuerung über WLAN, GPS oder sonstige Auslöser ist meiner Meinung nach mit der App EasyProfile (siehe Artikel Customizing bei Android (Launcher Lockscreen und Tastaturlayout ersetzen)) elegant möglich.

Sobald NFC gewählt ist kann auch schon die erste Aufgabe angelegt werden.



Diese kann auch wieder per "+" hinzugefügt werden.



Aus verschiedenen Kategorien können nun Aufgaben gewählt werden. Folgende Kategorien stehen mit unterschiedlichen Funktionen zur Verfügung:

• WLAN & Netzwerk
  Hier können WiFi Funktionen und GPS Funktionen verwaltet werden (WLAN Hotspot, GPS an/aus, Modile Daten an/aus, ...)
• Bluetooth
  Hier können verschiedene Bluetooth Optionen verwaltet werden (Verbindung zu ein Geröt, Bluetooth an/aus, ..)
• Töne & Lautstärke
  Klingelton, Medien-lautstärke , ...
• Display
  Helligkeit, Rotation, ...
• Social Media
  Facebook Anmeldung, Tweet per Twitter senden, ..
• Nachrichten
  E-Mail oder Nachrichten senden, ..
• Anwendungen & Shortcuts
  Anwendung starten, URL öffnen, ..
• Medien
  Medienwiedergabe
• Alarm
  Alarm stellen
• Telefon
  Telefonnummer wählen
• ...
  und diverse andere (inklusive Integration mit TASKER

Teilweise sind für einzelne Funktionen auch Rootberechtigungen erforderlich.

Im Beispiel wurden verschiedene Optionen aus der Kategorie Töne & Lautstärke gewählt.



Hier sind die einzelnen Lautstärke für die Aufgabe Ton an hinterlegt (Klingelton auf 2, Medien auf 7 und so weiter). Durch Weiter geht es zum nächsten Schritt.

Neben einer einfachen Aufgabe kann jedoch auch ein Switch (ein Wechsel zwischen zwei Aufgaben) definiert werden.



Neben der Einschaltung der Lautstärke können hier die Töne auf lautlos gestellt werden.



Hier wird die Lautstärke des Smartphones auf stumm geschaltet. Somit ist nur noch der Alarm (Wecker) auf normaler Lautstärke, so dass ein Wecken noch funktioniert. Zu beachten ist, dass der Klingelton bei 0 auf Vibration gestellt ist.

Als Abschluss kann nun der NFC Tag beschrieben werden.



Der einmal beschriebene NFC Tag kann von jeden (natürlich NFC fähigen) Smartphone mit dieser App gelesen und umgesetzt werden, so dass diese Aufgabe nicht in der eigenen Aufgabenliste gepflegt sein muss.

Nutzung von NFC am Smartphone

Die NFC Funktion (zum Datenaustausch mit anderen NFC-fähigen Geräten) ist in den Geräteeinstellungen unter Verbindungen in der Rubrik "Verbinden und freigeben" zu finden. Zur Nutzung von NFC ist zu beachten, dass das Smartphone entsperrt sein muss (kein Sperrbildschirm aktiv) bevor es auf einen NFC-Tag gelegt wird. Dieses ist sinnvoll um das versehentliche Aktivieren von Aufgaben per NFC zu vermeiden.

In der Statusleiste erfolgt dann eine Meldung, welche Aufgaben ausgeführt worden sind.

Neben der Nutzung am Nachtisch können hier auch verschiedene Aufgaben für den Arbeitsplatz (Verbinden mit WLAN an der Arbeit) oder am Auto (Bluetoothaktivierung, Tweet "Bin unterwegs", ...) genutzt werden.

Bei anfänglicher Skepsis kann hier doch recht schnell eine Menge an praktischer Anwendungen gefunden werden.


Nachtrag:

Für meine Zwecke (lautlos, wenn das Handy zur Nachtruhe ans Ladekabel gehängt wird, ist jedoch der Trigger "Lädt auf" als "Wenn Ladegerät angeschlossen ist" in Kombination mit "Nur zu bestimmten Zeiten" an "bestimmten Tagen" mit der Option Bei Verbindung mit bestimmten "WLAN Netzwerk" sinnvoller, da dieses ohne NFC funktioniert und damit kein NFC benötigt.

Der entsprechende Trigger lautet dann:


Wenn Ladegerät angeschlossen bzw. Wenn Ladegerät getrennt ist.

Diese Option kann sowohl von der Zeit (Wochentage und Uhrzeit) oder auch über das verbundene Netzwerk (WLAN Netz) eingeschränkt werden.

Als Aktion kann dann das Ton-Profil umgeschaltet werden.



Als Optionen sind hier die Einstellungen "Stumm", "Vibration" und "Aus" vorhanden. Die Option "Aus" wechselt hierbei zum Standardprofil, womit das Smartphone wieder hellwach ist und entsprechend Krach macht.

Ein anderes Einsatzgebiet (neben der Koppelung mit Bluetooth Lautsprecher) ist die Integration mit Haushaltsgeräten.



So wird automatisch ein Timer an der Waschmaschine gestellt, so dass die folgende Ladung Wäsche eingeladen werden kann.

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

Wie im Artikel Einzelposten FI Hauptbuch (Auswertung Buchungen Partnergesellschaft) beschrieben besteht die Möglichkeit der Auswertung von Einzelposten des Moduls FI durch eine Query über die Tabelle BSEG "Belegsegment Buchhaltung". Hier sind alle einzelnen Belepositionen der Buchungsbelege im SAP System hinterlegt.

Wie beschrieben wurden die absoluten Buchungswerte (ohne Vorzeichen) in Kombination mit den "Soll-/Haben-Kennzeichen" ausgewertet, so dass hier das Ergebnis (der gebuchte Wert) bei einer Habenbuchung negativ und bei der Sollbuchung positiv dargestellt wird. Dieses entspricht auch der Darstellung in Einzelpostenlisten.

Der Nachteil an der Verwendung der Tabelle BSEG ist, dass hier als Belegdatum lediglich das Feld Valutadatum vorhanden sind. Die Information über Belegdatum und Buchungsdatum ist im Belegkopf des Buchungsbeleg zu finden und wird in der Tabelle BKPF gespeichert.

Clustertabellen versus transparente Tabellen

Ein Join über die Tabellen BSEG und BKPF ergibt als Fehlermeldung den Hinweis, dass Pool- und Cluster-Tabellen nicht in einem Tabellen-Join verwendet werden können.

Bei der Tabelle BSEG handelt es sich um eine Clustertabelle, die dem Cluster RFBLG zugeordnet ist. Innehrlab eines Cluster werden Felder anderer Tabellen komprimiert in einer einzelnen Spalte gespeichert. Hierdurch ist eine Verwendung von Cluster-Tabellen ebenso wie Pool-Tabellen nicht in Joins möglich.
Clustertabellen sammeln hierdurch mehrere Einzeltabellen in einer Tabelle.

Die Einzelpostenbelege sind je nach Kontenarten in transparenten Sekundärtabellen (als Sekundärindizes) zu finden. Diese sind entsprechend schneller auszuwerten und können auch einzeln in einer Query verwendet werden.
Hierbei werden von der Art her Tabellen für ausgeglichene Posten (BSA*) und für offene Posten (BSI*) unterschieden. Dabei können folgende transparente Tabellen relevant sein:

• BSIS "Buchhaltung: Sekundärindex für Sachkonten" (Sachkonten offene Einzelposten)
• BSAS "Buchhaltung: Sekundärindex für Sachkonten (ausgegl. Posten"
  (Sachkonten ausgeglichene Posten)
• BSID "Buchhaltung: Sekundärindex für Debitoren"
  (Debitoren Offene Einzelposten)
• BSAD "Buchhaltung: Sekundärindex für Debitoren
  (ausgebl. Posten)" (Debitoren ausgeglichene Posten)
• BSIK "Buchhaltung: Sekundärindex für Kreditoren"
  (Kreditoren Offene Einzelposten)
• BSAK "Buchhaltung: Sekundärindex für Kreditoren (ausgegl. Posten"
  (Kreditoren ausgeglichene Posten)

Der Vorteil der Clustertabelle BSEG ist jedoch, dass diese alle Belegpositionen enthält unabhängig davon ob diese offen oder schon ausgeglichen sind (gleiches gilt natürlich auch für den Belegkopf in der Tabelle BKPF).

Tabellen-Joins können dennoch nur über Transparente Tabellen ausgeführt werden, so dass hier eine Möglichkeit wäre sowohl die oben erwähnten Tabellen mit der Tabelle BKPF zu verknüpfen (outer join). Dieses hat jedoch den Nachteil, dass hier nicht über Felder wie Kostenstelle, Innenauftrag oder Profit-Center selektiert werden können, da diese Informationen ja wiederum in den Einzeltabellen gespeichert sind (auf der Belegposition).

Um nun dennoch die Informationen aus den Belegkopf (Tabelle BKPF) wie zum Beispiel das Belegdatum beziehungsweise Buchungsdatum zu erhalten und gleichzeitig auf die Merkmale der Belegposition (Tabelle BSEG) wie Kostenträger oder Sachkonto zuzugreifen bietet es sich an statt einzelne Tabellen zu verknüpfen eine logische Datenbank zu verwenden.

Logische Datenbank (LDBA)

Logische Datenbanken enthalten schon Verknüpfungen und können direkt als Grundlage für ein Infoset verwendet werden. Dieses hat den Vorteil, dass man nicht selbst erst einzelne Tabellenverknüpfungen erstellen muss. Die Struktur einer logischen Datenbank kann in der Transaktion SE36 eingesehen werden.

1. Infoset definieren
Bei der Anlage des Infoset über die Transaktion SQ02 wird als Datenquelle die logische Datenbank BRF definiert.

Hier sollen nach Angabe der logischen Datenbank die auszuwertenden Knoten mit ausgewählt werden. Für eine Auswertung von BKPF und BSEG würde es ausreichen nur diese beiden Knoten auszuwerten. Diese werden dann als Feldgruppen ausgewählt. Innerhalb der Struktur sind aber auch alle anderen Bestandteile der logischen Datenbank vorhanden, so dass hier einzelne Werte in Feldgruppen übernommen werden können. Wie bei jeden Infoset kann es sinnvoll sein so viele Datenfelder wie möglich zu übernehmen, da dieses Infoset dann auch für andere Queries verwendet werden kann.

Um eine vergleichbare Datengrundlage wie in der eingangs beschriebenen Query zu erhalten werden folgende Daten aus den jeweiligen Strukturen übernommen:

Aus der Struktur Belegsegment Buchhaltung (BSEG):
BSEG-VBUND Partner Gesellschaftsnummer
BSEG-PRCTR Profitcenter
BSEG-BELNR Belegnummer eines Buchhaltungsbeleges
BSEG-GJAHR Geschäftsjahr
BSEG-SHKZG Soll-/Haben-Kennzeichen
BSEG-SGTXT Positionstext
BSEG-AUFNR Auftragsnummer
BSEG-KOSTL Kostenstelle
BSEG-DMBTR Betrag in Hauswährung
BSEG-HKONT Sachkonto der Hauptbuchhaltung

Aus der Struktur Belegkopf für Buchhaltung (BKPF):
BKPF-BUDAT Buchungsdatum im Beleg
BKPF-BLDAT Belegdatum im Beleg

2. Query anlegen
Mit Bezug auf dieses Infoset kann dann eine entsprechende Query über die Transaktion SQ01 angelegt werden.

Hierbei ist zu beachten, dass in der Grundliste die einzelnen Merkmale in der Reihenfolge BKPF und danch BSEG ausgegeben werden. Es ist bspw. nicht möglich erst Daten aus BSEG und zwischen drin aus BKPF auszugeben. Jedoch kann das Layout der Query später durch eine Anzeigevariante angepasst werden.

In der späteren Query sind Buchungskreis, Belegnummer und Geschäftsjahr automatisch als Selektionskriterium vorhanden. Das Buchungsdatum und die Referenznummer sind dabei ebenfalls aktiv wie die Möglichkeit der freien Abgrenzung über weitere Parameter des Belegkopfes.

Sofern weitere Felder als Selektionsvariablen definiert werden erscheinen diese im Selektionsbild als Programmabgrenzung.

Innerhalb der Query werden nun auf folgende Felder der einzelnen Tabellen Zugriff genommen bzw. in der Grundliste zugewiesen. Hierbei ist L als Listenfeld und S als Selektionsfeld zu verstehen.

Dabei werden nun auch die oben angelegten lokalen Felder mit übernommen.

Die Felder werden hier in der Reihenfolge angeben, wie diese dann auch in der Query ausgegeben werden sollen:

• Buchungsdatum im Beleg (L,S) BKPF-BUDAT
• Belegdatum im Beleg (L,S) BKPF-BLDAT
• Jahr (L) BSEG-GJAHR
• Belegnummer (L) BSEG-BELNR
• Hauptbuch (L,S) BSEG-HKONT
• Soll-/Haben-Kennzeichen (L) BSEG-SHKZG
• Betrag (L) BSEG-DMBTR
• Kostenstelle (L,S) BSEG-KOSTL
• Auftrag (L,S) BSEG-AUFNR
• Prctr (L,S) BSEG-PRCTR
• Text (L) BSEG-SGTXT
• Partnergesellschaft (L,S) BSEG-VBUND

Der Vorteil der Verwendung der logischen Datenbank ist sicherlich, dass nun auch Daten aus den Belegkopf (neben Datum sind hier auch Transaktion oder Erfasser) verfügbar sind.

Nun kann die Query ebenso, wie in der vorherigen Auswertung der Tabelle BSEG noch um lokale Felder erweitert werden, so dass hier ebenfalls das Soll/Haben Kennzeichen berücksichtigt wird.

Hierfür wird innerhalb der Query mit lokalen Feldern gearbeitet.

Um lokale Felder zu definieren wird nicht direkt die Grundliste der Query bearbeitet (wo auch das Layoutdesign gepflegt wird) sondern innerhalb der Querypflege (Transaktion SQ01) mit "nächstes Bild (F6)" auf die Feldauswahl der Query gewechselt.

Über
BEARBEITEN->KURZBEZEICHNUNG
kann für die einzelnen Felder eine Kurzbezeichnung eingestellt werden. Nun werden rechts neben den Datenfeldern Eingabefelder für die Kurzbezeichnung angegeben. Hier erhalten nun folgende Felder eine Kurzbezeichnung (in der Beschreibung ist die Bezeichnung, gefolgt von der angelegten Kurzbezeichnung und des dahinter technisch liegenden Tabellenfeldes angegeben):

• Soll-/Haben-Kennzeichen - SHKZG (BSEG-SHKZG)
• Betrag in Hauswährung - DMBTR (BSEG-DMBTR)
• Kostenstelle - KOSTL (BSEG-KOSTL)
• Auftragsnummer - AUFNR (BSEG-AUFNR)

Diese Kurzbezeichnung ist notwendig, da wir auf diese dann Bezug nehmen, wenn wir ein eigens Feld mit einer Formel anlegen.

Dieses geht über

BEARBEITEN->LOKALES FELD->ANLEGEN

Dieses Lokale Feld wird dann in der Feldgruppe angelegt, in der wir uns gerade befinden. In unseren Fall also ebenfalls in der Feldgruppe der Tabelle BSEG bzw. der Bezeichnung der Feldgruppe "Belegsegment Buchhaltung".


Hierbei werden drei lokale Felder mit folgenden Eigenschaften angelegt.

1. Feld (Gebuchter Wert)
Kurzbezeichnung: WERT
Feldbezeichnung: gebuchter Wert
Überschrift: gebuchter Wert
gleiche Eigenschaften wie: DMBTR
Berechnungsvorschrift:
Hier wird eine komplexe Berechnung mit folgenden Bedingungen hinterlegt:

• Bedingung: SHKZG = 'S'
  Formel: 1 * DMBTR
• Bedingung: SHKZG = 'H'
  Formel: -1 * DMBTR

2. Feld (Identifikation ob Innenauftrag oder Kostenstelle)
Kurzbezeichnung: IAK
Feldbezeichnung: IAK
Überschrift: Innenauftrag oder Kostenstelle
Eigenschaften: Textfeld (Anzahl Zeichen: 2)
Berechnungsvorschrift:
Hier wird eine komplexe Berechnung mit folgenden Bedingungen hinterlegt:

• Bedingung: KOSTL>0
  Formel: 'K'
• Bedingung: AUFNR>0
  Formel: 'IA'
• sonst: ''

Diese Berechnung ist möglich, da auf eine Belegzeile nicht Innenauftrag und Kostenstelle gleichzeitg ausgewiesen werden.

3. Feld (Bebuchter Kostenträger)
Kurzbezeichnung: KTR
Feldbezeichnung: Kostenträger
Überschrift: Kostenträger
gleiche Eigenschaften wie: AUFNR
Berechnungsvorschrift:
Hier wird eine komplexe Berechnung mit folgenden Bedingungen hinterlegt:

• Bedingung: KOSTL+AUFNR>0
  Formel: KOSTL+AUFNR
• sonst: ''

Sofern Kostenstellen und Innenaufträge unabhängige Nummernkreise ohne Überschneidung haben. Dieses könnte als Beispiel der Fall sein, wenn Kostenstellen mit 1* oder 2* beginnen und etwaige Innenaufträge mit 3* bis 8*.

Diese lokalen Felder können nun ebenfalls in der Grundliste der Query mit aufgenommen werden.

Ergänzung:
Eine mögliche Erweiterung dieser Query in Richtung eines umfassenden Belegjournals für die Finanzbuchhaltung könnte dabei wie folgt gestaltet sein.

 

Beschreibung	Tabellenfeld
Buchungskreis	BSEG-BUKRS
Jahr	BSEG-GJAHR
Geschäftsmonat	BKPF-MONAT
Belegnummer	BSEG-BELNR
Belegart	BKPF-BLART
Belegdatum	BKPF-BLDAT
Buchungsdatum	BKPF-BUDAT
Soll/Haben Kennzeichen	BSEG-SHKZG
Hauptbuch	BSEG-HKONT
Steuerkennzeichen	BSEG-MWSKZ
Profit-Center	BSEG-PRCTR
Kostenstelle	BSEG-KOSTL
Auftrag	BSEG-AUFNR
IAK	Formel siehe Beschreibung
Kostenträger	Formel siehe Beschreibung
Betrag Hauswährung	BSEG-DMBTR
gebuchter Wert	Formel siehe Beschreibung
Text	BSEG-SGTXT
Zuordnungsnummer	BSEG-ZUONR
Partnergesellschaft	BSEG-VBUND
Bezeichnung Partnergesellschaft	Zusatzfeld TEXT_BSEG_VBUND
Debitor	BSEG-KUNNR
Text Debitor	Zusatzfeld TEXT_BSEG_KUNNR
Kreditor	BSEG-LIFNR
Text Kreditor	Zusatzfeld TEXT_BSEG_LIFNR
Referenz	BKPF-XBLNR
Ausgleichsdatum	BSEG-AUGDT

Fazit
Insgesamt ist bei dieser Query jedoch zu beachten, dass hier ausschliesslich FI Buchungen erfasst werden. Sofern CO Buchungen (auf sekundäre Kostenarten) erfolgen werden diese nicht mit ausgegeben.

Und was ist nun mit Tee Buchungen im Modul CO?

Hierfür gibt es jedoch die Möglichkeit, wie im Artikel "Query Einzelpostenliste IST über CO Objekte (Auflösen von Innenauftrag, Kostenstelle) sowie Benutzerstammdaten und Erfassungsdatum" beschrieben, die Istbuchungen im CO und somit sowohl die fortgeschriebenen FI Buchungen auf primäre Kostenarten als auch die CO internen Buchungen auf sekundären Kostenarten mit auszuwerten. Daneben können auch Planbuchungen im CO, wie im Artikel "CO Planeinzelposten Objekt und Partnerobjekt auswerten / Mehrere Felder summieren" beschrieben, ebenfalls über eine Query ausgwertet werden.
 

---

Hinweis:

Eine kurze Einführung in das Thema SAP Query habe ich im Artikel
"Grundlagen Kurzeinführung und Handbuch SAP Query" beschrieben und hoffe Ihnen hier eine Einführung ins Thema bieten zu können.

---