Averageifs avec plusieurs critères sur la même colonne

Bonjour!

Je suis bloqué sur la fonction averageifs() à laquelle j'aimerais appliquer plusieurs critères sur la même colonne. Faire la moyenne si ma colonne critère contient du texte avec certains caractères.

Le code fonctionne avec la ligne suivante:

Application.WorksheetFunction.AverageIfs(rng.Columns(Col), rng.Columns(1), "*pir_glu*")

mais pas avec celle-ci:

Application.WorksheetFunction.AverageIfs(rng.Columns(Col), rng.Columns(1), "*pir_glu*", rng.Columns(1), "*pir_pro*")

où j'ai une erreur d'exécution 1004 définie par l'application ou par l'objet.

Mon but étant de faire la moyenne de toutes les cellules de la colonne d'indice "col" pour lesquelles les cellules de la colonne (1) contiennent du texte avec *pir_glu* ou *pir_pro*...

Je sais qu'on peux utiliser la fonction averageifs() de cette façon (enfin je crois )

J'ai essayé plein de choses différentes mais sans succès donc si quelqu'un peu m'aider merci d'avance!!!

Bonjour,

Je pense avant tout à une mauvaise utilisation de la fonction.

Mais l'aide d'excel est un peu confuse.

Ton erreur 1004 est une erreur 2007, soit #DIV/0!.

Dans le classeur, j'ai reproduit des exemples dans une feuille de calcul :

=MOYENNE.SI.ENS(B2:B10;A2:A10;"pir_pro";A2:A10;"pir_glu") retourne #DIV/0!

Ces 2 formules retournent le bon résultat :

=MOYENNE(SI((A2:A10="pir_glu")+(A2:A10="pir_pro");B2:B10))

=SOMMEPROD((A2:A10="pir_pro")+(A2:A10="pir_glu");B2:B10)/SOMMEPROD((A2:A10="pir_pro")+(A2:A10="pir_glu"))

La 1ère. est matricielle.

Bon, il y a des pros des formules. Attendons leurs commentaires avisés.

Après, il faudra coder ces formules en VBA !?

Cdlt.

Bonjour Jean-Eric!

Merci pour ta réponse!

Merci pour ce fichier, j'avais pas pensé à vérifier comme ça! Je trouvais ça bizarre surtout que la première formule me renvoie la même erreur que la deuxième si la chaîne de caractère recherchée dans la colonne n'est pas trouvée... Donc la fonction averageifs() ne serait pas à utiliser avec des conditions sur la même colonne...

Effectivement il faudrait que je puisse le faire en VBA mais du coup je vais arrêté de chercher avec cette fonction et envisager une autre solution...

Et puis effectivement si quelqu'un à un commentaire à faire sur ta réponse je suis preneur!

*Du coup je laisse le sujet ouvert encore un peu au cas où...

Rebonjour!

Je reviens sur ce poste car à défaut de pouvoir appliquer plusieurs contraintes sur une même colonne avec la fonction averageifs(), j'essaye de faire une boucle sur ma colonne avec le code suivant qui fonctionne très bien dans le fichier exemple:

Sub somme_si_ens()
Dim CEL As Range, RAN As Range
Dim RES As Double
Dim DIV As Integer

With Feuil1

    DERLIG = .Range("A" & Rows.Count).End(xlUp).Row
    DERCOL = .Cells(1, Cells.Columns.Count).End(xlToLeft).Column
    Set Rng = .Cells(2, 1).Resize(DERLIG - 1, DERCOL)

End With

For col = 2 To DERCOL
RES = 0
DIV_1 = 0
Set RAN = Rng.Range(Rng.Cells(2, 1), Rng.Cells(DERLIG, 1))

        For Each CEL In RAN
            If CEL.Value Like "*pir_glu*" Then
                RES = RES + CEL.Offset(0, col - 1).Value
                DIV_1 = DIV_1 + 1
              End If
        Next CEL

Rng.Cells(12, col).Value = RES
MsgBox DIV_1
Next col
End Sub

Par contre dans mon fichier original (que je ne peux pas poster malheureusement) et où j'ai inséré exactement le même code à l'intérieur d'un code un peu plus long, ma variable RES garde la valeur de "0". En faisant un debeugage pas à pas je me rends compte que le curseur saute toujours les deux lignes qui se trouvent entre "If" et "End if" (contrairement au fichier exemple où ces deux lignes sont toujours lues)!!

Je pense que ça sera difficile sans le fichier de base mais si quelqu'un a une idée...???

Dans mon fichier original il y a d'autres lignes de code entre "for col = 2 to dercol" et "next col" mais ce sont juste des attributions de valeurs à des cellules...

De plus j'aimerais savoir s'il est possible de mettre plusieurs conditions après l'opérateur "like"?? ici je n'ai que "*pir_glu*" mais est-il possible d'en ajouter d'autres et comment? J'ai cherché mais je n'ai rien trouvé qui fonctionne...

En tout merci d'avance si quelqu'un a une piste!!

Bonsoir Maroon, Jean-Eric, le forum,

Je ne suis pas un pro des formules (ni du vba non plus, ) mais pourquoi ne pas filtrer tes données avec tes 2 critères et ensuite faire la moyenne des données filtrées ?

Cordialement,

Bonsoir xorsankukai ! Bonsoir Jean-Eric !

Merci pour ta proposition xorsankukai, cette solution pourra me servir dans certains cas. Par contre il me fallait aussi la solution en macro...

Du coup à force de refaire et refaire des modifications je viens de comprendre que ce qui n'allait pas c'est que l'opérateur Like tient compte de la casse dans les arguments !! J'y avais pensé au début mais je pense que mon code n'était pas encore bon à ce moment là donc ça ne fonctionnait pas, et ensuite j'ai plus pensé à revenir sur cette possibilité!!

Autre chose, j'ai réussi à trouver une solution pour attribuer plusieurs arguments à l'opérateur Like comme ceci:

If cel.Value Like "*pir_glu*" Or cel.Value Like "*pir_pro*" Or cel.Value Like "etc..."

Oui ça non plus j'y ai pas pensé tout de suite, je cherchais des combinaisons avec les "" et les **

Merci pour votre implication!! J'espère que ce petit soucis évitera à d'autres de chercher....

Et pour la fonction averageifs() avec plusieurs contriantes sur la même colonne, s'il n'y a pas de commentaires d'ici quelques temps c'est que Jean-Eric a raison et que cette fonction ne peut pas s'utiliser comme ça....

Re,

Merci pour ta proposition xorsankukai, cette solution pourra me servir dans certains cas. Par contre il me fallait aussi la solution en macro...

A l'aide de l’enregistreur de macro, on obtient ceci:

Sub Bouton1_Cliquer()
 Dim derlig As Integer

 Application.ScreenUpdating = False

  With Sheets("Feuil1")
   derlig = .Range("A" & Rows.Count).End(xlUp).Row + 3
   .ListObjects("Tableau1").Range.AutoFilter Field:=1, Criteria1:= _
        "=pir_glu", Operator:=xlOr, Criteria2:="=pir_pro"
   .Range("B" & derlig).FormulaR1C1 = "=SUBTOTAL(1,Tableau1[Valeur1])"
   .Range("B" & derlig).AutoFill Destination:=Range("B" & derlig & ":E" & derlig), Type:=xlFillDefault
   .Range("B" & derlig & ":E" & derlig).NumberFormat = "0.00"
   .ListObjects("Tableau1").Range.AutoFilter Field:=1
  End With
End Sub

Cordialement,

Bonjour xorsankukai! Bonjour Jean-Eric!

Merci pour ce code! Je vais prendre un moment pour le comprendre ce week-end parce que c'est un peu confus pour un débutant comme moi, et je reviens.

A plus,