XL 2013 erreur dans une formule excel Sommeprod

Boostez vos compétences Excel avec notre communauté !

Rejoignez Excel Downloads, le rendez-vous des passionnés où l'entraide fait la force. Apprenez, échangez, progressez – et tout ça gratuitement ! 👉 Inscrivez-vous maintenant !

Cattitude

XLDnaute Nouveau
Bonjour,
Afin de gérer le planning de mes collègues au travail, j'ai fait un tableur sur excel que j'ai incrémenté au fur et a mesure.
Petit soucis, dans les formules contenu dans la plupart des colonnes de C à AG il y a un bug que je pense identifier mais que je n'arrive pas a corriger.
Lorsqu'un des collègues est absent, un bouton VBA dévolu à ça permet de noter son absence dans une liste (de AS à AX). tout fonctionnait à merveille avant quo'n me demande d'y ajouter des absence en demi journée (matin et/ou aprem) et la le bug arrive : dans ma formule tant qu'il n'y a que des "journée" notée dans la colonne AW tout fonctionne mais des qu'il y a un "matin" ou "après midi", seule la cellule concerné fonctionne et toute les autres s’éteignent (mise en forme conditionnelle) et font comme si la toute première boucle "si" des formules de C à AG passait en valeur "si faux".
je pense qu'il faudrait que j'ajoute un étage "Si" au moment de mon premier sommeprod mais alors je ne sais pas comment récupérer la bonne ligne dans la liste de AS:AX pour y mettre la bonne valeur.
L'explication peut être un peu confuse mais vous comprendrez surement mieux avec le fichier. Si besoin le mot de passe est "corinne".
Je maitrise mieux excel que le VBA c'est pour ca que je le fais ainsi et si une solution est possible en excel je préférerai.
Merci d'avance a tous
 

Pièces jointes

Bonjour,

Afin de gérer le planning de mes collègues au travail, j'ai fait un tableur sur excel que j'ai incrémenté au fur et a mesure.
Excel est un tableur.


Petit soucis, dans les formules contenu dans la plupart des colonnes de C à AG il y a un bug que je pense identifier mais que je n'arrive pas a corriger.
Parles-tu de cette formule mise en matriciel ??? :
=SI(SOMMEPROD((($AS$31:$AS$244<=DATE(Saisir_Année;RECHERCHEV($B$2;correspondance_mois;2;FAUX);C$3))*1)*(($AT$31:$AT$244>=DATE(Saisir_Année;RECHERCHEV($B$2;correspondance_mois;2;FAUX);C$3))*1)*($AU$31:$AU$244=SI(ESTERREUR(RECHERCHEV(DATE(Saisir_Année;RECHERCHEV($B$2;correspondance_mois;2;FAUX);C$3);jours_ferie;2;FAUX));SI(ESTVIDE(INDEX(DECALER(SI(format_planning="numéros de planning";$C$248;$Q$248);LIGNE(INDEX($A:$A;EQUIV(SI(C$19="";"normal";C$19);$A:$A;0);1))-LIGNE($C$248)+1;SI(EST.IMPAIR(C$1);0;7);14;7);LIGNE($B5)-LIGNE($B$4);EQUIV(C$2;$C$245:$I$245;0);1));"";INDEX(DECALER(SI(format_planning="numéros de planning";$C$248;$Q$248);LIGNE(INDEX($A:$A;EQUIV(SI(C$19="";"normal";C$19);$A:$A;0);1))-LIGNE($C$248)+1;SI(EST.IMPAIR(C$1);0;7);14;7);LIGNE($B5)-LIGNE($B$4);EQUIV(C$2;$C$245:$I$245;0);1));""))*(SI(OU($AW$31:$AW$244="matin";$AW$31:$AW$244="après midi");$AW$31:$AW$244=$B5;1)))>0;INDEX($AS$31:$AV$244;SOMMEPROD((($AS$31:$AS$244<=DATE(Saisir_Année;RECHERCHEV($B$2;correspondance_mois;2;FAUX);C$3))*1)*(($AT$31:$AT$244>=DATE(Saisir_Année;RECHERCHEV($B$2;correspondance_mois;2;FAUX);C$3))*1)*($AU$31:$AU$244=SI(ESTERREUR(RECHERCHEV(DATE(Saisir_Année;RECHERCHEV($B$2;correspondance_mois;2;FAUX);C$3);jours_ferie;2;FAUX));SI(ESTVIDE(INDEX(DECALER(SI(format_planning="numéros de planning";$C$248;$Q$248);LIGNE(INDEX($A:$A;EQUIV(SI(C$19="";"normal";C$19);$A:$A;0);1))-LIGNE($C$248)+1;SI(EST.IMPAIR(C$1);0;7);14;7);LIGNE($B5)-LIGNE($B$4);EQUIV(C$2;$C$245:$I$245;0);1));"";INDEX(DECALER(SI(format_planning="numéros de planning";$C$248;$Q$248);LIGNE(INDEX($A:$A;EQUIV(SI(C$19="";"normal";C$19);$A:$A;0);1))-LIGNE($C$248)+1;SI(EST.IMPAIR(C$1);0;7);14;7);LIGNE($B5)-LIGNE($B$4);EQUIV(C$2;$C$245:$I$245;0);1));""))*LIGNE($AS$31:$AS$244))-30;4;1);SI(ESTERREUR(RECHERCHEV(DATE(Saisir_Année;RECHERCHEV($B$2;correspondance_mois;2;FAUX);C$3);jours_ferie;2;FAUX));SI(ESTVIDE(INDEX(DECALER(SI(format_planning="numéros de planning";$C$248;$Q$248);LIGNE(INDEX($A:$A;EQUIV(SI(C$19="";"normal";C$19);$A:$A;0);1))-LIGNE($C$248)+1;SI(EST.IMPAIR(C$1);0;7);14;7);LIGNE($B5)-LIGNE($B$4);EQUIV(C$2;$C$245:$I$245;0);1));"";INDEX(DECALER(SI(format_planning="numéros de planning";$C$248;$Q$248);LIGNE(INDEX($A:$A;EQUIV(SI(C$19="";"normal";C$19);$A:$A;0);1))-LIGNE($C$248)+1;SI(EST.IMPAIR(C$1);0;7);14;7);LIGNE($B5)-LIGNE($B$4);EQUIV(C$2;$C$245:$I$245;0);1));""))
 
Dernière édition:
Je ne sais pas si la formule est simplifiable, mais au niveau du VBA il y a quelques simplifications possibles. 😉

VB:
    If bouton_verrouillage.Value = True Then
        Call verrouiller
    ElseIf bouton_verrouillage.Value = False Then
        If bouton_deverrouillage.Value = False Then
        bouton_deverrouillage.Value = True
        Else
        End If
    End If
doit pouvoir s'écrire comme ceci (et peut-être même plus simplement) :
VB:
    If bouton_verrouillage.Value Then
        Call verrouiller
    Else
        bouton_deverrouillage.Value = True
    End If



VB:
If ActiveSheet.Range("AQ23") <> "" Then
    ActiveSheet.Range("AQ23").ClearContents
Else
End If
peut s'écrire :
VB:
If ActiveSheet.Range("AQ23") <> "" Then ActiveSheet.Range("AQ23").ClearContents
et je dirais même simplement :
VB:
ActiveSheet.Range("AQ23").ClearContents



VB:
If ActiveSheet.Range("AQ20").Value <> "" Then
Else
    ActiveSheet.Range("AQ20").Value = "Modification de la feuille IMPOSSIBLE"
End If
peut s'écrire :
VB:
If ActiveSheet.Range("AQ20").Value = "" Then ActiveSheet.Range("AQ20").Value = "Modification de la feuille IMPOSSIBLE"


etc.
 
Dernière édition:
Je pense que si tu mettais des dates au lieu de mettre juste le quantième du mois (en C3:AG3, C24:AG24, etc.), ça simplifierait déjà pas mal ta formule matricielle de la mort qui tue à base de SommeProd, puisque tu pourrais probablement remplacer DATE(Saisir_Année;RECHERCHEV($B$2;correspondance_mois;2;FAUX);C$3) par C$3.
 
Dernière édition:
Je pense que si tu mettais des dates au lieu de mettre juste le quantième du mois (en C3:AG3, C24:AG24, etc.), ça simplifierait déjà pas mal ta formule matricielle de la mort qui tue à base de SommeProd, puisque tu pourrais probablement remplacer DATE(Saisir_Année;RECHERCHEV($B$2;correspondance_mois;2;FAUX);C$3) par C$3.
Toute les modifs sont faites, j'ai écrémé pas mal de MFC et corrigé tes suggestions sur mon VBA, en esperant que je n'ai pas crée de bug mais ca on verra avec l'utilisation ! merci bcp en tout cas !
 
Dans ce cas, désolé c'est beaucoup trop complexe pour mon neurone... 🙁


Les formules à rallonge, plus les 496 MFC de la feuille, ça fait un peu ramer Excel sur mon PC (qui a tout de même plus de 15 ans).
Salut l'ami TooFatBoy, bonjour Cattitude,
décomposée, la formule incriminée donne ceci :
VB:
=SI(Valeur1>0;INDEX($AS$31:$AV$244;SOMMEPROD(Valeur2*1*Valeur3*1*Valeur4)-30;4);SI(ESTERREUR(RECHERCHEV(Valeur0;jours_ferie;2;FAUX));SI(ESTVIDE(Valeur6);"";Valeur6);""))
avec
Valeur0 = DATE(Saisir_Année;RECHERCHEV($B$2;correspondance_mois;2;FAUX);C$3))
Valeur1 = SOMMEPROD(($AS$31:$AS$244<=Valeur2*1)*($AT$31:$AT$244>=Valeur2*1)*($AU$31:$AU$244=SI(ESTERREUR(RECHERCHEV(Valeur2;jours_ferie;2;FAUX));SI(ESTVIDE(Valeur8;SI(EST.IMPAIR(C$1);0;7);14;7);LIGNE($B5)-LIGNE($B$4);EQUIV(C$2;$C$245:$I$245;0);1));"";Valeur6);""))*(SI(OU($AW$31:$AW$244="matin";$AW$31:$AW$244="après midi");$AW$31:$AW$244=$B5;1)))
Valeur2 = $AS$31:$AS$244<=Valeur0
Valeur3 = $AT$31:$AT$244>=Valeur0
Valeur4 = ($AU$31:$AU$244=SI(ESTERREUR(Valeur7);SI(ESTVIDE(Valeur6);"";Valeur6);""))*LIGNE($AS$31:$AS$244)
Valeur5 =SI(format_planning="numéros de planning";$C$248;$Q$248)
Valeur6 =INDEX(DECALER(Valeur5;LIGNE(INDEX($A:$A;EQUIV(SI(C$19="";"normal";C$19);$A:$A;0);1))-LIGNE($C$248)+1;SI(EST.IMPAIR(C$1);0;7);14;7);LIGNE($B5)-LIGNE($B$4);EQUIV(C$2;$C$245:$I$245;0);1)
Valeur7 =RECHERCHEV(Valeur0;jours_ferie;2;FAUX)
Je vous laisse à tous les deux le soin de voir où ça pêche ! 😅
Cordialement,
 
décomposée, la formule incriminée donne ceci :
VB:
=SI(Valeur1>0;INDEX($AS$31:$AV$244;SOMMEPROD(Valeur2*1*Valeur3*1*Valeur4)-30;4);SI(ESTERREUR(RECHERCHEV(Valeur0;jours_ferie;2;FAUX));SI(ESTVIDE(Valeur6);"";Valeur6);""))

Je vous laisse à tous les deux le soin de voir où ça pêche ! 😅
Waouh !!! J'ai passé la nuit dessus mais ça a fait surchauffer mon neurone et du coup j'ai abandonné.

Je pensais que toi tu trouverais une solution en deux temps trois mouvements.
Mais c'est déjà énorme ce que tu as réussi à faire ! J'admire le travail !!! 🤩 😎
 
Salut l'ami TooFatBoy, bonjour Cattitude,
décomposée, la formule incriminée donne ceci :
VB:
=SI(Valeur1>0;INDEX($AS$31:$AV$244;SOMMEPROD(Valeur2*1*Valeur3*1*Valeur4)-30;4);SI(ESTERREUR(RECHERCHEV(Valeur0;jours_ferie;2;FAUX));SI(ESTVIDE(Valeur6);"";Valeur6);""))
avec
Valeur0 = DATE(Saisir_Année;RECHERCHEV($B$2;correspondance_mois;2;FAUX);C$3))
Valeur1 = SOMMEPROD(($AS$31:$AS$244<=Valeur2*1)*($AT$31:$AT$244>=Valeur2*1)*($AU$31:$AU$244=SI(ESTERREUR(RECHERCHEV(Valeur2;jours_ferie;2;FAUX));SI(ESTVIDE(Valeur8;SI(EST.IMPAIR(C$1);0;7);14;7);LIGNE($B5)-LIGNE($B$4);EQUIV(C$2;$C$245:$I$245;0);1));"";Valeur6);""))*(SI(OU($AW$31:$AW$244="matin";$AW$31:$AW$244="après midi");$AW$31:$AW$244=$B5;1)))
Valeur2 = $AS$31:$AS$244<=Valeur0
Valeur3 = $AT$31:$AT$244>=Valeur0
Valeur4 = ($AU$31:$AU$244=SI(ESTERREUR(Valeur7);SI(ESTVIDE(Valeur6);"";Valeur6);""))*LIGNE($AS$31:$AS$244)
Valeur5 =SI(format_planning="numéros de planning";$C$248;$Q$248)
Valeur6 =INDEX(DECALER(Valeur5;LIGNE(INDEX($A:$A;EQUIV(SI(C$19="";"normal";C$19);$A:$A;0);1))-LIGNE($C$248)+1;SI(EST.IMPAIR(C$1);0;7);14;7);LIGNE($B5)-LIGNE($B$4);EQUIV(C$2;$C$245:$I$245;0);1)
Valeur7 =RECHERCHEV(Valeur0;jours_ferie;2;FAUX)
Je vous laisse à tous les deux le soin de voir où ça pêche ! 😅
Cordialement,
C est génial merci beaucoup pour ton temps.
Ce que j ai pu repérer c est que le problème semble venir de la valeur1 et plus particulièrement de la fin avec les histoires de matin et après midi car à ce moment ça me renvoie une valeur6

Je m explique : tant que dans la colonne AW je n ai que des « journée » le code fonctionne très bien ( et cela doit valider la valeur 1 en « vraie »)
Dès que j ai un seul « matin » ou « après midi » dans la colonne AW, la valeur 1 semble passer en « fausse » et valider ainsi une Valeur6.
Et ce qui est étonnant c est que je peux avoir plusieurs cellule « validé en valeur1 » avec uniquement des « journée » en AW, si une seule donnée « matin » ou « après midi » apparaît en AW et la cellule concerné passé bien en valeur 1 sans soucis mais toute celle déjà en valeur 1 passent en valeur 6…
Je ne sais pas si j ai réussi à être clair 😅
 
Pas pour moi. Là encore ça a fait surchauffer mon neurone. 🤯

Mais elle est censé faire quoi ta formule ?
En fait si tu regarde mon fichier, la feuille fichier source sert de modèle pour créer chaque année des plannings pour mes collègues. Les plannings sont calé sur un roulement semaine paire/impaire qui se trouve tout en bas( planning « normal ») et se trouve en bas également les planning quand quelqu un prend des vacances et qui remplace qui. ( je passe tout le bazar du calcul des heures/jours/jours de congés des différents tableaux)
Sur la droite des mois/planning se trouve la colonne pour les formations et les abscences. (Les absences dont font fais partie la fameuse colonne AW)
Et donc sur un planning établi, je peux aller cliquer sur un des boutons « absence », renseigner la date de l abscence et si une collègue remplacera la personne manquante.
Mais lorsque j ai intégré à leur demande la possibilité d avoir des abscences uniquement matin ou aprem, j ai implémenté ma rotule matricielle déjà assez longue pour y ajouter cette fonction.
Qui fonctionne à condition de n avoir qu une seule abscnce d une demi journée pour toute et par an ( moi je m en fout mais elle font la troncbe 😅) ou que des abscence de journée entière !
Et j y avais ajouté des planning perso à imprimer et à exporter sur Google agenda mais ça pas de soucis ça fonctionne.
 
- Navigue sans publicité
- Accède à Cléa, notre assistante IA experte Excel... et pas que...
- Profite de fonctionnalités exclusives
Ton soutien permet à Excel Downloads de rester 100% gratuit et de continuer à rassembler les passionnés d'Excel.
Je deviens Supporter XLD

Discussions similaires

Réponses
2
Affichages
204
  • Question Question
Microsoft 365 Formule Outlook,
Réponses
8
Affichages
115
Réponses
2
Affichages
217
Réponses
12
Affichages
508
Réponses
15
Affichages
625
Retour