La semaine dernière, je suis tombé sur un problème Excel dans un forum. Je suis immédiatement passé à l'action et j'ai créé une longue formule qui commençait par OFFSET().
En quelques heures, il a été abattu par d'autres experts d'Excel car il contenait des formules volatiles.
J'ai immédiatement reconnu le péché capital que j'avais commis.
Donc, avec cette confession, permettez-moi de partager ce que j'ai appris sur les fonctions volatiles dans Excel. En termes simples, c'est une fonction qui ralentira votre feuille de calcul Excel, car elle recalculera la formule encore et encore. Un certain nombre d'actions peuvent déclencher cela (décrites plus loin dans cet article).
Un exemple très simple de fonction volatile est la fonction NOW() (pour obtenir la date et l'heure actuelles dans une cellule). Chaque fois que vous modifiez une cellule dans une feuille de calcul, elle est recalculée. C'est bien si vous avez un petit ensemble de données et moins de formules, mais lorsque vous avez de grandes feuilles de calcul, cela peut considérablement ralentir le traitement.
Voici une liste de certaines fonctions volatiles courantes, qui devraient être évitées :
Formules super volatiles :
- RAND()
- À PRÉSENT()
- AUJOURD'HUI()
Formules presque volatiles :
- DÉCALAGE()
- CELLULE()
- INDIRECT()
- INFO()
La bonne nouvelle est que mes INDEX(), ROWS() et COLUMNS() préférés ne présentent pas de volatilité. La mauvaise nouvelle est que le formatage conditionnel est volatile
Assurez-vous également que vous n'avez pas ces fonctions dans des fonctions non volatiles, telles que IF(), LARGE(), SUMIFS() et COUNTIFS(), car cela rendrait éventuellement la formule entière volatile.
Par exemple, supposons que vous ayez une formule =Si(A1>B1, « Trump Excel », RAND()). Maintenant, si A1 est supérieur à B1, il renvoie Trump Excel, mais si ce n'est pas le cas, il renvoie RAND(), qui est une fonction volatile.
Déclencheurs qui recalculent les formules volatiles
- Saisie de nouvelles données (si Excel est en mode recalcul automatique).
- Demander explicitement à Excel de recalculer tout ou partie d'un classeur.
- Supprimer ou insérer une ligne ou une colonne.
- Enregistrement d'un classeur pendant que le « Recalculer avant d'enregistrer » l'option est définie (c'est dans Fichier-> Options-> Formule).
- Exécution de certaines actions de filtrage automatique.
- Double-cliquer sur un séparateur de ligne ou de colonne (en mode de calcul automatique).
- Ajouter, modifier ou supprimer un nom défini.
- Renommer une feuille de calcul.
- Modification de la position d'une feuille de calcul par rapport à d'autres feuilles de calcul.
- Masquer ou afficher des lignes, mais pas des colonnes.
Si vous avez beaucoup de formules dans votre feuille de calcul qui la ralentissent, je vous suggère de passer en mode de calcul manuel. Cela arrête le recalcul automatique et vous donne le pouvoir de dire à Excel quand calculer (en cliquant sur « Calculer maintenant » ou en appuyant sur F9). Cette option est disponible dans Formules-> Options de calcul.
Tutoriels associés :
- 10 façons super soignées de nettoyer les données dans les feuilles de calcul Excel.
- 10 conseils de saisie de données Excel que vous ne pouvez pas vous permettre de manquer.
