Nachdem ich, gemeinsam mit einigen anderen von mir gern gelesenen Bloggern die Frage "Wenn du nur noch 5 Excel Formeln verwenden dürftest, welche wären das?" auf excel-koenig.de beantwortet hatte hat sich das Thema Design und Formatierung von Tabellen besonders im Artikel "Die 10 Excel Gebote" von Excel-Koenig.de oder auch die rs-1 Methode im Artikel "So arbeitest Du in Excel mit der rS1.Methode" auf clevercalcul wieder einmal in den Vordergrund gedrängt und dabei mich wieder daran erinnert, dass ich hier gerne meine eigenen Tabellen hier etwas ausführlicher betrachten mag. Daneben möchte ich auch den Artikel "Meine Daten – meine Informationsquelle: Wichtige Tipps für das Reporting" von Ing. Katharina Schwarzer (soprani.at) empfehlen, wo ebenfalls das Thema angesprochen wird.

In diesem Zusammenhang ist mir auch eine Möglichkeit Vorlagen in Excel angesprochen. Persönlich habe ich dieses bezogen auf Powerpoint und Excel schon gerne genutzt und im Artikel "Microsoft Office Vorlagen und Änderungsverfolgungen" beschrieben.

Allerdings hat tabellenexperte.de eine Anleitung unter "Excel-Quickie Nr. 3: Standard-Vorlagen" angelegt in der entsprechend diese Vorlagen ebenfalls angesprochen werden.

In den Kommentaren bin ich auf den Gedanken gekommen, dass hier in der Fußnote der Dateiname und Pfad zur Datei hinterlegt werden kann und werde sicherlich sowohl die Mappe.xltx als auch Tabelle.xltx im Autostartverzeichnis von Excel anlegen und mir hier eine entsprechende Vorlage basteln.

Gerade für ausgedruckte Versionen ist es hilfreich, wenn hier der Pfad zur Datei auf Dauer hinterlegt wird.

Dateiname und Dateipfad als Fußzeile einfügen

In Excel kann im Ribbon Seitenlayout in der Befehlsgruppe "Seite einrichten über die Schaltfläche "Drucktitel" im Register "Kopfzeile/Fußzeile" über die Option "&[Pfad]&[Datei]" einen Pfad zu hinterlegen.



Hier ist auch direkt die Schaltfläche eingefügt werden.



Ebenso besteht die Möglichkeit direkt in einer Zeile über die Formel
=ZELLE("dateiname")
eintragen.

Zur Verdeutlichung hier diese Formel als Formel sowie als Ergebnis:



Auch hier eignet sich diese Formel als Element und das Ergebnis ist auch direkt zu sehen.
 

Dateiname und Pfad als festen Wert in die Fußzeile hinterlegen per Makro

Im Gespräch mit Kolleginnen und Kollegen eignet sich diese Methode tatsächlich für ausgedruckte Versionen von Dateien. Allerdings wird dieser Dateipfad regelmäßig aktualisiert, so dass es sich hier eher empfehlenswert scheint, entweder das Ergebnis der obigen Formel erneut als Text einzufügen oder aber als Makro hier den Dateiname und Dateipfad direkt als Text ohne Veränderung festzulegen.

Hier war der Gedanke folgendes Makro zu hinterlegen:

Sub DateipfadundName_in_Fußzeile()
Dim i As Integer
For I = 1 To Sheets.Count
Worksheets(I).Activate
With ActiveSheet.PageSetup
.LeftFooter = ThisWorkbook.FullName
End With
Next I
End Sub

Leider ist es nicht möglich dieses Makro aus der persönlichen Makroarbeitsmappe auszuführen, da in diesen Fall nur der Pfad zur PERSONAL.XLSB hinterlegt wird. Dennoch mag ich gerne auf den Artikel "Excel Umgang mit Makros und Visual Basic for Applications (VBA)" hinweisen.

Grundsätzlich empfinde ich ein durchdachtes Design von Tabellenblättern als sehr hilfreich und verweise auch hier auf den Artikel "Formulare gestalten in Excel". Vermutlich wird hier auch die eingangs erwähnte Antwort zu den fünf wichtigsten Formeln in Form eines Artikel noch geschrieben werden. Allerdings sind die letzten Wochen durch ein anderes großes Thema zum Thema SAP noch in der Arbeit ist.

Im Rahmen eines Finanzberichtswesen werden verschiedene Projekte nach ihren verschiedenen Merkmalen ausgewertet und sollen je Fachbereich nach Kostenstelle und Finanzierungszweck (Finuse) einzelne Projekte mit ihren jeweiligen Salden aus mehreren Datenquellen zusammengestellt werden. Basis für die Auswertung sind in den Artikeln "»Rechercheberichte de lux« im Modul PSM FM Haushaltsmanagement" beziehungsweise "Drittmittelstatistik nach LOMZ über Recherchebericht und SAP Query" ausgewertet werden.

Was ist ein Finanzierungszweck?
Die Rolle des Feldes Finanzierungszweck im SAP Modul PSM ist in den beiden Artikeln "PSM-FM Grundlagen Finanzierungszweck im Haushaltsmanagement bei Recherchebericht und Selektion", "Gruppierung von Finanzierungszwecken bei Drittmittelprojekten per Zusatzfeldcoding mit IF oder CASE" beschrieben.

Die entsprechende Grundtabelle (Datengrundlage) sieht dabei im Ausschnitt und sehr vereinfacht wie folgt aus:


 

Pivottabelle klassisches Layout anlegen

Der naheliegende Gedanke diese Tabelle mit einer Pivottabelle (siehe auch Artikel "Pivottabellen ab Excel 2010 dynamischer filtern mit Datenschnitten am Beispiel Hochschulfinanzstatistik" ) anzulegen.

Im Ergebnis sieht eine eingefügte PivotTabelle dann wie folgt aus:



Die Darstellung der Werte Fachbereich, Kostenstelle, Finuse und Auftrag auf einer Ebene ist durch die Pivottabellen-Optionen (rechte Maustaste auf die Pivottabelle) und hier der Reiter Anzeige und die Option Klassisches PivotTabellen-Layout festgelegt worden.



Ferner sind für die einzelnen Zellen keine Teilergebnisse festgelegt worden.

Geplant ist nun eigentlich für die einzelnen Fachbereiche die Ergebnisse je Kostenstelle zu kopieren und als Tabelle zur Verfügung zu stellen.

Hier gab es dann jedoch die Rückmeldung, dass die leeren Zellen unterhalb der mehrfach vorkommenden Kostenstelle aufgefüllt werden sollten. Leider ist mir keine Option in den Pivottabellen bekannt, dass sich hier die Gruppierung wiederholen lässt. Daher hilft hier eine kleine Formellösung weiter.

Vor der Pivottabelle wurden daher vier weitere Spalten eingefügt und dabei mit einer Formel die Fachbereich, Kostenstelle und Finuse (Finanzierungszweck) aufgefüllt.

Zellenbeschriftungen per Wenn Funktion automatisch auffüllen

Die automatische Auffüllen der leeren Zellenbeschriftungsfelder ist über eine WENN Funktion gelöst:



Die Formel prüft ob die Pivottabellenzelle einen Wert hat (im Beispiel F3 ungleich leer sprich "") um dann den entsprechenden Eintrag einzfügen, andernfalls wird der Wert eine Zelle oberhalb dieser Formel eingetragen. Da die Formel nach unten ausgefüllt wird, wird dann tatsächlich immer der entsprehcende Wert ergänzt so dass hier die Zellenbeschriftungen ebenfalls nach unten ausgefüllt wird.

Die Formeln sehen dabei wie folgt aus:



in Zelle  A3 wird dabei auf das Feld D3 in der Pivottabelle Bezug genommen und durch die Formel =WENN(d3<>"";d3;a2) hier würde auf jeden Fall ein Wert vorhanden sein, aber shcon in Zelle A4 wird durch die Formel =WENN(d4<>"";d4;A3) der Wert aus A3 ausgewiesen, wenn hier kein Wert in der Pivottabelle steht.

Hierbei sind dann tatsächlich alle Kostenstellen und FInanzierungszwecke ergänzt und die Tabelle ist etwas besser lesbar.. Eleganter kann dieses aber mit einer bedingten Formatierung erfolgen.



Durch die Regel "Werte formatieren, für die diese Formel wahr ist" wird geschaut, ob der Eintrag mit der Zelle drüber identisch ist.

Hier kann die Schriftfarbe in einen Grauton dargestellt werden, so dass sich wiederholende Werte entsprechend absetzen, wie am Beispiel des FB 03 ersichtlich ist.



Hier zeigt sich erneut wie sinnvoll die Verwendung der bedingten Formatierung zum schnellen Erfassen von Daten genutzt werden kann.

Weitere Beispiele für die Anwendung von bedingten Formatierungen können unter "Excel: bedingte Formatierung mit Pfeilen (Darstellung Tendenzen bei Veränderungen)" oder auch im Artikel "Leistungsmengen im Grundbudget je Fächergruppe (Cluster) im Vergleich oder bedingte Formatierung für Minimalwerte und Maximalwerte" betrachtet werden.

Insgesamt ist diese Formellösung eine echte Erleichterung im Vergleich des manuellen Auffüllen der leeren Tabellenzellen.
 

Eigentlich entwerfe ich ja lieber Berichte und setze mich mit den Daten selbst auseinander aber hin und wieder ist es auch erforderlich diese vernünftig als Folien zu präsentieren.

Präsentationen für Schulungen oder zur Dokumentation

Dieses kann sowohl als Ergebnis einer Auswertung sein (in der eben nicht nur Diagramme dargestellt werden sollen) , Schulungen zu "Grundlagen Kurzeinführung und Handbuch SAP Query", "Grundlagen Kurzeinführung und Handbuch Report Painter Report Writer" oder auch einer Schulung rund um Excel ganz im Sinne des Artikel "Unterschiedliche Auswertungsmöglichkeiten im Controlling (Report Writer, Recherchebericht, SAP Query) und natürlich Excel ;-)" oder auch einfach für sonstige Vorträge.

Durch die Reduktion auf einzelne Folien kommt man oft auch eher auf den entscheidenden Punkt als in einer 295 seitigen Dokumentation.

Natürlich gibt es auch an Powerpointvorträgen Kritik (besonders wenn man eine der mitgelieferten Vorlagen verwendet und die Präsentation optisch immer wieder gleich aussieht und sich nur das Thema ändert, aber auf der anderen Seite sollte man nie das Werkzeug beschimpfen, wenn das fertige Werk nicht gefällt. Ich kenne auch noch Personen die einen kompletten Vortrag verteilt auf einzelne Exceltabellen basierend vortragen können und auch Menschen die ihren gesamten Schriftverkehr in Excel abwickeln, aber wenn man sich einmal mit Powerpoint (oder einer anderen Präsentationssoftware) auseinander gesetzt hat kann dies tatsächlich eine große Arbeitserleichterung sein. Selbstvertändlich ist auch der Vortrag am Whiteboard oder mit Kreide und Tafel auch heute noch verbreitet.... :-) Alternativ kann hier auch der klassische Vortrag am Whiteboard sehr hilfreich sein.

Alternative Vortragsmethoden

Hier kann ich besonders den "Workshop Bewerbung 3.0 – Sebstpräsentation in Social Media" auf der Seite / Blog berufundkarriereseite.de empfehlen :-). Wobei ich das Thema Mindmap und Sketchnote eher in einen anderen Artikel (siehe "Mindmapping und Sketchnotes im Beruf nutzen für Brainstorming oder Mind Mapping mit XMIND") behandeln würde.

Trotzdem kann auch mit Software ein gutes Ergebnis erzielt werden und mittlerweile schätze ich diese Software sehr und nutze diese auch gerne im beruflichen Alltag.

Nun aber tatsächlich zu Powerpoint

In den folgenden Abschnitten möchte ich einige Punkte im Zusammenhang mit der Erstellung einer Präsentation erwähnen, die mir schon ein wenig weiter geholfen haben.
 

Office Vorlage

In vielen Unternehmen wird oft schon eine Vorlage für Präsentationen zur Verfügung gestellt die dann in Unternehmensfarben gestaltet ist und auch sonst einige Vorteile bietet.
Zumindest mir hat eine solche Vorlage sehr geholfen und mittlerweile arbeite ich mit Powerpoint beinahe ebenso gerne wie mit Excel oder anderen Werkzeugen die dann doch die tägliche Arbeit um ein kleines Stückchen Kreativität erweitern.

Die Vorteile von Microsoft Office Vorlagen an zentraler Stelle habe ich schon im Artikel "Microsoft Office Vorlagen und Änderungsverfolgungen" näher erläutert. Gerade bei Powerpoint ist es hier sehr praktisch über DATEI->NEU "Meine Vorlagen" eine entsprechende Präsentaion mit Layoutvorgaben zu verwenden und diese dann tatsächlich für unterschiedliche Zwecke nutzen zu können.

Im Artikel "Schnelle Präsentation – Arbeiten Sie mit einer Vorlage" hat Ing. Katharina Schwarzer einen sehr guten Überblick über die Möglichkeiten einer Vorlage dargestellt. Besonders bei Fragen zu Office kann ich hier das Blog Soprani Software empfehlen, dass einige "Tipps aus der Feder (besser: Tastatur) der Expertin zu den wunderbaren Excel-Word-Access-PowerPoint-Outlook-InfoPath-OneNote-Lync-Feature"  nahezu täglich veröffentlicht. :-) Die Beschreibung ist so passend, dass ich diese einfach einmal kopiert habe ;-).
 

Layoutvorgaben durch Masterfolie

Bei der Gestaltung einer Präsentation ist es sinnvoll sich vorab über das Layout Gedanken zu machen. Hierfür gibt es im Register ANSICHT in der Befehlsgruppe MASTERANSICHTEN die Schaltfläche FOLIENMASTER um hier das Masterlayout festzulegen. Nun sind auf der linken Seite Folienmaster und Layoutfolien zu finden.
Dabei wird das Masterlayout (oberste Folie) als Grundlage für alle Layoutfolien, die weiter unten ausgegliedert sind hervorgehoben.Die Layoutfolien sind dann als Ergänzung für das Layout vorgesehen.
 

Abschnitte einfügen um Präsentation zu gliedern

Nachdem die einzelnen Folien ein Layout zugewiesen bekommen haben und der Inhalt eingefügt wurde kann es sehr sinnvoll sein zwischen einzelnen Folien einen Abschnitt durch die rechte Maustaste einzufügen.

Durch die Abschnitte  während der Präsentation über die rechte Maustaste auf durch "GEHE ZU ABSCHNITT" zum Anfang des Abschnitt gewechselt werden.



Dieses ist sehr parktisch um während einer Präsentation oder in der Fragerunde nochmals auf ein Thema zurückgreifen zu können.

Daneben hilft es aber auch uneinheitliche Folien in der Ansicht FOLIENSORTIERUNG", welche im Ribbon Ansicht in der Befehlsgruppe Präsentationsansichten zu finden ist, einen Überblick über die Präsentation zu behalten.

Autokorrektur des Textformat

Powerpoint selbst passt bei Textfeldern allerdings automatisch die Größe des Textes sowohl vom Zeilenabstand als auch von der Textgröße an, wenn mehr Text eingetragen wird. Daher ist es sinnvoll die Formatierung des Textfeld mit der rechten Maustaste im Master anzuklicken und bei FORM FORMATIEREN unter Textfeld die Option bei "AUTOMATISCH ANPASSEN" auf "Größe nicht automatisch anpassen" umzustellen.



Ebenso kann diese Einstellung für die gesamte Präsentation in der Autokorrektur unter DATEI -> OPTIONEN -> DOKUMENTENPRÜFUNG ->AUTOKORREKTUR
im Reiter "Autoformat während der Eingabe" im Abschnitt "Während der Eingabe übernehmen" durch Deaktivieren der beiden Punkte "Titeltext und Untertiteltext an Platzhalter automatisch anpassen" abgestellt werden.

Gerade wenn man selbst dazu neigt Einzelfolien mit Textwüsten vollzukleistern kann es hilfreich sein, hier in der Präsentationsvorlage eine entsprechende Schriftgröße eingestellt zu haben und so tatsächlich zu vermeiden, dass die Texte nicht zu viel Platz in der Folie einnehmen.
 

Änderungen überprüfen

Gerade wenn man im Team eine Präsentation bearbeitet hat ist es interessant zu erfahren, welche Ändeurngen hier vorgenommen worden sind. Hier bietet zwar Powerpoint keinen Änderungsmodus wie zum Beispiel in Winword (siehe Abschnitt  "Arbeiten mit der Änderungsnachverfolgung bzw. die Funktion Änderungen überprüfen") aber im Ribbon ÜBERPRÜFEN gibt es in der Befehlsgruppe Vergleichen die Schaltfläche "VERGLEICHEN" durch diese kann eine andere Präsentation als Datei ausgewählt werden und diese wird mit der aktuellen Präsentation verglichen und kombiniert.

Durch den "Überarbeitungsbereich" kann nun jede Änderung (Löschen von Folien, Textänderungen etc.) nachvollzogen werden.



Dieses ist besonders dann praktisch wenn die Präsentation recht umfangreich ist und nicht eine direkte Abstimmung zwischend en Vortrageneden erfolgen kann.
 

Countdown oder ist die Präsentation bald vorbei

Früher hatte ich gerne in der Masterfolie ebenfalls Seite X von Y in der Kopfzeile eingetragen. Grundsätzlich ist dieses eine schöne Idee, allerdings gibt es nicht die Möglichkeit die Gesamtzahl der Seiten automatisch per Feld eingeben zu können, so dass die Gesamtfolienanzahl direkt eingetragen werden muss und schlimmstenfalls dort Folie 31  von 25 zu lesen wäre.... was natürlich sehr selten passiert aber dann doch peinlich sein kann.

Gerade bei besonders spannenden Präsentationen, als Beispiel sei hier eine Schulung zum Thema SAP Query erwähnt die von mir vor einigen wenigen Kolleginnen und Kollegen gehalten wurde, kann eine Endanzahl von Folien auch dafür sorgen, dass die Teilnehmenden das Ziel vor Augen haben und nur noch die verbleibenden Zahl von Folien abzählen, bis die Präsenation fertig ist... dieses kann dann ebenfalls zu ein wenig Hetze und Stress auf beiden Seiten führen.

Besser ist es daher entweder die Folien gar nicht zu nummerieren (oder bspw. falls die Folien später als Handout verteilt werden sollen) nur die Foliennumemr mit ausgegeben wird.

Animationen

Auf Animationen oder Musikuntermalung verzichte ich eigentlich immer, da ich hier wenig künsterlerisches Gespür habe und oft auch nicht die Übergänge mit der entsprechenden Performance hinbekomme. Dafür bin ich mittlerweile ein absoluter Fan davon, dass die Powerpointpräsentation auch als PDF gespeichert werden kann und entsprechend weiter gegeben werden kann. Genauso wenig traue ich mich ja auch heutzutage animierte GIF und die Schriftart Comic Sans MS auf Internetseiten zu verwenden....

Wobei dieses auch eine persönliche Geschmacksfrage und abhängig vom eigentlichen Thema ist. Ich habe schon unheimlich gute Präsentationen mitbekommen die einfach am Whiteboard gehalten werden.

 

Diagramme in Powerpoint einfügen

Sehr häufig habe ich Diagramme oder Tabellen in Excel entworfen und möchte diese dann "einfach" in eine bestehende Präsentation einfügen. Leider ist hier dann ein entsprechendes Farbschemata gewählt worden, so dass hier die Tabelle angepasst wird oder sich die Farben des Diagramm ändern. Sofern ich per STRG + V ein Diagramm einfüge wird das Zieldesign der Präsentation verwendet und Arbeitsmappe eingebettet.

Wesentlich angenehmer ist es daher per rechte Maustaste über die Einfügeoptionen

die zweite oder vierte Option (Ursprüngliche Formatierung beibehalten und Arbeitsmappe einbinden oder Daten verknüpfen) zu wählen. Die letzte Option bindet die Daten als Bild ein, was allerdings Nachteile hat wenn die Daten skaliert oder angepasst werden sollen.

Trotzdem kann auch dieses eine sinnvolle Option sein. 

Der eigentliche Vortrag

Oftmals fällt mir im letzten Moment ein, dass für einen Vortrag noch der Beamer bestellt werden muss, die Präsentation auf einen USB Stick gehört und schlimmstenfalls auch noch an einen fremden Rechner die Präsentation gehalten werden soll.

Hier kann dann der Hinweis auf Präsentationsmodus bei Windows (zur Darstellung eines Bildschirms am Beamer und den Rest am Laptop ganz hilfreich sein. Die entsprechende Tastenkombinationen sind im Artikel "Hilfreiche Tastenkombinationen unter Windows" aufgelistet.

Rechtliche Aspekte

Ein vielleicht nicht immer naheliegender Aspekt sind die rechtlichen Fragen rund um eine Präsentation. Hier sind die beiden Beiträge von Rechtsanwalt Dr. Thomas Schwenke inklusive Handlungsempfehlungen sehr hilfreich.

• "Präsentationsfolien: Bild- und Fotorecht – die Basics im Überblick"
• "Urheberrecht und Präsentationsunterlagen – Pflichtwissen für Vortragende & Veranstalter"

Gerade bei der Verwendung von Fotos ist diese eine Überlegung wert ob nicht doch lieber mit anderen Elementen der Vortrag aufgelockert werden kann.
 

Inhaltliche Gestaltung der Präsentation

"Content is King" gilt nicht nur im Bereich Webdesign sondern noch viel mehr bei Präsentationen. So lustig auch Cliparts sind so sehr können diese doch oft vom eigentlichen Inhalt ablenken. Eine gute Präsentationsvorlage unterstreicht in meinen Augen den Inhalt und liefert einen Rahmen ohne dabei selbst in den Mittelpunkt gesetzt zu werden. Daher bin ich sehr begeistert wenn ein Veranstalter oder ein Unternehmen eine entsprechende Vorlage zur Verfügung stellt die auf der einen Seite die Corporate Identity (CI) widerspiegelt aber auf der anderen Seite auch den dargestellten Inhalt klar präsentiert.

Dennoch kann auch eine Bildsprache und eindrucksvolle Präsentation überzeugen. Hier möchte ich als Anregung den Artikel "PowerPoint kann auch anders Tipps und Tricks für überzeugende Vorträge"  aus der CT 18 / 2013 empfehlen und sei es nur als Denkanstoß oder Faszination was auch möglich ist. Hier sind auch andere Präsentationsformen, bspw. mit PREZI vorgestellt.

Als Basis für ein Hochschulbudget werden bestimmte Leistungsmengen erfasst und diese mit einem Preis bewertet und so eine Abgeltung zugewiesen. So wird zwischen Grundbudget (zur Finanzierung der Grundleistung der Hochschule) und Erfolgsbudget (mit verschiedenen Leistungsindikatoren als bekanntestes Beispiel dürfte das Drittmittelvolumen sein) unterschieden. Dabei werden je Hochschule Leistungsmengen erfasst und diese mit Preisen bewertet.

Als Beispiel für das Grundbudget sind hier zum Beispiel als Leistungsmenge die Studierende in Regelstudienzeit je Fächergruppe (Cluster)  und je nach Hochschulart (vereinfacht gesagt FH und Uni) mit unterschiedlichen Clusterpreisen bewertet.

So könnte eine Gegenüberstellung der einzelnen Hochschulen wie folgt aussehen:



Wobei dieses einen Mittelwert an Studierende in Regelstudienzeit je Fächergruppe (Cluster) entsprechen die dann mit einem Clusterpreis multipliziert werden und eine entsprechende Leistungsabgeltung erhalten.

Neben Grundbudget gibt es dann noch Erfolgsbudget, welches andere Paramter beinhaltet und diese dann ebenfalls entsprechend bewertet werden. Zum Erfolgsbudget können zum Beispiel Parameter wie:

• Drittmittelvolumen
• Berufung von Frauen
• Absolvent_inn_en
• Promotionen
• oder auch andere Parameter

genutzt werden. So kann das Erfolgsbudget auch in Teilbudgetgruppen mit eigenen Parametern zusammengefasst werden. Beispiele sind Teilbudget Forschung und Nachwuchs, Gender, Lehre und Internationalisierung mit eigenen Leistungsmengen, Prämien und entsprechender Leistungsabgeltung

Da die Zahlen relativ kleinteilig sind ist hier die Frage, wie die entsprechenden Minimalwerte und Maximalwerte je Hochschulart festgelegt werden. Im oberen Beispiel sollen jedoch nur die Parameter für das Grundbudget (Studierende in Regelstudienzeit) betrachtet werden und dabei die höchsten und niedrigsten Werte je Cluster und Hochschulart hervorgehoben werden.

Hierzu wird die bedingte Formatierung in Excel genutzt. Da sowohl für die Universitäten als auch für die Hochschulen für angewandte Wissenschaften (HAW im Beispiel noch mit der "alten" Bezeichnung FH angegeben) eigene Minimalwerte und Maximalwerte angelegt werden sollen ist es sinnvoll je zwei bedingte Formatierungen in der Zelle B2 und der Zelle E2 anzulegen und diese dann per Format übertragen auf die Zellen B2:D11 bzw E2:G11 zu übertragen.

Hierzu wird im Ribbon Start die Schaltfläche bedingte Formatierung in der Befehlsgruppe Formatvorlagen aufgerufen. Im Beispiel ist dieses dann der Punkt "Neue Regel" wie in der Abbildung zu sehen.



Als Option wird nun der Punkt "Formel zur Ermittlung der zu formatierenden Werte verwenden". Im Beispiel für die Zelle B2 soll der Minimalwert im Cluster je Universität mit roten Hintergrund hervorgehoben werden.




Daneben soll für die Zelle B2 auch der Maximalwert im Cluster je Universität mit einen grünen Hintergrund hervorgehoben werden. Hierzu sieht die Formel wie folgt aus:



Hierzu wurde unter der Schaltfläche Formatieren im Register "Ausfüllen" eine dezente rote beziehungsweise grüne Hintergrundfarbe gewählt.

Über die Schaltfläche "Format übertragen" (Pinselsymbol) kann dieses auch auf alle anderen Leistungsmengen der Universitäten übertragen werden.

Ähnlich kann auch für den Bereich der Hochschulen für angewandte Wissenschaft (ehemals Fachhochschulen hier als FH bezeichnet) eine entsprechende Hervorhebung erfolgen.

Im Ergebnis sieht dann die Tabelle mit der entsprechenden Hervorhebung der höchsten und der niedrigsten Leistungsmengen wie folgt aus:



So lassen sich auf einen Blick auch entsprechende Potentiale in den Hochschulen sehen oder auch Schwerpunkte in den einzelnen Hochschulen ausmachen. So scheint die Uni A hervorragend im Bereich der Naturwissenschaften zu sein, während die Uni B ihren Schwerpunkt in den Rechts- und Wirtschaftswissenschaften beziehungsweise Sozialwissenschaften zu haben scheint.
 

Formeln zur bedingten Formatierung

Maximalwert Universitäten in Zelle B2
Maximalwert mit grün	=UND(B2>0;B2=MAX($B2:$D2))
Minimalwert mit rot	=UND(B2>0;B2=MIN($B2:$D2))
Minimalwert Hochschule angewandter Wissenschaft in Zelle E2
Maximalwert mit grün	=UND(E2>0;E2=MAX($E2:$G2))
Minimalwert mit rot	=UND(E2>0;E2=MIN($E2:$G2))

Durch die relativen und festen Bezüge können die Formeln per Format übertragen problemlos auf die anderen Zellen der Hochschulen im jeweiligen Bereich übertragen werden.

Da Excel eine Zelle ohne Werte als 0 interpretiert soll die bedingte Formatierung auch nur angewandt werden, wenn überhaupt ein Wert vorhanden ist. Ansonsten wären bei der Hochschulart FH alle Felder im Cluster III der Geisteswissenschaften rot markiert.

Um nun zweitplatzierten oder weitere Werte hervorzuheben könnten statt MIN und MAX auch die Formeln KKLEINSTE für die entsprechende zweitkleinste, drittkleinste und so weiter Zahl verwendet werden. Hierdurch würde sich eine ganze Ampel darstellen lassen. Hier ist es aber sicherlich eleganter mit Farbskalen zu arbeiten. Wodurch echte Ampeln oder Farbverläufe dargestellt werden können.

Um auf einen Blick Schwerpunkte in den einzelnen Hochschulen festzustellen erscheint mir jedoch der Blick auf die Maximalwerte und Minimalwerte für einen direkten Vergleich der Leistungsmengen hiflreicher. Zumindest fällt es mir bei vielen Hochschulen leichter die beiden Werte optisch zu betrachten anstatt einen entsprechenden Farbverlauf zu interpretieren.

Bei einer Datenzeile erscheint mir das noch lesbar, aber bei mehreren Zeilen ist dieses für mich nicht mehr mit einen Blick erkennbar. Dennoch mag ich auch dieses Beispiel über alle Hochschulen zur Verdeutlichung anfügen.



Gerade in Verbindung mit Leistungsmengen kann hier Excel tatsächlich helfen um innerhalb einer Gruppe entsprechende Werte direkt zu vergleichen und bietet sich dabei natürlich für alle möglichen Werte an. Als Beispiel sind hier die Leistungsmengen eines fiktiven Haushaltsplan gewählt worden es kann diese Methode natürlich auch für andere Werte (von Energiemengen, Kosten, Erlöse, ...) genutzt werden. Insgesamt müssen solche Daten dann nur vorliegen beziehungsweise entsprechend erfasst werden.

Nachdem Einladungen per Serienmail versandt worden sind (siehe Artikel "Serienmails über Serienbrieffunktion in Winword per Outlook, Thunderbird oder anderen Mailprogramm versenden") kommt es an der Erfassung von Rückmeldungen.

Hierbei werden mit Farben folgende Rückmeldungen festgehalten worden:

• Liste mit Teilnahmegebühren und farbliche Hervorhebung ob die Gebühr bezahlt worden ist oder nicht.
• Liste mit Rückmeldungen ob an einer Veranstaltung teilgenommen wird oder eben nicht.

In beiden Fällen sind die Formatvorlagen Gut (grüne Hintergrundfarbe), Neutral (gelbe Hintergrundfarbe) oder auch Schlecht (rot/rosa Hintergrund) genutzt worden um einen Zustand zu definieren. Nun liegen zwar wunderschöne Farben in der Tabelle vor, aber eventuell sollen nun ja auch die Teilnehmenden gezählt werden, oder aber auch die gezahlten oder noch offenen Beträge zusammengefasst werden.

Nehmen wir als Beispiel einmal folgende Ausgangstabellen, wie in der Abbildung zu sehen ist.



Nun könnte man natürlich auf die Idee kommen die einzelnen Rückmeldungen oder auch Rechnungsbeträge per Filter, wie im Artikel "Vorteil von Excel Formatvorlagen und Filter nach Farben oder Zellensymbolen aus bedingter Formatierung" zu sortieren um dann ein entsprechendes Teilergebnis zu berechnen. Dieses ist dann allerdings unschön, da sich ja die Farben auch ändern können. Zum Beispiel könnte die Rechnung Kunde-A-02 ja doch noch bezahlt werden und der Rechnungsbetrag ist einfach untergegangen.

Hier hat mich ein Kollege auf die Excel4-Makrofunktionen ZELLE.ZUORDNEN hingewiesen. Eine solche Makrofunktion kann nicht direkt im Tabellenblatt genutzt werden sondern muss im Namensmanager als benannte Formel eingerichtet werden. Eine sehr gute umfassende Beschreibung ist von Frank Arendt-Theilen  im Artikel "Die Funktion ZELLE.ZUORDNEN() " veröffentlicht worden (der Artikel ist auch in der Microsoft Answers zu finden, aber mir ist die Verlinkung auf ein persönliches Blog immer lieber).

Frank Arendt-Theilen ist auch als Dozent unter anderen bei Video2brain als Autor tätig. Auf einige gute Schulungsvideos zu Excel (und andere Office Produkte) sind im Artikel "Video2brain - Onlineschulung per Videostreaming unter Android, Windows, iOS und Web" zu finden.

Das Schöne an dieser Funktion ist, dass hier nicht VBA aktiviert werden muss sondern diese direkt funktioniert. Allerdings muss in "neueren" Excelversionen (ab 2007) zur Nutzung der Makrofunktion die Datei als "Excel Arbeitsmappe mit Makros" (Dateiendung XLSM) gespeichert werden.

Nun aber zur tatsächlichen Lösung. Für Facebook Abonenten (siehe facebook.com/Unkelbach) ist dieses schon vor einigen Tagen angesprochen worden. Nun möchte ich aber die Lösung etwas ausführlicher beschreiben und zum Ende eine offene Frage stellen in der Hoffnung, dass vielleicht andere Excelblogs eine Lösung für diese Frage haben.

Über den Ribbon FORMELN kann in der Befehlsgruppe "Definierte Namen" der Namensmanager aufgerufen werden und hier zwei neue Namen definiert werden, wie in der folgende Abbildung schon dargestellt worden ist.



Zur besseren Lesbarkeit noch einmal beide definierte Namensfunktionen:
 

Namensmanager definierte Funktionen

Name	bezieht sich auf
Farbe_L1	=ZELLE.ZUORDNEN(63;INDIREKT("ZS(-1)";))
Farbe_Zelle	=ZELLE.ZUORDNEN(63;INDIREKT("ZS";))

Dabei wird über Farbe_L1 der Wert der Hintergrundfarbe der linken Zelle und über Farbe_Zelle der Wert der Hintergrundfarbe der aktuellen Zelle ausgegeben. Die indirekte Zuordnung von Zellen und Spalten anstatt direkt mit Werten zu arbeiten ist sehr verständlich (inklusive Hinweis auf internationale Excelversionen) im Artikel "#INDIREKT mit #Nummerierung" von Katharina Schwarzer auf soprani.at (Soprani Software @KatharinaKanns) beschrieben. Insgesamt sind nebenbei einige Twitteraccounts zu Excel sehr lesenswert, aber das nur am Rande.

Beschreibung ZELLE.ZUORDNEN

Die EXCEL4-Makroformel ZELLE.ZUORDNEN ist dabei wie folgt aufgebaut:
ZELLE.ZUORDNEN(Typ;Bezug)
Als Typ ist im oberen Beispiel 63 (Wert der Farbe für die Füllung (Hintergrund) einer Zelle und als Bezug ist wie beschrieben die Zelle eine Spalte (-1) der aktuellen Zelle oder aber die aktuelle Zelle selbst festgehalten.  Näheres dazu ist auch im Artikel von soprani.at zu finden.

Sofern die Vordergrundfarbe (Muster) abgefragt werden soll kann hier als Typ 64 gewählt werden.  Für die weiteren Argumente und weitere Typen verweise ich auf den Artikel von Frank Arendt-Theilen.

Jetzt können wir über =Farbe_L1 den Wert der Hintergrundfarbe einer Zelle links von der Formeleingabe ausgeben lassen.

Formeln automatisch auf markierte Zellen übertragen lassen (STRG + ENTER)

Hierzu markiere ich die Zellen D9:D16 sowie H9:H16  und trage als Wert in der Eingabezeile die Anweisung ein, dass hier die Hintergrundfarbe ausgegeben werden soll. Durch die Tastenkombination STRG und ENTER schliesse ich die Eingabe ab.

Auch wenn ich die Formel eigentlich in der Zelle H9 eingetragen habe, wird diese Formel automatisch auch in den anderen Zellen eingetragen.

Dieses ist eine Form des automatischen Ausfüllens durch das nicht Formatierungen überschrieben werden und gerade bei umfangreicher formatierten Tabellen für mich mittlerweile eine der Lieblingstastenkombinationen in Excel ist.

Im Ergebnis ist nun in der Spalte neben der Eingabe die Hintergrundfarbe als Zahlenwert stehen.



Hier kann nun per Zählenwenn oder SummeWenn im oberen Abschnitt die Teilnehmenden oder die Rechnungsbeträge ausgewiesen werden. Dabei kann natürlich auch in der Formel selbst ein Bezug auf die Zelle in der Legende per Farbe_L1 genommen werden.

ZÄHLEWENN Hintergrundfarbe der Zelle übereinstimmt

Um die jeweiligen Rückmeldungen zu zählen wird die Formel

=ZÄHLENWENN($D$9:$D$16;Farbe_L1)

in den Zellen C3 bis C6 eingetragen, so dass hier die übereinstimmende Hintergrundfarben gezählt werden.



So hat als Beispiel GUT den Hintergrundfarbwert 35, so dass hier die beiden Rückmeldungen von Andreas und Claudia gezählt werden. Die beiden negativen Rückmeldungen von Gustav und Heinrich werden natürlich ebenso gezählt.

SUMMEWENN Hintergrundfarbe

Ebenso kann natürlich auch eine Summe gebildet werden, wobei der Suchbereich die Zellen H9:H16 und der Summenbereich die Zellen G9:G16 sind (elegant wäre es natürlich hier ebenfalls mit Namen zu arbeiten ;-)).Hier lautet die Formel demnach in den Zellen G3:G6

=SUMMEWENN($H$9:$H$16;Farbe_L1;$G$9:$G$16)

wie auch in der Abbildung zu sehen ist.

Farbergebnis mit direkten Bezug auf Zelle

Wir hatten ja eingangs auch per Namensmanager Farbe_Zelle definiert. Dieses kommt in der Summenzeile nun im Einsatz und gibt direkt in der Zelle selbst die Summen bzw. Teilnehmenden aus.

So lautet die Formel für die Teilnehmende:

=ZÄHLENWENN($D$9:$D$16;Farbe_Zelle)

und für die Summe der Rechnungen;

=SUMMEWENN($H$9:$H$16;Farbe_Zelle;$G$9:$G$16)

und das gewünschte Ergebnis sieht dann wie folgt aus, wobei ich hier die Hilfsspalten D und H entsprechend ausgeblendet (bzw. Gruppiert) habe.


Im Ergebnis kann so also tatsächlich mit der Hintergrundfarbe in Excel gerechnet werden.
Natürlich ist der umgekehrte Weg vorhandene Zellen bedingt zu formatieren (siehe "Excel: bedingte Formatierung mit Pfeilen (Darstellung Tendenzen bei Veränderungen)") etwas pflegeleichter aber hier kann direkt mit entsprechend vorhandenen Formatierungen gearbeitet werden. Ausserdem sind Farben ja auch sprechende Informationen ;-) Ebenfalls ein Vorteil ist, dass fehlende Teilnehmende zum Beispiel Elisabeth oder Emil ebenfalls in der Liste ergänzt werden können und natürlich auch weitere Rechnungen in der Liste als bezahlt markiert oder auch andere Positionen ergänzt werden.
 

Offene Frage an andere Excelexperten

Leider habe ich es ohne Hilfsspalten nicht geschafft eine solche Berechnung hinzubekommen, würde mich aber sehr freuen, wenn als Kommentar eine entsprechende Lösung (gerne auch durch einen anderen Blogartikel auf den ich dann verlinken würde) ergänzt werden könnte. Ein variabler Index über die Hintergrundfarbwerte in Form einer Matrixfunktion wäre hier natürlich ein absoluter Königsweg, den ich aber leider nicht geschafft habe zu beschreiten.

Aber auch mit der Hilfsspalte selbst ist die Lösung für manche Anwendungsfälle schon sehr hilfreich. Besonders elegant ist diese Lösung auch für Einrichtungen bei denen VBA per Gruppenrichtlinie in Excel deaktiviert ist... wobei dadurch auch die Excelansicht in SAP nicht mehr funktioniert was dann aber ein anderes Problemfeld ist.

Nachtrag Makrofunktionen und Excel 2016:

Ein Kollege hat mich an dieser Stelle auf einen Artikel zum Thema "In Excel mit Farben rechnen" von Martin (tabellenexperte.de) hingewiesen.

Martin Weiß weist mich im Artikel zu den Kommentaren auf folgenden Sachverhalt aufmerksam gemacht:

Martin Weiß (tabellenexperte.de)"Was mich jedoch gerade viel mehr irritiert: Die Lösung mit der ZELLE.ZUORDNEN-Funktion scheint unter der aktuellsten Excel-2016-Version nicht mehr korrekt zu arbeiten. Es wird nur noch ein #BEZUG!-Fehler ausgespuckt. In der exakt gleichen Variante unter Excel 2007 läuft alles einwandfrei. Da wird doch nicht etwa Microsoft diese schöne Funktion eingestampft haben…?"

Darauf hatte ich damals auf folgenden Umstand hingewiesen:

Andreas Unkelbach: "Hallo Martin,

es scheint tatsächlich so zu sein, dass die Formel ZELLE.ZUORDNEN(63, ZELLE) noch funktioniert, so liefert mir zum Beispiel der Namensmanager mit ZELLE.ZUORDNEN(63, A2) die Hintergrundfarbe der Zelle A2. Allerdings scheint der indirekte Bezug mit =ZELLE.ZUORDNEN(63;INDIREKT(“ZS”;)) nicht mehr zu klappen…. was extrem schade ist.

Von daher könnte man zwar für die einzelnen Zellen eine Hintergrundfarbe ermitteln, aber es ist nicht mehr möglich bezogen auf die aktuelle Zelle die Hintergrundfarbe der versetzten Zelle auszulesen.

Vielleicht gibt es ja eine andere Bezugsformel, die hier ab Excel 2016 in Verbindung zur ZELLE.ZUORDNEN genutzt werden kann.

In Office 2013 scheint die Formel noch funktioniert zu haben siehe:
http://answers.microsoft.com/de-de/msoffice/wiki/msoffice_excel-mso_other/die-excel4-makrofunktion-zellezuordnen/6ee8af02-b52c-45b7-94ef-7f7bb7e45d88

Vielleicht hat es durchaus Vorteile nicht immer die aktuellste Excelversion zu nutzen ??

Verwirrte Grüße
Andreas
 

Vielen Dank an dieser Stelle an meinen Kollegen für den Hinweis auf obigen Artikel den ich gerne zum Anlass nehme um auf die Filterfunktion nach Farben und Teilergebnis, das beides auf elegante Weise ebenfalls ein Rechnen nach Farben ermöglicht.

Manchmal ist Excel wirklich spannend. im Artikel "Summieren nach Farbe mit ZELLEN.ZUORDNEN (ohne VBA)" ist Lukas Rohr (excelnova.org) ebenfalls auf diese Formel eingegangen aber unter Excel 2019 scheint diese wieder zu funktionieren:

Lukas Rohr "Mensch, ist ja spannend! Es scheint der Fehler wurde wieder behoben. Bei mir Excel 2019 (Office 365 Abo) Version funktioniert das jetzt wieder (also auch mit Argument 63)! Ich habe jetzt natürlich nicht jede Variante der Argumente durchprobiert, aber ich bin bis jetzt noch über kein solches Problem gestolpert."

Vielen Dank für diesen Hinweis..eigentlich müssste ich diese Formel nun auch unter Excel 2016 erneut testen in der Hoffnung, dass dank Update die Formel vielleicht doch wieder funktioniert....

Update 2020:
Unter Excel 2016 funktioniert die Formel wieder :-))) Offensichtlich ist hier durch ein Update die Makroformel wieder funktionierend.

An dieser Stelle muss ich übrigens meiner Frau zustimmen, die meinte als ich ihr erzählte, dass mich ein Kollege auf einen anderen Blogartikel hingewiesen hatte, den ich auch schon kommentiert hatte "Dein Internet ist ganz schön klein.. "  
 

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.