Erstellen einer wöchentlichen Anwesenheitsliste
Die Aufgabe
Jede Woche soll eine Anwesenheitsliste ausgedruckt werden, in welcher die Namen von Kursteilnehmern bereits erfasst sind. Es soll möglichst so sein, dass so wenig Eingaben und Änderungen erforderlich sind.
Die fertige Lösung
Es führen viele Wege nach Rom. Und ausnahmsweise zeige ich Ihnen gleich zu Beginn einmal das Ziel. So können Sie sich gleich davon überzeugen, ob das in Etwa etwas sein könnte, was Ihnen weiter hilft. Laden Sie diese Excel-Datei und befassen Sie sich gerne ein wenig damit. Sie ist vollkommen ohne Makros / VBA, nur mit Formeln aufgebaut.
Vorgehensweise
Die Mappe sollte eigentlich selbsterklärend sein. Aber einige kleine Hinweise sind doch angebracht, damit Sie vielleicht das eine oder andere besser verstehen. – Eigentlich müssen Sie nach Fertigstellung der Mappe jede Woche nur noch zwei Positionen ändern und eine Position überprüfen. Sie schauen nach, ob die Namen in der zweiten Tabelle (Teilnehmer) dem aktuellen Stand entsprechen und nehmen erforderlichenfalls Korrekturen vor. Danach geben Sie in C1 die Kalenderwoche ein und in B20 Strg. (Punkt), um das aktuelle Datum als festen Wert einzutragen. Das dürfen Sie natürlich gerne auch per Hand machen, aber bitte keine Funktion. Automatisch wird nun daraus der zu der Kalenderwoche gehörende Montag berechnet und die kalendarischen Daten der Woche werden in B5:F5 automatisch eingetragen.
Das war’s dann auch schon. Drucken und fertig. – Nächste Woche das gleiche Spiel.
Aber warum sollte das Datum nicht mit einer Funktion wie HEUTE() oder JETZT() eingegeben werden? Zwei Gründe sind dafür verantwortlich: Wenn Sie die Mappe jede Woche aus welchen Gründen auch immer unter einem Namen mit dem Zusatz der Kalenderwoche speichern, dann wird dieses Datum auch immer ohne Ihr Zutun aktualisiert.
Und zweitens, am Jahresende wird es „eng”. Begründung: Die Funktion für die Berechnung des Montags der entsprechenden Kalenderwoche brauch ja auch eine Jahreszahl, klar … Und die wird aus B20 geholt. Also, wenn Sie am 30.12. schon die Tabelle für die erste Januarwoche ausdrucken, dann werden Sie darauf achten müssen, ob der erste Montag im neuen Jahr noch zur letzten KW des alten oder schon zur 1. KW des neuen Jahres gehört. Ist letzteres der Fall, dann geben Sie als KW eine 1 ein und beim Datum in B20 einfach den 1.Januar des Folgejahres.
Einzelheiten
Hier nun einige Details zu Formeln und auch zu den beiden Tabellenblättern. Für gestandene Profis ist das alles selbsterklärend, Sie finden hier als geübter User vielleicht doch noch den einen oder anderen Tipp.
Die Tabelle „Teilnehmer”
Da gibt es nun wirklich nicht viel zu sagen. Die einzige Einschränkung: Die Namen der Teilnehmer beginnen in A2. Prinzipiell war’s das auch schon. Schön wäre, wenn keine Leerzelle dazwischen wäre, ist aber auch nicht so tragisch. Die Anzahl ist egal, die Reihenfolge auch.
Die Tabelle „Liste”
Hier gibt es einiges anzumerken. Ich beginne mit C1, dem Feld für die Kalenderwoche. Es ist zwar sinnvoll, dort nur die höchste Zahl der KWs im Jahr anzugeben, aber wenn Sie beispielsweise 75 eingeben würden, dann käme auch ein realer Wert heraus. Start ist immer die erste Kalenderwoche des Jahres, welches in B20 steht. Da wird dann in dem Fall 74*7 (Tage) addiert und Sie sind in der 75. Woche ab dem „Startdatum”.
Die Formatierung der Zellen A6:A18 (die Namen) ist so vorgenommen, dass zwar Texte aber keine Zahlen angezeigt werden. Der Hintergrund: Um in der Liste stets über eine aktuelle Namensaufstellung zu verfügen, werden die (aktuell gehaltenen) Namen der Kursteilnehmer aus dem Tabellenblatt Teilnehmer automatisch übernommen. In genau der Reihenfolge, wie sie in der anderen Tabelle aufgeführt sind. Und ein leerer Eintrag bleibt auch leer.
Logisch? Nein, denn die Zelle ist hier nicht leer. Schauen Sie einmal in die Bearbeitungszeile von A18. Und? Ja, da steht eine Formel drin. Und somit ist die Zelle nicht leer. Aber das kommt noch schöner. Da steht eine Null drin. Und obwohl Sie keine WENN()-Funktion sehen, wird die Null nicht angezeigt.
Es liegt am Zahlenformat, welches ich dort angewendet habe. Es ist ein benutzerdefiniertes Format und sieht so aus:
;;@ Zwei Semikola und ein at-sign (@, zu Deutsch: Klammeraffe). Und falls Sie immer noch nicht so recht daran glauben, dass da in Wirklichkeit doch eine Zahl, die Null drin steht, eine kleine Exkursion in den Bereich der Fehlersuche.
- Klicken Sie auf A11
- Markieren Sie in der Editierzeile (derzeitiger offizieller Name: Bearbeitungsleiste)
- Drücken Sie F9
- Erkennen Sie, dass auf diese Weise das Ergebnis der Formel auch hier angezeigt wird.
- WICHTIG! Jetzt Esc, um die eigentliche Formel wieder herzustellen.
- Wiederholen Sie das Ganze nun in einer Zelle, die derzeit noch leer ist. Alles klar?
- Vergessen Sie nicht Esc!
Wenn Sie irgendwann einmal mehr Namen brauchen als 12, dann wäre es der Formatierung wegen am besten, diese zwischen der Zeile 6 und Zeile 18 einzufügen. Dann bleibt auch das punktierte Raster korrekt erhalten.
Wollen Sie sich noch die Formel zur Übernahme der Namen „antun”? Na gut, zumindest ansatzweise will ich Ihnen das erklären. Die Formel lautet:
=INDEX(Teilnehmer!A:A;ZEILE()-4)
Mit Index() suchen Sie in einem bestimmten, definierten Bereich einen Wert, der an einer definierten Position steht. Der Wert wird dann zurückgegeben. Hier suchen Sie in der Tabelle Teilnehmer in der gesamten Spalte A an eine Position, welche durch die Zeilennummer der aktuellen Zelle bestimmt wird. Und von der Zeilennummer wird noch 4 abgezogen.
Das hört sich schon schwierig an,aber wenn Sie das an Hans in A6 nachvollziehen, dann klärt sich das vielleicht etwas eher auf. Die Funktion ZEILE() gibt hier ja den Wert 6 zurück. Jetzt ziehe ich 4 davon ab, und es bleibt der Wert 2. Jetzt kommt wieder der INDEX() ins Spiel. In der Spalte A soll der Wert aus der (eben berechneten) 2. Position ab Beginn wiedergegeben werden. Und da steht der „Hans”.
Und dann bleiben noch die beiden Zelle B5 und C5. Ich beginne mit C5, weil das leichter ist. Dort steht schlicht und einfach: =B5+1. Also ein Tag nach dem Datum in B5. Also der Dienstag jener Woche. Diese Formel aus C5 ist einfach durch Ziehen bis F5 kopiert worden.
B5 ist eine einfach großartige Formel. Sie dient der Berechnung des Montags der entsprechenden Kalenderwoche und der Autor ist Franz Pölt, gefunden und gesehen in Excelformeln.de