Microsoft 365 Formule Excel pour ignorer certaines cellules

Fipat

XLDnaute Occasionnel

• 23 Mai 2024

• #2

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.

""

 

Vote positif 0 Vote négatif

sylvanu

XLDnaute Barbatruc

Supporter XLD

• 23 Mai 2024

• #3

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.

 

Vote positif 0 Vote négatif

sylvanu

XLDnaute Barbatruc

Supporter XLD

• 23 Mai 2024

• #4

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

Vote positif 0 Vote négatif

sylvanu

XLDnaute Barbatruc

Supporter XLD

• 23 Mai 2024

• #5

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

 

Pièces jointes

• test formule (1).xlsx

  119.6 KB · Affichages: 1

Vote positif 0 Vote négatif

A

ALS35

XLDnaute Impliqué

• 23 Mai 2024

• #6

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

Vote positif 0 Vote négatif

Solution

sylvanu

XLDnaute Barbatruc

Supporter XLD

• 23 Mai 2024

• #7

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

 

Vote positif 0 Vote négatif

Fipat

XLDnaute Occasionnel

• 23 Mai 2024

• #8

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: 23 Mai 2024

Vote positif 0 Vote négatif

Fipat

XLDnaute Occasionnel

• 23 Mai 2024

• #9

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.

 

Vote positif 0 Vote négatif

A

ALS35

XLDnaute Impliqué

• 23 Mai 2024

• #10

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

 

Vote positif 0 Vote négatif

J

JHA

XLDnaute Barbatruc

• 23 Mai 2024

• #11

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

Vote positif 0 Vote négatif

Fipat

XLDnaute Occasionnel

• 23 Mai 2024

• #12

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.

 

Vote positif 0 Vote négatif

J

JHA

XLDnaute Barbatruc

• 23 Mai 2024

• #13

Bonjour à tous,

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

JHA

 

Pièces jointes

• test formule.xlsx

  120.6 KB · Affichages: 2

Vote positif 0 Vote négatif