Monat: Juli 2015

(Komplexere) Vorhaben in Excel umsetzen

Roman Allgemein , , , , , , , , ,

Dieser Beitrag stellt Dir ein mögliches Vorgehen dar, wie Du komplexere Vorhaben in Excel umsetzen kannst. Dieser Beitrag knüpft auch zu Teilen an Professionelles Präsentieren von Tabellen an, wo gewisse grundsätzliche Tipps dargestellt wurden.

Nehmen wir an, Du möchtest einen eigenen Report konstruieren, der Deine Verkäufe auf Tagesbasis darstellt und vergleicht. Damit ist bereits ein entscheidender Schritt getan: Wir wissen in etwa, was unser Ziel ist. Natürlich kann man sich bereits ein Bild davon machen, wie der Report aussehen soll. Eine grobe Idee zu haben, ist sicherlich nicht schlecht. Bevor ich aber mit Diagrammen und Kennzahlen loslege, beschäftige ich mich mit der Datenbasis. Woher stammen meine Zahlen und in welcher Form stehen sie mir zur Verfügung? Insbesondere, da es sich um eine Tagesauswertung handelt, sollte die Aufbereitung möglichst vereinfacht sein. Mit der effektiven Automatisierung von einzelnen Schritten beschäftige ich mich jedoch ebenfalls erst später, da dies sonst unnötig die Komplexität erhöht. Ich möchte das Ganze erst einmal "zum Laufen bringen". Sobald alles funktioniert und ich auch mögliche Schwierigkeiten oder Gefahren von meinem Reporting erkannt habe, widme ich mich der Automatisierung.

Sobald Du weisst, welche Daten verarbeitet werden, kannst Du auch den möglichen Output bestimmen. Es gilt nun abzugleichen, ob dies den Erwartungen und dem definierten Ziel entspricht. Falls nicht, kann man eventuell die Datenbasis um weitere Werte ergänzen.

Ich arbeite generell mit separaten Tabellenblättern - ein eigenes für den fertigen Report, ein eigenes für den "Input"/die Datenbasis. Nun können anhand von Beispiel- oder Testdaten die gewünschten Kennzahlen ermittelt und allfällige Diagramme erstellt werden. Bei Formeln gehe ich Schritt für Schritt vor. Nehmen wir als Beispiel eine Wenn-Dann-Formel. Ich erarbeite mir zuerst die Überprüfung (das "Wenn") und erfasse danach erst das "Dann". Insbesondere bei verschachtelten Wenn-Dann-Formeln (falls bei "Sonst" gleich eine nächste Wenn-Dann-Formel angehängt wird - also verschiedene Fälle abgedeckt werden sollen) ist ein solches Vorgehen hilfreich. Auch eine Skizze kann nützlich sein, um sämtliche Szenarien aufzuzeigen. Unmittelbar in einem Atemzug eine verschachtelte Wenn-Dann-Formel zu erstellen, ist extrem anspruchsvoll.

Zwischendurch zusätzliche Beispieldaten aufzunehmen kann helfen, um die spätere Aufbereitung des Berichts zu simulieren. Es zeigt aber auch auf, wie beispielsweise negative oder sogar fehlerhafte Werte den Report beeinflussen. Darauf kannst Du zu diesem Zeitpunkt gut reagieren und Sicherheiten einbauen. Mit Dropdown-Listen, respektive der Datenüberprüfung (Daten → Datenüberprüfung), können manuell vorgenommene Eintragungen auf vordefinierte Formate oder Werte eingeschränkt werden (beispielsweise, dass in der Spalte "Datum" zwingend auch ein solches eingetragen wird).

Sobald sämtliche Kennzahlen korrekt ermittelt sind, widme ich mich der tatsächlichen Anordnung und Aspekten der Ästethik. Welche Informationen sollen wo angegeben, welche Farben (Corporate Design) sollen verwendet werden, was soll der Titel beinhalten et cetera. Auch Überlegungen zum Druckbereich können an dieser Stelle gemacht werden.

Da wir nun genau wissen, wie unser Report funktioniert, können wir uns mit der Automatisierung befassen - also die Exceltabelle dynamisch machen. Formeln wie "=HEUTE()" und "=BEREICH.VERSCHIEBEN" sowie bedingte Formatierungen kommen hier zum Einsatz. Schau Dir hierzu auch die beiden Beiträge Dynamische Ergebnisse sowie Dynamische Titel an.  Auch mittels Makro kann selbstverständlich die Erstellung des Berichts vereinfacht oder gar komplett automatisiert werden.

Zu einem späteren Zeitpunkt müssen oftmals weitere Kennzahlen in den Bericht aufgenommen oder wiederum entfernt werden. Es kann auch sein, dass sich die Datenquelle verschiebt oder verändert, worauf es zu reagieren gilt. Die da vorzunehmenden Tätigkeiten entsprechen jedoch stets dem oben erklärten Vorgehen. Und eines ist garantiert: Durch Konzeption und Konstruktion von solchen Tabellen verbessert man sein Handwerk in Excel enorm!

Einführung in den Namensmanager

Roman Allgemein ,

In diesem Beitrag möchte ich Dir den Namensmanager vorstellen. Du findest diese Funktion im Menüband unter "Formeln". In Excel kann einem Bereich ein Name gegeben und zu einem späteren Zeitpunkt kann darauf zugegriffen werden. Als einfaches Beispiel: Wir können mittels Formel eine Summe bilden. Die Formel dazu lautet:

=SUMME(A2:A6)

Markierst Du nun den Bereich A2 bis A6, kannst Du im Namenfeld, wo "A2" steht, einfach einen Namen reinschreiben und mit Enter bestätigen. Im Bild unten siehst Du das Namenfeld. Wichtig zu beachten ist, dass Du bei der Namensgebung keine Leerschläge oder Sonderzeichen verwenden darfst. Zahlen hingegen sind erlaubt. Anstatt im Namenfeld den Namen einzugeben, gelangst Du im Menüband via "Formeln" → "Namen definieren" zum gleichen Ziel.

Namensfeld

Wenn Du nun erneut die Summe bilden möchtest, kannst Du direkt mit der Eingabe von "=SUMME(" beginnen und Dich dann auf Deinen definierten Namen beziehen. Dieser ist nun stellvertretend für den Bereich A2 bis A6. Du findest Deinen Namen auch in der Liste mit den Formeln, allerdings mit einem anderen Kennzeichen vornedran:

VerwendungInFormel

Anpassungen an bestehenden Namen

Um im Nachhinein den definierten Bereich anzupassen oder zu erweitern, kannst Du via "Formeln" auf "Namensmanager" klicken. Dies könnte der Fall sein, wenn Du weitere Daten in Deiner Excel-Tabelle erfasst hast. Im Namensmanager findest Du sämtliche definierten Namen:

Namensmanager

Falls Du Anpassungen vornehmen musst, weil Du beispielsweise weitere Zahlenwerte eingetragen hast, kannst Du den Bereich unter "Bearbeiten..." neu kennzeichnen. Eine schnellere Variante besteht darin, im unteren Bildrand bei "Bezieht sich auf:" einfach die Zahl von "...$A$6" durch den neuen Zeilenindex (beispielsweise 10) zu ersetzen.

Es gibt natürlich weitere Möglichkeiten, wo die Definition von Namen hilfreich ist. Du kannst Dir damit sogenannte Dropdown-Listen erstellen, damit aus einer Liste nur vorgegebene Werte ausgewählt werden können. Das Erweitern des Bereiches im Falle von neuen Zahlenwerten gemäss dem Beispiel oben kann umgangen werden, wenn Du den Bezug mit der Formel "BEREICH.VERSCHIEBEN" dynamisch machst. Dadurch wird Dein Name automatisch um die neuen Werte ergänzt. Dieser komplexeren Formel sowie den Dropdown-Listen möchte ich jedoch eigene Beiträge widmen.

"For"-Loop in VBA

Roman VBA (Makros) , , , , , , , , , , ,

Bei Makros kann man jeden Befehl einzeln programmieren oder aufzeichnen. In einer ersten Einführung in die VBA-Umgebung habe ich Dir gezeigt, wie Du mit dem Makro-Rekorder arbeiten kannst und schnell zu mehr Code kommst. Sich wiederholende Befehle kann man selbstverständlich alle einzeln in Code schreiben. Es ist jedoch deutlich übersichtlicher, wenn Du Wiederholungen mit sogenannten Loops (Schleifen) programmierst. In diesem Beitrag möchte ich Dir die "For"-Schleife zeigen.

Mit diesem Loop kannst Du einen Befehl x-beliebig ausführen lassen. Mit dem nachfolgenden Code wird im Bereich A1:A10 jeweils "Mein erster Loop" geschrieben:

For x = 1 To 10
Cells(x, 1) = "Mein erster Loop"
Next

Betrachten wir die einzelnen Bestandteile genauer: Der Loop beginnt mit "For". Danach verwenden wir X als Variable, beginnend bei 1 bis 10. In der zweiten Code-Zeile verwenden wir "Cells" - also Zelle. In Zelle "x, 1" soll unser Text geschrieben werden. Wichtig zu beachten ist, dass in "Cells" die Reihenfolge gegenüber Excel umgekehrt ist - es beginnt hier mit der Zeile (Row), danach wird erst nach der Spalte (Column) gefragt. In Excel ist zuerst die Spalte und dann die Zeile anzugeben (beispielsweise A1). Der Befehl "Next" erweitert das X um eins und die Schlaufe wird erneut durchlaufen. Probier es mal aus und führe Dein Makro mit F8 Schritt für Schritt aus. Du kannst mit dem Cursor auf X bleiben, dann zeigt es Dir jeweils den aktuellen Wert der Variable an. Zu Beginn ist dies "x = Leer", danach nimmt es die Werte 1 bis 10 an.

Weitere Anwendungsbereiche

Abhängig von Deinem spezifischen Makro kann diese For-Schleife in unterschiedlicher Form zur Anwendung kommen. Im ersten Beispiel oben hast Du Dich auf die Zeile bezogen. Dasselbe funktioniert natürlich auch mit der Spalte [Cells(1, x)]. Eine weitere Möglichkeit besteht darin, eine Zelle auf dessen Wert zu überprüfen. In der nachfolgenden Datenbank soll das Geschlecht jeweils ausgeschrieben werden (W = Weiblich; M = Männlich).

For-Schleife

Dazu schreiben wir folgenden Code:

For x = 2 To 7
If Cells(x, 1) = "M" Then
Cells(x, 2) = "Männlich"
End If
If Cells(x, 1) = "W" Then
Cells(x, 2) = "Weiblich"
End If
Next

Die For-Schleife beginnt diesmal bei X = 2. Danach findet eine Überprüfung mittels "If-Then" statt. Pro Durchlauf habe ich jeweils nach "M" und anschliessend nach "W" gesucht - einfacher wäre die Verwendung von "If-Then-Else", also "Wenn-Dann-Sonst". Ich decke diesen Fall nicht in diesem Beitrag ab, Du kannst das aber gerne selbst ausprobieren!

Den letzten Fall, den ich noch aufzeigen möchte, ist das Entfernen von Zeilen. Nehmen wir an, wir möchten nur die weiblichen Personen angezeigt haben. Unsere Überprüfung soll also nach "M" suchen und in dem Fall die gesamte Zeile entfernen. Der Code dazu lautet wie folgt:

For x = 2 To 7
If Cells(x, 1) = "M" Then
Rows(Cells(x, 1).Row).Delete
x = x - 1
End If
Next

Der Check in den ersten beiden Zeilen bleibt gleich wie im oberen Beispiel. Danach wird die Zeile der Zelle "x, 1" entfernt. Da sämtliche unteren Zeilen nach oben rücken, müssen wir in dem Fall x unbedingt wieder um 1 zurücksetzen, ansonsten werden einzelne Zeilen ungewollt übersprungen. Je nach dem, wie Deine Daten angeordnet sind, kannst Du mit "x - 1" oder "x + y" y-beliebig viele Zeilen überspringen, bevor Dein Loop wieder zur Anwendung kommt. Deine Variable in der For-Schleife bezieht sich dann nicht auf die Zeile selbst, sondern auf den Fall Nummer x. Dein Loop verarbeitet somit x-beliebige Fälle - und den Code dazu brauchst Du nur ein einziges Mal zu erfassen.

Natürlich könnten sämtliche dieser Befehle leicht in Excel mittels Formel "WENN", dem Setzen eines Filters und dem Löschen der jeweiligen Zeilen vorgenommen werden. Falls es sich aber um eine grosse Tabelle handelt, oder ein wiederkehrender Prozess vorliegt, kann das Makro abgespeichert und jederzeit aufgerufen werden - es lediglich ein initialer Aufwand zur Programmierung notwendig, danach entstehen erhebliche Zeitgewinne und das Fehlerpotenzial wird minimiert (sofern alles korrekt programmiert wurde).