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:



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.



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:


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 ;-).

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:



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:



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.



Im folgenden Fenster geben wir die Formel für das Feld Einnahmen_VZÄ ein.


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.



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.


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.



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:
 

Liste berechneter Felder

FELD	FORMEL
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.



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.

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.



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:
 

Liste berechneter Felder

FELD	FORMEL
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.
 

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).


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).


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.


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 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.



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.



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.

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.




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.



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.



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:


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.

An dieser Stelle können Sie über die Schaltfläche "Als Standard festlegen" in der Kontenliste das entsprechende Mailkonto im Register E-Mail als Standardkonto festlegen. Sinnvoll kann dieses zum Beispiel für eine Funktionsmailanschrift sein.

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.

Unter Outlook 2016 (ggf. auch vorher) sollte unter DATEI->OPTIONEN in den Outlook-Optionen unter E-MAIL im Abschnitt Nachrichten senden die Option "Beim Verfassen neuer Nachrichten immer das Standardkonto verwenden" aktiviert werden. Andernfalls wird das zu letzt genutzte Konto zum Versand genutzt.

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.

 

Seit Excel 2007 sind im Ribbon Start unter der Symbolgruppe " Formatgruppen" hier werden bestimmte Eigenschaften zusammengefasst, so dass auf einen Mausklick bestimmte Vorlagen auf Zellen angewendet werden können. Diese empfinde ich besonders geeignet, da hierdurch problemlos offene Punkte oder kritische Werte mit der Notwendigkeit von Korrekturen hervorgehoben werden können.



An gleicher Stelle ist auch die bedingte Formatierung zu finden auf die im Artikel "Excel: bedingte Formatierung mit Pfeilen (Darstellung Tendenzen bei Veränderungen)" näher drauf eingegangen ist. Neben der optischen Hervorhebungen von Formeln (Formatvorlage "Berechnungen") oder auch Hervorhebung von positiven Ergebnissen (Gut) oder kritischen zu prüfenden Elementen ("Schlecht") sind diese Formatvorlagen auch sehr praktisch um noch offene Eingaben die in einer Übersicht noch ergänzt werden müssen, durch die Formatvorlage "Eingabe" gekennzeichnet werden können.

Gerade bei umfangreichen Listen ist hier eine weitere Funktion noch sehr praktisch. in Verbindung mit der Filterfunktion. Die Sortierenfunktion ist im Ribbon "Daten" zu finden und in der Gruppe "Sortieren und Filtern" kann ein entsprechender Filter (über das Trichter Symbol über Daten gesetzt werden. Im Artikel "Mehrere Autofilter im Tabellenblatt einer Exceltabelle setzen" hat sich ja schon ein Beispiel ergeben, warum ich recht gerne mit der Filterfunktion arbeite.

Ein weiterer Vorteil dieser Funktion ist, dass nicht nur nach Zahlenwerten sondern auch nach Farben sortiert werden kann. So kann, sofern ich die Formatvorlage "Eingabe" verwendet habe, je Spalte nach notwendigen Eingaben gefiltert werden und genau diese fehlende Daten noch ergänzt werden.

In der Abbildung wird die Spalte D nach allen als "Eingabe" formatierten Zellen gefiltert.



Auf diese Weise werden dann auch nur die Lehreinheiten beziehungsweise  Studiengänge  dargestellt bei denen noch eine Ergänzung erforderlich ist.

In unseren Beispiel wird hier also nur noch der "Studiengang 1B" dargestellt.



Gerade wenn man mit mehreren Personen an einer Excelarbeitsmappe arbeitet sind solche Kleinigkeiten sehr hilfreich um schnell einen Überblick zu erhalten oder um auf wesentliche Punkte hinzuweisen.

Ein weiteres Anwendungsgebiet wäre bei der bedingten Formatierung nach Zellensymbolen zu filtern, wie in unterer Abbildung dargestellt wird.



Gerade bei der Auswertung von Auffälligkeiten sind solche farblichen Hervorhebungen (oder auch Symbole) sehr nützlich und man kann sich dank dieser Filter dann um die wesentlichen zu klärenden Punkte kümmern. Sofern in einer Tabelle auch noch eine Farblegende eingefügt ist ergibt sich hier eine praktische Funktion um auf bestimmte Auffälligkeiten hinzuweisen. Besonders umfangreiche Berichte (als Beispiel seien hier die "Rechercheberichte im Modul FI (Bilanzanalyse)" genannt) haben oftmals einzelne Positionen zu denen eine Rückfrage erforderlich ist oder auch das Einfügen eines erläuternden Kommentars sehr hilfreich sein kann. Auf diese Weise funktioniert dann auch das abteilungsübergreifende Arbeiten zum Beispiel zwischen der Finanzbuchhaltung und Controlling 

Ausgangslage:
Für einen Dreijahresbericht wurden bisher Jahresdaten für drei Jahre dargestellt und jedes Jahr aufs neue für ein Jahr aktualisiert. Hierbei wurden die "Altdaten" einfach ausgeblendet bzw. der Druckbereich in Excel passend gemacht.

So wurden die Ergebnisse auf Ebene Fachbereich, Lehreinheit und als kleinste Ebene Studiengang wie folgt dargestellt:



Für die Dreijahressicht (Berichtstermin ist Anfang 2016) sollen die Jahre 2013 bis 2015 dargestellt werden. Hierzu sind in den folgenden Spalten die aktuellen Jahresdaten einzutragen und die Vorjahresspalten auszublenden beziehungsweise durch Gruppierung nicht darzustellen. Entsprechend wären hier nur die Spalten A sowie D bis F für den Bericht dargestellt worden. Die Jahresdaten für 2016 sind dann in der Spalte G künftig zu ergänzen.

Da das Berichtswesen mehr in eine kaufmännische Sicht übergeführt werden soll, wurde der Wunsch geäußert alle Berichtsdaten nun absteigend darzustellen, so dass Aktuelles Jahr, Vorjahr und VorVorjahr dargestellt werden sollen (2015, 2014, 2013). Eine solche Darstellung entspricht der umgekehrten Reihenfolge und würde ein Aktualisieren der Daten erschweren, sofern man in der entsprechenden Tabelle auch die Vorjahresdaten weitehrin erhalten lassen möchte.

Damit die Datenhaltung weiterhin erfolgen kann, wurde hier die Tabelle ein wenig abgeändert und um die Funktion WVERWEIS ergänzt.

Verweisfunktionen in Excel

Grundsätzlich gibt es in Excel drei Verweisfunktionen, wobei die SVERWEIS Funktion vermutlich die bekannteste ist.

Einen Grundlagenartikel zum Thema Verweisfunktionen habe ich im Artikel "Grundlagen in Excel Verweisfunktionen SVERWEIS WVERWEIS und VERWEIS" ausführlicher vorgestellt. Worauf ich nach einer kurzen Zusammenfassung noch einmal hinweise:

Als kurze Zusammenfassung hier einen kleinen Überblick.


1. VERWEIS Funktion
Dabei ist die Formel/Funktion VERWEIS die Grundfunktion und ist am dynamischsten in der Anwendung. Ein Anwendungsbeispiel wurde im Artikel "Prüfung inwieweit ein Wert, bspw. eine Kostenstelle, innerhalb eines Intrevalls (Gruppe) liegt in Excel" schon einmal dargestellt.

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.

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.

Eine ausführliche Beschreibung der unterschiedlichen Verweis-Funktionen ist im Artikel "Grundlagen in Excel Verweisfunktionen SVERWEIS WVERWEIS und VERWEIS" zu finden. Hier ist auch auf eine Alternative mit INDEX und VERGLEICH verwiesen worden.
 

Anwendungsbeispiel WVERWEIS Funktion (Jahresdaten umsortieren)

Was bedeutet dieses nun aber für unsere Ursprungstabelle?
Hier haben wir die Tabelle um 4 Spalten (im Beispiel die Spalten B bis E) erweitert und die Spalten F bis J über die Symbolleiste (Ribbon) Formeln über die Funktion Namen definieren die Bezeichnung "Jahresdaten_Studis" zugewiesen. Ferner sind in der Spalte E die einzelnen Zeilen  der Datentabelle (Jahresdaten_Studis) durchnummeriert worden.


Der definierte Name mit Bezug auf F1 bis J10 kann in den folgenden Jahren immer wieder erweitert werden, soll aber für unser Beispiel die Matrix der einzelnen Jahre sein und anhand der WVERWEIS Funktion für die Darstellung der letzten drei Jahre in den Spalten B bis D genutzt werden.



Die Formel in der Zelle B2 lautet wie folgt:

=WVERWEIS(B$1;Jahresdaten_Studis;$E2;falsch)

Hierdurch wird fix die Zeile 1 als Suchvektor über die Matrix "Jahresdaten_Studies" genutzt um anhand des Zeilenindex (in der Spalte E) die genaue Übereinstimmung (falsch) auszugeben. Hierbei wird als Suchkriterium das entsprechende darzustellende Geschäftsjahr genutzt. Durch Setzung des Druckbereichs auf =$A$1:$D$10 werden dann auch tatsächlich nur die relevanten Daten mit ausgedruckt aber es ist weiterhin möglich die aktuellen Daten als "neue" Spalte einzufügen.

Durch die  mit $ markierten absoluten Bezüge (Zeile $1, Jahresdaten_Studies; Spalte $E) kann die Formel auch in den anderen Zellen kopiert werden und per Inhalte einfügen Formel entsprechend automatisch von Excel angepasst werden.

Im konkreten Beispiel sehen die Formeln wie folgt aus:


Jede Zelle verweist dabei auf das für sie relevante Jahr in der Zeile 1 und durchsucht die Jahresdaten_Studis. Ferner soll als Ergebnis die in der Spalte E angegebene Ergebniszeile ausgegeben werden.

Mit aktivierter Umbruchvorschau kann das Ergebnis der Formel dargestellt werden.



Auf diese Weise werden die Studierendendaten in absteigender Jahresfolge dargestellt und außerhalb des Druckbereichs können aktuelle Daten erfasst werden. Hierdurch sind auch komplexere Entscheidungen wie Wechsel der Jahresdarstellung unter Beibehaltung der Grunddaten mit relativ wenig Aufwand umzusetzen.