Hoe berekent u het gemiddelde van cellen uit verschillende werkbladen in Excel?

Als u het gemiddelde wilt berekenen van hetzelfde bereik over meerdere werkbladen—bijvoorbeeld om de gemiddelde verkoop in bereik A1:A10 te bepalen over bladen met de namen Sheet1 tot en met Sheet5—biedt Excel een directe, formulegebaseerde oplossing. Deze aanpak werkt het beste wanneer alle bladen dezelfde structuur en consistente naamgeving hebben.

Stappen:

Selecteer een lege cel waarin u het resultaat wilt plaatsen (bijvoorbeeld cel C3) en voer de volgende formule in:

=AVERAGE(Sheet1:Sheet5!A1:A10)

Nadat u op Enter hebt gedrukt, geeft Excel het gemiddelde van het beperkte bereik over alle bladen van Sheet1 tot Sheet5.

Als u waarden uit verschillende bereiken over bladen wilt middelen, kunt u deze handmatig opsommen:

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

Deze versie is handig wanneer de bereiken per werkblad verschillen. Voer deze in uw resultaatcel in en druk op Enter.

Kutools voor Excel verbetert uw mogelijkheid om waarden uit dezelfde cel of hetzelfde bereik over meerdere werkbladen te extraheren en te consolideren met de functie Automatisch werkbladverwijzingen verhogen. Dit is vooral handig bij een groot aantal werkbladen met een consistente lay-out.

Gebruiksstappen:

1. Open een nieuw werkblad (bijvoorbeeld een samenvattingsblad) en selecteer vervolgens de cel waarin u het gemiddelde wilt berekenen—bijvoorbeeld D7.

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

3. In het dialoogvenster:
- Kies een vulvolgorde uit de keuzelijst Vulvolgorde(bijv.)Vullen per kolom, dan per rij).
- Vink in het vak Werkbladlijst de bladen aan die de cel bevatten waarvan u het gemiddelde wilt berekenen.
- Klik op Bereik invullen en sluit het dialoogvenster.

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

=AVERAGE(D7:D11)

Druk op Enter om het resultaat te verkrijgen. Dit vereenvoudigt consolidatie, maar wordt niet automatisch uitgebreid naar nieuw toegevoegde bladen—u moet de functie opnieuw uitvoeren wanneer de lijst met bladen wijzigt.

In sommige gevallen moet u mogelijk gelijktijdig gemiddelden berekenen voor meerdere corresponderende cellen over verschillende werkbladen—bijvoorbeeld om de resultaten van A1, B1 en C1 van elk werkblad samen te vatten. Met standaardformules kan dit omslachtig zijn, maar het Kutools voor Excel-hulpprogramma Combineren (werkbladen en werkmappen) vereenvoudigt dit proces aanzienlijk.

Hoe gebruikt u deze functie:

1. Klik op KUTOOLS PLUS > Combineren om de wizard Werkbladen samenvoegen te openen.

2. In de wizard (Stap 1 van 3):
Vink Gegevens samenvoegen en berekenen vanuit meerdere werkmappen naar één werkblad aan en klik op Volgende om door te gaan.

3. In stap 2 van 3:
- Selecteer onder Werkbladlijst de werkbladen die u wilt opnemen.
- Gebruik de knop Bladeren om het bereik voor het gemiddelde te definiëren.
- Klik op Hetzelfde bereik als de bereiken op alle bladen identiek zijn.
- Klik op Volgende om verder te gaan.

4. In stap 3 van 3:
Selecteer Gemiddelde in de keuzelijst Functie. Configureer eventueel rij- en kolomlabels naar behoefte en klik op Voltooien.

5. Er verschijnt een dialoogvenster met de vraag of u de huidige instellingen als scenario wilt opslaan voor toekomstig gebruik. Kies Ja of Nee, afhankelijk van uw behoeften.

Elke cel in uw gedefinieerde Plaatsingsgebied lijst geeft nu het gemiddelde weer van de corresponderende cellen uit alle Geselecteerde werkbladen. Deze methode is vooral nuttig voor terugkerende bewerkingen of bij het snel consolideren van grote hoeveelheden gestructureerde gegevens.

Voor gebruikers die het gemiddelde van cellen over meerdere werkbladen willen automatiseren—vooral wanneer de bladnamen niet opeenvolgend zijn, vaak wijzigen of wanneer u het bereik tijdens runtime dynamisch wilt opgeven—is een VBA-macro een efficiënte oplossing. Deze methode is het meest geschikt voor gevorderde gebruikers of werkmappen waarin bladen regelmatig worden toegevoegd of hernoemd.

De volgende VBA-code stelt u in staat om bladnamen en celbereiken dynamisch in te voeren en vervolgens het gemiddelde van het opgegeven bereik over alle genoemde bladen te berekenen. Deze code is ideaal voor het consolideren van waarden uit complexe of regelmatig bijgewerkte werkmappen.

Hoe u deze VBA-oplossing instelt en gebruikt:

1. Ga naar het tabblad Ontwikkelaar in Excel. Als dit niet zichtbaar is, schakel het dan in via Bestand > Opties > Het lint aanpassen. Klik op Visual Basic om de editor te openen. Ga vervolgens 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. Druk op F5 in de editor om de macro uit te voeren, of sluit de editor en ga naar Ontwikkelaar > Macro's, selecteer AverageAcrossSheets en klik op Uitvoeren.

3. Voer, wanneer hierom wordt gevraagd, een komma-gescheiden lijst met werkbladnamen in (bijv.)Sheet1,Sheet3,Summary) en geef daarna het bereik op (bijv. A1:A10).

4. De macro berekent het totaal en het aantal voor elk geldig werkblad en toont het gemiddelde in een berichtvenster.

Opmerkingen bij parameters:

• Bladnamen zijn niet hoofdlettergevoelig, maar moeten exact overeenkomen.
• Een bereik kan bestaan uit één enkele cel, een volledige kolom (bijvoorbeeld B:B) of een rechthoekig gebied (bijvoorbeeld D2:E12).
• Ongeldige of ontbrekende bladen worden automatisch en zonder melding overgeslagen.