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

Guide « 108 astuces pour grands débutants sur Excel ! » Offert
Booster votre productivité en téléchargeant gratuitement votre ebook PDF des meilleures astuces pour les débutants et les pros d'Excel
Excel formation - 00 Astuces - 100
 

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 :

This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.
 

 

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 :

Excel formation - identifier cellules dépendantes - 01

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 :

Excel formation - identifier cellules dépendantes - 02

  • 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 :

Excel formation - identifier cellules dépendantes - 03

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 ».

Excel formation - identifier cellules dépendantes - 04

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.

Excel formation - identifier cellules dépendantes - 05

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 » :

Excel formation - identifier cellules dépendantes - 06

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 :

Excel formation - identifier cellules dépendantes - 07

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

 

 



Articles qui pourraient vous intéresser

INDEX-EQUIV : Astuces de pro sur Excel !
Découvrez l'Effaceur d’Excel !
Comment détecter, supprimer ou bloquer les doublons sur Excel ?
La fonctionnalité méconnue pour des prévisions en un clin d’œil sur Excel
Comment changer la couleur d'un graphique en fonction de la valeur dans Excel ?
Comment imprimer les titres de colonnes sur toutes les pages dans Excel ?
TOP3 des (vraies) méthodes pour retirer les chiffres derrière la virgule sur Excel
Comment savoir si une cellule est utilisée dans une autre formule sur Excel ?
Comment faire pour attribuer des couleurs différentes à chaque point dans un graphique Excel ?
Comment ajouter une moyenne mobile sur un graphique Excel ?
Comment rendre vos graphiques Excel plus esthétiques avec des dégradés multicolores ?
Comment créer des listes personnalisées sur Excel en un rien de temps ?

Contact - Plan du site - À propos - Contact

© Excelformation.fr 2018-2024 - Excel, simplement

Haut de la page

Excelformation.fr propose de nombreuses formations pratiques et gratuites sur Microsoft Excel : des cours pour apprendre à structurer et analyser les données, des tutos sur les fonctions avancées, les formules et les formules avancées d'Excel, les formules matricielles, les mise-en-formes conditionnelles, les graphiques Excel (xls, xlsx, xlsm,…). Des tutoriels pour apprendre à automatiser les tâches répétitives (via des macro-commandes en VBA) de vos classeurs Excel.