Retranscrire formules en VBA
Bonjour à tous,
Je viens de découvrir votre forum en faisant des recherches pour mettre au point le tableur que je vais vous présenter.
Mon besoin est le suivant : J'ai un listing de professionnels où chaque ligne correspond à une action que réalise le professionnel (Mise au norme, chaudière électrique,...) avec des informations sur le professionnel (date d'inscription, département).
Dans un second tableau, je souhaiterai connaître la répartition des mes professionnels par action et par département.
J'ai réussi à obtenir ce que je voulais en :
- en calculant la pondération de chaque action avec 1/NB.SI.ENS(...) dans mon premier tableau
- avec un sommeprod dans mon second tableau
Le soucis est qu'avec 95 départements, 150 actions et 10 000 professionnels mon tableau est très lourd à calculer (>15 minutes).
Est-il possible de transcrire le fonctionnement de mon tableau et des mes formules en VBA ? Si oui, vais-je gagner en optimisation et en rapidité de traitement et comment m'y prendre ?
En bonus, dans l'onglet couverture, je souhaiterai résumer la couverture des départements par action (afficher les départements où l'on à plus d'1 professionnel)
D'avance merci pour votre temps et votre aide,
bonjour,
Comme ça pour faire avancer le schmilblick, je dirai que plus rapide que les SOMMEPROD et autre **.ENS... :
YAKE les fonctions de Base de Données qui allient rapidité et souplesse.
Dans les cas les plus extrêmes les fonctions de Base de Données sont décisives !
Inconvénient : Faut pas être manchot ! C'est pour ceux qui veulent pas se casser le bonnet que Crosoft à pondu des fonctions comme SOMMEPROD et **.ENS... qui font perdre dans la majorité des cas un peu de temps, mébon... Comme ça les fatigue pas trop d'aller boire un café, la plupart des utilisateurs s'en contentent...
Après, VBA... je ne sais pas, je n'ai pas évalué : Cet exemple est trop ridicule ! Peut-être qu'une combinaison fonctions de Base de Données + Dictionnary serait pas mal...
A+
Il n'est donc pas certain que le VBA soit plus rapide que les fonctions standards ?
J'ai regardé du côté des fonctions de Base de Données, mais par exemple BDLIRE ne permet pas plusieurs résultats.
Quelqu'un aurait une piste pour commencer à retranscrire en VBA ?
Merci Jean-Eric,
c'est vraiment pas mal, mais comment ne pas prendre en compte les doublons ?
Je m'explique :
Pour les travaux "Génériques" je n'ai bien que 2 professionnels dans le 44 (n°1 et n°7)
Hors le TdC m'en indique 3 ! (Je ne peux pas utiliser PowerPivot)
J'y arrive en ajoutant une colonne de pondération en F:F
=SIERREUR((1/NB.SI.ENS(E:E;E2;A:A;A2;C:C;C2));"")Mais j'imagine qu'il existe une solution plus "propre" directement dans le TdC ?
Re,
Il faut penser à actualiser le TCD après modifications dans le tableau.
Cdlt.
bonjour à tous
c'est cette colonne "de pondération" qui intrigue
- à quoi sert-elle (je ne comprends pas la formule
) ? - pourquoi n'a-t-elle pas de titre dans le fichier d'origine ?
- est-elle la cause de la lenteur ?
si on peut la virer, alors les TCD devraient aller super vite
Bonjour,
A-priori c'est ton =(1/NB.SI.ENS(...) qui a un soucis.
Si on mesure le temps de calcul sur tes 9 lignes on n'a que 0.005 s, mais après un temps de latence de 4-5 s où je ne sais pas ce qu'excel peut bien bricoler (?)
Réduire la plage au nécessaire n'y change rien.
Je te propose :
- 1 colonne intermédiaire en F avec : =A2&C2&E2
- en G : =1/NB.SI($F$2:$F$10;F2) à la place de ton nb.si.ens()
Par curiosité, l'exemple tu l'as fait dans un classeur neuf ou à partir du réel ?
Si c'est à partir d'une copie essayer de le recréer dans un classeur neuf car la latence pour 9 lignes est quand même 'étrange'. Par sécurité autant ne pas se balader des trucs zarbi qui te causeront de plus en plus de soucis au fil du temps.
eric
edit : fausse alerte, c'est le recalcul des sommeprod dépendents la cause de la latence.
Un exemple en TCD
Edit : fausse alerte, en fait ce sont tes sommeprod dépendants derrière qui sont la cause de la latence le temps qu'il les ré-évalue
Merci à tous pour vos réponses,
jmd a écrit :c'est cette colonne "de pondération" qui intrigue
-à quoi sert-elle (je ne comprends pas la formule
) ? si on peut la virer, alors les TCD devraient aller super vite
Elle me sert à retirer les doublons : un pro peut être listé plusieurs fois pour un même type de travaux et je veux donc qu'il ne compte que pour 1.
eriiic a écrit :Par curiosité, l'exemple tu l'as fait dans un classeur neuf ou à partir du réel ?
Si c'est à partir d'une copie essayer de le recréer dans un classeur neuf car la latence pour 9 lignes est quand même 'étrange'. Par sécurité autant ne pas se balader des trucs zarbi qui te causeront de plus en plus de soucis au fil du temps.
L'exemple vient en effet du classeur réel ce qui peut expliquer une latence résiduelle.
Je suis plutôt à l'aise avec la solution de Jean-Eric et le TdC (avec la formule de pondération), cependant ma demande initiale se complexifie
La solution est parfaite pour connaître la répartition totale à un instant T, mais à l'usage et lorsque j'ajouterai de nouveaux professionnels j'aimerai pouvoir visualiser rapidement les départements où nous avons de nouveaux pros depuis une certaine date.
Je ne sais pas comment le représenter dans le TdC (Filtre ?)
Par exemple, en entrant une date de mise à jour au 01/07/2017, le rendu visuel ci-dessous permettrait de m'alerter que pour le type de travaux "générique" j'ai de nouveaux pros sur le 47,48,49. Le dpt 44 n'est pas en vert car j'avais déjà un pro avant le 01/07.
Un filtre pourrait aussi faire l'affaire afin d'afficher seulement les départements où je n'avais pas de pros avant la date de MAJ et où j'ai à présent au moins 1 pro.
Je ne sais pas si l'expression de ma demande est très clair ?
20syl a écrit :Merci à tous pour vos réponses,
jmd a écrit :c'est cette colonne "de pondération" qui intrigue
-à quoi sert-elle (je ne comprends pas la formule
) ? si on peut la virer, alors les TCD devraient aller super vite
Elle me sert à retirer les doublons : un pro peut être listé plusieurs fois pour un même type de travaux et je veux donc qu'il ne compte que pour 1.
c'est donc une synthèse sur le tableau (prise en compte de TOUTES les colonnes en une seule valeur). Les synthèses sont de préférence (groooooosse préférence
autre exemple de synthèses : sommes, moyennes, mini, maxi, écart type. Tu vois le topo.
Ok, je n'avais pas pensé à regarder du côté de la synthèse des valeurs mais comment fais-tu pour prendre en compte toutes les colonnes en une seule valeur dans mon cas ?