Probleme avec SOMMEPROD

[KIM]

Bonjour le Forum,
Bonjour le forum,
J'ai passé toute la journée d'hier et la soirée sans résultat. Je bloque sur une formule simple SOMMEPROD qui me donne le resultat #VALEUR!
Je fais appel comme d'habitude au forum et vous en remercie d'avance.

Je recupère un fichier de plus de 25000 lignes. Dans ce fichier de plusieurs colonnes j''en utilise 3:
1ere col: Code_du_DEP (normalement du Texte)
2° col: montant commande (normalement numérique)
3° col Code du projet (normalement TEXTE)

Je dois faire un récapitulatif selon une liste de projet qu'on me donne:
Ce tableau recap possède :
1° col: Liste des codes projets
2° col: Récap dépense globale par projet et les autres colonnes : Dépenses globale par projet et par DEP

Je me suis rendu compte que dans le fichier Origine il existe des cellules Texte dans la 2° col des montants commande.
Dans mon tableau recap j'utilise:
=SOMMEPROD(($B5:$B1153)*($C5:$C1153=$F16)*($A5:$A1153=J$4))
j'ai essayé aussi
=SOMMEPROD(CNUM($B5:$B115)*TEXTE($C5:$C115;'')=TEXTE($F16;''))

Je copie une centaine de lignes de mon fichier, je supprime les cellules contenant du TEXTE dans la 2° COl
et les formules sont correctes,
Je fais la meme chose sur le fichier origine et j'applique à tout mon fichier les formules
et là j'ai toujours l'erreur #VALEUR!
ci-joint une extraction du fichier d'origine
MERCI DE VOTRE AIDE
Amicalement
KIM [file name=prKIM1.zip size=23794]http://www.excel-downloads.com/components/com_simpleboard/uploaded/files/prKIM1.zip[/file]

 

[KIM]

Dan, Bonjour et le forum,
Avec mes excuses encore,
je n'ai pas fait le lien avec les 2 TCD, le tien et celui de Celeda.
Ton TCD fait les sommes par DEP et projets, parcontre celui de Celeda met un x pour chaque liaison projet-DEP. et comme je suis nul en TCD, pour moi je pensais que ces 2 TCD sont differents l'un de l'autre.
Je n'ai et n'avais aucune intention d'ouvrir des fils identiques en parallèle avec toute sincérité.
Merci encore
Bien amicalement
KIM

 

[Dan]

Kim,

Pas de problème, je suis sûr que cela partait d'une bonne intention.

Bon, les deux TCD sont identiques d'une certaine manière, d'un coté tu as la somme du montant des commandes par Projets et dans Lien supprimé tu as les X. Donc tu pourrais les utiliser ensemble finalement.

Si tu regardes les lignes et colonnes du TCD, tu verras qu'elles sont identiques. Les données viennent soit des DR ou de la colonne CDE.

Soit tu utilises les fonctions SOMMEPROD et autres, et tu adaptes chaque fois en fonction de ta base de données ou, tu utilises le TCD qui, dès qu’il est figé, te permet directement d’avoir le résultat. C'est un choix que tu dois faire et dans ce cas, il est nécessaire de bien organiser la base de données qui sert à sa construction.

C'est la raison de mes questions tout au long de ce fil.





Message édité par: Dan, à: 24/10/2005 17:12

 

[KIM]

Bonjour Dan et le forum,
Mon projet arrive à sa fin, mon tableau TDB est construit avec les formules SOMMEPROD (pour le moment car je ne maitrise pas les TCD, ce sera pour le prochain).
La methode que j'utilise est tres simple: Toutes mes formules pour la construction du TDB se trouvent dans la col 2 de le feuille TDB de K2:BS2 et je les recopie par macro de K8 à BS308.
Dans le TDB je n'ai que les resultats des formules et mon TDB est propre.
ET voilà la surprise, quand j'ai intégré toutes mes données dans les feuilles CDE et FAC, je me retrouve avec 40 000 lignes par feuille et 300 lignes pour le TDB.
EXCEL bloque et ne fait plus rien. Mon PC est un P4 3GHZ avec 512Mo de memoire.
Je fais appel à toi Dan et au Forum pour améliorer ma macro par exemple en utilisant des tableaux en memoire ou ... que je ne maitrise pas.

Dan, Faut-il ouvrir un autre fil ou je reste sur le meme?

En attendant j'ai découpé ma macro en 3 (voir fichier joint), Excel ne bloque plus mais il me faut plus de 10mns de calcul.

Merci d'avance
Bien amicalement
KIM
[file name=prKIM51.zip size=17976]http://www.excel-downloads.com/components/com_simpleboard/uploaded/files/prKIM51.zip[/file]

 

[Dan]

Bonsoir Kim,

Non, c'est mieux de rester sur le même fil.

40000 lignes par feuille, c'est énorme cela...ta RAM est complètement saturée là !!
Faudrait déjà essayer de travailler avec des noms pour toutes tes formules.

Je vais regarder ton fichier dès que je peux.

 

[Dan]

Bonsoir Kim,

Je viens de regarder un peu ta macro. Mais là je ne comprends absolument pas ce que tu veux faire.
Un extrait de la macro TDB 1 ci-dessous :

Range('K2:BS2').Select -> tu sélectionnes la plage K2 BS2
Application.CutCopyMode = False -> correspond à ESC - cette instruction est à supprimer
Selection.Copy -> tu copies la plage. Ici tu en fais quoi de cette copie ?
Range('K8:K' & n_line).Select -> tu sélectionnes la colonne K à partir de K8 + n_line
ActiveSheet.Paste -> tu colles la plage K2 BS2 ?
Application.CutCopyMode = False -> correspond à ESC - cette intruction est à supprimer
Selection.Copy -> tu copies quoi ici ?
Selection.PasteSpecial Paste:=xlFormats -> tu colles les formats de quoi ?
Selection.PasteSpecial Paste:=xlValues -> tu colles les valeurs de quoi ?

Comme tu le vois, il y a des choses incompréhensibles.

Je ne sais pas si j'arriverai à t'aider mais peux-tu déjà m'expliquer pas à pas ce que tu fais dans cette macro.

Merci d'avance

Message édité par: Dan, à: 28/10/2005 08:56

 

[KIM]

Bonjour Dan et le forum,
Merci d'avance
1- Je travaille avec des noms dans les formules sommeprod. Toutes les colonnes utilisées dans le calcul sont nommées en utilisant la fonction DECALER
Exemple:
cdemnt1= =DECALER(CDE!$L$5;;;NBVAL(CDE!$A:$A)-1)
2- de la macro
Ci-joint un fichier avec la macro à exucuter:
la plage des formules: H2:M2
le TDB à remplir :H8:M30 selon les données des col A

Merci bEAUCOUP Dan. Effectivement 512 Mo de memoire n'est plus suffisant et je dois trouver une sol à ce blocage d'excel.
Bien amicalement
KIM
[file name=prKIM113.zip size=26020]http://www.excel-downloads.com/components/com_simpleboard/uploaded/files/prKIM113.zip[/file]

 

[Dan]

Bonsoir Kim,

Rempalce ta macro par celle ci-dessous :

Sub TDB_V1()

Dim w_nfile As String
Dim n_line As Byte
    With Application
        .ScreenUpdating = False
        .Calculation = xlCalculationAutomatic
    End With
    w_nfile = ActiveWorkbook.Name
    MyPath = ActiveWorkbook.Path
    Sheets('PJ1').Activate
    If Worksheets('PJ1').AutoFilterMode Then
    Selection.AutoFilter
    End If
    Windows(w_nfile).Activate
    n_line = 30
    Range('H8:M' & n_line).Select
    Selection.ClearContents

    Range('H2:M2').copy
    ActiveSheet.Paste Destination:=Range('H8:H' & n_line)
    With Selection
         .Copy
         .PasteSpecial Paste:=xlFormats
         .PasteSpecial Paste:=xlValues
    End With
    Range('G2').Select
     With Application
        .CutCopyMode = False
        .DisplayAlerts = False
        .ScreenUpdating = True
    End With
    MsgBox 'c'est fini'
End Sub

J'ai apporté quelques changements qui rendent plus clair à lire mais aussi ta macro plus rapide je pense.
Fais un essai chez toi.



Message édité par: dan, à: 29/10/2005 00:15

 

[KIM]

Bonjour Dan et le forum,
J'ai testé ta macro, elle fonctionne comme la mienne sur des fichiers de taille normale. Je l'ai testé sur mon fichier de donnéées enorme, Excel reste bloquer : 'Ce programme ne repond pas'
Merci d'avance s'il y a moyen d'optimiser ce code
Bien amicalement
KIM

 

[Dan]

Bonsoir Kim,

Essaie de faire défiler ta macro pas à pas via la touche F8.
Pour ce faire va dans VBA, place ton curseur sur la macro puis appuie sur F8 pour faire défiler la macro et voir où cela bloque.

D'autre part, combien as tu de lignes dans lesquelles tu recopies tes formules ? cela correspond à la variable n_line.

Le pb vient certainement de là non ?

 

[KIM]

Bonsoir Dan et le forum,
Merci de ta disponibilité, j'espère trouver une solution car je dois rendre rapidement le Tableau de Bord, consolidation des commandes et des Factures et le reste à facturé.
Je ne suis pas devant mon ordi, je ferai le test demandé des que possible.
Pour repondre à ta question, la variable n_line est à 217 et ne depassera pas 300.
J'ai commandé 512Mo supplementaire de RAM, mais il faut plus d'une semaine pour la recevoir.
Merci de ton aide et de celle du forum
Bien amicalement
KIM

 

[KIM]

Re,
Dan, je viens d'executer la macro pas à pas,
elle s'execute normalement jusqu'à la ligne
Range('K2:BS2').Copy
elle se bloque au niveau de la ligne suivante:
ActiveSheet.Paste Destination:=Range('K8:K' & n_line)
n_line a bien la valeur 217

Sinon je pourrai envisager un classeur par DEP (20 classeurs) avec chacun 2 feuilles CMD et FAC et creer un classeur de consolidation avec une feuille de TDB mais je ne sais pas faire. comment appliquer les formules sommeprod sur plusieurs classeurs fermés se trouvant dans un seul repertoire? les formules risquent d'etre tres longues et depassent le nombre de car autorisé.

Bien Amicalement
KIM

 

[Dan]

Re,

Pour voir si cela bloque, essaie de remplacer le code

ActiveSheet.Paste Destination:=Range('H8:H' & n_line)

par

ActiveSheet.Paste Destination:=Range('H8:M8')

Par ailleurs tu parles de K2:BS2, dans ta macro je vois K2:M2, c'est différent ?

 

[KIM]

Re,
Range('H8:H' & n_line) et Range('H8:M8') correspondent au fichier exemple envoyé sur ce fil: File name: prKIM113.zip
La macro fonctionne dans ce fichier.
Je l'ai adapté à mon fichier de travail et la plage des formules est:
K2:BS2 et non plus K2:M2
n_line=217
et la plage du tableau de bors n'est plus Range('H8:M8')
mais Range ('H8:BS217)
et les feuilles CMD et FAC font plus de 40 000 lignes
Dans ce fichier de travail la macro se bloque au niveau de:
ActiveSheet.Paste Destination:=Range('K8:K' & n_line)

Sub TDB_V1()

Dim w_nfile As String
Dim n_line As Byte
With Application
.ScreenUpdating = False
.Calculation = xlCalculationAutomatic
End With
w_nfile = ActiveWorkbook.Name
MyPath = ActiveWorkbook.Path
Sheets('PJ1').Activate
If Worksheets('PJ1').AutoFilterMode Then
Selection.AutoFilter
End If
Windows(w_nfile).Activate
' n_line = 30
' Range('H8:M' & n_line).Select
n_line = ActiveWorkbook.Sheets('PJ1').Range('F2')
Range('K8:BS' & n_line).Select
Selection.ClearContents

' Range('H2:M2').Copy
' ActiveSheet.Paste Destination:=Range('H8:H' & n_line)
Range('K2:BS2').Copy
' ActiveSheet.Paste Destination:=Range('K8:K' & n_line)
ActiveSheet.Paste Destination:=Range('K8:BS217')

With Selection
.Copy
.PasteSpecial Paste:=xlFormats
.PasteSpecial Paste:=xlValues
End With
Range('G2').Select
With Application
.CutCopyMode = False
.DisplayAlerts = False
.ScreenUpdating = True
End With
MsgBox 'c'est fini'
End Sub


Je viens de faire le test en remplacant Range('K8:K' & n_line)
par Range('K8:BS217')
meme blocage au niveau de:
ActiveSheet.Paste Destination:=Range('K8:BS217')
aucun changement
qu'en penses-tu?
KIM

 

[Dan]

Re,

Oui mais j'aurais voulu que tu essaies en faisant varier la plage.
Voilà pourquoi je te demandais d'abord H8:M8

Essaie en reduisant progressivement ta plage pour voir à quel moment cela bloque.

Par exemple : H8:AA8, H8:BA8, etc..



Edition :

Mets ceci aussi :

Range('K2:BS2').Copy Destination:=Range('K2:BS217')

à la place de

Range('K2:BS2').Copy 
ActiveSheet.Paste Destination:=Range('K8:BS217')

Message édité par: Dan, à: 30/10/2005 23:52

 

[KIM]

Re,
J'ai decoupe le traitement en 2 phases:
Phase 1:
Range('K2:AP2').Copy
ActiveSheet.Paste Destination:=Range('K8:K' & n_line)
Phase 2
Range('AQ2:BS2').Copy
ActiveSheet.Paste Destination:=Range('AQ8:AQ' & n_line)
la macro s'est bien déroulée mais avec un temps d'execution de 1/2h par phase.
Si je laisse dérouler la macro normalement en une phase, l'ordinateur se mettra en veille et l'execution ne sera pas terminée. Je pense que c'est une question de capacité.
2 solutions:
1- decoupage de la macro en plusiers phases (OK, ça fonctionne mais ave un temps enorme par phase)
2- Modifier completement la macro avec une autre analyse (optimisation par une autre approche d'analyse) mais quoi, je ne sais pas.

Merci d'avance si tu as d'autres propositions
Bien amicalement
KIM