Hoe het gemiddelde van cellen uit verschillende werkbladen in Excel te berekenen?

Als je het gemiddelde moet berekenen van hetzelfde bereik over verschillende werkbladen - bijvoorbeeld om de gemiddelde verkoop in het bereik A1:A10 over de werkbladen met de namen Werkblad1 tot Werkblad5 te vinden - biedt Excel een directe formule-gebaseerde oplossing. Deze aanpak werkt het beste wanneer alle werkbladen dezelfde structuur en consistente naamgeving hebben.

Stappen:

Selecteer een lege cel waar je het resultaat wilt (bijvoorbeeld cel C3), en voer de volgende formule in:

=AVERAGE(Sheet1:Sheet5!A1:A10)

Nadat je op Enter hebt gedrukt, zal Excel het gemiddelde van het gespecificeerde bereik retourneren over alle werkbladen van Werkblad1 tot Werkblad5.

Als je gemiddelden moet berekenen van verschillende bereiken over werkbladen, kun je ze handmatig opsommen:

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

Deze versie is nuttig wanneer de bereiken variëren tussen de werkbladen. Voer het in je resultaatcel in en druk op Enter.

Kutools voor Excel verbetert je vermogen om waarden te extraheren en samen te voegen uit dezelfde cel of bereik over meerdere werkbladen met behulp van de functie Dynamisch werkbladverwijzingen verhogen. Dit is vooral nuttig bij het werken met een groot aantal werkbladen met een consistente indeling.

Gebruiksstappen:

1. Open een nieuw werkblad (zoals een samenvattingsblad), selecteer dan de cel waar je het gemiddelde wilt berekenen - bijvoorbeeld D7.

2. Ga naar Kutools > Meer (binnen de Formule groep) > Dynamisch werkbladverwijzingen verhogen.

3. In het dialoogvenster:
  - Kies een vulvolgorde uit de Vulvolgorde keuzelijst (bijvoorbeeld Vullen per kolom, dan per rij).
  - In de Werkbladlijst, schakel de werkbladen in die de cel bevatten waarvan je het gemiddelde wilt berekenen.
  - Klik Vulbereik, sluit dan het dialoogvenster.

4. De geselecteerde celwaarden worden weergegeven in het bereik (bijvoorbeeld D7:D11). Voer vervolgens de volgende formule in een andere lege cel in om het gemiddelde te berekenen:

=AVERAGE(D7:D11)

Druk op Enter om het resultaat te krijgen. Dit vereenvoudigt de consolidatie maar breidt zich niet automatisch uit om nieuwe toegevoegde werkbladen te omvatten - je moet de functie opnieuw uitvoeren wanneer de lijst met werkbladen verandert.

In sommige gevallen moet je mogelijk gemiddelden berekenen voor verschillende overeenkomstige cellen over meerdere werkbladen tegelijk - bijvoorbeeld het samenvatten van resultaten voor A1, B1 en C1 van elk werkblad. Dit kan omslachtig worden met standaardformules, maar Kutools voor Excel's Combine (werkbladen en werkboeken) hulpmiddel vereenvoudigt dit proces enorm.

Hoe gebruik je deze functie:

1. Klik Kutools Plus > Combine om de Combineer werkbladen wizard te openen.

2. In de wizard (Stap 1 van 3):
Schakel Consolideer en bereken waarden uit meerdere werkboeken naar één werkblad, klik dan Volgende om door te gaan.

3. In Stap 2 van 3:
  - Selecteer de werkbladen om in te sluiten onder Werkbladlijst.
  - Gebruik de Bladeren knop om het bereik voor het gemiddelde te definiëren.
  - Klik Zelfde bereik als de bereiken identiek zijn tussen alle werkbladen.
  - Klik Volgende om door te gaan.

4. In Stap 3 van 3:
Selecteer Gemiddelde uit de Functie keuzelijst. Configureer optioneel rij/kolomlabels indien nodig, klik dan Voltooien.

5. Een dialoogvenster vraagt of je de huidige instellingen als een scenario voor later gebruik wilt opslaan. Kies Ja of Nee op basis van je behoeften.

Nu zal elke cel in je gedefinieerde uitvoerbereik het gemiddelde weerspiegelen van de overeenkomstige cellen uit alle geselecteerde werkbladen. Deze methode is vooral nuttig voor terugkerende operaties of wanneer je grote hoeveelheden gestructureerde gegevens snel moet consolideren.

Voor gebruikers die het berekenen van gemiddelden over meerdere werkbladen moeten automatiseren - vooral wanneer de werkbladnamen niet-aangrenzend zijn, vaak veranderen of wanneer je bereiken tijdens runtime wilt specificeren - kan een VBA-macro een efficiënte oplossing zijn. Deze methode is het beste voor gevorderde gebruikers of werkboeken waar werkbladen vaak worden toegevoegd of hernoemd.

De volgende VBA-code laat je dynamisch werkbladnamen en celbereiken invoeren, waarna het gemiddelde wordt berekend van het gespecificeerde bereik over alle genoemde werkbladen. Het is ideaal voor het consolideren van waarden uit complexe of vaak bijgewerkte werkboeken.

Hoe stel je deze VBA-oplossing in en gebruik je deze:

1. Ga naar het tabblad Ontwikkelaar in Excel. Als het niet zichtbaar is, schakel het dan in via Bestand > Opties > Lint aanpassen. Klik Visual Basic om de editor te openen. Ga dan naar Invoegen > Module en plak de volgende code:

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. Om de macro uit te voeren, druk F5 in de editor, of sluit het af en ga naar Ontwikkelaar > Macros, selecteer AverageAcrossSheets, en klik Uitvoeren.

3. Wanneer daarom gevraagd wordt, voer een komma-gescheiden lijst van werkbladnamen in (bijvoorbeeld Werkblad1,Werkblad3,Samenvatting), specificeer dan het bereik (bijvoorbeeld A1:A10).

4. De macro berekent het totaal en de telling vanaf elk geldig werkblad en toont het gemiddelde in een berichtenvenster.

Parameteropmerkingen:

• Werkbladnamen zijn hoofdletterongevoelig maar moeten exact overeenkomen.
• Bereik kan een enkele cel, volledige kolom (bijvoorbeeld B:B) of rechthoekig bereik (bijvoorbeeld D2:E12) zijn.
• Ongeldige of ontbrekende werkbladen worden stilzwijgend overgeslagen.