Andreas Unkelbach
Werbung


Sonntag, 10. April 2016
12:58 Uhr

Pivottabellen ab Excel 2010 dynamischer filtern mit Datenschnitten am Beispiel Hochschulfinanzstatistik

Durch "Pivot Tabellen Datenschnitte: Erstellen, Formatieren, Verbinden, und Sortieren" von Lukas Rohr (excelnova.org) bin ich erstmals auf das Thema Datenschnitten bei Pivot-Tabellen aufmerksam geworden. Seit Excel 2010 ist diese Technik in Excel vorhanden und hilft dabei Filter zu setzen und gleichzeitig einen Überblick über die einzelnen ausgeblendeten Felder zu behalten.

Getreu den Motto "Es ist schon alles geschrieben, nur nicht von jedem!" möchte ich hier ebenfalls ein Beispiel zur Anwendung von Datenschnitten darstellen, auch wenn ich bisher sehr gerne auf diesen Artikel verwiesen habe. Grundlage für die zu erstellende Pivot-Tabelle ist eine Auswertung der Hochschulfinanzstatistik.

Finanzen der Hochschule - Hochschulfinanzstatistik

Wie im Artikel "Hochschulstatistische Kennzahlen" erläutert melden Hochschulen ihre "Aufwendungen und Investitionsausgaben" und  "Erträge" nach Arten und in fachlicher Gliederung. Hierbei werden die einzelnen Bereiche der Hochschulen nach "Fächergruppen" und "Lehr und Forschungsbereiche" untergliedert. Die Art der Aufwendungen und Erträge werden dabei durch SYF-Codes abgebildet.

Dieser bundeseinheitliche Schlüssel gliedert die Einnahmen und Ausgaben (bzw. Ertrag und Aufwand)  sowie Investitionen nach der Systematik der Finanzarten.  Die Lehr- und Forschungsbereichen werden nach Fächergruppenschlüssel dargestellt.

Hierbei werden die Hochschulen auf Bundesebene nach ihrer Art des Rechnungswesen unterschieden (kaufmännisch oder kameral). Für das folgende Beispiel betrachten wir den "Fragebogen für Hochschulen mit kaufmännischem Rechnungswesen" der Jahreserhebung zur Hochschulfinanzstatistik.

So wird zum Beispiel die "Evangelische Theologie" mit Fächergruppenschlüssel 020 in der Fächergruppe "01 Sprach und Kulturwissenschaften" und die "Informatik" mit Fächergruppenschlüssel 350 in der Fächergruppe "04 Mathematik, Naturwissenschaften".

Neben der Fächergruppen und Fachgebieten sind dadrunter auch noch die einzelnen Studienfächer einsortiert (als Beispiel  innerhalb der Fächergruppe 350 (Informatik) das Studienfach "Bioinformatik - 3540" oder innerhalb der Fächergruppe 020 (Evangelischen Theologie) das Studienfach "Praktische Theologie und Religionspädagogik (evang. Th.) - 0250"

Daneben gibt es noch Zentrale Einrichtungen zu denen unter anderen die Hochschulverwaltung (880) aber auch die Zentralbibliothek (900) oder das Hochschulrechenzentrum (910) gezählt wird, die jedoch auch wiederum feiner gegliedert werden können.

Hierbei berichten die Hochschulen sowohl in einer Personalstandstatistik als auch im Rahmen einer Finanzstatistik über ein Jahr. Für dieses Beispiel wird die Finanzstatistik näher betrachtet.

Wie erwähnt sind alle Aufwendungen (als Beispiel Energiekosten (522)) oder auch Erträge (u.a. aus Drittmitteln vom öffentlichen Bereich (63) oder anderen Bereich (64)) entsprechend in SYF-Code vorhanden.

Im Form eines Fragebogen werden diese Daten dann einzeln an das jeweilige Statistik Amt gesandt und im Rahmen einer Bundesstatistik zusammengefasst.

Grundtabelle - Einzeldaten nach Hochschule, Fächergruppe und SYF-Code

Im Rahmen einer Gegenüberstellung mehrere Jahre dieser Finanzstatistik kann eine entsprechende rein fiktive Grundtabelle nun wie in der folgenden Abbildung aussehen.

Grundtabelle Einzeldaten nach Jahr und Art
Über diese Tabelle kann dann über den Ribbon "Einfügen" mit der Schaltfläche "Pivottabelle" eine Pivottabelle erstellt werden.

Pivottabelle anlegen und eventuell um berechnete Felder ergänzen

Soweit ist dieses wie in jeder anderen Pivottabelle möglich.  In der Grundtabelle habe ich schon anhand des Feldes "Hochschule" die Hochschulart "Uni" oder "FH" durch die ersten drei Ziffern des Feldes "Hochschule" bestimmt. Dieses ist natürlich auch durch ein "berechnetes Feld" in der eigentlichen Pivottabelle möglich. Die hierzu erforderlichen Einstellungen habe ich in den Artikeln "Excel Berechnete Felder in Pivottabellen" und "Excel Pivottabelle Darstellung Grenzwerte Einnahmen auf Projekte je Person durch Zuordnung VZÄ auf verantwortlicher Kostenstelle" ausführlicher beschrieben.

Die auszuwertende Pivottabelle ist dabei wie folgt aufgebaut.

PivotTabelle-Feldliste

Als Filter ist hier lediglich Summe gewählt um nicht sowohl die Gruppe "01 Sprach- und Kulturwissenschaften" als auch die hier eingeordneten Lehr- und Forschungsbereiche wie zum Beispiel die Theologie doppelt zu zählen.

Selbstverständlich könnte man nun jede Menge Filter in der Pivottabelle selbst setzen.

Filtern von Daten über Datenschnitten

Eine wesentlich komfortablere Variante ist es jedoch nun hier mit Datenschnitten zu arbeiten.

Hierzu kann, sofern man in der Pivottabelle ein Feld ausgewählt hat, über den Ribbon "Optionen" innerhalb der Befehlsgruppe "Sortieren und Filtern" die Schaltfläche "Datenschnitt einfügen" einfügen (siehe Abbildung).

Datenschnitt einfügen

Hier besteht nun die Möglichkeit für jedes Feld der Datentabelle ein Datensegment festzulegen. Im einfachen Beispiel sind dies folgende Punkte:

Datenschnitt auswählen

Im Ergebnis haben wir nun mehrere Datensegmente die dynamisch den Inhalt der Pivottabelle filtern. Anhand der Energiekosten in den LFB Informatik für Fachhochschulen im Jahr 2012 ist dieses mit fiktiven Zahlen erläutert.

Ergebnis Datenschnitten

Mittlerweile sind Datenschnitten einer der Gründe warum ich den Wechsel nach Office 2010 sehr schätze und diese tatsächlich als Innovation wahrnehme. Anhand der Farben sind hier auch in den einzelnen Datenschnitten noch weitere Informationen enthalten.

Dunkelblau hinterlegt sind die Felder welche ausgewählt und auch Daten enthalten sind
Dieses trifft im Beispiel auf die Daten  der Hochschulart  "FH" oder SYF-Code "522 - Energiekosten" zu.

Innerhalb der Datenschnitten "LFB-Fächergruppe" oder auch "Hochschule" ist darüber hinaus auch zu erkennen, welche Daten durch die Auswahl in den anderen Datenschnittpunkten nicht mit angezeigt werden, obgleich hier Daten hinterlegt sind. Dieses wird durch eine transparente Farbe dargestellt.

Für die Anwendende der Pivot-Tabelle ist nun aber auch möglich anhand der Datensegmente selbst eine Auswahl an darzustellende Werte mit der Maustaste oder für mehrere Werte durch STRG und der Maustaste auszuwählen, so dass hier nicht mit Filtern sondern anhand der dargestellten Werte eine Auswahl getroffen werden kann.

Weitere Erläuterungen und sonstige Anwendungsgebiete


Damals (2014) hatte ich nur auf Facebook auf diesen Artikel hingewiesen (siehe Posting vom 7. August 2014) bin aber auch heute noch sehr begeistert von den dort beschriebenen Möglichkeiten und möchte daher auch hier im Blog nochmals auf die gelungene und umfassende Anleitung von excelnova.org zu diesen Thema verweisen.

Grundsätzlich nutze ich meine Facebook Seite tatsächlich neben Blog eine gute Quelle um über aktuelle Fundstücke rund um das Themengebiet meiner Seite informiert zu werden. Außerdem nutze ich auch selbst diese Seite um kurze Hinweise oder Webfundstücke festzuhalten die nicht unbedingt in einen ganzen Artikel beschrieben werden oder manchmal erst Jahre später als Artikel erscheinen.

Die eingangs verlinkte Anleitung stellt in meinen Augen eine umfassende Anleitung dar, auf die ich bisher immer gerne verwiesen habe und mit diesen Artikel eigentlich nur um einen weiteren Anwendungsfall ergänzen möchte. Vielleicht haben ja auch andere Kolleginnen und Kollegen im Bereich des Hochschulberichtswesen auf diese Art und Weise eine Anregung diese Technik für eigene Auswertungen zu verwenden.

Datemschnitt (Slicers) auf als Tabelle formatierte Tabellen ab Excel 2013

Durch Martin Weiß (tabellenexperte.de) ist ein weiterer Vorteil der Datenschnitten ab Excel 2013 erwähnt. Im Artikel "Bequemer filtern mit Datenschnitten" weist er darauf hin, dass ab Excel 2013 tatsächlich Datenschnitten auch auf "als Tabelle formatierte" Tabellen angewandet werden kann. Bis Excel 2010 ist die Funktion Datenschnitt (englisch “Slicers”) nur für Pivot-Tabellen verfügbar. Dieses spricht eigentlich noch mehr dafür Datentabellen tatsächlich als Tabellen zu formatieren. Ein weiterer Vorteil ist hier auch im Artikel "Mehrere Autofilter im Tabellenblatt einer Exceltabelle setzen" beschrieben.
 

Basis der Auswertungen Report Painter & Report Writer

Wenn es um Auswertungen im Hochschulberichtswesen geht sind auch folgende Artikel interessant:"Auswertung Statistische Kennzahlen auf Innenaufträge für Lehrimport und Lehrexport auf Ebene Studiengänge", "Studienbereiche und Fächergruppenschlüssel".

Wie schon im Artikel "Unterschiedliche Auswertungsmöglichkeiten im Controlling (Report Writer, Recherchebericht, SAP Query) und natürlich Excel ;-)" beschrieben sind viele dieser Berichsanforderungen mit SAP zu erfüllen. Technisch liefert hier der Artikel "Grundlagen Kurzeinführung und Handbuch Report Painter Report Writer" ein passendes Werkzeug.

Daneben kann aber auch im Umgang mit öffentlichen Daten für das eigene Arbeitsumfeld eine Menge an interessanter Auswertungen erstellt werden. Im Rahmen des Zensus 2011 wurde dieses auch im Artikel "Ergebnisse des Zensus 2011" am Beispiel einer "Auswertung höchster Berufsabschluss in der Altersklasse 18 bis 29 für die Hochschulstandorte in Hessen" aber auch mit Links zu weiteren öffentlichen Daten rund um Open Data bzw. Bildungswesen ergänzt..

Hinweis: Aktuelle Buchempfehlungen besonders SAP Fachbücher sind unter Buchempfehlungen inklusive ausführlicher Rezenssionen und Bestellmöglichkeit zu finden.
Diesen und weitere Texte von Andreas Unkelbach finden Sie auf http://www.andreas-unkelbach.de


Dienstag, 15. März 2016
17:59 Uhr

Aktualisierungsabfrage in Access mit WENN (sowohl A als auch B) DANN Funktion

Eigentlich versuche ich soweit es irgendwie geht Daten aus SAP so aufzubereiten, dass eine Nachbearbeitung in nachgelagerten Systemen (im Beispiel Tabellenkalkulation oder Datenbanken) nicht mehr erforderlich ist. Dennoch gibt es hin und wieder Fälle, wo eine entsprechende Bearbeitung zum Beispiel in Access notwendig ist, da noch nicht an alle Auswertungen gedacht wurde oder man eine weitere Information aus den vorhandenen Daten auslesen möchte...

Während ich in Excel keine Probleme habe Wenn-Dann Funktionen mit Verschachtelungen zu versehen fällt mir dieses in Access etwas schwerer, da der Syntax ein klein wenig abweicht und statt Zellbezüge hier Bezüge auf einzelne Tabellenfelder genommen werden muss und auch der Syntax von Formeln etwas von Excel abweicht, was im zweiten Lösungsweg zu sehen ist. Vermutlich ist meine Vorliebe für Excel einer der Gründe warum ich mich lieber mit Tools zur Erstellung einer MindMap (siehe "Mindmapping und Sketchnotes im Beruf nutzen für Brainstorming oder Mind Mapping mit XMIND") als intensiver mit Access beschäftige, auch wenn es hin und wieder durchaus praktisch ist.

Ausgangslage
Im Rahmen einer Aktualisierungsabfrage auf einzelne Tabellen soll das Feld "QoS" in der Tabelle "Ergebnistabelle" anhand folgender Fälle aktualisiert werden beziehungsweise einen Wert erhalten.
  • Sind die letzten 2 Ziffern des Innenauftrag kleiner oder gleich 49 dann soll als Wert "S" zurückgegeben werden.
  • Handelt es sich bei den letzten 2 Ziffern des Innenauftrag um größer oder gleich 50 dann soll als Wert "Q" zurückgegeben werden.
  • Sofern die dritte Stelle des Innenauftrag eine 4 enthält ist in jeden Fall als Wert "Q" auszugeben.
In der Tabelle "Ergebnistabelle" ist der Innenauftrag in unseren Beispiel im Feld "Projekt" abgespeichert.

Im Rahmen der Aktualisierungsabfrage wird nun also für das Feld "QoS"  der Tabelle "Ergebnistabelle" im Feld Aktualisieren folgende Formel eingegeben.

Am Einfachsten ist dieses, wenn über die rechte Maustaste die Option "Aufbauen" gewählt wird. Hierdurch erscheint der Ausdrucksgenerator und wir bekommen sämtliche Felder der Datenbank aber auch alle möglichen Ausdrücke zur Auswahl angezeigt. Für unseren Fall arbeiten wir aber mit den Ausdrücken WENN, LINKS und RECHTS. Wobei ich hier zwei Lösungsansätze habe.

Verschachtele WENN dann WENN Funktion

Nachdem wir den Ausdrucksgeneartor aufgerufen haben erhalten wir ein Fenster in dem die folgende Formel eingetragen werden kann:

Wenn
(
 Rechts(Links([Ergebnistabelle]![Projekt];3);1)*1=4;
 "Q";
 Wenn
   (
    Rechts([Ergebnistabelle]![Projekt];2)*1<50;"S";"Q"
   )
)

Zur Erklärung:
Im ersten Abschnitt wird überprüft, ob bei der Innenauftragsnummer an vierter Stelle eine 4 steht. In diesen Fall handelt es sich in jeden Fall um "Q", andernfalls (DANN) wird nun nicht ein Wert ausgegeben sondern eine weitere Wenn Dann Funktion geprüft. Wenn die letzten beiden Ziffern kleiner als 50 sind handelt es sich um "S" ansonsten trifft tatsächlcih "Q" zu.

Da die Funktionen LINKS und RECHTS eigentlich für Varianten eines Strings gedacht sind (Textbestandteile auslesen) habe ich mit * 1 in der Formel diese zur Sicherheit auch als Zahl behandelt. Da die Innenauftragsnummer aber auf jeden Fall eine Zahl sein sollte, kann dieser Part auch ausgelassen werden.

Im Ergebnis haben wir nun abhängig von der Innenauftragsnummer entweder S oder Q im Feld "QoS" stehen.

WENN ( SOWOHL ... ALS AUCH ... ) dann Funktion

Eine bessere Variante ist in folgender Abbildung dargestellt, an der auch der abweichende Syntax zu Excel ersichtlich ist.

Access Ausdrucks-Generatur mit Wenn Funktion

Während ich in Excel mit WENN(UND();"S";"Q") gearbeitet habe ist der Syntax bei Verschachtelungen mit WENN  und UND in Access etwas anders. Für unser Fallbeispiel würde die Aktualisierung wie folgt definiert werden:

Wenn
(
  Rechts([Ergebnistabelle]![Projekt];2)<=49
   UND
  Rechts(Links([Ergebnistabelle]![Projekt];3);1)<>4;
 "S";"Q"
)

Zur Erklärung:
Sofern die letzten beiden Ziffern des Innenauftrags < oder = 49 sind UND die dritte Stelle keine 4 ist, wird das Feld "QoS" mit den Wert "S" versehen, andernfalls mit "Q".

Diese Schreibweise ist natürlich wesentlich einfacher, allerdings muss man sich vom Syntax der Klammer bei UND () lösen, wie dieses in Excel erfolgen würde.

In beiden Varianten hat nun das Feld "QoS" abhängig von der Innenauftragsnummer im Feld Projekt eine entsprechende Werztuweisung und es können hier weitere Gruppierungen oder sonstige Auswertungen vorgenommen werden.

Hinweis: Aktuelle Buchempfehlungen besonders SAP Fachbücher sind unter Buchempfehlungen inklusive ausführlicher Rezenssionen und Bestellmöglichkeit zu finden.
Diesen und weitere Texte von Andreas Unkelbach finden Sie auf http://www.andreas-unkelbach.de


Samstag, 6. Februar 2016
10:04 Uhr

Excel: Verschiedene Möglichkeiten einen Mittelwert über Zahlen ohne Nullwerte zu ermitteln

Das Positive an Excel ist ja, dass es mehr als einen Weg gibt um per Formel das gewünschte Ergebnis zu erhalten So hatte ich eine Tabelle von Kostenstellen bei denen für einen bestimmten Wert ein Durchschnitt gebildet werden soll. Es handelte sich um zehn Kostenstellen und insgesamt vier Werten. Wobei bei den anderen Kostenstellen eine 0 ausgewiesen wurde.

Um nun einen Durchschnittwert für diesen Betrag zu erhalten, war mein erster Gedanke, dass ich die Formel Mittelwert verwende. Das Ergebnis brauchte, durch die Berücksichtigung der 0 bei einzelnen Kostenstellen nicht das von mir vorgesehene Ergebnis.

Anhand der abgebildeten Tabelle wird dieses deutlich:

Zehn Einzelwerte, davon teilweise welche mit 0

In der Zelle B12 habe ich als Formel =MITTELWERT(B2:B11) eingetragen und als Durchschnittswert 80,00 € erhalten. Allerdings liegen ja die Einzelwerte allesamt über den Durchschnitt und für eine Planung wäre dieses dann eher ein ungünstiger Planansatz.

Die Formel MITTELWERT stellt mathematisch das arithmetische Mittel dar.  Hierbei wird eine Summe über alle Einzelwerte gebildet und diese durch die Anzahl der Werte dividiert. Entsprechend werden hier auch die Nullwerte berücksichtigt.

Um nun aber einen Mittelwert ohne 0 Werte zu berechnen, muss ich bei der Anzahl der Werte die 0 ausschliessen.

Eigentlich gibt es hier verschiedene Lösungsansätze von denen ich einige vorstellen mag.
 

Anzahl Werte ohne Null

Meine erste Überlegung war es die Summe über die Einzelwerte zu bilden und dann in Excel die Anzahl der Werte zu ermitteln, die nicht 0 sind.

Die Formel ANZAHL war mein erster Ansatz, allerdings gibt es hier nicht die Möglichkeit, ein einschränkendes Argument einzugeben.

Durch einen Beitrag auf clevercalcul  "So lässt sich die Funktion SUMMENPRODUKT nutzen, 13 Fälle" bin ich auf die Formel SUMMENPRODUKT gelandet, die auch weitere Argumente mit aufnehmen kann.

Hier ist eine ziemlich geniale Anwendung der Formel eine Überprüfung eines Bereiches mit zu übergeben wo die Werte ungleich 0 sind.

Die entsprechende Formel lautet:

=SUMMENPRODUKT((B2:B11<>0)*1)


Dabei wird ein WAHR als 1 und ein Falsch als 0 gezählt, so dass ich hier die Summe aus 0,1,0,1,0,0,1,0,1,0 mit 1 multipliziere und damit 4 erhalte.

Begeistert über diese Anwendung hatte ich diese ebenfalls im Kommentar eingegeben und wurde direkt auf zwei weitere, tatsächlich einfachere, Möglichkeiten hingewiesen.

Durch die Formel ZÄHLENWENN werden die nichtleeren Zellen eines Bereiches gezählt, deren Inhalt mit den Suchkriterium übereinstimmen. Konkret lautet die Formel dann:

ZÄHLENWENN(B2:B11;"<>0")

Wenn ich nun einen dieser beiden Werte dann als Divisor (Nenner) und die Summe der Einzelwerte als Dividend (Zähler) nehme  erhalte ich durch Summe / Anzahl zu berücksichtigten Werte das von mir eigentlich erhoffte Ergebnis, welches auch in folgender Abbildung ersichtlich ist.

Ermittlung der Anzahl an Werten, die nicht 0 sind

Insgesamt erhalte ich bei beiden Formeln dann als Durchschnittswert 200, was schon eher meiner Vorstellung entspricht.

Mittelwert ohne 0 Werte

Mit etwas Abstand zu dieser Berechnung gibt es allerdings auch in Excel nicht nur die indirekte Berechnung eines passenden Durchschnitt sondern auch zwei Möglichkeiten einen Mittelwert ohne Null zu berechnen.

Die einfachste Möglichkeit wäre hier die Formel MITTELWERTWENN
Konkret würde diese wie folgt aufgebaut sein:

MITTELWERTWENN(B2:B11;"<>0";B2:B11)

Hier wird tatsächlich nur ein Mittelwert gebildet, wenn die einzelnen Zellen keine 0 enthalten. Diese Formel ist ab Excel 2007 verfügbar. Davor war es erforderlich eine Matrixformel zu erstellen.

Hierzu wird die Formel

MITTELWERT(WENN(B2:B11<>0;B2:B11))

eingegeben und muss mit STRG, UMSCHALTTASTE (Shift) und EINGABETASTE (Enter) in eine Matrixformel umgewandelt werden.

Die Formel wird von Exceel darauf zwischen zwei geschweiften Klammern { und } als Matrixformel dargestellt. Eine direkte Eingabe der Formel mit geschweiften Klammern vor und hinter der Formel konvertiert die Formel jedoch nicht in eine Matrixformel, weswegen diese direkt über die Tastenkombination abgeschlossen wird. Dieses gilt auch, wenn die Formel nachträglich geändert wird und dann erneut mit STRG + UMSCHALT (Shift) + EINGABETASTE (Enter) abgeschlossen werden muss.

Zur Verdeutlichung dient folgende Abbildung:
Mittelwert ohne Null durch Mittelwertwenn oder Matrixformel

In jeden Fall ist hier dann tatsächlich der Mittelwert über die Einzelwerte 100, 200, 400 und 100 genommen werden und dürfte für einen Planansatz tatsächlich sinnvoller als das arithmetische Mittel von 80 sein.

Zur besseren Lesbarkeit der Formel, sollte ich aber tatsächlich künftig MITTELWERTWENN verwenden, da dieses dann auch schon anhand der Funktion beschreibt, welchen Durchschnitt ich hier berechnen möchte. Die verschiedenen mathematischen Mittelwertbetrachtungen (inkl. Häufigkeiten und Ausreißer) habe ich bei meiner Überlegung nun bewust ausgeblendet sondern lediglich das arithmetische Mittel ohne NULL betrachtet. Wie sagte mal ein Kollege "Als Controller sollte man nicht mehr als die Grundrechenarten anwenden +, -, / und * reichen in der Regel für 95 % aller Probleme aus...." . Insgesamt hat mir die Überlegung, trotz umständlichen Weg, sehr viel Spaß gemacht und auch der Austausch mit Gerhard Pundt von clevercalcul war hier sehr hilfreich, obgleich ich letzte Woche tatsächlich etwas durcheinander war und sein Beispiel statt ZÄHLENWENN mit ZÄHLEWENN versucht hatte nachzuvollziehen und über die Fehlermeldung erstaunt war... was manchmal so ein Buchstabe eben ausmacht ;-).

Hinweis: Aktuelle Buchempfehlungen besonders SAP Fachbücher sind unter Buchempfehlungen inklusive ausführlicher Rezenssionen und Bestellmöglichkeit zu finden.
Diesen und weitere Texte von Andreas Unkelbach finden Sie auf http://www.andreas-unkelbach.de


Sonntag, 25. Oktober 2015
15:15 Uhr

Excel Pivottabelle Darstellung Grenzwerte Einnahmen auf Projekte je Person durch Zuordnung VZÄ auf verantwortlicher Kostenstelle

Die Einnahmen eines Jahres werden auf Innenaufträgen erfasst, die über die verantwortliche Kostenstellen einzelnen Abteilungen zugeordnet sind.  In jeder Abteilung beziehungsweise auf jeder Kostenstelle sind entsprechende Personen beschäftigt, die für diese Einnahmen verantwortlich sind. Diese sind auf den einzelnen Kostenstellen über die Kennzahl VZÄ oder FTE erfasst so dass im Rahmen einer Auswertung sowohl eine Liste der Personen auf Kostenstellen, als auch der Einnahmen auf einzelne Innenaufträge für den entsprechenden Zeitraum vor liegen.

Für das Jahr 2014 sehen die Werte in unseren Beispiel wie folgt aus:

Übersicht Jahr-Auftrag-Kostenstelle-Einnahmen-VZÄ

Anhand der Zahlen soll über einen Grenzwert in Höhe von 20.000 € bestimmt werden, welche Anzahl an Personen oberhalb dieses Wertes Einnahmen erzielt haben, welche unterhalb dieses Wertes Einnahmen erzielten und welche gar keine Einnahmen erzielt haben.

Durch Ausscheiden von Personen kann es auch passieren, dass (bedingt durch eine Stichtagsbetrachtung) dieser Schwellenwert für eine Kostenstelle tatsächlich überschritten wurde, aber im fraglichen Zeitraum gar keine Person auf dieser Kostenstelle beschäftigt war. Auch dieses soll entsprechend festgehalten werden.

Im ersten Moment bietet sich hier eine Pivottabelle an über die je Kostenstelle sowohl die Summe der Einnahmen als auch die besetzten Personen ausgewiesen werden.

Diese könnte dann wie folgt aussehen:

Pivottabelle Kostenstelle-Einnahmen-VZÄ

Hier sind nun je Kostenstelle die Summe der Einnahmen und auch die besetzten Stellen in Form der VZÄ zu sehen.

Nun könnte direkt Einnahmen / VZÄ manuell errechnet werden und die entsprechende Fragestellung beantwortet werden. Eine schönere Alternative ist es natürlich, wenn Excel selbst hier eine passende Antwort geben würde.

Die Schwierigkeit dabei ist jedoch, dass an manchen Kostenstellen tatsächlich Einnahmen erzielt werden, aber 0 Personen beschäftigt sind. Dieses würde bei einer einfachen Berechnung die Fehlermeldung #DIV/0! herovrrufen.

Um dieses zu vermeiden besteht in Excel allerdings auch die Möglichkeit über entsprechende Formeln diese Fehler abzufangen.

Hierzu verwenden wir, wie im Artikel "Excel Berechnete Felder in Pivottabellen" beschrieben die Möglichkeit innerhalb einer Pivottabelle eigene Felder ergänzend zu den Grunddaten anzulegen.

Hierzu legen wir insgesamt fünf berechnete Felder an, wobei das erste ein Hilfsfeld ist, welches wir in der späteren Pivottabelle ausblenden bzw. nicht in der Feldliste aufführen werden.

In der erstellten Pivottabelle wird über Ribbon "Optionen" in der Befehlsgruppe "Berechnung" über "Felder, Elemente und Gruppen" ein "Berechnetes Feld" eingefügt.

Pivottabelle berechnetes Feld einfügen

Im folgenden Fenster geben wir die Formel für das Feld Einnahmen_VZÄ ein.
Berechnung Einnahmen je Person bzw. VZÄ

Durch die Formel WENNFEHLER werden entweder die Einnahmen je VZÄ durch Division ermittelt, oder wenn die Kostenstelle keine VZÄ (und somit keine Person vorhanden ist) direkt die Einnahmen ausgegeben. Dieses ist nachher noch wichtig  um ermitteln zu können, welche Kostenstelle die Wertgrenze überschritten hat, obgleich keine Person beschäftigt war.

Im nächsten Schritt definieren wir ein Feld, dass die Anzahl der Personen der Kostenstelle ausgeben soll, sofern der Wert Einnahmen_VZÄ (also Einnahmen pro Person) oberhalb oder gleich des definierten Grenzwertes in Höhe von 20.000 liegt.

= WENN(UND( Einnahmen_VZÄ>20000-0,01; VZÄ>0,1); VZÄ;0)

Die hinter diesen Wert liegende Formel lautet:

= WENN(UND( Einnahmen_VZÄ>20000-0,01; VZÄ>0,1); VZÄ;0)

Innerhalb der WENN Funktion wird entweder der Wert VZÄ ausgegeben, wenn sowohl die Einnahmen größer als 20.000 - 0,01 sind und die VZÄ größer als 0,1 ist.

Auf ebensolche Weise geben wir noch folgende Felder ein die ich als Formelliste aufgeführt habe.
Liste berechneter Formeln

Dieser  Überblick über die berechneten Felder kann ebenfalls im Ribbon Optionen der Pivot-Tabelle unter "Berechnung" bei "Felder, Elemente und Gruppen" durch die Option "Formeln auflisten" erstellt werden.

Formeln auflisten

Hierdurch wird ein neues Tabellenblatt angelegt in dem alle berechnete Formeln aufgeführt sind.


Der Übersicht halber sind die einzelnen Formeln folgender Tabelle zu entnehmen:
 

FELD FORMEL
Liste berechneter Felder
Einnahmen_VZÄ = WENNFEHLER(Einnahmen /VZÄ;Einnahmen )
VZÄ_>Grenzwert
= WENN(UND(Einnahmen_VZÄ >20000-0,01;VZÄ >0,1);VZÄ;0)
ohneVZÄ_>Grenzwert = WENN(UND(Einnahmen_VZÄ>20000-0,01;VZÄ<0,1);VZÄ;0)
VZÄ_<Grenzwert = WENN(UND(Einnahmen_VZÄ >0,1;Einnahmen_VZÄ<20000-0,01;VZÄ>0,1);VZÄ;0)
VZÄ_ohne-Einnahmen =WENN(UND(Einnahmen_VZÄ <0,1;VZÄ >0,1);VZÄ;0)

Im Ergebnis haben wir nun eine Pivottabelle in der auch die einzelnen Ergebnisse auf Ebene der Person gewertet werden. Durch eine bedingte Formatierung, können die einzelnen Spalten, sofern diese > 0,1 sind auch farblich hervorgehoben werden. Ferner besteht die Möglichkeit auch eine passende Summenformel in der Tabelle aufzuführen.

Dieses ist am Einfachsten über Ribbon Start innerhalb der Gruppe Formatierung, durch Bedingte Formatierung und hier die "Regel zum Hervorheben von Zellen" über die Bedingung "Größer als" möglich, wie in der folgenden Abbildung zu sehen.

Bedingte Formatierung zum Hervorheben Einnahmen oberhalb Grenzwert je Person

Ebenso kann auch mit den anderen Feldern verfahren werden, so dass im Ergebnis auf einen Blick die entsprechenden Personen ersichtlich sind. Die Beschriftung der einzelnen Felder kann in der Wertfeldeinstellung noch angepasst werden.

Darstellung Einnahmen nach VZÄ und Berücksichtigung Grenzwert

Die auf diese Weise erhobenen Daten können dann, zum Beispiel in Form eines Mehrjahresvergleich, als ein 3D-Säulendiagramm dargestellt werden, so dass die einzelnen Daten hintereinander dargestellt werden.

3D-Säulen Diagramm

Dieses 3D-Säulendiagramm ist vergleichbar erstellt worden, wie im Artikel "Gestapelte Säulendiagramme in Excel mit Summenausweis"  beschrieben. Die hinterste Spalte (Gesamtzahl) wurde ausgeblendet, so dass in der unter den Diagramm befindlichen Tabelle die Gesamtzahl der VZÄ ausgegeben werden kann, ohne dass diese als separate "sichtbare" Säule im entsprechenden Diagramm dargestellt wird. Durch die 3D Darstellung ist auch eine entsprechende Entwicklung in den Einnahmen / Person ersichtlich. Allerdings kann nur in der oberen (Pivot)Tabelle abgelesen werden um welche Kostenstelle es sich handelt, da das Diagramm lediglich eine Gesamtsicht über alle relevanten Kostenstellen liefert.
 

Nachtrag:

Wesentlich eleganter kann natürlich auch der GRENZWERT als berechnetes Feld gepflegt werden, so dass die Formeln wie folgt anzupassen sind. Künftig brauchen dann nicht mehr die einzelnen Formeln angepasst werden sondern lediglich das berechnete Feld "GRENZWERT".
bersicht halber sind die angepassten Formeln folgender Tabelle zu entnehmen:
 
FELD FORMEL
Liste berechneter Felder
Einnahmen_VZÄ = WENNFEHLER(Einnahmen /VZÄ;Einnahmen )
GRENZWERT = 200000
VZÄ_>Grenzwert
= WENN(UND(Einnahmen_VZÄ >GRENZWERT-0,01;VZÄ >0,1);VZÄ;0)
ohneVZÄ_>Grenzwert = WENN(UND(Einnahmen_VZÄ>GRENZWERT-0,01;VZÄ<0,1);VZÄ;0)
VZÄ_<Grenzwert = WENN(UND(Einnahmen_VZÄ >0,1;Einnahmen_VZÄ<GRENZWERT-0,01;VZÄ>0,1);VZÄ;0)
VZÄ_ohne-Einnahmen =WENN(UND(Einnahmen_VZÄ <0,1;VZÄ >0,1);VZÄ;0)

Das Ergebnis wäre identisch, jedoch müsste für andere Bereiche die Formel nur im berechneten Feld Grenzwert angepasst werden.
Dieses kann besonders dann interessant sein, wenn der Grenzwert entsprechend häufig angepasst werden muss. Leider ist es jedoch nicht möglich den Grenzwert durch einen Feldbezug festzulegen, da keine Tabellenbezüge, Namen und Arrays innerhalb Pivottabellenformeln unterstützt werden.

Datenanalyse mit Excel

Weitere Artikel rund um die Datenanalyse mit Excel sind auch in den Artikeln "Datentrends für Drittmittelstatistik mit Sparklines ab Excel 2010 darstellen durch Liniendiagramme in Zellen", "Excel: bedingte Formatierung mit Pfeilen (Darstellung Tendenzen bei Veränderungen)" und "Gestapelte Säulendiagramme in Excel mit Summenausweis" zu finden.
 

Hinweis: Aktuelle Buchempfehlungen besonders SAP Fachbücher sind unter Buchempfehlungen inklusive ausführlicher Rezenssionen und Bestellmöglichkeit zu finden.
Diesen und weitere Texte von Andreas Unkelbach finden Sie auf http://www.andreas-unkelbach.de


Montag, 24. August 2015
20:48 Uhr

Serienmails über Serienbrieffunktion in Winword per Outlook, Thunderbird oder anderen Mailprogramm versenden

Die Serienbrieffunktion von Microsoft Office Winword eignet sich nicht nur zum Erstellen von gedruckten Serienbriefen sondern ermöglicht es auch, in Kombination mit einen entsprechenden Mailprogramm (überraschenderweise mittlerweile nicht nur mit OutlooK), Serienmails zu versenden. Da mir diese Funktion noch nicht bekannt war mag ich diese hier kurz vorstellen.

Vorraussetzungen für Serienmails


Für die Nutzung der Mailfunktion ist ein MAPI-kompatibles E-Mail-Programm erforderlich. Mittlerweile unterstützt auch Mozilla Thunderbird die Schnittstelle MAPI , so dass nun nicht nur über Microsoft Exchange Server oder Outlook entsprechende Mails versandt werden können, sondern auch andere Mailprogramme, sofern Sie diese Schnittstelle unterstützen, hier kompatibel sind.

Dieses war in der Vergangenheit wohl nocht nicht der Fall, deswegen bin ich angenehm über die Erkenntniss überrascht, dass nun auch mit alternativen Mailprogrammen ein Versenden von individuellen Mailtexten per Serienmailfunktion möglich ist.

Ideal funktioniert daher die Kombination von Outlook und Winword von Microsoft. Hier müssen jedoch beide aus der gleichen Office Version stammen. So  muss bei der Verwendung von Microsoft Office  Winword 2007 auch tatsächlich Microsoft Office Outlook 2007 verwandt werden.

Mittlerweile wird die Schnittstelle MAPI aber auch von anderen Mailprogrammen sehr gut unterstützt, so dass ebenfalls ein Versand über Mozilla Thunderbird möglich ist. Wichtig ist hierbei, dass das entsprechende Mailprogramm als Standardanwendung zum Versand von E-Mail eingerichtet wurde. Ferner sollte darauf geachtet werden, dass ein Konto als Standardmailkonto festgelegt wird. Gerade wenn man mehrere Mailkonten hat, ist es sicherlich sinnvoll hier noch einmal vorab die entsprechenden Einstellungen zu kontrollieren.

Im folgenden Beispiel sollen Anmeldebestätigungen zu einer Schulung an einzelne Teilnehmende versandt werden.
 

Datengrundlage vorbereiten

Sind die Softwareanforderungen gegeben so kann sich als erstes Gedanke um die Grunddaten für die Versendung einer Mail gemacht werden. Als Beispiel soll zu einer Schulung eingeladen werden zu der schon die Liste der Teilnehmenden vorliegt. Diese ist wie folgt in Excel angelegt worden (siehe Abbildung).

Excel Tabelle mit Serienmaildaten
Um die Erstellung der Serienmail leichter zu machen, wurde direkt die korrekte Anrede als Spalte mit angegeben. Insgesamt stehen in der Exceltabelle nun folgende Felder zur Verfügung:
  • Anrede
  • Name
  • Mail
  • Schulung
  • Termin
Diese Tabelle kann, nachdem alle Empfänger (Schulungsteilnehmer) erfasst worden sind, gespeichert werden und gleich als Grundlage für den Mailversand genutzt werden.

Serienmails in Winword erstellen

Grundsätzlich funktioniert das Erstellen von Serienmails vergleichbar zur Erstellung von Serienbriefen in Winword. Durch Wechseln zum Ribbon "Sendungen" kann aus der Befehlsgruppe "Seriendruck starten" die Funktion "Seriendruck starten" aufgerufen werden. (siehe Abbildung).
Winword Ribbon Sendungen - Befehlsgruppe Seriendruck starten

Neben der direkten Auswahl welche Art von Seriendruck erstellt werden soll, bietet sich hier der "Seriendruck-Assistent mit Schritt für Schritt Anweisung" an (der auch in der Abbildung ausgewählt wurde.
Seriendruck Assistent mit Schritt für Schritt Anleitung

Der Assistent wird nun in der rechten Spalte von Winword eingeblendet und es besteht die Möglichkeit in sechs Schritten die Serienmailfunktion auszuwählen.

Hier bietet es sich an die einzelnen Schritte durchzuklicken (durch Auswahl und danach den Link "Weiter").

Schritt 1: Dokumententyp auswählen


Durch die Wahl von "E-Mail-Nachrichten" wird nun kein Ausdruck erstellt sondern eine Mail an jeden einzelnen Datensatz versandt. Sofern ein Serienbrief gedruckt werden soll kann die folgende Anleitung aber auch dafür genutzt werden.

Schritt 1 - Dokumententyp - E-Mail-Nachrichten

Schritt 2: Startdokument wählen


Im Schritt 2 wird nachgefragt, ob das aktuelle Dokument oder eine Vorlage bzw. anderes Dokument als Grundlage für die Mail verwendet werden soll. Es bietet sich an hier "Aktuelles Dokument verwenden" zu wählen, sofern man noch kein anderes Dokument erstellt hat. Letzteres bietet sich zum Beispiel an, wenn Sie ein aufwändiges Formular entwickelt haben oder einen Briefkopf oder vergleichbares verwenden wollen.

Schritt 3: Empfänger auswählen


Im Schritt 3 können die Empfänger der Serienmail gewählt werden. Sofern schon eine Liste vorhanden ist, wie es bei uns durch obige Excel-Tabelle der Fall ist, bietet es sich an den Punkt vorhandene Liste zu wählen und über Durchsuchen die entsprechende Tabelle auszuwählen.

Schritt 3 Vorhandene Liste verwenden (Datenquelle)

Als Datenquellen kommen eine Vielzahl von Formaten in Frage, sofern die Funktion "Neue Liste eingeben" gewählt wird, erstellt Winword eine entsprechende Access-Datenbank. Sinnvoller ist es aber sich hier vorher ein paar Gedanken gemacht zu haben, gerade wenn es um Felder wie Anrede oder zur Auswahl stehenden weiteren Feldern geht.

Wie schon oben erwähnt haben wir eine recht einfache Tabelle als Grundlage genommen in der schon Anrede, Name, Mail, Schulung und Termin festgelegt sind. Natürlich können hier auch wesentlich komplexere Tabellen verwendet werden. Hierbei sollte aber gerade bei Datumsfeldern darauf geachtet werden, wie das Datum in der Tabelle hinterlegt ist. Persönlich habe ich hier die Formatierung Text gewählt, so dass bei Verwendung des Feldes keine Umformatierung in der Mail erfolgt.


Standardmäßig werden als Speicherort von Datenquellen  unterhalb des Benutzerordners im Verzeichnis  AppData -> Roaming -> Microsoft -> Queries angeboten. Allerdings kann dieser auch geändert werden. In unseren Fall wird die Excel-Tabelle Schulungsteilnehmer als Grundlage für den Mailversand genommen.

Nun erscheint eine Auswahl der in dieser Tabelle vorhandenen Tabellenblätter. Dabei sind sowohl Erstellungsdatum, als auch Datum der letzten Änderung ersichtlich.

Tabelle auswählen - Erste Datenreihe enthält Spaltenüberschriften

Hier haben wir glücklicherweise das Tabellenblatt entsprechend umbenannt, so dass hier direkt das Blatt "Anmeldungen Schulung" ausgewählt werden kann. Wichtig ist darüber hinaus noch, dass der Punkt "Erste Datenreihe enthält Spaltenüberschriften markiert wurde, so dass später leichter mit den einzelnen Feldern gearbeitet werden kann.

Im darauf folgenden Dialog können die Seriendruckempfänger ausgewählt werden.
Seriendruckempfänger
Durch Setzen des Hacken neben den ersten Datenfeld können einzelne Empfänger ausgewählt werden. Dieses hat den Vorteil, dass Sie auch erst einmal eine Testmail an sich selbst senden können und nicht gleich an alle Schulungsteilnehmenden die Anmeldebestätigung versenden.

Gerade bei der Verwendung von E-Mails kommt man doch viel zu leicht auf den Sendebutton, ohne dass man dieses eigentlich zu diesen Zeitpunkt wollte... ;-). Dieses kann besonders unangenehm sein, wenn die letzten Änderungen in der Grundtabelle noch nicht erfolgt sind.
 

Schritt 4: Schreiben Sie Ihre E-Mail-Nachricht

Im vierten Schritt kann nun die eigentliche Mail formuliert werden. Über die Funktion weitere Elemente können einzelne Felder der Datentabelle als Platzhalter eingefügt werden, so dass diese dann später mit den einzelnen Daten der Tabelle gefüllt werden.

Mailtext mit Seriendruckfelder verfassen


Auf diese Weise können Sie nun ihre indidviduelle Mail schreiben und dabei die einzelnen Felder aus der Tabelle verwenden. Im oberen Beispiel ist hier ein sehr einfacher Mailtext gewählt worden. Normalerweise würde ich mindestens noch die eigenen Kontaktdaten als Signatur ergänzen.

Ferner ist es sicherlich auch sinnvoll noch weitere Informationen zum Veranstaltungsort und sonstige Angaben zu ergänzen, was aber für dieses Beispiel dann doch eher irrelevant sein dürfte.
 

Schritt 5: Vorschau auf ihre E-Mail-Nachrichten


Im Schritt 5 erhalten wir eine Vorschau auf den Mailtext, in der dann auch alle Seriendruckfelder mit einen Beispieldatensatz gefüllt sind. Im Beispiel wäre dieses eine Bestägigung an mich selbst, dass die Schulung statt finden wird.

Mailtextvorschau und Empfängernavigation

Hierbei kann zwischen den einzelnen in Schritt 3 gewählten Empfängern gewechselt werden. Dieses ist insbesondere bei der Wahl der richtigen Anrede hilfreich aber auch um vorab schon einmal zu kontrollieren, ob alle Felder tatsächlich korrekt gefüllt wurden.

Schritt 6: Mail versenden


Zum Abschluss kann nun die Funktion "Fertigstellen und zusammenführen" beziehungsweise Zusammenführen "E-Mail.." ausgewählt werden.

Seriendruck in E-Mail

Die einzelnen Empfänger werden im An: Feld des Mailprogramms eingetragen.
Die Betreffszeile ist bei allen Mails identisch, allerdings wird sich der Mailtext, je nach ihren im Schritt 4 festgelegten Text unterscheiden. Da diese Funktion auch mehrfach aufgerufen werden kann schadet es nichts, wenn im ersten Anlauf nur an den ersten Datensatz (in der Regel ein Testfall mit ihrer eigenen Mailanschrift) versandt wird.

Eine wichtige Funktion ist noch das Format in dem die Mail versandt werden soll. Sofern kein reiner Text (Fettdruck Graphiken etc.) verwendet worden bietet sich entweder HTML oder als Anlage an. Andernfalls kann auch "Nur-Text" als Mailformat gewählt werden.

Serienmailfunktion Microsoft Office Winword und Mozilla Thunderbird

Je nach Mailprogramm wird nun direkt die Mail an die entsprechenden Adressaten versandt. Sollten Sie ebenfalls Mozilla Thunderbird in einer aktuellen Version verwenden erhalten Sie noch folgende Sicherheitsabfrage:

Sicherheitsabfrage vor Versenden von Mails
Sobald Sie diese bestätigt haben werden direkt ihre Mails versandt und die Schulungsteilnehmenden erhalten eine individuelle Schulungseinladung. Die Mail landet dann direkt im Postfach und kann von den einzelnen Teilnehmenden ausreichend bewundert werden und per Antwort auf die Einladung reagiert werden.

Es sollte hierbei vor den Versand jedoch darauf geachtet werden, ob der eigene Mailserver ab einer bestimmten Anzahl von ausgehenden Mails nicht auf Grund von Spamverdacht eine Zustellung verweigert. Hier kann es eventuell hilfreich sein, wenn die Zahl der Empfänger in einzelne Gruppen aufgeteilt wird. Für den ein oder anderen dürfte noch interessant sein, dass anhand des Quelltext der Mail nicht ersichtlich ist, dass dieses eine Massenmail per Serienbrieffunktion war.

Absendermailkonto wählen

Sofern Sie mehr als ein Mailkonto betreiben (bspw. auch eine Funktionsmailadresse) wird das Standardkonto zum Versand der Mails genommen.

In Outlook können Sie diese Einstellung unter DATEI->INFORMATIONEN->KONTOEINSTELLUNGEN ändern und ein zweites Konto hinzufügen. Ferner kann es für Outlook 2010 erforderlich sein, dass Sie zusätzlich im Reiter Datendatei auch das jeweilige Postfach des Mailkonto zum Versand der Serienmails ebenfalls als Standard wählen.

Eine pragmatische Alternative wäre es noch, dass Sie Absendername und Absenderadresse ihres regulären Mailkonto entsprechend ändern würden.
Diese Einstellung ist sowohl für POP3 Konteon (PST Dateien) als auch für Exchangekonten (OST Dateien) möglich, jedoch nicht für IMAP Konten.

 

Hinweis: Aktuelle Buchempfehlungen besonders SAP Fachbücher sind unter Buchempfehlungen inklusive ausführlicher Rezenssionen und Bestellmöglichkeit zu finden.
Diesen und weitere Texte von Andreas Unkelbach finden Sie auf http://www.andreas-unkelbach.de


<< Frühere Einträge Spätere Einträge >>
Hinauf




Werbung


Logo Andreas-Unkelbach.de
© 2004 - 2018 Andreas Unkelbach
Andreas Unkelbach

Stichwortverzeichnis
(Tagcloud)


Aktuelle Infos (Abo)

Facebook Twitter Google+

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

Kaffeekasse 📖 Wunschliste