Wie berechnet man Durchschnitte aus Zellen verschiedener Arbeitsblätter in Excel?

Wenn Sie den Durchschnitt desselben Bereichs über mehrere Arbeitsblätter hinweg berechnen müssen – beispielsweise den durchschnittlichen Umsatz im Bereich A1:A10 über die Blätter „Sheet1“ bis „Sheet5“ – bietet Excel eine direkte, lösungsorientierte Formel. Dieser Ansatz funktioniert am besten, wenn alle Blätter dieselbe Struktur und eine konsistente Benennung aufweisen.

Schritte:

Wählen Sie eine leere Zelle für das Ergebnis aus (z. B. C3) und geben Sie die folgende Formel ein:

=AVERAGE(Sheet1:Sheet5!A1:A10)

Nachdem Sie Enter gedrückt haben, gibt Excel den Durchschnittswert des begrenzten Bereichs über alle Blätter von Sheet1 bis Sheet5 zurück.

Wenn Sie Werte aus unterschiedlichen Bereichen über mehrere Blätter hinweg mitteln müssen, können Sie diese manuell auflisten:

=AVERAGE(A1:A5, Sheet2!A3:A6, Sheet3!A7:A9, Sheet4!A2:A10, Sheet5!A4:A7)

Diese Variante ist besonders hilfreich, wenn sich die Bereiche zwischen den Blättern unterscheiden. Geben Sie sie in Ihre Ergebniszelle ein und drücken Sie Enter.

Kutools für Excel verbessert Ihre Fähigkeit, Werte aus derselben Zelle oder demselben Bereich über mehrere Arbeitsblätter hinweg zu extrahieren und zu konsolidieren – dank der Funktion Automatische Inkrementierung von Arbeitsblattverweisen. Das ist besonders nützlich, wenn Sie eine große Anzahl von Blättern mit konsistentem Layout verwalten.

Verwendungsschritte:

1. Öffnen Sie ein neues Arbeitsblatt (z. B. ein Zusammenfassungsblatt) und wählen Sie dann die Zelle aus, in der Sie den Durchschnitt berechnen möchten – beispielsweise D7.

2. Gehen Sie zu Kutools > Mehr(innerhalb der Gruppe)Formel) > Automatische Inkrementierung von Arbeitsblattverweisen.

3. Im Dialogfeld:
– Wählen Sie aus der Dropdown-Liste Füllreihenfolgeeine Reihenfolge aus (z. B.)Spalte füllen, dann Zeile füllen).
– Aktivieren Sie im Feld Arbeitsblattliste die Blätter, die die zu mittelnde Zelle enthalten.
– Klicken Sie auf Bereich füllen und schließen Sie anschließend das Dialogfeld.

4. Die ausgewählten Zellwerte werden im Bereich aufgelistet (z. B.)D7:D11). Geben Sie anschließend in einer anderen leeren Zelle die folgende Formel ein, um den Durchschnitt zu berechnen:

=AVERAGE(D7:D11)

Drücken Sie Enter, um das Ergebnis zu erhalten. Dies vereinfacht die Konsolidierung, erweitert sich jedoch nicht automatisch um neu hinzugefügte Blätter – sobald sich die Blattliste ändert, müssen Sie die Funktion erneut ausführen.

In einigen Fällen müssen Sie Durchschnitte für mehrere entsprechende Zellen gleichzeitig über mehrere Arbeitsblätter hinweg berechnen – beispielsweise Ergebnisse aus A1, B1 und C1 jedes Arbeitsblatts zusammenfassen. Mit Standardformeln kann das schnell mühsam werden, doch das Kutools für Excel-Hilfsprogramm zum Kombinieren (von Arbeitsblättern und Arbeitsmappen) vereinfacht diesen Vorgang erheblich.

So verwenden Sie diese Funktion:

1. Klicken Sie auf KUTOOLS PLUS > Kombinieren, um den Assistenten „Arbeitsblätter konsolidieren“ zu öffnen.

2. Im Assistenten (Schritt 1 von 3):
Aktivieren Sie Daten von mehreren Arbeitsmappen konsolidieren und berechnen, und klicken Sie anschließend auf Weiter, um fortzufahren.

3. In Schritt 2 von 3:
– Wählen Sie unter Arbeitsblattliste die einzubeziehenden Arbeitsblätter aus.
– Verwenden Sie die Schaltfläche Durchsuchen, um den Bereich für die Mittelwertbildung festzulegen.
– Klicken Sie auf Gleicher Bereich, falls die Bereiche in allen Blättern identisch sind.
– Klicken Sie auf Weiter, um fortzufahren.

4. In Schritt 3 von 3:
Wählen Sie im Dropdown-Menü unter Funktion die Option Durchschnitt aus. Konfigurieren Sie optional Zeilen- und Spaltenbeschriftungen nach Bedarf und klicken Sie anschließend auf Fertig stellen.

5. Ein Dialogfeld fragt, ob Sie die aktuellen Einstellungen als Szenario für die zukünftige Verwendung speichern möchten. Wählen Sie je nach Bedarf Ja oder Nein.

Nun spiegelt jede Zelle in Ihrem definierten Platzierungsbereich der Liste den Durchschnitt der entsprechenden Zellen aus allen Ausgewählte Arbeitsblätter wider. Diese Methode eignet sich besonders für wiederkehrende Vorgänge oder bei der schnellen Konsolidierung großer Mengen strukturierter Daten.

Für Benutzer, die die Mittelwertbildung über mehrere Arbeitsblätter hinweg automatisieren möchten – insbesondere wenn die Blattnamen nicht nebeneinanderliegend sind, sich häufig ändern oder wenn Bereiche zur Laufzeit festgelegt werden sollen – bietet ein VBA-Makro eine effiziente Lösung. Diese Methode eignet sich ideal für fortgeschrittene Anwender oder Arbeitsmappen, bei denen regelmäßig Blätter hinzugefügt oder umbenannt werden.

Der folgende VBA-Code ermöglicht es Ihnen, Blattnamen und Zellbereiche dynamisch einzugeben und anschließend den Durchschnitt des angegebenen Bereichs über alle aufgelisteten Blätter zu berechnen – ideal zur Konsolidierung von Werten aus komplexen oder häufig aktualisierten Arbeitsmappen.

So richten Sie diese VBA-Lösung ein und verwenden sie:

1. Gehen Sie in Excel zur Registerkarte Entwickler. Falls diese nicht sichtbar ist, aktivieren Sie sie über Datei > Optionen > Menüband und Symbolleisten anpassen. Klicken Sie auf Visual Basic, um den Editor zu öffnen. Gehen Sie dann zu Einfügen > Modul und fügen Sie den folgenden Code ein:

Sub AverageAcrossSheets()
    Dim xSheetNames As String
    Dim xCellRange As String
    Dim xArr As Variant
    Dim xSheet As Worksheet
    Dim xTotal As Double
    Dim xCount As Long
    Dim i As Integer
    
    On Error Resume Next
    xTitleId = "KutoolsforExcel"
    
    xSheetNames = Application.InputBox("Enter sheet names separated by commas (e.g., Sheet1,Sheet3,Summary):", xTitleId, Type:=2)
    If xSheetNames = "" Then Exit Sub
    
    xCellRange = Application.InputBox("Enter cell or range to average (e.g., A1 or A1:B10):", xTitleId, Type:=2)
    If xCellRange = "" Then Exit Sub
    
    xArr = Split(xSheetNames, ",")
    xTotal = 0
    xCount = 0
    
    For i = LBound(xArr) To UBound(xArr)
        Set xSheet = Nothing
        Set xSheet = ThisWorkbook.Sheets(Trim(xArr(i)))
        
        If Not xSheet Is Nothing Then
            If Not IsError(Application.WorksheetFunction.Average(xSheet.Range(xCellRange))) Then
                xTotal = xTotal + Application.WorksheetFunction.Sum(xSheet.Range(xCellRange))
                xCount = xCount + xSheet.Range(xCellRange).Count
            End If
        End If
    Next i
    
    If xCount = 0 Then
        MsgBox "No valid data found!", vbExclamation, xTitleId
    Else
        MsgBox "The average across selected sheets and range is: " & xTotal / xCount, vbInformation, xTitleId
    End If
End Sub

2. Um das Makro auszuführen, drücken Sie im Editor F5 oder schließen Sie den Editor und wechseln Sie zu Entwickler > Makros, wählen Sie AverageAcrossSheets aus und klicken Sie auf Ausführen.

3. Geben Sie bei der Aufforderung eine durch Kommas getrennte Liste von Arbeitsblattnamen ein (z. B.)Sheet1,Sheet3,Summary) und legen Sie anschließend den Bereich fest (z. B. A1:A10).

4. Das Makro berechnet Summe und Anzahl aus jedem gültigen Arbeitsblatt und zeigt den Durchschnitt in einem Meldungsfeld an.

Hinweise zu Parametern:

• Arbeitsblattnamen sind nicht groß-/kleinschreibungsabhängig, müssen jedoch exakt übereinstimmen.
• Der Bereich kann eine einzelne Zelle, eine ganze Spalte (z. B. B:B) oder ein rechteckiger Bereich (z. B. D2:E12) sein.
• Ungültige oder fehlende Arbeitsblätter werden automatisch und ohne Hinweis übersprungen.