XL 2013 Accéler une serie de sommeprod sur plusieurs lignes et variables

[GADENSEB]

Bonjour le forum,
J'ai joins mon fichier crashtest
Je cherche à accélérer une série de sommeprod (colonne D) qui sont tjrs sur le mm principe avec seule variable la colonne B

=SOMMEPROD((LIGNE=B6)*(FUTUR="non")*(BQ="oui")*(DEBITCREDIT))

Dans mon fichier original j'ai une trentaine de ligne.
Le temps de calcul est super long.......Donc je me dis que l'on peut accélérer le tout en macro....
QQn aurait une idée ?

Bonne journée

seb

 

[gosselien]

Bonjour,

s'il rame avec 30 lignes, faut changer le pc alors
le tcd fait ce que tu demandes je vois

P.

 

[GADENSEB]

lol
En fait, je n'aime pas trop le TCD ... lol

 

[gosselien]

Essaie un tcd et des sommeprod sur 10.000 lignes et tu verras

 

[Paf]

Bonjour GADENSEB, gosselien,

Chaque plage nommée est définie jusqu'à la ligne 3982 pour 30 lignes utiles !!! on manipule donc plus de 3900 lignes inutilement !

en réduisant la définition de chaque plage, on devrait gagner du temps!

A+

 

[Dranreb]

En fait, je n'aime pas trop le TCD

Moi non plus je ne les aime pas. C'est pourquoi j'ai toujours sous le coude des modules de service qui permettent de produire en VBA le même genre de rapports mais avec toute liberté de présentation souhaitée.
Par ailleurs la mise sous forme de tableau apporterait une simplification par des noms de colonnes correctement dimensionnées en nombre de lignes.

 

[GADENSEB]

@ Ce lien n'existe plus : je veux bien te croire, Excel à de superbes outils en interne, mais les tcd tiennent vite bcp de place.
@Ce lien n'existe plus : en fait ce fichier est un extract de ma bdd (5000 lignes environ) que j'ai retaillée pour ne pas tt faire apparaitre
@Dranreb : Je n'ais pas bien compris

Par ailleurs la mise sous forme de tableau apporterait une simplification par des noms de colonnes correctement dimensionnées en nombre de lignes.

 

[Dranreb]

Alors explications:

La mise sous forme de tableau de l'onglet Accueil, groupe Styles débouche sur la création d"un objet de type ListObject faisant partie d'une collection ListObjects d'un objet Worksheet qui représenterait la feuille.
Les plages de colonnes couvrant des lignes faisant effectivement partie du tableau sont connues sous les noms de leurs titres dans le tableau (propriété DataBodyRange des éléments de la collection ListColumns de l'objet) et il existe aussi une syntaxe particulière (un peu lourde je trouve toutefois, dans certains cas) pour s'y référer dans les formules. Votre 1ère formule en D6 deviendrait :

=SOMMEPROD((Tableau1[LIGNE]=B6)*(Tableau1[FUTUR]="NON")*(Tableau1[BQ]="OUI");Tableau1[DEBITCREDIT])

(si toutefois vous n'aurez pas pris soin de renommer Tableau1 en quelque chose de plus court et plus mnémonique)

 

[GADENSEB]

@ Dranreb :
Technique intéressante,
e viens de la tester sur mon fichier crash-test
Cela réduit vraiment le temps de calcul sur une grosse bdd ?
Ma bdd est une plage normal pas un tableau.
J'alimente ma bdd via un busf.
Est-ce qu'un tableau de ce genre s'alimente aussi via usf ?
Sans changer le code existant ?

À+
Seb

 

[Dranreb]

On peut bien sûr mettre à jour une plage mise sous forme de tableau à l'aide d'un UserForm.
Seule restriction: on ne peut y insérer de ligne ailleurs que derrière la dernière par la voie classique. Mais les modifications resteront minimes même par la voie prévue (instructions comme :
Set PlageLigne = LO.ListRows.Add.Range, déclarés: PlageLigne As Range, LO As ListObject, ce dernier ayant été initialisé par Set LO = UnWorksheet.ListObjects(1) ou bien = PlageQuiEnFaitPartie.ListObject)

Un de mes objets ComboBoxLiées, définis par un module de classe du même nom, conserve à votre disposition en tant que propriété Lignes la collection ListRows d'un ListObject si on lui en spécifie un au début (méthode .Plage), ou même, d'après le Range plutôt spécifié d'habitude, s'il le trouve tout seul.
Il accepte d'ailleurs alors dans ses .Add, derrière chaque ComboBox dont on lui confie la charge, un titre en guise de spécification de colonne.
Il est donc aussi conçu pour travailler avec un ListObject.

 

[GADENSEB]

Hello Danreb,
Merci pour l'explication
Cette méthode va coincer avec mon code actuel.

Bon dommage

 

[Dranreb]

Bonjour.
Pourquoi ça ?
Il n'y aurait aucune raison, même s'il était mal écrit…

 

[GADENSEB]

Je pense que les codes sont bien ecrits
en voici un extrait pour Saisie et Modification de la BDD
Qu'est ce que tu en penses ? cela pourrais se compléter avec ton code?

Private Sub Enregistrement_Données()
With Me
    tExport(1) = .TB_Code.Text
    'tExport(2) = Format(.TB_Date.Text, "dd/mm/yyyy")
    tExport(2) = CDate(Format(.TB_Date.Text, "dd/mm/yyyy"))
    tExport(4) = .CO_Mois.Text
    tExport(5) = .CO_BudgetReel.Text
    tExport(6) = .CO_Compte.Text
    tExport(7) = .CO_Poste.Text
    tExport(10) = .TB_Numero.Text
    tExport(11) = .TB_Libelle.Text
    tExport(12) = .CO_ModeRegl.Text
    tExport(13) = .CO_Tiers.Text
    tExport(15) = .CO_BQ.Text
    tExport(16) = .TB_Debit.Text
    tExport(17) = .TB_Credit.Text
End With
End Sub



Sub Saisie()
    If Not InitFait Then Initialisation
    If Not InitUF Then Initialisation_UF
    Mode = 0
    Usf_Saisie.Show
    If Not Satisf Then Exit Sub
    Ligne = shC.[a65000].End(xlUp).Row + 1
    Transposition_Données
    If bVirement Then Virement
    If bDouble Then Doubler_Ligne
    If bRecursivite Then Recursivite_Ligne
    Formules
    Formats
    Tridonnées
    ExtractRestant30J
    ListBudgetMois
    shI.Activate
End Sub

Sub Modifier()
    If Not InitFait Then Initialisation
    If Not InitUF Then Initialisation_UF
    Mode = 1
    Recherche_Ligne
    If Not Trouver Then Exit Sub
    Enregistrement_Ligne
    Usf_Saisie.Show
    If Not Satisf Then Exit Sub
    Transposition_Données
    Formules
    Formats
    Tridonnées
    ExtractRestant30J
    ListBudgetMois
    shI.Activate
End Sub

 

[Dranreb]

Bonjour.
Oui. Plus besoin de End(xlUp).Row, on aura LO.ListRows.Count
Pourquoi ne pas faire LO.ListRows(Ligne).Range.Value = tExport en modificaton et LO.ListRows.Add.Range.Value = tExport en création ?

 

[GADENSEB]

Hello
je reviens avec un peu de retard.

L'idée est bonne.
Par contre ce que je n'arrive pas à comprendre.. est-ce que le fait de transformer en tableau la bdd, cela va vraiment accélérer mes 30/40 sommeprod ?

Bonne journée
Seb