Réaliser des synoptiques avec Excel

Pour partager cette vidéo sur les réseaux sociaux ou sur un site, voici son url :


Sujets que vous pourriez aussi aimer :


Synoptique Excel - Mise en forme conditionnelle

Dans cette formation nous allons réaliser la représentation graphique d'un phénomène physique à l'aide d'Excel. Nous allons schématiser une cuve d'une certaine contenance. Cette cuve doit pouvoir se remplir ou se vider selon l'ouverture ou la fermeture de ses vannes. Ces variations visuelles doivent réagir à certaines valeurs saisies dans des cellules. Nous allons donc mettre en oeuvre la mise en forme conditionnelle appliquée à la réalisation de synoptiques. Certes le terme synoptique peut sembler peu approprié dans la mesure où nous n'allons pas représenter ici un processus compliqué. Mais dans la méthode, le principe est le même.


Le principe, très simple, est illustré par la capture ci-dessus. Une cuve, sur la gauche de la feuille, peut se remplir à hauteur de la contenance indiquée dans le tableau de bord, représenté sur la droite. Mais des conditions doivent être respectées. Pour que la cuve se remplisse, la vanne de remplissage, celle du haut, doit être ouverte, tandis que la vanne de vidage doit être fermée. Alors l'arrière-plan de la cuve revêt une couleur, matérialisant l'eau, à hauteur de la contenance choisie. Si la vanne de vidage est ouverte, la cuve se vide. Donc le bleu matérialisant l'eau doit disparaître, même si la contenance est indiquée et que la vanne de remplissage est ouverte.

Le panneau de contrôle, liste déroulante et cases à cocher
Ce sont les actions depuis le panneau de contrôle qui vont permettre de modifier dynamiquement l'aspect de la cuve, selon les conditions énoncées précédemment. Nous avons besoin de deux cases à cocher pour ouvrir ou fermer les vannes. Puis nous avons besoin d'une liste déroulante pour choisir une hauteur de remplissage. C'est la raison pour laquelle des nombres sont inscrits dans la colonne R de la feuille. Pour la créer, il suffit de nous inspirer de la formation Excel pour apprendre à créer des listes déroulantes.

• Télécharger le classeur synoptique-excel.xlsx en cliquant sur son lien,
• Puis, l'ouvrir dans Excel,
• Sélectionner la cellule qui doit permettre de définir la contenance, soit N9,
• Cliquer sur l'onglet Données en haut de la fenêtre Excel pour activer son ruban,
• Dans la section Outils de données, cliquer sur le bouton Validation de données,
• Dans la zone Autoriser de la boîte de dialogue qui suit, choisir Liste,
• Cliquer alors dans la zone Source située plus bas pour y placer le point d'insertion,
• Sélectionner les cellules contenant les valeurs à intégrer, soit la plage R5:R15,
• Cliquer sur Ok pour valider la création de la liste déroulante.

De retour sur la feuille, vous remarquez la présence d'une flèche associée à la cellule de la contenance, lorsque cette dernière est sélectionnée. Si vous cliquez sur cette flèche, une liste déroulante se déploie. Et vous remarquez qu'elle contient toutes les valeurs de la plage de cellules que nous avons désignée. Nous avions aussi vu comment créer des listes déroulantes dynamiques sur une source de données évolutive, au cours d'une formation Excel.


La présence des nombres qui ont permis de construire cette liste déroulante, sur la droite de la feuille, est disgracieuse. Comme il s'agit de la source de données à laquelle la liste déroulante est liée, nous ne pouvons pas la supprimer. Mais nous pouvons la masquer. Ainsi elle n'apparaîtra plus, mais existera toujours.

• Cliquer avec le bouton droit de la souris sur l'étiquette R de la colonne,
• Dans le menu contextuel, choisir Masquer,

Les données retournées par la liste déroulante dans la cellule, sont fort logiquement des valeurs numériques brutes. Il s'agit en réalité de Litres et nous souhaitons donner cette information à l'utilisateur. Comme vous le savez, une unité de mesure, tout comme une devise ne doit pas s'écrire dans la cellule. Sinon la cellule serait transformée en texte. Il doit s'agir d'un format à appliquer, soit d'un effet visuel. Nous avions d'ailleurs appris à créer des formats numériques personnalisés au cours d'une formation Excel. Nous allons répliquer ces méthodes :

• Sélectionner de nouveau la cellule de la contenance, soit N9,
• Dans la section Nombre du ruban Accueil, dérouler la liste des formats,
• Tout en bas de la liste, choisir Autres formats numériques,
• Dans la boîte de dialogue qui suit, sélectionner la catégorie Personnalisée dans la liste de gauche,
• Dans la liste centrale, sélectionner le format désigné par le symbole 0 sous la ligne standard,
• Dans la zone Type, cliquer après le symbole 0 pour y placer le point d'insertion,
• Puis taper 'Litres' pour l'unité de mesure,
• Valider ce format numérique par Ok,

Le texte Litre doit bien être saisi entre guillemets. Les guillemets doivent commencer juste après le symbole 0, pour ne pas risquer d'obtenir une valeur divisée. Dans les guillemets, la saisie commence par un espace avant le texte pour que l'unité de mesure ne soit pas collée au nombre.


De retour sur la feuille, vous constatez que toute valeur choisie par le biais de la liste déroulante, s'affiche au format Litre dans sa cellule. La barre de formule quant à elle continue bien d'indiquer la valeur brute contenue par cette cellule, sans l'unité. Cela prouve que le format a bien été appliqué et que la cellule est toujours numérique.

Il s'agit maintenant de proposer des cases à cocher pour que l'utilisateur puisse ouvrir et fermer les vannes. Il faut deux cases à cocher, une pour chaque vanne. Si la case correspondante est cochée, la vanne est ouverte, sinon elle est fermée. Excel propose d'interagir facilement avec ces contrôles. Elles sont présentes dans le ruban Développeur qui n'est pas proposé par défaut. La formation pour débuter la programmation VBA Excel, explique entre autres, comme activer ce ruban.

• Réaliser un clic droit n'importe où sur le ruban actif,
• Dans le menu contextuel, choisir Personnaliser le ruban,
• Dans la liste de droite de la boîte de dialogue qui suit, cocher la case Développeur,
• Valider cette personnalisation en cliquant sur Ok,
• De retour sur Excel, cliquer sur l'onglet Développeur en haut de la fenêtre,
• Dans la section Contrôles du ruban, cliquer sur le bouton Insérer,
• Dans la liste des contrôles, choisir la case à cocher,
• Tracer un rectangle dans la cellule de la première vanne, soit N11,

La case à cocher apparaît dans la cellule avec un texte associé. Nous n'avons pas besoin de ce texte descriptif.

• Cliquer sur la zone de texte associée à la case pour activer sa saisie,
• Supprimer l'intégralité du texte,
• Cliquer avec le bouton droit de la souris sur la case à cocher,
• Dans le menu contextuel, choisir Copier,
• Réaliser un clic droit sur la cellule de la vanne de vidage,
• Dans le menu contextuel, choisir Coller,
• Si nécessaire déplacer la copie ainsi créée afin de bien la positionner dans sa cellule,

En ayant répliqué la première case à cocher, nous obtenons un contrôle sans texte associé. Ces cases à cocher peuvent être liées à des cellules de la feuille. Ainsi elles inscrivent des valeurs dans ces cellules, en fonction de leur état, cochée ou décochée. Ce sont ces valeurs que nos formules récupéreront pour bâtir un format conditionnel, permettant de donner l'illusion que la cuve se remplit ou se vide, selon que les vannes sont ouvertes ou fermées.

• Cliquer avec le bouton droit sur la première case à cocher,
• Dans le menu contextuel, choisir Format de contrôle,
• Activer l'onglet Contrôle de la boîte de dialogue qui suit,
• Cliquer dans la zone cellule liée pour l'activer,
• Sélectionner par exemple la cellule S3 sur la feuille et valider par Ok,
• Réitérer les mêmes opérations pour la seconde case à cocher en la liant à la cellule S4,
• Cliquer n'importe où dans la feuille pour désélectionner la dernière case,

Désormais, lorsque vous cliquez dans les cases pour les cocher ou les décocher, des valeurs associées s'inscrivent dans les cellules liées. VRAI pour cochée, soit vanne ouverte, et FAUX dans le cas contraire. Il nous suffira donc de réaliser nos tests sur les valeurs de ces cellules, qui restituent l'état des cases à cocher et donc des vannes.

Mise en forme conditionnelle et critères
Il s'agit maintenant de faire réagir le remplissage de la cuve, en fonction des données du panneau de contrôle. Et nous verrons que le format dynamique permet de faire réagir des cellules en fonction d'un contenu issu d'autres cellules. En effet, généralement la mise en forme conditionnelle modifie dynamiquement l'aspect des cellules en fonction des valeurs qu'elles contiennent elles-mêmes.

La conduite supérieure qui mène jusqu'à la vanne de remplissage, doit se parer de bleu pour matérialiser l'eau, dès lors qu'une valeur supérieure à 0 est fournie par la liste déroulante. Mais cette eau ne doit pas franchir la vanne si cette dernière est fermée. Il s'agit donc de vérifier des critères en cohérence avec le panneau de contrôle pour appliquer les mises en valeur logiques. Pour ce faire :

• Sélectionner la plage de cellules I5:I7,
• Tout en maintenant la touche CTRL enfoncée, sélectionner la cellule H7,

La touche CTRL permet de réaliser des multi sélections de plages et cellules non contigües. C'est une des techniques enseignées par la formation sur les trucs et astuces dans Excel. Désormais toutes les cellules matérialisant la conduite jusqu'à la vanne, sont sélectionnées. Nous pouvons donc paramétrer un format dynamique pour ces dernières, car concernées par la même règle.

• Cliquer sur le bouton Mise en forme conditionnelle du ruban Accueil,
• Tout en bas de la liste, cliquer sur Nouvelle règle,
• Dans la section Sélectionnez un type de règle de la boîte de dialogue qui suit, choisir la dernière ligne : Utiliser une formule pour déterminer...,
• Cliquer alors dans la zone de saisie, en bas de la boîte de dialogue, afin de définir le critère de la règle,
• Sélectionner la cellule de la liste déroulante sur la feuille, soit N9,
• Et taper >0 pour définir la condition,

Nous obtenons la ligne suivante : =$N$9>0. En d'autres termes, nous demandons à Excel de vérifier si la valeur définie par le biais de la liste déroulante est bien supérieure à 0. Si tel est le cas, nous devons matérialiser l'eau, donc remplir les cellules d'une couleur de remplissage bleue. Pour ce faire :

• Cliquer sur le bouton Format en bas de la boîte de dialogue,
• Activer l'onglet Remplissage de la nouvelle boîte de dialogue qui apparaît,
• Cliquer sur une case de couleur bleue pour définir la couleur de fond,
• Valider par Ok,

Nous sommes de retour sur la première boîte de dialogue. Un aperçu du format est proposé. Cette mise en forme doit se déclencher lorsque la règle définie juste au-dessus est vérifiée.

• Valider de nouveau par Ok,

La première règle est posée. Si vous choisissez une contenance égale à zéro avec la liste déroulante, le bleu disparaît au profit de la couleur d'origine de la conduite, le vert. Si vous choisissez une valeur de contenance supérieure à 0, le bleu remplace le vert, matérialisant l'eau qui demande à s'écouler. Mais pour l'instant elle est bloquée par la vanne fermée. La cuve ne peut donc pas se remplir.

Pour que la cuve se remplisse, de nombreux critères doivent être vérifiés ensemble. D'une part la vanne de remplissage doit être ouverte tandis que dans le même temps, la vanne de vidage doit être fermée. Donc en nous référant aux cellules liées, nous devons vérifier que la cellule S3 indique VRAI quand dans le même temps, la cellule S4 indique FAUX (Et(S3='VRAI'; S4='FAUX')). Mais ce n'est pas tout. La cuve se remplit en fonction de la contenance définie par le biais de la liste déroulante. Pour traduire cette contrainte en critère à vérifier pour le format dynamique, il faut que la valeur choisie dans la liste soit supérieure ou égale à la valeur de la graduation inscrite sur la partie gauche de la cuve.

• Sélectionner toutes les cellules concernées de la cuve soit, C7:E16,
• Cliquer sur le bouton Mise en forme conditionnelle et choisir Nouvelle Règle,
• Dans la boîte de dialogue, sélectionner le dernier type de règle : Utiliser une formule...,
• Cliquer dans la zone de saisie de la formule pour y placer le point d'insertion,
• Taper la formule suivante :

=ET($N$9>=$B7; $S$3=VRAI; $S$4=FAUX)

La mise en place de ce format dynamique est régi par une formule, donc nous débutons par le symbole =. Trois critères doivent être vérifiés ensemble, si l'un d'entre eux n'est pas vrai, la condition globale est considérée comme non vérifiée. Donc nous utilisons la fonction Excel ET pour lister tous les critères, séparés par des points-virgules. Le premier critère consiste à vérifier que la contenance choisie dans la liste déroulante est supérieure ou égale à la graduation ( $N$9>=$B7 ), située en colonne B. Nous initialisons ce critère en prenant comme référence la première cellule de la plage sélectionnée. Nous figeons la cellule de la graduation en colonne (Dollar devant le B) mais pas en ligne. En effet, ce critère est strictement identique pour toutes les cellules de la cuve situées sur la même ligne. En revanche, pour les lignes du dessous, le critère doit être vérifié avec la graduation correspondante, donc la cellule B7 est libre de se déplacer en ligne. Puis nous vérifions en même temps que la vanne de remplissage est bien ouverte ($S$3=VRAI) et que la vanne de vidage est bien fermée ($S$4=FAUX).

• Cliquer sur le bouton Format et choisir une couleur de remplissage bleue,
• Puis, valider les deux boîtes de dialogue,

Si vous ouvrez la première vanne en cochant sa case, que vous fermez la seconde, et que dans le même temps vous choisissez une contenance avec la liste déroulante, la cuve se remplit instantanément jusqu'à la graduation équivalente.

Il reste à remplir dynamiquement les parties manquantes des conduites. Concernant la conduite de vidage, toutes les cellules situées après la vanne doivent se remplir de bleu selon trois conditions. La contenance doit être supérieure à zéro et dans le même temps, la vanne de vidage et la vanne de remplissage doivent être ouvertes pour permettre l'écoulement.

• Sélectionner les cellules situées après la vanne de vidage, soit G16, H16, I16 et I17,
• Cliquer sur le bouton Mise en forme conditionnelle du ruban et choisir Nouvelle règle,
• Dans la boîte de dialogue, sélectionner le dernier type : Utiliser une formuleÂ…,
• Dans la zone de saisie, taper la formule suivante pour vérifier les trois critères :

=ET($N$9>0; $S$3=VRAI; $S$4=VRAI)

Nous utilisons de nouveau la fonction Excel ET pour vérifier les trois conditions ensemble. La contenance doit être définie au-dessus de zéro ($N$9>0). La vanne de remplissage doit être ouverte en même temps que la vanne de vidage, pour permettre l'écoulement ($S$3=VRAI;$S$4=VRAI).

• Cliquer sur le bouton Format et définir une couleur de remplissage bleue,
• Valider les deux boîtes de dialogue,

Pour que les parties restantes des conduites se remplissent, deux conditions suffisent. La contenance doit être supérieure à zéro et la vanne de remplissage doit être ouverte. Ces deux parties sont les cellules de la conduite de remplissage après la vanne et les cellules de la conduite de vidage, avant la vanne.

• Sélectionner les cellules G7, F7 et F16 à l'aide de la touche CTRL,
• Cliquer sur le bouton Mise en forme conditionnelle et choisir Nouvelle règle,
• Sélectionner le dernier type de règle : Utiliser une formuleÂ…,
• Dans la zone de saisie, taper la formule suivante :

=ET($N$9>0; $S$3=VRAI)

La contenance doit être définie au-delà de zéro ($N$9>0) en même temps que la première vanne doit être ouverte ($S$3=VRAI)).

Il reste une dernière spécificité pour les cellules du bas de la cuve. Le niveau doit correspondre quand la vanne de vidage est ouverte. Les cellules en regard de la graduation 1L doivent être remplies même quand la cuve se vide et que la contenance est définie. Excel permet de cumuler des règles de format dynamique sur une même plage de cellules.

• Sélectionner la plage de cellules C16:E16,
• Cliquer sur le bouton Mise en forme conditionnelle et choisir Nouvelle règle,
• Choisir le dernier type de règle : Utiliser une formule,
• Dans la zone de saisie, taper la formule suivante :

=ET($S$4=VRAI; $S$3=VRAI; $N$9>0)

La vanne de vidage doit être ouverte ($S$4=VRAI) en même temps que la contenance doit être définie ($N$9>0) et que la vanne de remplissage est ouverte ($S$3=VRAI). Comme les formats conditionnels peuvent se cumuler lorsqu'ils ne sont pas contradictoires, ces cellules resteront remplies de bleu lorsque la vanne de vidage est fermée et que la vanne de remplissage est ouverte et que la contenance est définie.

• Comme toujours cliquer sur le bouton Format pour définir un remplissage bleu,
• Puis valider les deux boîtes de dialogue,

Il ne reste plus qu'à tester en faisant varier les contraintes à l'aide des contrôles du panneau de contrôle. Dans l'exemple de la capture ci-dessous, la contenance est définie à 8 Litres. La cuve se remplit jusqu'à la hauteur correspondante de la graduation. En effet, dans le même temps la vanne de remplissage est ouverte tandis que la vanne de vidage est fermée pour empêcher l'écoulement.


Dans cet autre exemple, illustré ci-dessous, bien que la contenance soit définie sur 8 Litres et que la vanne de remplissage soit ouverte, la cuve ne dépasse pas les 1 Litre. En effet, dans le même temps, la vanne de vidage est ouverte et on considère que le débit de sa conduite est supérieur.