Comment savoir si une cellule est utilisée dans une autre formule sur Excel ?

Dans ce tutoriel, je vais vous montrer comment identifier rapidement les cellules utilisées dans une formule Excel, que la dépendance soit directe ou indirecte.

En effet, comprendre quelles cellules contribuent à une formule spécifique est essentiel pour prévenir d'éventuelles erreurs et pour approfondir votre compréhension du fonctionnement de vos calculs.

Alors que la détection des dépendances directes est relativement connue, je vais vous dévoiler une méthode inédite pour repérer également les dépendances indirectes, c’est-à-dire quelles sont les cellules dont les résultats sont influencés par la cellule active, même si elles ne sont pas directement référencées dans la formule de celle-ci.

Téléchargement

Vous pouvez télécharger le fichier d'exemple de cet article en cliquant sur le lien suivant :

Tutoriel Vidéo

1. Présentation

Lorsque nous travaillons sur des feuilles de calcul complexes, il peut parfois être utile de savoir quelles sont les cellules qui font référence à une cellule donnée.

Cela permettra en effet dans un premier temps d’éviter les erreurs de calcul potentielles, en particulier lorsque nous travaillons sur des feuilles contenant un grand nombre de cellules, car si nous modifions l’une de ces cellules sans savoir qu'elle est utilisée dans d'autres formules, nous risquons de perturber l'intégrité de l’ensemble de nos calculs. D’autant plus que ces erreurs peuvent ensuite être difficiles à détecter ce qui peut entraîner des résultats incorrects, des pertes de temps considérables, voire des conséquences financières graves.

Détecter les dépendances entre les cellules permettra également de comprendre le fonctionnement des feuilles de calculs, ce qui facilite la compréhension de la logique de celle-ci, en particulier lorsque nous ne sommes pas directement l’auteur de ces formules.

C’est pour cela qu’Excel dispose directement d’une panoplie d’outils dédiée à la recherche de dépendance et que nous allons découvrir dans ce tutoriel au travers d’un exemple concret.

Nous avons ici un tableau qui contient des données de chiffre d'affaires mensuel sur trois années d’une entreprise :

Les données de ce tableau sont ensuite utilisées à plusieurs endroits dans la feuille de calcul et dans le classeur pour réaliser des calculs.

2. Utilisation de l'Outil de recherche de dépendances

Comme je viens de nous l’expliquer, pour identifier rapidement ces cellules, nous pouvons utiliser l’outil de recherche de dépendances.

Pour cela, nous commençons par sélectionner l’une des cellules du tableau, puis nous nous rendons dans le menu « Formules » du ruban.

Dans le groupe « Vérification des formules », nous retrouvons les différentes options d’analyse de la formule saisie dans la cellule sélectionnée :

• Repérer les Antécédents : Cette option permet de visualiser les cellules dont les données contribuent directement à la formule sélectionnée. Ces cellules sont référencées dans la formule avec des flèches dirigées vers la cellule sélectionnée.
• Repérer les Dépendants : Cette option montre les cellules qui dépendent de la formule sélectionnée. Ces cellules dépendantes de la formule apparaissent avec des flèches dirigées depuis la cellule sélectionnée.
• Supprimer les Flèches : Une fois que nous avons l’une de ces options "Repérer les Antécédents" ou "Repérer les Dépendants" et que nous souhaitons effacer les flèches et les indications visuelles, nous pouvons utiliser cette option pour les supprimer.
• Nous retrouvons également les boutons « Afficher les Formules » qui permet de basculer entre l'affichage des résultats des formules et l'affichage des formules elles-mêmes, « Vérification des Erreurs » qui permet de rechercher des erreurs dans une formule et « Évaluer la Formule » pour suivre étape par étape l'évaluation d'une formule avec les valeurs intermédiaires correspondantes.

Ici, nous allons nous intéresser à l’option « Recherche de dépendances ».

Lorsque nous cliquons sur ce bouton, Excel affiche une série de flèches dans la cellule d'origine :

La flèche bleue indique la ou les cellules dépendantes positionnées directement sur la feuille de calcul dans laquelle se trouve la cellule sélectionnée.

La flèche en pointillée noire affiche quant à elle la ou les cellules positionnées sur une autre feuille de calcul du classeur actif.

Pour accéder aux cellules correspondantes, nous double cliquons simplement sur celle-ci pour qu’Excel affiche la boîte de dialogue « Atteindre ».

Sur cette boîte de dialogue, Excel affiche une liste de cellules correspondantes aux cellules d'origines.

Ensuite, il suffit de double cliquer sur la référence correspondante pour sélectionner la cellule dépendante.

Il faut savoir que cette boîte de dialogue pourra également afficher les cellules dépendantes positionnées sur d’autres feuilles de calcul, à condition que celle-ci soit actuellement ouverte.

Dans le cas contraire, Excel ne pourra pas la détecter, même si la feuille correspondante est enregistrée.

Comme nous l’avons vu un peu plus tôt, si nous souhaitons masquer les flèches, il suffit maintenant de cliquer sur le bouton « Supprimer les Flèches ».

3. Identifier les dépendances indirectes

Les cellules que nous venons d’identifier avec la méthode vue juste avant correspondent aux dépendances directes, c’est-à-dire qu’il s’agit des cellules utilisées directement dans la formule de la cellule sélectionnée.

En revanche, cette méthode ne permet pas d’identifier les dépendances indirectes, c’est-à-dire les cellules qui influencent la formule de la cellule sélectionnée sans être mentionnée directement par celle-ci.

Pour nous en rendre compte, il suffit de modifier l’une des valeurs de la colonne « 2022 » :

Ici, pour forcer le trait nous saisissons un très grand nombre dans les ventes du mois de janvier.

Nous pouvons alors constater que le résultat de la cellule G12 a fortement été impacté par la modification, alors que celle-ci n’est pas identifiée par une flèche bleue.

Il s’agit donc d’une dépendance indirecte.

Pour les identifier, nous allons devoir répéter manuellement cette opération sur chaque cellule identifiée par l’outil.

Si nous souhaitons automatiser cette opération, nous allons pouvoir mettre en place une petite macro VBA.

Pour faire simple, le VBA est un langage de programmation qui de décupler les possibilités offertes de base par les applications de la suite Office, y compris Excel, Word et PowerPoint.

Ici, pour que ce tutoriel soit facilement compréhensible, je vais vous expliquer chaque ligne de cette macro.

Mais avance cela, pour créer une nouvelle macro, nous allons tout d’abord ouvrir l'éditeur VBE (Visual Basic for Applications) en utilisant le raccourci clavier [Alt]+[F11].

L'éditeur VBE est l'environnement de développement dans lequel nous pouvons écrire, éditer et exécuter des macros VBA pour Excel.

Pour créer la macro, il ne reste plus qu’à insérer un nouveau module depuis le menu « Insertion » du ruban.

Cela étant fait, nous allons commencer par saisir la ligne suivante qui déclare une nouvelle macro appelée « dependances », dans laquelle nous insérons un argument nommé « c » que nous typons en tant que « Range », c’est-à-dire en tant que cellule Excel.

Cette procédure sera utilisée pour afficher les dépendances directes de la cellule passée en argument.

Sub dependances(c As Range)

Ensuite, nous déclarons une variable « dependant », elle aussi en tant que « Range », qui va nous permettre de parcourir les cellules dépendantes de la cellule spécifiée en argument.

    Dim dependant As Range

Maintenant, nous allons demander à VBA de continuer l'exécution du code même en cas d'erreur, ce qui permet de gérer les situations où une cellule n'a pas de dépendants directs sans provoquer une interruption du code.

    On Error Resume Next

Puis, nous allons simuler l’appuie sur le bouton « Repérer les Dépendants » que nous avons découvert au début de ce tutoriel en appelant la méthode « ShowDependents » qui permet d’afficher les flèches de dépendance dans la feuille de calcul Excel pour la cellule spécifiée « c ».

Cela met en évidence les cellules qui dépendent directement de la cellule « c ».

    c.ShowDependents

Il ne reste plus qu’à mettre en place une boucle « For Each » pour parcourir toutes les cellules dépendantes directes de la cellule « c », et pour chacune d'entre elles, nous appelons de manière récursive la macro « dependances » que nous sommes en train de mettre en place.

Cela permet d'explorer toutes les dépendances, directes et indirectes, de la cellule spécifiée.

Mais avant de revenir sur cette ligne, je vais vous détailler les deux notions que je viens d’aborder, à savoir ce qu’est une boucle « For Each » et également ce qu’est qu’une macro récursive :

• La boucle « For Each » va nous permettre de parcourir tous les éléments d'une collection ou d'un ensemble d'objets. Ici nous l’utilisons pour parcourir toutes les cellules dépendantes directes de la cellule « c ». Pour chaque élément de la collection, la boucle répète un ensemble d'instructions jusqu’à ce que tous les éléments aient été traités.
• Ensuite, une macro récursive est une macro qui s'appelle elle-même pendant son exécution. Concrètement, la macro commence par exécuter un ensemble d'instructions, et à un certain point de son exécution, elle s'appelle elle-même, en utilisant les arguments appropriés. À ce moment-là, une nouvelle instance de la macro reprend l'exécution à partir du début, avec les nouveaux arguments. Ce processus de récursivité est utilisé pour explorer toutes les dépendances d'une cellule, y compris les dépendances indirectes. Chaque appel récursif traite une nouvelle cellule dépendante directe, permettant ainsi d'obtenir une vue complète des dépendances de la cellule spécifiée c.

    For Each dependant In c.DirectDependents
        dependances dependant
    Next

Attention toutefois, étant donné que la macro « dépendances » attend un argument, nous ne pouvons pas l’appeler directement, et nous allons devoir intercaler une macro intermédiaire de lancement, qui va consister à analyser les dépendances directes et indirectes de la cellule active :

Il ne reste plus qu’à lancer cette macro « dependancesIndirectes » pour obtenir les flèches des cellules dépendantes de tous les niveaux.