Remplacer formule trop longue par VBA

[gerard55]

Bonjour à tous,
J'ai une base de données qui indique le nombre de jours de congés (indiqués soit CP ou CS) en fonction des ateliers renseignés(en A3 en A4 ...) . Je souhaite connaître dans un calendrier sur une autre feuille le nombre de personnes absentes chaque jour en fonction des ateliers sélectionnés. Je le fais avec une formule mais, cette formule ne calcule que les CP . Excel sature déjà et je dois doubler la formule. Je ne vois qu'une solution c'est d'utiliser VBA mais je ne vois pas comment faire.
Merci pour votre aide
A+
Gérard

 

[ROGER2327]

Re : Remplacer formule trop longue par VBA

Bonjour à tous.

Très-amusant. J'ouvre et je trouve :​

=SI($A$3<>"";SOMMEPROD((DECALER('D:\Mes documents\Mes Docs\My eBooks\ESAT\chef d''atelier\2014\Gestion Cong OP\[BDCONG-2.xlsm]CongD'!$N$6;;EQUIV('D:\Mes documents\Mes Docs\My eBooks\ESAT\chef d''atelier\2014\Gestion Cong OP\[BDCONG-2.xlsm]Calen  atelier'!A10;'D:\Mes documents\Mes Docs\My eBooks\ESAT\chef d''atelier\2014\Gestion Cong OP\[BDCONG-2.xlsm]CongD'!$N$5:$NN$5;0)-1;145)="CP")*('D:\Mes documents\Mes Docs\My eBooks\ESAT\chef d''atelier\2014\Gestion Cong OP\[BDCONG-2.xlsm]Calen  atelier'!$A$3='D:\Mes documents\Mes Docs\My eBooks\ESAT\chef d''atelier\2014\Gestion Cong OP\[BDCONG-2.xlsm]CongD'!$D$6:$D$150));0)+SI($A$4<>"";SOMMEPROD((DECALER('D:\Mes documents\Mes Docs\My eBooks\ESAT\chef d''atelier\2014\Gestion Cong OP\[BDCONG-2.xlsm]CongD'!$N$6;;EQUIV('D:\Mes documents\Mes Docs\My eBooks\ESAT\chef d''atelier\2014\Gestion Cong OP\[BDCONG-2.xlsm]Calen  atelier'!A10;'D:\Mes documents\Mes Docs\My eBooks\ESAT\chef d''atelier\2014\Gestion Cong OP\[BDCONG-2.xlsm]CongD'!$N$5:$NN$5;0)-1;145)="CP")*('D:\Mes documents\Mes Docs\My eBooks\ESAT\chef d''atelier\2014\Gestion Cong OP\[BDCONG-2.xlsm]Calen  atelier'!$A$4='D:\Mes documents\Mes Docs\My eBooks\ESAT\chef d''atelier\2014\Gestion Cong OP\[BDCONG-2.xlsm]CongD'!$D$6:$D$150));0)+SI($D$3<>"";SOMMEPROD((DECALER('D:\Mes documents\Mes Docs\My eBooks\ESAT\chef d''atelier\2014\Gestion Cong OP\[BDCONG-2.xlsm]CongD'!$N$6;;EQUIV('D:\Mes documents\Mes Docs\My eBooks\ESAT\chef d''atelier\2014\Gestion Cong OP\[BDCONG-2.xlsm]Calen  atelier'!A10;'D:\Mes documents\Mes Docs\My eBooks\ESAT\chef d''atelier\2014\Gestion Cong OP\[BDCONG-2.xlsm]CongD'!$N$5:$NN$5;0)-1;145)="CP")*('D:\Mes documents\Mes Docs\My eBooks\ESAT\chef d''atelier\2014\Gestion Cong OP\[BDCONG-2.xlsm]Calen  atelier'!$D$3='D:\Mes documents\Mes Docs\My eBooks\ESAT\chef d''atelier\2014\Gestion Cong OP\[BDCONG-2.xlsm]CongD'!$D$6:$D$150));0)+SI($D$4<>"";SOMMEPROD((DECALER('D:\Mes documents\Mes Docs\My eBooks\ESAT\chef d''atelier\2014\Gestion Cong OP\[BDCONG-2.xlsm]CongD'!$N$6;;EQUIV('D:\Mes documents\Mes Docs\My eBooks\ESAT\chef d''atelier\2014\Gestion Cong OP\[BDCONG-2.xlsm]Calen  atelier'!A10;'D:\Mes documents\Mes Docs\My eBooks\ESAT\chef d''atelier\2014\Gestion Cong OP\[BDCONG-2.xlsm]CongD'!$N$5:$NN$5;0)-1;145)="CP")*('D:\Mes documents\Mes Docs\My eBooks\ESAT\chef d''atelier\2014\Gestion Cong OP\[BDCONG-2.xlsm]Calen  atelier'!$D$4='D:\Mes documents\Mes Docs\My eBooks\ESAT\chef d''atelier\2014\Gestion Cong OP\[BDCONG-2.xlsm]CongD'!$D$6:$D$150));0)+SI($I$3<>"";SOMMEPROD((DECALER('D:\Mes documents\Mes Docs\My eBooks\ESAT\chef d''atelier\2014\Gestion Cong OP\[BDCONG-2.xlsm]CongD'!$N$6;;EQUIV('D:\Mes documents\Mes Docs\My eBooks\ESAT\chef d''atelier\2014\Gestion Cong OP\[BDCONG-2.xlsm]Calen  atelier'!A10;'D:\Mes documents\Mes Docs\My eBooks\ESAT\chef d''atelier\2014\Gestion Cong OP\[BDCONG-2.xlsm]CongD'!$N$5:$NN$5;0)-1;145)="CP")*('D:\Mes documents\Mes Docs\My eBooks\ESAT\chef d''atelier\2014\Gestion Cong OP\[BDCONG-2.xlsm]Calen  atelier'!$I$3='D:\Mes documents\Mes Docs\My eBooks\ESAT\chef d''atelier\2014\Gestion Cong OP\[BDCONG-2.xlsm]CongD'!$D$6:$D$150));0)+SI($I$4<>"";SOMMEPROD((DECALER('D:\Mes documents\Mes Docs\My eBooks\ESAT\chef d''atelier\2014\Gestion Cong OP\[BDCONG-2.xlsm]CongD'!$N$6;;EQUIV('D:\Mes documents\Mes Docs\My eBooks\ESAT\chef d''atelier\2014\Gestion Cong OP\[BDCONG-2.xlsm]Calen  atelier'!A10;'D:\Mes documents\Mes Docs\My eBooks\ESAT\chef d''atelier\2014\Gestion Cong OP\[BDCONG-2.xlsm]CongD'!$N$5:$NN$5;0)-1;145)="CP")*('D:\Mes documents\Mes Docs\My eBooks\ESAT\chef d''atelier\2014\Gestion Cong OP\[BDCONG-2.xlsm]Calen  atelier'!$I$4='D:\Mes documents\Mes Docs\My eBooks\ESAT\chef d''atelier\2014\Gestion Cong OP\[BDCONG-2.xlsm]CongD'!$D$6:$D$150));0)+SI($N$3<>"";SOMMEPROD((DECALER('D:\Mes documents\Mes Docs\My eBooks\ESAT\chef d''atelier\2014\Gestion Cong OP\[BDCONG-2.xlsm]CongD'!$N$6;;EQUIV('D:\Mes documents\Mes Docs\My eBooks\ESAT\chef d''atelier\2014\Gestion Cong OP\[BDCONG-2.xlsm]Calen  atelier'!A10;'D:\Mes documents\Mes Docs\My eBooks\ESAT\chef d''atelier\2014\Gestion Cong OP\[BDCONG-2.xlsm]CongD'!$N$5:$NN$5;0)-1;145)="CP")*('D:\Mes documents\Mes Docs\My eBooks\ESAT\chef d''atelier\2014\Gestion Cong OP\[BDCONG-2.xlsm]Calen  atelier'!$N$3='D:\Mes documents\Mes Docs\My eBooks\ESAT\chef d''atelier\2014\Gestion Cong OP\[BDCONG-2.xlsm]CongD'!$D$6:$D$150));0)+SI($N$4<>"";SOMMEPROD((DECALER('D:\Mes documents\Mes Docs\My eBooks\ESAT\chef d''atelier\2014\Gestion Cong OP\[BDCONG-2.xlsm]CongD'!$N$6;;EQUIV('D:\Mes documents\Mes Docs\My eBooks\ESAT\chef d''atelier\2014\Gestion Cong OP\[BDCONG-2.xlsm]Calen  atelier'!A10;'D:\Mes documents\Mes Docs\My eBooks\ESAT\chef d''atelier\2014\Gestion Cong OP\[BDCONG-2.xlsm]CongD'!$N$5:$NN$5;0)-1;145)="CP")*('D:\Mes documents\Mes Docs\My eBooks\ESAT\chef d''atelier\2014\Gestion Cong OP\[BDCONG-2.xlsm]Calen  atelier'!$N$4='D:\Mes documents\Mes Docs\My eBooks\ESAT\chef d''atelier\2014\Gestion Cong OP\[BDCONG-2.xlsm]CongD'!$D$6:$D$150));0)+SI($N$5<>"";SOMMEPROD((DECALER('D:\Mes documents\Mes Docs\My eBooks\ESAT\chef d''atelier\2014\Gestion Cong OP\[BDCONG-2.xlsm]CongD'!$N$6;;EQUIV('D:\Mes documents\Mes Docs\My eBooks\ESAT\chef d''atelier\2014\Gestion Cong OP\[BDCONG-2.xlsm]Calen  atelier'!A10;'D:\Mes documents\Mes Docs\My eBooks\ESAT\chef d''atelier\2014\Gestion Cong OP\[BDCONG-2.xlsm]CongD'!$N$5:$NN$5;0)-1;145)="CP")*('D:\Mes documents\Mes Docs\My eBooks\ESAT\chef d''atelier\2014\Gestion Cong OP\[BDCONG-2.xlsm]Calen  atelier'!$N$5='D:\Mes documents\Mes Docs\My eBooks\ESAT\chef d''atelier\2014\Gestion Cong OP\[BDCONG-2.xlsm]CongD'!$D$6:$D$150));0)+SI($U$3<>"";SOMMEPROD((DECALER('D:\Mes documents\Mes Docs\My eBooks\ESAT\chef d''atelier\2014\Gestion Cong OP\[BDCONG-2.xlsm]CongD'!$N$6;;EQUIV('D:\Mes documents\Mes Docs\My eBooks\ESAT\chef d''atelier\2014\Gestion Cong OP\[BDCONG-2.xlsm]Calen  atelier'!A10;'D:\Mes documents\Mes Docs\My eBooks\ESAT\chef d''atelier\2014\Gestion Cong OP\[BDCONG-2.xlsm]CongD'!$N$5:$NN$5;0)-1;145)="CP")*('D:\Mes documents\Mes Docs\My eBooks\ESAT\chef d''atelier\2014\Gestion Cong OP\[BDCONG-2.xlsm]Calen  atelier'!$U$3='D:\Mes documents\Mes Docs\My eBooks\ESAT\chef d''atelier\2014\Gestion Cong OP\[BDCONG-2.xlsm]CongD'!$F$6:$F$150));0)+SI($U$4<>"";SOMMEPROD((DECALER('D:\Mes documents\Mes Docs\My eBooks\ESAT\chef d''atelier\2014\Gestion Cong OP\[BDCONG-2.xlsm]CongD'!$N$6;;EQUIV('D:\Mes documents\Mes Docs\My eBooks\ESAT\chef d''atelier\2014\Gestion Cong OP\[BDCONG-2.xlsm]Calen  atelier'!A10;'D:\Mes documents\Mes Docs\My eBooks\ESAT\chef d''atelier\2014\Gestion Cong OP\[BDCONG-2.xlsm]CongD'!$N$5:$NN$5;0)-1;145)="CP")*('D:\Mes documents\Mes Docs\My eBooks\ESAT\chef d''atelier\2014\Gestion Cong OP\[BDCONG-2.xlsm]Calen  atelier'!$U$4='D:\Mes documents\Mes Docs\My eBooks\ESAT\chef d''atelier\2014\Gestion Cong OP\[BDCONG-2.xlsm]CongD'!$F$6:$F$150));0)

Je serais normal, je refermerais. Mais je ne suis pas normal.
Je remplace​

'D:\Mes documents\Mes Docs\My eBooks\ESAT\chef d''atelier\2014\Gestion Cong OP\[BDCONG-2.xlsm]CongD'

par​

X

et​

'D:\Mes documents\Mes Docs\My eBooks\ESAT\chef d''atelier\2014\Gestion Cong OP\[BDCONG-2.xlsm]Calen  atelier'

par​

Y

et j'obtiens​

=SI($A$3<>"";SOMMEPROD((DECALER(X!$N$6;;EQUIV(Y!A10;X!$N$5:$NN$5;0)-1;145)="CP")*(Y!$A$3=X!$D$6:$D$150));0)+SI($A$4<>"";SOMMEPROD((DECALER(X!$N$6;;EQUIV(Y!A10;X!$N$5:$NN$5;0)-1;145)="CP")*(Y!$A$4=X!$D$6:$D$150));0)+SI($D$3<>"";SOMMEPROD((DECALER(X!$N$6;;EQUIV(Y!A10;X!$N$5:$NN$5;0)-1;145)="CP")*(Y!$D$3=X!$D$6:$D$150));0)+SI($D$4<>"";SOMMEPROD((DECALER(X!$N$6;;EQUIV(Y!A10;X!$N$5:$NN$5;0)-1;145)="CP")*(Y!$D$4=X!$D$6:$D$150));0)+SI($I$3<>"";SOMMEPROD((DECALER(X!$N$6;;EQUIV(Y!A10;X!$N$5:$NN$5;0)-1;145)="CP")*(Y!$I$3=X!$D$6:$D$150));0)+SI($I$4<>"";SOMMEPROD((DECALER(X!$N$6;;EQUIV(Y!A10;X!$N$5:$NN$5;0)-1;145)="CP")*(Y!$I$4=X!$D$6:$D$150));0)+SI($N$3<>"";SOMMEPROD((DECALER(X!$N$6;;EQUIV(Y!A10;X!$N$5:$NN$5;0)-1;145)="CP")*(Y!$N$3=X!$D$6:$D$150));0)+SI($N$4<>"";SOMMEPROD((DECALER(X!$N$6;;EQUIV(Y!A10;X!$N$5:$NN$5;0)-1;145)="CP")*(Y!$N$4=X!$D$6:$D$150));0)+SI($N$5<>"";SOMMEPROD((DECALER(X!$N$6;;EQUIV(Y!A10;X!$N$5:$NN$5;0)-1;145)="CP")*(Y!$N$5=X!$D$6:$D$150));0)+SI($U$3<>"";SOMMEPROD((DECALER(X!$N$6;;EQUIV(Y!A10;X!$N$5:$NN$5;0)-1;145)="CP")*(Y!$U$3=X!$F$6:$F$150));0)+SI($U$4<>"";SOMMEPROD((DECALER(X!$N$6;;EQUIV(Y!A10;X!$N$5:$NN$5;0)-1;145)="CP")*(Y!$U$4=X!$F$6:$F$150));0)

que je réécris​

=SI($A$3<>"";SOMMEPROD((DECALER(X!$N$6;;EQUIV(Y!A10;X!$N$5:$NN$5;0)-1;145)="CP")*(Y!$A$3=X!$D$6:$D$150));0)
+SI($A$4<>"";SOMMEPROD((DECALER(X!$N$6;;EQUIV(Y!A10;X!$N$5:$NN$5;0)-1;145)="CP")*(Y!$A$4=X!$D$6:$D$150));0)
+SI($D$3<>"";SOMMEPROD((DECALER(X!$N$6;;EQUIV(Y!A10;X!$N$5:$NN$5;0)-1;145)="CP")*(Y!$D$3=X!$D$6:$D$150));0)
+SI($D$4<>"";SOMMEPROD((DECALER(X!$N$6;;EQUIV(Y!A10;X!$N$5:$NN$5;0)-1;145)="CP")*(Y!$D$4=X!$D$6:$D$150));0)
+SI($I$3<>"";SOMMEPROD((DECALER(X!$N$6;;EQUIV(Y!A10;X!$N$5:$NN$5;0)-1;145)="CP")*(Y!$I$3=X!$D$6:$D$150));0)
+SI($I$4<>"";SOMMEPROD((DECALER(X!$N$6;;EQUIV(Y!A10;X!$N$5:$NN$5;0)-1;145)="CP")*(Y!$I$4=X!$D$6:$D$150));0)
+SI($N$3<>"";SOMMEPROD((DECALER(X!$N$6;;EQUIV(Y!A10;X!$N$5:$NN$5;0)-1;145)="CP")*(Y!$N$3=X!$D$6:$D$150));0)
+SI($N$4<>"";SOMMEPROD((DECALER(X!$N$6;;EQUIV(Y!A10;X!$N$5:$NN$5;0)-1;145)="CP")*(Y!$N$4=X!$D$6:$D$150));0)
+SI($N$5<>"";SOMMEPROD((DECALER(X!$N$6;;EQUIV(Y!A10;X!$N$5:$NN$5;0)-1;145)="CP")*(Y!$N$5=X!$D$6:$D$150));0)
+SI($U$3<>"";SOMMEPROD((DECALER(X!$N$6;;EQUIV(Y!A10;X!$N$5:$NN$5;0)-1;145)="CP")*(Y!$U$3=X!$F$6:$F$150));0)
+SI($U$4<>"";SOMMEPROD((DECALER(X!$N$6;;EQUIV(Y!A10;X!$N$5:$NN$5;0)-1;145)="CP")*(Y!$U$4=X!$F$6:$F$150));0)

et que je réécris​

=SI($A$3<>"";SOMMEPROD((DECALER(X!$N$6;;EQUIV(Y!A10;X!$N$5:$NN$5;0)-1;145)="CP")
                      *(Y!$A$3=X!$D$6:$D$150));0)
+SI($A$4<>"";SOMMEPROD((DECALER(X!$N$6;;EQUIV(Y!A10;X!$N$5:$NN$5;0)-1;145)="CP")
                      *(Y!$A$4=X!$D$6:$D$150));0)
+SI($D$3<>"";SOMMEPROD((DECALER(X!$N$6;;EQUIV(Y!A10;X!$N$5:$NN$5;0)-1;145)="CP")
                      *(Y!$D$3=X!$D$6:$D$150));0)
+SI($D$4<>"";SOMMEPROD((DECALER(X!$N$6;;EQUIV(Y!A10;X!$N$5:$NN$5;0)-1;145)="CP")
                      *(Y!$D$4=X!$D$6:$D$150));0)
+SI($I$3<>"";SOMMEPROD((DECALER(X!$N$6;;EQUIV(Y!A10;X!$N$5:$NN$5;0)-1;145)="CP")
                      *(Y!$I$3=X!$D$6:$D$150));0)
+SI($I$4<>"";SOMMEPROD((DECALER(X!$N$6;;EQUIV(Y!A10;X!$N$5:$NN$5;0)-1;145)="CP")
                      *(Y!$I$4=X!$D$6:$D$150));0)
+SI($N$3<>"";SOMMEPROD((DECALER(X!$N$6;;EQUIV(Y!A10;X!$N$5:$NN$5;0)-1;145)="CP")
                      *(Y!$N$3=X!$D$6:$D$150));0)
+SI($N$4<>"";SOMMEPROD((DECALER(X!$N$6;;EQUIV(Y!A10;X!$N$5:$NN$5;0)-1;145)="CP")
                      *(Y!$N$4=X!$D$6:$D$150));0)
+SI($N$5<>"";SOMMEPROD((DECALER(X!$N$6;;EQUIV(Y!A10;X!$N$5:$NN$5;0)-1;145)="CP")
                      *(Y!$N$5=X!$D$6:$D$150));0)
+SI($U$3<>"";SOMMEPROD((DECALER(X!$N$6;;EQUIV(Y!A10;X!$N$5:$NN$5;0)-1;145)="CP")
                      *(Y!$U$3=X!$F$6:$F$150));0)
+SI($U$4<>"";SOMMEPROD((DECALER(X!$N$6;;EQUIV(Y!A10;X!$N$5:$NN$5;0)-1;145)="CP")
                      *(Y!$U$4=X!$F$6:$F$150));0)

et je commence à y voir quelque chose.

Maintenant je suis fatigué. Je vais me coucher.​

Bonne nuit !


ℝOGER2327
#7310

Dimanche 15 Palotin 141 (Adoration du Pal - fête Suprême Tierce)
15 Floréal An CCXXII, 4,3131h - ver-à-soie
2014-W18-7T10:21:05Z

 

[Robert]

Re : Remplacer formule trop longue par VBA

Bonjour le fil, bonjour le forum,

J'aime la folie de Roger ! Bonne nuit à toi...

 

[gerard55]

Re : Remplacer formule trop longue par VBA

Bonjour ROGER 2327. Merci pour ce travail. Je salue ton courage et ta ténacité. Je suis désolé pour ce fichier qui n'était pas claire. Les formules avaient gardé leurs adresses d'origine. Je ne connaissais pas cette manière de simplifier les formules.
Cependant, je vais rencontrer le même problème car ces formules, je dois les dupliquer sur les 365 jours du calendrier et je dois les doubler pour qu'il prenne aussi en compte les CS. Excel va saturer. C'est pourquoi, je pense qu'en VBA ce serait plus simple.
A+
Gérard