Als Office-Insider steht mir seit kurzem die Excel-Funktion LET zur Verfügung. Mit ihr lassen sich Formeln lesbarer machen und Berechnungen in Formeln werden beschleunigt. Wie ich mit LET die Funktion ISOKALENDERWOCHE optimiere und einen gewichteten Mittelwert ohne Hilfsspalte berechne, zeige ich in diesem Beitrag.
Die Funktion ISOKALENDERWOCHE liefert für ein Datum die Zahl der ISO-Kalenderwoche des Jahres, aber leider nicht das Jahr selbst, zu dem die Kalenderwoche gehört.
Sowohl für den 1.1.2019 als auch für den 31.12.2019 liefert ISOKALENDERWOCHE das gleiche Ergebnis: die 1. Obwohl beide Tage im Kalenderjahr 2019 liegen, gehört der 1.1.2019 in die Kalenderwoche 1 des Jahres 2019, der 31.12.2019 hingegen in KW 1 des Jahres 2020.
Das Jahr der beiden Tage (also 2019) hilft hier nicht weiter. Ausschlaggebend ist das Jahr des Donnerstags der Woche, in dem der Tag liegt. Denn liegen mindestens 4 Tage (also Donnerstag bis Sonntag) einer Woche im neuen Jahr, gehören auch Montag bis Mittwoch in die erste Kalenderwoche des neuen Jahres. Somit gehören der 30. und der 31.12.2019 zur ersten Kalenderwoche des Jahres 2020.
Für das ISO-Jahr ist der Donnerstag der Woche ausschlaggebend. Steht mein Datum in A1, berechne ich den Donnerstag der Woche wie folgt:
=A1+4-WOCHENTAG(A1;11)
An das ISO-Kalenderjahr hänge ich nun noch die zweistellige ISO-Kalenderwoche an:
=JAHR(A1+4-WOCHENTAG(A1;11))&"-KW"&TEXT(ISOKALENDERWOCHE(A1);"00")
Mit der LET-Funktion kann ich Berechnungsergebnisse in Variablen speichern. Diese Variablen gelten nur innerhalb der LET-Funktion. Da ich in meiner Funktion mehrfach den Inhalt der Zelle A1 (das Datum) verwende, speichere ich das Datum in einer Variablen (MeinTag) und verwende anschließend in der Berechnung nur noch diese Variable.
Außerdem führe ich die Berechnung in Teilschritten durch: Zuerst berechne ich den Donnerstag der Woche und daraus das ISO-Jahr. Anschließend berechne ich die zweistellige ISO-Kalenderwoche und füge dann Jahr und Kalenderwoche zusammen:
=LET( MeinTag; A1; DonnerstagDerWoche; MeinTag + 4 - WOCHENTAG( MeinTag; 11 ); IsoJahr; JAHR( DonnerstagDerWoche ); IsoKw; TEXT(ISOKALENDERWOCHE( MeinTag );"00"); IsoJahrUndKw; IsoJahr & "-KW" & IsoKw; IsoJahrUndKw )
Um den durchschnittlichen Verkaufspreis auf herkömmlich Art zu berechnen, habe ich meiner Tabelle ein Hilfsspalte »Erlös« hinzugefügt. Das Ergebnis (8,84 Euro) ergibt sich aus der Summe aller Erlöse (1.900 Euro) dividiert durch die verkaufte Menge (215).
Dank der LET-Funktion kann ich auf die Hilfsspalte verzichten und mache die einzelnen Berechnungsschritte durch die Verwendung von Variablen nachvollziehbar:
=LET( Preise;A2:A6; Rabatte;B2:B6; Stücke;C2:C6; Gesamterlös;SUMME(Preise*(1-Rabatte)*Stücke); VerkaufteStücke;SUMME(Stücke); GewMittelwert;Gesamterlös/VerkaufteStücke; GewMittelwert )
Über den Autor