Amélioration d'une boucle - Avis et possibilités

Le 02/08/2021 à 14:00

Bonjour à tous,

Je suis nouveau sur le forum.

Pour mettre le cadre : je suis un utilisateur de VBA depuis quelques années, je me considère comme un bon bricoleur du dimanche. Concrètement, j'arrive souvent à mes fins avec l'enregistreur de macro et les précieux posts de ce forum et des autres. En d'autres termes : mes codes fonctionnent (tant bien que mal) mais il y a peut-être des choses qui vous feront bondir... et je m'en excuse d'avance :) L'objectif de ce post est justement de peut-être gommer les énormités que j'ai pu écrire pour améliorer le rendu.

Seulement voilà, mes petites compétences sont désormais insuffisantes (ainsi que le temps que j'ai à allouer pour progresser seul à ce niveau).

Dans un cadre professionnel, j'ai monté un tableau de bord. Le tableau de synthèse final a nécessité une simple boucle, mais sur une centaine de ligne et un trentaine de colonne.

J'aurai voulu fluidifier cette macro pour en améliorer l'utilisation. Pour le moment elle n'est lancée qu'en cliquant sur un bouton, j'aurai voulu qu'elle se relance quand j'actualise l'un ou l'autre des paramètres, seulement, avec son temps d'exécution je n'y pense pas pour le moment !!

Concernant le fichier :

La boucle en question est contenu dans la feuille "MENU", "Private Sub CommandButton3_Click()". Elle se lance à partir de la feuille "MENU", en cliquant sur le bouton : "Consulter l'état de synthèse". Avec l'anonymisation des données, j'ai supprimé par mal de lignes et/ou données confidentielles donc cela semble s'alimenter assez vite (5 secondes) car le fichier moins chargé peut-être (j'ai du le réduire de 10mo à 1 pour le transmettre ici.

27amelioration-boucle.zip (695.79 Ko)

J'ai gardé un même volume de lignes et de colonnes dans le principe.
Malheureusement, dans la réalité c'est autour de 20 secondes de traitement.

Merci d'avance pour votre aide et très bonne journée,

Charles

Le 06/08/2021 à 01:32

Salut Charles,

J'ai déjà adapté une partie de ta procédure mais, n'étant pas fortiche en formules et même si j'en devine le contenu, leur complexité me fait hésiter.
Comme je le saisis, tu veux pour chaque tracteur le total de chaque section entre les deux dates [F16:G16] ?
Pourquoi ne traites-tu pas cela en 'Synthèse' sans doublons de tracteurs ?

Tu me confirmes ? ...comprends pas grand'chose avec certitude là-dedans et les suivantes...

        .Range("B" & i) = WorksheetFunction.SumIfs(Sheets("CA").Range("T:T"), Sheets("CA").Range("R:R"), .Range("A" & i), Sheets("CA").Range("D:D"), "<=" & Sheets("MENU").Range("G16") * 1, Sheets("CA").Range("D:D"), ">=" & Sheets("MENU").Range("F16") * 1) + Application.WorksheetFunction.SumIfs(Sheets("CA").Range("T:T"), Sheets("CA").Range("R:R"), Application.VLookup(.Range("A" & i), Sheets("paire").Range("F:G"), 2, False), Sheets("CA").Range("D:D"), "<=" & Sheets("MENU").Range("G16") * 1, Sheets("CA").Range("D:D"), ">=" & Sheets("MENU").Range("F16") * 1)
        .Range("C" & i) = WorksheetFunction.SumIfs(Sheets("ent").Range("G:G"), Sheets("ent").Range("C:C"), .Range("A" & i), Sheets("ent").Range("D:D"), "<=" & Sheets("MENU").Range("G16") * 1, Sheets("ent").Range("D:D"), ">=" & Sheets("MENU").Range("F16") * 1) + Application.WorksheetFunction.SumIfs(Sheets("ent").Range("G:G"), Sheets("ent").Range("C:C"), Application.VLookup(.Range("A" & i), Sheets("paire").Range("F:G"), 2, False), Sheets("ent").Range("D:D"), "<=" & Sheets("MENU").Range("G16") * 1, Sheets("ent").Range("D:D"), ">=" & Sheets("MENU").Range("F16") * 1)
        .Range("D" & i) = WorksheetFunction.SumIfs(Sheets("conso").Range("J:J"), Sheets("conso").Range("E:E"), .Range("A" & i), Sheets("conso").Range("D:D"), "<=" & Sheets("MENU").Range("G16") * 1, Sheets("conso").Range("D:D"), ">=" & Sheets("MENU").Range("F16") * 1) * Sheets("MENU").Range("K12") + Application.WorksheetFunction.SumIfs(Sheets("conso").Range("J:J"), Sheets("conso").Range("E:E"), Application.VLookup(.Range("A" & i), Sheets("paire").Range("F:G"), 2, False), Sheets("conso").Range("D:D"), "<=" & Sheets("MENU").Range("G16") * 1, Sheets("conso").Range("D:D"), ">=" & Sheets("MENU").Range("F16") * 1) * Sheets("MENU").Range("K12")
        .Range("E" & i) = WorksheetFunction.SumIfs(Sheets("km").Range("V:V"), Sheets("km").Range("F:F"), .Range("A" & i), Sheets("km").Range("Q:Q"), "<=" & Sheets("MENU").Range("G16") * 1, Sheets("km").Range("Q:Q"), ">=" & Sheets("MENU").Range("F16") * 1) * Sheets("MS").Range("C9")
        If IsError(Application.VLookup(.Range("A" & i), Sheets("loyer").Range("B:H"), 4, False) * CUMUL / Application.VLookup(.Range("A" & i), Sheets("loyer").Range("B:P"), 15, False)) Then
            .Range("F" & i) = Application.VLookup(Application.VLookup(.Range("A" & i), Sheets("paire").Range("F:G"), 2, False), Sheets("loyer").Range("B:H"), 4, False) * CUMUL / Application.VLookup(Application.VLookup(.Range("A" & i), Sheets("paire").Range("F:G"), 2, False), Sheets("loyer").Range("B:P"), 15, False)
        Else
            If IsError(Application.VLookup(Application.VLookup(.Range("A" & i), Sheets("paire").Range("F:G"), 2, False), Sheets("loyer").Range("B:H"), 4, False) * CUMUL / Application.VLookup(Application.VLookup(.Range("A" & i), Sheets("paire").Range("F:G"), 2, False), Sheets("loyer").Range("B:P"), 15, False)) Then
                .Range("F" & i) = Application.VLookup(.Range("A" & i), Sheets("loyer").Range("B:H"), 4, False) * CUMUL / Application.VLookup(.Range("A" & i), Sheets("loyer").Range("B:P"), 15, False)
            Else
                .Range("F" & i) = Application.VLookup(.Range("A" & i), Sheets("loyer").Range("B:H"), 4, False) * CUMUL / Application.VLookup(.Range("A" & i), Sheets("loyer").Range("B:P"), 15, False) + Application.VLookup(Application.VLookup(.Range("A" & i), Sheets("paire").Range("F:G"), 2, False), Sheets("loyer").Range("B:H"), 4, False) * CUMUL / Application.VLookup(Application.VLookup(.Range("A" & i), Sheets("paire").Range("F:G"), 2, False), Sheets("loyer").Range("B:P"), 15, False)
            End If
        End If


A+

Le 06/08/2021 à 09:40

Bonjour Curulis57 !

Avant toute chose : un grand merci pour ton retour et le temps que tu as passé là-dessus :)

L'amélioration du code fonctionne, je te le confirme, mais la performance que j'attend (c'est peut-être idyllique et inatteignable, les experts me diront !) ne l'est pas : c'est encore trop long au calcul.

Oui tu as bien compris : dans la feuille "MENU" j'indique bien le bornage de date à appliquer. L'objectif est de pouvoir regarder les variations, d'une période à l'autre. Du coup je suis amené à souvent bouger ce bornage, et donc relancer le calcul qui est (trop) long.

Je ne comprend pas la question suivante : "Pourquoi ne traites-tu pas cela en 'Synthèse' sans doublons de tracteurs ?". Enfin, je suis pas sûr de bien l'interpréter.

La liste de tracteur qui arrive en feuille "synthèse" est déjà unique (traitement réalisé par ailleurs). Maintenant je comprend la question ainsi : pourquoi je ne met pas le code directement dans la feuille synthèse ?

Techniquement, rien ne s'y oppose en effet, mais si mes bornes de dates sont sur la feuilles "MENU" ça complique la chose. Je veux bien essayer cela, mais il faudrait que la modification des dates soit accessible du Userform (parce que je vois pas où autrement, la feuille est pleine de données).

Penses-tu qu'en termes d'efficacité ce montage est souhaitable ? Des Textbox sur l'UF (qui au pire alimente ma feuille "MENU") ? Et je rajoute un bouton pour lancer le calcul sur l'UF également ?

Merci pour ton aide,

Un bon vendredi à tous :)

Charles

Le 06/08/2021 à 10:19

Salut Charles,

ce n'est pas tout à fait cela ma question: y a-t-il, dans ta synthèse d'autres conditions à respecter que le bornage [F16-G16] ?
Les codes envoyés étaient à toi : je te demandais de me confirmer cette question : il n'y a encore rien d'optimisé là-dessus.

Quant aux doublons, ce sont les n° d'immatriculation mais cela est sans doute dû à tes manipulations d'anonymisation.
Il y a bien 17.000 fois AAA-111-AA !

+

Le 06/08/2021 à 10:26

Bonjour Charles222, Salut Curulis57,

Pour un meilleur temps de calcul, il faut selon moi :

- borner le code par ces 2 lignes :

application.calculation = xlcalculationmanual
'code
application.calculation = xlcalculationautomatic

- utiliser un tableau dynamique afin de restituer les résultats en une seule fois :

t(i, 1) = WorksheetFunction.SumIfs(Sheets("CA").Range("T:T"), Sheets("CA").Range("R:R"), .Range("A" & i), Sheets("CA").Range("D:D"), "<=" & Sheets("MENU").Range("G16") * 1, Sheets("CA").Range("D:D"), ">=" & Sheets("MENU").Range("F16") * 1) + Application.WorksheetFunction.SumIfs(Sheets("CA").Range("T:T"), Sheets("CA").Range("R:R"), Application.VLookup(.Range("A" & i), Sheets("paire").Range("F:G"), 2, False), Sheets("CA").Range("D:D"), "<=" & Sheets("MENU").Range("G16") * 1, Sheets("CA").Range("D:D"), ">=" & Sheets("MENU").Range("F16") * 1)
t(i, 2) = WorksheetFunction.SumIfs(Sheets("ent").Range("G:G"), Sheets("ent").Range("C:C"), .Range("A" & i), Sheets("ent").Range("D:D"), "<=" & Sheets("MENU").Range("G16") * 1, Sheets("ent").Range("D:D"), ">=" & Sheets("MENU").Range("F16") * 1) + Application.WorksheetFunction.SumIfs(Sheets("ent").Range("G:G"), Sheets("ent").Range("C:C"), Application.VLookup(.Range("A" & i), Sheets("paire").Range("F:G"), 2, False), Sheets("ent").Range("D:D"), "<=" & Sheets("MENU").Range("G16") * 1, Sheets("ent").Range("D:D"), ">=" & Sheets("MENU").Range("F16") * 1)

- et aussi, il faut des tableaux structurés !

Sheets("CA").Range("T:T") devient range("Base[nomcolonneT]") '<<< Base est le nom du tableau ici

Sheets("CA").Range("R:R") devient range("Base[nomcolonneR]")

Ca évite de calculer sur 1 million de lignes inutilement tout en conservant l'aspect dynamique du code et de la tenue des tableaux.

Cdlt,

Le 06/08/2021 à 11:38

Bonjour,

Merci à tous les deux pour vos réponses :)

@Curulis57 --> ah tiens ! J'ai pourtant copier/coller et il me semblait, qu'en termes de "dimensions" le texte est plus concis donc j'en ai conclu qu'il y avait des petites coupes :)
Oui, c'est effectivement une erreur pour l'immatriculation en question. J'ai casser la machine volontairement, donc pas impossible que cela génère des anomalies !

@3GB --> je prend bonne note de ces conseils.

Concernant le bornage : tu peux m'en dire plus sur ces deux lignes ? J'avoue être confus sur comment les utiliser en pratique.

Je vais tenter de faire fonctionner comme ça. Je suis néophyte en tableau structurés et tableau dynamique en VBA : les deux sont compatibles en fait ? Ou je dois choisir l'une ou l'autre de tes propositions ? Ou alors je fais des tableaux structurés sur chaque feuille qui contient des données PUIS je restitue en TCD ?

Merci pour vos aides respectives !!

Une belle journée à vous

Charles

Le 06/08/2021 à 12:05

Salut Charles,
Salut 3GB,

déso, Charles, mais si tu ne m'expliques pas, en français, les conditions de calcul de chaque colonne de ta synthèse (!! pige pas tes formules !!), je ne pourrai pas aller plus loin !


A+

Le 06/08/2021 à 12:25

Rebonjour Curulis57,

Concrètement sur toute la macro :

-En ligne 2 ==> je ne fais que créer des totaux : donc à 95%, sur cette ligne 2, des sommes sur la sélection filtrée. Les 5% restants pour des données qui ne sont pas sommables (genre des consommations aux 100 km, pour lesquelles je fais des Moyenne sur la sélection filtrée).

-Pour les autres lignes :

==> à 49% du temps : des somme.si qui vont chercher dans des feuilles de données. Donc basiquement des somme.si avec comme critère 1 l'immatriculation puis en critère 2 le bornage de la date. Chaque colonne va piocher la donnée dans une feuille précise. En fait, j'ai plusieurs outils qui me permettent d'extraire plein d'information, mais pas de système central informatique permettant de tout reboucler automatiquement. C'est l'objet de mon tableau : je compile les données : Chiffre d'affaire / consommation / entretien / péage / etc. Ce tableau synthèse affichant... tout ! Une ligne = une immat et une colonne = une information.

==> l'autre 49% : des recherches-verticales. Idem, je recherche l'immatriculation présente dans la feuille synthèse, dans un tableau de données d'une autre feuille.

Donc effectivement, la remarque de 3GB est plutôt pertinente : je pense que je boucle/somme sur des colonnes entières/onglet entier. Mais peut-être qu'en termes de formules on peut faire mieux, c'est tout à fait possible.

==> les 2 derniers % : simple somme sur cellules calculées (genre "total du coût" = somme des colonne X à Y, X et Y étant calculé juste avant dans la boucle)

Merci !

Charles

Le 06/08/2021 à 14:08

Re,

Concernant le bornage : tu peux m'en dire plus sur ces deux lignes ? J'avoue être confus sur comment les utiliser en pratique.

En fait, la première rend le calcul manuel et la seconde le remet en automatique. Il faut donc mettre la 1ère en tout début de macro et l'autre en toute fin. Il est possible que ça ne change rien et ne serve pas étant donné qu'on veut fonctionner en mémoire pour rompre tant que possible les liens avec Excel le temps du programme. Mais, si jamais le fichier contenait des fonctions volatiles, je crois qu'elles pourraient être recalculées inutilement (ça reste à confirmer !).

Je vais tenter de faire fonctionner comme ça. Je suis néophyte en tableau structurés et tableau dynamique en VBA : les deux sont compatibles en fait ? Ou je dois choisir l'une ou l'autre de tes propositions ? Ou alors je fais des tableaux structurés sur chaque feuille qui contient des données PUIS je restitue en TCD ?

Les 2 sont compatibles. Les tableaux structurés sont une fonctionnalité d'Excel permettant de mieux gérer et sécuriser les données. Les tableaux dynamiques (à ne pas confondre avec les TCD) sont des variables multidimensionnels pouvant contenir plusieurs éléments. Elles permettent d'éviter de repasser par le support qu'est Excel et souvent d'accélérer une procédure.

je pense que je boucle/somme sur des colonnes entières/onglet entier

Et justement, le problème ici, ce ne sont pas les formules (ni même le calcul automatique) mais le fait qu'on écrit en boucle sur la feuille de calcul. C'est cette opération qui prend du temps (sachant que ça a des répercussions ensuite sur le calcul). Ce qu'il faut chercher à faire, c'est écrire les résultats dans un tableau VBA (array) et ensuite coller le tableau d'un coup (une seule écriture sur la feuille).

Mais, quand même, des formules calculées en boucle qui portent sur toute une colonne, ça peut être long, d'où la nécessité de recourir à des tableaux structurés.

Cdlt,

Le 06/08/2021 à 14:44

Re,

@ 3GB : au top !

Merci pour ces précisons aussi claires que précieuses !

Je vais tenter de bricoler avec ça, je vous tiens au courant :)

Un bon week end à vous !

Le 06/08/2021 à 15:14

Avec plaisir ! Bon courage pour ce bricolage et n'hésitez pas si vous avez des questions.

Bon week-end à vous aussi !

Le 08/08/2021 à 09:08

Bonjour à tous,

pourquoi tu veux faire ça en vba ?
J'ai l'impression qu'à part des formules ta macro ne fait rien d'autre.
Les formules sur feuilles utilisent tous les threads et tu peux avoir 4 ou plus calculs en parallèle selon ton proc, vba n'en utilise qu'un seul.
Tu devrais faire un test, et demander si tu as un point de blocage qui t'en empêche.

Et en 1er, travaille sur les plages utiles (tableau structuré ou noms dynamiques) comme expliqué précédemment.
Impératif pour les matricielles et tu n'as que ça pour ainsi dire.
eric

Le 10/08/2021 à 14:50

Bonjour à tous,

Eriiic, merci pour ton post !

J'ai un peu bricolé comme prévu : le calcul du tableau dynamique reste long lui aussi. Même s'il est collé en une fois, j'ai bien galéré, il tombe pas ou il faut mais pas c'est pas la question => le calcul reste trop long.

Du coup Eriiic oui je ne sais pas pourquoi je n'ai pas essayé. J'ai bricolé rapidement et effectivement c'est beaucoup plus efficace et cela répondrai bien au besoin. Ma question, avant de m'y lancer corps et âmes : j'ai juste fait les formules pour tester la vitesse. Maintenant vous me confirmez que pour ne pas "fausser" les formules je dois bien m'initier aux "noms dynamiques" ? En gros il ne faut pas que mes insertion de colonne dans les feuilles retravaillées viennent saboter le travail ! Je ne maîtrise pas le sujet, donc avant d'y investir du temps je voulais être sûr :)

Dans mon esprit : je défini les futures plages de mes rechecheV avec des noms dynamiques. Il faut que cette plage soit augmenter pour chaque row/column.insert. Maintenant en pratique je sais pas comment, mais l'idée est bien celle la ?

Très bonne journée,

Charles

Le 10/08/2021 à 19:55

Bonjour,

oui, c'est le principe.
Mais si tu peux mettre ton tableau en Tableau structuré (Insertion / Tableau) ça se fait tout seul.
Les ajouts de lignes et de colonnes s'intègrent automatiquement au tableau.

Tape "=", amènes le curseur en début de ligne ou en haut de colonne jusqu'à ce que le curseur change en flèche épaisse et clique.
La notation structurée pour la ligne ou colonne s'écrit.

sinon :
[http://www.mdf-xlpages.com/modules/smartsection/item.php?itemid=64 Nommer une Plage Dynamique]
eric

Rechercher des sujets similaires à "amelioration boucle avis possibilites"