Optimisation mise en forme conditionnelle

Bonjour,

J'ai lu tous les posts du forum sur la mise en forme conditionnelle! (je plaisante: la recherche m'en retourne 5000+ 8) )

Bon, plus sérieusement je cherche des conseils pour optimiser un fichier que j'ai réalisé dans le cadre de mon travail et, de manière plus générale, apprendre les bonnes pratiques pour créer des outils performants.

En deux mots: je suspecte que la lenteur est principalement dûe aux 10 règles de mise en forme conditionnelle qui s'appliquent sur mon tableau 365x80.. mais je pourrais me tromper (j'ai sur d'autres onglets du sommeprod, des formules {} (le nom m'échape) du indirect etc..)

Le plus simple est-il que je poste mon fichier? (Je pourrai le mettre en PJ au besoin mais il faudra que je l'allège et retire les infos confidentielles)

Dans le doute, je tente une description du fichier ci-dessous, peut-être que ma description en fera crier quelques uns qui y découvriront une énormité en terme de choix technique

Merci d'avance,

Ze

Concrètement: j'ai un planning sur 365j pour 80 personnes, donc 365*80 cases dans lesquelles je vais mettre une abréviation indiquant ce que fait la personne le jour en question.

Chaque activité est liée à un chef de projet et j'ai associé une couleur à chacun d'eux.

J'ai un petit tableau (*) qui liste pour chaque chef de projet les abréviations qui lui sont associées et j'ai créé une règle de mise en forme conditionnelle par chef de projet qui compare la valeur de la case du planning aux abréviations listées pour le chef de projet donné.

Ainsi lorsque j'entre une abréviation dans mon planning, la case prend la couleur du chef de projet pour lequel travaillera la personne ce jour-là.

Seul hic: il s'écoule une ou deux secondes entre l'appui de la touche Entrée et la coloration de la case et c'est ce délai que je cherche à réduire.

(*): le petit tableau lui-même est un synthèse d'un autre qui n'est pas ordonné. Et comme l'utilisateur final est susceptible de rajouter des lignes et d'en laisser des vides, j'ai également défini des noms par le biaias de formules avec des offset afin de faire sauter les cases vides.

L'idée était pour moi de soulager excel en ne lui faisant scanner que le nombre de cases minimum, mais d'un autre côté l'appele du nom lui fait effectuer une opération à chaque fois ce qui ne serait pas le cas s'il était codé en dur.

J'ai aussi des formules qui comptent dans mon planning combien de jours sont passés sur telle ou telle activité (à coup de sommeprod) mais j'ai le sentiment (totalement subjectif) que ces calculs sont légers pour excel en comparaison de ma coloration.

Bonjour,

Difficile de dire comme ça.

Envoie ton fichier. Si trop volumineux passer par cjoint et nous communiquer le lien.

Inutile de trop te casser la tête pour anonymiser : Enlève les N° de tél et les adresses ça suffit bien (Eventuellement le nom et les coordonnées de l'entreprise...)

A+

Finalement il est moins lourd que ce que je craignais (je ne joue pas encore dans la cour des grands

Bonjour,

Ben pour moi, ce sont bien tes formules qui pourraient alourdir le temps de traitement du fichier.

Les mises en formes me semblent négligeable dans ce cas.

Tu peux gagner une bricole en remplaçant la formule H3 (et suivantes) de "Projects Overview" par

=NB.SI(Planning;B3)

Pour le reste... Les SOMMEPROD INDEX EQUIV PETITEVALEUR ne sont que des formules gourmandes !

Cependant il faudrait en dire plus sur l'opération qui prend 2 secondes chez toi :

Sur mon portable aucune intervention ne demande plus d'une fraction de seconde d'attente.

Je n'ai pas analysé plus que cela les relations entre les différentes feuilles mais il ne me semble pas au vu de ce que tu nous transmets que la première feuille intervienne directement dans le planning ?

Si la perte de temps est importante il y aurait peut-être intérêt à utiliser des formules masquées comme j'ai fait dans (O7:Z27) et d'utiliser cette formule dans une macro Activate et(/ou) Calculate pour cette feuille et tu figes tes résultats en dur...

Tu fais la même chose pour les SOMMEPROD (colonne B)et les SI(OU... (Colonne C-N) et tu seras plus embêté.

Moi personnellement je ne perd plus mon temps à écrire des formules quand je peux faire autrement...

Pour le reste je ne vois rien de spécial à dire.

A+

Waoh, il y a matière à réflexion.

Je vais regarder ça dans les jours qui viennent (week-end en famille donc ça risque d'avancer lentement ) et je te tiendrai au courant de mes avancées.

Merci d'avoir pris le temps de regarder et pour les quelques pistes que tu donnes.

Juste pour répondre aux deux questions que tu évoques:

• la durée est de 1-2sec entre l'entrée d'un acronyme dans le planning et la coloration de la case, probablement parce que les ordis d'entreprise ne sont pas des bêtes de course
• la première feuille intervient dans le planning: elle sert (uniquement) à l'utilisation de la coloration conditionnelle. Est-ce que je gagnerai du temps si le contenu de la première feuille était planqué dans la même feuille que le planning ou serait-ce négligeable?

Merci encore en tout cas. Cela faisait des jours que je cherchais des pistes pour avancer un peu!

[Edit]: pour le INDIRECT en H3 c'est parce que l'utilisateur final utilise les filtres et si je mets B3, lorsque la case change de position du fait d'un classement par ordre alphabetique, et se retrouve par exemple en H27 je veux qu'elle vise B27 et non plus B3

Re,

Finalement la curiosité a été la plus grande, j'ai donc pris le temps de jeter un oeil.

Donc:

le sommeprod etc.. des colonnes cachées dans mon premier onglet me servent à classer les résultats, en effet si on utilise les filtres du 3ème onglet et qu'on perd l'ordre alphabétique, le tableau de l'onglet 1 ne présente plus les acronymes dans l'ordre et sans "blancs".

Est-ce que ma méthode est très lourde?

Concernant ta formule masquée (je me suis bien graté la tête 2-3 minutes avant de comprendre l'astuce ^^) est-ce que ça apporte quelque chose en terme de performance tel que? Si oui, pourquoi/comment?

Ou est-ce que ça sert uniquement avec la macro dont tu parles ie. je désactive le calcul automatique et place un bouton sur lequel l'utilisateur peut cliquer pour "rafraîchir" le tableau? (c'était une piste que j'avais envisagé, mais comme j'étais persuadé que c'était ma mise en forme conditionnelle qui pêchait le plus je voulais commencer par ça)

Enfin, ta remarque "je ne perd plus mon temps à écrire des formules quand je peux faire autrement" me laisse perplexe? Que fais-tu et que ne fais-tu pas (ou plus) ?

En attendant j'ai testé sur mon macbook (2,8GHz 16G de RAM, il n'est pas bien vieux encore) et j'ai bien 1/2 seconde d'attente quand j'entre un acronyme sur le planning avant que la coloration ne s'effectue.

Bonsoir,

Mon temps de calcul est effectivement négligeable une fraction de seconde comme sur ton Mac, donc il faut chercher ailleurs l'inertie sur les bécanes d'entreprise. (Peut-être un problème de partage des ressources avec des taches de fond ?)

J'ai même essayé en compliquant un peu la chose et en faisant un collage spécial de plusieurs valeurs distinctes, le temps de calcul est toujours non significatif.

Aucun intérêt à rapatrier le tableau des couleurs dans la même feuille, Une feuille claire et bien structurée est cent fois préférable à une usine à gaz alambiquée. Sur ce plan la conception de ta feuille 1 et des autres, c'est pas mal du tout.

J'aurai sans doute pas fait comme ça, mais comme je ne cerne pas forcément les tenants et aboutissants de ta conception, on se contentera de ça. C'est de toute façon déjà exemplaire, après on rentre dans le chipotage...

Est-ce que ma méthode est très lourde?

Je ne sais pas, j'ai un peu de mal à évaluer la finalité de cette gymnastique de filtre et le sommeprod.

De toute façon il faut se dire que "in fine" la meilleure méthode est celle qu'on maîtrise le mieux... avec le minimum d'embarras.

Les formules masquées...

Une formule masquée ou non reste une formule avec tous ses inconvénients, le masquage n'a qu'un intérêt évident : C'est plus facile à coder en VBA et plus facile à maintenir...

Entre...

ActiveCell.Formula = SLFRM

et...

ActiveCell.FormulaR1C1 = _
        "=IFERROR(INDEX(Acronymes,MATCH(SMALL(R7C[-12]:R106C[-12],ROW()-ROW(R6C)),R7C[-12]:R106C[-12],0)),"""")"

YAPA photo !

De plus au lieu de confier le boulot au compilateur, tu lui fais sous-traiter TOUSSA en mémoire vive, c'est tout benef. Enfin moi je vois ça comme ça, je me trompe peut-être... Je ne suis pas un pro : just a bricolo !

Le gain en Ko ou en temps de traitement est probablement ténu. Je ne me suis jamais amusé à le mesurer. Mais quand je fais un lifting, je commence toujours par là. Surtout sur de très grands tableaux.

Que fais-tu et que ne fais-tu pas (ou plus) ?

J'évite de mettre des formules. Sauf pour les additions simples...

1. Parce que je ne suis pas extrêmement doué pour ce sport !

2. Parce que c'est quand même très gourmand en ressources...

Un classeur de 150 ko est quand même plus cool à manipuler que le même version 1,5 Mo... Je n'entrerai pas plus dans les détails, car cette technique suppose une bonne maîtrise de la programmation

Bon. En résumé tu as un bon classeur, à moins de 200 ko avec toutes ces formules (sans parler de la douzaine de colonnes masquées également avec des formules "à la noix" dans "Projects Overview") inutile de chercher la petite bête et de finasser : On est certainement très près d'un minimum absolu.

Bon WE

A+

Ça y est, j'ai trouvé la case "check: résolu" \o/

Merci encore pour le coup de main!