Worum geht's?
Was wir mit dieser Datei erstellen wollen, ist kein Buchhaltungstool, sondern vielmehr eine Möglichkeit, dir zu zeigen, wie dein erstes eigenes Geld eingeteilt werden kann, damit am Ende des Geldes nicht zu viel Monat übrigbleibt. Mit dem Tool können nun verschiedene Varianten durchgespielt werden und es soll bewusst gemacht werden, dass der richtige Umgang mit Geld gar nicht so einfach und ungefährlich ist.
Was wir mit dieser Datei erstellen wollen, ist kein Buchhaltungstool, sondern vielmehr eine Möglichkeit, dir zu zeigen, wie dein erstes eigenes Geld eingeteilt werden kann, damit am Ende des Geldes nicht zu viel Monat übrigbleibt. Mit dem Tool können nun verschiedene Varianten durchgespielt werden und es soll bewusst gemacht werden, dass der richtige Umgang mit Geld gar nicht so einfach und ungefährlich ist.
Inhalt:
|
Teil 1: Release 1 erstellen
|
Teil 2: Release 2 erstellen
|
Teil 3: Release 3 erstellen
|
Teil 4: final Release
|
Arbeitstechnik:
Du kannst für dich selber eine effiziente Arbeitsweise zusammenstellen. Dazu steht dir folgendes Material zur Verfügung:
Du kannst für dich selber eine effiziente Arbeitsweise zusammenstellen. Dazu steht dir folgendes Material zur Verfügung:
- die Kursunterlagen online
- die Kursunterlagen als PDF
- die Vorlagedatei zum Starten
- die Releases jeden Teils zur Überprüfung deiner Arbeit
- die Erklärvideos zu jedem Teil
- die Druckfunktion am Ende des Kurses
- das Gesamtvideo
Teil 1
Die Vorlage Ausbildungsbudget-roh.xlsx
Lade die Vorlagedatei herunter und speichere diese lokal auf deinem Computer. Die Datei sollte nicht umbenannt oder während der Arbeit in einen andern Ordner verschoben werden, denn nur so sprechen wir stets vom selben und die Makros werden später immer funktionieren. Die Vorlage enthält bereits das Tabellenblatt Cockpit, also die Schaltzentrale oder Übersicht mit einigen Angaben. Des Weiteren ist auch schon ein Sheet Essen erstellt und mit allen notwendigen Formeln und Zelleigenschaften formatiert. Verändere die beiden Tabellenblätter nicht! (Abb. 1 und 2)
Lade die Vorlagedatei herunter und speichere diese lokal auf deinem Computer. Die Datei sollte nicht umbenannt oder während der Arbeit in einen andern Ordner verschoben werden, denn nur so sprechen wir stets vom selben und die Makros werden später immer funktionieren. Die Vorlage enthält bereits das Tabellenblatt Cockpit, also die Schaltzentrale oder Übersicht mit einigen Angaben. Des Weiteren ist auch schon ein Sheet Essen erstellt und mit allen notwendigen Formeln und Zelleigenschaften formatiert. Verändere die beiden Tabellenblätter nicht! (Abb. 1 und 2)
Anpassung der Menüleiste
Damit wir für diese Arbeit alle notwendigen Tools zur Verfügung haben (es werden auch Makros erstellt), müssen wir gegebenenfalls die Menüleiste noch etwas anpassen. Zum einen sind das die Entwicklertools (Bild 3) und zum andern der Befehl Maske (Bild 4) im Menü Daten.
Damit wir für diese Arbeit alle notwendigen Tools zur Verfügung haben (es werden auch Makros erstellt), müssen wir gegebenenfalls die Menüleiste noch etwas anpassen. Zum einen sind das die Entwicklertools (Bild 3) und zum andern der Befehl Maske (Bild 4) im Menü Daten.
Du holst das Menü Entwicklertools über Rechtsklick in die Menüleiste - Menüband anpassen - Häklein bei Entwicklertools - OK. (Bild 5)
Zum Erzeugen der Schaltfläche Maske wählst du den gleichen Weg über Menüband anpassen, klappst das Dropdown auf und wählst dann Alle Befehle, erstellen rechts eine Neue Gruppe und nennst diese beispielsweise Darstellen oder Erfassen. Nun suchst du links nach dem Eintrag Maske, fügst diesen über die Schaltfläche Hinzufügen zur vorher erstellten Gruppe hinzu und bestätigst mit OK. Nach dem Schliessen der Optionen erscheint die Maske im Menü Daten ganz rechts. (Bild 6)
Die weiteren Tabellenblätter gemäss Cockpit erstellen
Wähle im Register das Sheet Essen aus und klicke mit der rechten Maustaste auf den Registereintrag. Im Kontextmenü klickst du auf den Eintrag Verschieben oder kopieren, markierst Kopie erstellen und ans Ende stellen. (Bild 7)
Wähle im Register das Sheet Essen aus und klicke mit der rechten Maustaste auf den Registereintrag. Im Kontextmenü klickst du auf den Eintrag Verschieben oder kopieren, markierst Kopie erstellen und ans Ende stellen. (Bild 7)
Benenne das Blatt Essen (2) um in Bekleidung. Ändere die Angaben oben und definiere die Verknüpfung in Zelle C2 richtig. Lösche nicht die Formeln in der Saldospalte! (Bild 8)
Verfahre analog mit dem Kopieren aller anderen Tabellenblätter. Achtung! Das letzte Sheet unterscheidet sich etwas von allen anderen! Es handelt sich dort nämlich um Einnahmen, statt Ausgaben. Daher ändert auch die Formel in der Saldospalte (also unbedingt anpassen!) (Bild 9)
Sheets mit Cockpit verknüpfen
Schliesslich geht es darum, die Abrechnungen aus den einzelnen Sheets mit dem Cockpit zu verknüpfen. Dazu öffnest du das Cockpit und verknüpfst in den Zellen C13 - C20, sowie in C6 die entsprechenden effektiven Inhalte. Bei Abgaben Zuhause bleibt der Wert fix und du kannst C13 gleich B13 setzen, indem du C13 anklickst, dann das „="-Zeichen tippst, B13 anklickst und mit Enter die Eingabe bestätigst. Bei Essen auswärts geht es ein bisschen anders, denn hier verknüpfst du auf ein anderes Sheet. C14 anklicken | „=" | Register Essen wählen | D30 | Enter. (Bild 10) Verfahre mit den übrigen Verknüpfungen ebenso.
Schliesslich geht es darum, die Abrechnungen aus den einzelnen Sheets mit dem Cockpit zu verknüpfen. Dazu öffnest du das Cockpit und verknüpfst in den Zellen C13 - C20, sowie in C6 die entsprechenden effektiven Inhalte. Bei Abgaben Zuhause bleibt der Wert fix und du kannst C13 gleich B13 setzen, indem du C13 anklickst, dann das „="-Zeichen tippst, B13 anklickst und mit Enter die Eingabe bestätigst. Bei Essen auswärts geht es ein bisschen anders, denn hier verknüpfst du auf ein anderes Sheet. C14 anklicken | „=" | Register Essen wählen | D30 | Enter. (Bild 10) Verfahre mit den übrigen Verknüpfungen ebenso.
Hinweis: In einer Zelle, hier D30, ein Total zu berechnen, ist der eine Weg. Unschön daran ist eben das Vorhandensein dieses Eintrags und es kann ja auch sein, dass die Positionen die Zeile 30 überschreiten. Das kannst du verhindern, indem du die Summe der Spalte D, egal wie viele Zahlen diese enthält, direkt mit dem Cockpit verknüpfst. Dazu dient eine ganz einfache Formel bei der jeweiligen Position im Cockpit:
Bilde zum Schluss noch die Summen und Differenzen im Cockpit mit Hilfe des Funktionsassistenten ƒx. (Bild 11, 12)
Das Ergebnis sollte dann so aussehen. (Bild 13)
Speichere deine Arbeit unter dem Namen Ausbildungsbudget-R1.xlsx (du kannst zur Kontrolle nun die Datei hier herunterladen und mit deiner Version vergleichen).
Erklär-Video zu R1
Teil 2
Hat mit deiner R1 alles bestens funktioniert, öffne diese Datei; wenn nicht arbeite mit dieser R1 weiter.
Dropdownliste zur Blattauswahl erstellen
Um in Cockpit eine Dropdownliste zu erstellen benötigen wir ein weiteres Tabellenblatt mit den Listeneinträgen. Da wir gedenken ein Makro zu verwenden, müssen wir die Datei nun in einem anderen Format speichern. Speichere jetzt also die geöffnete Datei R1 unter dem Namen
Ausbildungsbudget-R2 und zwar als Dateityp .xlsm, also Excel-Arbeitsmappe mit Makros (*.xlsm). (Bild 14) Ebenfalls solltest du unter den Programmoptionen bei Makrosicherheit Makros zulassen einstellen.
Dropdownliste zur Blattauswahl erstellen
Um in Cockpit eine Dropdownliste zu erstellen benötigen wir ein weiteres Tabellenblatt mit den Listeneinträgen. Da wir gedenken ein Makro zu verwenden, müssen wir die Datei nun in einem anderen Format speichern. Speichere jetzt also die geöffnete Datei R1 unter dem Namen
Ausbildungsbudget-R2 und zwar als Dateityp .xlsm, also Excel-Arbeitsmappe mit Makros (*.xlsm). (Bild 14) Ebenfalls solltest du unter den Programmoptionen bei Makrosicherheit Makros zulassen einstellen.
Erstelle ein weiteres leeres Sheet mit dem Namen Werteliste (Bild 15) und trage in die Zellen A1 bis A9 folgende Inhalte ein. (Bild 16) Speichere die Arbeitsmappe und begib dich ins Cockpit.
Im Menü Entwicklertools siehst du unter dem Werkzeugkoffersymbol das Formularsteuerelement Kombinationsfeld. Das ist das Dropdownfeld. (Bild 17) Wähle es aus und ziehe ein Feld im Cockpit auf. (Bild 18) Du kannst noch den Hinweis „bitte auswählen" darüber hinschreiben.
Nun macht das Feld aber gerade noch überhaupt nichts. Wir müssen das Steuerelement formatieren. Wechsle dazu im Menü Entwicklertools in die Entwurfsansicht und klicke dann das Feld mit der rechten Maustaste an. Im sich öffnenden Kontextmenü wählst du den Eintrag Steuerelement formatieren. (Bild 19) Bei Eingabebereich trägst du durch wechseln auf das Sheet Werteliste und markieren von A1 - A9 den abgebildeten Parameter ein. Als Zellverknüpfung klickst du im Cockpit auf die Zelle F2 und als Dropdownzeilen wählst du 9 und bestätigst mit OK. (Bild 20) Wenn du nun den Entwurfsmodus verlässt, klappt das Dropdown zwar schön auf, aber die Sheets werden noch nicht gewechselt. Dazu brauchts nun noch ein Makro-Modul, das wir in Visual Basic schreiben.
Makro Modul 1 schreiben
Im Menü Entwicklertools siehst du ganz links die Schaltfläche Visual Basic, die du anklickst. Wähle dort Neu - Modul (Bild 21) und schreibe das Modul ganz genau wie abgebildet. (Bild 22)
Im Menü Entwicklertools siehst du ganz links die Schaltfläche Visual Basic, die du anklickst. Wähle dort Neu - Modul (Bild 21) und schreibe das Modul ganz genau wie abgebildet. (Bild 22)
Schaltfläche „zurück" erstellen
Damit wir per Klick bequem wieder zu Cockpit gelangen, erstelle eine Schaltfläche dazu. Diese kannst du dann auf alle Sheets kopieren, da die Funktion immer die gleiche ist, nämlich: zurück zum Cockpit. Über die Entwicklertools fügst du diesmal eine Schaltfläche (erstes Symbol) auf dem Sheet Essen oben rechts ein. Auch dieser müssen wir eine Funktion zuweisen, indem wir den Schritt in einem Minimakro aufzeichnen. In den Entwicklertools findest du oben links Makro aufzeichnen (roter Punkt). Den drückst du und Excel verlangt einen Namen für das Makro. Nenne es einfach „zurück" und bestätige mit OK - die Aufzeichnung läuft jetzt. Wechsle zum Cockpit und vergiss nicht, die Aufzeichnung zu beenden. (Bild 24) Nun kann des Makro der vorher erstellten Schaltfläche zugewiesen werden. Dazu klicke mit der rechten Maustaste im Entwurfsmodus im Sheet Essen auf die Schaltfläche, wähle Makro zuweisen und das zuvor erstellte Makro. (Bild 25) Die Schaltfläche beschriftest du sinnigerweise mit „zurück", statt mit
„Schaltfläche 1". Jetzt kannst du diese Sachaltfläche auf die übrigen Sheets kopieren ausser auf das Blatt Werteliste, welches wir später eh ausblenden werden.
Damit wir per Klick bequem wieder zu Cockpit gelangen, erstelle eine Schaltfläche dazu. Diese kannst du dann auf alle Sheets kopieren, da die Funktion immer die gleiche ist, nämlich: zurück zum Cockpit. Über die Entwicklertools fügst du diesmal eine Schaltfläche (erstes Symbol) auf dem Sheet Essen oben rechts ein. Auch dieser müssen wir eine Funktion zuweisen, indem wir den Schritt in einem Minimakro aufzeichnen. In den Entwicklertools findest du oben links Makro aufzeichnen (roter Punkt). Den drückst du und Excel verlangt einen Namen für das Makro. Nenne es einfach „zurück" und bestätige mit OK - die Aufzeichnung läuft jetzt. Wechsle zum Cockpit und vergiss nicht, die Aufzeichnung zu beenden. (Bild 24) Nun kann des Makro der vorher erstellten Schaltfläche zugewiesen werden. Dazu klicke mit der rechten Maustaste im Entwurfsmodus im Sheet Essen auf die Schaltfläche, wähle Makro zuweisen und das zuvor erstellte Makro. (Bild 25) Die Schaltfläche beschriftest du sinnigerweise mit „zurück", statt mit
„Schaltfläche 1". Jetzt kannst du diese Sachaltfläche auf die übrigen Sheets kopieren ausser auf das Blatt Werteliste, welches wir später eh ausblenden werden.
Makros für die Eingabemaske
Du weisst nun wie man Schaltflächen erstellt, Makros dafür aufzeichnet und zuweist. Bei den Makros für die Eingabemaske geht das genau gleich, muss aber, da der Befehl immer wieder für den Datenbereich eines anderen Tabellenblatts gilt, für jedes Blatt eigens erstellt werden. Das ist etwas aufwändig, jedoch Routinearbeit. Gehe in folgenden Schritten vor: (Bilderreihe)
Du weisst nun wie man Schaltflächen erstellt, Makros dafür aufzeichnet und zuweist. Bei den Makros für die Eingabemaske geht das genau gleich, muss aber, da der Befehl immer wieder für den Datenbereich eines anderen Tabellenblatts gilt, für jedes Blatt eigens erstellt werden. Das ist etwas aufwändig, jedoch Routinearbeit. Gehe in folgenden Schritten vor: (Bilderreihe)
- Blatt wählen (z.B. Essen)
- Schaltfläche aufziehen
- Aufzeichnung starten und Namen vergeben (jedes Blatt anders, ohne Leerzeichen!)
- Datenbereich markieren
- Menü Daten die Schaltfläche Maske anklicken
- Schliessen wählen
- Aufzeichnung beenden
- Makro der Schaltfläche zuweisen
- Schaltfläche benennen
Die entsprechende Positionstabelle wächst beim Schliessen dann automatisch, der Saldo und das Total der Ausgaben werden neu berechnet und ins Cockpit übertragen. (Bilder 26 und 27)
Super! Vergiss nicht, die Datei unter Ausbildungsbudget-R2.xlsm zu speichern. Du kannst deine Lösung auch mit meiner Version von R2 vergleichen.
Erklär-Video R2
Teil 3
Bedingte Formatierung
Du arbeitest nun mit deiner R2 weiter oder lädst meine Version R2 herunter und übst damit.
Damit dir stark vom Budget abweichende unangenehme Zahlen auffallen, sollen diese im Cockpit rot dargestellt werden. Um das zu erreichen, bedienst du dich der „bedingten Formatierung" von Zellen. Markiere dazu die Zelle C13 und wähle im Menü Start - Bedingte Formatierung - Neue Regel - Nur Zellen formatieren, die enthalten - Zellwert - grösser als - =$B$13 und dann die Schaltfläche Formatieren. (Bild 28)
Du arbeitest nun mit deiner R2 weiter oder lädst meine Version R2 herunter und übst damit.
Damit dir stark vom Budget abweichende unangenehme Zahlen auffallen, sollen diese im Cockpit rot dargestellt werden. Um das zu erreichen, bedienst du dich der „bedingten Formatierung" von Zellen. Markiere dazu die Zelle C13 und wähle im Menü Start - Bedingte Formatierung - Neue Regel - Nur Zellen formatieren, die enthalten - Zellwert - grösser als - =$B$13 und dann die Schaltfläche Formatieren. (Bild 28)
Unter Formatieren setzest du die Schriftart auf rot und bestätigst alles mit OK. Über den Befehl Format übertragen kopierst du diese Formatierung au die Zellen C14 - C20 und je nachdem, ob die Bedingung erfüllt ist, erscheinen die Zahlen rot. (Bild 29)
Bei den Zellen D13 - D20 und D23 lautet die bedingte Formatierung:
Testdaten eingeben
Gib nun unter allen Positionen Testdaten ein und zwar so, dass auch einmal das Budget überschritten wird, so siehst du auch gleich, ob die bedingte Formatierung überall klappt.
Feedback erzeugen mit "wenn - dann"
Im Cockpit sollst du nun noch eine Rückmeldung erhalten, wie du deine Ausgaben planst, oder wie es um die Einhaltung deines Budgets steht. Dazu verwenden wir in der Zelle A25 eine „Wenn - dann"-Funktion.
Klicke auf die Zelle A25 und suche im Funktionsassistenten ƒx nach der Funktion wenn. Gib dort die Parameter wie folgt ein: (Bild 30)
Gib nun unter allen Positionen Testdaten ein und zwar so, dass auch einmal das Budget überschritten wird, so siehst du auch gleich, ob die bedingte Formatierung überall klappt.
Feedback erzeugen mit "wenn - dann"
Im Cockpit sollst du nun noch eine Rückmeldung erhalten, wie du deine Ausgaben planst, oder wie es um die Einhaltung deines Budgets steht. Dazu verwenden wir in der Zelle A25 eine „Wenn - dann"-Funktion.
Klicke auf die Zelle A25 und suche im Funktionsassistenten ƒx nach der Funktion wenn. Gib dort die Parameter wie folgt ein: (Bild 30)
Probiere die Funktionen aus, indem du bei den Positionen beliebig die Ausgaben veränderst. Alles klar?
Speichere nun deine Arbeit unter dem neuen Namen Ausbildungsbudget-R3.xlsm. Vergleiche sie auch mit meiner R3.
Speichere nun deine Arbeit unter dem neuen Namen Ausbildungsbudget-R3.xlsm. Vergleiche sie auch mit meiner R3.
Erklär-Video R3
Teil 4 (final)
Du weisst, falls deine R3 nicht zufriedenstellend läuft, lade die R3 von mir herunter, damit du weiterarbeiten kannst.
Diagramm erstellen
Zum Schluss geht es darum, die Daten „Budget und effektiv" noch visuell in einem aussagekräftigen Diagramm darzustellen. Es gibt dazu verschiedene Darstellungsformen und du kannst eigentlich selbst entscheiden, welche dir „aussagekräftig" erscheint. Ich schlage gruppierte Säulen vor.
Damit wir das Diagramm schliesslich auf einem Separaten Tabellenblatt haben, erstellst du noch ein neues Sheet mit dem Namen Diagramm.
Markiere den Datenbereich im Cockpit, der für das Diagramm verwendet werden soll. (Bild 31) Klicke auf einen leeren Bereich im Cockpit und dann im Menü Einfügen auf Diagramme und wähle 3D-Säulen (gruppiert). Das Diagramm wird im Rohbau erstellt.
Diagramm erstellen
Zum Schluss geht es darum, die Daten „Budget und effektiv" noch visuell in einem aussagekräftigen Diagramm darzustellen. Es gibt dazu verschiedene Darstellungsformen und du kannst eigentlich selbst entscheiden, welche dir „aussagekräftig" erscheint. Ich schlage gruppierte Säulen vor.
Damit wir das Diagramm schliesslich auf einem Separaten Tabellenblatt haben, erstellst du noch ein neues Sheet mit dem Namen Diagramm.
Markiere den Datenbereich im Cockpit, der für das Diagramm verwendet werden soll. (Bild 31) Klicke auf einen leeren Bereich im Cockpit und dann im Menü Einfügen auf Diagramme und wähle 3D-Säulen (gruppiert). Das Diagramm wird im Rohbau erstellt.
Dieses Diagramm schneidest du mit Ctrl+x aus und fügst es auf dem Sheet Diagramm mit Ctrl+v wieder ein. Dort kannst du es nach Belieben optimieren bis es dir gefällt. (Bild 32)
Passe jetzt wieder die Navigation an, indem du im Cockpit eine Schaltfläche zum Diagramm und beim Diagramm die Schaltfläche zurück erneut einsetzest. Das haben wir ja bereits ausführlich geübt beim Makros erstellen.
Archiv
Als Option können wir auch noch ein Tabellenblatt Archiv erstellen. Dorthin kannst du verschiedene Budget-Varianten aus dem Cockpit per Printscreen kopieren und ablegen. Das kann sehr sinnvoll sein. (Bild 33)
Archiv
Als Option können wir auch noch ein Tabellenblatt Archiv erstellen. Dorthin kannst du verschiedene Budget-Varianten aus dem Cockpit per Printscreen kopieren und ablegen. Das kann sehr sinnvoll sein. (Bild 33)
Druckfunktion einrichten
Wie du auf der Abbildung 33 siehst, kannst du im Cockpit auch noch eine Druckfunktion einrichten. Das bewältigst du wiederum mit einem ganz einfachen Makro, das deinen Drucker aufruft.
Letzte Kosmetik
Damit nun alles noch etwas professionell aussieht, kannst du insbesondere im Cockpit und beim Diagramm noch die Gitternetzlinien ausblenden. Das geht übers Menü Ansicht - Gitternetzlinien (kein Häklein).
(Bild 34)
Wie du auf der Abbildung 33 siehst, kannst du im Cockpit auch noch eine Druckfunktion einrichten. Das bewältigst du wiederum mit einem ganz einfachen Makro, das deinen Drucker aufruft.
Letzte Kosmetik
Damit nun alles noch etwas professionell aussieht, kannst du insbesondere im Cockpit und beim Diagramm noch die Gitternetzlinien ausblenden. Das geht übers Menü Ansicht - Gitternetzlinien (kein Häklein).
(Bild 34)
Letztendlich kannst du noch das Tabellenblatt Werteliste ausblenden, das ja lediglich fürs Dropdown existieren muss. Klicke mit der rechten Maustaste auf das Register und wähle im Kontextmenü ausblenden. (Bild 35)
Muster-Video final-Release
Herzliche Gratulation - das war's!
Speichere deine Arbeit unter dem Namen Ausbildungsbudget-final.xlsm. Hier wäre auch meine -final zum Vergleich.
Anhang
VBA
Unter (Visual Basic - Ansicht - Projekt-Explorer) kannst du alle Makros und Tabellenblätter sehen, die du erstellt hast.
Speichere deine Arbeit unter dem Namen Ausbildungsbudget-final.xlsm. Hier wäre auch meine -final zum Vergleich.
Anhang
VBA
Unter (Visual Basic - Ansicht - Projekt-Explorer) kannst du alle Makros und Tabellenblätter sehen, die du erstellt hast.
Hier auf YouTube kannst du dir das gesamte Video zum Projekt ansehen.
Ihre Kommentare, Fragen und Kritik zu diesem Beitrag >> hier deponieren.