AIDE CODE VBA à la place SOMMEPROD TRES LOURDE

Question

J'ai changé le titre de ma demande et je me suis rassuré que le sujet n'a pas été traité avant. J'ai une fonction sur un fichier très lourd, je souhaite la remplacer par code VBA. Vous trouvez plus de détail dans le Pc...

eriiic · Answer

On s'est croisés sur l'autre post. Je colle ma réponse : tes champs Salaire de base Heure suppl Prime régulière Prime Périodique Cnss nationaux Cnss Expatriés Régime retraire RCC n'existent pas dans les feuilles mois. Impossible de faire quoi que ce soit sans explications claires et complètes. Et propose un modèle de résultat en colonnes avec 1 ligne par agent comme tout bdd qui se respecte. Là ça va te faire 2000 colonnes, illisible et ch... pour mettre les résultats. Au pire on pourrait mettre qcq lignes par agent si tu préfères mais pas les uns à coté des autres. eric...

galopin01 · Answer

Je me suis intéressé un peu à ce cas. Je ne suis pas tout à fait sur d'avoir bien compris, mais si c'est le cas ça vaut le coup de poster car cela illustrerait parfaitement le bénéfice qu'on peut tirer de l'utilisation des fonctions de Base de Données sur de très grandes feuilles. Ce que je comprend : Les SOMMEPROD ne font pas référence aux champs mais au N° des comptes contenu dans ces champs. Bon ça revient au même parce que les mensuelles ne contiennent qu'un seul N° de compte donc on n'a pas beaucoup de données à tester... d'ailleurs on n'a qu'un seul résultat de référence : Q10 Sur le mode d'extraction choisi : On est sur 125 colonnes par agent (et non 2000 : les autres colonnes correspondent aux autres mois) ce qui est quand même surement déjà trop pour être lisible mébon... Si mon hypothèse est bonne... Je ne connais qu'une solution pour se sortir de ce truc : Quand les SOMMEPROD trouvent leurs limites les fonctions de base de donnée commencent à devenir rentables. L'inconvénient est que l'ensemble du classeur n'est guère adapté à ce genre de calcul (Nom de champs à rallonge, nom d'onglets fantaisistes, aucun nom de plage dynamique...) Néanmoins j'ai adapté le mois de janvier pour sortir quelques résultats avec les différents comptes... et un seul matricule. De plus j'ai restreint les formules aux quelques lignes de janvier. La feuille1 donne un modèle de grille d'extraction Avec ça et une 'tite macro qui tourne sur tous les matricules et toutes les feuilles, on doit obtenir un résultat satisfaisant même si on base sur des mois de 200 000 lignes. Mais bien sur ça suppose qu'on peut "normer" un peu les feuilles sources. Sinon inutile de se fatiquer pour rien... ça serait bien qu'on puisse disposer d'une BD des matricules ça serait bien qu'on puisse disposer de 12 mois non identiques, mais avec des N° de comptes différents, parce que là pour les tests... Inutile de fournir 200 000 lignes ni les 12 mois : 1 seul mois de quelques centaines de lignes seraientt bien suffisant mais avec un éventail de comptes suffisamment représentatif pour tester les calculs. Si tu ne peux pas poster ton zip en pièce jointe par ce qu'il est trop volumineux, tu le poses sur http://cjoint.com/index.php et tu nous donnes le lien pour le récupérer. Bon comme je n'ai pas envie d'attendre pour que tu nous donnes de toute façon un fichier riquiqui, j'ai essayé de simuler un fichier "maison". Sur la base de ton fichier et de ce que j'ai compris, j'ai complété le fichier de janvier que j'ai bricolé pour arriver à 200 000 lignes et en diversifiant les N° de compte pour que tous les comptes puissent être comptabilisés. J'ai dupliqué la feuille Janvier sur les autres mois pour obtenir 12 feuilles de 200 000 lignes environ. J'ai créé quelques plages nommées, renommées les mois et quelques bricoles pour obtenir des requêtes homogènes... L'ensemble pèse la bagatelle de 60 Mo en xlsm : 20 Mo en xlsb... je travaille donc en xlsb j'ai ensuite fait une grille d'extraction pour ces données (la feuille "Extr") Comme je ne doute de rien, j'ai ensuite coché l'option Calcul Automatique : sur mon petit portable le chargement se fait en quelques secondes (une dizaine maximum pour le recalcul et l'affichage) Ce qu'on peut considérer comme acceptable. Bien sur l'extraction ne considère qu'un Matricule à la fois il faudrait faire une petite macro pour passer en revue tous les matricules et les afficher "en dur" sur la feuille "Extr" ce qui ne semble pas insurmontable sur la base d'un millier de matricules : Une bonne douzaine de milliers de ligne n'aggraveront pas le cas... Le seul écueil : sur la base de la réflexion d'Eric je ne suis pas tout à fait certain d'avoir bien compris la question. J'attendrai donc une confirmation avant d'essayer de lancer une macro sur tous les matricules si c'est bon. Heu... Comme tu parles de 200 000 lignes je suppose que tu es capable d'ouvrir des classeurs de format .xlsb Si certain veulent un classeur réduit pour rentrer au format .xls je donnerai un autre lien avec seulement 50 000 lignes sur demande. Le fichier démo à télécharger : Hum... 17 Mo ! A+...

eriiic · Answer

Si tu ne réponds pas aux questions tu vas continuer à chercher tout seul... eric...

galopin01 · Answer

Mon fichier test est un peu différent du précédent et comporte 11 000 matricules différents sur 190 000 lignes par mois et chaque mois comporte des chiffres et des comptes différents pour obtenir un temps de réponse assez voisin. Comme tu ne réponds pas aux questions et que ton fichier est assez minimaliste j'ai utilisé des matricules un peu différents, (AAA AAB...) mais ça ne change rien au traitement du problème . Ma solution prend moins de 10 minutes pour 50 Matricules (enregistrement compris). Soit environ 24 heures si tu as 11 000 matricules à traiter. Malgré le ventilo qui tourne plein pot, pour faire cuire les oeufs, c'est au poil... Les procs doivent être bien rodés maintenant ! C'est tout de suite plus abordable si tu n'as que quelques centaines de matricules comme sur ton fichier... Et comme au final il n'y a aucune formule ç'est jouable. ça t'intéresse ? Le fichier Nota : Comme la macro est vraiment très simple, si tu te lances vraiment dans le truc il devrait être interressant de calculer toutes les sommes (en ligne) dans la macro : ça ne ralongerait pas excessivement le traitement (C'est juste le nombre de matricules qui est déterminant) mais l'inscription en dur allègerait d'autant le résultat qui n'a vraiment besoin d'aucun calcul supplémentaire... A+...

galopin01 · Answer

Inutile d'arroser plusieurs forums si tu ne testes pas les réponses qu'on te donne, si tu ne cherches pas à comprendre et si tu ne réponds pas aux questions posées. Si tu ne comprends pas une réponse, demande un complément d'explications. En général le répondeur ne sera pas avare de détails... Le fait d'avoir 200 000 lignes est (relativement) de peu d'importance en VBA. L'important c'est le nombre de fois qu'on va être obligé de parcourir ces 200 000 lignes : S'il n'y a que quelques centaines de matricules disctincts (sur l'ensemble des feuilles), En quelques minutes l'affaire est classée. Si en revanche il faut calculer sur plusieurs milliers de matricules. C'est un peu plus long... Avec la méthode proposée qui ne nécessite que quelques modifications mineures, il faut compter 2 heures par tranches de 1000 matricules : Même si ça peut paraître important c'est non négligeable car ça allège considérablement le classeur : on peut même supprimer la feuille de requêtes après traitement. (puisqu'il n'y a aucune formule) Donc inutile de répéter à chaque fois la même demande et de joindre un classeur réduit au ridicule : Donne toi autant de mal à essayer et comprendre les réponses que les répondeurs en mettent à concocter des réponses et tout le monde sera content... Dans le cas présent tu aurais certainement gagné une semaine d'attente et moi je n'aurai pas perdu une après midi pour rien. A+...

galopin01 · Answer

A toi de voir si tu as besoin de ces chiffres...

Tu as 1/2 heure de préparation pour faire une extraction de tous les matricules sur la feuille Param : Tu pars en Week-End Vendredi et quand tu reviens ton tableau est terminé, sauvegardé et tout et tout...

J'ai testé la macro pendant 4 heures et elle tourne impeccable...

J'ai volontairement rajouté une sauvegarde toutes les 10 minutes compte tenu du grand nombre de données. Au total ça fait peut-être une heure de perdue en plus, mébon.

Tu peux même supprimer les feuille "Extr" et Param après.

En plus tu n'es même pas enquiquiné par le recalcul automatique puisque tous les calculs sont "en dur" donc il n'y a aucune liaison.

Tu nous au courant si quelqu'un à trouvé mieux !

A+

galopin01 · Answer

La macro de ce classeur est une démo et permet de visualiser le résultat au bout des 50 premiers matricules. Pour traiter vos 10 000 matricules, il suffit d'enlever les 3 lignes indiquées dans le code. Bien sur vous devez importer vos propres données et établir votre propre liste de matricules dans la feuille "Param" J'insiste bien sur le fait que pour que ça fonctionne vous devez établir dans la feuille "Param" (colonne 1) une liste ou tous les matricules existants sont répertoriés sans doublons. Quand le traitement sera terminé vous pourrez supprimer les feuilles "Extr" et "Param" J'ai supprimé la ligne et les colonnes comportant des formules SOMME() dans la feuille "Récap" (Colonne A) pour ne pas perdre du temps de traitement. Il ne sera pas bien difficile de les rajouter après. A+...

galopin01 · Answer

Il n'est pas utile de trier quoi que ce soit. Simplement il faut une liste( sans doublons) de tous les matricules existants . Cette liste peu importe qu'elle soit triée doit être dans la feuille Param (colonne A) A+...

galopin01 · Answer

Ok,

Je me suis apperçu que le calcul "sur ordre" avait disparu dans mon dernier classeur.

N'oubliez pas de le remettre avant de lancer la macro, sinon le temps de traitement devient affolant.

Si vous lancez juste un test pas de problème.

N'oubliez pas de supprimer les 3 lignes de macro indiquées dans mes explications quand vous lancerez la macro pour le traitement définitif.

Bonsoir.

galopin01 · Answer

Je me demande parfois si je parle bien français... 1 - Il n'est pas utile de trier les matricules. Vous n'avez pas besoin de macro pour supprimer les doublons : Sélectionnez la colonne A dans la feuille "Param" puis utilisez la commande du MENU (Ruban) Données &gt; Supprimer les doublons &gt; Colonne A 2 - Le code de la macro de calcul pour tous les matricules : &#91;code=vb&#93;Sub CalcAll() Dim i&, ii&, a%, b%, c%, k%, x&, iR&, LCel, Tablo, WsC As Worksheet Worksheets("Extr").Activate Application.ScreenUpdating = False iR = 9 Set WsC = Worksheets("Recap") ii = Range("MleAll").Count For i = 1 To ii LCel = Range("T17") Range("A5") = Worksheets("Param").Cells(i, 1) Worksheets("Extr").Range("A1:V17").Calculate While Range("T17") = LCel DoEvents Wend Tablo = Range("A5:T17") ' Affichage ------------------------------------- x = iR + i WsC.Cells(x, 1) = Tablo(1, 1) For a = 1 To 12 c = 8 + a * 9 For b = 2 To 20 Step 3 WsC.Cells(x, c + k) = Tablo(2, b) k = k + 1 Next k = 0 Next ' --------------------------------- Fin Affichage If i Mod 50 = 0 Then ActiveWorkbook.Save End If Next End Sub&#91;/code A+...