Microsoft 365 Formule Excel pour ignorer certaines cellules

[Fipat]

Bonjour,

J'explique mon souci, j'ai un classeur ou je veux faire la moyenne de certaines cellules.
sur le fichier joint si je prend la ligne 27
J'utilise cette formule

=SIERREUR(ARRONDI(SOMME(SI(Q27>0;Q27;0); SI(AD27>0;AD27;0); SI(AQ27>0;AQ27;0); SI(BD27>0;BD27;0); SI(BQ27>0;BQ27;0); SI(CD27>0;CD27;0)) / SOMME((Q27>0)+(AD27>0)+(AQ27>0)+(BD27>0)+(BQ27>0)+(CD27>0));0);"")

dans cette ligne j'ai une valeur négative et une valeur à 0, pour en faire la moyenne ma formule fonctionne, car elle ignore les valeurs négative et les valeurs égale à 0, mais je voudrais dans le cas ou j'ai une valeur négative prendre en compte seulement que les cellule après la valeur négative.
Dans la ligne 27 (qui est le bon exemple) ma formule en CE27 renvoi la moyenne de 710, ce qui est normal, mais vu que j'ai une valeur positive avant le valeur négative et que je ne veux pas prendre en compte les valeurs avant la négative (si existante) le résultat devrait être 762 et non 710.

Je veux prendre en compte que les valeurs positive et ignorer celle qui sont avant la valeur négative.
Seule les cellules sélectionnées sont a prendre en compte car entre ces cellules d'autres valeurs sont groupées et ne sont pas à prendre en compte.
Le fichier exemple sera peut-être plus explicite.

Merci pour votre aide.

 

[Fipat]

Bonjour,

Je me permet de relancer le sujet pour une réflexion.
Si je commence comme ceci :

=SI(OU(Q11<=0; AD11<=0; AQ11<=0; BD11<=0; BQ11<=0; CD11<=0);

Cela vérifie dans un premier si une de ces cellules est à 0 ou négative, c'est bien cela ?


Ensuite si je rajoute ceci :

 MOYENNE(SI(Q11>0; Q11); SI(AD11>0; AD11); SI(AQ11>0; AQ11); SI(BD11>0; BD11); SI(BQ11>0; BQ11); SI(CD11>0; CD11));
    ""

Ceci devrait normalement calculer la moyenne des cellules Q11, AD11, AQ11, BD11, BQ11, et CD11 en ignorant celles qui sont négatives ou égales à 0, non ?

J'avais espéré, mais le résultat n'est pas le bon.

Je galère vraiment sur cette formule. Merci pour votre aide.

""

 

[sylvanu]

Bonjour Fipat,
A t on le droit au VBA ? car ce serait complexe en formules.
Votre post précédent ne peux pas marcher car la première formule détectera si une valeur est négative ou non mais ne permet pas de calculer la moyenne à partir d'une cellule précise.

 

[sylvanu]

... si oui, un essai en PJ avec cette fonction perso :

Function Fipat(Ligne%)
Dim T(1 To 6), i%, j%, N%
T(1) = Cells(Ligne, "Q"): T(2) = Cells(Ligne, "AD"): T(3) = Cells(Ligne, "AQ")
T(4) = Cells(Ligne, "BD"): T(5) = Cells(Ligne, "BQ"): T(6) = Cells(Ligne, "CD")
For i = 1 To 6
    If T(i) < 0 Then
        For j = 1 To i: T(j) = 0: Next j
    End If
Next i
For i = 1 To 6
    If T(i) > 0 Then N = N + 1
Next i
For i = 1 To 6: Fipat = Fipat + T(i): Next i
If N = 0 Then Fipat = "" Else Fipat = Fipat / N
End Function

La syntaxe, quelque soit la ligne est : =Fipat(Ligne())

 

[sylvanu]

... sinon avec des colonnes masquées, voir PJ.

 

[ALS35]

Bonjour à tous,
Effectivement ne prendre que les valeurs après la valeur négative est complexe par formule.
Néanmoins une proposition valable pour Excel 365 qui donne le résultat souhaité :

=LET(t;CHOISIRCOLS($A11:$CD11;17;30;43;56;69;82);n;SIERREUR(EQUIVX(VRAI;t<0);0);d;PRENDRE(t;;n-NBVAL(t));SIERREUR(MOYENNE(SI(d=0;"";d));""))

Cordialement

 

[sylvanu]

C'est vrai que 365 offre bien des possibilités absentes sur les versions antérieures. 🙂

 

[Fipat]

Bonjour Sylvanu, ALS35,

Merci infiniment à tous les deux.

Sylvanu,

• Pour le VBA oui cela serait une solution, mais ce fichier est utilisé par plusieurs personne et certains n'ont pas le droit à l’accès dev. Ce qui sera bloquant car les lignes qui peuvent augmenter par l'action de plusieurs utilisateurs.
• La solution des colonnes cachées est Nickel, cela fonctionne très bien. Je n'avais pas pensé à décomposer ainsi.

ALS35,

• Cette formule fonctionne parfaitement, je vais essayer de la déchiffrer pour bien comprendre son action.
• Est-ce que cela veut dire que si un utilisateur a une version antérieure à 365, cela ne fonctionnera pas avec sa version ?

En tout cas, milles merci à vous deux pour votre aide précieuse 🙂

 

[Fipat]

Ah oui désolé, j'ai choisis une des solutions comme étant la solution qui résous ma demande, mais les trois propositions sont des solutions qui fonctionnent.
🙂👍👍👍

 

[ALS35]

Re,
@sylvanu
Oui 365 est puissant, mais en fait la formule n'utilise que le même principe que tu as codé en VBA
@Fipat
Cette formule n'est valable que pour 365 et ne fonctionnera pas sur les autres versions (même 2021)
Cordialement

 

[JHA]

Bonjour à tous,

A essayer

=SI(CD11<=0;0;SI(BQ11<=0;CD11;SI(BD11<=0;SOMME(BQ11;CD11)/2;SI(AQ11<=0;SOMME(CD11;BQ11;BD11)/3;SI(AD11<=0;SOMME(AQ11;BD11;BQ11;CD11)/4;SI(Q11<=0;SOMME(AD11;AQ11;BD11;BQ11;CD11)/5;SOMME(Q11;AD11;AQ11;BD11;BQ11;CD11)/6))))))

JHA

 

[Fipat]

Bonjour JHA,

Formule qui fonctionne à merveille et qui commence à être complexe pour moi.
Mais je vais la déchiffrer en détail pour essayer de la comprendre.

Si je peux me permettre, j'ai un deuxième point à traiter que je n'avais pas pris en compte dans ce travail.
Je voudrais rajouter un garde fou pour des nombres incohérents.
Je m'explique si j'ai par exemple 997 en 2022 et que en 2021 j'ai une saisie à 12500 ou en 2023 d’ailleurs, je voudrais mettre un garde fou en me disant que d'une année à l'autre je ne peux pas avoir une différence de plus 1500.
Pour information ces données sont des compteurs de matériels.

Si je dois faire un deuxième sujet pour cela, merci de me le préciser pour que je le lance.
Merci beaucoup à tous pour votre intervention.

 

[JHA]

Bonjour à tous,

J'ai mis une MFC en rouge pour le total dépassant de 1500 de l'année précédente.

JHA