Discussion :

[graphikris]

Bonjour,

Galère avec le SOMMEPROD, je m'explique :
1 fichier, 2 onglets "Plan actions" et "recap"

Dans l'onglet "plan actions", j'ai un vrai tableau dans lequel j'ai nommé les colonnes. Parmi ces colonnes, j'ai :
- "date de fin previsionnelle"
- "date de fin recalee"
- "date de fin reelle"

Dans l'onglet "recap", j'ai egalement un vrai tableau. La colonne A nommée "Annee-Actions" comporte en A2 "01/01/2020" au format aaaa soit "2020" pour A3 j'ai "2021", A4 "2022" et A5 "2023"
Je dois en colonne B calculer par année, le nbre de date en retard se situant sur l'onglet "plan actions".

Si en colonne "date de fin reelle" j'ai une date, je ne doit donc pas compter le retard puisque l'action concernée est cloturée.
Il faut donc que je calcule uniquement le nbre de date qui sont soit en retard en "date de fin previsionnelle" qui n'ont pas ete recalee ou celles qui ont ete recalée est qui sont en retard par rapport à la date du jour.

j'ai tapé cette formule mail elle me renvoie #valeur.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=SOMMEPROD(--(ANNEE(Tab_Plan_Actions[Date de fin recalée])=ANNEE([@Actions]));--(ANNEE(Tab_Plan_Actions[Date de fin prévisionnelle])=ANNEE([@Actions]));OU(Tab_Plan_Actions[Date de fin recalée]<AUJOURDHUI();Tab_Plan_Actions[Date de fin prévisionnelle]<AUJOURDHUI());--(ESTVIDE(Tab_Plan_Actions[Date de fin réelle])))

Quelqu'un pourrait il m'aider ?

Cordialement,
Graphikris.

[Pierre Dumas]

Bonjour

Je peux vous proposer la solution suivante :




Basée sur la formule ci-dessous :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
=SOMMEPROD(1*(ANNEE(Tabl1[DFPrév])=[@Actions])*(Tabl1[DFRéelle]<LaDJ)*(Tabl1[DFRéelle]<>0))
+SOMMEPROD(1*(ANNEE(Tabl1[DFPrév])=[@Actions])*(Tabl1[DFRecalée]<LaDJ)*(Tabl1[DFRecalée]<>0))
+SOMMEPROD(1*(ANNEE(Tabl1[DFPrév])=[@Actions])*(Tabl1[DFRéelle]=0)*(Tabl1[DFRecalée]=0)*(Tabl1[DFPrév]<LaDJ))

Afin d'être un peu plus lisible, j'ai facilité de la façon suivante :
- mon tableau bleu est nommé Tabl1
- la date du jour est en E1 est est nommée LaDJ (mais peut bien sûr être remplacée par AUJOURDHUI())
- les actions dans mon tableau orange sont des années (mais peuvent bien sûr être remplacées par ANNEE([@Actions]))
- j'ai fait 3 SOMMEPROD que j'additionne afin de faciliter grandement la compréhension et la maintenance (pour info : passer à la ligne dans une formule s'effectue en faisant Alt+Entrée)

Le début de chaque formule est le même : on ne prends en compte que les lignes dont l'année de la date de fin prévisionnelle est égale à l'année de l'action.
Puis, sur la première ligne, on cherche celles dont la date de fin réelle est inférieure à aujourd'hui (tout en étant différente de 0).
Sur la deuxième ligne, on cherche celles dont la date de fin recalée est inférieure à aujourd'hui (tout en étant différente de 0).
Sur la troisième ligne, on cherche celles dont la date de fin réelle est égale à zéro, et dont la date de fin recalée est égale à zéro et dont la date de fin prévisionnelle est inférieure à aujourd'hui.


En espérant avoir bien compris et que cela aide

Bon après-midi

Pierre Dumas

[graphikris]

Bonjour Pierre,

je vous remercie pour votre aide.
J'ai remplacé vos noms de colonnes par les miens mais les résultats sont incorrects.
Voici mon code :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
=SOMMEPROD(1*(ANNEE(Tab_Plan_Actions[Date de fin prévisionnelle])=[@Actions])*(Tab_Plan_Actions[Date de fin réelle]<AUJOURDHUI())*(Tab_Plan_Actions[Date de fin réelle]<>0))
+SOMMEPROD(1*(ANNEE(Tab_Plan_Actions[Date de fin prévisionnelle])=[@Actions])*(Tab_Plan_Actions[Date de fin recalée]<AUJOURDHUI())*(Tab_Plan_Actions[Date de fin recalée]<>0))
+SOMMEPROD(1*(ANNEE(Tab_Plan_Actions[Date de fin prévisionnelle])=[@Actions])*(Tab_Plan_Actions[Date de fin réelle]=0)*(Tab_Plan_Actions[Date de fin recalée]=0)*(Tab_Plan_Actions[Date de fin prévisionnelle]<AUJOURDHUI()))

et voici mon vrai tableau avec mes dates. en fond vert les actions cloturées et en fond rouge les actions en retard. Ici j'ai 1 action en retard pour 2020 et 2 pour 2021 et pour l'instant j'ai "0" pour toutres les années.

[Pierre Fauconnier]

Salut.

Les ET et OU ne passent pas en matricielle. On est obligé d'utiliser l'arithmétique booléenne de bout en bout.

Le fait d'avoir des dates et pas uniquement les années dans ton tableau de bord complexifie et allonge un peu la formule. La version XL365 permettrait de gagner un peu grâce à la fonction LET qui permet de définir des variables dans la formule.


=SOMMEPROD((Tableau1[Date de fin réelle]="")*((((Tableau1[Date de fin recalée]<>"")*Tableau1[Date de fin recalée]<AUJOURDHUI())*(ANNEE(Tableau1[Date de fin recalée])=ANNEE(E1)))+((Tableau1[Date de fin recalée]="")*(Tableau1[Date de fin prévisionnelle]<AUJOURDHUI())*(ANNEE(Tableau1[Date de fin prévisionnelle])=ANNEE(E1)))>0))




En arithmétique booléenne, ET se traduit par la multiplication et OU se traduit par l'addition. Si tu veux tester ET(A;OU(B;C)), tu dois réaliser l'opération A * ((B+C)>0) qui va transformer tes valeurs booléennes en valeurs numériques (VRAI = 1 et FAUX = 0). Dans l'exemple qui nous concerne, tout tient donc dans un seul SOMMEPROD.

[graphikris]

Merci beaucoup Pierre Fauconnier,

Sans vous je serais toujours en galère. Vous m'enlevez une énorme épine.
Vous êtes brillant

[Pierre Dumas]

Bonsoir

Même si Pierre Fauconnier (que je salue) a déjà posté une bonne réponse, ainsi que la bonne explication (petit problème sur l'année que vous n'aviez pas pris en compte comme je l'avais indiqué), je donne la mienne (histoire que je n'ai pas bossé pour rien ).



La formule est :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
=SOMMEPROD(1*(ANNEE(Tabl1[DFPrév])=ANNEE([@Actions]))*(Tabl1[DFRecalée]<AUJOURDHUI())*(Tabl1[DFRecalée]<>0)*(Tabl1[DFRéelle]=0))
+SOMMEPROD(1*(ANNEE(Tabl1[DFPrév])=ANNEE([@Actions]))*(Tabl1[DFRéelle]=0)*(Tabl1[DFRecalée]=0)*(Tabl1[DFPrév]<AUJOURDHUI()))

En espérant que cela soit aussi une aide

Belle fin de journée

Pierre Dumas

[Pierre Fauconnier]

Salut Pierre,

J'espère que tu vas bien

La tienne est plus lisible et compréhensible que la mienne puisqu'elle éclate le OU en en deux SOMMEPROD. On s'y retrouve plus facilement dans les parenthèses que dans la mienne =>

[Pierre Dumas]

Bonjour Pierre

Oui, je vais bien et j'espère que tu vas au moins aussi bien que moi

Quant à la formule j'avais aussi commencé en une seule, mais je me suis emberlificoté. J'avais donc un peu changé de fusil d'épaule en utilisant deux SOMMEPROD.
De plus, en écrivant sur plusieurs lignes la formule dans la cellule, je trouve que cela aide grandement à la maintenance.

Belle soirée à toi et à ceux qui lisent.

Pierre