Ich habe eine kleine Serie zu einem häufig auftretenden Thema konzipiert: Tabellen für ein ganzes Jahr mit mehreren Schichten pro Tag. Zum Beispiel drei Schichten oder Dienste: Frühdienst, Spätdienst, Nachtdienst. Deshalb werden für jeden Tag drei Zeilen benötigt, das macht rund 1000 Zeilen pro Jahr.
In der Tabelle soll später sortiert und gefiltert werden, beispielsweise nach einem Zeitraum oder nur nach einer bestimmten Schicht. Darüber hinaus werden die ausgewählten Zeilen gezählt, die Werte addiert, gemittelt oder kumuliert. Mit Formatierungen sollen die Sonnabende und Sonntage hervorgehoben werden, Feiertage eingetragen werden.
Deshalb gibt es folgenden Plan:
Vorbereitende Arbeiten: Benutzerdefinierte Sortierungsliste, Sortieren und Filtern, Verkettete Ausdrücke
Datumsspalte und Schichtspalte mit einem Trick mit der Autoausfüllfunktion erstellen, das Jahr als Variable, die Schaltjahrfrage
Bedingte Formatierung für Wochentage, Kalenderwochen, DBANZAHL und ZÄHLENWENNS
Feiertage
Performance-Fragen
Filtern mit flexiblen, speicherbaren Kriterien, Makrounterstützung
In mehreren Blogbeiträgen soll also dieses Thema erschlossen werden. Die Beispieldatei werde ich hier zum Herunterladen bereitstellen. Sicher geht das Thema an manchen Stellen ins Detail, an anderen bleiben Fragen offen. Aber dazu gibt es ja die Kommentarfunktion. Ergänzen Sie oder fragen Sie.
Register „Daten“, Gruppe „Sortieren und Filtern“, Schaltfläche „Erweitert…“
Dieser Tipp ist für die Controlling-Profis unter Ihnen, die regelmäßig das Erweiterte Filter (Spezialfilter) anwenden. Speichern Sie verschiedene Filterkriterien in einem Datenblatt, um entweder die Datenbank-Funktionen von Excel zu nutzen, oder um auch nur eine größere Tabelle zu filtern, ohne jedes Mal die Filterkriterien in die Autofilter der Spalten neu eingeben zu müssen.
Für das erweiterte Filter muss des Dialogformular des Spezialfilters ausgefüllt werden. In diesem Formular sind die Adressen der Quelltabelle (Listenbereich in diesem oder in einem anderen Blatt), des Kriterienbereiches und gegebenenfalls des Zielbereichs sowie die Duplikatebereinigung anzugeben.
Spezialfilter-Formular, ausgefüllt mit Bereichsnamen anstelle von Adressbezügen
Häufig werden aufgezeichnete Makros verwendet. Sie werden anschließend einer Schaltfläche zugewiesen. Vor dem Aufzeichnen empfiehlt es sich, die drei Bereiche mit Namen zu versehen. Im aufgezeichneten Makro stehen dann die Bereichsnamen in der VBA-Zeile. Der VBA-Code wird dadurch unabhängig von Zeilen und Spalten.
Nun zum Tipp
Für eine besonders flexible Abfrage verwenden Sie einen Kriterienbereich mit etwas mehr Zeilen und Spalten als gewohnt, beispielsweise mit 3 Spalten und 3 Zeilen (oder mehr). Benennen Sie den Bereich der neun Kriterienfelder und ihrer drei Spaltenköpfe etwa mit krit3x3. Geben Sie dann krit3x3 in das Spezialfilter-Dialogformular als Kriterienbereich ein. Schließen Sie den Vorgang mit Klick auf die OK-Schaltfläche ab.
Zeichnen Sie diesen Vorgang des Dialogausfüllens und des OK mit dem Makrorekorder auf. Der VBA-Code heißt dann etwa
Sub Filter3x3()
Range("Rohdaten").AdvancedFilter _
Action:=xlFilterCopy, _
CopyToRange:=Range("Zieltabelle"), _
CriteriaRange:=Range("krit3x3"), _
Unique:=False
End Sub
Mehr wird zum Filtern nicht benötigt. Setzen Sie sich eine Schaltfläche oder ein Icon in das Rechenblatt und weisen ihm die Filterprozedur zu.
Flexibler Kriterienbereich und Schaltfläche zum Auslösen des Filterbefehls
Nun lassen sich die Spalten im Kriterienbereich krit3x3 beliebig umbenennen, und Sie können mehrere Kriterien einfügen. Kriterien in einer Zeile sind UND-Kriterien, die in verschiedenen Zeilen sind ODER-Kriterien. Für nicht verwendete Zeilen geben Sie FALSCH, XXX oder etwas anderes, nicht Vorhandenes ein. Dann klicken Sie auf Ihre Schaltfläche und erhalten das Filterergebnis.
Der benannte Bereich krit3x3 ist farblich hervorgehoben, beschriftet und mit einer Schaltfläche ergänzt. Im Bild werden nur zwei Zeile für das Kriterium benötigt. Denken Sie daran: Was in einer Zeile steht, sind UND-Filter (gleichzeitige Erfüllung), was in einer anderen Zeile steht, sind ODER-Kriterien (alternative Bedingung).
