Wie berechnet man den Durchschnitt von Zellen aus verschiedenen Blättern in Excel?

Wenn Sie den Durchschnitt desselben Bereichs über mehrere Arbeitsblätter hinweg berechnen müssen – zum Beispiel, um den durchschnittlichen Umsatz im Bereich A1:A10 über Blätter namens Sheet1 bis Sheet5 zu finden – bietet Excel eine direkte formelbasierte Lösung. Dieser Ansatz funktioniert am besten, wenn alle Blätter dieselbe Struktur und konsistente Namen haben.

Schritte:

Wählen Sie eine leere Zelle aus, in der Sie das Ergebnis anzeigen möchten (z. B. Zelle C3), und geben Sie die folgende Formel ein:

=AVERAGE(Sheet1:Sheet5!A1:A10)

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

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

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

Diese Version ist hilfreich, wenn die Bereiche zwischen den Blättern variieren. Geben Sie sie in Ihre Ergebniszelle ein und drücken Sie die Eingabetaste.

Kutools für Excel verbessert Ihre Fähigkeit, Werte aus derselben Zelle oder demselben Bereich über mehrere Arbeitsblätter hinweg mithilfe seiner Funktion „Automatische Inkrementierung von Arbeitsblattverweisen“ zu extrahieren und zusammenzufassen. Dies ist besonders hilfreich, wenn Sie mit einer großen Anzahl von Blättern mit konsistentem Layout arbeiten.

Verwendungsschritte:

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

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

3. Im Dialogfeld:
  - Wählen Sie eine Füllreihenfolge aus dem Füllreihenfolge Dropdown-Menü aus (z. B. Spalte füllen, dann Zeile füllen).
  - In der Arbeitsblattliste, aktivieren Sie die Blätter, die die Zelle enthalten, die Sie mitteln möchten.
  - Klicken Sie auf Bereich füllen, dann schließen Sie das Dialogfeld.

4. Die ausgewählten Zellenwerte werden im Bereich (z. B. D7:D11) aufgelistet. Geben Sie dann die folgende Formel in eine andere leere Zelle ein, um den Durchschnitt zu berechnen:

=AVERAGE(D7:D11)

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

In einigen Fällen müssen Sie möglicherweise Durchschnittswerte für mehrere entsprechende Zellen über mehrere Arbeitsblätter gleichzeitig berechnen – zum Beispiel, um Ergebnisse für A1, B1 und C1 aus jedem Arbeitsblatt zusammenzufassen. Dies kann mit Standardformeln umständlich werden, aber das Hilfsprogramm „Konsolidieren (Arbeitsblätter und Arbeitsmappen)“ von Kutools für Excel vereinfacht diesen Prozess erheblich.

So verwenden Sie diese Funktion:

1. Klicken Sie auf Kutools Plus > Konsolidieren um den Konsolidierungs-Assistenten für Arbeitsblätter zu öffnen.

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

3. In Schritt 2 von 3:
  - Wählen Sie die zu berücksichtigenden Arbeitsblätter unter Arbeitsblattliste.
  - Verwenden Sie die Durchsuchen Schaltfläche um den Bereich für die Durchschnittsberechnung zu definieren.
  - Klicken Sie auf Gleicher Bereich wenn die Bereiche auf allen Blättern identisch sind.
  - Klicken Sie auf Weiter um fortzufahren.

4. In Schritt 3 von 3:
Wählen Sie Durchschnitt aus dem Funktion Dropdown-Menü. Konfigurieren Sie optional Zeilen-/Spaltenbeschriftungen nach Bedarf und klicken Sie dann auf Fertig.

5. Ein Dialogfeld fragt, ob Sie die aktuellen Einstellungen als Szenario für zukünftige Verwendung speichern möchten. Wählen Sie Ja oder Nein basierend auf Ihren Bedürfnissen.

Nun wird jede Zelle in Ihrem definierten Ausgabebereich den Durchschnitt der entsprechenden Zellen aus allen ausgewählten Arbeitsblättern widerspiegeln. Diese Methode ist besonders nützlich für wiederkehrende Operationen oder wenn große Mengen strukturierter Daten schnell zusammengefasst werden sollen.

Für Benutzer, die das Mitteln von Zellen über mehrere Arbeitsblätter automatisieren müssen – insbesondere wenn die Blattnamen nicht angrenzend sind, sich häufig ändern oder Sie Bereiche zur Laufzeit angeben möchten – kann ein VBA-Makro eine effiziente Lösung sein. Diese Methode ist am besten für fortgeschrittene Benutzer oder Arbeitsmappen geeignet, in denen Blätter oft hinzugefügt oder umbenannt werden.

Der folgende VBA-Code ermöglicht es Ihnen, Blattnamen und Zellbereiche dynamisch einzugeben und berechnet dann den Durchschnitt des angegebenen Bereichs über alle aufgelisteten Blätter. Es ist ideal für die Konsolidierung von Werten aus komplexen oder häufig aktualisierten Arbeitsmappen.

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

1. Gehen Sie zur Registerkarte Entwickler in Excel. Wenn sie nicht sichtbar ist, aktivieren Sie sie über Datei > Optionen > Menüband 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 F5 im Editor oder schließen Sie ihn und gehen Sie zu Entwickler > Makros, wählen Sie AverageAcrossSheets aus und klicken Sie auf Ausführen.

3. Wenn Sie dazu aufgefordert werden, geben Sie eine durch Kommas getrennte Liste von Arbeitsblattnamen ein (z. B. Sheet1,Sheet3,Zusammenfassung), dann geben Sie den Bereich an (z. B. A1:A10).

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

Parameterhinweise:

• Blattnamen sind nicht case-sensitiv, aber sie müssen genau übereinstimmen.
• 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 Blätter werden stillschweigend übersprungen.