Office-Blog

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.

Beispiel 1: ISO-Kalenderwoche und ISO-Jahr

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.

Sowohl für den 1.1.2019 als auch für den 31.12.2019 liefert ISOKALENDERWOCHE die 1

Den Donnerstag der Woche berechnen

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)

ISO-Kalenderjahr und ISO-Kalenderwoche ermitteln

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")

Mehr Übersichtlichkeit durch die neue Funktion LET

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
)

Beispiel 2: Gewichteter Mittelwert

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).

Um den durchschnittlichen Verkaufspreis ohne die LET-Funktion zu berechnen, benötige ich die Hilfsspalte »Erlöse«

Dank der LET-Funktion kann ich auf die Hilfsspalte verzichten und mache die einzelnen Berechnungsschritte durch die Verwendung von Variablen nachvollziehbar:

• Innerhalb der LET-Funktion lege ich drei Namen (»Preise«, »Rabatte« und »Stücke«) für die jeweiligen Spalten an.
• In der Variablen »Gesamterlös« addiere ich alle Einzelerlöse (also die jeweiligen rabattierten Preise multipliziert mit der Stückzahl) und mache dadurch die Hilfsspalte überflüssig.
• In der Variablen »VerkaufteStücke« berechne ich, wie viele Produkte verkauft wurden.
• In der Variablen »GewMittelwert« speichere ich das Ergebnis.
• Dieses Ergebnis lasse ich von der Funktion LET zurückliefen. So weiß ich genau, was die Funktion LET berechnet.

=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
)