Formeln

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

Verknüpfungen zu externen Quellen

Roman Allgemein, Formeln , , , , , ,

Verknüpfungen und Verweise auf externe Dateien sind gang und gäbe. Solange man auf seinem eigenen Gerät arbeitet oder Zugang zu den verlinkten Dateien hat, stellt dies oftmals kein Problem dar. Beim Öffnen der Haupt-Excel-Datei wird jeweils ein Dialog gestartet, ob man die Verknüpfungen aktualisieren möchte:

VerknuepfungAktualisieren_Dialog

Das birgt jedoch gewisse Risiken und teilweise möchte man die gezeigten Werte auf keinen Fall aktualisieren. Falls man auf "Aktualisieren" klickt, kann es auch vorkommen, dass man die Datei neu verlinken müsste. Dies tritt dann auf, wenn der erwartete Dateipfad nicht gefunden werden kann (die Datei wurde verschoben, umbenannt oder ist nicht auf unserem Gerät verfügbar - je nach dem, mit wem und wie man zusammenarbeitet). Es ist naheliegend, dass man dann auf "Nicht aktualisieren" klickt. Wie man die lästige Dialogbox jedoch gar nicht mehr angezeigt bekommt, zeige ich Dir weiter unten.

Datenvorbereitung und Beispiel

Für unser illustratives Beispiel habe ich bestimmte "Monatsdaten" aufbereitet, die jeweils zu verlinken sind, um das (laufende) Resultat des Jahres zu sehen. Es wäre nun an der Zeit, die Juli-Daten einzufügen:

VerknuepfungAktualisieren_Monatsdaten

Mit der untenstehenden Formel leiten wir die Daten aus der Ursprungsdatei (und dem Tabellenblatt "Juli-Daten") her:

='[Verlinkte-Datei.xlsx]Juli-Daten'!$C$4

Die Eingabe sieht in Excel wie folgt aus und liefert uns als Resultat "125", wie dies in der anderen Datei steht:

VerknuepfungAktualisieren_Formel

Dialogbox "Verknüpfungen zu externen Quellen" ausblenden

Sobald Du einen Link zu einer externen Datei hinterlegst, wird eine Schaltfläche unter dem Menüpunkt "Daten" aktiv: "Verknüpfungen bearbeiten". Du findest die Funktion hier:

VerknuepfungAktualisieren_VerknBearbeiten

Du hättest in der gezeigten Maske die Möglichkeit, Verknüpfungen komplett zu löschen. Dies ersetzt alle Bezüge zu externen Dateien mit deren effektiven Werte. Das kann sinnvoll sein, gerade dann, wenn die Datei an jemanden gesendet werden soll. Alternativ kann man links unten auf "Eingabeaufforderung beim Start..." klicken und dort auswählen, dass keine Warnung angezeigt werden soll sowie keine Verknüpfung aktualisiert werden soll. Dies führt dazu, dass beim Öffnen der Datei die lästige Dialogbox gar nicht mehr angezeigt wird.

VerknuepfungAktualisieren_Eingabeaufforderung

Dass keine Dialogbox mehr angezeigt wird, ist auch gültig für zukünftige Verlinkungen. Das bedeutet: Fügst Du weitere Links ein, wird man dazu auch nicht aufgefordert, ob die Daten aktualisiert werden sollen. Du könntest diese Eingabeaufforderung beim Start also einmalig hinterlegen (und ein fiktiver Bezug einrichten, damit die Schaltfläche überhaupt verfügbar ist und die Verlinkung anschliessend erneut löschen).

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.