[résolu] remplacer SOMMEPROD par une macro

[matt31]

Bonjour,

ne trouvant pas ce que je souhaite dans les précédents posts du forum, j'essaie d'obtenir une aide plus précise.

J'ai mis en place une fonction SOMMEPROD d'après des données provenant d'un autre classeur. Elle fonctionne sans problème.
Par contre, cela renvoie à chaque semaine de l'année et donc à un fichier différent pour chaque ligne de mon classeur (1 ligne par semaine renvoie à un fichier par semaine).
Je voudrais préparer toutes les lignes à l'avance mais la fonction me renvoie #DIV/0! car les fichiers semaine ne sont pas encore remplis jusqu'à la fin de l'année et du coup, je ne peux pas utiliser le résultat final (moyenne) faussé par cet affichage.

Je pensais donc agir différemment en mettant en place un code VBA pour remplacer cette fonction SOMMEPROD.
J'utilise actuellement ceci :
(SOMMEPROD(([EM 60 - S 32.xls]Séjours à coder'!$X$3:$X$55="codé sans CRH")*([EM 60 - S 32.xls]Séjours à coder'!$L$3:$L$55="V"))+SOMMEPROD(([EM 60 - S 32.xls]Séjours à coder'!$X$3:$X$55="codé sans actes")*([EM 60 - S 32.xls]Séjours à coder'!$L$3:$L$55="V"))+SOMMEPROD(([EM 60 - S 32.xls]Séjours à coder'!$X$3:$X$55="codé sans CRH,ni actes")*([EM 60 - S 32.xls]Séjours à coder'!$L$3:$L$55="V")))/([EM 60 - S 32.xls]statistiques'!$H$16)
Le calcul étant très long, je ne peux rajouter SI(ESTERREUR... pour enlever ce #DIV/0!

Je n'arrive pas à créer ce code VBA que j'insèrerai dans une macro qui en fonction de la semaine indiquée dans une autre cellule, donnera le chemin d'accès aux données. Cette dernière partie est ok.

Quelqu'un peut-il m'aider à créer ce code en VBA?
Merci par avance?


Comment

 

[Misange]

Re : remplacer SOMMEPROD par une macro

Bonjour
Tu as déjà 167 messages sur ton compteur, et tu n'a pas encore pris je pense le temps de lire la charte... Dommage tu y aurais lu qu'un petit classeur exemple résumant ton problème est d'une grande aide pour ceux qui veulent bien t'aider à le résoudre.

dans le cas présent, je ne vois pas pourquoi tu prépares à l'avance des lignes vides pour tes futures données vu que tu as excel 2010. Utilise la fonctionnalité tableau (onglet accueil/style/mettre sous forme de tableau), elle est exactement faite pour résoudre ce genre de pb
voir là
Ce lien n'existe plus

 

[matt31]

Re : remplacer SOMMEPROD par une macro

merci pour le conseil.
J'ai bien lu la charte et le problème est que tous mes fichiers sont très volumineux et avec de multiples liens. Je suis donc en train d'essayer de simplifier au maximum.
Merci pour le lien mais cela ne modifie pas les données à l'intérieur de ma fonction.

Sur mon fichier "calcul exhaustivité..." je rentre l'EM en C et la semaine en D. Le chemin vers le fichier se crée ainsi et cela remplie automatiquement E et F.
Pour la colonne G, je pensais mettre une fonction SOMMEPROD décrite ci-dessus mais je préfèrerais le faire en VBA et si possible l'intégrer à la macro du fichier pour que cela se remplisse au fur et à mesure.
Le fichier "EM..." est une extrait (sans les autres feuilles et sans les macros) du type de fichier sur lequel je vais chercher mes renseignements. Je n'ai laissé que les données principales.
Encore merci

 

[hbenalia]

Re : remplacer SOMMEPROD par une macro

Bonjour à tous,

Une seule formule ne ralentirait pas le calcul, il se peut qu'il y ait d'autres formules ou MFC qui sont en cause de ce ralentissement... Le seul problème que présente la formule c'est qu'elle est un peu longue mais qui peut être simplifiée en:

=SOMMEPROD((('[EM 60 - S 32.xls]Séjours à coder'!$X$3:$X$55="codé sans CRH")+('[EM 60 - S 32.xls]Séjours à coder'!$X$3:$X$55="codé sans actes")+('[EM 60 - S 32.xls]Séjours à coder'!$X$3:$X$55="codé sans CRH,ni actes"))*('[EM 60 - S 32.xls]Séjours à coder'!$L$3:$L$55="V"))/'[EM 60 - S 32.xls]statistiques'!$H$16

ou si on nomme la petite partie '[EM 60 - S 32.xls]Séjours à coder'!$X$3:$X$55 de cette formule par Plage (par exemple) avec la formule :

='[EM 60 - S 32.xls]Séjours à coder'!$X$3:$X$55

la formule précédente serait (plus simple) sous la forme:

=SOMMEPROD(((Plage="codé sans CRH")+(Plage="codé sans actes")+(Plage="codé sans CRH,ni actes"))*(DECALER(Plage;;-12)="V"))/'[EM 60 - S 32.xls]statistiques'!$H$16

ou encore plus simple:

=SOMMEPROD((Plage=TRANSPOSE({"codé sans CRH";"codé sans actes";"codé sans CRH,ni actes"}))*(DECALER(Plage;;-12)="V"))/'[EM 60 - S 32.xls]statistiques'!$H$16

Cordialement

 

[Misange]

Re : remplacer SOMMEPROD par une macro

Il n'est pas nécessaire de mettre des fichiers complets très lourds pour illustrer une question : mieux vaut refaire à partir de rien un petit exemple qui ILLUSTRE la question, permet de bien comprendre comment ça marche de façon à être capable ensuite de refaire soi même sur le vrai fichier .

Bravo à hbenali qui a eu le courage de décortiquer des formules du genre

=(SOMMEPROD(('\\Srv-dim33\timcodage\Suivi et Evaluation des activités TIM\S. et E. Activités Professionnalisées\2012\Neuro Sciences\EM 60\[EM 60 - S 16.xls]Séjours à coder'!$X$3:$X$55="codé sans CRH")*('\\Srv-dim33\timcodage\Suivi et Evaluation des activités TIM\S. et E. Activités Professionnalisées\2012\Neuro Sciences\EM 60\[EM 60 - S 16.xls]Séjours à coder'!$L$3:$L$55="V"))+SOMMEPROD(('\\Srv-dim33\timcodage\Suivi et Evaluation des activités TIM\S. et E. Activités Professionnalisées\2012\Neuro Sciences\EM 60\[EM 60 - S 16.xls]Séjours à coder'!$X$3:$X$55="codé sans actes")*('\\Srv-dim33\timcodage\Suivi et Evaluation des activités TIM\S. et E. Activités Professionnalisées\2012\Neuro Sciences\EM 60\[EM 60 - S 16.xls]Séjours à coder'!$L$3:$L$55="V"))+SOMMEPROD(('\\Srv-dim33\timcodage\Suivi et Evaluation des activités TIM\S. et E. Activités Professionnalisées\2012\Neuro Sciences\EM 60\[EM 60 - S 16.xls]Séjours à coder'!$X$3:$X$55="codé sans CRH,ni actes")*('\\Srv-dim33\timcodage\Suivi et Evaluation des activités TIM\S. et E. Activités Professionnalisées\2012\Neuro Sciences\EM 60\[EM 60 - S 16.xls]Séjours à coder'!$L$3:$L$55="V")))/('\\Srv-dim33\timcodage\Suivi et Evaluation des activités TIM\S. et E. Activités Professionnalisées\2012\Neuro Sciences\EM 60\[EM 60 - S 16.xls]statistiques'!$H$16)

Ne pouvant pas tester les résultats sans devoir modifier tous les chemins.... je lui laisse (et j'applaudis !)

Pour en revenir aux tableaux : si sur tes fichiers EM60... tu mets tes données sous forme de tableau, cela crée automatiquement des noms définis dynamiques assez simples à manipuler dans des somme prod et autre, plus parlants que
[EM 60 - S 16.xls]Séjours à coder'!$L$3:$L$55
puisque le nom d'un tableau est défini au niveau du classeur et non de la feuille
cela deviendrait par exemple
EM60S11[validation] (en nommant le tableau EM60S11)
et si il n'y a que 32 lignes cette semaine tu ne travaillerais que sur 32 et sur 110 si il y a eu une grosse activité la semaine suivante...

 

[matt31]

Re : remplacer SOMMEPROD par une macro

Déjà merci à vous 2.
Je me suis apparemment mal exprimé et vais tenter de reformuler.
Mon problème n'est pas un temps de calcul de la formule qui est trop long.

Quelques explications.
J'ai 132 EM. Pour chaque EM j'ai un répertoire qui contient 52 fichiers hebdomadaire intitulé "EM (n° de l'EM) - S 01" et ainsi jusqu'à 52.
Tous ces fichiers hebdomadaires sont identiques. J'ai une macro qui me crée automatique 52 fichiers intitulés comme expliqué ci-dessus d'après le n° d'EM indiqué en A1 de la 1ère feuille de mon modèle. Difficile dans ces conditions de modifier tous les fichiers en créant des tableaux et en les nommant. D'autres part, est-ce que les tableaux ne vont se nommer tous de la même manière?

J'ai maintenant un fichier "calcul d'exhaustivité..." qui me permet en entrant l'EM et la semaine de récupérer automatiquement des valeurs d'une page du fichier hebdomadaire correspondant à la semaine et à l'EM renseignée. Le chemin est créé dans la macro.
En plus de cela, je voudrais que le calcul fait avec ma formule SOMMEPROD soit fait automatiquement et donc insérer dans la macro pour qu'il corresponde à la semaine et l'EM renseignée.

J'espère avoir été plus clair.
Sinon, les solutions proposées fonctionnent mais ne sont pas applicables à mon problème. Je ne pourrais pas renommer plus de 6800 fichiers et il faudra de toute manière adapter la formule en fonction de l'EM et la semaine voulue. Ce que je souhaite absolument éviter.

Merci encore pour l'aide.

 

[matt31]

Re : remplacer SOMMEPROD par une macro

j'essaie de faire remonter au cas où quelqu'un pourrait m'aider...
Merci par avance

 

[Misange]

Re : remplacer SOMMEPROD par une macro

Le plus simple je pense :
pars d'un fichier plus simple (les données dans le même classeur que la formule)
écris ta formule somme prod (regarde la simplification proposée par hbenalia... si ça marche comme tu veux sa formule est quand même autrement plus courte)
copie la formule
lance l'enregistreur de macro
colle la formule dans la cellule
arrête l'enregistreur et regarde le code.
Rejoue la séquence et vérifie que tu récupères bien le bon résultat.
Ensuite seulement tu remplaces petit à petit les différents éléments de ta formule dans la macro par des variables qui vont représenter le nom du classeur, la semaine et tout et tou. teste au fur et à mesure. Plus le nom de tes classeurs, onglets... contient d'espaces, est long plus tu as de chances de faire des erreur en jouant avec les & et les "".

 

[matt31]

Re : remplacer SOMMEPROD par une macro

Merci,

j'ai bien essayé avec l'enregistreur de macro mais j'ai des difficultés à l'intégrer dans la macro initiale.

Concernant le nom des fichiers, malheureusement cela ne dépend pas de moi et je ne pourrai rien y faire. Il y a des choses imposées par des personnes qui ne travaille pas ensuite sur ces fichiers.

Je vais essayer de recommencer...

 

[Misange]

Re : remplacer SOMMEPROD par une macro

Tu te lances dans un truc franchement pas simple.
Je t'ai fait un tout petit classeur exemple pour te monter comment je procede dans ce genre de cas pour progresser par étape et garder la trace de mes essais. Avec les noms de réseau de classeur de feuilles que tu as, tu n'es à mon avis pas au bout de tes peines. Plus simple de rester avec des formules. A toi de voir.

 

[matt31]

Re : remplacer SOMMEPROD par une macro

je te remercie.
J'y suis arrivé même s'il a fallu batailler un peu. Le fichier est en pièce jointe.

P.S. : je ne choisis pas non plus les couleurs... (pour ça j'ai malheureusement une chef...)

 

[Misange]

Re : [résolu] remplacer SOMMEPROD par une macro

Bravo et bonne continuation à Toulouse