Formeln

In dieser Themengruppe sind Anleitungen zur Anwendung von einzelnen Formeln eingeordnet.

Berechnung von Datums-Differenzen und Arbeitstagen

Roman Formeln , , , , , , , , ,

In diesem Beitrag stelle ich Dir verschiedene Varianten vor, wie Du die Differenz oder gar die Arbeitstage (Wochentage) zwischen bestimmten Start- und Enddaten ermitteln kannst. Wir ziehen bei der Arbeitstag-Berechnung zudem die Wochenenden oder allfällige Feiertage in Betracht, was je nach Anwendungsfall einen weiteren Nutzen bietet.

Datumsdifferenzen (DATEDIF)

Hinter jedem Datum steckt ein Zahlenwert. Dies wird ersichtlich, wenn man das Zellenformat eines eingetragenen Datums auf "Zahl" oder "Standard" umstellt. Somit kann mit einer einfachen Differenz (=A2-A1) eine Differenz in Anzahl Tagen ermittelt werden. Dies lässt bereits einiges zu. Bitte stelle Dir hier jeweils die Frage, was Du genau mit der Differenz aussagen möchtest - denn allenfalls musst Du Dein Ergebnis um +1 erhöhen, falls sowohl Start- wie auch Enddatum dazugezählt werden sollen. Die Differenz aus 11.05.2021 und 08.05.2021 ergibt demnach "3".

Wer in Excel etwas auf "Entdeckungstour" gehen möchte, kann durch Eingabe von "=" und einem beliebigen Buchstaben viele verfügbaren Formeln finden. Zur Ermittlung von Datumsdifferenzen empfehle ich einen "Geheimtipp", der sich jedoch nicht in diesen Formeln finden lässt. Es handelt sich um "=DATEDIF", welche in Excel nach wie vor vorhanden ist, um Berechnungen aus "Lotus 1-2-3" (dem Vorgänger von Excel) zu unterstützen. Die Syntax der Formel lautet:

=DATEDIF(Startdatum; Enddatum; Einheit)

Die ersten beiden Argumente sind selbsterklärend, doch die Angabe zur "Einheit" benötigt etwas Hintergrundwissen (und birgt weiteres Potenzial). Da es sich um eine nicht übersetzte Formel handelt, sind für "Einheit" angelsächsische Eingaben notwendig. Es lässt sich mit der Angabe "D" deshalb die Datumsdifferenz in Tagen (Days) ermitteln. Die Angabe "M" würde die Monate (Months) und "Y" die Anzahl Jahre (Years) als Ergebnis anzeigen. Man könnte also mit der Verbindung von Text- und Formeln einige spannenden Informationenaufführen ("Zwischen den beiden Daten liegen x Jahre, y Monate und z Jahre.").

Neben diesen "eindimensionalen" Angaben bietet sich zudem die Möglichkeit, beispielsweise die Jahre oder Monate zweier Daten zu ignorieren. Ausgehend vom 08.01.2020 bis zum 11.05.2021 könnte man demnach mit der Angabe "MD" nur die Anzahl Tage zwischen dem "11." und dem "8." eines Monats ermitteln - Monats- oder Jahresangaben werden somit ignoriert. Mit "YD" hingegen wird die Jahresangabe ignoriert, was im genannten Beispiel (8. Januar bis 11. Mai) 124 Tage ergibt. Die dritte Option wäre "YM", bei dem die Differenz der Monate zwischen dem Aus- und dem Enddatum ermittelt wird, während Tage und Jahre ignoriert werden.

NETTOARBEITSTAGE

Die obigen Beispiele gehen jeweils von 7 Tagen in der Woche aus. Im Geschäftsalltag möchte man vielleicht eher die Arbeitstage berücksichtigen. Ohne aufwändig die Wochentage aller Daten abzuziehen (Die Formel =TEXT(Datum,"TTTT") zeigt Dir übrigens den Wochentag an) kann eine einzige Formel zur Anwendung kommen: "NETTOARBEITSTAGE". Dies ist die Syntax der Formel:

=NETTOARBEITSTAGE(Ausgangsdatum; Enddatum; [Freie_Tage])

Analog dem ersten Beispiel mit der simplen Datumsdifferenz benötigt es auch hier zwei Datumsangaben. Die Angabe zu den "freien Tagen" (Feiertage) ist optional. Die nachfolgende Abbildung illustriert die zwei unterschiedlichen Herangehensweise basierend auf dem Startdatum 08.05.2021 (Samstag) und 11.05.2021 (Dienstag):

Datumsdifferenzen

Über die "Differenz" kann man sich wie oben erwähnt natürlich streiten und mit +1 eine Differnez von 4 Tagen erhalten (sollten jeweils Ganztage gezählt werden). Mit der Formel "NETTOARBEITSTAGE" werden die Wochenend-Tage ignoriert. Für den Freitag, 7. Mai bis 11. Mai würden wir ein Resultat von "3" erhalten.

Nehmen wir einen Feiertag mit in unsere Kalkulation: Den Donnerstag, 13. Mai 2021 (Auffahrt). Die Arbeitswoche vom 10. Mai sollte demnach 4 Arbeitstage beinhalten. Ohne jegliche Angaben würde Dir die Formel jedoch "5" als Resultat zurückgeben. Binde hierzu eine Tabelle mit beliebigen Datumswerten an (stelle sicher, dass nur Daten darin enthalten sind - also ohne Tabellenüberschriften oder ähnlichem). Im nachfolgenden Screenshot gehen wir vom 10. Mai aus (Zelle "B2") und ermitteln die Anzahl Arbeitstage bis und mit Freitag, 14. Mai (Zelle "B3") unter Berücksichtigung einer etwas umfangreicheren Feiertags-Liste im Bereich "E2 bis E4":

Nettoarbeitstage_Feiertage

Als Resultat erhält man dadurch "4" Nettoarbeitstage.

Beliebige Wochenende mit NETTOARBEITSTAGE.INTL

Eine letzte Zusatzoption bietet Dir die Formel "NETTOARBEITSTAGE.INTL". Hierin eingeschoben ist zudem noch die Angabe zu den Wochenenden, welche individueller gestaltet werden können - es lassen sich sowohl zweitägige Wochenenden oder Einzeltage verwenden, was eine Vielzahl an Optionen zur Berechnung von Arbeits- und Einsatzplänen etc. bietet:

Nettoarbeitstage_Wochenende

Konfigurationsdatei in Excel (Config)

Roman Allgemein, Formeln, VBA (Makros) , , , , , , , ,

In einem älteren Beitrag habe ich einst aufgeführt, dass ein Blatt "Parameter" zum professionellen Präsentieren von Excel-Tabellen und -Reports hilfreich ist (siehe diesen Blogeintrag). Wichtige Angaben oder Inhalte von Drop-Down-Listen werden als Einträge auf einem (ausgeblendeten) Tabellenblatt hinterlegt. Eine Konfigurationsdatei (Config) ist dem sehr ähnlich, die hier vorgestellten Ansätze könntest Du durchaus auch in einem "Parameter"-Sheet hinterlegen.

Eine Konfigurationsdatei dient dazu, an einem zentralen Ort bestimmte Einstellungen vorzunehmen, es ist aus Wartungssicht deshalb deutlich komfortabler, als Anpassungen in diversen Formeln, Tabellenblättern oder VBA-Codezeilen vorzunehmen. Insbesondere bei grösseren Dateien oder Skripten ist es je länger je unübersichtlicher, eine Config-Datei ist dort empfohlen. Man bindet also möglichst viele übergreifenden Angaben, die allenfalls ändern könnten (manuell geändert werden müssen) in eine Konfig-Datei ein. Ein weitergehendes Beispiel könnten Dateipfade sein - respektive dynamische Pfade und Ordnerverzeichnisse. Stelle Dir vor, Du teilst Deine Datei mit einigen Arbeitskollegen. Je nach dem, wenn Ihr das File lokal öffnet, befindet sich die Datei jedoch in einem anderen Verzeichnis. Wenn man neben dem aktuellen File auch eine zweite Datei im Stammverzeichnis ansprechen möchte (z.B. eine Input- oder Vormonats-Datei), dann führen starre Pfade zu Fehlern. Im Beitrag "Dateipfad mit einer Formel darstellen" sind die Grundlagen aufgeführt, wie ein dynamischer Pfad hinterlegt werden kann.

Hier führe ich Dir eine Beispieldatei auf, die ein Blatt "Config" enthält:

Config- Konfigurations-Datei.xlsx

Es werden Variablen definiert, die allerdings im Rahmen dieses Blogs nicht weiter verwendet werden. Du könntest diese entsprechend einem Diktionär (Variable des Typs "Dictionary") einlesen - insbesondere im VBA-Kontext. Du kannst aber auch selber simplere Definitionen mit Zellbezügen anstellen. Relevant für den Excel-Teil an und für sich ist der Inhalt in der Spalte B: Dort sind unterschiedliche Varianten der Datei oder des Stammverzeichnisses (Root Path) direkt ausgelesen. Je nach dem, wo die Datei abgespeichert ist, sind andere Werte (und dort relevante Gegebenheiten) eingetragen.

Abhängige (dynamische) Drop-Down-Listen

Roman Allgemein, Formeln , , , , ,

In Formularen sind abhängige Drop-Down-Listen oftmals unabdingbar. Was bedeutet das? Wählt man im ersten Feld eine Oberkategorie "Obst" oder "Fleischwaren", dann sollen in einer zweiten Auswahlliste nur die entsprechenden Einträge zur Option stehen. Wir haben diese abhängigen Drop-Down-Listen bereits im Beitrag "Dropdown einer Dropdown-Liste erstellen - abhängige Dropdown" illustriert. Das funktioniert dann einwandfrei, wenn sich die Inhalte der Unterkategorien nicht ändern. Ansonsten ist es notwendig, die Bereiche (Namen) von "Obst" oder "Fleischwaren" den neuen Umständen entsprechend anzupassen (Bereich erweitern oder reduzieren). Es suggeriert also eine dynamische Drop-Down-Liste. Wie man einen solchen Namen (mit der Formel BEREICH.VERSCHIEBEN) anlegt, kannst Du in diesem Beitrag nachschlagen: Bereich eines Namens automatisch erweitern

Wir würden also alle Namen für die Drop-Down-Listen entsprechend anlegen, wie im Screenshot unten dargestellt (mit der Formel "BEREICH.VERSCHIEBEN", referenzierend auf das separate Blatt "Parameter"):

Namenslisten-Dynamisch

Wählt man im Anschluss im Formular die erste Option zu "Essen", sollte in der abhängigen Drop-Down-Liste dann die jeweilige Unterkategorie abrufbar sein. Dies ist jedoch nicht der Fall, die Auswahl-Liste lässt sich mit Klick auf den Erweiterungspfeil gar nicht erst aufrufen und anzeigen. Wie können wir also sowohl abhängige wie auch dynamische Drop-Down-Listen erstellen? Das Vorgehen zeige ich Dir nachfolgend Schritt für Schritt auf, lösche jedoch bitte im Namensmanager (Menüpunkt "Formeln" → "Namensmanager") allfällige bereits definierten Namen für Deine Drop-Down-Listen.

Abhängige, dynamische Drop-Down-Listen (Lösungsansatz)

Wir müssen die ursprünglichen Daten auf dem separaten Blatt "Parameter" also anders definieren, um jederzeit unsere Auswahl-Kriterien ergänzen und unmittelbar nachher im Formular abrufen können. Gehe dazu im Menüband auf das Register "Einfügen" und selektiere die Option "Tabelle". Du erstellst nun also eine Tabelle und wählst als erstes den Bereich "A1:A3" aus, um die Oberkategorie abzubilden. Falls Du die Zeile 1 inkludierst, wähle zudem "Tabelle hat Überschriften".

TabelleEinfuegenUndDefinieren

Wiederhole den Vorgang auch für die Unterkategorien "Fleischwaren" und "Obst".

Wenn Du nun in der Spalte A Fleischwaren und Obst markierst, siehst Du links oben im Namensfeld (normalerweise steht da die Zelle "A2" und so weiter) "Tabelle1" stehen. Der Name "Tabelle1" ist aktuell also stellvertretend für diesen Bereich. Klicke in dieses Namensfeld und überschreibe einfach "Tabelle1" mit "Essen" - bestätige mit der Enter-Taste. Du wirst in dem Namensfeld kurz etwas aufblinken sehen, danach steht allerdings wieder "Tabelle1" drin. Mache das nun auch für B2:B3 (=Tabelle2) und setze da den Namen "Fleischwaren" sowie für C2:C4 (=Tabelle3) und definiere da "Obst". Rufe nun erneut Deinen Namensmanager auf (Menüpunkt "Formeln" → "Namensmanager"). Dort siehst Du die Resultate entsprechend - beachte auch die Spalte "Bezieht sich auf", die auf die jeweilige Tabelle referenziert:

NamensmanagerTabellenReferenzen

Navigiere nun auf Dein effektives Formular. Wähle die Hauptkategorie (Essen) und versuche nun, Deine Unterkategorie (Drop-Down-Einstellung: =INDIREKT(B5) -- wobei B5 dem Feld mit Fleischwaren/Obst entspricht) kann nun korrekt aufgerufen werden:

FormularDynamischeDropDownUnterkategorie

Zuletzt kannst Du prüfen, ob Deine Listen auf dem Blatt "Parameter" tatsächlich dynamisch sind. Gib einfach einen neuen Begriff unterhalb ein, beispielsweise "Schweinefleisch" als zusätzliche Fleischware. Die Tabelle erweitert sich automatisch (Du siehst dies auch farblich) - und auf der Drop-Down-Liste im Formular kannst Du die neue Option ebenfalls gleich selektieren.