XL 2013 Optimisation formule

[jpdeekay]

Bonsoir à tous,

Nouveau sur le forum, j'arrive en général à glâner les informations dans des problèmes déjà résolus par certains. Mon problème actuel est le suivant :

J'ai créer un workbook pour le traitement d'un rapport qui comporte plusieurs centaines de millers de lignes. Mon problème est que toute manipulation sur le rapport m'impose un temps de traitement de 5 à 10 minutes. Quelqu'un pourrait-il y jeter un oeil et me dire comment optimiser les formules ? Voir peut-être me suggerer une solution alternative ? Je poste en pièce-jointe le workbook qui traite les données. (que j'ai bien sûr reduit à +/- 2000 lignes)

Explication du traitement :

Chaques ligne correspondont à des mesure sur un temps d'une heure pour un objet (D)
Colonne B -> K : données bruts.
Colonne L : simple addition pour traitement ultérieur
Colonne M : Countif -> compte les fois ou pour chaque ID (D), la valeur en L:L est supérieure à une valeur (U5)
Colonne N : Countif -> compte les fois ou pour chaques ID (D), la valeur en K:K est supérieure à une valeur (U3)
Colonne O : IF/AND -> indique YES si pour un ID (D), le nombre d'itération en M:M est supérieur à U6
Colonne P : Indique si O = YES, la moyenne pour chaque ID (D) des valeurs en L:L si elle sont plus grandes que U5
Colonne Q : IF/AND -> indique YES si pour un ID (D), le nombre d'itération en N:N est supérieur à U4

Voilà, j'espère que quelqu'un comprendra ma logique et trouvera des racourcis ou une methode plus efficace

Merci d'avance

JP

 

[Dugenou]

Bonjour,
Oui c'est certain que les countif sur 200 K lignes et le moyenne.si.ens vous consomment du temps de calcul !!
Je ne comprends pas la finalité ni l'objet du calcul, Je ne peux que vous recommander de calculer les résultats ligne par ligne pour faire des somme et des moyennes à la fin ou bien retraiter les données avec un TCD ou powerquery quitte a faire des colonnes avec un calcul intermédiaire mais par ligne.
Sinon il y a la solution "du bourrin" : vous ne mettez les calculs que dans la première ligne (les autres sont vides) vous avez ainsi un temps de calcul diminué pour toutes vos saisies. Ensuite quand il faut calculer vous recopiez vos formules vers le bas et vous allez prendre un café.

Cordialement

 

[jpdeekay]

Bonjour,

Merci de votre réponse.

Ce rapport contient des mesures d'une heure sur X jours pour un objet (D) Il y a une mesure entre l'@ IP local (E) et peer (G). On mesure le pourcentage de perte à l'aller et au retour (H et I) le temps de parcours moyen (J) et le temps de parcours max (K).Ce traitement à pour but de créér une alerte si pour un objet, on dépasse les valeurs renseignées en U3-U6.

Concernant les TCD (pivot ?) cela ne me permet pas de manipuler (champs filtre) les valeurs comme je le souhaite (IF>, MAXIF et AVGIF) ou du moins pas à ma connaissance.

Pour Power query, cela s'attaquerait plus à la mise en forme qu'au traitement des données non ?

Merci

JP

 

[Dugenou]

Bonjour,
Il me semble qu'on pourrait calculer l'écart ligne par ligne et ensuite seulement faire un TCD qui permettrait de filtrer toutes les valeurs qui dépassent la norme ou faire la moyenne des écarts sur une adjacent note id. Quand à PowerQuery je crois que ça fait plus que de la mise en forme. Il faudrait que @chris jette un oeil.

Cordialement

 

[chris]

Bonjour à tous

...Quand à PowerQuery je crois que ça fait plus que de la mise en forme.

Oui c'est un requêteur qui peut donc traiter des grosses tables et faire aussi certains calculs

Déjà tu pourrais mettre sous forme de tableau structuré car dans certaines formules comme
MOYENNE.SI.ENS(L:L;D...
tu traite inutilement 1million de lignes

Je regarde plutôt en soirée côté PowerQuery

 

[chris]

Re

Bon déjà quel intérêt de =SIERREUR(H4+I4;SIERREUR(MAX(H4;I4);0))
l'erreur n'existe que si valeur non numérique et sinon si une des valeur vaut 0, on a forcement le max des 2 donc pas compris

Si je comprends bien, dans ton exemple aucun ligne ne dépasse les seuils

J'ai donc changé ceux-ci pour modéliser : le résultat est-il celui à atteindre ?

 

[mapomme]

=SIERREUR(H4+I4;SIERREUR(MAX(H4;I4);0))

A mon avis la formule peut-être remplacée par =SOMME(H4:I4)

 

[chris]

Re

A mon avis la formule peut-être remplacée par =SOMME(H4:I4)

C'est aussi mon avis et ce que j'ai utilisé dans PQ...

 

[jpdeekay]

Merci à tous pour vos réponses.

J'utilise cette formule car dans certain cas, il n'y a pas de valeur numerique dans ces cellules (N/A), cette formule me permet d'eviter un retour d'erreur. Je me suis mis sur Excel au mois de mars et ce workbook est un condensé brouillon de ce que j'ai pu apprendre...

Effectivement dans mon exemple aucune valeurs ne dépasse, j'ai beaucoup réduits pour permettre l'upload.

En parrallèle, suivant vos recommandations, j'ai utilisé une autre méthode. Je filtre d'abord les valeurs dépassant le seuil, ce qui me réduit à quelque miliers de lignes. Ensuite j'ai modifié mon tableau en en remplacant les COUNTIFS avec COUNTIF (juste pour les itérations). (et désolé pour les termes Anglais, je ne les connais pas en FR)

Cela fonctionne beaucoup plus vite effectivement, mais cela demande de la manipulation et je dois faire cela pour plusieurs fichiers/critères.

Je pense me débrouiller comme ça pour le moment et me pencher sur l'apprentissage du VBA ou autre code histoire d'automatiser le traitement des données.

Merci pour vos recommandations, et le temps passé.

Salutations,

JP

 

[mapomme]

J'utilise cette formule car dans certain cas, il n'y a pas de valeur numerique dans ces cellules (N/A)

D'après ton fichier, nous ne pouvions pas savoir que les colonne H et I pouvaient contenir des valeurs d'erreurs du type #N/A.