Ausgangslage
Für die Darstellung der Lehrleistung / Lehrnachfrage von einzelnen Studiengängen zu Lehreinheiten werden statische Kennzahlen auf Ebene der Studiengänge (Innenaufträge) und Lehreinheiten (Kostenstellen) genutzt. Pro Lehreinheit gibt es eine entsprechende statische Kennzahl, die die Höhe der Lehrnachfrage als SWS darstellt.

Die Lehrnachfrage stellt die Leistungsbeziehungen zwischen den Lehreineheiten und den einzelnen Studiengängen dar. Die Lehrnachfrage stellt gem. KapVO die Lehrnachfrage in SWS dar. Sie dient der Verrechnung von Lehreinheiten (Fachbereichen) auf Studiengängen. Sie ergibt sich aus der Multiplikation von Studierenden mit  Curricularnormwert (CNW) der entsprechenden Lehreinheit innerhalb eines Fachbereichs. Der CNW stellt hierbei den Lehraufwand je Studierenden dar.

 

Maßeinheit für statistische Kennzahlen festlegen

Als Maßeineheiten können entweder die schon vorhandenen Einheiten (ST für Stück oder PERS für Personen) verwendet werden oder über die Transaktion CUNI eigene Einheiten im Customizing definiert werden. Die entsprechende Einstellung ist im Customizing Pfad unter der Transaktion SPRO unter:

• SAP Netweaver
• Allgemeine Einstellungen
• Maßeinheiten überprüfen

zu finden.Hier kann dann zum Beispiel die Maßeinheit SWS mit 3 Dezimalstellen festgelegt werden.

Buchen von statistischen Kennzahlen

Die statischen Kennzahlen können innerhalb SAP entweder über die Transaktion KB31N im Ist oder über die Transaktion KP46 im Plan erfasst beziehungsweise geändert werden. Die statistische Kennzahlen werden oft für die Umlage von Kosten verwendet, können aber auch zur Kennzahlenanalyse verwendet werden. Bei der Verwendung von Innenaufträgen als Empfänger von Umlagen sind im Artikel "Innenaufträge als Empfänger von Umlagezyklen (KSUB)" Hinweise gegeben.

Auswertung statischer Kennzahlen

Eine Auswertung der gebuchten statistischen kennzahlen kann innerhalb des SAP Standardmenüs über die Transaktion S_ALR_87013645 (Stat. Kennzahlen: Periodenaufriß) erfolgen. Allerdings sind hier nur Kostenstellen auswertbar. Ferner ist hier nur auf Basis der Kostenstellen und der entsprechenden statischen Kennzahl eine Auswahl möglich.

Für eine Analyse der Leistungsbeziehung wäre es aber auch interessant den Lehrimport je Studiengang über die auf diesen Innenauftrag gebuchten statistischen Kennzahlen als auch den Lehrexport über die Auswertung der Lehrnachfrage über eine Lehreinheit und der Ausgabe der entsprechend zugeordneten Studiengänge (Innenaufträge).

Grundvorraussetzung

Für eine passende Auswertung wurden zwei Stammdatengruppen angelegt. Zum einen wurden die statischen Kennzahlen für die Lehrnachfrage in eine Kennzahlengruppe über die Transaktion KBH1 (Statistische Kennzahlengruppe anlegen) zusammengefasst. Diese Gruppe LENA umfasst damit alle Lehrnachfragen der einzelnen Lehreinheiten (bspw. LENA001 für Betriebswirtschaftslehre. LENA002 für Architektur, ...).
Alternativ findet sich diese Transaktion innerhalb des SAP Menü unter:

• Rechnungswesen
• Controlling
• Kostenstellenrechnung
• Stammdaten
• Statistische Kennzahlengruppe
• Anlegen (Transaktion KBH1) bzw. Ändern (KBH2)

Daneben wurden die einzelnen Studiengänge (abgebildet als Innenaufträge) über die Transaktion KOH3 zu einer Innenauftragsgruppe zusammengefasst. Es bietet sich hier an die Studiengänge nach Studiengangsclustern und Abschlussarten zu gliedern, so dass hier ebenfalls noch eine detailreichere Auswertung erfolgen kann.

So könnte der Aufbau einer Innenauftragsgruppe STUD folgende Untergruppen haben

• Cluster I (Sozialwissenschaften)
• Cluster II (Rechts- und Wirtschaftswissenschaften)
  • Abschlußart Diplom (51)
  • Bachelor (84)
  • Master (90)
• Cluster III (Geisteswissenschaften)
• Cluster IV (Sportwissenschaften)
• Cluster V (u.a. Angew. und Darst. Kunst ohne Einzelunterricht)
• Cluster VI (Darst. Kunst mit Einzelunterricht)
• Cluster VII (u.a. Ingenieurwissenschaften)
• Cluster VIII (u.a. Naturwissenschaften)
• Cluster IX (Veterinärmedizin)
• Cluster X (Medizin und Zahnmedizin)
• Cluster XI (Kleine Fächer )

sein. Unterhalb der Abschlussartsgruppen können dann die einzelnen Studiengänge eingeordnet werden. Entsprechend würde die Obergruppe  mit STUD bezeichnet und die einzelnen Cluster möglicherweise als STUD_02 in Kombination mit der Abschlussart STUD_02_84. Alternativ könnte dieses auch im Kurztext des Innenauftrags (als Cluster, Studienfach und Abschlussart) oder als Nummer des Auftrags verschlüsselt werden. Da sich Studienfachbezeichnung oder Schlüsselung hin und wider ändern, kann es sinnvoller sein diese Information tatsächlich im Kurztext zu hinterlegen und die Innenaufträge fortlaufend zu nummerieren. Hier hätte eine solche Innenauftragsgruppe auch den Vorteil, dass alle Studiengänge im Cluster 02 ausgewertet werden können.

Eine kurze Einführung ins Thema Studiengangsbezeichnung kann durch den Artikel "Studienbereiche und Fächergruppenschlüssel"  gewonnen werden.

Innerhalb eines Report Painter Bericht können nun die einzelnen Kennzahlen ausgewertet werden.

Da es sich um eine Auswertung innerhalb der Kostenstellenrechnung handelt ist die Bibliothek 1VK (basierend auf die Struktur CCSS) ausgewählt. Hier können sowohl Kostenstellen als auch Innenaufträge ausgewertet werden. Insgesamt werden zwei Berichte angelegt, die beide in der gleichen Berichtsgruppe zugeordnet werden, so dass sowohl eine Auswertung über Import als auch Export von Lehrleistungen erfolgen kann.
 

1. ReportPainter Bericht Lehrimport

Hier werden die auf die Studiengänge verbuchten Lehrnachfrage in Höhe der SWS je Lehreinheit  (Kennzahlen innerhalb der Gruppe LENA) ausgewertet.

Allgemeine Selektion:
KostRechKreis (Variable) KOKRS01
Periode   1 bis 12
Auftrag (Gruppe , Variable) 1AUFSET

Schlüsselspalten
Basiskennzahl ist in allen Spalten die Statistische Menge. Die einzelnen Lehrnachfragen wurden hierbei in den jeweiligen Jahr in unterschiedlichen Planversionen erfasst. Entsprechend sind sowohl Planversion als auch Geschäftsjahr ein Kriterium zur Ausgabe der Statistischen Menge.

LN &1GJAHLJ Version &1VERP-A
Werttyp  1  (Plan)
Bewertung 0
Geschäftsjahr (Variable) 1GJAHLJ
Version (Variable) 1VERP-A

Dieses stellt die Planwerte für das laufende Jahr in der selektierten Planversion A dar.

LN &1GJAHVJ Version &1VERP-B
Werttyp  1  (Plan)
Bewertung 0
Geschäftsjahr (Variable) 1GJAHVJ
Version (Variable) 1VERP-B

Dieses stellt die Planwerte für das vorherige Jahr in der selektierten Planversion B dar.

LN &1GJAHVV Version &1VERP-C
Werttyp  1  (Plan)
Bewertung 0
Geschäftsjahr (Variable) 1GJAHVV
Version (Variable) 1VERP-C

Dieses stellt die Planwerte für das vorvorherige Jahr in der selektierten Planversion C dar.

Wurden die statischen Kennzahlen im IST erfasst kann hier auch der Werttyp 4 für Ist genommen werden. Sofern die Kennzahlen aus IST nach Plan kopiert wurden oder über die Transaktion KP46 erfasst wurden, kann sich hier auch die Version entsprechend angepasst werden. So könnte zum Beispiel einheitlich die Version KLR für alle Spalten verwendet werden.

Schlüsselzeilen:
Stat.Kennzahl (Gruppe) LENA

Hier wird die Kennzahlengruppe LENA ausgewertet.

Wird nun der Bericht gestartet kann für einen beliebigen Studiengang (durch Angabe des entsprechenden Innenauftrages) eine Auswertung über die importierte Lehrleistung erhoben werden.

2. Report Painter Bericht Lehrexport

Der zweite Bericht beantwortet die Frage an welche Studiengänge eine Lehreinheit Leistung erbracht hat.Hierzu wird in der Selektion nicht ein Studiengang sondern die entsprechende statistische Kennzahl (zum Beispiel LENA001) angegeben und in der Ausgabe erfolgen alle Studiengänge die entsprechende SWS als Leistung bezogen haben.

Über Bearbeiten->Variation und Auflösen des Merkmals Auftrag können auch mehrere Innenaufträge in der Selektion angegeben werden und innerhalb der Navigation dann zwischen den einzelnen Studiengängen gewechselt werden.

Schlüsselspalten
Basiskennzahl ist auch hier  in allen Spalten die Statistische Menge. Die einzelnen Lehrnachfragen wurden hierbei in den jeweiligen Jahr in unterschiedlichen Planversionen erfasst. Entsprechend sind sowohl Planversion als auch Geschäftsjahr ein Kriterium zur Ausgabe der Statistischen Menge.

LN &1GJAHLJ Version &1VERP-A
Werttyp  1  (Plan)
Bewertung 0
Geschäftsjahr (Variable) 1GJAHLJ
Version (Variable) 1VERP-A

Dieses stellt die Planwerte für das laufende Jahr in der selektierten Planversion A dar.

LN &1GJAHVJ Version &1VERP-B
Werttyp  1  (Plan)
Bewertung 0
Geschäftsjahr (Variable) 1GJAHVJ
Version (Variable) 1VERP-B

Dieses stellt die Planwerte für das vorherige Jahr in der selektierten Planversion B dar.

LN &1GJAHVV Version &1VERP-C
Werttyp  1  (Plan)
Bewertung 0
Geschäftsjahr (Variable) 1GJAHVV
Version (Variable) 1VERP-C

Dieses stellt die Planwerte für das vorvorherige Jahr in der selektierten Planversion C dar.

Der Unterschied stellt sich nun in den Schlüsselzeilen dar.

Schlüsselzeilen:
Innenauftrag (Gruppe) STUD
Hier wird die eingangs beschriebene Innenauftragsgruppe (nach Cluster und Abschlußart) ausgewertet. Über Bearbeiten->Variation wird das Merkmal Stat.Kennzahl aufgelöst, so dass hier bei der Angabe der Kennzahlengruppe LENA auch zwischen einzelnen Lehrnachfragen gewechselt werden kann.
 

3. Bericht zu Lehrimport und Lehrexport ausführen

Um einen umfassenden Bericht zu erhalten kann nun, sofern beide Berichte der selben Berichtsgruppe zugeordnet sind, diese über die Transaktion GR55 gestartet werden.

In der Selektion können nun für die Innenauftragsgruppe STUD und für die Statische Kennzahl die Kennzahlengruppe LENA gewählt werden, so dass hier die gesamte Lehrverneutzung innerhalb der einzelnen Studiengänge und Lehreinheiten dargestellt werden kann.

Sofern eine Umlage auf Basis dieser Kennzahlen erfolgt, kann ein solcher Bericht der Qualitätskontrolle und gegebenenfalls Qualitätssicherung dienen. Daneben können hier aber auch aus SAP direkt Leistungsbeziehungen innerhalb der einzelnen Studiengänge und der Lehreinheiten ermittelt werden.

Ein vergleichbarer Bericht ist natürlich auch mit anderen Bereichen, zum Beispiel der Umlage von Gebäudekosten nach in Anspruch genommener Raumfläche, Auswertung von Studierenden oder sonstigen Kennzahlen möglich. Leider ist die Berechnung der Lehrnachfrage als Produkt aus Curricularnormwert-Anteil und entsprechender Jahrgangsbreite eines Studiengang auch auf diese Weise noch nicht direkt in SAP (zumindest im SAP Modul Controlling) möglich. Dennoch bieten auch solche Berichte ein interessantes Toolset im Rahmen der Hochschulplanung.

Eine weitere Frage, in Verbindung mit kennzahlenbasierter Kostenumlage, ist jedoch oftmals, welcher Anteil von Kosten aus welcher Umlagestufe erfolgte.

Hier kann der Artikel "ReportWriter: Ergebnisse Planumlage (KSUB) je Partnerobjekt"  einen kurzen Einblick bieten und ermöglicht so eine Erhebung der einzelnen Zwischenergebnisse eines Betriebsabrechnungbogen auf Ebene der einzelnen Lehreinheiten oder auch Studiengänge als Produkte einer Lehreinheit. Bei der Aufbereitung solcher Datensets ist allerdings eine ausführliche Dokumentation und Darstellung der einzelnen Umlageschritte unerlässlich. Für eine relativ umfangreiche Dokumentation einer solchen Umlage und der damit verbundenen Kennzahlen ist unter vielen anderen Programmen sicherlich auch das Programm DIA zu empfehlen. Die Funktionsweise dieses Programms habe ich in verschiedenen Artikeln unter den Stichwort (TAG) "DIA" erläutert. Allerdings sollte wie bei jeden anderen Bericht auch hier der Berichtszweck und das Berichtsziel genau definiert werden und die dargestellten Ergebnisse auch entsprechend erläutert werden.

---

Hinweis:

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

---

Ausgangslage

Die Grundlage der klassischen Budgetierung ist im Artikel "SAP PSM-FM klassische Budgetierung mit unterschiedlichen Budgetversionen" beschrieben. Grundsätzlich werden innerhalb der klassischen Budgetierung in  PSM-FM Belege auf Basis der Hierarchie der Finanzstellen und Finanzpositionen gebucht.

Sofern beim Buchen des Budgets bei der Angabe von Fond und Finanzstelle nicht die richtige Finanzstelle angegeben wurde (diese sollte der verantwortlichen Kostenstelle des entsprechenden Innenauftrages entsprechen) bleibt tatsächlich nur die Möglichkeit diesen Beleg über die Transaktion FR89 zu stornieren.

Im Rahmen der klassischen Budgetierung werden inhaltlich zwei Belege gebucht. Der Erfassungsbeleg umfasst dabei die eigentliche Buchungsinformation (Angabe von Sender und Empfänger) und der Hierarchiebeleg bucht im Rahmen der Hierarchie der Finanzstellen und Finanzpositionen alle entsprechende Ebenen innerhalb dieser Hierarchie.

Oftmals wurde jedoch nicht falsche Kontierungselemente eingegeben, die dann entsprechend storniert oder umgebucht werden müssten, sondern der Budgettext weist eine fehlerhafte Information aus.

Während in anderen Modulen die Belege nicht verändert werden können, ist diese Information tatsächlich auch bei einen gebuchten Beleg änderbar. Hierzu müssen jedoch sowohl der Erfassungsbeleg als auch der Hierarchiebeleg geändert werden.

Änderung des Hierarchiebeleg

Die Änderung des Hierarchiebeleges erfolgt entweder über die Transaktion FM2E direkt oder über folgenden Menüpfad innerhalb des SAP Menüs.

• Rechnungswesen
• Public Sector Management
• Haushaltsmanagement
• Budgetierung
• Klassische Budgetierung
• Belege
• Hierarchiebelege
• Ändern ( Transaktion FM2E)

Nach Eingabe der Hierarchiebelegnummer kann hier über das Feld Text der Belegkopftext des Hierarchiebelegs geändert werden. Über die Ansichtsschaltfläche (Brille mit ErfassBeleg) kann auch der zugehörige Erfassungsbeleg  (Transaktion FR60) angezeigt werden. Über die entsprechende Erfassungsbelegnummer kann nun auch dieser Beleg geändert werden.

Änderung des Erfassungsbeleg

Der Erfassungsbeleg kann entweder direkt über die Transaktion FR59 oder innerhalb des SAP Menü an folgender Stelle.

• Rechnungswesen
• Public Sector Management
• Haushaltsmanagement
• Budgetierung
• Klassische Budgetierung
• Belege
• Erfassungsbelege
• Ändern ( Transaktion FR59)

An dieser Stelle befindet sich auch die eingangs erwähnte Stornofunktion (Transaktion FR89), welche bei reiner Textänderung jedoch nicht erforderlich ist. Hier scheint im ersten Moment das Erfassungsbild der Belegänderung wie in der Belegansicht mit gesperrten (nicht änderbaren) Feldern versehen zu sein. Über die Schaltfläche Belegkopf (Zylindersymbol bzw. STRG+UMSCHALT+0) kann jedoch ebenfalls der Belegkopf und damit über das Feld Text der Belegkopftext geändert werden.

Interessanterweise ändert sich der eigentliche Beleg dadurch nicht sondern nur der Text. Die wesentlichen Informationen, wie Betrag, Fond und Finanzstelle lassen sich hier zwar nicht ändern, allerdings ist es oftmals sehr hilfreich den Budgettext nach erfolgter Buchung nochmals ändern zu können.

Ausgangslage
Da das SAP System ein paar Tage offline sein wird, soll für ein Bericht anhand einer Kostenträger festgestellt werden, in welchen Bereich diese liegt. Grundsätzlich sollten Kostenträger soweit sprechend sein, dass man auch anhand der Nummer erkennen sollte, wo diese zuzuordnen ist, aber teilweise kann dieses (zum Beispiel bedingt durch Umgliederung von Kostenstellen oder bei Innenaufträgen) problematisch sein.

Daher wurden die einzelnen Gruppen in einer Exceltabelle exportiert, so dass hier nachgesehen werden kann, in welcher Gruppe ein Kostenträger liegt. Hierbei können sowohl Einzelwerte als auch Intervalle eine entsprechende Gruppe darstellen.

Um das Beispiel etwas zu vereinfachen (und nicht mit der Kostenstellenstruktur zu arbeiten) sind in diesen Beispiel statt einzelner Kostenstellen Notenpunkte und als Kostenstellengruppen die entsprechende Schulnote genommen worden.

Im Beispiel soll durch die Eingabe der Kostenstelle 11 (Punkte) direkt ausgegeben werden, dass dieses der Kostenstellengruppe (Schulnote) "Note 2 - Gut" entspricht.

Hierzu wurde die Tabelle wie folgt aufgebaut.


Hierbei wurden im Namensmanager die einzelnen Spaltenüberschriften einen entsprechenden Zellenbereich zugeordnet (KS_von umfasst die Zellen A2 bis A7).
Die Definition von Namensräumen durch Verwendung des Namensmanager ist im Artikel "Formulare gestalten in Excel" beschrieben.

Wenn es sich bei den Werten um Einzelwerte handeln würde (dieses ist im Fall der 0 Punkte der Fall) könnte nun wie im Artikel "Die Funktion SVERWEIS ohne #NV ausgeben" verfahren werden. Da es sich jedoch um Intervalle handelt und zum Beispiel die Kostenstelle 11 zwischen 10 und 12 in der Kostenstellengruppe "Note 2 - Gut" liegt muss hier eine andere Formel verwendet werden.

Funktion VERWEIS  Syntaxversion VEKTORVERSION

Hierzu kann die die Vektorversion der Formel VERWEIS verwendet werden.

Der Syntax der Version lautet dabei wie folgt:

=VERWEIS(Suchkriterium;Suchvektor;[Ergebnisvektor])

Die Besonderheit dieser Funktion liegt darin, dass sie wenn die VERWEIS-Funktion keinen Wert finden kann, der mit dem jeweiligen Wert von Suchkriterium übereinstimmt, den größten Wert in Suchvektor verwendet, der kleiner oder gleich dem Wert von Suchkriterium ist.

Für unser Beispiel würde die Formel zur Ermittlung der Kostenstellengruppe wie folgt lauten:

=WENN(ISTNV(VERWEIS(1;1/(B10>=KS_von)/(B10<=KS_bis);KS_Gruppe));"Nicht zugeordnet";VERWEIS(1;1/(B10>=KS_von)/(B10<=KS_bis);KS_Gruppe))

Hierbei funktioniert die Funktion wie folgt:
Überprüfe jede Zeile, ob Teil 2 und Teil 3 (getrennt durch /) des SuchVEKTORS! wahr sind und liefere dann die passende Zeile aus dem Zielvektor zurück.

Dieses funktioniert, da durch den Ausdruck (B10>=KS_von) ein WAHR ausgegeben wird, welches einer 1 entspricht, ebenso würde der Fall WAHR (und damit 1) eintreten, wenn B10<=KS_bis entspricht. Sofern kein Treffer erfolgt würde ein #NV ausgegeben werden.
Dieser Fall ist in der obigen Formel durch die Formel ISTNV und der Wertausgabe "nicht zugeordnet" abgefangen.


Das Ergebnis dieser Formel kann dann auch wiederum in einer SVERWEIS oder vergleichbare Funktion verwendet werden um zum Beispiel Berichtsempfänger einer Kostenstelle (Kostenstellengruppenverantwortliche) zu erhalten oder um ergänzende Funktionen zu erhalten. So könnte zum Beispiel bei der Schulnote 4 ausreichend noch ein Hinweis auf Nachprüfung möglich erfolgen.

Kleiner Hinweis am Rand:

In der Zelle C11 wurde die Formel um ein =WENN(B11="";"";   ... ) ergänzt, da andernfalls kein Wert als 0 interpretiert werden würde und somit keine Angabe automatisch der Schulnote 6 - ungenügend entspricht.

Fazit
Zwar ist es schöner Kostenstellengruppen direkt im System auszuwerten, aber manchmal kann es hilfreich sein auch Berichte außerhalb des Systems nach den einzelnen Stammdaten auswerten zu können. Sehr hilfreich ist hier die im Artikel "Auflösen von Stammdatengruppen nach Einzelwerten - Einzelwerte zu Sets" beschriebene Methode zum Export von Stammdatengruppen aus SAP.

Weitere Verweisfunktionen in Excel

Insgesamt hat Excel hier drei Verweisfunktionen:

1. VERWEIS Funktion

Die in diesen Artikel dargestellte Formel/Funktion VERWEIS ist die Grundfunktion aller Verweisfunktionen und ist am dynamischsten in der Anwendung, wie hier ja auch zu sehen ist.

2. SVERWEIS Funktion

Die SVERWEIS Funktion dürfte eine der bekannteren Formeln im Controlling sein.
Dabei ist sie wie folgt aufgebaut.

    =SVERWEIS(Suchkriterum;Matrix;Spaltenindex;Bereich_Verweis)

Anhand eines Suchkriteriums wird eine Matrix (Suchbereich) durchsucht und die entsprechende Spalte ausgegeben, die mit der ersten Spalte übereinstimmt. Ferner wird über Bereich_Verweis festgelegt ob eine exakte übereinstimmung (FALSCH) oder ungefähre Übereinstimmung (WAHR) gewünscht ist.

Im Artikel "SVERWEIS ohne NV und dynamische Größen für Datenbereiche" ist diese anhand eines Beispiels beschrieben.
 

3. WVERWEIS Funktion

Diese Funktion arbeitet vergleichbar zur SVERWEIS Funktion gibt aber nicht Spalten sondern gibt das Ergebnis aus einen Zeilenindex heraus.

Dabei ist sie wie folgt aufgebaut.

    =WVERWEIS(Suchkriterum;Matrix;Zeilenindex;Bereich_Verweis)

Als Eselsbrücke könnte man sich merken, dass die SVERWEIS Funktion senkrecht nach der ersten Spalte die Daten durchsucht, wohingegen die WVERWEIS Funktion waagrecht in der ersten Zeile nach einer Übereinstimmung sucht und die folgenden Daten dann anhand des übergegeben Index ausgibt.

Ein Beispiel für die WVERWEIS Funktion ist im Artikel "Excel: WVERWEIS Funktion oder Jahresdaten umgruppieren für Mehrjahresvergleich" erläutert.

Ausgangslage:
Es werden die Ergebnisse (Saldo von Kostenstellengruppen) bei unterschiedlichen Rechenmodellen betrachtet. Hierbei sollen die Veränderungen der Variante C zur Variante B ausgedrückt werden.

Während beim "Saldo A" Die Verändeurng von 100 auf 150 korrekt mit + 50 % aus der Berechung (Variante C - Variante B) im Verhältnis zu (/) Variante B ausgewiesen wird, wird beim Saldo B bei dieser Berechnung ein Verändung um - 50 % dargestellt, obgleich der Saldo um 50 % (Veränderung von +50 zu -100) eine Wertsteigerung erfahren hat.

Ursache hierfür ist die mathematische Regel zum Teilen durch negative Zahlen.

Teilt man eine positive Zahl durch eine negative oder eine negative Zahl durch eine positive, so ist das Ergebnis negativ.

Mathematisch ausgedrückt: "Ein Bruch ist kleiner Null, wenn Zähler und Nenner verschiedene Vorzeichen haben."

Um nun doch noch eine "sinnvollere" Darstellung der Veränderung dieser Salden zu erhalten gibt es in Excel 3 Varianten.


Anhand des Beispiels stehen die Werte in den Spalten B3 und C3. Sofern wir mit negativen Zahlen rechnen, kann nicht die Formel (C3-B3)/B3 verwendet werden. Statt dieser Variante bieten sich drei andere Varianten ein.

1. Wenn Funktion
Die entsprechende Formel lautet hierbei:

=WENN(UND(C3>B3;B3<0);(C3-B3)/B3*-1;(C3-B3)/B3)

Sofern die Variante C einen höheren Wert als Variante B hat und der Wert der Variante B kleiner als 0 ist, wird die Veränderung von C zu B ins Verhältnis zur Variante B gesetzt und mit -1 multipliziert, andernfalls wird das Verhältnis direkt ausgegeben.

2. Vorzeichen Funktion
Die entsprechende Formel lautet hierbei:

=VORZEICHEN(C3-B3)*ABS((C3-B3)/B3)

Über die Funktion Vorzeichen wird ermittelt, ob es sich bei der Veränderung von C zu B um eine positive oder negative Veränderung handelt und danach die absolute Veränderung (ohne Verzeichen) von C nach B im Verhältnis zu B mit den entsprechenden ermittelten Vorzeichen ausgegeben.

3. Verhältnis zum absoluten Wert
Eine Kurzfassung und die in meinen Augen elegantere Variante ist in der Zelle D6 angewendet.
Hier lautet die Formel wie folgt:

=(C3-B3)/ABS(B3)

Hier wird die Veränderung zwischen der Variante C zur Variante B ermittelt und diese ins Verhältnis zum absoluten Wert der Variante B gesetzt. Das Vorzeichen der Veränderung wird anhand der Berechnung im Zähler des Bruches ermittelt, so dass hierdurch am einfachsten die prozentuale Veränderung ausgedrückt werden kann. Ferner ist diese Variante um einiges einfacher zu lesen, als die Variante mit der WENN Funktion.

Fazit:
Somit können sowohl negative, positive als auch gar keine prozentuale Veränderungen beim Vergleich zweier Ergebnis dargestellt werden. Gerade beim Vergleich von Salden einer Kontengruppe oder von verschiedenen Kostenträgern kann es öfters auch einmal ein negatives Ergebnis geben, so dass dieser Fall beim Vergleich zweier Varianten oder Geschäftsjahre durchaus relevant sein kann und bei der ersten Berechnung (wie in der Zelle D3) zu Verwirrungen führen kann.

Nachtrag / Fragen aus Kommentaren:
Ev:

"Danke! Wie ist das bei Veränderungen von Null auf eine positiven Wert? Ich nutze die Formel für eine Budgetkalkulation. Technisch gibt es hier eine "Verbesserung", d.h. es müsste einen prozentualen Anstieg geben, allerdings wird die Formel mit #DIV Fehlermeldung angezeigt. Wie kann man das lösen? Danke vorab!"

Dieses hatte ich wie folgt vorgeschlagen zu lösen:

wenn ich von 0 ausgehe ist prozentual eigentlich unendliches Wachstum beziehungsweise kann hier mathematisch kein prozentuales Wachstum von einer Basis von 0 sinnvoll angegeben werden. Die Steigerung müsste ja auch als Multiplikation dargestellt werden.  Wenn es um reine Darstellung geht würde ich im obigen Beispiel folgende Formel verwenden.   =(C3-B3)/ABS(WENN(B3=0;1;B3))  Hier würde dann also der Absolute Wert ausgegeben werden.

Insgesamt freue ich mich jedes Mal aufs Neue, wenn auch zu älteren Ertikeln noch spannende Fragen aufkommen : 

 

Berichtswesen nicht nur mit Excel
Beruflich ist ein Schwerpunkt meiner Arbeit das Controlling und Berichtswesen. Neben Excel arbeite ich hier auch besonders gerne mit SAP. Schon bei der Konzeption eines umfangreichen Berichtes und etwaiger Dashboards ist es hier hilfreich sich im Vorfeld passende Gedanken zu machen. Hier habe ich im Buch »Berichtswesen im SAP®-Controlling« (Buchvorstellung, für 19,95 EUR bestellen) einige Punkte festgehalten.

Im Blog finden Sie aber auch regelmäßig Praxisbeispiele rund um die Themen SAP, Berichtswesen und Controlling. Viele Beispiele sind dabei mit Bezug zur Hochschule aber können, wie der Artikel "Statistische Kennzahlen für Verrechnung in SAP - Umlage und Verteilung nicht nur im Hochschulcontrolling und Hochschulberichtswesen" auch für andere Branchen genutzt und als Grundlage zum Aufbau eines eigenen Berichtswesens genutzt werden.

Ich würde mich freuen, wenn meine Bücher (Publikationen) aber auch Schulungen (Workshop & Seminare) auch für Sie interessant wären. Weitere Partnerangebote, wie auch eine Excel Schulung zu Pivot finden Sie ebenfalls unter der Rubrik Onlineshop.

Ausgangslage
Innerhalb einer Access-Datenbank sollen verschiedene Berichte zusammengefasst und durch Abfragen miteinander verbunden werden. Hierzu ist es jedoch erforderlich, dass die zugrunde liegenden Tabellen aktualisiert werden und dafür in die Datenbank importiert werden können. Da es sich um eine Vielzahl von Tabellen handelt ist die Importfunktion in Access etwas umständlich. Vergleichbar zum Artikel "Excel Umgang mit Makros und Visual Basic for Applications (VBA)" bietet aber auch Access die Möglichkeit Makros zur Arbeitserleichterung zu verwenden an.
 

Makro unter Access zum Import erstellen

Zur Erstellung eines Makros kann im Ribbon Erstellen in der Befehlsgruppe Andere die Funktion Makro ausgewählt werden.

Hierbei unterscheiden sich die Oberflächen zwischen Access 2007 und Access 2010 erheblich. Daher wird im Folgenden sowohl auf Access 2007 als auch Access 2010 eingegangen.

Makro unter Access 2007 erstellen

Damit alle Funktionen zur Erstellung eines Makros aktiviert sind sollten sowohl die Schaltfläche "Alle Aktionen anzeigen", "Bedingungen" als auch "Argumente" in der Symbolleiste Entwurf aktiviert sein.

Insbesondere die Möglichkeit Bedingungen vor der Ausführung von Aktionen zu hinterlegen ermöglicht es für jede Aktion vorher eine Rückfrage einzuholen.

1. Vorhandene Tabellen löschen
Als ersten Schritt sollen die bestehenden importierten Tabellen in der Datenbank gelöscht werden. Hierzu wird über die Aktion "Löschen Objekt" der Objekttyp Tabelle und über das Feld Objektklasse die eigentliche Tabelle gelöscht. Im Beispiel ist dieses die Tabelle "Grunddaten".

Durch die Bedigung Meldung("Tabelle löschen";1)=1 wird diese Aktion nur bei Bestätigung ausgeführt.

2. Neue/Aktualisierte Tabellen einfügen
Über die Funktion "TransferArbeitsblatt" kann sowohl der Import als auch Export von Tabellen erfolgen. Im Beispiel soll eine Exceltabelle (ExcelWorkbook) als Tabelle "Grundtabelle" importiert werden. Dieses funktioniert über den Transfertyp Importieren, Dateiformat Excel Workbook und Angabe des Tabellennamen "Grundtabelle"

Hierbei können sowohl der Dateiname hinterlegt werden (im Beispiel B:import.xls) als auch über das Feld Bereich das zu importierende Tabellenblatt (im Beispiel Grunddaten!A-Z) angegeben werden. Hier zeigt sich auch schon der Nachteil von Makros, da sowohl der Dateiname als auch der Speicherort fix angegeben werden müssen.Über die Option Besitzt Feldnamen wird die erste Zeile der Tabelle als Datensatzbeschreibung verwendet. Durch die Bedingung kann wiederum abgesichert werden, dass nicht aus Versehen unbeabsichtigt eine Tabelle importiert wird.

3. Abfrage per Makro starten
Wenn alle Tabellen importiert sind kann über die Aktion "ÖffnenAbfrage" eine entsprechende Abfrage gestartet werden.

Durch die Ansicht Datenblatt erscheint die Abfrage auch direkt als Auswertung und kann exportiert oder weiter bearbeitet werden.

Makro unter Access 2010 erstellen

Ab Access 2010 hat sich die Darstellung zur Erstellung von Makros von einer Tabellenform und darunter liegenden Attributen zu einer Baumstruktur verändert. Ferner wurde die Aktion TransferArbeitsblatt in die Aktion ImportierenExportierenTabellenblatt umbenannt. Bestehende Makros werden direkt umgewandelt.
Das eingangs beschriebene Makro würde unter Access 2010 wie folgt geschrieben werden:

Makro zu Visual Basic konvertieren

Sicherlich sind solche Makros eine Arbeitserleichterung beim Import und auch der Verarbeitung von außerhalb der eigentlichen Access Datenbank liegenden Tabellen und sonstigen Daten.

Allerdings sind prinzipbedingt die einzelnen Abfragen auch relativ eingeschränkt in ihrer Funktionsweise. So war es in Access 2007 nur möglich für jede einzelne Aktion eine entsprechende Bedingung zu definieren. Ab Access 2010 können innerhalb der Bedingung auch mehrere Aktionen eingefügt werden. Allerdings ist auch unter Access der Import von Tabellenblättern nur über die Angabe des genauen Dateinamens inklusive Speicherort möglich. Hier gibt es unter VBA aber darüber hinaus gehende Möglichkeiten.  Im ersten Schritt muss hierfür das angelegte Makro in eine VBA Funktion umgewandelt werden

Unter Access 2007 kann über die Datenbanktools die Funktion "Makros zu Visual Basic konvertieren" aufgerufen werden.

Ab Access 2010 befindet sich diese Funktion in der Entwurfsansicht des jeweiligen Makros.

Unter beiden Funktionen wird das vorher entworfene Makro dann als VBA Code dargestellt und kann als Modul im VBA Editor betrachtet und bearbeitet werden.

Der VBA Code für das oben beschriebene Makro würde dabei wie folgt umgewandelt werden.

Das beschriebene Makro ist nun als Function angelegt und steht als VBA Modul in der Access Datenbank zur Verfügung.

Per Makro VBA Funktion ausführen

Diese Funktion kann dann als Code über ein neues Makro aufgerufen werden.
Unter Access 2007 ist dieses über die Aktion "AusführenCode" möglich.

Hier wird direkt die nun vorhandene Funktion Import ohne weitere Argumente ausgeführt.
Ab Access 2010 hat sich hier eigentlich nur die Darstellung verändert, so dass auch hier der Code ausgeführt werden kann.

Erweiterung Makro um VBA Code

Der Vorteil bei der Umwandlung zu VBA besteht darin, dass nun die aus der Makrofunktion bekannten Funktionen erheblich erweitert werden können.

Dynamischer Speicherort zum Import von Daten verwenden

Wie beim Makro TransferArbeitsblatt beziehungsweise ImportierenExportierenTabellenblatt beschrieben muss zum Import einer Tabelle nach Access sowohl der Dateiname der Exceltabelle als auch der Speicherort (im Beispiel war dieses b:import.xls) angegeben werden. Sofern die Datenbank aber flexibel genutzt werden soll, oder aber sich der Speicherort der zu importierenden Daten auch ändern kann, kann folgende Ergänzung des VBA Codes sinnvoll sein.

    Dim Pfad As String
    Pfad = Application.CurrentProject.Path
    
    If MsgBox("Import aus " & Pfad & " ?", vbYesNo) = vbYes Then
       Pfad = InputBox("Pfadnamen ohne abschliessenden ")
    End If
    
    MsgBox "Import aus " & Pfad & " !", vbInformation, "Importquelle"

Über die Anweisung DIM Pfad As String wird eine Variable von Typ String definiert. Diese Variable bekommt als Wert den aktuellen Projektpfad der Accessdatenbank zugewiesen, so dass die Datenbank im Idealfall zu den zu importierenden Dateien kopiert werden kann.
Sofern die Importdaten jedoch in einen anderen Pfad liegen kann über die InputBox ein anderer Pfad angegeben werden. In Form einer Infobox wird danach noch einmal der Importpfad angegeben.

Die Anweisung zum Import der Tabelle import.xls kann nun wie folgt angepasst werden:

If (MsgBox("Tabelle importieren", 1) = 1) Then
        DoCmd.TransferSpreadsheet acImport, 10, "Grundtabelle", Pfad & "import.xls", True, "Grunddaten!A:Z"
End If

Hier wird nun ebenfalls das Tabellenblatt Grunddaten aus der Tabelle import.xls importiert. Jedoch kann der Speicherort vorher nochmals geändert werden. Sofern dieser Pfad nicht geändert wurde, wird der Pfad der Access-Datenbank verwendet.

Verschachtelte Bedingungen

Ein weiterer Vorteil innerhalb VBA ist, dass hier auch mehrere Bedingungen miteinander verschachtelt werden können oder auch mehrere Aktionen nach einer Bedingung gestartet werden können.

Sollen nach erfolgreichen Import der Tabellen auch mehrere Abfragen gestartet werden kann dieses über folgende Anweisung erfolgen.

    MsgBox "Daten wurden importiert", vbOKOnly, "Keine Panik"
    If (MsgBox("Auswertungen starten", 1) = 1) Then
        DoCmd.OpenQuery "110 Auswertung 1", acViewNormal, acReadOnly
        DoCmd.OpenQuery "120 Auswertung 2", acViewNormal, acReadOnly
        DoCmd.OpenQuery "130 Auswertung 3", acViewNormal, acReadOnly
    MsgBox "Speichern Sie die einzelnen Abfragen und kopieren die Daten über Inhalte Einfügen->Transponieren in die Zieltabelle", vbInformation, "Daten ausgewertet"
    End If

Hierdurch werden nach erfolgreichen Importiert der Tabellen drei Auswertungen gestartet und eine Anweisung ausgegeben, wie mit diesen weiter verfahren werden soll. Dieses erfolgt über die Anweisung DoCmd.OpenQuery verbunden mit der Darstellung Normal und Schreibgeschützt. Danach wird über die Anweisung MsgBox eine Anweisung zur weiteren Verarbeitung der Daten ausgegeben.

Insgesamt würde die angepasste Anweisung wie folgt lauten:

Entsprechend könnten auch weitere Anweisung innerhalb des VBA Codes angepasst werden und hierdurch eine wesentlich flexiblere Importfunktion gestaltet werden. Darüber hinaus können auch weitere Informationen und Anweisungen zur Handhabung der Datenbank hinterlegt werden.

Als Gesamtcode würde dieses dann in Access 2010 wie folgt aussehen:

Function Import()
 Dim Pfad As String
    Pfad = Application.CurrentProject.Path
    
    If MsgBox("Import aus " & Pfad & " ?", vbYesNo) = vbYes Then
       Pfad = InputBox("Pfadnamen ohne abschliessenden ")
    End If
    
    MsgBox "Import aus " & Pfad & " !", vbInformation, "Importquelle"

    If (MsgBox("Tabelle löschen?", 1) = 1) Then
        DoCmd.DeleteObject acTable, "Grunddaten"
    End If

If (MsgBox("Tabelle importieren", 1) = 1) Then
  DoCmd.TransferSpreadsheet acImport, 10, "Grundtabelle", Pfad & "import.xls", True, "Grunddaten!A:Z"
End If
MsgBox "Daten wurden importiert", vbOKOnly, "Keine Panik"
        If (MsgBox("Auswertungen starten", 1) = 1) Then
            DoCmd.OpenQuery "110 Auswertung 1", acViewNormal, acReadOnly
            DoCmd.OpenQuery "120 Auswertung 2", acViewNormal, acReadOnly
            DoCmd.OpenQuery "130 Auswertung 3", acViewNormal, acReadOnly
MsgBox "Daten über Inhalte Einfügen->Transponieren in die Zieltabelle", vbInformation, "Daten ausgewertet"
End If

Import_Exit:
    Exit Function

Import_Err:
    MsgBox Error$
    Resume Import_Exit

End Function