Microsoft 365 Formule Excel pour ignorer certaines cellules

Fipat

XLDnaute Occasionnel
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
VB:
=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.
 

Pièces jointes

  • test formule.xlsx
    119.7 KB · Affichages: 10
Solution
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é :
VB:
=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

Fipat

XLDnaute Occasionnel
Bonjour,

Je me permet de relancer le sujet pour une réflexion.
Si je commence comme ceci :
VB:
=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 :
Code:
 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

XLDnaute Barbatruc
Supporter XLD
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

XLDnaute Barbatruc
Supporter XLD
... si oui, un essai en PJ avec cette fonction perso :
VB:
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())
 

Pièces jointes

  • test formule (1).xlsm
    123.6 KB · Affichages: 1

ALS35

XLDnaute Occasionnel
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é :
VB:
=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
 

Pièces jointes

  • test formule.xlsx
    133.1 KB · Affichages: 3

Fipat

XLDnaute Occasionnel
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 :)
 
Dernière édition:

JHA

XLDnaute Barbatruc
Bonjour à tous,

A essayer
VB:
=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
 

Pièces jointes

  • test formule.xlsx
    120.3 KB · Affichages: 2

Fipat

XLDnaute Occasionnel
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.
 

Discussions similaires

Réponses
6
Affichages
296

Statistiques des forums

Discussions
313 322
Messages
2 097 141
Membres
106 851
dernier inscrit
Rv34