Discussion :

[damsaga]

Bonjour à tous,

Merci d'avance à ceux qui auront la patience de lire jusqu'au bout : )
Voici le problème:

Dans un fichier excel j'ai un onglet "planning" qui me sert à "consolider" les valeurs d'un onglet "base de données" qui contient des lignes de commande de 9000 articles. Il y a beaucoup de ligne de commandes...

Plus précisément le but est d'avoir la somme des commandes pour chaque article en fonction de différents critères (année, mois, machine utilisée) afin d'obtenir une vision du volume de commande pour chaque article
J'arrive à mes fins à coup de somme.si.ens(avec 6 critères de comparaison entre mon onglet planning et base de données).

Lorsque mon tableau de consolidation compte 10 articles sa marche bien, le calcul automatique s'effectue en instantanée. Par contre lorsque je mets la liste qui m'intéresse (300 articles à consolider sur les 9000, là sa met bien 1 minute à recalculer la feuille...)

Mon problème est donc que mes formules somme.si.ens ralentissent trop le calcul, je dois conserver cette consolidation mais mon objectif est d'avoir un temps de recalcul acceptable (quelques secondes grand maximum).

Enfin zut alors je demande pas un calcul si compliqué que sa à excel :p.

J'espère avoir été clair.

Merci par avance pour votre aide

[Menhir]

Les SOMME.SI (et, à fortiori, SOMME.SI.ENS) sont, comme les fonctions matricielles, très gourmandes en ressources et en processeur.
La seule méthode que je vois pour toi d'avoir une temps de traitement acceptable avec une si grosse quantité de données et de formules, c'est de passer par du VBA. Une boucle For to et quelques Case (ou Find), ça prendrait sans doute beaucoup moins de temps de traitement.

[mercatog]

Un TCD?

[damsaga]

Merci pour vos réponses,

J'ai pensé au TCD mais dans mon cas le souci est que ma plage de consolidation est fixe (de aujoud'hui à 12 mois) alors que la base de données contient des commandes à l'intérieur et à l'extérieur de ce périmètre restreint. Donc il me faudrait malgré tout réappliquer des formules conditionnelles sur ce TCD pour avoir la consolidation voulue.

C'est comme sa que je vois les choses après j'exploite peut être mal le TCD. Je souhaitais également ne pas utiliser le TCD car les valeurs "consolidées" pourront être écrasées manuellement dans certains cas.

Bonne soirée.

PS: le traitement par macro for en lecture directe sur une feuille de 9000 lignes avec 6 critères à comparer à chaque ligne me parait assez lourd aussi non ?

[Menhir]

Depuis que j'ai créé en VBA un programme de résolution du Sudoku qui met moins de 10 s à résoudre les plus compliqués (et ils représentent beaucoup plus que 9000 combinaisons à examiner), je ne m'étonne plus de rien en matière de vitesse de macro.
Je suis prêt à parier à 10 contre 1 qu'une macro traitant tes 9000 lignes mettrait moins de 10 s. En tout cas, ça irait certainement 100 fois plus vite que tes multiples SOMME.SI. A condition bien sûr qu'elle soit écrite correctement, c'est-à-dire en évitant les Select/Selection.
Elle ne serait pas bien compliquée à écrire, tu ne perds donc rien à essayer.

[78chris]

Bonjour

Concernant le TCD, il est très simple de le filtrer sur une période donnée. Si le tableau de consolidation est déclaré en Tableau (au sens 2007-2013) s'il est vidé puis rerempli avec un nombre de ligne différent, le TCD s'adaptera à sa nouvelle taille...

Aujourd'hui tableaux et TCD donnent des possibilités proches des bases de données (table et requête) ...

[mercatog]

Merci Chris

[blepy]

Bonjour,

Juste une remarque, la fonction SOMME.SI.ENS a l'avantage de combiner plusieurs conditions, mais n'est-elle pas trop gourmande ?
Aussi, personnellement, j'utilise une clé concaténée dans la feuille de données, par exemple, créer une colonne supplémentaire qui s'appelle Clé1, qui fait la concaténation vendeur, produit, mois et cette colonne devient clé pour un SOMME.SI.
Pour autant SOMME.SI restera une fonction gourmande, mais cela me semble intéressant d'évaluer le gain versus le SOMME.SI.ENS

[damsaga]

Bonjour et merci à blepy, mercatog, 78chris, Menhir pour vos réponses.

Je vais essayer les deux alternatives TCD et macro. Pour ce qui est des tableaux (au sens 2007-2013) je vais regarder cela de plus près car je ne connaissais pas encore les possibilités qu'ils offrent.

Je reviens vers vous dans la semaine dès que je trouve du temps pour m'y coller.

Bonne journée.

[damsaga]

Rebonjour,

J'essaie actuellement la solution TCD mais je ne vois pas comment me passer des sommes conditionnelles. En effet ce planning consolidé est utilisé comme antécédent dans des calculs d'adéquation charge/capacité en fonction des codes articles. donc si la taille du tableau varie alors les formules qui vienne piocher dessus ne prendront pas toutes les données. Je ne sais pas si je suis clair...

Ci-dessous un aperçu du fichier pour vous apporter plus de vision sur le contenu du classeur, le planning consolidé=ce qui prend du temps à recalculer à cause des somme.si.ens ET la base de données.

Voici les formules utilisées :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=SOMME.SI.ENS(QTE_MEL_PREV;MACHINE_PREV;$E$7;DATE_PREV;"<="&$E$6;REF_MEL_PREV;$C33)+SOMME.SI.ENS(QTE_MEL_PREV;MACHINE_PREV;$E$7;DATE_PREV;">"&$E$6;REF_MEL_PREV;$C33;ANNÉE_PREV;ANNEE($E$6);MOIS_PREV;MOIS($E$6))

Comme vous pouvez le constater, chaque cellule de mon planning consolidé va piocher dans la base de données avec un certain nombre de critère.



Un grand merci à ceux qui pourront m'aider !

[Menhir]

En macro VBA, ça donne quelque chose du genre :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
Sub test()
 
Dim Lig As Integer              ' N° ligne traitée
Dim Res(10, 10, 10) As Integer  ' Résultats (Machine, Ref, Date)
Dim Ind(5) As Integer           ' Indices pour Res
 
For Lig = 2 To Cell(2, 1).End(xlDown).Row
 
   ' ----- MACHINE -----
   Select Case Cells(Lig, 5).Value
   Case "AB3078P"
      Ind(1) = 1
   Case "AB3177P"
      Ind(1) = 2
   Case "VI7110G"
      Ind(1) = 3
   Case Else
      Ind(1) = 10
   End Select
 
   ' ----- REF -----
   Select Case Cells(Lig, 3).Value
   Case "PF176"
      Ind(2) = 1
   Case Else
      Ind(2) = 10
   End Select
 
   ' ----- DATE -----
   Select Case Cells(Lig, 2).Value
   Case Is < DateSerial(2014, 9, 1)
      Ind(3) = 1
   Case Is < DateSerial(2014, 10, 1)
      Ind(3) = 2
   Case Is < DateSerial(2014, 11, 1)
      Ind(3) = 3
   Case Else
      Ind(3) = 10
   End Select
 
   Res(Ind(1), Ind(2), Ind(3)) = Res(Ind(1), Ind(2), Ind(3)) + Cells(Lig, 7)
 
Next Lig
 
End Sub

C'est juste le principe que j'ai pu essayer de déduire de ta formule.
Il faudra bien sûr compléter les Case (j'ai mis les n° de colonne un peu au hasard) et ajuster les dimensions des tableaux de variables et des indices.
Il faudra aussi faire quelques boucles pour placer des résultats (contenu dans les variables Res) dans les bonnes cases à la fin.
Il est aussi sans doute possible de remplacer le Select sur la date par une formule pour déterminer le Ind(3) en fonction du mois et de la date.