Wie berechnet man in Excel den Durchschnitt basierend auf dem Wochentag?
In Excel stoßen Sie häufig auf Szenarien, in denen Sie den Durchschnittswert einer Zahlenliste basierend auf der jeweiligen Woche berechnen müssen. Beispielsweise könnten Sie Verkaufsdaten analysieren, um die durchschnittliche Bestellmenge an Montagen, Werktagen oder Wochenenden zu ermitteln. Diese Anforderung tritt regelmäßig in Verkaufsberichten, Leistungsanalysen oder anderen zeitbasierten Auswertungen auf. Im Folgenden finden Sie mehrere praktische Lösungen zur Berechnung des wöchentlichen Durchschnitts – mithilfe von Formeln, VBA-Code und PivotTables. Diese Methoden helfen Ihnen, fundierte Erkenntnisse zu gewinnen und Ihre Datenverteilung gemäß Kalendermustern besser zu verstehen.
↳ Ein bestimmter Wochentag (z. B. Montag)
↳ Werktage (Mo–Fr)
↳ Wochenenden (Sa–So)
➤ Automatisches Bilden des Durchschnitts nach Wochentag mithilfe eines VBA-Makros
➤ PivotTable: Gruppieren und Bilden des Durchschnitts nach Wochentag ohne Formeln
Berechnen des Durchschnitts basierend auf Wochentag mithilfe von Formeln
Berechnen des Durchschnitts basierend auf einem bestimmten Wochentag
Um den Durchschnitt der Werte zu berechnen, die einem bestimmten Wochentag zugeordnet sind – beispielsweise allen Montagen –, können Sie in Excel Arrayformeln oder SUMPRODUCT nutzen. Das ist besonders hilfreich, um tägliche Trends zu verdichten und Muster in Ereignissen zu erkennen. Möchten Sie etwa den durchschnittlichen Bestellwert ausschließlich für Montage in Ihrem Datensatz ermitteln, wenden Sie die folgende Methode an:
Geben Sie die folgende Formel in eine leere Zelle ein:
=AVERAGE(IF(WEEKDAY(D2:D15)=2,E2:E15))Drücken Sie anschließend gleichzeitig Strg + Umschalt + Enter. Damit signalisieren Sie Excel, dass es sich um eine Arrayformel handelt – so kann jede Zeile individuell verarbeitet werden, um das korrekte Ergebnis zu liefern.
Hinweise und Erläuterungen:
- D2:D15 ist Ihre Liste mit Daten. Stellen Sie sicher, dass es sich um gültige Excel-Datumswerte handelt.
- 2 steht für Montag. Die Zahlen für die Wochentage lauten: Sonntag = 1, Montag = 2, Dienstag = 3, Mittwoch = 4, Donnerstag = 5, Freitag = 6, Samstag = 7.
- E2:E15 ist der Bereich der Zahlen, deren Durchschnitt Sie berechnen möchten – beispielsweise Bestellmengen, Umsätze oder ähnliche Kennzahlen.
Tipps:
- Wenn Ihre Excel-Version dynamische Arrayformeln unterstützt (Office 365 oder neuer), können Sie die Formel direkt eingeben – ganz ohne Strg + Umschalt + Enter.
- Prüfen Sie, ob leere Zellen oder solche ohne Datumsangabe vorhanden sind, um Formelfehler zu vermeiden.
Als flexible Alternative können Sie SUMPRODUCT nutzen, um dasselbe Ergebnis zu erzielen – ohne Arrayformel und ideal geeignet für größere Datensätze:
=SUMPRODUCT((WEEKDAY(D2:D15,2)=1)*E2:E15)/SUMPRODUCT((WEEKDAY(D2:D15,2)=1)*1)Geben Sie diese Formel in eine Zelle ein und drücken Sie anschließend Enter. Dabei ist D2:D15 Ihr Datumsbereich, E2:E15 Ihr Datenbereich, und 1 steht für Montag (bei Verwendung des zweiten Arguments der WEEKDAY-Funktion als 2, wodurch Montag = 1, Dienstag = 2, ..., Sonntag = 7 gilt).
Berechnen des Durchschnitts basierend auf Werktagen
Um den Durchschnittswert für Werktage (Montag bis Freitag) in Ihren Daten zu berechnen, können Sie die folgende Arrayformel verwenden:
=AVERAGE(IF(WEEKDAY(D2:D15,2)={1,2,3,4,5},E2:E15))Geben Sie diese Formel in eine leere Zelle ein und bestätigen Sie sie mit Strg + Umschalt + Enter.
Hinweise und Erläuterungen:
- Diese Formel berechnet den Durchschnitt ausschließlich für Zeilen, deren Datum auf einen Werktag – also von Montag bis Freitag – fällt.
- Stellen Sie sicher, dass Ihre Datumswerte in D2:D15 gültig sind – andernfalls liefert WEEKDAY möglicherweise nicht die erwarteten Ergebnisse.
Tipps:
- Wenn Sie die Array-Eingabe umgehen möchten, verwenden Sie stattdessen die folgende SUMMENPRODUKT-Alternative.
- Stellen Sie sicher, dass die Datums-Spalte echte Excel-Daten und keine Textwerte enthält.
Eine weitere Möglichkeit hierfür bietet eine SUMPRODUCT-Formel:
=SUMPRODUCT((WEEKDAY(D2:D15,2)<6)*E2:E15)/SUMPRODUCT((WEEKDAY(D2:D15,2)<6)*1)Geben Sie einfach diese Formel ein und drücken Sie Enter. Sie berechnet den Durchschnitt der Werte, deren Wochentagsnummer kleiner als 6 ist – also von Montag bis Freitag.
Berechnen des Durchschnitts basierend auf Wochenenden
Zur Berechnung des Durchschnitts ausschließlich für Wochenendwerte (Samstag und Sonntag) verwenden Sie die folgende Arrayformel:
=AVERAGE(IF(WEEKDAY(D2:D15,2)={6,7},E2:E15))Geben Sie diese Formel in eine leere Zelle ein und bestätigen Sie sie mit Strg + Umschalt + Enter.
Hinweise und Erläuterungen:
- Diese Formel zielt auf Daten ab, bei denen WEEKDAY 6 oder 7 (Samstag oder Sonntag) zurückgibt, wenn das zweite Argument auf 2 gesetzt ist.
Tipps:
- Für große Datensätze kann die SUMPRODUCT-Alternative schneller sein und erspart Ihnen die Array-Eingabe.
- Stellen Sie sicher, dass leere Zeilen oder Nicht-Daten korrekt behandelt werden, um unerwartete Durchschnittswerte zu vermeiden.
Eine schnellere Option mit SUMPRODUCT, die ohne Array-Eingabe funktioniert:
=SUMPRODUCT((WEEKDAY(D2:D15,2)>5)*E2:E15)/SUMPRODUCT((WEEKDAY(D2:D15,2)>5)*1)Überprüfen Sie wie immer die Datumswerte und achten Sie auf leere Zellen, um genaue Ergebnisse zu gewährleisten.
VBA-Code – Automatisierung der Durchschnittsberechnung nach Wochentag mithilfe eines Makros
Für Anwender, die einen vollautomatischen Ansatz bevorzugen und über große Datensätze verfügen oder häufige Aktualisierungen benötigen, bietet sich VBA an: Damit durchlaufen Sie Ihre Daten, gruppieren Einträge nach Wochentag und berechnen für jeden Tag automatisch Durchschnitte. Diese Methode ist ideal, wenn Sie manuelle Formelanpassungen vermeiden und schnell eine übersichtliche Wochentagszusammenfassung erstellen möchten.
Vorteile: Eliminiert manuelle Schritte, erstellt eine vollständige Zusammenfassung und lässt sich für die weitere Verarbeitung anpassen.
Nachteile: Erfordert die Aktivierung von Makros und grundlegende VBA-Kenntnisse; möglicherweise nicht geeignet für stark dynamische oder cloudbasierte Tabellen.
Vorgehensweise:
1. Klicken Sie auf Entwicklertools > Visual Basic, um den VBA-Editor zu öffnen. Wählen Sie im Fenster Einfügen > Modul, und fügen Sie den folgenden Code in das neue Modul ein:
Sub AverageOrdersByWeekday()
Dim dict As Object
Set dict = CreateObject("Scripting.Dictionary")
Dim cell As Range
Dim ws As Worksheet
Dim datesRange As Range, valuesRange As Range
Dim i As Long, dayKey As String
Dim sumArr(1 To 7) As Double
Dim countArr(1 To 7) As Long
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set ws = Application.ActiveSheet
Set datesRange = Application.InputBox("Select the date range", xTitleId, Selection.Address, Type:=8)
Set valuesRange = Application.InputBox("Select the corresponding values range", xTitleId, "", Type:=8)
For i = 1 To datesRange.Count
If IsDate(datesRange.Cells(i).Value) Then
Dim wd As Integer
wd = Weekday(datesRange.Cells(i).Value, 2)
sumArr(wd) = sumArr(wd) + valuesRange.Cells(i).Value
countArr(wd) = countArr(wd) + 1
End If
Next i
Dim resWs As Worksheet
Set resWs = Worksheets.Add
resWs.Name = "Weekday Averages"
resWs.Cells(1, 1).Value = "Weekday"
resWs.Cells(1, 2).Value = "Average"
Dim dayNames As Variant
dayNames = Array("Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday", "Sunday")
For i = 1 To 7
resWs.Cells(i + 1, 1).Value = dayNames(i - 1)
If countArr(i) > 0 Then
resWs.Cells(i + 1, 2).Value = sumArr(i) / countArr(i)
Else
resWs.Cells(i + 1, 2).Value = "No data"
End If
Next i
End Sub2. Um das Makro auszuführen, klicken Sie auf die Schaltfläche 
oder drücken Sie F5. Anschließend erscheint eine Eingabeaufforderung, in der Sie den Datumsbereich (z. B. D2:D15) und den zugehörigen Wertebereich (z. B. E2:E15) auswählen können.
Das Makro erstellt ein neues Arbeitsblatt, das den Durchschnitt für jeden Wochentag zusammenfasst. Der Durchschnitt wird für Montag bis Sonntag berechnet; falls für einen Wochentag keine Daten vorhanden sind, wird „Keine Daten“ angezeigt.
Vorsichtsmaßnahmen und Tipps:
- Stellen Sie sicher, dass die Bereiche „Daten“ und „Werte“ dieselbe Größe haben und zeilenweise exakt ausgerichtet sind.
- Makro Damit das Makro ausgeführt werden kann, müssen Sie Ihre Arbeitsmappe als makrofähige Datei (*.xlsm) speichern.
- Sollte ein Fehler auftreten, überprüfen Sie, ob Ihre Bereiche leere oder ungültige Einträge enthalten.
- Sie können den Code anpassen, um einen Filter für bestimmte Wochentage einzufügen oder die Zusammenfassung zu erweitern.
PivotTable – Verwenden Sie PivotTable, um Daten nach Wochentag zu gruppieren und Durchschnitte ohne Formeln zu berechnen
Eine weitere Methode zur Analyse und Durchschnittsberechnung Ihrer Daten nach Woche ist die Verwendung von PivotTable. Dieser Ansatz ist benutzerfreundlich und erfordert weder manuelle Formeln noch Programmierkenntnisse. Mit PivotTable können Sie Daten dynamisch gruppieren, Durchschnitte berechnen und Ergebnisse bei Änderungen Ihrer Daten sofort aktualisieren.
Vorteile: Schnell eingerichtet, funktioniert mit großen Datensätzen, aktualisiert sich automatisch, sobald neue Daten hinzugefügt werden, und unterstützt weitere Analysen wie Filtern und Sortieren.
Nachteile: Erfordert, dass die Daten in einer Excel-Tabelle oder einem strukturierten Bereich organisiert sind; bietet im Vergleich zu VBA-Lösungen eingeschränkte Anpassungsmöglichkeiten.
Vorgehensweise:
1. Fügen Sie eine Hilfsspalte für Wochentagnamen hinzu:
Geben Sie in einer leeren Spalte (z. B.)F) in F2 ein:
=TEXT(D2,"dddd")Kopieren Sie die Formel nach unten, um sie an Ihre Datenzeilen anzupassen. (Es wird davon ausgegangen, dass sich die Daten in)D2:D15 befinden.)
2.Wählen Sie Ihren Quellbereich einschließlich der Hilfsspalte aus (z. B.)D2:F15). Für optimale Ergebnisse wandeln Sie ihn in eine Excel-Tabelle um (Strg+T) und behalten Sie die Auswahl bei.
3. Wechseln Sie zu Einfügen > PivotTable. Wählen Sie im Dialogfeld „PivotTable erstellen“ den gewünschten Speicherort (ein neues Arbeitsblatt wird empfohlen) und klicken Sie auf OK.
4. Im Bereich „PivotTable-Felder“:
— Ziehen Sie das Hilfsfeld Wochentag (Spalte F) in den Bereich Zeilen.
— Ziehen Sie Ihr numerisches Feld (z. B.)Bestellungen aus Spalte E) in den Bereich Werte.
5. Ändern Sie die Aggregation auf Mittelwert:
Klicken Sie im Bereich Werte auf das Dropdown-Menü, wählen Sie Wert-Feld-Einstellungen, dann Mittelwert, und bestätigen Sie mit OK.
6. (Optional) Sortieren Sie die Wochentage von Montag bis Sonntag:
Klicken Sie mit der rechten Maustaste auf eine beliebige Wochentagsbezeichnung > Sortieren > Weitere Sortieroptionen, oder fügen Sie einen kleinen benutzerdefinierten Sortierhelfer (1–7) hinzu und sortieren Sie danach. Sie können Zahlen auch über Wert-Feld-Einstellungen > Zahlenformat formatieren.
7. Aktualisieren Sie die PivotTable, sobald sich die Daten ändern:
Nachdem Sie die Quelltabelle aktualisiert haben, klicken Sie an einer beliebigen Stelle in der PivotTable und wählen Sie Aktualisieren(oder)Daten > Alle aktualisieren).
Tipps und Fehlerbehebung:
- Stellen Sie sicher, dass die Datums-Spalte gültige Excel-Daten (keine Textwerte) enthält – andernfalls schlägt die Wochentagsformel möglicherweise fehl.
- Wenn die Durchschnittswerte falsch erscheinen, überprüfen Sie, ob das Feld Werte auf Durchschnitt und nicht auf Summe eingestellt ist.
- Verwenden Sie nach Änderungen oder dem Hinzufügen einer Zeile in der Quelltabelle Aktualisieren, damit die PivotTable neu berechnet wird.
- Für Gebietsschemaeinstellungen mit Semikolon geben Sie stattdessen
=TEXT(D2;"dddd")ein.
Die Verwendung einer PivotTable für die Wochentagsanalyse vereinfacht den Prozess und unterstützt Sie dabei, interaktive Berichte zu erstellen – ideal für Präsentationen oder den Austausch Ihrer Erkenntnisse mit anderen.
Entfesseln Sie die Magie von Excel mit KUTOOLS AI
- Intelligente Ausführung: Führen Sie Zelloperationen durch, analysieren Sie Daten und erstellen Sie Diagramme – alles ganz einfach per Sprachbefehl.
- Benutzerdefinierte Formeln: Erstellen Sie maßgeschneiderte Formeln, um Ihre Arbeitsabläufe optimal zu optimieren.
- VBA-Programmierung: Schreiben und implementieren Sie VBA-Code ganz mühelos.
- Formelinterpretation: Verstehen Sie komplexe Formeln spielend leicht.
- Textübersetzung: Überwinden Sie Sprachbarrieren direkt in Ihren Tabellenkalkulationen.
Verwandte Artikel:
Wie berechnet man den Durchschnitt zwischen zwei Datumsangaben in Excel?
Wie berechnet man den Durchschnitt von Zellen in Excel anhand mehrerer Kriterien?
Wie berechnet man den Durchschnitt der drei höchsten oder niedrigsten Werte in Excel?
Beste Office-Produktivitätstools
Verbessern Sie Ihre Excel-Kenntnisse mit Kutools für Excel und erleben Sie Effizienz wie nie zuvor.Kutools für Excel bietet über 300 erweiterte Funktionen zur Steigerung der Produktivität und Zeit sparen.Klicken Sie hier, um die Funktion zu erhalten, die Sie am dringendsten benötigen...
Office Tab bringt eine tabbasierte Oberfläche in Office und macht Ihre Arbeit viel einfacher
- Aktivieren Sie tabbasiertes Bearbeiten und Lesen in Word, Excel, PowerPoint, Publisher, Access, Visio und Project.
- Öffnen und erstellen Sie mehrere Dokumente in neuen Registerkarten desselben Fensters – statt jedes in einem separaten Fenster zu öffnen.
- Steigert Ihre Produktivität um 50 % und erspart Ihnen täglich Hunderte von Mausklicks!
Alle Kutools-Add-Ins – ein Installationsprogramm
Kutools for Office-Paket bündelt Add-Ins für Excel, Word, Outlook und PowerPoint sowie Office Tab Pro – ideal für Teams, die mit mehreren Office-Anwendungen arbeiten.
- Alles-in-einem-Paket— Add-Ins für Excel, Word, Outlook & PowerPoint sowie Office Tab Pro
- Ein Installationsprogramm, eine Lizenz— innerhalb weniger Minuten eingerichtet (MSI-fähig)
- Funktioniert besser zusammen— optimierte Produktivität über alle Office-Anwendungen hinweg
- 30-tägige Vollversion zum Testen— keine Registrierung, keine Kreditkarte erforderlich
- Bestes Preis-Leistungs-Verhältnis— sparen Sie im Vergleich zum Kauf einzelner Add-Ins