Lenteur extrême (et aléatoire) des filtres automatiques : formats conditionnels anti doublons

Le
Étienne Chouard
Bonjour à tous :o)



Est-ce qu'il y a une cause connue (ou plusieurs) de

lenteur extrême (et aléatoire, non systématique) des filtres automatiques ?



Je m'explique : dans un gros tableau, une feuille de grande taille
(plusieurs milliers de lignes sur 20 colonnes seulement, presque sans
formule de calcul (sauf des "formats conditionnels" et des "données
validation" complexes), les filtres automatiques fonctionnent de façon
aléatoire :



Aussi bien en les lançant à la main qu'en les lançant par VBA,

- parfois, ils sont fulgurants de vitesse (30 lignes sélectionnées parmi 10
000 en un quart de seconde),

- parfois, ils sont d'une lenteur extrême, au point de tout bloquer : 1
minute 30 pour montrer 2 lignes filtrées.



J'ai déjà essayé :



- recalcul automatique OFF

- rafraîchissement de l'écran OFF

- réduction de la fenêtre pour avoir moins de caractères à afficher lors de
chaque filtre (.)



************************************

J'ai aussi remarqué qu'Excel 2003 est (parfois spectaculairement) plus lent
qu'Excel 2002 ou, autrement dit, que du code VBA qui est inexplicablement
lent sur Excel 2003 peut redevenir absolument fulgurant sous Excel XP
(2002).

************************************



Est-ce que vous auriez connaissance d'autres informations (comme cette
différence de vitesse entre les versions d'Excel) qui permettrait d'accélérer
l'exécution des filtres automatiques ?



Un réglage de la base de registre ?

pour diminuer, par exemple, le nombre de commandes annulables (qui doivent
probablement prendre de la mémoire et du temps).



******



J'ai trouvé (ce matin, en même temps que je vous écris, je continue à
chercher) : le format conditionnel qui signale en rouge d'éventuels doublons
(sur la colonne A, par exemple, la formule est "=NB.SI(A:A;A1=>0"), ce
format conditionnel si astucieux et si pratique, est une cause de lenteur
extrême quand on en abuse et qu'on le fait calculer sur des milliers de
cellules.



*****************************************************************

Est-ce qu'il y a un moyen de bloquer d'une seule commande le recalcul des
formats conditionnels ?



(Il me semblait que le recalcul manuel empêchait le recalcul des formats
conditionnels, mais comme je l'ai dit, mes filtres auto sont lents même en
recalcul OFF alors qu'ils redeviennent fulgurants en supprimant carrément
les formats conditionnels).



Peut-être en mémorisant puis en supprimant le format conditionnel en début
de traitement et en le rétablissant en fin de traitement ?. Mais c'est
lourdingue.

*****************************************************************



Voilà, je crois que j'ai réglé mon problème tout seul :o) , mais :



1) je suis toujours preneur de techniques accélératrices s'il y en a.



2) cette expérience pourra peut-être servir à quelqu'un.



Mille mercis pour tout ce que vous faites.

L'entraide est belle et attachante.



Étienne.
Vidéos High-Tech et Jeu Vidéo
Téléchargements
Vos réponses
Gagnez chaque mois un abonnement Premium avec GNT : Inscrivez-vous !
Trier par : date / pertinence
d.sundow
Le #16659461
Bonjour,

Si la table filtrée comporte dans une colonne une formule (ex. la
colonne C=la colonne A + la colonne B) et que la table est très
importante, les temps de recalcul sont interminables (ce qui est bien
pratique pour aller se tirer un café et frimer devant les collègues).

Si la table filtrée comporte beaucoup de formules très tarabiscottées
qui plus est, alors là, c'est plus de la patience qu'il faut; c'est
des cours de zen pour pas péter un cable.

Donc personnellement je fais un collage plat de la base, soit la manip
suivante sur la table non filtrée :

a) ctrlA (selection de l'entier de la feuille)
b) ctrlC (copie de l'ensemble de la feulle)
c) Edition collage spécial/Valeurs/OK

Les formules ont été nettoyées pour ne conserver que les valeurs des
cellules.

Cordialement,

S.
paulkener Hors ligne
Le #26318936
Le vendredi 29 Août 2008 à 08:38 par Étienne Chouard :
Bonjour à tous :o)



Est-ce qu'il y a une cause connue (ou plusieurs) de

lenteur extrême (et aléatoire, non systématique) des
filtres automatiques ?



Je m'explique : dans un gros tableau, une feuille de grande taille
(plusieurs milliers de lignes sur 20 colonnes seulement, presque sans
formule de calcul (sauf des "formats conditionnels" et des
"données
validation" complexes), les filtres automatiques fonctionnent de
façon
aléatoire :



Aussi bien en les lançant à la main qu'en les lançant par
VBA,

- parfois, ils sont fulgurants de vitesse (30 lignes
sélectionnées parmi 10
000 en un quart de seconde),

- parfois, ils sont d'une lenteur extrême, au point de tout bloquer : 1
minute 30 pour montrer 2 lignes filtrées.



J'ai déjà essayé :



- recalcul automatique OFF

- rafraîchissement de l'écran OFF

- réduction de la fenêtre pour avoir moins de caractères
à afficher lors de
chaque filtre (.)



************************************

J'ai aussi remarqué qu'Excel 2003 est (parfois spectaculairement) plus
lent
qu'Excel 2002 ou, autrement dit, que du code VBA qui est inexplicablement
lent sur Excel 2003 peut redevenir absolument fulgurant sous Excel XP
(2002).

************************************



Est-ce que vous auriez connaissance d'autres informations (comme cette
différence de vitesse entre les versions d'Excel) qui permettrait
d'accélérer
l'exécution des filtres automatiques ?



Un réglage de la base de registre ?

pour diminuer, par exemple, le nombre de commandes annulables (qui doivent
probablement prendre de la mémoire et du temps).



******



J'ai trouvé (ce matin, en même temps que je vous écris, je
continue à
chercher) : le format conditionnel qui signale en rouge d'éventuels
doublons
(sur la colonne A, par exemple, la formule est
"=NB.SI(A:A;A1=>0"), ce
format conditionnel si astucieux et si pratique, est une cause de lenteur
extrême quand on en abuse et qu'on le fait calculer sur des milliers de
cellules.



*****************************************************************

Est-ce qu'il y a un moyen de bloquer d'une seule commande le recalcul des
formats conditionnels ?



(Il me semblait que le recalcul manuel empêchait le recalcul des formats

conditionnels, mais comme je l'ai dit, mes filtres auto sont lents même
en
recalcul OFF alors qu'ils redeviennent fulgurants en supprimant
carrément
les formats conditionnels).



Peut-être en mémorisant puis en supprimant le format conditionnel
en début
de traitement et en le rétablissant en fin de traitement ?. Mais c'est
lourdingue.

*****************************************************************



Voilà, je crois que j'ai réglé mon problème tout
seul :o) , mais :



1) je suis toujours preneur de techniques accélératrices s'il y
en a.



2) cette expérience pourra peut-être servir à quelqu'un.



Mille mercis pour tout ce que vous faites.

L'entraide est belle et attachante.



Étienne.


Bonjour à tous !

J'ai eu le même problème. Mon tableau fait presque 7000 lignes avec plusieurs colonnes contenant des formules 'nb.si'
le pb est que de façon aléatoire, la fonction filtre automatique d'Excel devenait lente, ça ramait un max...
J'en parle au passé car, il me semble avoir trouvé une solution au pb =)
Ce sont bien les formules 'nb.si' qui sont à l'origine de la lenteur car une fois éliminées, le filtre ne rame plus du tt. J'ai d'autres colonnes à formules avec des 'rechercheV' par ex, mais celles là ne posent pas de pb.
Il va falloir modifier les formules 'nb.si' (en gardant le même résultat bien entendu)
en ajoutant les formules 'indirect' et '&ligne'
si la formule de base est:
=nb.si(A:A;A2)
la formule magique est
=nb.si(A:A;indirect("Feuil1!A"&ligne(A2)))

et là, Ô miracle, le tri se fait instantanément !

Bien à vous,

Paul Kener
Publicité
Poster une réponse
Anonyme