Optimisation temps de calcul - formule matricielle avec Frequence()

choup67

XLDnaute Occasionnel
EDIT : j'ai séparé les calculs et sommeprod fonctionne rapidement. Le problème vient donc de la formule matricielle. Existe t-il une autre formule que je pourrais utiliser pour faire un comptage selon conditions et hors doublons?
Bonjour à tous,

J'ai un tableau de bord dans lequel j'utilise la fonction sommeprod et fréquence afin d'obtenir depuis une base de données des comptages en fonction de condition.

Le soucis, c'est que mon fichier met 35 min pour effectuer les calculs. La base fait entre 3000 et 10 000 lignes et les champs sont nommés par des noms dynamiques utilisant DECALER.

Existe t-il un moyen de rendre les temps de traitement plus courts? ou d'obtenir les infos dont j'ai besoin par le biais d'autres formules?

Structure de mon fichier :
Onglet Toisage : contient une extraction d'un ERP mise à jour 1x par mois
onglet Base : contient une extraction d'un ERP avec la mise à jour d'une formule (rechercheV) en colonne BX se basant sur toisage
Onglet TdB : contient le tableau de bord en se basant sur les informations de l'onglet Base.

Ce dont j'ai besoin dans TdB :
1) Mise à jour des dates automatique par rapport à la date du jour

2) Pour chaque pays, je veux connaitre par statut et par date :
* Le total du poids commandé
* le nombre de ligne total

3) Pour chaque pays, je veux connaitre
* le total de poids commandé par jour (indépendamment du statut)
* le nombre de ligne total parjour (indépendamment du statut)
* Le nombre de référence (donc nombre de lignes hors doublons en se basant sur le code article) par jour
* Parmis les références par jour, le nombre (hors doublons) dont le champ "BV" de l'onglet base est à "OUI"
* Parmis les références par jour, le nombre (hors doublons) dont le champ "BV" de l'onglet base est à "Non" ou Vide

Pour y arriver, j'ai utilisé les fonctions sommeprod et frequence.

J'ai pris un très petit echantillon de mes données afin de montrer la structure de mes fichiers et le code que j'ai utilisé.

En réalité :
Toisage : contient 40 000 lignes
Base : contient de 3000 à 10 000 lignes
TdB : contient 256 lignes (avec les formules) : chaque ligne corrrespond à un pays

Pouvez vous m'aider à optimiser la façon dont j'ai créer ce tableau de bord afin de rendre l'execution des calculs plus rapides?

Merci.
 

Pièces jointes

  • Optimisation.xlsm
    317.9 KB · Affichages: 169
  • Optimisation.xlsm
    317.9 KB · Affichages: 207
  • Optimisation.xlsm
    317.9 KB · Affichages: 243
Dernière édition:

poulpor78

XLDnaute Junior
Re : Optimisation temps de calcul - fichier avec SOMMEPROD

Bonjour,

D'abord joli travail, tu as dû y passer un certain temps !

Même si je n'ai certainement pas le niveau pour te conseiller, je te fais partager mes réflexions. On ne sait jamais.

A première vue, je commencerais à travailler sur deux axes :

- en vba : tu saisis une formule puis tu calcules. Pourquoi ne pas passer en calcul manuel puis calculer à la fin.

- sur les formules de ton tableau de bord, j'essaierais de sortir de tes formules certains calculs réalisés plusieurs fois. Par exemple, tu utilises plusieurs fois NBVAL(Base!A:A)-1 => pourquoi ne pas mettre cette formule en ligne 3 du tdb puis faire référence à ce résultat dans test formules de calcul ? Ainsi les calculs seraient moins lourds.


Autrement, plus radical, on pourrait imaginer partir sur la formule =Indirect
La formule suivante :
=SOMMEPROD((DECALER(Base!$X$2;;;NBVAL(Base!G:G)-1)=D$4)*(DECALER(Base!$E$2;;;NBVAL(Base!G:G)-1)=$C8)*(DECALER(Base!$G$2;;;NBVAL(Base!G:G)-1)=F$5);DECALER(Base!$BW$2;;;NBVAL(Base!G:G)-1))

Cette formule peut être remplacée par :
=SOMMEPROD((Base!$X$2:$X$8=D$4)*(Base!$E$2:$E$8=$C8)*(Base!$G$2:$G$8=F$5);Base!$W$2:$W$8)

Le 8 peut être calculé sur une ligne masquée en haut du tdb. On pourrait également saisir (calculer?) les lettres X, E, G, W dans des lignes plus hautes (à insérer) pour déterminer les plages à construire.

Sans certitude, j'imagine que decaler est plus lourd que indirect.


Pour finir, je ferais également visant à déterminer quels types de formules me consomment du temps : les matricielles à accolades ou les sommeprod ?

Poulpor
 

choup67

XLDnaute Occasionnel
Re : Optimisation temps de calcul - fichier avec SOMMEPROD

Salut poulpor78,

Merci pour tes pistes de reflexion.

Effectivement, j'étais tellement à fond le nez dedans que j'avais pas vu que je n'avais pas utilisé des noms dynamiques partout.

J'ai donc corrigé l'ensemble des formules sommeprod en remplacement les DECALER() par des noms dynamiques. Ce qui me donne des formules du type :

Code:
=SOMMEPROD((Date=$D$4)*(Pays=$C7)*(Statut=D$5);(Poids))

concernant le second point, à l'ouverture du fichier le mode de calcul passe en manuel. C'est lorsqu'on clique sur "metrre à jour" que les calculs se font.

Code:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.Calculation = xlCalculationAuto
End Sub

Private Sub Workbook_Open()
Application.Calculation = xlCalculationManual
End Sub

Code:
Sub MaJ()
'
' MaJ Macro
'
Dim DerLign As Integer

Application.ScreenUpdating = False

DerLign = Sheets("Base").Range("A" & Rows.Count).End(xlUp).Row

With Sheets("Base")
    .Range("BW2:BW" & DerLign).Value = 1
    .Range("BX2:BX" & DerLign).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-65],Toisage,12,0),"""")"
    .Calculate
End With
 

With Sheets("TdB")
    .Range("D4:DI256").Calculate
End With

Application.ScreenUpdating = True

End Sub

Je viens de relancer le calcul, pour le moment, ça mouline. On va voir si ça prends moins de 35 minutes mais je n'ai pas l'impression que ça ait changé quelque chose.

Je me demande si ce n'est pas plutôt frequence() qui est long. Le soucis, c'est que j'ai choppé cette formule sur le net en l'adaptant à mon besoin, mais en réalité, je ne comprends pas comment elle fonctionne. J'en avais besoin pour pouvoir gérer les doublons avec conditions. Peut être existe t-il une meilleure solution?
 

choup67

XLDnaute Occasionnel
Re : Optimisation temps de calcul - fichier avec SOMMEPROD

C'était toujours aussi long.

J'ai effectué le calcul uniquement sur les cellules avec sommeprod et c'est rapide.

Donc le problème vient bien de la formule matricelle. Une idée?
 

Misange

XLDnaute Barbatruc
Re : Optimisation temps de calcul - formule matricielle avec Frequence()

Bonjour

Sans regarder tout en détails, une réflexion immédiate et évidente
1) transforme tous tes tableaux en vrais tableaux excel et oublie le fait de devoir définir des noms dynamiques avec la fonction decaler : c'est une fonctionnalité intégrée aux tableaux (onglet accueil/Style mettre sous forme de tableaux)
plus di'infos ici


2) utilise les tableaux croisés dynamiques au maximum et réserve pour ce qui n'est VRAIMENT pas faisable autrement l'utilisation de formules notamment de formules matricielles. LEs TCD sont infiniment plus puissants et rapides que les macros et que les formules matricielles.
Ce lien n'existe plus

3) ne travaille JAMAIS sur des colonnes entières avec excel 2007 et +. C'est un facteur majeur pour ralentir

Un petit essai en PJ de ce que j'ai compris de tes analyses.
La disposition peut être changée bien entendu.
Tu peux aussi de cette façon très facilement synthétiser tes données par mois, années et ajouter autant d'autres analyses que tu le souhaites et même faire tes graphiques en même temps automatiquement.
 

Pièces jointes

  • Optimisation.xlsm
    318 KB · Affichages: 96
  • Optimisation.xlsm
    318 KB · Affichages: 99
  • Optimisation.xlsm
    318 KB · Affichages: 99

poulpor78

XLDnaute Junior
Re : Optimisation temps de calcul - formule matricielle avec Frequence()

Re,

J'imagine que sur le fichier original, la base de travail est longue. Une piste pourrait donc être de créer un code qui sépare la base en un onglet / pays. Ainsi, les calculs seraient plus rapides.

Concernant la formule avec la formule matricielle avec 'fréquence', je m'avoue complètement incompétent.

Poulpor
 

choup67

XLDnaute Occasionnel
Re : Optimisation temps de calcul - formule matricielle avec Frequence()

Merci pour ton retour Misange.

Effectivement le TCD est adapté pour la 1ère partie de mon besoin (calcul de poids et de nb de lignes). Mais même avec sommeprod, au final c'est très rapide.

C'est la 2nd partie qui me pose problème. J'ai besoin de compter le nombre de référence (hors doublons) par pays et date puis parmis ces références savoir lesquelles ont la valeur toisage à OUI ou à NON et vide.

Pour le coup, j'ai trouvé la formule matricielle avec Fréquence() sur XLD mais elle semble très lourde en calcul. Existe t-il un autre moyen de calculer le nombre de référence (hors doublons) selon la date puis selon la date et la valeur du toisage?
 

Misange

XLDnaute Barbatruc
Re : Optimisation temps de calcul - formule matricielle avec Frequence()

Quand dans un TCD on a besoin de compter des valeurs hors doublons, le plus simple est de mettre une colonne supplémentaire dans la base en ajoutant un test.

Le problème avec ton classeur c'est que toi tu t'y retrouves mais moi plus difficilement : c'est quelle colonne tes "références"
de quelle date parles tu il y en a plein dans ton classeur... Essaie de te mettre à la place du répondeur :)

un essai de ce que j'ai compris

Peut être que tes sommeprod vont "vite" avec quelques lignes. Mais dès que tu as des tableaux importants, ce n'est plus la même histoire.
Ici j'ai été obligée d'en ajouter un à ta base de données quand même mais il n'y a pas d'autre calcul matriciel (les TCD font en fait des calculs matriciels mais en langage machine ce qui va autrement plus vite).
Mais c'est ton classeur et ton temps c'est toi qui vois !
 

Pièces jointes

  • Optimisation.xlsm
    318.6 KB · Affichages: 87
  • Optimisation.xlsm
    318.6 KB · Affichages: 95
  • Optimisation.xlsm
    318.6 KB · Affichages: 100

Discussions similaires

Réponses
6
Affichages
277

Statistiques des forums

Discussions
311 720
Messages
2 081 899
Membres
101 834
dernier inscrit
Jeremy06510