Voorwaardelijke gemiddeldes berekenen over vele tabbladen

[maurits21]

Hallo,

Ik wil een rapporatage maken over gegevens (evaluatie werknemers) die zich over meerdere tabbladen bevinden. Het aantal tabbladen zal toenemen, de gezochte gegegevens staan telkens in dezelfde cel. Ik kom tot een gemiddelde door de tabbladen met gegevens in te sluiten tussen een tabblad A en een tabblad Z. De formule voor de gemiddelde waarde wordt dan: =GEMIDDELDE(A:Z!A1) waarbij cel A1 de waarde bevat. Hiermee heb ik een gemiddelde score voor de hele groep.

Nu wil ik echter ook per Teamleider rapporteren. Daarvoor dacht ik de volgende formule te gebruiken: =GEMIDDELDEN.ALS(A:Z!C10;A:Z!D8;"Naam Teamleider")

Helaas werkt deze formule niet. Zie bijlage als voorbeeld. Tabblad _Score is waar ik de totalen kwijt wil.Bekijk bijlage Evaluatieformulier voorbeeld.xlsmBekijk bijlage Evaluatieformulier voorbeeld.xlsm

 

[rebmog]

Volgens mij lukt dat niet met standaard Excel-functies. Een specifieke functie kan uitkomst bieden:

Function tst(r1 As Range, zoektekst As Range, r2 As Range) As Double
    Application.Volatile
    For i = 1 To Sheets.Count
        If Sheets(i).Range(r1.Address) = zoektekst Then
            j = j + Sheets(i).Range(r2.Address)
            k = k + 1
        End If
    Next
    tst = j / k
End Function

De functie kun je dan bijvoorbeeld in cel C30 van het totaalblad aanroepen met: =tst(D7;C28;C10)

 

[maurits21]

Bedankt, maar ben niet zo thuis in VBA. Ik begrijp dat ik her en der nog wat variabelen in moet voegen, maar waar en wat?

Edit
En het aanroepen van de functie bevat de cel C28. Waarom wordt deze opgenomen? Bevat zowel op het indivu als het totaalblad geen waarde.

 

[rebmog]

Deze functie moet je in een module plaatsen. Aangezien jouw voorbeeld al VBA bevat ging ik ervan uit dat je daarmee op de hoogte was. In het voorbeeld dat je geplaatst hebt is C28 van het totaalblad geen lege cel, maar staat daarin "Peter van der Waal (WTP)". Dit is de waarde waarop gezocht wordt in de diverse tabbladen. Deze waarde wordt gezocht in cel D7 van elk tabblad (dat is de eerste positie van de formule) en daarna wordt het gemiddelde bepaald op basis van waarden in cel C10 van elk tabblad (dat is de laatste positie van de formule).

NB:Ik heb in deze functie gekozen voor het ingeven van celadressen, omdat je de formule dan makkelijker kunt kopiëren.

 

[maurits21]

VBA was het betere google-werk. Voortborduren op werk van een ander lukt me meestal wel. Heb het voor elkaar, nu eens uitzoeken of ik het voor alle velden voor elkaar krijg. Nogmaals dank!

 

[maurits21]

Hallo Rebmog,

Toch nog iets dat niet goed gaat. De gemiddelde score per competentie wordt nu niet goed berekend. Lege cellen zouden niet mee moeten tellen. Ik heb nu 11 beoordelingen van 1 afdeling waarvan er slechts 1 na de eerste maand beoordeeld is. Toch is de gemiddelde score voor die afdeling 0,27 ipv 3. Hoe is dit zo op te lossen dat alleen gevulde cellen meetellen voor het gemiddelde? Alvast wederom mijn dank!

 

[rebmog]

Ik ging er van uit dat altijd waarden werden ingevuld. Maar hierbij een aangepaste functie die het gemiddelde alleen berekent over ingevulde scores.

Function tst(r1 As Range, zoektekst As Range, r2 As Range) As Double
    Application.Volatile
    For i = 1 To Sheets.Count
        With Sheets(i)
            If .Range(r1.Address) = zoektekst And .Name <> "_score" Then
                If .Range(r2.Address) <> "" Then
                    j = j + .Range(r2.Address)
                    k = k + 1
                End If
            End If
        End With
    Next
    tst = j / k
End Function

 

[maurits21]

GOUD! Nogmaals bedankt, het werkt nu feilloos