Tabellen mit eingebauter Funktionalität (Serie Schichtplan – 3)

„Oh, bunt!“ war meine erste Reaktion, als ich mit Excel 2010 die Schaltfläche „Als Tabelle formatieren“ auf der Start-Registerkarte testete. Und ich war froh, auf der plötzlich vorhandenen Tabellen-Tool-Registerkarte ENTWURF die Schaltfläche „In Bereich konvertieren“ gefunden zu haben. Die erste Einschätzung hat sich gründlich geändert, die „intelligenten Tabellen“ sind aus meinem Alltag nicht mehr wegzudenken. Microsoft nennt die Tabellen ‚ListObjects‘.

Außer ‚bunt‘ passiert nämlich eine ganze Reihe mehr:

  • Die Tabelle bekommt eine eigene Registerkarte ENTWURF
  • Die Tabelle (nicht das Blatt) bekommt einen Namen; korrigieren Sie den Vorschlag durch sprechende Namen, wie „tabVorbereitungen“, „tabSchichtplan_2017“ oder „tabKunden“
  • Jede Spalte erhält einen Namen, meist aus der Kopfzeile, den ich in Formeln verwenden kann.

    Sprechende Formeln unter Verwendung der Feldnamen
    Sprechende Formeln unter Verwendung der Feldnamen
  • Formeln in der Tabelle verwenden die Spaltennamen – die Formeln werden sprechend
  • Scrollt der Tabellenkopf über den oberen Blattrand, werden statt der Spaltenbuchstaben B, C, D … nun die Namen der Tabellenfelder angezeigt – sparen Sie sich das fixieren der Tabelle

 

Tabelle: Spalten einfügen und löschen
Kein Markieren eines Zellbereichs ist notwendig, um eine Spalte einzufügen.
  • Ergänzen Sie neue Spalten entweder mithilfe des Kontextmenüs aus einer ausgewählten Zelle heraus oder durch einfaches Losschreiben am rechten Rand. Übrigens werden beim Einfügen mithilfe des Kontextmenüs nur die Tabellenbereiche erweitert, die Umgebung des Tabellenobjekts bleibt unverändert.
  • Neue Spalten bekommen automatisch das Tabellenformat.
  • Schreibt man in die erste Daten-Zeile der Tabelle eine Formel, so wird die Formel automatisch auf die gesamte Spalte erweitert! Nur bei Formatänderungen wie Zahl zu Währung bedarf es noch eines Doppelklicks mit dem kleinen Schwarzen.
  • In eine neue Zeile gelangt man mit der Tabulatortaste. Alle Formatierungen und Formeln werden übernommen. Fügt man einen ganzen Zeilenblock an die Tabelle an, genügen meist wenigen Klicks mit dem ‚kleinen Schwarzen‘ (der Autoausfüllfunktion), um alle neuen Zeilen anzupassen.
  • Die Tabelle hat eine sichtbare Endmarkierung, die sich mit der rechten Maustaste positionieren lässt.
  • Tabellen lassen sich mit einem Klick mit Pivottabellen zusammenfassen. Dabei wird nur der Name der Tabelle übergeben. Ändert sich später die Größe der Tabelle, genügt ein Aktualisieren der darauf basierenden Pivottabellen, um alle aktuellen Daten auszuwerten.
  • Es stehen viele Vorlagen und Optionen zur Formatierung der Tabelle bereit: Das ist sehr datensparsam, da nicht jede Zelle, sondern nur die Tabelle als Ganzes Formatierungsinformationen erhält. Beim Einfügen oder Löschen von Zeilen wird ggfs. die Hell-Dunkel-Markierung der Zeilen automatisch aktualisiert.
  • Sie können eine Ergebniszeile unterhalb der Tabelle einblenden, in der für alle Spalten die Teilergebnis-Funktion zur Verfügung steht. Diese Funktion reagiert auf die Filtereinstellungen und berechnet jeweils nur Werte aus den sichtbaren Zeilen.

Was sollten Sie vorbereitend tun?

  • Ihre Tabelle darf nur EINE Kopfzeile haben, die Zeile darüber sollte leer sein. Sind für die Spaltenbezeichnungen mehr Zeilen erforderlich, kann man den Platz über der Leerzeile verwenden, ihn genauso wie die Kopfzeile formatieren und die Leerzeile ganz flach oder ausgeblendet einrichten.
  • Auch rechts und links und an den Ecken sollten die Zellen leer bleiben
  • Verwenden Sie für die Spaltenbezeichnungen nur Buchstaben und Zahlen, auf keinen Fall Punkte, Bindestriche oder Zeilenumbrüche oder andere Sonderzeichen.
  • Die Tabelle kann im Blatt beliebig platziert werden. Wenn Sie Auswertungen im selben Blatt berechnen wollen, empfehle ich immer, dies oberhalb der Tabelle zu tun. Dann können nach unten unbegrenzt Daten ergänzt werden, ohne die Ergebnisse zu verschieben.
  • Einzige Ausnahme hinsichtlich der Platzierung gilt für Zugriffe mit ODBC. Wenn Sie von außerhalb auf Excel auf die Tabelle zugreifen wollen, um beispielsweise einen Serienbrief mit Adressen aus der Tabelle zu schreiben, dann erwartet die ODBC-Schnittstelle die erste Zelle in $A$1. In diesem Fall überlassen Sie der Tabelle das gesamte Blatt. Wenn Sie Berechnungen und Auswertungen zu dieser Tabelle machen wollen, nehmen Sie ein neues Blatt. Die Tabelle hat ja von Ihnen einen sprechenden Namen bekommen, so dass Sie Ihre Formeln auch ohne zu Markieren schreiben können.

Verkettet bis zur Eindeutigkeit (Serie Schichtplan – 2)

In unserer geplanten Tabelle, dem Schichtplan,  kommen in einer Spalte die Datumswerte mehrmals vor, und in der Schichtspalte wiederholen sich die Schichtbezeichnungen. Jetzt lassen sich einzelne Tage filtern, indem in der Spalte der Datumswerte das Datum oder der Datumsbereich eingegeben wird. Im zweiten Schritt filtert man dann nach der Schicht. Geht das auch in einem Schritt?

Legen Sie sich eine kleine Testtabelle mit wenigen Zeilen mit Datum und Schichtbezeichnung an. Vielleicht haben Sie das auch schon im vorigen Blogbeitrag getan, als es um die Benutzerdefinierten Schichtlisten ging. Dann können Sie beispielsweise in die erste Zeile „FD“ eintippen und die Autoausfüllen-Funktion benutzen.

Legen Sie eine dritte Spalte an, die eine eindeutige Schichtkennung enthalten soll, die sich aus dem Datum und der Schichtbezeichnung zusammensetzt. Die Formel dafür enthält entweder die Funktion VERKETTEN() oder den Verkettungsoperator „&“. Auf die Anzeige des Jahres haben wir hier verzichtet.

=VERKETTEN(TEXT(C12; „TT.MM.“) ; “ – „; D12)
oder

=TEXT(C12;“TT.MM.“) & “ – “ & D12

Testtabelle
Testtabelle mit nur wenigen Daten

Jetzt haben Sie eine Möglichkeit in einem Schritt die richtigen Schichten zusammenzusuchen.

Benutzerdefinierter Filter

Noch besser: Die Spalte Schicht&Datum eignet sich als Suchspalte für die Nachschlagefunktion SVERWEIS.

Tipp: Richten Sie später für die Dauer der Arbeiten an der Tabelle für diese Spalte eine bedingte Formatierung für  „Doppelte Werte“ ein. Dann wird die Spalte sofort rot, wenn sich doppelte Daten einschleichen, z.B. am Jahreswechsel bei einem Nicht-Schaltjahr.

Im nächsten Beitrag geht es um die Frage, ab wann es sich lohnt, eine Tabelle „Als Tabelle (zu) formatieren“.

 

Gute Vorarbeit für eine Tabelle: Sortierlisten erstellen (Serie Schichtplan – 1)

Eine Liste wie „Frühschicht, Spätschicht, Nachtschicht“ oder „FD, SD, ND“ hat die Eigenschaft, eine natürliche, hier zeitliche Ordnung zu besitzen. Aber diese Ordnung lässt sich leider nicht mit dem Alphabet herstellen. Für eine nicht alphabetische Sortierung bietet Excel die „Benutzerdefinierten Listen“ an. Das Dialogfeld für die Listen befindet sich wie bei Office meistens ganz am Ende: Datei – Optionen – Erweitert – ganz nach unten scrollen und die Schaltfläche BENUTZERDEFINIERTE LISTEN finden.

Optionen-Benutzerdefinierte Listen

Wenn Sie zuvor Ihre Liste in einem Excel-Blatt eingetragen und anschließend markiert hatten, wird der Adressbereich unten rechts angezeigt. Nun können Sie ihn importieren. Oder Sie tippen die Werte in das Feld rechts oben, nach jedem Wert mit ENTER abschließen und in einer neuen Zeile weiterschreiben, zum Schluss auf die Schaltfläche HINZUFÜGEN klicken. Jetzt ist die neue Liste in der linken Seite aufgenommen.

Schichttabelle mit Formeln

Neue Beitragsserie: Schicht-Tabellen für ein ganzes Jahr

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.

Los geht es!

Schnelles Datum in Word

Ganz so leicht wie in Excel mit Strg + Punkt​ ein Datum einfügen – das geht in Word leider nicht.

Alt + I = Einfügen     

Aber dennoch: Das nacheinander Drücken der Tasten Alt , I und D  öffnet das Dialogfeld zum Einfügen eines Datums, welches Sie mit Enter schnell schließen können. Im Dialogfeld könnten Sie noch Formate auswählen und sich für eine Datums-Feldfunktion oder den Datums-Text entscheiden. Für ersteres müssen Sie das Häkchen bei Automatisch aktualisieren setzen.

 

Format und Aktualisierung einrichten
Format und Aktualisierung einrichten

Kontextmenü zum Datum
Kontextmenü zum Datum

 
 
 
 

 

 

 

Sind Sie sich schon sicher, dass Sie die Datums-Feldfunktion einsetzen wollen, führt auch die Kombination Alt + Umschalt + D (gleichzeitiges Drücken) zum Ziel.

Feldfunktion Datum
In der Feldfunktion ist das Datumsformat festgelegt

 

 

 

Um den Tipp komplett zu machen: Wollen Sie nachträglich aus der Feldfunktion ein festes Datum erstellen, tippen Sie dazu gleichzeitig  Alt, Umschalt und F9

Spruch der Woche

Schieben Sie es nicht auf Outlook, wenn Ihre Zeit nicht reicht – es ist nur ein Programm!

Access-Datenbanken im Training

Jetzt habe ich endlich die Pläne für die konzipierten Access-Kurse hier veröffentlicht. Natürlich sind das nur Vorschläge. In einem Firmenseminar oder einem Individualtraining mit ein bis vier Teilnehmern kann man die Themen ganz genau anpassen an das, was gebraucht wird. Was halten Sie von Workshops?

MS Access – bei den Themen

Schnelles Datum in die Excel-Zelle

Es ist simpel, dennoch kennen viele Nutzer nicht die folgende Tastenkombination:

Strg + „.“ (ohne Anführungsstriche) fügt das aktuelle Datum ein.

Strg + Umschalt + „.“ fügt die aktuelle Uhrzeit ein.

Möchten Sie Datum und Uhrzeit einfügen, geben Sie nacheinander beide Tastenkombinationen mit einem Leerzeichen dazwischen ein:

Strg + „.“  –> “ “  –>  Strg + Umschalt + „.“ (In der Mitte steht ein Leerzeichen.)

Mit den so eingegebenen Daten kann man auch richtig rechnen, es werden die Zahlenwerte und nicht etwa nur Zeichenfolgen in die Zellen eingetragen.

Namen für häufig verwendete Zellbezüge

Häufig gibt es in Excel-Berechnungen einige Zellen, die in mehreren Formeln weiterverwendet werden.

Um diese Formeln leichter lesbar zu machen und sie auch leichter schreiben zu können, haben Sie die Möglichkeit, einzelne Zellen und ganze Zellbereiche mit Namen zu versehen. Beispielsweise benennen Sie den Bereich $A$5:$A$55 mit „Umsatz“, kann die Summe über diese Zellen dann einfach mit =SUMME(Umsatz) berechnet werden.

Markieren Sie dazu einfach die benötigten Zelle(n) und schreiben dann in das Namenfeld (in der Bearbeitenleiste links) den gewünschten Namen. Vergessen Sie hier nicht, die Eingabe mit der Enter-Taste zu beenden. Wenn Sie dann das Aufklappfeld rechts am Namenfeld klicken, finden Sie den vergebenen Namen in der Liste. Sie können diese Liste zusätzlich als GoTo-Liste verwenden, denn nach der Auswahl werden die zuvor markierten Zellen wieder markiert angezeigt. Auch wenn dazu das Blatt gewechselt werden muss.

Umfassende Unterstützung für die Namensvergabe bietet die Gruppe „Definierte Namen“ auf der Registerkarte Formeln. Sie finden dort den Namensmanager, der alle Namen mit deren Zellbezügen, Gültigkeitsbereichen und aktuellen Werten auflistet. Hier können Sie auch neue Namen erstellen, Namen umbenennen, Bezüge korrigieren sowie Namen löschen.

Daneben gibt es noch einige Schaltflächen, um Namen auf verschiedene Weisen effektiv zu vergeben. Haben Sie die zu benennenden Zellen markiert, klicken Sie auf „Namen definieren“. Excel analysiert die darüber liegende und die linke Zelle, ob eine passende Benennung zu finden ist und schlägt diese meist vor. Haben Sie die Zelle mit einer geeigneten Benennung schon mitmarkiert, wie beispielsweise die Spaltenbezeichnungen einer Tabelle, dann klicken Sie auf „Aus Auswahl erstellen“. Excel fragt noch einmal nach, ob die Namen aus der oberen Zeile oder der linken Spalte verwendet werden sollen, und vergibt entsprechend die Namen. Dabei wird die Beschriftungszeile oder -spalte nicht mit in den Bezug genommen.

Sie können also mit zwei Klicks eine ganze Spalte mit Eingangsgrößen aus den Beschriftungen benennen, oder eine komplette Tabelle mit Bereichsnamen für jede Spalte versehen.

Flexibler Kriterienbereich für das Spezialfilter

Register "Daten", Gruppe "Sortieren und Filtern", Schaltfläche "Erweitert..."
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).