Andreas Unkelbach
Logo Andreas-Unkelbach.de

Artikel zum Stichwort Excel

Alle folgende Artikel sind unter den angegeben Stichwort (TAG) einsortiert. Sollte der gesuchte Artikel nicht dabei sein kann hier auch die Artikelsuche weiter helfen.

Oft sind aber auch die aktuellen Artikel in der jeweiligen Kategorie im Menü interessant.
Espresso Tutorial - die digitale SAP Bibliothek

SAP Fachliteratur ist unter Buchempfehlungen inklusive ausführlicher Rezenssionen und Bestellmöglichkeit zu finden.



Montag, 15. Mai 2017
21:30 Uhr

Grundlagen in Excel Verweisfunktionen SVERWEIS WVERWEIS und VERWEIS

Vor einiger Zeit bat mich ein Kollege darum die unterschiedlichen Verweisfunktionen in Excel einmal kurz darzustellen. Auch wenn ich in letzter Zeit mehr mit Diagrammen und Pivot-Tabellen arbeite (siehe zum Beispiel im Artikel "Datentrends für Drittmittelstatistik mit Sparklines ab Excel 2010 darstellen durch Liniendiagramme in Zellen") mag ich doch als kleine Grundlage für Excel die von mir gerne verwendeten Verweisfunktionen anhand eines kurzen Beispiels vorstellen.

Hierbei habe ich die drei Verweisfunktionen (SVERWEIS, WVERWEIS und VERWEIS) anhand eines kurzen Beispiels in Excel dargestellt.

Beispiel SVERWEIS

Die Funktion SVERWEIS sucht zeilenweise eine Matrix nach einen vorgegebenen Kriterium ab und liefert den Spaltenindex beim positiven Fund zurück.

Hierbei kann das Suchkriterium in eine Zelle stehen auf die entsprechend Bezug genommen wird.

Nehmen wir einmal eine konkrete Tabelle als Beispiel in der Kostenartengruppen als Spalten und Kostenstellen als Zeilen hinterlegt sind.

Die Tabelle sieht nun wie folgt aus:

Matrix SVERWEIS in Spalten Kostenartengruppen und in Zeilen Kostenstellen
In gelb ist hier die Matrix (in unseren Fall die Datentabelle hervorgehoben) über die einzelnen Spalten der Tabelle ist der Spaltenindex hervorgehoben (die erste Spalte ist 1, die zweite Spalte 2 und so weiter). Bei der Verwendung von SVERWEIS kommt der ersten Spalte eine besondere Bedeutung zu, diese ist gleichzeitig das Suchkriterium über das per SVERWEIS eine Kontrolle erfolgt. Hier muss also das Suchkriterium stets am Anfang der Spalte stehen. Die Ausgabe erfolgt anhand des Spaltenindex welcher in Pfeilrichtung dargestellt ist. Konkret möchten wir nun also für die Kostenstelle 102 den aktuellen Saldo erhalten. Dazu verwenden wir nun die SVERWEIS Funktion wie in folgender Abbildung zu sehen.

Formel SVERWEIS

Die Formel SVERWEIS ist wie folgt aufgebaut:

=SVERWEIS(Suchkriterium;Matrix;Spaltenindex;FALSCH)

Als erstes Argument wird das Suchkriterium übergeben (in unseren Beispiel die Kostenstelle 102, danach folgt die Matrix welche durchsucht wird (unsere Datentabelle nach Kostenstellen und Kostenarten), der Spaltenindex um anzugeben welche Spalte ausgegeben wird und als Abschlussparameter FALSCH damit nur genaue Übereinstimmungen ausgegeben werden sollen.

Im Ergebnis haben wir hier also tatsächlich den Saldo in Höhe von 78,00 für die Kostenstelle 102.

Beispiel WVERWEIS

Was ist aber wenn unser Suchkriterium nicht am Anfang der Zeile sondern in der Spalte steht? Hier kann die Formel WVERWEIS verwendet werden in der die Matrix anhand des Zeilenindex ausgegeben wird. Als konkretes Beispiel haben wir wieder eine Tabelle mit Kostenstellen als Zeilen und den Saldo des entsprechenden Geschäftsjahres als Spalten.

Beispiel Matrix WVERWEIS Jahr als Spalte und Kostenstelle als Zeile
Hier soll nun für die ein ausgewähltes Geschäftsjahr die einzelnen Werte der Kostenstellen ausgegeben werden. Das Ziel ist es zum Beispiel die einzelnen Salden für das Jahr 2015 auszugeben. Anhand des Geschäftsjahres ist auch schon ersichtlich, wann ich diese Dokumentation erstellt hatte ;-).

Die Formel ist dabei wie folgt aufgebaut:
Formel WVERWEIS
Hier ist nun das Suchkriterium das Geschäftsjahr und jede Kostenstelle hat einen eigenen Index zur Ausgabe der im Geschäftsjahr relevanten Salden.

Technisch betrachtet lautet die Formel wie folgt:

=WVERWEIS(Suchkriterium;Matrix;Zeilenindex;FALSCH)

Das Suchkriterium ist das in Zelle B22 angegebene Geschäftsjahr, die Matrix die Datentabelle und als Zeilenindex wird die Position (Zeile) der relevanten Kostenstelle angegeben. Selbstverständlich sollen im Controlling auch nur exakte Werte angegeben werden, daher ist abschliessend auch der Parameter FALSCH mit übergeben.

Beispiel VERWEIS

Was ist aber, wenn wir eine im Berichtswesen gar nicht so unübliche Tabelle mit einer Vielzahl von Spalten haben? Ist es hier tatsächlich erforderlich die einzelnen Zeilen oder Spalten abzuzählen nur um die relevanten Ergebnisse zu definieren?

Wie nicht anders zu erwarten ist die Antwort auf die Frage eindeutig NEIN. Hier kann die Funktion VERWEIS genutzt werden durch die die durchsuchenden Werte und die Ergebnisse tatsächlcih voneinander getrennt sind. Zur Verdeutlichung auch wieder ein kurzes Beispiel.

VERWEIS mit Suchvektor und Ergebnisvektor
Bei der Verweisfunktion sollen die einzelnen Kostenstellen nach den Suchkriterium durchsucht werden (diese werden als Suchvektor bezeichnet) und innerhalb der Budgetwerte die auf gleicher Position befindlichen Budgetwerte ausgegeben werden. Dabei können die Kostenstellen und die Budgetwerte an vollständigen unabhängigen Positioinen der Tabelle stehen (die Kostenstellen könnten sogar senkrecht und die Budgetwerte waagrecht hinterlegt sein) wichtig ist nur, dass der Suchvektor und Ergebnisvektor identisch viele Anzahl an Zellen in einer Richtung umfasst.

Die sehr übersichtliche Formel ist dann in folgender Abbildung abgebildet:
Formel Verweis mit Suchvektor und Ergebnisvektor

Die Formel lautet dabei technisch wie folgt:

=VERWEIS(Suchkriterium;Suchvektor;Ergebnisvektor)

Dabei ist das Suchkriterium in der Zelle A 40 die Kostenstelle 102 und als Suchvektor wird nun geschaut an welche Position diese Kostenstelle im Suchvektor auftaucht (es ist der dritte Wert) und im Ergebnis wird auch diese Position im Ergebnisvektor ausgegeben.

Damit ist diese Funktion schon eine der flexibleren Funktionen (sofern man sich nicht mit INDEX auseinander setzen mag) und ermöglicht so ein schnelles Ergebnis.

Fazit

Natürlich können diese Verweisfunktionen auch etwas komplizierter ausfallen. Im Artikel "Prüfung inwieweit ein Wert, bspw. eine Kostenstelle, innerhalb eines Intrevalls (Gruppe) liegt in Excel" hatte ich die Verweisfunktion genutzt ob ein Wert innerhalb eines vorgegebenen Intervall liegt. Im Artikel "SVERWEIS ohne NV und dynamische Größen für Datenbereiche" bin ich auf das Thema eingegangen welche Vorkehrung getroffen werden kann, wenn ein Wert nicht gefunden wird.

Hinweis INDEX / VERGLEICH als Alternative

Wer sich nicht aus Nostalgiegründen an die Verweisfunktionen klammert kann aber auch die Funktionen INDEX / VERGLEICH nutzen die auch im Ergebnis bei Artur von excel-koenig.de im Artikel "5 Excel Formeln, die du kennen solltest" Anfang 2017 zusammengestellt worden sind.

Hier kann ich unter anderen auf einen Artikel von Martin Weiß (tabellenexperte.de) zum Thema "Ein echtes Dream-Team: INDEX und VERGLEICH" hinweisen.
 

Praxisbeispiele Verweisfunktionen


Persönlich bin ich wieder auf die Entwurfsversion dieses Beitrag aufmerksam geworden nachdem ich die Zusammenstellung mehrere Artikel zu Verweisfunktionen von Gerhard Pundt von clevercalcul (siehe Beitrag "29 empfehlenswerte Artikel zu SVERWEIS / VLOOKUP" im Feedreader als ToRead gefunden hatte :-). Wobei ich gerade bemerke, dass er auch im Artikel "3 Verweisfunktionen von Excel, die du kennen solltest" praktische Anwendungsbeispiele für diese Funktionen findet.

Wie schon im Kommentar erwähnt empfinde ich die VERWEIS Funktion tatsächlich als flexibel, jedoch muss ich zugeben, dass der Vorteil, dass anhand der Formelnamens direkt klar ist, woran sich die Formel orientiert ebenfalls charmant. Insbesondere wenn die Formel auch noch mit Spalte() als Index genutzt wird.

 

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, 18. März 2017
11:33 Uhr

Dateipfad und Dateiname als Fußzeile automatisch in Excel setzen

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.

Excel Drucktitel

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

Dateipfad und Dateiname

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:

Formel Zelle um Dateiname anzugeben

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.



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, 14. Februar 2017
18:42 Uhr

Leerzeilen bei Zeilenbeschriftungen in Excel Pivottabellen auffüllen

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:

Grundtabelle nach Fachbereich Auftrag und Finanzierungszweck
 

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:

Pivot Tabelle nach Fachbereich Kostenstelle Finanzierungszweck und Auftrag

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.

Pivot Tabellen Optionen

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:

Zellenbeschriftungen per Wenn Funktion erweitern

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:

Wenn Funktion um Zellenbeschriftungen darzustellen

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.

Bedingte Formatierung identischer Werte

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.

Darstellung mit durchgehender Zellenbeschriftung

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.
 

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, 1. Oktober 2016
10:56 Uhr

Leistungsmengen im Grundbudget je Fächergruppe (Cluster) im Vergleich oder bedingte Formatierung für Minimalwerte und Maximalwerte

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:

Leistungsmengen je Fächergruppe bzw. Hochschulcluster

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.

Neue Regel für bedingte Formatierung anlegen

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.

Minimale Leistungsmenge Cluster 1 Universität


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:

Maximale Leistungsmenge Cluster 1 Uni

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:

Ergebnis Darstellung der höchsten und niedrigsten Leistungsmenge

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.

Farbskala über Studierendezahlen

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.

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, 17. September 2016
11:18 Uhr

Excel rechnet mit Farben oder ZÄHLENWENN bzw. SUMMEWENN anhand der Hintergrundfarbe der Zelle dank ZELLE.ZUORDNEN ohne VBA

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.

Tabelle mit Rückmeldungen zur Teilnahme oder Rechnungen die farblich markiert sind

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.

Namensmanager mit EXCEL4-Makrofunktion zur Bestimmung der Farbwerte der Hintergrundfarbe

Zur besseren Lesbarkeit noch einmal beide definierte Namensfunktionen:
 
Name bezieht sich auf
Namensmanager definierte Funktionen
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.

Formel farbe_L1 zur Berechnung der Hintergrundfarbe

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.

Hintergrundfarbe als Zahl dargestellt

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.

ZÄHLEWENN Hintergrundfarbe übereinstimmt

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.

SUMMEWENN  Hintergrundfarbe übereinstimmt

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.

Ergebnis mit der Hintergrundfarbe einer Tabellenzelle rechnen
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.

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


Mittwoch, 25. Mai 2016
20:15 Uhr

Barcode EAN bzw. GTIN Prüfziffer mit Excel berechnen, Code erstellen und per App einscannen

Ausgangslage:
Zur Digitalisierung von Belegen werden im Rechnungswesen die entsprechenden Buchhaltungsbelege mit einen Barcode versehen der über einen Scanner eingelesen wird, wodurch die im Barcode hinterlegte Nummer (vergleichbar eines EAN Code) in das Buchhaltungssystem übergeben und dadurch später der eingescannte Beleg zugeordnet werden kann. Hierzu wird der Code über eine Scanpistole eingelesen.

Alternativ kann auch der Code händisch eingetragen werden. Allerdings ist unterhalb des Barcode nur die ID aber nicht die Prüfziffer eines zehnstelligen Codes eingegeben. Somit stellen sich hier zwei Fragen.
  1. Kann, zum Beispiel. für ein Testsystem ein gültiger Barcode errechnet werden?
  2. Wie kann der Code ohne Scannpistole am Rechner eingelesen werden?

Beide Fragen möchte ich in diesen Artikel erläutern. Danach möchte ich noch auf weitere Anwendungsgebiete von Barcodes bzw. QR Code eingehen.
 

Gütigen Barcode berechnen (Prüfziffer nach Modulo 10 Berechnung mit Gewichtung 3)


Der verwendete Barcode besteht aus einer, je nach Codeart unterschiedlichen Länge an Zeichen und endet mit einer Prüfziffer.

Zur Überprüfung der Prüfziffer werden die einzelnen Ziffern des hinter den Barcode befindlichen Zahlenwerte beginnend mit der vorletzten Zahl von rechts nach links abwechselnd mit 3 und 1 multipliziert und danach die einzelnen Produkte addiert werden. Die Prüfziffer berechnet sich dabei durch das sogenannte "Modulo 10 Berechnungsverfahren mit der Gewichtung 3". Hierzu wird die Differenz aus der ermittelten Produktsumme und den nächsten Vielfachen von 10.

Innerhalb Excel kann dieses über die REST Funktion ermittelt werden. Für eine zwölfstellige EAN ist dieses im Artikel "EAN-Prüfziffer berechnen" auf excelformeln.de beschrieben.
Hierbei wird über eine Matrixformel die Multiplikation mit 3 und 1 durch ein Array aus Zeilen  gelöst.

Hierbei werden sowohl für die einzelnen zu verwendeten Ziffern aus der vorgegebenen Zahl als auch für die Erhebung der Faktoren 3 und 1 eine entsprechende Matrix aus den einzelnen Zellen genommen.

Statt dessen kann in einer Formel natürlich auch direkt die Ziffern und die Faktoren 1 und 3 verwendet werden.

Nehmen wir an, dass der EAN Code aus 9 Ziffern besteht und die 10. Ziffer dann tatsächlich die Prüfziffer nach oberen Schema wäre.

Hier kann dann die Formel, ausgehend von einen neunziffrigen Wert in Zelle $A$1  wie folgt aufgebaut werden:

=REST(10-REST(SUMMENPRODUKT((TEIL($A$1;{1;2;3;4;5;6;7;8;9};1))*{3;1;3;1;3;1;3;1;3});10);10)

Durch diese Formel wird durch die Restwertberechnung zu 10 eine passende Prüfziffer aus einer vorgegebenen neunstelligen Zahl ermittelt.

Natürlich wäre es  wesentlich schöner, wenn die Länge der Ziffer dynamisch berechnet werden kann, aber dass gebe ich doch lieber als Artikelanregung an clevercalcul weiter (siehe weiter unten)  ;-).


Um die einzelnen Rechenschritte nachvollziehen zu können, kann dieses anhand der fiktiven Codenummer 130719789 nachvollzogen werden
 

Position
 
1 2 3 4 5 6 7 8 9
Ziffer  1  3  0  7  1  9  7  8  9
Faktor 3 1 3 1 3 1  3  1  3
Produkt 3 3 0 7 3 9 21 8 27
1. Summenprodukt (A) 81
2. Nächstes Vielfache von 10 (B) 90
B - A 9
Gültiger Barcode nach Verfahren 1307197899

Somit kann ein entsprechend gültiger Barcode auch errechnet werden.
Technisch betrachtet handelt es sich hierbei um einen Barcode nach  "2/5 Interleaved" und ist in der Norm ISO/IEC 16390 spezifiziert. Oftmals findet man auch die Bezeichnung ITF für Interleaved 2 of 5 − Interleaved Two of Five (siehe Abschnitt in der Wikipedia). In unseren Fall hat der Code eine Gesamtlänge von 10 Zeichen, so dass dieser auch als ITF-10 bezeichnet werden kann. Dieser wird dann von einen Scanner erkannt und direkt ins Buchhaltungssystem als Eingabe übergeben.

Dynamische Länge von Artikelnummern und Prüfziffern berechnen.

In meinen Beispiel ist dieses für eine vorgegebene Länge mit 9 Zeichen + 1 Prüfziffer erläutert worden. Im Artikel "Wie du dynamisch die Prüfziffer eines Barcodes berechnest" hat Gerhard Pundt eine alternative Verfahrensweise beschrieben, die mir auch durch das Beispiel der ISBN besonders gut gefallen hat.Gerade da meistens die Codes innerhalb einer Größenordnung von 9 bis 14 Ziffern vorliegen ist diese Methode wesentlich schöner zu verstehen als die bei Excelformeln.de "EAN-Prüfziffer berechnen " unter Zuhilfenahme der Funktion ZEILE() berechneten Werte mit einer Länge() des Ausgangswertes anzupassen :-)))
 

Einlesen von Barcodes mit App statt Scanpistole


Zur Umsetzung kann jedoch auch eine App verwendet werden und der Code dann per Hand eingegeben werden. Hierzu kann ich die App Barcode Scanner empfehlen, welche ich im folgenden Abschnitt ausführlicher vorstellen möchte.

Barcode Scanner

App Barcode Scanner
Marketlink Kurzbeschreibung:
Der Barcode Scanner erkennt verschiedene Codes (u.a. EAN, ISBN oder auch QR Codes) und kann diese entsprechend umwandeln. Teilweise setzen auch andere Programme auf diese Funktion auf.

Dieses hat den Vorteil, dass so auch die im Artikel "Android App: Buchverwaltung MyBookDroid (Android App zum Bücherarchivieren mit ISBN Scanner)" vorgestellte App zur Verwaltung der heimischen Bibliothek sich der Möglichkeit des Scan von ISBN Codes bedienen kann.

Nachdem die App erfolgreich installiert und gestartet wurde kann anhand der Kamera der zu scannende Code gesucht werden.

Code zum Scannen suchen

Hierbei wird zur Unterstützung ein helles Rechteck angezeigt innerhalb dessen der Barcode positioniert werden soll.

Relativ schnell wird der entsprechende Barcode dann auch umgewandelt, wie am Beispiel der ISBN zu sehen ist.

ISBN Code eingescant

Da es sich beim Code um die 13-stellige EAN-13/GTIN-13 nach dem System GS1  (genauer ISBN oder ISSN) wird automatisch in der App diese mit Büchern verbunden, so dass gleich eine Buchsuche angeboten wird.

Entsprechend hilfreich kann dieses auch sein um den EAN Code von anderen Produkten (Lebensmittel, Elektronik, ..) zu lesen. Besonders hilfreich ist dieses natürlich auch, da hier diese Informationen dann auch geteilt beziehungsweise an eine andere App weiter gegeben werden kann.

QR Code und Anwendungsgebiete

Eine besondere Form der Barcode sind die sogenannten QR Code. Dieser wurde von der japanischen Firma Denso Wave entwickelt und war ursprünglich für die Logistik in der Automobilproduktion des Toyota-Konzern verwendet. Die Verwendung des QR-Codes ist lizenz- und kostenfrei und kann für verschiedene Anwendungsgebiete verwandt werden.


Verschiedene Anwendungsgebiete sind hier in Verbindung mit einem QR Code Generator möglich.

Auf der Seite goqr.me können  für eigene Anwendungszwecke entsprechende Codes generiert werden.

Dabei sind einige der Möglichkeiten:
  • URL - um eine Internetseite zu hinterlegen
  • TEXT - für eine einfache Textnachricht
  • VCARD - für eine Visitenkarte
  • SMS - als Vorlage für eine SMS
  • CALL - für einen Telefonanruf
  • GEOLOCATION - für einen Standort
  • EVENT - für einen Termin
  • EMAIL - für eine E-Mail
  • WIFI - für WLAN Zugangsdaten
Da goqr.me auch eine Schnittstelle zur Generierung von QR Codes anbietet kann dieses bspw. für ein Wiki genutzt werden um entsprechende Seiten mit einen Barcode zu versehen. Auch in Werbeanzeigen ist immer öfters ein Barcode zu finden der eventuelle Zusatzinformationen verbirgt. Grundsätzlich ermöglichen es solche Codes tatsächlich ein wenig die digitale und analoge Welt auch spielerisch miteinander verschmelzen zu lassen.

Gerade mit letzteren Punkt bieten solche QR Codes eine tolle Alternative zu NFC Tags an (siehe Artikel "NFC Tags zum Steuern von Smartphones unter Android nutzen").

In der ct 10/2016 ist im Artikel "WLAN-Butler Raspberry Pi als Gäste-WLAN-Automat" ein spannendes Projekt mit Verwendung eines generierten QR Code für WLAN vorgestellt.

 

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, 12. April 2016
21:20 Uhr

Datentrends für Drittmittelstatistik mit Sparklines ab Excel 2010 darstellen durch Liniendiagramme in Zellen

Neben Datenschnitten bei Pivot-Tabellen (siehe Artikel "Pivottabellen ab Excel 2010 dynamischer filtern mit Datenschnitten am Beispiel Hochschulfinanzstatistik") sind die Sparklines eine neue Funktion ab Excel 2010.

Definition laut Wikipedia zu "Sparkline":
"Sparklines, auch Wortgrafiken genannt, werden dazu benutzt, um Zahlen in einem Text auf platzsparende Weise grafisch zu erklären. In der Form eines stark miniaturisierten (Zeitreihen)- Diagramms zeigen Sparklines die historische Entwicklung eines numerischen Wertes und geben ihm so den Kontext, der für seine Interpretation wichtig ist. Entwickelt und benannt wurde das Konzept von Edward Tufte, einem Professor der Yale-Universität."

In Excel sind Sparklines tatsächlich kleine Diagramme die nicht als extra Objekt (Diagramm) aus Tabellen generiert werden sondern eher ein Diagramm welches im Hintergrund einer Zelle gelegt werden kann um für eine Datenreihe einen Trend darzustellen. Eine klassische Verwendung von Sparklines sind sicherlich
Währungs- oder Börsenkurse aber auch zur Datenanalyse können diese für Zeitreihen verwendet werden. Gerade im Bereich der Analyse von Drittmitteln können diese recht schnell einen ersten Eindruck von Trends vermitteln und dabei platzsparender als Diagramme sein.

Als Beispiel kann folgende stark vereinfachte Tabelle mit Drittmitteldaten für die Geschäftsjahre 2010 bis 2015 betrachtet werden.

Drittmitteljahresvergleich auf Ebene Fachbereiche

Hierbei sind sowohl positive als auch negaitve Werte erfasst, allerdings ist trotz niedriger Werte nicht sofort erkennbar, wie sich insgesamt die Werte für den einzelnen Fachbereich oder die Abteilung entwickelt hat.

 

Bisherige Formen der Trendanalyse beziehungsweise Vergleich von einzelnen Werten


Bevor in diesen Artikel ausführlicher auf die Möglichkeiten der Sparklines eingegangen wird, möchte ich noch einmal die bisher hier im Blog schon dargestellten Formen der Analyse von Daten in Form von Diagrammen oder andere meistens weitergehende Analysen in Erinnerung rufen beziehungsweise auf diese Möglichkeiten verweisen
.

Bedingte Formatierung für Vergleich zweier Werte dank bedingter Formatierung mit Pfeilen

Für den Vergleich von zwei Werten (Vergleich von Salden von zwei Jahre) kann die im Artikel "Excel: bedingte Formatierung mit Pfeilen (Darstellung Tendenzen bei Veränderungen)" beschriebene Methode verwendet werden.
Zur Erinnerung diese Darstellung sieht dann wie folgt aus:

Bedingte Formatierung zum Vergleich zweier Werte

Hier wird anhand von Pfeilen erklärt, wie sich der Saldo 2011 zum Saldo 2012 verändert hat.
Wenn ich nun aber mehrere Jahre, wie im oberen Beispiel die Jahre 2010 bis 2015, vergleichen möchte kann ich dieses allerdings nicht mehr durch eine einfache Pfeilrichtung darstellen sondern möchte hier einen Trend darstellen.

Grenzwertanalyse in Form von Pivottabellen und entsprechende 3D-Säulendiagramm

Eine ernsthafte Analyse würde hier die Drittmittel ggf. noch auf Ebene einzelner Lehreinheiten, Instute oder gar Professuren herunterbrechen um diese dann auch ausführlicher, wie bspw. im Artikel "Excel Pivottabelle Darstellung Grenzwerte Einnahmen auf Projekte je Person durch Zuordnung VZÄ auf verantwortlicher Kostenstelle" ausführlicher zu analysieren. Hierdurch könnte ein dreidimensionales Säulendigramm in der Art von folgender Abbildung entstehen.

Grenzwertanalyse von Drittmittel je Person bzw. VZÄ
 

Vergleich unterschiedlicher Werte über gestapelte Säulendiagramme mit Summenausweis

Alternativ könnten die Daten auch noch um einzelne Mittelgeber angereichert werden und hier vergleichbar zum im Artikel "Gestapelte Säulendiagramme in Excel mit Summenausweis" beschriebenen Diagramm mit Unterscheidung je Mittelgeber anstatt Gesamtkosten getrennt nach Personal, Sach und AfA erstellt werden.

Stapeldiagramm Gesamtkosten, AfA, Sachkosten und Personal

Diese Diagramme sind allesamt sicherlich sinnvoll benötigen aber relativ viel Platz und nehmen eigentlich gleichzeitig auch schon eine Interpretation der Daten vorweg. Im Gegensatz dazu können Sparklines direkt eine schnelle Analyse von Zahlenreihen ermöglichen.
 

Trendanalyse von mehreren Daten durch das Erstellen von Sparklines (Einführung in Grundlagen von Sparklines ab Excel 2010)

Um nun tatsächlich Sparklines zu nutzen werden die Zellen H2 bis H5 markiert und über den Ribbon "Einfügen" in der Befehlsgruppe "Sparklines" eine der Sparklinesarten eingefügt.

Symbolleiste Einfügen - Befehlsgruppe Sparklines

Insgesamt stehen hier drei Arten von Sparklines zur Verfügung.
Arten von Sparklines
Im einzelnen sind dieses:
  • Liniensparklines
    Hier werden die einzelnen Werte mit einer Linie verbunden, so dass eine Entwicklung dargestellt wird.
  • Säulensparklines
    Hier wird jeder Wert als eigene Säule dargestellt (klassisches Säulendiagramm)
  • Gewinn-/Verlustsparkline
    Hierbei wird ebenfalls ein Säulendiagramm dargestellt. Allerdings werden hier nicht die exakten Werte dargestellt sondern positive Werte als +1 und negative als -1 eingefügt. Somit sind hier auf einen Blick positive und negative Tendenzen ersichtlich. Allerdings werden diese nicht in der Höhe unterschieden.
Da hier eine Entwicklung graphisch dargestellt wird, bietet sich das "Liniensparkline" an.
Die entsprechende Sparklines werden in den Hintergrund einer Zelle eingefügt. Da wir mehrere Zellen markiert haben (Zelle H2 bis H5) sind diese gleichzeitig als gruppiert, so dass hier Formatierungs- und Skalierungsoptionen gemeinsam genutzt werden können. Zwar kann eine Gruppierung von ausgewählten Sparklines auch im Nachhinein vorgenommen werden, allerdings ist es so meiner Meinung nach viel einfacher.

Im nächsten Schritt wird der Datenbereich der darzustellenden Daten festgelegt.

Sparklines Datenbereich festlegen

Im Beispiel wird der Datenbereich mit B2:G5 beschrieben, Dieses entspricht den einzelnen Drittmittelwerten je Fachbereich. Als Position der Sparklines wird hierbei die Spalte H beziehungsweise die Zellen H2 bis H5 festgelegt.

Danach werden in der Spalte H schon die ersten Sparklines dargestellt und der Ribbon (Befehlsleiste) für Sparklines Entwürfe öffnet sich.

Sparklines Befehlsleiste Ribbon Entwurf

Soweit sieht die Entwicklung schon einmal sehr nett aus, allerdings können hier noch weitere Einstellungen vorgenommen werden, die oftmals sinnvoll sind.

Unter der Schaltfläche "Daten bearbeiten" kann die Behandlung von Leeren Zellen festgelegt werden.

Sparklines Behandlung von leeren oder ausgeblendeten Zellen

Hier bietet sich bei Liniendiagrammen die Option "Datenpunkte mit einer Linie verbinden".
Innerhalb der Befehlsgruppe "Anzeigen" habe ich den Punkt "Markierungen" aktiviert, so dass hier jeder Sparkliniedatenpunkt hervorgehoben wird (es wird ein Punkt auf der Linie dargestellt). In der Befehlsgruppe "Formatvorlage" können dann auch noch entsprechende Farben und Formate der Linie beziehungsweise auch vorhandene Formatvorlagen ausgewählt werden. Ein in meinen Augen wichtiger Punkt ist hier tatsächlich die Möglichkeit negative Datenpunkte rot zu färben.

Sparkline Datenpunktfarbe festlegen

Damit sehen die einzelnen Linien schon einmal wesentlich "schöner" aus.

Da hier mehrere Sparklines als Gruppe definiert wurden (entweder gleich bei der Erstellung oder über die in der Befehlsgruppe "Gruppieren" vorhandene Schaltfläche "Gruppieren") besteht nun noch eine weitere wichtige Möglichkeit zur Definition der einzelnen Achsen. Hierbei habe ich folgende Einstellungen vorgenommen.

Sparklineachse für Sparklinegruppe festlegen

Besonders wichtig sind hier in meinen Augen, "Achse anzeigen" und sowohl für Minimalwert und Höchstwert identische Werte je Sparkline zu verwenden. Um ein wenig Abstand zu haben, besteht auch die Möglichkeit hier einen benutzerdefinierten Wert festzulegen, so dass hier ein wenig Platz oben und unten im Diagramm vorhanden ist. Hierbei ist zu beachten, dass tatsächlich nur der vorhandene Zellenplatz für die Sparkline festgelegt wird.

Im Ergebnis für obiges Beispiel kann dieses dann wie folgt aussehen:

Sparkline Beispiel Linie

Das Ergebnis ist tatsächlich rot markiert. Soweit dürften die Möglichkeiten relativ klar sein. Besonders charmant ist dabei, dass durch das Einfügen von Liniendiagrammen im Hintergrund der Zelle hier auch noch Text oder andere Möglichkeiten vorhanden sind. Für die Ergebniszeile (Ergebnis) habe ich hier diese Möglichkeit direkt genutzt.

Sparkline Ergebnis über verbundene Zellen

Hierbei wurde in der Zelle B7 ein Sparkline über die Ergebniszeile (Summe) der Zellen B6:G6 festgelegt. Zur schöneren Darstellung wurden dann die Zellen B7 bis G7 miteinander verbunden und entsprechend die Zellenhöhe angepasst.

Da die Sparkline im Hintergrund der Zelle eingefügt ist kann hier tatsächlich noch eine ergänzende Information zum Beispiel der Text "Entwicklung Drittmittel" ergänzt werden.

Zu beachten ist hier, dass diese Sparklines nur in aktuellen Excelversionen (ab Excel 2010) und im XLSX Format dargestellt werden. Ebenso wie die im Artikel "Pivottabellen ab Excel 2010 dynamischer filtern mit Datenschnitten am Beispiel Hochschulfinanzstatistik" vorgestellten Datenschnitten wird diese Funktion dann gar nicht dargestellt. Entsprechend sinnvoll kann es daher sein die so erstellten Tabellen entweder als PDF oder vor der Versendung mit als XLSX Datei sich zu versichern, dass die Empfängerseite auch die Tabelle lesen kann.

Insgesamt sind diese Sparklines eine gute Funktion um "weitergehende" Analysen oder Deutungen von Daten vorzunehmen und hier eine direkte Interpretationshilfe für eine Datenreihe festzulegen.

Insgesamt hat diese Funktion tatsächlich einen Vorteil um auf schnelle Art und Weise einen Hinweis und "schnelle" Erfassung von unterschiedlichen Daten festzulegen. Diese visuelle Darstellung von Daten dürfte tatsächlich vielen Personen helfen hier die dargestellten Daten auf einen Blick zu erfassen.

Daten visualisieren und verstehen

Gerade visuell geprägte Menschen können so komplexe Daten relativ schnell vermittelt werden. Daher möchte ich hier auch noch weitere Themen hierzu ansprechen beziehungsweise auf solche verweisen.

Im Bereich der Datenvisualisierung möchte ich in diesen Zusammenhang auch noch auf zwei weitere Artikel in diesen Blog verweisen. So wurde das Thema Mindmap im Artikel "Mindmapping und Sketchnotes im Beruf nutzen für Brainstorming oder Mind Mapping mit XMIND" oder auch für "Ereignisgesteuerte Prozesskette in DIA darstellen (weitere Objekte einfügen)" mit der "Software für Prozessbeschreibungen (DIA)" vorgestellt.

Excel und andere Software bieten hier natürlich eine Menge an Möglichkeiten, allerdings sollte sich bei der Erstellung von solchen Datenbeschreibungen auch Gedanken darum gemacht werden wie hier Daten aufbereitet werden und tatsächlich für die einzelnen Berichtsempfänger auch passend aufbereitet werden. Allerdings ist diess ein Thema was wohl in jeder Form des Berichtswesen relevant ist. Ich bin hier auf jeden Fall gespannt, wie dieses für anstehenden Berichte und Projekte tatsächlich umzusetzen ist .. aber dieses Thema ist sicherlich für die weitere Zukunft ein Thema.

Versenden von Berichten oder Ablage

Neben der technischen Möglichkeiten im Berichtswesen sollten auch immer die Inhalte und die zu vermittelnden Punkte zwischen Sender und Empfänger beachtet werden. Ich hoffe, dass auch mit den Sparklines dieses gelungen ist.Nachdem die Auswertung erstellt ist kann diese entweder per Serienmailfunktion wie im Artikel "Serienmails über Serienbrieffunktion in Winword per Outlook, Thunderbird oder anderen Mailprogramm versenden" beschrieben versandt werden oder alternativ innerhalb eines ERP Systems in einer zentralen Ablage wie im Artikel "SBWP: Berechtigungen Allgemeine Ablage" beschrieben abgelegt werden.

Weitere Informationen zum Berichtswesen

Ferner sollten natürlich auch die Möglichkeiten des Berichtswesen in den einzelnen Anwendungen berücksichtigt werden. Für meine tägliche Arbeit habe ich im Artikel "Unterschiedliche Auswertungsmöglichkeiten im Controlling (Report Writer, Recherchebericht, SAP Query) und natürlich Excel ;-)" einige dieser Möglichkeiten dargestellt. Gerade im Bereich der internen Weiterbildung empfehle ich aber auch gerne "Video2brain - Onlineschulung per Videostreaming unter Android, Windows, iOS und Web" (besonders die Excelschulungen) oder auch die unterschiedlichen Socialmedia Angebote (u.a. Fachblogs) die ich im Artikel "Praktische Nutzung von social media Diensten für meinen Arbeitsalltag" vorgestellt habe. Für die eigene Weiterbildung in SAP kann ich ansonsten noch die SAP Bibliothek-Flatrate empfehlen.

Ein Eindruck vom Verlagsangebot kann sich auch im Artikel "Espresso Tutorial SAP Fachbücher Neuerscheinungen und Katalog 2016 " gemacht 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


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


Donnerstag, 31. März 2016
20:44 Uhr

Datei mit unbekannter Endung in Windows über rechte Maustaste direkt an Excel senden

Wenn in Windows eine unbekannte Datei geöffnet werden soll, fragt Windows oft nach welches Programm zur Öffnung genutzt werden soll. Hier kann gleichzeitig für den  Dateityp anhand der Dateiendung auch festgelegt werden mit welchen Programm die Datei geöffnet werden soll. Dabei gibt es eine Vorschlagliste oder alternativ kann über die Schaltfläche "Durchsuchen" auch ein beliebiges Programm ausgewählt werden (siehe Abbildung).

Öffnen mit -> Windows Programmauswahl

Dieses ist jedoch relativ umständlich besonders wenn man für einen Dateityp möglicherweise auch einmal ein anderes Programm nutzen mag und nicht immer daran denken möchte den Hacken bei "Dateityp immer mit den ausgewählten Programm öffnen".

Oft erhält man jedoch eine Datei, meistens aus irgendeinen System exportiert, die man auf jeden Fall per Excel öffnen mag, die aber nicht eine der üblichen Dateiendungen wie CSV, XLS etc. haben. Als Beispiel mag hier das im SAP Umfeld beliebte "MUSTER.DAT" für exportierte Listen erwähnt sein, die dann später in Excel weiter bearbeitet werden sollen.

Natürlich ist es möglich auch für *.DAT als Dateityp Excel zu verwenden, über die Website endungen.de die richtige Anwendung zu suchen oder aus Excel die Datei per Öffnen in Excel zu betrachten.

Eine schönere bzw. flexiblere Funktion ist es über die rechte Maustaste die Funktion "Senden an" zu verwenden.

Hierzu kann im Benutzerordner unter:

C: > USER > "Benutzername" > AppData > Roaming > Microsoft > Windows > SendTo

Eine Verknüpfung auf die EXCEL.EXE im Installationsverzeichnis von Microsoft Office hinterlegt werden.

Für Office 2010 wäre dieses zum Beispiel (bei der 64 Bit Version) folgender Dateipfad (sofern Sie nicht an anderer Stelle Office installiert haben:

C: > Program Files (x86) > Microsoft Office >Office14 >
und hier die ausführbare Datei "EXCEL.EXE".

Ebenso können Sie natürlich auch
  • "ONENOTE.EXE" für Microsoft One Note
  • "WINWORD.EXE" für Microsoft Winword
  • oder auch "MSACCESS.EXE"  sprich Microsoft Access
als Verknüpfung hinzufügen. Ferner geht dieses auch mit allen möglichen anderen Programmen. In meinen Fall wären das eine Bildbearbeitung, Texteditor (und HTML Editor) sowie die einzelnen OpenOffice Anwendungen.

Wenn Sie nun mit der rechten Maustaste auf eine unbekannte Datei gehen ist es Ihnen möglich durch die Funktion "Senden an" diese für eine weitere Bearbeitung an Excel oder ein beliebiges anderes Programm zu senden. Natürlich können Sie hier auch Verknüpfungen zu Ordnern ablegen, so dass die Datei direkt an die passende Stelle kopiert wird.

Als Beispiel könnte das "Kontextmenü" wie folgt aussehen:
Datei -> Senden an -> Excel unter Windows


Auf diese Weise kann man auf einfache Weise die "Öffnen mit ..." Auswahl umgehen.

Sofern man ohnehin schon im Ordner AppData verweilt kann man hier auch gleichzeitig die Vorlagen für Office unter

C: > USER > "Benutzername" > AppData > Roaming > Microsoft > Templates

wie im Artikel "Microsoft Office Vorlagen und Änderungsverfolgungen" beschrieben ablegen oder auch unter

C: > USER > "Benutzername" > AppData > Roaming > Microsoft > Excel > XLSTART

die persönliche Arbeitsmappe mit für alle Excel-Dokumente verfügbaren Makros wie im Artikel "Excel Umgang mit Makros und Visual Basic for Applications (VBA)" beschrieben speichern.

Der Vorteil ist, dass hierfür keine Administrationsrechte erforderlich sind, da diese Ordner im eigenen Benutzerprofil liegen und damit von einen selbst bearbeitet werden können.

Insgesamt ist dieses ein ziemlicher Komfortgewinn insbesondere wenn man nicht erst die Anwendung öffnen möchte um aus dieser die fraglichen Dokumente zu öffnen oder nur hin und wieder eine Anwendung zum Öffnen bestimmter Dokumente verwenden mag statt der in Windows durch die Dateiendung vorgegebenen Programme.

Obgleich ich für meine Lieblingsprogramme natürlich auch sehr gerne die entsprechend zugewiesenen Tastenkombinationen verwende, was ebenfalls den persönlichen Workflow verbessert.

Diese Möglichkeit ist im Artikel "Hilfreiche Tastenkombinationen unter Windows" ausführlich beschrieben.

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


Freitag, 26. Februar 2016
18:22 Uhr

Video2brain - Onlineschulung per Videostreaming unter Android, Windows, iOS und Web

An vielen Hochschulbibliotheken wird ein Zugang für Video2brain für Hochschulangehörige angeboten, aber auch als Nichthochschulangehöriger kann diese Onlineplattform interessant sein. Das Unternehmen gehört zum Linkedin Austria Konzern und hat seinen Unternehmenssitz in Graz.

Kurzbeschreibung Angebot

Das Angebot umfasst mehr als 1.600 deutschsprachige Lehr- und Trainingsvideos aus den Bereichen Informatik, Business, Lifestyle, Bildbearbeitung Fotografie und Webdesign.
Video-Streaming-Portal: mehr als 1.600 deutsche Lehr- und Trainingsvideos aus den Bereichen Informatik, Business, Lifestyle, Bildbearbeitung, Fotografie und Webdesign. Hierbei werden die Schulungen in verschiedene Kategorien zusammengefasst und können sowohl nach Software als auch Hersteller durchsucht werden.

Hinweis: Tag der offenen Türo bei video2brain - Probegucken

Zum Schnuppern dürfte der 29. Februar 2016 interessant sein, da hier ein Tag der offenen Tür mit freien Zugang zu allen Kursen angeboten wird. Auf sind nähere Informationen zu finden. Das Angebot kann von 28. Februar 2016 11:00 Uhr bis 01. März um 11:00 Uhr genutzt werden.

Nebenbei ist dieser Terminhinweis auch gleichzeitig ein Beispiel für Terminangaben wie im Artikel "Strukturierte Daten auf Website oder Semantisches Web (schema.org)" beschrieben.

Neben der reinen Weboberfläche von der die Videos abgerufen werden können, werden vom Anbieter aber auch Apps für Android, Apple iOS, Windows und Mac angeboten.

Für die Registrierung kann entweder eine E-Mailadresse verwendet werden (was besonders dann zu empfehlen ist, wenn die Hochschule oder die Firma ein Abo anbietet) oder aber per Linkedin, Facebook oder Google+ verwendet werden.

Sollte ihre Hochschule ebenfalls ein Abo von video2brain anbieten ist ein Besuch der Seite https://www.video2brain.com/de/education hier eine gute Anlaufstelle ansonsten kann sich unter https://www.video2brain.com/de/registrierung angemeldet werden.

Während der Rheinwerk Verlag und auch Espresso Tutorials eine eigene Onlineplattform nutzen bieten vor allem Addison-Wesley und Markt+Techni, die zur Mediengruppe Pearson gehören im Rahmen einer Kooperation Schulungsvideos an. Ebenfalls finden sich viele Kurse zu Adobe Produkten im Angebot.

Dozenten können sich bei video2brain bewerben und nach erfolgreicher Annahme wird in Graz das Video von video2brain gedreht. Einen spannenden Artikel zu den Hintergruünden als Dozent schildert hier Tim Schürmann im Artikel "Video2brain: Eine Alternative zu Udemy? Video-Trainings bei Video2brain veröffentlichen" auf trainerbibel.de.

Während ich die Website ein wenig unübersichtlich empfand bin ich gerade am Testen des Angebot per Android App und hier wesentlich zufriedener.

Video2brain

App Video2brain
Marketlink: Eine Onlineregistrierung ist aus den oben beschriebenen Gründen per Weboberfläche sinnvoll.

Anmeldung und Registrierung

Nachdem die App installiert ist kann sich hier mit den Zugangsdaten angemeldet werden.
Bei der Anmeldung kann sich entweder als Person oder im Rahmen eines Unternehmens (oder Hochschule) auch per Organisation angemeldet werden.

Anmeldung als Person


Anmeldung als Privatperson

Wie beschrieben können sich "Privatpersonen" entweder per Social Webdienst (also Linkedin, Facebook, Google+) oder aber mit ihren Benutzernamen und Passwort anmelden. Letzteres ist für Hochschulangehörige auch nach Registrierung außerhalb des Hochschulnetzes möglich, sofern die Hochschule eine entsprechende Zugangsberechtigung hat.

Anmeldung als Organisation

Anmeldung als Organisation bei Brain2Video

Für einen Organisationszugang kann man nun die entsprechende Organisation auswählen und sich über die IP-Standortlizenz anmelden. Hierdurch sind keine Benutzerdaten erhoben, jedoch ist eine Anmeldung nur innerhalb des Hochschulnetzes (ggf. VPN) möglich. Als Beispiel ist hier die Universitäts- und Stadtbibliothek Köln ausgewählt.

Bibliothek und Trainingsverlauf

Mit einen Benutzer besteht die Möglichkeit auf Videos Lesezeichen zu setzen oder auch über den Trainingsverlauf in ein gesehenes Video wieder einzusteigen.

Hierbei ist die Bibliothek in Oberkategorien eingeteilt, die auch wieder Unterkategorien hat.
Ein kurzer Überblick dürfte hier die Hauptkategorie bieten.

Video2brain Bibliothek Hauptübersicht

Unterhalb der Hauptkategorie sind dann noch weitere Unterkategorien und auch beliebte Softwareprodukte ausgewiesen. Ferner kann auch über das gesamte Angebot die Lupe zur Suche verwendet werden. Interessant ist auch die Schaltfläche Trainingsverlauf.

Video2brain Trainingsverlauf

Über den Trainingsverlauf kann jederzeit in ein schon begonnenes Training erneut eingestiegen werden.

Aufbau der Schulungsvideos

Die einzelnen Videotutorials oder Trainings sind in kleine Trainingseinheiten von einigen Minuten eingeteilt, so dass hier entweder ein spezielles Thema angesteuert werden kann oder auch das Training nach einer Einheit abgebrochen und erneut fortgesetzt werden.

Ein gutes Beispiel ist hier in meinen Augen der Verlauf bei der Schulung zum Thema Matrixformeln.

Video2brain Excelschulung zu Matrixformeln

Während im oberen Bereich das Video läuft kann weiter unten die einzelne Kapitel aufgerufen werden, die Kursbeschreibung oder Hintergrundinfos zum Autoren oder auch weitere vorgeschlagene Videos zum Thema angezeigt werden.

Preise, Abomodelle, Einzelabruf

Neben dem Abomodell, welches monatlich kündbar (für 19,99 Euro) oder zu unterschiedlichen Jahresgebühren verfügbar ist (siehe Abos im Vergleich) können einzelne Schulungen auch käuflich erworben werden und dann dauerhaft betrachtet werden.

Nach der Registrierung stehen aber auch teilweise Gratiskurse zur Verfügung und zu jedem Video kann die Einleitung und teilweise auch das ein oder andere Probekapitel betrachtet werden.

Empfehlenswerte Office-Schulungen

Der von mir angesprochene Excelkurs "Excel: Matrixformeln Formeln für Fortgeschrittene" kann zum Beispiel auch für 39,95 Euro erworben werden.

Wenn man ohnehin schon ein Abo abgeschlossen hat ist auch der Kurs "Excel 2010: Datenanalyse Listen, Tabellen und Datenbanken auswerten" (29,95 Euro) sehenswert.

Derzeit habe ich noch "Excel 2010: Diagramme - So präsentieren Sie Ihre Zahlen professionell und verständlich" (39,95 Euro) und "Excel 2010: Dynamische Diagramme - Intelligente Zahlenpräsentation für Fortgeschrittene" (29,95 Euro) auf meiner Liste und bin hier von einigen Anregungen sehr angetan.

Gerade wenn der Wechsel in Richtung einer neuen Office Version geht, kann ich aber auch den Kurs "Von Office 2010 auf Office 2016 umsteigen" (29,95 Euro)

Hier ist es einfach eine Rechenfrage, ob sich nur ein einzelner Kurs lohnt, oder ob man sich für einen Monat immer mal wieder ein Einzelabo oder direkt Jahresabo nutzt.

SAP Schulungen

Für SAP sind in der Hauptsache zu Business One und Netweaver Kurse zu finden. Für die Kernmodule und SAP ERP sind leider weniger Kurse vorhanden. Allerdings bietet der Anbieter auch eine Themenwunschliste und vielleicht erweitert sich hier ja noch das Angebot.

Alternativ kann ich für SAP natürlich auch das Flatrate-Angebot von Espresso Tutorials empfehlen, dass neben Bücher auch Videos im Angebot hat.
Espresso Tutorial - Die digitale SAP Bibliothek

Auf der Seite "Espresso Tutorials - die digitale Bibliothek" kann das Angebot von Espresso Tutorials betrachtet werden. Für einen Jahresbetrag erhält man Zugriff auf den Bestand an eBooks des Verlags zu unterschiedlichen SAP Themen. Auf dieser Seite kann sich auch ein Überblick über die vorhandenen Medien verschafft werden.

Fazit

Auch wenn ich persönlich in Bezug auf Videotutorials sehr skeptisch bin, muss ich zugeben, dass ich sehr froh bin, dass mich ein Kollege auf dieses Angebot hingewiesen hat und die Qualität der Videos ebenso wie der vermittelte Stoff überzeugen mich durchaus. Allerdings sollte man ernsthaft darüber nachdenken, welche Kurse für einen interessant sind und eventuell dann tatsächlich nur ein Monatsabo oder eben einen einzelnen Kurs kaufen.

Als Alternative zum bewegten Bild sind für mich einfach auch heute noch Buchstaben sehr hilfreich, aber wie schon im Artikel "Onleihe - mein Weg zur digitalen Bibliothek" beschrieben kann ein neues Medium tatsächlich auch den Einstieg in ein bisher noch nciht erlerntes Thema sehr hilfreich sein. Dieses hatte ich ja auch erst beim Thema "Mindmapping und Sketchnotes im Beruf nutzen für Brainstorming oder Mind Mapping mit XMIND" bemerkt. In anderen Bereichen (Blogs, Zeitschriften etc.) ist mir ja ohnehin der Umgang mit "Infotainment oder Nachrichtenapps für Android" vertraut und ich möchte heutzutage nicht mehr darauf verzichten.

Videostreaming auf Monitor / Smart-TV

Ich nutze tatsächlich das Angebot am Tablet und bin davon sehr angetan. Für Personen die gerne am heimischen Smart-TV (bspw. per Amazon Fire TV oder Google Chromecast wobei hier durch Browser Plugin die Weboberfläche an das Smart-TV gesandt werden kann... ebenso gibt es die Möglichkeiten per Miracast das Tablet am Smart-TV zu streamen.... allerdings bin ich hier nicht so wirklich überzeugt und tatsächlich froh über ein Tablet.

Tatsächlich unterstützt Video2Brain auch direkt Chromecast, so dass die App im Hintergrund läuft und eine Schulung in der Statusleiste pausiert (bzw. gesteuert) werden kann und direkt an Google Chromecast gestreamt werden kann. Hierdurch ist die Überlegung naheliegend ein solches Angebot auch auf einen großen Monitor ablaufen zu lassen bspw. für eine Gruppenschulung.

Für mich ist dieses auch eine gute Option um nebenbei am Rechner einzelne Schritte nachvollziehen zu können.

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


Freitag, 4. September 2015
15:13 Uhr

Grundlagen - Berichte von SAP nach Excel exportieren

Gerade die Weiterverarbeitung von Berichten in einer Tabellenkalkulation (in der Regel tatsächlich Excel) dürfte für jeden immer mal wieder ein Thema sein.

So ist es auch nicht weiter verwunderlich, dass dieses Thema sowohl im Artikel "Office Integration - Excelansicht in SAP und Daten kopieren nach Excel" (dieser Artikel ist besonders dann lesenswert, wenn die exportierten Daten in anderen Excelmappen weiterverarbeitet werden sollen - Stichwort: fehlerhafte Farben, Darstellung negatives Vorzeichen, Zahlen als Text,...) als auch unter "Darstellung negatives Vorzeichen in SAP - Standardlayout anpassen für vorangestelltes Vorzeichen" behandelt wurde. Trotzdem möchte ich ergänzend auf das Thema Exportieren nach Excel für verschiedene Berichtstools eingehen.

Bericht Exportieren im Report Writer (Expertenmodus erforderlich)

Innerhalb mit Report Painter / Report Writer erstellten Berichten, wie auch Standardberichten wie Kostenstellen Ist/Plan/Abweichung (Transaktion S_ALR_87012993), besteht die Möglichkeit innerhalb des Menüs BERICHT-EXPORTIEREN (Tastenkombination UMSCH + F2) entsprechende Berichte nach Excel zu exportieren.

Sollte diese Option bei Ihnen im Menü nicht aufgezeigt werden, sondern nur die Möglichkeiten Drucken.., Senden... und Beenden, liegt dieses daran, dass Sie noch nicht den Expertenmodus aktiviert haben.

Über die Schaltfläche Optionen/Office Integration (Tastenkombination STRG+UMSCHALT+F12) kann nicht nur die Office Integration (eine Darstellung des Berichtes in MS Excel) sondern auch die Aktivierung des Expertenmodus vorgenommen werden.

Durch die Aktivierung des Expertenmodus stehen Ihnen nicht nur die wesentlichen Funktionen im Bericht zur Verfügung sondern auch spezielle Funktionen wie Bericht exportieren.. , Layouteinstellungen oder das sichern von Extrakten.

Exkurs Extrakte

Durch Extrakte haben Sie die Möglichkeit umfangreiche Berichte zu speichern und diese direkt aufzurufen ohne dass dieser Bericht erneut erstellt werden muss. Hier dürfte der Artikel "ReportWriter Grundlagen - Extrakte sichern und verwalten" einen guten Überblick bieten.

Sofern der Expertenmodus aktiviert ist steht die Exportfunktion zur Verfügung und Sie können den Bericht als Exceltabelle weiterbearbeiten oder auch versenden.
 

Bericht exportieren nach Excel aus Recherchebericht

Zum Export von Rechercheberichten (sei es nun im Modul PSM-FM oder Bilanzberichte) gibt es ebenfalls die Möglichkeit über die "Berichtsausgabe an XXL" Hierzu kann die Schaltfläche Exportieren  oder BERICHT->EXPORTIEREN (STRG + UMSCH + F12) verwendet werden. Diese startet die Funktion Seite an XXL übergeben, wonach automatisch Excel gestartet wird und hier die Daten entweder als Tabelle oder Pivottabelle übergeben werden.

Je nach dargestellten Merkmal kann jedoch statt "Berichtsausgabe an XXL" auch das Fenster "Exportieren auf dem Präsentationsserver" dargestellt werden. Dieses ist zum Beispiel der Fall wenn statt Fonds die Darstellung auf Finanzstellen oder Finanzpositionen innerhalb einer Hierarchie  gewählt wurde. Ferner dürfte dieses auch der Fall sein, wenn eine GuV Struktur entsprechend dargestellt wird. Dieses Thema wurde unter anderen im Artikel "
Rechercheberichte im Modul FI (Bilanzanalyse)" (diese Rechercheberichte bieten sich auch als Quartalsberichte an).

Hier bietet es sich dann an im Abschnitt Ausgabedatei unter Datei eine temporäre Datei. Es bietet sich hier als Dateiname muster.dat in einem einfach zu erreichenden Pfad an. gewählt werden und im Abschnitt PC-Applikation starten unter Programm C:/EXCEL/EXCEL so dass hier direkt Excel gestartet wird mit eben dieser Datei. Es bietet sich dann natürlich an, diese direkt unter einen anderen Dateinamen zu speichern.


 

ALV Listen nach Tabellenkalkulation exportieren

Sofern Sie eine ALV Liste vorliegen haben, zum Beispiel eine SAP Query Auswertung (im Ausgabeformat SAP List Viewer) haben Sie ebenfalls die Möglichkeit über die Schaltfläche EXPORTIEREN die Option TABELLENKALKULATION als Auswahlmöglichkeit.
Teilweise kann diese Funktion auch im Menü unter BERICHT->EXPORTIEREN oder LISTE->EXPORTIEREN    TABELLENKALKULATION hinterlegt sein. So können Berichte, die nicht als "als interaktive Struktur" dargestellt werden, diese ALV Liste ebenfalls mitsamt der Formatierung nach Excel übertragen werden.

Nachdem Sie diese gewählt haben erscheint ein Auswahlmenü in welchen Format die Tabellenkalkulation übergeben werden soll.

Hierzu stehen Ihnen drei Formate zur Auswahl ("Excel (im MHTML Format)", "OpenOffice (im OpenDocument Format 2.0" sowie "Aus allen verfügbaren Formaten wählen"). Hierbei handelt es sich bei der letzten Option um eine Auswahlliste aller verfügbaren Formate.

Hier habe ich (vor Nutzung von Office 2007, 2010, 2013 oder neuere Versionen) immer das Format "Excel (im bisherigen XXL-Format)" empfohlen. Dieses hat jedoch den Nachteil, dass keine Filter, Sortierung oder Zwischensummen übernommen werden und auch das Layout der Tabelle eher gewöhnungsbedürftig ist (gelber Hintergrund und nicht unbedingt die Spaltenreihenfolge wie in der Query Ansicht).

Seit der Einführung von Office 2007 erscheint mir hier die Option "Excel (im Office 2007 XLSX Format)" wesentlich geeigneter, da hierdurch auch Sortierungen, Zwischensummen sowie der Tabellenaufbau inklusive Filterungen übernommen werden. Von daher ist es empfehlenswert die Excelversion zu wählen die entweder etwas kleiner oder identisch zur lokal eingestzten Excel Version ist. Auf diese Weise bleibt der "Kontoauszug" auch von der Darstellung in Excel identisch zur Darstellug in SAP.

Formatvorauswahl treffen und wieder aufheben

Neben der Auswahl des Formates bietet diese Exportfunktion auch die Aktivierung der Schaltfläche "Immer das gewählte Format anwenden". Hierdurch erscheint beim Export nicht mehr die Nachfrage nach den gewünschten Format. Dieses hat jedoch den Nachteil, dass Sie zum Beispiel nach Einführung einer neuen Excel-Version nicht mehr in das Auswahlmenü für das neue Format gelangen sondern der Export direkt ins ausgewählte Format erfolgt.

Zum Glück ist innerhalb der Liste dieses Auswahlmenü über die rechte Maustaste und hier der Punkt "Tabellenkalkulation ..." wieder aufrufbar, so dass hier erneut ein Format gewählt werden kann und ggf. die Option "immer das gewählte Format anwenden" deaktiviert werden kann.

Technischer Hintergrund SALV_BS_ADMIN

Die Standardeinstellungen werden in der Tabelle SALV_BS_ADMIN ("Tabelle zur Anlage von Steuerwerten zum ALV") je Nutzer gespeichert. Diese kann über den ABAP Report SALV_BS_ADMIN_MAINTAIN durch die SAP Basis gepflegt werden, sofern die User nicht selbst die Option Tabellenkalkulation auswählen.

Durch diesen Report (Transaktion SE80 oder SA38) können die Einstellungen je User eingesehen aber auch gelöscht, geändert oder auch angelegt werden.

Weiterführender Hinweis Downloadverzeichnis

Im Artikel "Downloadverzeichnis für SAP Berichte festlegen" ist auch beschrieben, wie das Zielverzeichnis für einen Export festgelegt werden kann.

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, 16. August 2015
17:00 Uhr

Vorteil von Excel Formatvorlagen und Filter nach Farben oder Zellensymbolen aus bedingter Formatierung

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.

Formatvorlagen in MS Excel ab 2007

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.

Excel - Nach Farbe filtern

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.

Excel - Ergebnis Filter

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.

Excel - Nach Zellensymbol filtern

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

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, 13. Juni 2015
18:52 Uhr

Excel: WVERWEIS Funktion oder Jahresdaten umgruppieren für Mehrjahresvergleich

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:

Darstellung Ergebnis Fachbereich, Lehreinheit, Studiengang aufsteigend

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.

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.

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.

Grundlagendaten Namen definieren Jahresdaten_Studis
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.

Beispiel WVERWEIS

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:

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

Ergebnis WVERWEIS absteigende Jahresdarstellung der Studierendendaten

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.



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, 8. Juni 2015
18:56 Uhr

Excel: nur sichtbare Zellen markieren und kopieren

Ausgangslage:
In einer Exceltabelle wurden verschiedene Zeilen (im Beispiel die Zeile 3 und 5) ausgeblendet bzw. durch Gruppieren bestimmte Spalten (C bis F) nicht mit angezeigt.
In unseren Beispiel sollen nun tatsächlich diese drei Kostenstellen und ihre Ergebnisse kopiert werden und in einer anderen Tabelle über die Zwischenablage eingefügt werden.

Excel-Tabelle mit ausgeblendeten oder gruppierten Daten

Hierzu ist die Tabelle entsprechend markiert worden und mittels STRG C bzw. Kopieren in die Zwischenablege übernommen und in eine neue Tabelle eingefügt worden.
Allerdings ist das Ergebnis eher unerfreulich, da hier auch die ausgeblendeten Zeilen und Spalten mit eingefügt werden.
Excel fügt auch ausgeblendete Daten aus der Zwischenablage mit ein

Hier unterscheidet sich Excel tatsächlich im Verhalten wenn Daten gefiltert oder ausgeblendet werden.

Allerdings besteht die Möglichkeit auch nur sichtbare Zellen zu markieren.Hierzu selektieren wir wiederum die Tabelle und wechseln, je nach Excelversion zu folgender Funktion:

Bis Excel 2003 war diese Funktion unter
  • Bearbeiten
  • Gehe zu
verborgen.
Ab Excel 2007 ist diese Funktion in der Symbolleiste im Ribbon Start in der Befehlsgruppe Bearbeiten unter Suchen und Auswählen zu finden (siehe Screenshot):
Excel- Bearbeiten- Suchen und Auswählen - Gehe zu

Hier kann dann über Inhalte die Funktion Sichtbare Zeilen ausgewählt werden.
Hierzu ist als erstes auf die Schaltfläche Inhalte zu wechseln.
Gehe zu - Inhalte für weiteres Menü

Nachtrag:
Manchmal sieht man den Baum vor lauter Bäumen nicht.

Ein wesentlich einfacher Weg zum Aufrufen der Funktion ist ab Excel 2007 ist in der Symbolleiste im Ribbon Start in der Befehlsgruppe Bearbeiten unter Suchen und Auswählen als Punkt "Inhalte auswählen" zu finden (siehe Screenshot)
Suchen und Auswählen - Inhalte auswählen

Im erscheinenden Dialog kann, wie unten dargestellt, die Option "nur sichtbare Zellen"  gewählt werden.
Nur sichtbare Zellen auswählen

Hierdurch werden nur die sichtbaren Zellen der Selektion ausgewählt und es können nur die relevanten Daten kopiert werden.

Da der Weg zu dieser Funktion sehr umständlich ist, bietet es sich an, diese Funktion in der Symbolleiste für den Schnellzugriff zu hinterlegen. Hierzu kann in der oberesten Zeile (ab Excel 2007) über die Schaltfläche "Symbolleiste für den Schnellzugriff anpassen" über "Weitere Befehle"  über die Anzeige von "Alle Befehle" die Funktion "Sichtbare Zellen markieren" hinzugefügt werden.

Alternativ ist diese Funktion über folgende Tastenkombination

 ALT  +    ;  

(bzw.  ALT  +    UMSCH  +    ,   )

auch direkt möglich aufzurufen.

Persönlich würde ich aber statt mit der Funktion Ausblenden eher Filter setzen, da bei gefilterten Daten tatsächlich nur die ausgewählten Daten kopiert werden.

Im Artikel "Mehrere Autofilter im Tabellenblatt einer Exceltabelle setzen" wird daher auch erläutert, wie man mehr als einen Filter innerhalb eines Tabellenblatt setzen kann.

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


Donnerstag, 12. Februar 2015
18:37 Uhr

Mehrere Autofilter im Tabellenblatt einer Exceltabelle setzen

Grundsätzllich ist das Setzen von Autofiltern (über die Symbolleiste / Ribbon "Daten" in der Rubrik "Sortieren und Filtern" über die Schaltfläche Filtern (STRG + UMSCH + L) eine feine Sache. Hierdurch können innerhalb einer Tabelle nach den Überschriften entsprechende Sortierungen vorgenommen werden und so eine schnelle Übersicht erstellt werden.

Innerhalb eines Controllingberichtes sind nun aber mehrere Datenbereiche vorhanden, so dass heir der Wunsch geäußert wurde mehr als eine Filteroption innerhalb eines Tabellenblattes zu haben. Leider ist das Setzen von Filtern nur einmalig innerhalb eines Tabellenblatt möglich, so dass hier keine zwei Filter auf einen Tabellenblatt gesetzt werden können.

Eine Alternative wäre es nun entweder für die anzuzeigende Daten eine separate Tabelle zu verwenden, diese mit verschiedenen Pivot-Auswertungen darzustellen oder die jeweiligen Daten als Tabelle zu formatieren.

Hierzu können markierte Daten innerhalb des Ribbon "Einfügen" in eine Tabelle über die Schaltfläche Tabelle (STRG + T) in der Rubrik Tabellen formatiert werden. Auf diese Weise werden die Daten als Tabelle dargestellt und Excel fügt automatisch bei den Tabellenberschriften eine entsprechende Filterfunktion ein. In früheren Excelversionen (Excel 2003) war diese Funktion im Menü unter  Daten -> Liste -> Liste erstellen zu finden. Dieses dürfte auch der Grund sein, warum  neben der Tastenkombination STRG und T auch die Kombination aus STRG und L funktioniert. Der Vorteil der Formatierung von einzelnen Datenblöcken als Liste beziehungsweise Tabelle ist, dass hier jeder Bereich einen eigenen Filter für die jeweiligen Daten.

Als ein Beispiel ist in folgender Tabelle sowohl eine Tabelle nach einzelnen Fachbereichen als auch nach einzelnen Studienfächern vorgesehen. Durch das Einfügen der Tabelle über die Tastenkombination STRG T wird wie in der folgenden Abbildung dargestellt nachgefragt, ob die Tabelle auch Überschriften enthält.

Daten als Tabelle formatieren in Excel
Nachdem wir dieses bestätigt haben ist nun auch die Tabelle "nach Studienfächern" mit einer Filterfunktion versehen. Ferner kann über die Tabellentools Ferner kann nun über die Tabellentools die jeweilige Tabelle individuell über Formatierungsvorlagen ansprechend formatiert werden.
Tabellentools - Entwurf für Formatierung

Im Ergebnis können nun beide Tabellen innerhalb des Tabellenblatt gefiltert werden.

Zwei Filterfunktionen innerhalb eines Tabellenblatt
So könnte in dieser Darstellung sowohl die Daten der Fachbereiche, als auch die Daten des jeweiligen Studienfachs gefiltert werden. Daneben hat diese Vorgehensweise in meinen Augen noch den Vorteil, dass auch die Daten etwas besser strukturiert beziehungsweise optisch aufgewertet werden.

Ab Excel 2013 bieten solch formatierte Tabellen noch eine spannende weitere Funktion, da hier auch der Einsatz von Datenschnitten wie im Artikel "Pivottabellen ab Excel 2010 dynamischer filtern mit Datenschnitten am Beispiel Hochschulfinanzstatistik" beschrieben.

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, 22. Juni 2014
18:09 Uhr

Prüfung inwieweit ein Wert, bspw. eine Kostenstelle, innerhalb eines Intrevalls (Gruppe) liegt in Excel

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

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

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

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

Hierzu wurde die Tabelle wie folgt aufgebaut.

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

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

Funktion VERWEIS  Syntaxversion VEKTORVERSION


Hierzu kann die die Vektorversion der Formel VERWEIS verwendet werden.

Der Syntax der Version lautet dabei wie folgt:

=VERWEIS(Suchkriterium;Suchvektor;[Ergebnisvektor])

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

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

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

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

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

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

Kleiner Hinweis am Rand:

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

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


Weitere Verweisfunktionen in Excel

Insgesamt hat Excel hier drei Verweisfunktionen:

1. VERWEIS Funktion

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

2. SVERWEIS Funktion

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

    =SVERWEIS(Suchkriterum;Matrix;Spaltenindex;Bereich_Verweis)

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

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

3. WVERWEIS Funktion

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

Dabei ist sie wie folgt aufgebaut.

    =WVERWEIS(Suchkriterum;Matrix;Zeilenindex;Bereich_Verweis)

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

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



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, 22. Juni 2014
14:08 Uhr

Prozentuale Veränderung bei negativen Zahlen in Excel

Ausgangslage:
Es werden die Ergebnisse (Saldo von Kostenstellengruppen) bei unterschiedlichen Rechenmodellen betrachtet. Hierbei sollen die Veränderungen der Variante C zur Variante B ausgedrückt werden.
Delta (Prozentualte Abweichung weist bei negative Zahlen einen Fehler auf)
Während beim "Saldo A" Die Verändeurng von 100 auf 150 korrekt mit + 50 % aus der Berechung (Variante C - Variante B) im Verhältnis zu (/) Variante B ausgewiesen wird, wird beim Saldo B bei dieser Berechnung ein Verändung um - 50 % dargestellt, obgleich der Saldo um 50 % (Veränderung von +50 zu -100) eine Wertsteigerung erfahren hat.

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

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

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

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

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

1. Wenn Funktion
Die entsprechende Formel lautet hierbei:

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

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

2. Vorzeichen Funktion
Die entsprechende Formel lautet hierbei:

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

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

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

=(C3-B3)/ABS(B3)

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

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



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, 23. Februar 2014
17:30 Uhr

Excel Berechnete Felder in Pivottabellen

Ausgangslage
In einer Exceltabelle sind für verschiedene Kostenstellen entweder das Budget oder der gebuchte Aufwand je Geschäftsjahr in einer Tabelle angegeben. Nun soll eine entsprechende Zusammenfassung des Ergebnis pro Geschäftsjahr ausgegeben werden.

Lösung
Das ideale Werkzeug hierfür ist die Anwendung von Pivottabellen.

Die Ausgangsdaten sehen zum Beispiel wie folgt aus:
Grundtabelle für Pivotauswertung
Innerhalb des Tabellenblattes sind in der Spalte A das Jahr, B die Kostenstelle, C das Budget und in D der im jeweiligen Jahr gebuchte Aufwand zu finden.

Da diese Tabelle fortgeschrieben wird, ist es nicht sicher, bis wohin die Daten gefüllt werden.

1. Grunddaten festlegen
Wie im Artikel SVERWEIS ohne NV und dynamische Größen für Datenbereiche unter "Die Funktion BEREICH.VERSCHIEBEN - Datenbereich dynamisch von der Größe anlegen" beschrieben kann es, sofern weitere Daten in der Liste erwartet werden, sinnvoll sein, die Datengrundlage der Pivottabelle entsprechend anzupassen.


Im Beispiel kann innerhalb des Ribbon "Formeln" in der Befehlsgruppe "Definierte Namen" ein entsprechender Name definiert werden (etwas ausführlicher ist dieses auch im Artikel Formulare gestalten in Excel erläutert).

Hierbei wird der "dynamische Datenbereich" wie folgt festgelegt:
Datenbereich dynamisch festlegen durch BEREICH.VERSCHIEBEN

Durch die Formel
  • =Bereich.Verschieben($A$1;;;Anzahl2($A:$A);Anzahl2($1:$1))
wird den Namen Grunddaten automatisch der Inhalt der Tabelle im Blatt Grundtabelle zugewiesen. Dank der Formel wird der Bereich von der Zelle A1 bis zur Zelle D8 festgelegt (entsprechend ist dieser Bereich auch mit einer gestrichelten Linie von Excel hinterlegt).

Hierbei werden die Anzahl der Zeilen aus der Spalte A und die Anzahl der Spalten aus der Zeile 1 ermittelt.

Sofern nun weitere Daten hinzukommen wird der Bereich entsprechend angepasst.


2. Pivottabelle anlegen

Innerhalb des Ribbon "Einfügen" kann nun eine Pivottabelle eingefügt werden.

Pivottabelle einfügen

Als Bereich der zu analysierenden Daten wird nun der vorher angelegte Name durch
  • =Grunddaten
eingetragen.

Auf diese Weise wird die Datengrundlage der Pivottabelle autmatisch erneut berechnet, sollten weitere Datenzeilen eingefügt werden.

PivotTabelle Feldliste

Innerhalb des Spaltenvorrates können nun die einzelnen Datenfelder aus der Feldliste Als Spalten, Zeilen oder Werte einer Pivottabelle übernommen werden.

Zugewiesene Pivotfelder

Innerhalb der unter Werte angegebenen Felder können über die Wertfeldeinstellungen festgelegt werden, wie diese Daten ausgegeben werden (die häufigst genutzten Methoden dürften wohl Summe, Anzahl oder auch Mittelwert sein).

Wertfeldeinstellungen

Im Ergebnis sind nun nach Jahren Budget und Aufwand je Kostenstelle ausgegeben. Um nun das noch verfügbare Budget zu berechnen kann ein berechnetes Feld im Ribbon "Optionen" in der Befehlsgruppe "Berechnung" über "Felder, Elemente und Gruppen" ein "Berechnetes Feld" eingefügt werden.

Berechnetes Feld

Bei der Definition dieses Feldes kann dann auf die anderen Felder zugegriffen werden.

Formel für berechnetes Feld

Im Beispiel hat das berechnete Feld den Namen "Verfügbar" und berechnet sich aus der Formel:
  • =Budget - Aufwand
Sofern diese Formel definiert ist, kann das Feld durch "Hinzufügen" aktiviert werden und befindet sich nun ebenfalls in der Feldliste.

Neues Feld

Im Beispiel kann nun in der Pivotabelle auch die Summe über Verfügbar ermittelt werden, so dass hier für 2013 ersichtlich ist, dass die Kostenstelle A noch 350 zur Verfügung hat (Budget in Höhe von 1000 abzüglich Aufwand in Höhe von 650).

Diese Berechnungen können natürlich auch mit anderen Formeln genutzt werden. So könnten bei Preisen die Bruttopreise aus den Nettopreisen ermittelt werden, oder eine prozentuale Lohnsteigerung berücksichtigt werden.

Sofern sich ein Überblick über die berechneten Felder geschafft werden soll ist dieses ebenfalls im Ribbon Optionen der Pivot-Tabelle unter "Berechnung" bei "Felder, Elemente und Gruppen" durch die Option "Formeln auflisten" möglich.

Formeln auflisten

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

Liste berechneter Formeln im Tabellenblatt

Dieses Tabellenblatt kann dann auch direkt als Dokumentation verwendet werden.

Ein weiteres (umfangreicheres) Beispiel für berechnete Felder innerhalb Pivot-Tabellen ist im Artikel "Excel Pivottabelle Darstellung Grenzwerte Einnahmen auf Projekte je Person durch Zuordnung VZÄ auf verantwortlicher Kostenstelle" ausführlich beschrieben. Hier werden auch innerhalb der berechneten Felder passende Formeln verwendet.

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, 15. Februar 2014
09:40 Uhr

Office Integration - Excelansicht in SAP und Daten kopieren nach Excel

Beim Export von Daten aus SAP (oder auch einen anderen ERP System) kann es hin und wieder Anpassungsbedarf geben, da die Daten in einen Format zur Verfügung gestellt werden, dass nicht ohne weiteres weiter verarbeitet werden kann.

Einige dieser Probleme (und deren Lösung) möchte ich anhand von typischen exportierten Berichten erläutern. Was die Erstellung von Berichten innerhalb SAP anbelangt sind einige Artikel in der Rubrik SAP zu finden. Unter anderen dürfte der Artikel "Unterschiedliche Auswertungsmöglichkeiten im Controlling / Finanzbuchhaltung bzw. Rechnungswesen (Report Writer, Recherchebericht, SAP Query) und natürlich Excel ;-)" einen guten Überblick bieten.

Ferner ist im Artikel "Grundlagen - Berichte von SAP nach Excel exportieren" der Weg von SAP nach Excel für Report Writer/Painter Berichte, Rechercheberichte und auch ALV Listen (bspw. für SAP Query) erläutert.





Fehlerhafte Farben beim Kopieren


Werden bei aktivierter Office-Integration Daten aus der in SAP dargestellten Excelmappe in eine andere Excelmappe kopiert werden hier die Hintergrundfarben der Datenzellen abweichend von der Ansicht in SAP dargestellt.


© SAP SE. Alle Rechte vorbehalten

Im Beispiel werden die blau hinterlegten Datenzeilen bei der Kopie in Lila (Schlüsselspalte) und Rot (Daten) anstatt in den Blautönen dargestellt. Die Summenzeilen werden nicht in Gelb sondern Grün dargestellt und die Berichtsinformationen sind in schwarzer Schrift auf schwarzen Hintergrund gehalten.

Ursache hierfür ist, dass in der generierten Excel-Mappe der Office-Integration die Farbpalette von Excel an die Farben des SAP GUI angepasst werden.

Dieses hat den Nachteil, dass beim Kopieren in eine "normale" Excelmappe diese Farbinformationen nicht mehr stimmen.

Um nun dennoch diese Daten ordentlich weiter bearbeiten zu können gibt es zwei Möglichkeiten:

1. Daten als Text über Inhalte einfügen kopieren

Excel bietet über die rechte Maustaste die Option Inhalte einfügen:

Inhalte einfügen

Sind aus der integrierten Excelmappe die Daten kopiert können diese hier als Text eingefügt werden. Hierzu ist die Option Einfügen Werte zu wählen.

Hierdurch werden die Daten als reiner Text in die neue Excelmappe eingefügt.

2. Als OpenDocument-Spreadsheet (*.ods) speichern

Eine zweite Möglichkeit, unter Beibehaltung der Formatierung, ist es die Daten über die Speichernfunktion in ein Format zu speichern in dem die Anpassung der Farbpalette nicht mitgespeichert wird.

Hierzu wird die Funktion
  • Speichern unter
  • Andere Formate
aufgerufen. Hier kann dann nicht nur ein Dateiname sondern beim Dateityp auch "OpenDocument-Spreadsheet (*.ods)" gewählt werden.

Die so gespeicherte *.ods Datei kann wiederum in Excel geöffnet werden und von dort als "normale" Exceldatei gespeichert werden, ohne dass hier die Farbpalette angepasst ist.

Diese Methode hat gleichzeitig den Vorteil, dass die Standardfarben (blau/gelb etc.) von SAP auch in der Tabelle erhalten bleibt. Sofern die ODS Datei dann in Excel geöffnet wird kann diese wieder als XLS gespeichert werden, so dass hier die zugewiesenen Farben von SAP erhalten bleiben, ohne dass künftig Probleme beim Kopieren der Werte entstehen.





 

Zahlen als Text in Zahlen umwandeln


Ein weiteres Problem können Zahlen (bspw. aus Stammdatenlisten) sein, die als Text formatiert sind. Auch wenn die Zahl als Zahl formatiert wurde (über die rechte Maustaste und "Zellen formatieren" oder die Symbolleiste Start in der Gruppe Zahl) weist Excel dennoch auf den Fehler hin, dass es sich hier um eine Zahl handelt, die als Text formatiert ist. Nun kann entweder aus den Kontextmenü der Hinweismeldung die "Textzahl" in eine richtige Zahl verwandelt werden:

Fehlermeldung Die Zahl ist als Text formatiert

Alternativ bieten sich auch hier zwei andere Alternativen an:

1. Multiplikation mit 1

Die Textzahlen können durch Multiplikation mit 1 in eine echte Zahl umgewandelt werden.

Hierzu ist in eine andere Zelle eine 1 einzutragen (und zu kopieren). Nun können die Textzahlen markiert werden und über "Inhalte einfügen" die Funktion "Multiplizieren" gewählt werden. Hierdurch werden die Textzahlen mit der 1 aus der Zwischenablage multipliziert und damit auch in eine Zahl umgewandelt.

Dadurch sind diese auch als Zahl markiert und können bspw. per SVERWEIS in einer anderen Tabelle verwendet werden.

2. Makro

Sofern häufiger Daten kopiert werden und diese in Zahlen umgewandelt werden müssen bietet es sich an, hierfür ein entsprechendes Makro zu verwenden.

Dieses sollte wie im Artikel Excel Umgang mit Makros und Visual Basic for Applications (VBA) in die Persönliche Makroarbeitsmappe gespeichert werden, so dass diese innerhalb aller Excelmappen zur Verfügung steht.

Folgender Makrocode kann hierbei helfen:

Sub SAP_Markierung_Text_in_Zahl()
'
' SAP_Markierung_Text_in_Zahl Makro
' Wandelt aus Markierung als Text formatierte Zahlen in Zahl um
'

'
For Each Wert In Selection
Selection.NumberFormat = "0"
Wert.Value = Wert.Value
Next Wert
End Sub

Hier können die "Textzahlen" markiert werden und durch Aufruf des Makros werden die Textzahlen als Zahlen ohne Dezimalstellen formatiert, was sich gerade für Stammdatenlisten (Innenaufträge oder Kostenstellen) anbietet. Das Makro formatiert die Werte als Nummern und weist den Wert erneut den Wert zu, womit der Wert erneut eingetragen wird.

3. Nutzen der Funktion "Text in Spalten"
Eine weitere Alternative wurde mir per Kommentar empfohlen. Hierzu können die fraglichen Zahlen markiert werden und die Funktion "Text in Spalten"  (zu finden innerhalb des Ribbon "Daten" in der Befehlsgruppe "Datentools" ) starten. Eigentlich ist diese Funktion dafür gedacht den Inhalt einer Zelle auf mehrere Zellen aufzuteilen um beispielsweise die Angabe von Kostenstelle und Kostenstellenbezeichnung über Trennzeichen oder feste Breite direkt aufzuteilen. Sofern hier direkt auf "Fertig stellen" geklickt wird, sollen ebenfalls die Daten in Zahlen umgewandelt werden.



Negativzeichen hinter Zahlenwert


Negative Werte werden in SAP beim Export nach Excel als Text formatiert und das Negativzeichen rechts vom Wert dargestellt.

Um nun mit diesen Zahlen (Beispielsweise 19,78-) zu rechnen ist es erforderlich das Negativzeichen vor der Zahl zu setzen.

Auch hier bieten sich zwei Möglichkeiten an:

1. Auslesen der Zahl ohne nachgestelltes - und Multiplikation mit -1

Hierzu kann die Zahl ohne nachgestelltes Negativzeichen genommen werden und mit -1 multipliziert werden. Dieses ist dann in einer Extraspalte erforderlich.

Befindet sich der Wert 19,78- in der Zelle A2 könnte die Formel wie folgt lauten

=LINKS(A2;LÄNGE(A2)-1)*-1


Dieses hat jedoch den Nachteil, dass hier auch positive Zahlen entsprechend verrechnet werden.

Optimiert wäre dieses in Kombination mit einer Wenn Funktion wie zum Beispiel

=WENN(UND(RECHTS(A2;1)="-";ISTZAHL(LINKS(A2;LÄNGE(A2)-1)*1));LINKS(A2;LÄNGE(A2)-1)*-1;A2)


Nun wird nur mit -1 multipliziert, wenn das rechte Zeichen tatsächlich ein "-" ist, sofern es sich dabei um eine Zahl handelt.

Dieses ist verständlicherweise auch ziemlich aufwändig.

2. Makrolösung

Auch hier bietet sich ein Makro an, welches aus den ausgewählten Werten wiederum

Sub SAP_Export_Buchhaltung()
'
' SAP_Export_Buchhaltung Makro
' Wandelt Markierte Werte aus SAP in Buchhaltungszahlen um Minuszeichen wird vorran gestellt
'

'
AnzahlWerte = Cells(Rows.Count, Selection.Column).End(xlUp).Row
For Each Wert In Selection
If Wert.Row > AnzahlWerte Then Exit For
If Right(Wert.Value, 1) = "-" And IsNumeric(Wert) _
Then Wert.Value = Left(Wert, Len(Wert) - 1) * (-1)
Next
Selection.Style = "Comma"
End Sub

 

 

Dieses Makro wandelt die markierten Werte mit vor ran gestellten Negativzeichen um und wird nur auf Werte angewendet wenn es sich um Zahlen handelt, so dass bspw. "Text-" bestehen bleibt. Ferner werden alle Werte im Format Buchhaltung mit Tausenderkennzeichen und ohne Währungszeichen formatiert.
Neben dieser Lösung in Excel besteht aber, zumindest für Berichte im Controlling auch die Möglichkeit, dass Standardlayout in SAP so umzustellen, dass auch hier schon die Daten mit vorrangestellten Vorzeichen ausgegeben werden. Dieses ist im Artikel "Darstellung negatives Vorzeichen in SAP - Standardlayout anpassen für vorrangestelltes Vorzeichen" näher erläutert.




Zeilen oder Spalten einfügen


Um die Liste von nützlichen Makros zu erweitern bieten sich auch noch zwei weitere Makros an, die eine bestimmte Zahl an Zeilen oder Spalten in die Excelmappe einfügen. Dieses kann ggf. schneller sein, als einen Bereich an Zeilen zu markieren und per rechter Maustaste die Funktion "Zeile einfügen" aufzurufen.

Makro zum Einfügen von Zeilen:

Sub Excel_Zeilen_einfügen()
anzahlzeilen = InputBox("Anzahl einzufügender Zeilen")
For i = 1 To anzahlzeilen
Selection.Insert Shift:=xlDown
Next i
End Sub

Makro zum Einfügen von Spalten:

Sub Excel_Spalten_einfügen()
anzahlspalten = InputBox("Anzahl einzufügender Spalten")
For i = 1 To anzahlspalten
Selection.Insert Shift:=xlToRight
Next i
End Sub

Insgesamt erleichtern solche Makros definitv die tägliche Arbeit. Ergänzend würde ich auch noch gerade für größere Tabellen, das im Artikel Inhaltsverzeichnis in Excel über vorhandene Tabellenblätter in die persönliche Makroarbeitsmappe übernehmen.

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, 12. Januar 2014
11:25 Uhr

Urlaubsplaner und dynamischer Kalender mit Monatsansicht (Urlaubsplaner 2016/2017 in Excel)

Ausgangslage
Für eine Urlaubsplanung soll für das laufende Kalenderjahr eine Excelliste erstellt werden in der für jeden Monat ein Tabellenblatt mit allen Beschäftigten und die jeweiligen Wochentage innerhalb des Monats aufgelistet werden, so dass hier die entsprechende Urlaubsplanung erfolgen kann.

Dabei sollen die Wochentage (Montag bis Sonntag) ausgegeben werden und sowohl Wochenende (Samstag und Sonntag) als auch Feiertage farblich hervorgehoben werden.

Als Vorlage dient hier das Tabellenblatt "Monat" wobei in der Zelle A8 der Januar für das Jahr 2014 ausgewählt ist:
Tabellenblatt Monat - Monatsblattansicht Januar 2014

Im Beispiel ist der Neujahrestag (1. Januar) als Feiertag grau hinterlegt und das Wochenende vom 25. bis 26. Januar 2014 ebenfalls mit blau hinterlegt.

Ferner hat der Monat Januar 31 Tage.

Da die Tabelle händisch sehr aufwändig zu erstellen ist, sollen für alle Monate nach Eingabe eines Kalenderjahres die einzelnen Wochentage ermittelt werden und auch nur die Tage ausgegeben werden, die im Monat auch vorhanden sind. Insbesondere gilt dieses natürlich für Februar, wo sowohl 28 als auch 29 Tage möglich sind.

Ziel ist es, die Liste der Beschäftigten im Tabellenblatt "Monat" zu aktualisieren und im Tabellenblatt "Einstellungen" das Jahr einzutragen um über ein Makro die Monate Januar bis Dezember des Jahres zu erstellen.

Tabellenblatt Einstellungen zur Wahl des Kalenderjahres

Wie im Beitrag "Excel Umgang mit Makros und Visual Basic for Applications (VBA)" beschrieben wird über die Schaltfläche "Kalenderblätter anlegen" das Blatt Monat 12 mal kopiert und mit entsprechender Monatsauswahl in den jeweiligen Monat unbenannt.

Bis es soweit ist, bedarf es aber einiger Schritte.

Nachtrag: Excel-Datei als Beispielvorlage


Nach Erläuterung der einzelnen Schritte wird zum Abschluss die Umsetzung als Exceldatei zur Verfolgung gestellt. Zum besseren Verständnis ist es aber sicherlich sinnvoll auch die einzelnen Schritte bis zur Fertigstellung nachzuvollziehen, da dadurch auch Anpassungen an die eigenen Bedrüfnisse wesentlich leichter werden.


Lösung

Zur Umsetzung eines Kalenders werden in diesen Beispiel einige Hilfstabellen verwendet, die dann entsprechende Informationen zum jeweiligen Monat enthalten. Da der Urlaubsplaner innerhalb Hessen verwendet werden soll, sind hier zum Beispiel die hessischen Feiertage (damit sind nicht lokale Feiertage wie der Wäldchestag gemeint) eingeplant.

1. Hilfstabelle: Wieviele Tage hat ein Monat


Jeder Monat im Jahr hat 28 Tage. Als Besonderheit hat der Februar entweder 28 Tage (normales Jahr) oder 29 Tage (im Schaltjahr).

Entsprechend wurde eine Tabelle mit allen Monaten angelegt und sowohl der Monat als Zahl als auch die Anzahl des Tages für den Monat festgelegt.

Das Tabellenblatt "Monate" sieht dabei wie folgt aus:

Tabellenblatt Monate zur Ermittlung Anzahl Tage im Monat

Dieses Tabellenblatt ist als Hilfstabelle ausgeblendet und bedarf auch keiner weiteren Anpassungen.

Die Zellen A2 bis A13 haben hierbei den Namen "Monat" zugewiesen bekommen um diesen als Auswahlfeld für die einzelnen Monatsblätter nutzen zu können.
 
  • Die Definition von Namensräumen durch Verwendung des Namensmanager sowie die Anlage von Dropdownliste in Excel ist im Artikel "Formulare gestalten in Excel" beschrieben.


Daneben sind die Zellen A2 bis C13 als "Tage_Monat" definiert. Durch die Namenszuweisung muss nun nicht mehr auf das Tabellenblatt Monate verwiesen werden sondern auf die zugewiesenen Namen.

Die Monate Januar sowie März bis Dezember haben stets die gleichen Anzahl an Tagen (entweder 30 oder 31) nur der Monat Februar hat entweder 28 oder 29 Tage, je nachdem ob es sich um ein Schaltjahr oder um ein normales Jahr handelt.

Zur Berechnung des Schaltjahres wurde abhängig vom Geschäftsjahr folgende Formel verwendet:

=WENN((REST(Geschäftsjahr;4)=0)-(REST(Geschäftsjahr;100)=0)+(REST(Geschäftsjahr;400)=0)=0;28;29)

Ein Schaltjahr tritt dann auf, wenn ein Jahr durch 4, aber nicht auch durch 100 ohne Rest teilbar ist, mit der Ausnahme, dass ein durch 400 ohne Rest teilbares Jahr wiederum ein Schaltjahr ist (z. B. das Jahr 2000).

Zur Erklärung der Formel
Rest(Geschäftsjahr;4=0) liefert FALSCH aus, sofern das Jahr durch 4 mit Rest teilbar ist, ansonsten WAHR. Ähnlich verhält es sich mit den anderen Bestandteilen dieser Formel.

2. Hilfstabelle: Feiertage


Eine der wichtigsten Punkte ist die Behandlung von Feiertagen. Da bestimmte Feiertage auf unterschiedliche Termine im Jahr fallen gibt es unterschiedliche Berechnungsformen. Bezugsdaten sind dabei Ostersonntag und Weihnachten beziehungsweise der 4. Advent.

Zur Berechnung der Osterformel wurde ein mathematisches Verfahren entwickelt. Die Gaußsche Osterformel von Carl Friedrich Gauß erlaubt die Berechnung des Osterdatums für ein gegebenes Jahr.
 


Da nicht alle Feiertage in allen Bundesländern gültig sind bedarf es auch der Information, ob ein Feiertag entsprechende Gültigkeit hat.
  • Die gesetzliche Grundlage für Feiertage in Hessen ist zum Beispiel das Hessisches Feiertagsgesetz (HFeiertagsG) hier sind die gesetzlichen Feiertage im § 1 Gesetzliche Feiertage HfeiertagsG geregelt.

Hierzu wurde das Tabellenblatt "Feiertage" angelegt, welches ebenfalls als Hilfstabelle ausgeblendet ist. Hier kann es jedoch sinnvoll sein, diese Tabelle eingeblendet zu behalten, um etwaige Feiertage ergänzen zu können.

Tabellenblatt Feiertage

Dabei haben die Zellen A2 bis C25 den Namen "Feiertage_Hessen" zugewiesen, so dass dieses Array später verwendet werden kann.

Zur leichteren Berechnung, beziehungsweise besserer Lesbarkeit der Formeln, wurde der Zelle A8 der Name "VierteAdvent", der Zelle A10 der Name "Weihnachten" und der Zelle A16 der Name "Ostersonntag" zugewiesen.

Somit kann in den einzelnen Formeln Bezug auf das jeweilige Datum genommen werden. In der Spalte C ist für eine spätere Hervorhebung des Feiertages für gesetzlich gültige Feiertage eine 2 einzutragen, so dass diese später gesondert behandelt werden. Im Beispiel sind die für Hessen relevante Feiertage markiert.

Hierbei haben die einzelnen Formeln folgenden Aufbau:
Zelle Datumsformel Feiertag
A2 =VierteAdvent-35 Volkstrauertag
A3 =VierteAdvent-32 Buss- u. Bettag
A4 =VierteAdvent-28 Totensonntag/Ewigkeitssonntag
A5 =VierteAdvent-21 1. Advent
A6 =VierteAdvent-14 2. Advent
A7 =VierteAdvent-7 3. Advent
A8 =Weihnachten-WOCHENTAG(Weihnachten;2) 4. Advent „VierteAdvent“
A9 =DATUM(Geschäftsjahr;12;24) Heiligabend
A10 =DATUM(Geschäftsjahr;12;25) 1. Weihnachtstag „Weihnachten“
A11 =DATUM(Geschäftsjahr;12;26) 2. Weihnachtstag
A12 =DATUM(Geschäftsjahr;12;31) Silvester
A13 =Ostersonntag-52 Altweiber
A14 =Ostersonntag-48 Rosenmontag
A15 =Ostersonntag-2 Karfreitag
A16 =7*RUNDEN((4&-Geschäftsjahr)/7+REST(19*REST(Geschäftsjahr;19)-7;30)*0,14;)-6 Ostersonntag „Ostersonntag“
A17 =Ostersonntag+1 Ostermontag
A18 =Ostersonntag+39 Himmelfahrt
A19 =Ostersonntag+49 Pfingstsonntag
A20 =Ostersonntag+50 Pfingstmontag
A21 =Ostersonntag+60 Fronleichnam
A22 =DATUM(Geschäftsjahr;5;1) Tag der Arbeit
A23 =DATUM(Geschäftsjahr;10;3) Tag der deutschen Einheit
A24 =DATUM(Geschäftsjahr;12;6) Nikolaus
A25 =DATUM(Geschäftsjahr;1;1) Neujahrstag
Basis ist dabei das im Blatt "Einstellungen" zugewiesene Geschäftsjahr.

Im Wesentlichen werden bei Feiertagen die Funktionen DATUM und WOCHENTAG genutzt. Gerade beim 4. Advent wird dieses deutlich, ist dieses doch der Sonntag vor Weihnachten.

Die Formel DATUM setzt aus den Daten Jahr, Monat, Tag ein entsprechendes Datum zusammen.

So kann der 13. Juli 1978 als =DATUM(1978,07,13) ausgegeben werden. Dieses ist als Formel später noch wichtig, um das Monatsblatt zu gestalten.

Die Formel WOCHENTAG ermittelt um welchen Tag in der Woche es sich bei einen Datum handelt.

Neben einen Bezug auf das Datum kann auch ein Typ der Woche mit angegeben werden. Standardmäßig beginnt bei Excel die Woche mit Sonntag. Um nun die Wochentage ab Montag zu zählen kann in der Formel der Montag als erster Wochentag mit angegeben werden.

Ein Teil der Formel in der Zelle A8 liefert durch WOCHENTAG(Weihnachten;2) auf den wievielten Tag in der Woche ab Montag Weihnachten fällt. Um nun den Sonntag vor Weihnachten zu ermitteln wird diese Anzahl von Tagen von Weihnachten abgezogen.

Fällt nun der 1. Weihnachtsfeiertag auf Freitag, dieses ist am 25.12.2015 der Fall, so liefert die Formel den 5. Wochentag so dass der vierte Advent fünf Tage vor den 25.12.2015 liegt und damit am 20.12.2015.

3. Aufbau des Monatblattes


Zur Darstellung der einzelnen Monate werden wiederum einige Hilfszeilen verwendet, die sowohl das Datum, als auch etwaige weitere Informationen zum Monat (Anzahl der Tage, Wochentag, Feiertag, gesetzlich relevanter Feiertag) verwenden und dadurch das Aussehen eines jeden Monats gestalten.

Zur besseren Lesbarkeit wurden hier einzelne Hilfszeilen verwendet, obgleich die farbliche Hervorhebung des Wochenendes oder Feiertages über die bedingte Formatierung auch direkt als Formel funktionieren würde.

Der erste Tag eines Monats beinhaltet folgende Hilfszeilen:

Formeln am 1. Tag eines Monats

Ab den 28. Tag eines Monats wird es dann relevant, wie viele Tage ein Monat hat (unter Berücksichtigung eines Schaltjahres).

Formeln am 29. Tag eines Monats

Sofern ab Spalte B der Monat mit 1. beginnt wären die Tage 29 bis 31 dann in den Spalten AD bis AF zu finden.
 
Zelle Inhalt Formel
A1 Jahr =Geschäftsjahr
A8 Monat Liste mit =Monat - Definitionen von Listen ist im Artikel "Formulare gestalten in Excel" beschrieben
B2 Tage im Monat =SVERWEIS(A8;Tage_Monat;2;FALSCH)
B14 Tag Für die Tage 1 bis 28 ein fester Wert (Beispiel 1)
B3 Datum =WENN(B14>0;DATUM(Geschäftsjahr;WENN(ISTNV(SVERWEIS($A$8;Tage_Monat;3;FALSCH));0;SVERWEIS($A$8;Tage_Monat;3;FALSCH));B14);““)
B4 Feiertag =WENN(ISTNV(SVERWEIS(B3;Feiertage_Hessen;3;FALSCH));0;SVERWEIS(B3;Feiertage_Hessen;3;FALSCH))
B5 Bezeichnung Feiertag =WENN(ISTNV(SVERWEIS(B3;Feiertage_Hessen;2;FALSCH));““;SVERWEIS(B3;Feiertage_Hessen;2;FALSCH))
B6 Wochenende =WENN(B3=““;0;WENN(WOCHENTAG(B3;2)>5;1;0))
B7 Hilfstabelle =B4+B6
B9 Bezeichnung =WENN(B3=““;““;WENN(B5=““;TEXT(B3;“TTTT“);B5))
AD3 Datum =WENN(B2>28;WENN(AD14>0;DATUM(Geschäftsjahr;WENN(ISTNV(SVERWEIS($A$8;Tage_Monat;3;FALSCH));0;SVERWEIS($A$8;Tage_Monat;3;FALSCH));AD14);““);““)
AD9 Bezeichnung =WENN(AD3=““;““;WENN(AD5=““;TEXT(AD3;“TTTT“);AD5))
AD14 Tag =WENN(B2>28;29;““)
AE3 Datum =WENN(B2>29;WENN(AE14>0;DATUM(Geschäftsjahr;WENN(ISTNV(SVERWEIS($A$8;Tage_Monat;3;FALSCH));0;SVERWEIS($A$8;Tage_Monat;3;FALSCH));AE14);““);““)
AE9 Bezeichnung =WENN(AE3=““;““;WENN(AE5=““;TEXT(AE3;“TTTT“);AE5))
AE14 Tag =WENN(B2>29;30;““)
AF3 Datum =WENN(B2>30;WENN(AF14>0;DATUM(Geschäftsjahr;WENN(ISTNV(SVERWEIS($A$8;Tage_Monat;3;FALSCH));0;SVERWEIS($A$8;Tage_Monat;3;FALSCH));AF14);““);““)
AF9 Bezeichnung =WENN(AF3=““;““;WENN(AF5=““;TEXT(AF3;“TTTT“);AF5))
AF14 Tag =WENN(B2>30;31;““)
In den einzelnen Formeln wird wiederum Bezug auf die ausgeblendeten Hilfstabellen genommen.

Zur Erklärung noch eine kurze Erläuterung zu den einzelnen Formeln anhand der Bezeichnung.

Jahr
Hier wird Bezug auf das Jahr, welches im Tabellenblatt "Einstellungen" festgelegt wurde genommen. Die entsprechende Zelle hat auch die Bezeichnung Geschäftsjahr

Monat
In der Zelle A8 kann der jeweilige Monat aus einer Liste unter Bezug auf den Namen Monat ausgewählt werden. Innerhalb des Tabellenblatt "Monat" kann hier jeder Monat ausgewählt werden, bei der Anlage der einzelnen Monate wird später der entsprechende Monat vorausgewählt, so dass hier im Januar der Monat Januar ausgewählt ist usw.. Wie erwähnt ist die Definition von Dropdownliste in Excel im Artikel "Formulare gestalten in Excel" beschrieben.

Tage im Monat
In der Zelle B2 wird über die Formel SVERWEIS die Anzahl der Tage im entsprechend gewählten Monat ausgegeben. Dieses kann von 28 bis 31 jeder Wert sein (Januar hat 31 Tage, Februar 28 oder 29, März 31 Tage, April 30 Tage und so weiter).

Tag
Abhängig vom Wert in der Spalte B2 werden hier entweder ein fester Wert (für die Tage 1 bis 28) oder je nach Wert in der Zelle B2 in den Zellen AD14, AE14 und AF14 die Werte 29 bis 31 ausgegeben. Wenn der Monat nicht ausreichend Tage hat, wird hier "" ausgegeben.

Datum
In der Zeile Datum werden abhängig von der Zeile 14 (entspricht Tag) das Datum aus Geschäftsjahr, Monat und Tag ausgegeben. Aus der Tabelle Monate wird zum Text des Monats der Monat als Zahl ermittelt. So ist dieses bei Januar 1 und bei Dezember 12. Um etwaige Fehler zu vermeiden wurde die Formel noch mit ISTNV erweitert. Sofern die Hilfstabellen korrekt sind, kann dieser Part auch ausgespart werden.

Die Verwendung von ISTNV im SVERWEIS ist im Artikel "SVERWEIS ohne NV und dynamische Größen für Datenbereiche " beschrieben.

Wenn die Zeile Tag keinen Wert hat wird hier ebenfalls "" ausgegeben.

Feiertag
In der Zeile 3 wird als Feiertag die dritte Spalte aus Feiertage_Hessen ausgegeben, bzw. wenn das Datum nicht als gesetzlicher Feiertag in Hessen relevant ist der Wert 0.

In der Tabelle Feiertage wurde in der dritten Spalte eine 2 eingetragen, sofern der entsprechende Feiertag ein gesetzlicher Feiertag in Hessen ist.

Bezeichnung Feiertag
Handelt es sich beim Datum um einen Feiertag wird in der Zeile 5 die Bezeichnung des Feiertages ausgegeben. Dieses ist unabhängig davon, ob der Tag ein gesetzlicher Feiertag oder nicht ist. Denkbar ist es hier bspw. das Datum des Betriebsausfluges schon Anfang des Jahres festzulegen ;-).

Wochenende
Hier wird anhand des vorhandenen Datums festgestellt um den wievielten Wochentag es sich handelt. Sofern der Wochentag größer als 5 (beginnend mit Montag = 1 und somit nach Freitag = 5) ist wird hier eine 1 ausgegeben. Andernfalls wird hier eine 0 ausgegeben.

Hilfstabelle
In der Zeile 7 werden sowohl die Zeile 4 (gesetzlicher Feiertag = 2 sonst 0) und Wochenende (1 oder 0) addiert.

Hierdurch können Feiertage und Wochenenden voneinnander unterschieden werden.

An einen Wochenende ist der Wert = 1 und an einen Feiertag > 1.

Man könnte auch sagen, dass ein Feiertag mehr Wert als ein Wochenende hat.

Bezeichnung
Hier wird die Bezeichnung des Tages ermittelt. Handelt es sich um einen Feiertag wird der Wert aus der Zeile 5 "Bezeichnung Feiertag" genommen. Ansonsten wird über die Formel TEXT die Bezeichnung des Wochentages ermittelt.

Die Formel hat dabei das Format =TEXT(Wert;Textformat)

Wenn nun das Datum in der Zelle B3 steht kann der Wochentag über die Formel
=TEXT(B3;"TTTT") (ergibt den Wochentag in Langform bspw. Montag) oder über =TEXT(B3;"TTT") (ergibt den Wochentag in Kurzform bspw. Mo) ermittelt werden.

Für das Monatsblatt soll die Tagesbezeichnung in Langform daher TTTT genommen werden. Ferner sind die Zellen über Zelle Formatierung in der Registerkarte Ausrichtung um 90 Grad gedreht dargestellt.

Tagesbezeichnung um 90 Grad gedreht

Besonderheiten
Wie beschrieben wurde die Problematik eines Schaltjahres, bzw. der unterschiedlichen Monatslänge, in den Spalten AD bis AF durch eine Anpassung der Formeln in der Zeile 3 (Datum), 9 (Bezeichnung) und 14 (Tag) angepasst.

Die übrigen Formeln können entsprechend übertragen werden.

4. Bedingte Formatierung


Über die Funktion "Bedingte Formatierung" sind entsprechende Formatregeln für jede Spalte hinterlegt um Wochenende oder Feiertage besonders hervorzuheben bzw. die Tage 29 bis 31 im Bedarfsfall auszublenden (ohne Rahmen und ohne Farbhintergrund) festzulegen.

Bedingte Formatierung für Datumsspalte

Im Standardfall sind die einzelnen Spalten wie im rot umrandeten Beispielfall formatiert. Der Hintergrund des Tages und der Tagesbezeichnung ist gelb und die einzelnen Beschäftigten haben ein entsprechende Zelle für den Urlaubstag. Sofern es sich um eine Wochenende handelt (Zeile 7 =1) wird die Spalte blau hervorgehen. Wenn es sich um einen Feiertag handeln würde (Zeile 7 >1) wäre die Hervorhebung grau)

Wenn in der Zeile 3 "Datum" kein Datum angegeben ist werden die Zellen auch ohne Formatierung (weißer Hintergrund, kein Rahmen) ausgegeben.

5. Makro zum Anlegen der einzelnen Kalendermonate


Zwischenergebnis:
Im Kalenderblatt Monat könnte nun jeder Monat einzeln ausgewählt werden und das Blatt Monat entsprechend kopiert werden und als entsprechender Monat umbenannt werden.

Wie im Artikel "Excel Umgang mit Makros und Visual Basic for Applications (VBA)" beschrieben eignet sich hier der Einsatz eines Makros.

Grundlage Tabellenblatt Monat
Das Tabellenblatt Monat stellt dabei die Grundlage für alle Monate dar und enthält die Liste der einzelnen Beschäftigten:

Tabellenblatt Monat mit Beschäftigtenliste

Hier ist in der Zelle B1 (im Gegensatz zum hier beschriebenen Artikel) der Monat eingetragen.

Im Blatt Einstellungen kann dann das entsprechende Kalenderjahr eingetragen werden und über die Schaltfläche "Kalenderblätter anlegen" ein Makro zum Kopieren und Umbenennen des Tabellenblatt "Monat" gestartet werden.


Tabellenblatt Einstellungen zur Wahl des Kalenderjahres

Das entsprechende Makro "Kalender anlegen" hat dabei folgenden VBA Code:

Sub Kalender_anlegen()

'
' Kalender_anlegen Makro
' Sofern die Beschäftigtenliste vollständig ist, werden die einzelnen Monatsblätter kopiert
'

'
Range("B1").Select
Sheets("Monat").Visible = True
Sheets("Monat").Select
Range("B1").Select
Sheets("Monat").Copy After:=Sheets(5)
Sheets("Monat (2)").Select
Sheets("Monat (2)").Name = "Januar"
Range("B1").Select
ActiveCell.FormulaR1C1 = "Januar"
Sheets("Januar").Select
Sheets("Januar").Copy After:=Sheets(6)
Sheets("Januar (2)").Select
Sheets("Januar (2)").Name = "Februar"
Range("B1").Select
ActiveCell.FormulaR1C1 = "Februar"

Sheets("Februar").Select
Sheets("Februar").Copy After:=Sheets(7)
Sheets("Februar (2)").Select
Sheets("Februar (2)").Name = "März"
Range("B1").Select
ActiveCell.FormulaR1C1 = "März"

Sheets("März").Select
Sheets("März").Copy After:=Sheets(8)
Sheets("März (2)").Select
Sheets("März (2)").Name = "April"
Range("B1").Select
ActiveCell.FormulaR1C1 = "April"

Sheets("April").Select
Sheets("April").Copy After:=Sheets(9)
Sheets("April (2)").Select
Sheets("April (2)").Name = "Mai"
Range("B1").Select
ActiveCell.FormulaR1C1 = "Mai"

Sheets("Mai").Select
Sheets("Mai").Copy After:=Sheets(10)
Sheets("Mai (2)").Select
Sheets("Mai (2)").Name = "Juni"
Range("B1").Select
ActiveCell.FormulaR1C1 = "Juni"


Sheets("Juni").Select
Sheets("Juni").Copy After:=Sheets(11)
Sheets("Juni (2)").Select
Sheets("Juni (2)").Name = "Juli"
Range("B1").Select
ActiveCell.FormulaR1C1 = "Juli"


Sheets("Juli").Select
Sheets("Juli").Copy After:=Sheets(12)
Sheets("Juli (2)").Select
Sheets("Juli (2)").Name = "August"
Range("B1").Select
ActiveCell.FormulaR1C1 = "August"

Sheets("August").Select
Sheets("August").Copy After:=Sheets(13)
Sheets("August (2)").Select
Sheets("August (2)").Name = "September"
Range("B1").Select
ActiveCell.FormulaR1C1 = "September"

Sheets("September").Select
Sheets("September").Copy After:=Sheets(14)
Sheets("September (2)").Select
Sheets("September (2)").Name = "Oktober"
Range("B1").Select
ActiveCell.FormulaR1C1 = "Oktober"

Sheets("Oktober").Select
Sheets("Oktober").Copy After:=Sheets(15)
Sheets("Oktober (2)").Select
Sheets("Oktober (2)").Name = "November"
Range("B1").Select
ActiveCell.FormulaR1C1 = "November"


Sheets("November").Select
Sheets("November").Copy After:=Sheets(16)
Sheets("November (2)").Select
Sheets("November (2)").Name = "Dezember"
Range("B1").Select
ActiveCell.FormulaR1C1 = "Dezember"

Sheets("Monat").Select
ActiveWindow.SelectedSheets.Visible = False
Sheets("Anleitung").Select
ActiveWindow.SelectedSheets.Visible = False

Sheets("Einstellungen").Select
ActiveWindow.SelectedSheets.Visible = False

End Sub


Hierbei wird das Blatt Monat als Kopiervorlage für die Monate Januar bis Dezember genutzt und zum Abschluss das Tabellenblatt Monat ausgeblendet.


Ferner werden die Tabellenblätter "Monat", "Anleitung" und "Einstellungen" ausgeblendet.

Im Ergebnis ist damit das gesamte Jahr mit einzelnen Monatsblättern angelegt.

Ergebnis Urlaubsplan Jahr

Danach kann die Tabelle entsprechend gespeichert werden. Hier bietet sich dann die Speicherung als XLS (oder XLSX) ohne Makro an.

Da das Tabellenblatt "Monat" einen Blattschutz hat (es sollen nur bestimmte Personen den Urlaub übertragen können), wird dieser ebenfalls mit kopiert.

Somit kann hier die tagesgenaue Planung von Urlaub beginnen. Die Grundtabelle (ohne angelegten Monate) kann auch für die Folgejahre wieder genutzt werden und sollte daher entsprechend zum Beispiel als "Vorlage_Urlaubsplaner mit Makro für Kopie der Monatsblätter.xlsm" gespeichert werden.

6. Urlaubskontingente verwalten


Sofern auch das Urlaubskonto von Beschäftigten mit in Excel verwaltet werden soll bietet sich noch eine Anpassung in der Vorlage Tabellenblatt "Monat" an.

Über die Excelfunktion ZÄHLENWENN kann am Ende eines jeden Monats gezählt werden, wieviel Urlaub genommen worden ist.

Die Funktion ZÄHLENWEN zählt gefüllte Zellen eines Bereiches, wenn diese ein bestimmtes Kriterium entsprechen. Sollen mehrere Kriterien überprüft werden, bietet sich hier die Funktion ZÄHLENWENNS an.

Im Beispiel soll jedoch nur der genommene Jahresurlaub gezählt werden.

Hierzu bekommt die Farblegende für den Jahresurlaub noch den Wert "JU".

Beispiel Zählenwenn

In der Zelle AH62 wird durch die Formel =ZÄHLENWENN(B62:AF62;$AH$11) der Urlaub im entsprechenden Monat gezählt.

Soll neben dem Jahresurlaub auch der Sonderurlaub gezählt werden (sprich JU und SU bzw. Zellen AH11 und AH13 wäre die Formel wie folgt anzupassen =ZÄHLENWENNS(B62:AF62;AH11;B62:AF62;AH13) wobei dieses natürlich nicht nur in der Zelle AH62 sondern für jede beschäftigte Person zu ergänzen wäre.

Dieses kann für alle Beschäftigten im Monat erfolgen.

Nach Start des unter 5. beschriebenen Makros zur Anlage der einzelnen Monatsblätter kann nun eine Vorlage für ein Urlaubskonto je Beschäftigten angelegt werden, indem in jeden einzelnen Monat aus der Spalte AH der genommene Urlaub heraus gerechnet wird.


Da noch keine Einzelmonate angelegt worden sind bietet es sich an, ein weiteres Makro anzulegen, dass dann eine Vorlage für die Urlaubsübersicht anlegt.

Hierbei wird für die erste Beschäftigte der Urlaubsplan angelegt, so dass im Folgenden die weiteren Beschäftigten per Autoausfüllen ergänzt werden können.

Dieses ist erforderlich, da nicht ohne Vorhandener Arbeitsblätter eine Formel wie geplanter Urlaub = Januar!AH15+Februar!AH15 ... usw. angelegt werden kann.

Die elegantere Methode dürfte jedoch sein, das Monatsblatt als Vorlage mit der Summe der genommenen Urlaube (über ZÄHLEWENN) zu erstellen und ein Tabellenblatt in einer fertigen Jahresplan zu erstellen. Hier kann dann das Tabellenblatt "Urlaubskonto" von der alten in die neue Exceltabelle kopiert werden (rechte Maustaste auf die Registerkarte und Kopieren in die neue Datei).

Da die Formel auch hier relativ auf die Monate Januar bis Dezember verweist, kann diese dann als Vorlage genommen werden.

7. Nachtrag Vorlage & Copyright

Da ich vermehrt um die fertige Exceldatei angefragt worden bin habe ich die 💾 Datei als Vorlage ebenfalls online gestellt. Es ist sicherlich sinnvoll diese später als Vorlage_Urlaubsplaner.xlsm zu speichern. Nachdem das Makro ausgeführt wurde (und alle Monatsblätter angelegt worden sind) ist es sinnvoll die Datei dann als Urlaubsplaner_Jahr zu speichern. Das Blatt Feiertage ist im Gegensatz zum Blatt Monate nicht ausgeblendet, so dass die Feiertage noch auf das eigene Bundesland angepasst werden können. Die unter 6. erläuterte Variante zur Urlaubskontingenteverwaltung ist dabei allerdings noch nicht umgesetzt kann aber wie unter 6. erläutert entsprechend der lokalen Erfordernisse leicht eingefügt werden.

Die Datei dient ausschliesslich der Darstellung dieses Artikels und darf nicht für eigene Veröffentlichungen verwendet werden. Sofern Sie die Datei persönlich (oder an der Arbeit) verwenden möchten habe ich nichts dagegen. Es sollte jedoch die Urheberangabe (URL zu diesen Artikel) erhalten bleiben. Selbstverständlich kann das Blatt Monat an ihre eigenen Bedürfnisse (Abteilung, Name, Abwesenheiten etc.) angepasst werden.

Eine kurze Rückmeldung, gerne in Form eines Kommentars, würde mich sehr freuen.

Da der Artikel an sich schon sehr umfangreich ist hoffe ich, dass mit der beigefügten Datei die einzelnen Formeln noch klarer werden.

Für weitere Rückfragen stehe ich gerne zur Verfügung. Hierzu ist insbesondere die Kommentarfunktion unterhalb dieses Artikels gedacht. Dieses hat auch den Vorteil, dass andere Personen mit vergleichbaren Fragen ebenfalls von einer Antwort profitieren.

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, 29. Dezember 2013
14:45 Uhr

Excel Umgang mit Makros und Visual Basic for Applications (VBA)

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

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

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

Das Ergebnis sieht dabei wie folgt aus:
Grundliste mit Leerzeichen

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

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

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

Makro aufzeichnen

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

Symbolleiste Ansicht - Gruppe Makros

als auch in der sogenannten Entwicklerleiste.

Dieses Symbolleiste mit verschiedenen Symbolen zur Entwicklung von Makros und VBA Elementen kann wie folgt aktiviert werden:
  • Excel 2010
    • Registerkarte Datei
    • Optionen
    • Menüband anpassen
    • Unter der Auswahl Menüband anpassen
      Hauptregisterkarten
    • Aktivieren des Hacken bei Entwicklertools
  • Excel 2007
    • Microsoft Office Schaltfläche(Windows Ball linke Ecke)
    • Excel Optionen
    • Häufig verwendet
    • Aktivieren von "Entwicklerregisterkarte in der Multifunktionsleiste anzeigen"
Nun ist neben der Registerkarte Ansicht auch eine Registerkarte "Entwicklertools" vorhanden.
Symbolleiste Entwicklertools

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

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

Makro aufzeichnen

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

EXKURS: Persönliche Makroarbeitsmappe

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

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

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

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

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

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


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

Dieses gilt sowohl für die Entwicklersymbolleiste:

Aufzeichnung beenden auf Entwicklersymbolleiste

als auch in der allgemeinen Makrofunktion in der Symbolleiste Ansicht

Aufzeichnung beenden auf Ansichtsymbolleiste

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

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

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

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

Excel - Makros verwalten

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

VBA - Microsoft Visual Basic for Application Editor

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

  • Sub Export()
    ' Dieses weist den Namen Export zu
    Sheets("Endtabelle").Select
    Sheets("Endtabelle").Copy After:=Sheets(4)


    ' Diese Anweisung selektiert das Blatt Endtabelle und kopiert es hinter das 4. Arbeitsblatt

    Sheets("Endtabelle (2)").Select
    Sheets("Endtabelle (2)").Name = "Export.csv"


    ' Nun wird die Kopie in Export.csv umbenannt

    ActiveWorkbook.Worksheets("Export.csv").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Export.csv").Sort.SortFields.Add Key:=Range( _
    "A2:A1840"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
    xlSortNormal
    With ActiveWorkbook.Worksheets("Export.csv").Sort
    .SetRange Range("A1:G1840")
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
    End With



    'Nun werden die Zellen A1 bis G1840 nach der Spalte A ab Zeile 2 bis 1840 sortiert

    ' Soweit stammt der Code auch aus der Makroaufzeichnung. Nun wird dieser noch um eine Anweisung ergänzt, dass das Tabellenblatt unter Verwendung des Buchungskreises in der Spalte A umbenannt wird

    ActiveSheet.Name = "Export_" & Range("A2") & ".csv"

    ' Die Bezeichnung wird aus der Zelle A2 des gerade sortierten Tabellenblatt übernommen


    'Durch End Sub kann der Code dann abgeschlossen werden
    End Sub


  •  


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

Ergebnis für CSV Export

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

Formularsteuerelemente einfügen

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

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

Steuerelement Schaltfläche einfügen

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

Makro Schaltfläche zuweisen

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

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

Text der Schaltfläche bearbeiten

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

Beispiel Schaltfläche mit Text

Somit ist die Tabelle mittlerweile fertig.

Hinweis: Speichern Excel Arbeitsmappe mit Makros

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

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

Dateityp mit Endung XLSM

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

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

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

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


Freitag, 29. November 2013
17:27 Uhr

SVERWEIS ohne NV und dynamische Größen für Datenbereiche

Im Rahmen eines Rechercheberichtes soll an mehreren Standorten eine vorgegebene Bilanzversion ausgewertet werden und die einzelnen Bilanzpositionen dann in einer einheitlichen Form in Excel dargestellt werden. Im Artikel Rechercheberichte im Modul FI (Bilanzanalyse) wurde schon beschrieben, wie eine Bilanzversion in SAP ausgewertet werden kann.

Der entsprechende Recherchebericht kann dann nach Excel exportiert werden und hier als Datengrundlage verwendet werden.

Jedoch sind hier, bedingt durch die Darstellung der Daten zwei Probleme zu beachten: Beide Probleme werden anhand des beschriebenen Berichtes erläutert.



Ausgangslage
Problematisch ist, dass in einen Recherchebericht nur Merkmale (Bilanzpositionen) ausgegeben werden, die auch gebucht worden sind.

Um hier einen einheitlichen Namen auszugeben besteht die Möglichkeit die Darstellung der einzelnen Bilanzpositionen von der Darstellung des Namens auch auf die Schlüsselbezeichnung umzustellen.

Dieses ist im ausgeführten Recherchebericht unter
  • Einstellungen
  • Merkmalsdarstellung
  • Durch den Punkt "Schlüssel und Bezeichnung"
möglich.

Dieser Recherchebericht kann dann nach Excel exportiert werden:

Hier werden dann die Kopfdaten ausgegeben und die einzelnen Bilanzpositionen untereinander. Hierbei werden dann alle GuV-Knoten mit ausgegeben.


Sieht der Bericht zum Beispiel einen Knoten 921 vor, der aber am entsprechenden Standort nicht gebucht wurde, so werden hier in der Grundtabelle auch keine Werte ausgegeben.

Weitere Beispiele wären:
  • Liste mit Plandaten, die nicht an jeder Kostenstelle auftreten
  • Korrekturwerte für einzelne Bereiche
  • Budgetwerte die noch nicht im System eingebucht worden sind
. Der erste Gedanke wäre sicherlich hier eine Datenbanklösung zu basteln und entsprechende Abfragen miteinander zu verknüpfen.

Sofern sich die Struktur der Daten jedoch nicht weiter ändert und die Einstiegshürde relativ gering gehalten werden soll ist hier auch Excel eine gute Lösung.




Die Funktion SVERWEIS ohne #NV ausgeben


Grundsätzlich ist der Aufbau der Formel SVERWEIS wie folgt:

=SVERWEIS(Suchkriterium;Matrix;Spaltenindex;Bereich Verweis(WAHR ungefähre Übereinstimmung FALSCH genaue Übereinstimmung)


Wird nun in Excel mit der Formel SVERWEIS gearbeitet, so wird hier als Ergebnis #NV ausgegeben, sofern das Suchkriterium nicht innerhalb der Matrix vorhanden ist.

Um dieses zu vermeiden besteht die Möglichkeit über eine Wenn-Dann Funktion die SVERWEIS Ausgabe zu überprüfen. Hier könnte ab Excel 2007 die Funktion WENNFEHLER angewandt werden. Diese gibt nur einen Wert einer Funktion aus, sofern es keine Fehlermeldung gibt.

Beispiel:
=WENNFEHLER(FORMEL;Wert_falls_Fehler)

Sofern auch frühere Excel Versionen genutzt werden, sollte eine Wenn-Dann Funktion in Kombination mit der Formel ISTNV genutzt werden.

Nehmen wir an, dass die technische GuV Position im Beispiel SKR04/400 lautet und die Werte innerhalb einer Matrix der wir den Namensraum Grunddaten gegeben haben liegen, dann würde die Formel:


=WENN(ISTNV(SVERWEIS("*SKR04/400*";Grunddaten;$B$7;FALSCH));0;SVERWEIS("*SKR04/400*";Grunddaten;$B$7;FALSCH))


Wobei hier der auszugebende Wert in der Spalte der Matrix definiert ist, der in der Zelle B7 angegeben ist. Sofern im genutzten Recherchebericht die Ist-Werte in der 2. Spalte (und 2 in der Zelle B7 eingetragen ist) liegen wird entweder der gefundenen Wert in der 2, Spalte der Matrix ausgegeben, oder eine 0 ausgeben.

Allgemein ausgedrückt bieten sich hier also folgende Varianten an:

=WENNFEHLER (SVERWEIS(Kriterium;Matrix;Spaltenindex;falsch))

oder über die Hilfsfunktion ISTNV

=WENN(ISTNV (SVERWEIS(Kriterium;Matrix;Spaltenindex;falsch);""; SVERWEIS(Kriterium;Matrix;Spaltenindex;falsch))





Die Funktion BEREICH.VERSCHIEBEN - Datenbereich dynamisch von der Größe anlegen

Im Beispiel wird die Namensbezeichnung Grunddaten verwendet. Dieses kann sinnvoll sein, wenn bei den eingespielten Grunddaten (im Beispiel eine GuV) nicht klar ist, wie viel Zeilen und Spalten der Datenbereich hat. Je nach genutzten Recherchebericht können hier unterschiedliche Spalten und natürlich auch Zellen vorhanden sein.

Daher kann es hilfreich sein, diesen Bereich über eine dynamische Formel zuzuweisen.

Unter der Annahme, dass der Recherchebericht in das Tabellenblatt Grunddaten kopiert wird, kann über den Namensmanager per Formel ein dynamischer Bereich definiert werden.

Der Namensmanager findet sich unter Excel 2003 war unter Einfügen, Namen, Definieren möglich. Ab Excel 2007 ist diese Funktion innerhalb des Ribbon Formel auf der Schaltfläche Namen definieren zu finden.

Namensmanager bezieht sich auf Bereich verschieben

Hier hat der Name Grunddaten folgende Formel als Wert hinterlegt:

=Bereich.Verschieben($A$1;;;Anzahl2($A:$A);Anzahl2($5:$5))

Durch die Formel Bereich Verschieben wird ab der ZELLE1 der Bezug auf die Höhe als Anzahl der Zeilen in der Spalte A und auf Breite als Anzahl der Spalten in der Zeile 5 gezählt.

Dieses ist erforderlich, da im Beispiel erst ab Zeile 5 die Überschriften des Rechercheberichtes erscheinen und in den Spalten 1 bis 4 noch die Kopfdaten des Rechercheberichtes angegeben werden.

Durch die Formel Anzahl2 wird die Anzahl der "nicht leerer Zellen" eines Bereiches festgelegt. Im Beispiel erfolgt die Zählung ab Zeile 5.

Die Funktion BEREICH.VERSCHIEBEN

Zur Erklärung lautet der eigentliche Syntax der Formel BEREICH.VERSCHIEBEN wie folgt

BEREICH.VERSCHIEBEN(Bezug; Zeilen; Spalten; [Höhe]; [Breite])

Bezug ist hierbei ab welche Position der Bereich verschoben werden soll.

Zeilen wäre um welche Anzahl von Zeilen der Bereich verschoben werden soll, da aber alle Daten erfasst werden sollen, ist dieser im Beispiel durch ;; auf 0 gesetzt.

Spalten wäre um welche Anzahl von Spalten der Bereich verschoben werden soll, da aber alle Daten erfasst werden sollen, ist dieser im Beispiel durch ;; auf 0 gesetzt.

Höhe beschreibt den Bezug in Zeilen des neuen Bereiches (daher wurden hier die Anzahl der nicht leeren Zellen gezählt um den gesamten Bericht als Bezug zu erhalten)

Breite beschreibt den Bezug in Spalten des neuen Bereiches (daher wurden hier die Anzahl der nicht leeren Zellen gezählt um den gesamten Bericht als Bezug zu erhalten)

Sowohl Höhe als auch Breite sind in dieser Formel optional erfüllen jedoch exakt den Zweck, den die Zuweisung des Bereiches für die Grunddaten nützlich macht.


FAZIT
Gerade der dynamische Größenbereich für Datenbereiche ist nicht nur für dieses Beispiel geeignet sondern auch sehr nützlich um den Datenbereich einer Pivot-Tabelle zu definieren.
Hier bietet sich aber oftmals folgende Formelzuweisung als Datengrundlage einer Pivot-Tabelle an:

=Bereich.Verschieben($A$1;;;Anzahl2($A:$A);Anzahl2($1:$1))

Hier werden dann tatsächlich die Anzahl der Zeilen und die Anzahl der Spalten ab der Zelle A1 gezählt. Durch die Möglichkeit diese Formel ebenfalls per Namensmanager einen Namen zuzuweisen kann beim Einfügen einer Pivot-Tabelle über Ribbon Einfügen und hier auf Pivottabelle einfügen der entsprechende Name als Grundlage zugewiesen werden.
Pivottabelle erstellen - Bereich festlegen
Durch die Zuweisung des Bereichs Grunddaten als Datengrundlage wird dieser Bereich auch entsprechend aktualisiert, wenn in der entsprechenden Tabelle weitere Datenzeilen hinzugefügt 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


Dienstag, 9. Juli 2013
22:16 Uhr

Excel: Hyperlinks zu externen Arbeitsmappen identifizieren

Ausgangslage

Beim Aufruf einer Excelmappe kommt es ab Excel 2007/2010 zur Fehlermeldung "Die automatische Aktualisierung von Hyperlinks wurde deaktiviert". Hintergrund ist, dass Excel hier nicht mehr aktiv Werte aktualisiert sondern innerhalb der Excelmappe mit vertrauenswürdigen Quellen arbeitet. Innerhalb der Fehlermeldung besteht auch die Möglichkeit innerhalb der Vertrauenseinstellungen für externen Inhalt eine entsprechende Aktualisierung zuzulassen.

Dieses ist sicherlich sinnvoll bei einzelnen Arbeitsmappen. Sofern in einer Arbeitsmappe jedoch mit unterschiedlichen Quellen gearbeitet wurde und eine finale Version erstellt werden soll in der alle abgestimmten Daten enthalten sind, besteht die Notwendigkeit diese Verweise zu identifizieren und entsprechend zu ändern.


Lösung

Manchmal ist die Lösung für solche Probleme relativ einfach. Ein Verweis auf eine andere Exceltabelle als Quelle wird innerhalb Formel mit eckigen Klammern [] ausgedrückt.

Um nun solche Verweise zu finden, besteht die Möglichkeit in der gesamten Arbeitsmappe nach "[" in Formeln zu suchen. Hierzu sollten die Optionen in der Funktion "Suchen" in folgenden Punkten geändert werden:

Durchsuchen: Arbeitsmappe
Suchen in : Formeln

und nun kann nach [ gesucht werden.

In Excel 2003 ist der Suchdialog unter Bearbeiten->Suchen und in Excel 2007/2010 auf der Registerkarte Start in der Gruppe Bearbeiten durch "Suchen und Auswählen" zu finden.

Alternativ ist dieses auch über die Tastenkombination STRG + F möglich.

Nachtrag:
Es sollte tatsächlich nur nach [ in Formeln gesucht werden und nicht nach =[ da sonst Verknüpfungen nicht gefunden werden.Das = erübrigt sich durch die Option "Suchen in: Formeln".

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, 4. Juni 2013
18:20 Uhr

Inhaltsverzeichnis in Excel über vorhandene Tabellenblätter

Ausgangslage
Bei umfangreichen Tabellen (bspw. mehrere Tabellenblätter innerhalb einer Excelmappe) ist es oft sinnvoll im ersten Blatt eine Übersicht mit Definitionen oder ein Inhaltsverzeichnis anzulegen. Sinnvollerweise kann hier über die Funktion HYPERLINK auch auf die dahinter liegenden Excelmappen verwiesen werden. Dieses kann aber bei mehreren Tabellenblättern recht mühsam sein entsprechend diese anzulegen.



Im Beispiel gibt es ein Tabellenblatt für die Gesamtsicht und eines für einzelne Abteilungen, Fachbereiche und Verwaltung.

Lösung:
Durch einen Beitrag im VBA Entwickler-Treff von XING bin ich auf den Beitrag "Inhaltsverzeichnis für Arbeitsmappe automatisch erstellen" gestossen und habe dieses Makro für mich wie folgt angepasst.




    Sub Inhaltsverzeichnis()
    Dim intTab As Integer
    Dim tbl As Worksheet
    Dim intZeile As Integer

    Set tbl = Worksheets.Add(before:=Worksheets(1))
    intZeile = 2

    ' eingefügtes Blatt "Inhaltsverzeichnis" nennen
    Worksheets(1).Name = "Inhaltsverzeichnis"
    ActiveSheet.Name = Worksheets(1).Name
    Cells(1, 1).Value = "Enthaltene Blätter"

    For intTab = 2 To ActiveWorkbook.Worksheets.Count
    tbl.Cells(intZeile, 1).Value = Worksheets(intTab).Name

    tbl.Cells(intZeile, 1).Hyperlinks.Add _
    Anchor:=tbl.Cells(intZeile, 1), Address:="", SubAddress:= _
    "'" & Worksheets(intTab).Name & "'!A1", _
    ScreenTip:="Klicken Sie um zur Tabelle zu gelangen", _
    TextToDisplay:=Worksheets(intTab).Name


    intZeile = intZeile + 1
    Next intTab

    ' Spaltenbreite fixieren
    Worksheets(1).Cells.EntireColumn.AutoFit

    End Sub


In dieser Variante wird ein Blatt mit der Bezeichnung Inhaltsverzeichnis angelegt und mit entsprechenden Hyperlinks ergänzt. Dieses Beispiel ist im weiteren auch dokumentiert.

Angepasste Version:
Hier wird als Überschrift die Zelle A1 jeden Blattes übernommen und das erste Tabellenblatt ohne Namenszuweisung angelegt. Hierdurch kommt es zu keinen Fehler, solle ein Blatt mit der Bezeichnung Inhaltsverzeichnis schon vorhanden sein.


    Sub Inhalt_mit_Überschrift()
    Dim intTab As Integer
    Dim tbl As Worksheet
    Dim intZeile As Integer

    Set tbl = Worksheets.Add(before:=Worksheets(1))
    intZeile = 2


    ' Zellenüberschriften

    ActiveSheet.Name = Worksheets(1).Name
    Cells(1, 1).Value = "Überschrift"
    Cells(1, 2).Value = "Link"
    Cells(1, 1).Font.Bold = True
    Cells(1, 2).Font.Bold = True


    For intTab = 2 To ActiveWorkbook.Worksheets.Count

    ' Ausgabe der Zelle A1 eines jeden Arbeitsblattes als Überschrift


    tbl.Cells(intZeile, 1).Value = "='" & Worksheets(intTab).Name & "'!a1"

    'Übernahme der Registerblattfarbe als Schriftfarbe


    tbl.Cells(intZeile, 1).Font.Color = Worksheets(intTab).Tab.Color


    'Setzen eines Hyperlinks auf Tabellenblatt

    tbl.Cells(intZeile, 2).Value = Worksheets(intTab).Name

    tbl.Cells(intZeile, 2).Hyperlinks.Add _
    Anchor:=tbl.Cells(intZeile, 2), Address:="", SubAddress:= _
    "'" & Worksheets(intTab).Name & "'!A1", _
    ScreenTip:="Klicken Sie um zur Tabelle zu gelangen", _
    TextToDisplay:=Worksheets(intTab).Name


    intZeile = intZeile + 1
    Next intTab


    Worksheets(1).Cells.EntireColumn.AutoFit

    End Sub


Durch dieses Makro wird die vorhandene Tabelle um ein weiteres Blatt erweitert und in diesen werden alle vorhandenen Blätter mit einen entsprechenden Link aufgelistet.

Zur Erstellung von Makros ist bei Excel 2007 erst einmal die Registerkarte "Entwickleroptionen" unter den Punkt "Die am häufigsten verwendeten Optionen bei der Arbeit mit Excel" unter der Kategorie "Häufig verwende" zu aktivieren.

Nun findet sich der Makroeditor in der Symbolleiste "Entwicklertools" und kann hier direkt über Makros aufgerufen werden. In Excel 2010 ist dieser in der Symbolleiste Ansicht zu finden.

Hier kann nun im Feld Makroname ein Name bspw. Inhaltsverzeichnis eingetragen werden und durch den Klick auf "Erstellen" ein Makro angelegt werden.

Dabei wird durch sub Name und endsub() das entsprechende Makro angelegt. Hier kann das oben angegebene Makro eingefügt werden.

Makrocode einfügen

Nun kann das VisualBasic Fenster geschlossen werden und zur eigentlichen Tabelle zurückgekehrt werden.

Das Makro ist nun direkt gespeichert und kann über den Makroeditor wiederum durch "Ausführen" gestartet werden.

Makro ausführen

Hierbei hat das Makro unabhängig vom gewählten Makronamen die im Code mit sub Name() angegebene Bezeichnung. Im Beispiel also Inhaltsverzeichnis.

Im Ergebnis sieht das erste Tabellenblatt nun wie folgt aus:
Inhaltsverzeichnis

Durch einen Klick auf einen entsprechenden Link kann auch direkt auf das Exceltabellenblatt gewechselt werden.

Der Vorteil dieses Makro ist, dass es unabhängig von bereits vorhandenen Tabellenblättern angelegt werden kann und in der Reihenfolge der Tabellenblätter eine Übersicht über die einzelnen Tabellenblätter erstellt.

Es ist sinnvoll dieses Makro in Verbindung mit den im Artikel Office Integration - Excelansicht in SAP und Daten kopieren nach Excel vorgestellten Makros zur Übernahme von Daten aus Buchhaltungssystemen in eine persönliche Makroarbeitsmappe (siehe Artikel Excel Umgang mit Makros und Visual Basic for Applications (VBA) zu übernehmen.

Zusammenfassung (Zwei Makros mit A1 oder A2 als Überschrift)

    Sub Inhalt_mit_Überschrift_aus_a1()
    '
    ' Inhalt_mit_Überschrift_aus_a1 Makro
    ' Erstellt ein Inhaltsverzeichnis aller Tabellenblaetter und verwendet als Überschrift die Zelle A1 eines jeden Tabellenblattes
    '

    '
    Dim intTab As Integer
    Dim tbl As Worksheet
    Dim intZeile As Integer
    Set tbl = Worksheets.Add(before:=Worksheets(1))
    intZeile = 2
    ActiveSheet.Name = Worksheets(1).Name
    Cells(1, 1).Value = "Überschrift"
    Cells(1, 2).Value = "Link"
    Cells(1, 1).Font.Bold = True
    Cells(1, 2).Font.Bold = True
    For intTab = 2 To ActiveWorkbook.Worksheets.Count
    tbl.Cells(intZeile, 1).Value = "='" & Worksheets(intTab).Name & "'!a1"
    tbl.Cells(intZeile, 1).Font.Color = Worksheets(intTab).Tab.Color
    tbl.Cells(intZeile, 2).Value = Worksheets(intTab).Name
    tbl.Cells(intZeile, 2).Hyperlinks.Add _
    Anchor:=tbl.Cells(intZeile, 2), Address:="", SubAddress:= _
    "'" & Worksheets(intTab).Name & "'!A1", _
    ScreenTip:="Klicken Sie um zur Tabelle zu gelangen", _
    TextToDisplay:=Worksheets(intTab).Name
    intZeile = intZeile + 1
    Next intTab
    Worksheets(1).Cells.EntireColumn.AutoFit
    End Sub



    Sub Inhalt_mit_Überschrift_aus_a2()
    '
    ' Inhalt_mit_Überschrift_aus_a2 Makro
    ' Erstellt ein Inhaltsverzeichnis aller Tabellenblaetter und verwendet als Überschrift die Zelle A2 eines jeden Tabellenblattes
    '

    '
    Dim intTab As Integer
    Dim tbl As Worksheet
    Dim intZeile As Integer
    Set tbl = Worksheets.Add(before:=Worksheets(1))
    intZeile = 2
    ActiveSheet.Name = Worksheets(1).Name
    Cells(1, 1).Value = "Überschrift"
    Cells(1, 2).Value = "Link"
    Cells(1, 1).Font.Bold = True
    Cells(1, 2).Font.Bold = True
    For intTab = 2 To ActiveWorkbook.Worksheets.Count
    tbl.Cells(intZeile, 1).Value = "='" & Worksheets(intTab).Name & "'!a2"
    tbl.Cells(intZeile, 1).Font.Color = Worksheets(intTab).Tab.Color
    tbl.Cells(intZeile, 2).Value = Worksheets(intTab).Name
    tbl.Cells(intZeile, 2).Hyperlinks.Add _
    Anchor:=tbl.Cells(intZeile, 2), Address:="", SubAddress:= _
    "'" & Worksheets(intTab).Name & "'!A2", _
    ScreenTip:="Klicken Sie um zur Tabelle zu gelangen", _
    TextToDisplay:=Worksheets(intTab).Name
    intZeile = intZeile + 1
    Next intTab
    Worksheets(1).Cells.EntireColumn.AutoFit
    End Sub



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, 4. Juni 2013
17:00 Uhr

Deaktivierte Elemente (bspw. Ribbons) in Excel aktivieren

Adobe Acrobat Distiller nutzt zur Erzeugung von PDF aus Excel ein Extra Ribbon von dem aus die Druckfunktion (bzw. PDF Erstellungsfunktion) aufgerufen werden kann.Dieses wird durch ein Excel Add-In verwirklicht.

Nun kann es passieren, dass diese Symbolleiste ausgeblendet wird.

Innerhalb der Excel-Optionen befindet sich in der Kategorie Add-Ins beim Punkt "Verwalten:" die Auswahlfunktion "Deaktivierte Elemente" (Drop-Down Liste am Ende). Durch "Gehe zu" können hier einmal deaktivierte Elemente wieder aktiviert 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


Montag, 27. Mai 2013
19:22 Uhr

Mehrere Excelblätter einer Arbeitsmappe nebeneinander anzeigen

Ausgangslage
In einer Excelarbeitsmappe werden in einzelnen Tabellenblättern Daten gepflegt. Nun besteht aber die Notwendigkeit mehr als ein Tabellenblatt angezeigt zu bekommen.

Sofern man mehrere Arbeitsmappen offen hat ist dieses unter auf der Symbolleiste Ansicht im Punkt Fenster bei "Nebeneinander anzeigen" oder unter "Alle anordnen" möglich.

Lösung
Um nun aber auch verschiedene Arbeitsblätter einer Excelmappe nebeneinnader angezeigt zu bekommen bsteht die Möglichkeit in der Symbolleiste Ansicht die Funktion "Neues Fenster" innerhalb der Gruppe Fenster zu wählen.

Mehrere Mappen einer Exceltabelle anzeigen

Hierdurch wird eine Excelmappe in mehreren Fenstern geöffnet die dann ebenfalls über "Nebeneinnader anzeigen" oder über Fenster -> "Teilen" aufgeteilt werden können. Hierbei werden die dargestellten Excelblätter mit einer durchgehenden Fensternummerierung dargestellt (Eine Arbeitsmappe.XLSX:1, Eine Arbeitsmappe.XLSX:2, ...).

Die einzelnen Fenster können dabei auch geschlossen werden. Erst beim letzten Schließen kommt dann die Meldung "Wollen Sie speichern".

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


Donnerstag, 2. Mai 2013
16:25 Uhr

Gestapelte Säulendiagramme in Excel mit Summenausweis

Ausgangslage
Aus einer Tabelle bestehend aus Kostenstellen, Personalkosten, Sachkosten, AfA und Summe soll ein Stapeldiagramm in Excel erstellt werden in dem die einzelnen Kosten (Personal-, Sachkosten und AfA) dargestellt werden und darüberhinaus auch eine Summe dargestellt wird.

Lösung
Zwar ist es innerhalb der Exceldiagramme nicht möglich die dargestellten Wertachsen zu summieren, jedoch kann mit einer Hilfsachse innerhalb der dem Diagramm zugrunde liegenden Exceltabelle ein entsprechendes Ergebnis erzielt werden.

Hierzu wird folgende Tabelle als Grundlage genommen.
Excel Tabelle mit Hilfszeile (Summe)
Die Spalte "Gesamtkosten" berechnet sich dabei aus den Spalten B bis D.
Nun wird diese Tabelle markiert und über die Symbolleiste Einfügen über
Säule->
2D Säulen = Gestapelte Säulen
Excel -Gestapelte Säulendiagramm einfügen
ein gestapeltes Säulendiagramm über diese Tabelle eingefügt.

Hier werden nun die einzelnen Kosten als Säulen und die Kostenstellen als Legenden angezeigt.
Diagramm Kosten als Säule

Über die rechte Maustaste können nun Zeile und Spalten getauscht werden, so dass die Kostenarten als Legende (Reihen) und die Kostenstellen als Rubriken (als horizontale Achsen) dargestellt werden.

Diagramm Datenquelle auswählen

Im Ergebnis sieht das Diagramm nun wie folgt aus:
Diagramm Kostenstellen als Säule

Hier sind die einzelnen Kostenblöcke mit unterschiedlichen Säulenfarben dargestellt.

Nun markieren wir die Säule Gesamtkosten und wählen mit der rechten Maustaste die Option "Datenreihen formatieren". Hier geben wir als Füllung "keine Füllung" und als Rahmenfarbe "keine Linie" an.

Diagramm - Datenreihe formatieren

Im Ergebnis wird nun die Säule nicht mehr angezeigt. Dafür können wir nun über die Symbolleiste Layout die Legende oberhalb angezeigt werden. Hierbei kann dann auch die Legende "Gesamtkosten" markiert und gelöscht werden. Ferner kann hier über
Layout->
Datentabelle->
"Datentabelle mit Legendensymbol anzeigen"
auch eine Wertetabelle ausgegeben werden,in der die Gesamtkosten aufgeführt sind und darunter die Einzelkosten unterhalb des Diagramms aufgeführt sind.

Gestapeltes Säulendiagramm mit Summe

Dadurch, dass für Gesamtkosten weder eine Farbe noch Linien angegeben worden sind, erscheinen die Gesamtkosten in der Datentabelle aber auch im Diagramm als Überschrift, die auch entsprechend formatiert werden kann. In der Legende (unterhalb von "Diagrammtitel") kann der Begriff Gesamtkosten markiert und gelöscht werden. Auf diese Weise sind tatsächlich nur die drei Kostenblöcke als Legende angegeben. Ferner wurden die Einzelwerte der Gesamtkosten auf identische Höhe gezogen und fett markiert und unterstrichen.

Ein weiteres Beispiel für ein Säulendiagramm (3D-Säulendiagramm) ist im Artikel "Excel Pivottabelle Darstellung Grenzwerte Einnahmen auf Projekte je Person durch Zuordnung VZÄ auf verantwortlicher Kostenstelle" beschrieben um hier Einnahmen pro VZÄ auszuweisen.

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" und  "Excel: bedingte Formatierung mit Pfeilen (Darstellung Tendenzen bei Veränderungen)" 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


Samstag, 16. März 2013
12:59 Uhr

Excel: bedingte Formatierung mit Pfeilen (Darstellung Tendenzen bei Veränderungen)

Seit Excel 2007 bietet die bedingte Formatierung nicht mehr nur die Darstellung in Farben sondern auch eine Darstellung mit Symbolen (Pfeile, Ampel, etc.) an, die eine schnelle Bewertung von Veränderungen auf einen Blick ermöglicht.

Ausgangslage
Es sollen zwei Salden miteinander verglichen werden und die entsprechende Veränderung bzw. Tendenzen dargestellt werden. So sollen kleiner Veränderungen mit einen aufsteigenden oder absteigenden Pfeil (im Beispiel gelb) und größere Veränderungen mit einen Pfeil nach unten (in rot) bzw. nach oben (in grün) dargestellt werden.

Lösung
Hier bietet Excel eine Lösung auf der Symbolleiste "Start" durch die Funktion "bedingte Formatierung" an.

Nehmen wir an, dass wir eine Tabelle mit folgenden Spalten haben
  • Kostenstelle
  • Saldo 2011
  • Saldo 2012
  • Veränderung
Hierbei wird die Spalte Veränderung durch Saldo 2012 - Saldo 2011 berechnet und weist einen absoluten Wert aus.

Nun soll neben der absoluten Veränderung auch eine entsprechende Tendenz entweder nur als Pfeil oder ergänzend zum absoluten Wert dargestellt werden.

Um dieses zu erreichen sind die Zahlenwerte in der Spalte Veränderung zu markieren und unter der Schaltfläche
"Bedingte Formatierung" (im Ribbon Start" die Funktion "Neue Regel auszuwählen".

Als Regeltyp sollte der Punkt "Alle Zellen basiernd auf ihren Wert formatieren" gewählt werden. Formatstil sollte "Symbolsätze" sein

Unter Symbolart kann nun ausgewählt werden, welchen Symbolsatz (Ampfel, Pfeile etc.) verwendet werden sollen.

Im oberen Beispiel wäre dieses die Symbolart "5 Pfeile farbig".

Sofern die absoluten Werte verglichen werden sollen, ist als Regel der Typ "Zahl" zu verwenden, wodurch feste Wertgrenzen angegeben werden können. Andernfalls besteht auch die Möglichkeit hier Prozentwerte oder Formeln zu hinterlegen. Durch den Hacken "Nur Symbole anzeigen" besteht auch die Möglichkeit nur Symbole und nicht länger die Werte mit anzugeben.

Als Beispiel könnte die Regel wie folgt aussehen:
Excel: Beispiel bedingte Formatierung

Hier wird folgende Regel angewandt:
a) Veränderungen größer als 2500 führt zu einen grünen bzw. roten Pfeil nach oben oder nach unten.
b) Veränderungen zwischen 500 und 2500 werden als Tendenz mit einen aufsteigenden bzw. absteigenden Pfeil angezeigt.
c) Veränderungen bis zu 500 werden als stabil betrachtet.

Die verwendeten Symbole können dabei entsprechend auch verändert werden und so auch nur zwei Symbole grüner Pfeil nach oben, roter Pfeil nach unten und kein Zellensymbol gewählt werden. Allerdings besteht hier nur die Möglichkeit auf vorhandene Zellensymbole zurückzugreifen.


Eigener Symbolsatz für bedingte Formatierung verwenden (roter Pfeil nach oben)

Die verwendeten Symbole können dabei entsprechend auch verändert werden und so auch nur zwei Symbole grüner Pfeil nach oben, roter Pfeil nach unten und kein Zellensymbol gewählt werden. Allerdings besteht hier nur die Möglichkeit auf vorhandene Zellensymbole zurückzugreifen.

Verfügbare Symbolsätze bedingte Formatierung

Da in letzter Zeit oftmals die Frage aufkam, wie andere Symbole genutzt werden können (bspw. roter Pfeil nach oben. Hier fällt mir als Lösung nur ein über eine WENN Funktion die Unicodezeichen ▲ und ▼ entsprechend einzufärben. Ähnliche Möglichkeiten bieten natürlich auch andere Zeichen oder Sonderzeichen wie aus Symbolschriftarten.

Nachtrag August 2016:
Eine ausführlichere Beschreibung hat hier Martin Weiß im Artikel "Bedingte Formatierung mit eigenen Symbolen" auf tabellenexperte.de geschildert. Wobei mir besonders schön die optische Lösung von Symbol und Wert in einer Zelle gefallen hat.



 

Filter und Sortierung und weitere Beispiele zur bedingten Formatierung

Ein weiterer Vorteil der bedingten Formatierung ist auch im Artikel "Vorteil von Excel Formatvorlagen und Filter nach Farben oder Zellensymbolen aus bedingter Formatierung" dargestellt und kann gerade bei umfangreicheren Daten sehr hilfreich sein.

Auch im Artikel "Excel Pivottabelle Darstellung Grenzwerte Einnahmen auf Projekte je Person durch Zuordnung VZÄ auf verantwortlicher Kostenstelle" wird mit der bedingten Formatierung gearbeitet um in der Pivottabelle ergänzend zum Diagramm auch an den Grunddaten entsprechende Zuordnungen farblich dargestellt zu bekommen. Ein klassisches Beispiel ist auch die Formatierung von Feiertagen im Urlaubsplaner, wie sie im Artikel "Urlaubsplaner und dynamischer Kalender mit Monatsansicht (Urlaubsplaner 2015/2016 in Excel)" vorgestellt wurde.

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 Pivottabelle Darstellung Grenzwerte Einnahmen auf Projekte je Person durch Zuordnung VZÄ auf verantwortlicher Kostenstelle" 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


Freitag, 8. Februar 2013
19:50 Uhr

Ursache für nicht mehr automatisch berechnete Formeln in Excel

Ausgangslage
In einer etwas umfangreicheren Exceltabelle scheinen wenn dann Funktionen oder auch andere Berechnungen nicht mehr zu funktionieren, obgleich die üblichen Fehler (Datenformat, fehlerhafte Bezüge etc.) schon ausgeschlossen waren.

Lösung
Eine etwas versteckte Funktion innerhalb Microsoft Excel findet sich in den Excel-Optionen, zu finden unter Datei/Office Button -> Optionen, im Reiter Formeln.


Excel bis 2003:
Datei->Optionen->Formeln
bzw. ab Excel 2007:
Office Schaltfläche->Exceloptionen->Formeln


Hier läßt sich das automatische Berechnen von Formeln auf manuell umstellen, so dass Formeln nicht direkt bei der Eingabe berechnet werden. Der Punkt lautet hierbei Arbeitsmappenberchnung.

Soweit hatte ich diesen Fehler auch schon unter Formeln in Excel werden nicht berechnet beschrieben.

Das Heimtückische an dieser Funktion ist, dass diese nicht für die gerade geöffnete Excelmappe gilt, sondern als übergreifende Einstellungen. Dieses bedeutet, dass wenn man eine Exceldatei mit entsprechender Einstellung geöffnet hat, dass diese Funktion auch bei allen anderen danach geöffneten Dateien Wirkung zeigt.

Es mag sicher Anwendungsgebiete für diese Funktion geben, aber im Falle eines ungewollten Aktivieren kann diese mehr als unerwünscht sein.

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


Freitag, 21. Dezember 2012
14:38 Uhr

Unerwünschtes optisches Runden in Excel

Ausgangslage
In einer Exceltabelle sind Stundenangaben mit Nachkommastellen erfasst worden. Hierbei sind diese Werte als "Standard" formatiert, so dass Zahlen nur mit Nachkommastellen ausgegeben werden, wenn auch welche vorhanden sind.

Nun tritt folgendes Phänomen auf. Zahlen werden, obgleich dieses nicht vorgegeben ist direkt in der Anzeige gerundet. So wird der Wert 2,5 als 3 angezeigt. Intern wird aber weiterhin mit 2,5 gerechnet.

Ursache
Hier passt Excel 2007 / 2010 die Zahlen leider in einer unerwünschten Form an, sofern die Spalten nicht ausreichend Platz bieten. So wird 2,5 als 3 angezeigt, es sei denn die Spalte hat die optimale Spaltenbreite.

Lösung
Hier kann entweder in Excel direkt die Spaltenbreite angepasst werden, oder alternativ innerhalb der Symbolleiste Start bei Format die Funktion "Spaltenbreite automatisch anpassen" gewählt werden, so dass hier keine Anzeigeprobleme mehr entstehen.

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, 4. November 2012
14:40 Uhr

Formulare gestalten in Excel

Ausgangslage
Im Rahmen einer Budgetierungsvorlage wurde überlegt ein entsprechendes Formular zu entwerfen, das sowohl von den Budgetverantwortlichen her leicht zu verstehen ist, aber auch problemlos die zur Buchung notwendigen Daten bereitstellt. Ziel ist es die eingegebenen Daten dann direkt per SCATT oder LSMW in das SAP System einspielen zu können.

Anhand dieser Anforderung wurde sich nicht für ein Winword-Formular sondern für Excel entschieden.

Anhand dieses Beispiel sollen die grundsätzlichen Funktionen der Formulargestaltung in MS Excel erläutert werden.

Im kommenden Beispiel mag ich einige Funktionen von Excel vorstellen, die mir bei dieser Aufgabe geholfen haben und die auch für andere Problemstellungen hilfreich sein können.

Lösungsansatz

1. Dropdownliste in Excel

Innerhalb der Tabelle soll eine Auswahlliste zur Verfügung gestellt werden in der ein Bezug auf eine Liste vorhanden ist. Früher war dieses über das Menü Daten->Gültigkeit->Liste möglich hier einen entsprechenden Bereich als gültige Auswahlfelder zu hinterlegen.

Seit Excel 2007 ist diese Funktion innerhalb der Ribbons an folgender Stelle versteckt:

Ribbon (Symbolleiste) Daten
Datenüberprüfung

Und hier kann innerhalb der Datenüberprüfung im Bereich Einstellungen als Gültigkeitskriterium Liste
als Quelle ein Bereich innerhalb des Excelblattes bspw. A1:A4 hinterlegt werden. Leider ist dieses nicht mit Bezug auf andere Tabellenblätter möglich. Hier gibt es aber die Möglichkeit anstatt eines festen Bereiches auch einen Namen anzugeben. Dieser kann wie unter 2. beschrieben im Namensmanager erstellt werden. Auf diese Weise kann der Bereich auch in einen anderen Blatt aufgeführt sein.

Im Bereich Eingabemeldung kann dann noch ein Hinweis angegeben werden, welche Daten dort eingetragen werden sollen, und im Bereich Fehlermeldung kann eine Meldung ausgegeben werden, sofern ein Wert ausserhalb dieser Liste eingegeben wird.

2. Namensmanager

Wie im Beispiel der Liste erwähnt, ist es manchmal hilfreich, einen fixen Bereich einen Namen zu geben. Hierfür bietet sich bei Formularen an, ein Extratabellenblatt mit allen relevanten Stammdaten anzulegen.

So haben wir bspw. eine Liste aller relevanten Innenaufträge innnerhalb der Tabelle hinterlegt. Über den Namensmanager läßt sich hier ein Bereich innerhalb der Exceltabelle mit einen bestimmten Namen versehen. Unter Excel 2003 war dieses unter Einfügen, Namen, Definieren möglich. Ab Excel 2007 ist diese Funktion unter

Ribbon Formel
und die Schaltfläche Namen definieren möglich.

Im Beispiel haben wir ein Tabellenblatt mit der Spalte Innenauftrag und einer entsprechenden Liste aller Aufträge. Nun kann hier der Name Innenauftrag bspw. auf
=Stammdaten_Kostenträger!$a$1:$a$999 definiert werden.

Nun kann in der Funktion Daten überprüfen statt eines fixen Bereiches Als Quelle für das Gültigkeitskriterium auch =Innenauftrag verwendet werden und damit ein Bezug auf ein ganz anderes Blatt hergeleitet werden.

Die Namensfunktion funktioniert jedoch nicht nur mit einer Spalte sondern auch mit Bereichen.

So kann sich dieser Bereich auch auf den Innenauftrag, Kurztext, verantwortliche Kostenstelle beziehen.

Im Beispiel wäre dieses dann ein definierter Namen Stammdaten-Innenauftrag der sich auf =Stammdaten_Kostenträger!$a$1:$c$999.
In der Spalte A wäre der Innenauftrag, B die Bezeichnung und C die verantwortliche Kostenstelle hinterlegt.

3. SVERWEIS

In unseren Formular haben wir nun ein Auswahlfeld mit Bezug auf die gültigen Innenaufträge. Sobald dieser Innenauftrag ausgewählt ist, soll automatisch die Bezeichnung und die verantwortliche Kostenstelle ermittelt werden.

Gehen wir davon aus, dass die Auswahlliste in der Spalte D definiert ist und in den Spalten E fehlende Daten erhoben werden sollen.

Hier kann nun die Funktion SVERWEIS mit entsprechenden Bezug auf die Bereichsnamen verwendet werden.

=WENN(D4<>"";SVERWEIS(D4;Stammdaten_Kostenträger;2;FALSCH);"")

Hier wird Bezug auf die Spalte D genommen und das 2. Feld aus den Bereich Stammdaten_Kostenträger (entspricht der Bezeichnung) ausgegeben, durch die Angabe von FALSCH erfolgt nur eine Ausgabe, wenn die Werte exakt übereinstimmen. Durch eine WENN Funktion
wird hier auch nur die Bezeichnung ausgegeben, wenn ein Auftrag ausgewählt wurde, ansonsten bleibt das Feld leer.

4. Tabellenblatt ausblenden /einfärben

Um die Hilfstabelle mit den Stammdaten auszublenden kann einfach das Register (Tabellenblatt) Stammdaten_Kostenträger mit der rechten Maustaste ausgeblendet werden. Auf diese Weise wird das Tabellenblatt nicht mehr angezeigt und kann auf Anhieb nicht verändert werden. Ebenso können hier einzelne Registerblätter auch eingefärbt werden.

5. Blattschutz

Ausgeblendete Tabellenblätter können grds. mit der rechten Maustaste auch wieder eingeblendet werden. Neben Feldern kann auch die Struktur einer Tabelle geschützt werden. Dieses ist im Ribbon "Überprüfen" über die Funktion Arbeitsmappe schützen möglich.

Ein absoluter Schutz ist dieses zwar nicht (siehe Excel Blattschutz aufheben) aber grds. hilft dieses zumindest gegen unabsichtliches Löschen von Hilfsdaten. Neben der Struktur können auch die einzelnen Felder über Zelle formatieren im Reiter Schutz als Gesperrt bzw. ungesperrt definiert werden. Wird das Excelblatt nun über Blatt schützen gesperrt können auch nur noch die nicht gesperrten Felder ausgefüllt werden.

Sofern man mit einer geschützten Exceltabelle arbeiten muss, hilft die Tastenkombination STRG und # weiter durch die die einzelnen Formeln angezeigt werden. Schliesslich möchte man ja nicht ohne Not den Blattschutz einer Exceltabelle umgehen.





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


Mittwoch, 16. Mai 2012
10:00 Uhr

Formeln in Excel werden nicht berechnet

Ausgangslage:
In einer Excelmappe sind verschiedene Zellen mit der Formel VERKETTEN verbunden worden und per "Automatisch ausfüllen" auf die Folgezeilen übertragen worden. Hierbei sind zwar die Formeln auch entsprechend angepasst bspw. VERKETTEN(a2;c2;d2) und in der Folgezeile VERKETTEN(a3;c3;d3) jedoch werden die Werte nicht aktualisiert bzw. erst nach Speichern erneuten Öffnen der Tabelle.

Lösung:
Hier wurde in der Exceltabelle die automatische Arbeitsmappenberechnung auf Manuell gesetzt.

Unter
Datei->Optionen->Formeln
bzw.
Office Schaltfläche->Exceloptionen->Formeln

kann die Arbeitsmappenberechnung wieder auf automatisch gesetzt werden. Diese Einstellung ist automatisch bei neuen Tabellen vorhanden, kann jedoch auf einzelne Exceltabellen abgestellt werden.

Wie unter Ursache für nicht mehr automatisch berechnete Formeln in Excel beschrieben gilt diese Funktion als übergreifende Einstellung, so dass hiervon alle offene Excelarbeitsmappen betroffen sin.

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, 9. Februar 2010
09:07 Uhr

Excel: negative Zeiten

Problem:

In Excel sollen negative Zeitstunden erfasst werden. Diese ergeben sich aus der Istarbeitszeit abzgl. der Sollarbeitszeit.

Beispiel:

In einer 35 Stunden Woche wurden nur 30 Stunden gearbeitet. Hier weicht die Istarbeitszeit um 5 Stunden von der Sollarbeitszeit ab und soll in Excel als:

- 5:00 dargestellt werden und mit einen Stundensatz berechnet werden.

Lösung:

Microsoft empfiehlt Negative Zeiten bei Zeitberechnungen durch die Option "1904-Datumswerte" berechnen zu aktivieren. Nachteil ist, dass hier alle Datumswerte entsprechend verändert werden.

Alternativ kann dieses auch per Formel gelöst werden: siehe Beitrag auf Excelformeln.de.

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


Donnerstag, 28. Januar 2010
10:57 Uhr

Zeilen und Spalte vertauschen (Daten transponieren)

Ausgangslage:

In einer Liste sind pro Spalte in den Zeilen Daten, bspw. Stunden, erfasst worden

Beispiel:

Nutzer Objekt-A Objekt-B
801
802
803

Für eine zweite Auswertung sind nun jedoch diese Stunden in folgender Form auszuwerten.

Objekt 801 802 803
Objekt-A
Objekt-B

Die Zeilen und Spalten sind also zu vertauschen.

Lösung:

Excel bietet die Möglichkeit kopierte Daten zu transponieren.

Dieses ist über
Bearbeiten -> Kopieren
und danach über
Bearbeiten -> Inhalte einfügen

möglich.

Hier sind dann die Funktionen
Einfügen: Werte
sowie
Transponieren
zu markieren.

Nun sind die Zeilen als Spalten und die Spalten als Zeilen eingefügt.

Ergänzung:
Sind die Daten jedoch Formeln, bspw. mit festen Bezügen, ist diese Funktion nicht ohne weiteres Möglich.

Hier kann sich jedoch folgender Möglichkeit bedient werden.

1.) Umwandeln von Formeln als Text
Über Bearbeiten-> Ersetzen
kann "=" bspw. durch "#FORMEL#" ersetzt werden.
Nun sind die Formeln als Texte markiert und das Transponieren funktioniert.

2.) Umwandeln des Textes in Formeln
Nach erfolgter Transponierung kann nun "#FORMEL#" auch wieder in "=" ersetzt werden und die Formeln funkionieren wieder.


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


Freitag, 25. Juli 2008
18:48 Uhr

Einige Officehilfen

Microsoft bietet online Office Kurse für Ihre Produkte an.

Daneben gibt es aber auch in der Knowledgebase einen Artikel einige Tipps für Excel an.

Aber auch die Seite ExcelFormeln.de ist bei verzwickten Problemen immer eine gute Hilfe.

Ein mir bis dahin weniger bekanntes Verzeichnis von Onlineangeboten für Access und Excel findet sich unter Yaccess - Das Access Portal beziehungsweise unter Yexcel - Das Excel-Portal. Hier sind diverse Links nach unterschiedlichen Kategorien gesammelt und auch kurz beschrieben.
 
Daneben gibt es aber auch einige Artikel im Blog zum Thema EXCEL !
 

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, 6. Mai 2008
22:54 Uhr

Formelansicht in Excel

Ausgangslage:

Ein geschütztes Exceldokument erlaubt keinen Zugriff auf Felder die Formeln enthalten, so dass die Berechnungen nicht sichtbar sind.


Lösung:

Ist das Passwort nicht bekannt hilft die Tastenkombination   STRG   und  #    um in die Formelansicht zu wechseln. Hier werden statt Zahlen dann die Formeln dargestellt.
Ein erneutes  STRG  und  #   lässt einen wieder in die Normalansicht zurückkehren.

Excel 2003
  • Menü Extras
  • Formelüberwachung
  • Formelüberwachungsmodus
Ab Excel 2007 ist diese Funktion innerhalb der Symbolleiste Formeln zu finden

Bis Excel 2007 konnte mit der Tastenkombination aus STRG und # in die Formelansicht gewechselt werden.

Formelansicht in Excel ab Office 2010

Ab Excel 2010 ist diese Tastenkombination, abweichend zur Beschreibung in der Menüleiste jedoch durch die Funktion "Wert als Datum formatieren" ersetzt worden

Die Formelansicht ist an unterschiedlichen Stellen in den jeweiligen Versionen von Excel zu finden.


Excel 2007 / 2010
  • Ribbon Formeln
  • Gruppe
  • Formelüberwachung
  • "Formel anzeigen"
Alternativ zum Aufrufen per Maus kann dieses auch über folgende Tastenkombination aufgerufen werden.Hierzu sollten die Tasten   ALT  +   M   gedrückt werden um auf die Symbolleiste Formeln zu wechseln.

Nun die  ALT  Taste gedrückt halten und je nach Office Version entweder  O   (Excel 2010) oder  (Excel 2013) drücken.

Alternativ besteht auch die Möglichkeit, wie im Artikel "Excel Blattschutz aufheben" beschrieben, den Blattschutz auch ohne Kenntniss des Passwortes aufzuheben.

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, 25. Juli 2006
08:02 Uhr

Excel Blattschutz aufheben

PC-Welt berichtet von einer einfachen Weise den Microsoft Excel Blattschutz (Kennwort) mittels eines VB Makro aufzuheben.

--- BOF pcwBreaker.bas ----
Sub pcwBreaker()

Dim i As Integer, j As Integer, k As Integer
Dim l As Integer, m As Integer, n As Integer
Dim i1 As Integer, i2 As Integer, i3 As Integer
Dim i4 As Integer, i5 As Integer, i6 As Integer
On Error Resume Next
For i = 65 To 66: For j = 65 To 66: For k = 65 To 66
For l = 65 To 66: For m = 65 To 66: For i1 = 65 To 66
For i2 = 65 To 66: For i3 = 65 To 66: For i4 = 65 To 66
For i5 = 65 To 66: For i6 = 65 To 66: For n = 32 To 126

Kennwort = Chr(i) & Chr(j) & Chr(k) & Chr(l) & Chr(m) & Chr(i1) & Chr(i2) & Chr(i3) & Chr(i4) & Chr(i5) & Chr(i6) & Chr(n)
ActiveSheet.Unprotect Kennwort
If ActiveSheet.ProtectContents = False Then
MsgBox "Fertig" & vbcr & "Das alternative Kennwort lautet:" & vbcr & Kennwort
Exit Sub
End If
Next: Next: Next: Next: Next: Next
Next: Next: Next: Next: Next: Next

End Sub
--- EOF pcwBreaker.bas ----

Kurzanleitung:
Extras->Makro->VB editor
Einfügen des VBA

Extras->Makro-> pcwBreaker Makro ausführen.

Die Tabelle ist entsperrt und es wird ein alternatives Passwort ausgegeben, womit der Passwortschutz auch wieder aktiviert werden kann.

Alternativ kann dieses Makro wie im Artikel "Excel Umgang mit Makros und Visual Basic for Applications (VBA)" beschrieben in der persönlichen Makroarbeitsmappe hinterlegt werden.

Weitere sinnvolle Makros für die persönliche Makroarbeitsmappe

Insbesondere im Zusammenhang mit SAP könnte noch folgender Artikel Office Integration - Excelansicht in SAP und Daten kopieren nach Excel interessant sein, da hier noch einige andere sinnvolle Makros u.a. zur Umwandlung von Beträgen in der Form 7,50- nach - 7,50 und verschiedene andere Aspekte des Datenexports erläutert werden. Darüber hinaus könnte auch der Artikel Inhaltsverzeichnis in Excel über vorhandene Tabellenblätter nützlich sein.

Nachtrag Blattschutz in aktuelle Excel Version aufheben (XLSX)


In neueren Excelversionen ist dieses Makro allerdings nicht mehr funktionierend, da Microsoft den Hashwert bzw. Verschlüsselungsalgorithmus inklusive Salt verbessert hat.

Hier besteht aber noch die Möglichkeit das Passwort manuell komplett zu entfernen.

Seit der neuen Office Version handelt es sich bei DOCX, XLSX etc. nicht mehr um reine Dateien sondern im technischen Sinne um Container die mehrere Elemente enthalten (einzelne Dateien).

Ebenso wie im Artikel "Medien (bspw. Bilder) aus DOCX extrahieren" beschrieben können auch in Exceldateien nun einzelne Elemente des Containers entpackt oder auch bearbeitet werden. Entsprechend kann die XLSX Datei als ZIP umbenannt werden und innerhalb der Unterordner
  • XL
  • worksheet

die einzelnen Tabellenblätter  sheet*.xml  mit einen Texteditor bearbeitet werden. Hier ist dann auch sowohl Hashwert als auch Salt in der XML Datei gespeichert. Um nun das Passwort zu entfernen reicht es aus die Attribute des Tags sheetprotection zu entfernen.
Sollte die gesamte Arbeitsmappe mit einen Passwort verschlüsselt sein, kann hier in der Datei workbook.xml mit den Tag workbookprotection ebenso verfahren werden. Danach ist das Passwort entfernt und der Blatt- bzw. Arbeitsmappenschutz kann aufgehoben 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


Hinauf




Werbung


© 2004 - 2017 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