Spesenformular - Gültigkeitsprüfung und Zellschutz
© Data Becker GmbH & Co. KG, 2000
Auszug aus "Excel 2000 Direkt", Kapitel 15
Autorin: Dr. Silvia Rothen, rothen ecotronics, Bern, Schweiz
Letzte Überarbeitung: 13.05.18
Recht viele Excelbenutzer/innen füllen im Normalfall bereits bestehende Tabellen aus oder passen diese an, müssen aber selten eigene Tabellen erstellen. Es gibt in Excel einige Werkszeuge, um solche von anderen auszufüllende Tabellen - sie lassen sich unter dem Begriff Formulare zusammenfassen - narrensicher zu machen.
Mit Zell-, Arbeitsmappen- und Dateischutz lässt sich verhindern, dass Formeln oder ganze Blätter unabsichtlich gelöscht werden und die Gültigkeitsprüfung fängt unsinnige Eingaben ab. Am Beispiel eines Spesenformulars werden Sie beides gleich selbst ausprobieren.
Mit diesem einfachen Spesenformular lernen Sie Gültigkeitsprüfung und Schutzmechanismen in Excel kennen.
Inhaltsverzeichnis
- 1. Notwendiges Übel - Beschriftungen für ein Spesenformular
- 2. Optische Korrekturen - Formatierungen
- 3. Ein paar Berechnungen - Formeln und Zahlenformate
- 4. Falscher Fünfziger - Eingabefehler abfangen mit der Gültigkeitsprüfung
- 5. Kanalisiert - Eingaben beschränken mit Listen
- 6. Katastrophenschutz - Zellen sperren
- 7. Stets zu Diensten - Ein Formular als Vorlage speichern
- Download der Excel-Datei
- Quellenangabe und Buchtipp
1. Notwendiges Übel - Beschriftungen für ein Spesenformular
Sobald ein Betrieb mehr als zwei bis drei Leute umfasst, braucht es eine Spesenregelung, mit der auswärtige Mahlzeiten, Fahrkosten und weitere Aufwendungen vergütet werden. Vielerorts erfasst man diese Spesen auf einem gedruckten Formular. Mit einem elektronischen Spesenformular kann man aber die Eingabe vereinfachen und die Daten anschließend weiterverarbeiten, ohne dass sie eingetippt werden müssen.
Öffnen Sie eine neue Excel-Arbeitsmappe und geben Sie ihr den Namen "Spesenformular.xls". Das erste Blatt benennen Sie mit einem Doppelklick auf das Blattregister "Spesenformular" und das zweite "Eingabeliste". Das dritte Blatt ist überflüssig. Mit einem Rechtsklick auf das Blattregister und entfernen Sie es.
Diese zwei Blätter brauchen Sie für das Spesenformular.
Ein einfaches Spesenformular sollte mindestens Eingabefelder für Name, Datum, ev. auch Zeiten, Anzahl Mahlzeiten und gewähltes Verkehrsmittel enthalten. Geben Sie dem Blatt den Titel "Spesenformular" und erstellen Sie die Beschriftungen, so wie sie in der Abbildung zu sehen sind.
Mit den Beschriftungen allein sieht das Spesenformular wenig überzeugend aus
2. Optische Korrekturen - Formatierungen
Sie werden staunen, wie viel besser das Formular aussieht, wenn es erst richtig formatiert ist. Nachdem Sie wie üblich den Titel fett und mit grosser Schrift formatiert haben, ist sicher einmal das Wichtigste, die Eingabefelder hervorzuheben.
Da sie mehrmals benutzt werden, reißen Sie die Paletten für Rahmen und Füllfarbe von der Symbolleiste ab, indem Sie die Palette mit dem schwarzen Dreieck rechts neben der Schaltfläche öffnen, auf die graue Titelleiste fahren, so dass sie blau wird, und dann mit gedrückter linker Maustaste die Leiste nach unten ziehen.
Paletten können Sie auch losgelöst von der Symbolleiste anzeigen
Markieren Sie den ganzen Formularbereich von A3:G21 und verpassen Sie ihm z. B. eine blassblaue Hintergrundfarbe. Anschließend markieren Sie B5:C7 und dann mit gedrückter "Ctrl"-Taste B9:B10, D9:D10, B12:B13, B15:B16, B18 sowie B19:C19. Diese nicht zusammenhängenden Zellbereiche, die späteren Eingabefelder, erhalten als Füllfarbe .
In den Eingabefeldern löschen Sie die Füllfarbe wieder
Aus der Palette wählen Sie , solange die Eingabefelder noch markiert sind. Um die Spaltenbreite zu optimieren, markieren Sie die Spaltenköpfe von A und C, bevor Sie sie mit einem Doppelklick auf die optimale Breite erweitern.
Ein Doppelklick auf den Rand des Spaltenkopfes für die optimale Spaltenbreite
Die Spalte E hat nur den Zweck, die Berechnungsspalten von den Eingabefeldern zu trennen, deshalb können Sie diese Spalte auf ca. 20 Pixel verkleinern. Als nächstes kommt etwas, wovon ich sonst eher abrate: Markieren Sie B5:C5, rufen Sie das Dialogfenster auf und klicken Sie im Register das letzte Kontrollkästchen an. Auf die gleiche Art verbinden Sie B6:C6 sowie B7:C7 und B19:C19 zu je einer einzigen Zelle.
Bei Eingabeformularen hat das Verbinden von Zellen aus ästhetischen Gründen seine Berechtigung
Zum Abschluss bringen Sie mit ein paar Rahmenlinien mehr Struktur in die Tabelle. Hier spielt die richtige Reihenfolge eine Rolle. Markieren Sie A3:G3 und mit gedrückter "Ctrl"-Taste A21:G21, klicken Sie dann als erstes auf die Schaltfläche und als zweites auf in der Palette . Dann markieren Sie F3:F21 und klicken wieder auf . Zuletzt markieren Sie noch den ganzen Tabellenbereich von A3:G21 und klicken ein drittes Mal auf . Danach schließen Sie die zwei Paletten mit ein Klick auf das X in der Titelleiste.
Eine dicke Konturlinie um eine Tabelle sollten Sie immer ganz zum Schluss erstellen
3. Ein paar Berechnungen - Formeln und Zahlenformate
Im vorliegenden Formular werden die Ess- und Transportspesen mit fixen Ansätzen pro Mahlzeit bzw. Kilometer vergütet. Für diese Ansätze tragen Sie in Zelle F12 z. B. "10", in Zelle F13 "25" und in F16 "0,5" ein. Die Formeln in G12 und G13 sind einfache Multiplikationen, d. h. "=B12*F12" bzw. "=B13*F13". In G18 kommt der Bezug "=B18".
Die Kilometervergütung in G16 ist demgegenüber etwas anspruchsvoller. Während PW für Personenwagen und ÖV für öffentliche Verkehrsmittel steht, heißt FF Firmenfahrzeug. Für dieses erhält man keine Vergütung. Mit der folgenden WENN-Formel kann man das berücksichtigen: "=WENN(GROSS(B15)="FF";0;B16*F16)". Die Funktion GROSS wandelt Text in Großbuchstaben um. Sie ist nötig, damit auch die Eingabe "ff" als Firmenfahrzeug erkannt wird.
Diese Formel erkennt auch Eingaben in Kleinbuchstaben richtig
In Zelle G21 wird die ganze Spalte mit "=SUMME(G4:G20)" noch zusammengezählt. Danach markieren Sie G18 und mit gedrückter "Ctrl"-Taste F4:G21 und wählen aus dem Kontextmenü und dann das Register . Mit dem Zahlenformat in der Abbildung erhalten Sie zwei Dezimalstellen, Tausendertrennzeichen und etwas Abstand von rechten Zellrand.
Mit diesem Format werden Zahlen leserlich
Auch die Zellen B12:B13 und B16 versehen Sie noch mit demselben Zahlenformat, aber dieses Mal ohne Dezimalstellen.
4. Falscher Fünfziger - Eingabefehler abfangen mit der Gültigkeitsprüfung
Nun kommt das grundlegend Neue an dieser Tabelle. Seit der Version 97 haben Sie nämlich die Möglichkeit, für Eingabefelder eine Gültigkeitsprüfung vorzunehmen, um unerwünschte Eingaben abzufangen.
Markieren Sie B12, B13 sowie B16 und gehen Sie ins Menü . In den drei Registern dieses Fenster haben Sie die Möglichkeit, Eingaben abzufangen, den Benutzer vor der Eingabe mit einer Meldung auf mögliche Eingaben hinzuweisen und mit einer Fehlermeldung zu begründen, weshalb eine Eingabe nicht akzeptiert wird. Fangen Sie im ersten Register an. Unter wählen Sie , in Daten steht und ins Feld Minimum tippen Sie "0".
Mit dem ersten Register der Gültigkeitsprüfung können Sie unerwünschte Eingaben verhindern
Dann wechseln Sie ins Register Eingabemeldung, schreiben ins Feld Titel z. B. "Anzahl" und darunter "Bitte geben Sie eine positive, ganze Zahl ein!"
Diese Meldung wird gezeigt, sobald der Benutzer ins Eingabefeld klickt
Im Register Fehlermeldung können Sie bei falschen Eingaben darauf hinweisen, weshalb die Eingabe nicht akzeptiert wird. Im Listenfeld unter Stil stehen drei verschiedene Icons zur Verfügung.
Die zweite Meldung wird angezeigt, wenn eine falsche Eingabe gemacht wird
Wenn Sie die drei Register so wie in den Abbildungen ausgefüllt haben, können Sie das Dialogfenster mit OK verlassen. Gleich danach empfiehlt es sich, die Gültigkeitsprüfung zu testen, z. B. indem Sie Text, negative Zahlen oder Dezimalzahlen in diese Zellen einzugeben versuchen.
Prüfen Sie sofort, ob die Gültigkeitsprüfung funktioniert
Bei der Gültigkeitsprüfung können Sie nicht nur Zahlen auf einen bestimmten Bereich beschränken, sondern ebenso Datums- oder Zeitwerte. Beschränken Sie das Abreisedatum in B9 in der soeben gezeigten Art auf , , "1.1.99" und versehen Sie es mit Meldungen. Für B10 und D10 gehen Sie gleich vor, wählen aber , , "0:00". Noch restriktiver können Sie das Rückkehrdatum behandeln, da es auf jeden Fall größer oder gleich dem Abreisedatum sein muss.
So stellen Sie sicher, dass die Rückkehr hinter der Abreise liegt
5. Kanalisiert - Eingaben beschränken mit Listen
Für die Transportart ist etwas ganz spezielles vorgesehen, da in diesem Feld nur genau die drei Eingaben PW, FF oder ÖV erlaubt sind. Damit sich niemand vertippt, können Sie diese Eingaben als Listenfeld zur Verfügung stellen.
Als erstes müssen Sie Ihre Liste zusammenstellen, am besten auf einem separaten Hilfsblatt. Wechseln Sie also ins Blatt , erstellen Sie die Tabelle in der Abbildung und benennen Sie den Bereich A1:A3 "Transportart". Diese Benennung ist zwingend, weil die Gültigkeitsprüfung nicht mit externen Bezügen arbeiten kann.
Auf dem Blatt Eingabeliste erstellen Sie eine Liste namens "Transportart"
Kehren Sie ins Blatt Spesenformular zurück, klicken Sie auf B15 und rufen Sie das Register vom Menü auf. Im Feld wählen Sie Liste und in Quelle steht "=Transportart".
So schnell kommen Sie zu einem Listenfeld
Auf Meldungen können Sie bei diesem Feld verzichten, da die Eingabe mit dem Listenfeld ohnehin beschränkt ist. Weil das Listenfeld den Text in der benachbarten Zelle verdeckt, machen Sie Spalte C etwas breiter und formatieren die Texte in Spalte A und C rechtbündig.
Ein Listenfeld kanalisiert die Eingabemöglichkeiten
Sobald in einem Feld die Eingabemöglichkeiten auf mehr als zwei bis drei Eingaben beschränkt sind, lassen sich statt der Gültigkeitsprüfung auch Steuerelemente wie Kombinationsfelder, Optionsschaltflächen oder Kontrollkästchen einsetzen.
6. Katastrophenschutz - Zellen sperren
Wenn so viel Herzblut in die Erstellung einer Tabelle fließt, dann möchte man seine Arbeit vor unbeabsichtigter Zerstörung schützen. Mit Zell- und Blattschutz kann man z. B. verhindern, dass Formeln gelöscht oder Formatierungen geändert werden.
Der Schutz von Zellen funktioniert in Excel zweistufig: Erstens muss man im Register des Fensters angeben, ob Zellen gesperrt werden oder nicht. Standardmäßig sind alle Zellen gesperrt, deshalb müssen Sie nun im Spesenformular als erstes für alle weiß formatierten Eingabefelder aufheben.
Um Zellen zu schützen, muss man im ersten Schritt die Sperrung von Eingabezellen aufheben
Der Schutz zeigt aber erst dann eine Wirkung, wenn Sie den Blattschutz aktivieren. Wählen Sie dazu .
Im zweiten Schritt müssen Sie den Blattschutz aktivieren
Sie erhalten ein Fenster, in dem Sie angeben, ob Sie , und/oder schützen möchten. Zusätzlich können Sie ein Kennwort vergeben, damit der Schutz nicht aufgehoben werden kann. Auch wenn dieses Passwort knackbar ist, sollten Sie mit der Vergabe von Kennwörtern selektiv umgehen. Nichts ist ärgerlicher, als nach drei Jahren eine Arbeitsmappe zu öffnen und festzustellen, dass man sie nicht ändern kann, weil man das Passwort vergessen hat.
Hier geben Sie an, welche Elemente vom Blattschutz betroffen sein sollen, und vergeben eventuell ein Kennwort
Sobald Sie nun außerhalb der Eingabefelder versuchen, Eingaben zu machen, erhalten Sie eine Warnung, die auf den Schutz hinweist. Auch sämtliche Formatierungen sind gesperrt. Hier erhalten Sie keine Meldung, aber bei einem Formatierversuch ertönt ein akustisches Signal.
Eine Meldung erinnert Sie an den Blattschutz
7. Stets zu Diensten - Ein Formular als Vorlage speichern
Das Spesenformular hat vor allem dann praktischen Wert, wenn man es als Vorlage speichert. Dazu wählen Sie , wechseln im Listenfeld Dateiname auf und speichern die Datei unter dem Namen "Spesenformular.xlt" im Ordner , der sich in Ihrem Benutzerprofil im Windows-Verzeichnis befindet.
Mit diesem Dateityp machen Sie aus einer Arbeitsmappe eine Mustervorlage
So sieht das fertige Spesenformular aus
Download der Excel-Datei
Die vollständige Excel-Datei mit dem Spesenformular (Version Excel 2000, 15 KB) können Sie hier herunterladen.
Quellenangabe und Buchtipp
Der vorliegende Artikel stammt aus "Excel 2000 Direkt", dem Excel-Buch der Autorin, Bei Data Becker ist es noch als Antiquariatsartikel für schlappe Euro 2,53 erhältlich. Bestellen Sie mit dem Stichwort "excel 2000 direkt" unter Data Becker.
Links zu Excel generell und zu weiteren Office-Programmen finden Sie in der Office-Linkliste in der Kategorie "Excel".
Diese Webseite wurde am 03.05.18 um 22:34 von rothen ecotronics erstellt oder überarbeitet.
Printed on 100% recycled electrons!