Evaluate([SumProduct(if avec variable renvoi err 2015

[fzefergr]

Bonjour,

J'ai cherché sur le forum un code pour effectuer un calcul matriciel.
x1 = Evaluate([SumProduct(if(B7:B2763<>"",(1/countif(B7:B2786,B7:B2763))*((H7:H2763)=NomEtab)))])
x2 = Evaluate([SumProuct((H7:H2763=NomEtab)*(F7:F2763>(today()-30))))])
x1 c'est le calcul du nombre de personne sans doublon
x2 c'est un nombre de doc valide

Chaque mois les fichiers augmentent en nbr de ligne.
je souhaiterais mettre en variable la fin du tableau ainsi que NomEtab
Mes tests me remonte une erreur 2015.

Est-ce que quelqu'un aurait la solution ?
Merci d'avance

 

[mapomme]

Bonjour fzefergr,

Une piste en utilisant des noms dynamiques:

• définir colB avec la définition : =DECALER('Feuil 1'!$B$7;0;0;NBVAL('Feuil 1'!$B$7:$B$55555);1)
• définir colF avec la définition : =DECALER(colB;0;4)
• définir colH avec la définition : =DECALER(colB;0;6)

nota : dans la définition ci-dessus de colB, remplacer Feuil 1 par le nom de votre feuille

Puis remplacer dans les deux formules x1 et x2:

• B7:B2763 par ColB
• F7:F2763 par colF
• H7:H2763 pat colH

 

[fzefergr]

Salut mapomme
merci de ta réponse.
J'ai oublié un truc dans mon message d'origine ... je cherche en code VBA

Je ne vois pas comment coder ta réponse :/

 

[mapomme]

Re,

Il faudrait joindre un fichier exemple. D'autre part, je n'avais pas fait attention, la colonne B semble pouvoir contenir des cellules vides. Est-ce le cas ? Si oui, il faut changer la définition de colB. J'attends votre fichier exemple représentatif...

 

[fzefergr]

Bonsoir Pomme,

Tu trouveras le fichier en pj.
Concernant la formule, il n'y a pas de cellule vide en B, mais j'ai pas trouvé + simple pour récupérer le nombre qui me convient

 

[Bebere]

bonjour
bienvenue à fzefergr
une proposition

Sub Essai()
    Dim l As Long, d As Dictionary, a, madate As Date
    With Feuil1
        l = .Range("B65536").End(xlUp).Row
        .Range("B7:B" & l).Name = "ColB"
        .Range("F7:F" & l).Name = "ColF"
        .Range("H7:H" & l).Name = "ColH"
        a = .Range("B7:K" & l)
    End With
    Set d = New Dictionary    'x1
    For l = 1 To UBound(a)
        d(a(l, 1)) = a(l, 1)
    Next
    Feuil1.[B2] = d.Count

    Set d = New Dictionary    'x2
    For l = 1 To UBound(a)
        madate = Date - 30
        If a(l, 5) > madate Then d(a(l, 1)) = a(l, 1)
    Next
    Feuil1.[B3] = d.Count

End Sub

 

[fzefergr]

Bonsoir merci pour ta réponse Bebere,
j'utilisais des dico virtuel mais j'avais pas toute la notion.
J’essaierais le code des que possible.
peux-tu me dire ou se trouve la variable établissement ?
j'ai 5 ou 6 établissements à passer

 

[Bebere]

bonjour
il n'y a pas de variable établissement,je suppose alors que tu veux un résultat par établissement
si ce n'est pas cela,explique

 

[Bebere]

bonjour
cela devrait faire l'affaire

Sub Essai() 'code de JB adapté
    Dim i As Long, L As Long, d As New Dictionary, dico As Dictionary, a, b(), madate As Date
    Dim item, clé, clébase, indice As Long, ligne As Long
    With Feuil1
        L = .Range("B65536").End(xlUp).Row
        .Range("B7:B" & L).Name = "ColB"
        .Range("F7:F" & L).Name = "ColF"
        .Range("H7:H" & L).Name = "ColH"
        a = .Range("B7:K" & L)
    End With
    Set d = New Dictionary    'x1
    For L = 1 To UBound(a)    'établissement
        d(a(L, 7)) = a(L, 7)
    Next
    For Each item In d.Items
        Set dico = New Dictionary
        For L = 1 To UBound(a)
            If a(L, 7) = item Then
                clébase = item
                clé = clébase
                indice = 1
                Do While dico.Exists(clé)
                    clé = clébase & indice
                    indice = indice + 1
                Loop
                dico(clé) = L
            End If
        Next
        i = i + 1
        ReDim Preserve b(1 To 2, 1 To i)
        b(1, i) = item

        clébase = item
        clé = clébase
        indice = 1
        Do While dico.Exists(clé)
            ligne = dico(clé)
            madate = Date - 30
            If a(ligne, 5) > madate Then b(2, i) = b(2, i) + 1
            '  b(2, i) = a(ligne, 4)
            clé = clébase & indice
            indice = indice + 1
        Loop
    Next
   
    b = Application.Transpose(b)
    Feuil1.[M1].Resize(UBound(b, 1), UBound(b, 2)) = b

End Sub