Attribute VB_Name = "modKPIIDR11"
Option Explicit

Public Sub CalculerKPIHypothesesIDR()
    Dim wsPop As Worksheet
    Dim wsCalc As Worksheet
    Dim wsKPI As Worksheet
    Dim lastRowPop As Long
    Dim lastRowCalc As Long
    Dim effectif As Long
    Dim provisionTotale As Double
    Dim provisionMoyenne As Double
    Dim ageMoyen As Double
    Dim ancienneteMoyenne As Double
    Dim masseSalariale As Double
    Dim nbHommes As Long
    Dim nbFemmes As Long

    Set wsPop = Worksheets("POPULATION_SALARIES")
    Set wsCalc = Worksheets("CALCUL_IDR_IFC")
    Set wsKPI = ResetSheetIDR("KPI_HYPOTHESES_IDR")

    lastRowPop = wsPop.Cells(wsPop.Rows.Count, 1).End(xlUp).Row
    lastRowCalc = wsCalc.Cells(wsCalc.Rows.Count, 1).End(xlUp).Row
    effectif = lastRowPop - 1
    If effectif <= 0 Then
        MsgBox "Aucune donnee dans POPULATION_SALARIES.", vbExclamation
        Exit Sub
    End If

    ageMoyen = MoyenneNumeriqueIDR(wsPop.Range("R2:R" & lastRowPop))
    ancienneteMoyenne = MoyenneNumeriqueIDR(wsPop.Range("S2:S" & lastRowPop))
    masseSalariale = SommeNumeriqueIDR(wsPop.Range("N2:N" & lastRowPop))
    provisionTotale = SommeNumeriqueIDR(wsCalc.Range("AA2:AA" & lastRowCalc))

    If effectif > 0 Then provisionMoyenne = provisionTotale / effectif
    nbHommes = Application.WorksheetFunction.CountIf(wsPop.Range("D2:D" & lastRowPop), "H")
    nbFemmes = Application.WorksheetFunction.CountIf(wsPop.Range("D2:D" & lastRowPop), "F")

    wsKPI.Range("A1:B1").value = Array("Indicateur", "Valeur")
    wsKPI.Cells(2, 1).value = "Effectif"
    wsKPI.Cells(2, 2).value = effectif
    wsKPI.Cells(3, 1).value = "Provision Totale"
    wsKPI.Cells(3, 2).value = provisionTotale
    wsKPI.Cells(4, 1).value = "Provision Moyenne"
    wsKPI.Cells(4, 2).value = provisionMoyenne
    wsKPI.Cells(5, 1).value = "Age Moyen"
    wsKPI.Cells(5, 2).value = ageMoyen
    wsKPI.Cells(6, 1).value = "Anciennete Moyenne"
    wsKPI.Cells(6, 2).value = ancienneteMoyenne
    wsKPI.Cells(7, 1).value = "Masse Salariale"
    wsKPI.Cells(7, 2).value = masseSalariale
    wsKPI.Cells(8, 1).value = "Hommes"
    wsKPI.Cells(8, 2).value = nbHommes
    wsKPI.Cells(9, 1).value = "Femmes"
    wsKPI.Cells(9, 2).value = nbFemmes

    FormatReferentielIDR wsKPI, "A1:B1"
    wsKPI.Range("B3:B4").NumberFormat = "#,##0.00"
    wsKPI.Range("B7:B7").NumberFormat = "#,##0.00"
    wsKPI.Range("B5:B6").NumberFormat = "0.00"
    wsKPI.Columns.AutoFit
    MsgBox "KPI calcules.", vbInformation
End Sub

Private Function MoyenneNumeriqueIDR(ByVal plage As Range) As Double
    Dim cell As Range
    Dim total As Double
    Dim compteur As Long
    For Each cell In plage
        If Not IsError(cell.value) Then
            If IsNumeric(cell.value) Then
                total = total + CDbl(cell.value)
                compteur = compteur + 1
            End If
        End If
    Next cell
    If compteur > 0 Then MoyenneNumeriqueIDR = total / compteur Else MoyenneNumeriqueIDR = 0
End Function

Private Function SommeNumeriqueIDR(ByVal plage As Range) As Double
    Dim cell As Range
    Dim total As Double
    For Each cell In plage
        If Not IsError(cell.value) Then
            If IsNumeric(cell.value) Then total = total + CDbl(cell.value)
        End If
    Next cell
    SommeNumeriqueIDR = total
End Function