Qui n’a jamais connu de fichier Excel aux résultats complétement faux à cause d’erreurs de références dans une formule ? Ce serait ne jamais avoir travaillé sur Excel ! Les erreurs de références constituent en effet :
- La première source d’obsolescence d’un fichier : il suffit qu’une plage de données soit redimensionnée et que ces nouvelles dimensions ne soient pas reprises dans toutes les formules d’y référant…
- L’une des erreurs les plus difficiles à identifier : le résultat peut être correct dans certains cas mais ne pas prendre en compte l’intégralité des valeurs possibles…
- L’une des erreurs les plus pénibles à corriger : vérifier des adresses de cellules est le meilleur moyen de s’arracher les yeux et de perdre beaucoup de temps !
Voici donc 3 moyens simples d’éviter les erreurs de références dans vos formules Excel :
Le plus simple et le rapide pour éviter les erreurs de références : délimiter le bas d’une plage de donnée
Pas très pro mais efficace et simple, il vous suffit de matérialiser explicitement le bas de votre plage de données en utilisant une ligne de cellules dédiées.
Par exemple en tapant « END » (ou « FIN » si vous préférez !), en bas de votre tableau. Mettez en forme (couleur, gras, italique…) et conservez cette mise en forme à chaque fois que vous utiliserez cette méthode dans vos fichiers. Cela vous permettra de vous y retrouver plus facilement et vos collègues qui passeront sur vos fichiers adopteront la même méthode.
Les formules qui feront référence à ces données devront sélectionner la plage allant de la première ligne à la ligne jaune incluse. (cf cadre rouge ci-dessous).
Lorsque votre liste s’allongera, veillez à bien insérer les nouvelles lignes AVANT la ligne jaune. De cette manière, la plage de référence sera directement redimensionnée dans toutes les formules de vos fichiers ouverts.
Idem, si jamais vous devez ajouter une nouvelle colonne, insérez-la avant la dernière colonne pour être certain que les références de vos formules restent à jour. Il faudra juste veiller à mettre à jour le numéro de la colonne visée dans vos formules de type RECHERCHEV.
Bien sûr, il faut que le contenu éventuel de la dernière ligne soit compatible avec les formules : ne pas y mettre de texte si vous cherchez à faire un calcul utilisant toutes les valeurs des cellules. Dans ce cas-là, il faudra privilégier les deux autres solutions que je préconise plus bas. Cette technique vaudra donc surtout pour les tables de correspondance entre des données, destinée par exemple à des RECHERCHEV.
Nommer la plage de cellules pour sécuriser les références de vos formules
Evitez de vous crever les yeux avec des formules à rallonge mélangeant chiffres, lettres, dollars, parenthèses et autres ponctuations ! Lorsque vous faites fréquemment référence à une même plage de données, autant lui donner un nom.
Ceci vous permettra :
- De simplifier la rédaction et la relecture de vos formules. Avouez que « Pays » est plus explicite que « Feuil1!$B$3:$D$16 » !
- De vous assurer que toutes les formules y faisant référence reprendront bien les mêmes données : adieu la formule ou l’onglet non mis à jour qui exclut les trois dernières lignes que vous venez de rajouter…
Si vos formules intègrent le nom de la plage de données, il suffit de vérifier celui-ci une bonne fois pour toute ! Un gain de temps énorme, dans le maintien de vos fichiers comme dans l’élimination des erreurs !
Comment faire ?
- Sélectionnez votre plage de données
- Saisissez ensuite un nom de votre choix dans la case ci-dessous :
- Puis enfin tapez « Entrée »
Comment vérifier que votre plage de données nommée est correcte ?
Ouvrez la zone permettant de nommer les cellules et sélectionnez le nom recherché. En effet, les cellules correspondantes seront directement sélectionnées, quel que soit leur emplacement dans votre fichier.
Pour une plage de données de plus grande taille, il suffit de dézoomer : le nom de la plage s’affichera directement sans pour autant l’avoir préalablement sélectionné. Pratique pour vérifier que toutes les cellules voulues en font bien partie !
Comment modifier l’adresse d’une plage nommée ?
Une seule solution pour bien faire ! Ouvrez la boîte de dialogue « Gestionnaire de noms » dans le menu « Formule ».
Tous les noms de plages y apparaissent (précédés d’une petite icône pour les catégoriser). Nous retrouvons bien ici notre plage « Pays » avec un aperçu de ses premières valeurs, son adresse etc…
Pour modifier l’adresse de la plage, il suffit de :
- Sélectionner le nom dont on veut modifier les propriétés,
- puis de modifier l’adresse dans la boîte de dialogue intitulée : « Fait référence à : »
- et enfin de cliquer sur Fermer
La solution idéale pour éliminer les erreurs de références ? Travailler avec les Tableaux
Peu de gens exploite cette fonctionnalité d’Excel… et c’est bien dommage ! Cela dit, tant mieux pour vous si vous les adoptez ! Vous prendrez ainsi une longueur d’avance sur la majorité des utilisateurs !
Pour créer un tableau, osez simplement cliquer sur le bouton ci-contre, dans le menu « Insertion » :
Les Tableaux (voir mon article à ce sujet) sont vraiment LA solution pour éviter les erreurs de références dans les formules et garantir la longévité de vos fichiers.
Pour rappel :
- Chaque tableau à un nom unique dans un fichier,
- et chaque colonne porte le nom de son entête, quel que soit son positionnement dans le tableau.
Une colonne de données est donc définie par l’intitulé de son entête et le nom du tableau dans laquelle elle se trouve. Peu importe ensuite le nom de l’onglet et l’adresse des cellules qu’elle recouvre ! Vous les retrouverez toujours et vos formules tiendront compte de toute les modification que vous apporterez aux noms de tableaux ou d’entêtes.
Je vous encourage donc très vivement à prendre le temps de vous pencher sur les fonctionnalités des tableaux. Prenez une heure ou deux pour en découvrir tout le potentiel après avoir parcouru l’article Passez au tableau ! Ce sera sans doute les plus rentables dans tout votre apprentissage d’Excel !
