Monsieur Excel
Pour tout savoir faire sur Excel !

10 avril 2011

Sécurisez-vous avec decaler() !

La fonction decaler() est particulièrement utile pour sécuriser les formules en les rendant plus flexibles. Nous en avons un excellent exemple ci-dessous avec le modèle de la Figure 1 : nous avons des valeurs en colonne A et nous calculons en colonnes B et C les pourcentages d’évolution d’une ligne à l’autre.

Formule de B2 : =A2/A1-1
Formule de C2 : =A2/decaler(A2;-1;0)-1

Cas de la suppression d’une ligne

Si par exemple nous détruisons la ligne 7, nous obtenons le résultat de la Figure 2. Pour la colonne C, aucun problème ! En revanche, la colonne B affiche un message d’erreur…

Cas de l’insertion d’une ligne

Si en revanche nous insérons une ligne au-dessus de la ligne 8, nous obtenons le résultat de la Figure 3.

Dès que l’on entre une valeur en A8 (cf. Figure 4), deux choses se passent : la colonne C reçoit automatiquement la bonne formule et elle affiche les bons résultats en C8 et C9.

Dans le cas de la colonne B, si l’on se contente de recopier la formule de B7 en B8, B8 affichera le bon résultat, mais B9 continuera à afficher le faux résultat 9,1% ! Pour éviter ce problème, il faut penser à recopier la formule de B7 jusqu’en B9…

En guise de conclusion…

La formule de la colonne C est plus longue et plus complexe, mais bien plus sûre que celle de la colonne B, que ce soit lors de la suppression ou lors de l’insertion d’une ligne.

De la même façon, quand on effectue la somme d’un bloc avec en ligne 1 des titres et en ligne 5 le total, la formule classique =somme(A2:A4) n’offre aucune élasticité : toute ligne insérée entre la ligne 1 et la ligne 2, ou entre la ligne 4 et la ligne 5 sera ignorée dans le total.

Si en revanche on utilise dans ce cas la formule =somme(A1:decaler(A5;-1;0)), on dispose alors d’une formule totalement élastique qui prend automatiquement en compte les valeurs de lignes insérées juste au-dessus de la ligne 2 ou de la ligne 5.

Comme quoi il faut parfois utiliser des formules plus lourdes pour bénéficier d’un taux de sécurité maximal !