Comment une table de visualisation dynamique pour prendre des décisions avec Excel

Dans ce tutoriel, je vais vous montrer comment transformer une simple feuille Excel en un puissant outil de visualisation interactive pour simuler l'impact de multiples variables sur un résultat final.

Nous allons découvrir comment des listes déroulantes pour mettre à jour instantanément un graphique selon nos choix sans avoir besoin de compétences avancées en programmation.

Cette technique est particulièrement utile pour analyser des scénarios complexes où plusieurs facteurs influencent un résultat, comme l'ouverture d'un nouveau restaurant, le lancement d'un produit, ou même des prévisions de vente.

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

Pour illustrer ce tutoriel, nous allons pouvoir utiliser le tableau suivant dans lequel nous avons identifié dix facteurs clés qui influencent la réussite d'un nouveau restaurant.

Chacun de ces facteurs propose plusieurs options, et chaque option a un impact différent sur le taux de réussite global.

Notre objectif est de créer un simulateur interactif qui nous permettra de visualiser instantanément comment nos choix affectent les chances de succès du restaurant.

Voici le tableau principal que nous allons utiliser comme source de données :

Ici, l'idée est simple : chaque facteur contribue à la réussite globale du restaurant en fonction de l'option choisie.

Par exemple, si nous choisissons un emplacement en centre-ville, cela ajoute 15% à nos chances de réussite, mais si nous optons pour un quartier résidentiel, cela n'ajoute que 5%.

En combinant judicieusement ces facteurs, nous pourrons déterminer la meilleure stratégie pour maximiser les chances de succès.

2. Préparation de notre base de données

La première étape consiste à structurer correctement nos données dans Excel pour faciliter les calculs ultérieurs.

À côté de notre tableau principal, dans la colonne E, nous allons commencer par liste tous les facteurs uniques proposés dans le tableau.

Et pour cela, nous pouvons utiliser une formule UNIQUE.

Dans la cellule E7, nous utilisons la formule suivante :

=UNIQUE(A7:A36)

La fonction UNIQUE est particulièrement utile car elle élimine automatiquement les doublons, et est dynamique, ce qui signifie qu’il suffit de la saisir une seule fois pour que celle-ci s’étende automatiquement et retourne tous les résultats dans la plage des cellules.

En revanche, si vous utilisez une version d'Excel antérieure à Excel 2021 ou 365, la fonction UNIQUE n'est pas disponible.

Dans ce cas, vous pouvez utiliser une autre approche, comme créer manuellement la liste de facteurs uniques, ce qui dans notre cas signifie saisir les 10 facteurs.

Maintenant, pour obtenir les différents choix proposés pour chaque facteur, nous allons utiliser la formule suivante dans la cellule F1 :

=INDEX($B$7:$B$36;EQUIV($E7;$A$7:$A$36;)+F$6-1)

Cette formule combine deux fonctions puissantes :

• EQUIV() : Recherche une valeur spécifique dans une plage et renvoie sa position relative
• INDEX() : Récupère une valeur à une position spécifique dans une plage

Ainsi, avec la fonction EQUIV, nous recherchons la valeur de la cellule E7 (le premier facteur unique) dans la plage A7:A36 afin d’en obtenir la position (par exemple, si "Emplacement" est dans la cellule A7, EQUIV renvoie 1).

Après avoir trouvé la position du facteur, on y ajoute la valeur de F6 moins 1, c’est-à-dire un nombre (1, 2 ou 3) qui indique quelle option du facteur on souhaite récupérer (première, deuxième ou troisième option).

Enfin, la fonction INDEX utilise le résultat du calcul précédent comme position pour extraire la valeur correspondante dans la colonne B.

Pour finir, il ne reste plus qu’à étendre la formule sur les trois colonnes, pour obtenir la liste des trois options d’Emplacement disponibles, puis sur les 10 lignes :

3. Mise en place du système de visualisation interactive

Maintenant, voyons comment ajouter un moyen de sélection de l'une des options proposées rapidement.

Et pour cela, la méthode la plus rapide consiste à utiliser la validation des données.

Il s'agit d'une fonctionnalité d'Excel qui permet de contrôler le type de données qu'un utilisateur peut saisir dans une cellule. Elle offre différentes options comme limiter les entrées à des nombres dans une certaine plage, à des dates spécifiques, ou à des valeurs issues d'une liste prédéfinie. C'est cette dernière option qui nous intéresse particulièrement pour notre tableau de bord interactif, car elle permet de sélectionner des données directement depuis une liste.

Cela transforme une cellule ordinaire en une liste déroulante contenant uniquement les choix que nous avons définis, ce qui rend l'interface plus conviviale et réduit les risques d'erreur de saisie.

Pour cela, nous sélectionnons la cellule I7, puis nous nous rendons dans le menu « Données » > « Validation des données ».

Une boîte de dialogue s'ouvre alors.

Dans le champ « Autoriser », nous sélectionnons « Liste », puis dans le champ « Source », nous cliquons sur le bouton de sélection de plage qui se trouve à droite.

Nous sélectionnons la plage F7:H7, et nous appuyons 3 fois sur la touche F4 pour faire disparaître les symboles « $ », afin de permettre une recopie relative de la formule.

Ensuite, nous validons en appuyant sur le bouton « OK ».

La boîte de dialogue se ferme et comme vous pouvez le constater, une petite flèche apparaît à droite de la cellule I7 lorsque nous la sélectionnons.

En cliquant sur cette flèche, nous faisons apparaître la liste des trois options d'emplacement : Centre-ville, Zone commerciale et Quartier résidentiel.

Nous pouvons maintenant sélectionner l'une des options d'emplacement simplement en cliquant sur la valeur souhaitée dans la liste déroulante.

Cette approche est beaucoup plus intuitive et moins sujette aux erreurs qu'une saisie manuelle.

Ensuite, nous pouvons étendre la validation des données sur les 10 lignes en sélectionnant la cellule I7, puis en cliquant sur la poignée de recopie (le petit carré noir dans le coin inférieur droit de la cellule) et en la faisant glisser jusqu'à la cellule I16. Excel adaptera automatiquement la source de chaque liste déroulante pour qu'elle corresponde à la ligne sur laquelle elle se trouve.

Maintenant, les colonnes F, G et H ne nous sont plus utiles, nous pouvons les masquer, afin de garder notre tableau de bord épuré et centré sur les éléments interactifs.

Maintenant que nos listes déroulantes sont en place, nous devons créer des formules qui récupéreront automatiquement l'impact correspondant à l'option sélectionnée.

Pour cela, nous allons utiliser la fonction SOMME.SI.ENS, qui est parfaite pour ce type de recherche conditionnelle.

Dans la cellule J7 (à côté de la liste déroulante pour l'Emplacement), nous entrons la formule suivante :

=SOMME.SI.ENS($C$7:$C$36;$A$7:$A$36;E7;$B$7:$B$36;I7) 

Cette formule recherche dans notre tableau toutes les lignes où le facteur est « Emplacement » (valeur de la cellule E7) et l'option correspond à celle sélectionnée dans la cellule I7, puis elle additionne les valeurs d'impact correspondantes.

Nous copions cette formule sur toutes les lignes :

Pour la cellule Total (J16), nous utilisons simplement la formule SOMME pour additionner tous les impacts (nous pouvons l’insérer automatiquement en appuyant sur [Alt]+[=] lorsque les cellules sont sélectionnées) :

=SOMME(J7:J16)

À côté de notre tableau, nous allons calculer le « Taux de réussite : ».

Comme le score maximal possible est de 150 (15 points pour chacun des 10 facteurs), nous utilisons la formule suivante dans la cellule K17 :

=J17/150

Ensuite, nous ajoutons une interprétation textuelle.

En E18, nous utilisons une formule SI pour afficher un message adapté :

=SI(K17>=80%;"Succès fort probable  !";SI(K17>=70%;"Succès modéré possible";"Risque élevé  d'échec"))