Lenteur extrême (et aléatoire) des filtres automatiques : formats conditionnels anti doublons
2 réponses
É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.
Cette action est irreversible, confirmez la suppression du commentaire ?
Signaler le commentaire
Veuillez sélectionner un problème
Nudité
Violence
Harcèlement
Fraude
Vente illégale
Discours haineux
Terrorisme
Autre
d.sundow
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.
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.
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
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
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)))
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)))
