Optimiser le temps de calcul sur un gros fichier

[MatMat86]

Bonjour,

Je prépare actuellement un classeur permettant d'analyser de manière automatique des données recueillies par un enregistreur: celui-ci me donne un débit par minute, le tout sur un mois (40300 enregistrement donc). Mon premier problème est de compiler ces enregistrements sous forme de données horaires.

Feuille 1:

A / B / C / D

1 Date / De / à / Débit
2 02/04/2010 / 22:35 22:36 / 150
3 02/04/2010 / 22:36 22:35 / 120

Feuille 2:

A / B / C / D

1 Date / De / à / Débit
2 02/04/2010 / 22:00 23:00 / ?
3 02/04/2010 / 23:00 / 00:00 / ?

Pour compléter les "?", j'utilise la formule suivante:

=SOMMEPROD((Feuil1!$A$2:$A$40300=Feuil2!A2)*(Feuil1!$B$2:$B$40300>=Feuil2!B2)*(Feuil1!$B$2:$B$40300<Feuil2!C2)*(Données!$D$2:$D$40300))/60

Le problème est que le calcul sur les 40300 points prend un temps fou, surtout que je dois rendre ce fichier utilisable sur des pc moins puissants que le mien (et sur Excel 2003).

Y'a t'il une solution plus appropriée, ou le nombre de calculs est-il définitivement trop important?


Merci d'avance pour votre aide.

Mat

 

[pierrejean]

Re : Optimiser le temps de calcul sur un gros fichier

Bonjour MatMat86

Et bienvenue sur XLD

Il est peut etre possible de faire quelque chose mais un fichier exemple avec une ciquantaine de lignes nous serait bien utile

 

[MatMat86]

Re : Optimiser le temps de calcul sur un gros fichier

Bonjour Pierrejean,

En cherchant à réduire la taille de mon fichier pour le poster, j'ai découvert un problème d'origine inconnue, qui augmente considérablement la taille de mon fichier (7 Mo -> 19 Mo). Je vous envoie un exemple avez le nouveau fichier "light. Par curiosité, qu'est ce qui peut provoquer une telle augmentation de taille?

Merci

 

[MatMat86]

Re : Optimiser le temps de calcul sur un gros fichier

Petite erreur dans le fichier joint, dans la feuille 2, les formules pour le débit et la pluie sont des formules matricielles.

 

[pierrejean]

Re : Optimiser le temps de calcul sur un gros fichier

Re

Un poil surbooké aujourd'hui
Serais-tu OK pour un calcul par Vba ?

 

[MatMat86]

Re : Optimiser le temps de calcul sur un gros fichier

Pourquoi pas, mais je n'y connais rien du tout! Mais je ne demande qu'à apprendre ^^!

 

[pierrejean]

Re : Optimiser le temps de calcul sur un gros fichier

Re

un essai
Dans le fichier ci-joint ,les formules des colonnes D et E de la feuille Bilan horaire sont effacées a la fermeture du fichier et réecrites a l'ouverture
Pour tester l'efficacité tu rempliras simplement la feuille Données puis tu fermes et le rouvre.La taille du fichier devrait s'en ressentir
Pour la transmission sur d'autres PC , tu peux faire sur place (colonnes D et E) un copier/coller Special Valeurs uniquement

 

[MatMat86]

Re : Optimiser le temps de calcul sur un gros fichier

Re

Merci beaucoup! Je viens de le tester, j'ai une erreur 1004 (Autofill de la classe Range). J'ai ajouté un un 9 dans la destination et le bug n'apparait plus, mais encore une fois, comme je n'y connais rien, ce n'était peut être pas la chose à faire.

'recopie incrementée
Sheets("Bilan horaire").Range("D9:E9").AutoFill Destination:=Range("D9:E9" & Sheets("Bilan horaire").Range("A65536").End(xlUp).Row), Type:=xlFillDefault
End Sub

Par contre, les colonnes D et E de bilan horaire ne se remplissent qu'avec la valeur de la première ligne (pas d'incrémentation de la formule).

 

[pierrejean]

Re : Optimiser le temps de calcul sur un gros fichier

Re

Non cela n'etait pas à faire

Par contre cette erreur peut survenir si sur l'une des lignes la formule provoque une erreur
Verifie en manuel que ce n'est pas le cas

 

[MatMat86]

Re : Optimiser le temps de calcul sur un gros fichier

Non j'ai vérifié. J'ai toujours l'erreur 1004 qui apparait du coup...

 

[pierrejean]

Re : Optimiser le temps de calcul sur un gros fichier

Re

je ne comprends vraiment pas !!
J'ai testé sous Excel 2000 et Excel 2007 et tout se passe bien
Que se passe-t-il si tu mets cette ligne en commentaire ?
Avant la fermeture du fichier (demande si sauvegarde) les colonnes D et E de Bilan horaire doivent s'effacer. Sauvegarder bien sur .Verifier si en D9 et E9 les formules sont correctes et tester l'AutoFill en manuel

 

[MatMat86]

Re : Optimiser le temps de calcul sur un gros fichier

Quand je ferme le fichier, les colonnes D et E s'effacent bien. Quand je relance le fichier, le message d'erreur apparaît, et le débogage me surligne:

Sheets("Bilan horaire").Range("D9:E9").AutoFill Destination:=Range("D9:E" & Sheets("Bilan horaire").Range("A65536").End(xlUp).Row), Type:=xlFillDefault

La formule affichée en D est:

=SOMMEPROD((Données!$A$9:$A$9=A9)*(Données!$B$9:$B$9>=B9)*(Données!$B$9:$B$9<C9)*(Données!$D$9:$D$9))/60

(donc à priori il ne selectionne pas toutes les données?)

 

[pierrejean]

Re : Optimiser le temps de calcul sur un gros fichier

Re
Je viens d'avoir le problème et je crois avoir la solution
Teste cette version

 

[MatMat86]

Re : Optimiser le temps de calcul sur un gros fichier

Re

Merci beaucoup, ça fonctionne parfaitement. Malheureusement, je crois que je me suis mal exprimé, car cette méthode utilise toujours la formule SOMMEPROD matricielle, que je souhaiterais remplacer par une autre moins gourmande en calculs. J'avais pensé à utiliser MOYENNE, mais je ne sais pas si il est possible de faire en sorte d'incrémenter avec un pas de 60. Je m'explique:

Dans la feuille bilan horaire, dans les colonnes D et E, faire respectivement la moyenne d'une partie de la colonne D et E de la feuille Données:

en D9: =MOYENNE(Données!D9: D68)
en D10: =MOYENNE(Données!D69: D128)
...etc (sur 680 lignes)

Le faire à la main me paraît un peu fastidieux, mais je n'arrive pas à trouver un moyen d'incrémenter Bilan horaire!D9 de manière à ce que la plage de données soit à chaque fois décalée de 60 lignes...

Merci beaucoup de m'aider dans ma tâche!

 

[pierrejean]

Re : Optimiser le temps de calcul sur un gros fichier

Re

C'est moi qui avais mal compris
J'ai cru que le problème essentiel etait le poids du fichier
Voici un version avec un bouton en page Bilan horaire qui remplira les colonnes D et E avec les formules type Moyenne